DROP TABLE OPENJPA_SEQUENCE_TABLE;

DROP TABLE ORDER_DETAIL;
DROP TABLE ORDER_INVOICE;
DROP TABLE ORDERS;
DROP TABLE CUSTOMER;
DROP TABLE PRODUCT;

-- CREATE DATABASE "JPA-RELATIONSHIPBD"
--   WITH OWNER = postgres
--        ENCODING = 'SQL_ASCII'
--        TABLESPACE = pg_default;

CREATE TABLE OPENJPA_SEQUENCE_TABLE (ID SMALLINT NOT NULL, SEQUENCE_VALUE BIGINT, PRIMARY KEY (ID));

CREATE TABLE CUSTOMER (
      CUST_ID INTEGER NOT NULL ,
      FIRST_NAME VARCHAR(50) ,
      LAST_NAME  VARCHAR(50) NOT NULL,
      STREET  VARCHAR(50),
      APPT  VARCHAR(20),
      CITY  VARCHAR(25),
      ZIP_CODE  VARCHAR(10) NOT NULL,
      EMAIL  VARCHAR(30) NOT NULL,
      LAST_UPDATED_TIME TIMESTAMP
);

ALTER TABLE CUSTOMER ADD CONSTRAINT CUSTOMER_PK Primary Key (CUST_ID);

CREATE TABLE PRODUCT (
      PROD_ID INTEGER NOT NULL ,
      PROD_NAME VARCHAR(50) NOT NULL,
      PROD_DESC  VARCHAR(200),
      REGULAR_PRICE  DOUBLE PRECISION,
      LAST_UPDATED_TIME TIMESTAMP
     
);

ALTER TABLE PRODUCT ADD CONSTRAINT PRODUCT_PK Primary Key (PROD_ID);

CREATE TABLE ORDERS (
      ORDER_ID INTEGER NOT NULL ,
      CUST_ID INTEGER NOT NULL ,
      TOTAL_PRICE  DOUBLE PRECISION,
      OREDER_DESC  VARCHAR(100),
      ORDER_DATE  TIMESTAMP,
      LAST_UPDATED_TIME TIMESTAMP
);

ALTER TABLE ORDERS ADD CONSTRAINT ORDER_PK Primary Key (ORDER_ID);

ALTER TABLE ORDERS ADD CONSTRAINT ORDER_FK Foreign Key (CUST_ID) REFERENCES CUSTOMER (CUST_ID);


CREATE TABLE ORDER_DETAIL (
      ORDER_ID INTEGER NOT NULL ,
      PROD_ID INTEGER NOT NULL,
      PRICE  DOUBLE PRECISION,
      LAST_UPDATED_TIME TIMESTAMP
);

ALTER TABLE ORDER_DETAIL ADD CONSTRAINT ORDER_DETAIL_PK Primary Key (ORDER_ID,PROD_ID);

ALTER TABLE ORDER_DETAIL ADD CONSTRAINT ORDER_ID_FK Foreign Key (ORDER_ID) REFERENCES ORDERS (ORDER_ID);

ALTER TABLE ORDER_DETAIL ADD CONSTRAINT PROD_ID_FK Foreign Key (PROD_ID) REFERENCES PRODUCT (PROD_ID);

CREATE TABLE ORDER_INVOICE (
      INVOICE_ID INTEGER NOT NULL ,
      ORDER_ID INTEGER NOT NULL ,
      DATE_RAISED TIMESTAMP,
      AMOUNT_DUE  DOUBLE PRECISION,
      DATE_SETTLED TIMESTAMP,
      DATE_CANCELLED TIMESTAMP,
      LAST_UPDATED_TIME TIMESTAMP
);

ALTER TABLE ORDER_INVOICE ADD CONSTRAINT ORDER_INVOICE_PK Primary Key (INVOICE_ID);

ALTER TABLE ORDER_INVOICE ADD CONSTRAINT ORDER_INVOICE_FK Foreign Key (ORDER_ID) REFERENCES ORDERS (ORDER_ID);


INSERT INTO CUSTOMER(CUST_ID,FIRST_NAME,LAST_NAME,STREET,APPT,CITY,ZIP_CODE,EMAIL,LAST_UPDATED_TIME) 
	VALUES (100,'Jupiter','Jone','10 Downing Street','123','London','12345', 'j@nobody.com', current_timestamp);


