-- ============================================================
--  Bdzoon E-Commerce — Full MySQL Database Schema + Seed Data
--  Run: mysql -u root -p bdzoon < bdzoon_mysql.sql
-- ============================================================

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

SET FOREIGN_KEY_CHECKS = 0;

-- ── USERS ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name                VARCHAR(100) NOT NULL,
    email               VARCHAR(150) UNIQUE,
    phone               VARCHAR(20) UNIQUE,
    password            VARCHAR(255) NOT NULL,
    role                ENUM('admin','customer') DEFAULT 'customer',
    avatar              VARCHAR(255),
    date_of_birth       DATE,
    gender              ENUM('male','female','other'),
    district            VARCHAR(100),
    loyalty_points      INT UNSIGNED DEFAULT 0,
    is_active           TINYINT(1) DEFAULT 1,
    email_verified_at   TIMESTAMP NULL,
    phone_verified_at   TIMESTAMP NULL,
    remember_token      VARCHAR(100),
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_role (role),
    INDEX idx_phone (phone)
) ENGINE=InnoDB;

-- ── CATEGORIES ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS categories (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id   BIGINT UNSIGNED NULL,
    name        VARCHAR(100) NOT NULL,
    slug        VARCHAR(120) UNIQUE NOT NULL,
    icon        VARCHAR(50),
    image       VARCHAR(255),
    description TEXT,
    is_active   TINYINT(1) DEFAULT 1,
    sort_order  INT DEFAULT 0,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_slug (slug),
    INDEX idx_parent (parent_id)
) ENGINE=InnoDB;

-- ── PRODUCTS ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS products (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id         BIGINT UNSIGNED NOT NULL,
    name                VARCHAR(200) NOT NULL,
    slug                VARCHAR(220) UNIQUE NOT NULL,
    description         TEXT,
    short_description   VARCHAR(300),
    price               DECIMAL(10,2) NOT NULL,
    sale_price          DECIMAL(10,2),
    sku                 VARCHAR(100) UNIQUE,
    stock               INT UNSIGNED DEFAULT 0,
    images              JSON,
    sizes               JSON,
    colors              JSON,
    brand               VARCHAR(100),
    badge               VARCHAR(30),
    is_active           TINYINT(1) DEFAULT 1,
    is_featured         TINYINT(1) DEFAULT 0,
    is_flash_deal       TINYINT(1) DEFAULT 0,
    flash_deal_ends_at  TIMESTAMP NULL,
    meta_title          VARCHAR(200),
    meta_description    VARCHAR(300),
    deleted_at          TIMESTAMP 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 CASCADE,
    INDEX idx_slug (slug),
    INDEX idx_category (category_id),
    INDEX idx_active (is_active),
    INDEX idx_featured (is_featured),
    INDEX idx_flash (is_flash_deal),
    FULLTEXT idx_search (name, description, brand)
) ENGINE=InnoDB;

-- ── COUPONS ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS coupons (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code        VARCHAR(50) UNIQUE NOT NULL,
    type        ENUM('percent','flat','shipping') NOT NULL,
    value       DECIMAL(8,2) NOT NULL,
    min_order   DECIMAL(10,2),
    max_discount DECIMAL(10,2),
    usage_limit INT UNSIGNED,
    used_count  INT UNSIGNED DEFAULT 0,
    starts_at   TIMESTAMP NULL,
    expires_at  TIMESTAMP NULL,
    is_active   TINYINT(1) DEFAULT 1,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_code (code),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

-- ── ADDRESSES ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS addresses (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT UNSIGNED NOT NULL,
    name        VARCHAR(100) NOT NULL,
    phone       VARCHAR(20) NOT NULL,
    address     TEXT NOT NULL,
    district    VARCHAR(100) NOT NULL,
    city        VARCHAR(100),
    zip         VARCHAR(20),
    is_default  TINYINT(1) 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)
) ENGINE=InnoDB;

