-- Migration: Add missing columns for player analytics
-- Run this SQL script to fix the database schema issues

-- Add missing columns to 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 index for active sessions
CREATE INDEX IF NOT EXISTS idx_active_sessions ON player_analytics_sessions(is_active, last_heartbeat);

-- Add missing columns to player_analytics_events table if needed
ALTER TABLE player_analytics_events
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;

-- Create a table for real-time metrics if it doesn't exist
CREATE TABLE IF NOT EXISTS player_realtime_metrics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    video_id INT,
    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 (video_id),
    INDEX idx_last_updated (last_updated)
);

-- Update existing sessions to set total_watch_time_ms from watch_duration
UPDATE player_analytics_sessions 
SET total_watch_time_ms = watch_duration * 1000 
WHERE total_watch_time_ms = 0 AND watch_duration > 0;
