<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskINC45393 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
DROP MATERIALIZED VIEW geo.v_subscricao;
DROP MATERIALIZED VIEW geo.v_subscricao_coordenadas;

CREATE MATERIALIZED VIEW geo.v_subscricao AS
(
SELECT sp.id_proposta_mae                            as id_proposta_mae
     , sp.id                                         as id_proposta
     , ss.cd_status                                  as status_endosso
     , ps.ds_nome_safra                              as nome_safra
     , sprop.id                                      as id_segurado
     , pc.ds_nome_cultura                            as nome_cultura
     , CASE
           WHEN ppg.id_tipo_produto = 2
               THEN
               CASE
                   WHEN ppg.tp_epoca_cultivo = 'I'
                       THEN 'GRÃOS INVERNO'
                   ELSE 'GRÃOS VERÃO'
                   END
           ELSE ptp.ds_tipo_produto
    END                                              AS nome_grupo
     , CASE
           WHEN sc.id is not null
               THEN sc.ds_nome_fantasia
           ELSE sc2.ds_nome_fantasia
    END                                              AS nome_fantasia_corretor
     , CASE
           WHEN sprep.id is not null
               THEN sprep.ds_nome_preposto
           ELSE ''
    END                                              AS nome_sublogin
     , sm.nr_ibge                                    as codigo_ibge
     , sm.ds_nome_municipio                          as nome_municipio
     , se.ds_sigla                                   as sigla_uf
     , spc.id_cobertura                              as id_cobertura_principal
     , CASE
           WHEN coberturas_adicionais.ids_cobertura[1] is not null
               THEN coberturas_adicionais.ids_cobertura[1]
    END                                              AS cobertura_adicional_1
     , CASE
           WHEN coberturas_adicionais.ids_cobertura[2] is not null
               THEN coberturas_adicionais.ids_cobertura[2]
    END                                              AS cobertura_adicional_2
     , CASE
           WHEN coberturas_adicionais.ids_cobertura[3] is not null
               THEN coberturas_adicionais.ids_cobertura[3]
    END                                              AS cobertura_adicional_3
     , CASE
           WHEN coberturas_adicionais.ids_cobertura[4] is not null
               THEN coberturas_adicionais.ids_cobertura[4]
    END                                              AS cobertura_adicional_4
     , CASE
           WHEN coberturas_adicionais.ids_cobertura[5] is not null
               THEN coberturas_adicionais.ids_cobertura[5]
    END                                              AS cobertura_adicional_5
     , CASE
           WHEN coberturas_adicionais.ids_cobertura[6] is not null
               THEN coberturas_adicionais.ids_cobertura[6]
    END                                              AS cobertura_adicional_6
     , CASE
           WHEN coberturas_adicionais.ids_cobertura[7] is not null
               THEN coberturas_adicionais.ids_cobertura[7]
    END                                              AS cobertura_adicional_7
     , sis.vl_lmga                                   as valor_lmga
     , sis.vl_area                                   as valor_area
     , to_char(sp1.dt_vigencia_inicio, 'dd/mm/yyyy') as vigencia_inicio
     , to_char(sp1.dt_vigencia_fim, 'dd/mm/yyyy')    as vigencia_fim
     , CASE
           WHEN sp.dt_transmissao IS NOT NULL
               THEN to_char(sp.dt_transmissao, 'dd/mm/yyyy')
           ELSE ''
    END                                              AS data_transmissao
     , sis.nr_item_segurado                          as numero_item
     , sis.ds_item_segurado                          as descricao_item
     , (sp.id::varchar || sis.nr_item_segurado)::integer        as proposta_item
FROM seguro.propostas sp
         INNER JOIN seguro.propostas_endosso spe ON spe.id_proposta = sp.id
         INNER JOIN produto.produtos pp ON pp.id = sp.id_produto
         INNER JOIN produto.produtos_geral ppg ON ppg.id = pp.id_produto_geral
         INNER JOIN produto.culturas pc ON pc.id = ppg.id_cultura
         INNER JOIN produto.tipo_produto ptp ON ptp.id = ppg.id_tipo_produto
         INNER JOIN sistema.status ss ON ss.id = sp.id_status
         INNER JOIN produto.safras ps ON ps.id = pp.id_safra
         INNER JOIN seguro.propostas_proponentes spp ON spp.id_proposta = sp.id
         INNER JOIN seguro.proponentes sprop ON sprop.cpf_cnpj = spp.cpf_cnpj
         INNER JOIN sistema.usuarios su ON su.id = sp.id_usuario_criacao
         LEFT JOIN sistema.corretores sc ON sc.id_usuario = su.id
         LEFT JOIN sistema.prepostos sprep ON sprep.id_usuario = su.id
         LEFT JOIN sistema.corretores sc2 ON sc2.id = sprep.id_corretor
         INNER JOIN seguro.propostas_propriedades sppropriedades ON sppropriedades.id_proposta = sp.id
         INNER JOIN sistema.municipios sm ON sm.id = sppropriedades.id_municipio
         INNER JOIN sistema.estados se ON se.id = sm.id_estado
         INNER JOIN seguro.propostas sp1 ON sp1.id = sp.id_proposta_mae
         INNER JOIN seguro.itens_segurados sis ON sis.id_proposta = sp.id
         INNER JOIN seguro.propostas_coberturas spc ON spc.id_proposta = sp.id AND spc.fl_principal is true
         LEFT JOIN (SELECT array_agg(id_cobertura) as ids_cobertura, id_proposta
                    FROM seguro.propostas_coberturas
                    WHERE fl_principal is false
                    GROUP BY id_proposta) as coberturas_adicionais ON coberturas_adicionais.id_proposta = sp.id
WHERE ps.fl_vigente is true
  AND sis.vl_lmga > 0
  AND spe.fl_proposta_vigente is true
  AND sp.id_status NOT IN (2, 4, 5)
ORDER BY sp.id desc, sis.nr_item_segurado ASC
    );

