{$IFEXISTS SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'FPBOOK_ID' AND RDB$RELATION_NAME = 'LIB_FP_BOOKS'}

  DELETE FROM LIB_FP_BOOKS WHERE 1 = 1;
  /*
  DROP TABLE LIB_FP_BOOKS;
  */
  COMMIT WORK;

{$ENDIF}

{$IFEXISTS SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'FPLEVEL_ID' AND RDB$RELATION_NAME = 'LIB_FP_LEVELS'}

  DELETE FROM LIB_FP_SUBJECTS WHERE 1 = 1;
  DELETE FROM LIB_FP_SUBJECT_AREAS WHERE 1 = 1;
  DELETE FROM LIB_FP_LEVELS WHERE 1 = 1;
  /*
  DROP TABLE LIB_FP_LEVELS;
  */
  COMMIT WORK;

{$ENDIF}

{$IFEXISTS SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'FPSTAGE_ID' AND RDB$RELATION_NAME = 'LIB_FP_STAGES'}

  DELETE FROM LIB_FP_STAGES WHERE 1 = 1;
  /* 
  DROP TABLE LIB_FP_STAGES;
  */
  COMMIT WORK;

{$ENDIF}

{$IFEXISTS SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'S_ID' AND RDB$RELATION_NAME = 'LIB_FP_SUBJECTS'}

  DELETE FROM LIB_FP_SUBJECTS WHERE 1 = 1;
  /*
  DROP TABLE LIB_FP_SUBJECTS;
  */
  COMMIT WORK;

{$ENDIF}

{$IFEXISTS SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'SA_ID' AND RDB$RELATION_NAME = 'LIB_FP_SUBJECT_AREAS'}

  DELETE FROM LIB_FP_SUBJECT_AREAS WHERE 1 = 1;
  /*
  DROP TABLE LIB_FP_SUBJECT_AREAS;
  */
  COMMIT WORK;

{$ENDIF}

{$IFNOTEXISTS SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'PUBLISHER_ID' AND RDB$RELATION_NAME = 'LIB_FP_PUBLISHER'}

  CREATE TABLE LIB_FP_PUBLISHER (
    PUBLISHER_ID    BIGINT NOT NULL,
    PUBLISHER_NAME  VARCHAR(300) CHARACTER SET UTF8 NOT NULL,
    DATE_DEL        TIMESTAMP,
    DATE_MODIFY     TIMESTAMP
  );

  ALTER TABLE LIB_FP_PUBLISHER ADD PRIMARY KEY (PUBLISHER_ID);

  CREATE OR ALTER TRIGGER LIB_FP_PUBLISHER_BI0 FOR LIB_FP_PUBLISHER
  ACTIVE BEFORE INSERT POSITION 0
  AS
  DECLARE VARIABLE G BIGINT;
  BEGIN
    SELECT GEN * MULTIPLIER FROM GEN INTO :G;
    IF (NEW.PUBLISHER_ID IS NULL) THEN
      NEW.PUBLISHER_ID = GEN_ID(LIB_DICTGEN, 1) + :G;
    NEW.DATE_MODIFY = CURRENT_TIMESTAMP;
  END;

  CREATE OR ALTER TRIGGER LIB_FP_PUBLISHER_BU0 FOR LIB_FP_PUBLISHER
  ACTIVE BEFORE UPDATE POSITION 0
  AS
  BEGIN
    NEW.DATE_MODIFY = CURRENT_TIMESTAMP;
  END;

  GRANT ALL ON LIB_FP_PUBLISHER TO INISSOFT_ROLE;
  COMMIT WORK;

{$ENDIF}

{$IFEXISTS SELECT 1 FROM RDB$RELATION_FIELDS RF JOIN RDB$FIELDS F ON F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE WHERE RF.RDB$RELATION_NAME = 'LIB_FP_PUBLISHER' AND RF.RDB$FIELD_NAME = 'PUBLISHER_NAME' AND F.RDB$CHARACTER_LENGTH = 100}

  UPDATE RDB$FIELDS SET RDB$FIELD_LENGTH = 300, RDB$CHARACTER_LENGTH = 1200
  WHERE RDB$FIELD_NAME = (SELECT RDB$FIELD_SOURCE FROM RDB$RELATION_FIELDS WHERE RDB$RELATION_NAME = 'LIB_FP_PUBLISHER' AND RDB$FIELD_NAME = 'PUBLISHER_NAME');

  COMMIT WORK;

{$ENDIF}

{$IFEXISTS SELECT 1 FROM RDB$REF_CONSTRAINTS WHERE RDB$CONSTRAINT_NAME = 'FK_LIB_FPU_5'}

  ALTER TABLE LIB_FPU DROP CONSTRAINT FK_LIB_FPU_5;
  COMMIT WORK;

{$ENDIF}

