File manager

File manager - Edit - /home/webapp69.cm.in.th/u69319090006/shop/schema.sql

Back
-- ========================================================================= -- SkyMall E-commerce Database Schema & Seed Data -- ========================================================================= CREATE DATABASE IF NOT EXISTS `skymall_db` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE `skymall_db`; -- Drop existing tables to refresh schema cleanly SET FOREIGN_KEY_CHECKS = 0; DROP TABLE IF EXISTS `banned_users`; DROP TABLE IF EXISTS `order_items`; DROP TABLE IF EXISTS `orders`; DROP TABLE IF EXISTS `product_variants`; DROP TABLE IF EXISTS `product_images`; DROP TABLE IF EXISTS `products`; DROP TABLE IF EXISTS `coupons`; DROP TABLE IF EXISTS `users`; SET FOREIGN_KEY_CHECKS = 1; -- 1. Users CREATE TABLE `users` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `username` VARCHAR(50) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL, `email` VARCHAR(100) NOT NULL UNIQUE, `name` VARCHAR(100) NOT NULL, `role` ENUM('client', 'seller', 'admin') DEFAULT 'client', `seller_level` ENUM('Regular', 'Corporate') DEFAULT 'Regular', `shop_name` VARCHAR(150) NULL, `phone` VARCHAR(20) NULL, `points` INT DEFAULT 0, `is_banned` TINYINT(1) DEFAULT 0, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX `idx_role` (`role`), INDEX `idx_is_banned` (`is_banned`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 2. Banned Users Log CREATE TABLE `banned_users` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `user_id` INT NOT NULL, `banned_by` INT NOT NULL, `reason` TEXT NOT NULL, `banned_at` DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, FOREIGN KEY (`banned_by`) REFERENCES `users`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 3. Products CREATE TABLE `products` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `seller_id` INT NOT NULL, `name` VARCHAR(255) NOT NULL, `category` VARCHAR(100) NOT NULL, `description` TEXT NULL, `price` DECIMAL(10, 2) NOT NULL DEFAULT 0.00, `original_price` DECIMAL(10, 2) NULL DEFAULT 0.00, `stock` INT NOT NULL DEFAULT 0, `sold_count` INT NOT NULL DEFAULT 0, `rating` DECIMAL(3, 2) DEFAULT 5.00, `reviews_count` INT DEFAULT 0, `is_official` TINYINT(1) DEFAULT 0, `active` TINYINT(1) DEFAULT 1, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`seller_id`) REFERENCES `users`(`id`) ON DELETE CASCADE, INDEX `idx_category` (`category`), INDEX `idx_active` (`active`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 4. Product Images CREATE TABLE `product_images` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `product_id` INT NOT NULL, `image_path` VARCHAR(255) NOT NULL, `is_primary` TINYINT(1) DEFAULT 0, `sort_order` INT DEFAULT 0, FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 5. Product Variants CREATE TABLE `product_variants` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `product_id` INT NOT NULL, `name` VARCHAR(150) NOT NULL, `color` VARCHAR(50) DEFAULT 'Default', `size` VARCHAR(50) DEFAULT 'Default', `price` DECIMAL(10, 2) NOT NULL, `stock` INT NOT NULL DEFAULT 0, `active` TINYINT(1) DEFAULT 1, FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 6. Coupons CREATE TABLE `coupons` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `code` VARCHAR(50) NOT NULL UNIQUE, `discount_type` ENUM('fixed', 'percent') DEFAULT 'fixed', `discount_value` DECIMAL(10, 2) NOT NULL, `min_spend` DECIMAL(10, 2) DEFAULT 0.00, `max_discount` DECIMAL(10, 2) NULL, `expiry_date` DATE NOT NULL, `usage_limit` INT DEFAULT NULL, `used_count` INT DEFAULT 0, `is_active` TINYINT(1) DEFAULT 1, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 7. Orders CREATE TABLE `orders` ( `id` VARCHAR(50) PRIMARY KEY, `customer_id` INT NOT NULL, `customer_name` VARCHAR(100) NOT NULL, `customer_phone` VARCHAR(20) NOT NULL, `shipping_address` TEXT NOT NULL, `subtotal` DECIMAL(10, 2) NOT NULL, `discount` DECIMAL(10, 2) DEFAULT 0.00, `coupon_code` VARCHAR(50) NULL, `total` DECIMAL(10, 2) NOT NULL, `payment_method` VARCHAR(50) NOT NULL, `status` ENUM('Pending', 'Paid', 'Failed', 'Shipped', 'Success', 'Cancelled') DEFAULT 'Pending', `slip_image` VARCHAR(255) NULL, `reject_reason` TEXT NULL, `cancel_reason` TEXT NULL, `tracking_numbers` JSON NULL, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (`customer_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT, INDEX `idx_status` (`status`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 8. Order Items CREATE TABLE `order_items` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_id` VARCHAR(50) NOT NULL, `product_id` INT NOT NULL, `variant_id` INT NULL, `seller_id` INT NOT NULL, `product_name` VARCHAR(255) NOT NULL, `variant_name` VARCHAR(150) NULL, `price` DECIMAL(10, 2) NOT NULL, `quantity` INT NOT NULL, `total` 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, FOREIGN KEY (`seller_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================================= -- SEED DATA (Default Accounts & Products) -- รหัสผ่านเริ่มต้นของทุกบัญชีคือ: 123456 -- ========================================================================= -- Seed Users INSERT INTO `users` (`id`, `username`, `password`, `email`, `name`, `role`, `seller_level`, `shop_name`, `phone`, `points`) VALUES (1, 'admin', '$2y$10$Lba.xFfdLZKRA.hUquhbDOuoZqt4sIbux4AGSfJNTaV9Rvbb2Ynoq', 'admin@skymall.com', 'ผู้ดูแลระบบ SkyMall', 'admin', 'Corporate', 'SkyMall Official Store', '0891112233', 1500), (2, 'seller1', '$2y$10$Lba.xFfdLZKRA.hUquhbDOuoZqt4sIbux4AGSfJNTaV9Rvbb2Ynoq', 'seller1@skymall.com', 'สมชาย ไอทีการค้า', 'seller', 'Corporate', 'ร้านอุปกรณ์ไอที SkyTech', '0812345678', 500), (3, 'seller2', '$2y$10$Lba.xFfdLZKRA.hUquhbDOuoZqt4sIbux4AGSfJNTaV9Rvbb2Ynoq', 'seller2@skymall.com', 'วิภาดา แฟชั่นเฮาส์', 'seller', 'Regular', 'เสื้อผ้าแฟชั่น SkyThreads', '0898765432', 200), (4, 'customer1', '$2y$10$Lba.xFfdLZKRA.hUquhbDOuoZqt4sIbux4AGSfJNTaV9Rvbb2Ynoq', 'customer1@skymall.com', 'กิตติศักดิ์ ช้อปปิ้ง', 'client', 'Regular', NULL, '0865554433', 1450); -- Seed Products INSERT INTO `products` (`id`, `seller_id`, `name`, `category`, `description`, `price`, `original_price`, `stock`, `sold_count`, `rating`, `reviews_count`, `is_official`, `active`) VALUES (1, 2, 'Smartphone SkyPhone Pro Max', 'ไอที/สมาร์ทโฟน', 'สมาร์ทโฟนดีไซน์พรีเมียม กล้องระดับโปร ชิปประมวลผลความเร็วสูง แบตเตอรี่อึดตลอดวัน', 32900.00, 35900.00, 25, 120, 4.80, 42, 1, 1), (2, 3, 'เสื้อเชิ้ตลายสก็อตพรีเมียม SkyShirt', 'เสื้อผ้า/แฟชั่น', 'เสื้อเชิ้ตแขนยาวเนื้อผ้าคอตตอน 100% สวมใส่สบาย ระบายอากาศได้ดี เหมาะสำหรับทุกโอกาส', 890.00, 1290.00, 50, 85, 4.60, 28, 0, 1), (3, 2, 'หูฟังไร้สายบูลทูธ Noise Cancelling', 'ไอที/สมาร์ทโฟน', 'หูฟังไร้สายระบบตัดเสียงรบกวนอัจฉริยะ เสียงเบสนุ่มลึก ฟังเพลงต่อเนื่องยาวนานถึง 30 ชั่วโมง', 3590.00, 4990.00, 30, 210, 4.90, 65, 1, 1); -- Seed Product Images INSERT INTO `product_images` (`product_id`, `image_path`, `is_primary`, `sort_order`) VALUES (1, 'https://images.unsplash.com/photo-1511707171634-5f897ff02aa9?auto=format&fit=crop&w=600&q=80', 1, 1), (1, 'https://images.unsplash.com/photo-1592899677977-9c10ca588bbd?auto=format&fit=crop&w=600&q=80', 0, 2), (2, 'https://images.unsplash.com/photo-1596755094514-f87e34085b2c?auto=format&fit=crop&w=600&q=80', 1, 1), (3, 'https://images.unsplash.com/photo-1505740420928-5e560c06d30e?auto=format&fit=crop&w=600&q=80', 1, 1); -- Seed Product Variants INSERT INTO `product_variants` (`product_id`, `name`, `color`, `size`, `price`, `stock`, `active`) VALUES (1, 'Space Black / 256GB', 'Space Black', '256GB', 32900.00, 15, 1), (1, 'Titanium Silver / 512GB', 'Titanium Silver', '512GB', 37900.00, 10, 1), (2, 'Navy Blue / M', 'Navy Blue', 'M', 890.00, 20, 1), (2, 'Crimson Red / L', 'Crimson Red', 'L', 890.00, 30, 1), (3, 'Matte Black', 'Matte Black', 'Default', 3590.00, 20, 1), (3, 'Ivory White', 'Ivory White', 'Default', 3590.00, 10, 1); -- Seed Coupons INSERT INTO `coupons` (`code`, `discount_type`, `discount_value`, `min_spend`, `max_discount`, `expiry_date`, `is_active`) VALUES ('WELCOME100', 'fixed', 100.00, 500.00, NULL, '2027-12-31', 1), ('SKYVIP20', 'percent', 20.00, 1000.00, 500.00, '2027-12-31', 1), ('FREESHIP', 'fixed', 50.00, 300.00, NULL, '2027-12-31', 1);

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