CREATE TABLE IF NOT EXISTS simulated_race_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    simulation_run INT NOT NULL,
    race_id INT NOT NULL,
    horse_id INT NOT NULL,
    horse_name VARCHAR(255),
    finish_position INT,
    adjusted_score DECIMAL(10, 4),
    average_speed INT,
    back_speed INT,
    speed_last_race INT,
    prime_power INT,
    average_class INT,
    form_score DECIMAL(5,2),
    morale INT,
    days_off INT,
    odds_at_time VARCHAR(20),
    win_flag TINYINT(1) DEFAULT 0,
    position_diff_from_favorite INT,
    simulation_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*CREATE TABLE IF NOT EXISTS simulated_race_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    simulation_run INT NOT NULL, -- Tracks the batch (1 to 1000)
    race_id INT NOT NULL,
    horse_id INT NOT NULL,
    horse_name VARCHAR(255),
    finish_position INT,
    adjusted_score DECIMAL(10, 4),
    average_speed INT,
    back_speed INT,
    speed_last_race INT,
    prime_power INT,
    average_class INT,
    form_score DECIMAL(5,2),
    morale INT,
    days_off INT,
    simulation_notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- SQL: Create race_simulations table
CREATE TABLE IF NOT EXISTS race_simulations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    race_id INT NOT NULL,
    simulation_run INT NOT NULL,
    horse_id INT NOT NULL,
    adjusted_score DECIMAL(10,4),
    finish_position INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
*/