aa

CREATE TABLE Customers (
	Customer_ID INT PRIMARY KEY, 
	Name VARCHAR(100) NOT NULL,
	Email VARCHAR(90) UNIQUE,
	Address TEXT,
	Phone VARCHAR(20),
	Date_Joined DATE
);

CREATE TABLE Orders (
	Order_ID INT PRIMARY KEY,
	Customer_ID INT,
	Order_Date DATE NOT NULL,
	Total_Amount Decimal(10, 2) NOT NULL,
	Shipping_Address TEXT,
	FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

CREATE Table Categories (
	Category_ID INT PRIMARY KEY, 
	Category_Name VARCHAR(255) NOT NULL
);



CREATE TABLE Products (
	Product_ID INT PRIMARY KEY,
	Product_Name VARCHAR(255) NOT NULL,
	Category_ID INT, 
	Price DECIMAL(10, 2) NOT NULL, 
	Stock_Quantity INT NOT NULL, 
	FOREIGN KEY (Category_ID) REFERENCES Categories(Category_ID)
		ON DELETE SET NULL
		ON UPDATE CASCADE
);

CREATE TABLE OrderDetails (
	OrderDetail_ID INT PRIMARY KEY, 
	Order_ID INT,
	Product_ID INT,
	Quantity INT NOT NULL,
	Unit_Price DECIMAL (10, 2) NOT NULL,
	FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID)
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	FOREIGN KEY (Product_ID) REFERENCES Products(Product_ID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);

CREATE TABLE Payments (
	Payment_ID INT PRIMARY KEY,
	Order_ID INT, 
	Payment_Method VARCHAR(50) NOT NULL,
	Payment_Amount DECIMAL(10, 2) NOT NULL,
	Payment_Date DATE NOT NULL,
	FOREIGN KEY (Order_ID) REFERENCES Orders(Order_ID)
		ON DELETE CASCADE
		ON UPDATE CASCADE
);