CREATE MATERIALIZED VIEW geo.v_subscricao_coordenadas AS
(
SELECT id_proposta_mae
     , id_proposta
     , btrim(coordenadas_item[1]) as latitude
     , btrim(coordenadas_item[2]) as longitude
     , numero_item
     , proposta_item::integer
FROM (SELECT id_proposta_mae
           , id_proposta
           , string_to_array(unnest(meu_novo_array), ' ') as coordenadas_item
           , numero_item
           , proposta_item
      FROM (SELECT id_proposta_mae
                 , id_proposta
                 , numero_item
                 , proposta_item
                 , array_append(coordenadas_item, coordenadas_item[1]) as meu_novo_array
            FROM (SELECT sp.id_proposta_mae                     as id_proposta_mae
                       , sp.id                                  as id_proposta
                       , string_to_array(replace(replace(regexp_replace(spct.ds_coordenadas, '[\[\]{}":]', '', 'g'), 'latitude', ''), ',longitude', ' '), ',') as coordenadas_item
                       , sis.nr_item_segurado                   as numero_item
                       , sp.id::varchar || sis.nr_item_segurado as proposta_item
                  FROM seguro.propostas sp
                           INNER JOIN seguro.propostas_endosso spe ON spe.id_proposta = sp.id
                           INNER JOIN produto.produtos pp ON pp.id = sp.id_produto
                           INNER JOIN produto.produtos_geral ppg ON ppg.id = pp.id_produto_geral
                           INNER JOIN produto.safras ps ON ps.id = pp.id_safra
                           INNER JOIN seguro.itens_segurados sis ON sis.id_proposta = sp.id
                           LEFT JOIN seguro.propostas_croquis spcroquis ON spcroquis.id_proposta = sp.id AND spcroquis.fl_vigente is true
                           LEFT JOIN seguro.propostas_croquis_talhoes spct ON spct.id_croqui = spcroquis.id AND sis.id::varchar = any(string_to_array(spct.ds_item_segurado, ','))
                  WHERE ps.fl_vigente is true
                    AND sis.vl_lmga > 0
                    AND spe.fl_proposta_vigente is true
                    AND sp.id_status NOT IN (2, 4, 5)
                  ORDER BY sp.id desc, sis.nr_item_segurado
                 ) AS subscricao_inicial
           ) AS subscricao_coordenadas_base
     ) AS subscricao_coordenadas_final
    );
