/*
Script de criao do banco da DES 3.0 
*/
/*************************************************************************************************************************************
*/
/*********************************************************************************************************
Situacao especial
*/
CREATE TABLE situ_especial (
  idn_situ_esp INT NOT NULL IDENTITY,
  desc_situ_esp VARCHAR(60) NOT NULL,/*nome da situacao especial*/
  CONSTRAINT idx_pk_situ_espe PRIMARY KEY(idn_situ_esp),
  CONSTRAINT idx_uk_situ_espe UNIQUE(desc_situ_esp)
)
;
/*********************************************************************************************************
grupo de atividade
*/
CREATE TABLE grupo_atividade (
  idn_grup_atvd INT NOT NULL IDENTITY,
  desc_grup_atvd VARCHAR(50) NOT NULL,
  CONSTRAINT idx_pk_grup_atvd PRIMARY KEY(idn_grup_atvd)
)
;
/*********************************************************************************************************
Natureza da nota fiscal
*/
CREATE TABLE natureza_operacao (
  idn_natu_oper INT NOT NULL IDENTITY,
  desc_natu_oper VARCHAR(50) NOT NULL,/*natureza da operacao*/
  CONSTRAINT idx_pk_natu_oper PRIMARY KEY(idn_natu_oper),
  CONSTRAINT idx_uk_natu_oper UNIQUE(desc_natu_oper)
)
;
/*********************************************************************************************************
serie da nota fiscal
*/
CREATE TABLE serie (
  idn_serie INT NOT NULL IDENTITY,
  ordem_exib INT,
  desc_serie VARCHAR(60) NOT NULL,
  CONSTRAINT idx_pk_seri_nfe PRIMARY KEY(idn_serie),
  CONSTRAINT idx_uk_seri_nfe UNIQUE(desc_serie)
)
;
/*********************************************************************************************************
modelo da nota fiscal
*/
CREATE TABLE modelo (
  idn_modelo INT NOT NULL IDENTITY,
  ordem_exib INT,
  desc_modelo VARCHAR(60) NOT NULL,
  ind_numero BOOLEAN DEFAULT true NOT NULL,
  ind_evento BOOLEAN DEFAULT false NOT NULL,
  ind_visu SMALLINT DEFAULT 1 NOT NULL ,
  ind_des SMALLINT DEFAULT 1 NOT NULL,
  ind_pres_bh SMALLINT DEFAULT 1 NOT NULL,
  ind_pres_fora SMALLINT DEFAULT 1 NOT NULL,
  CONSTRAINT ck_visu_nfe CHECK(ind_visu = 1/*Emitida*/ OR ind_visu = 2/*Recebida*/ OR ind_visu = 3/*Ambos*/ OR ind_visu = 4 /*Cancelamento*/),
  CONSTRAINT ck_des_nfe CHECK(ind_des = 1/*Des completa*/ OR ind_des = 2/*nao pode des simplificada*/),
  CONSTRAINT ck_pres_bh_nfe CHECK(ind_pres_bh = 1/*todos s/ im*/ OR ind_pres_bh = 2/*apenas cpj s/im*/ OR   ind_pres_bh = 3/*apenas sem cnpj e s/ im*/OR ind_pres_bh = 4/*nenhum*/),
  CONSTRAINT ck_pres_fora_nfe CHECK(ind_pres_fora = 1/*todos*/ OR ind_pres_fora = 2/*apenas cpj*/ OR ind_pres_fora = 3/*apenas sem cnpj*/OR ind_pres_fora = 4/*nenhum*/),
  CONSTRAINT idx_pk_mode_nfe PRIMARY KEY(idn_modelo),
  CONSTRAINT idx_uk_mode_nfe UNIQUE(desc_modelo)
)
;
/*********************************************************************************************************
serie para tipo de documentos da nota fiscal
*/
CREATE TABLE cor_modelo_serie (
  idn_modelo_serie INT NOT NULL IDENTITY,
  idn_modelo INT NOT NULL,
  idn_serie INT NOT NULL,
  ind_sub_serie BOOLEAN DEFAULT false NOT NULL,
  ind_reco BOOLEAN DEFAULT false NOT NULL,
  ind_lote BOOLEAN DEFAULT false NOT NULL,
  CONSTRAINT fk_mode_seri_seri_nfe FOREIGN KEY(idn_serie) REFERENCES serie(idn_serie),
  CONSTRAINT fk_mode_seri_mode_nfe FOREIGN KEY(idn_modelo) REFERENCES modelo(idn_modelo)
)
;
/*********************************************************************************************************
Tipo de negócio
*/
CREATE TABLE tipo_negocio (
   idn_tipo_negc INT NOT NULL IDENTITY,
   desc_tipo_negc VARCHAR(80) NOT NULL,
   CONSTRAINT idx_pk_tipo_negc PRIMARY KEY(idn_tipo_negc),
   CONSTRAINT idx_uk_tipo_negc UNIQUE(desc_tipo_negc)
);
/*********************************************************************************************************
Regra especial de tributacao
*/
CREATE TABLE regime_especial_tributacao(
   idn_regi_espe_trbt INT NOT NULL IDENTITY,
   desc_regi_espe_trbt VARCHAR(60) NOT NULL,
   CONSTRAINT idx_pk_regi_espe_trbt PRIMARY KEY(idn_regi_espe_trbt),
   CONSTRAINT idx_uk_regi_espe_trbt UNIQUE(desc_regi_espe_trbt)
);
/*********************************************************************************************************
*/
CREATE TABLE cor_situ_modelo(
   idn_situ_modelo INT NOT NULL,
   idn_situ_esp INT NOT NULL,   
   idn_modelo INT NOT NULL,
   CONSTRAINT idx_pk_situ_mode PRIMARY KEY(idn_situ_modelo),
   CONSTRAINT fk_situ_mode_mode FOREIGN KEY(idn_modelo) REFERENCES modelo(idn_modelo),
   CONSTRAINT fk_situ_mode_situ FOREIGN KEY( idn_situ_esp) REFERENCES situ_especial( idn_situ_esp) 
);
/*********************************************************************************************************
*/
CREATE TABLE cor_situ_negocio(
   idn_situ_negocio INT NOT NULL,
   idn_situ_esp INT NOT NULL,
   idn_tipo_negc INT NOT NULL,
   CONSTRAINT idx_pk_situ_nego PRIMARY KEY(idn_situ_negocio),
   CONSTRAINT fk_situ_nego_negc FOREIGN KEY(idn_tipo_negc) REFERENCES tipo_negocio(idn_tipo_negc),
   CONSTRAINT fk_situ_nego_situ FOREIGN KEY( idn_situ_esp) REFERENCES situ_especial( idn_situ_esp) 
);
/*********************************************************************************************************
*/
CREATE TABLE cor_situ_natureza(
   idn_situ_natureza INT NOT NULL,
   idn_situ_esp INT NOT NULL,
   idn_natu_oper INT NOT NULL,
   CONSTRAINT idx_pk_situ_natu PRIMARY KEY(idn_situ_natureza),
   CONSTRAINT fk_situ_natu_natu FOREIGN KEY(idn_natu_oper) REFERENCES natureza_operacao(idn_natu_oper),   
   CONSTRAINT fk_situ_natu_situ FOREIGN KEY( idn_situ_esp) REFERENCES situ_especial( idn_situ_esp) 
);
/*********************************************************************************************************
*/
CREATE TABLE cor_situ_regime(
   idn_situ_regime INT NOT NULL,
   idn_situ_esp INT NOT NULL,
   idn_regi_espe_trbt INT NOT NULL,
   CONSTRAINT idx_pk_situ_regi PRIMARY KEY(idn_situ_regime),
   CONSTRAINT fk_situ_regi_regi FOREIGN KEY(idn_regi_espe_trbt) REFERENCES regime_especial_tributacao(idn_regi_espe_trbt),   
   CONSTRAINT fk_situ_regi_situ FOREIGN KEY( idn_situ_esp) REFERENCES situ_especial( idn_situ_esp) 
);


