<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskReservasV2 extends AbstractMigration
{
    public function up() : void {
        $this->execute(<<<SQL
CREATE MATERIALIZED VIEW sumario.relatorio_sinistralidadepg AS (
SELECT
	pr.id_proposta_mae AS id_proposta
	,pr.id_endosso
	,sp.id AS id_processo
	,pp.ds_nome_proponente
	,m.ds_nome_municipio
	,m.nr_ibge
	,es.ds_sigla
	,e.ds_nome_fantASia
	,pd.ds_nome_produto
	,pd.id_safra
	,its.id AS id_item_segurado
	,its.nr_item_segurado
	,its.ds_item_segurado
	,its.vl_area
	,its.vl_lmga
	,its.vl_rENDimento_tONelada
	,v.ds_nome_variedade
	,co.ds_nome_cobertura
	,co.id AS id_cobertura
	,pc.vl_taxa
	,pc.vl_franquia
	,pg.id_cultura
	,cu.ds_nome_cultura
	,itsc.vl_producao_garantida
FROM
	seguro.propostas pr
	,seguro.propostas_endosso pre 
	,seguro.itens_segurados its
	,seguro.itens_segurados_complemento itsc
	,seguro.propostas_proponentes pp
	,seguro.propostas_propriedades po
	,seguro.propostas_coberturas pc
	,produto.coberturas co
	,sistema.municipios m
	,sistema.estados es
	,produto.produtos pd
	,produto.produtos_geral pg
	,produto.culturas cu
	,produto.variedades v
	,sinistro.processos sp
	,sinistro.processos_empresas pe
	,sistema.empresas e
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 pre.id_proposta = pr.id 
	AND pre.fl_proposta_vigente = true
	AND pc.fl_principal = true
	AND pe.fl_ativo = true
	AND pg.id_tipo_produto = 3
	AND its.id_proposta = pr.id
	AND itsc.id_item_segurado = its.id
	AND pd.id = pr.id_produto
	AND pg.id = pd.id_produto_geral
	AND cu.id = pg.id_cultura
	AND pp.id_proposta = pr.id
	AND po.id_proposta = pr.id
	AND pc.id_proposta = pr.id
	AND co.id = pc.id_cobertura
	AND m.id = po.id_municipio
	AND es.id = m.id_estado
	AND sp.id_proposta = pr.id
	AND its.id_variedade = v.id
	AND pe.id_processo = sp.id
	AND e.id = pe.id_empresa
	AND its.vl_lmga > 0
	AND EXISTS (
		SELECT
			1
		FROM
			sinistro.laudos_finais_itens_segurados lfis,
			sinistro.laudos_finais lf
		WHERE
				lf.id = lfis.id_laudo_final
			AND lf.id_cobertura = pc.id_cobertura
			AND (lfis.fl_nao_vistoriada=false or (lfis.fl_nao_vistoriada=true AND lfis.id_motivo_quadra_nao_vistoriada=1))
			AND lfis.id_item_segurado = its.id
	)
ORDER BY
	pr.id, pc.id_cobertura, its.nr_item_segurado
);

CREATE MATERIALIZED VIEW sumario.relatorio_sinistralidadepg_itens_segurados AS (
SELECT
	lf.id
	,lfis.id AS id_laudo_final_item_segurado
	,lfis.vl_percent_perdas
	,lfis.dt_vistoria
	,lfis.dt_criacao
	,lfis.vl_percent_colhido
	,lfis.vl_area_sINistrada
	,lfis.vl_percent_area_afetada
	,lfis.vl_rENDimento_final_lavoura
	,lfis.vl_percent_perdas_chocho
	,lfis.vl_percent_colhido_chocho
	,lfis.id_motivo_quadra_nao_vistoriada
	,co.ds_nome_cobertura
	,CASE WHEN lf003.vl_graos_podres IS NOT NULL 
		THEN lf003.vl_graos_podres 
		ELSE lf034.vl_graos_podres 
		END AS vl_graos_podres
	,CASE WHEN lf003.vl_graos_brotados IS NOT NULL 
		THEN lf003.vl_graos_brotados 
		ELSE lf034.vl_graos_brotados 
		END AS vl_graos_brotados
	,CASE WHEN lf003.vl_pragas_doencas_outros IS NOT NULL 
		THEN lf003.vl_pragas_doencas_outros 
		ELSE lf034.vl_pragas_doencas_outros 
		END AS vl_pragas_doencas_outros
	,its.nr_item_segurado
	,pr.id_endosso
FROM
	sinistro.laudos_finais  lf
	INNER JOIN sinistro.laudos_finais_itens_segurados lfis ON lfis.id_laudo_final = lf.id
	INNER JOIN seguro.itens_segurados its ON its.id = lfis.id_item_segurado
	INNER JOIN produto.coberturas co ON co.id = lf.id_cobertura
	INNER JOIN seguro.propostas pr ON  pr.id = its.id_proposta
	INNER JOIN sumario.relatorio_sinistralidadepg pg ON pg.nr_item_segurado = its.nr_item_segurado AND pg.id_endosso = pr.id_endosso
	LEFT JOIN regulacao.lf003_itens_segurados lf003 ON lf003.id_laudo_final_item_segurado = lfis.id
	LEFT JOIN regulacao.lf034_itens_segurados lf034 ON lf034.id_laudo_final_item_segurado = lfis.id
ORDER BY
		lfis.dt_vistoria
);

CREATE MATERIALIZED VIEW sumario.relatorio_processos AS (
SELECT
   sp.id AS processo,
   pr.id_proposta_mae AS proposta,
   pr.id AS id_proposta_original,
   pr.nr_apolice AS apolice,
   sp.id_processo_seguradora,
   sprop.id AS id_proponente,
   sp.dt_criacao AS data_abertura,
   pd.ds_nome_produto AS produto,
   s1.cd_status||' - '||s1.ds_chave AS status_proposta,
   s2.cd_status||' - '||s2.ds_chave AS status_processo,
   e.ds_nome_fantASia AS empresa,
   u.ds_nome_usuario AS ds_nome_usuario_responsavel_liquidacao,
   pd.id_safra
FROM
   sinistro.processos sp
   LEFT JOIN sistema.usuarios u ON u.id = sp.id_usuario_responsavel_liquidacao,
   sinistro.processos_empresas pe,
   sistema.empresas e,
   seguro.propostas pr,
   seguro.propostas_endosso pre,
   produto.produtos pd,
   seguro.propostas_proponentes pp,
   seguro.proponentes sprop,
   sistema.status s1,
   sistema.status s2
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 pe.fl_ativo = true
   AND sp.id_proposta = pr.id
   AND pre.id_proposta = pr.id AND pre.fl_proposta_vigente = true
   AND pe.id_processo = sp.id
   AND e.id = pe.id_empresa
   AND pd.id = pr.id_produto
   AND pp.id_proposta = pr.id
   AND sprop.cpf_cnpj = pp.cpf_cnpj
   AND s1.id = pr.id_status
   AND s2.id = sp.id_status
ORDER BY
   sp.dt_criacao
);
				   
CREATE MATERIALIZED VIEW sumario.relatorio_sinistralidade_itens_segurados_vistorias AS (
SELECT
	DISTINCT(its.nr_item_segurado),
	pr.id_endosso
FROM
	sinistro.laudos_finais_itens_segurados lfis
	INNER JOIN seguro.itens_segurados its ON its.id = lfis.id_item_segurado
	INNER JOIN seguro.propostas pr ON pr.id = its.id_proposta
WHERE lfis.fl_nao_vistoriada=false
);

CREATE MATERIALIZED VIEW sumario.relatorio_sinistralidade AS (
SELECT
	  pr.id_proposta_mae AS id_proposta
	 ,pr.id_endosso
	 ,sp.id AS id_processo
	 ,pp.ds_nome_proponente
	 ,m.ds_nome_municipio
	 ,m.nr_ibge
	 ,es.ds_sigla
	 ,e.ds_nome_fantASia
	 ,pd.ds_nome_produto
	 ,pd.id_safra
	 ,its.id AS id_item_segurado
	 ,its.nr_item_segurado
	 ,its.ds_item_segurado
	 ,its.vl_area
	 ,its.vl_lmga
	 ,v.ds_nome_variedade
	 ,pg.id_cultura
	 ,cu.ds_nome_cultura
FROM
	  seguro.propostas pr
	  INNER JOIN seguro.propostas_endosso pre ON pre.id_proposta = pr.id AND pre.fl_proposta_vigente = true
	  INNER JOIN seguro.itens_segurados its ON its.id_proposta = pr.id
	  LEFT JOIN sumario.relatorio_sinistralidade_itens_segurados_vistorias itstemp ON itstemp.nr_item_segurado = its.nr_item_segurado AND itstemp.id_endosso = pr.id_endosso
	 ,seguro.propostas_proponentes pp
	 ,seguro.propostas_propriedades po
	 ,sistema.municipios m
	 ,sistema.estados es
	 ,produto.produtos pd
	 ,produto.produtos_geral pg
	 ,produto.culturas cu
	 ,produto.variedades v
	 ,sinistro.processos sp
	 ,sinistro.processos_empresas pe
	 ,sistema.empresas e
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 pe.fl_ativo = true
	 AND pd.id = pr.id_produto
	 AND pg.id = pd.id_produto_geral
	 AND cu.id = pg.id_cultura
	 AND pp.id_proposta = pr.id
	 AND po.id_proposta = pr.id
	 AND m.id = po.id_municipio
	 AND es.id = m.id_estado
	 AND sp.id_proposta = pr.id
	 AND its.id_variedade = v.id
	 AND pe.id_processo = sp.id
	 AND e.id = pe.id_empresa
	 AND pg.id_tipo_produto <> 3
ORDER BY
	 pr.id, its.nr_item_segurado
);

CREATE MATERIALIZED VIEW sumario.relatorio_liquidacoes_item AS (
SELECT
	pd.id,
	pd.ds_nome_produto,
	pd.id_safra,
	sp.id AS id_processo,
	sp.id_processo_seguradora,
	pr.id_proposta_mae AS id_proposta,
	pr.nr_apolice,
	sl.id AS id_liquidacao,
	sprop.id AS id_proponente,
	iseg.nr_item_segurado,
	iseg.vl_lmga,
	iseg.vl_area,
	lisc.vl_prejuizo_total AS vl_prejuizo,
	CASE WHEN lisc.vl_indenizacao >= 0 THEN lisc.vl_indenizacao ELSE 0 END AS vl_indenizacao,
	st.ds_status,
	CASE
		WHEN st.ds_chave='ENVIADA_SEGURADORA' THEN (
			SELECT
				max(dt_criacao)
			FROM
				sinistro.liquidacoes_status ls
			WHERE
				id_liquidacao=sl.id AND
				id_status=112
		)
	END AS dt_envio_seguradora,
	CASE
		WHEN (SELECT pc.fl_principal FROM seguro.propostas_coberturas pc
			   WHERE pc.id_cobertura = lisc.id_cobertura AND pc.id_proposta = pr.id
		) is false THEN 'Não'
		ELSE 'Sim'
	END AS principal,
	(SELECT ds_nome_cobertura FROM produto.coberturas WHERE id = lisc.id_cobertura) AS ds_nome_cobertura,
	(SELECT id_cobertura FROM produto.coberturas WHERE id = lisc.id_cobertura) AS id_cobertura,
	(SELECT vl_franquia FROM seguro.propostas_coberturas WHERE id_proposta = pr.id AND id_cobertura = lisc.id_cobertura) AS vl_franquia,
	CASE WHEN tl.fl_liquidacao_manual = true THEN 'Sim' ELSE 'Não' END AS liquidacao_manual,
	u.ds_nome_usuario AS ds_nome_usuario_responsavel_liquidacao,
	TO_CHAR(sl.dt_criacao, 'DD/MM/YYYY HH24:MI:ss') AS dt_liquidacao_hora
FROM
	sinistro.processos sp
	LEFT JOIN sistema.usuarios u ON u.id = sp.id_usuario_responsavel_liquidacao,
	seguro.propostas pr
	INNER JOIN seguro.propostas_endosso pre ON pre.id_proposta = pr.id AND pre.fl_proposta_vigente = true,
	produto.produtos pd,
	seguro.itens_segurados iseg,
	sinistro.liquidacoes sl,
	sinistro.tipo_liquidacao tl,
	sinistro.liquidacoes_status ls,
	sistema.status st,
	seguro.propostas_proponentes pp,
	seguro.proponentes sprop,
	sinistro.liquidacoes_itens_segurados_coberturas lisc
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 ls.fl_ativo = true AND
	pr.id = sp.id_proposta AND
	pd.id = pr.id_produto AND
	iseg.id_proposta = pr.id AND
	sl.id_processo = sp.id AND
	tl.id = sl.id_tipo_liquidacao AND
	ls.id_liquidacao = sl.id AND
	st.id = ls.id_status AND
	pp.id_proposta = pr.id AND
	sprop.cpf_cnpj = pp.cpf_cnpj AND
	(SELECT nr_item_segurado FROM seguro.itens_segurados WHERE id=lisc.id_item_segurado) = iseg.nr_item_segurado AND
	lisc.id_liquidacao = sl.id
ORDER BY
	pr.id,
	iseg.nr_item_segurado
);

CREATE MATERIALIZED VIEW sumario.relatorio_vistorias_preliminares AS (
SELECT
	  pr.id_proposta_mae AS id_proposta
	 ,sp.id AS id_processo
	 ,pp.ds_nome_proponente
	 ,m.ds_nome_municipio
	 ,m.nr_ibge
	 ,es.ds_sigla
	 ,e.ds_nome_fantASia
	 ,pd.ds_nome_produto
	 ,pd.id_safra
	 ,its.id AS id_item_segurado
	 ,its.nr_item_segurado
	 ,its.ds_item_segurado
	 ,its.vl_area
	 ,its.vl_lmga
	 ,v.ds_nome_variedade
	 ,pg.id_cultura
	 ,cu.ds_nome_cultura
FROM
	  seguro.propostas pr
	  INNER JOIN seguro.propostas_endosso pre ON pre.id_proposta = pr.id AND pre.fl_proposta_vigente = true
	  INNER JOIN (
		SELECT
			pr.id AS id_proposta
		FROM
			sinistro.laudos_preliminares lp,
			sinistro.processos sp,
			seguro.propostas pr
		WHERE
			lp.id_processo = sp.id AND
			sp.id_proposta = pr.id
		group BY
		pr.id
	  ) AS pr_pre ON pr_pre.id_proposta = pr.id
	 INNER JOIN seguro.itens_segurados its ON its.id_proposta = pr.id
	 INNER JOIN seguro.propostas_proponentes pp ON pp.id_proposta = pr.id
	 INNER JOIN seguro.propostas_propriedades po ON po.id_proposta = pr.id
	 INNER JOIN sistema.municipios m ON m.id = po.id_municipio
	 INNER JOIN sistema.estados es ON es.id = m.id_estado
	 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.culturas cu ON cu.id = pg.id_cultura
	 INNER JOIN produto.variedades v ON v.id = its.id_variedade
	 INNER JOIN sinistro.processos sp ON sp.id_proposta = pr.id
	 INNER JOIN sinistro.processos_empresas pe ON pe.id_processo = sp.id AND pe.fl_ativo = true
	 INNER JOIN sistema.empresas e ON e.id = pe.id_empresa
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	
	 )	
ORDER BY
	pr.id, its.nr_item_segurado
);

CREATE MATERIALIZED VIEW sumario.relatorio_vistorias_preliminares_itens_segurados AS (
SELECT 
	CASE WHEN i.ds_intensidade IS NOT NULL THEN SUBstring(i.ds_intensidade,1,1)||' '
	ELSE '0'
	END AS intensidade,
	lp.dt_vistoria AS dt_criacao,
	co.ds_nome_cobertura,
	vlp.id_vistoria,
	ef.ds_nome_estadio_fenologico,
	CASE WHEN lis2.vl_tamanho_planta is NOT NULL
				THEN lis2.vl_tamanho_planta
		WHEN lis3.vl_tamanho_planta is NOT NULL
				THEN lis3.vl_tamanho_planta
		WHEN lis4.vl_tamanho_planta is NOT NULL
				THEN lis4.vl_tamanho_planta
		ELSE NULL
	END AS tamanho_planta,
	string_agg(ds_nome_evento, ', ') AS eventos,
	li.id_item_segurado
FROM sinistro.laudos_preliminares lp
	INNER JOIN produto.coberturas co ON co.id = lp.id_cobertura
	INNER JOIN sinistro.laudos_preliminares_itens_segurados li ON li.id_laudo_prelimINar = lp.id
	INNER JOIN sistema.vistorias_laudos_preliminares vlp ON vlp.id_laudo_prelimINar = lp.id
	INNER JOIN sinistro.vistorias_avisos va ON va.id_vistoria = vlp.id_vistoria
	INNER JOIN sinistro.avisos_eventos ae ON ae.id_aviso = va.id_aviso
	INNER JOIN produto.eventos e ON e.id = ae.id_evento
	LEFT  JOIN sinistro.intensidades i ON i.id = li.id_intensidade
	LEFT  JOIN produto.estadios_fenologicos ef ON ef.id = li.id_estadio_fenologico
	LEFT  JOIN regulacao.lp002_itens_segurados lis2 ON lis2.id_laudo_prelimINar_item_segurado = li.id
	LEFT  JOIN regulacao.lp003_itens_segurados lis3 ON lis3.id_laudo_prelimINar_item_segurado = li.id
	LEFT  JOIN regulacao.lp004_itens_segurados lis4 ON lis4.id_laudo_prelimINar_item_segurado = li.id
	INNER JOIN sumario.relatorio_vistorias_preliminares rvp ON rvp.id_item_segurado = li.id_item_segurado
GROUP BY 
	i.ds_intensidade, 
	lp.dt_vistoria, 
	co.ds_nome_cobertura, 
	vlp.id_vistoria, 
	ef.ds_nome_estadio_fenologico, 
	lis2.vl_tamanho_planta, 
	lis3.vl_tamanho_planta, 
	lis4.vl_tamanho_planta,
	li.id_item_segurado
);
	
CREATE MATERIALIZED VIEW sumario.relatorio_vistorias_preliminares_nao_sinistrados AS (
SELECT lp.dt_vistoria AS dt_criacao,
    co.ds_nome_cobertura,
    vlp.id_vistoria,
    ef.ds_nome_estadio_fenologico,
    CASE WHEN lis2.vl_tamanho_planta is NOT NULL
                THEN lis2.vl_tamanho_planta
        WHEN lis3.vl_tamanho_planta is NOT NULL
                THEN lis3.vl_tamanho_planta
        WHEN lis4.vl_tamanho_planta is NOT NULL
                THEN lis4.vl_tamanho_planta
        ELSE NULL
    END AS tamanho_planta,
    lp.id_processo
FROM sinistro.laudos_preliminares lp
    INNER JOIN produto.coberturas co ON co.id = lp.id_cobertura
    INNER JOIN sinistro.laudos_preliminares_itens_segurados li ON li.id_laudo_prelimINar = lp.id
    INNER JOIN sistema.vistorias_laudos_preliminares vlp ON vlp.id_laudo_prelimINar = lp.id
    LEFT  JOIN produto.estadios_fenologicos ef ON ef.id = li.id_estadio_fenologico
    LEFT  JOIN regulacao.lp002_itens_segurados lis2 ON lis2.id_laudo_prelimINar_item_segurado = li.id
    LEFT  JOIN regulacao.lp003_itens_segurados lis3 ON lis3.id_laudo_prelimINar_item_segurado = li.id
    LEFT  JOIN regulacao.lp004_itens_segurados lis4 ON lis4.id_laudo_prelimINar_item_segurado = li.id
    INNER JOIN sumario.relatorio_vistorias_preliminares rvp ON rvp.id_processo = lp.id_processo
WHERE (lp.fl_evento_nao_constatado = true or lp.fl_dt_evento_em_cobertura = false)
);

CREATE materialized VIEW sumario.relatorio_propostas_canceladas_propostas AS (
	SELECT
		pr.id,
		pr.id AS id_proposta,
		pr.id_endosso,
		pr_mae.id_secao, -- sempre traz da proposta mae
		pr.id_proposta_renovada,
		pr.id_versao,
		seguro.nr_endosso(pr.id, pr.id_endosso) AS nr_endosso,
		pr.nr_apolice,
		TO_CHAR(pr.dt_vigencia_inicio,'DD/MM/YYYY') AS dt_vigencia_inicio,
		TO_CHAR(pr.dt_vigencia_fim,'DD/MM/YYYY') AS dt_vigencia_fim,
		TO_CHAR(pr.dt_transmissao, 'DD/MM/YYYY') AS dt_transmissao,
		pr.vl_custo_apolice,
		pr.id_status,
		pr.id_usuario_criacao,
		pr.nr_digito_verificador,
		pr.id_produto,
		TO_CHAR(seguro.dt_vigencia_inicio_original(pr.id),'DD/MM/YYYY') AS dt_vigencia_inicio_original,
		pr.id_proposta_mae,
		seguro.id_proposta_mae_completo(pr.id) AS id_proposta_mae_completo
	FROM
		seguro.propostas pr
		INNER JOIN seguro.propostas pr_mae ON pr.id_proposta_mae = pr_mae.id
		INNER JOIN produto.produtos pd ON 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 pd.id = pr.id_produto
);

CREATE materialized VIEW sumario.relatorio_propostas_canceladas_parcela_restituicao AS (
	SELECT
		coalesce(sum(vl_segurado),0.0) AS vl_restituido,
		pe.id_endosso
	FROM
		seguro.propostas pr
		INNER JOIN seguro.propostas_endosso pe ON pe.id_proposta = pr.id
		INNER JOIN seguro.propostas_parcelas pp ON pp.id_proposta = pr.id
		INNER JOIN sistema.status st ON st.id = pr.id_status 
			AND st.ds_chave NOT IN (
				'PROPOSTA_INCOMPLETA',
				'NAO_ENVIADA',
				'DEVOLVIDA',
				'PROPOSTA_CANCELADA',
				'ORCAMENTO_ENDOSSO',
				'ENDOSSO_INCOMPLETO',
				'ENDOSSO_ANULADO',
				'SOLICITACAO_CANCELADA'
			)
	WHERE
		pp.vl_segurado < 0
	GROUP BY
		pe.id_endosso
);

CREATE materialized VIEW sumario.relatorio_propostas_canceladas_parcela_cobranca AS (
	SELECT
		SUM(vl_segurado) AS vl_pago,
		pe.id_endosso
	FROM
		seguro.propostas pr
		INNER JOIN seguro.propostas_endosso pe ON pe.id_proposta = pr.id
		INNER JOIN seguro.propostas_parcelas pp ON pp.id_proposta = pr.id
		INNER JOIN sistema.status st ON st.id = pr.id_status AND st.ds_chave NOT IN ('PROPOSTA_INCOMPLETA' , 'NAO_ENVIADA' , 'DEVOLVIDA' , 'PROPOSTA_CANCELADA' , 'ORCAMENTO_ENDOSSO', 'ENDOSSO_INCOMPLETO', 'ENDOSSO_ANULADO', 'SOLICITACAO_CANCELADA')
	WHERE
		pp.vl_segurado > 0 AND pp.fl_pago = true
	GROUP BY
		pe.id_endosso
);

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

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_premio_adicional AS (
	SELECT id_proposta,sum(vl_premio) as premio_adicional
	  FROM seguro.propostas_coberturas
	  JOIN seguro.propostas ON propostas.id = propostas_coberturas.id_proposta
	  JOIN sistema.status ON status.id = propostas.id_status
	   AND status.ds_chave NOT IN ('PROPOSTA_INCOMPLETA' , 'NAO_ENVIADA' , 'DEVOLVIDA' , 'PROPOSTA_CANCELADA' , 'APOLICE_CANCELADA', 'ORCAMENTO_ENDOSSO', 'ENDOSSO_INCOMPLETO', 'ENDOSSO_ANULADO', 'SOLICITACAO_CANCELADA', 'SOLICITACAO_ENDOSSO')
	 WHERE propostas_coberturas.fl_del=false AND propostas_coberturas.fl_principal=false 
	 GROUP BY propostas_coberturas.id_proposta
);

CREATE materialized VIEW sumario.relatorio_vendas_premio_pago_segurado AS (
	SELECT propostas_parcelas.id_endosso,sum(propostas_parcelas.vl_segurado) as premio_pago_segurado
	FROM seguro.propostas_parcelas
	  JOIN seguro.propostas ON propostas.id = propostas_parcelas.id_proposta
	  JOIN sistema.status ON status.id = propostas.id_status
	  LEFT JOIN seguro.proposta_boletos ON propostas.id = proposta_boletos.id_proposta AND propostas_parcelas.nr_parcela = proposta_boletos.nr_parcela
	  LEFT JOIN seguro.propostas_versoes ON propostas_versoes.id = proposta_boletos.id_versao
	WHERE 
	  propostas_parcelas.fl_del=false 
	  AND (propostas_parcelas.fl_pago=true or propostas_parcelas.vl_segurado < 0 or proposta_boletos.fl_pago=true)
	  AND status.ds_chave NOT IN ('PROPOSTA_INCOMPLETA' , 'NAO_ENVIADA' , 'DEVOLVIDA' , 'PROPOSTA_CANCELADA' , 'APOLICE_CANCELADA', 'ORCAMENTO_ENDOSSO', 'ENDOSSO_INCOMPLETO', 'ENDOSSO_ANULADO', 'SOLICITACAO_CANCELADA', 'SOLICITACAO_ENDOSSO')
	GROUP BY 
	  propostas_parcelas.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 --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 {
        $this->execute(<<<SQL
calculo_reservas_endpoints

SQL
        );
    }
}