-- Read-modely: health, aktivní lokality pro joby, včerejší ekonomika (Discord), Loxone po změně režimu.

create or replace function ems.fn_health_summary()
returns jsonb
language sql
stable
as $fn$
  select jsonb_build_object(
    'db', 'ok',
    'active_plan_slots', (
      select count(*)::bigint
      from ems.planning_interval pi
      inner join ems.planning_run pr on pr.id = pi.run_id
      where pr.status = 'active'
    ),
    'timestamp', to_jsonb(now() at time zone 'utc')
  );
$fn$;

comment on function ems.fn_health_summary() is
  'Lehký health payload (COUNT aktivních intervalů + čas UTC).';

create or replace function ems.fn_health_detailed_db()
returns jsonb
language sql
stable
as $fn$
  select jsonb_build_object(
    'last_telemetry_age_sec', (
      select case
        when max(ti.measured_at) is null then -1
        else greatest(
          0,
          extract(epoch from (now() - max(ti.measured_at)))::int
        )
      end
      from ems.telemetry_inverter ti
    ),
    'last_plan_age_sec', (
      select case
        when max(pr.created_at) is null then -1
        else greatest(
          0,
          extract(epoch from (now() - max(pr.created_at)))::int
        )
      end
      from ems.planning_run pr
      where pr.status = 'active'
    )
  );
$fn$;

comment on function ems.fn_health_detailed_db() is
  'Stáří poslední telemetrie (globální max) a posledního aktivního planning_run.';

create or replace function ems.fn_vw_site_directory_active()
returns jsonb
language sql
stable
as $fn$
  select coalesce(
    jsonb_agg(
      jsonb_build_object(
        'id', sd.id,
        'code', sd.code,
        'name', sd.name,
        'timezone', sd.timezone,
        'latitude', sd.latitude,
        'longitude', sd.longitude,
        'active', sd.active,
        'notes', sd.notes,
        'created_at', sd.created_at
      )
      order by sd.id
    ),
    '[]'::jsonb
  )
  from ems.vw_site_directory sd
  where sd.active is true;
$fn$;

comment on function ems.fn_vw_site_directory_active() is
  'Řádky vw_site_directory pro active=true (joby po lokalitách).';

create or replace function ems.fn_site_economics_yesterday_notification(p_site_id int)
returns jsonb
language sql
stable
as $fn$
  select to_jsonb(d)
  from (
    select
      ed.import_kwh,
      ed.export_kwh,
      ed.import_cost_czk,
      ed.export_revenue_czk,
      ed.green_bonus_czk,
      ed.total_balance_czk,
      ed.planned_balance_czk
    from ems.vw_economics_daily ed
    where ed.site_id = p_site_id
      and ed.day_local = (
        (current_timestamp at time zone 'Europe/Prague')::date - 1
      )
    limit 1
  ) d;
$fn$;

comment on function ems.fn_site_economics_yesterday_notification(int) is
  'Včerejší řádek vw_economics_daily (Europe/Prague) pro denní Discord souhrn.';

create or replace function ems.fn_site_mode_loxone_bundle(p_site_id int)
returns jsonb
language sql
stable
as $fn$
  select jsonb_build_object(
    'mode_code', m.mode_code,
    'activated_at', m.activated_at,
    'loxone_mode_value', d.loxone_mode_value,
    'loxone_host', ep.host,
    'loxone_port', ep.port,
    'loxone_protocol', ep.protocol
  )
  from ems.site_operating_mode m
  join ems.operating_mode_def d on d.code = m.mode_code
  left join lateral (
    select se.host, se.port, se.protocol
    from ems.site_endpoint se
    where se.site_id = p_site_id
      and se.endpoint_type = 'loxone_http'
      and se.enabled is true
    order by se.id
    limit 1
  ) ep on true
  where m.site_id = p_site_id
  limit 1;
$fn$;

comment on function ems.fn_site_mode_loxone_bundle(int) is
  'Režim + Loxone endpoint po úspěšném zápisu režimu (API odpověď / push).';
