SET TERM ^ ;

execute block
as
begin
  if (not exists(select 1
                 from RDB$RELATIONS
                 where RDB$RELATION_NAME = 'XXX_NADANE_PRZESYLKI')) then
  begin
    execute statement 'CREATE TABLE XXX_NADANE_PRZESYLKI (
    ID_XXX_NADANE_PRZESYLKI  T_ID /* T_ID = INTEGER */,
    ID_SPOSOBDOST            T_ID /* T_ID = INTEGER */,
    ID_MSCSPRZEDAZY          VARCHAR(10) COLLATE WIN1250,
    NRPRZESYLKI              VARCHAR(255) COLLATE WIN1250,
    GUID                     VARCHAR(50) COLLATE WIN1250,
    ODBIORCA                 VARCHAR(255) COLLATE WIN1250,
    DOSTAWA                  VARCHAR(255) COLLATE WIN1250,
    ILOSC_PACZEK             INTEGER,
    WAGA                     VARCHAR(10) COLLATE WIN1250,
    DATA_NADANIA             T_STRING /* T_STRING = VARCHAR(255) */,
    STATUS                   VARCHAR(255) COLLATE WIN1250,
    ID_MAGAZYNMIEJSCE        VARCHAR(255) COLLATE WIN1250,
    KWOTA_POBRANIA           T_STRING /* T_STRING = VARCHAR(255) */,
    NR_RAPORTU               T_STRING100, /* T_STRING100 = VARCHAR(100) */

    CONSTRAINT PK_XXX_NADANE_PRZESYLKI PRIMARY KEY (ID_XXX_NADANE_PRZESYLKI),
    CONSTRAINT FK_XXX_NADANE_PRZESYLKI_1 FOREIGN KEY (ID_SPOSOBDOST) REFERENCES SPOSDOSTAWY (ID_SPOSDOSTAWY) ON DELETE SET NULL
)';
    execute statement 'CREATE SEQUENCE GEN_XXX_NADANE_PRZESYLKI_ID';
    execute statement 'ALTER SEQUENCE GEN_XXX_NADANE_PRZESYLKI_ID RESTART WITH 10001';
    execute statement 'CREATE INDEX XXX_NADANE_PRZESYLKI_IDX1 ON XXX_NADANE_PRZESYLKI (NRPRZESYLKI)';
    execute statement 'CREATE INDEX XXX_NADANE_PRZESYLKI_IDX2 ON XXX_NADANE_PRZESYLKI (GUID)';
    execute statement 'CREATE INDEX XXX_NADANE_PRZESYLKI_IDX3 ON XXX_NADANE_PRZESYLKI (DATA_NADANIA)';
  end
  else
  begin
    if (not exists(select 1
                   from RDB$RELATION_FIELDS RF
                   where RF.RDB$RELATION_NAME = 'XXX_NADANE_PRZESYLKI' and
                         RF.RDB$FIELD_NAME = 'KWOTA_POBRANIA')) then
      execute statement 'ALTER TABLE XXX_NADANE_PRZESYLKI ADD KWOTA_POBRANIA T_STRING';
    if (not exists(select 1
                   from RDB$RELATION_FIELDS RF
                   where RF.RDB$RELATION_NAME = 'XXX_NADANE_PRZESYLKI' and
                         RF.RDB$FIELD_NAME = 'ID_MAGAZYNMIEJSCE')) then
      execute statement 'ALTER TABLE XXX_NADANE_PRZESYLKI ADD ID_MAGAZYNMIEJSCE T_STRING';
    if (not exists(select 1
                   from RDB$RELATION_FIELDS RF
                   where RF.RDB$RELATION_NAME = 'XXX_NADANE_PRZESYLKI' and
                         RF.RDB$FIELD_NAME = 'NR_RAPORTU')) then
      execute statement 'ALTER TABLE XXX_NADANE_PRZESYLKI ADD NR_RAPORTU T_CECHAWARTOSC';

    if (not exists(select 1
                   from RDB$INDICES
                   where RDB$INDEX_NAME = 'XXX_NADANE_PRZESYLKI_IDX2')) then
    execute statement 'CREATE INDEX XXX_NADANE_PRZESYLKI_IDX2 ON XXX_NADANE_PRZESYLKI (GUID)';

    if (not exists(select 1
                   from RDB$INDICES
                   where RDB$INDEX_NAME = 'XXX_NADANE_PRZESYLKI_IDX3')) then
    execute statement 'CREATE INDEX XXX_NADANE_PRZESYLKI_IDX3 ON XXX_NADANE_PRZESYLKI (DATA_NADANIA)';

  end