/*********************************************************************************************************
*/
CREATE TABLE cor_atividade_modelo(
   idn_atividade_modelo INT NOT NULL,
   idn_modelo INT NOT NULL,
   idn_grup_atvd INT NOT NULL,
   CONSTRAINT idx_pk_ativ_mode PRIMARY KEY(idn_atividade_modelo),
   CONSTRAINT fk_ativ_mode_mode FOREIGN KEY(idn_modelo) REFERENCES modelo(idn_modelo),
   CONSTRAINT fk_ativ_mode_ativ FOREIGN KEY( idn_grup_atvd) REFERENCES grupo_atividade( idn_grup_atvd) 
);
/*********************************************************************************************************
*/
CREATE TABLE cor_atividade_negocio(
   idn_atividade_negocio INT NOT NULL,
   idn_grup_atvd INT NOT NULL,
   idn_tipo_negc INT NOT NULL,
   CONSTRAINT idx_pk_ativ_nego PRIMARY KEY(idn_atividade_negocio),
   CONSTRAINT fk_ativ_nego_negc FOREIGN KEY(idn_tipo_negc) REFERENCES tipo_negocio(idn_tipo_negc),
   CONSTRAINT fk_ativ_nego_ativ FOREIGN KEY( idn_grup_atvd) REFERENCES grupo_atividade( idn_grup_atvd) 
);
/*********************************************************************************************************
*/
CREATE TABLE cor_atividade_natureza(
   idn_atividade_natureza INT NOT NULL,
   idn_grup_atvd INT NOT NULL,
   idn_natu_oper INT NOT NULL,
   CONSTRAINT idx_pk_ativ_natu PRIMARY KEY(idn_atividade_natureza),
   CONSTRAINT fk_ativ_natu_natu FOREIGN KEY(idn_natu_oper) REFERENCES natureza_operacao(idn_natu_oper),   
   CONSTRAINT fk_ativ_natu_ativ FOREIGN KEY( idn_grup_atvd) REFERENCES grupo_atividade( idn_grup_atvd)  
);
/*********************************************************************************************************
*/
CREATE TABLE cor_atividade_regime(
   idn_atividade_regime INT NOT NULL,
   idn_grup_atvd INT NOT NULL,
   idn_regi_espe_trbt INT NOT NULL,
   CONSTRAINT idx_pk_ativ_regi PRIMARY KEY(idn_atividade_regime),
   CONSTRAINT fk_ativ_regi_regi FOREIGN KEY(idn_regi_espe_trbt) REFERENCES regime_especial_tributacao(idn_regi_espe_trbt),   
   CONSTRAINT fk_ativ_regi_ativ FOREIGN KEY( idn_grup_atvd) REFERENCES grupo_atividade( idn_grup_atvd)
);
/*********************************************************************************************************
*/
CREATE TABLE cor_modelo_negocio(
   idn_modelo_negocio INT NOT NULL,
   idn_modelo INT NOT NULL,
   idn_tipo_negc INT NOT NULL,
   CONSTRAINT idx_pk_mode_nego PRIMARY KEY(idn_modelo_negocio),
   CONSTRAINT fk_mode_nego_negc FOREIGN KEY(idn_tipo_negc) REFERENCES tipo_negocio(idn_tipo_negc),
   CONSTRAINT fk_mode_nego_mode_nfe FOREIGN KEY(idn_modelo) REFERENCES modelo(idn_modelo)
);
/*********************************************************************************************************
*/
CREATE TABLE cor_modelo_natureza(
   idn_modelo_natureza INT NOT NULL,
   idn_modelo INT NOT NULL,
   idn_natu_oper INT NOT NULL,
   CONSTRAINT idx_pk_mode_natu PRIMARY KEY(idn_modelo_natureza),
   CONSTRAINT fk_mode_natu_natu FOREIGN KEY(idn_natu_oper) REFERENCES natureza_operacao(idn_natu_oper),   
   CONSTRAINT fk_mode_natu_mode_nfe FOREIGN KEY(idn_modelo) REFERENCES modelo(idn_modelo)
);
/*********************************************************************************************************
*/
CREATE TABLE cor_modelo_regime(
   idn_modelo_regime INT NOT NULL,
   idn_modelo INT NOT NULL,
   idn_regi_espe_trbt INT NOT NULL,
   CONSTRAINT idx_pk_mode_regi PRIMARY KEY(idn_modelo_regime),
   CONSTRAINT fk_mode_regi_regi FOREIGN KEY(idn_regi_espe_trbt) REFERENCES regime_especial_tributacao(idn_regi_espe_trbt),   
   CONSTRAINT fk_mode_regi_mode_nfe FOREIGN KEY(idn_modelo) REFERENCES modelo(idn_modelo));



/*********************************************************************************************************
Junção Natureza de Operacao com tipo de negocio
*/
CREATE TABLE cor_negocio_natureza_regime (
   idn_negocio_natureza INT NOT NULL,
   idn_tipo_negc INT NOT NULL,
   idn_natu_oper INT NOT NULL,
   idn_regi_espe_trbt INT NOT NULL,
   CONSTRAINT idx_pk_negc_natu PRIMARY KEY(idn_negocio_natureza),
   CONSTRAINT fk_negc_natu_negc FOREIGN KEY(idn_tipo_negc) REFERENCES tipo_negocio(idn_tipo_negc),
   CONSTRAINT fk_negc_natu_natu FOREIGN KEY(idn_natu_oper) REFERENCES natureza_operacao(idn_natu_oper),
   CONSTRAINT fk_negc_natu_regi FOREIGN KEY(idn_regi_espe_trbt) REFERENCES regime_especial_tributacao(idn_regi_espe_trbt)   
);
/*********************************************************************************************************
*/
/*CREATE TABLE cor_negocio_regime(
   idn_negocio_regime INT NOT NULL,
   idn_regi_espe_trbt INT NOT NULL,
   idn_tipo_negc INT NOT NULL,
   CONSTRAINT idx_pk_nego_regi PRIMARY KEY(idn_negocio_regime),
   CONSTRAINT fk_nego_regi_regi FOREIGN KEY(idn_regi_espe_trbt) REFERENCES regime_especial_tributacao(idn_regi_espe_trbt),   
   CONSTRAINT fk_nego_regi_negc FOREIGN KEY(idn_tipo_negc) REFERENCES tipo_negocio(idn_tipo_negc)
);
/*********************************************************************************************************/
/*                                    ***  SERCIO TOMADO  ***                                            */ 
/*********************************************************************************************************/
/*********************************************************************************************************
Regra especial de responsabilidade
*/
CREATE TABLE situ_especial_respons(
   idn_situ_resp INT NOT NULL IDENTITY,
   desc_situ_resp VARCHAR(60) NOT NULL,
   CONSTRAINT idx_pk_situ_espe_resp PRIMARY KEY(idn_situ_resp),
   CONSTRAINT idx_uk_situ_espe_resp UNIQUE(desc_situ_resp)
);
/*********************************************************************************************************
motivo nao retencao
*/
CREATE TABLE motivo_nao_retencao (
  idn_motv_nao_retc INT NOT NULL IDENTITY,
  desc_motv_nao_retc VARCHAR(60) NOT NULL,
  CONSTRAINT idx_pk_motv_nao_retc PRIMARY KEY(idn_motv_nao_retc),
  CONSTRAINT idx_uk_motv_nao_retc UNIQUE(desc_motv_nao_retc)
);
/*********************************************************************************************************
*/
CREATE TABLE cor_modelo_respons(
   idn_modelo_respons INT NOT NULL,
   idn_modelo INT NOT NULL,
   idn_situ_resp INT NOT NULL,
   CONSTRAINT idx_pk_mode_resp PRIMARY KEY(idn_modelo_respons),
   CONSTRAINT fk_mode_resp_resp FOREIGN KEY(idn_situ_resp) REFERENCES situ_especial_respons(idn_situ_resp),
   CONSTRAINT fk_mode_resp_mode_nfe FOREIGN KEY(idn_modelo) REFERENCES modelo(idn_modelo)
);
/*********************************************************************************************************
*/
CREATE TABLE cor_modelo_nao_retencao(
   idn_modelo_nao_retencao INT NOT NULL,
   idn_modelo INT NOT NULL,
   idn_motv_nao_retc INT NOT NULL,
   CONSTRAINT idx_pk_mode_reten PRIMARY KEY(idn_modelo_nao_retencao),
   CONSTRAINT fk_mode_reten_resp FOREIGN KEY(idn_motv_nao_retc) REFERENCES motivo_nao_retencao(idn_motv_nao_retc),
   CONSTRAINT fk_mode_reten_mode_nfe FOREIGN KEY(idn_modelo) REFERENCES modelo(idn_modelo)
);
/*********************************************************************************************************
*/
CREATE TABLE cor_respons_nao_retencao(
   idn_respons_nao_retencao INT NOT NULL,
   idn_situ_resp INT NOT NULL,
   idn_motv_nao_retc INT NOT NULL,
   CONSTRAINT idx_pk_resp_reten PRIMARY KEY(idn_respons_nao_retencao),
   CONSTRAINT fk_resp_reten_resp FOREIGN KEY(idn_motv_nao_retc) REFERENCES motivo_nao_retencao(idn_motv_nao_retc),
   CONSTRAINT fk_resp_reten_mode_nfe FOREIGN KEY(idn_situ_resp) REFERENCES situ_especial_respons(idn_situ_resp)
);
/*********************************************************************************************************
Situacao especial
*/
CREATE TABLE situ_especial_tomador (
  idn_situ_toma INT NOT NULL IDENTITY,
  desc_situ_toma VARCHAR(60) NOT NULL,/*nome da situacao especial*/
  CONSTRAINT idx_pk_situ_espe_toma PRIMARY KEY(idn_situ_toma),
  CONSTRAINT idx_uk_situ_espe_toma UNIQUE(desc_situ_toma)
)
;
CREATE TABLE cor_situ_toma_respons(
   idn_situ_toma_respons INT NOT NULL,
   idn_situ_toma INT NOT NULL,
   idn_situ_resp INT NOT NULL,
   CONSTRAINT idx_pk_situ_resp PRIMARY KEY(idn_situ_toma_respons),
   CONSTRAINT fk_situ_resp_mode FOREIGN KEY(idn_situ_resp) REFERENCES situ_especial_respons(idn_situ_resp),
   CONSTRAINT fk_situ_resp_situ FOREIGN KEY(idn_situ_toma) REFERENCES situ_especial_tomador(idn_situ_toma) 
);
/*********************************************************************************************************
*/
CREATE TABLE cor_situ_toma_nao_retencao(
   idn_situ_toma_nao_retencao INT NOT NULL,
   idn_situ_toma INT NOT NULL,
   idn_motv_nao_retc INT NOT NULL,
   CONSTRAINT idx_pk_situ_reten PRIMARY KEY(idn_situ_toma_nao_retencao),
   CONSTRAINT fk_situ_reten_negc FOREIGN KEY(idn_motv_nao_retc) REFERENCES motivo_nao_retencao(idn_motv_nao_retc),
   CONSTRAINT fk_situ_reten_situ FOREIGN KEY(idn_situ_toma) REFERENCES situ_especial_tomador(idn_situ_toma) 
);
/*************************************************************************************************************************************
*/
CREATE TABLE cidade(
  idn_cidade INT NOT NULL,/*codigo bancao*/
  idn_uf CHAR(2) NOT NULL,
  desc_cidade VARCHAR(60) NOT NULL,/*nome da cidade*/
  CONSTRAINT ck_uf CHECK (idn_uf in ('MG', 'SP', 'RJ', 'AC', 'AL', 'AP', 'AM', 'BA', 'CE', 'DF', 'ES', 'GO', 'MA', 'MS', 'MT', 'PA', 'PB', 'PE', 'PI', 'PR', 'RN', 'RO', 'RR', 'RS', 'SC','SE','TO','EX')),/*unidade federativa*/
  CONSTRAINT idx_pk_cida PRIMARY KEY(idn_cidade),/*primaria codigo do ibge*/
  CONSTRAINT idx_uk_cida_uf UNIQUE(idn_uf,desc_cidade)/*nao pode existir mais de uma cidade para o mesmo estado*/
)
;
CREATE INDEX idx_cod_ibge ON cidade(idn_cidade);
/*********************************************************************************************************
Bairro
*/
CREATE TABLE bairro (
  idn_bair INT NOT NULL IDENTITY,/*codigo do bairro*/
  desc_bair VARCHAR(60) NOT NULL,/*nome do bairro*/
  CONSTRAINT idx_pk_bair PRIMARY KEY(idn_bair)
)
;
CREATE INDEX idx_nom_bair_cida ON bairro(desc_bair);
/*********************************************************************************************************
Logradouro
*/
CREATE TABLE logradouro (
  idn_logr INT NOT NULL IDENTITY,/*codigo do logradouro*/
  desc_logr VARCHAR(125) NOT NULL,/*nome do logradouro*/
  CONSTRAINT idx_pk_logr PRIMARY KEY(idn_logr)
)
;
CREATE INDEX idx_logr_cida ON logradouro(desc_logr);
/*********************************************************************************************************
faixa cep por unidade federativa
*/
CREATE TABLE faixa_cep_uf (
  cep_inic INT NOT NULL,/*numero do cep inicial*/
  cep_fina INT NOT NULL,/*numero do cep final*/
  idn_uf CHAR(2) NOT NULL,/*unidade federativa*/
  CONSTRAINT idx_pk_faix_cpuf_cpin PRIMARY KEY(idn_uf, cep_inic),
);
CREATE TABLE CEP_LOGRA_BAIRRO(
    IDN_CEP INTEGER NOT NULL,
    IDN_LOGR INTEGER NOT NULL,IDN_BAIR INTEGER NOT NULL,
    CONSTRAINT IDX_PK_CEP PRIMARY KEY(IDN_CEP),
    CONSTRAINT FK_CEP_LOGRA FOREIGN KEY(IDN_LOGR) REFERENCES LOGRADOURO(IDN_LOGR),
    CONSTRAINT FK_CEP_BAIRRO FOREIGN KEY(IDN_BAIR) REFERENCES BAIRRO(IDN_BAIR)
);
/*********************************************************************************************************
pessoa do persona
*/
CREATE TABLE empresa(
  idn_empr BIGINT NOT NULL IDENTITY,/*Id do Bancao*/
  insc_muni VARCHAR(11) DEFAULT '' NOT NULL,/*inscricao municipal obrigatória para empresa de Belo horizonte ibge = 310620*/
  cnpj VARCHAR(14), /*documento da pessoa CNPJ*/
  cpf VARCHAR(11),
  data_inclu TIMESTAMP NOT NULL,
  nome_empr VARCHAR(150) NOT NULL,/*nome da pessoa*/
  idn_situ_esp INT NOT NULL,/*situacao especial da empresa*/
  tip_situ_pess VARCHAR(1) NOT NULL,
  ind_scdd_libl SMALLINT NOT NULL,
  ind_simp_naci SMALLINT DEFAULT 2 NOT NULL,
  ind_ince_cult SMALLINT DEFAULT 2 NOT NULL,
  ind_subs_tribu SMALLINT DEFAULT 2 NOT NULL,
  ind_auth_nfse SMALLINT DEFAULT 2 NOT NULL,
  ind_imune SMALLINT DEFAULT 2 NOT NULL,
  nome_fant VARCHAR(80),/*nome fantasia*/
  data_atu TIMESTAMP,
  CONSTRAINT idx_pk_empr PRIMARY KEY(idn_empr),
  CONSTRAINT fk_empr_sies FOREIGN KEY(idn_situ_esp) REFERENCES situ_especial(idn_situ_esp),
  CONSTRAINT idx_uk_im_cnpj UNIQUE(insc_muni, cnpj)
);

