-- RollerLogic API schema (MySQL)
-- 35 tables — Basé sur un export prod OVH CloudDB du 06/02/2026, mis à jour le 09/02/2026 (pack_avatars)

CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `display_name` varchar(100) DEFAULT NULL,
  `accent` varchar(50) DEFAULT NULL,
  `title` varchar(80) DEFAULT NULL,
  `motto` varchar(200) DEFAULT NULL,
  `guest` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `is_admin` tinyint(1) DEFAULT '0',
  `password_hash` varchar(255) DEFAULT NULL,
  `failed_login_attempts` int DEFAULT '0',
  `locked_until` datetime DEFAULT NULL,
  `reset_token_hash` char(64) DEFAULT NULL,
  `reset_token_expires` datetime DEFAULT NULL,
  `email_verified` tinyint(1) NOT NULL DEFAULT '1',
  `email_verified_at` datetime DEFAULT NULL,
  `email_verify_token_hash` char(64) DEFAULT NULL,
  `email_verify_token_expires` datetime DEFAULT NULL,
  `avatar` varchar(100) DEFAULT NULL,
  `banned_at` datetime DEFAULT NULL,
  `ban_reason` varchar(500) DEFAULT NULL,
  `ban_until` datetime DEFAULT NULL,
  `vip_no_ads` tinyint(1) DEFAULT '0',
  `vip_expires_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_users_email_verify_token_hash` (`email_verify_token_hash`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `wallets` (
  `user_id` int NOT NULL,
  `points` int DEFAULT '0',
  `hints` int DEFAULT '0',
  `undos` int DEFAULT '0',
  `replays` int DEFAULT '0',
  `bonus_points` int DEFAULT '0',
  `last_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `wallets_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `progress` (
  `user_id` int NOT NULL,
  `difficulty` enum('easy','medium','hard','expert') NOT NULL,
  `completed` int DEFAULT '0',
  PRIMARY KEY (`user_id`,`difficulty`),
  CONSTRAINT `progress_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `refresh_tokens` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `token_hash` char(64) NOT NULL,
  `expires_at` datetime NOT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `token_hash` (`token_hash`),
  KEY `idx_refresh_user` (`user_id`),
  KEY `idx_refresh_expires` (`expires_at`),
  CONSTRAINT `refresh_tokens_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `audit_log` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `action` varchar(80) NOT NULL,
  `meta` json DEFAULT NULL,
  `ip` varchar(64) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `installation_id` varchar(64) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_audit_user` (`user_id`),
  KEY `idx_audit_created` (`created_at`),
  KEY `idx_audit_install` (`installation_id`),
  CONSTRAINT `audit_log_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `user_stats` (
  `user_id` int NOT NULL,
  `total_completions` int DEFAULT '0',
  `total_moves` int DEFAULT '0',
  `total_time` int DEFAULT '0',
  `tutorial_completed` tinyint(1) DEFAULT '0',
  `badges` json DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `user_stats_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `level_stats` (
  `user_id` int NOT NULL,
  `difficulty` enum('easy','medium','hard','expert') NOT NULL,
  `level_id` int NOT NULL,
  `completions` int DEFAULT '0',
  `last_moves` int DEFAULT '0',
  `last_time` int DEFAULT '0',
  `best_moves` int DEFAULT NULL,
  `best_time` int DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`difficulty`,`level_id`),
  KEY `idx_level_user` (`user_id`),
  CONSTRAINT `level_stats_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `recent_runs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `difficulty` enum('easy','medium','hard','expert') NOT NULL,
  `level_id` int NOT NULL,
  `moves` int NOT NULL,
  `time` int NOT NULL,
  `completed_at` datetime NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_recent_user` (`user_id`),
  KEY `idx_recent_created` (`created_at`),
  CONSTRAINT `recent_runs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `arcade_stats` (
  `user_id` int NOT NULL,
  `best_score` int DEFAULT '0',
  `best_levels` int DEFAULT '0',
  `last_score` int DEFAULT '0',
  `last_levels` int DEFAULT '0',
  `last_played_at` datetime DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `arcade_stats_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `infinite_stats` (
  `user_id` int NOT NULL,
  `best_levels` int DEFAULT '0',
  `last_levels` int DEFAULT '0',
  `last_played_at` datetime DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `infinite_stats_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `daily_progress` (
  `user_id` int NOT NULL,
  `completed` json DEFAULT NULL,
  `monthly_claims` json DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `daily_progress_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `daily_challenges` (
  `date_key` date NOT NULL,
  `difficulty` varchar(20) DEFAULT 'medium',
  `level_index` int NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`date_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `user_settings` (
  `user_id` int NOT NULL,
  `music` tinyint(1) DEFAULT '1',
  `sfx` tinyint(1) DEFAULT '1',
  `haptics` tinyint(1) DEFAULT '1',
  `theme` varchar(20) DEFAULT 'ocean',
  `animation_speed` varchar(20) DEFAULT 'normal',
  `sfx_volume` decimal(4,2) DEFAULT '0.70',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `music_volume` decimal(4,2) DEFAULT '0.40',
  `ball_skin` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `user_settings_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `user_privacy_consents` (
  `user_id` int NOT NULL,
  `consent_version` varchar(20) NOT NULL,
  `consent_status` varchar(20) NOT NULL,
  `ads_consent` tinyint(1) NOT NULL DEFAULT '0',
  `personalized_ads_consent` tinyint(1) NOT NULL DEFAULT '0',
  `analytics_consent` tinyint(1) NOT NULL DEFAULT '0',
  `consent_source` varchar(40) DEFAULT 'app',
  `installation_id` varchar(64) DEFAULT NULL,
  `ip` varchar(64) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `granted_at` datetime NOT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  KEY `idx_privacy_status` (`consent_status`),
  KEY `idx_privacy_updated` (`updated_at`),
  CONSTRAINT `user_privacy_consents_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `app_config` (
  `config_key` varchar(40) NOT NULL,
  `config_value` text NOT NULL,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `levels_catalog` (
  `id` int NOT NULL AUTO_INCREMENT,
  `difficulty` enum('easy','medium','hard','expert') NOT NULL,
  `level_index` int NOT NULL,
  `level_id` int NOT NULL,
  `seed` int NOT NULL,
  `signature` char(64) NOT NULL,
  `payload` json NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_level` (`difficulty`,`level_index`),
  KEY `idx_level_signature` (`signature`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `leaderboard_entries` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `difficulty` enum('easy','medium','hard','expert') NOT NULL,
  `level_index` int NOT NULL,
  `time_ms` int NOT NULL,
  `moves` int NOT NULL,
  `score` int GENERATED ALWAYS AS (greatest(100,((10000 - (floor((`time_ms` / 1000)) * 100)) - (`moves` * 10)))) STORED,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_level` (`user_id`,`difficulty`,`level_index`),
  KEY `idx_leaderboard_difficulty` (`difficulty`),
  KEY `idx_leaderboard_level` (`difficulty`,`level_index`),
  KEY `idx_leaderboard_user` (`user_id`),
  CONSTRAINT `leaderboard_entries_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `avatars` (
  `id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `category` enum('base','recompense','achat') NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `price` int DEFAULT '0',
  `unlock_condition` json DEFAULT NULL COMMENT 'Conditions de déblocage pour les récompenses',
  `display_order` int DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `idx_avatar_category` (`category`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `user_avatars` (
  `user_id` int NOT NULL,
  `avatar_id` int NOT NULL,
  `source` varchar(50) DEFAULT 'unknown',
  `metadata` json DEFAULT NULL,
  `unlocked_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`avatar_id`),
  KEY `idx_user_avatars_user` (`user_id`),
  KEY `idx_user_avatars_avatar` (`avatar_id`),
  CONSTRAINT `user_avatars_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `user_avatars_ibfk_2` FOREIGN KEY (`avatar_id`) REFERENCES `avatars` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `ad_rewards` (
  `user_id` int NOT NULL,
  `reward_date` date NOT NULL,
  `watched_count` int DEFAULT '0',
  `hints_earned` int DEFAULT '0',
  `bonus_points_earned` int DEFAULT '0',
  `last_watched_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`reward_date`),
  KEY `idx_ad_rewards_date` (`reward_date`),
  CONSTRAINT `ad_rewards_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `ad_reward_events` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `reward_date` date NOT NULL,
  `ad_type` varchar(32) NOT NULL COMMENT 'daily_reward, double_points, free_solution',
  `source` varchar(16) NOT NULL DEFAULT 'ad' COMMENT 'ad, vip',
  `reward_status` varchar(16) NOT NULL DEFAULT 'claimed' COMMENT 'started, claimed, expired, rejected',
  `session_nonce` varchar(96) DEFAULT NULL,
  `idempotency_key` varchar(128) DEFAULT NULL,
  `reward_amount` int NOT NULL DEFAULT '0' COMMENT 'points/hints selon ad_type',
  `watched_count_after` int DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `claimed_at` datetime DEFAULT NULL,
  `meta` json DEFAULT NULL,
  `ip` varchar(45) DEFAULT NULL,
  `user_agent` varchar(255) DEFAULT NULL,
  `installation_id` varchar(64) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ad_reward_events_user_date` (`user_id`,`reward_date`),
  KEY `idx_ad_reward_events_type_date` (`ad_type`,`reward_date`),
  UNIQUE KEY `uniq_ad_reward_events_session` (`session_nonce`),
  UNIQUE KEY `uniq_ad_reward_events_idempotency` (`user_id`,`idempotency_key`),
  CONSTRAINT `ad_reward_events_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `used_weekly_avatars` (
  `avatar_id` int NOT NULL COMMENT 'ID de l avatar (1-380)',
  `used_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`avatar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_weekly_avatars` (
  `user_id` int NOT NULL,
  `avatar_id` int NOT NULL COMMENT 'ID de l avatar animal (1-380)',
  `week_key` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `obtained_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `obtained_from` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`user_id`,`avatar_id`),
  CONSTRAINT `user_weekly_avatars_ibfk_1` 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_challenge_pack` (
  `user_id` int NOT NULL,
  `purchased_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `transaction_id` varchar(255) DEFAULT NULL,
  `price_paid` decimal(10,2) DEFAULT '9.99',
  PRIMARY KEY (`user_id`),
  CONSTRAINT `user_challenge_pack_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `weekly_progress` (
  `user_id` int NOT NULL,
  `week_key` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Format: YYYY-WXX',
  `completed_days` json DEFAULT NULL COMMENT 'Liste des jours complétés [1,2,3...]',
  `reward_claimed` tinyint(1) DEFAULT '0',
  `reward_type` enum('avatar','points') COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Type de récompense reçue',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`week_key`),
  KEY `idx_weekly_progress_week` (`week_key`),
  CONSTRAINT `weekly_progress_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `weekly_rewards` (
  `week_key` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Format: YYYY-WXX (ex: 2026-W05)',
  `avatar_id` int NOT NULL COMMENT 'ID de l avatar (1-380)',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`week_key`),
  UNIQUE KEY `idx_weekly_avatar` (`avatar_id`,`week_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table pour les packs d'avatars achetés (premium)
CREATE TABLE IF NOT EXISTS `avatar_packs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pack_id` varchar(50) NOT NULL COMMENT 'Identifiant unique du pack (ex: cool)',
  `name` varchar(100) NOT NULL COMMENT 'Nom du pack',
  `description` text DEFAULT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `folder_path` varchar(255) NOT NULL COMMENT 'Chemin du dossier des avatars',
  `avatar_count` int DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pack_id` (`pack_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table de liaison: quels avatars appartiennent à chaque pack (utilisé par le webhook Stripe)
CREATE TABLE IF NOT EXISTS `pack_avatars` (
  `pack_id` varchar(50) NOT NULL,
  `avatar_id` int NOT NULL,
  `display_order` int DEFAULT '0',
  PRIMARY KEY (`pack_id`,`avatar_id`),
  KEY `idx_pack_avatars_pack` (`pack_id`),
  KEY `idx_pack_avatars_avatar` (`avatar_id`),
  CONSTRAINT `pack_avatars_ibfk_1` FOREIGN KEY (`pack_id`) REFERENCES `avatar_packs` (`pack_id`) ON DELETE CASCADE,
  CONSTRAINT `pack_avatars_ibfk_2` FOREIGN KEY (`avatar_id`) REFERENCES `avatars` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table pour les achats de packs d'avatars par utilisateur
CREATE TABLE IF NOT EXISTS `user_avatar_packs` (
  `user_id` int NOT NULL,
  `pack_id` varchar(50) NOT NULL,
  `purchased_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `transaction_id` varchar(255) DEFAULT NULL,
  `price_paid` decimal(10,2) NOT NULL,
  PRIMARY KEY (`user_id`,`pack_id`),
  KEY `idx_user_avatar_packs_user` (`user_id`),
  KEY `idx_user_avatar_packs_pack` (`pack_id`),
  CONSTRAINT `user_avatar_packs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table pour les packs de billes (skins)
CREATE TABLE IF NOT EXISTS `ball_skin_packs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pack_id` varchar(50) NOT NULL COMMENT 'Identifiant unique du pack (ex: elementaires)',
  `name` varchar(100) NOT NULL COMMENT 'Nom du pack',
  `description` text DEFAULT NULL,
  `price` decimal(10,2) NOT NULL DEFAULT '0.00',
  `skin_ids` json NOT NULL,
  `skin_count` int DEFAULT '0',
  `is_active` tinyint(1) DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `pack_id` (`pack_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table pour les achats de packs de billes par utilisateur
CREATE TABLE IF NOT EXISTS `user_ball_skin_packs` (
  `user_id` int NOT NULL,
  `pack_id` varchar(50) NOT NULL,
  `purchased_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `transaction_id` varchar(255) DEFAULT NULL,
  `price_paid` decimal(10,2) NOT NULL,
  PRIMARY KEY (`user_id`,`pack_id`),
  KEY `idx_user_ball_packs_user` (`user_id`),
  KEY `idx_user_ball_packs_pack` (`pack_id`),
  CONSTRAINT `user_ball_skin_packs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table pour les skins de billes possédés par utilisateur
CREATE TABLE IF NOT EXISTS `user_ball_skins` (
  `user_id` int NOT NULL,
  `skin_id` varchar(50) NOT NULL,
  `source_pack` varchar(50) DEFAULT NULL,
  `unlocked_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`skin_id`),
  KEY `idx_user_ball_skins_user` (`user_id`),
  CONSTRAINT `user_ball_skins_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table pour le bonus quotidien de connexion
CREATE TABLE IF NOT EXISTS `daily_bonus` (
  `user_id` int NOT NULL,
  `last_claim_date` date DEFAULT NULL,
  `consecutive_days` int DEFAULT '0',
  `total_hints_earned` int DEFAULT '0',
  `total_solutions_earned` int DEFAULT '0',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `daily_bonus_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table pour le bonus arcade
CREATE TABLE IF NOT EXISTS `arcade_bonus` (
  `user_id` int NOT NULL,
  `total_games_played` int DEFAULT '0',
  `last_bonus_at` int DEFAULT '0',
  `total_hints_earned` int DEFAULT '0',
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `arcade_bonus_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table pour les transactions de paiement Stripe
CREATE TABLE IF NOT EXISTS `payment_transactions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `pack_id` varchar(50) NOT NULL,
  `pack_type` varchar(30) NOT NULL DEFAULT 'avatar',
  `provider` varchar(20) NOT NULL,
  `status` varchar(20) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(10) NOT NULL,
  `stripe_session_id` varchar(255) DEFAULT NULL,
  `stripe_payment_intent_id` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_payment_session` (`stripe_session_id`),
  KEY `idx_payment_user` (`user_id`),
  KEY `idx_payment_pack` (`pack_id`),
  CONSTRAINT `fk_payment_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table pour les achats de packs de consommables (hints, undos, replays)
CREATE TABLE IF NOT EXISTS `user_cash_pack_purchases` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `pack_id` varchar(50) NOT NULL,
  `pack_name` varchar(100) NOT NULL,
  `price_eur` decimal(10,2) NOT NULL,
  `items_hints` int DEFAULT '0',
  `items_undos` int DEFAULT '0',
  `items_replays` int DEFAULT '0',
  `bonus_points` int DEFAULT '0',
  `stripe_session_id` varchar(255) DEFAULT NULL,
  `stripe_payment_intent_id` varchar(255) DEFAULT NULL,
  `status` varchar(50) DEFAULT 'pending',
  `purchased_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `processed_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_pack` (`pack_id`),
  KEY `idx_stripe_session` (`stripe_session_id`),
  KEY `idx_status` (`status`),
  CONSTRAINT `user_cash_pack_purchases_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `wallet_history` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `resource` enum('points','hints','undos','replays','bonus_points') NOT NULL,
  `delta` int NOT NULL COMMENT 'Positif = crédit, négatif = débit',
  `balance_before` int NOT NULL DEFAULT 0,
  `balance_after` int NOT NULL DEFAULT 0,
  `source` varchar(80) NOT NULL COMMENT 'wallet.reward, wallet.purchase, daily_bonus, arcade_bonus, ads.reward, ads.double_points, weekly.claim, stripe.cash, wallet.consume',
  `meta` json DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_wh_user` (`user_id`, `created_at`),
  KEY `idx_wh_source` (`source`, `created_at`),
  CONSTRAINT `wallet_history_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
