create or replace function ems.fn_economics_monthly_chart(
  p_site_id int,
  p_month_start date,
  p_month_end date
)
returns jsonb
language sql
stable
as $fn$
  with base as (
    select
      r.day_local,
      case when l.site_id is not null then l.total_balance_czk else r.total_balance_czk end as tb,
      case when l.site_id is not null then l.net_cost_czk else r.net_cost_czk end as nc,
      case when l.site_id is not null then l.green_bonus_czk else r.green_bonus_czk end as gb,
      coalesce(l.grid_import_cashflow_czk, r.grid_import_cashflow_czk) as gic,
      coalesce(l.grid_export_revenue_czk, r.grid_export_revenue_czk) as ger
    from ems.fn_economics_daily_for_window(
      p_site_id,
      (p_month_start::timestamp at time zone 'Europe/Prague'),
      (p_month_end::timestamp at time zone 'Europe/Prague')
    ) r
    left join ems.audit_day_lock l
      on l.site_id = r.site_id
     and l.day_local = r.day_local
    where r.site_id = p_site_id
      and r.day_local >= p_month_start
      and r.day_local < p_month_end
  ),
  w as (
    select
      day_local,
      round(tb::numeric, 2) as daily_balance_czk,
      round((-nc)::numeric, 2) as daily_grid_balance_czk,
      round(gb::numeric, 2) as daily_green_bonus_czk,
      round(gic::numeric, 2) as daily_import_cost_czk,
      round(ger::numeric, 2) as daily_export_revenue_czk,
      sum(round(tb::numeric, 2)) over (
        order by day_local rows between unbounded preceding and current row
      ) as cumb,
      sum(round((-nc)::numeric, 2)) over (
        order by day_local rows between unbounded preceding and current row
      ) as cumg
    from base
  )
  select coalesce(
    jsonb_agg(
      jsonb_build_object(
        'day', day_local,
        'daily_balance_czk', daily_balance_czk,
        'daily_grid_balance_czk', daily_grid_balance_czk,
        'daily_green_bonus_czk', daily_green_bonus_czk,
        'daily_import_cost_czk', daily_import_cost_czk,
        'daily_export_revenue_czk', daily_export_revenue_czk,
        'cumulative_balance_czk', round(cumb::numeric, 2),
        'cumulative_grid_balance_czk', round(cumg::numeric, 2)
      )
      order by day_local
    ),
    '[]'::jsonb
  )
  from w;
$fn$;

comment on function ems.fn_economics_monthly_chart(int, date, date) is
  'Křivka měsíční bilance s běžícími součty (GET /economics/monthly-chart).';
