CREATE SCHEMA SUPPLY_CHAIN;

CREATE TABLE SUPPLY_CHAIN.SUPPLIER (
    supnr CHAR(4) PRIMARY KEY,
    supname VARCHAR(40) NOT NULL,
    supaddress VARCHAR(50),
    supcity VARCHAR(20),
    supstatus SMALLINT CHECK (supstatus >= 0 AND supstatus <= 100),
    supcategory VARCHAR(10) DEFAULT 'SILVER' NOT NULL
);

CREATE TYPE SUPPLY_CHAIN.product_type AS ENUM ('white', 'red', 'rose', 'sparkling');

CREATE TABLE SUPPLY_CHAIN.PRODUCT (
    prodnr CHAR(6) PRIMARY KEY,
    prodname VARCHAR(60) UNIQUE NOT NULL,
    prodtype SUPPLY_CHAIN.product_type,
    available_quantity INTEGER,
    prodimage BYTEA
);

CREATE TABLE SUPPLY_CHAIN.SUPPLIES (
    supnr CHAR(4)
        NOT NULL
        REFERENCES SUPPLY_CHAIN.SUPPLIER(supnr)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    prodnr CHAR(6)
        NOT NULL
        REFERENCES SUPPLY_CHAIN.PRODUCT(prodnr)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    purchase_price DECIMAL(8,2),
    deliv_period TIME,
    PRIMARY KEY (supnr, prodnr)
);

COMMENT ON COLUMN SUPPLY_CHAIN.SUPPLIES.purchase_price IS 'in EUR';
COMMENT ON COLUMN SUPPLY_CHAIN.SUPPLIES.deliv_period IS 'in days';

CREATE TABLE SUPPLY_CHAIN.PURCHASE_ORDER (
    ponr CHAR(7) PRIMARY KEY,
    podate DATE,
    supnr CHAR(4)
        NOT NULL
        REFERENCES SUPPLY_CHAIN.SUPPLIER(supnr)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE SUPPLY_CHAIN.PO_LINE (
    ponr CHAR(7)
        NOT NULL
        REFERENCES SUPPLY_CHAIN.PURCHASE_ORDER(ponr)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    prodnr CHAR(6)
        NOT NULL
        REFERENCES SUPPLY_CHAIN.PRODUCT(prodnr)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    quantity INTEGER,
    PRIMARY KEY (ponr, prodnr)
);