-- ============================================================
-- SCHÉMA BASE DE DONNÉES - APPLICATION PRONOSTICS HIPPIQUES
-- ============================================================
-- Version: 1.0
-- Description: Base de données pour stocker les données France-Galop
--              et calculer les probabilités de victoire
-- ============================================================

-- Suppression des tables si elles existent (pour réinitialisation)

-- ============================================================
-- TABLE: tracks (hippodromes)
-- ============================================================
CREATE TABLE tracks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    code VARCHAR(50),
    country VARCHAR(100) DEFAULT 'France',

    -- Caractéristiques de la piste
    track_length INT,                    -- Longueur totale en mètres
    straight_length INT,                 -- Longueur ligne droite en mètres
    track_direction ENUM('droite', 'gauche', 'mixte'),
    track_configuration TEXT,            -- Description configuration (forme, virages, etc.)

    -- Surfaces disponibles
    has_turf BOOLEAN DEFAULT TRUE,       -- Gazon
    has_dirt BOOLEAN DEFAULT FALSE,      -- Terre
    has_synthetic BOOLEAN DEFAULT FALSE, -- Synthétique

    -- Métadonnées
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_name (name),
    INDEX idx_country (country)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: horses (chevaux)
-- ============================================================
CREATE TABLE horses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    country_of_birth VARCHAR(100),

    -- Informations de base
    sex ENUM('M', 'F', 'H', 'C') NOT NULL,  -- M=Male, F=Female, H=Hongre, C=Castré
    birth_year INT NOT NULL,
    birth_date DATE,
    color VARCHAR(50),                       -- Robe (alezan, bai, gris, etc.)

    -- Pedigree
    sire VARCHAR(255),                       -- Père
    dam VARCHAR(255),                        -- Mère
    dam_sire VARCHAR(255),                   -- Père de la mère
    breeder VARCHAR(255),                    -- Éleveur
    owner VARCHAR(255),                      -- Propriétaire

    -- Historique médical/forme
    injury_history TEXT,                     -- Historique des blessures
    last_injury_date DATE,
    equipment_notes TEXT,                    -- Œillères, fers spéciaux, etc.

    -- Statistiques globales (mises à jour après chaque course)
    total_runs INT DEFAULT 0,
    total_wins INT DEFAULT 0,
    total_places INT DEFAULT 0,              -- Podiums (2e + 3e)
    total_earnings DECIMAL(12, 2) DEFAULT 0,

    -- Ratings/Figures
    current_rating INT,                      -- Rating actuel (OR, RPR, etc.)
    best_rating INT,
    last_speed_figure DECIMAL(6, 2),

    -- Métadonnées
    france_galop_url VARCHAR(500),           -- URL du profil France-Galop
    last_scraped_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_name (name),
    INDEX idx_birth_year (birth_year),
    INDEX idx_sex (sex),
    INDEX idx_owner (owner),
    FULLTEXT idx_fulltext_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: jockeys
-- ============================================================
CREATE TABLE jockeys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    country VARCHAR(100) DEFAULT 'France',

    -- Informations
    birth_year INT,
    weight_category VARCHAR(50),             -- Catégorie de poids
    license_number VARCHAR(100),

    -- Statistiques globales
    total_runs INT DEFAULT 0,
    total_wins INT DEFAULT 0,
    total_places INT DEFAULT 0,
    win_rate DECIMAL(5, 2) DEFAULT 0,        -- % victoires
    place_rate DECIMAL(5, 2) DEFAULT 0,      -- % podiums

    -- Forme récente (calculée sur 30 derniers jours)
    recent_runs INT DEFAULT 0,
    recent_wins INT DEFAULT 0,
    recent_form VARCHAR(50),                 -- Ex: "1-3-2-5-1"

    -- Métadonnées
    france_galop_url VARCHAR(500),
    last_scraped_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_name (name),
    INDEX idx_win_rate (win_rate),
    FULLTEXT idx_fulltext_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: trainers (entraîneurs)
-- ============================================================
CREATE TABLE trainers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    country VARCHAR(100) DEFAULT 'France',

    -- Informations
    training_center VARCHAR(255),            -- Centre d'entraînement
    license_number VARCHAR(100),
    specialization TEXT,                     -- Spécialités (distance, type de course)

    -- Statistiques globales
    total_runs INT DEFAULT 0,
    total_wins INT DEFAULT 0,
    total_places INT DEFAULT 0,
    win_rate DECIMAL(5, 2) DEFAULT 0,
    place_rate DECIMAL(5, 2) DEFAULT 0,

    -- Forme récente
    recent_runs INT DEFAULT 0,
    recent_wins INT DEFAULT 0,
    recent_form VARCHAR(50),

    -- Métadonnées
    france_galop_url VARCHAR(500),
    last_scraped_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_name (name),
    INDEX idx_win_rate (win_rate),
    FULLTEXT idx_fulltext_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: races (courses)
