<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

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

CREATE MATERIALIZED VIEW geo.v_subscricao_coordenadas AS
(
SELECT row_number() OVER (order by id_proposta_mae desc, numero_item) as id
     , 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
);

CREATE MATERIALIZED VIEW geo.v_coordenadas AS
(
SELECT pr.id as id_proposta
     , pr.dt_vigencia_inicio
     , m.ds_nome_municipio
     , e.ds_sigla
     , it.nr_item_segurado
     , itc.ds_coordenadas_geograficas
     , pp.ds_coordenadas
     , st.ds_chave
     , st.cd_status
     , m.nr_ibge
  FROM seguro.propostas pr
       JOIN seguro.propostas_propriedades pp ON pp.id_proposta = pr.id
       JOIN seguro.propostas_corretores pc ON pc.id_proposta = pr.id
       JOIN sistema.municipios m ON m.id = pp.id_municipio
       JOIN sistema.estados e ON e.id = m.id_estado
       JOIN produto.produtos pd ON pd.id = pr.id_produto
       JOIN produto.produtos_geral pg ON pg.id = pd.id_produto_geral
       JOIN produto.culturas c ON c.id = pg.id_cultura
       JOIN sistema.status st ON st.id = pr.id_status
       JOIN seguro.itens_segurados it ON it.id_proposta = pr.id
       JOIN seguro.itens_segurados_complemento itc ON itc.id_item_segurado = it.id
       JOIN seguro.propostas_endosso spe ON spe.id_proposta = pr.id AND spe.fl_proposta_vigente IS TRUE
       JOIN produto.safras ps ON ps.id = pd.id_safra
 WHERE ps.fl_vigente is true
   AND st.ds_chave NOT IN (
         'APOLICE_CANCELADA',
         'DEVOLVIDA',
         'ENDOSSO_INCOMPLETO',
         'NAO_ENVIADA',
         'ORCAMENTO_ENDOSSO',
         'PROPOSTA_CANCELADA',
         'PROPOSTA_INCOMPLETA',
         'SOLICITACAO_CANCELADA'
       )
   AND pc.id_usuario <> 487
 ORDER BY pr.id, it.nr_item_segurado
);
SQL
        );
    }

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

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