-- SportsPulse Database Schema
-- Production-grade sports live scores platform
-- Created: 2026-03-19

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================
-- CORE ENTITIES
-- ============================================

-- Sports table (football, basketball, cricket, etc.)
CREATE TABLE IF NOT EXISTS `sports` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `icon` VARCHAR(50) DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `sort_order` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_sports_slug` (`slug`),
    KEY `idx_sports_active` (`is_active`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Countries table
CREATE TABLE IF NOT EXISTS `countries` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `code` VARCHAR(10) DEFAULT NULL,
    `flag_url` VARCHAR(500) DEFAULT NULL,
    `local_flag` VARCHAR(255) DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_countries_slug` (`slug`),
    UNIQUE KEY `uk_countries_code` (`code`),
    KEY `idx_countries_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Leagues/Competitions table
CREATE TABLE IF NOT EXISTS `leagues` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `sport_id` INT UNSIGNED NOT NULL,
    `country_id` INT UNSIGNED DEFAULT NULL,
    `name` VARCHAR(200) NOT NULL,
    `slug` VARCHAR(200) NOT NULL,
    `logo_url` VARCHAR(500) DEFAULT NULL,
    `local_logo` VARCHAR(255) DEFAULT NULL,
    `type` ENUM('league', 'cup', 'friendly', 'international') NOT NULL DEFAULT 'league',
    `is_cup` TINYINT(1) NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `priority` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_leagues_slug` (`slug`),
    KEY `idx_leagues_sport` (`sport_id`),
    KEY `idx_leagues_country` (`country_id`),
    KEY `idx_leagues_active_priority` (`is_active`, `priority` DESC),
    CONSTRAINT `fk_leagues_sport` FOREIGN KEY (`sport_id`) REFERENCES `sports` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_leagues_country` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seasons table
CREATE TABLE IF NOT EXISTS `seasons` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `league_id` INT UNSIGNED NOT NULL,
    `year` VARCHAR(20) NOT NULL,
    `start_date` DATE DEFAULT NULL,
    `end_date` DATE DEFAULT NULL,
    `is_current` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_seasons_league_year` (`league_id`, `year`),
    KEY `idx_seasons_current` (`is_current`),
    CONSTRAINT `fk_seasons_league` FOREIGN KEY (`league_id`) REFERENCES `leagues` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Venues/Stadiums table
CREATE TABLE IF NOT EXISTS `venues` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(200) NOT NULL,
    `city` VARCHAR(100) DEFAULT NULL,
    `country_id` INT UNSIGNED DEFAULT NULL,
    `capacity` INT UNSIGNED DEFAULT NULL,
    `address` VARCHAR(500) DEFAULT NULL,
    `image_url` VARCHAR(500) DEFAULT NULL,
    `local_image` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_venues_country` (`country_id`),
    CONSTRAINT `fk_venues_country` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Teams table
CREATE TABLE IF NOT EXISTS `teams` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(200) NOT NULL,
    `slug` VARCHAR(200) NOT NULL,
    `short_name` VARCHAR(50) DEFAULT NULL,
    `code` VARCHAR(10) DEFAULT NULL,
    `logo_url` VARCHAR(500) DEFAULT NULL,
    `local_logo` VARCHAR(255) DEFAULT NULL,
    `country_id` INT UNSIGNED DEFAULT NULL,
    `venue_id` INT UNSIGNED DEFAULT NULL,
    `founded` YEAR DEFAULT NULL,
    `is_national` TINYINT(1) NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_teams_slug` (`slug`),
    KEY `idx_teams_country` (`country_id`),
    KEY `idx_teams_venue` (`venue_id`),
    KEY `idx_teams_active` (`is_active`),
    CONSTRAINT `fk_teams_country` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_teams_venue` FOREIGN KEY (`venue_id`) REFERENCES `venues` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- League-Team relationship (many-to-many)
CREATE TABLE IF NOT EXISTS `league_teams` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `league_id` INT UNSIGNED NOT NULL,
    `team_id` INT UNSIGNED NOT NULL,
    `season_id` INT UNSIGNED DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_league_teams` (`league_id`, `team_id`, `season_id`),
    KEY `idx_league_teams_team` (`team_id`),
    CONSTRAINT `fk_league_teams_league` FOREIGN KEY (`league_id`) REFERENCES `leagues` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_league_teams_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_league_teams_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Players table
