-- Script de création de la base de données pour le système de données sportives
-- Auteur: Manus AI
-- Date: 2025-07-26

CREATE DATABASE IF NOT EXISTS football_system CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE football_system;

-- Table des pays
CREATE TABLE countries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_id INT UNIQUE NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100) NOT NULL,
    flag_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_api_id (api_id),
    INDEX idx_name_en (name_en),
    INDEX idx_name_ar (name_ar)
);

-- Table des équipes
CREATE TABLE teams (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_id INT UNIQUE NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100) NOT NULL,
    logo_url VARCHAR(255),
    country_id INT,
    founded INT,
    venue_name VARCHAR(100),
    venue_capacity INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE SET NULL,
    INDEX idx_api_id (api_id),
    INDEX idx_name_en (name_en),
    INDEX idx_name_ar (name_ar),
    INDEX idx_country_id (country_id)
);

-- Table des ligues/compétitions
CREATE TABLE leagues (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_id INT UNIQUE NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100) NOT NULL,
    type VARCHAR(50) NOT NULL, -- 'League', 'Cup'
    logo_url VARCHAR(255),
    country_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE SET NULL,
    INDEX idx_api_id (api_id),
    INDEX idx_name_en (name_en),
    INDEX idx_name_ar (name_ar),
    INDEX idx_type (type),
    INDEX idx_country_id (country_id)
);

-- Table des saisons
CREATE TABLE seasons (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_id INT UNIQUE NOT NULL,
    league_id INT NOT NULL,
    year INT NOT NULL,
    start_date DATE,
    end_date DATE,
    is_current BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (league_id) REFERENCES leagues(id) ON DELETE CASCADE,
    INDEX idx_api_id (api_id),
    INDEX idx_league_id (league_id),
    INDEX idx_year (year),
    INDEX idx_is_current (is_current)
);

-- Table des stades
CREATE TABLE venues (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_id INT UNIQUE NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100) NOT NULL,
    city_en VARCHAR(100),
    city_ar VARCHAR(100),
    country_id INT,
    capacity INT,
    surface VARCHAR(50),
    image_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE SET NULL,
    INDEX idx_api_id (api_id),
    INDEX idx_name_en (name_en),
    INDEX idx_name_ar (name_ar),
    INDEX idx_country_id (country_id)
);

-- Table des matchs
CREATE TABLE fixtures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_id INT UNIQUE NOT NULL,
    league_id INT NOT NULL,
    season_id INT NOT NULL,
    date DATETIME NOT NULL,
    status VARCHAR(50) NOT NULL, -- 'Not Started', 'Live', 'Finished', 'Postponed', etc.
    elapsed INT DEFAULT 0,
    venue_id INT,
    home_team_id INT NOT NULL,
    away_team_id INT NOT NULL,
    home_goals INT DEFAULT 0,
    away_goals INT DEFAULT 0,
    home_half_time_goals INT DEFAULT 0,
    away_half_time_goals INT DEFAULT 0,
    home_full_time_goals INT DEFAULT 0,
    away_full_time_goals INT DEFAULT 0,
    home_extra_time_goals INT DEFAULT 0,
    away_extra_time_goals INT DEFAULT 0,
    home_penalty_goals INT DEFAULT 0,
    away_penalty_goals INT DEFAULT 0,
    referee VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (league_id) REFERENCES leagues(id) ON DELETE CASCADE,
    FOREIGN KEY (season_id) REFERENCES seasons(id) ON DELETE CASCADE,
    FOREIGN KEY (venue_id) REFERENCES venues(id) ON DELETE SET NULL,
    FOREIGN KEY (home_team_id) REFERENCES teams(id) ON DELETE CASCADE,
    FOREIGN KEY (away_team_id) REFERENCES teams(id) ON DELETE CASCADE,
    INDEX idx_api_id (api_id),
    INDEX idx_league_id (league_id),
    INDEX idx_season_id (season_id),
    INDEX idx_date (date),
    INDEX idx_status (status),
    INDEX idx_home_team_id (home_team_id),
    INDEX idx_away_team_id (away_team_id)
);

-- Table des joueurs
CREATE TABLE players (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_id INT UNIQUE NOT NULL,
    name_en VARCHAR(100) NOT NULL,
    name_ar VARCHAR(100) NOT NULL,
    firstname_en VARCHAR(50),
    firstname_ar VARCHAR(50),
    lastname_en VARCHAR(50),
    lastname_ar VARCHAR(50),
    age INT,
    birth_date DATE,
    nationality_id INT,
    height VARCHAR(10),
    weight VARCHAR(10),
    photo_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (nationality_id) REFERENCES countries(id) ON DELETE SET NULL,
    INDEX idx_api_id (api_id),
    INDEX idx_name_en (name_en),
    INDEX idx_name_ar (name_ar),
    INDEX idx_nationality_id (nationality_id)
);

