-- table creation
CREATE TABLE "FOREST"."CATEGORY"
(
   ID int NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
   NAME varchar(45) NOT NULL,
   TAGS varchar(45)
)
;
CREATE UNIQUE INDEX SQL_CATEGORY_ID_INDEX ON "FOREST"."CATEGORY"(ID)
;
CREATE TABLE "FOREST"."PERSON"
(
   ID int NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
   FIRSTNAME varchar(50) NOT NULL,
   LASTNAME varchar(100) NOT NULL,
   EMAIL varchar(45) NOT NULL UNIQUE,
   ADDRESS varchar(45) NOT NULL,
   CITY varchar(45) NOT NULL,
   PASSWORD varchar(100),
   DTYPE varchar(31)
)
;
CREATE UNIQUE INDEX SQL_PERSON_EMAIL_INDEX ON "FOREST"."PERSON"(EMAIL)
;
CREATE UNIQUE INDEX SQL_PERSON_ID_INDEX ON "FOREST"."PERSON"(ID)
;
CREATE TABLE "FOREST"."GROUPS"
(
    ID int NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
    NAME varchar(50) NOT NULL,
    DESCRIPTION varchar(300)
)
;
CREATE TABLE "FOREST"."PERSON_GROUPS"
(
   GROUPS_ID int NOT NULL,
   EMAIL varchar(45) NOT NULL
)
;
ALTER TABLE "FOREST"."PERSON_GROUPS"
ADD CONSTRAINT FK_PERSON_GROUPS_PERSON
FOREIGN KEY (EMAIL)
REFERENCES "FOREST"."PERSON"(EMAIL)
;
ALTER TABLE "FOREST"."PERSON_GROUPS"
ADD CONSTRAINT FK_PERSON_GROUPS_GROUPS
FOREIGN KEY (GROUPS_ID)
REFERENCES "FOREST"."GROUPS"(ID)
;
CREATE INDEX SQL_PERSONGROUPS_EMAIL_INDEX ON "FOREST"."PERSON_GROUPS"(EMAIL)
;
CREATE INDEX SQL_PERSONGROUPS_ID_INDEX ON "FOREST"."PERSON_GROUPS"(GROUPS_ID)
;

CREATE TABLE "FOREST"."ORDER_STATUS"
(
   ID int NOT NULL PRIMARY KEY,
   STATUS varchar(45) NOT NULL,
   DESCRIPTION varchar(200)
)
;
CREATE UNIQUE INDEX SQL_ORDERSTATUS_ID_INDEX ON "FOREST"."ORDER_STATUS"(ID)
;

CREATE TABLE "FOREST"."CUSTOMER_ORDER"
(
   ID int NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
   AMOUNT float(52) NOT NULL,
   DATE_CREATED timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
   CUSTOMER_ID int NOT NULL,
   STATUS_ID int NOT NULL
)
;
ALTER TABLE "FOREST"."CUSTOMER_ORDER"
ADD CONSTRAINT FK_CUSTOMER_ORDER_ORDER_STATUS1
FOREIGN KEY (STATUS_ID)
REFERENCES "FOREST"."ORDER_STATUS"(ID)
;
ALTER TABLE "FOREST"."CUSTOMER_ORDER"
ADD CONSTRAINT FK_CUSTOMER_ORDER_CUSTOMER1
FOREIGN KEY (CUSTOMER_ID)
REFERENCES "FOREST"."PERSON"(ID)
;
CREATE INDEX SQL_ORDER_STATUS_ID_INDEX ON "FOREST"."CUSTOMER_ORDER"(STATUS_ID)
;
CREATE INDEX SQL_ORDER_CUSTOMER_ID_INDEX ON "FOREST"."CUSTOMER_ORDER"(CUSTOMER_ID)
;
CREATE UNIQUE INDEX SQL_ORDER_ID_INDEX ON "FOREST"."CUSTOMER_ORDER"(ID)
;