-- ============================================================
CREATE TABLE races (
    id INT AUTO_INCREMENT PRIMARY KEY,
    track_id INT NOT NULL,

    -- Informations de base
    race_number INT NOT NULL,                -- Numéro de course dans la réunion
    race_name VARCHAR(255),
    race_date DATE NOT NULL,
    race_time TIME,

    -- Type de course
    race_type ENUM(
        'plat',                              -- Course de plat
        'obstacle',                          -- Obstacle
        'trot',                              -- Trot
        'autre'
    ) NOT NULL DEFAULT 'plat',

    race_category VARCHAR(100),              -- Handicap, Listed, Groupe I/II/III, Claimer, Maiden, etc.
    race_class VARCHAR(50),                  -- Classe (A, B, C, etc.)

    -- Conditions de course
    distance INT NOT NULL,                   -- Distance en mètres
    surface ENUM('gazon', 'terre', 'synthetique', 'sable') DEFAULT 'gazon',
    going VARCHAR(100),                      -- État du terrain (léger, bon, souple, collant, lourd, très lourd)
    going_code VARCHAR(20),                  -- Code standardisé (F=Firm, G=Good, GS=Good-Soft, S=Soft, H=Heavy)

    -- Conditions météo
    weather VARCHAR(100),                    -- Temps (ensoleillé, nuageux, pluie, etc.)
    temperature DECIMAL(4, 1),               -- Température en °C
    wind_speed INT,                          -- Vitesse du vent en km/h
    wind_direction VARCHAR(50),

    -- Configuration de course
    track_configuration VARCHAR(100),        -- Ligne droite, circulaire, etc.
    starting_method VARCHAR(50),             -- Départ (stalls, auto-start, etc.)

    -- Restrictions
    age_restriction VARCHAR(50),             -- Ex: "3 ans et plus", "2 ans"
    sex_restriction VARCHAR(50),             -- Ex: "Juments uniquement"

    -- Financier
    prize_money DECIMAL(12, 2),              -- Dotation totale
    currency VARCHAR(10) DEFAULT 'EUR',

    -- Participation
    declared_runners INT,                    -- Nombre de partants déclarés
    actual_runners INT,                      -- Nombre de partants réels

    -- Résultats (si course terminée)
    is_finished BOOLEAN DEFAULT FALSE,
    race_status ENUM('à_venir', 'en_cours', 'terminée', 'annulée') DEFAULT 'à_venir',
    winning_time DECIMAL(8, 3),              -- Temps du gagnant en secondes
    race_comments TEXT,                      -- Commentaires de course

    -- Pace analysis (rythme de course)
    pace_prediction ENUM('lent', 'modéré', 'rapide'),
    pace_actual ENUM('lent', 'modéré', 'rapide'),

    -- Métadonnées
    france_galop_url VARCHAR(500),
    last_scraped_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (track_id) REFERENCES tracks(id) ON DELETE CASCADE,

    INDEX idx_race_date (race_date),
    INDEX idx_track_date (track_id, race_date),
    INDEX idx_race_status (race_status),
    INDEX idx_race_type (race_type),
    INDEX idx_distance (distance),
    INDEX idx_surface (surface)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: race_entries (partants/chevaux dans une course)
-- ============================================================
CREATE TABLE race_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    race_id INT NOT NULL,
    horse_id INT NOT NULL,
    jockey_id INT,
    trainer_id INT,

    -- Informations de participation
    stall_number INT,                        -- Numéro de corde/barrière
    cloth_number INT,                        -- Numéro de selle/dossard
    weight_carried DECIMAL(5, 2),            -- Poids porté en kg
    weight_declared DECIMAL(5, 2),           -- Poids déclaré

    -- Équipement
    equipment TEXT,                          -- Œillères, langue attachée, etc.
    equipment_change BOOLEAN DEFAULT FALSE,  -- Changement d'équipement depuis dernière course

    -- Cotes
    morning_odds DECIMAL(8, 2),              -- Cote du matin
    starting_odds DECIMAL(8, 2),             -- Cote finale au départ
    betting_rank INT,                        -- Rang dans les paris (favori = 1)

    -- Résultats (si course terminée)
    final_position INT,                      -- Position finale (1=gagnant, 0=non placé)
    disqualified BOOLEAN DEFAULT FALSE,
    official_position INT,                   -- Position officielle après réclamations

    finish_time DECIMAL(8, 3),               -- Temps réalisé en secondes
    beaten_distance VARCHAR(50),             -- Écart avec le gagnant (longueurs, etc.)

    -- Commentaires
    jockey_comments TEXT,
    race_comments TEXT,                      -- Commentaire du déroulement pour ce cheval

    -- Avant-course
    pre_race_condition VARCHAR(100),         -- État physique avant course
    days_since_last_run INT,                 -- Jours depuis dernière course
    is_first_run BOOLEAN DEFAULT FALSE,      -- Premier départ de la carrière

    -- Métadonnées
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (race_id) REFERENCES races(id) ON DELETE CASCADE,
    FOREIGN KEY (horse_id) REFERENCES horses(id) ON DELETE CASCADE,
    FOREIGN KEY (jockey_id) REFERENCES jockeys(id) ON DELETE SET NULL,
    FOREIGN KEY (trainer_id) REFERENCES trainers(id) ON DELETE SET NULL,

    UNIQUE KEY unique_entry (race_id, horse_id),
    INDEX idx_race (race_id),
    INDEX idx_horse (horse_id),
    INDEX idx_jockey (jockey_id),
    INDEX idx_trainer (trainer_id),
    INDEX idx_final_position (final_position)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: sectionals (temps partiels/splits)
-- ============================================================
CREATE TABLE sectionals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    race_entry_id INT NOT NULL,

    -- Temps partiels par section
    furlong_2 DECIMAL(6, 3),                 -- Temps à 400m
    furlong_4 DECIMAL(6, 3),                 -- Temps à 800m
    furlong_6 DECIMAL(6, 3),                 -- Temps à 1200m
    furlong_8 DECIMAL(6, 3),                 -- Temps à 1600m

    -- Position par section
    position_start INT,                      -- Position au départ
    position_200m INT,
    position_400m INT,
    position_600m INT,
    position_800m INT,
    position_1000m INT,
    position_straight INT,                   -- Position entrée ligne droite

    -- Analyse du kick final
    final_furlong_time DECIMAL(6, 3),        -- Temps dernier 200m
    final_speed DECIMAL(6, 2),               -- Vitesse finale en km/h
    acceleration_rate DECIMAL(6, 2),         -- Taux d'accélération

    -- Métadonnées
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (race_entry_id) REFERENCES race_entries(id) ON DELETE CASCADE,

    INDEX idx_race_entry (race_entry_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: criteria_weights (poids des critères configurables)
-- ============================================================
CREATE TABLE criteria_weights (
    id INT AUTO_INCREMENT PRIMARY KEY,
    criterion_code VARCHAR(100) NOT NULL UNIQUE,  -- Code unique du critère
    criterion_name VARCHAR(255) NOT NULL,          -- Nom du critère
    weight DECIMAL(5, 2) NOT NULL,                 -- Poids en %
    description TEXT,                              -- Description du critère
    is_active BOOLEAN DEFAULT TRUE,                -- Actif ou non
    calculation_method TEXT,                       -- Méthode de calcul

    -- Métadonnées
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_criterion_code (criterion_code),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: scoring_history (historique des scores calculés)
-- ============================================================
-- Permet de garder une trace des calculs et faire des backtests
CREATE TABLE scoring_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    race_entry_id INT NOT NULL,

    -- Scores
    total_score DECIMAL(8, 4) NOT NULL,            -- Score total
    win_probability DECIMAL(6, 4) NOT NULL,        -- Probabilité calculée (0-1)
    predicted_rank INT,                            -- Rang prédit

    -- Détail des scores par critère (JSON)
    criteria_scores JSON,                          -- {"forme_recente": 8.5, "speed_figures": 7.2, ...}

    -- Résultat réel (pour backtesting)
    actual_position INT,                           -- Position réelle
    was_correct BOOLEAN,                           -- Prédiction correcte ?

    -- Métadonnées
    calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    calculation_version VARCHAR(50),               -- Version de l'algo de scoring

    FOREIGN KEY (race_entry_id) REFERENCES race_entries(id) ON DELETE CASCADE,

    INDEX idx_race_entry (race_entry_id),
    INDEX idx_calculated_at (calculated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- INSERTION DES POIDS DES 30 CRITÈRES PAR DÉFAUT
-- ============================================================
INSERT INTO criteria_weights (criterion_code, criterion_name, weight, description) VALUES
('forme_recente', 'Forme récente du cheval (3 dernières courses)', 11.5, 'Dernières performances = signal direct de la capacité actuelle'),
('speed_figures', 'Figures de vitesse / ratings', 10.5, 'Mesure quantitative de performance (RPR, OR, speed figures)'),
('distance_aptitude', 'Aptitude à la distance', 8.0, 'Preuves antérieures sur même distance ou voisinage'),
('surface_aptitude', 'Aptitude au type de piste/sol', 7.0, 'Préférence de surface (gazon/terre/synthétique)'),
('going_aptitude', 'État du terrain et adaptation', 6.0, 'Performance sur terrain sec, souple, boueux, etc.'),
('jockey_ability', 'Jockey - compétence et forme', 6.5, 'Compétence du jockey, forme récente, tactique'),
('trainer_ability', 'Entraîneur - forme et préparation', 5.5, 'Forme de l\'entraîneur, réussite sur type de course'),
('weight_carried', 'Poids porté', 5.0, 'Handicap / différence de poids entre concurrents'),
('track_history', 'Historique sur l\'hippodrome', 4.0, 'Performances antérieures sur la piste'),
('pace_analysis', 'Tactique de course / rythme', 4.5, 'Analyse du pace, position attendue en course'),
('sectional_performance', 'Dernière performance détaillée (splits)', 4.0, 'Temps partiels et accélérations'),
('race_type', 'Type de course / conditions', 3.0, 'Handicap, listed, classique, claimer, etc.'),
('draw_position', 'Tirage / numéro de départ', 3.0, 'Avantage ou désavantage de la barrière'),
('market_odds', 'Marché / cotes', 3.0, 'Information collective du marché des paris'),
('fitness_recency', 'Distance depuis dernière course', 2.0, 'Match fitness vs sur-réactivité'),
('rest_interval', 'Intervalle de repos', 2.0, 'Jours depuis la dernière course'),
('equipment_change', 'Changements d\'équipement', 2.0, 'Œillères, fers, etc.'),
('injury_history', 'Antécédents de blessures', 2.0, 'Interruptions de carrière, historique médical'),
('pedigree', 'Pedigree / génétique', 1.5, 'Adaptation génétique à distance & sol'),
('relative_weight', 'Poids relatif des concurrents', 1.5, 'Comparatif du poids dans le champ'),
('physical_condition', 'Condition physique pré-course', 1.0, 'Body language, aspect général'),
('travel_factor', 'Transport / voyage', 1.0, 'Changement de base, déplacement'),
('field_size', 'Taille du champ', 1.0, 'Nombre de partants'),
('age_sex', 'Âge et sexe', 1.0, 'Impact de l\'âge et du sexe sur la performance'),
('class_change', 'Progression / régression de classe', 1.0, 'Montée ou descente de niveau'),
('gate_behavior', 'Comportement au départ', 0.5, 'Fiabilité du break, comportement en box'),
('head_to_head', 'Historique face aux adversaires', 0.5, 'Résultats directs contre les mêmes chevaux'),
('weather_forecast', 'Météo prévue', 0.5, 'Pluie, vent prévus le jour de la course'),
('final_kick', 'Sectionnels / kick final', 0.5, 'Capacité d\'accélération finale'),
('random_incidents', 'Éléments aléatoires / incidents', 1.0, 'Facteurs imprévisibles');

-- ============================================================
-- VUES UTILES
-- ============================================================

-- Vue pour obtenir les dernières performances d'un cheval
CREATE OR REPLACE VIEW vw_horse_recent_form AS
SELECT
    re.horse_id,
    h.name as horse_name,
    r.race_date,
    r.distance,
    r.surface,
    r.going,
    re.final_position,
    re.finish_time,
    re.beaten_distance,
    t.name as track_name,
    j.name as jockey_name,
    re.starting_odds
FROM race_entries re
JOIN races r ON re.race_id = r.id
JOIN horses h ON re.horse_id = h.id
JOIN tracks t ON r.track_id = t.id
LEFT JOIN jockeys j ON re.jockey_id = j.id
WHERE r.is_finished = TRUE
ORDER BY re.horse_id, r.race_date DESC;

-- Vue pour les statistiques globales par hippodrome
CREATE OR REPLACE VIEW vw_track_statistics AS
SELECT
    t.id as track_id,
    t.name as track_name,
    COUNT(DISTINCT r.id) as total_races,
    COUNT(DISTINCT re.horse_id) as unique_horses,
    AVG(r.actual_runners) as avg_field_size,
    AVG(r.prize_money) as avg_prize_money
FROM tracks t
LEFT JOIN races r ON t.id = r.track_id
LEFT JOIN race_entries re ON r.id = re.race_id
GROUP BY t.id, t.name;

-- ============================================================
-- INDEX ADDITIONNELS POUR OPTIMISATION DES REQUÊTES
-- ============================================================

-- Index composites pour recherches fréquentes
CREATE INDEX idx_race_date_track ON races(race_date, track_id, race_status);
CREATE INDEX idx_horse_recent_races ON race_entries(horse_id, race_id);
CREATE INDEX idx_entry_results ON race_entries(race_id, final_position, starting_odds);

-- ============================================================
-- FIN DU SCHÉMA
-- ============================================================
