<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class Task2301201333 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL

            CREATE TABLE seguro.propostas_devolucoes (
                id SERIAL,
                id_proposta INTEGER NOT NULL,
                id_informacao_bancaria INTEGER,
                id_proponente INTEGER,
                id_beneficiario INTEGER,
                id_forma_pagamento INTEGER,
                dt_programada DATE,
                fl_conta_conjunta BOOLEAN DEFAULT false,
                CONSTRAINT pk_propostas_devolucoes PRIMARY KEY (id),
                CONSTRAINT fk_propostas_devolucoes_proposta FOREIGN KEY (id_proposta) REFERENCES seguro.propostas (id),
                CONSTRAINT fk_propostas_devolucoes_informacao_bancaria FOREIGN KEY (id_informacao_bancaria) REFERENCES sistema.informacoes_bancarias (id),
                CONSTRAINT fk_propostas_devolucoes_proponente FOREIGN KEY (id_proponente) REFERENCES seguro.proponentes (id),
                CONSTRAINT fk_propostas_devolucoes_beneficiario FOREIGN KEY (id_beneficiario) REFERENCES seguro.beneficiarios (id),
                CONSTRAINT fk_propostas_devolucoes_forma_pagamento FOREIGN KEY (id_forma_pagamento) REFERENCES sistema.formas_pagamento (id)
            ) INHERITS (public.campos_default);

            CREATE TABLE seguro.propostas_devolucoes_status (
                id SERIAL,
                id_proposta_devolucao INTEGER NOT NULL,
                id_status INTEGER NOT NULL,
                ds_observacao TEXT,
                fl_observacao_automatica BOOLEAN DEFAULT false,
                CONSTRAINT pk_propostas_devolucoes_status PRIMARY KEY (id),
                CONSTRAINT fk_propostas_devolucoes_status_proposta_devolucao FOREIGN KEY (id_proposta_devolucao) REFERENCES seguro.propostas_devolucoes (id),
                CONSTRAINT fk_propostas_devolucoes_status_proposta_status FOREIGN KEY (id_status) REFERENCES sistema.status (id)
            ) INHERITS (public.campos_default);

            CREATE TABLE seguro.propostas_devolucoes_arquivos (
                id SERIAL,
                id_proposta_devolucao INTEGER NOT NULL,
                ds_nome_arquivo NOME NOT NULL,
                CONSTRAINT pk_propostas_devolucao_arquivos PRIMARY KEY (id),
                CONSTRAINT fk_propostas_devolucao_arquivos_proposta_devolucao FOREIGN KEY (id_proposta_devolucao) REFERENCES seguro.propostas_devolucoes (id)
            ) INHERITS (public.campos_default);
            
            ALTER TABLE seguro.propostas_parcelas ADD COLUMN fl_liberar_devolucao BOOLEAN DEFAULT false;
            
            INSERT INTO sistema.tipo_status (id_usuario_criacao, dt_criacao, ds_tipo_status, ds_chave)
                VALUES (2068, now(), 'Devolução de prêmio', 'DEVOLUCAO_PREMIO');

            INSERT INTO sistema.status (id_usuario_criacao, dt_criacao, ds_status, cd_status, ds_chave, id_tipo_status)
                VALUES (2068, now(), 'Aguardando envio dos dados bancários', 1, 'AGUARDANDO_ENVIO_DADOS_BANCARIOS', (SELECT id FROM sistema.tipo_status WHERE ds_chave = 'DEVOLUCAO_PREMIO')),
                       (2068, now(), 'Em processamento da Seguradora', 2, 'EM_PROCESSAMENTO_SEGURADORA', (SELECT id FROM sistema.tipo_status WHERE ds_chave = 'DEVOLUCAO_PREMIO')),
                       (2068, now(), 'Agendada', 3, 'AGENDADA', (SELECT id FROM sistema.tipo_status WHERE ds_chave = 'DEVOLUCAO_PREMIO')),
                       (2068, now(), 'Devolução Efetuada', 4, 'DEVOLUCAO_EFETUADA', (SELECT id FROM sistema.tipo_status WHERE ds_chave = 'DEVOLUCAO_PREMIO')),
                       (2068, now(), 'Pendência Corretor', 5, 'PENDENCIA_CORRETOR', (SELECT id FROM sistema.tipo_status WHERE ds_chave = 'DEVOLUCAO_PREMIO')),
                       (2068, now(), 'Em análise', 6, 'EM_ANALISE', (SELECT id FROM sistema.tipo_status WHERE ds_chave = 'DEVOLUCAO_PREMIO')),
                       (2068, now(), 'Em análise - Com termo', 7, 'EM_ANALISE_COM_TERMO', (SELECT id FROM sistema.tipo_status WHERE ds_chave = 'DEVOLUCAO_PREMIO')),
                       (2068, now(), 'Em processamento da Seguradora - Com termo', 8, 'EM_PROCESSAMENTO_SEGURADORA_COM_TERMO', (SELECT id FROM sistema.tipo_status WHERE ds_chave = 'DEVOLUCAO_PREMIO')),
                       (2068, now(), 'Agendada - Com termo', 9, 'AGENDADA_COM_TERMO', (SELECT id FROM sistema.tipo_status WHERE ds_chave = 'DEVOLUCAO_PREMIO')),
                       (2068, now(), 'Devolução Efetuada - Com Termo', 10, 'DEVOLUCAO_EFETUADAO_COM_TERMO', (SELECT id FROM sistema.tipo_status WHERE ds_chave = 'DEVOLUCAO_PREMIO')),
                       (2068, now(), 'Finalizado - Crédito Reaproveitado ', 11, 'FINALIZADO_CREDITO_REAPROVEITADO', (SELECT id FROM sistema.tipo_status WHERE ds_chave = 'DEVOLUCAO_PREMIO'));

            INSERT INTO sistema.acao (id_usuario_criacao, dt_criacao, ds_label, ds_module, ds_controller, ds_action, id_parent, ds_descricao, fl_tipo)
                VALUES (2068, now(), 'Devoluções Prêmio', 'seguro', 'devolucao', 'index', 27, 'Devolução de prêmio', 'M');

            INSERT INTO sistema.acao (id_usuario_criacao, dt_criacao, ds_label, ds_module, ds_controller, ds_action, ds_param, id_parent, ds_image, fl_tipo, nr_ordem)
                VALUES (2068, now(), 'Histórico', 'seguro', 'devolucao', 'historico', 'id', (SELECT id FROM sistema.acao WHERE ds_module = 'seguro' AND ds_controller = 'devolucao' AND ds_action = 'index'), 'add_att_20x20.png', 'L', 0);

            INSERT INTO sistema.acao (id_usuario_criacao, dt_criacao, ds_label, ds_module, ds_controller, ds_action, ds_param, id_parent, ds_image, fl_tipo, nr_ordem)
                VALUES (2068, now(), 'Arquivos', 'seguro', 'devolucao', 'arquivos', 'id', (SELECT id FROM sistema.acao WHERE ds_module = 'seguro' AND ds_controller = 'devolucao' AND ds_action = 'index'), 'attach.png', 'L', 1);

            INSERT INTO sistema.acao (id_usuario_criacao, dt_criacao, ds_label, ds_module, ds_controller, ds_action, ds_param, id_parent, ds_image, fl_tipo, nr_ordem)
                VALUES (2068, now(), 'Dados Bancários', 'seguro', 'devolucao', 'dadosbancarios', 'id', (SELECT id FROM sistema.acao WHERE ds_module = 'seguro' AND ds_controller = 'devolucao' AND ds_action = 'index'), 'edit.png', 'L', 2);

            --INSERT INTO sistema.roles_acao (id_role, id_acao) VALUES (6, (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'index'));
            --INSERT INTO sistema.roles_acao (id_role, id_acao) VALUES (6, (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'historico'));
            --INSERT INTO sistema.roles_acao (id_role, id_acao) VALUES (6, (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'arquivos'));
            --INSERT INTO sistema.roles_acao (id_role, id_acao) VALUES (6, (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'dadosbancarios'));
            
            DELETE FROM sistema.usuarios_acao WHERE id_acao = (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'index');
            DELETE FROM sistema.usuarios_acao WHERE id_acao = (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'historico');
            DELETE FROM sistema.usuarios_acao WHERE id_acao = (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'arquivos');
            DELETE FROM sistema.usuarios_acao WHERE id_acao = (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'dadosbancarios');

            INSERT INTO sistema.usuarios_acao (id_usuario_criacao, dt_criacao, id_usuario, id_acao)
                SELECT 2068 as id_usuario_criacao,
                       now() as dt_criacao,
                       id_usuario,
                       (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'index') as id_acao
                  FROM sistema.funcionarios
                 WHERE id_empresa = 20 
                   AND fl_ativo is true;

            INSERT INTO sistema.usuarios_acao (id_usuario_criacao, dt_criacao, id_usuario, id_acao)
                SELECT 2068 as id_usuario_criacao,
                       now() as dt_criacao,
                       id_usuario,
                       (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'historico') as id_acao
                  FROM sistema.funcionarios
                 WHERE id_empresa = 20 
                   AND fl_ativo is true;

            INSERT INTO sistema.usuarios_acao (id_usuario_criacao, dt_criacao, id_usuario, id_acao)
                SELECT 2068 as id_usuario_criacao,
                       now() as dt_criacao,
                       id_usuario,
                       (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'arquivos') as id_acao
                  FROM sistema.funcionarios
                 WHERE id_empresa = 20 
                   AND fl_ativo is true;

            INSERT INTO sistema.usuarios_acao (id_usuario_criacao, dt_criacao, id_usuario, id_acao)
                SELECT 2068 as id_usuario_criacao,
                       now() as dt_criacao,
                       id_usuario,
                       (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'dadosbancarios') as id_acao
                  FROM sistema.funcionarios
                 WHERE id_empresa = 20 
                   AND fl_ativo is true;