end ^

execute block
as
begin
  if (not exists(select 1
                 from RDB$RELATIONS
                 where RDB$RELATION_NAME = 'XXX_WYSTDOKDODDOKUMZEW')) then
  begin
    execute statement 'CREATE TABLE XXX_WYSTDOKDODDOKUMZEW (
    ID_DODDOKUMZEW           INTEGER,
    ID_XXX_NADANE_PRZESYLKI  T_ID /* T_ID = INTEGER */,
    NAZWA                    CHAR(255),

    CONSTRAINT FK_XXX_WYSTDOKDODDOKUMZEW_1 FOREIGN KEY (ID_DODDOKUMZEW) REFERENCES DODDOKUMZEW (ID_DODDOKUMZEW) ON DELETE CASCADE,
    CONSTRAINT FK_XXX_WYSTDOKDODDOKUMZEW_2 FOREIGN KEY (ID_XXX_NADANE_PRZESYLKI) REFERENCES XXX_NADANE_PRZESYLKI (ID_XXX_NADANE_PRZESYLKI)
)';
  end
end ^

execute block
as
begin
  if (not exists(select 1
                 from RDB$RELATIONS
                 where RDB$RELATION_NAME = 'XXX_KURIER_RAPORTY')) then
  begin
    execute statement 'CREATE TABLE XXX_KURIER_RAPORTY (
    ID_RAPORT       T_ID NOT NULL /* T_ID = INTEGER */,
    PLIK            T_STRING1024 /* T_STRING1024 = VARCHAR(1024) */,
    DATA_CZAS       TIMESTAMP DEFAULT current_timestamp NOT NULL,
    STANOWISKO_WIN  T_STRING100 /* T_STRING100 = VARCHAR(100) */,
    ID_SPOSDOSTAWY  T_ID /* T_ID = INTEGER */,
    NR_RAPORTU      T_STRING100, /* T_STRING100 = VARCHAR(100) */

    CONSTRAINT PK_XXX_KURIER_RAPORTY PRIMARY KEY (ID_RAPORT),
    CONSTRAINT FK_XXX_KURIER_RAPORTY_1 FOREIGN KEY (ID_SPOSDOSTAWY) REFERENCES SPOSDOSTAWY (ID_SPOSDOSTAWY) ON DELETE SET NULL
)';
    execute statement 'CREATE SEQUENCE GEN_XXX_KURIER_RAPORTY_ID';
    execute statement 'ALTER SEQUENCE GEN_XXX_KURIER_RAPORTY_ID RESTART WITH 10001';
  end
  else
  begin
    if (not exists(select 1
                   from RDB$RELATION_FIELDS RF
                   where RF.RDB$RELATION_NAME = 'XXX_KURIER_RAPORTY' and
                         RF.RDB$FIELD_NAME = 'NR_RAPORTU')) then
      execute statement 'ALTER TABLE XXX_KURIER_RAPORTY ADD NR_RAPORTU T_STRING100';

     if (not exists(select 1
                   from RDB$INDICES
                   where RDB$INDEX_NAME = 'FK_XXX_KURIER_RAPORTY_1')) then
      execute statement 'ALTER TABLE XXX_KURIER_RAPORTY ADD CONSTRAINT FK_XXX_KURIER_RAPORTY_1 FOREIGN KEY (ID_SPOSDOSTAWY) REFERENCES SPOSDOSTAWY (ID_SPOSDOSTAWY) ON DELETE SET NULL';
  end
