<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskInc69907 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
            REFRESH MATERIALIZED VIEW sumario.relatorio_propostas_canceladas_propostas;
            REFRESH MATERIALIZED VIEW sumario.relatorio_propostas_canceladas_parcela_restituicao;
            REFRESH MATERIALIZED VIEW sumario.relatorio_propostas_canceladas_parcela_cobranca;
            REFRESH MATERIALIZED VIEW sumario.relatorio_propostas_canceladas_sumario_parcelas;

            DROP MATERIALIZED VIEW IF EXISTS sumario.relatorio_propostas_canceladas;

            CREATE MATERIALIZED VIEW sumario.relatorio_propostas_canceladas AS (
                SELECT seguro.fn_proposta_motivos_cancelamento(pr.id) AS motivo_cancelamento,
                    pr.id AS id_proposta,
                    pr.id_proposta_mae,
                    pr.id_secao,
                    pr.id_proposta_mae_completo,
                    pr.nr_endosso,
                    pr.id_proposta_renovada,
                    pr.nr_apolice,
                    pr.dt_vigencia_inicio_original AS dt_vigencia_inicio,
                    pr.dt_vigencia_fim,
                    pr.id_status,
                    pr.vl_custo_apolice,
                    pr.id_usuario_criacao,
                    po.ds_coordenadas,
                    (((((((pg.id_ramo || '.'::text) || pd.id_seguradora) || '.'::text) || pr.id_produto) || '.'::text) || pr.id) || '-'::text) || pr.nr_digito_verificador AS id_proposta_composto,
                        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,
                    u.tp_usuario,
                    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,
                    ( SELECT sum(propostas_coberturas.vl_premio) AS sum
                            FROM seguro.propostas_coberturas
                            WHERE propostas_coberturas.id_proposta = pr.id AND propostas_coberturas.fl_principal = false) AS premio_adicional,
                    ( SELECT sum(propostas_coberturas.vl_premio) AS sum
                            FROM seguro.propostas_coberturas
                            WHERE propostas_coberturas.id_proposta = pr.id AND propostas_coberturas.fl_principal = true) AS premio_principal,
                    ( SELECT propostas_coberturas.vl_franquia
                            FROM seguro.propostas_coberturas
                            WHERE propostas_coberturas.id_proposta = pr.id AND propostas_coberturas.fl_principal = true) AS valor_franquia,
                    ( SELECT propostas_parcelas.vl_segurado
                            FROM seguro.propostas_parcelas
                            WHERE propostas_parcelas.id_proposta = pr.id AND propostas_parcelas.nr_parcela = 1) AS valor_primeira_parcela,
                    ( SELECT to_char(propostas_parcelas.dt_vencimento::timestamp with time zone, 'DD/MM/YYYY'::text) AS to_char
                            FROM seguro.propostas_parcelas
                            WHERE propostas_parcelas.id_proposta = pr.id AND propostas_parcelas.nr_parcela = 1) AS vencto_primeira_parcela,
                    ps.dt_criacao AS dt_cancelamento,
                        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,
                    ps.ds_observacao,
                        CASE
                            WHEN st.ds_chave::text = ANY (ARRAY['PROPOSTA_CANCELADA'::character varying, 'DEVOLVIDA'::character varying]::text[]) THEN 0::numeric
                            ELSE COALESCE(tmp_cobranca.vl_pago, 0::numeric) + COALESCE(tmp_restituicao.vl_restituido, 0::numeric)
                        END AS vl_retido,
                    pd.id_safra,
                    cr.ds_nome as contrato_resseguro
                    FROM seguro.propostas_endosso pe,
                    sumario.relatorio_propostas_canceladas_propostas pr
                    LEFT JOIN seguro.proposta_boletos pb ON pb.id_proposta = pr.id AND pb.fl_ativo = true
                    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
                    LEFT JOIN seguro.propostas_consulta_cadin pca ON pca.fl_ativo = true AND pca.id_proposta = pr.id_proposta_mae
                    LEFT JOIN seguro.propostas_status ps ON ps.id_proposta = pr.id
                    LEFT JOIN sumario.relatorio_propostas_canceladas_parcela_restituicao tmp_restituicao ON tmp_restituicao.id_endosso = pr.id_endosso
                    LEFT JOIN sumario.relatorio_propostas_canceladas_parcela_cobranca tmp_cobranca ON tmp_cobranca.id_endosso = pr.id_endosso,
                    seguro.propostas_propriedades po,
                    sumario.itens_segurados ist,
                    sumario.relatorio_propostas_canceladas_sumario_parcelas pat,
                    seguro.propostas_proponentes pp,
                    seguro.proponentes sp,
                    seguro.propostas_corretores pc,
                    sistema.corretores co,
                    produto.produtos pd,
                    produto.produtos_geral pg,
                    produto.tipo_produto ptp,
                    produto.culturas c,
                    produto.safras sf,
                    sistema.municipios m1,
                    sistema.estados e1,
                    sistema.status st,
                    sistema.usuarios u
                    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 = ANY (ARRAY['PROPOSTA_CANCELADA'::character varying, 'DEVOLVIDA'::character varying, 'APOLICE_CANCELADA'::character varying]::text[])) AND pc.id_usuario <> 487 AND pe.id_proposta = pr.id AND pe.fl_proposta_vigente = true AND ps.fl_ativo = true AND ist.id_proposta = pr.id AND pat.id_endosso = pr.id_endosso AND po.id_proposta = pr.id AND pp.id_proposta = pr.id AND sp.cpf_cnpj::text = pp.cpf_cnpj::text AND pc.id_proposta = pr.id AND co.id_usuario = pc.id_usuario AND pd.id = pr.id_produto AND pg.id = pd.id_produto_geral AND ptp.id = pg.id_tipo_produto AND c.id = pg.id_cultura AND sf.id = pd.id_safra AND m1.id = po.id_municipio AND e1.id = m1.id_estado AND st.id = pr.id_status AND pr.id_usuario_criacao = u.id AND ps.id_status = st.id AND ps.id_proposta = pr.id
                    ORDER BY pr.id
            );

            REFRESH MATERIALIZED VIEW sumario.relatorio_propostas_canceladas;
