<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class Task2303291355 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
UPDATE produto.produtos_coberturas_eventos ppce_update
   SET fl_excluir_aviso_sinistro = true
  FROM produto.produtos pp
       INNER JOIN produto.produtos_coberturas ppc ON ppc.id_produto = pp.id
       INNER JOIN produto.coberturas pc ON pc.id = ppc.id_cobertura
       INNER JOIN produto.coberturas_eventos pce ON pce.id_cobertura = pc.id
       INNER JOIN produto.produtos_coberturas_eventos ppce ON ppce.id_produto = pp.id
                                                          AND ppce.id_cobertura = pc.id
                                                          AND ppce.id_evento = pce.id_evento
 WHERE pp.id_safra in(25, 26)
   AND pc.id in(18, 29, 32)
   AND pce.id_evento = 10
   AND ppce.id_produto = ppce_update.id_produto
   AND ppce.id_cobertura = ppce_update.id_cobertura
   AND ppce.id_evento = ppce_update.id_evento;
SQL
);
    }

    public function down(): void
    {
        $this->execute(<<<SQL
UPDATE produto.produtos_coberturas_eventos ppce_update
   SET fl_excluir_aviso_sinistro = false
  FROM produto.produtos pp
       INNER JOIN produto.produtos_coberturas ppc ON ppc.id_produto = pp.id
       INNER JOIN produto.coberturas pc ON pc.id = ppc.id_cobertura
       INNER JOIN produto.coberturas_eventos pce ON pce.id_cobertura = pc.id
       INNER JOIN produto.produtos_coberturas_eventos ppce ON ppce.id_produto = pp.id
                                                          AND ppce.id_cobertura = pc.id
                                                          AND ppce.id_evento = pce.id_evento
 WHERE pp.id_safra in(25, 26)
   AND pc.id in(18, 29, 32)
   AND pce.id_evento = 10
   AND ppce.id_produto = ppce_update.id_produto
   AND ppce.id_cobertura = ppce_update.id_cobertura
   AND ppce.id_evento = ppce_update.id_evento;

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