-- ============================================================================
-- 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;
