-- ============================================================
-- Profil systematické chyby PV forecastu po 15min slotu dne
-- (aditivní korekce per pole: corrected_i = max(0, forecast_i - delta_i[slot]))
-- + součtový profil `deltas` pro starší klienty (součet delt přes pole).
-- ============================================================

create or replace function ems.fn_pv_forecast_delta_profile(
  p_site_id int,
  p_data_from timestamptz,
  p_data_to timestamptz DEFAULT now(),
  p_half_life_days numeric DEFAULT 14,
  p_threshold_w int DEFAULT 150,
  p_top_n_days int DEFAULT 3,
  p_non_top_day_factor numeric DEFAULT 0.02,
  p_day_weight_gamma numeric DEFAULT 1.0
)
RETURNS jsonb
LANGUAGE sql
STABLE
SET work_mem = '64MB'
AS $fn$
  WITH   eff AS (
    SELECT
      coalesce(cal.delta_learn_min_ts, timestamptz '2026-04-11T22:00:00Z') AS delta_learn_min_ts,
      coalesce(cal.half_life_days, p_half_life_days) AS half_life_days,
      coalesce(cal.threshold_w, p_threshold_w) AS threshold_w,
      coalesce(cal.top_n_days, p_top_n_days) AS top_n_days,
      coalesce(cal.non_top_day_factor, p_non_top_day_factor) AS non_top_day_factor,
      coalesce(cal.day_weight_gamma, p_day_weight_gamma) AS day_weight_gamma,
      greatest(1::numeric, coalesce(cal.reference_day_weight_mult, 3::numeric))
        AS reference_day_w_mult
    FROM ems.site s
    LEFT JOIN ems.site_pv_forecast_calibration cal ON cal.site_id = s.id
    WHERE s.id = p_site_id
  ),
  tz AS (
    SELECT coalesce(nullif(trim(s.timezone), ''), 'Europe/Prague') AS tz_name
    FROM ems.site s
    WHERE s.id = p_site_id
  ),
  bounds AS (
    SELECT
      greatest(
        p_data_from,
        p_data_to - interval '120 days',
        (SELECT delta_learn_min_ts FROM eff)
      ) AS ts_from,
      p_data_to AS ts_to,
      greatest((SELECT half_life_days FROM eff), 1::numeric) AS half_life_days,
      greatest((SELECT threshold_w FROM eff), 0::numeric) AS threshold_w
  ),
  best AS (
    select distinct on (fa.interval_start, fa.pv_array_id)
      fa.interval_start,
      fa.pv_array_id,
      fa.forecast_power_w,
      fa.actual_power_w,
      fa.forecast_created_at
    from ems.forecast_accuracy fa
    cross join bounds b
    where fa.site_id = p_site_id
      and fa.interval_start >= b.ts_from
      and fa.interval_start < b.ts_to
      and fa.actual_power_w is not null
      and fa.forecast_created_at <= fa.interval_start
      and coalesce(fa.learning_eligible, true) is true
    order by fa.interval_start, fa.pv_array_id, fa.forecast_created_at desc
  ),
  slots AS (
    SELECT
      b.interval_start,
      b.pv_array_id,
      b.forecast_power_w::numeric AS forecast_w,
      b.actual_power_w::numeric AS actual_w,
      (
        (extract(hour FROM (b.interval_start AT TIME ZONE tz.tz_name))::int * 60)
        + extract(minute FROM (b.interval_start AT TIME ZONE tz.tz_name))::int
      ) / 15 AS slot_of_day,
      (b.interval_start AT TIME ZONE tz.tz_name)::date AS day_local,
      extract(epoch FROM (now() - b.interval_start)) / 86400.0 AS age_days
    FROM best b
    CROSS JOIN tz
  ),
  slot_totals AS (
    SELECT
      s.interval_start,
      s.day_local,
      s.slot_of_day,
      max(s.age_days) AS age_days,
      sum(s.forecast_w) AS forecast_total_w,
      sum(s.actual_w) AS actual_total_w
    FROM slots s
    GROUP BY s.interval_start, s.day_local, s.slot_of_day
  ),
  day_energy AS (
    SELECT st.day_local, sum(st.actual_total_w)::numeric / 4000.0 AS energy_kwh
    FROM slot_totals st
    GROUP BY st.day_local
  ),
  ref AS (
    SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY de.energy_kwh) AS med_kwh
    FROM day_energy de
  ),
  slot_steps AS (
    SELECT
      st.*,
      lag(st.actual_total_w) OVER (PARTITION BY st.day_local ORDER BY st.interval_start) AS prev_actual_w
    FROM slot_totals st
    cross join bounds bthr
    WHERE st.slot_of_day BETWEEN 20 AND 80
      AND st.actual_total_w > bthr.threshold_w
  ),
  day_jump AS (
    SELECT
      ss.day_local,
      percentile_cont(0.5) WITHIN GROUP (ORDER BY abs(ss.actual_total_w - ss.prev_actual_w)) AS med_jump_w
    FROM slot_steps ss
    WHERE ss.prev_actual_w IS NOT NULL
    GROUP BY ss.day_local
  ),
  day_med AS (
    SELECT
      st.day_local,
      percentile_cont(0.5) WITHIN GROUP (ORDER BY st.actual_total_w) AS p50_actual_w
    FROM slot_totals st
    cross join bounds bthr
    WHERE st.actual_total_w > bthr.threshold_w
    GROUP BY st.day_local
  ),
  day_stats AS (
    SELECT
      de.day_local,
      de.energy_kwh,
      dj.med_jump_w,
      dm.p50_actual_w,
      CASE
        WHEN (SELECT med_kwh FROM ref) IS NULL OR (SELECT med_kwh FROM ref) <= 0 THEN 0.5
        ELSE greatest(
          0.0,
          least(
            1.0,
            (de.energy_kwh - (SELECT med_kwh FROM ref) * 0.55)
            / nullif((SELECT med_kwh FROM ref) * 0.35, 0)
          )
        )
      END AS w_energy,
      CASE
        WHEN dj.med_jump_w IS NULL OR dm.p50_actual_w IS NULL THEN 0.35
        ELSE greatest(
          0.0,
          least(
            1.0,
            1.0
            - (
              dj.med_jump_w
              / nullif(greatest(300.0, dm.p50_actual_w * 0.25), 0)
            )
          )
        )
      END AS w_smooth
    FROM day_energy de
    LEFT JOIN day_jump dj ON dj.day_local = de.day_local
    LEFT JOIN day_med dm ON dm.day_local = de.day_local
  ),
  day_rank AS (
    SELECT
      ds.day_local,
      row_number() OVER (
        ORDER BY
          (coalesce(ds.w_energy, 0.35) * coalesce(ds.w_smooth, 0.35)) DESC,
          ds.day_local DESC
      ) AS rn
    FROM day_stats ds
  ),
  ref_wall AS (
    SELECT d.day_local
    FROM ems.site_pv_forecast_reference_day d
    WHERE d.site_id = p_site_id
  ),
  filtered AS (
    SELECT
      s.pv_array_id,
      s.slot_of_day,
      (s.forecast_w - s.actual_w) AS error_w,
      exp(-s.age_days / nullif(b.half_life_days, 0))
        * (
          CASE
            WHEN e.top_n_days IS NULL THEN 1::numeric
            WHEN e.top_n_days < 1 THEN 1::numeric
            WHEN dr.rn <= e.top_n_days THEN 1::numeric
            ELSE greatest(0::numeric, least(1::numeric, coalesce(e.non_top_day_factor, 0.02)))
          END
        )
        * (
          0.05
          + 0.95
            * power(
              greatest(
                0.0,
                least(1.0, coalesce(ds.w_energy, 0.35) * coalesce(ds.w_smooth, 0.35))
              ),
              greatest(0.25, least(coalesce(e.day_weight_gamma, 1.0), 8.0))
            )
        )
        * CASE
          WHEN EXISTS (SELECT 1 FROM ref_wall rw WHERE rw.day_local = s.day_local)
          THEN e.reference_day_w_mult
          ELSE 1::numeric
        END AS w
    FROM slots s
    CROSS JOIN bounds b
    CROSS JOIN eff e
    JOIN slot_totals st ON st.interval_start = s.interval_start
    LEFT JOIN day_stats ds ON ds.day_local = s.day_local
    LEFT JOIN day_rank dr ON dr.day_local = s.day_local
    WHERE s.slot_of_day BETWEEN 0 AND 95
      AND (s.actual_w > b.threshold_w OR s.forecast_w > b.threshold_w)
  ),
  agg_by_array AS (
    SELECT
      f.pv_array_id,
      f.slot_of_day,
      count(*) AS sample_count,
      sum(f.w) AS w_sum,
      CASE
        WHEN sum(f.w) > 0 THEN sum(f.error_w * f.w) / sum(f.w)
        ELSE NULL
      END AS delta_w
    FROM filtered f
    GROUP BY f.pv_array_id, f.slot_of_day
  ),
  agg_total AS (
    SELECT
      sp.slot_of_day,
      sum(coalesce(ab.sample_count, 0))::bigint AS sample_count,
      sum(coalesce(round(ab.delta_w)::int, 0))::int AS delta_w
    FROM generate_series(0, 95) AS sp(slot_of_day)
    LEFT JOIN agg_by_array ab ON ab.slot_of_day = sp.slot_of_day
    GROUP BY sp.slot_of_day
  ),
  arrays_block AS (
    SELECT coalesce(jsonb_object_agg(apa.id::text, arr.pack), '{}'::jsonb) AS deltas_by_array
    FROM ems.asset_pv_array apa
    CROSS JOIN LATERAL (
      SELECT jsonb_build_object(
        'deltas',
        coalesce(
          jsonb_agg(
            jsonb_build_object(
              'slot_of_day', sp.slot_of_day,
              'delta_w', coalesce(round(a.delta_w)::int, 0),
              'sample_count', coalesce(a.sample_count, 0)
            )
            ORDER BY sp.slot_of_day
          ),
          '[]'::jsonb
        )
      ) AS pack
      FROM generate_series(0, 95) AS sp(slot_of_day)
      LEFT JOIN agg_by_array a
        ON a.pv_array_id = apa.id
       AND a.slot_of_day = sp.slot_of_day
    ) arr
    WHERE apa.site_id = p_site_id
  ),
  spine AS (
    SELECT generate_series(0, 95) AS slot_of_day
  )
  SELECT jsonb_build_object(
    'site_id', p_site_id,
    'data_from', (SELECT ts_from FROM bounds),
    'data_to', (SELECT ts_to FROM bounds),
    'delta_learn_min_ts', (SELECT delta_learn_min_ts FROM eff),
    'half_life_days', (SELECT half_life_days FROM bounds),
    'threshold_w', (SELECT threshold_w FROM bounds),
    'top_n_days', (SELECT top_n_days FROM eff),
    'deltas',
    coalesce(
      jsonb_agg(
        jsonb_build_object(
          'slot_of_day', sp.slot_of_day,
          'delta_w', coalesce(at.delta_w, 0),
          'sample_count', coalesce(at.sample_count, 0)
        )
        ORDER BY sp.slot_of_day
      ),
      '[]'::jsonb
    ),
    'deltas_by_array', (SELECT deltas_by_array FROM arrays_block)
  )
  FROM spine sp
  LEFT JOIN agg_total at ON at.slot_of_day = sp.slot_of_day;
$fn$;

COMMENT ON FUNCTION ems.fn_pv_forecast_delta_profile IS
  'Aditivní delta profil PV forecastu po 15min slotu dne (96 slotů) per pv_array_id v deltas_by_array; deltas je součet delt přes pole.'
  ' Zdroj forecast_accuracy learning_eligible, cutoff kalibrace, váhy dnů.'
  ' Dny z ems.site_pv_forecast_reference_day (den = interval ve site.timezone) mají násobenou váhu (site_pv_forecast_calibration.reference_day_weight_mult nebo default 3).';
