-- ============================================
-- MIGRATION 009: Création table horse_performances
-- ============================================
-- Stocke l'historique complet des performances de chaque cheval
-- Essentiel pour les prédictions IA (analyse de forme)

CREATE TABLE IF NOT EXISTS horse_performances (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    -- Relations
    horse_id INT UNSIGNED NOT NULL,
    race_id INT UNSIGNED NOT NULL,
    track_id INT UNSIGNED NOT NULL,
    jockey_id INT UNSIGNED DEFAULT NULL,
    trainer_id INT UNSIGNED DEFAULT NULL,

    -- Contexte de la course
    race_date DATE NOT NULL,
    distance INT UNSIGNED NOT NULL COMMENT 'Distance en mètres',
    going VARCHAR(50) DEFAULT NULL COMMENT 'État du terrain',
    surface ENUM('gazon', 'synthetique', 'terre', 'sable') DEFAULT NULL,
    race_category VARCHAR(50) DEFAULT NULL COMMENT 'Handicap, Groupe I/II/III, Listed, Maiden',
    race_type VARCHAR(50) DEFAULT NULL COMMENT 'Plat, Haies, Steeple-Chase, Cross',
    number_of_runners INT UNSIGNED DEFAULT NULL COMMENT 'Nombre de partants',

    -- Performance du cheval
    number INT UNSIGNED DEFAULT NULL COMMENT 'Numéro de départ',
    final_position INT UNSIGNED DEFAULT NULL COMMENT 'Position finale (1, 2, 3, ...)',
    beaten_distance VARCHAR(50) DEFAULT NULL COMMENT 'Distance battue (longueurs)',
    finish_time DECIMAL(10,3) DEFAULT NULL COMMENT 'Temps final en secondes',
    weight DECIMAL(5,2) DEFAULT NULL COMMENT 'Poids porté en kg',
    odds DECIMAL(10,2) DEFAULT NULL COMMENT 'Cote de départ',

    -- Métadonnées
    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_track_id (track_id),
    INDEX idx_race_date (race_date),
    INDEX idx_final_position (final_position),
    INDEX idx_horse_date (horse_id, race_date DESC),

    -- Contraintes de clés étrangères
    CONSTRAINT fk_hp_horse FOREIGN KEY (horse_id) REFERENCES horses(id) ON DELETE CASCADE,
    CONSTRAINT fk_hp_race FOREIGN KEY (race_id) REFERENCES races(id) ON DELETE CASCADE,
    CONSTRAINT fk_hp_track FOREIGN KEY (track_id) REFERENCES tracks(id) ON DELETE CASCADE,
    CONSTRAINT fk_hp_jockey FOREIGN KEY (jockey_id) REFERENCES jockeys(id) ON DELETE SET NULL,
    CONSTRAINT fk_hp_trainer FOREIGN KEY (trainer_id) REFERENCES trainers(id) ON DELETE SET NULL,

    -- Contrainte unique : un cheval ne peut avoir qu'une seule performance par course
    UNIQUE KEY unique_horse_race (horse_id, race_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Historique des performances de chaque cheval pour analyse IA';

-- ============================================
-- VUES UTILES POUR ANALYSE
-- ============================================

-- Vue : Dernières 5 performances par cheval
CREATE OR REPLACE VIEW v_horse_last_5_performances AS
SELECT
    hp.*,
    h.name as horse_name,
    t.name as track_name,
    r.race_name
FROM horse_performances hp
INNER JOIN horses h ON hp.horse_id = h.id
INNER JOIN tracks t ON hp.track_id = t.id
INNER JOIN races r ON hp.race_id = r.id
ORDER BY hp.horse_id, hp.race_date DESC;

-- Vue : Statistiques de forme par cheval (30 derniers jours)
CREATE OR REPLACE VIEW v_horse_form_30days AS
SELECT
    horse_id,
    COUNT(*) as races_count,
    AVG(final_position) as avg_position,
    SUM(CASE WHEN final_position = 1 THEN 1 ELSE 0 END) as wins,
    SUM(CASE WHEN final_position <= 3 THEN 1 ELSE 0 END) as places,
    MAX(race_date) as last_race_date,
    DATEDIFF(CURDATE(), MAX(race_date)) as days_since_last_race
FROM horse_performances
WHERE race_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
GROUP BY horse_id;

-- ============================================
-- TRIGGER : Mise à jour automatique depuis race_entries
-- ============================================

DELIMITER //

CREATE TRIGGER tr_update_horse_performances_after_race_entry_update
AFTER UPDATE ON race_entries
FOR EACH ROW
BEGIN
    -- Si final_position est défini, créer ou mettre à jour horse_performances
    IF NEW.final_position IS NOT NULL THEN
        INSERT INTO horse_performances (
            horse_id, race_id, track_id, jockey_id, trainer_id,
            race_date, distance, going, surface, race_category, race_type, number_of_runners,
            number, final_position, beaten_distance, finish_time, weight, odds
        )
        SELECT
            NEW.horse_id,
            NEW.race_id,
            r.track_id,
            NEW.jockey_id,
            NEW.trainer_id,
            r.race_date,
            r.distance,
            r.going,
            r.surface,
            r.race_category,
            r.race_type,
            r.number_of_runners,
            NEW.number,
            NEW.final_position,
            NEW.beaten_distance,
            NEW.finish_time,
            NEW.weight,
            NEW.odds
        FROM races r
        WHERE r.id = NEW.race_id
        ON DUPLICATE KEY UPDATE
            final_position = NEW.final_position,
            beaten_distance = NEW.beaten_distance,
            finish_time = NEW.finish_time,
            weight = NEW.weight,
            odds = NEW.odds,
            updated_at = CURRENT_TIMESTAMP;
    END IF;
END//

CREATE TRIGGER tr_create_horse_performances_after_race_entry_insert
AFTER INSERT ON race_entries
FOR EACH ROW
BEGIN
    -- Si final_position est défini dès l'insertion, créer horse_performances
    IF NEW.final_position IS NOT NULL THEN
        INSERT INTO horse_performances (
            horse_id, race_id, track_id, jockey_id, trainer_id,
            race_date, distance, going, surface, race_category, race_type, number_of_runners,
            number, final_position, beaten_distance, finish_time, weight, odds
        )
        SELECT
            NEW.horse_id,
            NEW.race_id,
            r.track_id,
            NEW.jockey_id,
            NEW.trainer_id,
            r.race_date,
            r.distance,
            r.going,
            r.surface,
            r.race_category,
            r.race_type,
            r.number_of_runners,
            NEW.number,
            NEW.final_position,
            NEW.beaten_distance,
            NEW.finish_time,
            NEW.weight,
            NEW.odds
        FROM races r
        WHERE r.id = NEW.race_id
        ON DUPLICATE KEY UPDATE
            final_position = NEW.final_position,
            beaten_distance = NEW.beaten_distance,
            finish_time = NEW.finish_time,
            weight = NEW.weight,
            odds = NEW.odds,
            updated_at = CURRENT_TIMESTAMP;
    END IF;
END//

DELIMITER ;

-- ============================================
-- PROCÉDURE : Remplir horse_performances depuis les données existantes
-- ============================================

DELIMITER //

CREATE PROCEDURE sp_populate_horse_performances()
BEGIN
    INSERT INTO horse_performances (
        horse_id, race_id, track_id, jockey_id, trainer_id,
        race_date, distance, going, surface, race_category, race_type, number_of_runners,
        number, final_position, beaten_distance, finish_time, weight, odds
    )
    SELECT
        re.horse_id,
        re.race_id,
        r.track_id,
        re.jockey_id,
        re.trainer_id,
        r.race_date,
        r.distance,
        r.going,
        r.surface,
        r.race_category,
        r.race_type,
        r.number_of_runners,
        re.number,
        re.final_position,
        re.beaten_distance,
        re.finish_time,
        re.weight,
        re.odds
    FROM race_entries re
    INNER JOIN races r ON re.race_id = r.id
    WHERE re.final_position IS NOT NULL
    ON DUPLICATE KEY UPDATE
        final_position = VALUES(final_position),
        beaten_distance = VALUES(beaten_distance),
        finish_time = VALUES(finish_time),
        weight = VALUES(weight),
        odds = VALUES(odds),
        updated_at = CURRENT_TIMESTAMP;

    SELECT ROW_COUNT() as rows_inserted;
END//

DELIMITER ;

-- ============================================
-- NOTE D'UTILISATION
-- ============================================
--
-- Pour peupler la table avec les données existantes:
--   CALL sp_populate_horse_performances();
--
-- Pour analyser la forme d'un cheval:
--   SELECT * FROM v_horse_form_30days WHERE horse_id = 123;
--
-- Pour obtenir les 5 dernières courses d'un cheval:
--   SELECT * FROM v_horse_last_5_performances WHERE horse_id = 123 LIMIT 5;
--
