CREATE TABLE IF NOT EXISTS race_horses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    race_id INT NOT NULL,
    horse_id INT NOT NULL,
    odds VARCHAR(20),
    morning_line VARCHAR(20),
    jersey_number INT,
    run_style VARCHAR(10),
    days_off INT,
    speed_last_race INT,
    prime_power INT,
    average_class INT,
    jockey_win_pct DECIMAL(5,2),
    trainer_win_pct DECIMAL(5,2),
    earnings_at_race DECIMAL(15,2),
    position_result INT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*
-- --------------------------------------------------------
-- Table structure for table `race_horses`
-- --------------------------------------------------------

CREATE TABLE IF NOT EXISTS `race_horses` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,

    -- Foreign keys
    `race_id` INT NOT NULL,
    `horse_id` INT NOT NULL,

    -- Race-specific horse data
    `odds` VARCHAR(20),                       -- e.g., "7/2"
    `morning_line` VARCHAR(20),              -- e.g., "5/2"
    `jersey_number` INT,                     -- Number worn during the race
    `run_style` VARCHAR(10),                 -- e.g., "E8", "S0", "P2"
    `days_off` INT,                          -- Days since last race
    `speed_last_race` INT,                   -- Speed rating last race
    `prime_power` INT,                       -- BRIS Prime Power rating
    `average_class` INT,                     -- BRIS class rating average

    `jockey_win_pct` DECIMAL(5,2),           -- e.g., 14.25 (%)
    `trainer_win_pct` DECIMAL(5,2),          -- e.g., 12.50 (%)
    `earnings_at_race` DECIMAL(15,2),        -- Lifetime earnings at race time

    `position_result` INT DEFAULT NULL,      -- 1 = winner, 2 = second, etc.

    -- Relationships
    FOREIGN KEY (`race_id`) REFERENCES `races`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`horse_id`) REFERENCES `horses`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
*/