<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskInc66161 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
            REFRESH MATERIALIZED VIEW sumario.relatorio_propostas_canceladas_propostas;
            REFRESH MATERIALIZED VIEW sumario.relatorio_vendas_sumario_parcelas;
            REFRESH MATERIALIZED VIEW sumario.relatorio_vendas_premio_adicional;
            REFRESH MATERIALIZED VIEW sumario.relatorio_vendas_premio_pago_segurado;

            DROP MATERIALIZED VIEW sumario.relatorio_vendas;

            CREATE MATERIALIZED VIEW sumario.relatorio_vendas AS (
                SELECT pr.id AS id_proposta,
                    pr.id_proposta_renovada,
                    pr.id_versao,
                    pr2.id_secao,
                    pr.nr_endosso,
                    pr.nr_apolice,
                    pr.dt_vigencia_inicio,
                    pr.dt_vigencia_fim,
                    pr.id_status,
                    pr.vl_custo_apolice,
                    pr.id_usuario_criacao,
                    pr.dt_vigencia_inicio_original,
                    pr.id_proposta_mae,
                    (((((((pg.id_ramo || '.'::text) || pd.id_seguradora) || '.'::text) || pr.id_produto) || '.'::text) || pr2.id) || '-'::text) || pr2.nr_digito_verificador AS id_proposta_composto,
                    CASE
                    WHEN seguro.nr_endosso(pr.id, pr.id_endosso) > 0::double precision THEN to_char(pr2.dt_transmissao, 'DD/MM/YYYY'::text)
                    ELSE pr.dt_transmissao
                    END AS dt_transmissao,
                    po.ds_coordenadas,
                    CASE
                    WHEN pg.id = 41 THEN 'FRUTAS E HORTALIÇAS'::character varying
                    WHEN pg.id_tipo_produto = 2 THEN
                    CASE
                    WHEN pg.tp_epoca_cultivo = 'I'::bpchar THEN ptp.ds_tipo_produto::text || ' RN - INVERNO'::text
                    ELSE ptp.ds_tipo_produto::text || ' RN - VERÃO'::text
                    END::character varying
                    WHEN pg.id_tipo_produto = 3 THEN
                    CASE
                    WHEN pg.tp_epoca_cultivo = 'I'::bpchar THEN 'GRÃOS PG - INVERNO'::text
                    ELSE 'GRÃOS PG - VERÃO'::text
                    END::character varying
                    ELSE ptp.ds_tipo_produto::character varying
                    END AS tipo_produto,
                    sp.id AS id_proponente,
                    co.ds_nome_abreviado AS ds_nome_fantasia,
                    pc.vl_comissao,
                    pd.ds_nome_produto,
                    sf.ds_nome_safra,
                    c.ds_nome_cultura,
                    st.ds_status,
                    pb.nu_boleto,
                    cr.ds_nome as ds_nome_contrato,
                    CASE
                    WHEN pb.fl_pago = true THEN 'Pago'::text
                    ELSE 'Pendente'::text
                    END AS fl_boleto_pago,
                    u.tp_usuario,
                    pre.ds_nome_preposto,
                    ist.vl_area AS total_area,
                    ist.vl_lmga AS total_lmga,
                    m1.ds_nome_municipio AS municipio_propriedade,
                    e1.ds_sigla AS estado_propriedade,
                    m1.nr_ibge,
                    pat.valor_total,
                    pat.valor_segurado,
                    pat.valor_subvencao_federal,
                    pat.valor_subvencao_estadual,
                    CASE
                        WHEN ss_subvencao_federal.ds_chave = 'SEM_SUBVENCAO'
                            THEN '-'
                        ELSE ss_subvencao_federal.ds_status
                    END AS subvencao_concedida,
                    CASE
                        WHEN subvencao_estadual.quest_sub_estadual = '' OR subvencao_estadual.quest_sub_estadual IS NULL
                            THEN 'Não'
                        ELSE subvencao_estadual.quest_sub_estadual
                    END AS quest_sub_estadual,
                    tmp_adicional.premio_adicional,
                    tmp_pg_segurado.premio_pago_segurado,
                    pco.vl_premio AS premio_principal,
                    pco.vl_franquia AS valor_franquia,
                    v_parcela.valor_primeira_parcela,
                    v_parcela.vencto_primeira_parcela,
                    q_pronamp.quest_pronamp,
                    q_organico.quest_organico,
                    pd.id_safra
                FROM seguro.propostas_endosso pe
                JOIN sumario.relatorio_propostas_canceladas_propostas pr ON pe.id_proposta = pr.id
                JOIN seguro.propostas pr2 ON pr2.id = pr.id_proposta_mae
                JOIN seguro.propostas_propriedades po ON po.id_proposta = pr.id
                JOIN sumario.itens_segurados ist ON ist.id_proposta = pr.id
                JOIN sumario.relatorio_vendas_sumario_parcelas pat ON pat.id_endosso = pr.id_endosso
                JOIN seguro.propostas_proponentes pp ON pp.id_proposta = pr.id
                JOIN seguro.proponentes sp ON sp.cpf_cnpj::text = pp.cpf_cnpj::text
                JOIN seguro.propostas_corretores pc ON pc.id_proposta = pr.id
                JOIN sistema.corretores co ON co.id_usuario = pc.id_usuario
                INNER JOIN seguro.propostas_subvencao sps ON sps.id_proposta = pr.id_proposta_mae
                JOIN produto.produtos pd ON pd.id = pr.id_produto
                JOIN produto.produtos_geral pg ON pg.id = pd.id_produto_geral
                JOIN produto.tipo_produto ptp ON ptp.id = pg.id_tipo_produto
                JOIN produto.culturas c ON c.id = pg.id_cultura
                JOIN produto.safras sf ON sf.id = pd.id_safra
                JOIN sistema.municipios m1 ON m1.id = po.id_municipio
                JOIN sistema.estados e1 ON e1.id = m1.id_estado
                JOIN sistema.status st ON st.id = pr.id_status
                INNER JOIN sistema.status ss_subvencao_federal ON ss_subvencao_federal.id = sps.id_status_subvencao_federal
                JOIN sistema.usuarios u ON pr.id_usuario_criacao = u.id
                LEFT JOIN (
                    SELECT
                        CASE
                            WHEN ss.ds_chave = 'SEM_SUBVENCAO'
                                THEN '-'
                            ELSE ss.ds_status
                        END AS quest_sub_estadual,
                        propostas_subvencao.id_proposta
                    FROM seguro.propostas_subvencao
                    INNER JOIN sistema.status ss ON ss.id = propostas_subvencao.id_status_subvencao_estadual)
                AS subvencao_estadual ON subvencao_estadual.id_proposta = pr.id_proposta_mae
                LEFT JOIN sumario.relatorio_vendas_premio_pago_segurado tmp_pg_segurado ON tmp_pg_segurado.id_endosso = pr.id_endosso
                LEFT JOIN seguro.propostas_consulta_cadin pca ON pca.fl_ativo = true AND pca.id_proposta = pr.id_proposta_mae
                LEFT JOIN seguro.proposta_boletos pb ON pb.id_proposta = pr.id AND pb.fl_ativo = true AND pb.id_versao = pr.id_versao AND pb.nr_parcela = 1
                LEFT JOIN ( SELECT
                CASE
                WHEN propostas_questionario.ds_resposta::text = '1'::text THEN 'Sim'::text
                ELSE 'Não'::text
                END AS quest_pronamp,
                propostas_questionario.id_proposta
                FROM seguro.propostas_questionario
                WHERE propostas_questionario.id_atributo_rn = 145) q_pronamp ON q_pronamp.id_proposta = pr.id
                LEFT JOIN ( SELECT
                CASE
                WHEN propostas_questionario.ds_resposta::text = '1'::text THEN 'Sim'::text
                ELSE 'Não'::text
                END AS quest_organico,
                propostas_questionario.id_proposta
                FROM seguro.propostas_questionario
                WHERE propostas_questionario.id_atributo_rn = 146) q_organico ON q_organico.id_proposta = pr.id
                LEFT JOIN ( SELECT
                CASE
                WHEN propostas_questionario.ds_resposta::text = '1'::text THEN 'Sim'::text
                ELSE 'Não'::text
                END AS quest_sub_estadual,
                propostas_questionario.id_proposta
                FROM seguro.propostas_questionario
                WHERE propostas_questionario.id_atributo_rn = 154) q_sub_est ON q_sub_est.id_proposta = pr.id_proposta_mae
                LEFT JOIN sumario.relatorio_vendas_premio_adicional tmp_adicional ON tmp_adicional.id_proposta = pr.id
                LEFT JOIN seguro.propostas_coberturas pco ON pco.fl_principal = true AND pco.id_proposta = pr.id AND pco.fl_del = false
                LEFT JOIN ( SELECT propostas_parcelas.id_proposta,
                propostas_parcelas.vl_segurado AS valor_primeira_parcela,
                to_char(propostas_parcelas.dt_vencimento::timestamp with time zone, 'DD/MM/YYYY'::text) AS vencto_primeira_parcela
                FROM seguro.propostas_parcelas
                WHERE propostas_parcelas.nr_parcela = 1) v_parcela ON v_parcela.id_proposta = pr.id
                LEFT JOIN sistema.prepostos pre ON pre.id_usuario = u.id
                LEFT JOIN seguro.propostas_contratos_resseguro pcr ON pcr.id_proposta = pr.id
                LEFT JOIN produto.contratos_resseguro cr ON cr.id = pcr.id_contrato_resseguro
                WHERE (pd.id_safra IN ( SELECT safras.id
                FROM produto.safras
                JOIN ( SELECT safras_1.id
                FROM produto.safras safras_1
                WHERE safras_1.fl_vigente = true) vigente ON vigente.id >= safras.id
                WHERE safras.id = vigente.id OR safras.id = (vigente.id - 1))) AND (st.ds_chave::text <> ALL (ARRAY['PROPOSTA_INCOMPLETA'::character varying::text, 'NAO_ENVIADA'::character varying::text, 'DEVOLVIDA'::character varying::text, 'PROPOSTA_CANCELADA'::character varying::text, 'APOLICE_CANCELADA'::character varying::text, 'ORCAMENTO_ENDOSSO'::character varying::text, 'ENDOSSO_INCOMPLETO'::character varying::text, 'ENDOSSO_ANULADO'::character varying::text, 'SOLICITACAO_CANCELADA'::character varying::text, 'SOLICITACAO_ENDOSSO'::character varying::text, 'SOLICITACAO_CANCELADA'::character varying::text])) AND pe.fl_proposta_vigente = true AND pc.id_usuario <> 487
                ORDER BY pr.id);

