-- multi-signatory-coordination (wave-12)
-- Adds signing order, reminder tracking columns to contract_signatories
-- Extends contract_audit_log event + actor_type constraints

-- ── contract_signatories additions ─────────────────────────────────────────────

ALTER TABLE contract_signatories
  ADD COLUMN IF NOT EXISTS "order"          INTEGER NOT NULL DEFAULT 1,
  ADD COLUMN IF NOT EXISTS reminder_sent_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS reminder_count   INTEGER NOT NULL DEFAULT 0;

--> statement-breakpoint

-- ── contract_audit_log: drop + recreate expanded event check ──────────────────
-- Postgres does not support ALTER CONSTRAINT; must drop and recreate.

ALTER TABLE contract_audit_log
  DROP CONSTRAINT IF EXISTS contract_audit_log_event_check;

--> statement-breakpoint

ALTER TABLE contract_audit_log
  ADD CONSTRAINT contract_audit_log_event_check
    CHECK (event IN ('sent','viewed','signed','declined','voided','downloaded','reminder_sent','link_resent','signatory_replaced'));

--> statement-breakpoint

-- ── contract_audit_log: drop + recreate expanded actor_type check ─────────────

ALTER TABLE contract_audit_log
  DROP CONSTRAINT IF EXISTS contract_audit_log_actor_type_check;

--> statement-breakpoint

ALTER TABLE contract_audit_log
  ADD CONSTRAINT contract_audit_log_actor_type_check
    CHECK (actor_type IN ('staff','signatory','system'));
