-- sql/schema.sql (realtime minimal)
DROP TABLE IF EXISTS odds;
DROP TABLE IF EXISTS fixtures;

CREATE TABLE fixtures (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ext_id VARCHAR(64) NULL, -- id do provedor
  match_date DATETIME NOT NULL,
  league VARCHAR(120) NULL,
  home_team VARCHAR(120) NOT NULL,
  away_team VARCHAR(120) NOT NULL,
  status VARCHAR(30) DEFAULT 'scheduled',
  UNIQUE KEY unique_ext (ext_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE odds (
  id INT AUTO_INCREMENT PRIMARY KEY,
  fixture_id INT NOT NULL,
  book VARCHAR(80) NOT NULL,
  home DECIMAL(7,3) NULL,
  draw DECIMAL(7,3) NULL,
  away DECIMAL(7,3) NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fixture_id) REFERENCES fixtures(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
