-- 全結果レース向けAI予想実績テーブル
USE keiba_ai;

CREATE TABLE IF NOT EXISTS all_race_ai_compare (
    id                      BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT PRIMARY KEY,
    race_id                 BIGINT UNSIGNED     NOT NULL,
    race_date               DATE                NOT NULL,
    track_code              VARCHAR(8)          NOT NULL,
    race_number             TINYINT UNSIGNED    NOT NULL,
    race_name               VARCHAR(256)        DEFAULT NULL,
    ai_honmei_umaban        TINYINT UNSIGNED    DEFAULT NULL COMMENT '◎本命（堅実1位）',
    ai_honmei_name          VARCHAR(128)        DEFAULT NULL,
    ai_honmei_score         DECIMAL(8,2)        DEFAULT NULL,
    ai_taikou_umaban        TINYINT UNSIGNED    DEFAULT NULL COMMENT '〇対抗（堅実2位）',
    ai_taikou_name          VARCHAR(128)        DEFAULT NULL,
    ai_taikou_score         DECIMAL(8,2)        DEFAULT NULL,
    ai_tanana_umaban        TINYINT UNSIGNED    DEFAULT NULL COMMENT '▲単穴（堅実3位）',
    ai_tanana_name          VARCHAR(128)        DEFAULT NULL,
    ai_tanana_score         DECIMAL(8,2)        DEFAULT NULL,
    ai_renka_umaban         TINYINT UNSIGNED    DEFAULT NULL COMMENT '△連下（堅実4位）',
    ai_renka_name           VARCHAR(128)        DEFAULT NULL,
    ai_renka_score          DECIMAL(8,2)        DEFAULT NULL,
    winner_umaban           TINYINT UNSIGNED    DEFAULT NULL,
    winner_name             VARCHAR(128)        DEFAULT NULL,
    match_honmei_winner     TINYINT(1)          DEFAULT NULL COMMENT '1=◎が1着',
    match_taikou_winner     TINYINT(1)          DEFAULT NULL COMMENT '1=〇が1着',
    match_honmei_or_taikou_winner TINYINT(1)    DEFAULT NULL COMMENT '1=◎or〇が1着',
    status                  VARCHAR(24)         NOT NULL DEFAULT 'ok',
    error_message           VARCHAR(512)        DEFAULT NULL,
    ai_snapshot_json        JSON                DEFAULT NULL,
    generated_at            DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_all_race_id (race_id),
    KEY idx_all_race_date (race_date),
    KEY idx_all_race_track (track_code, race_date),
    CONSTRAINT fk_all_race_compare_race FOREIGN KEY (race_id) REFERENCES races(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='結果確定済み全レースのAI予想実績';
