
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = 'VCLib' AND table_name = 'T_GEN_NUMBERS') 
THEN
	CREATE TABLE VCLib.T_GEN_NUMBERS
	(
	    "gennumbersid" uuid NOT NULL,
	    "entityid" integer NOT NULL,
	    "numbername" varchar(64) NOT NULL,
	    "nextnumber" bigint NOT NULL
	);
	ALTER TABLE VCLib.T_GEN_NUMBERS ADD CONSTRAINT PK_T_GEN_NUMBERS
	PRIMARY KEY (GenNumbersID);
	ALTER TABLE VCLib.T_GEN_NUMBERS ADD CONSTRAINT FK_T_ENTITIES_T_GEN_NUMBERS_EntityID
	FOREIGN KEY("entityid") REFERENCES VCLib.T_ENTITIES (EntityID);
END IF;

INSERT INTO VCLib.T_GEN_NUMBERS (GenNumbersID, EntityID, NumberName, NextNumber)
SELECT uuid_generate_v4(), gen.EntityID, gen.NumberName, MAX(gen.NextNumber) AS NextNumber
FROM RDS.T_GEN_NUMBERS gen
JOIN VCLib.T_ENTITIES ent ON ent.EntityID = gen.EntityID
WHERE ent."Schema" IN ('STOR', 'PHARM', 'RDS') 
GROUP BY gen.NumberName, gen.EntityID;