SET FOREIGN_KEY_CHECKS = 0;
SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

CREATE TABLE IF NOT EXISTS permissions (
  id          VARCHAR(36)   PRIMARY KEY DEFAULT (UUID()),
  name        VARCHAR(100)  NOT NULL UNIQUE,
  description TEXT,
  module      VARCHAR(100)  NOT NULL,
  action      VARCHAR(50)   NOT NULL,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `roles` (
  id          VARCHAR(36)   PRIMARY KEY DEFAULT (UUID()),
  name        VARCHAR(100)  NOT NULL UNIQUE COMMENT 'System name / slug (e.g., super-admin)',
  label       VARCHAR(100)  NOT NULL COMMENT 'Display name (e.g., Super Admin)',
  description TEXT,
  is_system   BOOLEAN       NOT NULL DEFAULT 0,
  is_active   BOOLEAN       NOT NULL DEFAULT 1,
  created_by  VARCHAR(36)   NULL,
  updated_by  VARCHAR(36)   NULL,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at  TIMESTAMP     NULL DEFAULT NULL,
  
  -- Check constraints
  CONSTRAINT chk_roles_name_format CHECK (name REGEXP '^[a-z0-9-]+$'),
  CONSTRAINT chk_roles_name_length CHECK (CHAR_LENGTH(name) >= 2),
  CONSTRAINT chk_roles_label_length CHECK (CHAR_LENGTH(label) >= 2),
  
  -- Indexes
  INDEX idx_roles_name (name),
  INDEX idx_roles_is_active (is_active),
  INDEX idx_roles_is_system (is_system),
  INDEX idx_roles_deleted_at (deleted_at),
  INDEX idx_roles_created_by (created_by),
  INDEX idx_roles_updated_by (updated_by),
  
  -- Foreign keys
  CONSTRAINT fk_roles_created_by FOREIGN KEY (created_by) REFERENCES employees(id) ON DELETE SET NULL,
  CONSTRAINT fk_roles_updated_by FOREIGN KEY (updated_by) REFERENCES employees(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `role_permissions` (
  id            VARCHAR(36)  PRIMARY KEY DEFAULT (UUID()),
  role_id       VARCHAR(36)  NOT NULL,
  permission_id VARCHAR(36)  NOT NULL,
  granted_by    VARCHAR(36),
  granted_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,

  UNIQUE KEY uk_role_permission (role_id, permission_id),
  INDEX idx_rp_role       (role_id),
  INDEX idx_rp_permission (permission_id),

  CONSTRAINT fk_rp_role
    FOREIGN KEY (role_id)       REFERENCES roles(id)       ON DELETE CASCADE,
  CONSTRAINT fk_rp_permission
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
  CONSTRAINT fk_rp_granted_by
    FOREIGN KEY (granted_by)    REFERENCES employees(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employees` (
  `id` varchar(36) NOT NULL DEFAULT uuid(),
  `employee_id` varchar(50) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` varchar(20) DEFAULT NULL,
  `work_email` varchar(255) NOT NULL,
  `work_phone` varchar(30) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `avatar_url` varchar(500) DEFAULT NULL,
  `status` varchar(30) NOT NULL DEFAULT 'active',
  `password_hash` varchar(255) DEFAULT NULL,
  `password_changed_at` timestamp NULL DEFAULT NULL,
  `two_fa_enabled` tinyint(1) NOT NULL DEFAULT 0,
  `failed_login_count` int(11) DEFAULT 0,
  `locked_until` timestamp NULL DEFAULT NULL,
  `last_login_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employee_roles` (
  id          VARCHAR(36)  PRIMARY KEY DEFAULT (UUID()),
  employee_id VARCHAR(36)  NOT NULL,
  role_id     VARCHAR(36)  NOT NULL,
  assigned_by VARCHAR(36),
  assigned_at TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at  TIMESTAMP    NULL,

  UNIQUE KEY uk_employee_role      (employee_id, role_id),
  INDEX idx_emp_roles_employee (employee_id),
  INDEX idx_emp_roles_role     (role_id),

  CONSTRAINT fk_er_employee
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_er_role
    FOREIGN KEY (role_id)     REFERENCES roles(id)     ON DELETE CASCADE,
  CONSTRAINT fk_er_assigned_by
    FOREIGN KEY (assigned_by) REFERENCES employees(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employee_permission_overrides` (
  id            VARCHAR(36)  PRIMARY KEY DEFAULT (UUID()),
  employee_id   VARCHAR(36)  NOT NULL,
  permission_id VARCHAR(36)  NOT NULL,
  override_type VARCHAR(10)  NOT NULL,
  granted_by    VARCHAR(36),
  granted_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at    TIMESTAMP    NULL,
  reason        TEXT,

  UNIQUE KEY uk_emp_perm_override (employee_id, permission_id),
  INDEX idx_epo_employee   (employee_id),
  INDEX idx_epo_permission (permission_id),
  INDEX idx_epo_expires    (expires_at),

  CONSTRAINT chk_override_type
    CHECK (override_type IN ('grant','deny')),
  CONSTRAINT fk_epo_employee
    FOREIGN KEY (employee_id)   REFERENCES employees(id)   ON DELETE CASCADE,
  CONSTRAINT fk_epo_permission
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE,
  CONSTRAINT fk_epo_granted_by
    FOREIGN KEY (granted_by)    REFERENCES employees(id)   ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `user_sessions` (
  id             VARCHAR(36)   PRIMARY KEY DEFAULT (UUID()),
  employee_id    VARCHAR(36)   NOT NULL,
  device_info    TEXT,
  ip_address     VARCHAR(45),
  expires_at     DATETIME      NOT NULL,  -- ✅ DATETIME in MariaDB
  revoked_at     DATETIME      NULL,      -- ✅ DATETIME
  revoke_reason  VARCHAR(30)   NULL,
  last_active_at TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  INDEX idx_sessions_employee (employee_id),
  INDEX idx_sessions_token    (token_hash),
  INDEX idx_sessions_active   (expires_at, revoked_at),
  
  CONSTRAINT fk_us_employee
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `two_factor_codes` (
  id          VARCHAR(36)  PRIMARY KEY DEFAULT (UUID()),
  employee_id VARCHAR(36)  NOT NULL UNIQUE, -- one active code per employee
  code_hash   VARCHAR(255) NOT NULL,
  attempts    TINYINT      NOT NULL DEFAULT 0,
  expires_at  DATETIME     NOT NULL,
  created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
 
  INDEX idx_2fa_employee (employee_id),
  INDEX idx_2fa_expires  (expires_at),
 
  CONSTRAINT fk_2fa_employee
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `audit_logs` (
  id          VARCHAR(36)   PRIMARY KEY DEFAULT (UUID()),
  actor_id    VARCHAR(36),
  actor_email VARCHAR(255),
  action      VARCHAR(100)  NOT NULL,         -- already hai CREATE/UPDATE etc
  entity_type VARCHAR(100),
  entity_id   VARCHAR(36),
  table_name  VARCHAR(100),
  location_id VARCHAR(36),
  old_values  JSON,
  new_values  JSON,
  diff        JSON,                           
  version     INT UNSIGNED,                   
  ip_address  VARCHAR(45),
  user_agent  TEXT,
  created_at  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_audit_actor   (actor_id),
  INDEX idx_audit_entity  (entity_type, entity_id),
  INDEX idx_audit_version (entity_type, entity_id, version), 
  INDEX idx_audit_table   (table_name),
  INDEX idx_audit_action  (action),
  INDEX idx_audit_created (created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `currencies` (
    id SERIAL PRIMARY KEY,
    code VARCHAR(10) UNIQUE NOT NULL,
    symbol VARCHAR(10) NOT NULL,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `languages` (
    `id` VARCHAR(36)  PRIMARY KEY DEFAULT (UUID()),
    `code` VARCHAR(10) UNIQUE NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `name_native` VARCHAR(50) NOT NULL,
    `is_rtl` BOOLEAN DEFAULT 0,
    `is_default` BOOLEAN DEFAULT 0,
    `is_active` BOOLEAN DEFAULT 1,
    `created_at` timestamp DEFAULT current_timestamp(),
    `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
    `deleted_at` timestamp NULL DEFAULT NULL,
    INDEX `idx_code` (`code`),
    INDEX `idx_default` (`is_default`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `unit_types` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `is_active` 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,
    
    KEY `idx_unit_types_is_active` (`is_active`),
    KEY `idx_unit_types_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `unit_type_translations` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `unit_type_id` VARCHAR(36) NOT NULL,
    `language_code` VARCHAR(10) NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY `uk_unit_type_lang` (`unit_type_id`, `language_code`),
    KEY `idx_unit_type_translations_unit_type` (`unit_type_id`),
    KEY `idx_unit_type_translations_language` (`language_code`),
    
    -- Foreign key constraints
    CONSTRAINT `fk_unit_type_translations_unit_type` 
        FOREIGN KEY (`unit_type_id`) REFERENCES `unit_types`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `categories` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `parent_id` VARCHAR(36) DEFAULT NULL,
    `icon` VARCHAR(100) DEFAULT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `is_indexable` BOOLEAN DEFAULT TRUE,
    `display_at_home` BOOLEAN DEFAULT TRUE COMMENT 'Reserved for a future storefront products-listing section, not the homepage category carousel',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,

    -- Foreign key constraint
    CONSTRAINT `fk_categories_parent`
        FOREIGN KEY (`parent_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL,

    -- Indexes
    INDEX `idx_categories_parent` (`parent_id`),
    INDEX `idx_categories_is_active` (`is_active`),
    INDEX `idx_categories_is_indexable` (`is_indexable`),
    INDEX `idx_categories_display_at_home` (`display_at_home`),
    INDEX `idx_categories_deleted_at` (`deleted_at`),
    INDEX `idx_categories_deleted_created` (`deleted_at`, `created_at`),
    INDEX `idx_categories_parent_deleted` (`parent_id`, `deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `category_translations` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `category_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,
    `category_schemas` JSON DEFAULT NULL,
    `alt_text` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign key constraints
    CONSTRAINT `fk_category_translations_category` 
        FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE,
    
    -- Unique constraints
    UNIQUE KEY `uk_category_translations_category_lang` (`category_id`, `language_code`),
    UNIQUE KEY `uk_category_translations_slug_lang` (`slug`, `language_code`),
    
    -- Indexes
    INDEX `idx_category_translations_category` (`category_id`),
    INDEX `idx_category_translations_language` (`language_code`),
    INDEX `idx_category_translations_slug` (`slug`),
    INDEX `idx_category_translations_meta_title` (`meta_title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `attributes` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `is_active` BOOLEAN DEFAULT TRUE,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `attribute_translations` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `attribute_id` VARCHAR(36) NOT NULL,
    `language_code` VARCHAR(10) NOT NULL,
    `attribute_name` VARCHAR(100) NOT NULL,
    FOREIGN KEY (`attribute_id`) REFERENCES `attributes`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON DELETE CASCADE,
    UNIQUE KEY `unique_attr_lang` (`attribute_id`, `language_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `attribute_options` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `attribute_id` VARCHAR(36) NOT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`attribute_id`) REFERENCES `attributes`(`id`) ON DELETE CASCADE,
    INDEX `idx_attribute` (`attribute_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `attribute_option_translations` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `option_id` VARCHAR(36) NOT NULL,
    `language_code` VARCHAR(10) NOT NULL,
    `value` VARCHAR(100) NOT NULL,
    FOREIGN KEY (`option_id`) REFERENCES `attribute_options`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON DELETE CASCADE,
    UNIQUE KEY `unique_option_lang` (`option_id`, `language_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `product_tags` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `slug` VARCHAR(100) UNIQUE NOT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_slug` (`slug`),
    INDEX `idx_active` (`is_active`),
    INDEX `idx_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `product_tag_translations` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `tag_id` VARCHAR(36) NOT NULL,
    `language_code` VARCHAR(10) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    
    UNIQUE KEY `unique_tag_lang` (`tag_id`, `language_code`),
    UNIQUE KEY `unique_name_lang` (`name`, `language_code`),
    FOREIGN KEY (`tag_id`) REFERENCES `product_tags`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON DELETE CASCADE,
    INDEX `idx_tag_language` (`language_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `products` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `sku` VARCHAR(100) UNIQUE NOT NULL,
    `type` ENUM('simple', 'variable') DEFAULT 'simple',
    `parent_id` VARCHAR(36) NULL,
    
    -- Category
    `category_id` VARCHAR(36) NULL,
    
    -- Pricing
    `price` DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    `compare_price` DECIMAL(15,2) NULL,
    `cost_price` DECIMAL(15,2) NULL,
    
    -- Inventory
    `stock_quantity` INT DEFAULT 0,
    `stock_status` ENUM('in_stock', 'out_of_stock', 'backorder') DEFAULT 'out_of_stock',
    `min_stock_threshold` INT DEFAULT 0,
    `allow_backorder` BOOLEAN DEFAULT FALSE,
    
    -- Status & Visibility
    `is_active` BOOLEAN DEFAULT TRUE,
    `is_featured` BOOLEAN DEFAULT FALSE,
    `is_indexable` BOOLEAN DEFAULT TRUE,
    `visibility` ENUM('visible', 'catalog', 'search', 'hidden') DEFAULT 'visible',
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    
    -- Meta
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`parent_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    INDEX `idx_sku` (`sku`),
    INDEX `idx_category` (`category_id`),
    INDEX `idx_parent` (`parent_id`),
    INDEX `idx_active` (`is_active`),
    INDEX `idx_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `product_translations` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `product_id` VARCHAR(36) NOT NULL,
    `language_code` VARCHAR(10) NOT NULL,
    
    -- Content
    `name` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL,
    `short_description` TEXT NULL,
    `description` LONGTEXT NULL,
    `excerpt` TEXT NULL,
    
    -- SEO
    `meta_title` VARCHAR(160) NULL,
    `meta_description` VARCHAR(320) NULL,
    `meta_keywords` VARCHAR(255) NULL,
    
    -- Schema
    `product_schemas` JSON NULL,
    
    UNIQUE KEY `unique_product_lang` (`product_id`, `language_code`),
    UNIQUE KEY `unique_slug_lang` (`slug`, `language_code`),
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON DELETE CASCADE,
    INDEX `idx_slug` (`slug`),
    INDEX `idx_language` (`language_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `product_images` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `product_id` VARCHAR(36) NOT NULL,
    `image_url` VARCHAR(500) NOT NULL,
    `cloudinary_public_id` VARCHAR(255) NOT NULL,
    
    `position` INT DEFAULT 0,
    `is_primary` BOOLEAN DEFAULT FALSE,
    `is_active` BOOLEAN DEFAULT TRUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    INDEX `idx_product_images` (`product_id`),
    INDEX `idx_primary` (`is_primary`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `product_image_alt_translations` (
    `image_id` VARCHAR(36) NOT NULL,
    `language_code` VARCHAR(10) NOT NULL,
    `alt_text` VARCHAR(255) NOT NULL,
    
    PRIMARY KEY (`image_id`, `language_code`),
    FOREIGN KEY (`image_id`) REFERENCES `product_images`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON DELETE CASCADE,
    INDEX `idx_image_alt` (`image_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `product_attributes` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `product_id` VARCHAR(36) NOT NULL,
    `attribute_id` VARCHAR(36) NOT NULL,
    `attribute_option_id` VARCHAR(36) NULL,
    
    -- Custom value (if not from predefined options)
    `custom_value` VARCHAR(255) NULL,
    
    -- Meta
    `is_visible` BOOLEAN DEFAULT TRUE,
    `is_variation` BOOLEAN DEFAULT FALSE,
    `position` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`attribute_id`) REFERENCES `attributes`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`attribute_option_id`) REFERENCES `attribute_options`(`id`) ON DELETE SET NULL,
    INDEX `idx_product_attribute` (`product_id`, `attribute_id`),
    UNIQUE KEY `unique_product_attribute_option` (`product_id`, `attribute_id`, `attribute_option_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `product_variations` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `product_id` VARCHAR(36) NOT NULL,
    `variation_sku` VARCHAR(100) UNIQUE NOT NULL,
    
    -- Pricing
    `price` DECIMAL(15,2) NOT NULL,
    `compare_price` DECIMAL(15,2) NULL,
    `cost_price` DECIMAL(15,2) NULL,
    
    -- Inventory
    `stock_quantity` INT DEFAULT 0,
    `stock_status` ENUM('in_stock', 'out_of_stock', 'backorder') DEFAULT 'out_of_stock',
    `min_stock_threshold` INT DEFAULT 0,
    `allow_backorder` BOOLEAN DEFAULT FALSE,
    
    -- Status
    `is_active` BOOLEAN DEFAULT TRUE,
    `is_indexable` BOOLEAN DEFAULT TRUE,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    INDEX `idx_variation_sku` (`variation_sku`),
    INDEX `idx_product_variations` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `product_variation_attributes` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `variation_id` VARCHAR(36) NOT NULL,
    `attribute_id` VARCHAR(36) NOT NULL,
    `attribute_option_id` VARCHAR(36) NOT NULL,
    
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`variation_id`) REFERENCES `product_variations`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`attribute_id`) REFERENCES `attributes`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`attribute_option_id`) REFERENCES `attribute_options`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_variation_attribute` (`variation_id`, `attribute_id`),
    INDEX `idx_variation_attributes` (`variation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `product_faqs` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `product_id` VARCHAR(36) NOT NULL,
    `position` INT DEFAULT 0,
    `is_active` BOOLEAN DEFAULT TRUE,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    INDEX `idx_product_faq` (`product_id`),
    INDEX `idx_active_faq` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `product_faq_translations` (
    `faq_id` VARCHAR(36) NOT NULL,
    `language_code` VARCHAR(10) NOT NULL,
    `question` VARCHAR(500) NOT NULL,
    `answer` LONGTEXT NOT NULL,
    
    PRIMARY KEY (`faq_id`, `language_code`),
    FOREIGN KEY (`faq_id`) REFERENCES `product_faqs`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON DELETE CASCADE,
    INDEX `idx_faq_language` (`language_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `product_tag_mappings` (
    `product_id` VARCHAR(36) NOT NULL,
    `tag_id` VARCHAR(36) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (`product_id`, `tag_id`),
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`tag_id`) REFERENCES `product_tags`(`id`) ON DELETE CASCADE,
    INDEX `idx_product_tags` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `product_reviews` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `product_id` VARCHAR(36) NOT NULL,
    `user_id` VARCHAR(36) NULL,
    `guest_name` VARCHAR(100) NULL,
    `guest_email` VARCHAR(100) NULL,

    `rating` TINYINT NOT NULL CHECK (rating >= 1 AND rating <= 5),
    `title` VARCHAR(255) NULL,
    `content` TEXT NOT NULL,

    -- Moderation
    `status` ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    -- Admin-curated: which approved reviews show as homepage testimonials
    -- (src/components/frontend/home/TestimonialsSection.tsx). Only meaningful
    -- when status = 'approved' — enforced at the application layer, not here.
    `display_on_home` BOOLEAN NOT NULL DEFAULT FALSE,

    -- Meta
    `is_verified` BOOLEAN DEFAULT FALSE,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` VARCHAR(255) NULL,

    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,

    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_product_rating` (`product_id`, `rating`),
    INDEX `idx_status` (`status`),
    INDEX `idx_display_on_home` (`display_on_home`),
    INDEX `idx_reviews_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `users` (
    `id` VARCHAR(100) PRIMARY KEY NOT NULL DEFAULT (UUID()),
    `name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(100) UNIQUE NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `is_active` TINYINT(1) DEFAULT 1,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL COMMENT 'Soft-delete for the admin Users module — same lifecycle as every other admin-manageable entity. Permanent delete (hard DELETE) is blocked at the DB level by orders.user_id ON DELETE RESTRICT for any user with real order history.',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `phone` VARCHAR(36) DEFAULT NULL,
    `email_verified` TINYINT(1) NOT NULL DEFAULT 0,
    `email_verified_at` DATETIME NULL DEFAULT NULL,
    `failed_login_count` INT NOT NULL DEFAULT 0,
    `locked_until` DATETIME NULL DEFAULT NULL,
    `last_login_at` DATETIME NULL DEFAULT NULL,
    `referral_code` VARCHAR(20) UNIQUE DEFAULT NULL COMMENT 'This user''s own code to share; generated lazily on first /account/referrals visit',
    `referred_by` VARCHAR(36) DEFAULT NULL COMMENT 'user_id of whoever referred this account, captured at registration via ?ref=CODE, never changes after',
    FOREIGN KEY (`referred_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
    INDEX `idx_users_deleted_at` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Referral commission ledger — one row per *referrer's first-order*
-- qualifying event (UNIQUE on order_id, since only a referred user's first
-- order ever creates one of these). `status` tracks the underlying order:
-- 'pending' while the order is still in flight, 'confirmed' once it's
-- delivered (only confirmed earnings count toward a withdrawable balance),
-- 'cancelled' if the order itself gets cancelled/returned/refunded.
CREATE TABLE IF NOT EXISTS `referral_earnings` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `referrer_id` VARCHAR(36) NOT NULL,
    `referred_user_id` VARCHAR(36) NOT NULL,
    `order_id` VARCHAR(36) NOT NULL,
    `order_amount` DECIMAL(12,2) NOT NULL,
    `commission_amount` DECIMAL(12,2) NOT NULL,
    `status` ENUM('pending','confirmed','cancelled') DEFAULT 'pending',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `confirmed_at` TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (`referrer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`referred_user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_order_earning` (`order_id`),
    INDEX `idx_referrer_status` (`referrer_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A customer's request to cash out their confirmed referral balance —
-- admin reviews and approves/rejects/marks-paid (see the new admin
-- Withdrawal Requests module), matching how Bank Transfer order payments
-- already get manually verified by an admin rather than auto-processed.
CREATE TABLE IF NOT EXISTS `withdrawal_requests` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `user_id` VARCHAR(36) NOT NULL,
    `amount` DECIMAL(12,2) NOT NULL,
    `bank_name` VARCHAR(100) NOT NULL,
    `account_title` VARCHAR(100) NOT NULL,
    `account_number` VARCHAR(50) NOT NULL,
    `status` ENUM('pending','approved','rejected','paid') DEFAULT 'pending',
    `admin_notes` TEXT DEFAULT NULL,
    `requested_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `processed_at` TIMESTAMP NULL DEFAULT NULL,
    `processed_by` VARCHAR(36) DEFAULT NULL,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`processed_by`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
    INDEX `idx_user_status` (`user_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Storefront /contact form submissions (POST /api/frontend/contact). Guests
-- can submit, so there's deliberately no FK to `users`.
CREATE TABLE IF NOT EXISTS `contact_messages` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(150) NOT NULL,
    `subject` VARCHAR(50) NOT NULL,
    `message` TEXT NOT NULL,
    `status` ENUM('new','read','replied') NOT NULL DEFAULT 'new',
    `ip_address` VARCHAR(45) NULL DEFAULT NULL,
    `user_agent` VARCHAR(500) NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_contact_messages_status` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `customer_sessions` (
    `id` VARCHAR(100) NOT NULL,
    `user_id` VARCHAR(100) NOT NULL,
    `device_info` TEXT NULL DEFAULT NULL,
    `ip_address` VARCHAR(45) NULL DEFAULT NULL,
    `expires_at` DATETIME NOT NULL,
    `revoked_at` DATETIME NULL DEFAULT NULL,
    `revoke_reason` VARCHAR(30) NULL DEFAULT NULL,
    `last_active_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_customer_sessions_user` (`user_id`),
    CONSTRAINT `fk_customer_sessions_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Handles both email-verification OTP codes and password-reset tokens via
-- `purpose`, mirroring how `two_factor_codes` stores employee 2FA codes.
CREATE TABLE IF NOT EXISTS `customer_verification_tokens` (
    `id` VARCHAR(36) NOT NULL DEFAULT (UUID()),
    `user_id` VARCHAR(100) NOT NULL,
    `purpose` ENUM('email_verify', 'password_reset') NOT NULL,
    `code_hash` VARCHAR(255) NOT NULL,
    `attempts` TINYINT(4) NOT NULL DEFAULT 0,
    `expires_at` DATETIME NOT NULL,
    `used_at` DATETIME NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_cvt_user_purpose` (`user_id`, `purpose`),
    CONSTRAINT `fk_cvt_user`
        FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `coupons` (
    `id` VARCHAR(100) PRIMARY KEY NOT NULL DEFAULT (UUID()),
    `code` VARCHAR(50) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `type` ENUM('percentage','fixed') NOT NULL,
    `value` DECIMAL(10,2) NOT NULL,
    `min_order_amount` DECIMAL(10,2) DEFAULT 0.00,
    `max_discount` DECIMAL(10,2) DEFAULT NULL,
    `usage_limit` INT(11) DEFAULT NULL,
    `usage_limit_per_user` INT(11) DEFAULT 1,
    `valid_from` DATETIME NOT NULL,
    `valid_until` DATETIME NOT NULL,
    `assignment_type` ENUM('public','manual') DEFAULT 'public',
    `is_offer` TINYINT(1) DEFAULT 0 COMMENT '1 = Show as promotion/offer on frontend',
    `is_featured` TINYINT(1) DEFAULT 0 COMMENT '1 = Featured on homepage',
    `is_active` TINYINT(1) DEFAULT 1,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_code` (`code`),
    INDEX `idx_dates` (`valid_from`, `valid_until`),
    INDEX `idx_active_dates` (`is_active`, `valid_from`, `valid_until`),
    INDEX `idx_assignment_type` (`assignment_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `coupon_translations` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `coupon_id` VARCHAR(36) NOT NULL,
    `language_code` VARCHAR(10) NOT NULL,
    
    -- Display text for offers
    `offer_title` VARCHAR(200) DEFAULT NULL COMMENT 'Localized offer title',
    `offer_badge` VARCHAR(50) DEFAULT NULL COMMENT 'Localized badge text',
    `offer_description` TEXT DEFAULT NULL COMMENT 'Localized offer description',
    
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`coupon_id`) REFERENCES `coupons`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON DELETE CASCADE,
    
    UNIQUE KEY `unique_coupon_lang` (`coupon_id`, `language_code`),
    INDEX `idx_coupon_lang` (`language_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `coupon_applicable_items` (
    `id` VARCHAR(100) PRIMARY KEY NOT NULL DEFAULT (UUID()),
    `coupon_id` VARCHAR(100) NOT NULL,
    `applicable_type` ENUM('all','category','product','variant') NOT NULL,
    `applicable_id` VARCHAR(100) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`coupon_id`) REFERENCES `coupons`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_coupon_item` (`coupon_id`, `applicable_type`, `applicable_id`),
    INDEX `idx_applicable_lookup` (`applicable_type`, `applicable_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `coupon_assignments` (
    `id` VARCHAR(100) PRIMARY KEY NOT NULL DEFAULT (UUID()),
    `coupon_id` VARCHAR(100) NOT NULL,
    `user_id` VARCHAR(100) NOT NULL,
    `assigned_by` VARCHAR(100) DEFAULT NULL,
    `assigned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `expires_at` DATETIME DEFAULT NULL,
    `usage_limit` INT(11) DEFAULT 1,
    `used_count` INT(11) DEFAULT 0,
    `is_used` TINYINT(1) DEFAULT 0,
    `last_used_at` TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (`coupon_id`) REFERENCES `coupons`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`assigned_by`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
    UNIQUE KEY `unique_coupon_user` (`coupon_id`, `user_id`),
    INDEX `idx_user` (`user_id`),
    INDEX `idx_usage` (`is_used`, `expires_at`),
    INDEX `idx_assigned` (`assigned_by`, `assigned_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `coupon_usage_log` (
    `id` VARCHAR(100) PRIMARY KEY NOT NULL DEFAULT (UUID()),
    `coupon_id` VARCHAR(100) NOT NULL,
    `user_id` VARCHAR(100) NOT NULL,
    `assignment_id` VARCHAR(100) DEFAULT NULL,
    `order_id` VARCHAR(100) DEFAULT NULL,
    `order_amount` DECIMAL(10,2) DEFAULT NULL,
    `discount_amount` DECIMAL(10,2) NOT NULL,
    `status` ENUM('applied','reverted','failed') DEFAULT 'applied',
    `applied_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `reverted_at` TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (`coupon_id`) REFERENCES `coupons`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`assignment_id`) REFERENCES `coupon_assignments`(`id`) ON DELETE SET NULL,
    INDEX `idx_coupon_usage` (`coupon_id`, `status`),
    INDEX `idx_user_usage` (`user_id`, `applied_at`),
    INDEX `idx_order` (`order_id`),
    INDEX `idx_applied_date` (`applied_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orders` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `order_number` VARCHAR(50) UNIQUE NOT NULL,
    `user_id` VARCHAR(36) NOT NULL COMMENT 'Always has user (auto-created for guests)',
    
    -- Status
    `status` ENUM(
        'pending', 'confirmed', 'shipped', 'delivered', 
        'cancelled', 'returned', 'refunded'
    ) DEFAULT 'pending',
    
    -- Payment
    `payment_method` ENUM('cod', 'bank_transfer', 'paypal', 'stripe') NOT NULL,
    `payment_status` ENUM('pending', 'paid', 'failed', 'refunded', 'partially_refunded') DEFAULT 'pending',
    
    -- Financials (Summary - Runtime calculated from order_items)
    `delivery_fee` DECIMAL(12,2) DEFAULT 0.00,
    `tax_amount` DECIMAL(12,2) DEFAULT 0.00 COMMENT 'VAT/GST computed from site_settings.vat_percentage at order time',
    `tax_percentage` DECIMAL(5,2) DEFAULT 0.00 COMMENT 'Snapshot of site_settings.vat_percentage used for tax_amount',
    `currency` VARCHAR(3) DEFAULT 'PKR',
    
    -- Coupon
    `coupon_id` VARCHAR(36) DEFAULT NULL,
    `coupon_code` VARCHAR(50) DEFAULT NULL COMMENT 'Snapshot of coupon code used',
    `coupon_discount_amount` DECIMAL(12,2) DEFAULT 0.00 COMMENT 'Coupon discount applied to whole order',
    
    -- Shipping Address (NULL if same as billing)
    `shipping_full_name` VARCHAR(100) DEFAULT NULL,
    `shipping_phone` VARCHAR(20) DEFAULT NULL,
    `shipping_address_line1` VARCHAR(255) DEFAULT NULL,
    `shipping_address_line2` VARCHAR(255) DEFAULT NULL,
    `shipping_city` VARCHAR(100) DEFAULT NULL,
    `shipping_state` VARCHAR(100) DEFAULT NULL,
    `shipping_postal_code` VARCHAR(20) DEFAULT NULL,
    `shipping_country` VARCHAR(100) DEFAULT 'Pakistan',
    `shipping_landmark` VARCHAR(255) DEFAULT NULL,
    
    -- Cancellation (Whole order cancel)
    `cancellation_reason` TEXT DEFAULT NULL,
    `cancelled_at` TIMESTAMP NULL DEFAULT NULL,

    -- Soft delete
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    
    -- Notes
    `admin_notes` TEXT DEFAULT NULL,
    `customer_notes` TEXT DEFAULT NULL COMMENT 'Includes delivery instructions, special requests etc.',
    
    -- Meta
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Foreign Keys
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`coupon_id`) REFERENCES `coupons`(`id`) ON DELETE SET NULL,
    
    -- Indexes
    UNIQUE INDEX `idx_order_number` (`order_number`),
    INDEX `idx_user` (`user_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_payment_status` (`payment_status`),
    INDEX `idx_deleted_at` (`deleted_at`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_status_created` (`status`, `created_at`),
    INDEX `idx_user_created` (`user_id`, `created_at`),
    INDEX `idx_coupon` (`coupon_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `order_items` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `order_id` VARCHAR(36) NOT NULL,
    
    -- Product reference
    `product_id` VARCHAR(36) NOT NULL,
    `variant_id` VARCHAR(36) DEFAULT NULL COMMENT 'NULL for simple products',
    
    -- Snapshots (data remains even if product is deleted/modified)
    `product_name` VARCHAR(255) NOT NULL,
    `sku` VARCHAR(100) NOT NULL,
    `variant_name` VARCHAR(255) DEFAULT NULL COMMENT 'e.g., "Red / XL"',
    `variant_sku` VARCHAR(100) DEFAULT NULL,
    `attributes_snapshot` JSON DEFAULT NULL COMMENT '[{"name":"Color","value":"Red"},{"name":"Size","value":"XL"}]',
    `image_url` VARCHAR(500) DEFAULT NULL,
    
    -- Pricing (at time of order)
    `quantity` INT NOT NULL DEFAULT 1,
    `unit_price` DECIMAL(12,2) NOT NULL,
    `compare_price` DECIMAL(12,2) DEFAULT NULL COMMENT 'MRP for showing savings',
    `subtotal` DECIMAL(12,2) NOT NULL COMMENT 'unit_price * quantity',
    
    -- Discount
    `discount_amount` DECIMAL(12,2) DEFAULT 0.00 COMMENT 'Item-level discount',
    
    -- Tax
    `tax_amount` DECIMAL(12,2) DEFAULT 0.00,
    `tax_percentage` DECIMAL(5,2) DEFAULT 0.00,
    
    -- Final price for this item
    `total_price` DECIMAL(12,2) NOT NULL COMMENT 'subtotal - discount + tax',
    
    -- Return tracking (per item)
    `is_returned` BOOLEAN DEFAULT FALSE,
    `return_quantity` INT DEFAULT 0,
    `return_reason` TEXT DEFAULT NULL,
    `return_requested_at` TIMESTAMP NULL DEFAULT NULL,
    `return_completed_at` TIMESTAMP NULL DEFAULT NULL,
    
    -- Meta
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Foreign Keys
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE RESTRICT,
    FOREIGN KEY (`variant_id`) REFERENCES `product_variations`(`id`) ON DELETE SET NULL,
    
    -- Indexes
    INDEX `idx_order` (`order_id`),
    INDEX `idx_product` (`product_id`),
    INDEX `idx_variant` (`variant_id`),
    INDEX `idx_return` (`is_returned`),
    INDEX `idx_order_return` (`order_id`, `is_returned`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `order_payment_history` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `order_id` VARCHAR(36) NOT NULL,
    `payment_method` VARCHAR(50) NOT NULL,
    `transaction_id` VARCHAR(100) DEFAULT NULL COMMENT 'Gateway transaction ID',
    `amount` DECIMAL(12,2) NOT NULL,
    `status` ENUM('pending', 'success', 'failed', 'refunded') DEFAULT 'pending',
    `gateway_response` JSON DEFAULT NULL COMMENT 'Full response from payment gateway',
    `note` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL AFTER `screenshot_url`,
    `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP AFTER `deleted_at`,
    
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    INDEX `idx_order_payments` (`order_id`),
    INDEX `idx_transaction_id` (`transaction_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `order_payment_history_log` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `payment_id` VARCHAR(36) NOT NULL,
    `order_id` VARCHAR(36) NOT NULL,
    `action` ENUM('CREATE', 'UPDATE', 'DELETE', 'RESTORE', 'PERMANENT_DELETE') NOT NULL,
    `changed_by` VARCHAR(36) DEFAULT NULL COMMENT 'Employee ID',
    `old_values` JSON DEFAULT NULL COMMENT 'Before changes',
    `new_values` JSON DEFAULT NULL COMMENT 'After changes',
    `diff` JSON DEFAULT NULL COMMENT 'What changed',
    `note` TEXT DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`payment_id`) REFERENCES `order_payment_history`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`changed_by`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_payment_log` (`payment_id`),
    INDEX `idx_order_log` (`order_id`),
    INDEX `idx_action` (`action`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `order_status_history` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `order_id` VARCHAR(36) NOT NULL,
    `old_status` VARCHAR(50) DEFAULT NULL COMMENT 'NULL for first status (pending)',
    `new_status` VARCHAR(50) NOT NULL,
    `changed_by` VARCHAR(36) DEFAULT NULL COMMENT 'Employee ID who changed',
    `note` TEXT DEFAULT NULL COMMENT 'Reason/Note for status change',
    `is_customer_visible` BOOLEAN DEFAULT TRUE COMMENT 'Show to customer in tracking?',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`changed_by`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_order_history` (`order_id`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_order_status` (`order_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `order_invoices` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `order_id` VARCHAR(36) NOT NULL,
    `invoice_number` VARCHAR(50) UNIQUE NOT NULL,
    `generated_by` VARCHAR(36) DEFAULT NULL COMMENT 'Employee ID',
    `generated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`generated_by`) REFERENCES `employees`(`id`) ON DELETE SET NULL,
    INDEX `idx_order_invoice` (`order_id`),
    INDEX `idx_invoice_number` (`invoice_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `carts` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `user_id` VARCHAR(36) DEFAULT NULL COMMENT 'NULL for guest users',
    `session_id` VARCHAR(100) DEFAULT NULL COMMENT 'For guest users',
    `coupon_code` VARCHAR(50) DEFAULT NULL COMMENT 'Applied coupon',
    `coupon_discount` DECIMAL(12,2) DEFAULT 0.00,
    `notes` TEXT DEFAULT NULL,
    `last_reminder_sent_at` TIMESTAMP NULL DEFAULT NULL,
    `reminder_count` INT DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_user` (`user_id`),
    INDEX `idx_session` (`session_id`),
    UNIQUE KEY `uk_user_cart` (`user_id`) COMMENT 'One cart per logged-in user'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `cart_items` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `cart_id` VARCHAR(36) NOT NULL,
    `product_id` VARCHAR(36) NOT NULL,
    `variant_id` VARCHAR(36) DEFAULT NULL COMMENT 'NULL for simple products',
    
    -- Product snapshot (so price/name changes don't affect cart)
    `product_name` VARCHAR(255) NOT NULL,
    `sku` VARCHAR(100) NOT NULL,
    `variant_name` VARCHAR(255) DEFAULT NULL,
    `variant_sku` VARCHAR(100) DEFAULT NULL,
    `image_url` VARCHAR(500) DEFAULT NULL,
    
    -- Pricing
    `quantity` INT NOT NULL DEFAULT 1,
    `unit_price` DECIMAL(12,2) NOT NULL COMMENT 'Price at time of adding to cart',
    `compare_price` DECIMAL(12,2) DEFAULT NULL,
    `subtotal` DECIMAL(12,2) NOT NULL COMMENT 'unit_price * quantity',
    
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`cart_id`) REFERENCES `carts`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`variant_id`) REFERENCES `product_variations`(`id`) ON DELETE SET NULL,
    INDEX `idx_cart` (`cart_id`),
    INDEX `idx_product` (`product_id`),
    INDEX `idx_variant` (`variant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wishlists` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `user_id` VARCHAR(36) DEFAULT NULL COMMENT 'NULL for guest users',
    `session_id` VARCHAR(100) DEFAULT NULL COMMENT 'For guest users',
    `product_id` VARCHAR(36) NOT NULL,
    `variant_id` VARCHAR(36) DEFAULT NULL COMMENT 'NULL for simple products',
    `product_name` VARCHAR(255) DEFAULT NULL COMMENT 'Snapshot',
    `sku` VARCHAR(100) DEFAULT NULL COMMENT 'Snapshot',
    `unit_price` DECIMAL(12,2) DEFAULT NULL COMMENT 'Snapshot',
    `image_url` VARCHAR(500) DEFAULT NULL COMMENT 'Snapshot',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `last_reminder_sent_at` TIMESTAMP NULL DEFAULT NULL,
    `reminder_count` INT DEFAULT 0,
    
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`variant_id`) REFERENCES `product_variations`(`id`) ON DELETE CASCADE,
    
    UNIQUE KEY `unique_wishlist` (`user_id`, `session_id`, `product_id`, `variant_id`),
    INDEX `idx_user` (`user_id`),
    INDEX `idx_session` (`session_id`),
    INDEX `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `banners` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `placement` ENUM('home_top', 'home_middle', 'home_bottom', 'sidebar', 'category_page') DEFAULT 'home_top',
    `link_url` VARCHAR(500) DEFAULT NULL,
    `position` INT DEFAULT 0,
    `is_active` TINYINT(1) DEFAULT 1,
    `is_hero_banner` TINYINT(1) DEFAULT 0 COMMENT 'Shown in the storefront homepage hero carousel',
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX `idx_banner_active` (`is_active`),
    INDEX `idx_banner_placement` (`placement`),
    INDEX `idx_banner_deleted` (`deleted_at`),
    INDEX `idx_banner_hero` (`is_hero_banner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `banner_images` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `banner_id` VARCHAR(36) NOT NULL,
    `language_code` VARCHAR(10) NOT NULL,
    `image` VARCHAR(500) NOT NULL COMMENT 'Cloudinary public_id',
    `alt_text` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`banner_id`) REFERENCES `banners`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON DELETE CASCADE,
    
    UNIQUE KEY `unique_banner_lang` (`banner_id`, `language_code`),
    INDEX `idx_banner_lang` (`language_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `bank_accounts` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `bank_name` VARCHAR(100) NOT NULL,
    `account_title` VARCHAR(100) NOT NULL,
    `account_number` VARCHAR(50) NOT NULL,
    `iban` VARCHAR(50) DEFAULT NULL,
    `branch_code` VARCHAR(20) DEFAULT NULL,
    `swift_code` VARCHAR(20) DEFAULT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `sort_order` INT DEFAULT 0,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX `idx_bank_accounts_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `pages` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `is_active` TINYINT(1) DEFAULT 1,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_pages_active` (`is_active`),
    INDEX `idx_pages_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `page_translations` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `page_id` VARCHAR(36) NOT NULL,
    `language_code` VARCHAR(10) NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `content` LONGTEXT DEFAULT NULL,
    `meta_title` VARCHAR(200) DEFAULT NULL,
    `meta_description` VARCHAR(300) DEFAULT NULL,
    `page_schemas` JSON DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`page_id`) REFERENCES `pages`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON DELETE CASCADE,
    
    UNIQUE KEY `unique_page_lang` (`page_id`, `language_code`),
    UNIQUE KEY `unique_slug_lang` (`slug`, `language_code`),
    INDEX `idx_page_lang` (`language_code`),
    INDEX `idx_page_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `posts` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `category_id` VARCHAR(36) DEFAULT NULL COMMENT 'Direct category reference',
    `image` VARCHAR(500) DEFAULT NULL COMMENT 'Cloudinary public_id for featured image',
    `is_active` TINYINT(1) DEFAULT 1,
    `is_featured` TINYINT(1) DEFAULT 0 COMMENT 'Featured on blog homepage',
    `published_at` TIMESTAMP NULL DEFAULT NULL COMMENT 'NULL = draft',
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL,
    
    INDEX `idx_posts_active` (`is_active`),
    INDEX `idx_posts_featured` (`is_featured`),
    INDEX `idx_posts_published` (`published_at`),
    INDEX `idx_posts_category` (`category_id`),
    INDEX `idx_posts_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `post_translations` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `post_id` VARCHAR(36) NOT NULL,
    `language_code` VARCHAR(10) NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `slug` VARCHAR(200) NOT NULL,
    `excerpt` TEXT DEFAULT NULL COMMENT 'Short summary',
    `content` LONGTEXT DEFAULT NULL,
    `alt_text` VARCHAR(255) DEFAULT NULL COMMENT 'Alt text for featured image',
    `meta_title` VARCHAR(200) DEFAULT NULL,
    `meta_description` VARCHAR(300) DEFAULT NULL,
    `meta_keywords` VARCHAR(255) DEFAULT NULL,
    `post_schemas` JSON DEFAULT NULL COMMENT 'Schema markup',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`post_id`) REFERENCES `posts`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON DELETE CASCADE,
    
    UNIQUE KEY `unique_post_lang` (`post_id`, `language_code`),
    UNIQUE KEY `unique_slug_lang` (`slug`, `language_code`),
    INDEX `idx_post_lang` (`language_code`),
    INDEX `idx_post_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `menus` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `location` VARCHAR(50) NOT NULL COMMENT 'header, footer, sidebar, etc.',
    `is_active` TINYINT(1) DEFAULT 1,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_menus_location` (`location`),
    INDEX `idx_menus_active` (`is_active`),
    INDEX `idx_menus_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `menu_items` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `menu_id` VARCHAR(36) NOT NULL,
    `parent_id` VARCHAR(36) NULL DEFAULT NULL,
    `type` ENUM('page', 'category', 'product', 'custom', 'post') NOT NULL,
    `reference_id` VARCHAR(36) NULL DEFAULT NULL COMMENT 'ID of page/category/product/post',
    `url` VARCHAR(500) NULL DEFAULT NULL COMMENT 'For custom links',
    `target` ENUM('_self', '_blank') DEFAULT '_self',
    `icon` VARCHAR(100) NULL DEFAULT NULL,
    `css_class` VARCHAR(100) NULL DEFAULT NULL,
    `display_type` ENUM('default', 'dropdown', 'mega') DEFAULT 'default' COMMENT 'How to display sub-items',
    `mega_columns` INT DEFAULT NULL COMMENT 'Number of columns for mega menu (2,3,4)',
    `mega_style` ENUM('default', 'cards', 'grid', 'list') DEFAULT 'default',
    `sort_order` INT DEFAULT 0,
    `is_active` TINYINT(1) DEFAULT 1,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`menu_id`) REFERENCES `menus`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`parent_id`) REFERENCES `menu_items`(`id`) ON DELETE CASCADE,
    
    INDEX `idx_menu_items_menu` (`menu_id`),
    INDEX `idx_menu_items_parent` (`parent_id`),
    INDEX `idx_menu_items_type` (`type`),
    INDEX `idx_menu_items_reference` (`reference_id`),
    INDEX `idx_menu_items_sort` (`sort_order`),
    INDEX `idx_menu_items_active` (`is_active`),
    INDEX `idx_menu_items_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `menu_item_translations` (
    `id` VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    `menu_item_id` VARCHAR(36) NOT NULL,
    `language_code` VARCHAR(10) NOT NULL,
    `label` VARCHAR(200) NOT NULL,
    `title_attr` VARCHAR(200) NULL DEFAULT NULL COMMENT 'Title attribute for SEO',
    `description` TEXT NULL DEFAULT NULL COMMENT 'For mega menu descriptions',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`menu_item_id`) REFERENCES `menu_items`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON DELETE CASCADE,
    
    UNIQUE KEY `unique_menu_item_lang` (`menu_item_id`, `language_code`),
    INDEX `idx_menu_item_lang` (`language_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `site_settings` (
    `id` TINYINT PRIMARY KEY DEFAULT 1,
    CONSTRAINT `single_row` CHECK (`id` = 1),
    
    -- General
    `site_name` VARCHAR(100) NOT NULL DEFAULT 'DesiCart.pk',
    `site_tagline` VARCHAR(200) DEFAULT NULL,
    `currency_display_format` ENUM('symbol', 'code', 'both') DEFAULT 'symbol',
    `homepage_layout` VARCHAR(50) NOT NULL DEFAULT 'home1',
    `timezone` VARCHAR(60) DEFAULT 'Asia/Karachi',
    `date_format` VARCHAR(20) DEFAULT 'd M Y',
    `time_format` VARCHAR(10) DEFAULT 'h:i A',

    -- Branding
    `logo` VARCHAR(255) DEFAULT NULL,
    `logo_alt` VARCHAR(200) DEFAULT NULL,
    `footer_logo` VARCHAR(255) DEFAULT NULL,
    `footer_logo_alt` VARCHAR(200) DEFAULT NULL,
    `footer_text` TEXT DEFAULT NULL,

    -- Contact
    `phone_number` VARCHAR(30) DEFAULT NULL,
    `phone_number_2` VARCHAR(30) DEFAULT NULL,
    `whatsapp_number` VARCHAR(30) DEFAULT NULL,
    `email` VARCHAR(100) DEFAULT NULL,
    `support_email` VARCHAR(100) DEFAULT NULL,
    `address` TEXT DEFAULT NULL,
    `google_maps_embed_url` TEXT DEFAULT NULL,

    -- Social Media
    `facebook_url` VARCHAR(255) DEFAULT NULL,
    `instagram_url` VARCHAR(255) DEFAULT NULL,
    `twitter_url` VARCHAR(255) DEFAULT NULL,
    `youtube_url` VARCHAR(255) DEFAULT NULL,
    `tiktok_url` VARCHAR(255) DEFAULT NULL,
    `pinterest_url` VARCHAR(255) DEFAULT NULL,
    `linkedin_url` VARCHAR(255) DEFAULT NULL,
    `snapchat_url` VARCHAR(255) DEFAULT NULL,

    -- Order & Pricing
    `min_order_amount` DECIMAL(10,2) DEFAULT 0.00,
    `vat_percentage` DECIMAL(5,2) DEFAULT 0.00,
    `vat_label` VARCHAR(30) DEFAULT 'GST',
    `enabled_payment_methods` JSON DEFAULT NULL,  -- ["cod", "bank_transfer", "paypal", "stripe"]
    `enable_guest_checkout` BOOLEAN DEFAULT TRUE,

    -- Delivery
    `delivery_charges` DECIMAL(10,2) DEFAULT 0.00,
    `free_delivery_threshold` DECIMAL(10,2) DEFAULT 1000.00,

    -- Refund policy (admin-decided, not a hardcoded rule — see POST /api/orders/[id]/refund)
    `refund_include_tax` BOOLEAN DEFAULT TRUE COMMENT 'Whether GST/tax is included when refunding an order (full or proportionally on a partial refund)',
    `refund_include_delivery_fee` BOOLEAN DEFAULT TRUE COMMENT 'Whether the delivery fee is refunded on a full-order refund',

    -- Referral
    `referral_enabled` BOOLEAN DEFAULT TRUE,
    `referral_reward_type` ENUM('percentage', 'fixed') DEFAULT 'percentage',
    `referral_reward_value` DECIMAL(10,2) DEFAULT 5.00,
    `referral_min_order_to_earn` DECIMAL(10,2) DEFAULT 300.00,

    -- SEO
    `robots_txt` TEXT DEFAULT NULL,
    `google_site_verification` VARCHAR(100) DEFAULT NULL,
    `bing_site_verification` VARCHAR(100) DEFAULT NULL,
    `pinterest_verification` VARCHAR(100) DEFAULT NULL,
    `yandex_verification` VARCHAR(100) DEFAULT NULL,
    `baidu_verification` VARCHAR(100) DEFAULT NULL,

    -- Analytics
    `google_analytics_id` VARCHAR(50) DEFAULT NULL,
    `google_tag_manager_id` VARCHAR(50) DEFAULT NULL,
    `facebook_pixel_id` VARCHAR(50) DEFAULT NULL,
    `tiktok_pixel_id` VARCHAR(50) DEFAULT NULL,
    `custom_body_scripts` TEXT DEFAULT NULL,
    `custom_footer_scripts` TEXT DEFAULT NULL,

    -- Legal Pages
    `privacy_policy_page_url` INT DEFAULT NULL,
    `terms_page_url` INT DEFAULT NULL,
    `return_policy_page_url` INT DEFAULT NULL,
    `shipping_policy_page_url` INT DEFAULT NULL,

    -- SMTP Settings
    `smtp_host` VARCHAR(100) DEFAULT NULL,
    `smtp_port` INT DEFAULT 587,
    `smtp_encryption` ENUM('tls', 'ssl', 'none') DEFAULT 'tls',
    `smtp_username` VARCHAR(100) DEFAULT NULL,
    `smtp_password` VARCHAR(255) DEFAULT NULL,
    `from_email` VARCHAR(100) DEFAULT NULL,
    `from_name` VARCHAR(100) DEFAULT NULL,

    -- Reviews
    `enable_reviews` BOOLEAN DEFAULT TRUE,

    -- Social Login (OAuth)
    `google_client_id` VARCHAR(255) DEFAULT NULL,
    `google_client_secret` VARCHAR(255) DEFAULT NULL,
    `facebook_app_id` VARCHAR(255) DEFAULT NULL,
    `facebook_app_secret` VARCHAR(255) DEFAULT NULL,

    -- Payment Gateway Keys (Recommended to add)
    `paypal_client_id` VARCHAR(255) DEFAULT NULL,
    `paypal_client_secret` VARCHAR(255) DEFAULT NULL,
    `paypal_mode` ENUM('sandbox', 'live') DEFAULT 'sandbox',
    `stripe_publishable_key` VARCHAR(255) DEFAULT NULL,
    `stripe_secret_key` VARCHAR(255) DEFAULT NULL,
    `stripe_webhook_secret` VARCHAR(255) DEFAULT NULL,

    -- Timestamps
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `skins` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) NOT NULL UNIQUE,
    `category` ENUM('ecommerce', 'admin', 'blog', 'corporate', 'creative', 'dark', 'minimal', 'vibrant') DEFAULT 'ecommerce',
    `description` TEXT DEFAULT NULL,
    `is_active` BOOLEAN DEFAULT TRUE,
    `is_default` BOOLEAN DEFAULT FALSE,
    `sort_order` INT DEFAULT 0,
    
    -- ==========================================
    -- LIGHT MODE COLORS
    -- ==========================================
    `light_color_cta` VARCHAR(7) DEFAULT '#D97706',
    `light_color_cta_hover` VARCHAR(7) DEFAULT '#F59E0B',
    `light_color_cta_light` VARCHAR(7) DEFAULT '#FEF3C7',
    `light_color_cta_dark` VARCHAR(7) DEFAULT '#B45309',
    `light_color_success` VARCHAR(7) DEFAULT '#059669',
    `light_color_success_light` VARCHAR(7) DEFAULT '#D1FAE5',
    `light_color_success_dark` VARCHAR(7) DEFAULT '#047857',
    `light_color_danger` VARCHAR(7) DEFAULT '#EF4444',
    `light_color_danger_light` VARCHAR(7) DEFAULT '#FEE2E2',
    `light_color_info` VARCHAR(7) DEFAULT '#3B82F6',
    `light_color_info_light` VARCHAR(7) DEFAULT '#EFF6FF',
    `light_color_warning` VARCHAR(7) DEFAULT '#F59E0B',
    `light_color_warning_light` VARCHAR(7) DEFAULT '#FEF3C7',
    `light_color_sidebar` VARCHAR(7) DEFAULT '#1E293B',
    `light_color_sidebar_hover` VARCHAR(7) DEFAULT '#334155',
    `light_color_background` VARCHAR(7) DEFAULT '#F8FAFC',
    `light_color_surface` VARCHAR(7) DEFAULT '#FFFFFF',
    `light_color_surface_alt` VARCHAR(7) DEFAULT '#F1F5F9',
    `light_color_border` VARCHAR(7) DEFAULT '#E2E8F0',
    `light_color_border_muted` VARCHAR(7) DEFAULT '#CBD5E1',
    `light_color_text` VARCHAR(7) DEFAULT '#0F172A',
    `light_color_text_secondary` VARCHAR(7) DEFAULT '#475569',
    `light_color_text_tertiary` VARCHAR(7) DEFAULT '#64748B',
    `light_color_text_muted` VARCHAR(7) DEFAULT '#94A3B8',
    `light_color_chart_grid` VARCHAR(7) DEFAULT '#E2E8F0',
    `light_shadow_card_sm` VARCHAR(100) DEFAULT '0 1px 2px 0 rgb(0 0 0 / 0.05)',
    `light_shadow_card_md` VARCHAR(100) DEFAULT '0 4px 6px -1px rgb(0 0 0 / 0.05)',
    `light_shadow_card_lg` VARCHAR(100) DEFAULT '0 10px 15px -3px rgb(0 0 0 / 0.08)',
    `light_shadow_card_xl` VARCHAR(100) DEFAULT '0 20px 25px -5px rgb(0 0 0 / 0.08)',
    `light_radius_card` VARCHAR(20) DEFAULT '1.5rem',
    `light_radius_button` VARCHAR(20) DEFAULT '2rem',
    
    -- ==========================================
    -- DARK MODE COLORS (Per Skin)
    -- ==========================================
    `dark_color_cta` VARCHAR(7) DEFAULT '#D97706',
    `dark_color_cta_hover` VARCHAR(7) DEFAULT '#F59E0B',
    `dark_color_cta_light` VARCHAR(7) DEFAULT '#422006',
    `dark_color_cta_dark` VARCHAR(7) DEFAULT '#B45309',
    `dark_color_success` VARCHAR(7) DEFAULT '#059669',
    `dark_color_success_light` VARCHAR(7) DEFAULT '#064E3B',
    `dark_color_success_dark` VARCHAR(7) DEFAULT '#047857',
    `dark_color_danger` VARCHAR(7) DEFAULT '#EF4444',
    `dark_color_danger_light` VARCHAR(7) DEFAULT '#7F1D1D',
    `dark_color_info` VARCHAR(7) DEFAULT '#3B82F6',
    `dark_color_info_light` VARCHAR(7) DEFAULT '#1E3A8A',
    `dark_color_warning` VARCHAR(7) DEFAULT '#F59E0B',
    `dark_color_warning_light` VARCHAR(7) DEFAULT '#422006',
    `dark_color_sidebar` VARCHAR(7) DEFAULT '#0B1120',
    `dark_color_sidebar_hover` VARCHAR(7) DEFAULT '#1E293B',
    `dark_color_background` VARCHAR(7) DEFAULT '#0B1120',
    `dark_color_surface` VARCHAR(7) DEFAULT '#111827',
    `dark_color_surface_alt` VARCHAR(7) DEFAULT '#1F2937',
    `dark_color_border` VARCHAR(7) DEFAULT '#1F2937',
    `dark_color_border_muted` VARCHAR(7) DEFAULT '#374151',
    `dark_color_text` VARCHAR(7) DEFAULT '#F9FAFB',
    `dark_color_text_secondary` VARCHAR(7) DEFAULT '#E5E7EB',
    `dark_color_text_tertiary` VARCHAR(7) DEFAULT '#D1D5DB',
    `dark_color_text_muted` VARCHAR(7) DEFAULT '#9CA3AF',
    `dark_color_chart_grid` VARCHAR(7) DEFAULT '#1F2937',
    `dark_shadow_card_sm` VARCHAR(100) DEFAULT '0 1px 2px 0 rgb(0 0 0 / 0.3)',
    `dark_shadow_card_md` VARCHAR(100) DEFAULT '0 4px 6px -1px rgb(0 0 0 / 0.3)',
    `dark_shadow_card_lg` VARCHAR(100) DEFAULT '0 10px 15px -3px rgb(0 0 0 / 0.3)',
    `dark_shadow_card_xl` VARCHAR(100) DEFAULT '0 20px 25px -5px rgb(0 0 0 / 0.3)',
    `dark_radius_card` VARCHAR(20) DEFAULT '1.5rem',
    `dark_radius_button` VARCHAR(20) DEFAULT '2rem',
    
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX `idx_category` (`category`),
    INDEX `idx_is_active` (`is_active`),
    INDEX `idx_is_default` (`is_default`),
    INDEX `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employee_skin_preferences` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `employee_id` VARCHAR(36) NOT NULL,           -- 👤 Employee (not user)
    `skin_id` INT NOT NULL,               -- 🎨 Selected skin
    `mode` ENUM('light', 'dark') DEFAULT 'light',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (`employee_id`) REFERENCES `employees`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`skin_id`) REFERENCES `skins`(`id`) ON DELETE CASCADE,
    UNIQUE KEY `unique_employee_skin` (`employee_id`),  -- ⚠️ Sirf 1 entry per employee
    
    INDEX `idx_employee_id` (`employee_id`),
    INDEX `idx_skin_id` (`skin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==========================================
-- 1. SUNSET BLAZE (Default)
-- ==========================================
INSERT INTO `skins` (
    `name`, `slug`, `category`, `description`, `is_default`, `sort_order`,
    
    -- Light Mode
    `light_color_cta`, `light_color_cta_hover`, `light_color_cta_light`, `light_color_cta_dark`,
    `light_color_background`, `light_color_surface`, `light_color_text`, `light_color_sidebar`,
    
    -- Dark Mode
    `dark_color_cta`, `dark_color_cta_hover`, `dark_color_cta_light`, `dark_color_cta_dark`,
    `dark_color_background`, `dark_color_surface`, `dark_color_text`, `dark_color_sidebar`
) VALUES (
    'Sunset Blaze', 'sunset-blaze', 'ecommerce', 'Warm orange and amber tones', TRUE, 1,
    
    -- Light
    '#D97706', '#F59E0B', '#FEF3C7', '#B45309',
    '#FFFBEB', '#FFFFFF', '#0F172A', '#1E293B',
    
    -- Dark (Warm tones with dark backgrounds)
    '#F59E0B', '#FBBF24', '#422006', '#B45309',
    '#1C1917', '#292524', '#FEF3C7', '#1C1917'
);

-- ==========================================
-- 2. OCEAN BLUE
-- ==========================================
INSERT INTO `skins` (
    `name`, `slug`, `category`, `description`, `is_default`, `sort_order`,
    
    -- Light Mode
    `light_color_cta`, `light_color_cta_hover`, `light_color_cta_light`, `light_color_cta_dark`,
    `light_color_background`, `light_color_surface`, `light_color_text`, `light_color_sidebar`,
    
    -- Dark Mode
    `dark_color_cta`, `dark_color_cta_hover`, `dark_color_cta_light`, `dark_color_cta_dark`,
    `dark_color_background`, `dark_color_surface`, `dark_color_text`, `dark_color_sidebar`
) VALUES (
    'Ocean Blue', 'ocean-blue', 'ecommerce', 'Cool blue tones for trust and calm', FALSE, 2,
    
    -- Light
    '#2563EB', '#3B82F6', '#DBEAFE', '#1D4ED8',
    '#EFF6FF', '#FFFFFF', '#0F172A', '#1E293B',
    
    -- Dark (Deep blue ocean theme)
    '#3B82F6', '#60A5FA', '#1E3A8A', '#1D4ED8',
    '#0F172A', '#1E293B', '#DBEAFE', '#0F172A'
);

-- ==========================================
-- 3. EMERALD GREEN
-- ==========================================
INSERT INTO `skins` (
    `name`, `slug`, `category`, `description`, `is_default`, `sort_order`,
    
    -- Light Mode
    `light_color_cta`, `light_color_cta_hover`, `light_color_cta_light`, `light_color_cta_dark`,
    `light_color_background`, `light_color_surface`, `light_color_text`, `light_color_sidebar`,
    
    -- Dark Mode
    `dark_color_cta`, `dark_color_cta_hover`, `dark_color_cta_light`, `dark_color_cta_dark`,
    `dark_color_background`, `dark_color_surface`, `dark_color_text`, `dark_color_sidebar`
) VALUES (
    'Emerald Green', 'emerald-green', 'ecommerce', 'Fresh green for nature brands', FALSE, 3,
    
    -- Light
    '#059669', '#10B981', '#D1FAE5', '#047857',
    '#ECFDF5', '#FFFFFF', '#0F172A', '#1E293B',
    
    -- Dark (Forest theme)
    '#10B981', '#34D399', '#064E3B', '#047857',
    '#022C22', '#064E3B', '#D1FAE5', '#022C22'
);

-- ==========================================
-- 4. ROYAL PURPLE
-- ==========================================
INSERT INTO `skins` (
    `name`, `slug`, `category`, `description`, `is_default`, `sort_order`,
    
    -- Light Mode
    `light_color_cta`, `light_color_cta_hover`, `light_color_cta_light`, `light_color_cta_dark`,
    `light_color_background`, `light_color_surface`, `light_color_text`, `light_color_sidebar`,
    
    -- Dark Mode
    `dark_color_cta`, `dark_color_cta_hover`, `dark_color_cta_light`, `dark_color_cta_dark`,
    `dark_color_background`, `dark_color_surface`, `dark_color_text`, `dark_color_sidebar`
) VALUES (
    'Royal Purple', 'royal-purple', 'ecommerce', 'Luxurious purple for premium brands', FALSE, 4,
    
    -- Light
    '#7C3AED', '#8B5CF6', '#EDE9FE', '#6D28D9',
    '#F5F3FF', '#FFFFFF', '#0F172A', '#1E293B',
    
    -- Dark (Deep purple royalty)
    '#8B5CF6', '#A78BFA', '#2E1065', '#6D28D9',
    '#1E1B4B', '#2E1065', '#EDE9FE', '#1E1B4B'
);

-- ==========================================
-- 5. MINIMAL WHITE
-- ==========================================
INSERT INTO `skins` (
    `name`, `slug`, `category`, `description`, `is_default`, `sort_order`,
    
    -- Light Mode
    `light_color_cta`, `light_color_cta_hover`, `light_color_cta_light`, `light_color_cta_dark`,
    `light_color_background`, `light_color_surface`, `light_color_text`, `light_color_sidebar`,
    
    -- Dark Mode
    `dark_color_cta`, `dark_color_cta_hover`, `dark_color_cta_light`, `dark_color_cta_dark`,
    `dark_color_background`, `dark_color_surface`, `dark_color_text`, `dark_color_sidebar`
) VALUES (
    'Minimal White', 'minimal-white', 'minimal', 'Clean and minimal white theme', FALSE, 5,
    
    -- Light
    '#475569', '#64748B', '#F1F5F9', '#334155',
    '#FFFFFF', '#F8FAFC', '#0F172A', '#1E293B',
    
    -- Dark (Clean dark minimal)
    '#94A3B8', '#CBD5E1', '#1E293B', '#475569',
    '#0B1120', '#111827', '#F1F5F9', '#0B1120'
);

-- ==========================================
-- 6. DARK KNIGHT
-- ==========================================
INSERT INTO `skins` (
    `name`, `slug`, `category`, `description`, `is_default`, `sort_order`,
    
    -- Light Mode
    `light_color_cta`, `light_color_cta_hover`, `light_color_cta_light`, `light_color_cta_dark`,
    `light_color_background`, `light_color_surface`, `light_color_text`, `light_color_sidebar`,
    
    -- Dark Mode
    `dark_color_cta`, `dark_color_cta_hover`, `dark_color_cta_light`, `dark_color_cta_dark`,
    `dark_color_background`, `dark_color_surface`, `dark_color_text`, `dark_color_sidebar`
) VALUES (
    'Dark Knight', 'dark-knight', 'dark', 'Bold dark theme for modern look', FALSE, 6,
    
    -- Light
    '#8B5CF6', '#A78BFA', '#EDE9FE', '#7C3AED',
    '#111827', '#1F2937', '#F9FAFB', '#0B1120',
    
    -- Dark (Even darker with neon accents)
    '#A78BFA', '#C4B5FD', '#2E1065', '#7C3AED',
    '#050505', '#0B1120', '#E5E7EB', '#050505'
);

-- ==========================================
-- 7. VIBRANT PINK
-- ==========================================
INSERT INTO `skins` (
    `name`, `slug`, `category`, `description`, `is_default`, `sort_order`,
    
    -- Light Mode
    `light_color_cta`, `light_color_cta_hover`, `light_color_cta_light`, `light_color_cta_dark`,
    `light_color_background`, `light_color_surface`, `light_color_text`, `light_color_sidebar`,
    
    -- Dark Mode
    `dark_color_cta`, `dark_color_cta_hover`, `dark_color_cta_light`, `dark_color_cta_dark`,
    `dark_color_background`, `dark_color_surface`, `dark_color_text`, `dark_color_sidebar`
) VALUES (
    'Vibrant Pink', 'vibrant-pink', 'vibrant', 'Energetic pink for fashion and beauty', FALSE, 7,
    
    -- Light
    '#EC4899', '#F472B6', '#FCE7F3', '#DB2777',
    '#FDF2F8', '#FFFFFF', '#0F172A', '#1E293B',
    
    -- Dark (Bold pink on dark)
    '#F472B6', '#F9A8D4', '#4C0519', '#DB2777',
    '#1C0A12', '#2D0A1A', '#FCE7F3', '#1C0A12'
);

-- ==========================================
-- 8. CORPORATE BLUE
-- ==========================================
INSERT INTO `skins` (
    `name`, `slug`, `category`, `description`, `is_default`, `sort_order`,
    
    -- Light Mode
    `light_color_cta`, `light_color_cta_hover`, `light_color_cta_light`, `light_color_cta_dark`,
    `light_color_background`, `light_color_surface`, `light_color_text`, `light_color_sidebar`,
    
    -- Dark Mode
    `dark_color_cta`, `dark_color_cta_hover`, `dark_color_cta_light`, `dark_color_cta_dark`,
    `dark_color_background`, `dark_color_surface`, `dark_color_text`, `dark_color_sidebar`
) VALUES (
    'Corporate Blue', 'corporate-blue', 'corporate', 'Professional for B2B and corporate', FALSE, 8,
    
    -- Light
    '#1E40AF', '#2563EB', '#DBEAFE', '#1E3A8A',
    '#F8FAFC', '#FFFFFF', '#0F172A', '#1E293B',
    
    -- Dark (Professional dark)
    '#3B82F6', '#60A5FA', '#1E3A8A', '#1E3A8A',
    '#0B1120', '#1E293B', '#E2E8F0', '#0B1120'
);