CREATE INDEX idx_cnpj ON empresa(cnpj);
CREATE INDEX idx_insc_muni ON empresa(insc_muni);
CREATE INDEX idx_nome_empr ON empresa(nome_empr);


/*********************************************************************************************************
enderecos fora do getm - empresa de fora e pessoas cadastradas para o sistema
*/
CREATE TABLE endereco (
  idn_ender BIGINT NOT NULL IDENTITY,/*id do endereco*/
  data_incl TIMESTAMP NOT NULL,/*data de inclusao do endereco*/
  bairro VARCHAR(60),/*codigo do bairro*/
  lograd VARCHAR(125) NOT NULL,/*descrio do logradouro*/
  numero INT,/*numero do imovel*/
  cep VARCHAR(8),/*numero do cep*/
  complemento VARCHAR(60),/*descricao do complemento do imovel*/
  idn_cidade INT,
  cod_pais INT,
  CONSTRAINT ck_cep_ende_extr CHECK(length(CAST(cep as VARCHAR(8))) <= 8 OR cep is null),
  CONSTRAINT idx_pk_ende_extr PRIMARY KEY(idn_ender),
  CONSTRAINT fk_ende_cida FOREIGN KEY(idn_cidade) REFERENCES cidade(idn_cidade)
)
;
/*********************************************************************************************************
profissao 
*/
CREATE TABLE profissao (
  idn_profissao INT NOT NULL IDENTITY,
  desc_profissao VARCHAR(50) NOT NULL,/*nome da profissao*/
  CONSTRAINT idx_pk_prof PRIMARY KEY(idn_profissao),
  CONSTRAINT idx_uk_prof UNIQUE(desc_profissao)
);
/*********************************************************************************************************
declarante
*/
CREATE TABLE declarante(
  idn_declarante INT NOT NULL IDENTITY,
  insc_muni VARCHAR(11) NOT NULL,
  cnpj VARCHAR(14),/*documento da pessoa CNPJ*/
  cpf VARCHAR(11),
  data_incl TIMESTAMP NOT NULL,
  nome_declarante VARCHAR(80) NOT NULL,/*nome da pessoa*/
  idn_situ_esp INT NOT NULL,
  ind_scdd_libl SMALLINT DEFAULT 2 NOT NULL,
  ind_simp_naci SMALLINT DEFAULT 2 NOT NULL,
  ind_ince_cult SMALLINT DEFAULT 2 NOT NULL,
  ind_subs_tribu SMALLINT DEFAULT 2 NOT NULL,
  ind_auth_nfse SMALLINT DEFAULT 2 NOT NULL,
  email VARCHAR(80),
  telefone VARCHAR(13),
  opc_centr BOOLEAN,
  opc_anual BOOLEAN,
  idn_empr BIGINT NOT NULL,
  CONSTRAINT ck_scdd_libl_empr_dcr CHECK(ind_scdd_libl = 1/*Sim*/ OR ind_scdd_libl = 2/*Não*/),
  CONSTRAINT ck_ince_cult_empr_dcr CHECK(ind_ince_cult = 1/*Sim*/ OR ind_ince_cult = 2/*Nao*/),
  CONSTRAINT ck_subs_tribu_empr_dcr CHECK(ind_subs_tribu = 1 OR ind_subs_tribu = 2 OR ind_subs_tribu = 3),
  CONSTRAINT idx_pk_empr_dcr PRIMARY KEY(idn_declarante),
  CONSTRAINT idx_uk_empr_dcr UNIQUE(cnpj,insc_muni,cpf),
  CONSTRAINT fk_empr_dcr_sies FOREIGN KEY(idn_situ_esp) REFERENCES situ_especial(idn_situ_esp),
  CONSTRAINT fk_empr FOREIGN KEY(idn_empr) REFERENCES empresa(idn_empr)
);
/*********************************************************************************************************
profissional liberais
*/
CREATE TABLE profissional_liberal (
  idn_prof_libe INT NOT NULL IDENTITY,
  cpf VARCHAR(11) NOT NULL,
  nome_prof_libe VARCHAR(50) NOT NULL,
  idn_profissao INT NOT NULL,
  idn_declarante INT NOT NULL,
  desc_outr_habi VARCHAR(50),
  reg_prof VARCHAR(30) NOT NULL,
  data_incl TIMESTAMP NOT NULL,
  data_excl TIMESTAMP,
  CONSTRAINT idx_pk_prof_libe PRIMARY KEY(idn_prof_libe),
  CONSTRAINT ck_cpf_prof_libe CHECK(length(cast(cpf as VARCHAR(11))) <= 11),
  CONSTRAINT fk_prof_libe_prof FOREIGN KEY(idn_profissao) REFERENCES profissao(idn_profissao),
  CONSTRAINT fk_prof_libe_dcrd FOREIGN KEY(idn_declarante) REFERENCES declarante(idn_declarante)
)
;
/*********************************************************************************************************
pessoa do persona
*/
CREATE TABLE cliente_fornecedor(
  idn_cliente BIGINT NOT NULL IDENTITY,
  insc_muni VARCHAR(11),
  cnpj VARCHAR(14),
  cpf VARCHAR(11),
  idn_ender BIGINT NOT NULL,
  data_incl TIMESTAMP NOT NULL,
  nome_cliente VARCHAR(150) NOT NULL,/*nome da pessoa*/
  ind_simp_naci SMALLINT DEFAULT 1 NOT NULL,
  email VARCHAR(80),
  telefone VARCHAR(13),
  idn_empr BIGINT,
  CONSTRAINT idx_pk_clie_forn PRIMARY KEY(idn_cliente),
  CONSTRAINT idx_uk_clie_forn UNIQUE(insc_muni,cnpj,cpf),
  CONSTRAINT fk_clie_forn_ende FOREIGN KEY(idn_ender) REFERENCES endereco(idn_ender),
  CONSTRAINT fk_clie_empr FOREIGN KEY(idn_empr) REFERENCES empresa(idn_empr)
);



