-- Script pour ajouter le système de packs d'avatars premium
-- À exécuter sur la base de données RollerLogic

-- Table pour les packs d'avatars disponibles à l'achat
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 pour enregistrer 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 de liaison: avatars inclus dans un 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;

-- Insérer le pack d'avatars Cool
INSERT INTO `avatar_packs` (`pack_id`, `name`, `description`, `price`, `folder_path`, `avatar_count`, `is_active`)
VALUES (
  'cool',
  'Pack Avatars Cool',
  'Une collection exclusive de 19 avatars fun et colorés !',
  23.99,
  'avatars/avatard_cool',
  19,
  1
) ON DUPLICATE KEY UPDATE 
  name = VALUES(name),
  description = VALUES(description),
  price = VALUES(price),
  folder_path = VALUES(folder_path),
  avatar_count = VALUES(avatar_count),
  is_active = VALUES(is_active);
