solorpower/supabase/tests/stats_write_consistency_test.sql

\set ON_ERROR_STOP on

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'anon') THEN
        CREATE ROLE anon NOLOGIN;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'authenticated') THEN
        CREATE ROLE authenticated NOLOGIN;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'service_role') THEN
        CREATE ROLE service_role NOLOGIN;
    END IF;
END;
$$;

CREATE TABLE public.plants (
    id text PRIMARY KEY,
    capacity double precision
);

CREATE TABLE public.daily_stats (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    plant_id text NOT NULL REFERENCES public.plants(id),
    date date NOT NULL,
    total_generation double precision DEFAULT 0,
    peak_kw double precision DEFAULT 0,
    generation_hours double precision DEFAULT 0,
    created_at timestamp with time zone DEFAULT now(),
    UNIQUE (plant_id, date)
);

CREATE TABLE public.monthly_stats (
    id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    plant_id text NOT NULL REFERENCES public.plants(id),
    month text NOT NULL,
    total_generation double precision DEFAULT 0,
    currnet_last_date text,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    UNIQUE (plant_id, month)
);

INSERT INTO public.plants (id, capacity) VALUES ('plant-a', 100);
INSERT INTO public.monthly_stats (plant_id, month, total_generation)
VALUES ('plant-a', '2026-05', 500);

\ir ../migrations/20260807000001_stats_write_consistency.sql

SELECT public.upsert_daily_stats(
    '[{"plant_id":"plant-a","date":"2026-06-01","total_generation":100,"peak_kw":20}]'::jsonb,
    'realtime',
    false
);

SELECT public.upsert_daily_stats(
    '[{"plant_id":"plant-a","date":"2026-06-01","total_generation":80,"peak_kw":15}]'::jsonb,
    'history',
    false
);

DO $$
DECLARE
    v_daily public.daily_stats%ROWTYPE;
    v_monthly public.monthly_stats%ROWTYPE;
BEGIN
    SELECT * INTO v_daily FROM public.daily_stats
    WHERE plant_id = 'plant-a' AND date = '2026-06-01';
    IF v_daily.total_generation <> 100
       OR v_daily.peak_kw <> 20
       OR v_daily.generation_hours <> 1
       OR v_daily.source <> 'realtime' THEN
        RAISE EXCEPTION 'automated decrease protection failed: %', row_to_json(v_daily);
    END IF;

    SELECT * INTO v_monthly FROM public.monthly_stats
    WHERE plant_id = 'plant-a' AND month = '2026-06';
    IF v_monthly.total_generation <> 100
       OR v_monthly.last_date <> '2026-06-01'::date
       OR v_monthly.source <> 'derived_daily' THEN
        RAISE EXCEPTION 'monthly trigger failed: %', row_to_json(v_monthly);
    END IF;
END;
$$;

SELECT public.upsert_daily_stats(
    '[{"plant_id":"plant-a","date":"2026-06-01","total_generation":120,"peak_kw":25}]'::jsonb,
    'history',
    false
);

SELECT public.upsert_daily_stats(
    '[{"plant_id":"plant-a","date":"2026-06-01","total_generation":90,"peak_kw":0}]'::jsonb,
    'excel_daily',
    true
);

DO $$
DECLARE
    v_daily public.daily_stats%ROWTYPE;
    v_monthly public.monthly_stats%ROWTYPE;
BEGIN
    SELECT * INTO v_daily FROM public.daily_stats
    WHERE plant_id = 'plant-a' AND date = '2026-06-01';
    IF v_daily.total_generation <> 90
       OR v_daily.peak_kw <> 25
       OR v_daily.generation_hours <> 0.9
       OR v_daily.source <> 'excel_daily' THEN
        RAISE EXCEPTION 'manual correction policy failed: %', row_to_json(v_daily);
    END IF;

    SELECT * INTO v_monthly FROM public.monthly_stats
    WHERE plant_id = 'plant-a' AND month = '2026-06';
    IF v_monthly.total_generation <> 90 THEN
        RAISE EXCEPTION 'monthly correction sync failed: %', row_to_json(v_monthly);
    END IF;
END;
$$;

INSERT INTO public.monthly_stats (
    plant_id, month, total_generation, last_date, source
) VALUES (
    'plant-a', '2026-07', 777, '2026-07-31', 'excel_monthly'
);

SELECT public.upsert_daily_stats(
    '[{"plant_id":"plant-a","date":"2026-07-01","total_generation":10,"peak_kw":1}]'::jsonb,
    'history',
    false
);

DO $$
DECLARE
    v_total double precision;
    v_source text;
    v_legacy_total double precision;
BEGIN
    SELECT total_generation, source INTO v_total, v_source
    FROM public.monthly_stats
    WHERE plant_id = 'plant-a' AND month = '2026-07';
    IF v_total <> 777 OR v_source <> 'excel_monthly' THEN
        RAISE EXCEPTION 'authoritative monthly value was overwritten';
    END IF;

    SELECT total_generation INTO v_legacy_total
    FROM public.monthly_stats
    WHERE plant_id = 'plant-a' AND month = '2026-05';
    IF v_legacy_total <> 500 THEN
        RAISE EXCEPTION 'legacy monthly value was changed during migration';
    END IF;
END;
$$;

SELECT 'stats_write_consistency_test passed' AS result;