DROP TABLE IF EXISTS `horses`;

CREATE TABLE `horses` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL UNIQUE,
  `run_style` VARCHAR(10) DEFAULT NULL,
  `average_speed` INT DEFAULT NULL,
  `back_speed` INT DEFAULT NULL,
  `speed_last_race` INT DEFAULT NULL,
  `prime_power` INT DEFAULT NULL,
  `average_class` INT DEFAULT NULL,
  `days_off` INT DEFAULT NULL,
  `jockey_win_pct` DECIMAL(5,2) DEFAULT NULL,
  `trainer_win_pct` DECIMAL(5,2) DEFAULT NULL,
  `earnings` DECIMAL(15,2) DEFAULT NULL,
  `win_count` INT DEFAULT 0,
  `race_count` INT DEFAULT 0,
  `form` VARCHAR(10) DEFAULT '',
  `morale` INT DEFAULT 5,
  `last_result` CHAR(1) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



/*
CREATE TABLE IF NOT EXISTS horses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,

    -- Performance style
    run_style VARCHAR(10),                     -- E.g., E8, P2, S0

    -- Speed ratings
    average_speed INT,
    average_distance INT,                      -- Optional future use
    best_speed INT,
    speed_last_race INT,

    -- Class and power
    prime_power INT,
    last_class INT,
    average_class INT,

    -- Time off and condition
    days_off INT,

    -- Jockey/trainer performance
    jockey_win_pct DECIMAL(5,2),
    trainer_win_pct DECIMAL(5,2),

    -- Earnings
    earnings DECIMAL(15,2),

    -- Metadata
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
*/