-- ============================================================
-- PV forecast sloty (15min) + aditivně korigovaný forecast
-- corrected = sum_i max(0, forecast_i - delta_profile_i[slot_of_day])
-- Agregace korekce v jednom průchodu (žádný korelovaný subselect na slot_spine).
-- ============================================================

create or replace function ems.fn_forecast_pv_slots_range_corrected(
  p_site_id int,
  p_from timestamptz,
  p_to timestamptz,
  p_delta_data_from timestamptz,
  p_delta_data_to timestamptz default now(),
  p_half_life_days numeric default 14,
  p_threshold_w int default 150
)
returns jsonb
language sql
stable
set work_mem = '64MB'
as $fn$
  with 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
      date_bin(interval '15 minutes', p_from, timestamptz '1970-01-01T00:00:00Z') as ts_from,
      case
        when p_to <= p_from then date_bin(interval '15 minutes', p_from, timestamptz '1970-01-01T00:00:00Z') + interval '15 minutes'
        when p_to > p_from + interval '60 days' then date_bin(interval '15 minutes', p_from, timestamptz '1970-01-01T00:00:00Z') + interval '60 days'
        else date_bin(interval '15 minutes', p_to, timestamptz '1970-01-01T00:00:00Z')
      end as ts_to
  ),
  slot_spine as (
    select gs as interval_start
    from bounds b,
    generate_series(
      b.ts_from,
      (b.ts_to - interval '15 minutes')::timestamptz,
      interval '15 minutes'
    ) as gs
  ),
  slot_tz as (
    select
      s.interval_start,
      (
        (extract(hour from (s.interval_start at time zone t.tz_name))::int * 60)
        + extract(minute from (s.interval_start at time zone t.tz_name))::int
      ) / 15 as slot_of_day
    from slot_spine s
    cross join tz t
  ),
  fc_by_array as (
    select distinct on (fpi.interval_start, fpr.pv_array_id)
      fpi.interval_start,
      fpr.pv_array_id,
      fpi.power_w::bigint as power_w
    from bounds b
    inner join ems.forecast_pv_interval fpi
      on fpi.interval_start >= b.ts_from
     and fpi.interval_start < b.ts_to
     and fpi.pv_array_id in (
       select apa.id from ems.asset_pv_array apa where apa.site_id = p_site_id
     )
    inner join ems.forecast_pv_run fpr
      on fpr.id = fpi.run_id
     and fpr.site_id = p_site_id
     and fpr.pv_array_id = fpi.pv_array_id
     and fpr.status = 'ok'
    order by fpi.interval_start, fpr.pv_array_id, fpr.created_at desc
  ),
  fc_totals as (
    select u.interval_start, coalesce(sum(u.power_w), 0)::bigint as pv_forecast_total_w
    from fc_by_array u
    group by u.interval_start
  ),
  profile as (
    -- VŽDY přes cache (inline fn_pv_forecast_delta_profile = ~44 s/site na prod;
    -- dashboard endpoint pv-slots-corrected na ní visel). Refresh řídí job.
    select ems.fn_pv_forecast_delta_profile_cached(
      p_site_id,
      p_delta_data_from,
      p_delta_data_to,
      p_half_life_days,
      p_threshold_w
    ) as j
  ),
  delta_by_array as (
    select (kv.key)::int as pv_array_id,
           (x->>'slot_of_day')::int as slot_of_day,
           (x->>'delta_w')::int as delta_w
    from profile p
    cross join lateral jsonb_each((p.j)->'deltas_by_array') kv(key, value)
    cross join lateral jsonb_array_elements(kv.value->'deltas') x
  ),
  deltas_legacy as (
    select (x->>'slot_of_day')::int as slot_of_day,
           (x->>'delta_w')::int as delta_w
    from profile p
    cross join lateral jsonb_array_elements(p.j->'deltas') x
  ),
  flags as (
    select exists (select 1 from delta_by_array) as use_per_array
  ),
  fc_with_sod as (
    select
      fa.interval_start,
      fa.pv_array_id,
      fa.power_w,
      st.slot_of_day
    from fc_by_array fa
    join slot_tz st on st.interval_start = fa.interval_start
  ),
  per_array_corrected as (
    select
      f.interval_start,
      coalesce(
        sum(greatest(0::bigint, f.power_w - coalesce(d.delta_w, 0)::bigint)),
        0
      )::bigint as pv_forecast_corrected_w
    from fc_with_sod f
    left join delta_by_array d
      on d.pv_array_id = f.pv_array_id
     and d.slot_of_day = f.slot_of_day
    group by f.interval_start
  ),
  legacy_corrected as (
    select
      sw.interval_start,
      greatest(
        0::bigint,
        coalesce(ft.pv_forecast_total_w, 0)::bigint
        - coalesce(dl.delta_w, 0)::bigint
      ) as pv_forecast_corrected_w
    from slot_tz sw
    left join fc_totals ft on ft.interval_start = sw.interval_start
    left join lateral (
      select dl0.delta_w
      from deltas_legacy dl0
      where dl0.slot_of_day = sw.slot_of_day
      limit 1
    ) dl on true
  ),
  corrected as (
    select
      st.interval_start,
      coalesce(ft.pv_forecast_total_w, 0)::bigint as pv_forecast_total_w,
      case
        when fl.use_per_array then coalesce(pac.pv_forecast_corrected_w, 0)::bigint
        else coalesce(leg.pv_forecast_corrected_w, 0)::bigint
      end as pv_forecast_corrected_w,
      st.slot_of_day
    from slot_tz st
    cross join flags fl
    left join fc_totals ft on ft.interval_start = st.interval_start
    left join per_array_corrected pac
      on fl.use_per_array
     and pac.interval_start = st.interval_start
    left join legacy_corrected leg
      on not fl.use_per_array
     and leg.interval_start = st.interval_start
  )
  select coalesce(
    jsonb_agg(
      jsonb_build_object(
        'interval_start', c.interval_start,
        'pv_forecast_total_w', c.pv_forecast_total_w,
        'pv_forecast_corrected_w', c.pv_forecast_corrected_w,
        'slot_of_day', c.slot_of_day
      )
      order by c.interval_start
    ),
    '[]'::jsonb
  )
  from corrected c;
$fn$;

comment on function ems.fn_forecast_pv_slots_range_corrected is
  'JSON pole {interval_start, pv_forecast_total_w, pv_forecast_corrected_w, slot_of_day} po 15 min pro [p_from, p_to). Korekce per pv_array_id z fn_pv_forecast_delta_profile.deltas_by_array (fallback na jedno pole `deltas`). Horizont max. 60 dní.';
