-- ============================================================
-- PV forecast sloty (15min) – RAW (bez korekcí), rozdělené na PV-A/PV-B
--
-- Nejnovější `ok` forecast_pv_run per (interval_start, pv_array_id).
-- Slouží pro audit/debug v planning_interval.*_forecast_raw_w.
-- ============================================================

create or replace function ems.fn_forecast_pv_slots_range_raw_ab(
  p_site_id int,
  p_from timestamptz,
  p_to timestamptz
)
returns jsonb
language sql
stable
set work_mem = '64MB'
as $fn$
  with 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
  ),
  fc_by_array as (
    select distinct on (fpi.interval_start, fpr.pv_array_id)
      fpi.interval_start,
      apa.controllable,
      fpi.power_w::bigint as power_w
    from bounds b
    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 apa0.id from ems.asset_pv_array apa0 where apa0.site_id = p_site_id
     )
    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'
    join ems.asset_pv_array apa
      on apa.id = fpr.pv_array_id
     and apa.site_id = p_site_id
    order by fpi.interval_start, fpr.pv_array_id, fpr.created_at desc
  ),
  fc_ab as (
    select
      s.interval_start,
      coalesce(sum(case when f.controllable then f.power_w else 0 end), 0)::bigint as pv_a_forecast_raw_w,
      coalesce(sum(case when not f.controllable then f.power_w else 0 end), 0)::bigint as pv_b_forecast_raw_w
    from slot_spine s
    left join fc_by_array f on f.interval_start = s.interval_start
    group by s.interval_start
  )
  select coalesce(
    jsonb_agg(
      jsonb_build_object(
        'interval_start', r.interval_start,
        'pv_a_forecast_raw_w', r.pv_a_forecast_raw_w,
        'pv_b_forecast_raw_w', r.pv_b_forecast_raw_w
      )
      order by r.interval_start
    ),
    '[]'::jsonb
  )
  from fc_ab r;
$fn$;

comment on function ems.fn_forecast_pv_slots_range_raw_ab is
  'RAW PV forecast po 15 min (bez korekcí), rozdělený na PV-A/PV-B, jako nejnovější ok run per array a slot.';

