<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class Task2208111355 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
update produto.culturas set ds_chave_cultura = 'PASTAGEM' where id = 102;
update produto.eventos set ds_chave_evento = 'ESTIAGEM' where id = 3;

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
              , avisos.chave_cobertura
           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 = '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 = '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
                             WHEN cu.ds_chave_cultura::text = 'PASTAGEM'::text THEN 'BAIXA DE ÍNDICE DE PRODUÇÃO FORRAGEIRA'::text
                             ELSE
                                 CASE
                                     WHEN a.fl_ocorrencia_replantio = TRUE 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
                             WHEN cu.ds_chave_cultura::text = 'PASTAGEM'::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 = 42)
                             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
                             WHEN cu.ds_chave_cultura::text = 'PASTAGEM'::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 = 42)
                             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
                       , 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 'COB_QUEDA_PARREIRAL'::text
                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN 'COB_GEADA'::text
                                     ELSE 'COB_GRANIZO'::text
                                 END
                             WHEN cu.ds_chave_cultura::text = 'MAÇA'::text THEN
                                 CASE
                                     WHEN ev.ds_nome_evento::text = 'Excesso de Chuva'::text THEN 'COB_PRODUCAO_MINIMA_MACA'::text
                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN 'COB_PRODUCAO_MINIMA_MACA'::text
                                     ELSE 'COB_GRANIZO'::text
                                 END
                             WHEN cu.ds_chave_cultura::text = 'PASTAGEM'::text THEN 'BAIXA_INDICE'::text
                             ELSE
                                 CASE
                                     WHEN a.fl_ocorrencia_replantio = TRUE THEN 'COB_GRANIZO'::text
                                     WHEN ev.ds_nome_evento::text = 'Geada'::text THEN 'COB_GEADA'::text
                                     ELSE 'COB_GRANIZO'::text
                                 END
                         END AS chave_cobertura
                    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, 'Incêndio'::character varying::text, 'Estiagem'::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
                     --AND pp.id IN (436934,447904,467195,469169)
                  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
                       , pcb.ds_chave_cobertura AS chave_cobertura
                    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
                ;

CREATE OR REPLACE VIEW sinistro.v_aviso_novo_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,
    avisos.chave_cobertura
   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 = '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 = '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
                    WHEN cu.ds_chave_cultura::text = 'PASTAGEM'::text THEN 'BAIXA DE ÍNDICE DE PRODUÇÃO FORRAGEIRA'::text
                    ELSE
                    CASE
                        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
                    WHEN cu.ds_chave_cultura::text = 'PASTAGEM'::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 = 42)
                    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
                    WHEN cu.ds_chave_cultura::text = 'PASTAGEM'::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 = 42)
                    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,
                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 'COB_QUEDA_PARREIRAL'::text
                        WHEN ev.ds_nome_evento::text = 'Geada'::text THEN 'COB_GEADA'::text
                        ELSE 'COB_GRANIZO'::text
                    END
                    WHEN cu.ds_chave_cultura::text = 'MAÇA'::text THEN
                    CASE
                        WHEN ev.ds_nome_evento::text = 'Excesso de Chuva'::text THEN 'COB_PRODUCAO_MINIMA_MACA'::text
                        WHEN ev.ds_nome_evento::text = 'Geada'::text THEN 'COB_PRODUCAO_MINIMA_MACA'::text
                        ELSE 'COB_GRANIZO'::text
                    END
                    WHEN cu.ds_chave_cultura::text = 'PASTAGEM'::text THEN 'BAIXA_INDICE'::text
                    ELSE
                    CASE
                        WHEN ev.ds_nome_evento::text = 'Geada'::text THEN 'COB_GEADA'::text
                        ELSE 'COB_GRANIZO'::text
                    END
                END AS chave_cobertura
           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 (ev.ds_nome_evento::text = ANY
          (ARRAY['Granizo'::character varying::text,
          'Variação Excessiva de Temperatura'::character varying::text,
          'Geada'::character varying::text,
          'Ventos Fortes'::character varying::text,
          'Excesso de Chuva'::character varying::text,
          'Estiagem'::character varying::text])) AND a.fl_transmissao_seguradora = false AND (pg.id_tipo_produto = ANY (ARRAY[1, 2]))
        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,
            co.ds_chave_cobertura 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 = 18) AS evento_seguradora,
            co.ds_chave_cobertura AS chave_cobertura
           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 seguro.propostas_coberturas pco ON pco.id_proposta = pp.id AND pco.fl_principal = true
             JOIN produto.coberturas co ON co.id = pco.id_cobertura
          WHERE pe.fl_proposta_vigente = true AND a.fl_transmissao_seguradora = false AND pg.id_tipo_produto = 3) avisos
  ORDER BY avisos.id_proposta, avisos.id DESC;

ALTER TABLE sinistro.v_aviso_novo_transmissao_essor
  OWNER TO postgres;
SQL
);
    }

    public function down(): void
    {
        $this->execute(<<<SQL
CREATE OR REPLACE VIEW sinistro.v_aviso_novo_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 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 (ev.ds_nome_evento::text = ANY 
          (ARRAY['Granizo'::character varying::text, 
          'Variação Excessiva de Temperatura'::character varying::text, 
          'Geada'::character varying::text, 
          'Ventos Fortes'::character varying::text, 
          'Excesso de Chuva'::character varying::text])) AND a.fl_transmissao_seguradora = false AND (pg.id_tipo_produto = ANY (ARRAY[1, 2]))
        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,
            co.ds_chave_cobertura 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 = 18) 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 seguro.propostas_coberturas pco ON pco.id_proposta = pp.id AND pco.fl_principal = true
             JOIN produto.coberturas co ON co.id = pco.id_cobertura
          WHERE pe.fl_proposta_vigente = true AND a.fl_transmissao_seguradora = false AND pg.id_tipo_produto = 3) avisos
  ORDER BY avisos.id_proposta, avisos.id DESC;

ALTER TABLE sinistro.v_aviso_novo_transmissao_essor
  OWNER TO postgres;

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, 'Incêndio'::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
                     --AND pp.id IN (436934,447904,467195,469169)
                  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
                ;
SQL
);
    }
}