-- ── ORDERS ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS orders (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             BIGINT UNSIGNED NOT NULL,
    order_number        VARCHAR(30) UNIQUE NOT NULL,
    status              ENUM('pending','confirmed','packed','shipped','delivered','cancelled','returned') DEFAULT 'pending',
    payment_status      ENUM('pending','paid','failed','refunded') DEFAULT 'pending',
    payment_method      VARCHAR(30) NOT NULL,
    subtotal            DECIMAL(10,2) NOT NULL,
    delivery_charge     DECIMAL(8,2) DEFAULT 0.00,
    coupon_id           BIGINT UNSIGNED NULL,
    coupon_code         VARCHAR(50),
    coupon_discount     DECIMAL(8,2) DEFAULT 0.00,
    total               DECIMAL(10,2) NOT NULL,
    address_id          BIGINT UNSIGNED NULL,
    shipping_name       VARCHAR(100) NOT NULL,
    shipping_phone      VARCHAR(20) NOT NULL,
    shipping_address    TEXT NOT NULL,
    shipping_district   VARCHAR(100) NOT NULL,
    tracking_number     VARCHAR(100),
    courier             VARCHAR(50),
    transaction_id      VARCHAR(100),
    notes               TEXT,
    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,
    FOREIGN KEY (coupon_id)  REFERENCES coupons(id) ON DELETE SET NULL,
    FOREIGN KEY (address_id) REFERENCES addresses(id) ON DELETE SET NULL,
    INDEX idx_user (user_id),
    INDEX idx_order_number (order_number),
    INDEX idx_status (status),
    INDEX idx_payment_status (payment_status),
    INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- ── ORDER ITEMS ────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS order_items (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id    BIGINT UNSIGNED NOT NULL,
    product_id  BIGINT UNSIGNED NULL,
    name        VARCHAR(200) NOT NULL,
    price       DECIMAL(10,2) NOT NULL,
    quantity    INT UNSIGNED NOT NULL DEFAULT 1,
    size        VARCHAR(20),
    color       VARCHAR(30),
    image       VARCHAR(255),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id)   REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
    INDEX idx_order (order_id),
    INDEX idx_product (product_id)
) ENGINE=InnoDB;

-- ── CART ITEMS ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cart_items (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT UNSIGNED NOT NULL,
    product_id  BIGINT UNSIGNED NOT NULL,
    quantity    INT UNSIGNED DEFAULT 1,
    size        VARCHAR(20),
    color       VARCHAR(30),
    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,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY uq_cart (user_id, product_id, size, color),
    INDEX idx_user (user_id)
) ENGINE=InnoDB;

-- ── WISHLISTS ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS wishlists (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT UNSIGNED NOT NULL,
    product_id  BIGINT UNSIGNED NOT NULL,
    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,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    UNIQUE KEY uq_wishlist (user_id, product_id)
) ENGINE=InnoDB;

-- ── REVIEWS ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS reviews (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id  BIGINT UNSIGNED NOT NULL,
    user_id     BIGINT UNSIGNED NOT NULL,
    rating      TINYINT UNSIGNED NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title       VARCHAR(100),
    body        TEXT NOT NULL,
    is_approved TINYINT(1) 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 (user_id)    REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_product (product_id),
    INDEX idx_approved (is_approved)
) ENGINE=InnoDB;

-- ── BANNERS ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS banners (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(150) NOT NULL,
    image       VARCHAR(255) NOT NULL,
    link        VARCHAR(255),
    type        ENUM('hero','promo') DEFAULT 'hero',
    sort_order  INT DEFAULT 0,
    is_active   TINYINT(1) DEFAULT 1,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_type (type),
    INDEX idx_active (is_active)
) ENGINE=InnoDB;

-- ── SANCTUM TOKENS ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS personal_access_tokens (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tokenable_type  VARCHAR(255) NOT NULL,
    tokenable_id    BIGINT UNSIGNED NOT NULL,
    name            VARCHAR(255) NOT NULL,
    token           VARCHAR(64) UNIQUE NOT NULL,
    abilities       TEXT,
    last_used_at    TIMESTAMP NULL,
    expires_at      TIMESTAMP NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tokenable (tokenable_type, tokenable_id)
) ENGINE=InnoDB;

-- ── PASSWORD RESETS ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS password_reset_tokens (
    email       VARCHAR(150) PRIMARY KEY,
    token       VARCHAR(255) NOT NULL,
    created_at  TIMESTAMP NULL
) ENGINE=InnoDB;

