-- Migration: Défis hebdomadaires avec récompenses avatars
-- Date: 2026-02-01

-- Table des récompenses hebdomadaires (avatar assigné à chaque semaine)
CREATE TABLE IF NOT EXISTS `weekly_rewards` (
  `week_key` VARCHAR(10) NOT NULL COMMENT 'Format: YYYY-WXX (ex: 2026-W05)',
  `avatar_id` INT NOT NULL COMMENT 'ID de l avatar (1-380)',
  `created_at` TIMESTAMP 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 des avatars déjà distribués (pour éviter les répétitions)
CREATE TABLE IF NOT EXISTS `used_weekly_avatars` (
  `avatar_id` INT NOT NULL COMMENT 'ID de l avatar (1-380)',
  `used_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`avatar_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table de progression hebdomadaire des joueurs
CREATE TABLE IF NOT EXISTS `weekly_progress` (
  `user_id` INT NOT NULL,
  `week_key` VARCHAR(10) 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') DEFAULT NULL COMMENT 'Type de récompense reçue',
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`, `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;

-- Table des avatars possédés par les joueurs
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)',
  `obtained_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `obtained_from` VARCHAR(10) DEFAULT NULL COMMENT 'week_key de la semaine où obtenu',
  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;

-- Index pour optimiser les requêtes
CREATE INDEX `idx_weekly_progress_week` ON `weekly_progress` (`week_key`);
