Esquema BD PGC Fabio

--ENUMs
--------------------
CREATE TYPE order_subtype AS ENUM ('sales', 'purchase');
CREATE TYPE product_subtype AS ENUM('good', 'service');

--ORDER and ORDER ITEM
---------------------

CREATE TABLE IF NOT EXISTS order_table (
    id integer,
    order_date DATE NOT NULL DEFAULT CURRENT_DATE,
    entry_date DATE NOT NULL DEFAULT CURRENT_DATE,
    subtype order_subtype NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS product (
    id integer,
    subtype product_subtype NOT NULL,
    name VARCHAR(255) NOT NULL DEFAULT '',
    introduction_date DATE NOT NULL DEFAULT CURRENT_DATE,
    sales_discontinuation_date DATE NOT NULL DEFAULT '9999-12-31',
    support_discontinuation_date DATE NOT NULL DEFAULT '9999-12-31',
    comment VARCHAR(255) NOT NULL DEFAULT '',
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS order_item (
    order_item_seq_id integer NOT NULL DEFAULT 1,
    quantity smallint NOT NULL DEFAULT 0,
    unit_price DECIMAL(13,4) NOT NULL DEFAULT 0,
    estimated_delivery_date DATE NOT NULL DEFAULT '1000-01-01',
    shipping_instructions VARCHAR(255) NOT NULL DEFAULT '',
    comment VARCHAR(255) NOT NULL DEFAULT '',
    order_id integer NOT NULL,
    Product_id integer NOT NULL,
    PRIMARY KEY (order_item_seq_id, order_id),
    FOREIGN KEY (order_id) REFERENCES order_table(id),
    FOREIGN KEY (Product_id) REFERENCES product(id)
);