-- ============================================================
-- SAFA MODEST HOUSE - E-Commerce Database Schema
-- Bangladesh-style e-commerce (bKash/Nagad, ৳ Taka)
-- MySQL 8.0+ / MariaDB 10.5+
-- ============================================================


-- Create database
CREATE DATABASE IF NOT EXISTS `safa_modest_house`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `safa_modest_house`;

-- ============================================================
-- CATEGORIES TABLE
-- ============================================================
CREATE TABLE `categories` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(120) NOT NULL,
  `slug` VARCHAR(140) NOT NULL,
  `description` TEXT NULL,
  `image` VARCHAR(255) NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_categories_slug` (`slug`),
  KEY `idx_categories_active` (`is_active`),
  KEY `idx_categories_sort` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PRODUCTS TABLE
-- ============================================================
CREATE TABLE `products` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` BIGINT UNSIGNED NOT NULL,
  `name` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(220) NOT NULL,
  `sku` VARCHAR(50) NULL,
  `short_description` VARCHAR(500) NULL,
  `description` TEXT NULL,
  `price` DECIMAL(10,2) NOT NULL COMMENT 'Regular price in Taka',
  `sale_price` DECIMAL(10,2) NULL COMMENT 'Sale price (if on discount)',
  `stock_qty` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `is_featured` TINYINT(1) NOT NULL DEFAULT 0,
  `total_sold` INT UNSIGNED NOT NULL DEFAULT 0,
  `avg_rating` DECIMAL(2,1) NOT NULL DEFAULT 0.0,
  `review_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `meta_title` VARCHAR(200) NULL,
  `meta_description` VARCHAR(300) NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_products_slug` (`slug`),
  KEY `idx_products_category` (`category_id`),
  KEY `idx_products_active` (`is_active`),
  KEY `idx_products_featured` (`is_featured`),
  KEY `idx_products_bestseller` (`total_sold` DESC),
  KEY `idx_products_price` (`price`),
  CONSTRAINT `fk_products_category`
    FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PRODUCT IMAGES TABLE (Multiple images per product)
