-- korekční faktor FVE forecast vs telemetrie (stejná logika jako compute_correction_factor v Pythonu)

create or replace function ems.fn_pv_forecast_correction_factor(
  p_site_id int,
  p_window_start timestamptz,
  p_window_end timestamptz,
  p_min_clamp numeric default 0.5,
  p_max_clamp numeric default 1.5
)
returns jsonb
language plpgsql
stable
as $fn$
declare
  v_actual numeric;
  v_forecast numeric;
  v_raw numeric;
  v_factor numeric := 1.0;
  v_clamped boolean := false;
begin
  -- Telemetrie je 1min (avg power). Energie v kWh ≈ sum(W) * (1/60 h) / 1000.
  select coalesce(sum(ti.pv_power_w) / 60.0 / 1000.0, 0)
  into v_actual
  from ems.telemetry_inverter ti
  where ti.site_id = p_site_id
    and ti.measured_at >= p_window_start
    and ti.measured_at < p_window_end;

  -- Forecast pro korekční faktor bereme stejně jako pro plánování/UI:
  -- nejnovější `ok` run per (interval_start, pv_array_id) v daném okně.
  select coalesce(sum(u.power_w) * 0.25 / 1000.0, 0)
  into v_forecast
  from (
    select distinct on (fpi.interval_start, fpr.pv_array_id)
      fpi.power_w
    from ems.forecast_pv_interval fpi
    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'
    where fpi.interval_start >= p_window_start
      and fpi.interval_start < p_window_end
      and fpi.pv_array_id in (
        select apa.id from ems.asset_pv_array apa where apa.site_id = p_site_id
      )
    order by fpi.interval_start, fpr.pv_array_id, fpr.created_at desc
  ) u;

  if v_forecast < 0.1 or coalesce(v_actual, 0) < 0.05 then
    return jsonb_build_object(
      'correction_factor', 1.0,
      'raw_factor', null,
      'clamped', false,
      'reason', 'insufficient_data',
      'actual_pv_wh', coalesce(v_actual, 0) * 1000,
      'forecast_pv_wh', coalesce(v_forecast, 0) * 1000,
      'window_start', p_window_start,
      'window_end', p_window_end
    );
  end if;

  v_raw := v_actual / nullif(v_forecast, 0);
  v_factor := greatest(p_min_clamp, least(p_max_clamp, v_raw));
  v_clamped := (v_factor <> v_raw);

  return jsonb_build_object(
    'correction_factor', v_factor,
    'raw_factor', v_raw,
    'clamped', v_clamped,
    'reason', 'ok',
    'actual_pv_wh', v_actual * 1000,
    'forecast_pv_wh', v_forecast * 1000,
    'window_start', p_window_start,
    'window_end', p_window_end
  );
end;
$fn$;
