-- スプレッドシート実績とAI予想の突合用
USE keiba_ai;

CREATE TABLE IF NOT EXISTS sheet_forecast_compare (
    id                      BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT PRIMARY KEY,
    race_date               DATE                NOT NULL,
    track_code              VARCHAR(8)          NOT NULL,
    race_number             TINYINT UNSIGNED    NOT NULL,
    race_id                 BIGINT UNSIGNED     DEFAULT NULL,
    sheet_race_name         VARCHAR(256)        DEFAULT NULL,
    sheet_marks             TEXT                DEFAULT NULL,
    sheet_honmei_name       VARCHAR(128)        DEFAULT NULL,
    sheet_honmei_umaban     TINYINT UNSIGNED    DEFAULT NULL,
    sheet_hit               VARCHAR(32)         DEFAULT NULL,
    sheet_payout_yen        INT                 DEFAULT NULL,
    sheet_pl_yen            INT                   DEFAULT NULL,
    ai_rank1_umaban         TINYINT UNSIGNED    DEFAULT NULL,
    ai_rank1_name           VARCHAR(128)        DEFAULT NULL,
    ai_rank1_score          DECIMAL(8,2)        DEFAULT NULL,
    ai_value1_umaban        TINYINT UNSIGNED    DEFAULT NULL,
    ai_value1_name          VARCHAR(128)        DEFAULT NULL,
    ai_value1_roi           DECIMAL(10,2)       DEFAULT NULL,
    winner_umaban           TINYINT UNSIGNED    DEFAULT NULL,
    winner_name             VARCHAR(128)        DEFAULT NULL,
    match_honmei_winner     TINYINT(1)          DEFAULT NULL COMMENT '1=sheet本命が1着',
    match_ai_rank1_winner   TINYINT(1)          DEFAULT NULL COMMENT '1=AI堅実1位が1着',
    match_ai_value1_winner  TINYINT(1)          DEFAULT NULL COMMENT '1=AI妙味1位が1着',
    match_ai_rank1_honmei   TINYINT(1)          DEFAULT NULL COMMENT '1=堅実1位=sheet本命馬番',
    match_ai_value1_honmei  TINYINT(1)          DEFAULT NULL COMMENT '1=妙味1位=sheet本命馬番',
    status                  VARCHAR(24)         NOT NULL DEFAULT 'ok',
    error_message           VARCHAR(512)        DEFAULT NULL,
    ai_snapshot_json        JSON                DEFAULT NULL,
    sheet_source_url        VARCHAR(512)        DEFAULT NULL,
    generated_at            DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_sheet_row (race_date, track_code, race_number),
    KEY idx_sheet_date (race_date),
    KEY idx_sheet_race (race_id),
    CONSTRAINT fk_sheet_fc_race FOREIGN KEY (race_id) REFERENCES races(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='スプレッドシート予想とAI比較';
