File manager - Edit - /home/webapp69.cm.in.th/u69319090039/Shop39/public/database_hosting.sql
Back
-- ============================================================ -- Production Database for Hosting (ไม่มี CREATE DATABASE / USE) -- Import ไฟล์นี้เข้าฐานข้อมูลที่สร้างไว้ใน phpMyAdmin แล้วเท่านั้น -- ============================================================ SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS support_tickets, news, promotions, banners, coupons, wishlists, 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, 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 -- Password for all accounts: Admin@123 / Customer@123 / Seller@123 -- -------------------------------------------------------- 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 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 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 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');
| ver. 1.4 |
Github
|
.
| PHP 7.4.33 | Generation time: 0.25 |
proxy
|
phpinfo
|
Settings