<?php

declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskSr59517 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
            CREATE OR REPLACE FUNCTION dms_text_to_lat_s(dms TEXT)
            RETURNS NUMERIC AS $$
            DECLARE
                deg NUMERIC; min NUMERIC; sec NUMERIC;
                s TEXT := regexp_replace(trim(dms), '\s+', ' ', 'g');
            BEGIN
                s := regexp_replace(s, '^\s*-\s*', '');
                IF s ~ '^\d+(?:\.\d+)? \d+(?:\.\d+)? \d+(?:\.\d+)?$' THEN
                    deg := split_part(s, ' ', 1)::NUMERIC;
                    min := split_part(s, ' ', 2)::NUMERIC;
                    sec := split_part(s, ' ', 3)::NUMERIC;
                ELSE
                    RAISE EXCEPTION 'Latitude inválida (D M S): %', dms;
                END IF;
                RETURN -1 * (deg + (min / 60.0) + (sec / 3600.0)); -- sempre Sul
            END;
            $$ LANGUAGE plpgsql IMMUTABLE;

            CREATE OR REPLACE FUNCTION dms_text_to_lon_w(dms TEXT)
            RETURNS NUMERIC AS $$
            DECLARE
                deg NUMERIC; min NUMERIC; sec NUMERIC;
                s TEXT := regexp_replace(trim(dms), '\s+', ' ', 'g');
            BEGIN
                s := regexp_replace(s, '^\s*-\s*', '');
                IF s ~ '^\d+(?:\.\d+)? \d+(?:\.\d+)? \d+(?:\.\d+)?$' THEN
                    deg := split_part(s, ' ', 1)::NUMERIC;
                    min := split_part(s, ' ', 2)::NUMERIC;
                    sec := split_part(s, ' ', 3)::NUMERIC;
                ELSE
                    RAISE EXCEPTION 'Longitude inválida (D M S): %', dms;
                END IF;
                RETURN -1 * (deg + (min / 60.0) + (sec / 3600.0)); -- sempre Oeste
            END;
            $$ LANGUAGE plpgsql IMMUTABLE;


            INSERT INTO manutencao.materialized_views (migration_name, ordem, schemaname, matviewname, dt_criacao)
            VALUES
                ('TaskSr59517', (SELECT max(ordem) + 1 FROM manutencao.materialized_views), 'seguro', 'mv_poligono_fechado', now()),
                ('TaskSr59517', (SELECT max(ordem) + 1 FROM manutencao.materialized_views), 'seguro', 'mv_liquidacao_item', now()),
                ('TaskSr59517', (SELECT max(ordem) + 1 FROM manutencao.materialized_views), 'seguro', 'mv_vendas_sinistros_item_cobertura', now());
            

            CREATE MATERIALIZED VIEW seguro.mv_poligono_fechado AS (
                WITH pontos AS (
                    SELECT
                        propostas_croquis_talhoes.id,
                        jsonb_array_elements(propostas_croquis_talhoes.ds_coordenadas::jsonb) AS ponto
                    FROM
                        seguro.propostas
                    INNER JOIN seguro.propostas_endosso
                        ON propostas_endosso.id_proposta = propostas.id
                            AND propostas_endosso.fl_proposta_vigente IS TRUE
                    INNER JOIN seguro.propostas proposta_mae
                        ON proposta_mae.id = propostas.id_proposta_mae
                    INNER JOIN seguro.propostas_croquis
                        ON propostas_croquis.id_proposta = propostas.id
                            AND propostas_croquis.fl_vigente IS TRUE
                    INNER JOIN seguro.propostas_croquis_talhoes
                        ON propostas_croquis.id = propostas_croquis_talhoes.id_croqui
                    WHERE proposta_mae.id_status IN (3, 102, 62, 13, 8)
                ),
                formatado AS (
                    SELECT
                        id,
                        ponto->>'longitude' AS lon,
                        ponto->>'latitude' AS lat,
                        row_number() OVER (PARTITION BY id ORDER BY (ponto->>'longitude')::numeric, (ponto->>'latitude')::numeric) AS ordem
                    FROM pontos
                )
                SELECT * FROM formatado
                UNION ALL
                SELECT
                    id,
                    lon,
                    lat,
                    9999 -- força o último ponto a ser o mesmo do primeiro
                FROM (
                    SELECT id, lon, lat
                    FROM formatado
                    WHERE ordem = 1
                ) AS primeiro
            );

            -- VIEW MATERIALIZADA PARA AUXILIAR NA DITRIBUIÇÃO DO VALOR DA INDENIZAÇÃO PARA OS ITENS
            CREATE MATERIALIZED VIEW seguro.mv_liquidacao_item AS (
                WITH liquidacao_manual_multirrisco_temp AS (
                    SELECT lisc.id_liquidacao
                        ,sum(sis.vl_area) AS vl_area_total
                        ,round((SELECT COALESCE(sum(vl_prejuizo_total), 0) FROM sinistro.liquidacoes_itens_segurados_coberturas i WHERE i.id_liquidacao = lisc.id_liquidacao AND vl_prejuizo_total > 0), 2) AS vl_prejuizo_total
                        ,round((SELECT COALESCE(sum(vl_indenizacao), 0) FROM sinistro.liquidacoes_itens_segurados_coberturas i WHERE i.id_liquidacao = lisc.id_liquidacao AND vl_indenizacao > 0), 2) AS vl_indenizacao_total
                    FROM sinistro.liquidacoes li
                    JOIN sinistro.tipo_liquidacao tli ON tli.id = li.id_tipo_liquidacao
                    JOIN sinistro.liquidacoes_itens_segurados_coberturas lisc ON lisc.id_liquidacao = li.id
                    JOIN seguro.itens_segurados sis ON sis.id = lisc.id_item_segurado
                    JOIN produto.coberturas pc ON pc.id = lisc.id_cobertura
                    WHERE ((pc.ds_chave_cobertura = 'COB_MULTIRISCO_II' AND tli.ds_chave = 'LIQ05') OR tli.ds_chave = 'LIQ17')
                    GROUP BY lisc.id_liquidacao
                ),
                liquidacao_manual_multirrisco_item_temp AS (
                    SELECT  prc.id_proposta
                        ,lisc.id_liquidacao
                        ,sis.nr_item_segurado
                        ,lisc.id_cobertura
                        ,round((lmmt.vl_prejuizo_total * ((sis.vl_area * 100) / lmmt.vl_area_total)) / 100,2)    AS vl_prejuizo
                        ,round((lmmt.vl_indenizacao_total * ((sis.vl_area * 100) / lmmt.vl_area_total)) / 100,2) AS vl_indenizacao
                    FROM sinistro.processos prc
                    JOIN sinistro.liquidacoes li ON li.id_processo = prc.id
                    JOIN sinistro.tipo_liquidacao tli ON tli.id = li.id_tipo_liquidacao
                    JOIN sinistro.liquidacoes_itens_segurados_coberturas lisc ON lisc.id_liquidacao = li.id
                    JOIN liquidacao_manual_multirrisco_temp lmmt ON lmmt.id_liquidacao = lisc.id_liquidacao
                    JOIN seguro.itens_segurados sis ON sis.id = lisc.id_item_segurado
                    JOIN seguro.propostas pr ON pr.id = prc.id_proposta
                    JOIN produto.produtos pd ON pd.id = pr.id_produto
                    JOIN produto.coberturas pc ON pc.id = lisc.id_cobertura
                    WHERE ((pc.ds_chave_cobertura = 'COB_MULTIRISCO_II' AND tli.ds_chave = 'LIQ05') OR tli.ds_chave = 'LIQ17')
                    GROUP BY  prc.id_proposta
                            ,sis.nr_item_segurado
                            ,sis.ds_item_segurado
                            ,lisc.id_cobertura
                            ,pc.ds_chave_cobertura
                            ,sis.vl_area
                            ,sis.id
                            ,li.id
                            ,lmmt.vl_area_total
                            ,lmmt.vl_prejuizo_total
                            ,lmmt.vl_indenizacao_total
                            ,lisc.id_liquidacao
                ),
                cte AS (
                    SELECT x.id_liquidacao
                        ,(x.vl_prejuizo_total - x.vl_prejuizo_sum) AS vl_prejuizo_diferenca
                        ,(x.vl_indenizacao_total - x.vl_indenizacao_sum) AS vl_indenizacao_diferenca
                        ,(SELECT t.nr_item_segurado FROM liquidacao_manual_multirrisco_item_temp t WHERE t.id_liquidacao = x.id_liquidacao ORDER BY t.nr_item_segurado DESC LIMIT 1 ) AS nr_item_segurado
                    FROM (
                        SELECT lit.id_liquidacao
                                ,sum(lit.vl_prejuizo) AS vl_prejuizo_sum
                                ,sum(lit.vl_indenizacao) AS vl_indenizacao_sum
                                ,lmmt.vl_indenizacao_total
                                ,lmmt.vl_prejuizo_total
                            FROM liquidacao_manual_multirrisco_item_temp lit
                            JOIN liquidacao_manual_multirrisco_temp lmmt ON lmmt.id_liquidacao = lit.id_liquidacao
                        GROUP by lit.id_liquidacao
                                ,lmmt.vl_indenizacao_total
                                ,lmmt.vl_prejuizo_total
                        HAVING sum(lit.vl_indenizacao) <> lmmt.vl_indenizacao_total
                            OR sum(lit.vl_prejuizo) <> lmmt.vl_prejuizo_total
                    ) AS x
                )
				SELECT x.id_proposta
					,x.nr_item_segurado
					,x.id_cobertura
					,round(sum(x.vl_prejuizo), 2) AS vl_prejuizo
					,round(sum(x.vl_indenizacao), 2) AS vl_indenizacao
				FROM (
					SELECT prc.id_proposta
						,sis.nr_item_segurado
						,lisc.id_cobertura
						,round(sum(lisc.vl_prejuizo_total), 2) AS vl_prejuizo
						,round(sum(lisc.vl_indenizacao), 2) AS vl_indenizacao
					FROM sinistro.processos prc
					JOIN sinistro.liquidacoes li ON li.id_processo = prc.id
					JOIN sinistro.tipo_liquidacao tli ON tli.id = li.id_tipo_liquidacao
					JOIN sinistro.liquidacoes_itens_segurados_coberturas lisc ON lisc.id_liquidacao = li.id
					JOIN seguro.itens_segurados sis ON sis.id = lisc.id_item_segurado
					JOIN seguro.propostas pr ON pr.id = prc.id_proposta
					JOIN produto.produtos pd ON pd.id = pr.id_produto
					JOIN produto.coberturas pc ON pc.id = lisc.id_cobertura
					WHERE NOT ((pc.ds_chave_cobertura = 'COB_MULTIRISCO_II' AND tli.ds_chave = 'LIQ05') OR tli.ds_chave = 'LIQ17')
					GROUP BY prc.id_proposta
							,sis.nr_item_segurado
							,sis.ds_item_segurado
							,lisc.id_cobertura
							,pc.ds_chave_cobertura

					UNION

						SELECT id_proposta
							,liquidacao_manual_multirrisco_item_temp.nr_item_segurado
							,liquidacao_manual_multirrisco_item_temp.id_cobertura
                            ,CASE WHEN cte.id_liquidacao IS NOT NULL THEN vl_prejuizo + cte.vl_prejuizo_diferenca ELSE vl_prejuizo END AS vl_prejuizo
							,CASE WHEN cte.id_liquidacao IS NOT NULL THEN vl_indenizacao + cte.vl_indenizacao_diferenca ELSE  vl_indenizacao END AS vl_indenizacao
						FROM liquidacao_manual_multirrisco_item_temp
                        LEFT JOIN cte
                            ON liquidacao_manual_multirrisco_item_temp.id_liquidacao = cte.id_liquidacao
                             AND liquidacao_manual_multirrisco_item_temp.nr_item_segurado = cte.nr_item_segurado
				) AS x
				GROUP BY x.id_proposta
						,x.nr_item_segurado
						,x.id_cobertura
			);
		
            CREATE MATERIALIZED VIEW seguro.mv_vendas_sinistros_item_cobertura AS (
                WITH liquidacao_item AS (
                    SELECT x.id_proposta
                        ,x.nr_item_segurado
                        ,x.id_cobertura
                        ,round(sum(x.vl_prejuizo), 2) AS vl_prejuizo
                        ,round(sum(x.vl_indenizacao), 2) AS vl_indenizacao
                    FROM (
                        SELECT prc.id_proposta
                            ,sis.nr_item_segurado
                            ,lisc.id_cobertura
                            ,round(sum(lisc.vl_prejuizo_total), 2) AS vl_prejuizo
                            ,round(sum(lisc.vl_indenizacao), 2) AS vl_indenizacao
                        FROM sinistro.processos prc
                        JOIN sinistro.liquidacoes li ON li.id_processo = prc.id
                        JOIN sinistro.tipo_liquidacao tli ON tli.id = li.id_tipo_liquidacao
                        JOIN sinistro.liquidacoes_itens_segurados_coberturas lisc ON lisc.id_liquidacao = li.id
                        JOIN seguro.itens_segurados sis ON sis.id = lisc.id_item_segurado
                        JOIN seguro.propostas pr ON pr.id = prc.id_proposta
                        JOIN produto.produtos pd ON pd.id = pr.id_produto
                        JOIN produto.coberturas pc ON pc.id = lisc.id_cobertura
                        WHERE NOT ((pc.ds_chave_cobertura = 'COB_MULTIRISCO_II' AND tli.ds_chave = 'LIQ05') OR tli.ds_chave = 'LIQ17')
                        GROUP BY prc.id_proposta
                                ,sis.nr_item_segurado
                                ,sis.ds_item_segurado
                                ,lisc.id_cobertura
                                ,pc.ds_chave_cobertura
                        UNION

                            SELECT id_proposta
                                ,nr_item_segurado
                                ,id_cobertura
                                ,vl_prejuizo
                                ,vl_indenizacao
                            FROM seguro.mv_liquidacao_item
                    ) AS x
                    GROUP BY x.id_proposta
                            ,x.nr_item_segurado
                            ,x.id_cobertura
                ),
                auxilio_calculo_premio_retido AS (
                    SELECT
                        propostas.id,
                        ROUND(SUM(itens_segurados.vl_lmga), 2) AS vl_lmga_total,
                        COUNT(itens_segurados.id) AS total_itens,
                        taxas.total_taxas,
                        taxas.total_coberturas
                    FROM seguro.propostas
                    INNER JOIN seguro.propostas_endosso
                        ON propostas_endosso.id_proposta = propostas.id
                            AND fl_proposta_vigente IS TRUE
                    INNER JOIN seguro.itens_segurados
                        ON itens_segurados.id_proposta = propostas.id
                    INNER JOIN (
                        SELECT
                            propostas_coberturas.id_proposta,
                            SUM(propostas_coberturas.vl_taxa) AS total_taxas,
                            COUNT(propostas_coberturas.id_cobertura) AS total_coberturas
                        FROM seguro.propostas
                        INNER JOIN seguro.propostas_endosso
                            ON propostas_endosso.id_proposta = propostas.id
                                AND propostas_endosso.fl_proposta_vigente IS TRUE
                        INNER JOIN seguro.propostas proposta_original
                            ON proposta_original.id = propostas.id_proposta_mae
                        INNER JOIN seguro.propostas_coberturas
                            ON propostas_coberturas.id_proposta = propostas.id
                        WHERE
                            propostas_coberturas.fl_ativo IS TRUE
                            AND proposta_original.id_status IN (3, 102, 62, 8, 13)
                        GROUP BY propostas_coberturas.id_proposta
                    ) AS taxas ON taxas.id_proposta = itens_segurados.id_proposta
                    GROUP BY propostas.id, taxas.total_taxas, taxas.total_coberturas
                )
                SELECT
                    sp.id_proposta_mae AS proposta,
                    CASE WHEN sp2.id_status = 5 OR sp2.id_status = 13 THEN 'CANCELADA' ELSE 'ATIVA' END AS status,
                    sp.id AS proposta_vigente,
                    sis.nr_item_segurado AS item,
                    UPPER(pc.ds_nome_cobertura) AS cobertura,
                    municipios.nr_ibge AS ibge,
                    sc.ds_nome_abreviado AS corretor,
                    CASE WHEN su.tp_usuario = 'P' THEN
                        UPPER(su.ds_nome_usuario)
                    ELSE
                        ''
                    END AS sublogin,
                    pp.ds_nome_produto AS produto,
                    pp.id_safra,
                    culturas.ds_nome_cultura AS cultura,
                    culturas.id AS id_cultura,
                    pv.ds_nome_variedade AS variedade,
                    to_char(sp2.dt_transmissao, 'DD/MM/YYYY') AS data_transmissao,
                    ps.ds_nome_safra AS safra,
                    sis.vl_area AS area,
                    spc.vl_franquia AS franquia,
                    CASE
                        WHEN spc.fl_principal = true THEN sis.vl_lmga
                        WHEN pc.ds_chave_cobertura = 'COB_PROTECAO_FITO' THEN sis.vl_area * 100
                        ELSE round((sis.vl_lmga * spc.vl_lmi )/100,2)
                    END as lmga_cobertura,
                    sis.vl_lmga AS vl_is,
                    CASE
                        WHEN ss.ds_chave = 'APOLICE_CANCELADA' AND auxilio_calculo_premio_retido.total_coberturas > 1 AND auxilio_calculo_premio_retido.total_taxas > 0
                            -- CALCULA PRÊMIO RETIDO, PROPORCIONAL AO IS (IMPORTÂNCIA SEGURADA) DE CADA ITEM CONSIDERANDO TAXA DA COBERTURA
                            THEN
                                ROUND(
                                    ((COALESCE(pagamento_retido.vl_pago, 0) + COALESCE(pagamento_restituido.vl_restituido, 0))) * ((sis.vl_lmga) / auxilio_calculo_premio_retido.vl_lmga_total) * (spc.vl_taxa / (auxilio_calculo_premio_retido.total_taxas))
                                    , 2
                                )
                        WHEN ss.ds_chave = 'APOLICE_CANCELADA'
                            -- CALCULA PRÊMIO RETIDO, PROPORCIONAL AO IS (IMPORTÂNCIA SEGURADA) DE CADA ITEM
                            THEN
                                ROUND(
                                    (COALESCE(pagamento_retido.vl_pago, 0) + COALESCE(pagamento_restituido.vl_restituido, 0))  * ((sis.vl_lmga) / auxilio_calculo_premio_retido.vl_lmga_total)
                                    , 2
                                )
                        ELSE
                            ROUND((sis.vl_lmga * spc.vl_taxa) / 100, 2)
                    END AS premio,
                    CASE
                        WHEN pp.ds_nome_produto ilike '%Multirrisco%' AND spc.fl_principal IS TRUE
                            THEN sis.vl_rendimento_tonelada
                        WHEN spc.fl_principal IS TRUE
                            THEN sis.vl_rendimento_tonelada
                        ELSE
                            NULL
                    END AS produtividade_esperada,
                    CASE
                        WHEN  pp.ds_nome_produto ilike '%Multirrisco%' AND spc.fl_principal IS TRUE
                            THEN sisc.vl_producao_garantida
                        WHEN spc.fl_principal IS TRUE
                            THEN sis.vl_rendimento_tonelada
                        ELSE
                            NULL
                    END AS produtividade_garantida,
                    CASE
                        WHEN pp.ds_nome_produto ilike '%Multirrisco%'
                            THEN ''
                        ELSE
                            '-'
                    END  AS produtividade_obtida,
                    ss.ds_status AS status_apolice,
                    CASE
                        WHEN avisos.id_proposta IS NOT NULL
                            THEN 1
                        ELSE
                            0
                    END AS cobertura_sinistrada,
                    avisos.eventos AS evento,
                    avisos.dt_sinistro AS data_sinistro,
                    avisos.dt_criacao_aviso AS data_aviso_sinistro,
                    to_char(slf.dt_vistoria, 'DD/MM/YYYY') AS data_vistoria_final,
                    empresas.ds_nome_fantasia AS empresa_vistoria,
                    vistoriadores_lf.vistoriadores AS vistoriador,
                    CASE
                        WHEN liquidacao_item.id_proposta IS NOT NULL
                            THEN 'Liquidado'
                        ELSE
                            'Digitado'
                    END AS status_indenizacao,
                    CASE WHEN liquidacao_item.vl_prejuizo < 0 THEN 0 ELSE liquidacao_item.vl_prejuizo END AS prejuizo,
                    CASE WHEN liquidacao_item.vl_indenizacao < 0 THEN 0 ELSE liquidacao_item.vl_indenizacao END AS indenizacao,
                    CASE
                        WHEN
                            (croqui_talhoes.ds_coordenadas_centroide IS NULL OR croqui_talhoes.ds_coordenadas_centroide = '')
                            AND sisc.dt_criacao > '2011-01-01 00:00:00'
                            AND sisc.ds_coordenadas_geograficas <> ''
                            AND sisc.ds_coordenadas_geograficas IS NOT NULL
                        THEN dms_text_to_lat_s(split_part(sisc.ds_coordenadas_geograficas, ',', 1))::TEXT
                        WHEN croqui_talhoes.ds_coordenadas_centroide LIKE '%,%' THEN split_part(croqui_talhoes.ds_coordenadas_centroide, ',', 1)
                        WHEN croqui_talhoes.ds_coordenadas_centroide LIKE '% %' THEN split_part(croqui_talhoes.ds_coordenadas_centroide, ',', 1)
                        WHEN
                            propostas_propriedades.dt_criacao > '2011-01-01 00:00:00'
                            AND propostas_propriedades.ds_coordenadas <> ''
                            AND propostas_propriedades.ds_coordenadas IS NOT NULL
                        THEN
                            CASE WHEN propostas_propriedades.ds_coordenadas LIKE '%,%' THEN dms_text_to_lat_s(split_part(propostas_propriedades.ds_coordenadas, ',', 1))::TEXT
                                ELSE dms_text_to_lat_s(array_to_string( (regexp_split_to_array(propostas_propriedades.ds_coordenadas, '\s+'))[1:3], ' ' ))::TEXT
                            END
                        ELSE ''
                    END AS latitude_centroide,
                    CASE
                        WHEN
                            (croqui_talhoes.ds_coordenadas_centroide IS NULL OR croqui_talhoes.ds_coordenadas_centroide = '')
                            AND sisc.dt_criacao > '2011-01-01 00:00:00'
                            AND sisc.ds_coordenadas_geograficas <> ''
                            AND sisc.ds_coordenadas_geograficas IS NOT NULL
                        THEN dms_text_to_lon_w(split_part(sisc.ds_coordenadas_geograficas, ',', 2))::TEXT
                        WHEN croqui_talhoes.ds_coordenadas_centroide LIKE '%,%' THEN split_part(croqui_talhoes.ds_coordenadas_centroide, ',', 2)
                        WHEN trim(croqui_talhoes.ds_coordenadas_centroide) LIKE '% %' THEN split_part(croqui_talhoes.ds_coordenadas_centroide, ',', 2)
                        WHEN
                            propostas_propriedades.dt_criacao > '2011-01-01 00:00:00'
                            AND propostas_propriedades.ds_coordenadas <> ''
                            AND propostas_propriedades.ds_coordenadas IS NOT NULL
                        THEN
                            CASE WHEN propostas_propriedades.ds_coordenadas LIKE '%,%' THEN dms_text_to_lon_w(split_part(propostas_propriedades.ds_coordenadas, ',', 2))::TEXT
                                ELSE dms_text_to_lon_w(array_to_string( (regexp_split_to_array(propostas_propriedades.ds_coordenadas, '\s+'))[4:6], ' ' ))::TEXT
                            END
                        ELSE ''
                    END AS longitude_centroide,
                    croqui_talhoes.id AS id_croqui_talhao,
                    ss.ds_chave AS status_proposta
                FROM
                    seguro.propostas sp
                INNER JOIN seguro.propostas_endosso
                        ON propostas_endosso.id_proposta = sp.id
                            AND fl_proposta_vigente IS TRUE
                INNER JOIN sistema.status ss
                    ON ss.id = sp.id_status
                INNER JOIN produto.produtos pp
                    ON pp.id = sp.id_produto
                INNER JOIN produto.safras ps
                    ON ps.id = pp.id_safra
                INNER JOIN produto.produtos_geral ppg
                    ON ppg.id = pp.id_produto_geral
                INNER JOIN produto.culturas
                    ON culturas.id = ppg .id_cultura
                INNER JOIN seguro.itens_segurados sis
                    ON sis.id_proposta = sp.id
                INNER JOIN seguro.itens_segurados_complemento sisc
                    ON sisc.id_item_segurado = sis.id
                INNER JOIN seguro.propostas_coberturas spc
                    ON spc.id_proposta = sis.id_proposta AND spc.fl_ativo IS TRUE
                INNER JOIN produto.coberturas pc
                    ON pc.id = spc.id_cobertura
                INNER JOIN seguro.propostas_corretores segpc
                    ON segpc.id_proposta = sp.id
                INNER JOIN sistema.corretores sc
                    ON sc.id_usuario = segpc.id_usuario
                INNER JOIN sistema.usuarios su
                    ON su.id = sp.id_usuario_criacao
                INNER JOIN produto.variedades pv
                    ON pv.id = sis.id_variedade
                INNER JOIN seguro.propostas sp2
                    ON sp2.id = sp.id_proposta_mae
                INNER JOIN seguro.propostas_propriedades
                    ON propostas_propriedades.id_proposta = sp.id
                INNER JOIN sistema.municipios
                    ON municipios.id = propostas_propriedades.id_municipio
                INNER JOIN auxilio_calculo_premio_retido
                    ON auxilio_calculo_premio_retido.id = sp.id
                LEFT JOIN seguro.propostas_croquis croqui
                    ON croqui.id_proposta = sp.id AND croqui.fl_vigente IS true
                LEFT JOIN seguro.propostas_croquis_talhoes croqui_talhoes
                    ON croqui_talhoes.id_croqui = croqui.id
                        AND sis.id::varchar = any(string_to_array(croqui_talhoes.ds_item_segurado,','))
                LEFT JOIN liquidacao_item
                    ON liquidacao_item.id_proposta = spc.id_proposta
						AND liquidacao_item.id_cobertura = spc.id_cobertura
						AND liquidacao_item.nr_item_segurado = sis.nr_item_segurado
                LEFT JOIN (
                    SELECT
                        avisos.id_proposta,
                        produtos_coberturas_eventos.id_cobertura,
                        string_agg(ds_nome_evento, ', ') AS eventos,
                        string_agg(to_char(avisos.dt_sinistro, 'DD/MM/YYYY'), ', ') AS dt_sinistro,
                        string_agg(to_char(avisos.dt_criacao, 'DD/MM/YYYY'), ', ') AS dt_criacao_aviso
                    FROM seguro.propostas
                    INNER JOIN produto.produtos
                        ON produtos.id = propostas.id_produto
                    INNER JOIN seguro.propostas_endosso
                        ON propostas_endosso.id_proposta = propostas.id
                            AND fl_proposta_vigente IS TRUE
                    INNER JOIN sinistro.avisos
                        ON avisos.id_proposta = propostas.id
                    INNER JOIN sinistro.avisos_eventos
                        ON avisos_eventos.id_aviso = avisos.id
                    INNER JOIN produto.eventos pe
                        ON pe.id = avisos_eventos.id_evento
                    INNER JOIN produto.produtos_coberturas_eventos
                        ON produtos_coberturas_eventos.id_produto = propostas.id_produto
                            AND produtos_coberturas_eventos.id_evento = avisos_eventos.id_evento
                    WHERE
                        avisos_eventos.fl_ativo IS TRUE
                        AND id_safra > 16
                    GROUP BY avisos.id_proposta, produtos_coberturas_eventos.id_cobertura

                    UNION

                    SELECT
                        avisos.id_proposta,
                        coberturas_eventos.id_cobertura,
                        string_agg(ds_nome_evento, ', ') AS eventos,
                        string_agg(to_char(avisos.dt_sinistro, 'DD/MM/YYYY'), ', ') AS dt_sinistro,
                        string_agg(to_char(avisos.dt_criacao, 'DD/MM/YYYY'), ', ') AS dt_criacao_aviso
                    FROM seguro.propostas
                    INNER JOIN produto.produtos
                        ON produtos.id = propostas.id_produto
                    INNER JOIN seguro.propostas_endosso
                        ON propostas_endosso.id_proposta = propostas.id
                            AND fl_proposta_vigente IS TRUE
                    INNER JOIN sinistro.avisos
                        ON avisos.id_proposta = propostas.id
                    INNER JOIN sinistro.avisos_eventos
                        ON avisos_eventos.id_aviso = avisos.id
                    INNER JOIN produto.eventos
                        ON eventos.id = avisos_eventos.id_evento
                    INNER JOIN produto.produtos_coberturas
                        ON produtos_coberturas.id_produto = propostas.id_produto
                    INNER JOIN produto.coberturas_eventos
                        ON coberturas_eventos.id_cobertura = produtos_coberturas.id_cobertura
                            AND coberturas_eventos.id_evento = eventos.id
                    WHERE
                        avisos_eventos.fl_ativo IS TRUE
                        AND id_safra <= 16
                    GROUP BY avisos.id_proposta, coberturas_eventos.id_cobertura

                ) AS avisos ON avisos.id_proposta = sp.id
                    AND spc.id_cobertura = avisos.id_cobertura
                LEFT JOIN sinistro.processos
                    ON processos.id_proposta = sp.id
                LEFT JOIN (
                    SELECT DISTINCT ON (id_processo)
                        laudos_finais.id,
                        laudos_finais.id_processo,
                        laudos_finais.dt_vistoria
                    FROM sinistro.laudos_finais
                    WHERE dt_vistoria = (
                        SELECT max(dt_vistoria)
                        FROM sinistro.laudos_finais slf2
                        WHERE
                            slf2.id_processo = laudos_finais.id_processo
                            AND dt_vistoria IS NOT NULL
                    )
                ) AS slf
                    ON slf.id_processo = processos.id
                LEFT JOIN sinistro.processos_empresas spe
                    ON spe.id_processo = processos.id
                        AND spe.fl_ativo IS TRUE
                LEFT JOIN sistema.empresas
                    ON empresas.id = spe.id_empresa
                LEFT JOIN (
                    SELECT
                        slff.id_laudo_final,
                        string_agg(usuarios_lf.ds_nome_usuario, ', ') AS vistoriadores
                    FROM sinistro.laudos_finais_funcionarios slff
                    INNER JOIN sistema.funcionarios funcionarios_lf
                        ON funcionarios_lf.id = slff.id_funcionario
                    INNER JOIN sistema.usuarios usuarios_lf
                        ON usuarios_lf.id = funcionarios_lf.id_usuario
                    GROUP BY slff.id_laudo_final
                ) AS vistoriadores_lf ON vistoriadores_lf.id_laudo_final = slf.id
                LEFT JOIN (
                    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 produto.produtos pd ON pd.id = pr.id_produto
                        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
                ) AS pagamento_retido ON pagamento_retido.id_endosso = propostas_endosso.id_endosso
                LEFT JOIN (
                    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 produto.produtos pd ON pd.id = pr.id_produto
                        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
                ) AS pagamento_restituido ON pagamento_restituido.id_endosso = propostas_endosso.id_endosso
                WHERE
                    sp2.id_status IN (3, 102, 62, 8, 13)
                    AND NOT (
                        -- NÃO TRAZER REGISTRO DE APÓLICES CANCELADAS COM PRÊMIO ZERADO
                        ss.ds_chave = 'APOLICE_CANCELADA'
                        AND ((COALESCE(pagamento_retido.vl_pago, 0) + COALESCE(pagamento_restituido.vl_restituido, 0)) * sis.vl_lmga ) = 0
                    )
            );
SQL
        );
    }

    public function down(): void
    {
        $this->execute(<<<SQL
            DELETE FROM manutencao.materialized_views WHERE migration_name = 'TaskSr59517';
            DROP MATERIALIZED VIEW IF EXISTS seguro.mv_vendas_sinistros_item_cobertura;
            DROP MATERIALIZED VIEW IF EXISTS seguro.mv_poligono_fechado;
            DROP MATERIALIZED VIEW IF EXISTS seguro.mv_liquidacao_item;
SQL
        );
    }
}