/******************************************************************************/
/*                                 Generators                                 */
/******************************************************************************/

CREATE GENERATOR GEN_CERTIFICADO_ID;
SET GENERATOR GEN_CERTIFICADO_ID TO 13;

CREATE GENERATOR GEN_DOWNLOADNOTA_ID;
SET GENERATOR GEN_DOWNLOADNOTA_ID TO 0;

CREATE GENERATOR GEN_EMITENTE_ID;
SET GENERATOR GEN_EMITENTE_ID TO 14;

CREATE GENERATOR GEN_LOTENFSE_ID;
SET GENERATOR GEN_LOTENFSE_ID TO 0;

CREATE GENERATOR GEN_LOTE_ID;
SET GENERATOR GEN_LOTE_ID TO 40;

CREATE GENERATOR GEN_NOTAFISCALSERVICO_ID;
SET GENERATOR GEN_NOTAFISCALSERVICO_ID TO 0;

CREATE GENERATOR GEN_NOTAFISCAL_ID;
SET GENERATOR GEN_NOTAFISCAL_ID TO 40;

CREATE GENERATOR GEN_WEBSERVICENFSE_ID;
SET GENERATOR GEN_WEBSERVICENFSE_ID TO 0;

CREATE GENERATOR GEN_WEBSERVICE_ID;
SET GENERATOR GEN_WEBSERVICE_ID TO 527;



/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/



CREATE TABLE CERTIFICADO (
    IDCERTIFICADO  INTEGER NOT NULL,
    VERSAO         INTEGER NOT NULL,
    NOME           VARCHAR(100) NOT NULL,
    IDEMITENTE     INTEGER,
    TIPOLOCAL      SMALLINT DEFAULT 0
);


CREATE TABLE DOWNLOADNOTA (
    IDDOWNLOADNOTA  INTEGER NOT NULL,
    DATAHORA        TIMESTAMP NOT NULL,
    IP              VARCHAR(50) NOT NULL,
    ENDERECOWEB     VARCHAR(100) NOT NULL,
    IDNOTAFISCAL    INTEGER NOT NULL,
    VERSAO          INTEGER NOT NULL
);


CREATE TABLE EMITENTE (
    IDEMITENTE          INTEGER NOT NULL,
    VERSAO              INTEGER NOT NULL,
    RAZAOSOCIAL         VARCHAR(100) NOT NULL,
    CNPJ                VARCHAR(14) NOT NULL,
    VERSAOSCHEMA        INTEGER NOT NULL,
    ESTADO              VARCHAR(255) NOT NULL,
    TIPOSISTEMA         SMALLINT,
    INSCRICAOMUNICIPAL  VARCHAR(25)
);


CREATE TABLE LOTE (
    IDLOTE          INTEGER NOT NULL,
    VERSAO          INTEGER NOT NULL,
    NUMERORECEBIDO  VARCHAR(50) NOT NULL,
    SEQUENCIA       INTEGER NOT NULL,
    STATUSLOTE      INTEGER NOT NULL,
    IDCERTIFICADO   INTEGER NOT NULL,
    IDEMITENTE      INTEGER NOT NULL,
    IDWEBSERVICE    INTEGER NOT NULL,
    XMLLOTEGERADO   BLOB SUB_TYPE 1 SEGMENT SIZE 80
);


CREATE TABLE LOTENFSE (
    IDLOTE         INTEGER NOT NULL,
    VERSAO         INTEGER NOT NULL,
    NUMERORECIBO   VARCHAR(50) NOT NULL,
    SEQUENCIA      INTEGER NOT NULL,
    STATUSLOTE     INTEGER NOT NULL,
    IDCERTIFICADO  INTEGER NOT NULL,
    IDEMITENTE     INTEGER NOT NULL,
    IDWEBSERVICE   INTEGER NOT NULL,
    XMLLOTEGERADO  BLOB SUB_TYPE 1 SEGMENT SIZE 80
);


CREATE TABLE NOTAFISCAL (
    IDNOTAFISCAL         INTEGER NOT NULL,
    VERSAO               INTEGER NOT NULL,
    CHAVEACESSO          VARCHAR(50) NOT NULL,
    CODIGONUMERICO       INTEGER NOT NULL,
    NUMERODOCUMENTO      INTEGER NOT NULL,
    SERIE                VARCHAR(3) NOT NULL,
    STATUSNOTA           INTEGER NOT NULL,
    IDLOTE               INTEGER,
    DOCDISTRIBUICAONOTA  BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    NOTAXML              BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    NUMEROPROTOCOLO      BIGINT,
    DOCCANCELAMENTONOTA  BLOB SUB_TYPE 1 SEGMENT SIZE 80
);


CREATE TABLE NOTAFISCALSERVICO (
    IDNOTAFISCALSERVICO  INTEGER NOT NULL,
    VERSAO               INTEGER NOT NULL,
    NUMERORPS            INTEGER NOT NULL,
    NUMERONOTA           INTEGER NOT NULL,
    SERIE                VARCHAR(3) NOT NULL,
    STATUSNOTA           INTEGER NOT NULL,
    IDLOTE               INTEGER,
    NOTAXMLAUTORIZADO    BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    NOTAXML              BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    NUMEROPROTOCOLO      BIGINT
);