/*********************************************************************************************************
Questionario
*/
CREATE TABLE questionario (
  idn_ques INT NOT NULL IDENTITY,
  desc_ques VARCHAR(255) NOT NULL,
  idn_ques_sim INT,
  idn_ques_nao INT,
  CONSTRAINT idx_pk_ques PRIMARY KEY(idn_ques),
  CONSTRAINT idx_fk_ques_sim FOREIGN KEY(idn_ques_sim) REFERENCES questionario(idn_ques),
  CONSTRAINT idx_fk_ques_nao FOREIGN KEY(idn_ques_nao) REFERENCES questionario(idn_ques)
);
/*********************************************************************************************************
questionario para pessoa declarada
*/
CREATE TABLE questionario_empresa (
  idn_ques_empr INT NOT NULL IDENTITY,
  idn_declarante INT NOT NULL,
  idn_ques INT NOT NULL,
  data_ques TIMESTAMP NOT NULL,
  opc_duvi_resp BOOLEAN DEFAULT false NOT NULL,
  opc_resp_fina BOOLEAN NOT NULL,
  CONSTRAINT idx_pk_ques_empr PRIMARY KEY(idn_ques_empr),
  CONSTRAINT idx_fk_ques_empr_ques FOREIGN KEY(idn_ques) REFERENCES questionario(idn_ques),
  CONSTRAINT idx_fk_ques_empr_empr_dcrd FOREIGN KEY(idn_declarante) REFERENCES declarante(idn_declarante)
);
/*********************************************************************************************************
Sem movimentacao
*/
CREATE TABLE sem_movimentacao (
  idn_sem_movi INT NOT NULL IDENTITY,
  idn_declarante INT NOT NULL,
  data_refe TIMESTAMP NOT NULL,
  tip_movi SMALLINT NOT NULL,
  CONSTRAINT ck_tipo_movi CHECK (tip_movi = 1/*emetida*/ OR tip_movi = 2/*recebida*/),
  CONSTRAINT idx_pk_sem_movi PRIMARY KEY(idn_sem_movi),
  CONSTRAINT idx_fk_sem_movi_pess FOREIGN KEY(idn_declarante) REFERENCES declarante(idn_declarante)
);
CREATE INDEX idx_sem_movi_tip on sem_movimentacao(tip_movi);
/*********************************************************************************************************
Incentivo
*/
CREATE TABLE incentivo_cultural (
  idn_ince INT NOT NULL IDENTITY,/*chave autoincremental*/
  idn_empr_idor BIGINT NOT NULL,/*incentivador*/
  num_term SMALLINT NOT NULL,
  ano_term SMALLINT NOT NULL,
  num_prcs VARCHAR(12) NOT NULL,
  desc_ince VARCHAR(150) NOT NULL,/*descricao do incentivo*/
  valor_ince NUMERIC(15,2) NOT NULL,
  data_ref TIMESTAMP NOT NULL,
  CONSTRAINT idx_pk_ince PRIMARY KEY(idn_ince),
  CONSTRAINT idx_uk_ince UNIQUE(idn_empr_idor, num_term, ano_term, num_prcs, desc_ince, valor_ince, data_ref),
  CONSTRAINT fk_ince_empr_idor FOREIGN KEY(idn_empr_idor) REFERENCES empresa(idn_empr)
);
/*********************************************************************************************************
Aliquotas para determinada situação 
*/
CREATE TABLE aliquota_simples_nacional (
  idn_aliq_simp_naci INT NOT NULL IDENTITY,/*id aliquota*/
  valor_aliq NUMERIC(6,4) NOT NULL,/*valor aliquota*/
  data_inic TIMESTAMP NOT NULL,/*data inicio de vigencia*/
  data_fim TIMESTAMP,/*data fim de vigencia*/
  CONSTRAINT ck_data_fim_simp_naci check(data_fim >= data_inic OR data_fim is null),
  CONSTRAINT idx_pk_aliq_situ PRIMARY KEY(idn_aliq_simp_naci),
  CONSTRAINT idx_uk_aliq_situ UNIQUE(valor_aliq,data_inic)
)
;


