-- Tables

/***** Object:  Table STOR.T_STORAGE_LINK_MARK_SCHEMES *****/
CREATE TABLE STOR.T_STORAGE_LINK_MARK_SCHEMES
(
    "storagelinkmarkschemesid" uuid NOT NULL,
    "consumer" uuid NULL,
    "supplier" uuid NULL,
    "mainschema" boolean NOT NULL,
    "markschemes" integer NOT NULL
);

-- Alters

ALTER TABLE STOR.T_STORAGE_LINK_MARK_SCHEMES ADD CONSTRAINT PK_T_STORAGE_LINK_MARK_SCHEMES
PRIMARY KEY (StorageLinkMarkSchemesID);

ALTER TABLE STOR.T_STORAGE_LINK_MARK_SCHEMES ADD CONSTRAINT FK_T_CONSUMER_T_STORAGE_LINK_MARK_SCHEMES_Consumer
FOREIGN KEY("consumer") REFERENCES STOR.T_CONSUMER (ConsumerID);

ALTER TABLE STOR.T_STORAGE_LINK_MARK_SCHEMES ADD CONSTRAINT FK_T_SUPPLIER_T_STORAGE_LINK_MARK_SCHEMES_Supplier
FOREIGN KEY("supplier") REFERENCES STOR.T_SUPPLIER (SupplierID);

ALTER TABLE STOR.T_STORAGE_LINK_MARK_SCHEMES ADD CONSTRAINT FK_T_MARK_SCHEMES_T_STORAGE_LINK_MARK_SCHEMES_MarkSchemes
FOREIGN KEY("markschemes") REFERENCES STOR.T_MARK_SCHEMES (Code);

Insert INTO STOR.T_STORAGE_LINK_MARK_SCHEMES(
			StorageLinkMarkSchemesID,
			Consumer,
			Supplier,
			MainSchema,
			MarkSchemes)
select gen_random_uuid(),
		c.ConsumerID,
		null,
		true,
		c.MarkSchemes
from STOR.T_CONSUMER c 
where c.MarkSchemes is not null;

Insert INTO STOR.T_STORAGE_LINK_MARK_SCHEMES(
			StorageLinkMarkSchemesID,
			Consumer,
			Supplier,
			MainSchema,
			MarkSchemes)
select gen_random_uuid(),
		null,
		s.SupplierID,
		true,
		s.MarkSchemes
from STOR.T_SUPPLIER s 
where s.MarkSchemes is not null;

ALTER TABLE STOR.T_CONSUMER DROP CONSTRAINT FK_T_MARK_SCHEMES_T_CONSUMER_MarkSchemes;

ALTER TABLE STOR.T_CONSUMER DROP COLUMN "markschemes";

ALTER TABLE STOR.T_SUPPLIER DROP CONSTRAINT FK_T_MARK_SCHEMES_T_SUPPLIER_MarkSchemes;

ALTER TABLE STOR.T_SUPPLIER DROP COLUMN "markschemes";