CREATE TABLE IF NOT EXISTS `players` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(200) NOT NULL,
    `slug` VARCHAR(200) NOT NULL,
    `firstname` VARCHAR(100) DEFAULT NULL,
    `lastname` VARCHAR(100) DEFAULT NULL,
    `photo_url` VARCHAR(500) DEFAULT NULL,
    `local_photo` VARCHAR(255) DEFAULT NULL,
    `team_id` INT UNSIGNED DEFAULT NULL,
    `nationality_id` INT UNSIGNED DEFAULT NULL,
    `position` VARCHAR(50) DEFAULT NULL,
    `birth_date` DATE DEFAULT NULL,
    `height` VARCHAR(20) DEFAULT NULL,
    `weight` VARCHAR(20) DEFAULT NULL,
    `shirt_number` INT UNSIGNED DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_players_slug` (`slug`),
    KEY `idx_players_team` (`team_id`),
    KEY `idx_players_nationality` (`nationality_id`),
    KEY `idx_players_active` (`is_active`),
    CONSTRAINT `fk_players_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_players_nationality` FOREIGN KEY (`nationality_id`) REFERENCES `countries` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- MATCH DATA
-- ============================================

-- Matches/Fixtures table
CREATE TABLE IF NOT EXISTS `matches` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `league_id` INT UNSIGNED NOT NULL,
    `season_id` INT UNSIGNED DEFAULT NULL,
    `home_team_id` INT UNSIGNED NOT NULL,
    `away_team_id` INT UNSIGNED NOT NULL,
    `venue_id` INT UNSIGNED DEFAULT NULL,
    `slug` VARCHAR(255) NOT NULL,
    `match_date` DATETIME NOT NULL,
    `status` VARCHAR(50) NOT NULL DEFAULT 'NS',
    `status_short` VARCHAR(10) DEFAULT NULL,
    `minute` VARCHAR(10) DEFAULT NULL,
    `home_score` INT DEFAULT NULL,
    `away_score` INT DEFAULT NULL,
    `home_ht_score` INT DEFAULT NULL,
    `away_ht_score` INT DEFAULT NULL,
    `home_ft_score` INT DEFAULT NULL,
    `away_ft_score` INT DEFAULT NULL,
    `home_et_score` INT DEFAULT NULL,
    `away_et_score` INT DEFAULT NULL,
    `home_pen_score` INT DEFAULT NULL,
    `away_pen_score` INT DEFAULT NULL,
    `referee` VARCHAR(100) DEFAULT NULL,
    `round` VARCHAR(100) DEFAULT NULL,
    `is_live` TINYINT(1) NOT NULL DEFAULT 0,
    `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
    `publish_status` ENUM('draft', 'published', 'archived') NOT NULL DEFAULT 'published',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_matches_slug` (`slug`),
    KEY `idx_matches_league` (`league_id`),
    KEY `idx_matches_season` (`season_id`),
    KEY `idx_matches_home_team` (`home_team_id`),
    KEY `idx_matches_away_team` (`away_team_id`),
    KEY `idx_matches_date` (`match_date`),
    KEY `idx_matches_status` (`status`),
    KEY `idx_matches_live` (`is_live`),
    KEY `idx_matches_featured` (`is_featured`),
    KEY `idx_matches_publish` (`publish_status`),
    KEY `idx_matches_date_live` (`match_date`, `is_live`),
    CONSTRAINT `fk_matches_league` FOREIGN KEY (`league_id`) REFERENCES `leagues` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_matches_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_matches_home_team` FOREIGN KEY (`home_team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_matches_away_team` FOREIGN KEY (`away_team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_matches_venue` FOREIGN KEY (`venue_id`) REFERENCES `venues` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Match Events (goals, cards, substitutions)
CREATE TABLE IF NOT EXISTS `match_events` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `match_id` INT UNSIGNED NOT NULL,
    `team_id` INT UNSIGNED DEFAULT NULL,
    `player_id` INT UNSIGNED DEFAULT NULL,
    `assist_player_id` INT UNSIGNED DEFAULT NULL,
    `type` VARCHAR(50) NOT NULL,
    `detail` VARCHAR(100) DEFAULT NULL,
    `minute` INT NOT NULL,
    `extra_minute` INT DEFAULT NULL,
    `comments` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_match_events_match` (`match_id`),
    KEY `idx_match_events_team` (`team_id`),
    KEY `idx_match_events_player` (`player_id`),
    KEY `idx_match_events_type` (`type`),
    CONSTRAINT `fk_match_events_match` FOREIGN KEY (`match_id`) REFERENCES `matches` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_match_events_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_match_events_player` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_match_events_assist` FOREIGN KEY (`assist_player_id`) REFERENCES `players` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Match Statistics
CREATE TABLE IF NOT EXISTS `match_statistics` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `match_id` INT UNSIGNED NOT NULL,
    `team_id` INT UNSIGNED NOT NULL,
    `stat_type` VARCHAR(50) NOT NULL,
    `stat_value` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_match_stats` (`match_id`, `team_id`, `stat_type`),
    KEY `idx_match_stats_match` (`match_id`),
    KEY `idx_match_stats_team` (`team_id`),
    CONSTRAINT `fk_match_stats_match` FOREIGN KEY (`match_id`) REFERENCES `matches` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_match_stats_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Match Lineups
CREATE TABLE IF NOT EXISTS `match_lineups` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `match_id` INT UNSIGNED NOT NULL,
    `team_id` INT UNSIGNED NOT NULL,
    `player_id` INT UNSIGNED NOT NULL,
    `position` VARCHAR(50) DEFAULT NULL,
    `grid` VARCHAR(10) DEFAULT NULL,
    `is_starter` TINYINT(1) NOT NULL DEFAULT 1,
    `shirt_number` INT UNSIGNED DEFAULT NULL,
    `rating` DECIMAL(3,1) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_match_lineups` (`match_id`, `team_id`, `player_id`),
    KEY `idx_match_lineups_match` (`match_id`),
    KEY `idx_match_lineups_team` (`team_id`),
    KEY `idx_match_lineups_player` (`player_id`),
    CONSTRAINT `fk_match_lineups_match` FOREIGN KEY (`match_id`) REFERENCES `matches` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_match_lineups_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_match_lineups_player` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- STANDINGS & STATS
-- ============================================

-- League Standings
CREATE TABLE IF NOT EXISTS `standings` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `league_id` INT UNSIGNED NOT NULL,
    `season_id` INT UNSIGNED NOT NULL,
    `team_id` INT UNSIGNED NOT NULL,
    `group_name` VARCHAR(50) DEFAULT NULL,
    `position` INT NOT NULL,
    `played` INT NOT NULL DEFAULT 0,
    `won` INT NOT NULL DEFAULT 0,
    `drawn` INT NOT NULL DEFAULT 0,
    `lost` INT NOT NULL DEFAULT 0,
    `goals_for` INT NOT NULL DEFAULT 0,
    `goals_against` INT NOT NULL DEFAULT 0,
    `goal_diff` INT NOT NULL DEFAULT 0,
    `points` INT NOT NULL DEFAULT 0,
    `form` VARCHAR(20) DEFAULT NULL,
    `description` VARCHAR(100) DEFAULT NULL,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_standings` (`league_id`, `season_id`, `team_id`, `group_name`),
    KEY `idx_standings_position` (`position`),
    KEY `idx_standings_team` (`team_id`),
    CONSTRAINT `fk_standings_league` FOREIGN KEY (`league_id`) REFERENCES `leagues` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_standings_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_standings_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Top Scorers
CREATE TABLE IF NOT EXISTS `top_scorers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `league_id` INT UNSIGNED NOT NULL,
    `season_id` INT UNSIGNED NOT NULL,
    `player_id` INT UNSIGNED NOT NULL,
    `team_id` INT UNSIGNED DEFAULT NULL,
    `goals` INT NOT NULL DEFAULT 0,
    `assists` INT NOT NULL DEFAULT 0,
    `penalties` INT NOT NULL DEFAULT 0,
    `appearances` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_top_scorers` (`league_id`, `season_id`, `player_id`),
    KEY `idx_top_scorers_goals` (`goals` DESC),
    KEY `idx_top_scorers_team` (`team_id`),
    CONSTRAINT `fk_top_scorers_league` FOREIGN KEY (`league_id`) REFERENCES `leagues` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_top_scorers_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_top_scorers_player` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_top_scorers_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- SYSTEM TABLES
-- ============================================

-- Provider Mappings (critical for multi-API support)
CREATE TABLE IF NOT EXISTS `provider_mappings` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `entity_type` ENUM('sport', 'country', 'league', 'season', 'team', 'player', 'venue', 'match') NOT NULL,
    `internal_id` INT UNSIGNED NOT NULL,
    `provider_name` VARCHAR(50) NOT NULL,
    `provider_entity_id` VARCHAR(100) NOT NULL,
    `extra_data` JSON DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_provider_mappings` (`entity_type`, `internal_id`, `provider_name`),
    KEY `idx_provider_mappings_provider` (`provider_name`, `entity_type`, `provider_entity_id`),
    KEY `idx_provider_mappings_internal` (`entity_type`, `internal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- API Cache
CREATE TABLE IF NOT EXISTS `api_cache` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `provider` VARCHAR(50) NOT NULL,
    `endpoint` VARCHAR(255) NOT NULL,
    `request_hash` VARCHAR(64) NOT NULL,
    `request_params` JSON DEFAULT NULL,
    `response_json` LONGTEXT NOT NULL,
    `status_code` INT NOT NULL,
    `fetched_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at` TIMESTAMP NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_api_cache_hash` (`request_hash`),
    KEY `idx_api_cache_expires` (`expires_at`),
    KEY `idx_api_cache_provider` (`provider`, `endpoint`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Slugs table (URL slugs management)
CREATE TABLE IF NOT EXISTS `slugs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `entity_type` VARCHAR(50) NOT NULL,
    `entity_id` INT UNSIGNED NOT NULL,
    `slug` VARCHAR(255) NOT NULL,
    `is_primary` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_slugs_slug` (`slug`),
    KEY `idx_slugs_entity` (`entity_type`, `entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sitemap URLs
CREATE TABLE IF NOT EXISTS `sitemap_urls` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `entity_type` VARCHAR(50) NOT NULL,
    `entity_id` INT UNSIGNED NOT NULL,
    `url` VARCHAR(500) NOT NULL,
    `priority` DECIMAL(2,1) NOT NULL DEFAULT 0.5,
    `changefreq` ENUM('always', 'hourly', 'daily', 'weekly', 'monthly', 'yearly', 'never') NOT NULL DEFAULT 'daily',
    `lastmod` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `is_indexable` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_sitemap_urls` (`entity_type`, `entity_id`),
    KEY `idx_sitemap_urls_indexable` (`is_indexable`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Search Index
CREATE TABLE IF NOT EXISTS `search_index` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `entity_type` VARCHAR(50) NOT NULL,
    `entity_id` INT UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `keywords` TEXT DEFAULT NULL,
    `search_text` TEXT NOT NULL,
    `url` VARCHAR(500) NOT NULL,
    `image_url` VARCHAR(500) DEFAULT NULL,
    `popularity` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_search_index` (`entity_type`, `entity_id`),
    FULLTEXT KEY `ft_search_index` (`title`, `keywords`, `search_text`),
    KEY `idx_search_index_popularity` (`popularity` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Assets (images, logos)
CREATE TABLE IF NOT EXISTS `assets` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `entity_type` VARCHAR(50) NOT NULL,
    `entity_id` INT UNSIGNED NOT NULL,
    `asset_type` VARCHAR(50) NOT NULL,
    `remote_url` VARCHAR(500) NOT NULL,
    `local_path` VARCHAR(255) DEFAULT NULL,
    `status` ENUM('pending', 'downloaded', 'failed', 'missing') NOT NULL DEFAULT 'pending',
    `retry_count` INT NOT NULL DEFAULT 0,
    `last_retry` TIMESTAMP NULL DEFAULT NULL,
    `error_message` VARCHAR(500) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_assets` (`entity_type`, `entity_id`, `asset_type`),
    KEY `idx_assets_status` (`status`),
    KEY `idx_assets_pending` (`status`, `retry_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sync Logs
CREATE TABLE IF NOT EXISTS `sync_logs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `sync_type` VARCHAR(50) NOT NULL,
    `provider` VARCHAR(50) NOT NULL,
    `status` ENUM('running', 'completed', 'failed', 'partial') NOT NULL DEFAULT 'running',
    `records_processed` INT NOT NULL DEFAULT 0,
    `records_created` INT NOT NULL DEFAULT 0,
    `records_updated` INT NOT NULL DEFAULT 0,
    `errors_count` INT NOT NULL DEFAULT 0,
    `error_message` TEXT DEFAULT NULL,
    `extra_data` JSON DEFAULT NULL,
    `started_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `completed_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_sync_logs_type` (`sync_type`, `status`),
    KEY `idx_sync_logs_started` (`started_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Favorites (user session-based)
CREATE TABLE IF NOT EXISTS `favorites` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `session_id` VARCHAR(64) NOT NULL,
    `entity_type` VARCHAR(50) NOT NULL,
    `entity_id` INT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_favorites` (`session_id`, `entity_type`, `entity_id`),
    KEY `idx_favorites_session` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Page Meta Cache
CREATE TABLE IF NOT EXISTS `page_meta_cache` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `page_type` VARCHAR(50) NOT NULL,
    `entity_id` INT UNSIGNED NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `h1` VARCHAR(255) NOT NULL,
    `meta_description` TEXT NOT NULL,
    `schema_json` JSON DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_page_meta_cache` (`page_type`, `entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Head to Head records
CREATE TABLE IF NOT EXISTS `head_to_head` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `team1_id` INT UNSIGNED NOT NULL,
    `team2_id` INT UNSIGNED NOT NULL,
    `total_matches` INT NOT NULL DEFAULT 0,
    `team1_wins` INT NOT NULL DEFAULT 0,
    `team2_wins` INT NOT NULL DEFAULT 0,
    `draws` INT NOT NULL DEFAULT 0,
    `team1_goals` INT NOT NULL DEFAULT 0,
    `team2_goals` INT NOT NULL DEFAULT 0,
    `last_match_id` INT UNSIGNED DEFAULT NULL,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_h2h_teams` (`team1_id`, `team2_id`),
    CONSTRAINT `fk_h2h_team1` FOREIGN KEY (`team1_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_h2h_team2` FOREIGN KEY (`team2_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_h2h_last_match` FOREIGN KEY (`last_match_id`) REFERENCES `matches` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;