/*********************************************************************************************************
Historico Situação especial da pessoa
*/
CREATE TABLE hist_situ_especial_empresa (
  idn_hist_situ INT NOT NULL IDENTITY,
  idn_empr BIGINT NOT NULL,/*id da empresa*/
  idn_situ_esp INT NOT NULL,/*nome da situacao especial*/
  data_inic_situ TIMESTAMP NOT NULL,/*data inicio vigencia*/
  data_fim_situ TIMESTAMP,/*data final vigencia*/
  CONSTRAINT ck_data_fim_situ check((data_fim_situ >= data_inic_situ) OR (data_fim_situ is null)),
  CONSTRAINT idx_pk_hist_empr_situ_espe PRIMARY KEY(idn_hist_situ),
  CONSTRAINT fk_hist_empr_situ_espe_sies FOREIGN KEY(idn_situ_esp) REFERENCES situ_especial(idn_situ_esp),
  CONSTRAINT fk_hist_empr_situ_pess FOREIGN KEY(idn_empr) REFERENCES empresa(idn_empr)
)
;
/*********************************************************************************************************
Historico Situação especial da pessoa
*/
CREATE TABLE hist_situ_especial_declarante (
  idn_hist_situ INT NOT NULL IDENTITY,
  idn_declarante INT NOT NULL,/*id da empresa*/
  idn_situ_esp INT NOT NULL,/*nome da situacao especial*/
  data_inic_situ TIMESTAMP NOT NULL,/*data inicio vigencia*/
  data_fim_situ TIMESTAMP,/*data final vigencia*/
  CONSTRAINT ck_data_fim_situ_dcrd check((data_fim_situ >= data_inic_situ) OR (data_fim_situ is null)),
  CONSTRAINT idx_pk_hist_empr_dcrd_situ_espe PRIMARY KEY(idn_hist_situ),
  CONSTRAINT fk_hist_empr_dcrd_situ_espe_sies FOREIGN KEY(idn_situ_esp) REFERENCES situ_especial(idn_situ_esp),
  CONSTRAINT fk_hist_empr_dcrd_situ_pess FOREIGN KEY(idn_declarante) REFERENCES declarante(idn_declarante)
)
;
/*********************************************************************************************************
Historico Simples Nacional da pessoa 
*/
CREATE TABLE hist_simples_nacional_empresa (
  idn_hist_simpl INT NOT NULL IDENTITY,
  idn_empr BIGINT NOT NULL,/*id empresa*/
  data_inic_sina TIMESTAMP NOT NULL,/*data inicio vigencia*/
  data_fim_sina TIMESTAMP,/*data final vigencia*/
  CONSTRAINT ck_datas_simp_naci check(data_fim_sina >= data_inic_sina OR data_fim_sina is null),
  CONSTRAINT idx_pk_hist_pess_sina PRIMARY KEY(idn_hist_simpl),
  CONSTRAINT fk_hist_pess_sina_pess FOREIGN KEY(idn_empr) REFERENCES empresa(idn_empr)
)
;
/*********************************************************************************************************
lista de serviço atual
*/
CREATE TABLE lista_servico (
  idn_list_serv INT NOT NULL,
  cod_list_serv VARCHAR(10) NOT NULL,
  desc_list_serv VARCHAR(500) NOT NULL,
  idn_legs INT,
  ind_situ_veto BOOLEAN DEFAULT FALSE NOT NULL,
  data_incl TIMESTAMP NOT NULL,
  CONSTRAINT idx_pk_list_serv PRIMARY KEY(idn_list_serv)
)
;
CREATE INDEX idx_cod_list_serv ON lista_servico(cod_list_serv);
/*********************************************************************************************************
historico lista de serviço
*/
CREATE TABLE hist_lista_servico (
  idn_list_serv INT NOT NULL,
  ind_situ_veto BOOLEAN DEFAULT FALSE NOT NULL,
  data_inic_vige TIMESTAMP NOT NULL,
  data_fina_vige TIMESTAMP,/*data final vigencia*/
  CONSTRAINT ck_datas_hist_list_serv check(data_fina_vige >= data_inic_vige OR data_fina_vige is null),
  CONSTRAINT idx_pk_hist_list_serv PRIMARY KEY(idn_list_serv),
  CONSTRAINT fk_hist_list_serv_ltsr FOREIGN KEY(idn_list_serv) REFERENCES lista_servico(idn_list_serv)
)
;
/*********************************************************************************************************
atividade
*/
CREATE TABLE atividade (
  idn_atvd VARCHAR(10) NOT NULL,/*codigo atividade*/
  idn_grup_atvd INT NOT NULL,/*id grupo*/
  idn_legs INT,/*id legislacao*/
  vrs_atvd SMALLINT DEFAULT 2 NOT NULL/*valor para cnae 2, deixei o campo caso mudasse o cnae 2 - valor 3 para ctiss*/,
  desc_atvd VARCHAR(500) NOT NULL,/*descricao atividade*/
  valor_aliq NUMERIC(6,4) NOT NULL,/*valor aliquota*/
  ind_iss BOOLEAN NOT NULL,    
  ind_visivel BOOLEAN NOT NULL,/*atividade sera exibida para o contribuinte*/
  ind_emis_tfs BOOLEAN NOT NULL,/*opcao emissao talao fiscal*/
  ind_scde_libe BOOLEAN NOT NULL,/*opcao sociedade liberal*/
  ind_inci_bhte BOOLEAN NOT NULL,/*opcao inc. BH*/
  num_nivl_deta SMALLINT NOT NULL,/*numero nivel detalhamento*/
  data_incl TIMESTAMP NOT NULL,/*data da inclusao*/
  CONSTRAINT idx_pk_ativ PRIMARY KEY(idn_atvd),
  CONSTRAINT fk_ativ_grup FOREIGN KEY(idn_grup_atvd) REFERENCES grupo_atividade(idn_grup_atvd)
)
;
CREATE INDEX idx_atvd_grup on atividade(idn_grup_atvd);
CREATE INDEX idx_atvd_tip on atividade(vrs_atvd);
/*********************************************************************************************************
Associacao entre os cnae
*/
CREATE TABLE hist_atividade(
   idn_atvd_atua VARCHAR(10) NOT NULL,/*atividade atual*/
   idn_atvd_ante VARCHAR(10) NOT NULL,/*atividade substituida*/
   CONSTRAINT idx_pk_atvd_cnae PRIMARY KEY(idn_atvd_atua,idn_atvd_ante),
   CONSTRAINT fk_atvd_cnae_atua FOREIGN KEY(idn_atvd_atua) REFERENCES atividade(idn_atvd),
   CONSTRAINT fk_atvd_cnae_ante FOREIGN KEY(idn_atvd_ante) REFERENCES atividade(idn_atvd)
);
/*********************************************************************************************************
Historico da atividade
*/
CREATE TABLE hist_atividade_aliq(
/* históricos de alíquotas de atividade econômicas de pessoas jurídicas e pessoas físicas. */
   idn_atvd       VARCHAR(10) NOT NULL,
   idn_legs       INT,
   data_inic_vige  TIMESTAMP NOT NULL,
   data_fina_vige  TIMESTAMP,
   valor_aliq       NUMERIC(6,4) NOT NULL,
   CONSTRAINT ck_datas_hist_ativ check(data_fina_vige >= data_inic_vige OR data_fina_vige is null),
   CONSTRAINT idx_pk_atvd_hist PRIMARY KEY(idn_atvd,data_inic_vige),
   CONSTRAINT fk_atvd_hist_atvd FOREIGN KEY(idn_atvd) REFERENCES atividade(idn_atvd)
)
;
/*********************************************************************************************************
Associacao lista de servico com atividade
*/
/*CREATE TABLE atividade_servico(
   idn_atvd VARCHAR(10) NOT NULL,
   idn_list_serv INT NOT NULL,
   CONSTRAINT idx_pk_atvd_serv PRIMARY KEY(idn_atvd,idn_list_serv),
   CONSTRAINT fk_atvd_serv_atvd FOREIGN KEY(idn_atvd) REFERENCES atividade(idn_atvd),
   CONSTRAINT fk_atvd_serv_serv FOREIGN KEY(idn_list_serv) REFERENCES lista_servico(idn_list_serv)
);*/
/*********************************************************************************************************
Associacao lista de servico com atividade
*/
CREATE TABLE hist_atividade_servico(
   idn_atvd VARCHAR(10) NOT NULL,/*atividade*/
   idn_list_serv INT NOT NULL,/*lista de servico*/
   data_inic_vige  TIMESTAMP NOT NULL,
   data_fina_vige  TIMESTAMP,
   CONSTRAINT ck_datas_hist_atvd_serv check(data_fina_vige >= data_inic_vige OR data_fina_vige is null),
   CONSTRAINT idx_pk_hist_atvd_serv PRIMARY KEY(idn_atvd,idn_list_serv,data_inic_vige),
   CONSTRAINT fk_hist_atvd_serv_atvd FOREIGN KEY(idn_atvd) REFERENCES atividade(idn_atvd),
   CONSTRAINT fk_hist_atvd_serv_serv FOREIGN KEY(idn_list_serv) REFERENCES lista_servico(idn_list_serv)
);
/*********************************************************************************************************
atividade da pessoa
*/
CREATE TABLE empresa_atividade (
  idn_empr_ativ INT NOT NULL IDENTITY,
  idn_empr BIGINT NOT NULL,
  idn_atvd VARCHAR(10) NOT NULL,/*codigo atividade*/
  ind_atvd SMALLINT NOT NULL,
  data_incl TIMESTAMP NOT NULL,/*data de inclusao*/
  CONSTRAINT ck_indc_atvd check(ind_atvd = 1/*Principal*/ OR ind_atvd = 2/*Secundaria*/),
  CONSTRAINT idx_pk_empr_ativ PRIMARY KEY(idn_empr_ativ),
  CONSTRAINT fk_empr_ativ_ativ FOREIGN KEY(idn_atvd) REFERENCES atividade(idn_atvd),
  CONSTRAINT fk_empr_ativ_empr FOREIGN KEY(idn_empr) REFERENCES empresa(idn_empr),
  CONSTRAINT idx_uk_empr_atvd UNIQUE(idn_empr, idn_atvd)
)
;
/*********************************************************************************************************
atividade da pessoa declarada
*/
CREATE TABLE atividade_declarante (
  idn_ativ_decl INT NOT NULL IDENTITY,
  idn_declarante INT NOT NULL,
  idn_atvd VARCHAR(10) NOT NULL,/*codigo atividade*/
  ind_atvd SMALLINT NOT NULL,
  data_incl TIMESTAMP NOT NULL,/*data de inclusao*/
  CONSTRAINT ck_indc_atvd_dcrd check(ind_atvd = 1/*Principal*/ OR ind_atvd = 2/*Secundaria*/),
  CONSTRAINT idx_pk_empr_dcrd_ativ PRIMARY KEY(idn_ativ_decl),
  CONSTRAINT fk_empr_dcrd_ativ_ativ FOREIGN KEY(idn_atvd) REFERENCES atividade(idn_atvd),
  CONSTRAINT fk_empr_dcrd_ativ_empr FOREIGN KEY(idn_declarante) REFERENCES declarante(idn_declarante),
)
;
/*********************************************************************************************************
indice monetário
campos: tip_indi
          1 OTN
          2 UFPBH
          3 BTNF/BTN
          4 TRA (no sei o que  isso)
          5 SELIC
          6 UFIR
          7 VCM (no sei o que  isso)
          8 DOLAR
          9 URV
SPL
TAXA EXPEDIENTE
UFPBH
*/
CREATE TABLE parametro_calculo (
  data_ref TIMESTAMP NOT NULL,
  tip_param SMALLINT NOT NULL,
  valor_param NUMERIC(15,7) NOT NULL,
  CONSTRAINT idx_pk_indi_mone PRIMARY KEY(data_ref,tip_param)
)
;
/*********************************************************************************************************
feriado
*/
CREATE TABLE feriado (
  data_feri TIMESTAMP NOT NULL,
  tip_feri SMALLINT NOT NULL,
  desc_feri VARCHAR(60) NOT NULL,
  CONSTRAINT ck_tipo_feri CHECK(tip_feri = 1 OR tip_feri = 2 OR tip_feri = 3),
  CONSTRAINT idx_pk_feri PRIMARY KEY(data_feri)
)
;
/*********************************************************************************************************
Motivo compensacao
*/
CREATE TABLE motivo_compensacao (
  idn_moti_comp INT NOT NULL IDENTITY,
  desc_moti_comp VARCHAR(150) NOT NULL,/*nome do motivo da compensacao*/
  CONSTRAINT idx_pk_moti PRIMARY KEY(idn_moti_comp),
  CONSTRAINT idx_uk_moti UNIQUE(desc_moti_comp)
)
;


