-- 既存DB向け: circuit 列追加（中央/地方の区別。本番は JRA のみ使用）
USE keiba_ai;

SET @db := DATABASE();

-- tracks
SET @sql := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'tracks' AND COLUMN_NAME = 'circuit'
    ),
    'SELECT 1',
    'ALTER TABLE tracks ADD COLUMN circuit VARCHAR(8) NOT NULL DEFAULT ''JRA'' COMMENT ''JRA=中央競馬 LOCAL=地方競馬'' AFTER name_short, ADD KEY idx_track_circuit (circuit)'
  )
);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- races
SET @sql := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.COLUMNS
      WHERE TABLE_SCHEMA = @db AND TABLE_NAME = 'races' AND COLUMN_NAME = 'circuit'
    ),
    'SELECT 1',
    'ALTER TABLE races ADD COLUMN circuit VARCHAR(8) NOT NULL DEFAULT ''JRA'' COMMENT ''JRA=中央競馬 LOCAL=地方競馬'' AFTER track_id, ADD KEY idx_race_circuit (circuit)'
  )
);
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

UPDATE tracks SET circuit = 'JRA' WHERE circuit IS NULL OR circuit = '';
UPDATE races SET circuit = 'JRA' WHERE circuit IS NULL OR circuit = '';
