-- migrate_campaigns_v1.sql
-- Campaigns: zones array + client archiving + archive rule provision
-- Sales jobs: campaign_id link
-- Run: docker exec -i postgres psql -U lmadmin -d lm360 < /var/www/360lm/installation/migrate_campaigns_v1.sql

ALTER TABLE installation.campaigns
  ADD COLUMN IF NOT EXISTS zones               TEXT[]  NOT NULL DEFAULT '{}',
  ADD COLUMN IF NOT EXISTS client_archived     BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS client_archived_at  TIMESTAMPTZ DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS client_archived_by  TEXT    DEFAULT NULL,
  ADD COLUMN IF NOT EXISTS client_archive_rule TEXT    DEFAULT NULL;

ALTER TABLE sales.jobs
  ADD COLUMN IF NOT EXISTS campaign_id TEXT DEFAULT NULL;

CREATE INDEX IF NOT EXISTS idx_sales_jobs_campaign_id
  ON sales.jobs(campaign_id)
  WHERE campaign_id IS NOT NULL;

NOTIFY pgrst, 'reload schema';
