CREATE DATABASE IF NOT EXISTS `jiddain_school_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `jiddain_school_db`;

CREATE TABLE IF NOT EXISTS `product_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(120) NOT NULL,
  `slug` VARCHAR(120) NOT NULL,
  `description` TEXT NULL,
  `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_product_categories_slug` (`slug`),
  KEY `idx_product_categories_status` (`status`),
  KEY `idx_product_categories_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `products` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(180) NOT NULL,
  `slug` VARCHAR(180) NOT NULL,
  `code` VARCHAR(60) NULL,
  `description` TEXT NULL,
  `short_description` TEXT NULL,
  `price` DECIMAL(10,2) NULL,
  `original_price` DECIMAL(10,2) NULL,
  `discount_price` DECIMAL(10,2) NULL,
  `category_id` INT UNSIGNED NOT NULL,
  `image` VARCHAR(255) NOT NULL DEFAULT 'img/product/default.png',
  `image_views` JSON NULL,
  `status` ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
  `featured` TINYINT(1) NOT NULL DEFAULT 0,
  `note` VARCHAR(255) NULL,
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_products_slug` (`slug`),
  UNIQUE KEY `uq_products_code` (`code`),
  KEY `idx_products_category_id` (`category_id`),
  KEY `idx_products_status` (`status`),
  KEY `idx_products_featured` (`featured`),
  KEY `idx_products_price` (`price`),
  CONSTRAINT `fk_products_category`
    FOREIGN KEY (`category_id`) REFERENCES `product_categories` (`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `product_images` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` INT UNSIGNED NOT NULL,
  `image_url` VARCHAR(255) NOT NULL,
  `view` VARCHAR(40) NOT NULL DEFAULT 'front',
  `sort_order` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_product_images_product_id` (`product_id`),
  KEY `idx_product_images_sort_order` (`sort_order`),
  CONSTRAINT `fk_product_images_product`
    FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `product_categories` (`name`, `slug`, `description`, `status`, `sort_order`) VALUES
('stationary', 'stationary', 'Durable office and school files, available in bulk-friendly quantities.', 'active', 1),
('Handmade Products', 'handmade', 'Fabric and paper flowers, bouquets and customised greeting cards.', 'active', 2),
('Cleaning Products', 'cleaning', 'Phenyl, handwash, dishwash and liquid soap made fresh in multiple pack sizes.', 'active', 3),
('Seasonal Products', 'seasonal', 'Festival-only pieces made around Diwali, Raksha Bandhan and other occasions.', 'active', 4)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`), `description` = VALUES(`description`), `status` = VALUES(`status`), `sort_order` = VALUES(`sort_order`);

INSERT INTO `products` (`name`, `slug`, `code`, `description`, `short_description`, `price`, `category_id`, `image`, `status`, `featured`, `note`, `sort_order`)
SELECT 'Box File', 'sta-01', 'STA-01', 'Built for daily handling — office floors, school bags and shop counters — with a mechanism and board sturdy enough for repeat use.', 'Product code STA-01 from our stationary range.', 110.00, c.id, 'img/product/30.png', 'active', 1, '', 1
FROM `product_categories` c WHERE c.slug = 'stationary'
ON DUPLICATE KEY UPDATE `description` = VALUES(`description`), `price` = VALUES(`price`), `image` = VALUES(`image`), `status` = VALUES(`status`), `featured` = VALUES(`featured`), `note` = VALUES(`note`), `sort_order` = VALUES(`sort_order`);

INSERT INTO `products` (`name`, `slug`, `code`, `description`, `short_description`, `price`, `category_id`, `image`, `status`, `featured`, `note`, `sort_order`)
SELECT 'Flowers Bouquet (Velvet)', 'hmp-01', 'HMP-01', 'Soft velvet floral bouquet handcrafted for gifting and decor.', 'Handcrafted bouquet with a premium finish.', 300.00, c.id, 'img/product/6.png', 'active', 1, '', 2
FROM `product_categories` c WHERE c.slug = 'handmade'
ON DUPLICATE KEY UPDATE `description` = VALUES(`description`), `price` = VALUES(`price`), `image` = VALUES(`image`), `status` = VALUES(`status`), `featured` = VALUES(`featured`), `note` = VALUES(`note`), `sort_order` = VALUES(`sort_order`);

INSERT INTO `products` (`name`, `slug`, `code`, `description`, `short_description`, `price`, `category_id`, `image`, `status`, `featured`, `note`, `sort_order`)
SELECT 'Handwash (Rose, Lemon, Lavender)', 'cln-02', 'CLN-02', 'Handwash available in multiple fragrance options and bulk packs.', 'Rose, lemon and lavender variants available.', 430.00, c.id, 'img/product/25.png', 'active', 1, '', 3
FROM `product_categories` c WHERE c.slug = 'cleaning'
ON DUPLICATE KEY UPDATE `description` = VALUES(`description`), `price` = VALUES(`price`), `image` = VALUES(`image`), `status` = VALUES(`status`), `featured` = VALUES(`featured`), `note` = VALUES(`note`), `sort_order` = VALUES(`sort_order`);

INSERT INTO `products` (`name`, `slug`, `code`, `description`, `short_description`, `price`, `category_id`, `image`, `status`, `featured`, `note`, `sort_order`)
SELECT 'Floating Lotus Lights', 'floating-lotus-lights', NULL, 'Seasonal decorative light set for festive spaces and home styling.', 'Festive seasonal product.', 0.00, c.id, 'img/product/13.png', 'active', 0, 'Seasonal availability varies.', 4
FROM `product_categories` c WHERE c.slug = 'seasonal'
ON DUPLICATE KEY UPDATE `description` = VALUES(`description`), `price` = VALUES(`price`), `image` = VALUES(`image`), `status` = VALUES(`status`), `featured` = VALUES(`featured`), `note` = VALUES(`note`), `sort_order` = VALUES(`sort_order`);

SELECT 'Schema and sample data loaded successfully.' AS status;