File manager

File manager - Edit - /home/webapp69.cm.in.th/u69319090040/Shop/database.sql

Back
-- ============================================================ -- NEXUS Commerce - Complete Database Schema & Initial Seed Data -- Import this SQL file into phpMyAdmin / MySQL Workbench -- ============================================================ -- Disable foreign key checks for smooth table dropping & creation SET FOREIGN_KEY_CHECKS = 0; -- 1. Users Table DROP TABLE IF EXISTS `users`; CREATE TABLE `users` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(100) NOT NULL, `username` VARCHAR(50) UNIQUE NOT NULL, `phone` VARCHAR(20) UNIQUE NOT NULL, `email` VARCHAR(150), `password_hash` VARCHAR(255) NOT NULL, `role` ENUM('customer', 'seller', 'creator', 'admin') DEFAULT 'customer', `level` ENUM('Explorer', 'Shopper', 'Insider', 'VIP', 'Elite') DEFAULT 'Explorer', `xp` INT DEFAULT 120, `coins` INT DEFAULT 250, `avatar_url` VARCHAR(255), `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 2. Stores Table DROP TABLE IF EXISTS `stores`; CREATE TABLE `stores` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `user_id` INT NOT NULL, `store_name` VARCHAR(100) NOT NULL, `logo_url` VARCHAR(255), `banner_url` VARCHAR(255), `rating` DECIMAL(3,2) DEFAULT 4.90, `trust_score` INT DEFAULT 98, `response_rate` INT DEFAULT 99, `is_verified` TINYINT(1) DEFAULT 1, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 3. Categories Table DROP TABLE IF EXISTS `categories`; CREATE TABLE `categories` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(100) NOT NULL, `icon_class` VARCHAR(50), `slug` VARCHAR(100) UNIQUE NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 4. Products Table DROP TABLE IF EXISTS `products`; CREATE TABLE `products` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `store_id` INT NOT NULL, `category_id` INT NOT NULL, `title` VARCHAR(255) NOT NULL, `description` TEXT, `price` DECIMAL(10,2) NOT NULL, `original_price` DECIMAL(10,2), `stock` INT DEFAULT 50, `sku` VARCHAR(50), `trust_score` INT DEFAULT 95, `sales_count` INT DEFAULT 1420, `view_count` INT DEFAULT 8900, `rating_avg` DECIMAL(3,2) DEFAULT 4.85, `review_count` INT DEFAULT 328, `is_group_buy` TINYINT(1) DEFAULT 0, `group_price` DECIMAL(10,2), `image_url` VARCHAR(255), `gallery` JSON, `specs` JSON, `ai_summary` JSON, `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`store_id`) REFERENCES `stores`(`id`) ON DELETE CASCADE, FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 5. Collections Table DROP TABLE IF EXISTS `collections`; CREATE TABLE `collections` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `user_id` INT NOT NULL, `title` VARCHAR(150) NOT NULL, `description` TEXT, `cover_image` VARCHAR(255), `likes_count` INT DEFAULT 142, `items` JSON, `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; -- 6. Orders Table DROP TABLE IF EXISTS `orders`; CREATE TABLE `orders` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_sn` VARCHAR(50) UNIQUE NOT NULL, `user_id` INT NOT NULL, `total_amount` DECIMAL(10,2) NOT NULL, `discount_amount` DECIMAL(10,2) DEFAULT 0.00, `coins_used` INT DEFAULT 0, `final_amount` DECIMAL(10,2) NOT NULL, `status` ENUM('pending', 'preparing', 'packed', 'shipped', 'out_for_delivery', 'delivered', 'returned') DEFAULT 'preparing', `tracking_number` VARCHAR(100), `shipping_address` TEXT, `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; -- 7. Order Items Table DROP TABLE IF EXISTS `order_items`; CREATE TABLE `order_items` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_id` INT NOT NULL, `product_id` INT NOT NULL, `quantity` INT NOT NULL, `unit_price` DECIMAL(10,2) NOT NULL, FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE, FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- 8. Quests Table DROP TABLE IF EXISTS `quests`; CREATE TABLE `quests` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `title` VARCHAR(100) NOT NULL, `description` VARCHAR(255), `reward_coins` INT DEFAULT 20, `reward_xp` INT DEFAULT 100, `is_completed` TINYINT(1) DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ============================================================ -- INITIAL SEED DATA -- ============================================================ INSERT INTO `categories` (`id`, `name`, `icon_class`, `slug`) VALUES (1, 'Tech & Gadgets', 'ph-cpu', 'tech'), (2, 'Cyber Fashion', 'ph-t-shirt', 'fashion'), (3, 'Smart Home', 'ph-house-line', 'home'), (4, 'Audio & Studio', 'ph-headphones', 'audio'), (5, 'Gaming Gear', 'ph-game-controller', 'gaming'); INSERT INTO `users` (`id`, `name`, `username`, `phone`, `email`, `password_hash`, `role`, `level`, `xp`, `coins`, `avatar_url`) VALUES (1, 'Alex Vance', 'alexvance', '0812345678', 'alex@nexus.io', 'password123', 'customer', 'Insider', 450, 380, 'https://images.unsplash.com/photo-1534528741775-53994a69daeb?w=150'), (2, 'CyberPulse Store', 'cyberpulse', '0898765432', 'seller@cyberpulse.com', 'password123', 'seller', 'Elite', 2400, 1500, 'https://images.unsplash.com/photo-1618005182384-a83a8bd57fbe?w=150'), (3, 'John Creator', 'johncreator', '0877778888', 'john@creators.com', 'password123', 'creator', 'VIP', 1200, 890, 'https://images.unsplash.com/photo-1507003211169-0a1dd7228f2d?w=150'), (4, 'Super Admin NEXUS', 'admin_nexus', '0800000000', 'admin@nexus.io', 'adminpass', 'admin', 'Elite', 9999, 9999, 'https://images.unsplash.com/photo-1535713875002-d1d0cf377fde?w=150'); INSERT INTO `stores` (`id`, `user_id`, `store_name`, `logo_url`, `rating`, `trust_score`, `response_rate`, `is_verified`) VALUES (1, 2, 'CyberPulse Official', 'https://images.unsplash.com/photo-1618005182384-a83a8bd57fbe?w=150', 4.90, 98, 99, 1), (2, 3, 'Aura Audio Lab', 'https://images.unsplash.com/photo-1590658268037-6bf12165a8df?w=150', 4.80, 94, 96, 1); INSERT INTO `products` (`id`, `store_id`, `category_id`, `title`, `description`, `price`, `original_price`, `stock`, `sku`, `trust_score`, `sales_count`, `rating_avg`, `is_group_buy`, `group_price`, `image_url`, `gallery`, `specs`, `ai_summary`) VALUES (1, 1, 1, 'NEXUS Pro Wireless ANC Headphones - Obsidian Black', 'Active Noise Cancelling over-ear headphones with 50-hour battery, Spatial Audio, ultra-light magnesium alloy frame.', 2490.00, 3290.00, 38, 'NEX-AUD-01', 97, 840, 4.90, 1, 1990.00, 'https://images.unsplash.com/photo-1505740420928-5e560c06d30e?w=600', '["https://images.unsplash.com/photo-1505740420928-5e560c06d30e?w=600"]', '{"Battery": "50 Hours", "Bluetooth": "v5.3"}', '{"verdict": "Best ANC under ฿3000"}'), (2, 1, 5, 'CyberDeck V1 Ergonomic Mechanical RGB Keyboard', 'Gasket mounted hot-swappable keyboard with South-facing RGB and PBT keycaps.', 1890.00, 2450.00, 18, 'NEX-KB-02', 95, 1250, 4.80, 1, 1490.00, 'https://images.unsplash.com/photo-1587829741301-dc798b83add3?w=600', '["https://images.unsplash.com/photo-1587829741301-dc798b83add3?w=600"]', '{"Switches": "Linear Yellow", "Keycaps": "PBT"}', '{"verdict": "Thocky typing experience"}'), (3, 2, 1, 'Aura LightBar Pro - Monitor Screen Bar with Wireless Controller', 'Zero screen glare monitor lamp with auto-dimming ambient light.', 1290.00, 1690.00, 50, 'NEX-LGT-03', 98, 2100, 4.95, 0, 1290.00, 'https://images.unsplash.com/photo-1527443224154-c4a3942d3acf?w=600', '["https://images.unsplash.com/photo-1527443224154-c4a3942d3acf?w=600"]', '{"CRI": "Ra > 95", "Color Temp": "2700K-6500K"}', '{"verdict": "Essential lighting for desks"}'); INSERT INTO `orders` (`id`, `order_sn`, `user_id`, `total_amount`, `discount_amount`, `final_amount`, `status`, `tracking_number`, `shipping_address`) VALUES (1, 'NEX-2026-001', 1, 2490.00, 200.00, 2290.00, 'preparing', 'TH902847291', 'Alex Vance, 123 Sukhumvit Rd, Bangkok 10110'), (2, 'NEX-2026-002', 1, 1890.00, 0.00, 1890.00, 'packed', 'TH902847292', 'Somsak P., 45 Silom Rd, Bangkok 10500'); INSERT INTO `order_items` (`id`, `order_id`, `product_id`, `quantity`, `unit_price`) VALUES (1, 1, 1, 1, 2490.00), (2, 2, 2, 1, 1890.00); INSERT INTO `quests` (`id`, `title`, `description`, `reward_coins`, `reward_xp`, `is_completed`) VALUES (1, 'Daily Attendance Check-In', 'Claim your daily login attendance reward', 15, 50, 0), (2, 'Try Interactive 360 Product Room', 'Rotate products in 3D room', 30, 100, 0); SET FOREIGN_KEY_CHECKS = 1;

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