-- Comprehensive Database Migration Fix
-- This script fixes all missing columns and tables

-- Ensure database is selected
USE flick_db;

-- 1. Fix player_analytics_sessions table
ALTER TABLE player_analytics_sessions 
ADD COLUMN IF NOT EXISTS total_watch_time_ms BIGINT DEFAULT 0 AFTER watch_duration,
ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT TRUE AFTER completion_rate,
ADD COLUMN IF NOT EXISTS last_heartbeat TIMESTAMP NULL AFTER is_active,
ADD COLUMN IF NOT EXISTS player_id VARCHAR(255) AFTER session_id,
ADD COLUMN IF NOT EXISTS quality VARCHAR(50) AFTER browser,
ADD COLUMN IF NOT EXISTS buffering_count INT DEFAULT 0 AFTER quality,
ADD COLUMN IF NOT EXISTS buffering_duration DECIMAL(10,2) DEFAULT 0 AFTER buffering_count,
ADD COLUMN IF NOT EXISTS error_count INT DEFAULT 0 AFTER buffering_duration;

-- 2. Fix player_analytics_events table
ALTER TABLE player_analytics_events
ADD COLUMN IF NOT EXISTS player_id VARCHAR(255) AFTER session_id,
ADD COLUMN IF NOT EXISTS buffering_duration_ms INT DEFAULT 0 AFTER video_timestamp,
ADD COLUMN IF NOT EXISTS error_count INT DEFAULT 0 AFTER buffering_duration_ms,
ADD COLUMN IF NOT EXISTS quality_level VARCHAR(50) AFTER error_count;

-- 3. Create or update video_player_settings table
CREATE TABLE IF NOT EXISTS video_player_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    player_id VARCHAR(255) UNIQUE NOT NULL,
    autoplay BOOLEAN DEFAULT FALSE,
    muted BOOLEAN DEFAULT TRUE,
    controls BOOLEAN DEFAULT TRUE,
    loop_video BOOLEAN DEFAULT FALSE,
    playback_rate DECIMAL(3,2) DEFAULT 1.00,
    default_quality VARCHAR(50) DEFAULT 'auto',
    volume DECIMAL(3,2) DEFAULT 1.00,
    seek_buttons BOOLEAN DEFAULT TRUE,
    seek_seconds INT DEFAULT 10,
    skip_intro_enabled BOOLEAN DEFAULT FALSE,
    skip_intro_seconds INT DEFAULT 0,
    skip_outro_enabled BOOLEAN DEFAULT FALSE,
    skip_outro_seconds INT DEFAULT 0,
    theater_mode BOOLEAN DEFAULT FALSE,
    pip_enabled BOOLEAN DEFAULT TRUE,
    keyboard_shortcuts BOOLEAN DEFAULT TRUE,
    show_thumbnails BOOLEAN DEFAULT TRUE,
    show_captions BOOLEAN DEFAULT TRUE,
    default_caption_language VARCHAR(10) DEFAULT 'en',
    playback_quality_selector BOOLEAN DEFAULT TRUE,
    remember_position BOOLEAN DEFAULT TRUE,
    remember_volume BOOLEAN DEFAULT TRUE,
    watermark_enabled BOOLEAN DEFAULT FALSE,
    watermark_position VARCHAR(20) DEFAULT 'bottom-right',
    watermark_opacity DECIMAL(3,2) DEFAULT 0.50,
    analytics_enabled BOOLEAN DEFAULT TRUE,
    heartbeat_interval INT DEFAULT 10000,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_player_id (player_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Create player_realtime_metrics table if it doesn't exist
CREATE TABLE IF NOT EXISTS player_realtime_metrics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    video_id INT,
    player_id VARCHAR(255),
    active_sessions INT DEFAULT 0,
    current_viewers INT DEFAULT 0,
    peak_concurrent INT DEFAULT 0,
    peak_time TIMESTAMP NULL,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_video_player (video_id, player_id),
    INDEX idx_last_updated (last_updated),
    INDEX idx_player_id (player_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 5. Create analytics_daily_stats table if missing
CREATE TABLE IF NOT EXISTS analytics_daily_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    video_id INT NOT NULL,
    player_id VARCHAR(255),
    date DATE NOT NULL,
    total_watch_hours DECIMAL(10,2) DEFAULT 0,
    unique_viewers INT DEFAULT 0,
    total_views INT DEFAULT 0,
    avg_watch_time DECIMAL(10,2) DEFAULT 0,
    completion_count INT DEFAULT 0,
    buffering_events INT DEFAULT 0,
    error_events INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_video_date (video_id, date),
    INDEX idx_date (date),
    INDEX idx_video_date (video_id, date),
    INDEX idx_player_id (player_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 6. Create player_configurations table for advanced settings
CREATE TABLE IF NOT EXISTS player_configurations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_name VARCHAR(100) NOT NULL,
    player_type VARCHAR(50) DEFAULT 'videojs',
    skin_theme VARCHAR(50) DEFAULT 'default',
    primary_color VARCHAR(7) DEFAULT '#ff0000',
    secondary_color VARCHAR(7) DEFAULT '#ffffff',
    control_bar_position VARCHAR(20) DEFAULT 'bottom',
    big_play_button BOOLEAN DEFAULT TRUE,
    loading_spinner BOOLEAN DEFAULT TRUE,
    error_display BOOLEAN DEFAULT TRUE,
    text_track_settings BOOLEAN DEFAULT TRUE,
    playback_rates JSON DEFAULT '["0.5", "0.75", "1", "1.25", "1.5", "2"]',
    quality_levels JSON DEFAULT '["auto", "1080p", "720p", "480p", "360p", "240p"]',
    hotkeys JSON,
    plugins JSON,
    custom_css TEXT,
    custom_js TEXT,
    is_default BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_config_name (config_name),
    INDEX idx_is_default (is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 7. Insert default player settings if not exists
INSERT IGNORE INTO video_player_settings (player_id, autoplay, muted, controls) 
VALUES ('default', FALSE, TRUE, TRUE);

-- 8. Insert default player configuration
INSERT IGNORE INTO player_configurations (config_name, is_default) 
VALUES ('default', TRUE);

-- 9. Add indexes for better performance
CREATE INDEX IF NOT EXISTS idx_sessions_active ON player_analytics_sessions(is_active, last_heartbeat);
CREATE INDEX IF NOT EXISTS idx_sessions_video ON player_analytics_sessions(video_id, is_active);
CREATE INDEX IF NOT EXISTS idx_sessions_user ON player_analytics_sessions(user_id, is_active);
CREATE INDEX IF NOT EXISTS idx_sessions_player ON player_analytics_sessions(player_id);
CREATE INDEX IF NOT EXISTS idx_events_player ON player_analytics_events(player_id);

-- 10. Update any NULL player_id values with a default
UPDATE player_analytics_sessions 
SET player_id = CONCAT('player_', session_id) 
WHERE player_id IS NULL;

UPDATE player_analytics_events 
SET player_id = CONCAT('player_', session_id) 
WHERE player_id IS NULL;

-- Success message
SELECT 'Database migration completed successfully!' as message;
