-- ============================================================================
-- Add League Popularity Column and Data
-- Date: 2025-12-31 (Updated: 2026-01-22)
-- Description: Adds popularity column to soccer_leagues and sets values
--              based on real-world league rankings
-- SAFE FOR LIVE: Can be run multiple times without errors
-- ============================================================================

-- Step 1: Add popularity column (SAFE - will skip if exists)
ALTER TABLE `soccer_leagues` 
ADD COLUMN IF NOT EXISTS `popularity` INT(11) NOT NULL DEFAULT 0 AFTER `country`;

-- Step 1b: Add index (SAFE - will skip if exists)
CREATE INDEX IF NOT EXISTS `idx_popularity` ON `soccer_leagues` (`popularity`);

-- Step 2: Set popularity values for major leagues (Exact Match by Name + Country)

-- Top European Leagues (95-100)
UPDATE `soccer_leagues` SET `popularity` = 100 WHERE `name` = 'Premier League' AND `country` = 'England';
UPDATE `soccer_leagues` SET `popularity` = 97 WHERE `name` = 'La Liga' AND `country` = 'Spain';
UPDATE `soccer_leagues` SET `popularity` = 94 WHERE `name` = 'Serie A' AND `country` = 'Italy';
UPDATE `soccer_leagues` SET `popularity` = 94 WHERE `name` = 'Bundesliga' AND `country` = 'Germany';
UPDATE `soccer_leagues` SET `popularity` = 91 WHERE `name` = 'Ligue 1' AND `country` = 'France';

-- Major European Competitions (95-100) - TOP PRIORITY alongside Premier League
UPDATE `soccer_leagues` SET `popularity` = 100 WHERE `name` = 'UEFA Champions League' AND `country` = 'World';
UPDATE `soccer_leagues` SET `popularity` = 100 WHERE `name` = 'UEFA Europa League' AND `country` = 'World';
UPDATE `soccer_leagues` SET `popularity` = 95 WHERE `name` = 'UEFA Europa Conference League' AND `country` = 'World';

-- Strong European Leagues (72-85)
UPDATE `soccer_leagues` SET `popularity` = 85 WHERE `name` = 'Primeira Liga' AND `country` = 'Portugal';
UPDATE `soccer_leagues` SET `popularity` = 83 WHERE `name` = 'Eredivisie' AND `country` = 'Netherlands';
UPDATE `soccer_leagues` SET `popularity` = 75 WHERE `name` = 'Belgian Pro League' AND `country` = 'Belgium';
UPDATE `soccer_leagues` SET `popularity` = 78 WHERE `name` = 'Championship' AND `country` = 'England';
UPDATE `soccer_leagues` SET `popularity` = 72 WHERE `name` = 'Scottish Premiership' AND `country` = 'Scotland';

-- South American Competitions & Leagues (75-90)
UPDATE `soccer_leagues` SET `popularity` = 90 WHERE `name` = 'Copa Libertadores' AND `country` = 'World';
UPDATE `soccer_leagues` SET `popularity` = 82 WHERE `name` = 'Brasileirão Série A' AND `country` = 'Brazil';
UPDATE `soccer_leagues` SET `popularity` = 75 WHERE `name` = 'Copa Sudamericana' AND `country` = 'World';

-- Americas (70-76)
UPDATE `soccer_leagues` SET `popularity` = 76 WHERE `name` = 'Liga MX' AND `country` = 'Mexico';
UPDATE `soccer_leagues` SET `popularity` = 70 WHERE `name` = 'MLS' AND `country` = 'USA';

-- Asia & Middle East (58-72)
UPDATE `soccer_leagues` SET `popularity` = 72 WHERE `name` = 'Saudi Pro League' AND `country` = 'Saudi-Arabia';
UPDATE `soccer_leagues` SET `popularity` = 68 WHERE `name` = 'Super Lig' AND `country` = 'Turkey';
UPDATE `soccer_leagues` SET `popularity` = 62 WHERE `name` = 'J1 League' AND `country` = 'Japan';
UPDATE `soccer_leagues` SET `popularity` = 58 WHERE `name` = 'K League 1' AND `country` = 'South-Korea';

