create or replace function ems.fn_plan_current_bundle(p_site_id int)
returns jsonb
language plpgsql
stable
as $fn$
declare
  v_run jsonb;
  v_run_id int;
  v_batt_wh float;
  v_intervals jsonb;
  v_total_cost numeric;
  v_curtailed numeric;
  v_charge bigint;
  v_discharge bigint;
  v_export bigint;
  v_pv_kwh numeric;
  v_cap numeric;
  v_cov numeric;
  v_scarcity numeric;
  v_horizon_start timestamptz;
  v_horizon_end timestamptz;
  v_chart_end timestamptz;
  v_fc_from timestamptz;
  v_fc_to timestamptz;
  v_fc_slots jsonb;
begin
  select to_jsonb(pr)
  into v_run
  from ems.planning_run pr
  where pr.site_id = p_site_id
    and pr.status = 'active'
  order by pr.created_at desc
  limit 1;

  if v_run is null then
    return jsonb_build_object('error', 'no_active_plan');
  end if;

  v_run_id := (v_run->>'id')::int;
  v_horizon_start := (v_run->>'horizon_start')::timestamptz;
  v_horizon_end := (v_run->>'horizon_end')::timestamptz;
  v_chart_end := greatest(v_horizon_end, v_horizon_start + interval '96 hours');

  -- Kanonický PV forecast jen za horizontem uloženého plánu (graf až 96 h).
  if v_horizon_end < v_chart_end then
    v_fc_from := v_horizon_end;
    v_fc_to := v_chart_end;
    v_fc_slots := ems.fn_forecast_pv_slots_range_canonical_ab(
      p_site_id,
      v_fc_from,
      v_fc_to,
      now()
    );
  else
    v_fc_slots := '[]'::jsonb;
  end if;

  select coalesce(sum(ab.usable_capacity_wh), 0)::float
  into v_batt_wh
  from ems.asset_battery ab
  where ab.site_id = p_site_id;

  with fc_slot as (
    select
      c.interval_start,
      (coalesce(c.pv_a_forecast_canonical_w, 0) + coalesce(c.pv_b_forecast_canonical_w, 0))::bigint as pv_forecast_total_w,
      coalesce(c.pv_a_forecast_canonical_w, 0)::bigint as pv_a_forecast_solver_w,
      coalesce(c.pv_b_forecast_canonical_w, 0)::bigint as pv_b_forecast_solver_w
    from jsonb_to_recordset(v_fc_slots) as c(
      interval_start timestamptz,
      pv_a_forecast_canonical_w bigint,
      pv_b_forecast_canonical_w bigint
    )
  ),
  joined as (
    select
      jsonb_build_object(
        'interval_start', pi.interval_start,
        'battery_setpoint_w', pi.battery_setpoint_w,
        'battery_soc_target_pct', pi.battery_soc_target_pct,
        'grid_setpoint_w', pi.grid_setpoint_w,
        'export_limit_w', pi.export_limit_w,
        'export_mode', pi.export_mode,
        'deye_physical_mode', pi.deye_physical_mode,
        'deye_gen_cutoff_enabled', pi.deye_gen_cutoff_enabled,
        'ev1_setpoint_w', pi.ev1_setpoint_w,
        'ev2_setpoint_w', pi.ev2_setpoint_w,
        'ev1_via_bat_w', pi.ev1_via_bat_w,
        'ev2_via_bat_w', pi.ev2_via_bat_w,
        'heat_pump_enabled', pi.heat_pump_enabled,
        'pv_a_curtailed_w', pi.pv_a_curtailed_w,
        'expected_cost_czk', pi.expected_cost_czk,
        'effective_buy_price', pi.effective_buy_price,
        'effective_sell_price', pi.effective_sell_price,
        'is_predicted_price', coalesce(pi.is_predicted_price, false),
        'pv_power_w', ai.actual_pv_power_w,
        'pv_forecast_total_w',
          coalesce(pi.pv_a_forecast_solver_w, 0)
          + coalesce(pi.pv_b_forecast_solver_w, 0),
        'pv_a_forecast_solver_w', pi.pv_a_forecast_solver_w,
        'pv_b_forecast_solver_w', pi.pv_b_forecast_solver_w,
        'load_baseline_w', pi.load_baseline_w
      ) as j,
      pi.interval_start,
      pi.expected_cost_czk,
      pi.pv_a_curtailed_w,
      pi.battery_setpoint_w,
      pi.grid_setpoint_w,
      (coalesce(pi.pv_a_forecast_solver_w, 0) + coalesce(pi.pv_b_forecast_solver_w, 0))::bigint as pv_forecast_total_w
    from ems.planning_interval pi
    left join ems.audit_interval ai
      on ai.site_id = p_site_id
     and ai.interval_start = pi.interval_start
    where pi.run_id = v_run_id
    union all
    select
      jsonb_build_object(
        'interval_start', fs.interval_start,
        'battery_setpoint_w', null,
        'battery_soc_target_pct', null,
        'grid_setpoint_w', null,
        'export_limit_w', null,
        'export_mode', null,
        'deye_physical_mode', null,
        'deye_gen_cutoff_enabled', null,
        'ev1_setpoint_w', null,
        'ev2_setpoint_w', null,
        'ev1_via_bat_w', null,
        'ev2_via_bat_w', null,
        'heat_pump_enabled', null,
        'pv_a_curtailed_w', null,
        'expected_cost_czk', null,
        'effective_buy_price', null,
        'effective_sell_price', null,
        'is_predicted_price', false,
        'pv_power_w', null,
        'pv_forecast_total_w', fs.pv_forecast_total_w,
        'pv_a_forecast_solver_w', fs.pv_a_forecast_solver_w,
        'pv_b_forecast_solver_w', fs.pv_b_forecast_solver_w,
        'load_baseline_w', null
      ) as j,
      fs.interval_start,
      null::numeric as expected_cost_czk,
      null::int as pv_a_curtailed_w,
      null::int as battery_setpoint_w,
      null::int as grid_setpoint_w,
      fs.pv_forecast_total_w
    from fc_slot fs
    where fs.interval_start >= v_horizon_start
      and fs.interval_start < v_chart_end
      and not exists (
        select 1
        from ems.planning_interval pi2
        where pi2.run_id = v_run_id
          and pi2.interval_start = fs.interval_start
      )
  ),
  agg as (
    select
      coalesce(jsonb_agg(j order by interval_start), '[]'::jsonb) as intervals,
      coalesce(
        sum(
          case
            when expected_cost_czk is not null then expected_cost_czk::numeric
            else 0::numeric
          end
        ),
        0::numeric
      ) as total_cost,
      coalesce(
        sum(coalesce(pv_a_curtailed_w, 0)::numeric * 0.25 / 1000.0),
        0::numeric
      ) as curtailed_kwh,
      coalesce(
        sum(
          case
            when battery_setpoint_w is not null and battery_setpoint_w > 0 then 1
            else 0
          end
        ),
        0::bigint
      ) as charge_slots,
      coalesce(
        sum(
          case
            when battery_setpoint_w is not null and battery_setpoint_w < 0 then 1
            else 0
          end
        ),
        0::bigint
      ) as discharge_slots,
      coalesce(
        sum(
          case
            when grid_setpoint_w is not null and grid_setpoint_w < 0 then 1
            else 0
          end
        ),
        0::bigint
      ) as export_slots
    from joined
  ),
  pv96 as (
    select coalesce(
      sum(
        greatest(0::numeric, coalesce(pv_forecast_total_w, 0)::numeric) * 0.25 / 1000.0
      ),
      0::numeric
    ) as pv_kwh
    from (
      select pv_forecast_total_w
      from joined
      order by interval_start
      limit 96
    ) z
  )
  select
    a.intervals,
    a.total_cost,
    a.curtailed_kwh,
    a.charge_slots,
    a.discharge_slots,
    a.export_slots,
    p.pv_kwh
  into strict
    v_intervals,
    v_total_cost,
    v_curtailed,
    v_charge,
    v_discharge,
    v_export,
    v_pv_kwh
  from agg a
  cross join pv96 p;

  v_cap := greatest(1::numeric, coalesce(v_batt_wh, 0::float)::numeric / 1000.0);
  v_cov := least(1::numeric, greatest(0::numeric, coalesce(v_pv_kwh, 0) / v_cap));
  v_scarcity := round(0.65::numeric + 0.35 * v_cov, 4);

  return jsonb_build_object(
    'run', v_run,
    'intervals', v_intervals,
    'summary', jsonb_build_object(
      'total_expected_cost_czk', round(v_total_cost, 4),
      'total_pv_curtailed_kwh', round(v_curtailed, 6),
      'charge_slots', v_charge,
      'discharge_slots', v_discharge,
      'export_slots', v_export,
      'pv_scarcity_factor', v_scarcity
    )
  );
end;
$fn$;

comment on function ems.fn_plan_current_bundle is
  'Aktivní planning_run + intervaly + souhrn (GET /plan/current). PV za horizont plánu z canonical forecast; delta profil z cache; intervals nesou ev1/ev2_via_bat_w (EV energie z baterie — UI nemá cenit slotovým buy).';
