-- Centralize daily-stat write policy and keep monthly aggregates in sync.

ALTER TABLE public.daily_stats
    ADD COLUMN IF NOT EXISTS updated_at timestamp with time zone NOT NULL DEFAULT now(),
    ADD COLUMN IF NOT EXISTS source text NOT NULL DEFAULT 'legacy';

UPDATE public.daily_stats
SET updated_at = COALESCE(created_at, now())
WHERE updated_at IS NULL;

ALTER TABLE public.daily_stats
    DROP CONSTRAINT IF EXISTS daily_stats_source_check;

ALTER TABLE public.daily_stats
    ADD CONSTRAINT daily_stats_source_check CHECK (
        source IN ('legacy', 'realtime', 'daily_summary', 'history', 'excel_daily')
    );

ALTER TABLE public.monthly_stats
    RENAME COLUMN currnet_last_date TO last_date;

ALTER TABLE public.monthly_stats
    ALTER COLUMN last_date TYPE date
    USING CASE
        WHEN last_date IS NULL OR btrim(last_date) = '' THEN NULL
        WHEN last_date ~ '^\d{4}-\d{2}-\d{2}$' THEN last_date::date
        ELSE NULL
    END;

ALTER TABLE public.monthly_stats
    ADD COLUMN IF NOT EXISTS source text NOT NULL DEFAULT 'legacy';

ALTER TABLE public.monthly_stats
    DROP CONSTRAINT IF EXISTS monthly_stats_source_check;

ALTER TABLE public.monthly_stats
    ADD CONSTRAINT monthly_stats_source_check CHECK (
        source IN ('legacy', 'derived_daily', 'excel_monthly', 'history_monthly')
    );

COMMENT ON COLUMN public.daily_stats.updated_at IS '통계 값이 실제로 변경된 시각';
COMMENT ON COLUMN public.daily_stats.source IS '현재 일 발전량 값을 결정한 저장 경로';
COMMENT ON COLUMN public.monthly_stats.last_date IS '월간 합계에 포함된 마지막 일자';
COMMENT ON COLUMN public.monthly_stats.source IS '월간 값의 생성 경로';

