<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskINC49232 extends AbstractMigration
{
    public function up(): void
    {
        $this->dropViews();
        $this->execute(<<<SQL
CREATE MATERIALIZED VIEW sumario.relatorio_vistorias_preliminares AS (
SELECT
	  pr.id_proposta_mae AS id_proposta
	 ,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 (
		SELECT
			pr.id AS id_proposta
		FROM
			sinistro.laudos_preliminares lp,
			sinistro.processos sp,
			seguro.propostas pr
		WHERE
			lp.id_processo = sp.id AND
			sp.id_proposta = pr.id
			AND lp.id_status NOT IN (206, 208)
		group BY
		pr.id
	  ) AS pr_pre ON pr_pre.id_proposta = pr.id
	 INNER JOIN seguro.itens_segurados its ON its.id_proposta = pr.id
	 INNER JOIN seguro.propostas_proponentes pp ON pp.id_proposta = pr.id
	 INNER JOIN seguro.propostas_propriedades po ON po.id_proposta = pr.id
	 INNER JOIN sistema.municipios m ON m.id = po.id_municipio
	 INNER JOIN sistema.estados es ON es.id = m.id_estado
	 INNER JOIN produto.produtos pd ON pd.id = pr.id_produto
	 INNER JOIN produto.produtos_geral pg ON pg.id = pd.id_produto_geral
	 INNER JOIN produto.culturas cu ON cu.id = pg.id_cultura
	 INNER JOIN produto.variedades v ON v.id = its.id_variedade
	 INNER JOIN sinistro.processos sp ON sp.id_proposta = pr.id
	 INNER JOIN sinistro.processos_empresas pe ON pe.id_processo = sp.id AND pe.fl_ativo = true
	 INNER JOIN sistema.empresas e ON e.id = pe.id_empresa
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
	 )
ORDER BY
	pr.id, its.nr_item_segurado
);
       
CREATE MATERIALIZED VIEW sumario.relatorio_vistorias_preliminares_itens_segurados AS (
SELECT CASE
           WHEN i.ds_intensidade IS NOT NULL THEN SUBSTRING(i.ds_intensidade,1,1)||' '
           ELSE '0'
       END AS intensidade,
       lp.dt_vistoria AS dt_criacao,
       co.ds_nome_cobertura,
       vlp.id_vistoria,
       ef.ds_nome_estadio_fenologico,
       CASE WHEN lis2.vl_tamanho_planta is not null
                 THEN lis2.vl_tamanho_planta
            WHEN lis3.vl_tamanho_planta is not null
                 THEN lis3.vl_tamanho_planta
            WHEN lis4.vl_tamanho_planta is not null
                 THEN lis4.vl_tamanho_planta
            ELSE null
        END as tamanho_planta,
        string_agg(ds_nome_evento, ', ') as eventos,
        lp.fl_dt_evento_em_cobertura,
        lp.fl_evento_nao_constatado,
        lp.id_status,
        lp.id_cobertura,
        li.id_item_segurado
FROM sinistro.laudos_preliminares lp
     INNER JOIN produto.coberturas co ON co.id = lp.id_cobertura
     INNER JOIN sinistro.laudos_preliminares_itens_segurados li ON li.id_laudo_preliminar = lp.id
     INNER JOIN sistema.vistorias_laudos_preliminares vlp ON vlp.id_laudo_preliminar = lp.id
     LEFT  JOIN sinistro.vistorias_avisos va ON va.id_vistoria = vlp.id_vistoria
     LEFT  JOIN sinistro.avisos_eventos ae ON ae.id_aviso = va.id_aviso
     LEFT  JOIN produto.eventos e ON e.id = ae.id_evento
     LEFT  JOIN sinistro.intensidades i ON i.id = li.id_intensidade
     LEFT  JOIN produto.estadios_fenologicos ef ON ef.id = li.id_estadio_fenologico
     LEFT  JOIN regulacao.lp002_itens_segurados lis2 ON lis2.id_laudo_preliminar_item_segurado = li.id
     LEFT  JOIN regulacao.lp003_itens_segurados lis3 ON lis3.id_laudo_preliminar_item_segurado = li.id
     LEFT  JOIN regulacao.lp004_itens_segurados lis4 ON lis4.id_laudo_preliminar_item_segurado = li.id
WHERE lp.id_status NOT IN (206, 208)
GROUP BY
    i.ds_intensidade,
    lp.dt_vistoria,
    co.ds_nome_cobertura,
    vlp.id_vistoria,
    ef.ds_nome_estadio_fenologico,
    lis2.vl_tamanho_planta,
    lis3.vl_tamanho_planta,
    lis4.vl_tamanho_planta,
    lp.fl_dt_evento_em_cobertura,
    lp.fl_evento_nao_constatado,
    lp.id_status,
    lp.id_cobertura,
    li.id_item_segurado
ORDER BY
    lp.dt_vistoria
);
       
CREATE MATERIALIZED VIEW sumario.relatorio_vistorias_preliminares_nao_sinistrados AS (
SELECT co.ds_nome_cobertura,
       vlp.id_vistoria,
       ef.ds_nome_estadio_fenologico,
       li.id_item_segurado,
       CASE WHEN lis2.vl_tamanho_planta is not null
                THEN lis2.vl_tamanho_planta
           WHEN lis3.vl_tamanho_planta is not null
                THEN lis3.vl_tamanho_planta
           WHEN lis4.vl_tamanho_planta is not null
                THEN lis4.vl_tamanho_planta
           ELSE null
        END as tamanho_planta,
       lp.fl_dt_evento_em_cobertura,
       lp.fl_evento_nao_constatado,
       lp.id_status,
       lp.id_cobertura,
       lp.id_processo,
       lp.dt_vistoria,
       lp.dt_criacao,
       lp.id
 FROM sinistro.laudos_preliminares lp
      INNER JOIN produto.coberturas co ON co.id = lp.id_cobertura
      LEFT JOIN sinistro.laudos_preliminares_itens_segurados li ON li.id_laudo_preliminar = lp.id
      INNER JOIN sistema.vistorias_laudos_preliminares vlp ON vlp.id_laudo_preliminar = lp.id
      LEFT  JOIN produto.estadios_fenologicos ef ON ef.id = li.id_estadio_fenologico
      LEFT  JOIN regulacao.lp002_itens_segurados lis2 ON lis2.id_laudo_preliminar_item_segurado = li.id
      LEFT  JOIN regulacao.lp003_itens_segurados lis3 ON lis3.id_laudo_preliminar_item_segurado = li.id
      LEFT  JOIN regulacao.lp004_itens_segurados lis4 ON lis4.id_laudo_preliminar_item_segurado = li.id
WHERE (lp.fl_evento_nao_constatado = true or lp.fl_dt_evento_em_cobertura = false)
  AND lp.id_status NOT IN (206, 208)
);
SQL
        );

        $this->createViewsDependentes();
    }

    public function down(): void
    {
        $this->dropViews();
        $this->execute(<<<SQL
CREATE MATERIALIZED VIEW sumario.relatorio_vistorias_preliminares AS (
SELECT
	  pr.id_proposta_mae AS id_proposta
	 ,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 (
		SELECT
			pr.id AS id_proposta
		FROM
			sinistro.laudos_preliminares lp,
			sinistro.processos sp,
			seguro.propostas pr
		WHERE
			lp.id_processo = sp.id AND
			sp.id_proposta = pr.id
		group BY
		pr.id
	  ) AS pr_pre ON pr_pre.id_proposta = pr.id
	 INNER JOIN seguro.itens_segurados its ON its.id_proposta = pr.id
	 INNER JOIN seguro.propostas_proponentes pp ON pp.id_proposta = pr.id
	 INNER JOIN seguro.propostas_propriedades po ON po.id_proposta = pr.id
	 INNER JOIN sistema.municipios m ON m.id = po.id_municipio
	 INNER JOIN sistema.estados es ON es.id = m.id_estado
	 INNER JOIN produto.produtos pd ON pd.id = pr.id_produto
	 INNER JOIN produto.produtos_geral pg ON pg.id = pd.id_produto_geral
	 INNER JOIN produto.culturas cu ON cu.id = pg.id_cultura
	 INNER JOIN produto.variedades v ON v.id = its.id_variedade
	 INNER JOIN sinistro.processos sp ON sp.id_proposta = pr.id
	 INNER JOIN sinistro.processos_empresas pe ON pe.id_processo = sp.id AND pe.fl_ativo = true
	 INNER JOIN sistema.empresas e ON e.id = pe.id_empresa
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
	 )
ORDER BY
	pr.id, its.nr_item_segurado
);
       
CREATE MATERIALIZED VIEW sumario.relatorio_vistorias_preliminares_itens_segurados AS (
SELECT
	CASE WHEN i.ds_intensidade IS NOT NULL THEN SUBstring(i.ds_intensidade,1,1)||' '
	ELSE '0'
	END AS intensidade,
	lp.dt_vistoria AS dt_criacao,
	co.ds_nome_cobertura,
	vlp.id_vistoria,
	ef.ds_nome_estadio_fenologico,
	CASE WHEN lis2.vl_tamanho_planta is NOT NULL
				THEN lis2.vl_tamanho_planta
		WHEN lis3.vl_tamanho_planta is NOT NULL
				THEN lis3.vl_tamanho_planta
		WHEN lis4.vl_tamanho_planta is NOT NULL
				THEN lis4.vl_tamanho_planta
		ELSE NULL
	END AS tamanho_planta,
	string_agg(ds_nome_evento, ', ') AS eventos,
	li.id_item_segurado
FROM sinistro.laudos_preliminares lp
	INNER JOIN produto.coberturas co ON co.id = lp.id_cobertura
	INNER JOIN sinistro.laudos_preliminares_itens_segurados li ON li.id_laudo_prelimINar = lp.id
	INNER JOIN sistema.vistorias_laudos_preliminares vlp ON vlp.id_laudo_prelimINar = lp.id
	INNER JOIN sinistro.vistorias_avisos va ON va.id_vistoria = vlp.id_vistoria
	INNER JOIN sinistro.avisos_eventos ae ON ae.id_aviso = va.id_aviso
	INNER JOIN produto.eventos e ON e.id = ae.id_evento
	LEFT  JOIN sinistro.intensidades i ON i.id = li.id_intensidade
	LEFT  JOIN produto.estadios_fenologicos ef ON ef.id = li.id_estadio_fenologico
	LEFT  JOIN regulacao.lp002_itens_segurados lis2 ON lis2.id_laudo_prelimINar_item_segurado = li.id
	LEFT  JOIN regulacao.lp003_itens_segurados lis3 ON lis3.id_laudo_prelimINar_item_segurado = li.id
	LEFT  JOIN regulacao.lp004_itens_segurados lis4 ON lis4.id_laudo_prelimINar_item_segurado = li.id
	INNER JOIN sumario.relatorio_vistorias_preliminares rvp ON rvp.id_item_segurado = li.id_item_segurado
GROUP BY
	i.ds_intensidade,
	lp.dt_vistoria,
	co.ds_nome_cobertura,
	vlp.id_vistoria,
	ef.ds_nome_estadio_fenologico,
	lis2.vl_tamanho_planta,
	lis3.vl_tamanho_planta,
	lis4.vl_tamanho_planta,
	li.id_item_segurado
);
       
CREATE MATERIALIZED VIEW sumario.relatorio_vistorias_preliminares_nao_sinistrados AS (
SELECT lp.dt_vistoria AS dt_criacao,
       co.ds_nome_cobertura,
       vlp.id_vistoria,
       ef.ds_nome_estadio_fenologico,
       CASE WHEN lis2.vl_tamanho_planta is NOT NULL
                   THEN lis2.vl_tamanho_planta
           WHEN lis3.vl_tamanho_planta is NOT NULL
                   THEN lis3.vl_tamanho_planta
           WHEN lis4.vl_tamanho_planta is NOT NULL
                   THEN lis4.vl_tamanho_planta
           ELSE NULL
        END AS tamanho_planta,
       lp.id_processo,
       lp.dt_vistoria,
       lp.id
FROM sinistro.laudos_preliminares lp
    INNER JOIN produto.coberturas co ON co.id = lp.id_cobertura
    INNER JOIN sinistro.laudos_preliminares_itens_segurados li ON li.id_laudo_prelimINar = lp.id
    INNER JOIN sistema.vistorias_laudos_preliminares vlp ON vlp.id_laudo_prelimINar = lp.id
    LEFT  JOIN produto.estadios_fenologicos ef ON ef.id = li.id_estadio_fenologico
    LEFT  JOIN regulacao.lp002_itens_segurados lis2 ON lis2.id_laudo_prelimINar_item_segurado = li.id
    LEFT  JOIN regulacao.lp003_itens_segurados lis3 ON lis3.id_laudo_prelimINar_item_segurado = li.id
    LEFT  JOIN regulacao.lp004_itens_segurados lis4 ON lis4.id_laudo_prelimINar_item_segurado = li.id
    INNER JOIN sumario.relatorio_vistorias_preliminares rvp ON rvp.id_processo = lp.id_processo
WHERE (lp.fl_evento_nao_constatado = true or lp.fl_dt_evento_em_cobertura = false)
);
SQL
        );

        $this->createViewsDependentes();
    }

    private function dropViews()
    {
        $this->execute(<<<SQL
DROP MATERIALIZED VIEW IF EXISTS sumario.relatorio_sinistralidade_processada;
DROP MATERIALIZED VIEW IF EXISTS sumario.relatorio_vistorias_preliminares_nao_sinistrados;
DROP MATERIALIZED VIEW IF EXISTS sumario.relatorio_vistorias_preliminares_itens_segurados;
DROP MATERIALIZED VIEW IF EXISTS sumario.relatorio_vistorias_preliminares;
SQL
        );
    }

    private function createViewsDependentes()
    {
        $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 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
        );
    }
}