SQL
        );
    }

    public function down():void
    {
        $this->execute(<<<SQL
            REFRESH MATERIALIZED VIEW sumario.relatorio_propostas_canceladas_propostas;
            REFRESH MATERIALIZED VIEW sumario.relatorio_propostas_canceladas_parcela_restituicao;
            REFRESH MATERIALIZED VIEW sumario.relatorio_propostas_canceladas_parcela_cobranca;
            REFRESH MATERIALIZED VIEW sumario.relatorio_propostas_canceladas_sumario_parcelas;

            DROP MATERIALIZED VIEW IF EXISTS sumario.relatorio_propostas_canceladas;

            CREATE MATERIALIZED VIEW sumario.relatorio_propostas_canceladas AS (
            SELECT seguro.fn_proposta_motivos_cancelamento(pr.id) AS motivo_cancelamento,
                pr.id AS id_proposta,
                pr.id_proposta_mae,
                pr.id_secao,
                pr.id_proposta_mae_completo,
                pr.nr_endosso,
                pr.id_proposta_renovada,
                pr.nr_apolice,
                pr.dt_vigencia_inicio_original AS dt_vigencia_inicio,
                pr.dt_vigencia_fim,
                pr.id_status,
                pr.vl_custo_apolice,
                pr.id_usuario_criacao,
                po.ds_coordenadas,
                (((((((pg.id_ramo || '.'::text) || pd.id_seguradora) || '.'::text) || pr.id_produto) || '.'::text) || pr.id) || '-'::text) || pr.nr_digito_verificador AS id_proposta_composto,
                    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,
                u.tp_usuario,
                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,
                ( SELECT sum(propostas_coberturas.vl_premio) AS sum
                        FROM seguro.propostas_coberturas
                        WHERE propostas_coberturas.id_proposta = pr.id AND propostas_coberturas.fl_principal = false) AS premio_adicional,
                ( SELECT sum(propostas_coberturas.vl_premio) AS sum
                        FROM seguro.propostas_coberturas
                        WHERE propostas_coberturas.id_proposta = pr.id AND propostas_coberturas.fl_principal = true) AS premio_principal,
                ( SELECT propostas_coberturas.vl_franquia
                        FROM seguro.propostas_coberturas
                        WHERE propostas_coberturas.id_proposta = pr.id AND propostas_coberturas.fl_principal = true) AS valor_franquia,
                ( SELECT propostas_parcelas.vl_segurado
                        FROM seguro.propostas_parcelas
                        WHERE propostas_parcelas.id_proposta = pr.id AND propostas_parcelas.nr_parcela = 1) AS valor_primeira_parcela,
                ( SELECT to_char(propostas_parcelas.dt_vencimento::timestamp with time zone, 'DD/MM/YYYY'::text) AS to_char
                        FROM seguro.propostas_parcelas
                        WHERE propostas_parcelas.id_proposta = pr.id AND propostas_parcelas.nr_parcela = 1) AS vencto_primeira_parcela,
                ps.dt_criacao AS dt_cancelamento,
                    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,
                ps.ds_observacao,
                    CASE
                        WHEN st.ds_chave::text = ANY (ARRAY['PROPOSTA_CANCELADA'::character varying, 'DEVOLVIDA'::character varying]::text[]) THEN 0::numeric
                        ELSE COALESCE(tmp_cobranca.vl_pago, 0::numeric) + COALESCE(tmp_restituicao.vl_restituido, 0::numeric)
                    END AS vl_retido,
                pd.id_safra
                FROM seguro.propostas_endosso pe,
                sumario.relatorio_propostas_canceladas_propostas pr
                LEFT JOIN seguro.proposta_boletos pb ON pb.id_proposta = pr.id AND pb.fl_ativo = true
                LEFT JOIN seguro.propostas_consulta_cadin pca ON pca.fl_ativo = true AND pca.id_proposta = pr.id_proposta_mae
                LEFT JOIN seguro.propostas_status ps ON ps.id_proposta = pr.id
                LEFT JOIN sumario.relatorio_propostas_canceladas_parcela_restituicao tmp_restituicao ON tmp_restituicao.id_endosso = pr.id_endosso
                LEFT JOIN sumario.relatorio_propostas_canceladas_parcela_cobranca tmp_cobranca ON tmp_cobranca.id_endosso = pr.id_endosso,
                seguro.propostas_propriedades po,
                sumario.itens_segurados ist,
                sumario.relatorio_propostas_canceladas_sumario_parcelas pat,
                seguro.propostas_proponentes pp,
                seguro.proponentes sp,
                seguro.propostas_corretores pc,
                sistema.corretores co,
                produto.produtos pd,
                produto.produtos_geral pg,
                produto.tipo_produto ptp,
                produto.culturas c,
                produto.safras sf,
                sistema.municipios m1,
                sistema.estados e1,
                sistema.status st,
                sistema.usuarios u
                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 = ANY (ARRAY['PROPOSTA_CANCELADA'::character varying, 'DEVOLVIDA'::character varying, 'APOLICE_CANCELADA'::character varying]::text[])) AND pc.id_usuario <> 487 AND pe.id_proposta = pr.id AND pe.fl_proposta_vigente = true AND ps.fl_ativo = true AND ist.id_proposta = pr.id AND pat.id_endosso = pr.id_endosso AND po.id_proposta = pr.id AND pp.id_proposta = pr.id AND sp.cpf_cnpj::text = pp.cpf_cnpj::text AND pc.id_proposta = pr.id AND co.id_usuario = pc.id_usuario AND pd.id = pr.id_produto AND pg.id = pd.id_produto_geral AND ptp.id = pg.id_tipo_produto AND c.id = pg.id_cultura AND sf.id = pd.id_safra AND m1.id = po.id_municipio AND e1.id = m1.id_estado AND st.id = pr.id_status AND pr.id_usuario_criacao = u.id AND ps.id_status = st.id AND ps.id_proposta = pr.id
                ORDER BY pr.id
            );
SQL
        );
    }
}