-- Doplní jeden řádek baseline_load_forecast_accuracy po vyplnění auditu (stejný interval).

CREATE OR REPLACE FUNCTION ems.fn_fill_baseline_load_forecast_accuracy(
    p_site_id INT,
    p_interval_start TIMESTAMPTZ
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    v_run_id              INT;
    v_forecast            INT;
    v_actual_load         INT;
    v_actual_ev           INT;
    v_actual_hp           INT;
    v_actual_baseline     INT;
    v_err                 INT;
    v_pct                 NUMERIC(8,4);
    v_lead                NUMERIC(6,2);
    v_created             TIMESTAMPTZ;
BEGIN
    SELECT
        ai.planning_run_id,
        ai.actual_load_power_w,
        ai.actual_ev_power_w,
        ai.actual_heat_pump_power_w
    INTO v_run_id, v_actual_load, v_actual_ev, v_actual_hp
    FROM ems.audit_interval ai
    WHERE ai.site_id = p_site_id
      AND ai.interval_start = p_interval_start;

    IF v_run_id IS NULL THEN
        RETURN;
    END IF;

    SELECT pi.load_baseline_w
    INTO v_forecast
    FROM ems.planning_interval pi
    WHERE pi.run_id = v_run_id
      AND pi.interval_start = p_interval_start;

    IF v_forecast IS NULL THEN
        RETURN;
    END IF;

    IF v_actual_load IS NULL THEN
        RETURN;
    END IF;

    v_actual_baseline := GREATEST(0,
        v_actual_load
        - COALESCE(v_actual_ev, 0)
        - COALESCE(v_actual_hp, 0)
    );

    v_err := v_forecast - v_actual_baseline;
    IF v_actual_baseline > 0 THEN
        v_pct := (v_err::NUMERIC / v_actual_baseline) * 100.0;
    ELSE
        v_pct := NULL;
    END IF;

    SELECT pr.created_at INTO v_created
    FROM ems.planning_run pr
    WHERE pr.id = v_run_id;

    IF v_created IS NOT NULL THEN
        v_lead := EXTRACT(EPOCH FROM (p_interval_start - v_created)) / 3600.0;
    ELSE
        v_lead := NULL;
    END IF;

    INSERT INTO ems.baseline_load_forecast_accuracy (
        site_id,
        interval_start,
        planning_run_id,
        forecast_baseline_w,
        actual_baseline_w,
        error_w,
        error_pct,
        lead_time_hours,
        filled_at
    ) VALUES (
        p_site_id,
        p_interval_start,
        v_run_id,
        v_forecast,
        v_actual_baseline,
        v_err,
        v_pct,
        v_lead,
        now()
    )
    ON CONFLICT (site_id, interval_start) DO UPDATE SET
        planning_run_id       = EXCLUDED.planning_run_id,
        forecast_baseline_w   = EXCLUDED.forecast_baseline_w,
        actual_baseline_w     = EXCLUDED.actual_baseline_w,
        error_w               = EXCLUDED.error_w,
        error_pct             = EXCLUDED.error_pct,
        lead_time_hours       = EXCLUDED.lead_time_hours,
        filled_at             = EXCLUDED.filled_at;
END;
$$;

COMMENT ON FUNCTION ems.fn_fill_baseline_load_forecast_accuracy IS
'Po fn_fill_audit_interval: uloží odchylku plánovaného load_baseline vs. skutečný bazál z auditu.';
