<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskInc55749 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute(<<<SQL
            DROP VIEW sinistro.v_laudos_cobranca_previa;
            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,
                    vistoria_agendamento.perito,
                    vistoria_agendamento.id_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 safras.id - 2
                        FROM produto.safras
                        WHERE safras.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 (
                        SELECT
                            sva.id_vistoria,
                            array_agg(fu.id::character varying) AS id_perito,
                            string_agg(ds_nome_funcionario::text, ', '::text) AS perito
                        FROM
                            sistema.vistorias_agendamentos sva
                        LEFT JOIN sistema.agendamentos_funcionarios af ON af.id_agendamento = sva.id_agendamento
                        LEFT JOIN sistema.funcionarios fu ON fu.id = af.id_funcionario
                        GROUP BY id_vistoria
                    ) vistoria_agendamento  ON vistoria_agendamento.id_vistoria = pvp.id_vistoria
                    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));
SQL
        );
    }

    public function down(): void
    {
        $this->execute(<<<SQL
            DROP VIEW sinistro.v_laudos_cobranca_previa;
            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,
                    ARRAY[fu.id::character varying] AS id_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 safras.id - 2
                        FROM produto.safras
                        WHERE safras.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));
SQL
        );
    }
}