-- ==============================================
-- Migration: Add Missing Tables and Indexes
-- Date: 2026-01-17
-- Description: Fixes critical missing tables and adds performance indexes
-- ==============================================

-- ==============================================
-- 1. MISSING TABLES
-- ==============================================

-- Match Details Cache Tracker (referenced in MatchDetailsRepository.php)
CREATE TABLE IF NOT EXISTS match_details_cache (
    fixture_id INT PRIMARY KEY,
    has_events TINYINT(1) DEFAULT 0,
    events_count INT DEFAULT 0,
    has_statistics TINYINT(1) DEFAULT 0,
    statistics_count INT DEFAULT 0,
    has_lineups TINYINT(1) DEFAULT 0,
    lineups_count INT DEFAULT 0,
    last_fetched_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (fixture_id) REFERENCES fixtures(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Head to Head Table (referenced in MatchDetailsRepository.php)
CREATE TABLE IF NOT EXISTS head_to_head (
    id INT AUTO_INCREMENT PRIMARY KEY,
    team1_id INT NOT NULL,                  -- Lower team ID (sorted for consistency)
    team2_id INT NOT NULL,                  -- Higher team ID
    fixture_id INT NOT NULL,
    match_date DATETIME,
    league_id INT,
    league_name VARCHAR(255),
    league_logo VARCHAR(500),
    home_team_id INT,
    away_team_id INT,
    home_team_name VARCHAR(255),
    away_team_name VARCHAR(255),
    home_team_logo VARCHAR(500),
    away_team_logo VARCHAR(500),
    goals_home INT DEFAULT 0,
    goals_away INT DEFAULT 0,
    status_short VARCHAR(10) DEFAULT 'FT',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY unique_h2h_fixture (team1_id, team2_id, fixture_id),
    INDEX idx_teams (team1_id, team2_id),
    INDEX idx_match_date (match_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- App Settings Table (referenced in DailyFetcher.php and api.php)
CREATE TABLE IF NOT EXISTS app_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    setting_type ENUM('string', 'int', 'bool', 'json') DEFAULT 'string',
    description VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default settings
INSERT IGNORE INTO app_settings (setting_key, setting_value, setting_type, description) VALUES
('fetch_all_leagues', '0', 'bool', 'Fetch all leagues instead of just selected ones'),
('auto_refresh_interval', '15', 'int', 'Auto refresh interval in seconds'),
('timezone', 'Asia/Bangkok', 'string', 'Default timezone for display'),
('odds_bookmaker', 'bet365', 'string', 'Preferred bookmaker for odds'),
('enable_websocket', '1', 'bool', 'Enable WebSocket real-time updates');

-- ==============================================
-- 2. ADD is_selected COLUMN TO LEAGUES TABLE
-- ==============================================
-- Check and add is_selected column if not exists
SET @col_exists = (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'leagues'
    AND COLUMN_NAME = 'is_selected'
);

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE leagues ADD COLUMN is_selected TINYINT(1) DEFAULT 0 AFTER season',
    'SELECT "Column is_selected already exists"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Set default selected leagues (top leagues)
UPDATE leagues SET is_selected = 1 WHERE id IN (39, 140, 135, 78, 61, 2, 3, 1);

-- ==============================================
-- 3. PERFORMANCE INDEXES
-- ==============================================

-- Composite index for live fixture queries (worker.php)
-- DROP INDEX IF EXISTS idx_live_status_elapsed ON fixtures;
CREATE INDEX IF NOT EXISTS idx_live_status_elapsed ON fixtures(is_live, status_short, elapsed);

-- Index for timestamp-based queries (auto-finish logic)
-- Already exists as idx_timestamp, but add composite for better performance
CREATE INDEX IF NOT EXISTS idx_status_timestamp ON fixtures(status_short, timestamp_utc);

-- Index for leagues is_selected filter
CREATE INDEX IF NOT EXISTS idx_leagues_selected ON leagues(is_selected);

-- Composite index for fixture events ordering
CREATE INDEX IF NOT EXISTS idx_events_fixture_time ON fixture_events(fixture_id, time_elapsed, time_extra);

-- ==============================================
-- 4. ADD MISSING COLUMNS TO fixture_lineups
-- ==============================================
-- Add player_photo column if not exists
SET @col_exists = (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'fixture_lineups'
    AND COLUMN_NAME = 'player_photo'
);

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE fixture_lineups ADD COLUMN player_photo VARCHAR(500) AFTER player_grid',
    'SELECT "Column player_photo already exists"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add coach columns if not exist
SET @col_exists = (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'fixture_lineups'
    AND COLUMN_NAME = 'coach_id'
);

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE fixture_lineups ADD COLUMN coach_id INT DEFAULT NULL AFTER is_substitute, ADD COLUMN coach_name VARCHAR(255) DEFAULT NULL AFTER coach_id, ADD COLUMN coach_photo VARCHAR(500) DEFAULT NULL AFTER coach_name',
    'SELECT "Coach columns already exist"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ==============================================
-- 5. ADD bet_choice AND bet_result TO ODDS TABLE
-- ==============================================
SET @col_exists = (
    SELECT COUNT(*)
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'odds'
    AND COLUMN_NAME = 'bet_choice'
);

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE odds ADD COLUMN bet_choice VARCHAR(50) DEFAULT NULL AFTER tip, ADD COLUMN bet_result ENUM(''win'', ''lose'', ''push'', ''pending'') DEFAULT ''pending'' AFTER bet_choice',
    'SELECT "bet_choice and bet_result columns already exist"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ==============================================
-- 6. FIX odds.handicap_value TYPE (VARCHAR to DECIMAL)
-- ==============================================
-- Note: This is a breaking change if data exists. Only run on fresh install.
-- To migrate existing data:
-- ALTER TABLE odds MODIFY COLUMN handicap_value DECIMAL(4,2) DEFAULT NULL;
-- (Commented out for safety - run manually if needed)

-- ==============================================
-- DONE
-- ==============================================
SELECT 'Migration 002 completed successfully' AS status;