SQL
);
    }

    public function down(): void
    {
        $this->execute(<<<SQL
DROP MATERIALIZED VIEW geo.v_subscricao;
DROP MATERIALIZED VIEW geo.v_subscricao_coordenadas;

CREATE MATERIALIZED VIEW geo.v_subscricao AS
(
SELECT sp.id_proposta_mae                            as id_proposta_mae
     , sp.id                                         as id_proposta
     , ss.cd_status                                  as status_endosso
     , ps.ds_nome_safra                              as nome_safra
     , sprop.id                                      as id_segurado
     , pc.ds_nome_cultura                            as nome_cultura
     , CASE
           WHEN ppg.id_tipo_produto = 2
               THEN
               CASE
                   WHEN ppg.tp_epoca_cultivo = 'I'
                       THEN 'GRÃOS INVERNO'
                   ELSE 'GRÃOS VERÃO'
                   END
           ELSE ptp.ds_tipo_produto
    END                                              AS nome_grupo
     , CASE
           WHEN sc.id is not null
               THEN sc.ds_nome_fantasia
           ELSE sc2.ds_nome_fantasia
    END                                              AS nome_fantasia_corretor
     , CASE
           WHEN sprep.id is not null
               THEN sprep.ds_nome_preposto
           ELSE ''
    END                                              AS nome_sublogin
     , sm.nr_ibge                                    as codigo_ibge
     , sm.ds_nome_municipio                          as nome_municipio
     , se.ds_sigla                                   as sigla_uf
     , spc.id_cobertura                              as id_cobertura_principal
     , CASE
           WHEN coberturas_adicionais.ids_cobertura[1] is not null
               THEN coberturas_adicionais.ids_cobertura[1]
    END                                              AS cobertura_adicional_1
     , CASE
           WHEN coberturas_adicionais.ids_cobertura[2] is not null
               THEN coberturas_adicionais.ids_cobertura[2]
    END                                              AS cobertura_adicional_2
     , CASE
           WHEN coberturas_adicionais.ids_cobertura[3] is not null
               THEN coberturas_adicionais.ids_cobertura[3]
    END                                              AS cobertura_adicional_3
     , CASE
           WHEN coberturas_adicionais.ids_cobertura[4] is not null
               THEN coberturas_adicionais.ids_cobertura[4]
    END                                              AS cobertura_adicional_4
     , CASE
           WHEN coberturas_adicionais.ids_cobertura[5] is not null
               THEN coberturas_adicionais.ids_cobertura[5]
    END                                              AS cobertura_adicional_5
     , CASE
           WHEN coberturas_adicionais.ids_cobertura[6] is not null
               THEN coberturas_adicionais.ids_cobertura[6]
    END                                              AS cobertura_adicional_6
     , CASE
           WHEN coberturas_adicionais.ids_cobertura[7] is not null
               THEN coberturas_adicionais.ids_cobertura[7]
    END                                              AS cobertura_adicional_7
     , sis.vl_lmga                                   as valor_lmga
     , sis.vl_area                                   as valor_area
     , to_char(sp1.dt_vigencia_inicio, 'dd/mm/yyyy') as vigencia_inicio
     , to_char(sp1.dt_vigencia_fim, 'dd/mm/yyyy')    as vigencia_fim
     , CASE
           WHEN sp.dt_transmissao IS NOT NULL
               THEN to_char(sp.dt_transmissao, 'dd/mm/yyyy')
           ELSE ''
    END                                              AS data_transmissao
     , sis.nr_item_segurado                          as numero_item
     , sis.ds_item_segurado                          as descricao_item
     , (sp.id::varchar || sis.nr_item_segurado)::integer        as proposta_item
FROM seguro.propostas sp
         INNER JOIN seguro.propostas_endosso spe ON spe.id_proposta = sp.id
         INNER JOIN produto.produtos pp ON pp.id = sp.id_produto
         INNER JOIN produto.produtos_geral ppg ON ppg.id = pp.id_produto_geral
         INNER JOIN produto.culturas pc ON pc.id = ppg.id_cultura
         INNER JOIN produto.tipo_produto ptp ON ptp.id = ppg.id_tipo_produto
         INNER JOIN sistema.status ss ON ss.id = sp.id_status
         INNER JOIN produto.safras ps ON ps.id = pp.id_safra
         INNER JOIN seguro.propostas_proponentes spp ON spp.id_proposta = sp.id
         INNER JOIN seguro.proponentes sprop ON sprop.cpf_cnpj = spp.cpf_cnpj
         INNER JOIN sistema.usuarios su ON su.id = sp.id_usuario_criacao
         LEFT JOIN sistema.corretores sc ON sc.id_usuario = su.id
         LEFT JOIN sistema.prepostos sprep ON sprep.id_usuario = su.id
         LEFT JOIN sistema.corretores sc2 ON sc2.id = sprep.id_corretor
         INNER JOIN seguro.propostas_propriedades sppropriedades ON sppropriedades.id_proposta = sp.id
         INNER JOIN sistema.municipios sm ON sm.id = sppropriedades.id_municipio
         INNER JOIN sistema.estados se ON se.id = sm.id_estado
         INNER JOIN seguro.propostas sp1 ON sp1.id = sp.id_proposta_mae
         INNER JOIN seguro.itens_segurados sis ON sis.id_proposta = sp.id
         INNER JOIN seguro.propostas_coberturas spc ON spc.id_proposta = sp.id AND spc.fl_principal is true
         LEFT JOIN (SELECT array_agg(id_cobertura) as ids_cobertura, id_proposta
                    FROM seguro.propostas_coberturas
                    WHERE fl_principal is false
                    GROUP BY id_proposta) as coberturas_adicionais ON coberturas_adicionais.id_proposta = sp.id
WHERE ppg.id_tipo_produto in (1, 2)
  AND ps.fl_vigente is true
  AND sis.vl_lmga > 0
  AND spe.fl_proposta_vigente is true
  AND sp.id_status NOT IN (2, 4, 5)
ORDER BY sp.id desc, sis.nr_item_segurado ASC
    );

CREATE MATERIALIZED VIEW geo.v_subscricao_coordenadas AS
(
SELECT id_proposta_mae
     , id_proposta
     , btrim(coordenadas_item[1]) as latitude
     , btrim(coordenadas_item[2]) as longitude
     , numero_item
     , proposta_item::integer
FROM (SELECT id_proposta_mae
           , id_proposta
           , string_to_array(unnest(meu_novo_array), ' ') as coordenadas_item
           , numero_item
           , proposta_item
      FROM (SELECT id_proposta_mae
                 , id_proposta
                 , numero_item
                 , proposta_item
                 , array_append(coordenadas_item, coordenadas_item[1]) as meu_novo_array
            FROM (SELECT sp.id_proposta_mae                     as id_proposta_mae
                       , sp.id                                  as id_proposta
                       , string_to_array(replace(replace(regexp_replace(spct.ds_coordenadas, '[\[\]{}":]', '', 'g'), 'latitude', ''), ',longitude', ' '), ',') as coordenadas_item
                       , sis.nr_item_segurado                   as numero_item
                       , sp.id::varchar || sis.nr_item_segurado as proposta_item
                  FROM seguro.propostas sp
                           INNER JOIN seguro.propostas_endosso spe ON spe.id_proposta = sp.id
                           INNER JOIN produto.produtos pp ON pp.id = sp.id_produto
                           INNER JOIN produto.produtos_geral ppg ON ppg.id = pp.id_produto_geral
                           INNER JOIN produto.safras ps ON ps.id = pp.id_safra
                           INNER JOIN seguro.itens_segurados sis ON sis.id_proposta = sp.id
                           LEFT JOIN seguro.propostas_croquis spcroquis ON spcroquis.id_proposta = sp.id AND spcroquis.fl_vigente is true
                           LEFT JOIN seguro.propostas_croquis_talhoes spct ON spct.id_croqui = spcroquis.id AND sis.id::varchar = any(string_to_array(spct.ds_item_segurado, ','))
                  WHERE ppg.id_tipo_produto in (1, 2)
                    AND ps.fl_vigente is true
                    AND sis.vl_lmga > 0
                    AND spe.fl_proposta_vigente is true
                    AND sp.id_status NOT IN (2, 4, 5)
                  ORDER BY sp.id desc, sis.nr_item_segurado
                 ) AS subscricao_inicial
           ) AS subscricao_coordenadas_base
     ) AS subscricao_coordenadas_final
);
SQL
);
    }
}