USE siconv;

TRUNCATE TABLE siconv.siconv_convenio;
TRUNCATE TABLE siconv.siconv_desbloqueio_cr;
TRUNCATE TABLE siconv.siconv_desembolso;
TRUNCATE TABLE siconv.siconv_emenda;
TRUNCATE TABLE siconv.siconv_empenho;
TRUNCATE TABLE siconv_historico_situacao;
TRUNCATE TABLE siconv_plano_aplicacao_detalhado;
TRUNCATE TABLE siconv.siconv_programa;
TRUNCATE TABLE siconv.siconv_programa_proposta;
TRUNCATE TABLE siconv_proposta;

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\SICONV\\CSV\\siconv_convenio.csv'
INTO TABLE siconv.siconv_convenio
CHARACTER SET utf8
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
    NR_CONVENIO,
    ID_PROPOSTA,
    DIA,
    MES,
    ANO,
    DIA_ASSIN_CONV,
    SIT_CONVENIO,
    SUBSITUACAO_CONV,
    SITUACAO_PUBLICACAO,
    INSTRUMENTO_ATIVO,
    IND_OPERA_OBTV,
    NR_PROCESSO,
    UG_EMITENTE,
    DIA_PUBL_CONV,
    DIA_INIC_VIGENC_CONV,
    DIA_FIM_VIGENC_CONV,
    DIA_FIM_VIGENC_ORIGINAL_CONV,
    DIAS_PREST_CONTAS,
    DIA_LIMITE_PREST_CONTAS,
    DATA_SUSPENSIVA,
    DATA_RETIRADA_SUSPENSIVA,
    DIAS_CLAUSULA_SUSPENSIVA,
    SITUACAO_CONTRATACAO,
    IND_ASSINADO,
    MOTIVO_SUSPENSAO,
    IND_FOTO,
    QTDE_CONVENIOS,
    QTD_TA,
    QTD_PRORROGA,
    VL_GLOBAL_CONV,
    VL_REPASSE_CONV,
    VL_CONTRAPARTIDA_CONV,
    VL_EMPENHADO_CONV,
    VL_DESEMBOLSADO_CONV,
    VL_SALDO_REMAN_TESOURO,
    VL_SALDO_REMAN_CONVENENTE,
    VL_RENDIMENTO_APLICACAO,
    VL_INGRESSO_CONTRAPARTIDA,
    VL_SALDO_CONTA,
    VALOR_GLOBAL_ORIGINAL_CONV
);

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\SICONV\\CSV\\siconv_desbloqueio_cr.csv'
INTO TABLE siconv.siconv_desbloqueio_cr
CHARACTER SET utf8
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
    NR_CONVENIO,
    NR_OB,
    DATA_CADASTRO,
    DATA_ENVIO,
    TIPO_RECURSO_DESBLOQUEIO,
    VL_TOTAL_DESBLOQUEIO,
    VL_DESBLOQUEADO,
    VL_BLOQUEADO
);

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\SICONV\\CSV\\siconv_desembolso.csv'
INTO TABLE siconv.siconv_desembolso
CHARACTER SET utf8
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
    ID_DESEMBOLSO,
    NR_CONVENIO,
    DT_ULT_DESEMBOLSO,
    QTD_DIAS_SEM_DESEMBOLSO,
    DATA_DESEMBOLSO,
    ANO_DESEMBOLSO,
    MES_DESEMBOLSO,
    NR_SIAFI,
    VL_DESEMBOLSADO
);

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\SICONV\\CSV\\siconv_emenda.csv'
INTO TABLE siconv.siconv_emenda
CHARACTER SET utf8
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
    ID_PROPOSTA,
    QUALIF_PROPONENTE,
    COD_PROGRAMA_EMENDA,
    NR_EMENDA,
    NOME_PARLAMENTAR,
    BENEFICIARIO_EMENDA,
    IND_IMPOSITIVO,
    TIPO_PARLAMENTAR,
    VALOR_REPASSE_PROPOSTA_EMENDA,
    VALOR_REPASSE_EMENDA
);

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\SICONV\\CSV\\siconv_empenho.csv'
INTO TABLE siconv.siconv_empenho
CHARACTER SET utf8
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
    ID_EMPENHO,
    NR_CONVENIO,
    NR_EMPENHO,
    TIPO_NOTA,
    DESC_TIPO_NOTA,
    DATA_EMISSAO,
    COD_SITUACAO_EMPENHO,
    DESC_SITUACAO_EMPENHO,
    VALOR_EMPENHO
);

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\SICONV\\CSV\\siconv_programa.csv'
INTO TABLE siconv.siconv_programa
CHARACTER SET utf8
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
    COD_ORGAO_SUP_PROGRAMA,
    DESC_ORGAO_SUP_PROGRAMA,
    ID_PROGRAMA,
    COD_PROGRAMA,
    NOME_PROGRAMA,
    SIT_PROGRAMA,
    DATA_DISPONIBILIZACAO,
    ANO_DISPONIBILIZACAO,
    DT_PROG_INI_RECEB_PROP,
    DT_PROG_FIM_RECEB_PROP,
    DT_PROG_INI_EMENDA_PAR,
    DT_PROG_FIM_EMENDA_PAR,
    DT_PROG_INI_BENEF_ESP,
    DT_PROG_FIM_BENEF_ESP,
    MODALIDADE_PROGRAMA,
    NATUREZA_JURIDICA_PROGRAMA,
    UF_PROGRAMA,
    ACAO_ORCAMENTARIA
);

LOAD DATA LOW_PRIORITY LOCAL INFILE 'C:\\SICONV\\CSV\\siconv_programa_proposta.csv'
INTO TABLE siconv.siconv_programa_proposta
CHARACTER SET utf8
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
    ID_PROGRAMA,
    ID_PROPOSTA
);

LOAD DATA LOCAL INFILE 'C:\\SICONV\\CSV\\siconv_proposta.csv' IGNORE
INTO TABLE siconv.siconv_proposta
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(
    ID_PROPOSTA,
    UF_PROPONENTE,
    MUNIC_PROPONENTE,
    COD_MUNIC_IBGE,
    COD_ORGAO_SUP,
    DESC_ORGAO_SUP,
    NATUREZA_JURIDICA,
    NR_PROPOSTA,
    DIA_PROP,
    MES_PROP,
    ANO_PROP,
    DIA_PROPOSTA,
    COD_ORGAO,
    DESC_ORGAO,
    MODALIDADE,
    IDENTIF_PROPONENTE,
    NM_PROPONENTE,
    CEP_PROPONENTE,
    ENDERECO_PROPONENTE,
    BAIRRO_PROPONENTE,
    NM_BANCO,
    SITUACAO_CONTA,
    SITUACAO_PROJETO_BASICO,
    SIT_PROPOSTA,
    DIA_INIC_VIGENCIA_PROPOSTA,
    DIA_FIM_VIGENCIA_PROPOSTA,
    OBJETO_PROPOSTA,
    ITEM_INVESTIMENTO,
    ENVIADA_MANDATARIA,
    VL_GLOBAL_PROP,
    VL_REPASSE_PROP,
    VL_CONTRAPARTIDA_PROP
);