File manager

File manager - Edit - /home/webapp69.cm.in.th/u69319090039/Shop39/database/database.sql

Back
-- Production Database Blueprint for Multi-Vendor Marketplace (PHP 8 + MySQL) -- NOTE: On shared web hosting (e.g. Hostinger, cPanel), database creation must be done via phpMyAdmin / Control Panel. -- CREATE DATABASE IF NOT EXISTS marketplace_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; -- USE marketplace_db; SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS support_tickets, news, promotions, banners, coupons, wishlists, store_reviews, reviews, order_items, orders, product_variants, product_images, products, categories, seller_profiles, users; SET FOREIGN_KEY_CHECKS = 1; -- -------------------------------------------------------- -- Table: users -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) NOT NULL UNIQUE, email VARCHAR(100) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL, full_name VARCHAR(100) NOT NULL, phone VARCHAR(20) NULL, role ENUM('admin', 'customer') NOT NULL DEFAULT 'customer', avatar VARCHAR(255) DEFAULT 'default-avatar.png', status ENUM('active', 'suspended', 'pending') NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_users_role_status (role, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Table: seller_profiles -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS seller_profiles ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL UNIQUE, shop_name VARCHAR(100) NOT NULL, slug VARCHAR(120) NOT NULL UNIQUE, logo VARCHAR(255) DEFAULT 'default-shop-logo.png', banner VARCHAR(255) DEFAULT 'default-shop-banner.png', description TEXT NULL, phone VARCHAR(20) NOT NULL, address TEXT NOT NULL, latitude DECIMAL(10,8) NULL, longitude DECIMAL(11,8) NULL, promptpay_number VARCHAR(30) NULL, bank_account VARCHAR(100) NULL, id_card_doc VARCHAR(255) NOT NULL, status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending', rejection_reason TEXT 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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Table: categories -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS categories ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, parent_id INT UNSIGNED NULL, name VARCHAR(100) NOT NULL, slug VARCHAR(120) NOT NULL UNIQUE, image VARCHAR(255) NULL, status ENUM('active', 'inactive') DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (parent_id) REFERENCES categories (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Table: category_size_options -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS category_size_options ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_id INT UNSIGNED NOT NULL, option_name VARCHAR(100) NOT NULL, sort_order INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE CASCADE, UNIQUE KEY cat_option_unique (category_id, option_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Table: products -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS products ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, seller_id INT UNSIGNED NOT NULL, category_id INT UNSIGNED NOT NULL, title VARCHAR(255) NOT NULL, slug VARCHAR(255) NOT NULL UNIQUE, sku VARCHAR(100) NOT NULL UNIQUE, barcode VARCHAR(100) NULL, description LONGTEXT NOT NULL, price DECIMAL(10,2) NOT NULL, sale_price DECIMAL(10,2) NULL, stock_quantity INT NOT NULL DEFAULT 0, weight_kg DECIMAL(8,2) DEFAULT 0.00, status ENUM('active', 'inactive', 'out_of_stock') DEFAULT 'active', views INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (seller_id) REFERENCES seller_profiles (id) ON DELETE CASCADE, FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE RESTRICT, INDEX idx_product_price (price, sale_price), INDEX idx_product_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Table: product_images -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS product_images ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id INT UNSIGNED NOT NULL, image_path VARCHAR(255) NOT NULL, is_primary TINYINT(1) DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Table: product_variants -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS product_variants ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id INT UNSIGNED NOT NULL, variant_type VARCHAR(50) NOT NULL, variant_value VARCHAR(100) NOT NULL, price_modifier DECIMAL(10,2) DEFAULT 0.00, stock_quantity INT NOT NULL DEFAULT 0, FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Table: coupons -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS coupons ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(50) NOT NULL UNIQUE, discount_type ENUM('fixed', 'percent') NOT NULL DEFAULT 'fixed', discount_value DECIMAL(10,2) NOT NULL, min_spend DECIMAL(10,2) DEFAULT 0.00, max_discount DECIMAL(10,2) NULL, usage_limit INT DEFAULT NULL, used_count INT DEFAULT 0, start_date DATETIME NOT NULL, end_date DATETIME NOT NULL, status ENUM('active', 'inactive') DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Table: orders & order_items -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS orders ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_number VARCHAR(50) NOT NULL UNIQUE, user_id INT UNSIGNED NOT NULL, seller_id INT UNSIGNED NOT NULL, coupon_id INT UNSIGNED NULL, discount_amount DECIMAL(10,2) DEFAULT 0.00, total_amount DECIMAL(10,2) NOT NULL, shipping_address TEXT NOT NULL, status ENUM('pending', 'waiting_payment', 'paid', 'preparing', 'shipping', 'delivered', 'cancelled', 'refunded') DEFAULT 'pending', payment_slip VARCHAR(255) NULL, tracking_number VARCHAR(100) 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 RESTRICT, FOREIGN KEY (seller_id) REFERENCES seller_profiles (id) ON DELETE RESTRICT, FOREIGN KEY (coupon_id) REFERENCES coupons (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS order_items ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, variant_info VARCHAR(255) NULL, price DECIMAL(10,2) NOT NULL, quantity INT NOT NULL, subtotal DECIMAL(10,2) NOT NULL, FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE, FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Table: reviews -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS reviews ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, rating TINYINT UNSIGNED NOT NULL CHECK (rating BETWEEN 1 AND 5), comment TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE, FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Table: wishlists -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS wishlists ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, product_id INT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY user_product_unique (user_id, product_id), FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, FOREIGN KEY (product_id) REFERENCES products (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Table: banners -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS banners ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(150) NOT NULL, image_path VARCHAR(255) NOT NULL, link_url VARCHAR(255) NULL, sort_order INT DEFAULT 0, status ENUM('active', 'inactive') DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Table: promotions -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS promotions ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(150) NOT NULL, description TEXT NULL, banner_image VARCHAR(255) NULL, start_date DATETIME NOT NULL, end_date DATETIME NOT NULL, status ENUM('active', 'inactive') DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- Table: news -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS news ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, slug VARCHAR(255) NOT NULL UNIQUE, content LONGTEXT NOT NULL, cover_image VARCHAR(255) NULL, status ENUM('published', 'draft') DEFAULT 'published', 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; -- -------------------------------------------------------- -- Table: support_tickets -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS support_tickets ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT UNSIGNED NOT NULL, subject VARCHAR(255) NOT NULL, message TEXT NOT NULL, status ENUM('open', 'in_progress', 'resolved', 'closed') DEFAULT 'open', 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 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- -------------------------------------------------------- -- SEED DATA (Default Accounts, Categories, Shops & Sample Data) -- Password for all accounts: -- admin : Admin@123 -- customer : Customer@123 -- seller : Seller@123 -- -------------------------------------------------------- -- Insert Seed Users with Valid BCrypt Hashes INSERT INTO users (id, username, email, password, full_name, phone, role, status) VALUES (1, 'admin', 'admin@marketplace.com', '$2y$10$kmPU2h4p4lM13YmexO7N7e6upsni7ujclIbGElGz.ggE2AxYEd8i', 'Platform Administrator', '0812345678', 'admin', 'active'), (2, 'customer', 'customer@marketplace.com', '$2y$10$kmPU2h4p4lM13YmexO7N7e6upsni7ujclIbGElGz.ggE2AxYEd8i', 'John Doe Customer', '0898765432', 'customer', 'active'), (3, 'seller', 'seller@marketplace.com', '$2y$10$kmPU2h4p4lM13YmexO7N7e6upsni7ujclIbGElGz.ggE2AxYEd8i', 'Jane Smith Seller', '0855555555', 'customer', 'active'); -- Insert Seed Seller Profile for User ID 3 INSERT INTO seller_profiles (id, user_id, shop_name, slug, description, phone, address, promptpay_number, bank_account, id_card_doc, status) VALUES (1, 3, 'Tech & Fashion Hub', 'tech-fashion-hub', 'Official flagship store for premium electronics and apparel.', '0855555555', '123 Sukhumvit Road, Bangkok 10110', '0855555555', 'KBANK 123-4-56789-0', 'id_card_seller3.pdf', 'approved'); -- Insert Seed Categories INSERT INTO categories (id, parent_id, name, slug, status) VALUES (1, NULL, 'Electronics', 'electronics', 'active'), (2, NULL, 'Apparel & Clothing', 'apparel-clothing', 'active'), (3, 1, 'Smartphones & Accessories', 'smartphones-accessories', 'active'), (4, 2, 'Men\'s Fashion', 'mens-fashion', 'active'); -- Insert Seed Products INSERT INTO products (id, seller_id, category_id, title, slug, sku, barcode, description, price, sale_price, stock_quantity, weight_kg, status) VALUES (1, 1, 3, 'Wireless Noise-Canceling Headphones', 'wireless-noise-canceling-headphones', 'SKU-HEADSET-001', '8850001112223', 'High fidelity sound with active noise cancelation.', 2990.00, 2490.00, 50, 0.45, 'active'), (2, 1, 4, 'Premium Cotton T-Shirt', 'premium-cotton-t-shirt', 'SKU-TSHIRT-001', '8850001112224', '100% Organic breathable cotton material.', 590.00, 450.00, 100, 0.20, 'active'); -- Insert Seed Product Images INSERT INTO product_images (product_id, image_path, is_primary) VALUES (1, 'assets/images/sample-headphone.jpg', 1), (2, 'assets/images/sample-tshirt.jpg', 1); -- Insert Seed Product Variants INSERT INTO product_variants (product_id, variant_type, variant_value, price_modifier, stock_quantity) VALUES (2, 'size', 'S', 0.00, 25), (2, 'size', 'M', 0.00, 25), (2, 'size', 'L', 0.00, 25), (2, 'size', 'XL', 50.00, 25); -- Insert Seed Coupon INSERT INTO coupons (code, discount_type, discount_value, min_spend, start_date, end_date, status) VALUES ('WELCOME100', 'fixed', 100.00, 500.00, '2026-01-01 00:00:00', '2026-12-31 23:59:59', 'active'); -- Insert Seed Order INSERT INTO orders (id, order_number, user_id, seller_id, coupon_id, discount_amount, total_amount, shipping_address, status) VALUES (1, 'ORD-20260801-0001', 2, 1, 1, 100.00, 2390.00, '456 Rama 9 Road, Huai Khwang, Bangkok 10310', 'paid'); INSERT INTO order_items (order_id, product_id, variant_info, price, quantity, subtotal) VALUES (1, 1, 'Standard Color', 2490.00, 1, 2490.00); -- -------------------------------------------------------- -- Table: store_reviews -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS store_reviews ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id INT UNSIGNED NOT NULL, seller_id INT UNSIGNED NOT NULL, user_id INT UNSIGNED NOT NULL, rating TINYINT UNSIGNED NOT NULL CHECK (rating BETWEEN 1 AND 5), comment TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE, FOREIGN KEY (seller_id) REFERENCES seller_profiles (id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, UNIQUE KEY unique_order_review (order_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

| ver. 1.4 | Github | . | PHP 7.4.33 | Generation time: 0.24 | proxy | phpinfo | Settings