-- ============================================================
-- SuperSam: Автодозвон — базовая миграция (Фаза 1)
-- 01.09.2026. Аддитивная, безопасна для прод-базы (общий Supabase).
-- Применять: docker exec supabase-db psql -U supabase_admin -d postgres -f <file>
-- ============================================================

-- 1) Лог робо-звонков
create table if not exists public.autocall_log (
  id bigint generated always as identity primary key,
  order_group_id uuid references public.order_groups(id) on delete set null,
  campaign_type text not null default 'autocall_slot',
  customer_phone text not null,
  attempt_no smallint not null,
  scenario text not null check (scenario in ('A','B','C')),
  call_started_at timestamptz not null,
  answered_at timestamptz,
  duration_sec smallint,
  sip_code smallint,
  channel_state text,
  dtmf_day smallint check (dtmf_day in (1,2) or dtmf_day is null),
  dtmf_half smallint check (dtmf_half in (1,2) or dtmf_half is null),
  changed boolean not null default false,
  outcome text not null check (outcome in (
    'confirmed','confirmed_kept_previous','confirmed_silent',
    'no_answer','no_input_day','no_input_half',
    'operator','opt_out','invalid_number','error')),
  was_test_mode boolean not null default false,
  asterisk_uniqueid text,
  created_at timestamptz not null default now()
);
create index if not exists idx_autocall_log_group   on public.autocall_log(order_group_id, created_at desc);
create index if not exists idx_autocall_log_phone   on public.autocall_log(customer_phone, created_at desc);
create index if not exists idx_autocall_log_outcome on public.autocall_log(outcome, created_at desc);
create index if not exists idx_autocall_log_uniq    on public.autocall_log(asterisk_uniqueid);
comment on table public.autocall_log is 'Лог робо-звонков (автодозвон через АТС клиента). UI читает outcome.';
grant select on public.autocall_log to authenticated;

-- 2) order_groups: отказ от автозвонков
alter table public.order_groups add column if not exists autocall_opt_out boolean not null default false;
create index if not exists idx_order_groups_autocall_optout
  on public.order_groups(autocall_opt_out) where autocall_opt_out = true;

-- 3) Новые колонки настроек кампаний
alter table public.sms_campaign_settings add column if not exists delay_after_sms_minutes int;
alter table public.sms_campaign_settings add column if not exists retry_interval_minutes int;
alter table public.sms_campaign_settings add column if not exists attempts_per_day int;
alter table public.sms_campaign_settings add column if not exists no_input_escalate_after int;
alter table public.sms_campaign_settings add column if not exists next_day_attempt_times text;
alter table public.sms_campaign_settings add column if not exists call_timeout_seconds int;

-- 4) Строка кампании автодозвона (выключена, тестовая)
insert into public.sms_campaign_settings
  (campaign_type, enabled, test_mode, test_phone, work_hours_start, work_hours_end, work_days,
   max_attempts, delay_after_sms_minutes, retry_interval_minutes, attempts_per_day,
   no_input_escalate_after, next_day_attempt_times, call_timeout_seconds)
values
  ('autocall_slot', false, true, '9067029953', 9, 20, '1,2,3,4,5,6',
   5, 90, 120, 3,
   2, '10:00,14:00', 30)
on conflict (campaign_type) do nothing;

-- 5) Представление флагов для реестра (без N+1)
create or replace view public.order_group_comms_flags as
select
  og.id,
  og.autocall_opt_out,
  og.delivery_date_source,
  og.notification_status,
  og.delivery_status,
  (select l.outcome from public.autocall_log l where l.order_group_id = og.id order by l.created_at desc limit 1) as last_call_outcome,
  (select l.changed from public.autocall_log l where l.order_group_id = og.id order by l.created_at desc limit 1) as last_call_changed,
  (select count(*) from public.autocall_log l where l.order_group_id = og.id) as call_attempts,
  (select count(*) from public.autocall_log l where l.order_group_id = og.id
     and l.outcome in ('no_input_day','no_input_half')) as no_input_count
from public.order_groups og;
grant select on public.order_group_comms_flags to authenticated;