CREATE TABLE "FOREST"."PRODUCT"
(
   ID int NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
   NAME varchar(45) NOT NULL,
   PRICE decimal(10,2) NOT NULL,
   DESCRIPTION varchar(145) NOT NULL,
   IMG varchar(45),
   CATEGORY_ID int NOT NULL,
--   IMG_SRC VARCHAR (32000) FOR BIT DATA
   IMG_SRC blob(1073741823)
)
;
ALTER TABLE "FOREST"."PRODUCT"
ADD CONSTRAINT FK_PRODUCT_CATEGORY
FOREIGN KEY (CATEGORY_ID)
REFERENCES "FOREST"."CATEGORY"(ID)
;
CREATE UNIQUE INDEX SQL_PRODUCT_ID_INDEX ON "FOREST"."PRODUCT"(ID)
;
CREATE TABLE "FOREST"."ORDER_DETAIL"
(
   ORDER_ID int NOT NULL,
   PRODUCT_ID int NOT NULL,
   QTY int NOT NULL,
   CONSTRAINT SQL_ORDER_PRODUCT_PK PRIMARY KEY (ORDER_ID,PRODUCT_ID)
)
;
ALTER TABLE "FOREST"."ORDER_DETAIL"
ADD CONSTRAINT FK_ORDER_DETAIL_PRODUCT
FOREIGN KEY (PRODUCT_ID)
REFERENCES "FOREST"."PRODUCT"(ID)
;
ALTER TABLE "FOREST"."ORDER_DETAIL"
ADD CONSTRAINT FK_ORDER_DETAIL_ORDER
FOREIGN KEY (ORDER_ID)
REFERENCES "FOREST"."CUSTOMER_ORDER"(ID)
;
CREATE UNIQUE INDEX SQL_ORDER_DETAIL_INDEX ON "FOREST"."ORDER_DETAIL"
(
  ORDER_ID,
  PRODUCT_ID
)
;
CREATE INDEX SQL_ORDER_PRODUCT_ID_INDEX ON "FOREST"."ORDER_DETAIL"(PRODUCT_ID)
;
CREATE INDEX SQL_ORDER_DETAIL_ID_INDEX ON "FOREST"."ORDER_DETAIL"(ORDER_ID)
;
-- data
INSERT INTO "FOREST"."CATEGORY" (NAME,TAGS) VALUES ('Plants','Seeds, trees, flowers ...');
INSERT INTO "FOREST"."CATEGORY" (NAME,TAGS) VALUES ('Food','Foods, healthy items ...');
INSERT INTO "FOREST"."CATEGORY" (NAME,TAGS) VALUES ('Services','Fence installation, gardening ...');
INSERT INTO "FOREST"."CATEGORY" (NAME,TAGS) VALUES ('Tools','Tools for gardeners and landscapers');

INSERT INTO "FOREST"."PERSON" (FIRSTNAME,LASTNAME,EMAIL,ADDRESS,CITY,PASSWORD,DTYPE) VALUES ('Robert','Test','robert@example.com','Przykładowa ulica','Warszawa','81dc9bdb52d04dc20036dbd8313ed055','Client');
INSERT INTO "FOREST"."PERSON" (FIRSTNAME,LASTNAME,EMAIL,ADDRESS,CITY,PASSWORD,DTYPE) VALUES ('Admin','Admin','admin@example.com','Przykładowa ulica','Gliwice','81dc9bdb52d04dc20036dbd8313ed055','Administrator');
INSERT INTO "FOREST"."PERSON" (FIRSTNAME,LASTNAME,EMAIL,ADDRESS,CITY,PASSWORD,DTYPE) VALUES ('Jacek','Testowy','jack@example.com','Przykładowa ulica','Warszawa','81dc9bdb52d04dc20036dbd8313ed055','Client');
INSERT INTO "FOREST"."PERSON" (FIRSTNAME,LASTNAME,EMAIL,ADDRESS,CITY,PASSWORD,DTYPE) VALUES ('Payment','User','paymentUser@dukesforest.com','-','-','58175e1df62779046a3a4e2483575937','Client');

INSERT INTO "FOREST"."GROUPS" (NAME, DESCRIPTION) VALUES ('USERS', 'Klienci sklepu');
INSERT INTO "FOREST"."GROUPS" (NAME, DESCRIPTION) VALUES ('ADMINS', 'Administratorzy sklepu');

INSERT INTO "FOREST"."PERSON_GROUPS" (GROUPS_ID,EMAIL) VALUES (1,'robert@example.com');
INSERT INTO "FOREST"."PERSON_GROUPS" (GROUPS_ID,EMAIL) VALUES (2,'admin@example.com');
INSERT INTO "FOREST"."PERSON_GROUPS" (GROUPS_ID,EMAIL) VALUES (1,'jack@example.com');
INSERT INTO "FOREST"."PERSON_GROUPS" (GROUPS_ID,EMAIL) VALUES (1,'paymentUser@dukesforest.com');

INSERT INTO "FOREST"."ORDER_STATUS" (ID,STATUS,DESCRIPTION) VALUES (1,'Pending processing','');
INSERT INTO "FOREST"."ORDER_STATUS" (ID,STATUS,DESCRIPTION) VALUES (2,'Validating payment',''); 
INSERT INTO "FOREST"."ORDER_STATUS" (ID,STATUS,DESCRIPTION) VALUES (3,'Ready to ship','Payment approved');
INSERT INTO "FOREST"."ORDER_STATUS" (ID,STATUS,DESCRIPTION) VALUES (4,'Order shipped', '');
INSERT INTO "FOREST"."ORDER_STATUS" (ID,STATUS,DESCRIPTION) VALUES (5,'Order cancelled','$1000 order limit exceeded');
INSERT INTO "FOREST"."ORDER_STATUS" (ID,STATUS,DESCRIPTION) VALUES (6,'Order cancelled','Cancelled by administrator');

--CALL SYSCS_UTIL.SYSCS_IMPORT_DATA ('FOREST', 'PRODUCT', 'NAME,PRICE,DESCRIPTION,IMG,CATEGORY_ID,IMG_SRC', '2,3,4,5,6,7', '/tmp/products.del', null, null, null,0);
