<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskInc47317 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
            DROP MATERIALIZED VIEW IF EXISTS sumario.relatorio_sinistralidade_processada;

            CREATE MATERIALIZED VIEW sumario.relatorio_sinistralidade_processada AS (
                SELECT pr.id_proposta_mae AS id_proposta,
                    pr.id_endosso,
                    sp.id AS id_processo,
                    pp.ds_nome_proponente,
                    m.ds_nome_municipio,
                    m.nr_ibge,
                    es.ds_sigla,
                    e.ds_nome_fantasia,
                    pd.ds_nome_produto,
                    pd.id_safra,
                    its.id AS id_item_segurado,
                    its.nr_item_segurado,
                    its.ds_item_segurado,
                    its.vl_area,
                    its.vl_lmga,
                    v.ds_nome_variedade,
                    pg.id_cultura,
                    cu.ds_nome_cultura,
                    rsis.vl_percent_perdas,
                    rsis.vl_percent_colhido,
                    rsis.dt_vistoria,
                    rsis.dt_criacao,
                    rsis.ds_nome_cobertura,
                    rsis.vl_percent_perdas_chocho,
                    rsis.vl_percent_colhido_chocho,
                    rsis.id_laudo_final_item_segurado,
                    CASE WHEN cu.id = any(array[80,81]) AND rsis.id_laudo_final_item_segurado > 0
                         THEN coalesce((select 'Pré-Floração' as floracao
                                 from regulacao.lf005_itens_segurados lis
                                 where lis.id_laudo_final_item_segurado = rsis.id_laudo_final_item_segurado
                                     and fl_floracao ilike 'PRE'
                         ), 'Pós-Floração')
                         ELSE NULL::text
                    END as fase_cultura,
                    ARRAY( SELECT u.ds_nome_usuario
                        FROM sinistro.laudos_finais_itens_segurados_funcionarios lfis,
                            sistema.funcionarios f,
                            sistema.usuarios u
                        WHERE f.id = lfis.id_funcionario AND u.id = f.id_usuario AND lfis.id_laudo_final_item_segurado = rsis.id_laudo_final_item_segurado) AS ds_nome_usuarios,
                    NULL::text AS vl_percent_perdas_nao_sinistrado,
                    NULL::text AS vl_percent_colhido_nao_sinistrado,
                    rvpns.dt_vistoria AS dt_vistoria_nao_sinistrado,
                    rvpns.dt_criacao AS dt_criacao_nao_sinistrado,
                    rvpns.ds_nome_cobertura AS ds_nome_cobertura_nao_sinistrado,
                    NULL::text AS vl_percent_perdas_chocho_nao_sinistrado,
                    NULL::text AS vl_percent_colhido_chocho_nao_sinistrado,
                    ''::text AS fase_cultura_nao_sinistrado,
                    ARRAY( SELECT u.ds_nome_usuario
                        FROM sinistro.laudos_finais_funcionarios lf,
                            sistema.funcionarios f,
                            sistema.usuarios u
                        WHERE f.id = lf.id_funcionario AND u.id = f.id_usuario AND lf.id_laudo_final = rvpns.id) AS ds_nome_usuarios_nao_sinistrado
                FROM seguro.propostas pr
                    JOIN seguro.propostas_endosso pre ON pre.id_proposta = pr.id AND pre.fl_proposta_vigente = true
                    JOIN seguro.itens_segurados its ON its.id_proposta = pr.id
                    JOIN seguro.propostas_proponentes pp ON pr.id = pp.id_proposta
                    JOIN seguro.propostas_propriedades po ON pr.id = po.id_proposta
                    JOIN sistema.municipios m ON po.id_municipio = m.id
                    JOIN sistema.estados es ON m.id_estado = es.id
                    JOIN produto.produtos pd ON pr.id_produto = pd.id
                    JOIN produto.produtos_geral pg ON pd.id_produto_geral = pg.id
                    JOIN produto.culturas cu ON pg.id_cultura = cu.id
                    JOIN produto.variedades v ON its.id_variedade = v.id
                    JOIN sinistro.processos sp ON pr.id = sp.id_proposta
                    JOIN sinistro.processos_empresas pe ON sp.id = pe.id_processo
                    JOIN sistema.empresas e ON pe.id_empresa = e.id
                    LEFT JOIN sumario.relatorio_sinistralidade_itens_segurados_vistorias itstemp ON itstemp.nr_item_segurado = its.nr_item_segurado AND itstemp.id_endosso = pr.id_endosso
                    LEFT JOIN sumario.relatorio_sinistralidade_itens_segurados rsis ON its.nr_item_segurado = rsis.nr_item_segurado AND pr.id_endosso = rsis.id_endosso AND pd.id_safra = rsis.id_safra
                    LEFT JOIN sumario.relatorio_vistorias_preliminares_nao_sinistrados rvpns ON sp.id = rvpns.id_processo
                WHERE (pd.id_safra IN ( SELECT safras.id
                                          FROM produto.safras
                                          JOIN (SELECT safras_1.id
                                                  FROM produto.safras safras_1
                                                 WHERE safras_1.fl_vigente = true) vigente ON vigente.id >= safras.id
                                         WHERE safras.id = vigente.id
                                            OR safras.id = (vigente.id - 1)))
                  AND pg.id_tipo_produto <> 3
                  AND pe.fl_ativo = true
                ORDER BY pr.id, rsis.dt_vistoria, its.nr_item_segurado
            );