-- ============================================================
CREATE TABLE `product_images` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `file_path` VARCHAR(255) NOT NULL,
  `file_name` VARCHAR(150) NOT NULL,
  `is_primary` TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_images_product` (`product_id`),
  KEY `idx_images_primary` (`product_id`, `is_primary`),
  CONSTRAINT `fk_images_product`
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- CART ITEMS TABLE
-- ============================================================
CREATE TABLE `cart_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `cart_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `qty` INT UNSIGNED NOT NULL DEFAULT 1,
  `unit_price` DECIMAL(10,2) NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cart_product` (`cart_id`, `product_id`),
  KEY `idx_cart_items_cart` (`cart_id`),
  KEY `idx_cart_items_product` (`product_id`),
  CONSTRAINT `fk_cart_items_cart`
    FOREIGN KEY (`cart_id`) REFERENCES `carts`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_cart_items_product`
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- WISHLIST TABLE
-- ============================================================
CREATE TABLE `wishlists` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_wishlist_user_product` (`user_id`, `product_id`),
  KEY `idx_wishlist_user` (`user_id`),
  KEY `idx_wishlist_product` (`product_id`),
  CONSTRAINT `fk_wishlists_user`
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_wishlists_product`
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PROMO CODES TABLE
-- ============================================================
CREATE TABLE `promo_codes` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code` VARCHAR(50) NOT NULL,
  `description` VARCHAR(255) NULL,
  `discount_type` ENUM('percent','fixed') NOT NULL DEFAULT 'percent',
  `discount_value` DECIMAL(10,2) NOT NULL COMMENT 'Percentage or fixed amount in Taka',
  `min_order_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `max_discount_amount` DECIMAL(10,2) NULL COMMENT 'Cap for percentage discounts',
  `usage_limit` INT UNSIGNED NULL COMMENT 'Total times this code can be used',
  `used_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `starts_at` DATETIME NULL,
  `expires_at` DATETIME NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_promo_code` (`code`),
  KEY `idx_promo_active` (`is_active`),
  KEY `idx_promo_expiry` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ORDERS TABLE
-- ============================================================
CREATE TABLE `orders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_no` VARCHAR(30) NOT NULL COMMENT 'Unique order number e.g. SMH-20260112-0001',
  `user_id` BIGINT UNSIGNED NOT NULL,
  
  -- Order Status Flow: Pending → Processing → Shipped → Delivered (or Cancelled)
  `status` ENUM('Pending','Processing','Shipped','Delivered','Cancelled') NOT NULL DEFAULT 'Pending',
  
  -- Amount Breakdown (stored for historical accuracy)
  `subtotal` DECIMAL(10,2) NOT NULL COMMENT 'Sum of line items',
  `vat_percent` DECIMAL(5,2) NOT NULL DEFAULT 15.00,
  `vat_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `tax_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Additional tax if any',
  `shipping_cost` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  
  -- Promo/Discount
  `promo_code_id` BIGINT UNSIGNED NULL,
  `promo_code` VARCHAR(50) NULL COMMENT 'Stored for reference',
  `discount_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  
  `grand_total` DECIMAL(10,2) NOT NULL,
  
  -- Payment Info (bKash/Nagad manual)
  `payment_method` ENUM('bKash','Nagad') NOT NULL,
  `payment_status` ENUM('Unpaid','Submitted','Verified','Rejected') NOT NULL DEFAULT 'Unpaid',
  
  -- Shipping Details
  `shipping_name` VARCHAR(120) NOT NULL,
  `shipping_phone` VARCHAR(20) NOT NULL,
  `shipping_email` VARCHAR(190) NULL,
  `shipping_address` TEXT NOT NULL,
  `shipping_city` VARCHAR(80) NULL,
  `shipping_area` VARCHAR(100) NULL,
  `notes` TEXT NULL COMMENT 'Customer order notes',
  
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_orders_order_no` (`order_no`),
  KEY `idx_orders_user` (`user_id`),
  KEY `idx_orders_status` (`status`),
  KEY `idx_orders_payment` (`payment_status`),
  KEY `idx_orders_created` (`created_at` DESC),
  CONSTRAINT `fk_orders_user`
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_orders_promo`
    FOREIGN KEY (`promo_code_id`) REFERENCES `promo_codes`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ORDER ITEMS TABLE
-- ============================================================
CREATE TABLE `order_items` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` BIGINT UNSIGNED NOT NULL,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `product_name` VARCHAR(200) NOT NULL COMMENT 'Stored for historical reference',
  `product_sku` VARCHAR(50) NULL,
  `unit_price` DECIMAL(10,2) NOT NULL,
  `qty` INT UNSIGNED NOT NULL,
  `line_total` DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_order_items_order` (`order_id`),
  KEY `idx_order_items_product` (`product_id`),
  CONSTRAINT `fk_order_items_order`
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_order_items_product`
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ORDER PAYMENTS TABLE (bKash/Nagad manual payment records)
-- ============================================================
CREATE TABLE `order_payments` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` BIGINT UNSIGNED NOT NULL,
  `method` ENUM('bKash','Nagad') NOT NULL,
  `sender_mobile` VARCHAR(20) NOT NULL COMMENT 'Customer bKash/Nagad number',
  `trx_id` VARCHAR(80) NOT NULL COMMENT 'Transaction ID from bKash/Nagad',
  `amount` DECIMAL(10,2) NOT NULL,
  `submitted_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status` ENUM('Submitted','Verified','Rejected') NOT NULL DEFAULT 'Submitted',
  `verified_by_admin_id` BIGINT UNSIGNED NULL,
  `verified_at` DATETIME NULL,
  `admin_note` VARCHAR(500) NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_payment_trx` (`trx_id`),
  KEY `idx_payments_order` (`order_id`),
  KEY `idx_payments_status` (`status`),
  CONSTRAINT `fk_payments_order`
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_payments_admin`
    FOREIGN KEY (`verified_by_admin_id`) REFERENCES `admins`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- PRODUCT REVIEWS TABLE (with admin approval)
