\c store store;

CREATE TABLE IF NOT EXISTS customer (
    email       VARCHAR(100)    NOT NULL,
    lastname    VARCHAR(40)     NOT NULL,
    firstname   VARCHAR(40)     NOT NULL,
    phone       VARCHAR(15),
    street      VARCHAR(100),
    city        VARCHAR(50),
    st          VARCHAR(2),
    zip         INT,
    dob         DATE,
    CONSTRAINT pk_customer PRIMARY KEY (email)
);

CREATE TABLE IF NOT EXISTS employee (
    empid       INT             NOT NULL,
    lastname    VARCHAR(40)     NOT NULL,
    firstname   VARCHAR(40)     NOT NULL,
    hourlywage  DEC(4,2),
    manager     BOOLEAN         DEFAULT false NOT NULL,
    CONSTRAINT pk_employee PRIMARY KEY (empID)
);

CREATE TABLE IF NOT EXISTS sale (
    saleid      BIGINT          NOT NULL,
    saledate    DATE            NOT NULL,
    saletime    TIME            NOT NULL,
    email       VARCHAR(100),
    empid       INT             NOT NULL,
    CONSTRAINT pk_sale PRIMARY KEY (saleid),
    CONSTRAINT fk_sale_customer FOREIGN KEY (email)
        REFERENCES customer(email),
    CONSTRAINT fk_sale_employee FOREIGN KEY (empid)
        REFERENCES employee(empid)
);

CREATE TABLE IF NOT EXISTS orders (
    orderid     BIGINT          NOT NULL,
    orderdate   DATE            NOT NULL,
    ordertime   TIME            NOT NULL,
    email       VARCHAR(100)    NOT NULL,
    processdate DATE,
    processtime TIME,
    status      VARCHAR(10)     DEFAULT 'new' NOT NULL, 
    CONSTRAINT pk_orders PRIMARY KEY (orderid),
    CONSTRAINT fk_orders_customer FOREIGN KEY (email)
        REFERENCES customer(email)
);

CREATE TABLE IF NOT EXISTS payment (
    payid       BIGINT          NOT NULL,
    paytype     VARCHAR(10)     NOT NULL,
    paydate     DATE            NOT NULL,
    paytime     TIME            NOT NULL,
    CONSTRAINT pk_payment PRIMARY KEY (payid)
);

CREATE TABLE IF NOT EXISTS supplier (
    brand       VARCHAR(30)     NOT NULL,
    phone       VARCHAR(15)     NOT NULL,
    address     VARCHAR(100)    NOT NULL,
    CONSTRAINT pk_supplier PRIMARY KEY (brand)
);

CREATE TABLE IF NOT EXISTS item (
    itemid      BIGINT          NOT NULL,
    itemtype    VARCHAR(25)     NOT NULL,
    itemsize    VARCHAR(10)     NOT NULL,
    price       DEC(5,2)        NOT NULL,
    brand       VARCHAR(30)     NOT NULL,
    CONSTRAINT pk_item PRIMARY KEY (itemid),
    CONSTRAINT fk_item_supplier FOREIGN KEY (brand)
        REFERENCES supplier(brand)
);

CREATE TABLE IF NOT EXISTS color (
    colors      VARCHAR(10)      NOT NULL,
    itemid      INT              NOT NULL,
    CONSTRAINT pk_color PRIMARY KEY (colors, itemid),
    CONSTRAINT fk_color_item FOREIGN KEY (itemid)
        REFERENCES item(itemid)
);

CREATE TABLE IF NOT EXISTS shipment (
    shipid      BIGINT          NOT NULL,
    shipdate    DATE            NOT NULL,
    shiptime    TIME            NOT NULL,
    status      VARCHAR(10)     DEFAULT 'arrived' NOT NULL,
    brand       VARCHAR(30)     NOT NULL,
    CONSTRAINT pk_shipment PRIMARY KEY (shipid),
    CONSTRAINT fk_shipment_supplier FOREIGN KEY (brand)
        REFERENCES supplier(brand)
);

CREATE TABLE IF NOT EXISTS timeclock (
    datein      DATE            NOT NULL,
    timein      TIME            NOT NULL,
    timeout     TIME,
    empid       INT             NOT NULL,       -- dateout removed
    CONSTRAINT pk_timeclock PRIMARY KEY (datein, timein, empid),
    CONSTRAINT fk_timeclock_employee FOREIGN KEY (empid)
        REFERENCES employee(empid)
);