{$IFEXISTS SELECT 1 FROM LIB_FP_PUBLISHER WHERE PUBLISHER_ID = 11 AND PUBLISHER_NAME = 'АСТ'}

  DELETE FROM LIB_FP_PUBLISHER WHERE 1 = 1;
  COMMIT WORK;

{$ENDIF}

{$IFNOTEXISTS SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'OBJ_ID' AND RDB$RELATION_NAME = 'FPU_BOOKS_TEMP'}

  CREATE GLOBAL TEMPORARY TABLE FPU_BOOKS_TEMP (
    OBJ_ID  BIGINT
  ) ON COMMIT DELETE ROWS;

  GRANT ALL ON FPU_BOOKS_TEMP TO INISSOFT_ROLE;
  COMMIT WORK;

{$ENDIF}

{$IFNOTEXISTS SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'OBJ_ID' AND RDB$RELATION_NAME = 'LIB_FPU'}

  CREATE TABLE LIB_FPU (
    OBJ_ID        BIGINT NOT NULL,
    DATE_ADD      TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    DATE_MODIFY   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    DATE_DEL      TIMESTAMP,
    SUBJECT_ID    BIGINT,
    SA_ID         BIGINT,
    STAGE_ID      BIGINT,
    GRADE_FROM    SMALLINT NOT NULL,
    GRADE_TO      SMALLINT NOT NULL,
    YEAR_ADD      SMALLINT,
    YEAR_REMOVE   SMALLINT,
    PUBLISHER_ID  BIGINT,
    AUTHORS       VARCHAR(600),
    BOOKNAME      VARCHAR(1000) NOT NULL,
    ATTRS         SMALLINT DEFAULT 0 NOT NULL,
    VALID_BY      DATE,
    LANG_ID       BIGINT DEFAULT 329
  );

  ALTER TABLE LIB_FPU ADD CONSTRAINT PK_LIB_FPU PRIMARY KEY (OBJ_ID);

  ALTER TABLE LIB_FPU ADD CONSTRAINT FK_LIB_FPU_1 FOREIGN KEY (SUBJECT_ID) REFERENCES OBJECTS (OBJ_ID) ON DELETE SET NULL ON UPDATE CASCADE;
  ALTER TABLE LIB_FPU ADD CONSTRAINT FK_LIB_FPU_2 FOREIGN KEY (STAGE_ID) REFERENCES LISTVALS (LISTVAL_ID) ON DELETE CASCADE ON UPDATE CASCADE;
  ALTER TABLE LIB_FPU ADD CONSTRAINT FK_LIB_FPU_3 FOREIGN KEY (SA_ID) REFERENCES LISTVALS (LISTVAL_ID) ON DELETE CASCADE ON UPDATE CASCADE;
  ALTER TABLE LIB_FPU ADD CONSTRAINT FK_LIB_FPU_6 FOREIGN KEY (PUBLISHER_ID) REFERENCES LIB_FP_PUBLISHER (PUBLISHER_ID) ON DELETE CASCADE ON UPDATE CASCADE;

  COMMIT WORK;

  SET TERM ^ ;

  CREATE OR ALTER TRIGGER LIB_FPU_BI0 FOR LIB_FPU
  ACTIVE BEFORE INSERT POSITION 0
  as
  begin
    IF(NEW.OBJ_ID IS NULL) THEN NEW.OBJ_ID = GEN_ID(LIB_COMMONGEN, 1);
  end
  ^
  SET TERM ; ^

  GRANT ALL ON LIB_FPU TO INISSOFT_ROLE;
  COMMIT WORK;

{$ENDIF}

{$IFEXISTS SELECT 1 FROM RDB$REF_CONSTRAINTS WHERE RDB$CONSTRAINT_NAME = 'FK_LIB_FPU_4'}

  ALTER TABLE LIB_FPU DROP CONSTRAINT FK_LIB_FPU_4;
  COMMIT WORK;

{$ENDIF}

{$IFNOTEXISTS SELECT 1 FROM RDB$REF_CONSTRAINTS WHERE RDB$CONSTRAINT_NAME = 'FK_LIB_FPU_5'}

  ALTER TABLE LIB_FPU ADD CONSTRAINT FK_LIB_FPU_5 FOREIGN KEY (PUBLISHER_ID) REFERENCES LIB_FP_PUBLISHER (PUBLISHER_ID) ON DELETE SET NULL ON UPDATE CASCADE;
  COMMIT WORK;

{$ENDIF}

{$IFNOTEXISTS SELECT 1 FROM RDB$REF_CONSTRAINTS WHERE RDB$CONSTRAINT_NAME = 'FK_LIB_FPU_6'}

  ALTER TABLE LIB_FPU ADD CONSTRAINT FK_LIB_FPU_6 FOREIGN KEY (PUBLISHER_ID) REFERENCES LIB_FP_PUBLISHER (PUBLISHER_ID) ON DELETE CASCADE ON UPDATE CASCADE;
  COMMIT WORK;

{$ENDIF}