/*********************************************************************************************************
motivo_cancelamento
*/
CREATE TABLE motivo_cancelamento(
   idn_moti_canc INT NOT NULL IDENTITY,
   desc_moti_canc VARCHAR(60) NOT NULL,/*descricao motivo cancelamento*/
   CONSTRAINT idx_pk_moti_canc PRIMARY KEY(idn_moti_canc),
   CONSTRAINT idx_uk_moti_canc UNIQUE(desc_moti_canc)
)
;


/*********************************************************************************************************
empresa em evasao fiscal
*/
CREATE TABLE retencao_obrigatoria(
   cnpj VARCHAR(14) NOT NULL,
   tip_retencao SMALLINT, 
   CONSTRAINT idx_pk_ret_obr PRIMARY KEY(cnpj, tip_retencao)
);
/*********************************************************************************************************
AIDF 
*/
CREATE TABLE aidf (
  idn_aidf INT IDENTITY NOT NULL,
  num_aidf INT NOT NULL,
  data_incl TIMESTAMP NOT NULL,
  idn_empr BIGINT NOT NULL,
  idn_modelo INT NOT NULL,
  idn_serie INT NOT NULL,
  sub_serie CHAR(2),
  num_doct_inic  INT NOT NULL,  /* numeracao inicial do documento */
  num_doct_fina  INT NOT NULL,  /* numeracao final do documento */
  data_vali TIMESTAMP NOT NULL,
  CONSTRAINT ck_data_doct_aidf CHECK(num_doct_fina >= num_doct_inic),
  CONSTRAINT idx_pk_aidf PRIMARY KEY(idn_aidf),
  CONSTRAINT fk_aidf_mode FOREIGN KEY(idn_modelo) REFERENCES modelo(idn_modelo),
  CONSTRAINT fk_aidf_seri FOREIGN KEY(idn_serie) REFERENCES serie(idn_serie),
  CONSTRAINT fk_aidf_pess FOREIGN KEY(idn_empr) REFERENCES empresa(idn_empr),
  CONSTRAINT IDX_UK_AIDF_EMP_NUM UNIQUE(NUM_AIDF,IDN_EMPR,NUM_DOCT_INIC)
)
;
CREATE INDEX idx_aidf_empr ON aidf(idn_empr);
/*********************************************************************************************************
deducao de notas fiscais /OBS ARRUMAR tip_dedu/
*/
CREATE TABLE nota_deducao (
  idn_nota_dedu BIGINT NOT NULL IDENTITY,
  idn_declarante_pres INT NOT NULL,
  idn_cliente BIGINT NOT NULL,
  tip_nota_dedu SMALLINT NOT NULL,  /*1 = Material, 2 = Servio, 3 = Alimentao e bebidas/frigobar, 4 = Reembolso de despesas,  5 = Outras dedues no relacionadas e 6 = Repasse consorciado*/
  des_outr_tipo_dedu VARCHAR(150),
  numero BIGINT,
  valor_aliq NUMERIC(6,4) NOT NULL,
  valor_nf NUMERIC(15,2) NOT NULL,
  data_rece TIMESTAMP NOT NULL,
  opc_reco BOOLEAN DEFAULT false NOT NULL, /* 1 = retido, 2 = no retido */
  CONSTRAINT idx_pk_nota_dedu PRIMARY KEY(idn_nota_dedu),
//  CONSTRAINT ck_tipo_nota_dedu CHECK (tip_nota_dedu = 1 OR tip_nota_dedu = 2),
  CONSTRAINT fk_nota_dedu_pess_pres FOREIGN KEY(idn_declarante_pres) REFERENCES declarante(idn_declarante),
  CONSTRAINT fk_nota_dedu_pess_toma FOREIGN KEY(idn_cliente) REFERENCES cliente_fornecedor(idn_cliente)
);


/*********************************************************************************************************
creditos da empresa
*/
CREATE TABLE credito_declarante (
  idn_cred_decl IDENTITY NOT NULL,
  idn_moti_comp INT NOT NULL,
  idn_declarante INT NOT NULL,
  data_cred_empr TIMESTAMP NOT NULL,
  valor_cred_empr NUMERIC(15,2) NOT NULL,
  desc_cred_empr VARCHAR(255),
  CONSTRAINT idx_pk_cred_empr PRIMARY KEY(idn_cred_decl),
  CONSTRAINT fk_cred_empr_empr FOREIGN KEY(idn_declarante) REFERENCES declarante(idn_declarante),
  CONSTRAINT fk_cred_empr_moti FOREIGN KEY(idn_moti_comp) REFERENCES motivo_compensacao(idn_moti_comp)
)
;
/*********************************************************************************************************
obra -> olhar codigos e documentos
*/
CREATE TABLE obra (
  idn_obra INT IDENTITY NOT NULL,
  cod_obra VARCHAR(15) NOT NULL,
  num_art_obra VARCHAR(15) NOT NULL,
  CONSTRAINT idx_pk_obra PRIMARY KEY(idn_obra)
)
;


/**********************************************************************************************************
evento fazer
*/
CREATE TABLE evento (
  idn_evento BIGINT NOT NULL IDENTITY,
  desc_evnt VARCHAR(60) NOT NULL,
  CONSTRAINT idx_pk_even PRIMARY KEY(idn_evento)
);
/*********************************************************************************************************
ar_cancelada
*/
CREATE TABLE nf_cancelada (
  idn_canc BIGINT NOT NULL IDENTITY,
  idn_moti_canc INT NOT NULL,
  data_canc TIMESTAMP NOT NULL,
  num_subst BIGINT,
  desc_obsv VARCHAR(255),
  CONSTRAINT idx_pk_cancl PRIMARY KEY(idn_canc),
  CONSTRAINT fk_cancl_moti FOREIGN KEY(idn_moti_canc) REFERENCES motivo_cancelamento(idn_moti_canc)
)
;
/*********************************************************************************************************
servico_prestado
*/
CREATE TABLE servico_prestado (
  idn_serv_prest BIGINT IDENTITY NOT NULL,
  idn_declarante INT NOT NULL,
  opc_stat SMALLINT NOT NULL,
  data_emis TIMESTAMP NOT NULL,
  data_comp TIMESTAMP NOT NULL,
  idn_modelo INT NOT NULL,
  idn_serie INT NOT NULL,
  sub_serie VARCHAR(2),
  idn_tipo_negc INT NOT NULL,
  idn_natu_oper INT NOT NULL,
  idn_regi_espe_trbt INT NOT NULL,
  num_ini BIGINT,
  num_fin BIGINT,
  valor_bruto  NUMERIC(15,2),
  valor_serv NUMERIC(15,2) NOT NULL,
  valor_dedu NUMERIC(15,2),
  valor_base_calc NUMERIC(15,2),
  valor_aliq NUMERIC(6,4),
  valor_iss NUMERIC(15,2),
  opc_iss_reti SMALLINT NOT NULL,
  opc_simp_naci SMALLINT NOT NULL,
  idn_atvd VARCHAR(10),
  idn_list_serv INT,
  idn_cidade_pres INT,
  cod_pais INT,
  idn_cidade_inci INT,
  idn_cliente BIGINT,
  idn_intermed BIGINT,
  idn_evento BIGINT,
  data_evnt TIMESTAMP,
  idn_canc BIGINT,
  num_guia_nfse BIGINT,
  idn_situ_esp INT,
  idn_obra INT,
  data_altr TIMESTAMP,
  CONSTRAINT idx_pk_emit PRIMARY KEY(idn_serv_prest),
  CONSTRAINT fk_empr_pres FOREIGN KEY(idn_declarante) REFERENCES declarante(idn_declarante),
  CONSTRAINT fk_emit_tipo_negc FOREIGN KEY(idn_tipo_negc) REFERENCES tipo_negocio(idn_tipo_negc),
  CONSTRAINT fk_emit_natu_oper FOREIGN KEY(idn_natu_oper) REFERENCES natureza_operacao(idn_natu_oper),
  CONSTRAINT fk_emit_regi_espe_trbt FOREIGN KEY(idn_regi_espe_trbt) REFERENCES regime_especial_tributacao(idn_regi_espe_trbt),
  CONSTRAINT fk_emit_mode FOREIGN KEY(idn_modelo) REFERENCES modelo(idn_modelo),
  CONSTRAINT fk_emit_seri FOREIGN KEY(idn_serie) REFERENCES serie(idn_serie),
  CONSTRAINT fk_emit_atvd FOREIGN KEY(idn_atvd) REFERENCES atividade(idn_atvd),
  CONSTRAINT fk_emit_list_serv FOREIGN KEY(idn_list_serv) REFERENCES lista_servico(idn_list_serv),
  CONSTRAINT fk_emit_cida_pres FOREIGN KEY(idn_cidade_pres) REFERENCES cidade(idn_cidade),
  CONSTRAINT fk_emit_cida_inci FOREIGN KEY(idn_cidade_inci) REFERENCES cidade(idn_cidade),
  CONSTRAINT fk_emit_pess_toma FOREIGN KEY(idn_cliente) REFERENCES cliente_fornecedor(idn_cliente),
  CONSTRAINT fk_emit_pess_prep FOREIGN KEY(idn_intermed) REFERENCES cliente_fornecedor(idn_cliente),
  CONSTRAINT fk_emit_situ_espe FOREIGN KEY(idn_situ_esp) REFERENCES situ_especial(idn_situ_esp),
  CONSTRAINT fk_emit_obra FOREIGN KEY(idn_obra) REFERENCES obra(idn_obra),
  CONSTRAINT fk_emit_evnt FOREIGN KEY(idn_evento) REFERENCES evento(idn_evento),
  CONSTRAINT fk_emit_canc FOREIGN KEY(idn_canc) REFERENCES nf_cancelada(idn_canc)
)
;
CREATE INDEX IDX_DEC_DATA_EMIS ON SERVICO_PRESTADO(IDN_DECLARANTE,DATA_EMIS);
CREATE INDEX IDX_DEC_DATA_COMP ON SERVICO_PRESTADO(IDN_DECLARANTE,DATA_COMP);
/*********************************************************************************************************
deducao
*/
CREATE TABLE deducao(
   idn_dedu BIGINT NOT NULL IDENTITY,
   idn_serv_prest BIGINT NOT NULL,
   idn_nota_dedu BIGINT NOT NULL,
   data_dedu TIMESTAMP NOT NULL,
   valor_dedu NUMERIC(15,2) NOT NULL,
   CONSTRAINT idx_pk_dedu PRIMARY KEY(idn_dedu),
   CONSTRAINT fk_dedu_nota_dedu FOREIGN KEY(idn_nota_dedu) references nota_deducao(idn_nota_dedu),
   CONSTRAINT fk_dedu_nota_emit FOREIGN KEY(idn_serv_prest) references servico_prestado(idn_serv_prest)
);