-- ── CACHE ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS cache (
    `key`       VARCHAR(255) PRIMARY KEY,
    `value`     MEDIUMTEXT NOT NULL,
    expiration  INT NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cache_locks (
    `key`   VARCHAR(255) PRIMARY KEY,
    owner   VARCHAR(255) NOT NULL,
    expiration INT NOT NULL
) ENGINE=InnoDB;

-- ── SESSIONS ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sessions (
    id              VARCHAR(255) PRIMARY KEY,
    user_id         BIGINT UNSIGNED NULL,
    ip_address      VARCHAR(45),
    user_agent      TEXT,
    payload         LONGTEXT NOT NULL,
    last_activity   INT NOT NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_last_activity (last_activity)
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
--  SEED DATA
-- ============================================================

-- Admin user (password: Admin@1234)
INSERT INTO users (name, email, phone, password, role, loyalty_points, is_active) VALUES
('Bdzoon Admin', 'admin@bdzoon.com', '01849832178', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 0, 1);

-- Sample customers
INSERT INTO users (name, email, phone, password, role, loyalty_points, is_active) VALUES
('Rahela Begum',  'rahela@email.com',  '01700000001', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'customer', 120, 1),
('Kamal Hossain', 'kamal@email.com',   '01600000002', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'customer', 85,  1),
('Farida Akter',  'farida@email.com',  '01500000003', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'customer', 240, 1),
('Nusrat Jahan',  'nusrat@email.com',  '01400000004', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'customer', 680, 1);

-- Note: All passwords above = "password" (Laravel default bcrypt hash)
-- Admin password = Admin@1234 — change after first login

-- Categories
INSERT INTO categories (name, slug, icon, is_active, sort_order) VALUES
("Women's Clothing", 'womens-clothing', '🧕', 1, 1),
("Men's Clothing",   'mens-clothing',   '👔', 1, 2),
("Kids' Clothing",   'kids-clothing',   '👧', 1, 3),
('Electronics',      'electronics',     '📱', 1, 4),
('Gadgets',          'gadgets',         '⌚', 1, 5),
('Home & Living',    'home-living',     '🏠', 1, 6);

-- Sub-categories
INSERT INTO categories (parent_id, name, slug, icon, is_active, sort_order) VALUES
(1, 'Khimar',    'khimar',    '🧕', 1, 1),
(1, 'Abaya',     'abaya',     '🕌', 1, 2),
(1, 'Hijab',     'hijab',     '🧣', 1, 3),
(1, 'Burkha',    'burkha',    '🧕', 1, 4),
(1, 'Niqab',     'niqab',     '🧕', 1, 5),
(2, 'Panjabi',   'panjabi',   '👔', 1, 1),
(2, 'Jubba',     'jubba',     '🕌', 1, 2),
(2, 'Jacket',    'jacket',    '🧥', 1, 3),
(2, 'Shirt',     'shirt',     '👕', 1, 4),
(3, 'Khimar Set','khimar-set','🧕', 1, 1),
(3, 'Baby Dress','baby-dress','👗', 1, 2),
(3, 'School Bag','school-bag','🎒', 1, 3),
(4, 'Smart Watch','smart-watch','⌚', 1, 1),
(4, 'Earbuds',   'earbuds',   '🎧', 1, 2),
(5, 'Mini Fan',  'mini-fan',  '🌀', 1, 1),
(5, 'USB Gadgets','usb-gadgets','🔌', 1, 2);

-- Products
INSERT INTO products (category_id, name, slug, short_description, price, sale_price, sku, stock, brand, badge, is_active, is_featured, images, sizes, colors) VALUES
(1, 'Kids Cherry Khimar Set KCK116', 'kids-cherry-khimar-set-kck116', 'Premium kids khimar set, soft fabric', 850.00, 1200.00, 'BDZ-KCK-116', 28,  'Bdzoon Kids',    'hot',  1, 1, '[]', '["S","M","L"]',   '["Black","Navy","Brown"]'),
(1, 'Cherry Georgette Fabric Khimar','cherry-georgette-fabric-khimar','Georgette fabric, elegant design',     2200.00, NULL,    'BDZ-CGF-001', 20,  'Bdzoon Fashion', 'new',  1, 1, '[]', '["M","L","XL"]',  '["Black","White"]'),
(1, 'Georgette Fabric Abaya Set',    'georgette-fabric-abaya-set',    'Full abaya set with hijab',             2800.00, 3200.00, 'BDZ-GFA-001', 15,  'Bdzoon Fashion', 'disc', 1, 0, '[]', '["M","L","XL"]',  '["Black"]'),
(1, 'Hijab Collection Soft & Stylish','hijab-collection-soft-stylish', 'Soft premium hijab collection',        490.00, NULL,    'BDZ-HCS-001', 50,  'Bdzoon Fashion', '',     1, 0, '[]', '["One Size"]',    '["Black","White","Grey","Navy","Green"]'),
(1, 'Burkha Full Set Classic',       'burkha-full-set-classic',       'Classic full burkha set',               1800.00, 2100.00, 'BDZ-BFC-001', 12,  'Bdzoon Fashion', 'disc', 1, 0, '[]', '["M","L","XL"]',  '["Black"]'),
(2, 'Premium Ambroydary Panjabi',    'premium-ambroydary-panjabi',    'Embroidery work panjabi, festive wear', 1390.00, NULL,    'BDZ-PAP-001', 12,  'Bdzoon Men',     'new',  1, 0, '[]', '["M","L","XL","XXL"]','["White","Light Blue","Cream"]'),
(2, 'Tommy Hilfiger Cotton Jacket',  'tommy-hilfiger-cotton-jacket',  'Premium cotton jacket, casual wear',    990.00, 1200.00,  'BDZ-THJ-001', 8,   'Bdzoon Men',     'disc', 1, 1, '[]', '["M","L","XL"]',  '["Navy","Black","Olive"]'),
(2, 'President Tory Arabian Jubba',  'president-tory-arabian-jubba',  'Premium jubba for daily & prayer wear', 2190.00, 2590.00, 'BDZ-PTA-001', 15,  'Bdzoon Men',     'hot',  1, 0, '[]', '["M","L","XL","XXL"]','["White","Light Grey"]'),
(3, 'Premium School Bag PSB100',     'premium-school-bag-psb100',     'Durable school bag with multiple pockets',1290.00,1490.00,'BDZ-PSB-100', 74,  'Bdzoon Kids',    'hot',  1, 1, '[]', '["One Size"]',    '["Navy Blue","Black","Red"]'),
(3, 'Flannel Baby Dress FFB100',     'flannel-baby-dress-ffb100',     'Soft flannel dress for babies',         650.00, 850.00,  'BDZ-FBD-100', 30,  'Bdzoon Kids',    'disc', 1, 0, '[]', '["2Y","3Y","4Y","5Y"]','["Pink","Yellow","White"]'),
(3, 'Kids Alex Cherry Khimar KCK122','kids-alex-cherry-khimar-kck122','Premium khimar for young girls',        850.00, NULL,    'BDZ-KCK-122', 29,  'Bdzoon Kids',    'hot',  1, 0, '[]', '["S","M","L"]',   '["Black","Brown","Navy"]'),
(4, 'S2000 Promax Smart Watch Combo','s2000-promax-smart-watch-combo','1.8" AMOLED, heart rate, 100+ sports', 1450.00, 1650.00, 'BDZ-SW-S2000',3,   'Bdzoon Tech',    'disc', 1, 1, '[]', '["One Size"]',    '["Black","Silver","Rose Gold"]'),
(4, 'DBBL Smart Watch S3000',        'dbbl-smart-watch-s3000',        'AMOLED+, GPS, Bluetooth call support',  1850.00, 2200.00, 'BDZ-SW-S3000',10,  'Bdzoon Tech',    'disc', 1, 0, '[]', '["One Size"]',    '["Black","Brown"]'),
(4, 'Wireless Earbuds Pro',          'wireless-earbuds-pro',          'True wireless, 24hr battery, noise cancel',1350.00,1600.00,'BDZ-WEP-001', 22, 'Bdzoon Tech',    'new',  1, 0, '[]', '["One Size"]',    '["Black","White"]'),
(5, 'Liven Turbo Mini Fan',          'liven-turbo-mini-fan',          'USB rechargeable, 3-speed turbo fan',   1200.00, 1500.00, 'BDZ-LTF-001', 142, 'Bdzoon Tech',    'hot',  1, 1, '[]', '["One Size"]',    '["White","Pink","Blue"]'),
(5, 'USB Gadget Hub 4-Port',         'usb-gadget-hub-4-port',         '4-port USB 3.0 hub, compact design',    550.00, NULL,    'BDZ-UGH-001', 45,  'Bdzoon Tech',    'new',  1, 0, '[]', '["One Size"]',    '["Black","White"]'),
(1, 'Lilen Fabric Khimar Set LF121', 'lilen-fabric-khimar-set-lf121', 'Lilen fabric, premium quality khimar',  1590.00, NULL,   'BDZ-LFK-121', 8,   'Bdzoon Fashion', 'feat', 1, 0, '[]', '["M","L","XL"]',  '["Black","Dark Brown","Teal"]'),
(2, 'Men Casual Shirt Pack',         'men-casual-shirt-pack',         'Pack of 2 soft cotton casual shirts',   650.00, NULL,    'BDZ-MCS-001', 25,  'Bdzoon Men',     'new',  1, 0, '[]', '["M","L","XL","XXL"]','["White","Sky Blue","Mint"]');

-- ── 30 Additional Demo Products ────────────────────────────────────
INSERT INTO products (category_id, name, slug, short_description, price, sale_price, sku, stock, brand, badge, is_active, is_featured, images, sizes, colors) VALUES

-- Women's Clothing (cat 1) — 8 more
(1, 'Niqab Premium Black Collection',   'niqab-premium-black-collection',   'Premium niqab, lightweight & breathable',      680.00,  NULL,    'BDZ-NPC-001', 60,  'Bdzoon Fashion', '',     1, 0, '[]', '["One Size"]',        '["Black"]'),
(1, 'Floral Georgette Saree',           'floral-georgette-saree',           'Elegant floral print georgette saree',          3200.00, 3800.00, 'BDZ-FGS-001', 20,  'Bdzoon Fashion', 'disc', 1, 0, '[]', '["One Size"]',        '["Red","Blue","Green"]'),
(1, 'Cotton Summer Salwar Kameez',      'cotton-summer-salwar-kameez',      'Comfortable cotton summer dress',               1100.00, NULL,    'BDZ-CSS-001', 35,  'Bdzoon Fashion', 'new',  1, 0, '[]', '["S","M","L","XL"]', '["Pink","Yellow","White","Sky Blue"]'),
(1, 'Embroidery Work Kurti Set',        'embroidery-work-kurti-set',        'Beautiful embroidery kurti with palazzo',       1450.00, 1700.00, 'BDZ-EWK-001', 18,  'Bdzoon Fashion', 'hot',  1, 1, '[]', '["S","M","L","XL"]', '["Maroon","Navy","Green"]'),
(1, 'Silk Dupatta Premium Collection',  'silk-dupatta-premium-collection',  'Pure silk dupatta, vibrant colors',             550.00,  NULL,    'BDZ-SDP-001', 80,  'Bdzoon Fashion', '',     1, 0, '[]', '["One Size"]',        '["Red","Pink","Yellow","Blue","Purple"]'),
(1, 'Party Wear Lehenga Set',           'party-wear-lehenga-set',           'Stunning party lehenga with blouse & dupatta', 4500.00, 5500.00, 'BDZ-PWL-001', 8,   'Bdzoon Fashion', 'disc', 1, 1, '[]', '["S","M","L"]',      '["Red","Pink","Golden"]'),
(1, 'Abaya Full Length Classic Black',  'abaya-full-length-classic-black',  'Full-length classic abaya, premium fabric',     1850.00, 2200.00, 'BDZ-AFL-001', 25,  'Bdzoon Fashion', 'hot',  1, 0, '[]', '["M","L","XL","XXL"]','["Black"]'),
(1, 'Prayer Hijab Set with Cap',        'prayer-hijab-set-with-cap',        'Complete prayer hijab set with inner cap',      380.00,  NULL,    'BDZ-PHC-001', 120, 'Bdzoon Fashion', '',     1, 0, '[]', '["One Size"]',        '["White","Cream","Black"]'),

-- Men''s Clothing (cat 2) — 7 more
(2, 'Slim Fit Formal Shirt White',      'slim-fit-formal-shirt-white',      'Premium slim fit formal shirt for office',      850.00,  NULL,    'BDZ-SFS-001', 40,  'Bdzoon Men',     'new',  1, 0, '[]', '["S","M","L","XL","XXL"]','["White","Sky Blue","Light Pink"]'),
(2, 'Denim Jeans Premium Fit',          'denim-jeans-premium-fit',          'Premium quality stretch denim jeans',            1350.00, 1600.00, 'BDZ-DJP-001', 30,  'Bdzoon Men',     'disc', 1, 0, '[]', '["30","32","34","36","38"]','["Blue","Black","Grey"]'),
(2, 'Polo T-Shirt Summer Pack',         'polo-t-shirt-summer-pack',         'Pack of 3 premium polo t-shirts',               1200.00, NULL,    'BDZ-PTS-001', 50,  'Bdzoon Men',     'new',  1, 0, '[]', '["S","M","L","XL"]', '["White","Black","Navy","Red"]'),
(2, 'Men Formal Pant Office Wear',      'men-formal-pant-office-wear',      'Formal trouser for office and events',           950.00,  1100.00, 'BDZ-MFP-001', 28,  'Bdzoon Men',     'disc', 1, 0, '[]', '["30","32","34","36"]', '["Black","Dark Grey","Navy"]'),
(2, 'Eid Special Sherwani Set',         'eid-special-sherwani-set',         'Premium sherwani with kameez & pajama',          5500.00, 6500.00, 'BDZ-ESS-001', 12,  'Bdzoon Men',     'hot',  1, 1, '[]', '["M","L","XL","XXL"]','["Cream","Golden","Maroon"]'),
(2, 'Hooded Sweatshirt Winter Warm',    'hooded-sweatshirt-winter-warm',    'Thick fleece hoodie for winter',                 1150.00, NULL,    'BDZ-HSW-001', 35,  'Bdzoon Men',     'new',  1, 0, '[]', '["S","M","L","XL","XXL"]','["Grey","Black","Navy","Olive"]'),
(2, 'Lungi Premium Cotton Check',       'lungi-premium-cotton-check',       'Premium cotton lungi, comfortable daily wear',   420.00,  NULL,    'BDZ-LPC-001', 100, 'Bdzoon Men',     '',     1, 0, '[]', '["One Size"]',        '["Blue Check","Green Check","Red Check"]'),

-- Kids'' Clothing (cat 3) — 5 more
(3, 'Boys School Uniform Set',          'boys-school-uniform-set',          'Complete school uniform set for boys',           850.00,  1000.00, 'BDZ-BSU-001', 55,  'Bdzoon Kids',    'hot',  1, 0, '[]', '["4Y","6Y","8Y","10Y","12Y"]','["White & Navy","White & Black"]'),
(3, 'Girls Frock Party Dress',          'girls-frock-party-dress',          'Elegant party frock for little girls',           950.00,  1200.00, 'BDZ-GFP-001', 30,  'Bdzoon Kids',    'hot',  1, 1, '[]', '["3Y","5Y","7Y","9Y"]',  '["Pink","Red","Purple","Blue"]'),
(3, 'Kids Winter Jacket Warm',          'kids-winter-jacket-warm',          'Warm winter jacket for children',                780.00,  950.00,  'BDZ-KWJ-001', 20,  'Bdzoon Kids',    'disc', 1, 0, '[]', '["3Y","5Y","7Y","9Y","11Y"]','["Red","Blue","Green","Pink"]'),
(3, 'Baby Romper Set Newborn',          'baby-romper-set-newborn',          'Soft cotton romper set for newborns',            550.00,  NULL,    'BDZ-BRS-001', 45,  'Bdzoon Kids',    'new',  1, 0, '[]', '["0-3M","3-6M","6-9M"]','["Blue","Pink","Yellow","White"]'),
(3, 'Boys Casual Jogger Pant',          'boys-casual-jogger-pant',          'Comfortable jogger pants for active boys',       480.00,  NULL,    'BDZ-BCJ-001', 60,  'Bdzoon Kids',    '',     1, 0, '[]', '["4Y","6Y","8Y","10Y"]', '["Black","Grey","Navy"]'),

-- Electronics (cat 4) — 5 more
(4, 'Android Smart TV Box 4K',          'android-smart-tv-box-4k',          'Android 11, 4K HDR, 4GB RAM streaming box',    2800.00, 3200.00, 'BDZ-ATB-001', 15,  'Bdzoon Tech',    'hot',  1, 1, '[]', '["One Size"]',        '["Black"]'),
(4, 'Bluetooth Speaker Portable',       'bluetooth-speaker-portable',       '20W waterproof portable speaker, 12hr battery', 1800.00, 2100.00, 'BDZ-BSP-001', 28,  'Bdzoon Tech',    'disc', 1, 0, '[]', '["One Size"]',        '["Black","Blue","Red","Green"]'),
(4, 'Power Bank 20000mAh Fast Charge',  'power-bank-20000mah-fast-charge',  '20000mAh, 65W PD fast charging power bank',     2200.00, NULL,    'BDZ-PBF-001', 35,  'Bdzoon Tech',    'new',  1, 0, '[]', '["One Size"]',        '["Black","White"]'),
(4, 'USB-C Charging Cable 3-Pack',      'usb-c-charging-cable-3-pack',      '3-pack braided USB-C cable, 1m length',          480.00,  NULL,    'BDZ-UCC-001', 200, 'Bdzoon Tech',    '',     1, 0, '[]', '["1m","2m"]',        '["Black","White","Multi"]'),
(4, 'Wireless Mouse & Keyboard Combo',  'wireless-mouse-keyboard-combo',    '2.4GHz wireless combo, silent keys',             1650.00, 1900.00, 'BDZ-WMK-001', 20,  'Bdzoon Tech',    'disc', 1, 0, '[]', '["One Size"]',        '["Black","White"]'),

-- Gadgets (cat 5) — 5 more
(5, 'Smart LED Strip Light RGB',        'smart-led-strip-light-rgb',        'WiFi app controlled RGB LED strip 5m',           850.00,  1100.00, 'BDZ-SLS-001', 60,  'Bdzoon Tech',    'hot',  1, 1, '[]', '["3m","5m","10m"]', '["Multicolor"]'),
(5, 'Digital Kitchen Scale 5kg',        'digital-kitchen-scale-5kg',        'Precise 5kg kitchen scale, 1g accuracy',         480.00,  NULL,    'BDZ-DKS-001', 55,  'Bdzoon Tech',    '',     1, 0, '[]', '["One Size"]',        '["White","Black","Silver"]'),
(5, 'Mini Projector Portable HD',       'mini-projector-portable-hd',       '720P portable projector, 100 lumen, HDMI',       4200.00, 5000.00, 'BDZ-MPP-001', 10,  'Bdzoon Tech',    'disc', 1, 1, '[]', '["One Size"]',        '["White","Black"]'),
(5, 'Car Phone Holder Magnetic',        'car-phone-holder-magnetic',        'Strong magnetic car phone mount, universal',     350.00,  NULL,    'BDZ-CPH-001', 150, 'Bdzoon Tech',    '',     1, 0, '[]', '["One Size"]',        '["Black","Silver"]'),
(5, 'Smart Plug WiFi Remote Control',   'smart-plug-wifi-remote-control',   'WiFi smart plug, app & voice control',           650.00,  780.00,  'BDZ-SPW-001', 80,  'Bdzoon Tech',    'new',  1, 0, '[]', '["One Size"]',        '["White"]');

-- Coupons
INSERT INTO coupons (code, type, value, min_order, max_discount, usage_limit, is_active) VALUES
('BDZOON10', 'percent', 10.00, 0.00,    NULL,    NULL, 1),
('SAVE50',   'flat',    50.00, 500.00,  NULL,    500,  1),
('FREESHIP', 'shipping',80.00, 0.00,    NULL,    200,  1),
('EID30',    'percent', 30.00, 1000.00, 500.00,  500,  0),
('SUMMER20', 'percent', 20.00, 500.00,  300.00,  300,  1);

-- Banners
INSERT INTO banners (title, image, link, type, sort_order, is_active) VALUES
("Men's Collection Banner",   '/storage/banners/banner1.jpg', '/category/mens-clothing',   'hero',  1, 1),
("Women's Collection Banner", '/storage/banners/banner2.jpg', '/category/womens-clothing', 'hero',  2, 1),
("Kids' Collection Banner",   '/storage/banners/banner3.jpg', '/category/kids-clothing',   'hero',  3, 1),
('Eid Special Sale',          '/storage/banners/eid.jpg',     '/products?deals=1',          'promo', 1, 1),
('Combo Deal Banner',         '/storage/banners/combo.jpg',   '/products',                  'promo', 2, 1);

-- Sample addresses
INSERT INTO addresses (user_id, name, phone, address, district, city, is_default) VALUES
(2, 'Rahela Begum',  '01700000001', 'House 12, Road 5, Dhanmondi', 'Dhaka',      'Dhaka',     1),
(3, 'Kamal Hossain', '01600000002', 'Flat 3B, Block C, Nasirabad', 'Chittagong', 'Chittagong',1),
(4, 'Farida Akter',  '01500000003', 'House 8, Road 2, GEC Circle',  'Chittagong', 'Chittagong',1),
(5, 'Nusrat Jahan',  '01400000004', 'Flat 5A, Banani DOHS, Block H','Dhaka',      'Dhaka',     1);

-- Sample reviews
INSERT INTO reviews (product_id, user_id, rating, title, body, is_approved) VALUES
(1, 2, 5, 'Excellent quality!',    'My daughter loves it. Fabric is very soft and color exactly as shown. Fast delivery!', 1),
(1, 4, 5, 'Perfect for my daughter','Great quality. Ordered for the second time already. Highly recommended!', 1),
(7, 3, 5, 'Great jacket!',         'Premium quality jacket. Exactly as described. Size was perfect.', 1),
(9, 2, 4, 'Good school bag',       'Good quality bag. Lots of pockets. Kids love the color.', 1),
(12,5, 4, 'Nice smart watch',      'Good features for the price. Battery lasts about 5-6 days.', 1),
(15,2, 5, 'Best mini fan!',        'Works perfectly. Very powerful for its small size. Highly recommend!', 1),
(15,3, 5, 'Amazing fan',           'Bought for office use. 3-speed settings are very useful.', 1),
(15,4, 5, 'Love this product',     'Fast delivery and exactly as described. Will order again!', 1);

-- Sample order (delivered)
INSERT INTO orders (user_id, order_number, status, payment_status, payment_method, subtotal, delivery_charge, total, address_id, shipping_name, shipping_phone, shipping_address, shipping_district, tracking_number, courier, transaction_id) VALUES
(2, 'BDZ-2026-48291', 'delivered', 'paid', 'bkash', 5580.00, 80.00, 5660.00, 1, 'Rahela Begum', '01700000001', 'House 12, Road 5, Dhanmondi', 'Dhaka', 'PTH-2026-98423761', 'Pathao', 'TXN202600012345');

INSERT INTO order_items (order_id, product_id, name, price, quantity, size, color) VALUES
(1, 1, 'Kids Cherry Khimar Set KCK116', 850.00, 2, 'M', 'Black'),
(1, 12, 'S2000 Promax Smart Watch Combo', 1450.00, 1, 'One Size', 'Black'),
(1, 9, 'Premium School Bag PSB100', 1290.00, 1, 'One Size', 'Navy Blue');

-- ============================================================
--  USEFUL VIEWS
-- ============================================================

CREATE OR REPLACE VIEW v_product_summary AS
SELECT
    p.id, p.name, p.slug, p.price, p.sale_price, p.stock,
    p.badge, p.is_featured, p.is_flash_deal,
    c.name AS category_name, c.slug AS category_slug,
    COALESCE(AVG(r.rating), 0) AS avg_rating,
    COUNT(DISTINCT r.id)       AS review_count,
    COALESCE(SUM(oi.quantity), 0) AS total_sold
FROM products p
LEFT JOIN categories c  ON c.id = p.category_id
LEFT JOIN reviews r     ON r.product_id = p.id AND r.is_approved = 1
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE p.deleted_at IS NULL AND p.is_active = 1
GROUP BY p.id;

CREATE OR REPLACE VIEW v_order_summary AS
SELECT
    o.id, o.order_number, o.status, o.payment_status,
    o.payment_method, o.total, o.created_at,
    u.name AS customer_name, u.phone AS customer_phone,
    o.shipping_district,
    COUNT(oi.id) AS item_count
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

CREATE OR REPLACE VIEW v_dashboard_stats AS
SELECT
    (SELECT COUNT(*) FROM orders) AS total_orders,
    (SELECT SUM(total) FROM orders WHERE payment_status='paid') AS total_revenue,
    (SELECT COUNT(*) FROM users WHERE role='customer') AS total_customers,
    (SELECT COUNT(*) FROM products WHERE is_active=1) AS total_products,
    (SELECT COUNT(*) FROM orders WHERE status='pending') AS pending_orders,
    (SELECT COUNT(*) FROM products WHERE stock <= 10 AND stock > 0) AS low_stock,
    (SELECT COUNT(*) FROM products WHERE stock = 0) AS out_of_stock;

-- ============================================================
--  INDEXES FOR PERFORMANCE
-- ============================================================

CREATE INDEX idx_products_price      ON products (price);
CREATE INDEX idx_products_sale_price ON products (sale_price);
CREATE INDEX idx_orders_created      ON orders (created_at);
CREATE INDEX idx_reviews_product     ON reviews (product_id, is_approved);

SELECT '✓ Bdzoon MySQL database created successfully!' AS status;
SELECT '  Admin login: admin@bdzoon.com / Admin@1234' AS info;
