<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskInc53080 extends AbstractMigration
{
    public function up(): void
    {
        $rs = $this->fetchAll(<<<SQL
            SELECT
                spd.id AS id_proposta_devolucao,
                sprop.id AS id_proponente
            FROM
                seguro.propostas sp
            INNER JOIN seguro.propostas_endosso spe
                ON spe.id_proposta = sp.id AND spe.id_proposta = sp.id_proposta_mae
                    AND spe.fl_proposta_vigente IS TRUE
            INNER JOIN seguro.propostas_proponentes spp
                ON spp.id_proposta = sp.id
            INNER JOIN seguro.proponentes sprop
                ON sprop.cpf_cnpj = spp.cpf_cnpj
            INNER JOIN sistema.status ss
                ON ss.id = sp.id_status
            INNER JOIN seguro.propostas_devolucoes spd
                ON spd.id_proposta = sp.id
                    AND spd.id_proponente IS NULL
                    AND spd.id_beneficiario IS NOT NULL
            INNER JOIN seguro.propostas_devolucoes_status spds
                ON spds.id_proposta_devolucao = spd.id
                    AND spds.fl_ativo IS TRUE
            INNER JOIN seguro.propostas_cancelamento spc
                ON spc.id_proposta = sp.id
            WHERE
                sp.id_status = 5
                AND spds.id_status NOT IN(287, 286, 292, 293)
                AND spc.id_motivo_cancelamento = 29
SQL);

        $strUpdates = $idsPropostasDevolucoes = '';
        foreach ($rs as $row) {
            $idsPropostasDevolucoes .= !empty($idsPropostasDevolucoes) ? ", {$row['id_proposta_devolucao']}" : $row['id_proposta_devolucao'];
            $strUpdates .= "UPDATE seguro.propostas_devolucoes SET id_usuario_alteracao = 2068, id_beneficiario = NULL, id_proponente = {$row['id_proponente']} WHERE id = {$row['id_proposta_devolucao']};";
        }

        if (!empty($strUpdates)) {
            $this->execute(<<<SQL
                CREATE TABLE manutencao.propostas_devolucoes_backup_inc_53080 AS
                SELECT
                    *
                FROM
                    seguro.propostas_devolucoes
                WHERE
                    id IN ({$idsPropostasDevolucoes});
SQL
            );

            $this->execute($strUpdates);
        }
    }

    public function down(): void
    {
        $rs = $this->fetchAll(<<<SQL
            SELECT
                id,
                id_beneficiario
            FROM
                manutencao.propostas_devolucoes_backup_inc_53080
SQL
        );
        
        $strUpdates = '';
        foreach ($rs as $row) {
            $strUpdates .= "UPDATE seguro.propostas_devolucoes SET id_usuario_alteracao = 2068, id_beneficiario = {$row['id_beneficiario']}, id_proponente = NULL WHERE id = {$row['id']};";
        }

        if (!empty($strUpdates)) {
            $this->execute($strUpdates);
            $this->execute('DROP TABLE manutencao.propostas_devolucoes_backup_inc_53080;');
        }
    }
}