CREATE TABLE IF NOT EXISTS salecontains (
    saleid      BIGINT          NOT NULL,
    itemid      INT             NOT NULL,
    quantity    INT             NOT NULL,
    price       DEC(6,2)        NOT NULL,
    CONSTRAINT pk_salecontains PRIMARY KEY (saleid, itemid),
    CONSTRAINT fk_salecontains_sale FOREIGN KEY (saleid)
        REFERENCES sale(saleid),
    CONSTRAINT fk_salecontains_item FOREIGN KEY (itemid)
        REFERENCES item(itemid)
);

CREATE TABLE IF NOT EXISTS payssale (
    saleid      BIGINT          NOT NULL,
    payid       BIGINT          NOT NULL,
    CONSTRAINT pk_payssale PRIMARY KEY (saleid, payid),
    CONSTRAINT fk_payssale_sale FOREIGN KEY (saleid)
        REFERENCES sale(saleid),
    CONSTRAINT fk_payssale_payment FOREIGN KEY (payid)
        REFERENCES payment(payid)
);

CREATE TABLE IF NOT EXISTS ordercontains (
    orderid     BIGINT          NOT NULL,
    itemid      INT             NOT NULL,
    quantity    INT             NOT NULL,
    price       DEC(6,2)        NOT NULL,
    CONSTRAINT pk_ordercontains PRIMARY KEY (orderid, itemid),
    CONSTRAINT fk_ordercontains_orders FOREIGN KEY (orderid)
        REFERENCES orders(orderid),
    CONSTRAINT fk_ordercontains_item FOREIGN KEY (itemid)
        REFERENCES item(itemid)
);

CREATE TABLE IF NOT EXISTS paysorder (
    orderid     BIGINT          NOT NULL,
    payid       BIGINT          NOT NULL,
    CONSTRAINT pk_paysorder PRIMARY KEY (orderid, payid),
    CONSTRAINT fk_paysorder_orders FOREIGN KEY (orderid)
        REFERENCES orders(orderid),
    CONSTRAINT fk_paysorder_payment FOREIGN KEY (payid)
        REFERENCES payment(payid)
);

CREATE TABLE IF NOT EXISTS processes (
    orderid     BIGINT          NOT NULL,
    empid       INT             NOT NULL,
    processdate DATE            NOT NULL,
    processtime TIME            NOT NULL,
    status      VARCHAR(10)     DEFAULT 'invcheck' NOT NULL,
    CONSTRAINT pk_processes PRIMARY KEY (orderid, empid, status),   -- added status to the pkey
    CONSTRAINT fk_processes_orders FOREIGN KEY (orderid)
        REFERENCES orders(orderid),
    CONSTRAINT fk_processes_employee FOREIGN KEY (empid)
        REFERENCES employee(empid)
);

CREATE TABLE IF NOT EXISTS shipmentcontains (
    shipid      BIGINT          NOT NULL,
    itemid      INT             NOT NULL,
    quantity    INT             NOT NULL,
    price       DEC(6,2)        NOT NULL,
    CONSTRAINT pk_shipmentcontains PRIMARY KEY (shipid, itemid),
    CONSTRAINT fk_shipmentcontains_shipment FOREIGN KEY (shipid)
        REFERENCES shipment(shipid),
    CONSTRAINT fk_shipmentcontains_item FOREIGN KEY (itemid)
        REFERENCES item(itemid)
);

CREATE TABLE IF NOT EXISTS checks (
    shipid      BIGINT          NOT NULL,
    empid       INT             NOT NULL,
    shipdate    DATE            NOT NULL,
    shiptime    TIME            NOT NULL,
    status      VARCHAR(10)     DEFAULT 'counted' NOT NULL,
    CONSTRAINT pk_checks PRIMARY KEY (shipid, empid, status),       -- added status to the pkey
    CONSTRAINT fk_checks_shipment FOREIGN KEY (shipid)
        REFERENCES shipment(shipid),
    CONSTRAINT fk_checks_employee FOREIGN KEY (empid)
        REFERENCES employee(empid)
);