<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

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

DROP MATERIALIZED VIEW sumario.relatorio_vendas;
DROP MATERIALIZED VIEW sumario.relatorio_propostas_canceladas;
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 EXISTS (
			SELECT ds_chave
			FROM sistema.status
			WHERE ds_chave IN ('DEVOLVIDA','PROPOSTA_CANCELADA','APOLICE_CANCELADA')
			AND status.id = pr.id_status
		)
	  GROUP BY pp.id_endosso
);

CREATE MATERIALIZED VIEW sumario.relatorio_propostas_canceladas AS (
	SELECT seguro.fn_proposta_motivos_cancelamento(pr.id) as motivo_cancelamento,
		 pr.id as id_proposta
		,id_proposta_mae
		,pr.id_secao
		,id_proposta_mae_completo
		,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||'.'||pd.id_seguradora||'.'||pr.id_produto||'.'||pr.id||'-'||pr.nr_digito_verificador as id_proposta_composto

		,case 
			when pg.id = 41 --ID_TOMATE_INDUSTRIA
				then 'FRUTAS E HORTALIÇAS'
			when pg.id_tipo_produto = 2 --GRAOS 
				then case 
						when pg.tp_epoca_cultivo = 'I'
							then ptp.ds_tipo_produto || ' RN - INVERNO' 
						else ptp.ds_tipo_produto || ' RN - VERÃO' 
					 end
			when pg.id_tipo_produto = 3 --MULTIRRISCO 
				then case 
						when pg.tp_epoca_cultivo = 'I'
							then 'GRÃOS PG - INVERNO' 
						else 'GRÃOS PG - VERÃO' 
					 end
			else ptp.ds_tipo_produto 
		 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(vl_premio) from seguro.propostas_coberturas where id_proposta = pr.id and fl_principal=false) as premio_adicional
		,(select sum(vl_premio) from seguro.propostas_coberturas where id_proposta = pr.id and fl_principal=true) as premio_principal
		,(select vl_franquia from seguro.propostas_coberturas where id_proposta = pr.id and fl_principal=true) as valor_franquia

		,(select vl_segurado from seguro.propostas_parcelas where id_proposta = pr.id and nr_parcela = 1) as valor_primeira_parcela
		,(select to_char(dt_vencimento, 'DD/MM/YYYY') from seguro.propostas_parcelas where id_proposta = pr.id and nr_parcela = 1) as vencto_primeira_parcela

		,ps.dt_criacao as dt_cancelamento

		,case when pat.valor_subvencao_federal > 0 then
			case when pca.fl_sucesso = true then 'Sim' else 'Não' end
		else
			''
		end as subvencao_concedida,
		ps.ds_observacao as ds_observacao

		,case when st.ds_chave in ('PROPOSTA_CANCELADA', 'DEVOLVIDA')  then 0 else coalesce(tmp_cobranca.vl_pago,0) + coalesce(tmp_restituicao.vl_restituido,0) 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 = id_proposta_mae
		left join seguro.propostas_status ps on ps.id_proposta = pr.id
		left join sumario.relatorio_propostas_canceladas_parcela_restituicao as tmp_restituicao on tmp_restituicao.id_endosso = pr.id_endosso
		left join sumario.relatorio_propostas_canceladas_parcela_cobranca as 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 id
					   FROM produto.safras
					  WHERE fl_vigente = TRUE
			  ) AS vigente
				ON vigente.id >= safras.id
			 WHERE safras.id = vigente.id
				OR safras.id = vigente.id -1	

		)
		and st.ds_chave in ('PROPOSTA_CANCELADA', 'DEVOLVIDA', 'APOLICE_CANCELADA')
		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    = pp.cpf_cnpj
		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
);

