<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class Task2303171640 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
CREATE TEMP TABLE beneficiarios_banco AS
    SELECT id, cpf_cnpj
      FROM seguro.beneficiarios
     WHERE exists(SELECT 1
                    FROM sistema.agencias
                   WHERE ds_cnpj = cpf_cnpj);

DELETE FROM log.log_beneficiarios_arquivos_status llbas
      USING beneficiarios_banco bb
      WHERE bb.id = llbas.id_beneficiario;

CREATE TEMP TABLE beneficiarios_banco_informacoes_bancarias AS
     SELECT sib.id
       FROM sistema.informacoes_bancarias sib
            INNER JOIN seguro.informacoes_bancarias_beneficiarios sibb ON sibb.id_informacoes_bancarias = sib.id
            INNER JOIN beneficiarios_banco bb ON bb.id = sibb.id_beneficiario;

DELETE FROM seguro.informacoes_bancarias_beneficiarios sibb
      USING beneficiarios_banco bb
      WHERE bb.id = sibb.id_beneficiario;

DELETE FROM sistema.informacoes_bancarias sib
      USING beneficiarios_banco_informacoes_bancarias bbib
      WHERE bbib.id = sib.id;

DELETE FROM seguro.beneficiarios_arquivos sba
      USING beneficiarios_banco bb
      WHERE bb.id = sba.id_beneficiario;

DELETE FROM seguro.beneficiarios_pendencias_arquivos sbpa
      USING beneficiarios_banco bb
      WHERE bb.id = sbpa.id_beneficiario;

DELETE FROM seguro.beneficiarios_pendencias sbp
      USING beneficiarios_banco bb
      WHERE bb.id = sbp.id_beneficiario;

DELETE FROM seguro.beneficiarios sb
      USING beneficiarios_banco bb
      WHERE bb.id = sb.id;

CREATE TEMP TABLE beneficiarios_informacoes_bancarias AS
SELECT spb.nr_cpf_cnpj as cpf_cnpj, spb.id_proposta, sb.ds_nome_banco
FROM seguro.propostas_beneficiarios spb
         INNER JOIN sistema.agencias sa ON sa.ds_cnpj = spb.nr_cpf_cnpj
         INNER JOIN sistema.bancos sb ON sb.id = sa.id_banco
         LEFT JOIN seguro.informacoes_bancarias sib ON sib.cpf_cnpj = nr_cpf_cnpj AND sib.id_proposta = spb.id_proposta
WHERE sib.id is null
  AND spb.ds_tipo_conta = 's'
  AND spb.fl_del is false
GROUP BY spb.nr_cpf_cnpj, spb.id_proposta, sb.ds_nome_banco;

INSERT INTO seguro.informacoes_bancarias
SELECT 2068 as id_usuario_criacao,
       null as id_usuario_alteracao,
       now() as dt_criacao,
       null as dt_alteracao,
       true as fl_ativo,
       false as fl_del,
       nextval('seguro.informacoes_bancarias_id_seq'::regclass) as id,
       id_proposta,
       cpf_cnpj,
       null as id_banco,
       '' as nu_conta_corrente,
       '' as nu_digito_conta_corrente,
       '' as nu_agencia,
       '' as nu_digito_agencia,
       2 as id_forma_pagamento
FROM beneficiarios_informacoes_bancarias;

UPDATE seguro.propostas_beneficiarios spb
SET ds_nome_beneficiarios = bib.ds_nome_banco
FROM beneficiarios_informacoes_bancarias bib
WHERE bib.cpf_cnpj = spb.nr_cpf_cnpj
  AND bib.id_proposta = spb.id_proposta;
SQL
);
    }

    public function down(): void
    {
    }
}