/*********************************************************************************************************
Nota fiscal recebida
*/
CREATE TABLE servico_tomado (
  idn_serv_toma BIGINT IDENTITY NOT NULL,
  idn_declarante INT,
  data_emis TIMESTAMP NOT NULL,
  data_rece TIMESTAMP,
  data_comp TIMESTAMP NOT NULL,
  idn_modelo INT NOT NULL,
  idn_serie INT NOT NULL,
  sub_serie VARCHAR(2),
  idn_situ_espe_resp INT NOT NULL,
  idn_motv_nao_retc INT,
  num BIGINT,
  valor_serv NUMERIC(15,5) NOT NULL,
  valor_base_calc NUMERIC(15,2),
  valor_aliq NUMERIC(6,4) NOT NULL,
  valor_iss NUMERIC(15,5) NOT NULL,
  opc_iss_reti SMALLINT NOT NULL,
  opc_simp_naci SMALLINT NOT NULL,
  idn_cidade_pres INT,
  cod_pais INT,
  idn_cidade_inci int,
  idn_cliente BIGINT NOT NULL,
  idn_intermed BIGINT,
  idn_evento BIGINT,
  data_evnt TIMESTAMP,
  num_guia_nfse BIGINT,
  data_altr TIMESTAMP,
  CONSTRAINT idx_pk_rece PRIMARY KEY(idn_serv_toma),
  CONSTRAINT fk_rece_pess_pres FOREIGN KEY(idn_declarante) REFERENCES declarante(idn_declarante),
  CONSTRAINT fk_rece_pess_toma FOREIGN KEY(idn_cliente) REFERENCES cliente_fornecedor(idn_cliente),
  CONSTRAINT fk_rece_moti_rete FOREIGN KEY(idn_motv_nao_retc) REFERENCES motivo_nao_retencao(idn_motv_nao_retc),
  CONSTRAINT fk_rece_regr_espe FOREIGN KEY(idn_situ_espe_resp) REFERENCES situ_especial_respons(idn_situ_resp),
  CONSTRAINT fk_rece_mode FOREIGN KEY(idn_modelo) REFERENCES modelo(idn_modelo),
  CONSTRAINT fk_rece_seri FOREIGN KEY(idn_serie) REFERENCES serie(idn_serie),
  CONSTRAINT fk_rece_cida FOREIGN KEY(idn_cidade_pres) REFERENCES cidade(idn_cidade),
  CONSTRAINT fk_rece_pess_prep FOREIGN KEY(idn_intermed) REFERENCES cliente_fornecedor(idn_cliente),
  CONSTRAINT fk_rece_evnt FOREIGN KEY(idn_evento) REFERENCES evento(idn_evento),
  CONSTRAINT fk_rece_cida_inci FOREIGN KEY(idn_cidade_inci) REFERENCES cidade(idn_cidade)
);

CREATE INDEX IDX_DEC_DATA_RECE ON SERVICO_TOMADO(IDN_DECLARANTE,DATA_RECE);

/*********************************************************************************************************
guia iss proprio
*/
CREATE TABLE guia_iss_proprio (
  idn_guia BIGINT IDENTITY NOT NULL,
  num_guia BIGINT,
  tip_guia SMALLINT NOT NULL,
  sit_guia SMALLINT DEFAULT 2 NOT NULL,
  data_comp TIMESTAMP NOT NULL,
  data_emis TIMESTAMP NOT NULL,
  data_venc TIMESTAMP NOT NULL,
  valor_iss_devd NUMERIC(15,2) DEFAULT 0.00 NOT NULL,
  valor_cmps NUMERIC(15,2) DEFAULT 0.00 NOT NULL,
  valor_ince NUMERIC(15,2) DEFAULT 0.00 NOT NULL,
  valor_atuz NUMERIC(15,2) DEFAULT 0.00 NOT NULL,
  valor_juro NUMERIC(15,2) DEFAULT 0.00 NOT NULL,
  valor_mult NUMERIC(15,2) DEFAULT 0.00 NOT NULL,
  valor_taxa_banc NUMERIC(15,2) DEFAULT 0.00 NOT NULL,
  valor_final NUMERIC(15,2) DEFAULT 0.00 NOT NULL,
  idn_empr_guia INT,
  data_paga TIMESTAMP,
  data_canc TIMESTAMP,
  cod_barras VARCHAR(60),
  val_dedu NUMERIC(15,2) DEFAULT 0.00 NOT NULL,
  CONSTRAINT idx_pk_guia_prop PRIMARY KEY(idn_guia),
/*  CONSTRAINT ck_tip_guia CHECK(tip_guia in (1,2,5,6,8)),*//*1docfiscal2evento5nfse6trans8pl*/
  CONSTRAINT ck_sit_guia CHECK(sit_guia >=1 AND sit_guia<=6),/*1paga2aguardando3naopaga4cancelada5vencida6expurgo*/
  CONSTRAINT fk_guia_prop_pess FOREIGN KEY(idn_empr_guia) REFERENCES declarante(idn_declarante),
  CONSTRAINT uk_num_decl UNIQUE (num_guia, idn_empr_guia)
)
;
CREATE INDEX idx_fk_guia_prop_pess ON guia_iss_proprio(idn_empr_guia);
/*********************************************************************************************************
guia iss retido
*/
CREATE TABLE guia_iss_retido (
  idn_guia BIGINT IDENTITY NOT NULL,
  num_guia BIGINT,
  tip_guia SMALLINT NOT NULL,
  sit_guia SMALLINT DEFAULT 2 NOT NULL,
  data_comp TIMESTAMP NOT NULL,
  data_emis TIMESTAMP NOT NULL,
  data_venc TIMESTAMP NOT NULL,
  valor_iss_devd NUMERIC(15,2) DEFAULT 0.00 NOT NULL,
  valor_atuz NUMERIC(15,2) DEFAULT 0.00 NOT NULL,
  valor_juro NUMERIC(15,2) DEFAULT 0.00 NOT NULL,
  valor_mult NUMERIC(15,2) DEFAULT 0.00 NOT NULL,
  valor_final NUMERIC(15,2) DEFAULT 0.00 NOT NULL,
  idn_declarante INT,
  data_paga TIMESTAMP,
  data_canc TIMESTAMP,
  cod_barras VARCHAR(60),
  CONSTRAINT idx_pk_guia_retd PRIMARY KEY(idn_guia),
/*  CONSTRAINT ck_tip_guia_retd CHECK(tip_guia in (1,2,5,6,8)),*//*1docfiscal2evento5nfse6trans8pl*/
  CONSTRAINT ck_sit_guia_retd CHECK(sit_guia >=1 AND sit_guia<=6),/*1paga2aguardando3naopaga4cancelada5vencida6expurgo*/
  CONSTRAINT fk_guia_retd_toma FOREIGN KEY(idn_declarante) REFERENCES declarante(idn_declarante)
)
;
CREATE INDEX idx_fk_guia_retd_pres ON guia_iss_retido(idn_declarante);


