<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskSR47598V2 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
CREATE OR REPLACE FUNCTION coordenadas_dms_para_decimal(dms_text TEXT)
    RETURNS TEXT AS
$$
DECLARE
    dms TEXT[] := regexp_match(dms_text, '(\d+)\s(\d+)\s(\d+)\D+(\d+)\s(\d+)\s(\d+)');
    latitude float8;
    longitude float8;
    latitudeResultado float8;
    longitudeResultado float8;
BEGIN
    latitude := dms[1]::float8 + dms[2]::float8/60 + dms[3]::float8/3600;
    longitude := dms[4]::float8 + dms[5]::float8/60 + dms[6]::float8/3600;

    latitude := -1 * latitude;
    longitude := -1 * longitude;

    RETURN to_char(latitude, 'FM999999999.00000000') || ' ' || to_char(longitude, 'FM999999999.00000000');
END;
$$
    LANGUAGE 'plpgsql'
    IMMUTABLE
    STRICT;

DROP MATERIALIZED VIEW geo.v_coordenadas;
CREATE MATERIALIZED VIEW geo.v_coordenadas AS
(
SELECT id_proposta
     , dt_vigencia_inicio
     , ds_nome_municipio
     , ds_sigla
     , nr_item_segurado
     , ds_coordenadas_geograficas
     , ds_coordenadas
     , ds_chave
     , cd_status
     , nr_ibge
     , btrim(coordenadas_decimais[1]) as latitude
     , btrim(coordenadas_decimais[2]) as longitude
  FROM (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
             ,  string_to_array(coordenadas_dms_para_decimal(pp.ds_coordenadas), ' ') as coordenadas_decimais
        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) as result
);
       
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
        );
    }

    public function down(): void
    {
        $this->execute(<<<SQL
DROP MATERIALIZED VIEW geo.v_coordenadas;
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
);
       
DROP FUNCTION coordenadas_dms_para_decimal;

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