<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskSR50062 extends AbstractMigration
{
    public function up(): void
    {
        $this->dropViews();

        $this->execute(<<<SQL
CREATE MATERIALIZED VIEW sumario.relatorio_sinistralidade_itens_segurados_vistorias AS (
SELECT DISTINCT(its.nr_item_segurado)
     , pr.id_endosso
 FROM sinistro.laudos_finais_itens_segurados lfis
 	  INNER JOIN seguro.itens_segurados its ON its.id = lfis.id_item_segurado
 	  INNER JOIN seguro.propostas pr ON pr.id = its.id_proposta
 	  INNER JOIN produto.produtos pp ON pp.id = pr.id_produto
WHERE lfis.fl_nao_vistoriada = false
  AND (pp.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)))
);

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

        $this->createDependentViews();
    }

    public function down(): void
    {
        $this->dropViews();

        $this->execute(<<<SQL
CREATE MATERIALIZED VIEW sumario.relatorio_sinistralidade_itens_segurados_vistorias AS (
 SELECT DISTINCT(its.nr_item_segurado)
 	  , pr.id_endosso
 FROM sinistro.laudos_finais_itens_segurados lfis
 	  INNER JOIN seguro.itens_segurados its ON its.id = lfis.id_item_segurado
 	  INNER JOIN seguro.propostas pr ON pr.id = its.id_proposta
 WHERE lfis.fl_nao_vistoriada = false
 );

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

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

        $this->createDependentViews();
    }

    private function createDependentViews()
    {
        $this->execute(<<<SQL
CREATE MATERIALIZED VIEW sumario.relatorio_sinistralidade 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
FROM
	  seguro.propostas pr
	  INNER JOIN seguro.propostas_endosso pre ON pre.id_proposta = pr.id AND pre.fl_proposta_vigente = true
	  INNER JOIN seguro.itens_segurados its ON its.id_proposta = pr.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
	 ,seguro.propostas_proponentes pp
	 ,seguro.propostas_propriedades po
	 ,sistema.municipios m
	 ,sistema.estados es
	 ,produto.produtos pd
	 ,produto.produtos_geral pg
	 ,produto.culturas cu
	 ,produto.variedades v
	 ,sinistro.processos sp
	 ,sinistro.processos_empresas pe
	 ,sistema.empresas e
WHERE
	pd.id_safra IN (
	SELECT safras.id
	  FROM produto.safras
	  JOIN ( SELECT id
			   FROM produto.safras
			  WHERE fl_vigente = TRUE
	  ) AS vigente
		ON vigente.id >= safras.id
	 WHERE safras.id = vigente.id
		OR safras.id = vigente.id -1
	 )
	 AND pe.fl_ativo = true
	 AND pd.id = pr.id_produto
	 AND pg.id = pd.id_produto_geral
	 AND cu.id = pg.id_cultura
	 AND pp.id_proposta = pr.id
	 AND po.id_proposta = pr.id
	 AND m.id = po.id_municipio
	 AND es.id = m.id_estado
	 AND sp.id_proposta = pr.id
	 AND its.id_variedade = v.id
	 AND pe.id_processo = sp.id
	 AND e.id = pe.id_empresa
	 AND pg.id_tipo_produto <> 3
ORDER BY
	 pr.id, its.nr_item_segurado
);
SQL
        );
    }

    private function dropViews()
    {
        $this->execute(<<<SQL
DROP MATERIALIZED VIEW sumario.relatorio_sinistralidade_processada;
DROP MATERIALIZED VIEW sumario.relatorio_sinistralidade_itens_segurados;
DROP MATERIALIZED VIEW sumario.relatorio_sinistralidade;
DROP MATERIALIZED VIEW sumario.relatorio_sinistralidade_itens_segurados_vistorias
SQL
        );
    }
}