<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class Task2010221942 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute("
                         CREATE OR REPLACE VIEW sinistro.v_avisos_transmissao_essor AS
                         SELECT avisos.fl_ativo
                              , avisos.fl_del
                              , avisos.id
                              , avisos.dt_criacao
                              , avisos.dt_sinistro
                              , seguro.id_proposta_mae(avisos.id_proposta) AS id_proposta_mae
                              , avisos.id_proposta
                              , avisos.nr_apolice
                              , avisos.ds_nome_usuario
                              , avisos.id_processo
                              , avisos.id_processo_seguradora
                              , avisos.ds_nome_proponente
                              , avisos.cpf_cnpj
                              , avisos.id_produto
                              , avisos.ds_nome_produto
                              , avisos.id_status
                              , avisos.ds_nome_municipio
                              , avisos.ds_sigla
                              , avisos.id_estado
                              , avisos.id_municipio
                              , avisos.id_usuario
                              , avisos.id_safra
                              , avisos.id_cultura
                              , avisos.cobertura
                              , avisos.cd_identificador_seguradora
                              , avisos.evento_seguradora
                           FROM (
                                  SELECT a.fl_ativo
                                       , a.fl_del
                                       , a.id
                                       , a.dt_criacao
                                       , a.dt_sinistro
                                       , pp.id AS id_proposta
                                       , pp.nr_apolice
                                       , u.ds_nome_usuario
                                       , pc.id AS id_processo
                                       , pc.id_processo_seguradora
                                       , pppr.ds_nome_proponente
                                       , pppr.cpf_cnpj
                                       , pp.id_produto
                                       , pd.ds_nome_produto
                                       , a.id_status
                                       , mun.ds_nome_municipio
                                       , est.ds_sigla
                                       , est.id AS id_estado
                                       , mun.id AS id_municipio
                                       , u.id AS id_usuario
                                       , pd.id_safra
                                       , cu.id AS id_cultura
                                       , CASE
                                             WHEN cu.ds_chave_cultura::text = ANY (ARRAY['UVA DE VINHO'::character varying::text, 'UVA DE MESA'::character varying::text]) THEN
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Ventos Fortes'::text THEN 'QUEDA DE PARREIRAL'::text
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN 'GRANIZO'::text
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN 'GEADA'::text
                                                     ELSE 'GRANIZO'::text
                                                 END
                                             WHEN cu.ds_chave_cultura::text = 'MAÇA'::text THEN
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN 'GRANIZO'::text
                                                     WHEN ev.ds_nome_evento::text = 'Excesso de Chuva'::text THEN 'PRODUÇÃO MÍNIMA'::text
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN 'PRODUÇÃO MÍNIMA'::text
                                                     ELSE 'GRANIZO'::text
                                                 END
                                             ELSE
                                                 CASE
                                                     WHEN a.fl_ocorrencia_replantio = TRUE THEN 'GRANIZO'::text
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN 'GRANIZO'::text
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN 'GEADA'::text
                                                     ELSE 'GRANIZO'::text
                                                 END
                                         END AS cobertura
                                       , CASE
                                             WHEN cu.ds_chave_cultura::text = ANY (ARRAY['UVA DE VINHO'::character varying::text, 'UVA DE MESA'::character varying::text]) THEN
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Ventos Fortes'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 11)
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 1)
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 3)
                                                     ELSE ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 1)
                                                 END
                                             WHEN cu.ds_chave_cultura::text = 'MAÇA'::text THEN
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 1)
                                                     WHEN ev.ds_nome_evento::text = 'Excesso de Chuva'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 15)
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 15)
                                                     ELSE ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 1)
                                                 END
                                             ELSE
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 1)
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 3)
                                                     ELSE ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 1)
                                                 END
                                         END AS cd_identificador_seguradora
                                       , CASE
                                             WHEN cu.ds_chave_cultura::text = ANY (ARRAY['UVA DE VINHO'::character varying::text, 'UVA DE MESA'::character varying::text]) THEN
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Ventos Fortes'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 20)
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 1)
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 3)
                                                     ELSE ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 1)
                                                 END
                                             WHEN cu.ds_chave_cultura::text = 'MAÇA'::text THEN
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 1)
                                                     WHEN ev.ds_nome_evento::text = 'Excesso de Chuva'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 2)
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 15)
                                                     ELSE ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 1)
                                                 END
                                             ELSE
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 1)
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 3)
                                                     ELSE ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_cobertura = 1)
                                                 END
                                         END AS evento_seguradora
                                    FROM sinistro.avisos a
                                    JOIN seguro.propostas pp
                                      ON a.id_proposta = pp.id
                                    JOIN seguro.propostas_endosso pe
                                      ON pe.id_proposta = pp.id
                                    JOIN seguro.propostas_propriedades ppr
                                      ON ppr.id_proposta = pp.id
                                    JOIN seguro.propostas_corretores spc
                                      ON spc.id_proposta = pp.id
                                    JOIN sistema.municipios mun
                                      ON mun.id = ppr.id_municipio
                                    JOIN sistema.estados est
                                      ON est.id = mun.id_estado
                                    JOIN sinistro.processos pc
                                      ON pp.id = pc.id_proposta
                                    JOIN seguro.propostas_proponentes pppr
                                      ON pp.id = pppr.id_proposta
                                    JOIN produto.produtos pd
                                      ON pp.id_produto = pd.id
                                    JOIN produto.produtos_geral pg
                                      ON pg.id = pd.id_produto_geral
                                    JOIN produto.culturas cu
                                      ON cu.id = pg.id_cultura
                                    JOIN produto.safras s
                                      ON pd.id_safra = s.id
                                    JOIN sistema.usuarios u
                                      ON pp.id_usuario_criacao = u.id
                                    JOIN sinistro.avisos_eventos ae
                                      ON a.id = ae.id_aviso
                                    JOIN produto.eventos ev
                                      ON ev.id = ae.id_evento
                                   WHERE pe.fl_proposta_vigente = true
                                     AND (a.id_status = 27 OR a.id_status = 28 OR a.id_status = 30)
                                     AND (ev.ds_nome_evento::text = ANY (ARRAY['Granizo'::character varying::text, 'Geada'::character varying::text, 'Ventos Fortes'::character varying::text, 'Variação Excessiva de Temperatura'::character varying::text, 'Excesso de Chuva'::character varying::text, 'Tromba D''água'::character varying::text]))
                                     AND a.fl_transmissao_seguradora = false
                                     AND (pg.id_tipo_produto = ANY (ARRAY[1, 2]))
                                     AND pp.id_status = 8
                                     AND pd.id_safra >= 16
                                  UNION
                                  SELECT a.fl_ativo
                                       , a.fl_del
                                       , a.id
                                       , a.dt_criacao
                                       , a.dt_sinistro
                                       , pp.id AS id_proposta
                                       , pp.nr_apolice
                                       , u.ds_nome_usuario
                                       , pc.id AS id_processo
                                       , pc.id_processo_seguradora
                                       , pppr.ds_nome_proponente
                                       , pppr.cpf_cnpj
                                       , pp.id_produto
                                       , pd.ds_nome_produto
                                       , a.id_status
                                       , mun.ds_nome_municipio
                                       , est.ds_sigla
                                       , est.id AS id_estado
                                       , mun.id AS id_municipio
                                       , u.id AS id_usuario
                                       , pd.id_safra
                                       , cu.id AS id_cultura
                                       , CASE
                                             WHEN pcb.cd_cobertura_seguradora::text = '340'::text THEN 'MULTIRISCO II'::text
                                             ELSE 'MULTIRISCO'::text
                                         END AS cobertura
                                       , NULL::numeric AS cd_identificador_seguradora
                                       , (
                                             SELECT coberturas_eventos.cd_evento_seguradora
                                               FROM produto.coberturas_eventos
                                              WHERE coberturas_eventos.id_evento = (
                                                                                     (
                                                                                       SELECT avisos_eventos.id_evento
                                                                                         FROM sinistro.avisos_eventos
                                                                                        WHERE avisos_eventos.id_aviso = a.id
                                                                                        LIMIT 1
                                                                                     )
                                                                                   )
                                                AND (coberturas_eventos.id_cobertura = ANY (ARRAY[18, 32]))
                                           GROUP BY coberturas_eventos.cd_evento_seguradora   
                                         ) AS evento_seguradora
                                    FROM sinistro.avisos a
                                    JOIN seguro.propostas pp
                                      ON a.id_proposta = pp.id
                                    JOIN seguro.propostas_endosso pe
                                      ON pe.id_proposta = pp.id
                                    JOIN seguro.propostas_propriedades ppr
                                      ON ppr.id_proposta = pp.id
                                    JOIN seguro.propostas_corretores spc
                                      ON spc.id_proposta = pp.id
                                    JOIN sistema.municipios mun
                                      ON mun.id = ppr.id_municipio
                                    JOIN sistema.estados est
                                      ON est.id = mun.id_estado
                                    JOIN sinistro.processos pc
                                      ON pp.id = pc.id_proposta
                                    JOIN seguro.propostas_proponentes pppr
                                      ON pp.id = pppr.id_proposta
                                    JOIN produto.produtos pd
                                      ON pp.id_produto = pd.id
                                    JOIN produto.produtos_geral pg
                                      ON pg.id = pd.id_produto_geral
                                    JOIN produto.produtos_coberturas pdc
                                      ON pdc.id_produto = pd.id
                                    JOIN produto.coberturas pcb
                                      ON pcb.id = pdc.id_cobertura
                                     AND pdc.fl_cobertura_principal = true
                                    JOIN produto.culturas cu
                                      ON cu.id = pg.id_cultura
                                    JOIN produto.safras s
                                      ON pd.id_safra = s.id
                                    JOIN sistema.usuarios u
                                      ON pp.id_usuario_criacao = u.id
                                   WHERE pe.fl_proposta_vigente = true
                                     AND (a.id_status = 27 OR a.id_status = 28 OR a.id_status = 30)
                                     AND a.fl_transmissao_seguradora = false
                                     AND pg.id_tipo_produto = 3
                                     AND pp.id_status = 8
                                     AND pd.id_safra >= 1
                                ) avisos
                         ORDER BY avisos.id_proposta, avisos.id DESC
                                ;
                      ");
    }

    public function down(): void
    {
        $this->execute("
                         CREATE OR REPLACE VIEW sinistro.v_avisos_transmissao_essor AS
                         SELECT avisos.fl_ativo
                              , avisos.fl_del
                              , avisos.id
                              , avisos.dt_criacao
                              , avisos.dt_sinistro
                              , seguro.id_proposta_mae(avisos.id_proposta) AS id_proposta_mae
                              , avisos.id_proposta
                              , avisos.nr_apolice
                              , avisos.ds_nome_usuario
                              , avisos.id_processo
                              , avisos.id_processo_seguradora
                              , avisos.ds_nome_proponente
                              , avisos.cpf_cnpj
                              , avisos.id_produto
                              , avisos.ds_nome_produto
                              , avisos.id_status
                              , avisos.ds_nome_municipio
                              , avisos.ds_sigla
                              , avisos.id_estado
                              , avisos.id_municipio
                              , avisos.id_usuario
                              , avisos.id_safra
                              , avisos.id_cultura
                              , avisos.cobertura
                              , avisos.cd_identificador_seguradora
                              , avisos.evento_seguradora
                           FROM (
                                  SELECT a.fl_ativo
                                       , a.fl_del
                                       , a.id
                                       , a.dt_criacao
                                       , a.dt_sinistro
                                       , pp.id AS id_proposta
                                       , pp.nr_apolice
                                       , u.ds_nome_usuario
                                       , pc.id AS id_processo
                                       , pc.id_processo_seguradora
                                       , pppr.ds_nome_proponente
                                       , pppr.cpf_cnpj
                                       , pp.id_produto
                                       , pd.ds_nome_produto
                                       , a.id_status
                                       , mun.ds_nome_municipio
                                       , est.ds_sigla
                                       , est.id AS id_estado
                                       , mun.id AS id_municipio
                                       , u.id AS id_usuario
                                       , pd.id_safra
                                       , cu.id AS id_cultura
                                       , CASE
                                             WHEN cu.ds_chave_cultura::text = ANY (ARRAY['UVA DE VINHO'::character varying::text, 'UVA DE MESA'::character varying::text]) THEN
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Ventos Fortes'::text THEN 'QUEDA DE PARREIRAL'::text
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN 'GRANIZO'::text
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN 'GEADA'::text
                                                     ELSE 'GRANIZO'::text
                                                 END
                                             WHEN cu.ds_chave_cultura::text = 'MAÇA'::text THEN
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN 'GRANIZO'::text
                                                     WHEN ev.ds_nome_evento::text = 'Excesso de Chuva'::text THEN 'PRODUÇÃO MÍNIMA'::text
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN 'PRODUÇÃO MÍNIMA'::text
                                                     ELSE 'GRANIZO'::text
                                                 END
                                             ELSE
                                                 CASE
                                                     WHEN a.fl_ocorrencia_replantio = TRUE THEN 'GRANIZO'::text
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN 'GRANIZO'::text
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN 'GEADA'::text
                                                     ELSE 'GRANIZO'::text
                                                 END
                                         END AS cobertura
                                       , CASE
                                             WHEN cu.ds_chave_cultura::text = ANY (ARRAY['UVA DE VINHO'::character varying::text, 'UVA DE MESA'::character varying::text]) THEN
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Ventos Fortes'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 11)
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 1)
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 3)
                                                     ELSE ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 1)
                                                 END
                                             WHEN cu.ds_chave_cultura::text = 'MAÇA'::text THEN
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 1)
                                                     WHEN ev.ds_nome_evento::text = 'Excesso de Chuva'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 15)
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 15)
                                                     ELSE ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 1)
                                                 END
                                             ELSE
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 1)
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 3)
                                                     ELSE ( SELECT avisos_transmissao_seguradora.cd_identificador_seguradora
                                                        FROM sinistro.avisos_transmissao_seguradora
                                                       WHERE avisos_transmissao_seguradora.id_aviso = a.id AND avisos_transmissao_seguradora.id_cobertura = 1)
                                                 END
                                         END AS cd_identificador_seguradora
                                       , CASE
                                             WHEN cu.ds_chave_cultura::text = ANY (ARRAY['UVA DE VINHO'::character varying::text, 'UVA DE MESA'::character varying::text]) THEN
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Ventos Fortes'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 20)
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 1)
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 3)
                                                     ELSE ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 1)
                                                 END
                                             WHEN cu.ds_chave_cultura::text = 'MAÇA'::text THEN
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 1)
                                                     WHEN ev.ds_nome_evento::text = 'Excesso de Chuva'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 2)
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 15)
                                                     ELSE ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 1)
                                                 END
                                             ELSE
                                                 CASE
                                                     WHEN ev.ds_nome_evento::text = 'Granizo'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 1)
                                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_evento = ae.id_evento AND coberturas_eventos.id_cobertura = 3)
                                                     ELSE ( SELECT coberturas_eventos.cd_evento_seguradora
                                                        FROM produto.coberturas_eventos
                                                       WHERE coberturas_eventos.id_cobertura = 1)
                                                 END
                                         END AS evento_seguradora
                                    FROM sinistro.avisos a
                                    JOIN seguro.propostas pp
                                      ON a.id_proposta = pp.id
                                    JOIN seguro.propostas_endosso pe
                                      ON pe.id_proposta = pp.id
                                    JOIN seguro.propostas_propriedades ppr
                                      ON ppr.id_proposta = pp.id
                                    JOIN seguro.propostas_corretores spc
                                      ON spc.id_proposta = pp.id
                                    JOIN sistema.municipios mun
                                      ON mun.id = ppr.id_municipio
                                    JOIN sistema.estados est
                                      ON est.id = mun.id_estado
                                    JOIN sinistro.processos pc
                                      ON pp.id = pc.id_proposta
                                    JOIN seguro.propostas_proponentes pppr
                                      ON pp.id = pppr.id_proposta
                                    JOIN produto.produtos pd
                                      ON pp.id_produto = pd.id
                                    JOIN produto.produtos_geral pg
                                      ON pg.id = pd.id_produto_geral
                                    JOIN produto.culturas cu
                                      ON cu.id = pg.id_cultura
                                    JOIN produto.safras s
                                      ON pd.id_safra = s.id
                                    JOIN sistema.usuarios u
                                      ON pp.id_usuario_criacao = u.id
                                    JOIN sinistro.avisos_eventos ae
                                      ON a.id = ae.id_aviso
                                    JOIN produto.eventos ev
                                      ON ev.id = ae.id_evento
                                   WHERE pe.fl_proposta_vigente = true
                                     AND (a.id_status = 27 OR a.id_status = 28 OR a.id_status = 30)
                                     AND (ev.ds_nome_evento::text = ANY (ARRAY['Granizo'::character varying::text, 'Geada'::character varying::text, 'Ventos Fortes'::character varying::text, 'Variação Excessiva de Temperatura'::character varying::text, 'Excesso de Chuva'::character varying::text]))
                                     AND a.fl_transmissao_seguradora = false
                                     AND (pg.id_tipo_produto = ANY (ARRAY[1, 2]))
                                     AND pp.id_status = 8
                                     AND pd.id_safra >= 16
                                  UNION
                                  SELECT a.fl_ativo
                                       , a.fl_del
                                       , a.id
                                       , a.dt_criacao
                                       , a.dt_sinistro
                                       , pp.id AS id_proposta
                                       , pp.nr_apolice
                                       , u.ds_nome_usuario
                                       , pc.id AS id_processo
                                       , pc.id_processo_seguradora
                                       , pppr.ds_nome_proponente
                                       , pppr.cpf_cnpj
                                       , pp.id_produto
                                       , pd.ds_nome_produto
                                       , a.id_status
                                       , mun.ds_nome_municipio
                                       , est.ds_sigla
                                       , est.id AS id_estado
                                       , mun.id AS id_municipio
                                       , u.id AS id_usuario
                                       , pd.id_safra
                                       , cu.id AS id_cultura
                                       , CASE
                                             WHEN pcb.cd_cobertura_seguradora::text = '340'::text THEN 'MULTIRISCO II'::text
                                             ELSE 'MULTIRISCO'::text
                                         END AS cobertura
                                       , NULL::numeric AS cd_identificador_seguradora
                                       , (
                                             SELECT coberturas_eventos.cd_evento_seguradora
                                               FROM produto.coberturas_eventos
                                              WHERE coberturas_eventos.id_evento = (
                                                                                     (
                                                                                       SELECT avisos_eventos.id_evento
                                                                                         FROM sinistro.avisos_eventos
                                                                                        WHERE avisos_eventos.id_aviso = a.id
                                                                                        LIMIT 1
                                                                                     )
                                                                                   )
                                                AND (coberturas_eventos.id_cobertura = ANY (ARRAY[18, 32]))
                                           GROUP BY coberturas_eventos.cd_evento_seguradora   
                                         ) AS evento_seguradora
                                    FROM sinistro.avisos a
                                    JOIN seguro.propostas pp
                                      ON a.id_proposta = pp.id
                                    JOIN seguro.propostas_endosso pe
                                      ON pe.id_proposta = pp.id
                                    JOIN seguro.propostas_propriedades ppr
                                      ON ppr.id_proposta = pp.id
                                    JOIN seguro.propostas_corretores spc
                                      ON spc.id_proposta = pp.id
                                    JOIN sistema.municipios mun
                                      ON mun.id = ppr.id_municipio
                                    JOIN sistema.estados est
                                      ON est.id = mun.id_estado
                                    JOIN sinistro.processos pc
                                      ON pp.id = pc.id_proposta
                                    JOIN seguro.propostas_proponentes pppr
                                      ON pp.id = pppr.id_proposta
                                    JOIN produto.produtos pd
                                      ON pp.id_produto = pd.id
                                    JOIN produto.produtos_geral pg
                                      ON pg.id = pd.id_produto_geral
                                    JOIN produto.produtos_coberturas pdc
                                      ON pdc.id_produto = pd.id
                                    JOIN produto.coberturas pcb
                                      ON pcb.id = pdc.id_cobertura
                                     AND pdc.fl_cobertura_principal = true
                                    JOIN produto.culturas cu
                                      ON cu.id = pg.id_cultura
                                    JOIN produto.safras s
                                      ON pd.id_safra = s.id
                                    JOIN sistema.usuarios u
                                      ON pp.id_usuario_criacao = u.id
                                   WHERE pe.fl_proposta_vigente = true
                                     AND (a.id_status = 27 OR a.id_status = 28 OR a.id_status = 30)
                                     AND a.fl_transmissao_seguradora = false
                                     AND pg.id_tipo_produto = 3
                                     AND pp.id_status = 8
                                     AND pd.id_safra >= 1
                                ) avisos
                         ORDER BY avisos.id_proposta, avisos.id DESC
                                ;
                      ");
    }
}