-- ============================================================================
-- MIGRATION: Add ebook_categories table and category_id to ebooks
-- Version: 002
-- Date: 2026-02-02
-- ============================================================================

-- Create ebook_categories table
CREATE TABLE IF NOT EXISTS `ebook_categories` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `brand_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(120) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `icon` VARCHAR(50) DEFAULT NULL,
    `color` VARCHAR(7) DEFAULT NULL,
    `display_order` INT NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_ebook_cat_slug_brand` (`brand_id`, `slug`),
    KEY `idx_ebook_cat_brand` (`brand_id`),
    KEY `idx_ebook_cat_order` (`display_order`),
    CONSTRAINT `fk_ebook_cat_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add category_id column to ebooks table (if not exists)
SET @column_exists = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'ebooks'
    AND COLUMN_NAME = 'category_id'
);

SET @sql = IF(@column_exists = 0,
    'ALTER TABLE `ebooks` ADD COLUMN `category_id` INT UNSIGNED DEFAULT NULL AFTER `brand_id`',
    'SELECT "Column category_id already exists"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add foreign key constraint (if not exists)
SET @fk_exists = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'ebooks'
    AND CONSTRAINT_NAME = 'fk_ebooks_category'
);

SET @sql = IF(@fk_exists = 0,
    'ALTER TABLE `ebooks` ADD CONSTRAINT `fk_ebooks_category` FOREIGN KEY (`category_id`) REFERENCES `ebook_categories`(`id`) ON DELETE SET NULL',
    'SELECT "Foreign key fk_ebooks_category already exists"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add index on category_id (if not exists)
SET @idx_exists = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'ebooks'
    AND INDEX_NAME = 'idx_ebooks_category'
);

SET @sql = IF(@idx_exists = 0,
    'ALTER TABLE `ebooks` ADD INDEX `idx_ebooks_category` (`category_id`)',
    'SELECT "Index idx_ebooks_category already exists"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Insert default categories for URI (brand_id = 1)
INSERT IGNORE INTO `ebook_categories` (`brand_id`, `name`, `slug`, `description`, `icon`, `color`, `display_order`, `is_active`) VALUES
(1, 'Développement Personnel', 'developpement-personnel', 'Guides et cahiers pour votre croissance personnelle', 'bi-person-fill-up', '#6060b9', 1, 1),
(1, 'Bien-être', 'bien-etre', 'Ressources pour votre équilibre et sérénité', 'bi-heart-fill', '#E85D04', 2, 1),
(1, 'Spiritualité', 'spiritualite', 'Exploration intérieure et éveil spirituel', 'bi-stars', '#9333ea', 3, 1),
(1, 'Relations', 'relations', 'Améliorer vos relations interpersonnelles', 'bi-people-fill', '#ec4899', 4, 1);

-- ============================================================================
-- END OF MIGRATION
-- ============================================================================
