USE aihub;

CREATE TABLE IF NOT EXISTS sure_odds_leagues (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  country VARCHAR(60) NULL,
  logo_url VARCHAR(255) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  status ENUM('active','hidden') NOT NULL DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sure_odds_fixtures (
  id INT AUTO_INCREMENT PRIMARY KEY,
  league_id INT NOT NULL,
  home_team VARCHAR(100) NOT NULL,
  away_team VARCHAR(100) NOT NULL,
  kickoff_at DATETIME NOT NULL,
  status ENUM('scheduled','live','finished','cancelled') NOT NULL DEFAULT 'scheduled',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (league_id) REFERENCES sure_odds_leagues(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sure_odds_picks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  fixture_id INT NOT NULL,
  pick_date DATE NOT NULL,
  prediction VARCHAR(40) NOT NULL,
  pick_label VARCHAR(120) NOT NULL,
  odds DECIMAL(8,2) NOT NULL,
  confidence ENUM('high','medium','low') NOT NULL DEFAULT 'high',
  is_premium TINYINT(1) NOT NULL DEFAULT 0,
  result ENUM('pending','won','lost','void') NOT NULL DEFAULT 'pending',
  notes TEXT NULL,
  staking_enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (fixture_id) REFERENCES sure_odds_fixtures(id) ON DELETE CASCADE,
  INDEX idx_pick_date (pick_date)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sure_odds_stakes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  pick_id INT NOT NULL,
  stake DECIMAL(15,2) NOT NULL,
  potential_win DECIMAL(15,2) NOT NULL,
  payout DECIMAL(15,2) NOT NULL DEFAULT 0,
  status ENUM('open','won','lost','void','refunded') NOT NULL DEFAULT 'open',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  settled_at DATETIME NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (pick_id) REFERENCES sure_odds_picks(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT IGNORE INTO sure_odds_leagues (name, country, sort_order) VALUES
('Premier League', 'England', 1),
('La Liga', 'Spain', 2),
('World Cup', 'International', 3),
('UPL', 'Uganda', 4);