CREATE MATERIALIZED VIEW sumario.relatorio_vendas AS (
	SELECT pr.id AS id_proposta
		, pr.id_proposta_renovada
		, pr.id_versao
		, pr2.id_secao --trazer sempre a informacao da proposta mae
		, 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||'.'||pd.id_seguradora||'.'||pr.id_produto||'.'||pr2.id||'-'||pr2.nr_digito_verificador as id_proposta_composto
		, CASE WHEN seguro.nr_endosso(pr.id, pr.id_endosso) > 0 THEN
				TO_CHAR(pr2.dt_transmissao, 'DD/MM/YYYY')
			ELSE
				pr.dt_transmissao
			END AS dt_transmissao
		, po.ds_coordenadas
		, case 
			when pg.id = 41 -- ID_TOMATE_INDUSTRIA
				then 'FRUTAS E HORTALIÇAS'
			when pg.id_tipo_produto = 2 -- GRAOS 
				then case 
						when pg.tp_epoca_cultivo = 'I'
							then ptp.ds_tipo_produto || ' RN - INVERNO' 
						else ptp.ds_tipo_produto || ' RN - VERÃO' 
					 end
			when pg.id_tipo_produto = 3 -- MULTIRRISCO 
				then case 
						when pg.tp_epoca_cultivo = 'I'
							then 'GRÃOS PG - INVERNO' 
						else 'GRÃOS PG - VERÃO' 
					 end
			else ptp.ds_tipo_produto 
		 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'
			ELSE
				'Pendente'
			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 THEN
				CASE WHEN pca.fl_sucesso = true THEN
					'Sim'
				ELSE
					'Não'
				END
			ELSE
				''
			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 = '' OR q_sub_est.quest_sub_estadual IS NULL THEN
				'Não'
			ELSE
				q_sub_est.quest_sub_estadual
			END AS quest_sub_estadual
		, pd.id_safra
		 FROM seguro.propostas_endosso pe
	INNER JOIN sumario.relatorio_propostas_canceladas_propostas pr
		   ON pe.id_proposta = pr.id
	INNER JOIN seguro.propostas pr2
		   ON pr2.id = pr.id_proposta_mae
	INNER JOIN seguro.propostas_propriedades po
		   ON po.id_proposta = pr.id
	INNER JOIN sumario.itens_segurados ist
		   ON ist.id_proposta = pr.id
	INNER JOIN sumario.relatorio_propostas_canceladas_sumario_parcelas pat
		   ON pat.id_endosso = pr.id_endosso
	INNER JOIN seguro.propostas_proponentes pp
		   ON pp.id_proposta = pr.id
	INNER JOIN seguro.proponentes sp
		   ON sp.cpf_cnpj = pp.cpf_cnpj
	INNER JOIN seguro.propostas_corretores pc
		   ON pc.id_proposta = pr.id
	INNER JOIN sistema.corretores co
		   ON co.id_usuario = pc.id_usuario
	INNER JOIN produto.produtos pd
		   ON pd.id = pr.id_produto
	INNER JOIN produto.produtos_geral pg
		   ON pg.id = pd.id_produto_geral
	INNER JOIN produto.tipo_produto ptp
		   ON ptp.id = pg.id_tipo_produto
	INNER JOIN produto.culturas c
		   ON c.id = pg.id_cultura
	INNER JOIN produto.safras sf
		   ON sf.id = pd.id_safra
	INNER JOIN sistema.municipios m1
		   ON m1.id = po.id_municipio
	INNER JOIN sistema.estados e1
		   ON e1.id = m1.id_estado
	INNER JOIN sistema.status st
		   ON st.id = pr.id_status
	INNER JOIN sistema.usuarios u
		   ON pr.id_usuario_criacao = u.id
	LEFT JOIN sumario.relatorio_vendas_premio_pago_segurado AS 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 ds_resposta='1' THEN
						'Sim'
					  ELSE
						'Não'
					  END AS quest_pronamp
					, id_proposta
				 FROM seguro.propostas_questionario
				WHERE id_atributo_rn = 145) AS q_pronamp
		   ON q_pronamp.id_proposta = pr.id
	LEFT JOIN (SELECT CASE WHEN ds_resposta='1' THEN
						'Sim'
					  ELSE
						'Não'
					  END AS quest_organico
					, id_proposta
				 FROM seguro.propostas_questionario
				WHERE id_atributo_rn = 146) AS q_organico
		   ON q_organico.id_proposta = pr.id
	LEFT JOIN (SELECT CASE WHEN ds_resposta='1' THEN
						'Sim'
					  ELSE
						'Não'
					  END AS quest_sub_estadual
					, id_proposta
				 FROM seguro.propostas_questionario
				WHERE id_atributo_rn = 154) AS q_sub_est
		   ON q_sub_est.id_proposta = pr.id_proposta_mae
	LEFT JOIN sumario.relatorio_vendas_premio_adicional AS 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 id_proposta
					, vl_segurado AS valor_primeira_parcela
					, TO_CHAR(dt_vencimento, 'DD/MM/YYYY') AS vencto_primeira_parcela
				 FROM seguro.propostas_parcelas
				WHERE nr_parcela = 1) AS 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 id
					   FROM produto.safras
					  WHERE fl_vigente = TRUE
			  ) AS vigente
				ON vigente.id >= safras.id
			 WHERE safras.id = vigente.id
				OR safras.id = vigente.id -1					
			)
		  AND st.ds_chave not in ('PROPOSTA_INCOMPLETA' , 'NAO_ENVIADA' , 'DEVOLVIDA' , 'PROPOSTA_CANCELADA' , 'APOLICE_CANCELADA', 'ORCAMENTO_ENDOSSO', 'ENDOSSO_INCOMPLETO', 'ENDOSSO_ANULADO', 'SOLICITACAO_CANCELADA', 'SOLICITACAO_ENDOSSO')
		  AND pe.fl_proposta_vigente = true
		  AND pc.id_usuario  <> 487
	 ORDER BY pr.id
);
SQL
        );
    }

    public function down(): void {
        return;
    }
}