CREATE TABLE IF NOT EXISTS settings (
  setting_key VARCHAR(100) PRIMARY KEY,
  setting_value LONGTEXT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  google_sub VARCHAR(191) NOT NULL UNIQUE,
  email VARCHAR(191) NOT NULL UNIQUE,
  name VARCHAR(191) NOT NULL,
  avatar_url TEXT NULL,
  country_code VARCHAR(10) NULL,
  country_name VARCHAR(120) NULL,
  timezone VARCHAR(100) NOT NULL DEFAULT 'UTC',
  timezone_confirmed TINYINT(1) NOT NULL DEFAULT 0,
  wake_start TIME NOT NULL DEFAULT '05:00:00',
  wake_end TIME NOT NULL DEFAULT '23:00:00',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS remember_tokens (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  selector VARCHAR(64) NOT NULL UNIQUE,
  token_hash CHAR(64) NOT NULL,
  expires_at DATETIME NOT NULL,
  last_used_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_remember_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_remember_user (user_id),
  INDEX idx_remember_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS teams (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  api_team_id BIGINT NOT NULL UNIQUE,
  name VARCHAR(191) NOT NULL,
  code VARCHAR(20) NULL,
  country VARCHAR(120) NULL,
  iso2 VARCHAR(20) NULL,
  logo_url TEXT NULL,
  flag_url TEXT NULL,
  raw_json LONGTEXT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_teams_name (name),
  INDEX idx_teams_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS fixtures (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  api_fixture_id BIGINT NOT NULL UNIQUE,
  league_round VARCHAR(191) NULL,
  stage VARCHAR(80) NULL,
  fixture_date_utc DATETIME NOT NULL,
  timestamp_utc BIGINT NULL,
  status_short VARCHAR(20) NULL,
  status_long VARCHAR(120) NULL,
  elapsed INT NULL,
  venue_name VARCHAR(191) NULL,
  venue_city VARCHAR(120) NULL,
  home_team_api_id BIGINT NULL,
  away_team_api_id BIGINT NULL,
  home_team_name VARCHAR(191) NULL,
  away_team_name VARCHAR(191) NULL,
  goals_home INT NULL,
  goals_away INT NULL,
  score_home INT NULL,
  score_away INT NULL,
  winner_team_api_id BIGINT NULL,
  raw_json LONGTEXT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_fixture_date (fixture_date_utc),
  INDEX idx_fixture_stage (stage),
  INDEX idx_fixture_home (home_team_api_id),
  INDEX idx_fixture_away (away_team_api_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS standings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  group_name VARCHAR(120) NOT NULL,
  rank_num INT NOT NULL,
  team_api_id BIGINT NOT NULL,
  team_name VARCHAR(191) NOT NULL,
  points INT NOT NULL DEFAULT 0,
  goals_diff INT NOT NULL DEFAULT 0,
  played INT NOT NULL DEFAULT 0,
  win INT NOT NULL DEFAULT 0,
  draw INT NOT NULL DEFAULT 0,
  lose INT NOT NULL DEFAULT 0,
  goals_for INT NOT NULL DEFAULT 0,
  goals_against INT NOT NULL DEFAULT 0,
  description VARCHAR(191) NULL,
  raw_json LONGTEXT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_group_team (group_name, team_api_id),
  INDEX idx_group_rank (group_name, rank_num)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS rounds (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  round_name VARCHAR(191) NOT NULL UNIQUE,
  stage VARCHAR(80) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_favourite_teams (
  user_id BIGINT UNSIGNED NOT NULL,
  team_api_id BIGINT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (user_id, team_api_id),
  CONSTRAINT fk_fav_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS push_subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  endpoint TEXT NOT NULL,
  p256dh VARCHAR(255) NOT NULL,
  auth VARCHAR(255) NOT NULL,
  content_encoding VARCHAR(30) NOT NULL DEFAULT 'aes128gcm',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_push_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_push_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS match_reminders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  fixture_api_id BIGINT NOT NULL,
  notify_at_utc DATETIME NOT NULL,
  sent_at DATETIME NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_fixture (user_id, fixture_api_id),
  CONSTRAINT fk_reminder_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_reminder_due (notify_at_utc, sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sync_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sync_type VARCHAR(60) NOT NULL,
  status VARCHAR(30) NOT NULL,
  message TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_sync_type_date (sync_type, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
