File manager

File manager - Edit - /home/webapp69.cm.in.th/u69319090042/Shop/schema.sql

Back
-- Database creation handled by system CREATE TABLE IF NOT EXISTS `users` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `username` VARCHAR(50) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL, `role` ENUM('customer', 'admin') DEFAULT 'customer', `full_name` VARCHAR(100) NOT NULL, `email` VARCHAR(100) NOT NULL, `address` TEXT DEFAULT NULL, `points` INT DEFAULT 0, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `categories` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(50) NOT NULL UNIQUE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `products` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(100) NOT NULL, `description` TEXT DEFAULT NULL, `price` DECIMAL(10,2) NOT NULL, `stock` INT NOT NULL DEFAULT 0, `image_url` VARCHAR(255) DEFAULT NULL, `category_id` INT, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `cart` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `user_id` INT NOT NULL, `product_id` INT NOT NULL, `quantity` INT NOT NULL DEFAULT 1, FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE, FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `orders` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `user_id` INT NOT NULL, `total_amount` DECIMAL(10,2) NOT NULL, `discount_applied` DECIMAL(10,2) DEFAULT 0.00, `net_amount` DECIMAL(10,2) NOT NULL, `points_earned` INT DEFAULT 0, `points_redeemed` INT DEFAULT 0, `status` ENUM('pending', 'shipped', 'delivered') DEFAULT 'pending', `payment_method` VARCHAR(50) DEFAULT 'Credit Card', `shipping_address` TEXT NOT NULL, `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS `order_items` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `order_id` INT NOT NULL, `product_id` INT NOT NULL, `price` DECIMAL(10,2) NOT NULL, `quantity` INT NOT NULL, FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE, FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB; -- Insert initial categories INSERT INTO `categories` (`name`) VALUES ('Dresses'), ('Tops'), ('Skirts & Pants'), ('Accessories') ON DUPLICATE KEY UPDATE `name`=`name`; -- Insert admin user (password: admin123) INSERT INTO `users` (`username`, `password`, `role`, `full_name`, `email`, `address`, `points`) VALUES ('admin', '$2y$10$M657gDsk8gS/tYFzF0VlqeqW5l0W7sL3zQ5b/q9z407U1T3N/n2Y2', 'admin', 'System Administrator', 'admin@aoracloset.com', 'Admin Office, Bangkok', 0) ON DUPLICATE KEY UPDATE `username`=`username`; -- Insert seed products INSERT INTO `products` (`name`, `description`, `price`, `stock`, `image_url`, `category_id`) VALUES ('Floral Midi Dress', 'Beautiful pastel floral midi dress with puff sleeves. Perfect for summer picnics.', 590.00, 15, 'https://images.unsplash.com/photo-1595777457583-95e059d581b8?w=500&auto=format&fit=crop&q=60', 1), ('Korean Oversized Cardigan', 'Cozy knit oversized cardigan in cream white. Soft material, matches with any outfit.', 450.00, 20, 'https://images.unsplash.com/photo-1539571696357-5a69c17a67c6?w=500&auto=format&fit=crop&q=60', 2), ('High-Waist Wide Leg Trousers', 'Chic high-waist beige trousers that offer a tall silhouette. Great for casual and working styles.', 390.00, 10, 'https://images.unsplash.com/photo-1594633312681-425c7b97ccd1?w=500&auto=format&fit=crop&q=60', 3), ('Pastel Bow Hairclip', 'Lovely pink hair ribbon clip. Add a cute touch to your daily hairstyle.', 120.00, 50, 'https://images.unsplash.com/photo-1576243345690-4e4b79b63288?w=500&auto=format&fit=crop&q=60', 4) ON DUPLICATE KEY UPDATE `name`=`name`;

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