USE keiba_ai;

CREATE TABLE IF NOT EXISTS odds_lines (
  id                  BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT PRIMARY KEY,
  race_id             BIGINT UNSIGNED     NOT NULL,
  odds_type           VARCHAR(24)         NOT NULL COMMENT 'tansho/fukusho/wakuren/umaren/wide/umatan/sanrenpuku/sanrentan',
  combination         VARCHAR(32)         NOT NULL COMMENT '馬番組み合わせ 例 5 / 5-8 / 5-8-12',
  min_odds            DECIMAL(12,1)     DEFAULT NULL,
  max_odds            DECIMAL(12,1)     DEFAULT NULL,
  popularity          SMALLINT UNSIGNED   DEFAULT NULL,
  source_record_type  VARCHAR(8)          DEFAULT NULL COMMENT 'O1..O6',
  created_at          DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_odds_race_type_combo (race_id, odds_type, combination),
  KEY idx_odds_race_type (race_id, odds_type),
  CONSTRAINT fk_odds_race FOREIGN KEY (race_id) REFERENCES races(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='オッズ情報（正規化）';
