-- ==============================================
-- Migration: Add League Settings Table
-- Purpose: Store selected leagues for data fetching
-- ==============================================

-- Add is_selected column to leagues table to track which leagues to fetch
ALTER TABLE leagues
ADD COLUMN is_selected TINYINT(1) DEFAULT 0 AFTER is_active,
ADD INDEX idx_selected (is_selected);

-- Set default selected leagues (popular leagues)
UPDATE leagues SET is_selected = 1 WHERE id IN (
    39,   -- Premier League
    140,  -- La Liga
    135,  -- Serie A
    78,   -- Bundesliga
    61,   -- Ligue 1
    2,    -- Champions League
    3,    -- Europa League
    1,    -- World Cup
    848,  -- Conference League
    4,    -- Euro Championship
    9,    -- Copa America
    15,   -- FIFA Club World Cup
    45,   -- FA Cup
    48,   -- League Cup (EFL Cup)
    556,  -- Super Cup (England)
    143,  -- Copa del Rey
    137,  -- Coppa Italia
    81,   -- DFB Pokal
    66,   -- Coupe de France
    529,  -- Super Cup (Germany)
    547,  -- Super Cup (Italy)
    556   -- Community Shield
);

-- Create settings table for general app settings
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(255),
    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 INTO app_settings (setting_key, setting_value, setting_type, description) VALUES
('fetch_all_leagues', '0', 'bool', 'Fetch all leagues or only selected ones'),
('auto_refresh_interval', '15', 'int', 'Auto refresh interval in seconds'),
('max_api_calls_per_day', '7500', 'int', 'Maximum API calls per day')
ON DUPLICATE KEY UPDATE setting_key = setting_key;
