<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class Task2401151857 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
        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,
        (
            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 sinistro.laudos_finais_funcionarios ON laudos_finais_funcionarios.id_laudo_final = lf.id
        LEFT JOIN sistema.funcionarios ON funcionarios.id = laudos_finais_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
        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,
        (
            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
);
    }
}