SQL

CREATE DATABASE WEB;

USE WEB;

CREATE TABLE `USER` (
    `USER_ID` INTEGER NOT NULL AUTO_INCREMENT,
    `EMAIL` VARCHAR(50) NOT NULL,
    `PASSWORD` VARCHAR(255) NOT NULL,
    `NAME` VARCHAR(50) NOT NULL,
    `PHONE` VARCHAR(15) NOT NULL,
    `ROLE` ENUM('Admin','Customer') NOT NULL,
    PRIMARY KEY(`USER_ID`)
);

CREATE TABLE `PRODUCT` (
    `PD_ID` INTEGER NOT NULL AUTO_INCREMENT,
    `NAME` VARCHAR(50) NOT NULL,
    `PRICE` INTEGER NOT NULL,
    `TYPE_OF_CLOTHES` ENUM('Tees', 'Sweats', 'Shirts', 'Jeans', 'Shorts', 'Jackets', 'Sneakers', 'Sandals', 'Boxers', 'Socks', 'Hats') NOT NULL,
    `DESCRIPTION` TEXT,
    `CTGR_ID` INTEGER,
    `SIZE` ENUM('S','M','L','39','40','41','42', 'None') NOT NULL ,
    `IMAGE_ID` INTEGER,
    PRIMARY KEY (`PD_ID`)
);

CREATE TABLE `CATEGORY` (
    `CTGR_ID` INTEGER NOT NULL AUTO_INCREMENT,
    `NAME` VARCHAR(50),
    PRIMARY KEY(`CTGR_ID`)
);

CREATE TABLE `IMAGE` (
    `IMAGE_ID` INTEGER NOT NULL AUTO_INCREMENT,
    `IMAGE` BLOB NOT NULL,
    `PD_ID` INTEGER,
    PRIMARY KEY(`IMAGE_ID`)
);

CREATE TABLE `ORDER_DETAIL` (
    `PD_ID` INTEGER,
    `ORDER_ID` INTEGER,
    `AMOUNT` TINYINT,
    `PD_COST` INTEGER,
    `PAYMENT_ID` INTEGER,
    PRIMARY KEY(`PD_ID`, `ORDER_ID`)
);

CREATE TABLE `ORDER` (
    `ORDER_ID` INTEGER NOT NULL AUTO_INCREMENT,
    `CREATED_DATE` DATETIME,
    `COST` INTEGER,
    `USER_ID` INTEGER,
    `STATUS` ENUM('Confirming','Preparing','Delivering','Delivered') NOT NULL,
    PRIMARY KEY(`ORDER_ID`)
);

CREATE TABLE `PAYMENT` (
    `PAYMENT_ID` INTEGER NOT NULL AUTO_INCREMENT,
    `TYPE` ENUM('Cash','Card') NOT NULL,
    PRIMARY KEY(`PAYMENT_ID`)
);
CREATE TABLE `CART_ITEMS` (
    `CART_ITEM_ID` INTEGER NOT NULL AUTO_INCREMENT,  
    `USER_ID` INTEGER NOT NULL,                    
    `PD_ID` INTEGER NOT NULL,                      
    `QUANTITY` TINYINT NOT NULL,                    
    PRIMARY KEY(`CART_ITEM_ID`)
);

ALTER TABLE `ORDER_DETAIL`
ADD CONSTRAINT `FK_ORDER_DETAIL_PRODUCT` FOREIGN KEY(`PD_ID`) REFERENCES `PRODUCT`(`PD_ID`)
    ON UPDATE NO ACTION ON DELETE NO ACTION,
ADD CONSTRAINT `FK_ORDER_DETAIL_ORDER` FOREIGN KEY(`ORDER_ID`) REFERENCES `ORDER`(`ORDER_ID`)
    ON UPDATE NO ACTION ON DELETE NO ACTION,
ADD CONSTRAINT `FK_ORDER_DETAIL_PAYMENT` FOREIGN KEY(`PAYMENT_ID`) REFERENCES `PAYMENT`(`PAYMENT_ID`)
    ON UPDATE NO ACTION ON DELETE NO ACTION;
    
ALTER TABLE `ORDER`
ADD CONSTRAINT `FK_ORDER_USER` FOREIGN KEY(`USER_ID`) REFERENCES `USER`(`USER_ID`)
    ON UPDATE NO ACTION ON DELETE NO ACTION;