CREATE OR REPLACE FUNCTION public.refresh_monthly_stat(
    p_plant_id text,
    p_month text
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
DECLARE
    v_month_start date;
    v_next_month date;
    v_total double precision;
    v_last_date date;
    v_count integer;
BEGIN
    IF p_month !~ '^\d{4}-(0[1-9]|1[0-2])$' THEN
        RAISE EXCEPTION 'invalid month: %', p_month;
    END IF;

    v_month_start := (p_month || '-01')::date;
    v_next_month := (v_month_start + interval '1 month')::date;

    SELECT
        COALESCE(sum(ds.total_generation), 0)::double precision,
        max(ds.date),
        count(*)::integer
    INTO v_total, v_last_date, v_count
    FROM public.daily_stats AS ds
    WHERE ds.plant_id = p_plant_id
      AND ds.date >= v_month_start
      AND ds.date < v_next_month;

    IF v_count = 0 THEN
        DELETE FROM public.monthly_stats AS ms
        WHERE ms.plant_id = p_plant_id
          AND ms.month = p_month
          AND ms.source NOT IN ('excel_monthly', 'history_monthly');
        RETURN;
    END IF;

    INSERT INTO public.monthly_stats AS ms (
        plant_id,
        month,
        total_generation,
        last_date,
        updated_at,
        source
    ) VALUES (
        p_plant_id,
        p_month,
        round(v_total::numeric, 2)::double precision,
        v_last_date,
        now(),
        'derived_daily'
    )
    ON CONFLICT (plant_id, month) DO UPDATE
    SET total_generation = EXCLUDED.total_generation,
        last_date = EXCLUDED.last_date,
        updated_at = CASE
            WHEN ms.total_generation IS DISTINCT FROM EXCLUDED.total_generation
              OR ms.last_date IS DISTINCT FROM EXCLUDED.last_date
              OR ms.source IS DISTINCT FROM EXCLUDED.source
            THEN now()
            ELSE ms.updated_at
        END,
        source = EXCLUDED.source
    WHERE ms.source NOT IN ('excel_monthly', 'history_monthly');
END;
$$;

CREATE OR REPLACE FUNCTION public.sync_monthly_stat_from_daily()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
    IF TG_OP = 'DELETE' THEN
        PERFORM public.refresh_monthly_stat(OLD.plant_id, to_char(OLD.date, 'YYYY-MM'));
        RETURN OLD;
    END IF;

    IF TG_OP = 'UPDATE'
       AND OLD.plant_id IS NOT DISTINCT FROM NEW.plant_id
       AND OLD.date IS NOT DISTINCT FROM NEW.date
       AND OLD.total_generation IS NOT DISTINCT FROM NEW.total_generation THEN
        RETURN NEW;
    END IF;

    IF TG_OP = 'UPDATE'
       AND (OLD.plant_id IS DISTINCT FROM NEW.plant_id OR OLD.date IS DISTINCT FROM NEW.date) THEN
        PERFORM public.refresh_monthly_stat(OLD.plant_id, to_char(OLD.date, 'YYYY-MM'));
    END IF;

    PERFORM public.refresh_monthly_stat(NEW.plant_id, to_char(NEW.date, 'YYYY-MM'));
    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS daily_stats_sync_monthly ON public.daily_stats;

CREATE TRIGGER daily_stats_sync_monthly
AFTER INSERT OR UPDATE OR DELETE ON public.daily_stats
FOR EACH ROW EXECUTE FUNCTION public.sync_monthly_stat_from_daily();

CREATE OR REPLACE FUNCTION public.upsert_daily_stats(
    p_records jsonb,
    p_source text,
    p_allow_decrease boolean DEFAULT false
)
RETURNS SETOF public.daily_stats
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
DECLARE
    v_record jsonb;
    v_plant_id text;
    v_date date;
    v_total double precision;
    v_peak double precision;
    v_capacity double precision;
    v_result public.daily_stats%ROWTYPE;
BEGIN
    IF jsonb_typeof(p_records) <> 'array' THEN
        RAISE EXCEPTION 'p_records must be a JSON array';
    END IF;

    IF p_source NOT IN ('realtime', 'daily_summary', 'history', 'excel_daily') THEN
        RAISE EXCEPTION 'invalid daily stats source: %', p_source;
    END IF;

    FOR v_record IN SELECT value FROM jsonb_array_elements(p_records)
    LOOP
        v_plant_id := NULLIF(btrim(v_record ->> 'plant_id'), '');
        v_date := (v_record ->> 'date')::date;
        v_total := (v_record ->> 'total_generation')::double precision;
        v_peak := COALESCE((v_record ->> 'peak_kw')::double precision, 0);

        IF v_plant_id IS NULL THEN
            RAISE EXCEPTION 'plant_id is required';
        END IF;
        IF v_total < 0 OR v_peak < 0 THEN
            RAISE EXCEPTION 'generation values must be non-negative: % %', v_plant_id, v_date;
        END IF;

        SELECT COALESCE(p.capacity, 0)
        INTO v_capacity
        FROM public.plants AS p
        WHERE p.id = v_plant_id;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'unknown plant_id: %', v_plant_id;
        END IF;

        INSERT INTO public.daily_stats AS ds (
            plant_id,
            date,
            total_generation,
            peak_kw,
            generation_hours,
            created_at,
            updated_at,
            source
        ) VALUES (
            v_plant_id,
            v_date,
            round(v_total::numeric, 2)::double precision,
            round(v_peak::numeric, 2)::double precision,
            CASE
                WHEN v_capacity > 0
                THEN round((v_total / v_capacity)::numeric, 2)::double precision
                ELSE 0
            END,
            now(),
            now(),
            p_source
        )
        ON CONFLICT (plant_id, date) DO UPDATE
        SET total_generation = CASE
                WHEN p_allow_decrease OR EXCLUDED.total_generation > ds.total_generation
                THEN EXCLUDED.total_generation
                ELSE ds.total_generation
            END,
            peak_kw = GREATEST(COALESCE(ds.peak_kw, 0), EXCLUDED.peak_kw),
            generation_hours = CASE
                WHEN v_capacity > 0 THEN round((
                    (CASE
                        WHEN p_allow_decrease OR EXCLUDED.total_generation > ds.total_generation
                        THEN EXCLUDED.total_generation
                        ELSE ds.total_generation
                    END) / v_capacity
                )::numeric, 2)::double precision
                ELSE 0
            END,
            updated_at = CASE
                WHEN ds.total_generation IS DISTINCT FROM (
                    CASE
                        WHEN p_allow_decrease OR EXCLUDED.total_generation > ds.total_generation
                        THEN EXCLUDED.total_generation
                        ELSE ds.total_generation
                    END
                ) OR ds.peak_kw IS DISTINCT FROM GREATEST(COALESCE(ds.peak_kw, 0), EXCLUDED.peak_kw)
                THEN now()
                ELSE ds.updated_at
            END,
            source = CASE
                WHEN ds.total_generation IS DISTINCT FROM EXCLUDED.total_generation
                     AND (p_allow_decrease OR EXCLUDED.total_generation > ds.total_generation)
                THEN p_source
                ELSE ds.source
            END
        RETURNING ds.* INTO v_result;

        RETURN NEXT v_result;
    END LOOP;

    RETURN;
END;
$$;

REVOKE ALL ON FUNCTION public.upsert_daily_stats(jsonb, text, boolean) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.upsert_daily_stats(jsonb, text, boolean)
    TO anon, authenticated, service_role;

REVOKE ALL ON FUNCTION public.refresh_monthly_stat(text, text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.refresh_monthly_stat(text, text)
    TO authenticated, service_role;

-- Preserve legacy monthly totals, but populate their last included daily date.
UPDATE public.monthly_stats AS ms
SET last_date = daily.last_date
FROM (
    SELECT plant_id, to_char(date, 'YYYY-MM') AS month, max(date) AS last_date
    FROM public.daily_stats
    GROUP BY plant_id, to_char(date, 'YYYY-MM')
) AS daily
WHERE ms.plant_id = daily.plant_id
  AND ms.month = daily.month
  AND ms.last_date IS DISTINCT FROM daily.last_date;

-- Create or refresh only the current KST month. Historical totals remain untouched.
DO $$
DECLARE
    v_pair record;
    v_current_month text := to_char(timezone('Asia/Seoul', now()), 'YYYY-MM');
BEGIN
    FOR v_pair IN
        SELECT DISTINCT plant_id
        FROM public.daily_stats
        WHERE date >= (v_current_month || '-01')::date
          AND date < ((v_current_month || '-01')::date + interval '1 month')::date
    LOOP
        PERFORM public.refresh_monthly_stat(v_pair.plant_id, v_current_month);
    END LOOP;
END;
$$;