USE keiba_ai;

CREATE TABLE IF NOT EXISTS jv_app_records (
  id                  BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT PRIMARY KEY,
  dataspec            VARCHAR(16)         NOT NULL,
  record_type         VARCHAR(8)          NOT NULL,
  key_hash            CHAR(40)            NOT NULL COMMENT 'dataspec/record_type/論理キーのSHA1',
  logical_key         VARCHAR(255)        NOT NULL COMMENT '論理キー（レース/馬/作成日時等）',
  key_text            VARCHAR(64)         DEFAULT NULL COMMENT 'raw側 key_text',
  meet_year           SMALLINT UNSIGNED   DEFAULT NULL,
  meet_date           DATE                DEFAULT NULL,
  racecourse_code     VARCHAR(8)          DEFAULT NULL,
  race_number         TINYINT UNSIGNED    DEFAULT NULL,
  pedigree_reg_num    VARCHAR(16)         DEFAULT NULL,
  data_code           VARCHAR(8)          DEFAULT NULL,
  data_creation_date  DATE                DEFAULT NULL,
  source_raw_id       BIGINT UNSIGNED     NOT NULL COMMENT '元 jv_raw_records.id',
  fetched_at          DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  payload_json        JSON                NOT NULL,
  created_at          DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at          DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_jv_app_key (key_hash),
  KEY idx_jv_app_rt_date (record_type, meet_date),
  KEY idx_jv_app_course_date (racecourse_code, meet_date),
  KEY idx_jv_app_pedigree (pedigree_reg_num),
  KEY idx_jv_app_source_raw (source_raw_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='JVレコードのアプリ参照用最新展開';
