\! gs_ktool -d all
\! gs_ktool -g

DROP CLIENT MASTER KEY MyCMK CASCADE;
CREATE CLIENT MASTER KEY MyCMK WITH ( KEY_STORE = gs_ktool , KEY_PATH = "gs_ktool/1" , ALGORITHM = AES_256_CBC);
CREATE COLUMN ENCRYPTION KEY MyCEK WITH VALUES (CLIENT_MASTER_KEY = MyCMK, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);
CREATE COLUMN ENCRYPTION KEY MyCEK2 WITH VALUES (CLIENT_MASTER_KEY = MyCMK, ALGORITHM = AEAD_AES_256_CBC_HMAC_SHA256);
CREATE TABLE so_headers (
   id INTEGER ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = DETERMINISTIC),
   customer_id INTEGER  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK2, ENCRYPTION_TYPE = DETERMINISTIC),
   ship_to VARCHAR (255)
);

CREATE TABLE so_items (
   item_id INTEGER NOT NULL,
   so_id INTEGER ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = DETERMINISTIC),
   product_id INTEGER,
   qty INTEGER,
   net_price NUMERIC,
   PRIMARY KEY (item_id, so_id),
   FOREIGN KEY (so_id) REFERENCES so_headers (id)
);

CREATE TABLE so_items_r (
  item_id INTEGER NOT NULL,
  so_id int4  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = DETERMINISTIC) REFERENCES so_headers(id) ON DELETE RESTRICT,
  product_id INTEGER,
  qty INTEGER,
  net_price numeric,
  PRIMARY KEY (item_id,so_id)
);
CREATE TABLE so_items_c (
  item_id int4 NOT NULL,   
  so_id int4  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = DETERMINISTIC) REFERENCES so_headers(id) ON DELETE CASCADE,
  product_id int4,
  qty int4,
  net_price numeric,
  PRIMARY KEY (item_id,so_id)
);

CREATE TABLE payments (
   pay_id int,
   so_id INTEGER ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = DETERMINISTIC),
   customer_id INTEGER  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK2, ENCRYPTION_TYPE = DETERMINISTIC),
   FOREIGN KEY (so_id, customer_id) REFERENCES so_headers (id, customer_id)
);

CREATE TABLE so_items_a (
  item_id INTEGER NOT NULL,
  so_id int4  ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = MyCEK, ENCRYPTION_TYPE = DETERMINISTIC),
  product_id INTEGER,
  qty INTEGER,
  net_price numeric,
  PRIMARY KEY (item_id,so_id)
);

INSERT INTO so_headers VALUES (1,101, 'Vina');
INSERT INTO so_headers VALUES (2,103, 'Paris');

INSERT INTO so_items VALUES (10001, 1, 1001, 100, 37.28);
INSERT INTO so_items VALUES (10002, 6, 1001, 100, 37.28);
INSERT INTO so_items VALUES (10003, 2, 1001, 100, 37.28);
SELECT * from so_items ORDER BY item_id;

INSERT INTO so_items_r VALUES (10001, 1, 1001, 100, 37.28);
INSERT INTO so_items_r VALUES (10002, 6, 1001, 100, 37.28);
INSERT INTO so_items VALUES (10003, 2, 1001, 100, 37.28);
SELECT * from so_items_r ORDER BY item_id;
INSERT INTO so_items_a VALUES (10001, 1, 1001, 100, 37.28);
INSERT INTO so_items_a VALUES (10002, 6, 1001, 100, 37.28);
INSERT INTO so_items VALUES (10003, 2, 1001, 100, 37.28);
SELECT * from so_items_a ORDER BY item_id;
INSERT INTO so_items_c VALUES (10001, 1, 1001, 100, 37.28);
INSERT INTO so_items_c VALUES (10002, 6, 1001, 100, 37.28);
INSERT INTO so_items VALUES (10003, 2, 1001, 100, 37.28);
SELECT * from so_items_c ORDER BY item_id;

DELETE from so_headers where id =2;

SELECT * from so_items ORDER BY item_id;
SELECT * from so_items_a ORDER BY item_id;
SELECT * from so_items_r ORDER BY item_id;
SELECT * from so_items_c ORDER BY item_id;

INSERT INTO payments VALUES (100001, 1, 101);
INSERT INTO payments VALUES (100002, 1, 102);
ALTER TABLE so_items_a ADD CONSTRAINT fkey_a FOREIGN KEY (so_id) REFERENCES so_headers (id);

ALTER TABLE so_items_a DROP CONSTRAINT fkey_a;
ALTER TABLE so_items_a ADD CONSTRAINT constraint_fk
FOREIGN KEY (so_id)
REFERENCES so_headers (id)
ON DELETE CASCADE;

DROP TABLE so_headers;
DROP TABLE so_items;
DROP TABLE so_items_r;
DROP TABLE so_items_a;
DROP TABLE so_items_b;
DROP TABLE so_items_c;
DROP TABLE payments;
DROP CLIENT MASTER KEY MyCMK CASCADE;

\! gs_ktool -d all