-- Table des statistiques des joueurs par match
CREATE TABLE player_statistics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fixture_id INT NOT NULL,
    player_id INT NOT NULL,
    team_id INT NOT NULL,
    minutes_played INT DEFAULT 0,
    goals INT DEFAULT 0,
    assists INT DEFAULT 0,
    saves INT DEFAULT 0,
    yellow_cards INT DEFAULT 0,
    red_cards INT DEFAULT 0,
    offsides INT DEFAULT 0,
    fouls_committed INT DEFAULT 0,
    tackles INT DEFAULT 0,
    interceptions INT DEFAULT 0,
    duels_won INT DEFAULT 0,
    dribbles_attempts INT DEFAULT 0,
    dribbles_success INT DEFAULT 0,
    passes_total INT DEFAULT 0,
    passes_accuracy INT DEFAULT 0,
    shots_total INT DEFAULT 0,
    shots_on_target INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (fixture_id) REFERENCES fixtures(id) ON DELETE CASCADE,
    FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE CASCADE,
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
    INDEX idx_fixture_id (fixture_id),
    INDEX idx_player_id (player_id),
    INDEX idx_team_id (team_id)
);

-- Table des classements
CREATE TABLE standings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    league_id INT NOT NULL,
    season_id INT NOT NULL,
    team_id INT NOT NULL,
    rank_position INT NOT NULL,
    points INT DEFAULT 0,
    goals_diff INT DEFAULT 0,
    group_name VARCHAR(50),
    form VARCHAR(10), -- ex: 'WWLDL'
    description VARCHAR(100), -- ex: 'Promotion - Champions League'
    all_played INT DEFAULT 0,
    all_win INT DEFAULT 0,
    all_draw INT DEFAULT 0,
    all_lose INT DEFAULT 0,
    all_goals_for INT DEFAULT 0,
    all_goals_against INT DEFAULT 0,
    home_played INT DEFAULT 0,
    home_win INT DEFAULT 0,
    home_draw INT DEFAULT 0,
    home_lose INT DEFAULT 0,
    home_goals_for INT DEFAULT 0,
    home_goals_against INT DEFAULT 0,
    away_played INT DEFAULT 0,
    away_win INT DEFAULT 0,
    away_draw INT DEFAULT 0,
    away_lose INT DEFAULT 0,
    away_goals_for INT DEFAULT 0,
    away_goals_against INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (league_id) REFERENCES leagues(id) ON DELETE CASCADE,
    FOREIGN KEY (season_id) REFERENCES seasons(id) ON DELETE CASCADE,
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
    UNIQUE KEY unique_standing (league_id, season_id, team_id, group_name),
    INDEX idx_league_id (league_id),
    INDEX idx_season_id (season_id),
    INDEX idx_team_id (team_id),
    INDEX idx_rank_position (rank_position)
);

-- Table des actualités
CREATE TABLE news (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title_en VARCHAR(255) NOT NULL,
    title_ar VARCHAR(255) NOT NULL,
    content_en TEXT,
    content_ar TEXT,
    summary_en TEXT,
    summary_ar TEXT,
    source_url VARCHAR(255),
    source_name VARCHAR(100),
    published_at DATETIME NOT NULL,
    image_url VARCHAR(255),
    category VARCHAR(50), -- 'transfer', 'match', 'injury', 'general'
    is_featured BOOLEAN DEFAULT FALSE,
    is_published BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_published_at (published_at),
    INDEX idx_category (category),
    INDEX idx_is_featured (is_featured),
    INDEX idx_is_published (is_published)
);

-- Table des événements de match
CREATE TABLE match_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fixture_id INT NOT NULL,
    team_id INT NOT NULL,
    player_id INT,
    assist_player_id INT,
    time_elapsed INT NOT NULL,
    time_extra INT,
    type VARCHAR(50) NOT NULL, -- 'Goal', 'Card', 'Substitution', etc.
    detail VARCHAR(100), -- 'Yellow Card', 'Red Card', 'Normal Goal', etc.
    comments TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (fixture_id) REFERENCES fixtures(id) ON DELETE CASCADE,
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
    FOREIGN KEY (player_id) REFERENCES players(id) ON DELETE SET NULL,
    FOREIGN KEY (assist_player_id) REFERENCES players(id) ON DELETE SET NULL,
    INDEX idx_fixture_id (fixture_id),
    INDEX idx_team_id (team_id),
    INDEX idx_player_id (player_id),
    INDEX idx_type (type),
    INDEX idx_time_elapsed (time_elapsed)
);

-- Table des logs système
CREATE TABLE system_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    level VARCHAR(10) NOT NULL, -- 'DEBUG', 'INFO', 'WARNING', 'ERROR'
    message TEXT NOT NULL,
    context JSON,
    source VARCHAR(100), -- nom du script ou module
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_level (level),
    INDEX idx_source (source),
    INDEX idx_created_at (created_at)
);

-- Table des configurations système
CREATE TABLE system_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_setting_key (setting_key)
);

-- Insertion des données de configuration par défaut
INSERT INTO system_settings (setting_key, setting_value, description) VALUES
('site_name_ar', 'نظام البيانات الرياضية', 'اسم الموقع باللغة العربية'),
('site_name_en', 'Football Data System', 'Site name in English'),
('maintenance_mode', '0', 'Maintenance mode (0=off, 1=on)'),
('api_football_enabled', '1', 'Enable API-Football integration'),
('live_score_api_enabled', '1', 'Enable Live-score API integration'),
('auto_update_enabled', '1', 'Enable automatic data updates'),
('cache_enabled', '1', 'Enable caching system'),
('default_language', 'ar', 'Default language for the system');

-- Création des répertoires de logs (à faire manuellement)
-- mkdir -p logs
-- chmod 755 logs

