ALTER TABLE calendar_events
    ADD COLUMN IF NOT EXISTS occasion_key text,
    ADD COLUMN IF NOT EXISTS event_variant text NOT NULL DEFAULT 'PRIMARY',
    ADD COLUMN IF NOT EXISTS aliases jsonb NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS themes jsonb NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS campaign_tags jsonb NOT NULL DEFAULT '[]'::jsonb,
    ADD COLUMN IF NOT EXISTS importance_bps integer;
-- statement-break
UPDATE calendar_events
SET occasion_key = coalesce(occasion_key,event_key),
    importance_bps = coalesce(importance_bps,0)
WHERE occasion_key IS NULL OR importance_bps IS NULL;
-- statement-break
ALTER TABLE calendar_events
    ALTER COLUMN occasion_key SET NOT NULL,
    ALTER COLUMN importance_bps SET DEFAULT 0,
    ALTER COLUMN importance_bps SET NOT NULL,
    ADD CONSTRAINT calendar_events_importance_bps_check CHECK (importance_bps BETWEEN 0 AND 10000),
    ADD CONSTRAINT calendar_events_variant_check CHECK (event_variant IN ('PRIMARY','CAMPAIGN_WINDOW','OPERATIONAL_WINDOW'));
-- statement-break
CREATE UNIQUE INDEX IF NOT EXISTS calendar_events_canonical_occurrence_idx
    ON calendar_events (catalog_version_id, occasion_key, event_variant, start_date, end_date);
-- statement-break
CREATE INDEX IF NOT EXISTS calendar_events_ranked_context_idx
    ON calendar_events (catalog_version_id, importance_bps DESC, relevance_bps DESC, start_date);
-- statement-break
INSERT INTO schema_migrations(version) VALUES (9) ON CONFLICT DO NOTHING;