<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

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

CREATE MATERIALIZED VIEW sumario.relatorio_sinistralidade_itens_segurados AS (
 SELECT lf.id
    ,lfis.id as id_laudo_final_item_segurado
    ,lfis.vl_percent_perdas
    ,lfis.dt_vistoria
    ,lfis.dt_criacao
    ,lfis.vl_percent_colhido
    ,lfis.vl_area_sinistrada
    ,lfis.vl_percent_area_afetada
    ,lfis.vl_rendimento_final_lavoura
    ,lfis.vl_percent_perdas_chocho
    ,lfis.vl_percent_colhido_chocho
    ,lfis.id_motivo_quadra_nao_vistoriada
    ,co.id as id_cobertura
    ,co.ds_nome_cobertura
    ,svis.id_vistoria
    ,pr.id as id_proposta
    ,lfis.id_item_segurado
    ,its.nr_item_segurado
    ,pr.id_endosso
    ,pp.id_safra
    ,CASE WHEN lf003.vl_graos_podres IS NOT NULL
        THEN lf003.vl_graos_podres
        ELSE lf034.vl_graos_podres
        END as vl_graos_podres
    ,CASE WHEN lf003.vl_graos_brotados IS NOT NULL
        THEN lf003.vl_graos_brotados
        ELSE lf034.vl_graos_brotados
        END as vl_graos_brotados
    ,CASE WHEN lf003.vl_pragas_doencas_outros IS NOT NULL
        THEN lf003.vl_pragas_doencas_outros
        ELSE lf034.vl_pragas_doencas_outros
        END as vl_pragas_doencas_outros
FROM sinistro.laudos_finais lf
     INNER JOIN sinistro.laudos_finais_itens_segurados lfis ON lfis.id_laudo_final = lf.id
     INNER JOIN seguro.itens_segurados its ON its.id = lfis.id_item_segurado
     INNER JOIN produto.coberturas co ON co.id = lf.id_cobertura
     INNER JOIN seguro.propostas pr ON  pr.id = its.id_proposta
     INNER JOIN produto.produtos pp ON pp.id = pr.id_produto
     INNER JOIN sistema.vistorias_laudos_finais svlf ON svlf.id_laudo_final = lf.id
     INNER JOIN sistema.vistorias_itens_segurados svis ON svis.id_vistoria = svlf.id_vistoria AND svis.id_item_segurado = lfis.id_item_segurado
     LEFT JOIN regulacao.lf003_itens_segurados lf003 ON lf003.id_laudo_final_item_segurado = lfis.id
     LEFT JOIN regulacao.lf034_itens_segurados lf034 ON lf034.id_laudo_final_item_segurado = lfis.id
);
SQL
        );

        $this->createViewDependente();
    }

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

CREATE MATERIALIZED VIEW sumario.relatorio_sinistralidade_itens_segurados AS (
 SELECT lf.id,
    lfis.id AS id_laudo_final_item_segurado,
    lfis.vl_percent_perdas,
    lfis.dt_vistoria,
    lfis.dt_criacao,
    lfis.vl_percent_colhido,
    lfis.vl_area_sinistrada,
    lfis.vl_percent_area_afetada,
    lfis.vl_rendimento_final_lavoura,
    lfis.vl_percent_perdas_chocho,
    lfis.vl_percent_colhido_chocho,
    lfis.id_motivo_quadra_nao_vistoriada,
    co.ds_nome_cobertura,
        CASE
            WHEN lf003.vl_graos_podres IS NOT NULL THEN lf003.vl_graos_podres
            ELSE lf034.vl_graos_podres
        END AS vl_graos_podres,
        CASE
            WHEN lf003.vl_graos_brotados IS NOT NULL THEN lf003.vl_graos_brotados
            ELSE lf034.vl_graos_brotados
        END AS vl_graos_brotados,
        CASE
            WHEN lf003.vl_pragas_doencas_outros IS NOT NULL THEN lf003.vl_pragas_doencas_outros
            ELSE lf034.vl_pragas_doencas_outros
        END AS vl_pragas_doencas_outros,
    its.nr_item_segurado,
    pr.id_endosso,
    pd.id_safra
   FROM sinistro.laudos_finais lf
     JOIN sinistro.laudos_finais_itens_segurados lfis ON lfis.id_laudo_final = lf.id
     JOIN seguro.itens_segurados its ON its.id = lfis.id_item_segurado
     JOIN produto.coberturas co ON co.id = lf.id_cobertura
     JOIN seguro.propostas pr ON pr.id = its.id_proposta
     JOIN produto.produtos pd ON pd.id = pr.id_produto
     LEFT JOIN regulacao.lf003_itens_segurados lf003 ON lf003.id_laudo_final_item_segurado = lfis.id
     LEFT JOIN regulacao.lf034_itens_segurados lf034 ON lf034.id_laudo_final_item_segurado = lfis.id
);
SQL
        );

        $this->createViewDependente();
    }

    private function createViewDependente()
    {
        $this->execute(<<<SQL
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
    );

    }
}