-- Regional Leagues (38-52)
UPDATE `soccer_leagues` SET `popularity` = 52 WHERE `name` = 'A-League' AND `country` = 'Australia';
UPDATE `soccer_leagues` SET `popularity` = 48 WHERE `name` = 'Ligat Ha\'al' AND `country` = 'Israel';
UPDATE `soccer_leagues` SET `popularity` = 38 WHERE `name` = '1. Division' AND `country` = 'Cyprus';
UPDATE `soccer_leagues` SET `popularity` = 44 WHERE `name` = 'Ligue 1' AND `country` = 'Algeria';

-- Step 3: Pattern matching for additional leagues (only updates if popularity is still 0)

-- European Second Tiers
UPDATE `soccer_leagues` SET `popularity` = 68 WHERE `name` LIKE '%Segunda División%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 62 WHERE `name` LIKE '%Serie B%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 65 WHERE `name` LIKE '%2. Bundesliga%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 58 WHERE `name` LIKE '%Ligue 2%' AND `popularity` = 0;

-- Other European Leagues
UPDATE `soccer_leagues` SET `popularity` = 62 WHERE `name` LIKE '%Austrian Bundesliga%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 60 WHERE `name` LIKE '%Swiss Super League%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 60 WHERE `name` LIKE '%Greek Super League%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 58 WHERE `name` LIKE '%Czech First League%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 58 WHERE `name` LIKE '%Danish Superliga%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 56 WHERE `name` LIKE '%Norwegian Eliteserien%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 56 WHERE `name` LIKE '%Swedish Allsvenskan%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 55 WHERE `name` LIKE '%Ekstraklasa%' AND `popularity` = 0;

-- South America
UPDATE `soccer_leagues` SET `popularity` = 80 WHERE `name` LIKE '%Liga Profesional Argentina%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 68 WHERE `name` LIKE '%Primera A%' AND `country` = 'Colombia' AND `popularity` = 0;

-- Asia
UPDATE `soccer_leagues` SET `popularity` = 56 WHERE `name` LIKE '%Chinese Super League%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 54 WHERE `name` LIKE '%ISL%' AND `country` = 'India' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 52 WHERE `name` LIKE '%UAE Pro League%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 50 WHERE `name` LIKE '%Qatar Stars League%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 48 WHERE `name` LIKE '%Thai League%' AND `popularity` = 0;

-- Africa
UPDATE `soccer_leagues` SET `popularity` = 50 WHERE `name` LIKE '%Premier Division%' AND `country` = 'South Africa' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 48 WHERE `name` LIKE '%Egyptian Premier League%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 46 WHERE `name` LIKE '%Botola%' AND `country` = 'Morocco' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 42 WHERE `name` LIKE '%Nigerian%' AND `popularity` = 0;

-- Eastern Europe
UPDATE `soccer_leagues` SET `popularity` = 65 WHERE `name` LIKE '%Russian Premier League%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 63 WHERE `name` LIKE '%Ukrainian Premier League%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 52 WHERE `name` LIKE '%Croatian%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 50 WHERE `name` LIKE '%Serbian%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 48 WHERE `name` LIKE '%Romanian Liga%' AND `popularity` = 0;
UPDATE `soccer_leagues` SET `popularity` = 45 WHERE `name` LIKE '%Bulgarian%' AND `popularity` = 0;

-- Step 4: Verify the changes
SELECT 
    `name`, 
    `country`, 
    `popularity`
FROM `soccer_leagues`
WHERE `popularity` > 0
ORDER BY `popularity` DESC, `name` ASC
LIMIT 20;

-- ============================================================================
-- ROLLBACK (if needed)
-- ============================================================================
-- To remove the popularity column and index:
-- DROP INDEX `idx_popularity` ON `soccer_leagues`;
-- ALTER TABLE `soccer_leagues` DROP COLUMN `popularity`;
-- ============================================================================