INSERT INTO CUSTOMER(CUST_ID,FIRST_NAME,LAST_NAME,STREET,APPT,CITY,ZIP_CODE,EMAIL,LAST_UPDATED_TIME) 
	VALUES (110,'Mike','Peterson','20 Downing Street','456','New York','12345', 'm@nobody.com' ,current_timestamp);

INSERT INTO PRODUCT(PROD_ID,PROD_NAME,PROD_DESC,REGULAR_PRICE,LAST_UPDATED_TIME) 
	VALUES (2000,'A','Product A',60, current_timestamp);

INSERT INTO PRODUCT(PROD_ID,PROD_NAME,PROD_DESC,REGULAR_PRICE,LAST_UPDATED_TIME) 
	VALUES (2010,'B','Product B',30, current_timestamp);

INSERT INTO PRODUCT(PROD_ID,PROD_NAME,PROD_DESC,REGULAR_PRICE,LAST_UPDATED_TIME) 
	VALUES (2020,'C','Product C',40, current_timestamp);

INSERT INTO PRODUCT(PROD_ID,PROD_NAME,PROD_DESC,REGULAR_PRICE,LAST_UPDATED_TIME) 
	VALUES (2030,'D','Product D',50, current_timestamp);

INSERT INTO PRODUCT(PROD_ID,PROD_NAME,PROD_DESC,REGULAR_PRICE,LAST_UPDATED_TIME) 
	VALUES (2040,'E','Product E',70, current_timestamp);


INSERT INTO ORDERS(ORDER_ID,CUST_ID,TOTAL_PRICE,OREDER_DESC,ORDER_DATE,LAST_UPDATED_TIME) 
	VALUES (111,100,100,'Internet and phone',current_timestamp,current_timestamp);

INSERT INTO ORDERS(ORDER_ID,CUST_ID,TOTAL_PRICE,OREDER_DESC,ORDER_DATE,LAST_UPDATED_TIME) 
	VALUES (222,100,15,'Cable at discounted price',current_timestamp,current_timestamp);


INSERT INTO ORDERS(ORDER_ID,CUST_ID,TOTAL_PRICE,OREDER_DESC,ORDER_DATE,LAST_UPDATED_TIME) 
	VALUES (333,110,99,'3 in one offer',current_timestamp,current_timestamp);


INSERT INTO ORDER_DETAIL(ORDER_ID,PROD_ID,PRICE,LAST_UPDATED_TIME) 
	VALUES (111,2000,60,current_timestamp);

INSERT INTO ORDER_DETAIL(ORDER_ID,PROD_ID,PRICE,LAST_UPDATED_TIME) 
	VALUES (111,2020,40,current_timestamp);

INSERT INTO ORDER_DETAIL(ORDER_ID,PROD_ID,PRICE,LAST_UPDATED_TIME) 
	VALUES (222,2010,15,current_timestamp);

INSERT INTO ORDER_DETAIL(ORDER_ID,PROD_ID,PRICE,LAST_UPDATED_TIME) 
	VALUES (333,2000,33,current_timestamp);

INSERT INTO ORDER_DETAIL(ORDER_ID,PROD_ID,PRICE,LAST_UPDATED_TIME) 
	VALUES (333,2010,33,current_timestamp);

INSERT INTO ORDER_DETAIL(ORDER_ID,PROD_ID,PRICE,LAST_UPDATED_TIME) 
	VALUES (333,2020,33,current_timestamp);

INSERT INTO ORDER_INVOICE(INVOICE_ID,ORDER_ID,DATE_RAISED,AMOUNT_DUE,DATE_SETTLED,DATE_CANCELLED,LAST_UPDATED_TIME) 
	VALUES (1,111,current_timestamp,100,current_timestamp,null,current_timestamp);


INSERT INTO ORDER_INVOICE(INVOICE_ID,ORDER_ID,DATE_RAISED,AMOUNT_DUE,DATE_SETTLED,DATE_CANCELLED,LAST_UPDATED_TIME) 
	VALUES (2,222,current_timestamp,15,current_timestamp,current_timestamp,current_timestamp);


INSERT INTO ORDER_INVOICE(INVOICE_ID,ORDER_ID,DATE_RAISED,AMOUNT_DUE,DATE_SETTLED,DATE_CANCELLED,LAST_UPDATED_TIME) 
	VALUES (3,333,current_timestamp,99,current_timestamp,null,current_timestamp);

