CREATE TABLE customer_tbl
(
surname            VARCHAR (20)  CONSTRAINT surname_nn               NOT NULL,
forename           VARCHAR (20)  CONSTRAINT forname_nn               NOT NULL,
address            VARCHAR (40)  CONSTRAINT address_nn               NOT NULL,
post_code          VARCHAR (8)   CONSTRAINT post_code_nn             NOT NULL,
email              VARCHAR (20)	 CONSTRAINT email_nn                 NOT NULL,                   -- All customers sign up via our website so must have an email address
customer_id        VARCHAR (20)  CONSTRAINT customer_id_pk           PRIMARY KEY,
customer_since     DATE
);


CREATE TABLE commercial_tbl
(
company_code	     VARCHAR2(8),                                                                      -- Some companies are not registered as Ltd so do not have company code
company_name	     VARCHAR2(20)   CONSTRAINT company_name_nn          NOT NULL,
company_address    VARCHAR2(40)   CONSTRAINT company_address_nn       NOT NULL,
company_email 	   VARCHAR2(20)	  CONSTRAINT company_email_nn         NOT NULL,
company_id         VARCHAR2(20)	  CONSTRAINT company_id_pk            PRIMARY KEY,
customer_id	       VARCHAR2(20)	  CONSTRAINT customer_id_u 			      UNIQUE             CONSTRAINT customer_id_fk       REFERENCES customer_tbl(customer_id)
);


CREATE TABLE order_tbl
(
customer_id                       CONSTRAINT customer_id              REFERENCES customer_tbl(customer_id),
order_date_time    DATE,
order_id 		       VARCHAR(20)    CONSTRAINT order_id_pk              PRIMARY KEY

);


CREATE TABLE payment_tbl
(
payment_id         VARCHAR(9)     CONSTRAINT payment_id_pk            PRIMARY KEY,
payment_method     VARCHAR(20)    CONSTRAINT payment_method_nn        NOT NULL,
payment_total      NUMBER (6,2)   CONSTRAINT payment_total_nn         NOT NULL,
payment_confirmed  DATE,
amount_paid        NUMBER(6,2)    CONSTRAINT ammount_paid_nn          NOT NULL,
amount_owed        NUMBER(6,2),
billing_address    VARCHAR(30)    CONSTRAINT billing_address_nn       NOT NULL,
billing_address_post_code         VARCHAR(8)                          CONSTRAINT billing_address_post_code_nn            NOT NULL
);


CREATE TABLE pay_link_tbl
(
payment_id                        CONSTRAINT  payment_id              REFERENCES payment_tbl(payment_id),
order_id                          CONSTRAINT  order_id                REFERENCES order_tbl(order_id),
                                  CONSTRAINT  pay_link_pk             PRIMARY KEY (order_id, payment_id)
);


CREATE TABLE product_tbl
(
product_name                      VARCHAR(20)                         CONSTRAINT  product_name_nn NOT NULL,
product_dsc                       VARCHAR (50)                        CONSTRAINT  product_desc_nn NOT NULL,
product_price                     NUMBER(5,2)                         CONSTRAINT  product_price_nn NOT NULL,
product_id 	                      NUMBER (10)                         CONSTRAINT  product_id_pk PRIMARY KEY
);


CREATE TABLE product_category_tbl
(
category_id                      VARCHAR(99)                           CONSTRAINT category_id_pk                         PRIMARY KEY,
category_desc                    VARCHAR(50)                           CONSTRAINT category_desc_nn                        NOT NULL
);


CREATE TABLE product_category_link_tbl
(


category_id                      CONSTRAINT                           REFERENCES product_category_tbl(category_id),
product_id                       CONSTRAINT                           REFERENCES   product_tbl(product_id),
                                 CONSTRAINT product_category_link_pk  PRIMARY KEY (category_id, product_id)
);

CREATE TABLE delivery_tbl
(
delivery_date_time               DATE,                                                                                         --The package might not have been delivered yet
delivery_address                 VARCHAR(40)                          CONSTRAINT delivery_address_nn                     NOT NULL,
delivery_courier                 VARCHAR (20)                         CONSTRAINT delivery_courier_nn                     NOT NULL,
delivery_id                      VARCHAR(10)                          CONSTRAINT delivery_id_pk                          PRIMARY KEY
);

CREATE TABLE order_item_tbl
(
order_quantity 	                 NUMBER(5)                            CONSTRAINT order_quantity_nn                       NOT NULL,
order_id                         CONSTRAINT                           REFERENCES order_tbl(order_id),
delivery_id                      CONSTRAINT                           REFERENCES delivery_tbl(delivery_id),
product_id                       CONSTRAINT                           REFERENCES product_tbl(product_id),
                                 CONSTRAINT   order_item_tbl_pk       PRIMARY KEY (order_id, delivery_id, product_id)
);