-- ============================================================
-- PV forecast sloty (15min) – kanonický vstup pro plánování
--
-- Kombinuje:
-- 1) delta-korekci per-array (fn_pv_forecast_delta_profile)
-- 2) rolling multiplikativní faktor vs telemetrie (fn_pv_forecast_correction_factor)
--    s lineárním decay do 1.0 v p_decay_slots.
--
-- Výstup je rozsplitěný na PV-A (controllable=true) a PV-B (controllable=false),
-- protože curtailment v LP smí omezovat jen PV-A.
-- ============================================================

create or replace function ems.fn_forecast_pv_slots_range_canonical_ab(
  p_site_id int,
  p_from timestamptz,
  p_to timestamptz,
  p_now timestamptz default now(),
  p_delta_data_from timestamptz default (now() - interval '120 days'),
  p_delta_data_to timestamptz default now(),
  p_half_life_days numeric default 14,
  p_threshold_w int default 150,
  p_factor_window_h numeric default 1,
  p_factor_min_clamp numeric default 0.5,
  p_factor_max_clamp numeric default 1.5,
  p_decay_slots int default 16
)
returns jsonb
language plpgsql
stable
set work_mem = '64MB'
as $fn$
declare
  v_result jsonb;
  v_rolling_factor numeric;
begin
  -- Rolling faktor JEDNOU do proměnné: jako inlinovaná CTE (single-ref) se
  -- fn_pv_forecast_correction_factor vyhodnocovala per výstupní řádek
  -- (~300× ≈ 570k buffers / ~4 s — skutečná příčina pomalosti, ne plan cache).
  v_rolling_factor := coalesce(
    (ems.fn_pv_forecast_correction_factor(
      p_site_id,
      (p_now - (p_factor_window_h::text || ' hours')::interval)::timestamptz,
      p_now,
      p_factor_min_clamp,
      p_factor_max_clamp
    )->>'correction_factor')::numeric,
    1.0
  );
  -- PG 18 cachuje plán SQL/plpgsql statementů s parametry → generický plán
  -- (4.5–9 s / 600k buffers); force_custom_plan na funkci nepomohl. Execute
  -- s literály vynutí čerstvý plán dle skutečných hodnot: 0.4 s / 34k buffers.
  execute format($q$
  with tz as (
    select coalesce(nullif(trim(s.timezone), ''), 'Europe/Prague') as tz_name
    from ems.site s
    where s.id = %1$s
  ),
  bounds as (
    select
      date_bin(interval '15 minutes', %2$L::timestamptz, timestamptz '1970-01-01T00:00:00Z') as ts_from,
      case
        when %3$L::timestamptz <= %2$L::timestamptz then date_bin(interval '15 minutes', %2$L::timestamptz, timestamptz '1970-01-01T00:00:00Z') + interval '15 minutes'
        when %3$L::timestamptz > %2$L::timestamptz + interval '60 days' then date_bin(interval '15 minutes', %2$L::timestamptz, timestamptz '1970-01-01T00:00:00Z') + interval '60 days'
        else date_bin(interval '15 minutes', %3$L::timestamptz, timestamptz '1970-01-01T00:00:00Z')
      end as ts_to,
      date_bin(interval '15 minutes', %4$L::timestamptz, timestamptz '1970-01-01T00:00:00Z') as now_slot
  ),
  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
  ),
  profile as (
    select ems.fn_pv_forecast_delta_profile_cached(
      %1$s,
      %5$L::timestamptz,
      %6$L::timestamptz,
      %7$s,
      %8$s
    ) 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_by_array as (
    select distinct on (fpi.interval_start, fpr.pv_array_id)
      fpi.interval_start,
      fpr.pv_array_id,
      apa.controllable,
      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 apa0.id from ems.asset_pv_array apa0 where apa0.site_id = %1$s
     )
    inner join ems.forecast_pv_run fpr
      on fpr.id = fpi.run_id
     and fpr.site_id = %1$s
     and fpr.pv_array_id = fpi.pv_array_id
     and fpr.status = 'ok'
    inner join ems.asset_pv_array apa
      on apa.id = fpr.pv_array_id
     and apa.site_id = %1$s
    order by fpi.interval_start, fpr.pv_array_id, fpr.created_at desc
  ),
  fc_with_sod as (
    select
      fa.interval_start,
      fa.pv_array_id,
      fa.controllable,
      fa.power_w,
      st.slot_of_day
    from fc_by_array fa
    join slot_tz st on st.interval_start = fa.interval_start
  ),
  fc_delta as (
    select
      f.interval_start,
      f.controllable,
      sum(f.power_w)::bigint as raw_w,
      sum(
        greatest(
          0::bigint,
          f.power_w
          - (
            case
              when fl.use_per_array then coalesce(d.delta_w, 0)::bigint
              else coalesce(dl.delta_w, 0)::bigint
            end
          )
        )
      )::bigint as delta_w
    from fc_with_sod f
    cross join flags fl
    left join delta_by_array d
      on fl.use_per_array
     and d.pv_array_id = f.pv_array_id
     and d.slot_of_day = f.slot_of_day
    left join lateral (
      select dl0.delta_w
      from deltas_legacy dl0
      where dl0.slot_of_day = f.slot_of_day
      limit 1
    ) dl on not fl.use_per_array
    group by f.interval_start, f.controllable
  ),
  fc_ab as (
    select
      st.interval_start,
      coalesce(sum(case when fd.controllable then fd.raw_w else 0 end), 0)::bigint as pv_a_forecast_raw_w,
      coalesce(sum(case when not fd.controllable then fd.raw_w else 0 end), 0)::bigint as pv_b_forecast_raw_w,
      coalesce(sum(case when fd.controllable then fd.delta_w else 0 end), 0)::bigint as pv_a_forecast_delta_w,
      coalesce(sum(case when not fd.controllable then fd.delta_w else 0 end), 0)::bigint as pv_b_forecast_delta_w,
      st.slot_of_day
    from slot_tz st
    left join fc_delta fd on fd.interval_start = st.interval_start
    group by st.interval_start, st.slot_of_day
  ),
  with_factor as (
    select
      ab.interval_start,
      ab.slot_of_day,
      ab.pv_a_forecast_raw_w,
      ab.pv_b_forecast_raw_w,
      ab.pv_a_forecast_delta_w,
      ab.pv_b_forecast_delta_w,
      %13$s::numeric as rolling_factor,
      case
        when ab.interval_start < b.now_slot then 1.0::numeric
        when %12$s <= 0 then %13$s::numeric
        else
          case
            when ((extract(epoch from (ab.interval_start - b.now_slot)) / 900)::int) >= %12$s then 1.0::numeric
            else (1.0::numeric + (%13$s::numeric - 1.0::numeric) * (1.0::numeric - ((extract(epoch from (ab.interval_start - b.now_slot)) / 900)::numeric / %12$s::numeric)))
          end
      end as rolling_effective_factor
    from fc_ab ab
    cross join bounds b
  )
  select coalesce(
    jsonb_agg(
      jsonb_build_object(
        'interval_start', w.interval_start,
        'slot_of_day', w.slot_of_day,
        'pv_a_forecast_raw_w', w.pv_a_forecast_raw_w,
        'pv_b_forecast_raw_w', w.pv_b_forecast_raw_w,
        'pv_a_forecast_delta_w', w.pv_a_forecast_delta_w,
        'pv_b_forecast_delta_w', w.pv_b_forecast_delta_w,
        'rolling_factor', w.rolling_factor,
        'rolling_effective_factor', w.rolling_effective_factor,
        'pv_a_forecast_canonical_w', greatest(0::bigint, round(w.pv_a_forecast_delta_w::numeric * w.rolling_effective_factor))::bigint,
        'pv_b_forecast_canonical_w', greatest(0::bigint, round(w.pv_b_forecast_delta_w::numeric * w.rolling_effective_factor))::bigint,
        'pv_forecast_total_canonical_w',
          greatest(0::bigint, round(w.pv_a_forecast_delta_w::numeric * w.rolling_effective_factor))::bigint
          + greatest(0::bigint, round(w.pv_b_forecast_delta_w::numeric * w.rolling_effective_factor))::bigint
      )
      order by w.interval_start
    ),
    '[]'::jsonb
  )
  from with_factor w
$q$,
    p_site_id,
    p_from,
    p_to,
    p_now,
    p_delta_data_from,
    p_delta_data_to,
    p_half_life_days,
    p_threshold_w,
    p_factor_window_h,
    p_factor_min_clamp,
    p_factor_max_clamp,
    p_decay_slots,
    v_rolling_factor
  ) into v_result;
  return coalesce(v_result, '[]'::jsonb);
end;
$fn$;

comment on function ems.fn_forecast_pv_slots_range_canonical_ab is
  'Kanonická PV forecast řada po 15 min pro plánování: delta-korekce per-array (fn_pv_forecast_delta_profile) + rolling multiplikativní faktor (fn_pv_forecast_correction_factor) s decay. Vrací PV-A/PV-B (controllable) i total.';

