DROP TABLE CUSTOMER CASCADE CONSTRAINTS;
DROP TABLE ORDER_SOURCE CASCADE CONSTRAINTS;
DROP TABLE ORDERS CASCADE CONSTRAINTS;
DROP TABLE CATEGORY CASCADE CONSTRAINTS;
DROP TABLE ITEM CASCADE CONSTRAINTS;
DROP TABLE INVENTORY CASCADE CONSTRAINTS;
DROP TABLE ORDER_LINE CASCADE CONSTRAINTS;

CREATE TABLE CUSTOMER (
    C_ID        NUMBER(5) CONSTRAINT CUSTOMER_C_ID_PK PRIMARY KEY,
    C_LAST      VARCHAR2(30),
    C_FIRST     VARCHAR2(30),
    C_MI        VARCHAR2(1),
    C_BIRTHDATE DATE,
    C_ADDRESS   VARCHAR2(30),
    C_CITY      VARCHAR2(30),
    C_STATE     VARCHAR2(2),
    C_ZIP       VARCHAR2(10),
    C_DPHONE    VARCHAR2(10),
    C_EPHONE    VARCHAR2(10),
    C_USERID    VARCHAR2(15),
    C_PASSWORD  VARCHAR2(15)
);

CREATE TABLE ORDER_SOURCE (
    OS_ID   NUMBER(5) CONSTRAINT ORDER_SOURCE_OS_ID_PK PRIMARY KEY,
    OS_DESC VARCHAR2(30)
);

CREATE TABLE ORDERS (
    OD_ID     NUMBER(5) CONSTRAINT ORDERS_OD_ID_PK PRIMARY KEY,
    O_DATE    DATE,
    O_METHPMT VARCHAR2(5),
    C_ID      NUMBER(5),
    OS_ID     NUMBER(5),
    CONSTRAINT ORDERS_C_ID_FK FOREIGN KEY (C_ID) REFERENCES CUSTOMER(C_ID),
    CONSTRAINT ORDERS_OS_ID_FK FOREIGN KEY (OS_ID) REFERENCES ORDER_SOURCE(OS_ID)
);

CREATE TABLE CATEGORY (
    CAT_ID   NUMBER(5) CONSTRAINT CATEGORY_CAT_ID_PK PRIMARY KEY,
    CAT_DESC VARCHAR2(32)
);

CREATE TABLE ITEM (
    ITEM_ID    NUMBER(5) CONSTRAINT ITEM_ITEM_ID_PK PRIMARY KEY,
    ITEM_DESC  VARCHAR2(32),
    CAT_ID     NUMBER(5),
    ITEM_IMAGE VARCHAR(15),
    CONSTRAINT ITEM_CAT_ID_FK FOREIGN KEY (CAT_ID) REFERENCES CATEGORY(CAT_ID)
);

CREATE TABLE INVENTORY (
    INV_ID NUMBER(5) CONSTRAINT INVENTORY_INV_ID_PK PRIMARY KEY,
    ITEM_ID NUMBER(3),
    COLOR VARCHAR2(15),
    INV_SIZE VARCHAR2(2),
    INV_PRICE FLOAT(9),
    INV_QOH NUMBER(3),
    CONSTRAINT INVCENTORY_ITEM_ID_FK FOREIGN KEY (ITEM_ID) REFERENCES ITEM(ITEM_ID)
);

CREATE TABLE ORDER_LINE (
    O_ID        NUMBER(5) CONSTRAINT ORDER_LINE_O_ID_PK PRIMARY KEY,
    INV_ID      NUMBER(5),
    OL_QUANTITY NUMBER(3),
    CONSTRAINT ORDER_LINE_INV_ID_FK FOREIGN KEY (INV_ID) REFERENCES INVENTORY(INV_ID)
);