<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskInc58173 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute("CREATE TABLE manutencao.propostas_status_inc_58173 AS
                            SELECT *
                              FROM seguro.propostas_status
                             WHERE id IN (   SELECT id FROM (
                                             SELECT MAX(propostas_status.id) AS id
                                                  , id_proposta
                                               FROM seguro.propostas_status
                                              WHERE id_status = 8
                                                AND id_proposta IN ( 1002496, 1013290, 1013412, 1013424, 1016963, 1024955, 1025060, 1031538, 1033996
                                                                   , 1034786, 1035217, 1035558, 1035563, 1035571, 1035575, 1035579, 1035581, 1035802
                                                                   , 1035925, 1035975, 1036001, 1036029, 1036149, 1036173, 1036328, 1036341, 1036427
                                                                   , 1036435, 1036440, 1036446, 1036447, 1036550, 1036555, 1036883, 1036920, 1036962
                                                                   , 1036998, 1037048, 1037072, 1037093, 1037111, 1037124, 1037164, 1037256, 1037273
                                                                   , 1037312, 1037328, 1037363, 1037371, 1037476, 1037518, 1037541, 1037555, 1037633
                                                                   , 1037860, 1037917, 1037973, 1038001, 1038020, 1038025, 1038035, 1038058, 1038096
                                                                   , 1038129, 1038282, 1038304, 1038434, 1038531, 1038725, 1038747, 1038922, 1039324
                                                                   , 1039988, 1040558 
                                                                   )
                                           GROUP BY id_proposta
                                           ) AS interna
                                         )
        ");
        $this->execute("DELETE
                          FROM seguro.propostas_status
                         WHERE id IN (   SELECT id FROM (
                                         SELECT MAX(propostas_status.id) AS id
                                              , id_proposta
                                           FROM seguro.propostas_status
                                          WHERE id_status = 8
                                            AND id_proposta IN ( 1002496, 1013290, 1013412, 1013424, 1016963, 1024955, 1025060, 1031538, 1033996
                                                               , 1034786, 1035217, 1035558, 1035563, 1035571, 1035575, 1035579, 1035581, 1035802
                                                               , 1035925, 1035975, 1036001, 1036029, 1036149, 1036173, 1036328, 1036341, 1036427
                                                               , 1036435, 1036440, 1036446, 1036447, 1036550, 1036555, 1036883, 1036920, 1036962
                                                               , 1036998, 1037048, 1037072, 1037093, 1037111, 1037124, 1037164, 1037256, 1037273
                                                               , 1037312, 1037328, 1037363, 1037371, 1037476, 1037518, 1037541, 1037555, 1037633
                                                               , 1037860, 1037917, 1037973, 1038001, 1038020, 1038025, 1038035, 1038058, 1038096
                                                               , 1038129, 1038282, 1038304, 1038434, 1038531, 1038725, 1038747, 1038922, 1039324
                                                               , 1039988, 1040558 
                                                               )
                                       GROUP BY id_proposta
                                       ) AS interna
                                     )     
        ");
    }

    public function down(): void
    {
        $prodQuery = $this->getQueryBuilder();
        $prodQuery->select(['id_usuario_criacao'   => 'id_usuario_criacao'
                          , 'id_usuario_alteracao' => 'id_usuario_alteracao'
                          , 'dt_criacao'           => 'dt_criacao'
                          , 'dt_alteracao'         => 'dt_alteracao'
                          , 'fl_ativo'             => 'fl_ativo'
                          , 'fl_del'               => 'fl_del'
                          , 'id_proposta'          => 'id_proposta'
                          , 'id_status'            => 'id_status'
                          , 'ds_observacao'        => 'ds_observacao'
                          , 'id'                   => 'id'
                          ])
                  ->from('manutencao.propostas_status_inc_58173')
                  ->where(['id >' => 1]);
        $insere = $this->getQueryBuilder();
        $st = $insere
            ->insert(['id_usuario_criacao'
                    , 'id_usuario_alteracao'
                    , 'dt_criacao'
                    , 'dt_alteracao'
                    , 'fl_ativo'
                    , 'fl_del'
                    , 'id_proposta'
                    , 'id_status'
                    , 'ds_observacao'
                    , 'id'
                    ])
            ->into('seguro.propostas_status')
            ->values($prodQuery)
            ->execute();
 
        $this->table('manutencao.propostas_status_inc_58173')
             ->drop()
             ->save();
    }
}