end ^

execute block
as
begin
  if (not exists(select 1
                 from RDB$RELATIONS
                 where RDB$RELATION_NAME = 'XXX_KURIER_PACZKI')) then
  begin
    execute statement 'CREATE TABLE XXX_KURIER_PACZKI (
    NRPRZESYLKI     T_STRING50 NOT NULL /* T_STRING50 = VARCHAR(50) */,
    ID_SPOSOBDOST   T_ID NOT NULL /* T_ID = INTEGER */,
    GUID            T_STRING100 /* T_STRING255 = VARCHAR(255) */,
    LP              SMALLINT,
    TYP_KOD         T_STRING50 /* T_STRING50 = VARCHAR(50) */,
    ILOSC           SMALLINT DEFAULT 1,
    NIESTANDARDOWA  T_BOOLEANN DEFAULT 0 /* T_BOOLEANN = SMALLINT DEFAULT 0 */,
    WAGA            T_ILOSCSTAN DEFAULT 0 /* T_ILOSCSTAN = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
    WYSOKOSC        T_ILOSCSTAN DEFAULT 0 /* T_ILOSCSTAN = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
    SZEROKOSC       T_ILOSCSTAN DEFAULT 0 /* T_ILOSCSTAN = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
    DLUGOSC         T_ILOSCSTAN DEFAULT 0 /* T_ILOSCSTAN = NUMERIC(18,4) DEFAULT 0 NOT NULL */,
    OBJETOSC        T_ILOSCSTAN_NULL DEFAULT 0 /* T_ILOSCSTAN_NULL = NUMERIC(18,4) */,
    ZABEZPIECZENIE  T_STRING255 /* T_STRING255 = VARCHAR(255) */,
    ZAWARTOSC       T_STRING255 /* T_STRING255 = VARCHAR(255) */,
    REFERENCJE      T_STRING255 /* T_STRING255 = VARCHAR(255) */,
    PARAMETR        T_STRING255 /* T_STRING255 = VARCHAR(255) */,
    ILOSC_ZWROT     SMALLINT DEFAULT 0 NOT NULL,
    DATA_NADANIA    TIMESTAMP DEFAULT current_timestamp,
    ID_NAGL         T_ID /* T_ID = INTEGER */,
    ID_RAPORT       T_ID /* T_ID = INTEGER */,
    KWOTA_POBRANIA  T_CENA DEFAULT 0 NOT NULL,

    CONSTRAINT FK_XXX_KURIER_PACZKI_2 FOREIGN KEY (ID_SPOSOBDOST) REFERENCES SPOSDOSTAWY (ID_SPOSDOSTAWY) ON DELETE SET NULL

)';
    execute statement 'CREATE INDEX XXX_KURIER_PACZKI_IDX1 ON XXX_KURIER_PACZKI (NRPRZESYLKI)';
    execute statement 'CREATE INDEX XXX_KURIER_PACZKI_IDX2 ON XXX_KURIER_PACZKI (NRPRZESYLKI, ID_SPOSOBDOST)';
    execute statement 'CREATE INDEX XXX_KURIER_PACZKI_IDX3 ON XXX_KURIER_PACZKI (ID_NAGL)';
    execute statement 'CREATE INDEX XXX_KURIER_PACZKI_IDX4 ON XXX_KURIER_PACZKI (DATA_NADANIA)';
  end
  else
  begin

    if (not exists(select 1
                   from RDB$RELATION_FIELDS RF
                   where RF.RDB$RELATION_NAME = 'XXX_KURIER_PACZKI' and
                         RF.RDB$FIELD_NAME = 'ILOSC_ZWROT')) then
      execute statement 'ALTER TABLE XXX_KURIER_PACZKI ADD ILOSC_ZWROT SMALLINT DEFAULT 0 NOT NULL';

    if (not exists(select 1
                   from RDB$RELATION_FIELDS RF
                   where RF.RDB$RELATION_NAME = 'XXX_KURIER_PACZKI' and
                         RF.RDB$FIELD_NAME = 'DATA_NADANIA')) then
      execute statement 'ALTER TABLE XXX_KURIER_PACZKI ADD DATA_NADANIA TIMESTAMP DEFAULT current_timestamp';

    if (not exists(select 1
                   from RDB$RELATION_FIELDS RF
                   where RF.RDB$RELATION_NAME = 'XXX_KURIER_PACZKI' and
                         RF.RDB$FIELD_NAME = 'ID_NAGL')) then
      execute statement 'ALTER TABLE XXX_KURIER_PACZKI ADD ID_NAGL T_ID';

    if (not exists(select 1
                   from RDB$RELATION_FIELDS RF
                   where RF.RDB$RELATION_NAME = 'XXX_KURIER_PACZKI' and
                         RF.RDB$FIELD_NAME = 'ID_RAPORT')) then
      execute statement 'ALTER TABLE XXX_KURIER_PACZKI ADD ID_RAPORT T_ID';

    if (not exists(select 1
                   from RDB$RELATION_FIELDS RF
                   where RF.RDB$RELATION_NAME = 'XXX_KURIER_PACZKI' and
                         RF.RDB$FIELD_NAME = 'KWOTA_POBRANIA')) then
      execute statement 'ALTER TABLE XXX_KURIER_PACZKI ADD KWOTA_POBRANIA T_CENA DEFAULT 0 NOT NULL';

    if (not exists(select 1
                   from RDB$INDICES
                   where RDB$INDEX_NAME = 'FK_XXX_KURIER_PACZKI_2')) then
      execute statement 'ALTER TABLE XXX_KURIER_PACZKI ADD CONSTRAINT FK_XXX_KURIER_PACZKI_2 FOREIGN KEY (ID_SPOSOBDOST) REFERENCES SPOSDOSTAWY (ID_SPOSDOSTAWY) ON DELETE SET NULL';


    if (not exists(select 1
                   from RDB$INDICES
                   where RDB$INDEX_NAME = 'XXX_KURIER_PACZKI_IDX3')) then
      execute statement 'CREATE INDEX XXX_KURIER_PACZKI_IDX3 ON XXX_KURIER_PACZKI (ID_NAGL)';
    if (not exists(select 1
                   from RDB$INDICES
                   where RDB$INDEX_NAME = 'XXX_KURIER_PACZKI_IDX4')) then
      execute statement 'CREATE INDEX XXX_KURIER_PACZKI_IDX4 ON XXX_KURIER_PACZKI (DATA_NADANIA)';

  end
