<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskINC51300 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
DROP MATERIALIZED VIEW 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
     , lfis.vl_ha_area_replantada
     , ppc.fl_cobertura_principal as cobertura_principal
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 produto.produtos_coberturas ppc ON ppc.id_produto = pp.id AND ppc.id_cobertura = co.id
     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
);

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,
        rsis.id_cobertura,
        rsis.id_motivo_quadra_nao_vistoriada,
        rsis.vl_ha_area_replantada,
        rsis.cobertura_principal,
        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 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
     , lfis.vl_ha_area_replantada
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
);

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,
        rsis.id_cobertura,
        rsis.id_motivo_quadra_nao_vistoriada,
        rsis.vl_ha_area_replantada,
        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
        );
    }
}