ALTER TABLE `CART_ITEMS`
ADD CONSTRAINT `FK_CART-ITEMS_USER` FOREIGN KEY(`USER_ID`) REFERENCES `USER`(`USER_ID`)
    ON UPDATE NO ACTION ON DELETE CASCADE,         
ADD CONSTRAINT `FK_CART-ITEMS_PRODUCT` FOREIGN KEY(`PD_ID`) REFERENCES `PRODUCT`(`PD_ID`)
    ON UPDATE NO ACTION ON DELETE NO ACTION;
    
ALTER TABLE `PRODUCT`
ADD CONSTRAINT `FK_PRODUCT_CATEGORY` FOREIGN KEY(`CTGR_ID`) REFERENCES `CATEGORY`(`CTGR_ID`)
    ON UPDATE NO ACTION ON DELETE NO ACTION;
    
ALTER TABLE `IMAGE`
ADD CONSTRAINT `FK_IMAGE_PRODUCT` FOREIGN KEY(`PD_ID`) REFERENCES `PRODUCT`(`PD_ID`)
    ON UPDATE NO ACTION ON DELETE NO ACTION;

INSERT INTO `USER` (EMAIL, PASSWORD, NAME, PHONE, ROLE) VALUES 
('admin@example.com', 'adminpass', 'Admin User', '0123456789', 'Admin');

INSERT INTO `USER` (EMAIL, PASSWORD, NAME, PHONE, ROLE) VALUES
('JohnDoe@gmail.com', 'JohnDoe', 'John Doe', '0987654321', 'Customer'),
('AlexHirsch@gmail.com', 'AlexHirsc', 'Alex Hirsch', '0978123456', 'Customer');

INSERT INTO `PRODUCT` (NAME, PRICE, TYPE_OF_CLOTHES, DESCRIPTION, CTGR_ID, SIZE, IMAGE_ID) VALUES 
('Casual Shirt', 150000, 'Shirts', 'A comfortable casual shirt.', 1, 'M', 1),
('Slim Fit Jeans', 250000, 'Jeans', 'Stylish slim fit jeans.', 2, 'M', 2),
('Leather Belt', 50000, 'Accessories', 'High-quality leather belt.', 3, 'L', 3),
('Basic T-Shirt', 80000, 'Tees', 'Soft cotton t-shirt for everyday wear.', 1, 'M', 4),
('Hooded Sweatshirt', 200000, 'Sweats', 'Cozy hooded sweatshirt for cooler days.', 1, 'M', 5),
('Cargo Shorts', 120000, 'Shorts', 'Durable cargo shorts with multiple pockets.', 2, 'L', 6),
('Denim Jacket', 300000, 'Jackets', 'Classic denim jacket for a stylish look.', 1, 'L', 7),
('Running Sneakers', 180000, 'Sneakers', 'Comfortable sneakers for running and sports.', 1, '42', 8),
('Leather Sandals', 90000, 'Sandals', 'Stylish leather sandals for summer.', 2, '41', 9),
('Cotton Boxers', 60000, 'Boxers', 'Comfortable cotton boxers for daily wear.', 3, 'M', 10),
('Ankle Socks', 30000, 'Socks', 'Soft ankle socks for casual wear.', 3, 'None', 11),
('Baseball Cap', 40000, 'Hats', 'Stylish baseball cap for outdoor activities.', 3, 'None', 12);

INSERT INTO `CATEGORY` (NAME) VALUES 
('No Brand'), 
('The Sea Farer');

INSERT INTO `IMAGE` (IMAGE, PD_ID) VALUES 
(LOAD_FILE('C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Trousers.jpg'), 1);


INSERT INTO `ORDER` (CREATED_DATE, COST, USER_ID, STATUS) VALUES 
(NOW(), 150000, 1, 'Confirming'), 
(NOW(), 250000, 2, 'Delivering');

INSERT INTO `ORDER_DETAIL` (PD_ID, ORDER_ID, AMOUNT, PD_COST, PAYMENT_ID) VALUES 
(1, 1, 1, 150000, 1), 
(2, 2, 1, 250000, 2);

INSERT INTO `PAYMENT` (TYPE) VALUES 
('Cash'), 
('Card');

INSERT INTO `CART_ITEMS` (USER_ID, PD_ID, QUANTITY) VALUES 
(1, 1, 1), 
(2, 2, 2);

drop database WEB