-- ==============================================
-- Migration: Add Performance Indexes
-- Date: 2026-01-17
-- Description: Add composite indexes to improve query performance
-- ==============================================

-- Composite index for date range queries with status filter
-- Used by: getFixturesByDate, shouldRefreshFixtures
ALTER TABLE fixtures
ADD INDEX idx_match_date_status (match_date, status_short);

-- Composite index for live fixtures with date
-- Used by: getLiveFixtures, filtering today's live matches
ALTER TABLE fixtures
ADD INDEX idx_live_date (is_live, match_date);

-- Composite index for stale fixture detection
-- Used by: shouldRefreshFixtures (finding NS fixtures that should have started)
ALTER TABLE fixtures
ADD INDEX idx_status_date (status_short, match_date);

-- Update the view to use optimized query
DROP VIEW IF EXISTS v_today_fixtures;
CREATE VIEW v_today_fixtures AS
SELECT
    f.id,
    f.match_date,
    f.status_short,
    f.status_long,
    f.elapsed,
    f.goals_home,
    f.goals_away,
    f.is_live,
    l.id AS league_id,
    l.name AS league_name,
    l.logo AS league_logo,
    l.country AS league_country,
    l.country_flag,
    ht.id AS home_team_id,
    ht.name AS home_team_name,
    ht.logo AS home_team_logo,
    at.id AS away_team_id,
    at.name AS away_team_name,
    at.logo AS away_team_logo,
    o.home_odd,
    o.draw_odd,
    o.away_odd
FROM fixtures f
JOIN leagues l ON f.league_id = l.id
JOIN teams ht ON f.home_team_id = ht.id
JOIN teams at ON f.away_team_id = at.id
LEFT JOIN odds o ON f.id = o.fixture_id
WHERE f.match_date BETWEEN CONCAT(CURDATE(), ' 00:00:00') AND CONCAT(CURDATE(), ' 23:59:59')
ORDER BY f.is_live DESC, l.country, l.name, f.match_date;

-- Add table for tracking refresh timestamps (to avoid too frequent refreshes)
CREATE TABLE IF NOT EXISTS api_refresh_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    refresh_type VARCHAR(50) NOT NULL,
    target_date DATE NOT NULL,
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    fixtures_count INT DEFAULT 0,
    status ENUM('running', 'completed', 'failed') DEFAULT 'running',

    UNIQUE KEY unique_refresh (refresh_type, target_date, started_at),
    INDEX idx_type_date (refresh_type, target_date),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
