-- 30절. 입금요청 메일에 금액 칸과 빠진 상태와 자동 offset 을 더한다.
-- 001_init.sql 의 표 정의는 고치지 않는다.

alter table payment_notice_mails
  add column quoted_amount bigint;

do $$
declare
  r record;
begin
  for r in
    select con.conname
    from pg_catalog.pg_constraint con
    where con.conrelid = 'public.payment_notice_mails'::regclass
      and con.contype = 'c'
  loop
    execute format('alter table payment_notice_mails drop constraint %I', r.conname);
  end loop;
end
$$;

alter table payment_notice_mails
  add check ("trigger" in ('auto', 'manual')),
  add check (status in ('queued', 'sent', 'failed', 'skipped')),
  add check (
    ("trigger" = 'auto' and offset_days in (30, 7, 1, 0, -1))
    or ("trigger" = 'manual' and offset_days is null)
  ),
  add check (quoted_amount is null or quoted_amount >= 0);

drop index uq_auto_payment_notice;

create unique index uq_auto_payment_notice
  on payment_notice_mails (service_id, anchor_on, offset_days, to_address)
  where "trigger" = 'auto';