-- ============================================================
CREATE TABLE `product_reviews` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `product_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `order_id` BIGINT UNSIGNED NULL COMMENT 'Optional: link to purchase',
  `rating` TINYINT UNSIGNED NOT NULL COMMENT '1-5 stars',
  `review_title` VARCHAR(200) NULL,
  `review_text` TEXT NULL,
  `is_approved` TINYINT(1) NOT NULL DEFAULT 0,
  `approved_by_admin_id` BIGINT UNSIGNED NULL,
  `approved_at` DATETIME NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_reviews_product` (`product_id`),
  KEY `idx_reviews_user` (`user_id`),
  KEY `idx_reviews_approved` (`is_approved`),
  KEY `idx_reviews_rating` (`rating`),
  CONSTRAINT `fk_reviews_product`
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_reviews_user`
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_reviews_order`
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_reviews_admin`
    FOREIGN KEY (`approved_by_admin_id`) REFERENCES `admins`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `chk_rating` CHECK (`rating` >= 1 AND `rating` <= 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SITE SETTINGS TABLE (for hero banner, contact info, etc.)
-- ============================================================
CREATE TABLE `site_settings` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `setting_key` VARCHAR(100) NOT NULL,
  `setting_value` TEXT NULL,
  `setting_type` ENUM('text','number','json','boolean','image') NOT NULL DEFAULT 'text',
  `updated_at` DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ORDER STATUS HISTORY TABLE (for tracking)
-- ============================================================
CREATE TABLE `order_status_history` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` BIGINT UNSIGNED NOT NULL,
  `status` ENUM('Pending','Processing','Shipped','Delivered','Cancelled') NOT NULL,
  `note` VARCHAR(500) NULL,
  `changed_by_admin_id` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_status_history_order` (`order_id`),
  CONSTRAINT `fk_status_history_order`
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_status_history_admin`
    FOREIGN KEY (`changed_by_admin_id`) REFERENCES `admins`(`id`)
    ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Sample Categories
INSERT INTO `categories` (`name`, `slug`, `is_active`, `sort_order`) VALUES
('জামা (Shirts)', 'shirts', 1, 1),
('প্যান্ট (Pants)', 'pants', 1, 2),
('শাড়ি (Saree)', 'saree', 1, 3),
('পাঞ্জাবি (Panjabi)', 'panjabi', 1, 4),
('হিজাব ও বোরকা (Hijab & Borka)', 'hijab-borka', 1, 5),
('আনুষাঙ্গিক (Accessories)', 'accessories', 1, 6);

-- Sample Promo Code
INSERT INTO `promo_codes` (`code`, `description`, `discount_type`, `discount_value`, `min_order_amount`, `max_discount_amount`, `is_active`, `expires_at`) VALUES
('WELCOME10', 'নতুন গ্রাহকদের জন্য ১০% ছাড়', 'percent', 10.00, 500.00, 200.00, 1, '2026-12-31 23:59:59'),
('SAVE50', '৫০ টাকা ছাড়', 'fixed', 50.00, 300.00, NULL, 1, '2026-06-30 23:59:59');

-- Site Settings
INSERT INTO `site_settings` (`setting_key`, `setting_value`, `setting_type`) VALUES
('site_name', 'Safa Modest House', 'text'),
('site_tagline', 'আপনার বিশ্বস্ত ফ্যাশন গন্তব্য', 'text'),
('whatsapp_number', '8801712345678', 'text'),
('bkash_merchant_number', '01712345678', 'text'),
('nagad_merchant_number', '01812345678', 'text'),
('vat_percent', '15', 'number'),
('shipping_cost_dhaka', '60', 'number'),
('shipping_cost_outside', '120', 'number');
