<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskReservasV5 extends AbstractMigration
{
    public function up() : void {
        $this->execute(<<<SQL
DROP MATERIALIZED VIEW sumario.relatorio_propostas_canceladas;
DROP MATERIALIZED VIEW sumario.relatorio_vendas;
DROP MATERIALIZED VIEW sumario.relatorio_propostas_canceladas_sumario_parcelas;

CREATE MATERIALIZED VIEW sumario.relatorio_propostas_canceladas_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
    WHERE pp.id_proposta = pr.id AND (not EXISTS ( SELECT status.ds_chave
             FROM sistema.status
            WHERE (status.ds_chave::text = ANY (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 status.id = pr.id_status))
    GROUP BY pp.id_endosso
);

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_propostas_canceladas_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
);

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
        );
    }
}