-- Migration: Add CDN support and multi-quality storage

-- CDN provider configurations
CREATE TABLE IF NOT EXISTS db_cdn_providers (
    provider_id INT AUTO_INCREMENT PRIMARY KEY,
    provider_name VARCHAR(50) NOT NULL,
    provider_type ENUM('bunny', 'cloudflare', 's3', 'backblaze', 'wasabi', 'custom') NOT NULL,
    api_key VARCHAR(255),
    api_secret VARCHAR(255),
    storage_zone VARCHAR(100),
    pull_zone VARCHAR(100),
    cdn_hostname VARCHAR(255),
    region VARCHAR(50),
    is_active TINYINT DEFAULT 1,
    priority INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_active_priority (is_active, priority)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Video quality variants (for adaptive streaming)
CREATE TABLE IF NOT EXISTS db_video_qualities (
    quality_id INT AUTO_INCREMENT PRIMARY KEY,
    video_id INT NOT NULL,
    quality_label VARCHAR(20) NOT NULL,
    resolution VARCHAR(20),
    bitrate INT,
    file_path VARCHAR(512),
    cdn_url VARCHAR(512),
    file_size BIGINT,
    codec VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_video (video_id),
    FOREIGN KEY (video_id) REFERENCES db_videofiles(video_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- CDN cache status tracking
CREATE TABLE IF NOT EXISTS db_cdn_cache_status (
    cache_id INT AUTO_INCREMENT PRIMARY KEY,
    video_id INT NOT NULL,
    provider_id INT NOT NULL,
    quality_label VARCHAR(20),
    cdn_url VARCHAR(512),
    status ENUM('pending', 'uploading', 'ready', 'failed') DEFAULT 'pending',
    upload_progress INT DEFAULT 0,
    error_message TEXT,
    last_checked DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_video_provider (video_id, provider_id),
    INDEX idx_status (status),
    FOREIGN KEY (video_id) REFERENCES db_videofiles(video_id) ON DELETE CASCADE,
    FOREIGN KEY (provider_id) REFERENCES db_cdn_providers(provider_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- CDN bandwidth tracking (for cost monitoring)
CREATE TABLE IF NOT EXISTS db_cdn_bandwidth_usage (
    usage_id INT AUTO_INCREMENT PRIMARY KEY,
    provider_id INT NOT NULL,
    video_id INT,
    date DATE NOT NULL,
    bandwidth_mb DECIMAL(12,2) DEFAULT 0,
    requests_count INT DEFAULT 0,
    cost_estimate DECIMAL(10,4) DEFAULT 0,
    UNIQUE KEY unique_provider_video_date (provider_id, video_id, date),
    INDEX idx_provider_date (provider_id, date),
    FOREIGN KEY (provider_id) REFERENCES db_cdn_providers(provider_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;