-- Migration: Add A1 retry tracking to campaign_status table
-- Purpose: Prevent infinite error emails for empty A1 campaigns
-- Date: January 31, 2026

\echo 'Adding A1 retry tracking fields to campaign_status table...'

ALTER TABLE campaign_status
ADD COLUMN IF NOT EXISTS a1_retry_count INTEGER DEFAULT 0,
ADD COLUMN IF NOT EXISTS a1_last_retry_at TIMESTAMP,
ADD COLUMN IF NOT EXISTS a1_permanently_failed BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS a1_failure_reason TEXT;

\echo 'Fields added successfully'

-- Create index for faster queries
CREATE INDEX IF NOT EXISTS idx_campaign_status_a1_failed ON campaign_status(a1_permanently_failed);

\echo 'Index created'

-- Add comments for documentation
COMMENT ON COLUMN campaign_status.a1_retry_count IS 'Number of times A1→A2 processing attempted with empty folder';
COMMENT ON COLUMN campaign_status.a1_last_retry_at IS 'Timestamp of last retry attempt';
COMMENT ON COLUMN campaign_status.a1_permanently_failed IS 'TRUE if campaign failed all 3 retry attempts';
COMMENT ON COLUMN campaign_status.a1_failure_reason IS 'Description of why campaign was marked as permanently failed';

\echo ''
\echo '============================================================'
\echo 'Migration 003 complete!'
\echo '============================================================'
\echo 'Added fields: a1_retry_count, a1_last_retry_at, a1_permanently_failed, a1_failure_reason'
\echo 'Purpose: Track A1 empty folder retries (max 3 attempts)'
\echo '============================================================'