-- =====================================================
-- Coastal Command Bin Cleaning - Content Archive Script
-- Purpose: Archive existing content before PDF content update
-- Date: 2026-01-26
-- =====================================================

-- Step 1: Create archive table if it doesn't exist
CREATE TABLE IF NOT EXISTS content_sections_archive (
    archive_id INT AUTO_INCREMENT PRIMARY KEY,
    original_content_id INT,
    section_key VARCHAR(100),
    section_name VARCHAR(255),
    content TEXT,
    content_type VARCHAR(50),
    status VARCHAR(20),
    position_order INT,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    archive_reason VARCHAR(255),
    INDEX idx_archive_key (section_key),
    INDEX idx_archive_reason (archive_reason),
    INDEX idx_archived_at (archived_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 2: Archive all current content before updates
INSERT INTO content_sections_archive
    (original_content_id, section_key, section_name, content, content_type, status, position_order, archive_reason)
SELECT
    content_id,
    section_key,
    section_name,
    content,
    content_type,
    status,
    position_order,
    'Pre-PDF-Update-Backup-2026-01-26'
FROM content_sections
WHERE status = 'active';

-- Verification query (run separately to confirm backup)
-- SELECT COUNT(*) as archived_count FROM content_sections_archive WHERE archive_reason = 'Pre-PDF-Update-Backup-2026-01-26';
