-- Email ticket channels, inbound webhook token, message source, escalation flag on tickets
-- On Supabase self-hosted images, run as table owner if `postgres` is not superuser, e.g.:
--   docker compose exec -T db psql -U supabase_admin -d postgres -f ...

ALTER TYPE message_source ADD VALUE IF NOT EXISTS 'email';

DO $$
BEGIN
  CREATE TYPE channel_kind AS ENUM ('whatsapp', 'portal', 'email');
EXCEPTION
  WHEN duplicate_object THEN NULL;
END $$;

ALTER TABLE channels ALTER COLUMN phone_number TYPE VARCHAR(255);
ALTER TABLE channels ADD COLUMN IF NOT EXISTS kind channel_kind NOT NULL DEFAULT 'whatsapp';
ALTER TABLE channels ADD COLUMN IF NOT EXISTS inbound_token UUID;
CREATE UNIQUE INDEX IF NOT EXISTS idx_channels_inbound_token ON channels (inbound_token) WHERE inbound_token IS NOT NULL;

UPDATE channels SET kind = 'portal' WHERE phone_number = 'portal:system';

ALTER TABLE tickets ADD COLUMN IF NOT EXISTS needs_escalation BOOLEAN NOT NULL DEFAULT FALSE;

ALTER TABLE contacts ALTER COLUMN phone_number TYPE VARCHAR(255);

CREATE INDEX idx_tickets_needs_escalation ON tickets (needs_escalation) WHERE needs_escalation = TRUE;