CREATE TABLE WEBSERVICE (
    IDWEBSERVICE    INTEGER NOT NULL,
    VERSAO          INTEGER NOT NULL,
    ESTADO          VARCHAR(2) NOT NULL,
    URL             VARCHAR(200) NOT NULL,
    TIPOAMBIENTE    INTEGER NOT NULL,
    TIPOWEBSERVICE  INTEGER NOT NULL
);


CREATE TABLE WEBSERVICENFSE (
    IDWEBSERVICE    INTEGER NOT NULL,
    VERSAO          INTEGER NOT NULL,
    TIPOSISTEMA     SMALLINT NOT NULL,
    URL             VARCHAR(200) NOT NULL,
    TIPOAMBIENTE    INTEGER NOT NULL,
    TIPOWEBSERVICE  INTEGER NOT NULL
);




/******************************************************************************/
/*                                Primary Keys                                */
/******************************************************************************/

ALTER TABLE CERTIFICADO ADD CONSTRAINT PK_CERTIFICADO PRIMARY KEY (IDCERTIFICADO);
ALTER TABLE DOWNLOADNOTA ADD CONSTRAINT PK_DOWNLOADNOTA PRIMARY KEY (IDDOWNLOADNOTA);
ALTER TABLE EMITENTE ADD CONSTRAINT PK_EMITENTE PRIMARY KEY (IDEMITENTE);
ALTER TABLE LOTE ADD CONSTRAINT PK_LOTE PRIMARY KEY (IDLOTE);
ALTER TABLE LOTENFSE ADD CONSTRAINT PK_LOTENFSE PRIMARY KEY (IDLOTE);
ALTER TABLE NOTAFISCAL ADD CONSTRAINT PK_NOTAFISCAL PRIMARY KEY (IDNOTAFISCAL);
ALTER TABLE NOTAFISCALSERVICO ADD CONSTRAINT PK_NOTAFISCALSERVICO PRIMARY KEY (IDNOTAFISCALSERVICO);
ALTER TABLE WEBSERVICE ADD CONSTRAINT PK_WEBSERVICE PRIMARY KEY (IDWEBSERVICE);
ALTER TABLE WEBSERVICENFSE ADD CONSTRAINT PK_WEBSERVICENFSE PRIMARY KEY (IDWEBSERVICE);


/******************************************************************************/
/*                                  Triggers                                  */
/******************************************************************************/


SET TERM ^ ;



/******************************************************************************/
/*                            Triggers for tables                             */
/******************************************************************************/



/* Trigger: CERTIFICADO_BI */
CREATE TRIGGER CERTIFICADO_BI FOR CERTIFICADO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.IDCERTIFICADO IS NULL) THEN
    NEW.IDCERTIFICADO = GEN_ID(GEN_CERTIFICADO_ID,1);
END
^


/* Trigger: DOWNLOADNOTA_BI */
CREATE TRIGGER DOWNLOADNOTA_BI FOR DOWNLOADNOTA
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.IDDOWNLOADNOTA IS NULL) THEN
    NEW.IDDOWNLOADNOTA = GEN_ID(GEN_DOWNLOADNOTA_ID,1);
END
^


/* Trigger: EMITENTE_BI */
CREATE TRIGGER EMITENTE_BI FOR EMITENTE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.IDEMITENTE IS NULL) THEN
    NEW.IDEMITENTE = GEN_ID(GEN_EMITENTE_ID,1);
END
^


/* Trigger: LOTENFSE_BI */
CREATE TRIGGER LOTENFSE_BI FOR LOTENFSE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.IDLOTE IS NULL) THEN
    NEW.IDLOTE = GEN_ID(GEN_LOTENFSE_ID,1);
END
^


/* Trigger: LOTE_BI */
CREATE TRIGGER LOTE_BI FOR LOTE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.IDLOTE IS NULL) THEN
    NEW.IDLOTE = GEN_ID(GEN_LOTE_ID,1);
END
^


/* Trigger: NOTAFISCALSERVICO_BI */
CREATE TRIGGER NOTAFISCALSERVICO_BI FOR NOTAFISCALSERVICO
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.IDNOTAFISCALSERVICO IS NULL) THEN
    NEW.IDNOTAFISCALSERVICO = GEN_ID(GEN_NOTAFISCALSERVICO_ID,1);
END
^


/* Trigger: NOTAFISCAL_BI */
CREATE TRIGGER NOTAFISCAL_BI FOR NOTAFISCAL
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.IDNOTAFISCAL IS NULL) THEN
    NEW.IDNOTAFISCAL = GEN_ID(GEN_NOTAFISCAL_ID,1);
END
^


/* Trigger: WEBSERVICENFSE_BI */
CREATE TRIGGER WEBSERVICENFSE_BI FOR WEBSERVICENFSE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.IDWEBSERVICE IS NULL) THEN
    NEW.IDWEBSERVICE = GEN_ID(GEN_WEBSERVICENFSE_ID,1);
END
^


/* Trigger: WEBSERVICE_BI */
CREATE TRIGGER WEBSERVICE_BI FOR WEBSERVICE
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
  IF (NEW.IDWEBSERVICE IS NULL) THEN
    NEW.IDWEBSERVICE = GEN_ID(GEN_WEBSERVICE_ID,1);
END
^


SET TERM ; ^