SQL
        );

        $this->upViewPropostasBoletos();
        $this->upMigracaoDados();
    }

    public function down(): void
    {
        $this->downViewPropostasBoletos();

        $this->execute(<<<SQL

            CREATE temp TABLE propostas_devolucoes_temp AS (SELECT id_informacao_bancaria FROM seguro.propostas_devolucoes WHERE id_informacao_bancaria is not null);
            DROP TABLE seguro.propostas_devolucoes_arquivos;
            DROP TABLE seguro.propostas_devolucoes_status;
            DROP TABLE seguro.propostas_devolucoes;

            --DELETE FROM sistema.roles_acao WHERE id_role = 6 AND id_acao = (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'index');
            --DELETE FROM sistema.roles_acao WHERE id_role = 6 AND id_acao = (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'historico');
            --DELETE FROM sistema.roles_acao WHERE id_role = 6 AND id_acao = (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'arquivos');
            --DELETE FROM sistema.roles_acao WHERE id_role = 6 AND id_acao = (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'dadosbancarios');

            DELETE FROM sistema.usuarios_acao WHERE id_acao = (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'index');
            DELETE FROM sistema.usuarios_acao WHERE id_acao = (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'historico');
            DELETE FROM sistema.usuarios_acao WHERE id_acao = (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'arquivos');
            DELETE FROM sistema.usuarios_acao WHERE id_acao = (SELECT id FROM sistema.acao WHERE ds_controller = 'devolucao' AND ds_action = 'dadosbancarios');

            DELETE FROM sistema.acao WHERE ds_label = 'Dados Bancários' AND ds_module = 'seguro' AND ds_controller = 'devolucao' and ds_action = 'dadosbancarios';
            DELETE FROM sistema.acao WHERE ds_label = 'Arquivos' AND ds_module = 'seguro' AND ds_controller = 'devolucao' and ds_action = 'arquivos';
            DELETE FROM sistema.acao WHERE ds_label = 'Histórico' AND ds_module = 'seguro' AND ds_controller = 'devolucao' and ds_action = 'historico';
            DELETE FROM sistema.acao WHERE ds_label = 'Devoluções Prêmio' AND ds_module = 'seguro' AND ds_controller = 'devolucao' and ds_action = 'index';
            DELETE FROM sistema.status where id_tipo_status = (SELECT id FROM sistema.tipo_status WHERE ds_chave = 'DEVOLUCAO_PREMIO');
            DELETE FROM sistema.tipo_status WHERE ds_chave = 'DEVOLUCAO_PREMIO';
            ALTER TABLE seguro.propostas_parcelas DROP COLUMN fl_liberar_devolucao;

            DELETE FROM sistema.informacoes_bancarias WHERE id IN (SELECT id_informacao_bancaria FROM propostas_devolucoes_temp);
            DROP TABLE propostas_devolucoes_temp;
SQL
        );
    }

    private function upViewPropostasBoletos()
    {
        $this->execute(<<<SQL
            CREATE OR REPLACE VIEW seguro.v_propostas_boletos AS
                SELECT pp.id_proposta,
                    pp.nr_parcela,
                    pb.nu_boleto,
                    pp.dt_vencimento,
                    pp.vl_segurado,
                    pp.vl_subvencao_federal,
                    pp.vl_subvencao_estadual,
                    pv.ds_identificador_seguradora,
                    pv.id AS id_versao,
                        CASE
                            WHEN pb.fl_pago = true THEN 'Sim'::text
                            ELSE 'Não'::text
                        END AS fl_pago,
                    ( SELECT count(*) AS count
                        FROM seguro.propostas_versoes
                        WHERE propostas_versoes.id_proposta = pb.id_proposta AND propostas_versoes.id <= pb.id_versao) AS versao,
                    ('Boleto_'::text || pb.id_versao) || '.pdf'::text AS documento,
                    ((('Boleto_'::text || pb.id_proposta) || '_'::text) || pr.ds_identificador_seguradora::text) || '.pdf'::text AS documento_parcela,
                    pr.id_versao AS id_versao_proposta,
                    pp.fl_liberar_devolucao
                FROM seguro.propostas pr
                    JOIN seguro.propostas_parcelas pp ON pp.id_proposta = pr.id
                    LEFT JOIN seguro.proposta_boletos pb ON pr.id = pb.id_proposta AND pp.nr_parcela = pb.nr_parcela
                    LEFT JOIN seguro.propostas_versoes pv ON pv.id = pb.id_versao
                ORDER BY pp.id_proposta, pb.id_versao, pp.nr_parcela
SQL
        );
    }

    private function downViewPropostasBoletos()
    {
        $this->execute(<<<SQL
            DROP VIEW seguro.v_propostas_boletos;
            CREATE OR REPLACE VIEW seguro.v_propostas_boletos AS
                SELECT pp.id_proposta,
                    pp.nr_parcela,
                    pb.nu_boleto,
                    pp.dt_vencimento,
                    pp.vl_segurado,
                    pp.vl_subvencao_federal,
                    pp.vl_subvencao_estadual,
                    pv.ds_identificador_seguradora,
                    pv.id AS id_versao,
                        CASE
                            WHEN pb.fl_pago = true THEN 'Sim'::text
                            ELSE 'Não'::text
                        END AS fl_pago,
                    ( SELECT count(*) AS count
                        FROM seguro.propostas_versoes
                        WHERE propostas_versoes.id_proposta = pb.id_proposta AND propostas_versoes.id <= pb.id_versao) AS versao,
                    ('Boleto_'::text || pb.id_versao) || '.pdf'::text AS documento,
                    ((('Boleto_'::text || pb.id_proposta) || '_'::text) || pr.ds_identificador_seguradora::text) || '.pdf'::text AS documento_parcela,
                    pr.id_versao AS id_versao_proposta
                FROM seguro.propostas pr
                    JOIN seguro.propostas_parcelas pp ON pp.id_proposta = pr.id
                    LEFT JOIN seguro.proposta_boletos pb ON pr.id = pb.id_proposta AND pp.nr_parcela = pb.nr_parcela
                    LEFT JOIN seguro.propostas_versoes pv ON pv.id = pb.id_versao
                ORDER BY pp.id_proposta, pb.id_versao, pp.nr_parcela
SQL
        );
    }

    private function upMigracaoDados()
    {
        $sql = "
            SELECT pr.id as id_proposta,
                   p.id as id_proponente,
                   ibp.fl_conta_conjunta,
                   ib.id as id_informacao_bancaria,
                   ib.id_banco,
                   ib.ds_agencia,
                   ib.ds_conta,
                   ib.ds_tipo_conta,
                   ib.nu_digito_agencia,
                   ib.nu_digito_conta,
                   b.id as id_beneficiario,
                   CASE WHEN seguro.nr_endosso(pr.id, pr.id_endosso) = 0 
                        THEN (SELECT sum(vl_segurado) FROM seguro.propostas_parcelas pp WHERE pp.id_proposta = pr.id AND (fl_pago IS true))
                        ELSE (SELECT sum(vl_segurado) * -1 FROM seguro.propostas_parcelas pp WHERE pp.id_proposta = pr.id)
                   END as vl_previsto 
              FROM seguro.propostas pr
              JOIN produto.produtos prod ON prod.id = pr.id_produto
              JOIN seguro.propostas_proponentes pp ON pp.id_proposta = pr.id
              JOIN seguro.proponentes p ON p.cpf_cnpj = pp.cpf_cnpj
              LEFT JOIN seguro.informacoes_bancarias_propostas ibp ON ibp.id_proposta = pr.id_proposta_mae
              LEFT JOIN sistema.informacoes_bancarias ib ON ib.id = ibp.id_informacoes_bancarias
              LEFT JOIN seguro.propostas_beneficiarios pb ON pb.id_proposta = pr.id AND pb.fl_favorecido_autorizacao_credito is true
              LEFT JOIN seguro.beneficiarios b ON b.cpf_cnpj = pb.nr_cpf_cnpj
            WHERE prod.id_safra in (26, 25)
              AND ((
                        seguro.nr_endosso(pr.id, pr.id_endosso) = 0 
                        AND pr. id_status= 5
                        AND EXISTS(SELECT DISTINCT 1 FROM seguro.propostas_parcelas spp WHERE spp.id_proposta = pr.id AND (spp.fl_pago IS true))
                    ) OR (
                        seguro.nr_endosso(pr.id, pr.id_endosso) > 0 
                        AND pr.id_status IN(8, 212, 13) 
                        AND EXISTS(SELECT 1 FROM seguro.propostas_endosso_opcoes peo WHERE peo.id_proposta = pr.id AND peo.id_endosso_opcao = 2)
                        AND EXISTS(SELECT DISTINCT 1 FROM seguro.propostas_parcelas spp WHERE spp.id_proposta = pr.id AND vl_segurado < 0)
                  ));
        ";

        $query = $this->query($sql);
        $rs = $query->fetchAll();

        foreach ($rs as $row) {

            if (!empty($row['id_informacao_bancaria'])) {
                $this->execute("
                    INSERT INTO sistema.informacoes_bancarias (id_usuario_criacao, dt_criacao, id_banco, ds_agencia, ds_conta, ds_tipo_conta, nu_digito_agencia, nu_digito_conta)
                         VALUES (2068, now(), {$row['id_banco']}, '{$row['ds_agencia']}', '{$row['ds_conta']}', '{$row['ds_tipo_conta']}', '{$row['nu_digito_agencia']}', '{$row['nu_digito_conta']}')
                ");
            }

            $idInformacaoBancaria = (!empty($row['id_informacao_bancaria'])) ? "currval('sistema.informacoes_bancarias_id_seq')" : 'null';
            $idProponente = (empty($row['id_beneficiario'])) ? $row['id_proponente'] : 'null';
            $idBeneficiario = (!empty($row['id_beneficiario'])) ? $row['id_beneficiario'] : 'null';
            $flContaConjunta = ($row['fl_conta_conjunta']) ? "'t'" : "'f'";

            $this->execute("
                INSERT INTO seguro.propostas_devolucoes (id_usuario_criacao, dt_criacao, id_proposta, id_informacao_bancaria, id_proponente, id_beneficiario, id_forma_pagamento, fl_conta_conjunta)
                     VALUES (2068, now(), {$row['id_proposta']}, {$idInformacaoBancaria}, {$idProponente}, {$idBeneficiario}, 1, $flContaConjunta)
            ");

            $idStatus = "( SELECT s.id FROM sistema.status s JOIN sistema.tipo_status ts ON ts.id = s.id_tipo_status WHERE ";

            if (!empty($row['id_beneficiario'])) {
                $idStatus .= " upper(s.ds_chave) = 'EM_ANALISE_COM_TERMO' ";
                $idStatus .= " AND upper(ts.ds_chave) = 'DEVOLUCAO_PREMIO' ";
            } else if ($row['id_informacao_bancaria']) {
                $idStatus .= " upper(s.ds_chave) = 'EM_PROCESSAMENTO_SEGURADORA' ";
                $idStatus .= " AND upper(ts.ds_chave) = 'DEVOLUCAO_PREMIO' ";
            } else {
                $idStatus .= " upper(s.ds_chave) = 'AGUARDANDO_ENVIO_DADOS_BANCARIOS' ";
                $idStatus .= " AND upper(ts.ds_chave) = 'DEVOLUCAO_PREMIO' ";
            }

            $idStatus .= " )";
            
            $this->execute("
                INSERT INTO seguro.propostas_devolucoes_status (id_usuario_criacao, dt_criacao, id_proposta_devolucao, id_status, fl_observacao_automatica)
                     VALUES (2068, now(), currval('seguro.propostas_devolucoes_id_seq'), {$idStatus}, 't')
            ");
        }
    }
}