File manager

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

Back
DROP TABLE IF EXISTS `order_items`; DROP TABLE IF EXISTS `orders`; DROP TABLE IF EXISTS `cart`; DROP TABLE IF EXISTS `products`; DROP TABLE IF EXISTS `otp_tokens`; DROP TABLE IF EXISTS `remember_tokens`; DROP TABLE IF EXISTS `users`; -- 1. Users Table CREATE TABLE `users` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `username` VARCHAR(50) NOT NULL UNIQUE, `email` VARCHAR(100) DEFAULT NULL UNIQUE, `phone` VARCHAR(20) DEFAULT NULL UNIQUE, `password` VARCHAR(255) DEFAULT NULL, `google_id` VARCHAR(100) DEFAULT NULL, `facebook_id` VARCHAR(100) DEFAULT NULL, `line_id` VARCHAR(100) DEFAULT NULL, `apple_id` VARCHAR(100) DEFAULT NULL, `role` ENUM('admin', 'seller', 'buyer') DEFAULT 'buyer', `shipping_address` TEXT DEFAULT NULL, `bio` TEXT DEFAULT NULL, `profile_pic` VARCHAR(255) DEFAULT NULL, `is_banned` TINYINT(1) DEFAULT 0, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 2. Products Table CREATE TABLE `products` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `seller_id` INT NOT NULL, `title` VARCHAR(255) NOT NULL, `description` TEXT NOT NULL, `price` DECIMAL(10, 2) NOT NULL, `stock` INT NOT NULL DEFAULT 0, `category` VARCHAR(100) NOT NULL DEFAULT 'Other', `images` TEXT NOT NULL, -- JSON array of image file paths `sizes` VARCHAR(255) DEFAULT NULL, -- Comma-separated or null/optional `colors` VARCHAR(255) DEFAULT NULL, -- Comma-separated or null/optional `status` ENUM('approved', 'pending', 'rejected') DEFAULT 'approved', `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`seller_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 3. Cart Table CREATE TABLE `cart` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `user_id` INT NOT NULL, `product_id` INT NOT NULL, `quantity` INT NOT NULL DEFAULT 1, `size` VARCHAR(50) DEFAULT NULL, `color` VARCHAR(50) DEFAULT NULL, 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; -- 4. Orders Table CREATE TABLE `orders` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `buyer_id` INT NOT NULL, `total_amount` DECIMAL(10, 2) NOT NULL, `shipping_address` TEXT NOT NULL, `payment_method` ENUM('COD', 'Credit Card') DEFAULT 'COD', `status` ENUM('Pending', 'Shipped', 'Delivered', 'Cancelled') DEFAULT 'Pending', `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`buyer_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 5. Order Items Table CREATE TABLE `order_items` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_id` INT NOT NULL, `product_id` INT DEFAULT NULL, `quantity` INT NOT NULL, `price` DECIMAL(10, 2) NOT NULL, `size` VARCHAR(50) DEFAULT NULL, `color` VARCHAR(50) DEFAULT NULL, FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE, FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- Insert Seed Accounts -- Passwords are bcrypt-hashed versions of 'password123' -- ($2a$10$7d3j97H2kK/JkP67L3sK/.tT21Nq6G4KzNqG.9bF/j4Z5eG1jYx3a) INSERT INTO `users` (`id`, `username`, `email`, `password`, `role`, `shipping_address`, `bio`, `profile_pic`) VALUES (1, 'admin', 'admin@shop.com', '$2a$10$7d3j97H2kK/JkP67L3sK/.tT21Nq6G4KzNqG.9bF/j4Z5eG1jYx3a', 'admin', 'Main Headquarters, Room 101', 'System-wide platform administrator.', ''), (2, 'seller1', 'seller1@shop.com', '$2a$10$7d3j97H2kK/JkP67L3sK/.tT21Nq6G4KzNqG.9bF/j4Z5eG1jYx3a', 'seller', '123 Tech & Lifestyle Hub, Silicon Valley, CA', 'Electronics, Gadgets & Lifestyle Gear Store.', ''), (3, 'seller2', 'seller2@shop.com', '$2a$10$7d3j97H2kK/JkP67L3sK/.tT21Nq6G4KzNqG.9bF/j4Z5eG1jYx3a', 'seller', '456 Modern Living Ave, Design District, NY', 'Home Appliances, Apparel & Premium Goods.', ''), (4, 'buyer1', 'buyer1@shop.com', '$2a$10$7d3j97H2kK/JkP67L3sK/.tT21Nq6G4KzNqG.9bF/j4Z5eG1jYx3a', 'buyer', '789 Cozy Lane, Comfort City, TX', 'Casual buyer.', ''); -- Insert Seed Products across various categories INSERT INTO `products` (`id`, `seller_id`, `title`, `description`, `price`, `stock`, `category`, `images`, `sizes`, `colors`, `status`) VALUES (1, 2, 'Wireless Noise-Canceling Headphones', 'Premium over-ear Bluetooth headphones with active noise cancellation, 30-hour battery life, and crystal-clear audio quality.', 129.99, 50, 'Electronics', '["/uploads/mock-headphones.jpg"]', NULL, 'Matte Black,Silver,Navy', 'approved'), (2, 2, 'Smart Fitness Watch Series 5', 'Water-resistant smartwatch featuring heart rate monitoring, GPS tracking, sleep analysis, and customizable clock faces.', 89.50, 65, 'Gadgets', '["/uploads/mock-smartwatch.jpg"]', 'Standard', 'Midnight Black,Space Gray,Rose Gold', 'approved'), (3, 3, 'Italian Espresso Coffee Maker Machine', 'Compact high-pressure pump espresso maker with milk frother wand for crafting lattes, cappuccinos, and rich espresso shots at home.', 149.00, 30, 'Home & Living', '["/uploads/mock-coffee-machine.jpg"]', NULL, 'Stainless Steel,Matte Black', 'approved'), (4, 2, 'RGB Mechanical Gaming Keyboard', 'Tactile mechanical switches with customizable RGB backlighting, durable aluminum top plate, and anti-ghosting keys.', 69.99, 100, 'Electronics', '["/uploads/mock-keyboard.jpg"]', 'Full Size,Tenkeyless', 'Black,White', 'approved'), (5, 3, 'Vintage Canvas Leather Backpack', 'Water-resistant heavy canvas backpack with genuine leather straps, padded 15.6" laptop compartment, and multi-pocket layout.', 54.90, 85, 'Fashion', '["/uploads/mock-backpack.jpg"]', 'Medium,Large', 'Khaki,Army Green,Charcoal', 'approved'), (6, 3, 'Ergonomic Mesh Office Chair', 'High-back mesh computer chair with lumbar support, adjustable headrest, 3D armrests, and smooth rolling casters.', 185.00, 20, 'Home & Living', '["/uploads/mock-chair.jpg"]', NULL, 'Black,Gray', 'approved'), (7, 2, 'Organic Japanese Matcha Green Tea Powder', '100% authentic ceremonial grade organic matcha green tea rich in antioxidants, vibrant green color, and smooth taste.', 22.50, 140, 'Food & Beverages', '["/uploads/mock-matcha.jpg"]', '100g,250g', NULL, 'approved'), (8, 3, 'Pro Performance Running Sneakers', 'Lightweight breathable mesh running shoes with responsive foam cushioning and high-grip rubber outsole for all terrains.', 78.00, 90, 'Sports & Outdoors', '["/uploads/mock-shoes.jpg"]', 'US 7,US 8,US 9,US 10,US 11', 'Neon Yellow,Cyan Blue,Triple Black', 'approved'); -- 6. OTP Tokens Table (Email Login OTP) CREATE TABLE `otp_tokens` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `email` VARCHAR(100) NOT NULL, `otp_code` VARCHAR(255) NOT NULL, `token` VARCHAR(100) NOT NULL UNIQUE, `expires_at` DATETIME NOT NULL, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX `idx_otp_email` (`email`), INDEX `idx_otp_token` (`token`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- 7. Remember Me Tokens Table CREATE TABLE `remember_tokens` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `user_id` INT NOT NULL, `token_hash` VARCHAR(64) NOT NULL, `expires_at` DATETIME NOT NULL, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY `uq_user` (`user_id`), FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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