CREATE GENERATOR GEN_ZAUVAG_ID;
CREATE TABLE ZAUVAG (
    ID            BIGINT NOT NULL,
    DATA_ROZGL    DATE NOT NULL,
    DATA_MAIL     DATE,
    DATA_CORRECT  DATE,
    ID_OBLAST     INTEGER NOT NULL,
    ID_RAJON      INTEGER NOT NULL,
    TIP           SMALLINT,
    ID_PROG       INTEGER,
    ZMIST         VARCHAR(2500),
    RESULTS       VARCHAR(1000),
    PRIMER        VARCHAR(20)
);
ALTER TABLE ZAUVAG ADD CONSTRAINT PK_ZAUVAG PRIMARY KEY (ID);

ALTER TABLE ZAUVAG ADD CONSTRAINT FK_ZAUVAG_OBL FOREIGN KEY (ID_OBLAST) REFERENCES OBLAST (ID) ON DELETE SET NULL;
ALTER TABLE ZAUVAG ADD CONSTRAINT FK_ZAUVAG_PRG FOREIGN KEY (ID_PROG) REFERENCES PROGS (ID) ON DELETE SET NULL;
ALTER TABLE ZAUVAG ADD CONSTRAINT FK_ZAUVAG_RAJ FOREIGN KEY (ID_OBLAST, ID_RAJON) REFERENCES RAJON (ID_OBL, ID) ON DELETE SET NULL;
ALTER TABLE ZAUVAG ADD CONSTRAINT FK_ZAUVAG_TIP FOREIGN KEY (TIP) REFERENCES TIP_ZAUVAG (ID) ON DELETE SET NULL;

CREATE DESCENDING INDEX IDX_ZAUVAG_DATE_ROZGL ON ZAUVAG (DATA_ROZGL);
/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/
SET TERM ^ ;
/* Trigger: TR_ZAUVAG_ID */
CREATE OR ALTER TRIGGER TR_ZAUVAG_ID FOR ZAUVAG
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  /* Trigger text */
  if (new.id is null) then
    new.id = gen_id(GEN_ZAUVAG_ID,1);
end
^
SET TERM ; ^

CREATE GENERATOR GEN_ATTACH_ID;

CREATE TABLE ATTACH (
    ID           BIGINT NOT NULL,
    ID_ZAUV      BIGINT,
    FILENAME     VARCHAR(250),
    FILECONTENT  BLOB SUB_TYPE 0 SEGMENT SIZE 80,
    TIP_ATTACH   SMALLINT
);
ALTER TABLE ATTACH ADD CONSTRAINT PK_ATTACH PRIMARY KEY (ID);
CREATE INDEX IDX_ATTACH_ZAUVAG ON ATTACH (ID_ZAUV, ID);

/******************************************************************************/
/***                                Triggers                                ***/
/******************************************************************************/
SET TERM ^ ;
/* Trigger: TR_ATACH_ID */
CREATE OR ALTER TRIGGER TR_ATACH_ID FOR ATTACH
ACTIVE BEFORE INSERT POSITION 0
AS
begin
  /* Trigger text */
  if (new.id is null) then
    new.id = gen_id(gen_attach_id,1);
end
^
SET TERM ; ^

SELECT 
    ZAU.DATA_ROZGL, 
    ZAU.DATA_MAIL,
    ZAU.DATA_CORRECT, 
    lpad(trim(cast(ZAU.ID_OBLAST as char(5))),2,0) || lpad(trim(cast(ZAU.ID_RAJON as char(5))),2,0) || ' ' || RJ.NAME as rajon,
    tip.name as tip,
    prg.name as progs,
    ZAU.PRIMER, 
    ZAU.ZMIST,
    ZAU.RESULTS 
FROM 
    ZAUVAG ZAU, 
    rajon rj, 
    oblast obl, 
    progs prg, 
    tip_zauvag tip 
WHERE 
    (zau.id_oblast=obl.id)and
    (zau.id_rajon=rj.id)and
    (zau.id_prog=prg.id)and
    (zau.tip=tip.id)and
    (rj.id_obl=obl.id)and
    (zau.DATA_CORRECT is null) 
order by zau.data_rozgl DESC, rajon

SELECT 
    ZAU.DATA_ROZGL, 
    ZAU.DATA_MAIL,
    ZAU.DATA_CORRECT, 
    lpad(trim(cast(ZAU.ID_OBLAST as char(5))),2,0) || lpad(trim(cast(ZAU.ID_RAJON as char(5))),2,0) || ' ' || RJ.NAME as rajon,
    tip.name as tip,
    prg.name as progs,
    ZAU.PRIMER, 
    ZAU.ZMIST,
    ZAU.RESULTS 
FROM 
    ZAUVAG ZAU, 
    rajon rj, 
    oblast obl, 
    progs prg, 
    tip_zauvag tip 
WHERE 
    (zau.id_oblast=obl.id)and
    (zau.id_rajon=rj.id)and
    (zau.id_prog=prg.id)and
    (zau.tip=tip.id)and
    (rj.id_obl=obl.id)and
    (zau.DATA_CORRECT is null) 
order by zau.data_rozgl DESC, rajon

CREATE TABLE ZAUVAG (
    ID            BIGINT NOT NULL,
    DATA_ROZGL    DATE NOT NULL,
    DATA_MAIL     DATE,
    DATA_CORRECT  DATE,
    ID_OBLAST     INTEGER NOT NULL,
    ID_RAJON      INTEGER NOT NULL,
    TIP           SMALLINT,
    ID_PROG       INTEGER,
    ZMIST         VARCHAR(2500),
    RESULTS       VARCHAR(1000),
    PRIMER        VARCHAR(20)
);

ALTER TABLE ZAUVAG ADD CONSTRAINT PK_ZAUVAG PRIMARY KEY (ID);

ALTER TABLE ZAUVAG ADD CONSTRAINT FK_ZAUVAG_OBL FOREIGN KEY (ID_OBLAST) REFERENCES OBLAST (ID) ON DELETE SET NULL;
ALTER TABLE ZAUVAG ADD CONSTRAINT FK_ZAUVAG_PRG FOREIGN KEY (ID_PROG) REFERENCES PROGS (ID) ON DELETE SET NULL;
ALTER TABLE ZAUVAG ADD CONSTRAINT FK_ZAUVAG_RAJ FOREIGN KEY (ID_OBLAST, ID_RAJON) REFERENCES RAJON (ID_OBL, ID) ON DELETE SET NULL;
ALTER TABLE ZAUVAG ADD CONSTRAINT FK_ZAUVAG_TIP FOREIGN KEY (TIP) REFERENCES TIP_ZAUVAG (ID) ON DELETE SET NULL;

CREATE DESCENDING INDEX IDX_ZAUVAG_DATE_ROZGL ON ZAUVAG (DATA_ROZGL);