<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskSr51176 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
            INSERT INTO manutencao.materialized_views (dt_criacao, migration_name, ordem, schemaname, matviewname)
                VALUES ('2024-08-11', 'TaskSR47598V2', 24 , 'geo', 'v_coordenadas');

            DROP MATERIALIZED VIEW geo.v_subscricao;
            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
                    , spct.ds_coordenadas AS ds_coordenadas
                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
                        INNER 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, ','))
                        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
            );
SQL
        );
    }

    public function down(): void
    {
        $this->execute(<<<SQL
            DELETE FROM manutencao.materialized_views WHERE schemaname = 'geo' AND matviewname = 'v_coordenadas';

            DROP MATERIALIZED VIEW geo.v_subscricao;
            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
            );
SQL
        );
    }
}