<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskInc46655 extends AbstractMigration
{
    public function up(): void
    {
        $this->upSumarioParcelas();
        $this->upSumarioRelatorioVendasSumarioParcelas();
    }

    public function down(): void
    {
        $this->downSumarioRelatorioVendasSumarioParcelas();
        $this->downSumarioParcelas();
    }

    private function upSumarioParcelas(): void
    {
        $this->execute(<<<SQL
            CREATE OR REPLACE FUNCTION seguro.sumario_parcelas(id_endosso integer)
            RETURNS void
            LANGUAGE plpgsql
            AS \$function\$DECLARE
            v_where varchar;
            vid_endosso ALIAS FOR $1;
            BEGIN
            DROP TABLE IF EXISTS sumario_parcelas;
            
            v_where := '';
            IF vid_endosso > 0 THEN
                CREATE TEMP TABLE sumario_parcelas as (
                SELECT
                    pp.id_endosso,
                    sum(pp.vl_total) AS valor_total,
                    sum(pp.vl_segurado) AS valor_segurado,
                    sum(pp.vl_subvencao_federal) AS valor_subvencao_federal,
                    sum(pp.vl_subvencao_estadual) AS valor_subvencao_estadual
                FROM
                    seguro.propostas_parcelas pp,
                    seguro.propostas pr,
                    sistema.status st
                WHERE
                    pp.id_proposta = pr.id AND
                    st.id = pr.id_status AND
                    (st.ds_chave::text <> ALL (ARRAY['PROPOSTA_INCOMPLETA'::character varying, 'NAO_ENVIADA'::character varying, 'DEVOLVIDA'::character varying, 'PROPOSTA_CANCELADA'::character varying, 'ENDOSSO_ANULADO'::character varying, 'ORCAMENTO_ENDOSSO'::character varying, 'ENDOSSO_INCOMPLETO'::character varying, 'SOLICITACAO_ENDOSSO'::character varying]::text[])) AND
                    pp.id_endosso = vid_endosso
                GROUP BY
                    pp.id_endosso
                );
            ELSE
                CREATE TEMP TABLE sumario_parcelas as (
                SELECT
                    pp.id_endosso,
                    sum(pp.vl_total) AS valor_total,
                    sum(pp.vl_segurado) AS valor_segurado,
                    sum(pp.vl_subvencao_federal) AS valor_subvencao_federal,
                    sum(pp.vl_subvencao_estadual) AS valor_subvencao_estadual
                FROM
                    seguro.propostas_parcelas pp,
                    seguro.propostas pr,
                    sistema.status st
                WHERE
                    pp.id_proposta = pr.id AND
                    st.id = pr.id_status AND
                    (st.ds_chave::text <> ALL (ARRAY['PROPOSTA_INCOMPLETA'::character varying, 'NAO_ENVIADA'::character varying, 'DEVOLVIDA'::character varying, 'PROPOSTA_CANCELADA'::character varying, 'ENDOSSO_ANULADO'::character varying, 'ORCAMENTO_ENDOSSO'::character varying, 'ENDOSSO_INCOMPLETO'::character varying, 'SOLICITACAO_ENDOSSO'::character varying]::text[]))
                GROUP BY
                    pp.id_endosso
                );
            END IF;
            END;
            \$function\$

SQL
        );
    }

    private function downSumarioParcelas(): void
    {
        $this->execute(<<<SQL
            CREATE OR REPLACE FUNCTION seguro.sumario_parcelas(id_endosso integer)
            RETURNS void
            LANGUAGE plpgsql
            AS \$function\$DECLARE
            v_where varchar;
            vid_endosso ALIAS FOR $1;
            BEGIN
            DROP TABLE IF EXISTS sumario_parcelas;
            
            v_where := '';
            IF vid_endosso > 0 THEN
                CREATE TEMP TABLE sumario_parcelas as (
                SELECT
                    pp.id_endosso,
                    sum(pp.vl_total) AS valor_total,
                    sum(pp.vl_segurado) AS valor_segurado,
                    sum(pp.vl_subvencao_federal) AS valor_subvencao_federal,
                    sum(pp.vl_subvencao_estadual) AS valor_subvencao_estadual
                FROM
                    seguro.propostas_parcelas pp,
                    seguro.propostas pr,
                    sistema.status st
                WHERE
                    pp.id_proposta = pr.id AND
                    st.id = pr.id_status AND
                    (st.ds_chave::text <> ALL (ARRAY['PROPOSTA_INCOMPLETA'::character varying, 'NAO_ENVIADA'::character varying, 'DEVOLVIDA'::character varying, 'PROPOSTA_CANCELADA'::character varying, 'ENDOSSO_ANULADO'::character varying, 'ORCAMENTO_ENDOSSO'::character varying, 'ENDOSSO_INCOMPLETO'::character varying]::text[])) AND
                    pp.id_endosso = vid_endosso
                GROUP BY
                    pp.id_endosso
                );
            ELSE
                CREATE TEMP TABLE sumario_parcelas as (
                SELECT
                    pp.id_endosso,
                    sum(pp.vl_total) AS valor_total,
                    sum(pp.vl_segurado) AS valor_segurado,
                    sum(pp.vl_subvencao_federal) AS valor_subvencao_federal,
                    sum(pp.vl_subvencao_estadual) AS valor_subvencao_estadual
                FROM
                    seguro.propostas_parcelas pp,
                    seguro.propostas pr,
                    sistema.status st
                WHERE
                    pp.id_proposta = pr.id AND
                    st.id = pr.id_status AND
                    (st.ds_chave::text <> ALL (ARRAY['PROPOSTA_INCOMPLETA'::character varying, 'NAO_ENVIADA'::character varying, 'DEVOLVIDA'::character varying, 'PROPOSTA_CANCELADA'::character varying, 'ENDOSSO_ANULADO'::character varying, 'ORCAMENTO_ENDOSSO'::character varying, 'ENDOSSO_INCOMPLETO'::character varying]::text[])) 
                GROUP BY
                    pp.id_endosso
                );
            END IF;
            END;
            \$function\$

SQL
        );
    }

    private function upSumarioRelatorioVendasSumarioParcelas(): void
    {
        $this->execute(<<<SQL
            DROP MATERIALIZED VIEW IF EXISTS sumario.relatorio_vendas;
            DROP MATERIALIZED VIEW IF EXISTS sumario.relatorio_vendas_sumario_parcelas;

            CREATE MATERIALIZED VIEW sumario.relatorio_vendas_sumario_parcelas AS (
                SELECT
                    pp.id_endosso,
                    sum(pp.vl_total) AS valor_total,
                    sum(pp.vl_segurado) AS valor_segurado,
                    sum(pp.vl_subvencao_federal) AS valor_subvencao_federal,
                    sum(pp.vl_subvencao_estadual) AS valor_subvencao_estadual
                FROM
                    seguro.propostas_parcelas pp,
                    seguro.propostas pr,
                    sistema.status st
                WHERE
                    pp.id_proposta = pr.id AND
                    st.id = pr.id_status AND
                    (st.ds_chave::text <> ALL (ARRAY['PROPOSTA_INCOMPLETA'::character varying, 'NAO_ENVIADA'::character varying, 'DEVOLVIDA'::character varying, 'PROPOSTA_CANCELADA'::character varying, 'ENDOSSO_ANULADO'::character varying, 'ORCAMENTO_ENDOSSO'::character varying, 'ENDOSSO_INCOMPLETO'::character varying, 'SOLICITACAO_ENDOSSO'::character varying]::text[]))
                GROUP BY
                    pp.id_endosso
            );
SQL
        );

        $this->createSumarioRelatorioVendas();
    }

    private function downSumarioRelatorioVendasSumarioParcelas(): void
    {
        $this->execute(<<<SQL
            DROP MATERIALIZED VIEW IF EXISTS sumario.relatorio_vendas;
            DROP MATERIALIZED VIEW IF EXISTS sumario.relatorio_vendas_sumario_parcelas;

            CREATE MATERIALIZED VIEW sumario.relatorio_vendas_sumario_parcelas AS (
                SELECT
                    pp.id_endosso,
                    sum(pp.vl_total) AS valor_total,
                    sum(pp.vl_segurado) AS valor_segurado,
                    sum(pp.vl_subvencao_federal) AS valor_subvencao_federal,
                    sum(pp.vl_subvencao_estadual) AS valor_subvencao_estadual
                FROM
                    seguro.propostas_parcelas pp,
                    seguro.propostas pr,
                    sistema.status st
                WHERE
                    pp.id_proposta = pr.id AND
                    st.id = pr.id_status AND
                    (st.ds_chave::text <> ALL (ARRAY['PROPOSTA_INCOMPLETA'::character varying, 'NAO_ENVIADA'::character varying, 'DEVOLVIDA'::character varying, 'PROPOSTA_CANCELADA'::character varying, 'ENDOSSO_ANULADO'::character varying, 'ORCAMENTO_ENDOSSO'::character varying, 'ENDOSSO_INCOMPLETO'::character varying]::text[]))
                GROUP BY
                    pp.id_endosso
            );
SQL
        );

        $this->createSumarioRelatorioVendas();
    }

    private function createSumarioRelatorioVendas(): void
    {
        $this->execute(<<<SQL

REFRESH MATERIALIZED VIEW sumario.relatorio_propostas_canceladas_propostas;

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,
          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
    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, 'NAO_ENVIADA'::character varying, 'DEVOLVIDA'::character varying, 'PROPOSTA_CANCELADA'::character varying, 'APOLICE_CANCELADA'::character varying, 'ORCAMENTO_ENDOSSO'::character varying, 'ENDOSSO_INCOMPLETO'::character varying, 'ENDOSSO_ANULADO'::character varying, 'SOLICITACAO_CANCELADA'::character varying, 'SOLICITACAO_ENDOSSO'::character varying]::text[])) AND pe.fl_proposta_vigente = true AND pc.id_usuario <> 487
    ORDER BY pr.id
);
SQL
        );
    }
}