SQL
        );
    }

    public function down(): void
    {
        $this->execute(<<<SQL

            REFRESH MATERIALIZED VIEW sumario.relatorio_propostas_canceladas_propostas;
            REFRESH MATERIALIZED VIEW sumario.relatorio_vendas_sumario_parcelas;
            REFRESH MATERIALIZED VIEW sumario.relatorio_vendas_premio_adicional;
            REFRESH MATERIALIZED VIEW sumario.relatorio_vendas_premio_pago_segurado;
            
            DROP MATERIALIZED VIEW sumario.relatorio_vendas;

            CREATE MATERIALIZED VIEW sumario.relatorio_vendas AS (
                SELECT pr.id AS id_proposta,
                pr.id_proposta_renovada,
                pr.id_versao,
                pr2.id_secao,
                pr.nr_endosso,
                pr.nr_apolice,
                pr.dt_vigencia_inicio,
                pr.dt_vigencia_fim,
                pr.id_status,
                pr.vl_custo_apolice,
                pr.id_usuario_criacao,
                pr.dt_vigencia_inicio_original,
                pr.id_proposta_mae,
                (((((((pg.id_ramo || '.'::text) || pd.id_seguradora) || '.'::text) || pr.id_produto) || '.'::text) || pr2.id) || '-'::text) || pr2.nr_digito_verificador AS id_proposta_composto,
                CASE
                WHEN seguro.nr_endosso(pr.id, pr.id_endosso) > 0::double precision THEN to_char(pr2.dt_transmissao, 'DD/MM/YYYY'::text)
                ELSE pr.dt_transmissao
                END AS dt_transmissao,
                po.ds_coordenadas,
                CASE
                WHEN pg.id = 41 THEN 'FRUTAS E HORTALIÇAS'::character varying
                WHEN pg.id_tipo_produto = 2 THEN
                CASE
                WHEN pg.tp_epoca_cultivo = 'I'::bpchar THEN ptp.ds_tipo_produto::text || ' RN - INVERNO'::text
                ELSE ptp.ds_tipo_produto::text || ' RN - VERÃO'::text
                END::character varying
                WHEN pg.id_tipo_produto = 3 THEN
                CASE
                WHEN pg.tp_epoca_cultivo = 'I'::bpchar THEN 'GRÃOS PG - INVERNO'::text
                ELSE 'GRÃOS PG - VERÃO'::text
                END::character varying
                ELSE ptp.ds_tipo_produto::character varying
                END AS tipo_produto,
                sp.id AS id_proponente,
                co.ds_nome_abreviado AS ds_nome_fantasia,
                pc.vl_comissao,
                pd.ds_nome_produto,
                sf.ds_nome_safra,
                c.ds_nome_cultura,
                st.ds_status,
                pb.nu_boleto,
                cr.ds_nome as ds_nome_contrato,
                CASE
                WHEN pb.fl_pago = true THEN 'Pago'::text
                ELSE 'Pendente'::text
                END AS fl_boleto_pago,
                u.tp_usuario,
                pre.ds_nome_preposto,
                ist.vl_area AS total_area,
                ist.vl_lmga AS total_lmga,
                m1.ds_nome_municipio AS municipio_propriedade,
                e1.ds_sigla AS estado_propriedade,
                m1.nr_ibge,
                pat.valor_total,
                pat.valor_segurado,
                pat.valor_subvencao_federal,
                pat.valor_subvencao_estadual,
                CASE
                WHEN pat.valor_subvencao_federal > 0::numeric THEN
                CASE
                WHEN pca.fl_sucesso = true THEN 'Sim'::text
                ELSE 'Não'::text
                END
                ELSE ''::text
                END AS subvencao_concedida,
                tmp_adicional.premio_adicional,
                tmp_pg_segurado.premio_pago_segurado,
                pco.vl_premio AS premio_principal,
                pco.vl_franquia AS valor_franquia,
                v_parcela.valor_primeira_parcela,
                v_parcela.vencto_primeira_parcela,
                q_pronamp.quest_pronamp,
                q_organico.quest_organico,
                CASE
                WHEN q_sub_est.quest_sub_estadual = ''::text OR q_sub_est.quest_sub_estadual IS NULL THEN 'Não'::text
                ELSE q_sub_est.quest_sub_estadual
                END AS quest_sub_estadual,
                pd.id_safra
                FROM seguro.propostas_endosso pe
                JOIN sumario.relatorio_propostas_canceladas_propostas pr ON pe.id_proposta = pr.id
                JOIN seguro.propostas pr2 ON pr2.id = pr.id_proposta_mae
                JOIN seguro.propostas_propriedades po ON po.id_proposta = pr.id
                JOIN sumario.itens_segurados ist ON ist.id_proposta = pr.id
                JOIN sumario.relatorio_vendas_sumario_parcelas pat ON pat.id_endosso = pr.id_endosso
                JOIN seguro.propostas_proponentes pp ON pp.id_proposta = pr.id
                JOIN seguro.proponentes sp ON sp.cpf_cnpj::text = pp.cpf_cnpj::text
                JOIN seguro.propostas_corretores pc ON pc.id_proposta = pr.id
                JOIN sistema.corretores co ON co.id_usuario = pc.id_usuario
                JOIN produto.produtos pd ON pd.id = pr.id_produto
                JOIN produto.produtos_geral pg ON pg.id = pd.id_produto_geral
                JOIN produto.tipo_produto ptp ON ptp.id = pg.id_tipo_produto
                JOIN produto.culturas c ON c.id = pg.id_cultura
                JOIN produto.safras sf ON sf.id = pd.id_safra
                JOIN sistema.municipios m1 ON m1.id = po.id_municipio
                JOIN sistema.estados e1 ON e1.id = m1.id_estado
                JOIN sistema.status st ON st.id = pr.id_status
                JOIN sistema.usuarios u ON pr.id_usuario_criacao = u.id
                LEFT JOIN sumario.relatorio_vendas_premio_pago_segurado tmp_pg_segurado ON tmp_pg_segurado.id_endosso = pr.id_endosso
                LEFT JOIN seguro.propostas_consulta_cadin pca ON pca.fl_ativo = true AND pca.id_proposta = pr.id_proposta_mae
                LEFT JOIN seguro.proposta_boletos pb ON pb.id_proposta = pr.id AND pb.fl_ativo = true AND pb.id_versao = pr.id_versao AND pb.nr_parcela = 1
                LEFT JOIN ( SELECT
                CASE
                WHEN propostas_questionario.ds_resposta::text = '1'::text THEN 'Sim'::text
                ELSE 'Não'::text
                END AS quest_pronamp,
                propostas_questionario.id_proposta
                FROM seguro.propostas_questionario
                WHERE propostas_questionario.id_atributo_rn = 145) q_pronamp ON q_pronamp.id_proposta = pr.id
                LEFT JOIN ( SELECT
                CASE
                WHEN propostas_questionario.ds_resposta::text = '1'::text THEN 'Sim'::text
                ELSE 'Não'::text
                END AS quest_organico,
                propostas_questionario.id_proposta
                FROM seguro.propostas_questionario
                WHERE propostas_questionario.id_atributo_rn = 146) q_organico ON q_organico.id_proposta = pr.id
                LEFT JOIN ( SELECT
                CASE
                WHEN propostas_questionario.ds_resposta::text = '1'::text THEN 'Sim'::text
                ELSE 'Não'::text
                END AS quest_sub_estadual,
                propostas_questionario.id_proposta
                FROM seguro.propostas_questionario
                WHERE propostas_questionario.id_atributo_rn = 154) q_sub_est ON q_sub_est.id_proposta = pr.id_proposta_mae
                LEFT JOIN sumario.relatorio_vendas_premio_adicional tmp_adicional ON tmp_adicional.id_proposta = pr.id
                LEFT JOIN seguro.propostas_coberturas pco ON pco.fl_principal = true AND pco.id_proposta = pr.id AND pco.fl_del = false
                LEFT JOIN ( SELECT propostas_parcelas.id_proposta,
                propostas_parcelas.vl_segurado AS valor_primeira_parcela,
                to_char(propostas_parcelas.dt_vencimento::timestamp with time zone, 'DD/MM/YYYY'::text) AS vencto_primeira_parcela
                FROM seguro.propostas_parcelas
                WHERE propostas_parcelas.nr_parcela = 1) v_parcela ON v_parcela.id_proposta = pr.id
                LEFT JOIN sistema.prepostos pre ON pre.id_usuario = u.id
                LEFT JOIN seguro.propostas_contratos_resseguro pcr ON pcr.id_proposta = pr.id
                LEFT JOIN produto.contratos_resseguro cr ON cr.id = pcr.id_contrato_resseguro
                WHERE (pd.id_safra IN ( SELECT safras.id
                FROM produto.safras
                JOIN ( SELECT safras_1.id
                FROM produto.safras safras_1
                WHERE safras_1.fl_vigente = true) vigente ON vigente.id >= safras.id
                WHERE safras.id = vigente.id OR safras.id = (vigente.id - 1))) AND (st.ds_chave::text <> ALL (ARRAY['PROPOSTA_INCOMPLETA'::character varying::text, 'NAO_ENVIADA'::character varying::text, 'DEVOLVIDA'::character varying::text, 'PROPOSTA_CANCELADA'::character varying::text, 'APOLICE_CANCELADA'::character varying::text, 'ORCAMENTO_ENDOSSO'::character varying::text, 'ENDOSSO_INCOMPLETO'::character varying::text, 'ENDOSSO_ANULADO'::character varying::text, 'SOLICITACAO_CANCELADA'::character varying::text, 'SOLICITACAO_ENDOSSO'::character varying::text, 'SOLICITACAO_CANCELADA'::character varying::text])) AND pe.fl_proposta_vigente = true AND pc.id_usuario <> 487
                ORDER BY pr.id);
            
SQL
        );
    }
}