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_weekly', 'pro_monthly', 'pro_6months', '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 oauth_accounts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(40) NOT NULL,
  provider_user_id VARCHAR(190) NOT NULL,
  provider_email VARCHAR(190) NULL,
  email_verified TINYINT(1) NOT NULL DEFAULT 0,
  raw_profile 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_oauth_provider_user (provider, provider_user_id),
  KEY idx_oauth_user (user_id),
  KEY idx_oauth_email (provider_email),
  CONSTRAINT fk_oauth_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 api_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,
  device_name VARCHAR(190) NULL,
  platform VARCHAR(40) NOT NULL DEFAULT 'ios',
  last_used_at DATETIME NULL,
  expires_at DATETIME NOT NULL,
  revoked_at 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),
  UNIQUE KEY uq_api_tokens_hash (token_hash),
  KEY idx_api_tokens_user (user_id),
  KEY idx_api_tokens_expires (expires_at),
  CONSTRAINT fk_api_tokens_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 device_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash CHAR(64) NOT NULL,
  token_value VARCHAR(500) NOT NULL,
  platform VARCHAR(40) NOT NULL DEFAULT 'ios',
  apns_environment VARCHAR(20) NOT NULL DEFAULT 'production',
  device_name VARCHAR(190) NULL,
  notifications_enabled TINYINT(1) NOT NULL DEFAULT 1,
  last_seen_at 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),
  UNIQUE KEY uq_device_token_hash (token_hash),
  KEY idx_device_tokens_user (user_id),
  CONSTRAINT fk_device_tokens_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 live_match_states (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  api_fixture_id BIGINT UNSIGNED NOT NULL,
  match_id BIGINT UNSIGNED NULL,
  status_short VARCHAR(20) NULL,
  score_home INT NULL,
  score_away INT NULL,
  event_signature_hash CHAR(64) NULL,
  payload JSON NULL,
  last_checked_at 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),
  UNIQUE KEY uq_live_match_states_fixture (api_fixture_id),
  KEY idx_live_match_states_status (status_short)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS push_notification_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  device_token_id BIGINT UNSIGNED NULL,
  dedupe_key VARCHAR(190) NOT NULL,
  title VARCHAR(190) NOT NULL,
  body TEXT NOT NULL,
  payload JSON NULL,
  sent_at DATETIME NULL,
  error TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_push_dedupe_user_device (user_id, device_token_id, dedupe_key),
  KEY idx_push_user_created (user_id, created_at),
  CONSTRAINT fk_push_log_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_push_log_device FOREIGN KEY (device_token_id) REFERENCES device_tokens(id) ON DELETE SET NULL
) 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 match_analysis_snapshots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  analysis_date DATE NOT NULL,
  window_start DATETIME NOT NULL,
  window_end DATETIME NOT NULL,
  match_id BIGINT UNSIGNED NULL,
  api_fixture_id BIGINT UNSIGNED NOT NULL,
  status ENUM('pending', 'generating', 'ready', 'failed', 'locked') NOT NULL DEFAULT 'pending',
  match_snapshot JSON NULL,
  payload JSON NULL,
  error_message TEXT NULL,
  generated_at DATETIME NULL,
  locked_at 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),
  UNIQUE KEY uq_match_analysis_snapshot_fixture_date (analysis_date, api_fixture_id),
  KEY idx_match_analysis_snapshot_match (match_id),
  KEY idx_match_analysis_snapshot_status (status),
  KEY idx_match_analysis_snapshot_window (window_start, window_end),
  CONSTRAINT fk_match_analysis_snapshot_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 neo_recommendation_history (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  recommendation_key VARCHAR(190) NOT NULL,
  match_id BIGINT UNSIGNED NULL,
  api_fixture_id BIGINT UNSIGNED NULL,
  match_label VARCHAR(190) NOT NULL,
  recommendation_type VARCHAR(120) NULL,
  title VARCHAR(190) NOT NULL,
  explanation TEXT NULL,
  confidence TINYINT UNSIGNED NULL,
  estimated_probability DECIMAL(5,2) NULL,
  odds DECIMAL(5,2) NULL,
  bookmaker VARCHAR(120) NULL,
  market VARCHAR(120) NULL,
  market_value VARCHAR(120) NULL,
  risk_level VARCHAR(40) NULL,
  result_status ENUM('pending', 'validated', 'lost', 'void', 'review_required') NOT NULL DEFAULT 'pending',
  result_checked_at DATETIME 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_neo_recommendation_key (recommendation_key),
  KEY idx_neo_recommendation_fixture (api_fixture_id),
  KEY idx_neo_recommendation_status (result_status),
  KEY idx_neo_recommendation_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_pick_watchlist (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  match_id BIGINT UNSIGNED NULL,
  api_fixture_id BIGINT UNSIGNED NULL,
  recommendation_key VARCHAR(190) NOT NULL,
  title VARCHAR(190) NOT NULL,
  market VARCHAR(120) NULL,
  odds DECIMAL(5,2) NULL,
  bookmaker VARCHAR(120) NULL,
  status ENUM('pending', 'validated', 'lost', 'void') NOT NULL DEFAULT 'pending',
  notified_at DATETIME 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_user_pick_daily (user_id, recommendation_key),
  KEY idx_user_pick_user_status (user_id, status),
  KEY idx_user_pick_fixture (api_fixture_id),
  CONSTRAINT fk_user_pick_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_user_pick_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 user_favorites (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  favorite_type ENUM('match', 'team', 'competition') NOT NULL DEFAULT 'match',
  external_id VARCHAR(80) NOT NULL,
  label VARCHAR(190) NOT 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_user_favorite (user_id, favorite_type, external_id),
  KEY idx_user_favorites_user (user_id, created_at),
  CONSTRAINT fk_user_favorites_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 match_votes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  match_id BIGINT UNSIGNED NOT NULL,
  api_fixture_id BIGINT UNSIGNED NULL,
  choice ENUM('home', 'draw', 'away') NOT 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_match_votes_user_match (user_id, match_id),
  KEY idx_match_votes_match (match_id),
  KEY idx_match_votes_fixture (api_fixture_id),
  CONSTRAINT fk_match_votes_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_notification_preferences (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  preference_key VARCHAR(80) NOT NULL,
  preference_value VARCHAR(120) NOT 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_user_notification_preference (user_id, preference_key),
  CONSTRAINT fk_user_notification_preferences_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_discipline_settings (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  setting_key VARCHAR(80) NOT NULL,
  setting_value VARCHAR(120) NOT 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_user_discipline_setting (user_id, setting_key),
  CONSTRAINT fk_user_discipline_settings_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 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 ai_usage_logs (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NULL,
  request_type VARCHAR(80) NOT NULL DEFAULT 'chat',
  provider VARCHAR(40) NOT NULL,
  model VARCHAR(120) NOT NULL,
  input_tokens INT UNSIGNED NOT NULL DEFAULT 0,
  output_tokens INT UNSIGNED NOT NULL DEFAULT 0,
  total_tokens INT UNSIGNED NOT NULL DEFAULT 0,
  estimated_cost_cents INT UNSIGNED NOT NULL DEFAULT 0,
  budget_mode ENUM('normal', 'conservative', 'economy', 'system') NOT NULL DEFAULT 'normal',
  metadata JSON NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ai_usage_user_created (user_id, created_at),
  KEY idx_ai_usage_created (created_at),
  KEY idx_ai_usage_provider (provider, model),
  CONSTRAINT fk_ai_usage_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS ai_budget_periods (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  plan_key VARCHAR(40) NOT NULL DEFAULT 'free',
  period_start DATETIME NOT NULL,
  period_end DATETIME NOT NULL,
  budget_cents INT UNSIGNED NOT NULL DEFAULT 0,
  spent_cents INT UNSIGNED NOT NULL DEFAULT 0,
  mode ENUM('normal', 'conservative', 'economy') NOT NULL DEFAULT 'normal',
  last_checked_at 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),
  UNIQUE KEY uq_ai_budget_user_period (user_id, period_start, period_end),
  KEY idx_ai_budget_mode (mode),
  CONSTRAINT fk_ai_budget_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 promo_codes (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  code VARCHAR(60) NOT NULL,
  plan ENUM('pro_weekly', 'pro_monthly', 'pro_6months') NOT NULL,
  promo_amount_cents INT UNSIGNED NOT NULL,
  max_uses INT UNSIGNED NOT NULL DEFAULT 0,
  used_count INT UNSIGNED NOT NULL DEFAULT 0,
  valid_until DATETIME NULL,
  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_promo_codes_code (code),
  KEY idx_promo_codes_plan_active (plan, is_active),
  KEY idx_promo_codes_valid_until (valid_until)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS promo_code_redemptions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  promo_code_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(40) NOT NULL DEFAULT 'stripe',
  provider_session_id VARCHAR(190) NOT NULL,
  plan VARCHAR(40) NOT NULL,
  original_amount_cents INT UNSIGNED NOT NULL,
  paid_amount_cents INT UNSIGNED NOT NULL,
  discount_amount_cents INT UNSIGNED NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_promo_redemption_provider_session (provider, provider_session_id),
  KEY idx_promo_redemptions_code (promo_code_id),
  KEY idx_promo_redemptions_user (user_id),
  CONSTRAINT fk_promo_redemptions_code FOREIGN KEY (promo_code_id) REFERENCES promo_codes(id) ON DELETE CASCADE,
  CONSTRAINT fk_promo_redemptions_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 affiliate_profiles (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  slug VARCHAR(60) NOT NULL,
  promo_code VARCHAR(60) NOT NULL,
  status ENUM('active', 'disabled') NOT NULL DEFAULT 'active',
  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_affiliate_profiles_user (user_id),
  UNIQUE KEY uq_affiliate_profiles_slug (slug),
  UNIQUE KEY uq_affiliate_profiles_promo (promo_code),
  CONSTRAINT fk_affiliate_profiles_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 affiliate_referrals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_profile_id BIGINT UNSIGNED NOT NULL,
  affiliate_user_id BIGINT UNSIGNED NOT NULL,
  referred_user_id BIGINT UNSIGNED NOT NULL,
  source ENUM('link', 'checkout', 'manual', 'promo') NOT NULL DEFAULT 'link',
  first_cookie_code VARCHAR(60) NULL,
  locked_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_affiliate_referrals_referred (referred_user_id),
  KEY idx_affiliate_referrals_profile (affiliate_profile_id),
  KEY idx_affiliate_referrals_affiliate_user (affiliate_user_id),
  CONSTRAINT fk_affiliate_referrals_profile FOREIGN KEY (affiliate_profile_id) REFERENCES affiliate_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_referrals_affiliate_user FOREIGN KEY (affiliate_user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_referrals_referred_user FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS affiliate_clicks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_profile_id BIGINT UNSIGNED NOT NULL,
  affiliate_user_id BIGINT UNSIGNED NOT NULL,
  slug VARCHAR(60) NOT NULL,
  ip_hash CHAR(64) NULL,
  user_agent VARCHAR(500) NULL,
  referer VARCHAR(500) NULL,
  landing_path VARCHAR(255) NULL,
  clicked_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_affiliate_clicks_profile_day (affiliate_profile_id, clicked_at),
  KEY idx_affiliate_clicks_slug_day (slug, clicked_at),
  KEY idx_affiliate_clicks_ip_day (ip_hash, clicked_at),
  CONSTRAINT fk_affiliate_clicks_profile FOREIGN KEY (affiliate_profile_id) REFERENCES affiliate_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_clicks_affiliate_user FOREIGN KEY (affiliate_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS affiliate_commissions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_profile_id BIGINT UNSIGNED NOT NULL,
  affiliate_user_id BIGINT UNSIGNED NOT NULL,
  referred_user_id BIGINT UNSIGNED NOT NULL,
  referral_id BIGINT UNSIGNED NOT NULL,
  provider VARCHAR(40) NOT NULL DEFAULT 'stripe',
  provider_event_id VARCHAR(190) NULL,
  provider_payment_id VARCHAR(190) NULL,
  subscription_id VARCHAR(190) NULL,
  plan VARCHAR(40) NULL,
  gross_amount_cents INT NOT NULL DEFAULT 0,
  net_amount_cents INT NOT NULL DEFAULT 0,
  commission_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
  commission_amount_cents INT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'EUR',
  status ENUM('pending', 'payable', 'paid', 'canceled') NOT NULL DEFAULT 'payable',
  period_month CHAR(7) NOT NULL,
  paid_at DATETIME NULL,
  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),
  UNIQUE KEY uq_affiliate_commission_event (provider, provider_event_id),
  KEY idx_affiliate_commissions_profile_month (affiliate_profile_id, period_month),
  KEY idx_affiliate_commissions_referred (referred_user_id),
  KEY idx_affiliate_commissions_payment (provider_payment_id),
  CONSTRAINT fk_affiliate_commissions_profile FOREIGN KEY (affiliate_profile_id) REFERENCES affiliate_profiles(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_commissions_referral FOREIGN KEY (referral_id) REFERENCES affiliate_referrals(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_commissions_affiliate_user FOREIGN KEY (affiliate_user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_affiliate_commissions_referred_user FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS affiliate_payout_methods (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_profile_id BIGINT UNSIGNED NOT NULL,
  account_holder VARCHAR(160) NULL,
  iban VARCHAR(80) NULL,
  bic VARCHAR(40) NULL,
  country VARCHAR(80) NULL,
  address 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_affiliate_payout_profile (affiliate_profile_id),
  CONSTRAINT fk_affiliate_payout_profile FOREIGN KEY (affiliate_profile_id) REFERENCES affiliate_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS affiliate_payouts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  affiliate_profile_id BIGINT UNSIGNED NOT NULL,
  period_month CHAR(7) NOT NULL,
  amount_cents INT NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'EUR',
  status ENUM('pending', 'paid') NOT NULL DEFAULT 'pending',
  due_date DATE NULL,
  paid_at DATETIME NULL,
  admin_note 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_affiliate_payout_profile_month (affiliate_profile_id, period_month),
  CONSTRAINT fk_affiliate_payouts_profile FOREIGN KEY (affiliate_profile_id) REFERENCES affiliate_profiles(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 support_conversations (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  status ENUM('open', 'closed', 'offline_request') NOT NULL DEFAULT 'open',
  visitor_name VARCHAR(120) NULL,
  visitor_email VARCHAR(190) NULL,
  last_user_message_at DATETIME NULL,
  last_admin_message_at 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_support_conversations_user (user_id),
  KEY idx_support_conversations_status (status, updated_at),
  CONSTRAINT fk_support_conversations_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 support_messages (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  conversation_id BIGINT UNSIGNED NOT NULL,
  sender ENUM('user', 'admin', 'system') NOT NULL DEFAULT 'user',
  body TEXT NOT NULL,
  telegram_message_id BIGINT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_support_messages_conversation (conversation_id, id),
  KEY idx_support_messages_telegram (telegram_message_id),
  CONSTRAINT fk_support_messages_conversation FOREIGN KEY (conversation_id) REFERENCES support_conversations(id) ON DELETE CASCADE
) 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;