-- ============================================================ -- 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;