File manager

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

Back
-- ============================================================ -- MIGRATION SCRIPT: shop9_db → Multi-Vendor (v1 → v2) -- ใช้สำหรับ Database ที่มีข้อมูลอยู่แล้ว (ไม่ลบข้อมูลเดิม!) -- วิธีใช้: รันใน phpMyAdmin > SQL tab หรือ mysql CLI -- ============================================================ SET FOREIGN_KEY_CHECKS = 0; -- ============================================================ -- STEP 1: แก้ไขตาราง users -- เพิ่ม role 'vendor' และ status 'pending' -- เพิ่ม column avatar -- ============================================================ -- เพิ่ม avatar column (ถ้ายังไม่มี) ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `avatar` VARCHAR(255) DEFAULT NULL AFTER `phone`; -- แก้ไข ENUM role: customer → customer, vendor, admin ALTER TABLE `users` MODIFY COLUMN `role` ENUM('customer','vendor','admin') DEFAULT 'customer'; -- แก้ไข ENUM status: เพิ่ม pending ALTER TABLE `users` MODIFY COLUMN `status` ENUM('active','suspended','pending') DEFAULT 'active'; SELECT 'STEP 1 DONE: users table updated' AS migration_status; -- ============================================================ -- STEP 2: แก้ไขตาราง categories -- เพิ่ม image, sort_order, is_active -- ============================================================ ALTER TABLE `categories` ADD COLUMN IF NOT EXISTS `image` VARCHAR(255) DEFAULT NULL AFTER `icon`, ADD COLUMN IF NOT EXISTS `sort_order` INT DEFAULT 0 AFTER `image`, ADD COLUMN IF NOT EXISTS `is_active` TINYINT(1) DEFAULT 1 AFTER `sort_order`; -- เพิ่ม index ถ้ายังไม่มี ALTER TABLE `categories` ADD INDEX IF NOT EXISTS `idx_is_active` (`is_active`), ADD INDEX IF NOT EXISTS `idx_sort_order` (`sort_order`); -- Seed หมวดหมู่เพิ่มเติม 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 `sort_order` = VALUES(`sort_order`), `is_active` = VALUES(`is_active`); SELECT 'STEP 2 DONE: categories table updated' AS migration_status; -- ============================================================ -- STEP 3: สร้างตาราง vendors (ถ้ายังไม่มี) -- ============================================================ 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, `approved_at` DATETIME DEFAULT NULL, `commission_rate` DECIMAL(5,2) DEFAULT 0.00, `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, `rating` DECIMAL(3,2) DEFAULT 0.00, `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; SELECT 'STEP 3 DONE: vendors table created' AS migration_status; -- ============================================================ -- STEP 4: สร้างตาราง vendor_categories (ถ้ายังไม่มี) -- ============================================================ CREATE TABLE IF NOT EXISTS `vendor_categories` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `vendor_id` INT NOT NULL, `category_id` INT DEFAULT NULL, `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; SELECT 'STEP 4 DONE: vendor_categories table created' AS migration_status; -- ============================================================ -- STEP 5: แก้ไขตาราง products -- เพิ่ม vendor_id, vendor_category_id, view_count -- * ใช้ DEFAULT vendor ชั่วคราว (id=1) สำหรับสินค้าเดิม * -- ============================================================ -- เพิ่ม vendor_id (ถ้ายังไม่มี) พร้อม default = 1 ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `vendor_id` INT DEFAULT 1 AFTER `id`, ADD COLUMN IF NOT EXISTS `vendor_category_id` INT DEFAULT NULL AFTER `category_id`, ADD COLUMN IF NOT EXISTS `view_count` INT DEFAULT 0 AFTER `is_new`; -- เพิ่ม ENUM 'pending' ให้ status ALTER TABLE `products` MODIFY COLUMN `status` ENUM('active','inactive','pending') DEFAULT 'active'; -- เพิ่ม index ALTER TABLE `products` ADD INDEX IF NOT EXISTS `idx_vendor_id` (`vendor_id`), ADD INDEX IF NOT EXISTS `idx_vendor_category_id` (`vendor_category_id`); -- เพิ่ม foreign key ถ้ายังไม่มี (ignore error ถ้ามีแล้ว) -- NOTE: MySQL ไม่มี ADD FOREIGN KEY IF NOT EXISTS — ใช้ IGNORE pattern -- ถ้า FK มีอยู่แล้วจะ error ให้ ignore ได้ SELECT 'STEP 5 DONE: products table updated' AS migration_status; -- ============================================================ -- STEP 6: แก้ไขตาราง orders -- เพิ่ม note column -- ============================================================ ALTER TABLE `orders` ADD COLUMN IF NOT EXISTS `note` TEXT DEFAULT NULL AFTER `shipping_address`; ALTER TABLE `orders` ADD INDEX IF NOT EXISTS `idx_payment_status` (`payment_status`); SELECT 'STEP 6 DONE: orders table updated' AS migration_status; -- ============================================================ -- STEP 7: สร้างตาราง vendor_orders (ถ้ายังไม่มี) -- ============================================================ CREATE TABLE IF NOT EXISTS `vendor_orders` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_id` INT NOT NULL, `vendor_id` INT NOT NULL, `subtotal` DECIMAL(10,2) NOT NULL, `commission` DECIMAL(10,2) DEFAULT 0.00, `vendor_earning` DECIMAL(10,2) DEFAULT 0.00, `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; SELECT 'STEP 7 DONE: vendor_orders table created' AS migration_status; -- ============================================================ -- STEP 8: แก้ไขตาราง order_items -- เพิ่ม vendor_order_id, vendor_id -- ============================================================ ALTER TABLE `order_items` ADD COLUMN IF NOT EXISTS `vendor_order_id` INT DEFAULT NULL AFTER `order_id`, ADD COLUMN IF NOT EXISTS `vendor_id` INT DEFAULT NULL AFTER `vendor_order_id`; ALTER TABLE `order_items` ADD INDEX IF NOT EXISTS `idx_vendor_order_id` (`vendor_order_id`), ADD INDEX IF NOT EXISTS `idx_order_items_vendor_id` (`vendor_id`), ADD INDEX IF NOT EXISTS `idx_product_id` (`product_id`); SELECT 'STEP 8 DONE: order_items table updated' AS migration_status; -- ============================================================ -- STEP 9: สร้างตาราง vendor_applications (ถ้ายังไม่มี) -- ============================================================ 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, `status` ENUM('pending','approved','rejected') DEFAULT 'pending', `admin_note` TEXT DEFAULT NULL, `reviewed_by` INT DEFAULT NULL, `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; SELECT 'STEP 9 DONE: vendor_applications table created' AS migration_status; -- ============================================================ -- STEP 10: Seed Demo Vendors + Users (ถ้ายังไม่มี) -- ============================================================ -- เพิ่ม user vendor ตัวอย่าง (password: password123) INSERT INTO `users` (`id`, `fullname`, `email`, `password`, `phone`, `role`, `status`) VALUES (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 `role` = VALUES(`role`); -- เพิ่มร้านค้าตัวอย่าง 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 `status` = VALUES(`status`); -- Assign สินค้าเดิม (ทั้งหมด) → vendor_id = 1 (ถ้า vendor_id ยังเป็น NULL/0) UPDATE `products` SET `vendor_id` = 1 WHERE `vendor_id` IS NULL OR `vendor_id` = 0; SELECT 'STEP 10 DONE: Seed data inserted' AS migration_status; -- ============================================================ SET FOREIGN_KEY_CHECKS = 1; SELECT '✅ MIGRATION COMPLETE — Multi-Vendor schema v2.0 ready!' AS final_status; -- ============================================================

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