-- Operace nad PV forecastem v DB a správa referenčních dnů kalibrace (náhrada dřívějších .sh nástrojů).

create or replace function ems.fn_delete_forecast_pv_prague_calendar_day(
  p_day date,
  p_site_id int default null
)
returns table (
  targets_interval_rows bigint,
  deleted_forecast_accuracy_rows bigint,
  deleted_forecast_pv_interval_rows bigint,
  deleted_empty_forecast_pv_run_rows bigint
)
language plpgsql
volatile
as $fn$
declare
  v_ts_start timestamptz;
  v_ts_end timestamptz;
  v_tgt bigint;
  v_acc bigint;
  v_iv bigint;
  v_run bigint;
begin
  v_ts_start := (p_day::text || ' 00:00:00')::timestamp at time zone 'Europe/Prague';
  v_ts_end   := ((p_day + 1)::text || ' 00:00:00')::timestamp at time zone 'Europe/Prague';

  drop table if exists _ems_wipe_pv_forecast_targets;

  create temporary table _ems_wipe_pv_forecast_targets (
    run_id int not null,
    pv_array_id int not null,
    interval_start timestamptz not null
  ) on commit drop;

  insert into _ems_wipe_pv_forecast_targets (run_id, pv_array_id, interval_start)
  select fi.run_id, fi.pv_array_id, fi.interval_start
  from ems.forecast_pv_interval fi
  inner join ems.forecast_pv_run r on r.id = fi.run_id
  where fi.interval_start >= v_ts_start
    and fi.interval_start < v_ts_end
    and (p_site_id is null or r.site_id = p_site_id);

  get diagnostics v_tgt = row_count;

  delete from ems.forecast_accuracy fa
  using (select distinct run_id, interval_start from _ems_wipe_pv_forecast_targets) t
  where fa.run_id = t.run_id
    and fa.interval_start = t.interval_start;

  get diagnostics v_acc = row_count;

  delete from ems.forecast_pv_interval fi
  using _ems_wipe_pv_forecast_targets t
  where fi.run_id = t.run_id
    and fi.pv_array_id = t.pv_array_id
    and fi.interval_start = t.interval_start;

  get diagnostics v_iv = row_count;

  delete from ems.forecast_pv_run fr
  where fr.id in (select distinct run_id from _ems_wipe_pv_forecast_targets)
    and not exists (
      select 1 from ems.forecast_pv_interval x where x.run_id = fr.id
    );

  get diagnostics v_run = row_count;

  targets_interval_rows := v_tgt;
  deleted_forecast_accuracy_rows := v_acc;
  deleted_forecast_pv_interval_rows := v_iv;
  deleted_empty_forecast_pv_run_rows := v_run;
  return next;
end;
$fn$;

comment on function ems.fn_delete_forecast_pv_prague_calendar_day is
'Maze forecast_pv_interval (a navázané forecast_accuracy) pro řádky podle začátku intervalu '
  'na daný kalendářní den hranovaný půlnocí Europe/Prague — ne podle TZ lokality.'
  ' p_site_id NULL = všechny lokality. Prázdné forecast_pv_run v mazané množině smaže návazně.'
  ' Destruktivní vůči historii přesnosti; preferuj jen provozní re-import forecastu.';


create or replace function ems.fn_pv_forecast_sync_reference_days(
  p_site_id int,
  p_days_local date[],
  p_replace_existing boolean default false
)
returns int
language plpgsql
volatile
as $fn$
declare
  v_after int;
begin
  if not exists (select 1 from ems.site s where s.id = p_site_id) then
    raise exception using
      message = format('site_id %s neexistuje v ems.site', p_site_id),
      errcode = 'P0001';
  end if;

  if p_replace_existing then
    delete from ems.site_pv_forecast_reference_day d where d.site_id = p_site_id;
  end if;

  insert into ems.site_pv_forecast_reference_day (site_id, day_local)
  select p_site_id, d::date
  from unnest(p_days_local) as u(d)
  where d is not null
  on conflict (site_id, day_local) do nothing;

  select count(*)::int into v_after from ems.site_pv_forecast_reference_day d where d.site_id = p_site_id;
  return v_after;
end;
$fn$;

comment on function ems.fn_pv_forecast_sync_reference_days is
'Zapíše kalendářní dny (datum ve zdi site.timezone lokality při použití s fn_pv_forecast_delta_profile) jako referenční. '
  'p_replace_existing true smaže předchozí záznamy dané lokality; false jen doplňuje unnest bez přepsání. '
  'Vrací počet řádků v site_pv_forecast_reference_day po operaci.';

