USE aihub;

INSERT IGNORE INTO modules (slug, name, icon, description, status, is_core, sort_order) VALUES
('football', 'Football Center', '⚽', 'Live scores, analytics & predictions', 'enabled', 1, 3);

INSERT IGNORE INTO unlock_rules (module_id, rule_type, threshold_value, sort_order, status)
SELECT id, 'deposit_total', 0, 1, 'active' FROM modules WHERE slug = 'football' LIMIT 1;

CREATE TABLE IF NOT EXISTS fb_leagues (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(32) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  country VARCHAR(80) NULL,
  logo_url VARCHAR(255) NULL,
  tier ENUM('domestic','continental','international') DEFAULT 'domestic',
  sort_order INT DEFAULT 0,
  status ENUM('active','hidden') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fb_teams (
  id INT AUTO_INCREMENT PRIMARY KEY,
  league_id INT NULL,
  name VARCHAR(120) NOT NULL,
  short_name VARCHAR(20) NULL,
  logo_url VARCHAR(255) NULL,
  country VARCHAR(80) NULL,
  strength_rating DECIMAL(5,2) DEFAULT 50.00,
  form_rating DECIMAL(5,2) DEFAULT 50.00,
  attack_rating DECIMAL(5,2) DEFAULT 50.00,
  defense_rating DECIMAL(5,2) DEFAULT 50.00,
  home_advantage DECIMAL(4,2) DEFAULT 1.08,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_fb_teams_league (league_id),
  FOREIGN KEY (league_id) REFERENCES fb_leagues(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fb_fixtures (
  id INT AUTO_INCREMENT PRIMARY KEY,
  league_id INT NOT NULL,
  home_team_id INT NOT NULL,
  away_team_id INT NOT NULL,
  kickoff_at DATETIME NOT NULL,
  status ENUM('scheduled','live','finished','postponed','cancelled') DEFAULT 'scheduled',
  minute INT NULL,
  home_score INT DEFAULT 0,
  away_score INT DEFAULT 0,
  home_ht_score INT NULL,
  away_ht_score INT NULL,
  venue VARCHAR(120) NULL,
  round_label VARCHAR(60) NULL,
  external_ref VARCHAR(64) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_fb_fixtures_kickoff (kickoff_at),
  INDEX idx_fb_fixtures_status (status),
  FOREIGN KEY (league_id) REFERENCES fb_leagues(id) ON DELETE CASCADE,
  FOREIGN KEY (home_team_id) REFERENCES fb_teams(id) ON DELETE CASCADE,
  FOREIGN KEY (away_team_id) REFERENCES fb_teams(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fb_match_stats (
  fixture_id INT PRIMARY KEY,
  possession_home DECIMAL(5,2) NULL,
  possession_away DECIMAL(5,2) NULL,
  shots_home INT DEFAULT 0,
  shots_away INT DEFAULT 0,
  shots_on_home INT DEFAULT 0,
  shots_on_away INT DEFAULT 0,
  corners_home INT DEFAULT 0,
  corners_away INT DEFAULT 0,
  cards_home INT DEFAULT 0,
  cards_away INT DEFAULT 0,
  xg_home DECIMAL(4,2) NULL,
  xg_away DECIMAL(4,2) NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (fixture_id) REFERENCES fb_fixtures(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fb_team_form (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team_id INT NOT NULL,
  fixture_id INT NOT NULL,
  is_home TINYINT(1) NOT NULL,
  goals_for INT DEFAULT 0,
  goals_against INT DEFAULT 0,
  result CHAR(1) NULL,
  match_date DATE NOT NULL,
  INDEX idx_fb_form_team (team_id, match_date DESC),
  FOREIGN KEY (team_id) REFERENCES fb_teams(id) ON DELETE CASCADE,
  FOREIGN KEY (fixture_id) REFERENCES fb_fixtures(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fb_predictions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  fixture_id INT NOT NULL,
  market_type VARCHAR(40) NOT NULL,
  selection VARCHAR(40) NOT NULL,
  label VARCHAR(80) NOT NULL,
  odds DECIMAL(8,2) NOT NULL,
  confidence_pct DECIMAL(5,2) NOT NULL,
  risk_level ENUM('low','medium','high') DEFAULT 'medium',
  model_confidence DECIMAL(5,2) NULL,
  analyst_id INT NULL,
  is_premium TINYINT(1) DEFAULT 0,
  result ENUM('pending','won','lost','void') DEFAULT 'pending',
  reasoning TEXT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_fb_pred_fixture (fixture_id),
  FOREIGN KEY (fixture_id) REFERENCES fb_fixtures(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fb_injuries (
  id INT AUTO_INCREMENT PRIMARY KEY,
  team_id INT NOT NULL,
  player_name VARCHAR(80) NOT NULL,
  injury_type VARCHAR(80) NULL,
  status ENUM('out','doubtful','available') DEFAULT 'out',
  fixture_id INT NULL,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (team_id) REFERENCES fb_teams(id) ON DELETE CASCADE,
  FOREIGN KEY (fixture_id) REFERENCES fb_fixtures(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fb_analysts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  display_name VARCHAR(80) NOT NULL,
  bio TEXT NULL,
  avatar_url VARCHAR(255) NULL,
  total_picks INT DEFAULT 0,
  wins INT DEFAULT 0,
  roi_pct DECIMAL(6,2) DEFAULT 0,
  status ENUM('active','hidden') DEFAULT 'active',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fb_user_favorites (
  user_id INT NOT NULL,
  fav_type ENUM('team','league') NOT NULL,
  fav_id INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, fav_type, fav_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fb_prediction_tracking (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  prediction_id INT NOT NULL,
  bookmarked TINYINT(1) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_user_pred (user_id, prediction_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (prediction_id) REFERENCES fb_predictions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT IGNORE INTO fb_leagues (code, name, country, tier, sort_order) VALUES
('EPL', 'English Premier League', 'England', 'domestic', 1),
('EFL_CH', 'Championship', 'England', 'domestic', 2),
('LALIGA', 'La Liga', 'Spain', 'domestic', 3),
('SERIE_A', 'Serie A', 'Italy', 'domestic', 4),
('BUNDES', 'Bundesliga', 'Germany', 'domestic', 5),
('LIGUE1', 'Ligue 1', 'France', 'domestic', 6),
('UCL', 'UEFA Champions League', 'Europe', 'continental', 7),
('UEL', 'Europa League', 'Europe', 'continental', 8),
('UECL', 'Conference League', 'Europe', 'continental', 9),
('CAF_CL', 'CAF Champions League', 'Africa', 'continental', 10),
('WC', 'FIFA World Cup', 'International', 'international', 11),
('AFCON', 'Africa Cup of Nations', 'Africa', 'international', 12),
('MLS', 'Major League Soccer', 'USA', 'domestic', 13),
('SPL', 'Saudi Pro League', 'Saudi Arabia', 'domestic', 14);