ems/db/routines/R__042_fn_predict_negative_prices.sql

-- =============================================================
-- R__042_fn_predict_negative_prices.sql
-- Predikce oken se zvýšeným rizikem záporné spotové ceny (OTE).
-- Volat denně po importu cen a po forecastu FVE; výsledky ukládá do
-- ems.predicted_negative_price_window.
-- =============================================================

CREATE OR REPLACE FUNCTION ems.fn_predict_negative_price_windows(
    p_site_id INT,
    p_days_ahead INT DEFAULT 7
)
RETURNS TABLE (
    predicted_date DATE,
    window_start_hour INT,
    window_end_hour INT,
    probability_pct INT,
    expected_min_price NUMERIC(10, 4),
    reason TEXT
)
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE
    v_start DATE;
    v_end DATE;
    v_days INT;
BEGIN
    v_days := COALESCE(p_days_ahead, 7);
    IF v_days < 1 OR v_days > 60 THEN
        RAISE EXCEPTION 'p_days_ahead must be between 1 and 60, got %', p_days_ahead;
    END IF;

    v_start := (CURRENT_TIMESTAMP AT TIME ZONE 'Europe/Prague')::DATE + 1;
    v_end := (CURRENT_TIMESTAMP AT TIME ZONE 'Europe/Prague')::DATE + v_days;

    IF NOT EXISTS (SELECT 1 FROM ems.site s WHERE s.id = p_site_id) THEN
        RAISE EXCEPTION 'site not found: %', p_site_id;
    END IF;

    DELETE FROM ems.predicted_negative_price_window p
    WHERE p.site_id = p_site_id
      AND p.predicted_date BETWEEN v_start AND v_end;

    RETURN QUERY
    WITH hist_price AS (
        SELECT
            EXTRACT(DOW FROM mip.interval_start AT TIME ZONE 'Europe/Prague')::INT AS dow,
            EXTRACT(HOUR FROM mip.interval_start AT TIME ZONE 'Europe/Prague')::INT AS hour,
            COUNT(*)::INT AS total_slots,
            COUNT(*) FILTER (WHERE mip.buy_raw_price_czk_kwh < 0)::INT AS neg_slots,
            AVG(mip.buy_raw_price_czk_kwh) AS avg_price,
            MIN(mip.buy_raw_price_czk_kwh) AS min_price
        FROM ems.market_interval_price mip
        WHERE mip.market_source IN ('OTE_CZ', 'OTE_CZ_DAM')
          AND mip.interval_start >= NOW() - INTERVAL '6 months'
        GROUP BY 1, 2
        HAVING COUNT(*) >= 4
    ),
    latest_run AS (
        SELECT fpr.id
        FROM ems.forecast_pv_run fpr
        WHERE fpr.site_id = p_site_id
          AND fpr.status = 'ok'
        ORDER BY fpr.created_at DESC NULLS LAST
        LIMIT 1
    ),
    slot_power_hist AS (
        SELECT
            fpi.interval_start,
            SUM(fpi.power_w)::BIGINT AS total_w
        FROM ems.forecast_pv_interval fpi
        INNER JOIN ems.forecast_pv_run fpr ON fpr.id = fpi.run_id
        WHERE fpr.site_id = p_site_id
          AND fpr.status = 'ok'
          AND fpi.interval_start >= NOW() - INTERVAL '6 months'
        GROUP BY fpi.interval_start
    ),
    pv_max_by_hour AS (
        SELECT
            EXTRACT(HOUR FROM sp.interval_start AT TIME ZONE 'Europe/Prague')::INT AS hour,
            MAX(sp.total_w)::BIGINT AS hist_max_w
        FROM slot_power_hist sp
        GROUP BY 1
    ),
    pred_slot AS (
        SELECT
            fpi.interval_start,
            SUM(fpi.power_w)::BIGINT AS total_w
        FROM ems.forecast_pv_interval fpi
        INNER JOIN latest_run lr ON lr.id = fpi.run_id
        GROUP BY fpi.interval_start
    ),
    pred_by_hour AS (
        SELECT
            (ps.interval_start AT TIME ZONE 'Europe/Prague')::DATE AS d,
            EXTRACT(HOUR FROM ps.interval_start AT TIME ZONE 'Europe/Prague')::INT AS hour,
            MAX(ps.total_w)::BIGINT AS pred_max_w
        FROM pred_slot ps
        GROUP BY 1, 2
    ),
    future_days AS (
        SELECT gs::DATE AS d
        FROM generate_series(v_start, v_end, INTERVAL '1 day') AS gs
    ),
    hourly_base AS (
        SELECT
            fd.d AS predicted_date,
            h.hour,
            EXTRACT(DOW FROM fd.d)::INT AS dow,
            LEAST(
                100,
                GREATEST(
                    0,
                    (100.0 * hp.neg_slots / NULLIF(hp.total_slots, 0))::INT
                )
            ) AS base_prob,
            hp.min_price
        FROM future_days fd
        CROSS JOIN generate_series(0, 23) AS h (hour)
        INNER JOIN hist_price hp
            ON hp.dow = EXTRACT(DOW FROM fd.d)::INT
           AND hp.hour = h.hour
    ),
    hourly_adj AS (
        SELECT
            hb.predicted_date,
            hb.hour,
            hb.dow,
            hb.base_prob,
            hb.min_price,
            CASE
                WHEN pm.hist_max_w IS NULL OR pm.hist_max_w <= 0 THEN hb.base_prob
                WHEN ph.pred_max_w IS NULL THEN hb.base_prob
                WHEN ph.pred_max_w::NUMERIC > 0.8 * pm.hist_max_w::NUMERIC THEN
                    LEAST(100, hb.base_prob + 15)
                WHEN ph.pred_max_w::NUMERIC < 0.4 * pm.hist_max_w::NUMERIC THEN
                    GREATEST(0, hb.base_prob - 20)
                ELSE hb.base_prob
            END AS probability_pct
        FROM hourly_base hb
        LEFT JOIN pred_by_hour ph
            ON ph.d = hb.predicted_date
           AND ph.hour = hb.hour
        LEFT JOIN pv_max_by_hour pm ON pm.hour = hb.hour
    ),
    qualified AS (
        SELECT
            ha.predicted_date,
            ha.hour,
            ha.probability_pct,
            ha.min_price AS expected_hour_min,
            ha.hour
                - ROW_NUMBER() OVER (
                    PARTITION BY ha.predicted_date, ha.probability_pct
                    ORDER BY ha.hour
                ) AS grp
        FROM hourly_adj ha
        WHERE ha.probability_pct >= 30
    ),
    windows AS (
        SELECT
            q.predicted_date,
            q.probability_pct,
            MIN(q.hour) AS window_start_hour,
            MAX(q.hour) AS window_end_hour,
            MIN(q.expected_hour_min) AS expected_min_price
        FROM qualified q
        GROUP BY q.predicted_date, q.probability_pct, q.grp
    ),
    final_rows AS (
        SELECT
            w.predicted_date,
            w.window_start_hour,
            w.window_end_hour,
            w.probability_pct,
            ROUND(w.expected_min_price::NUMERIC, 4) AS expected_min_price,
            CASE
                WHEN w.probability_pct >= 70 THEN
                    'Historicky vysoká FVE výroba v tento čas – velmi pravděpodobné'
                WHEN w.probability_pct >= 50 THEN
                    'Víkendový vzor + dobrá předpověď FVE'
                ELSE
                    'Možné na základě historických dat'
            END AS reason
        FROM windows w
        WHERE w.probability_pct >= 25
    ),
    ins AS (
        INSERT INTO ems.predicted_negative_price_window (
            site_id,
            predicted_date,
            window_start_hour,
            window_end_hour,
            probability_pct,
            expected_min_price,
            reason
        )
        SELECT
            p_site_id,
            fr.predicted_date,
            fr.window_start_hour,
            fr.window_end_hour,
            fr.probability_pct,
            fr.expected_min_price,
            fr.reason
        FROM final_rows fr
        RETURNING
            predicted_negative_price_window.predicted_date,
            predicted_negative_price_window.window_start_hour,
            predicted_negative_price_window.window_end_hour,
            predicted_negative_price_window.probability_pct,
            predicted_negative_price_window.expected_min_price,
            predicted_negative_price_window.reason
    )
    SELECT
        ins.predicted_date,
        ins.window_start_hour,
        ins.window_end_hour,
        ins.probability_pct,
        ins.expected_min_price,
        ins.reason
    FROM ins;
END;
$$;

COMMENT ON FUNCTION ems.fn_predict_negative_price_windows(INT, INT) IS
'Predikuje okna se zvýšeným rizikem záporné nákupní ceny z historie OTE (6 měsíců), upraví podle forecastu FVE a zapíše do ems.predicted_negative_price_window. Volat denně po importu cen a forecastu.';