CREATE DATABASE IF NOT EXISTS neo_gpt
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE neo_gpt;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('user', 'pro', 'admin') NOT NULL DEFAULT 'user',
  subscription_status ENUM('free', 'active', 'canceled') NOT NULL DEFAULT 'free',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscriptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(60) NOT NULL DEFAULT 'stripe',
  provider_subscription_id VARCHAR(190) NULL,
  plan ENUM('free', 'pro_monthly', 'vip') NOT NULL DEFAULT 'free',
  status ENUM('trialing', 'active', 'past_due', 'canceled', 'incomplete') NOT NULL DEFAULT 'active',
  current_period_start DATETIME NULL,
  current_period_end DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_subscriptions_user (user_id),
  CONSTRAINT fk_subscriptions_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 competitions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  api_league_id INT NOT NULL,
  name VARCHAR(160) NOT NULL,
  country VARCHAR(120) NOT NULL,
  type ENUM('europeen', 'coupe_du_monde', 'autre') NOT NULL DEFAULT 'autre',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_competitions_api_league (api_league_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS matches (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  api_fixture_id BIGINT UNSIGNED NULL,
  competition_id BIGINT UNSIGNED NULL,
  kickoff_at DATETIME NOT NULL,
  status VARCHAR(80) NOT NULL DEFAULT 'À venir',
  home_team VARCHAR(160) NOT NULL,
  away_team VARCHAR(160) NOT NULL,
  home_logo VARCHAR(500) NULL,
  away_logo VARCHAR(500) NULL,
  venue VARCHAR(190) NULL,
  score_home INT NULL,
  score_away INT NULL,
  raw_payload JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_matches_api_fixture (api_fixture_id),
  KEY idx_matches_kickoff (kickoff_at),
  KEY idx_matches_competition (competition_id),
  CONSTRAINT fk_matches_competition FOREIGN KEY (competition_id) REFERENCES competitions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS analyses (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  match_id BIGINT UNSIGNED NOT NULL,
  title VARCHAR(220) NOT NULL,
  summary TEXT NOT NULL,
  team_form TEXT NULL,
  key_stats TEXT NULL,
  strengths TEXT NULL,
  weaknesses TEXT NULL,
  context TEXT NULL,
  confidence TINYINT UNSIGNED NOT NULL DEFAULT 50,
  risks TEXT NULL,
  recommendations_text TEXT NULL,
  explanation TEXT NULL,
  model_used VARCHAR(80) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_analyses_match (match_id),
  CONSTRAINT fk_analyses_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS daily_featured_analyses (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  analysis_date DATE NOT NULL,
  match_id BIGINT UNSIGNED NULL,
  api_fixture_id BIGINT UNSIGNED NULL,
  title VARCHAR(220) NOT NULL,
  description TEXT NOT NULL,
  confidence TINYINT UNSIGNED NULL,
  payload JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_daily_featured_date (analysis_date),
  KEY idx_daily_featured_match (match_id),
  KEY idx_daily_featured_api_fixture (api_fixture_id),
  CONSTRAINT fk_daily_featured_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS recommendations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  match_id BIGINT UNSIGNED NOT NULL,
  recommendation_type VARCHAR(120) NOT NULL,
  title VARCHAR(190) NOT NULL,
  explanation TEXT NOT NULL,
  confidence TINYINT UNSIGNED NOT NULL DEFAULT 50,
  odds DECIMAL(5,2) NULL,
  bookmaker VARCHAR(120) NULL,
  market VARCHAR(120) NULL,
  risk_level ENUM('Faible', 'Modéré', 'Moyen', 'Élevé') NOT NULL DEFAULT 'Modéré',
  is_free_preview TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('draft', 'published', 'archived') NOT NULL DEFAULT 'published',
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_recommendations_match (match_id),
  KEY idx_recommendations_status (status),
  CONSTRAINT fk_recommendations_match FOREIGN KEY (match_id) REFERENCES matches(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS chat_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  role ENUM('user', 'assistant') NOT NULL,
  content TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_chat_user_created (user_id, created_at),
  CONSTRAINT fk_chat_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 user_history (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  label VARCHAR(240) NOT NULL,
  metadata JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_history_user_created (user_id, created_at),
  CONSTRAINT fk_history_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 payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(80) NOT NULL DEFAULT 'stripe',
  provider_payment_id VARCHAR(190) NULL,
  status ENUM('pending', 'paid', 'failed', 'refunded') NOT NULL DEFAULT 'pending',
  amount_cents INT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'EUR',
  metadata JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_payments_user (user_id),
  CONSTRAINT fk_payments_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 settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  setting_key VARCHAR(120) NOT NULL,
  setting_value TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_settings_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS api_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  source VARCHAR(80) NOT NULL,
  level ENUM('info', 'warning', 'error') NOT NULL DEFAULT 'info',
  message VARCHAR(500) NOT NULL,
  payload JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_api_logs_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sessions (
  session_id VARCHAR(128) NOT NULL,
  expires INT UNSIGNED NOT NULL,
  data MEDIUMTEXT,
  PRIMARY KEY (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

USE neo_gpt;

INSERT INTO competitions (api_league_id, name, country, type, is_active)
VALUES
  (1, 'Coupe du Monde', 'Monde', 'coupe_du_monde', 1),
  (2, 'UEFA Champions League', 'Europe', 'europeen', 1),
  (3, 'UEFA Europa League', 'Europe', 'europeen', 1),
  (848, 'UEFA Conference League', 'Europe', 'europeen', 1),
  (4, 'UEFA Euro', 'Europe', 'europeen', 1),
  (5, 'UEFA Nations League', 'Europe', 'europeen', 1),
  (531, 'UEFA Super Cup', 'Europe', 'europeen', 1),
  (39, 'Premier League', 'England', 'europeen', 1),
  (140, 'Liga', 'Spain', 'europeen', 1),
  (135, 'Serie A', 'Italy', 'europeen', 1),
  (78, 'Bundesliga', 'Germany', 'europeen', 1),
  (61, 'Ligue 1', 'France', 'europeen', 1)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  country = VALUES(country),
  type = VALUES(type),
  is_active = VALUES(is_active);

INSERT INTO settings (setting_key, setting_value)
VALUES
  ('landing_headline', 'L’agent IA qui analyse les matchs'),
  ('pro_price_label', '19€/mois'),
  ('landing_support', 'Néo ne donne pas des pronostics. Néo explique, analyse et guide.')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);