ems/db/routines/R__082_fn_ote_day_signals_prague.sql

-- OTE CZ: signály pro "briefing" dalšího dne (TZ Europe/Prague)
--
-- Cíl: vrátit pár srozumitelných signálů (negativní/okolo nuly/špička),
-- které se dají posílat do Discordu bez logiky v Pythonu.

create or replace function ems.fn_ote_day_signals_prague(
  p_day date,
  p_lookback_days int default 14
)
returns jsonb
language sql
stable
as $fn$
  with params as (
    select
      greatest(1, least(coalesce(p_lookback_days, 14), 60))::int as lookback_days,
      0.25::numeric as zeroish_abs_czk_kwh,
      4::numeric as spike_interesting_czk_kwh,
      6::numeric as spike_extreme_czk_kwh
  ),
  day_slots as (
    select
      mip.interval_start,
      (mip.interval_start at time zone 'Europe/Prague') as local_ts,
      extract(hour from mip.interval_start at time zone 'Europe/Prague')::int as local_hour,
      mip.buy_raw_price_czk_kwh as price
    from ems.market_interval_price mip
    where mip.market_source = 'OTE_CZ'
      and (mip.interval_start at time zone 'Europe/Prague')::date = p_day
  ),
  day_agg as (
    select
      count(*)::int as slot_count,
      min(price) as min_price,
      max(price) as max_price,
      avg(price) as avg_price,
      count(*) filter (where price < 0)::int as neg_slots,
      count(*) filter (where abs(price) <= (select zeroish_abs_czk_kwh from params))::int as zeroish_slots,
      min(price) filter (where local_hour between 10 and 14) as noon_min_price,
      avg(price) filter (where local_hour between 10 and 14) as noon_avg_price,
      max(price) filter (where local_hour between 6 and 9) as morning_max_price,
      max(price) filter (where local_hour between 17 and 21) as evening_max_price,
      max(price) filter (where local_hour between 17 and 21)
        - avg(price) filter (where local_hour between 10 and 14) as evening_minus_noon
    from day_slots
  ),
  hist_hours as (
    select
      (mip.interval_start at time zone 'Europe/Prague')::date as d,
      extract(hour from mip.interval_start at time zone 'Europe/Prague')::int as h,
      avg(mip.buy_raw_price_czk_kwh) as avg_price
    from ems.market_interval_price mip
    cross join params p
    where mip.market_source = 'OTE_CZ'
      and (mip.interval_start at time zone 'Europe/Prague')::date < p_day
      and (mip.interval_start at time zone 'Europe/Prague')::date >= p_day - (p.lookback_days || ' days')::interval
    group by 1, 2
  ),
  hist_windows as (
    select
      avg(avg_price) filter (where h between 17 and 21) as avg_evening,
      avg(avg_price) filter (where h between 6 and 9) as avg_morning,
      avg(avg_price) filter (where h between 10 and 14) as avg_noon
    from hist_hours
  ),
  signals as (
    select jsonb_agg(s order by (s ->> 'severity') desc, (s ->> 'code')) as arr
    from (
      -- negativní
      select jsonb_build_object(
        'code', 'NEG_EXTREME',
        'severity', 3,
        'title', 'extrémně záporné ceny',
        'detail', format('min %.3f Kč/kWh, záporné sloty %s', (select min_price from day_agg), (select neg_slots from day_agg))
      ) s
      where (select min_price from day_agg) <= -0.50
         or (select neg_slots from day_agg) >= 8

      union all
      select jsonb_build_object(
        'code', 'NEG_PRESENT',
        'severity', 2,
        'title', 'záporné ceny',
        'detail', format('min %.3f Kč/kWh, záporné sloty %s', (select min_price from day_agg), (select neg_slots from day_agg))
      ) s
      where (select min_price from day_agg) < 0
        and not (
          (select min_price from day_agg) <= -0.50
          or (select neg_slots from day_agg) >= 8
        )

      -- okolo nuly přes den
      union all
      select jsonb_build_object(
        'code', 'NOON_ZEROISH',
        'severity', 2,
        'title', 'poledne okolo nuly',
        'detail', format('polední průměr %.3f Kč/kWh (10–14)', (select noon_avg_price from day_agg))
      ) s
      where coalesce((select noon_avg_price from day_agg), 999) <= (select zeroish_abs_czk_kwh from params)

      union all
      select jsonb_build_object(
        'code', 'MANY_ZEROISH',
        'severity', 1,
        'title', 'hodně slotů okolo nuly',
        'detail', format('okolo nuly slotů %s (|p| ≤ %.2f Kč/kWh)', (select zeroish_slots from day_agg), (select zeroish_abs_czk_kwh from params))
      ) s
      where (select zeroish_slots from day_agg) >= 16

      -- špička večer (hard)
      union all
      select jsonb_build_object(
        'code', 'EVENING_SPIKE_EXTREME',
        'severity', 3,
        'title', 'večer extrémně drahý',
        'detail', format('max večer %.3f Kč/kWh (17–21)', (select evening_max_price from day_agg))
      ) s
      where coalesce((select evening_max_price from day_agg), 0) >= (select spike_extreme_czk_kwh from params)

      union all
      select jsonb_build_object(
        'code', 'EVENING_SPIKE_INTERESTING',
        'severity', 2,
        'title', 'večer drahý',
        'detail', format('max večer %.3f Kč/kWh (17–21)', (select evening_max_price from day_agg))
      ) s
      where coalesce((select evening_max_price from day_agg), 0) >= (select spike_interesting_czk_kwh from params)
        and coalesce((select evening_max_price from day_agg), 0) < (select spike_extreme_czk_kwh from params)

      -- špička večer (relativní vůči posledním N dnům)
      union all
      select jsonb_build_object(
        'code', 'EVENING_SPIKE_REL',
        'severity', 2,
        'title', 'večer nadprůměrná špička',
        'detail', format(
          'max večer %.3f vs. průměr %.3f (lookback %s dní)',
          (select evening_max_price from day_agg),
          (select avg_evening from hist_windows),
          (select lookback_days from params)
        )
      ) s
      where (select avg_evening from hist_windows) is not null
        and coalesce((select evening_max_price from day_agg), 0) >= (select avg_evening from hist_windows) + 1.5
    ) t
  )
  select jsonb_build_object(
    'day', p_day,
    'lookback_days', (select lookback_days from params),
    'metrics', jsonb_build_object(
      'slot_count', (select slot_count from day_agg),
      'min_price', round(coalesce((select min_price from day_agg), 0)::numeric, 6),
      'max_price', round(coalesce((select max_price from day_agg), 0)::numeric, 6),
      'avg_price', round(coalesce((select avg_price from day_agg), 0)::numeric, 6),
      'neg_slots', (select neg_slots from day_agg),
      'zeroish_slots', (select zeroish_slots from day_agg),
      'noon_avg_price', round(coalesce((select noon_avg_price from day_agg), 0)::numeric, 6),
      'evening_max_price', round(coalesce((select evening_max_price from day_agg), 0)::numeric, 6),
      'avg_evening_last_n', round(coalesce((select avg_evening from hist_windows), 0)::numeric, 6),
      'avg_noon_last_n', round(coalesce((select avg_noon from hist_windows), 0)::numeric, 6)
    ),
    'signals', coalesce((select arr from signals), '[]'::jsonb)
  );
$fn$;

comment on function ems.fn_ote_day_signals_prague(date, int) is
  'Signály pro briefing OTE_CZ cen pro p_day (negativní/okolo nuly/špička), včetně relativní špičky proti posledním N dnům.';