end ^

execute block
as
begin
  if (not exists(select 1
                 from RDB$RELATIONS
                 where RDB$RELATION_NAME = 'XXX_RABEN_PRZESYLKI')) then
  begin
    execute statement 'CREATE TABLE XXX_RABEN_PRZESYLKI (
    NUMER                   VARCHAR(30) NOT NULL primary key,
    PLANOWANA_DATA_DOSTAWY  VARCHAR(20),
    DATA_NADANIA            TIMESTAMP,
    ADRES_ROZLADUNKU        VARCHAR(255),
    ILOSC_JEDN              INTEGER,
    WAGA                    T_STRING /* T_STRING = VARCHAR(255) */,
    MP                      INTEGER,
    M3                      NUMERIC(18,2),
    ROP                     VARCHAR(10),
    RODZAJ_TOWARU           VARCHAR(35),
    PROJEKT                 VARCHAR(35),
    SERWISY                 VARCHAR(100),
    NRWZ                    VARCHAR(255),
    REFERENCJE              VARCHAR(100),
    SENT                    VARCHAR(100),
    ZABEZPIECZENIE          VARCHAR(255),
    ID_NAGLS                VARCHAR(255),
    MSC_ZALADUNKU           VARCHAR(255))';

     execute statement 'CREATE INDEX XXX_RABEN_PRZESYLKI_IDX1 ON XXX_RABEN_PRZESYLKI (NUMER)';
  end
end ^

SET TERM ; ^