-- 15min sloty součtu FVE výkonů z posledních OK forecast runů (stejná logika jako fc_slot v fn_plan_current_bundle).

create or replace function ems.fn_forecast_pv_slots_range(
  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 as (
    select
      u.interval_start,
      coalesce(sum(u.power_w), 0)::bigint as pv_forecast_total_w
    from (
      select distinct on (fpi.interval_start, fpr.pv_array_id)
        fpi.interval_start,
        fpi.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
    ) u
    group by u.interval_start
  )
  select coalesce(
    jsonb_agg(
      jsonb_build_object(
        'interval_start', s.interval_start,
        'pv_forecast_total_w', fc.pv_forecast_total_w
      )
      order by s.interval_start
    ),
    '[]'::jsonb
  )
  from slot_spine s
  left join fc on fc.interval_start = s.interval_start;
$fn$;

comment on function ems.fn_forecast_pv_slots_range(int, timestamptz, timestamptz) is
  'JSON pole {interval_start, pv_forecast_total_w} po 15 min pro [p_from, p_to); doplnění grafu plánu za hranicí planning_interval. Horizont je omezený na max. 60 dní.';