{$IFNOTEXISTS SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'VALID_BY' AND RDB$RELATION_NAME = 'LIB_FPU'}

  ALTER TABLE LIB_FPU ADD VALID_BY DATE;
  COMMIT WORK;

{$ENDIF}

{$IFNOTEXISTS SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'LANG_ID' AND RDB$RELATION_NAME = 'LIB_FPU'}

  ALTER TABLE LIB_FPU ADD LANG_ID BIGINT DEFAULT 329;
  COMMIT WORK;

{$ENDIF}

{$IFEXISTS SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'PUBLISHER_ID' AND RDB$RELATION_NAME = 'LIB_FPU' AND RDB$NULL_FLAG = 1}

  ALTER TABLE LIB_FPU ALTER PUBLISHER_ID DROP NOT NULL;
  COMMIT WORK;

{$ENDIF}


{$IFNOTEXISTS SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'BOOK_ID' AND RDB$RELATION_NAME = 'LIB_FPU_CODES'}

  CREATE TABLE LIB_FPU_CODES (
    BOOK_ID   BIGINT NOT NULL,
    DATE_ADD  DATE NOT NULL,
    DATE_DEL  TIMESTAMP,
    BOOKCODE  VARCHAR(40) NOT NULL,
    ORDER_INFO  VARCHAR(100)
  );

  COMMIT WORK;
                                                                                
  ALTER TABLE LIB_FPU_CODES ADD CONSTRAINT PK_LIB_FPU_CODES PRIMARY KEY (BOOK_ID, DATE_ADD);
  ALTER TABLE LIB_FPU_CODES ADD CONSTRAINT FK_LIB_FPU_CODES_1 FOREIGN KEY (BOOK_ID) REFERENCES LIB_FPU (OBJ_ID) ON DELETE CASCADE ON UPDATE CASCADE;

  GRANT ALL ON LIB_FPU_CODES TO INISSOFT_ROLE;
  COMMIT WORK;

{$ENDIF}

{$IFNOTEXISTS SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'ORDER_INFO' AND RDB$RELATION_NAME = 'LIB_FPU_CODES'}

    ALTER TABLE LIB_FPU_CODES ADD ORDER_INFO VARCHAR(100) CHARACTER SET UTF8 COLLATE UTF8;
    COMMIT WORK;

{$ENDIF}

{$IFNOTEXISTS SELECT 1 FROM RDB$RELATION_FIELDS WHERE RDB$FIELD_NAME = 'OBJ_ID' AND RDB$RELATION_NAME = 'LIB_FPE'}

  CREATE TABLE LIB_FPE (
    OBJ_ID        BIGINT NOT NULL,
    DATE_ADD      TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
    DATE_MODIFY   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    DATE_DEL      TIMESTAMP,
    SUBJECT_ID    BIGINT,
    STAGE_ID      BIGINT,
    GRADE_FROM    SMALLINT NOT NULL,
    GRADE_TO      SMALLINT NOT NULL,
    PUBLISHER_ID  BIGINT,
    BOOKNAME      VARCHAR(400) NOT NULL,
    DESCRIPTION   VARCHAR(4000),
    ATTRS         SMALLINT DEFAULT 0 NOT NULL,
    ORDER_NUM1    VARCHAR(40),
    ORDER_NUM2    VARCHAR(40),
    VALID_BY      DATE,
    BOOK_CODE     VARCHAR(40)
  );

  ALTER TABLE LIB_FPE ADD CONSTRAINT PK_LIB_FPE PRIMARY KEY (OBJ_ID);
  ALTER TABLE LIB_FPE ADD CONSTRAINT FK_LIB_FPE_1 FOREIGN KEY (SUBJECT_ID) REFERENCES OBJECTS (OBJ_ID) ON DELETE SET NULL ON UPDATE CASCADE;
  ALTER TABLE LIB_FPE ADD CONSTRAINT FK_LIB_FPE_2 FOREIGN KEY (STAGE_ID) REFERENCES LISTVALS (LISTVAL_ID) ON DELETE CASCADE ON UPDATE CASCADE;
  ALTER TABLE LIB_FPE ADD CONSTRAINT FK_LIB_FPE_6 FOREIGN KEY (PUBLISHER_ID) REFERENCES LIB_FP_PUBLISHER (PUBLISHER_ID) ON DELETE CASCADE ON UPDATE CASCADE;
  COMMIT WORK;

  SET TERM ^ ;
  CREATE OR ALTER TRIGGER LIB_FPE_BI0 FOR LIB_FPE
  ACTIVE BEFORE INSERT POSITION 0
  as
  begin
    IF(NEW.OBJ_ID IS NULL) THEN NEW.OBJ_ID = GEN_ID(LIB_COMMONGEN, 1);
  end
  ^
  SET TERM ; ^

  GRANT ALL ON LIB_FPE TO INISSOFT_ROLE;
  COMMIT WORK;

{$ENDIF}
