SQL_Tables.sql

CREATE TABLE USERS (
    UserID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    FirstName VARCHAR2(100) NOT NULL,
    LastName VARCHAR2(100) NOT NULL,
    Email VARCHAR2(255) NOT NULL UNIQUE,
    UserPassword VARCHAR2(255) NOT NULL,
    PhoneNumber VARCHAR2(13) NOT NULL,
    UserRole VARCHAR2(50) DEFAULT 'Customer' CHECK (UserRole IN ('Customer', 'Admin'))
);

CREATE TABLE CATEGORIES (
    CategoryID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    CategoryName VARCHAR2(150) NOT NULL UNIQUE,
    ParentCategoryID NUMBER,
    CONSTRAINT fk_parent_category FOREIGN KEY (ParentCategoryID) REFERENCES CATEGORIES(CategoryID) ON DELETE SET NULL
);

CREATE TABLE ADDRESSES (
    AddressID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    UserID NUMBER NOT NULL,
    AddressTitle VARCHAR2(100) NOT NULL,
    ContactFirstName VARCHAR2(100),
    ContactLastName VARCHAR2(100),
    ContactPhoneNumber VARCHAR2(20),
    City VARCHAR2(100) NOT NULL,
    District VARCHAR2(100) NOT NULL,
    Neighborhood VARCHAR2(150) NOT NULL,
    StreetAddress VARCHAR2(255) NOT NULL,
    PostalCode VARCHAR2(10),
    AddressDetails CLOB,
    CONSTRAINT fk_address_user FOREIGN KEY (UserID) REFERENCES USERS(UserID) ON DELETE CASCADE 
);

CREATE TABLE PRODUCTS (
    ProductID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ProductName VARCHAR2(255) NOT NULL,
    Description CLOB,
    UnitPrice NUMBER(10, 2) NOT NULL CHECK (UnitPrice >= 0),
    StockQuantity NUMBER(10) DEFAULT 0 NOT NULL CHECK (StockQuantity >= 0),
    DateAdded DATE DEFAULT SYSDATE,
    CategoryID NUMBER NOT NULL,
    IsActive NUMBER(1) DEFAULT 1 NOT NULL CHECK (IsActive IN (0, 1)), 
    CONSTRAINT fk_product_category FOREIGN KEY (CategoryID) REFERENCES CATEGORIES(CategoryID)
);

CREATE TABLE ORDERS (
    OrderID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    UserID NUMBER NOT NULL,
    OrderDate TIMESTAMP DEFAULT SYSTIMESTAMP,
    TotalAmount NUMBER(12, 2) NOT NULL CHECK (TotalAmount >= 0),
    OrderStatus VARCHAR2(50) DEFAULT 'Pending Confirmation' NOT NULL
        CHECK (OrderStatus IN ('Pending Confirmation', 'Processing', 'Awaiting Payment', 'Shipped', 'Delivered', 'Cancelled', 'Returned')),
    ShippingAddressID NUMBER NOT NULL,
    CONSTRAINT fk_order_user FOREIGN KEY (UserID) REFERENCES USERS(UserID),
    CONSTRAINT fk_order_address FOREIGN KEY (ShippingAddressID) REFERENCES ADDRESSES(AddressID)
);

CREATE TABLE ORDER_DETAILS (
    OrderDetailID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    OrderID NUMBER NOT NULL,
    ProductID NUMBER NOT NULL,
    Quantity NUMBER(5) NOT NULL CHECK (Quantity > 0),
    SalePrice NUMBER(10, 2) NOT NULL, 
    TotalProductPrice AS (Quantity * SalePrice), 
    CONSTRAINT fk_orderdetail_order FOREIGN KEY (OrderID) REFERENCES ORDERS(OrderID) ON DELETE CASCADE, 
    CONSTRAINT fk_orderdetail_product FOREIGN KEY (ProductID) REFERENCES PRODUCTS(ProductID)
);


CREATE TABLE PAYMENTS (
    PaymentID NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    OrderID NUMBER NOT NULL UNIQUE,
    PaymentDate TIMESTAMP DEFAULT SYSTIMESTAMP,
    PaymentMethod VARCHAR2(50) NOT NULL CHECK (PaymentMethod IN ('Credit Card', 'Debit Card', 'Bank Transfer', 'Other')),
    PaymentAmount NUMBER(12, 2) NOT NULL,
    PaymentStatus VARCHAR2(50) DEFAULT 'Pending' NOT NULL CHECK (PaymentStatus IN ('Successful', 'Failed', 'Pending')),
    CONSTRAINT fk_payment_order FOREIGN KEY (OrderID) REFERENCES ORDERS(OrderID)
);