<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class Task2303161911 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
            CREATE OR REPLACE VIEW sinistro.v_laudos_cobranca_previa AS
            SELECT p.id AS id_proposta,
                v_id_proposta_mae.id_proposta_mae,
                pvp.id_vistoria,
                prc.id AS id_processo,
                prc.id_processo_seguradora,
                pvp.id AS id_proposta_vistoria,
                prop.ds_nome_proponente,
                pd.ds_nome_abreviado,
                m.ds_nome_municipio,
                e.ds_sigla,
                e.id AS id_estado,
                emp.ds_nome_fantasia,
                emp.id AS id_empresa,
                'Previa'::text AS tp_vistoria,
                pg.id_tipo_produto,
                cu.id AS id_cultura,
                cu.ds_nome_cultura,
                c.id AS id_cobertura,
                fu.ds_nome_funcionario AS perito,
                to_char(pvp.dt_laudo, 'YYYY-MM-DD HH24:MI:SS'::text) AS dt_vistoria,
                to_char(pvp.dt_laudo, 'DD/MM/YYYY'::text) AS dt_laudo,
                pvp.dt_laudo AS dt_laudo_order,
                sf.ds_chave AS ds_safra,
                c.ds_chave_cobertura,
                pd.id_seguradora,
                pd.id_safra,
                sf.ds_nome_safra,
                emp.cpf_cnpj AS cpf_cnpj_empresa,
                p.nr_apolice,
                em.ds_nome_municipio AS ds_nome_municipio_empresa,
                ee.ds_sigla AS ds_sigla_uf_empresa,
                (COALESCE(fm.ds_nome_municipio::character varying, 'NAO INFORMADO'::character varying)::text || '/'::text) || COALESCE(fe.ds_sigla, '-'::bpchar)::text AS municipio_perito,
                m.id AS id_municipio,
                to_char(p.dt_criacao::timestamp with time zone, 'YYYY-MM-DD'::text) AS data_criacao_proposta
            FROM seguro.propostas_endosso ped
                JOIN seguro.propostas p ON ped.id_proposta = p.id
                JOIN seguro.propostas_coberturas pc ON pc.id_proposta = p.id AND pc.fl_principal = true
                JOIN produto.coberturas c ON c.id = pc.id_cobertura
                JOIN produto.produtos pd ON pd.id = p.id_produto AND pd.id_safra >= (select id - 1 from produto.safras where fl_vigente = true)
                JOIN produto.safras sf ON sf.id = pd.id_safra
                JOIN produto.produtos_geral pg ON pg.id = pd.id_produto_geral
                JOIN produto.culturas cu ON cu.id = pg.id_cultura
                LEFT JOIN sinistro.processos prc ON prc.id_proposta = p.id
                JOIN seguro.propostas_propriedades pp ON p.id = pp.id_proposta
                JOIN seguro.propostas_proponentes prop ON p.id = prop.id_proposta
                JOIN sistema.municipios m ON pp.id_municipio = m.id
                JOIN sistema.estados e ON m.id_estado = e.id
                JOIN seguro.propostas_vistorias_previas pvp ON p.id = pvp.id_proposta AND pvp.fl_transmitido = true AND pvp.fl_nova_vistoria_custo_perito IS NOT TRUE
                LEFT JOIN sistema.vistorias_agendamentos va ON va.id_vistoria = pvp.id_vistoria
                LEFT JOIN sistema.agendamentos_funcionarios af ON af.id_agendamento = va.id_agendamento
                LEFT JOIN sistema.funcionarios fu ON fu.id = af.id_funcionario
                JOIN sistema.vistorias_empresas vemp ON pvp.id_vistoria = vemp.id_vistoria AND vemp.fl_ativo = true
                JOIN sistema.empresas emp ON vemp.id_empresa = emp.id
                LEFT JOIN seguro.v_id_proposta_mae ON v_id_proposta_mae.id_proposta = p.id_proposta_mae
                LEFT JOIN sistema.municipios em ON em.id = emp.id_municipio
                LEFT JOIN sistema.estados ee ON ee.id = em.id_estado
                LEFT JOIN sistema.municipios fm ON fm.id = fu.id_municipio
                LEFT JOIN sistema.estados fe ON fe.id = fm.id_estado
            ORDER BY (to_char(pvp.dt_laudo, 'DD/MM/YYYY'::text));


            CREATE OR REPLACE VIEW sinistro.v_laudos_cobranca_preliminar_final AS
            SELECT lf.id_cobertura,
                pr.id AS id_processo,
                v_id_proposta_mae.id_proposta_mae,
                po.ds_nome_proponente,
                m.ds_nome_municipio,
                e.ds_sigla,
                ep.ds_nome_fantasia,
                ep.id AS id_empresa,
                vlf.id_vistoria,
                cu.ds_nome_cultura,
                lf.ds_itens_segurados AS itens,
                lf.ds_variedades AS variedade,
                lf.vl_area_vistoriada,
                'Final'::text AS tp_vistoria,
                pg.id_tipo_produto,
                cu.id AS id_cultura,
                p.id AS id_proposta,
                e.id AS id_estado,
                pr.id_processo_seguradora,
                lf.dt_vistoria,
                vgvf.municipio_perito,
                vgvf.nome_perito AS perito,
                string_agg(vgaf.nome_auxiliar::text, ', '::text) AS auxiliar,
                lf.fl_evento_nao_constatado AS evento_nao_constatado,
                pd.id AS id_produto,
                pd.id_seguradora,
                pd.id_produto_geral,
                ep.cpf_cnpj AS cpf_cnpj_empresa,
                p.nr_apolice,
                em.ds_nome_municipio AS ds_nome_municipio_empresa,
                ee.ds_sigla AS ds_sigla_uf_empresa,
                m.id AS id_municipio,
                vgvf.id_perito,
                to_char(p.dt_criacao::timestamp with time zone, 'YYYY-MM-DD'::text) AS data_criacao_proposta
            FROM seguro.propostas_endosso ped
                JOIN seguro.propostas p ON ped.id_proposta = p.id AND ped.fl_proposta_vigente,
                seguro.propostas_proponentes po,
                seguro.propostas_propriedades pp,
                sistema.municipios m,
                sistema.estados e,
                produto.produtos pd,
                produto.produtos_geral pg,
                produto.culturas cu,
                sinistro.processos pr,
                sinistro.processos_empresas pe,
                sistema.empresas ep
                LEFT JOIN sistema.municipios em ON em.id = ep.id_municipio
                LEFT JOIN sistema.estados ee ON ee.id = em.id_estado,
                sistema.vistorias_laudos_finais vlf
                LEFT JOIN sinistro.v_get_auxiliares_final vgaf ON vgaf.id_laudo_final = vlf.id_laudo_final,
                sinistro.laudos_finais lf,
                sistema.usuarios u,
                sistema.vistorias_empresas ve,
                seguro.v_id_proposta_mae,
                sinistro.v_get_vistoriadores_final vgvf
            WHERE (lf.id_status = 110 OR lf.id_status = 205) AND pr.id_status <> 5 AND pr.id_proposta = p.id AND po.id_proposta = p.id AND pp.id_proposta = p.id AND pp.id_municipio = m.id AND pd.id = p.id_produto AND pg.id = pd.id_produto_geral AND cu.id = pg.id_cultura AND e.id = m.id_estado AND pe.id_processo = pr.id AND ep.id = pe.id_empresa AND vlf.id_laudo_final = lf.id AND lf.id_processo = pr.id AND u.id = lf.id_usuario_criacao 
            AND pd.id_safra >= (select id - 1 from produto.safras where fl_vigente = true) 
            AND ve.id_empresa = ep.id AND ve.id_vistoria = vlf.id_vistoria AND v_id_proposta_mae.id_proposta = p.id_proposta_mae AND vgvf.id_laudo_final = vlf.id_laudo_final
            GROUP BY lf.id_cobertura, pr.id, v_id_proposta_mae.id_proposta_mae, po.ds_nome_proponente, m.ds_nome_municipio, e.ds_sigla, ep.ds_nome_fantasia, ep.id, ep.cpf_cnpj, vlf.id_vistoria, cu.ds_nome_cultura, lf.ds_itens_segurados, lf.ds_variedades, lf.vl_area_vistoriada, 'Final'::text, pg.id_tipo_produto, cu.id, p.id, e.id, pr.id_processo_seguradora, lf.dt_vistoria, lf.fl_evento_nao_constatado, pd.id, pd.id_seguradora, em.ds_nome_municipio, ee.ds_sigla, vgvf.nome_perito, vgvf.municipio_perito, m.id, vgvf.id_perito
            UNION
            SELECT lp.id_cobertura,
                pr.id AS id_processo,
                v_id_proposta_mae.id_proposta_mae,
                po.ds_nome_proponente,
                m.ds_nome_municipio,
                e.ds_sigla,
                ep.ds_nome_fantasia,
                ep.id AS id_empresa,
                vlp.id_vistoria,
                cu.ds_nome_cultura,
                lp.ds_itens_segurados AS itens,
                lp.ds_variedades AS variedade,
                lp.vl_area_vistoriada,
                'Preliminar'::text AS tp_vistoria,
                pg.id_tipo_produto,
                cu.id AS id_cultura,
                p.id AS id_proposta,
                e.id AS id_estado,
                pr.id_processo_seguradora,
                lp.dt_vistoria,
                vgvp.municipio_perito,
                vgvp.nome_perito AS perito,
                string_agg(vgap.nome_auxiliar::text, ', '::text) AS auxiliar,
                lp.fl_evento_nao_constatado AS evento_nao_constatado,
                pd.id AS id_produto,
                pd.id_seguradora,
                pd.id_produto_geral,
                ep.cpf_cnpj AS cpf_cnpj_empresa,
                p.nr_apolice,
                em.ds_nome_municipio AS ds_nome_municipio_empresa,
                ee.ds_sigla AS ds_sigla_uf_empresa,
                m.id AS id_municipio,
                vgvp.id_perito,
                to_char(p.dt_criacao::timestamp with time zone, 'YYYY-MM-DD'::text) AS data_criacao_proposta
            FROM seguro.propostas_endosso ped
                JOIN seguro.propostas p ON ped.id_proposta = p.id AND ped.fl_proposta_vigente,
                seguro.propostas_proponentes po,
                seguro.propostas_propriedades pp,
                sistema.municipios m,
                sistema.estados e,
                produto.produtos pd,
                produto.produtos_geral pg,
                produto.culturas cu,
                sinistro.processos pr,
                sinistro.processos_empresas pe,
                sistema.empresas ep
                LEFT JOIN sistema.municipios em ON em.id = ep.id_municipio
                LEFT JOIN sistema.estados ee ON ee.id = em.id_estado,
                sistema.vistorias_laudos_preliminares vlp
                LEFT JOIN sinistro.v_get_auxiliares_preliminar vgap ON vgap.id_laudo_preliminar = vlp.id_laudo_preliminar,
                sinistro.laudos_preliminares lp,
                sistema.usuarios u,
                sistema.vistorias v,
                sistema.vistorias_empresas ve,
                seguro.v_id_proposta_mae,
                sinistro.v_get_vistoriadores_preliminar vgvp
            WHERE (lp.id_status = 76 OR lp.id_status = 204) AND pe.fl_ativo = true AND pr.id_status <> 5 AND pe.fl_ativo = true AND pr.id_proposta = p.id AND po.id_proposta = p.id AND pp.id_proposta = p.id AND pp.id_municipio = m.id AND pd.id = p.id_produto AND pg.id = pd.id_produto_geral AND cu.id = pg.id_cultura AND e.id = m.id_estado AND pe.id_processo = pr.id AND ep.id = pe.id_empresa AND vlp.id_laudo_preliminar = lp.id AND lp.id_processo = pr.id AND u.id = lp.id_usuario_criacao 
            AND pd.id_safra >= (select id - 1 from produto.safras where fl_vigente = true) 
            AND v.id = vlp.id_vistoria AND ve.id_empresa = ep.id AND ve.id_vistoria = vlp.id_vistoria AND v_id_proposta_mae.id_proposta = p.id_proposta_mae AND vgvp.id_laudo_preliminar = vlp.id_laudo_preliminar
            GROUP BY lp.id_cobertura, pr.id, v_id_proposta_mae.id_proposta_mae, po.ds_nome_proponente, m.ds_nome_municipio, e.ds_sigla, ep.ds_nome_fantasia, ep.id, ep.cpf_cnpj, vlp.id_vistoria, cu.ds_nome_cultura, lp.ds_itens_segurados, lp.ds_variedades, lp.vl_area_vistoriada, 'Preliminar'::text, pg.id_tipo_produto, cu.id, p.id, e.id, pr.id_processo_seguradora, lp.dt_vistoria, lp.fl_evento_nao_constatado, pd.id, pd.id_seguradora, em.ds_nome_municipio, ee.ds_sigla, vgvp.nome_perito, vgvp.municipio_perito, m.id, vgvp.id_perito
            ORDER BY 16;


SQL
        );
    }

    public function down(): void
    {
        //nao existe rollback para uma alteracao de uma view que ja existe
    }
}