-- Complete Database Fix Migration
-- This handles all dependencies and missing columns/tables

USE flick_db;

-- Step 1: Ensure base tables exist first
CREATE TABLE IF NOT EXISTS player_analytics_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(255) UNIQUE NOT NULL,
    user_id INT,
    video_id INT,
    video_title VARCHAR(500),
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP NULL,
    watch_duration INT DEFAULT 0,
    total_duration INT,
    completion_rate DECIMAL(5,2) DEFAULT 0,
    device_type VARCHAR(50),
    browser VARCHAR(100),
    ip_address VARCHAR(45),
    country VARCHAR(100),
    total_watch_time_ms BIGINT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    last_heartbeat TIMESTAMP NULL,
    player_id VARCHAR(255),
    quality VARCHAR(50),
    buffering_count INT DEFAULT 0,
    buffering_duration DECIMAL(10,2) DEFAULT 0,
    error_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS player_analytics_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(255) NOT NULL,
    event_type VARCHAR(50) NOT NULL,
    event_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    event_data JSON,
    video_timestamp INT,
    player_id VARCHAR(255),
    buffering_duration_ms INT DEFAULT 0,
    error_count INT DEFAULT 0,
    quality_level VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Step 2: Try to add missing columns if tables already existed (ignore errors)
ALTER TABLE player_analytics_sessions 
ADD COLUMN IF NOT EXISTS total_watch_time_ms BIGINT DEFAULT 0;
ALTER TABLE player_analytics_sessions
ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT TRUE;
ALTER TABLE player_analytics_sessions
ADD COLUMN IF NOT EXISTS last_heartbeat TIMESTAMP NULL;
ALTER TABLE player_analytics_sessions
ADD COLUMN IF NOT EXISTS player_id VARCHAR(255);
ALTER TABLE player_analytics_sessions
ADD COLUMN IF NOT EXISTS quality VARCHAR(50);
ALTER TABLE player_analytics_sessions
ADD COLUMN IF NOT EXISTS buffering_count INT DEFAULT 0;
ALTER TABLE player_analytics_sessions
ADD COLUMN IF NOT EXISTS buffering_duration DECIMAL(10,2) DEFAULT 0;
ALTER TABLE player_analytics_sessions
ADD COLUMN IF NOT EXISTS error_count INT DEFAULT 0;

-- Step 3: Add missing columns to player_analytics_events one by one 
ALTER TABLE player_analytics_events
ADD COLUMN IF NOT EXISTS player_id VARCHAR(255);
ALTER TABLE player_analytics_events
ADD COLUMN IF NOT EXISTS buffering_duration_ms INT DEFAULT 0;
ALTER TABLE player_analytics_events
ADD COLUMN IF NOT EXISTS error_count INT DEFAULT 0;
ALTER TABLE player_analytics_events
ADD COLUMN IF NOT EXISTS quality_level VARCHAR(50);

-- Step 4: Create 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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Step 5: Create player_configurations table
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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Step 6: Create player_realtime_metrics table
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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Step 7: Create analytics_daily_stats table
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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Step 8: Now add indexes (after columns exist)
ALTER TABLE player_analytics_sessions 
ADD INDEX IF NOT EXISTS idx_video_id (video_id),
ADD INDEX IF NOT EXISTS idx_user_id (user_id),
ADD INDEX IF NOT EXISTS idx_start_time (start_time),
ADD INDEX IF NOT EXISTS idx_session_date (DATE(start_time)),
ADD INDEX IF NOT EXISTS idx_sessions_active (is_active, last_heartbeat),
ADD INDEX IF NOT EXISTS idx_sessions_video (video_id, is_active),
ADD INDEX IF NOT EXISTS idx_sessions_user (user_id, is_active),
ADD INDEX IF NOT EXISTS idx_sessions_player (player_id);

ALTER TABLE player_analytics_events
ADD INDEX IF NOT EXISTS idx_event_type (event_type),
ADD INDEX IF NOT EXISTS idx_event_timestamp (event_timestamp),
ADD INDEX IF NOT EXISTS idx_events_player (player_id);

ALTER TABLE video_player_settings
ADD INDEX IF NOT EXISTS idx_player_id (player_id);

ALTER TABLE player_configurations
ADD INDEX IF NOT EXISTS idx_config_name (config_name),
ADD INDEX IF NOT EXISTS idx_is_default (is_default);

ALTER TABLE player_realtime_metrics
ADD UNIQUE KEY IF NOT EXISTS unique_video_player (video_id, player_id),
ADD INDEX IF NOT EXISTS idx_last_updated (last_updated),
ADD INDEX IF NOT EXISTS idx_player_id (player_id);

ALTER TABLE analytics_daily_stats
ADD UNIQUE KEY IF NOT EXISTS unique_video_date (video_id, date),
ADD INDEX IF NOT EXISTS idx_date (date),
ADD INDEX IF NOT EXISTS idx_video_date (video_id, date),
ADD INDEX IF NOT EXISTS idx_player_id (player_id);

-- Step 9: Add foreign key constraints (if parent table exists)
-- ALTER TABLE player_analytics_events 
-- ADD CONSTRAINT fk_session_id FOREIGN KEY (session_id) 
-- REFERENCES player_analytics_sessions(session_id) ON DELETE CASCADE;

-- Step 10: Insert default data
INSERT IGNORE INTO video_player_settings (player_id, autoplay, muted, controls) 
VALUES ('default', FALSE, TRUE, TRUE);

INSERT IGNORE INTO player_configurations (config_name, is_default) 
VALUES ('default', TRUE);

-- Step 11: Update NULL player_id values
UPDATE player_analytics_sessions 
SET player_id = CONCAT('player_', session_id) 
WHERE player_id IS NULL AND session_id IS NOT NULL;

UPDATE player_analytics_events e
JOIN player_analytics_sessions s ON e.session_id = s.session_id
SET e.player_id = s.player_id
WHERE e.player_id IS NULL;

-- Final message
SELECT 'Database migration completed!' as message;
