-- Table pour stocker l'historique des performances des chevaux
-- Cette table est remplie automatiquement lors du scraping

CREATE TABLE IF NOT EXISTS horse_performances (
    id INT AUTO_INCREMENT PRIMARY KEY,
    horse_id INT NOT NULL,
    race_id INT NOT NULL,
    race_date DATE NOT NULL,
    track_id INT,
    distance INT,
    surface VARCHAR(50),
    going VARCHAR(100),
    final_position INT,
    finish_time VARCHAR(20),
    beaten_distance DECIMAL(10,2),
    weight_carried DECIMAL(10,2),
    jockey_id INT,
    trainer_id INT,
    starting_odds VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Index pour performances
    INDEX idx_horse_id (horse_id),
    INDEX idx_race_id (race_id),
    INDEX idx_race_date (race_date),
    INDEX idx_horse_race (horse_id, race_id),

    -- Contraintes uniques pour éviter les doublons
    UNIQUE KEY unique_horse_race (horse_id, race_id),

    -- Clés étrangères
    FOREIGN KEY (horse_id) REFERENCES horses(id) ON DELETE CASCADE,
    FOREIGN KEY (race_id) REFERENCES races(id) ON DELETE CASCADE,
    FOREIGN KEY (track_id) REFERENCES tracks(id) ON DELETE SET NULL,
    FOREIGN KEY (jockey_id) REFERENCES jockeys(id) ON DELETE SET NULL,
    FOREIGN KEY (trainer_id) REFERENCES trainers(id) ON DELETE SET NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Index supplémentaires pour les requêtes ML
CREATE INDEX idx_horse_date ON horse_performances(horse_id, race_date DESC);
CREATE INDEX idx_final_position ON horse_performances(final_position);
