USE aihub;

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
('football_data_provider', 'simulated'),
('football_api_key', ''),
('football_sync_days_ahead', '7'),
('football_last_live_sync', '');

ALTER TABLE fb_leagues ADD COLUMN IF NOT EXISTS external_ref VARCHAR(32) NULL;
ALTER TABLE fb_teams ADD COLUMN IF NOT EXISTS external_ref VARCHAR(32) NULL;

CREATE UNIQUE INDEX IF NOT EXISTS uq_fb_fixtures_external ON fb_fixtures (external_ref);

CREATE TABLE IF NOT EXISTS fb_sync_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  provider VARCHAR(32) NOT NULL,
  action VARCHAR(40) NOT NULL,
  fixtures_updated INT DEFAULT 0,
  live_updated INT DEFAULT 0,
  message VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

UPDATE fb_leagues SET external_ref='39' WHERE code='EPL' AND external_ref IS NULL;
UPDATE fb_leagues SET external_ref='40' WHERE code='EFL_CH' AND external_ref IS NULL;
UPDATE fb_leagues SET external_ref='140' WHERE code='LALIGA' AND external_ref IS NULL;
UPDATE fb_leagues SET external_ref='135' WHERE code='SERIE_A' AND external_ref IS NULL;
UPDATE fb_leagues SET external_ref='78' WHERE code='BUNDES' AND external_ref IS NULL;
UPDATE fb_leagues SET external_ref='61' WHERE code='LIGUE1' AND external_ref IS NULL;
UPDATE fb_leagues SET external_ref='2' WHERE code='UCL' AND external_ref IS NULL;
UPDATE fb_leagues SET external_ref='3' WHERE code='UEL' AND external_ref IS NULL;
UPDATE fb_leagues SET external_ref='848' WHERE code='UECL' AND external_ref IS NULL;
UPDATE fb_leagues SET external_ref='253' WHERE code='MLS' AND external_ref IS NULL;
UPDATE fb_leagues SET external_ref='307' WHERE code='SPL' AND external_ref IS NULL;