-- ====================================================================
-- Brands module (2026-09-27)
--   1. `brands` + `brand_translations` — same shape as categories /
--      category_translations (multi-language name/slug/description/SEO,
--      Cloudinary public_id for the logo, soft delete).
--   2. `products.brand_id` — optional (NULL = no brand), ON DELETE SET NULL.
--   3. `brands:*` permissions (the standard 11 actions), granted to the
--      super_admin role.
-- Safe to run more than once.
--   npm run db:migrate -- 2026-09-27_brands.sql
-- ====================================================================

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS `brands` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `logo` VARCHAR(500) DEFAULT NULL COMMENT 'Cloudinary public_id (or a full URL)',
    `website_url` VARCHAR(500) DEFAULT NULL,
    `sort_order` INT DEFAULT 0,
    `is_active` BOOLEAN DEFAULT TRUE,
    `is_featured` BOOLEAN DEFAULT FALSE COMMENT 'Reserved for a future homepage brands strip',
    `is_indexable` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,

    INDEX `idx_brands_is_active` (`is_active`),
    INDEX `idx_brands_is_featured` (`is_featured`),
    INDEX `idx_brands_deleted_at` (`deleted_at`),
    INDEX `idx_brands_deleted_created` (`deleted_at`, `created_at`),
    INDEX `idx_brands_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `brand_translations` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `brand_id` VARCHAR(36) NOT NULL,
    `language_code` VARCHAR(10) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `meta_title` VARCHAR(160) DEFAULT NULL,
    `meta_description` TEXT DEFAULT NULL,
    `brand_schemas` JSON DEFAULT NULL,
    `alt_text` VARCHAR(255) DEFAULT NULL COMMENT 'Logo alt text',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT `fk_brand_translations_brand`
        FOREIGN KEY (`brand_id`) REFERENCES `brands`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `uk_brand_translations_brand_lang` (`brand_id`, `language_code`),
    UNIQUE KEY `uk_brand_translations_slug_lang` (`slug`, `language_code`),
    INDEX `idx_brand_translations_brand` (`brand_id`),
    INDEX `idx_brand_translations_language` (`language_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- products.brand_id (nullable — a brand is optional on a product)
SET @has_col = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND COLUMN_NAME = 'brand_id');
SET @sql = IF(@has_col = 0,
  'ALTER TABLE `products` ADD COLUMN `brand_id` VARCHAR(36) NULL DEFAULT NULL AFTER `category_id`, ADD INDEX `idx_brand` (`brand_id`)',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @has_fk = (SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'products' AND CONSTRAINT_NAME = 'fk_products_brand');
SET @sql = IF(@has_fk = 0,
  'ALTER TABLE `products` ADD CONSTRAINT `fk_products_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands`(`id`) ON DELETE SET NULL',
  'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Permissions — same name/action conventions as categories / authors.
INSERT IGNORE INTO `permissions` (`id`, `name`, `module`, `action`) VALUES
  (UUID(), 'brands:read',                'brands', 'read'),
  (UUID(), 'brands:create',              'brands', 'create'),
  (UUID(), 'brands:update',              'brands', 'update'),
  (UUID(), 'brands:delete',              'brands', 'delete'),
  (UUID(), 'brands:bulk-delete',         'brands', 'bulkdelete'),
  (UUID(), 'brands:activate-deactivate', 'brands', 'active_deactive'),
  (UUID(), 'brands:view-deleted',        'brands', 'view_deleted'),
  (UUID(), 'brands:permanent-delete',    'brands', 'delete_permanent'),
  (UUID(), 'brands:restore',             'brands', 'restore'),
  (UUID(), 'brands:bulk-restore',        'brands', 'restore_permanent'),
  (UUID(), 'brands:view-timeline',       'brands', 'view_timeline');

INSERT IGNORE INTO `role_permissions` (`id`, `role_id`, `permission_id`)
SELECT UUID(), r.id, p.id
FROM `roles` r
JOIN `permissions` p ON p.module = 'brands'
WHERE r.name = 'super_admin';