/*********************************************************************************************************
guia retido 
*/
CREATE TABLE guia_nota_retido (
  idn_guia_nota_retido INT NOT NULL IDENTITY,
  idn_serv_toma BIGINT NOT NULL,
  idn_guia BIGINT NOT NULL,
  valor_usad NUMERIC(15,2) NOT NULL,
  CONSTRAINT idx_pk_guia_nota_retd PRIMARY KEY(idn_guia_nota_retido),
  CONSTRAINT fk_guia_nota_retd_guia FOREIGN KEY(idn_guia) REFERENCES guia_iss_retido(idn_guia),
  CONSTRAINT fk_guia_evnt_retd_evnt FOREIGN KEY(idn_serv_toma) REFERENCES servico_tomado(idn_serv_toma)
)
;
/*********************************************************************************************************
guia proprio
*/
CREATE TABLE guia_nota_proprio (
  idn_guia_nota_proprio INT NOT NULL IDENTITY,
  idn_serv_prest BIGINT NOT NULL,
  idn_guia BIGINT NOT NULL,
  valor_usad NUMERIC(15,2) NOT NULL,
  CONSTRAINT idx_pk_guia_nota_prop PRIMARY KEY(idn_guia_nota_proprio),
  CONSTRAINT fk_guia_nota_prop_guia FOREIGN KEY(idn_guia) REFERENCES guia_iss_proprio(idn_guia),
  CONSTRAINT fk_guia_nota_prop_evnt FOREIGN KEY(idn_serv_prest) REFERENCES servico_prestado(idn_serv_prest)
)
;
/*********************************************************************************************************
guia Profissional
*/
CREATE TABLE guia_profissional (
  idn_guia_prof INT NOT NULL IDENTITY,
  idn_prof_libe INT NOT NULL,
  idn_guia BIGINT NOT NULL,
  CONSTRAINT idx_pk_guia_prof PRIMARY KEY(idn_guia_prof),
  CONSTRAINT fk_guia_prof_guia FOREIGN KEY(idn_guia) REFERENCES guia_iss_proprio(idn_guia),
  CONSTRAINT fk_guia_prof_prof FOREIGN KEY(idn_prof_libe) REFERENCES profissional_liberal(idn_prof_libe)
)
;
/*********************************************************************************************************
compensacao credito guia
*/
CREATE TABLE compensacao_guia(
  idn_compens_guia INT NOT NULL IDENTITY,
  idn_guia BIGINT NOT NULL,
  idn_cred_decl INT NOT NULL,
  valor_usad NUMERIC(15,2) NOT NULL,
  CONSTRAINT idx_pk_cops_cred_guia PRIMARY KEY(idn_compens_guia),
  CONSTRAINT fk_cops_cred_guia_moti FOREIGN KEY(idn_cred_decl) REFERENCES credito_declarante(idn_cred_decl),
  CONSTRAINT fk_cops_cred_guia_guia FOREIGN KEY(idn_guia) REFERENCES guia_iss_proprio(idn_guia)
)
;


/*********************************************************************************************************
incentivo guia
*/
CREATE TABLE incentivo_guia (
  idn_incent_guia INT NOT NULL IDENTITY,
  idn_ince INT NOT NULL,/*identificador do incentivo*/
  idn_guia BIGINT NOT NULL,
  valor_usad NUMERIC(15,2) NOT NULL,
  CONSTRAINT idx_guia_ince_prop PRIMARY KEY(idn_incent_guia),
  CONSTRAINT fk_guia_ince_prop_guia FOREIGN KEY(idn_guia) REFERENCES guia_iss_proprio(idn_guia),
  CONSTRAINT fk_guia_ince_prop_perd_ince FOREIGN KEY(idn_ince) REFERENCES incentivo_cultural(idn_ince)
)
;
/*******************************************************************************************************
Transmisso de Imveis - ITBI
*/
CREATE TABLE TRANS_IMOVEL(
IDN_TRANSIMOVEL BIGINT NOT NULL IDENTITY PRIMARY KEY,
IDN_DECLARANTE INTEGER,DATA_REF TIMESTAMP,
/*CPF_ADQ VARCHAR(11), CNPJ_ADQ VARCHAR(14),NOME_ADQ VARCHAR(50),*/
IDN_ENDER BIGINT,CPF_TRANS VARCHAR(11),
CNPJ_TRANS VARCHAR(14),
NOME_TRANS VARCHAR(150),
IDN_INDICE_CADASTRAL VARCHAR(15),
VALOR_BASICO NUMERIC(15),
COD_NATUREZA INTEGER,
DATA_REGISTRO TIMESTAMP,
NUM_LANCAMENTO VARCHAR(16),
NUM_TRANS INTEGER,
ANO_TRANS INTEGER,
NUM_MATRICULA VARCHAR(20),
DATA_INCL TIMESTAMP,
CONSTRAINT FK_TRANS_DEC FOREIGN KEY(IDN_DECLARANTE) REFERENCES DECLARANTE(IDN_DECLARANTE)
);


CREATE TABLE adquirente(
idn_adquirente BIGINT NOT NULL IDENTITY PRIMARY KEY,
idn_transimovel BIGINT,
cnpj VARCHAR(14),
cpf VARCHAR(11),
nome VARCHAR(150),
CONSTRAINT fk_adquirente_trans_imovel FOREIGN KEY(idn_transimovel) REFERENCES trans_imovel(idn_transimovel)
);
CREATE TABLE natureza_transimovel (
  idn_natu_trans INT NOT NULL IDENTITY,
  desc_natu_trans VARCHAR(70) NOT NULL,
  CONSTRAINT idx_pk_natu_trans PRIMARY KEY(idn_natu_trans),
  CONSTRAINT idx_uk_natu_trans UNIQUE(desc_natu_trans)
);


/********************************************************************************************************
Controle das atualizaes das tabelas
*/
CREATE TABLE CONTROLE_ATU(
IDN_CONTROLE_ATU INT NOT NULL IDENTITY,
NOME_TABELA VARCHAR(50) NOT NULL, 
DATA_ATU TIMESTAMP NOT NULL,
 PRIMARY KEY (IDN_CONTROLE_ATU)
)
;


/*
Servios Tomados com reteno de iss deduzidos em guias
*/
CREATE  TABLE DEDUCAO_GUIA(
IDN_DEDU_GUIA INTEGER NOT NULL IDENTITY,
IDN_GUIA BIGINT NOT NULL,
IDN_SERV_TOMA BIGINT NOT NULL,
VALOR NUMERIC(15,2) NOT NULL,
CONSTRAINT IDX_PK_COPS_DEDU_GUIA PRIMARY KEY(IDN_DEDU_GUIA),
CONSTRAINT FK_COPS_DEDU_GUIA_SERV FOREIGN KEY(IDN_SERV_TOMA) REFERENCES SERVICO_TOMADO(IDN_SERV_TOMA),
CONSTRAINT FK_COPS_DEDU_GUIA_GUIA FOREIGN KEY(IDN_GUIA) REFERENCES GUIA_ISS_PROPRIO(IDN_GUIA)
);

/*
Data/hora de Transmisso da ltima declarao vlida
*/
CREATE TABLE DATA_ENVIO(
idn_data_envio INT IDENTITY NOT NULL,
idn_declarante INT NOT NULL,
data_comp TIMESTAMP NOT NULL,
data_envio TIMESTAMP NOT NULL,
CONSTRAINT idx_pk_data_envio PRIMARY KEY(idn_data_envio),
CONSTRAINT fk_controle_alt_declarante FOREIGN KEY(idn_declarante) REFERENCES declarante(idn_declarante)
);




CREATE TABLE VERSAO (
NUM_VERSAO INTEGER );



CREATE TABLE DEDUCAO_SERVICO_TOMADO(
IDN_DEDU BIGINT NOT NULL IDENTITY,
IDN_SERV_PREST BIGINT NOT NULL,
IDN_SERV_TOMA BIGINT NOT NULL,
DATA_DEDU TIMESTAMP NOT NULL,
CONSTRAINT IDX_PK_DEDU_TOMA PRIMARY KEY(IDN_DEDU),
CONSTRAINT FK_DEDU_TOMA_SERV_TOMA FOREIGN KEY(IDN_SERV_TOMA) 
REFERENCES SERVICO_TOMADO(IDN_SERV_TOMA),CONSTRAINT FK_DEDU_TOMA_NOTA_EMIT FOREIGN KEY(IDN_SERV_PREST) REFERENCES SERVICO_PRESTADO(IDN_SERV_PREST)
);


CREATE TABLE MENSAGEM (
IDN_MSG  INTEGER IDENTITY NOT NULL, 
TIP_MSG INTEGER DEFAULT 0 NOT NULL, /* 0 - Erro; 1 - Sucesso; 2 - Ateno */
DESC_MSG VARCHAR(1000) NOT NULL, 
PRIMARY KEY (IDN_MSG) 
);

/*********************************************************************************************************
Associacao entre os cnae e ctiss
*/
/*CREATE TABLE ctiss_atividade(
   idn_atvd_ctiss VARCHAR(10) NOT NULL,/*cdigo ctiss*/
   idn_atvd_cnae VARCHAR(10) NOT NULL,/*cdigo cnae*/
   CONSTRAINT idx_pk_atvd_ctiss PRIMARY KEY(idn_atvd_ctiss,idn_atvd_cnae),
   CONSTRAINT fk_atvd_ctiss FOREIGN KEY(idn_atvd_ctiss) REFERENCES atividade(idn_atvd),
   CONSTRAINT fk_atvd_cnae FOREIGN KEY(idn_atvd_cnae) REFERENCES atividade(idn_atvd)
);
*/



