CREATE TABLE CATEGORIES (
    ID INTEGER NOT NULL,
    NAME VARCHAR(50),
    PARENT_CATEGORY INTEGER,
    PRIMARY KEY (ID)
);
CREATE TABLE GOODS (
    ID INTEGER NOT NULL,
    NAME VARCHAR(100),
    DESCRIPTION BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    CATEGORY INTEGER,
    PRIMARY KEY (ID)
);
CREATE TABLE SHOPS (
    ID INTEGER NOT NULL,
    NAME VARCHAR(100),
    DESCRIPTION BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    PRIMARY KEY (ID)
);
CREATE TABLE PURCHASES (
    ID INTEGER NOT NULL,
    SHOPPING_DATE DATE,
    SHOP INTEGER,
    PRIMARY KEY (ID)
);
CREATE TABLE PURCHASE_GOODS(
    PURCHASE_ID INTEGER,
    GOODS_ID INTEGER,
    PRICE INTEGER,
    COST INTEGER
);
ALTER TABLE CATEGORIES ADD CONSTRAINT FK_CATEGORIES 
FOREIGN KEY (PARENT_CATEGORY) REFERENCES CATEGORIES (ID) 
ON DELETE SET NULL;
ALTER TABLE GOODS ADD CONSTRAINT FK_GOODS 
FOREIGN KEY (CATEGORY) REFERENCES CATEGORIES (ID) 
ON UPDATE SET NULL;
ALTER TABLE PURCHASES ADD CONSTRAINT FK_PURCHASES 
FOREIGN KEY (SHOP) REFERENCES SHOPS (ID) 
ON DELETE SET NULL;
ALTER TABLE PURCHASE_GOODS ADD CONSTRAINT FK1_PURCHASE_GOODS
 FOREIGN KEY (GOODS_ID) REFERENCES GOODS (ID) 
ON DELETE CASCADE;
ALTER TABLE PURCHASE_GOODS ADD CONSTRAINT FK2_PURCHASE_GOODS 
FOREIGN KEY (PURCHASE_ID) REFERENCES PURCHASES (ID) 
ON DELETE CASCADE;

CREATE GENERATOR CATEGORIES_ID_GEN;
SET TERM ^ ;
CREATE TRIGGER SET_CATEGORIES_ID FOR CATEGORIES
ACTIVE BEFORE INSERT POSITION 0
AS 
BEGIN 
    if (NEW.ID is NULL) THEN
    NEW.ID = GEN_ID(CATEGORIES_ID_GEN, 1);
END^
SET TERM ; ^


CREATE GENERATOR SHOPS_ID_GEN;
SET TERM ^ ;
CREATE TRIGGER SET_SHOPS_ID FOR SHOPS
ACTIVE BEFORE INSERT POSITION 0
AS 
BEGIN 
    if (NEW.ID is NULL) THEN
    NEW.ID = GEN_ID(SHOPS_ID_GEN, 1);
END^
SET TERM ; ^


CREATE GENERATOR GOODS_ID_GEN;
SET TERM ^ ;
CREATE TRIGGER SET_GOODS_ID FOR GOODS
ACTIVE BEFORE INSERT POSITION 0
AS 
BEGIN 
    if (NEW.ID is NULL) THEN
    NEW.ID = GEN_ID(GOODS_ID_GEN, 1);
END^
SET TERM ; ^


CREATE GENERATOR PURCHASES_ID_GEN;
SET TERM ^ ;
CREATE TRIGGER SET_PURCHASES_ID FOR PURCHASES
ACTIVE BEFORE INSERT POSITION 0
AS 
BEGIN 
    if (NEW.ID is NULL) THEN
    NEW.ID = GEN_ID(PURCHASES_ID_GEN, 1);
END^
SET TERM ; ^
