<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class Task2210141634 extends AbstractMigration
{
    public function up(): void {
        $this->execute(<<<SQL
ALTER TABLE log.propostas_transmissao ADD COLUMN dt_envio timestamp without time zone;
ALTER TABLE log.propostas_transmissao ADD COLUMN dt_retorno timestamp without time zone;
DROP VIEW log.v_propostas_transmissao;
CREATE OR REPLACE VIEW log.v_propostas_transmissao AS
SELECT lpt.id,
    lpt.id_proposta,
    lpt.ds_retorno,
    lpt.fl_sucesso,
    lpt.ds_xml_transmissao,
    lpt.id_versao,
    lpt.ds_tipo_transmissao,
    lpt.dt_criacao,
    lpt.dt_envio,
    lpt.dt_retorno,
    st.ds_chave,
    u.ds_nome_usuario,
    ( SELECT count(*) AS count
           FROM seguro.propostas_versoes
          WHERE propostas_versoes.id_proposta = lpt.id_proposta AND propostas_versoes.id <= lpt.id_versao) AS versao
   FROM log.propostas_transmissao lpt,
    sistema.status st,
    sistema.usuarios u
  WHERE st.id = lpt.id_status AND u.id = lpt.id_usuario_criacao;
SQL
        );
    }

    public function down(): void {
        $this->execute(<<<SQL
ALTER TABLE log.propostas_transmissao DROP COLUMN dt_envio;
ALTER TABLE log.propostas_transmissao DROP COLUMN dt_retorno;
CREATE OR REPLACE VIEW log.v_propostas_transmissao AS
SELECT lpt.id,
    lpt.id_proposta,
    lpt.ds_retorno,
    lpt.fl_sucesso,
    lpt.ds_xml_transmissao,
    lpt.id_versao,
    lpt.ds_tipo_transmissao,
    lpt.dt_criacao,
    st.ds_chave,
    u.ds_nome_usuario,
    ( SELECT count(*) AS count
           FROM seguro.propostas_versoes
          WHERE propostas_versoes.id_proposta = lpt.id_proposta AND propostas_versoes.id <= lpt.id_versao) AS versao
   FROM log.propostas_transmissao lpt,
    sistema.status st,
    sistema.usuarios u
  WHERE st.id = lpt.id_status AND u.id = lpt.id_usuario_criacao;
SQL
        );
    }
}