<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class Task2310311240 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
        DROP VIEW sinistro.v_laudos_cobranca_preliminar_final;
        CREATE 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,
                (
                    COALESCE(
                    municipio_perito.ds_nome_municipio :: character varying,
                    'NAO INFORMADO' :: character varying
                    ) :: text || '/' :: text || COALESCE(estado_perito.ds_sigla, '-' :: bpchar) :: text
                ) AS municipio_perito,
                funcionarios.ds_nome_funcionario 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,
                funcionarios.id AS 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
                JOIN seguro.propostas_proponentes po ON po.id_proposta = p.id
                JOIN seguro.propostas_propriedades pp ON pp.id_proposta = p.id
                JOIN sistema.municipios m ON m.id = pp.id_municipio
                JOIN sistema.estados e ON e.id = m.id_estado
                JOIN produto.produtos pd ON pd.id = p.id_produto
                JOIN produto.produtos_geral pg ON pg.id = pd.id_produto_geral
                JOIN produto.culturas cu ON cu.id = pg.id_cultura
                JOiN sinistro.processos pr ON pr.id_proposta = p.id
                JOIN sinistro.processos_empresas pe ON pe.id_processo = pr.id
                JOIN sistema.empresas ep ON ep.id = pe.id_empresa
                JOIN sistema.municipios em ON em.id = ep.id_municipio
                JOIN sistema.estados ee ON ee.id = em.id_estado
                JOIN sinistro.laudos_finais lf ON lf.id_processo = pr.id
                JOIN sistema.vistorias_laudos_finais vlf ON vlf.id_laudo_final = lf.id
                LEFT JOIN sinistro.v_get_auxiliares_final vgaf ON vgaf.id_laudo_final = vlf.id_laudo_final
                JOIN sistema.usuarios u ON u.id = lf.id_usuario_criacao
                JOIN sistema.vistorias v ON v.id = vlf.id_vistoria
                JOIN sistema.vistorias_empresas ve ON ve.id_empresa = ep.id
                AND ve.id_vistoria = vlf.id_vistoria
                LEFT JOIN seguro.v_id_proposta_mae ON v_id_proposta_mae.id_proposta = p.id_proposta_mae
                LEFT JOIN sistema.vistorias_funcionarios ON vistorias_funcionarios.id_vistoria = vlf.id_vistoria
                LEFT JOIN sistema.funcionarios ON funcionarios.id = vistorias_funcionarios.id_funcionario
                LEFT JOIN sistema.municipios AS municipio_perito ON municipio_perito.id = funcionarios.id_municipio
                LEFT JOIN sistema.estados AS estado_perito ON estado_perito.id = municipio_perito.id_estado
                WHERE
                (
                    lf.id_status = 110
                    OR lf.id_status = 205
                )
                AND pr.id_status <> 5
                AND pd.id_safra >= (
                    select
                    id - 1
                    from
                    produto.safras
                    where
                    fl_vigente = true
                )
                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,
                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,
                municipio_perito.ds_nome_municipio,
                estado_perito.ds_sigla,
                funcionarios.ds_nome_funcionario,
                vgaf.nome_auxiliar,
                lf.fl_evento_nao_constatado,
                pd.id,
                pd.id_seguradora,
                pd.id_produto_geral,
                ep.cpf_cnpj,
                p.nr_apolice,
                em.ds_nome_municipio,
                ee.ds_sigla,
                m.id,
                funcionarios.id,
                p.dt_criacao
                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,
                (
                    COALESCE(
                    municipio_perito.ds_nome_municipio :: character varying,
                    'NAO INFORMADO' :: character varying
                    ) :: text || '/' :: text || COALESCE(estado_perito.ds_sigla, '-' :: bpchar) :: text
                ) AS municipio_perito,
                funcionarios.ds_nome_funcionario 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,
                funcionarios.id AS 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
                JOIN seguro.propostas_proponentes po ON po.id_proposta = p.id
                JOIN seguro.propostas_propriedades pp ON pp.id_proposta = p.id
                JOIN sistema.municipios m ON m.id = pp.id_municipio
                JOIN sistema.estados e ON e.id = m.id_estado
                JOIN produto.produtos pd ON pd.id = p.id_produto
                JOIN produto.produtos_geral pg ON pg.id = pd.id_produto_geral
                JOIN produto.culturas cu ON cu.id = pg.id_cultura
                JOIN sinistro.processos pr ON pr.id_proposta = p.id
                JOIN sinistro.processos_empresas pe ON pe.id_processo = pr.id
                JOIN sistema.empresas ep ON ep.id = pe.id_empresa
                JOIN sistema.municipios em ON em.id = ep.id_municipio
                JOIN sistema.estados ee ON ee.id = em.id_estado
                JOIN sinistro.laudos_preliminares lp ON lp.id_processo = pr.id
                JOIN sistema.vistorias_laudos_preliminares vlp ON vlp.id_laudo_preliminar = lp.id
                LEFT JOIN sinistro.v_get_auxiliares_preliminar vgap ON vgap.id_laudo_preliminar = vlp.id_laudo_preliminar
                JOIN sistema.usuarios u ON u.id = lp.id_usuario_criacao
                AND u.tp_usuario <> 'H' :: bpchar
                JOIN sistema.vistorias v ON v.id = vlp.id_vistoria
                JOIN sistema.vistorias_empresas ve ON ve.id_empresa = ep.id
                AND ve.id_vistoria = vlp.id_vistoria
                LEFT JOIN seguro.v_id_proposta_mae ON v_id_proposta_mae.id_proposta = p.id_proposta_mae
                LEFT JOIN sistema.vistorias_funcionarios ON vistorias_funcionarios.id_vistoria = vlp.id_vistoria
                LEFT JOIN sistema.funcionarios ON funcionarios.id = vistorias_funcionarios.id_funcionario
                LEFT JOIN sistema.municipios AS municipio_perito ON municipio_perito.id = funcionarios.id_municipio
                LEFT JOIN sistema.estados AS estado_perito ON estado_perito.id = municipio_perito.id_estado
                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 pd.id_safra >= (
                    select
                    id - 1
                    from
                    produto.safras
                    where
                    fl_vigente = true
                )
                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,
                vlp.id_vistoria,
                cu.ds_nome_cultura,
                lp.ds_itens_segurados,
                lp.ds_variedades,
                lp.vl_area_vistoriada,
                pg.id_tipo_produto,
                cu.id,
                p.id,
                e.id,
                pr.id_processo_seguradora,
                lp.dt_vistoria,
                municipio_perito.ds_nome_municipio,
                estado_perito.ds_sigla,
                funcionarios.ds_nome_funcionario,
                vgap.nome_auxiliar,
                lp.fl_evento_nao_constatado,
                pd.id,
                pd.id_seguradora,
                pd.id_produto_geral,
                ep.cpf_cnpj,
                p.nr_apolice,
                em.ds_nome_municipio,
                ee.ds_sigla,
                m.id,
                funcionarios.id,
                p.dt_criacao;
SQL
);
    }

    public function down(): void
    {
        $this->execute(<<<SQL
            DROP VIEW sinistro.v_laudos_cobranca_preliminar_final;
            CREATE 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 safras.id - 1
                        FROM produto.safras
                        WHERE safras.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 safras.id - 1
                        FROM produto.safras
                        WHERE safras.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
);
    }
}