SQL
        );
    }

    public function down(): void
    {
        $this->execute(<<<SQL
            DROP MATERIALIZED VIEW IF EXISTS sumario.relatorio_sinistralidade_processada;

            CREATE MATERIALIZED VIEW sumario.relatorio_sinistralidade_processada AS (
                SELECT pr.id_proposta_mae AS id_proposta,
                    pr.id_endosso,
                    sp.id AS id_processo,
                    pp.ds_nome_proponente,
                    m.ds_nome_municipio,
                    m.nr_ibge,
                    es.ds_sigla,
                    e.ds_nome_fantasia,
                    pd.ds_nome_produto,
                    pd.id_safra,
                    its.id AS id_item_segurado,
                    its.nr_item_segurado,
                    its.ds_item_segurado,
                    its.vl_area,
                    its.vl_lmga,
                    v.ds_nome_variedade,
                    pg.id_cultura,
                    cu.ds_nome_cultura,
                    rsis.vl_percent_perdas,
                    rsis.vl_percent_colhido,
                    rsis.dt_vistoria,
                    rsis.dt_criacao,
                    rsis.ds_nome_cobertura,
                    rsis.vl_percent_perdas_chocho,
                    rsis.vl_percent_colhido_chocho,
                    rsis.id_laudo_final_item_segurado,
                        CASE
                            WHEN (cu.id = ANY (ARRAY[80, 81])) AND rsis.id_laudo_final_item_segurado > 0 THEN ( SELECT
                                    CASE
                                        WHEN lis.fl_floracao::text = 'PRE'::text THEN 'Pré-Floração'::text
                                        ELSE 'Pós-Floração'::text
                                    END AS floracao
                            FROM regulacao.lf005_itens_segurados lis
                            WHERE lis.id_laudo_final_item_segurado = rsis.id_laudo_final_item_segurado)
                            ELSE NULL::text
                        END AS fase_cultura,
                    ARRAY( SELECT u.ds_nome_usuario
                        FROM sinistro.laudos_finais_itens_segurados_funcionarios lfis,
                            sistema.funcionarios f,
                            sistema.usuarios u
                        WHERE f.id = lfis.id_funcionario AND u.id = f.id_usuario AND lfis.id_laudo_final_item_segurado = rsis.id_laudo_final_item_segurado) AS ds_nome_usuarios,
                    NULL::text AS vl_percent_perdas_nao_sinistrado,
                    NULL::text AS vl_percent_colhido_nao_sinistrado,
                    rvpns.dt_vistoria AS dt_vistoria_nao_sinistrado,
                    rvpns.dt_criacao AS dt_criacao_nao_sinistrado,
                    rvpns.ds_nome_cobertura AS ds_nome_cobertura_nao_sinistrado,
                    NULL::text AS vl_percent_perdas_chocho_nao_sinistrado,
                    NULL::text AS vl_percent_colhido_chocho_nao_sinistrado,
                    ''::text AS fase_cultura_nao_sinistrado,
                    ARRAY( SELECT u.ds_nome_usuario
                        FROM sinistro.laudos_finais_funcionarios lf,
                            sistema.funcionarios f,
                            sistema.usuarios u
                        WHERE f.id = lf.id_funcionario AND u.id = f.id_usuario AND lf.id_laudo_final = rvpns.id) AS ds_nome_usuarios_nao_sinistrado
                FROM seguro.propostas pr
                    JOIN seguro.propostas_endosso pre ON pre.id_proposta = pr.id AND pre.fl_proposta_vigente = true
                    JOIN seguro.itens_segurados its ON its.id_proposta = pr.id
                    JOIN seguro.propostas_proponentes pp ON pr.id = pp.id_proposta
                    JOIN seguro.propostas_propriedades po ON pr.id = po.id_proposta
                    JOIN sistema.municipios m ON po.id_municipio = m.id
                    JOIN sistema.estados es ON m.id_estado = es.id
                    JOIN produto.produtos pd ON pr.id_produto = pd.id
                    JOIN produto.produtos_geral pg ON pd.id_produto_geral = pg.id
                    JOIN produto.culturas cu ON pg.id_cultura = cu.id
                    JOIN produto.variedades v ON its.id_variedade = v.id
                    JOIN sinistro.processos sp ON pr.id = sp.id_proposta
                    JOIN sinistro.processos_empresas pe ON sp.id = pe.id_processo
                    JOIN sistema.empresas e ON pe.id_empresa = e.id
                    LEFT JOIN sumario.relatorio_sinistralidade_itens_segurados_vistorias itstemp ON itstemp.nr_item_segurado = its.nr_item_segurado AND itstemp.id_endosso = pr.id_endosso
                    LEFT JOIN sumario.relatorio_sinistralidade_itens_segurados rsis ON its.nr_item_segurado = rsis.nr_item_segurado AND pr.id_endosso = rsis.id_endosso AND pd.id_safra = rsis.id_safra
                    LEFT JOIN sumario.relatorio_vistorias_preliminares_nao_sinistrados rvpns ON sp.id = rvpns.id_processo
                WHERE (pd.id_safra IN ( SELECT safras.id
                        FROM produto.safras
                            JOIN ( SELECT safras_1.id
                                FROM produto.safras safras_1
                                WHERE safras_1.fl_vigente = true) vigente ON vigente.id >= safras.id
                        WHERE safras.id = vigente.id OR safras.id = (vigente.id - 1))) AND pg.id_tipo_produto <> 3
                ORDER BY pr.id, its.nr_item_segurado
            );
SQL
        );
    }
}