File manager

File manager - Edit - /home/webapp69.cm.in.th/u69319090041/Shop41/schema.sql

Back
-- ============================================================ -- shop9_db \| Multi-Vendor Schema (v2.0) -- อัปเดตจาก Single-Vendor → Multi-Vendor -- ทุกตารางใช้ CREATE TABLE IF NOT EXISTS (safe ทั้ง fresh & existing) -- ============================================================ SET FOREIGN_KEY_CHECKS = 0; -- ============================================================ -- TABLE 1: users -- เพิ่ม role 'vendor', status 'pending', column avatar -- ============================================================ CREATE TABLE IF NOT EXISTS `users` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `fullname` VARCHAR(100) NOT NULL, `email` VARCHAR(100) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL, `phone` VARCHAR(20) DEFAULT NULL, `avatar` VARCHAR(255) DEFAULT NULL, `role` ENUM('customer','vendor','admin') DEFAULT 'customer', `status` ENUM('active','suspended','pending') DEFAULT 'active', `reset_token` VARCHAR(64) DEFAULT NULL, `reset_expires` DATETIME DEFAULT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- TABLE 2: login_attempts (ไม่เปลี่ยน) -- ============================================================ CREATE TABLE IF NOT EXISTS `login_attempts` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `ip_address` VARCHAR(45) NOT NULL, `email` VARCHAR(100) NOT NULL, `attempted_at` DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- TABLE 3: categories (หมวดหมู่หลัก — จัดการโดย Admin) -- เพิ่ม image, sort_order, is_active -- ============================================================ CREATE TABLE IF NOT EXISTS `categories` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(100) NOT NULL, `slug` VARCHAR(100) NOT NULL UNIQUE, `icon` VARCHAR(50) DEFAULT '🛍️', `image` VARCHAR(255) DEFAULT NULL, `sort_order` INT DEFAULT 0, `is_active` TINYINT(1) DEFAULT 1, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX (`is_active`), INDEX (`sort_order`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- TABLE 4: vendors ★ ใหม่ -- โปรไฟล์ร้านค้า (1 user → 1 vendor) -- ============================================================ CREATE TABLE IF NOT EXISTS `vendors` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `user_id` INT NOT NULL UNIQUE, `store_name` VARCHAR(150) NOT NULL, `store_slug` VARCHAR(150) NOT NULL UNIQUE, `store_logo` VARCHAR(255) DEFAULT NULL, `store_banner` VARCHAR(255) DEFAULT NULL, `description` TEXT DEFAULT NULL, `contact_email` VARCHAR(100) DEFAULT NULL, `contact_phone` VARCHAR(20) DEFAULT NULL, `store_address` TEXT DEFAULT NULL, `status` ENUM('pending','approved','suspended','rejected') DEFAULT 'pending', `approved_by` INT DEFAULT NULL COMMENT 'FK → users.id (admin)', `approved_at` DATETIME DEFAULT NULL, `commission_rate` DECIMAL(5,2) DEFAULT 0.00 COMMENT 'เปอร์เซ็นต์ค่าคอมมิชชัน', `bank_name` VARCHAR(100) DEFAULT NULL, `bank_account` VARCHAR(50) DEFAULT NULL, `bank_account_name` VARCHAR(100) DEFAULT NULL, `total_sales` DECIMAL(14,2) DEFAULT 0.00 COMMENT 'Cache ยอดขายสะสม', `rating` DECIMAL(3,2) DEFAULT 0.00 COMMENT 'คะแนนเฉลี่ยร้านค้า', `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, INDEX (`store_slug`), INDEX (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- TABLE 5: vendor_categories ★ ใหม่ -- หมวดหมู่ย่อยของแต่ละร้าน (จัดการโดย Vendor เอง) -- ============================================================ CREATE TABLE IF NOT EXISTS `vendor_categories` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `vendor_id` INT NOT NULL, `category_id` INT DEFAULT NULL COMMENT 'FK → categories (parent หลัก)', `name` VARCHAR(100) NOT NULL, `slug` VARCHAR(100) NOT NULL, `description` TEXT DEFAULT NULL, `sort_order` INT DEFAULT 0, `is_active` TINYINT(1) DEFAULT 1, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`vendor_id`) REFERENCES `vendors`(`id`) ON DELETE CASCADE, FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL, UNIQUE KEY `uq_vendor_cat_slug` (`vendor_id`, `slug`), INDEX (`vendor_id`), INDEX (`category_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- TABLE 6: products -- เพิ่ม vendor_id, vendor_category_id, view_count -- ============================================================ CREATE TABLE IF NOT EXISTS `products` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `vendor_id` INT NOT NULL COMMENT 'FK → vendors (เจ้าของสินค้า)', `category_id` INT NOT NULL COMMENT 'FK → categories (หมวดหลัก)', `vendor_category_id` INT DEFAULT NULL COMMENT 'FK → vendor_categories (หมวดย่อยของร้าน)', `name` VARCHAR(150) NOT NULL, `slug` VARCHAR(150) NOT NULL UNIQUE, `description` TEXT, `price` DECIMAL(10,2) NOT NULL, `normal_price` DECIMAL(10,2) DEFAULT NULL, `main_image` VARCHAR(255) NOT NULL, `is_featured` TINYINT(1) DEFAULT 0, `is_new` TINYINT(1) DEFAULT 1, `view_count` INT DEFAULT 0, `status` ENUM('active','inactive','pending') DEFAULT 'active', `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`vendor_id`) REFERENCES `vendors`(`id`) ON DELETE CASCADE, FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE CASCADE, FOREIGN KEY (`vendor_category_id`) REFERENCES `vendor_categories`(`id`) ON DELETE SET NULL, INDEX (`vendor_id`), INDEX (`category_id`), INDEX (`vendor_category_id`), INDEX (`status`), INDEX (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- TABLE 7: product_images (ไม่เปลี่ยน) -- ============================================================ CREATE TABLE IF NOT EXISTS `product_images` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `product_id` INT NOT NULL, `image_path` VARCHAR(255) NOT NULL, `thumb_path` VARCHAR(255) NOT NULL, `sort_order` INT DEFAULT 0, FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- TABLE 8: product_variants (ไม่เปลี่ยน) -- ============================================================ CREATE TABLE IF NOT EXISTS `product_variants` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `product_id` INT NOT NULL, `size` VARCHAR(20) NOT NULL, `color` VARCHAR(50) NOT NULL, `stock` 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 9: addresses (ไม่เปลี่ยน) -- ============================================================ CREATE TABLE IF NOT EXISTS `addresses` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `user_id` INT NOT NULL, `recipient_name` VARCHAR(100) NOT NULL, `phone` VARCHAR(20) NOT NULL, `address_line` TEXT NOT NULL, `province` VARCHAR(100) NOT NULL, `amphoe` VARCHAR(100) NOT NULL, `district` VARCHAR(100) NOT NULL, `zipcode` VARCHAR(10) NOT NULL, `is_default` TINYINT(1) DEFAULT 0, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- TABLE 10: orders (ออเดอร์หลักจากลูกค้า) -- เพิ่ม note -- ============================================================ CREATE TABLE IF NOT EXISTS `orders` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_no` VARCHAR(30) NOT NULL UNIQUE, `user_id` INT NOT NULL, `total_amount` DECIMAL(10,2) NOT NULL, `shipping_fee` DECIMAL(10,2) DEFAULT 0.00, `payment_method` ENUM('bank_transfer','promptpay','cod') NOT NULL, `payment_status` ENUM('pending','paid','failed') DEFAULT 'pending', `order_status` ENUM('pending','processing','shipped','completed','cancelled') DEFAULT 'pending', `payment_slip` VARCHAR(255) DEFAULT NULL, `tracking_number` VARCHAR(50) DEFAULT NULL, `shipping_name` VARCHAR(100) NOT NULL, `shipping_phone` VARCHAR(20) NOT NULL, `shipping_address` TEXT NOT NULL, `note` TEXT DEFAULT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, INDEX (`order_no`), INDEX (`order_status`), INDEX (`payment_status`), INDEX (`created_at`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- TABLE 11: vendor_orders ★ ใหม่ (สำคัญมาก) -- Sub-order แยกตามร้านค้า ทำให้แต่ละ vendor เห็นเฉพาะออเดอร์ของตัวเอง -- ============================================================ CREATE TABLE IF NOT EXISTS `vendor_orders` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_id` INT NOT NULL COMMENT 'FK → orders (main order)', `vendor_id` INT NOT NULL COMMENT 'FK → vendors', `subtotal` DECIMAL(10,2) NOT NULL COMMENT 'ยอดรวมของร้านนี้ก่อนหักค่าคอม', `commission` DECIMAL(10,2) DEFAULT 0.00 COMMENT 'ค่าคอมมิชชันที่หักให้แพลตฟอร์ม', `vendor_earning` DECIMAL(10,2) DEFAULT 0.00 COMMENT 'ยอดสุทธิที่ vendor ได้รับ', `status` ENUM('pending','processing','shipped','completed','cancelled') DEFAULT 'pending', `tracking_number` VARCHAR(50) DEFAULT NULL, `note` TEXT DEFAULT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE, FOREIGN KEY (`vendor_id`) REFERENCES `vendors`(`id`) ON DELETE CASCADE, INDEX (`order_id`), INDEX (`vendor_id`), INDEX (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- TABLE 12: order_items -- เพิ่ม vendor_order_id, vendor_id (denormalized เพื่อ query เร็ว) -- ============================================================ CREATE TABLE IF NOT EXISTS `order_items` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_id` INT NOT NULL, `vendor_order_id` INT NOT NULL COMMENT 'FK → vendor_orders', `product_id` INT NOT NULL, `vendor_id` INT NOT NULL COMMENT 'Denormalized FK เพื่อ query ง่าย', `product_name` VARCHAR(150) NOT NULL, `variant_info` VARCHAR(100) NOT 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 (`vendor_order_id`) REFERENCES `vendor_orders`(`id`) ON DELETE CASCADE, FOREIGN KEY (`vendor_id`) REFERENCES `vendors`(`id`) ON DELETE CASCADE, INDEX (`order_id`), INDEX (`vendor_order_id`), INDEX (`vendor_id`), INDEX (`product_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- TABLE 13: vendor_applications ★ ใหม่ -- ประวัติการสมัครเป็น Vendor (ใช้โดย Admin อนุมัติ) -- ============================================================ CREATE TABLE IF NOT EXISTS `vendor_applications` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `user_id` INT NOT NULL, `store_name` VARCHAR(150) NOT NULL, `description` TEXT DEFAULT NULL, `document` VARCHAR(255) DEFAULT NULL COMMENT 'ไฟล์เอกสารประกอบ (ถ้ามี)', `status` ENUM('pending','approved','rejected') DEFAULT 'pending', `admin_note` TEXT DEFAULT NULL COMMENT 'เหตุผลที่อนุมัติ/ปฏิเสธ', `reviewed_by` INT DEFAULT NULL COMMENT 'FK → users.id (admin)', `reviewed_at` DATETIME DEFAULT NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, INDEX (`user_id`), INDEX (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- TABLE 14: system_settings -- ============================================================ CREATE TABLE IF NOT EXISTS `system_settings` ( `setting_key` VARCHAR(50) PRIMARY KEY, `setting_value` JSON NOT NULL, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- SEED DATA -- (FOREIGN_KEY_CHECKS = 0 ยังคงเปิดอยู่จนกว่าจะสิ้นสุดไฟล์) -- ============================================================ -- Seed: Admin + Customer (password: password123) INSERT INTO `users` (`id`, `fullname`, `email`, `password`, `phone`, `role`, `status`) VALUES (1, 'Admin Store', 'admin@shop.com', '$2y$10$E9ZhfZ/arsvsSFMAuEd.huZlfkCLZzmgcovAev3gnqaEvTwfU3Ami', '0812345678', 'admin', 'active'), (2, 'สมชาย สายช้อป', 'user@shop.com', '$2y$10$E9ZhfZ/arsvsSFMAuEd.huZlfkCLZzmgcovAev3gnqaEvTwfU3Ami', '0898765432', 'customer', 'active'), (3, 'ร้านเสื้อมาตรฐาน', 'vendor1@shop.com','$2y$10$E9ZhfZ/arsvsSFMAuEd.huZlfkCLZzmgcovAev3gnqaEvTwfU3Ami', '0812223334', 'vendor', 'active'), (4, 'แฟชั่นสตรีท', 'vendor2@shop.com','$2y$10$E9ZhfZ/arsvsSFMAuEd.huZlfkCLZzmgcovAev3gnqaEvTwfU3Ami', '0823334445', 'vendor', 'active') ON DUPLICATE KEY UPDATE `updated_at` = `updated_at`; -- Seed: Categories หลัก (Admin จัดการ) INSERT INTO `categories` (`id`, `name`, `slug`, `icon`, `sort_order`, `is_active`) VALUES (1, 'เสื้อยืด (T-Shirt)', 't-shirt', '👕', 1, 1), (2, 'เสื้อเชิ้ต (Shirt)', 'shirt', '👔', 2, 1), (3, 'กางเกง (Pants)', 'pants', '👖', 3, 1), (4, 'เดรส & กระโปรง (Dress)', 'dress', '👗', 4, 1), (5, 'รองเท้า (Shoes)', 'shoes', '👟', 5, 1), (6, 'กระเป๋า (Bags)', 'bags', '👜', 6, 1), (7, 'เครื่องประดับ (Accessories)','accessories','💎', 7, 1) ON DUPLICATE KEY UPDATE `is_active` = VALUES(`is_active`), `sort_order` = VALUES(`sort_order`); -- Seed: Vendors (mock ร้านค้าตัวอย่าง) INSERT INTO `vendors` (`id`, `user_id`, `store_name`, `store_slug`, `description`, `contact_email`, `contact_phone`, `status`, `approved_by`, `approved_at`, `commission_rate`) VALUES (1, 3, 'ร้านเสื้อมาตรฐาน', 'standard-tee', 'ร้านเสื้อยืดคุณภาพดี ราคาเป็นมิตร มีสินค้าให้เลือกหลากหลาย', 'vendor1@shop.com', '0812223334', 'approved', 1, NOW(), 5.00), (2, 4, 'แฟชั่นสตรีท', 'fashion-street', 'เสื้อผ้าสไตล์สตรีทแฟชั่น ทันสมัย อัปเดตทุกสัปดาห์', 'vendor2@shop.com', '0823334445', 'approved', 1, NOW(), 5.00) ON DUPLICATE KEY UPDATE `store_name` = VALUES(`store_name`), `status` = VALUES(`status`), `updated_at` = VALUES(`updated_at`); -- Seed: Vendor Categories (หมวดหมู่ย่อยของแต่ละร้าน) -- ปิด FK ชั่วคราวเพื่อให้ INSERT ได้โดยไม่ติด constraint SET FOREIGN_KEY_CHECKS = 0; INSERT INTO `vendor_categories` (`id`, `vendor_id`, `category_id`, `name`, `slug`, `sort_order`, `is_active`) VALUES (1, 1, 1, 'เสื้อเปล่า (Blank Tee)', 'blank-tee', 1, 1), (2, 1, 1, 'เสื้อพิมพ์ลาย (Printed)', 'printed-tee', 2, 1), (3, 1, 1, 'เสื้อ Oversize', 'oversize-tee', 3, 1), (4, 2, 1, 'สตรีทแฟชั่น', 'street-fashion', 1, 1), (5, 2, 3, 'กางเกงสตรีท', 'street-pants', 2, 1) ON DUPLICATE KEY UPDATE `is_active` = VALUES(`is_active`), `sort_order` = VALUES(`sort_order`); SET FOREIGN_KEY_CHECKS = 1;

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