File manager

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

Back
-- Production Database Blueprint for Multi-Vendor Marketplace (PHP 8 + MySQL) -- หมายเหตุ: ไม่มี CREATE DATABASE / USE — Import ไฟล์นี้เข้าฐานข้อมูลที่สร้างไว้ใน phpMyAdmin ได้เลย SET FOREIGN_KEY_CHECKS = 0; -- ลบตารางตามลำดับ child → parent (เพื่อหลีกเลี่ยง FK constraint error) DROP TABLE IF EXISTS support_tickets; DROP TABLE IF EXISTS wishlists; DROP TABLE IF EXISTS reviews; DROP TABLE IF EXISTS order_items; DROP TABLE IF EXISTS orders; DROP TABLE IF EXISTS coupons; DROP TABLE IF EXISTS variant_option_values; DROP TABLE IF EXISTS product_variants; DROP TABLE IF EXISTS product_images; DROP TABLE IF EXISTS product_option_values; DROP TABLE IF EXISTS product_options; DROP TABLE IF EXISTS products; DROP TABLE IF EXISTS categories; DROP TABLE IF EXISTS seller_profiles; DROP TABLE IF EXISTS users; DROP TABLE IF EXISTS banners; DROP TABLE IF EXISTS promotions; DROP TABLE IF EXISTS news; 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', 'suspended', 'banned') NOT NULL DEFAULT 'pending', rejection_reason TEXT NULL, suspended_reason TEXT NULL, suspended_at DATETIME NULL, deleted_at DATETIME 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: 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_options -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS product_options ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id INT UNSIGNED NOT NULL, option_name VARCHAR(100) NOT NULL, sort_order INT 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_option_values -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS product_option_values ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, option_id INT UNSIGNED NOT NULL, value VARCHAR(100) NOT NULL, sort_order INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (option_id) REFERENCES product_options (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, sku VARCHAR(100) NULL, price DECIMAL(10,2) NULL, sale_price DECIMAL(10,2) NULL, stock INT NOT NULL DEFAULT 0, image VARCHAR(255) NULL, 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: variant_option_values -- -------------------------------------------------------- CREATE TABLE IF NOT EXISTS variant_option_values ( variant_id INT UNSIGNED NOT NULL, option_value_id INT UNSIGNED NOT NULL, PRIMARY KEY (variant_id, option_value_id), FOREIGN KEY (variant_id) REFERENCES product_variants (id) ON DELETE CASCADE, FOREIGN KEY (option_value_id) REFERENCES product_option_values (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 (ใช้ column ที่ตรงกับ schema จริง) INSERT INTO product_variants (product_id, sku, price, sale_price, stock) VALUES (2, 'SKU-TSHIRT-S', 590.00, 450.00, 25), (2, 'SKU-TSHIRT-M', 590.00, 450.00, 25), (2, 'SKU-TSHIRT-L', 590.00, 450.00, 25), (2, 'SKU-TSHIRT-XL', 640.00, 500.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);

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