File manager

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

Back
-- Schema for tables -- Users Table CREATE TABLE IF NOT EXISTS `users` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `username` VARCHAR(50) NOT NULL UNIQUE, `email` VARCHAR(100) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL, `full_name` VARCHAR(100) DEFAULT NULL, `phone` VARCHAR(20) DEFAULT NULL, `address` TEXT DEFAULT NULL, `role` ENUM('customer', 'admin', 'super_admin') DEFAULT 'customer', `status` ENUM('pending', 'approved', 'rejected') DEFAULT 'approved', `avatar` VARCHAR(255) DEFAULT NULL, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- Migration: Add avatar, full_name, phone, address to users table ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `full_name` VARCHAR(100) DEFAULT NULL AFTER `password`; ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `phone` VARCHAR(20) DEFAULT NULL AFTER `full_name`; ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `address` TEXT DEFAULT NULL AFTER `phone`; ALTER TABLE `users` ADD COLUMN IF NOT EXISTS `avatar` VARCHAR(255) DEFAULT NULL AFTER `status`; -- Products Table CREATE TABLE IF NOT EXISTS `products` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(100) NOT NULL, `description` TEXT, `price` DECIMAL(10, 2) NOT NULL, `stock` INT NOT NULL DEFAULT 0, `image_url` VARCHAR(255) DEFAULT NULL, `video_url` VARCHAR(500) DEFAULT NULL, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- Migration: Add video_url to existing products table (run if column not exists) ALTER TABLE `products` ADD COLUMN IF NOT EXISTS `video_url` VARCHAR(500) DEFAULT NULL AFTER `image_url`; -- Orders Table CREATE TABLE IF NOT EXISTS `orders` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `user_id` INT NOT NULL, `total_amount` DECIMAL(10, 2) NOT NULL, `status` ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled') DEFAULT 'pending', `tracking_number` VARCHAR(100) DEFAULT NULL, `shipping_address` TEXT NOT NULL, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB; -- Order Items Table CREATE TABLE IF NOT EXISTS `order_items` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_id` INT NOT NULL, `product_id` INT NOT NULL, `quantity` INT NOT NULL, `price` DECIMAL(10, 2) NOT NULL, FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE, FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE ) ENGINE=InnoDB; -- Seed Super Admin (Username: admin OR superadmin / Password: admin1234) -- Bcrypt hash for 'admin1234': $2y$10$E8lGqP5Qv3fB6A8qC1mDme.8yZ6W4n8O9m2R4l1K7y3F5G6H7J8K9 INSERT INTO `users` (`username`, `email`, `password`, `role`, `status`) VALUES ('superadmin', 'superadmin@cyberpunk.com', '$2y$10$8v8mQJqH9iN1y2u3v4w5x.1Y2Z3A4B5C6D7E8F9G0H1I2J3K4L5M6', 'super_admin', 'approved'), ('admin', 'admin@cyberpunk.com', '$2y$10$8v8mQJqH9iN1y2u3v4w5x.1Y2Z3A4B5C6D7E8F9G0H1I2J3K4L5M6', 'admin', 'approved') ON DUPLICATE KEY UPDATE `status`='approved'; -- Seed Pending Admin for Testing (Password: password123) INSERT INTO `users` (`username`, `email`, `password`, `role`, `status`) VALUES ('pending_admin', 'pending@cyberpunk.com', '$2y$10$zR8Q21mJ4L2J.sZ5i4U7IeVH1a3xU/6gqgK3OQyV19N837Lg9wL0a', 'admin', 'pending') ON DUPLICATE KEY UPDATE `id`=`id`; -- Seed Products INSERT INTO `products` (`name`, `description`, `price`, `stock`, `image_url`) VALUES ('Cybernetic Implant V1', 'Enhance your visual display and cognitive processing speed by 150%. Neural-link ready.', 1299.99, 12, 'https://images.unsplash.com/photo-1589254065878-42c9da997008?auto=format&fit=crop&q=80&w=600'), ('Neon Katanas Dual', 'Carbon-fiber reinforcement with customizable RGB edge glow. Extreme durability.', 549.99, 8, 'https://images.unsplash.com/photo-1534224039826-c7a0dea0e66a?auto=format&fit=crop&q=80&w=600'), ('Netrunner Deck Mk.IV', 'Pre-loaded with decryption protocols and icebreaker suites. Multi-band receiver included.', 2450.00, 5, 'https://images.unsplash.com/photo-1542751371-adc38448a05e?auto=format&fit=crop&q=80&w=600'), ('LED Cyberpunk Visor', 'High-definition head-up display with night-vision overlay and audio equalizer.', 89.00, 45, 'https://images.unsplash.com/photo-1572635196237-14b3f281503f?auto=format&fit=crop&q=80&w=600') ON DUPLICATE KEY UPDATE `id`=`id`; -- Seed a Customer and Order INSERT INTO `users` (`username`, `email`, `password`, `role`, `status`) VALUES ('johndoe', 'john@cyberpunk.com', '$2y$10$zR8Q21mJ4L2J.sZ5i4U7IeVH1a3xU/6gqgK3OQyV19N837Lg9wL0a', 'customer', 'approved') ON DUPLICATE KEY UPDATE `id`=`id`; -- Seed Order INSERT INTO `orders` (`id`, `user_id`, `total_amount`, `status`, `tracking_number`, `shipping_address`) VALUES (1, 3, 1388.99, 'paid', NULL, 'Neo-Tokyo Sector 7, Block B, Apt 404') ON DUPLICATE KEY UPDATE `id`=`id`; INSERT INTO `order_items` (`order_id`, `product_id`, `quantity`, `price`) VALUES (1, 1, 1, 1299.99), (1, 4, 1, 89.00) ON DUPLICATE KEY UPDATE `id`=`id`;

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