-- Clean Migration: Drop and Recreate Tables
-- WARNING: This will delete all existing data in these tables!

USE flick_db;

-- Step 1: Drop existing tables if they exist (to ensure clean structure)
DROP TABLE IF EXISTS player_analytics_events;
DROP TABLE IF EXISTS player_analytics_sessions;
DROP TABLE IF EXISTS video_player_settings;
DROP TABLE IF EXISTS player_configurations;
DROP TABLE IF EXISTS player_realtime_metrics;
DROP TABLE IF EXISTS analytics_daily_stats;

-- Step 2: Create player_analytics_sessions with ALL columns
CREATE TABLE 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,
    INDEX idx_video_id (video_id),
    INDEX idx_user_id (user_id),
    INDEX idx_start_time (start_time),
    INDEX idx_sessions_active (is_active, last_heartbeat),
    INDEX idx_sessions_video (video_id, is_active),
    INDEX idx_sessions_user (user_id, is_active),
    INDEX idx_sessions_player (player_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Step 3: Create player_analytics_events with ALL columns
CREATE TABLE 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,
    INDEX idx_session_id (session_id),
    INDEX idx_event_type (event_type),
    INDEX idx_event_timestamp (event_timestamp),
    INDEX idx_events_player (player_id),
    FOREIGN KEY (session_id) REFERENCES player_analytics_sessions(session_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Step 4: Create video_player_settings table
CREATE TABLE 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;

-- Step 5: Create player_configurations table
CREATE TABLE 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;

-- Step 6: Create player_realtime_metrics table
CREATE TABLE 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;

-- Step 7: Create analytics_daily_stats table
CREATE TABLE 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;

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

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

-- Final message
SELECT 'Clean migration completed - all tables recreated!' as message;
