-- Migration: Add campaign_status table
-- Purpose: Track campaign processing and live status to prevent duplicate webhooks
-- Date: November 5, 2025

\echo 'Creating campaign_status table...'

CREATE TABLE IF NOT EXISTS campaign_status (
    -- Primary Key
    id SERIAL PRIMARY KEY,

    -- Campaign Identification
    campaign_id VARCHAR(255) UNIQUE NOT NULL,       -- DAM campaign folder ID (unique!)
    campaign_number VARCHAR(50) NOT NULL,            -- C000000078
    campaign_name VARCHAR(500) NOT NULL,

    -- Live Status
    live_campaign VARCHAR(3) NOT NULL,               -- 'YES' or 'NO'
    status VARCHAR(10) NOT NULL,                     -- A1, A2, A4, A5, B1, B2, etc.

    -- Webhook Tracking
    webhook_sent BOOLEAN DEFAULT FALSE,
    webhook_sent_at TIMESTAMP,

    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

\echo 'Table created'

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_campaign_status_campaign_id ON campaign_status(campaign_id);
CREATE INDEX IF NOT EXISTS idx_campaign_status_number ON campaign_status(campaign_number);
CREATE INDEX IF NOT EXISTS idx_campaign_status_status ON campaign_status(status);
CREATE INDEX IF NOT EXISTS idx_campaign_status_live ON campaign_status(live_campaign);
CREATE INDEX IF NOT EXISTS idx_campaign_status_webhook_sent ON campaign_status(webhook_sent);

\echo 'Indexes created'

-- Create trigger for auto-updating updated_at
CREATE TRIGGER update_campaign_status_updated_at
    BEFORE UPDATE ON campaign_status
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

\echo 'Trigger created'

\echo ''
\echo '============================================================'
\echo 'Migration 002 complete!'
\echo '============================================================'
\echo 'Table: campaign_status'
\echo 'Indexes: 5'
\echo 'Triggers: 1'
\echo ''
\echo 'Purpose: Tracks campaign processing and live status'
\echo '  - Prevents duplicate A1→A2 processing'
\echo '  - Prevents duplicate A4 webhooks'
\echo '  - Tracks which campaigns are going live (YES/NO)'
\echo '============================================================'