-- ============================================================================ -- Sophia Core Database Schema -- Engine: InnoDB | Charset: utf8mb4 | Collation: utf8mb4_unicode_ci -- ============================================================================ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------------------------------------------------------- -- 1. users -- ---------------------------------------------------------------------------- CREATE TABLE `users` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `username` VARCHAR(100) NOT NULL, `email` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL, `display_name` VARCHAR(100) DEFAULT NULL, `role` ENUM('user','admin') NOT NULL DEFAULT 'user', `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 `uq_users_username` (`username`), UNIQUE KEY `uq_users_email` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 2. admin_users -- ---------------------------------------------------------------------------- CREATE TABLE `admin_users` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `username` VARCHAR(100) NOT NULL, `email` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL, `display_name` VARCHAR(100) DEFAULT NULL, `role` ENUM('super_admin','admin','editor') NOT NULL DEFAULT 'admin', `is_active` TINYINT(1) NOT NULL DEFAULT 1, `last_login_at` TIMESTAMP NULL 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 `uq_admin_users_username` (`username`), UNIQUE KEY `uq_admin_users_email` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 3. roles -- ---------------------------------------------------------------------------- CREATE TABLE `roles` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(50) NOT NULL, `description` TEXT DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uq_roles_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 4. permissions -- ---------------------------------------------------------------------------- CREATE TABLE `permissions` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `role_id` INT UNSIGNED NOT NULL, `permission` VARCHAR(100) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_permissions_role_id` (`role_id`), CONSTRAINT `fk_permissions_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 5. countries -- ---------------------------------------------------------------------------- CREATE TABLE `countries` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `code` VARCHAR(3) NOT NULL, `flag_url` 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`), UNIQUE KEY `uq_countries_code` (`code`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 6. competitions -- ---------------------------------------------------------------------------- CREATE TABLE `competitions` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `provider` VARCHAR(50) NOT NULL, `external_id` VARCHAR(100) NOT NULL, `name` VARCHAR(255) NOT NULL, `type` ENUM('league','cup') NOT NULL, `country_id` INT UNSIGNED DEFAULT NULL, `logo_url` VARCHAR(255) DEFAULT NULL, `season` VARCHAR(255) DEFAULT NULL, `season_id` INT UNSIGNED DEFAULT NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `is_popular` TINYINT(1) NOT NULL DEFAULT 0, `source_provider` VARCHAR(50) DEFAULT NULL, `source_updated_at` TIMESTAMP NULL 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_competitions_country_id` (`country_id`), KEY `idx_competitions_season_id` (`season_id`), CONSTRAINT `fk_competitions_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_competitions_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 7. seasons -- ---------------------------------------------------------------------------- CREATE TABLE `seasons` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `competition_id` INT UNSIGNED NOT NULL, `name` VARCHAR(100) NOT NULL, `year_start` YEAR DEFAULT NULL, `year_end` YEAR 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`), KEY `idx_seasons_competition_id` (`competition_id`), CONSTRAINT `fk_seasons_competition_id` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 8. venues -- ---------------------------------------------------------------------------- CREATE TABLE `venues` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) NOT NULL, `city` VARCHAR(255) DEFAULT NULL, `country` VARCHAR(255) DEFAULT NULL, `capacity` INT DEFAULT NULL, `image_url` 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 9. teams -- ---------------------------------------------------------------------------- CREATE TABLE `teams` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `provider` VARCHAR(50) NOT NULL, `external_id` VARCHAR(100) NOT NULL, `name` VARCHAR(255) NOT NULL, `short_name` VARCHAR(50) DEFAULT NULL, `country_id` INT UNSIGNED DEFAULT NULL, `logo_url` VARCHAR(255) DEFAULT NULL, `venue_id` INT UNSIGNED DEFAULT NULL, `founded_year` YEAR DEFAULT NULL, `source_provider` VARCHAR(50) DEFAULT NULL, `source_updated_at` TIMESTAMP NULL 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_teams_country_id` (`country_id`), KEY `idx_teams_venue_id` (`venue_id`), CONSTRAINT `fk_teams_country_id` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_teams_venue_id` FOREIGN KEY (`venue_id`) REFERENCES `venues` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 10. players -- ---------------------------------------------------------------------------- CREATE TABLE `players` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `provider` VARCHAR(50) NOT NULL, `external_id` VARCHAR(100) NOT NULL, `name` VARCHAR(255) NOT NULL, `position` VARCHAR(50) DEFAULT NULL, `nationality` VARCHAR(255) DEFAULT NULL, `date_of_birth` DATE DEFAULT NULL, `shirt_number` INT DEFAULT NULL, `photo_url` VARCHAR(255) DEFAULT NULL, `source_provider` VARCHAR(50) DEFAULT NULL, `source_updated_at` TIMESTAMP NULL 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 11. team_players -- ---------------------------------------------------------------------------- CREATE TABLE `team_players` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `team_id` INT UNSIGNED NOT NULL, `player_id` INT UNSIGNED NOT NULL, `season_id` INT UNSIGNED NOT NULL, `jersey_number` INT DEFAULT NULL, `position` VARCHAR(50) 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 `uq_team_players_season` (`team_id`, `player_id`, `season_id`), KEY `idx_team_players_player_id` (`player_id`), KEY `idx_team_players_season_id` (`season_id`), CONSTRAINT `fk_team_players_team_id` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_team_players_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_team_players_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 12. fixtures -- ---------------------------------------------------------------------------- CREATE TABLE `fixtures` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `provider` VARCHAR(50) NOT NULL, `external_id` VARCHAR(100) NOT NULL, `competition_id` INT UNSIGNED NOT NULL, `season_id` INT UNSIGNED NOT NULL, `home_team_id` INT UNSIGNED NOT NULL, `away_team_id` INT UNSIGNED NOT NULL, `venue_id` INT UNSIGNED DEFAULT NULL, `kickoff_at` DATETIME DEFAULT NULL, `status` ENUM('scheduled','live','paused','finished','cancelled','postponed') NOT NULL DEFAULT 'scheduled', `minute` INT NOT NULL DEFAULT 0, `home_score` TINYINT DEFAULT NULL, `away_score` TINYINT DEFAULT NULL, `home_halftime_score` TINYINT DEFAULT NULL, `away_halftime_score` TINYINT DEFAULT NULL, `round_name` VARCHAR(100) DEFAULT NULL, `source_updated_at` TIMESTAMP NULL 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 `uq_fixtures_provider_external` (`provider`, `external_id`), KEY `idx_fixtures_competition_id` (`competition_id`), KEY `idx_fixtures_season_id` (`season_id`), KEY `idx_fixtures_home_team_id` (`home_team_id`), KEY `idx_fixtures_away_team_id` (`away_team_id`), KEY `idx_fixtures_kickoff_at` (`kickoff_at`), KEY `idx_fixtures_status` (`status`), KEY `idx_fixtures_venue_id` (`venue_id`), CONSTRAINT `fk_fixtures_competition_id` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_fixtures_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_fixtures_home_team_id` FOREIGN KEY (`home_team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_fixtures_away_team_id` FOREIGN KEY (`away_team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_fixtures_venue_id` FOREIGN KEY (`venue_id`) REFERENCES `venues` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 13. fixture_events -- ---------------------------------------------------------------------------- CREATE TABLE `fixture_events` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `fixture_id` INT UNSIGNED NOT NULL, `team_id` INT UNSIGNED NOT NULL, `player_id` INT UNSIGNED DEFAULT NULL, `event_type` ENUM('goal','card','substitution','var','penalty') NOT NULL, `event_minute` TINYINT NOT NULL, `additional_minute` TINYINT DEFAULT NULL, `detail` VARCHAR(255) DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_fixture_events_fixture_id` (`fixture_id`), KEY `idx_fixture_events_team_id` (`team_id`), KEY `idx_fixture_events_player_id` (`player_id`), CONSTRAINT `fk_fixture_events_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_fixture_events_team_id` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_fixture_events_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 14. fixture_statistics -- ---------------------------------------------------------------------------- CREATE TABLE `fixture_statistics` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `fixture_id` INT UNSIGNED NOT NULL, `team_id` INT UNSIGNED NOT NULL, `stat_key` VARCHAR(50) NOT NULL, `stat_value` VARCHAR(50) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_fixture_statistics_fixture_id` (`fixture_id`), KEY `idx_fixture_statistics_team_id` (`team_id`), CONSTRAINT `fk_fixture_statistics_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_fixture_statistics_team_id` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 15. fixture_lineups -- ---------------------------------------------------------------------------- CREATE TABLE `fixture_lineups` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `fixture_id` INT UNSIGNED NOT NULL, `team_id` INT UNSIGNED NOT NULL, `player_id` INT UNSIGNED NOT NULL, `formation` VARCHAR(20) DEFAULT NULL, `position_x` DECIMAL(5,2) DEFAULT NULL, `position_y` DECIMAL(5,2) DEFAULT NULL, `is_substitute` TINYINT(1) NOT NULL DEFAULT 0, `is_captain` TINYINT(1) NOT NULL DEFAULT 0, `jersey_number` INT DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_fixture_lineups_fixture_id` (`fixture_id`), KEY `idx_fixture_lineups_team_id` (`team_id`), KEY `idx_fixture_lineups_player_id` (`player_id`), CONSTRAINT `fk_fixture_lineups_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_fixture_lineups_team_id` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_fixture_lineups_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 16. fixture_injuries -- ---------------------------------------------------------------------------- CREATE TABLE `fixture_injuries` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `fixture_id` INT UNSIGNED NOT NULL, `team_id` INT UNSIGNED NOT NULL, `player_id` INT UNSIGNED NOT NULL, `injury_type` VARCHAR(100) DEFAULT NULL, `injury_detail` TEXT DEFAULT NULL, `status` ENUM('doubtful','out','missing') NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_fixture_injuries_fixture_id` (`fixture_id`), KEY `idx_fixture_injuries_team_id` (`team_id`), KEY `idx_fixture_injuries_player_id` (`player_id`), CONSTRAINT `fk_fixture_injuries_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_fixture_injuries_team_id` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_fixture_injuries_player_id` FOREIGN KEY (`player_id`) REFERENCES `players` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 17. standings -- ---------------------------------------------------------------------------- CREATE TABLE `standings` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `competition_id` INT UNSIGNED NOT NULL, `season_id` INT UNSIGNED NOT NULL, `team_id` INT UNSIGNED NOT NULL, `position` INT NOT NULL, `played` INT NOT NULL, `won` INT NOT NULL, `drawn` INT NOT NULL, `lost` INT NOT NULL, `goals_for` INT NOT NULL, `goals_against` INT NOT NULL, `goal_difference` INT NOT NULL, `points` INT NOT NULL, `form` VARCHAR(10) DEFAULT NULL, `source_updated_at` TIMESTAMP NULL 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 `uq_standings_comp_season_team` (`competition_id`, `season_id`, `team_id`), KEY `idx_standings_season_id` (`season_id`), KEY `idx_standings_team_id` (`team_id`), CONSTRAINT `fk_standings_competition_id` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_standings_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_standings_team_id` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 18. team_form_snapshots -- ---------------------------------------------------------------------------- CREATE TABLE `team_form_snapshots` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `team_id` INT UNSIGNED NOT NULL, `competition_id` INT UNSIGNED NOT NULL, `season_id` INT UNSIGNED NOT NULL, `form_type` VARCHAR(20) NOT NULL DEFAULT 'last_5', `matches_played` INT NOT NULL, `won` INT NOT NULL, `drawn` INT NOT NULL, `lost` INT NOT NULL, `goals_for` INT NOT NULL, `goals_against` INT NOT NULL, `clean_sheets` INT NOT NULL, `btts_count` INT NOT NULL, `avg_goals_scored` DECIMAL(5,2) NOT NULL, `avg_goals_conceded` DECIMAL(5,2) NOT NULL, `snapshot_data` JSON DEFAULT NULL, `calculated_at` TIMESTAMP NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_team_form_snapshots_team_id` (`team_id`), KEY `idx_team_form_snapshots_competition_id` (`competition_id`), KEY `idx_team_form_snapshots_season_id` (`season_id`), CONSTRAINT `fk_team_form_snapshots_team_id` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_team_form_snapshots_competition_id` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_team_form_snapshots_season_id` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 19. head_to_head_records -- ---------------------------------------------------------------------------- CREATE TABLE `head_to_head_records` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `home_team_id` INT UNSIGNED NOT NULL, `away_team_id` INT UNSIGNED NOT NULL, `fixture_id` INT UNSIGNED NOT NULL, `home_score` TINYINT DEFAULT NULL, `away_score` TINYINT DEFAULT NULL, `played_at` DATETIME DEFAULT NULL, `competition_id` INT UNSIGNED NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_h2h_home_team_id` (`home_team_id`), KEY `idx_h2h_away_team_id` (`away_team_id`), KEY `idx_h2h_fixture_id` (`fixture_id`), KEY `idx_h2h_competition_id` (`competition_id`), CONSTRAINT `fk_h2h_home_team_id` FOREIGN KEY (`home_team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_h2h_away_team_id` FOREIGN KEY (`away_team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_h2h_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_h2h_competition_id` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 20. elo_ratings -- ---------------------------------------------------------------------------- CREATE TABLE `elo_ratings` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `team_id` INT UNSIGNED NOT NULL, `competition_id` INT UNSIGNED NOT NULL, `rating` DECIMAL(10,2) NOT NULL DEFAULT 1500, `matches_played` INT NOT NULL DEFAULT 0, `last_updated` TIMESTAMP NOT 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 `uq_elo_ratings_team_competition` (`team_id`, `competition_id`), KEY `idx_elo_ratings_competition_id` (`competition_id`), CONSTRAINT `fk_elo_ratings_team_id` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `fk_elo_ratings_competition_id` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 21. prediction_features -- ---------------------------------------------------------------------------- CREATE TABLE `prediction_features` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `fixture_id` INT UNSIGNED NOT NULL, `home_elo` DECIMAL(10,2) DEFAULT NULL, `away_elo` DECIMAL(10,2) DEFAULT NULL, `home_form_avg` DECIMAL(5,2) DEFAULT NULL, `away_form_avg` DECIMAL(5,2) DEFAULT NULL, `home_attack_strength` DECIMAL(5,2) DEFAULT NULL, `away_attack_strength` DECIMAL(5,2) DEFAULT NULL, `home_defense_strength` DECIMAL(5,2) DEFAULT NULL, `away_defense_strength` DECIMAL(5,2) DEFAULT NULL, `home_h2h_advantage` DECIMAL(5,2) DEFAULT NULL, `home_rest_days` INT DEFAULT NULL, `away_rest_days` INT DEFAULT NULL, `missing_key_players_home` INT DEFAULT NULL, `missing_key_players_away` INT DEFAULT NULL, `feature_version` VARCHAR(50) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uq_prediction_features_fixture_version` (`fixture_id`, `feature_version`), CONSTRAINT `fk_prediction_features_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 22. predictions -- ---------------------------------------------------------------------------- CREATE TABLE `predictions` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `fixture_id` INT UNSIGNED NOT NULL, `model_version` VARCHAR(50) NOT NULL, `home_win_probability` DECIMAL(5,2) DEFAULT NULL, `draw_probability` DECIMAL(5,2) DEFAULT NULL, `away_win_probability` DECIMAL(5,2) DEFAULT NULL, `predicted_home_goals` DECIMAL(4,2) DEFAULT NULL, `predicted_away_goals` DECIMAL(4,2) DEFAULT NULL, `btts_probability` DECIMAL(5,2) DEFAULT NULL, `over_2_5_probability` DECIMAL(5,2) DEFAULT NULL, `under_2_5_probability` DECIMAL(5,2) DEFAULT NULL, `confidence` DECIMAL(5,2) DEFAULT NULL, `data_quality` ENUM('high','medium','low','insufficient_data') DEFAULT NULL, `summary` TEXT DEFAULT NULL, `key_factors_json` JSON DEFAULT NULL, `risks_json` JSON DEFAULT NULL, `input_snapshot_json` JSON DEFAULT NULL, `raw_ai_response_json` JSON DEFAULT NULL, `generated_at` TIMESTAMP NOT NULL, `expires_at` TIMESTAMP NULL DEFAULT NULL, `status` ENUM('active','expired','superseded') NOT NULL DEFAULT 'active', `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 `uq_predictions_fixture_model` (`fixture_id`, `model_version`), KEY `idx_predictions_status` (`status`), KEY `idx_predictions_generated_at` (`generated_at`), CONSTRAINT `fk_predictions_fixture_id` FOREIGN KEY (`fixture_id`) REFERENCES `fixtures` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 23. prediction_score_probabilities -- ---------------------------------------------------------------------------- CREATE TABLE `prediction_score_probabilities` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `prediction_id` INT UNSIGNED NOT NULL, `score` VARCHAR(20) NOT NULL, `probability` DECIMAL(5,2) NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_prediction_score_probabilities_prediction_id` (`prediction_id`), CONSTRAINT `fk_prediction_score_probabilities_prediction_id` FOREIGN KEY (`prediction_id`) REFERENCES `predictions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 24. prediction_audits -- ---------------------------------------------------------------------------- CREATE TABLE `prediction_audits` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `prediction_id` INT UNSIGNED NOT NULL, `original_home_win` DECIMAL(5,2) DEFAULT NULL, `original_draw` DECIMAL(5,2) DEFAULT NULL, `original_away_win` DECIMAL(5,2) DEFAULT NULL, `adjusted_home_win` DECIMAL(5,2) DEFAULT NULL, `adjusted_draw` DECIMAL(5,2) DEFAULT NULL, `adjusted_away_win` DECIMAL(5,2) DEFAULT NULL, `ai_adjustment_percent` DECIMAL(5,2) DEFAULT NULL, `validation_status` ENUM('passed','adjusted','rejected') NOT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_prediction_audits_prediction_id` (`prediction_id`), CONSTRAINT `fk_prediction_audits_prediction_id` FOREIGN KEY (`prediction_id`) REFERENCES `predictions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 25. ai_requests -- ---------------------------------------------------------------------------- CREATE TABLE `ai_requests` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `endpoint` VARCHAR(255) NOT NULL, `prompt_hash` VARCHAR(64) NOT NULL, `request_body` JSON NOT NULL, `response_body` JSON DEFAULT NULL, `http_status` INT DEFAULT NULL, `duration_ms` INT NOT NULL, `model_used` VARCHAR(100) DEFAULT NULL, `tokens_input` INT NOT NULL, `tokens_output` INT NOT NULL, `success` TINYINT(1) NOT NULL, `error_message` TEXT DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_ai_requests_success` (`success`), KEY `idx_ai_requests_created_at` (`created_at`), KEY `idx_ai_requests_model_used` (`model_used`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 26. api_providers -- ---------------------------------------------------------------------------- CREATE TABLE `api_providers` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL, `type` ENUM('football','ai') NOT NULL, `base_url` VARCHAR(255) NOT NULL, `api_key_encrypted` VARCHAR(255) DEFAULT NULL, `is_active` TINYINT(1) NOT NULL DEFAULT 1, `is_primary` TINYINT(1) NOT NULL DEFAULT 0, `priority` INT NOT NULL DEFAULT 0, `rate_limit_per_minute` INT NOT NULL DEFAULT 60, `circuit_breaker_open` TINYINT(1) NOT NULL DEFAULT 0, `circuit_breaker_opens_at` TIMESTAMP NULL DEFAULT NULL, `last_success_at` TIMESTAMP NULL DEFAULT NULL, `last_error_at` TIMESTAMP NULL DEFAULT NULL, `consecutive_failures` 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 27. api_sync_logs -- ---------------------------------------------------------------------------- CREATE TABLE `api_sync_logs` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `provider` VARCHAR(255) NOT NULL, `sync_type` VARCHAR(50) NOT NULL, `status` ENUM('running','success','failed','partial') NOT NULL, `items_processed` INT NOT NULL DEFAULT 0, `items_failed` INT NOT NULL DEFAULT 0, `errors_json` JSON DEFAULT NULL, `duration_ms` INT NOT NULL, `started_at` TIMESTAMP NOT NULL, `finished_at` TIMESTAMP NULL DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_api_sync_logs_status` (`status`), KEY `idx_api_sync_logs_started_at` (`started_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 28. cron_locks -- ---------------------------------------------------------------------------- CREATE TABLE `cron_locks` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `job_name` VARCHAR(100) NOT NULL, `locked_at` TIMESTAMP NOT NULL, `locked_until` TIMESTAMP NOT NULL, `server_host` VARCHAR(255) DEFAULT NULL, `pid` INT DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uq_cron_locks_job_name` (`job_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 29. settings -- ---------------------------------------------------------------------------- CREATE TABLE `settings` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `setting_key` VARCHAR(100) NOT NULL, `setting_value` TEXT NOT NULL, `setting_group` VARCHAR(50) 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 `uq_settings_key` (`setting_key`), KEY `idx_settings_group` (`setting_group`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 30. cache_items -- ---------------------------------------------------------------------------- CREATE TABLE `cache_items` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `cache_key` VARCHAR(255) NOT NULL, `cache_value` LONGTEXT NOT NULL, `expiration` TIMESTAMP NULL DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uq_cache_items_key` (`cache_key`), KEY `idx_cache_items_expiration` (`expiration`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 31. notifications -- ---------------------------------------------------------------------------- CREATE TABLE `notifications` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `type` VARCHAR(50) NOT NULL, `title` VARCHAR(255) NOT NULL, `message` TEXT DEFAULT NULL, `level` ENUM('info','warning','error','success') NOT NULL, `is_read` TINYINT(1) NOT NULL DEFAULT 0, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_notifications_level` (`level`), KEY `idx_notifications_is_read` (`is_read`), KEY `idx_notifications_created_at` (`created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ---------------------------------------------------------------------------- -- 32. system_logs -- ---------------------------------------------------------------------------- CREATE TABLE `system_logs` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `level` VARCHAR(20) NOT NULL, `message` TEXT NOT NULL, `context_json` JSON DEFAULT NULL, `file` VARCHAR(255) DEFAULT NULL, `line` INT DEFAULT NULL, `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_system_logs_level` (`level`), KEY `idx_system_logs_created_at` (`created_at`), KEY `idx_system_logs_level_created_at` (`level`, `created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================================ -- Additional indexes for common queries -- ============================================================================ -- teams: lookup by provider + external_id CREATE INDEX `idx_teams_provider_external` ON `teams` (`provider`, `external_id`); -- players: lookup by provider + external_id CREATE INDEX `idx_players_provider_external` ON `players` (`provider`, `external_id`); -- fixtures: composite index for date-range queries by competition CREATE INDEX `idx_fixtures_comp_kickoff` ON `fixtures` (`competition_id`, `kickoff_at`); -- fixtures: find fixtures between two teams CREATE INDEX `idx_fixtures_home_away` ON `fixtures` (`home_team_id`, `away_team_id`, `kickoff_at`); -- standings: ordering by position within a competition season CREATE INDEX `idx_standings_position` ON `standings` (`competition_id`, `season_id`, `position`); -- elo_ratings: find top-rated teams in a competition CREATE INDEX `idx_elo_ratings_rating` ON `elo_ratings` (`competition_id`, `rating` DESC); -- predictions: active predictions for a fixture CREATE INDEX `idx_predictions_fixture_status` ON `predictions` (`fixture_id`, `status`); -- head_to_head: recent encounters between two teams CREATE INDEX `idx_h2h_teams_date` ON `head_to_head_records` (`home_team_id`, `away_team_id`, `played_at` DESC); -- team_form_snapshots: latest form snapshot per team/competition CREATE INDEX `idx_form_snapshots_lookup` ON `team_form_snapshots` (`team_id`, `competition_id`, `season_id`, `form_type`); -- cache_items: purge expired entries CREATE INDEX `idx_cache_expiration_lookup` ON `cache_items` (`expiration`, `cache_key`); -- ai_requests: by prompt hash for deduplication CREATE INDEX `idx_ai_requests_prompt_hash` ON `ai_requests` (`prompt_hash`); -- api_sync_logs: by provider + sync_type CREATE INDEX `idx_api_sync_logs_provider_type` ON `api_sync_logs` (`provider`, `sync_type`); SET FOREIGN_KEY_CHECKS = 1;