<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class Task2009011222 extends AbstractMigration
{
    public function up(): void
    {
        $this->execute("ALTER TABLE produto.produtos_geral ADD column id_epoca_cultivo CHARACTER(1)");

        $this->execute("DROP VIEW sinistro.v_avisos");

        $this->execute("CREATE OR REPLACE FUNCTION sinistro.busca_empresa_cnpj(
                            vid_safra integer,
                            vcpf_cnpj text,
                            vepoca_cultivo text)
                          RETURNS varchar AS
                        \$BODY\$
                        DECLARE
                             cpf_cnpj_empresa text;
                        BEGIN  
                            select
                                emp.ds_nome_fantasia
                            from    
                                seguro.propostas pr 
                                inner join seguro.propostas_proponentes pp on pp.id_proposta = pr.id
                                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 sinistro.processos sp on sp.id_proposta = pr.id
                                inner join sinistro.processos_empresas ppe ON ppe.id_processo = sp.id AND ppe.fl_ativo = true
                                inner join sistema.empresas emp ON emp.id = ppe.id_empresa
                            into
                                cpf_cnpj_empresa    
                            where
                                pg.id_epoca_cultivo = vepoca_cultivo
                                and pd.id_safra = vid_safra 
                                and pp.cpf_cnpj = vcpf_cnpj
                            limit 1;
                                
                            if cpf_cnpj_empresa is not null then
                                return cpf_cnpj_empresa;
                            else
                                return '';
                            end if;
                        
                        END;
                        \$BODY\$
                          LANGUAGE plpgsql VOLATILE
                          COST 100;
                        ALTER FUNCTION sinistro.busca_empresa_cnpj(INTEGER, TEXT, TEXT)
                          OWNER TO postgres;
        ");

        $this->execute("CREATE OR REPLACE VIEW sinistro.v_avisos WITH (security_barrier=false) AS 
                         SELECT DISTINCT ul.ds_nome_usuario AS ds_nome_usuario_responsavel,
                            cl.ds_nome_cliente,
                            a.fl_ativo,
                            a.fl_del,
                            a.id,
                            a.dt_criacao,
                            a.dt_sinistro,
                            pp.id AS id_proposta,
                            (to_char(seguro.dt_vigencia_inicio_original(pp.id)::timestamp with time zone, 'DD/MM/YYYY'::text) || ' - '::text) || to_char(pp.dt_vigencia_fim::timestamp with time zone, 'DD/MM/YYYY'::text) AS dt_vigencia,
                            pp.nr_apolice,
                            pc.id AS id_processo,
                            pc.id_processo_seguradora,
                            pppr.ds_nome_proponente,
                            pppr.cpf_cnpj,
                            pp.id_produto,
                            pd.ds_nome_produto,
                            u.ds_nome_usuario,
                            c.id AS id_corretor,
                            c.id_usuario AS id_usuario_corretor,
                            c.ds_nome_fantasia AS ds_nome_corretor,
                            s.id AS id_safra,
                            a.id_status,
                            mun.ds_nome_municipio,
                            est.ds_sigla,
                            est.id AS id_estado,
                            mun.id AS id_municipio,
                            ppr.ds_endereco,
                            st.ds_status,
                            pend.id_endosso,
                            pc.fl_pasta_aberta,
                            spc.id_usuario AS id_usuario_proposta_corretor,
                            sis.vl_lmga,
                            pe.ds_nome_evento AS ds_eventos,
                            empvist.ds_nome_fantasia AS ds_empresa_previa,

                            CASE WHEN a.id_status = 27 THEN 
                                CASE WHEN pdg.id_tipo_produto = 1 THEN
                                CASE
                                    WHEN empproc.ds_nome_fantasia IS NOT NULL THEN empproc.ds_nome_fantasia::character varying
                                    WHEN empcpf.ds_nome_fantasia IS NOT NULL THEN empcpf.ds_nome_fantasia::character varying
                                    ELSE ''::character varying
                                END
                                ELSE
                                CASE
                                    WHEN pdg.id_epoca_cultivo IS NULL or pdg.id_epoca_cultivo = ''  THEN
                                    CASE
                                    WHEN empproc.ds_nome_fantasia IS NOT NULL THEN empproc.ds_nome_fantasia::character varying
                                    WHEN empcpf.ds_nome_fantasia IS NOT NULL THEN empcpf.ds_nome_fantasia::character varying
                                    ELSE ''::character varying
                                    END
                                    ELSE
                                    CASE
                                    WHEN empproc.ds_nome_fantasia IS NOT NULL THEN empproc.ds_nome_fantasia::character varying
                                    ELSE sinistro.busca_empresa_cnpj(pd.id_safra,pppr.cpf_cnpj,pdg.id_epoca_cultivo) 
                                    END
                                END
                                END
                            END AS ds_empresa_cpf_segurado,

                            a.fl_novo,
                            empcpf.id AS id_empresa_vistoriadora,
                            emp.ds_nome_fantasia AS ds_nome_seguradora,
                                CASE
                                    WHEN v_nr_endosso.nr_endosso > 0 THEN v_nr_endosso.nr_endosso
                                    ELSE 0::bigint
                                END AS nr_endosso,
                            pp.id_proposta_mae,
                            pp.id_proposta_endossada,
                            v_id_proposta_mae_completo.chave_mae AS id_proposta_mae_completo,
                            ppe.id_empresa AS id_empresa_processo_sinistro,
                            pp.id_usuario_criacao,
                                CASE
                                    WHEN btrim(v_tem_parcela_vencida.id_endosso::character varying::text) <> ''::text OR v_tem_parcela_vencida.id_endosso IS NOT NULL THEN true
                                    ELSE false
                                END AS vencida,
                            empvist.ds_status AS ds_status_vistoria_previa
                           FROM sinistro.avisos a
                             JOIN seguro.propostas pp ON pp.id = a.id_proposta
                             JOIN seguro.propostas_endosso spe ON spe.id_proposta = pp.id AND spe.fl_proposta_vigente = true
                             JOIN sumario.itens_segurados sis ON sis.id_proposta = pp.id
                             JOIN seguro.propostas_endosso pend ON pend.id_proposta = pp.id
                             JOIN seguro.propostas_propriedades ppr ON ppr.id_proposta = pp.id
                             JOIN seguro.propostas_corretores spc ON spc.id_proposta = pp.id
                             JOIN sistema.corretores c ON c.id_usuario = spc.id_usuario
                             JOIN sistema.municipios mun ON mun.id = ppr.id_municipio
                             JOIN sistema.estados est ON est.id = mun.id_estado
                             JOIN seguro.propostas_proponentes pppr ON pppr.id_proposta = pp.id
                             JOIN seguro.proponentes ppo ON ppo.cpf_cnpj::text = pppr.cpf_cnpj::text
                             JOIN produto.produtos pd ON pd.id = pp.id_produto
                             JOIN produto.produtos_geral pdg ON pdg.id = pd.id_produto_geral
                             JOIN sistema.empresas emp ON emp.id = pd.id_seguradora
                             JOIN sistema.usuarios u ON u.id = a.id_usuario_criacao
                             JOIN produto.safras s ON s.id = pd.id_safra
                             JOIN sistema.status st ON st.id = a.id_status
                             JOIN sinistro.avisos_eventos sae ON sae.id_aviso = a.id
                             JOIN produto.eventos pe ON pe.id = sae.id_evento
                             LEFT JOIN sinistro.processos pc ON pc.id_proposta = pp.id
                             LEFT JOIN sinistro.processos_empresas ppe ON ppe.id_processo = pc.id AND ppe.fl_ativo = true
                             LEFT JOIN sistema.usuarios ul ON ul.id = pc.id_usuario_responsavel_liquidacao
                             LEFT JOIN seguro.clientes_propostas clp ON clp.id_proposta = pp.id
                             LEFT JOIN seguro.clientes cl ON clp.id_cliente = cl.id
                             LEFT JOIN sistema.empresas empcpf ON empcpf.id = ppo.id_empresa_vistoria
                             LEFT JOIN ( SELECT empr.ds_nome_fantasia,
                                    pvp.id_proposta,
                                    pe_1.id_endosso,
                                    ss.ds_status
                                   FROM seguro.propostas_endosso pe_1
                                     JOIN seguro.propostas_vistorias_previas pvp ON pe_1.id_proposta = pvp.id_proposta
                                     JOIN ( SELECT v.id_proposta,
                                            max(v.id) AS id
                                           FROM seguro.propostas_vistorias_previas v
                                          GROUP BY v.id_proposta) pvpp ON pvp.id = pvpp.id
                                     JOIN sistema.vistorias_empresas ve ON pvp.id_vistoria = ve.id_vistoria
                                     JOIN sistema.empresas empr ON ve.id_empresa = empr.id
                                     JOIN sistema.status ss ON ss.id = pvp.id_status_analise
                                  GROUP BY empr.ds_nome_fantasia, pvp.id_proposta, pe_1.id_endosso, ss.ds_status) empvist ON empvist.id_endosso = spe.id_endosso
                             LEFT JOIN sistema.empresas empproc ON empproc.id = ppe.id_empresa
                             LEFT JOIN seguro.v_nr_endosso ON v_nr_endosso.id_endosso = pp.id_endosso
                             LEFT JOIN seguro.v_id_proposta_mae_completo ON v_id_proposta_mae_completo.id_proposta = pp.id_proposta_mae
                             LEFT JOIN ( SELECT propostas.id_endosso,
                                    propostas.id,
                                    propostas_endosso.fl_proposta_vigente
                                   FROM seguro.propostas
                                     JOIN ( SELECT propostas_parcelas.id_proposta,
                                            propostas_parcelas.dt_vencimento,
                                            propostas_parcelas.vl_segurado
                                           FROM seguro.propostas propostas_1
                                             JOIN seguro.propostas_endosso propostas_endosso_1 ON propostas_endosso_1.id_endosso = propostas_1.id_endosso AND propostas_endosso_1.fl_proposta_vigente = true
                                             JOIN seguro.propostas_parcelas ON propostas_parcelas.id_proposta = propostas_1.id
                                             LEFT JOIN seguro.proposta_boletos ON proposta_boletos.id_proposta = propostas_endosso_1.id_proposta AND proposta_boletos.nr_parcela = propostas_parcelas.nr_parcela
                                          WHERE proposta_boletos.fl_pago = false) propostas_boletos ON propostas_boletos.id_proposta = propostas.id
                                     JOIN seguro.propostas_endosso ON propostas_endosso.id_endosso = propostas.id_endosso AND propostas_endosso.fl_proposta_vigente = true
                                  WHERE propostas_boletos.vl_segurado > 0::numeric AND propostas_boletos.dt_vencimento < to_char(now(), 'YYYY-mm-dd'::text)::date
                                  GROUP BY propostas.id_endosso, propostas.id, propostas_endosso.fl_proposta_vigente) v_tem_parcela_vencida ON v_tem_parcela_vencida.id_endosso = pp.id_endosso
                          WHERE 1 = 1
                          ORDER BY a.id;

                        ALTER TABLE sinistro.v_avisos
                          OWNER TO agrobrasilpg;
        ");

        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id = 50");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id = 67");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id = 70");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id =  6");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id = 52");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id =  7");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='I' where id =  9");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='I' where id = 10");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='I' where id = 12");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='I' where id = 15");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='I' where id = 16");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id = 18");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id = 69");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id = 61");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='I' where id = 24");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id = 30");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id =  3");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id = 31");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='I' where id = 49");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='I' where id = 48");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id = 71");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id = 72");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id = 37");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='V' where id = 38");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='I' where id = 63");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='I' where id = 64");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='I' where id = 42");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='I' where id = 43");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='I' where id = 44");
        $this->execute("update produto.produtos_geral set dt_alteracao=now(), id_usuario_alteracao=1009, id_epoca_cultivo='I' where id = 51");
    }

    public function down(): void
    {
        $this->execute("DROP VIEW sinistro.v_avisos");

        $this->execute("DROP FUNCTION sinistro.busca_empresa_cnpj(INTEGER, TEXT, TEXT)");

        $this->execute("CREATE OR REPLACE VIEW sinistro.v_avisos AS
                            SELECT DISTINCT ul.ds_nome_usuario AS ds_nome_usuario_responsavel, 
                               cl.ds_nome_cliente, a.fl_ativo, a.fl_del, a.id, a.dt_criacao, a.dt_sinistro, 
                               pp.id AS id_proposta, 
                               (to_char(seguro.dt_vigencia_inicio_original(pp.id)::timestamp with time zone, 'DD/MM/YYYY'::text) || ' - '::text) || to_char(pp.dt_vigencia_fim::timestamp with time zone, 'DD/MM/YYYY'::text) AS dt_vigencia, 
                               pp.nr_apolice, pc.id AS id_processo, pc.id_processo_seguradora, 
                               pppr.ds_nome_proponente, pppr.cpf_cnpj, pp.id_produto, pd.ds_nome_produto, 
                               u.ds_nome_usuario, c.id AS id_corretor, c.id_usuario AS id_usuario_corretor, 
                               c.ds_nome_fantasia AS ds_nome_corretor, s.id AS id_safra, a.id_status, 
                               mun.ds_nome_municipio, est.ds_sigla, est.id AS id_estado, 
                               mun.id AS id_municipio, ppr.ds_endereco, st.ds_status, pend.id_endosso, 
                               pc.fl_pasta_aberta, spc.id_usuario AS id_usuario_proposta_corretor, 
                               sis.vl_lmga, pe.ds_nome_evento AS ds_eventos, 
                               empvist.ds_nome_fantasia AS ds_empresa_previa, 
                                   CASE
                                       WHEN empproc.ds_nome_fantasia IS NOT NULL THEN empproc.ds_nome_fantasia::character varying
                                       WHEN empcpf.ds_nome_fantasia IS NOT NULL THEN empcpf.ds_nome_fantasia::character varying
                                       ELSE ''::character varying
                                   END AS ds_empresa_cpf_segurado, 
                               a.fl_novo, empcpf.id AS id_empresa_vistoriadora, 
                               emp.ds_nome_fantasia AS ds_nome_seguradora, 
                                   CASE
                                       WHEN v_nr_endosso.nr_endosso > 0 THEN v_nr_endosso.nr_endosso
                                       ELSE 0::bigint
                                   END AS nr_endosso, 
                               pp.id_proposta_mae, pp.id_proposta_endossada, 
                               v_id_proposta_mae_completo.chave_mae AS id_proposta_mae_completo, 
                               ppe.id_empresa AS id_empresa_processo_sinistro, pp.id_usuario_criacao, 
                                   CASE
                                       WHEN btrim(v_tem_parcela_vencida.id_endosso::character varying::text) <> ''::text OR v_tem_parcela_vencida.id_endosso IS NOT NULL THEN true
                                       ELSE false
                                   END AS vencida, 
                               empvist.ds_status AS ds_status_vistoria_previa
                              FROM sinistro.avisos a
                              JOIN seguro.propostas pp ON pp.id = a.id_proposta
                              JOIN seguro.propostas_endosso spe ON spe.id_proposta = pp.id AND spe.fl_proposta_vigente = true
                              JOIN sumario.itens_segurados sis ON sis.id_proposta = pp.id
                              JOIN seguro.propostas_endosso pend ON pend.id_proposta = pp.id
                              JOIN seguro.propostas_propriedades ppr ON ppr.id_proposta = pp.id
                              JOIN seguro.propostas_corretores spc ON spc.id_proposta = pp.id
                              JOIN sistema.corretores c ON c.id_usuario = spc.id_usuario
                              JOIN sistema.municipios mun ON mun.id = ppr.id_municipio
                              JOIN sistema.estados est ON est.id = mun.id_estado
                              JOIN seguro.propostas_proponentes pppr ON pppr.id_proposta = pp.id
                              JOIN seguro.proponentes ppo ON ppo.cpf_cnpj::text = pppr.cpf_cnpj::text
                              JOIN produto.produtos pd ON pd.id = pp.id_produto
                              JOIN sistema.empresas emp ON emp.id = pd.id_seguradora
                              JOIN sistema.usuarios u ON u.id = a.id_usuario_criacao
                              JOIN produto.safras s ON s.id = pd.id_safra
                              JOIN sistema.status st ON st.id = a.id_status
                              JOIN sinistro.avisos_eventos sae ON sae.id_aviso = a.id
                              JOIN produto.eventos pe ON pe.id = sae.id_evento
                              LEFT JOIN sinistro.processos pc ON pc.id_proposta = pp.id
                              LEFT JOIN sinistro.processos_empresas ppe ON ppe.id_processo = pc.id AND ppe.fl_ativo = true
                              LEFT JOIN sistema.usuarios ul ON ul.id = pc.id_usuario_responsavel_liquidacao
                              LEFT JOIN seguro.clientes_propostas clp ON clp.id_proposta = pp.id
                              LEFT JOIN seguro.clientes cl ON clp.id_cliente = cl.id
                              LEFT JOIN sistema.empresas empcpf ON empcpf.id = ppo.id_empresa_vistoria
                              LEFT JOIN ( SELECT empr.ds_nome_fantasia, pvp.id_proposta, pe_1.id_endosso, 
                               ss.ds_status
                              FROM seguro.propostas_endosso pe_1
                              JOIN seguro.propostas_vistorias_previas pvp ON pe_1.id_proposta = pvp.id_proposta
                              JOIN ( SELECT v.id_proposta, max(v.id) AS id
                                 FROM seguro.propostas_vistorias_previas v
                                GROUP BY v.id_proposta) pvpp ON pvp.id = pvpp.id
                              JOIN sistema.vistorias_empresas ve ON pvp.id_vistoria = ve.id_vistoria
                              JOIN sistema.empresas empr ON ve.id_empresa = empr.id
                              JOIN sistema.status ss ON ss.id = pvp.id_status_analise
                             GROUP BY empr.ds_nome_fantasia, pvp.id_proposta, pe_1.id_endosso, ss.ds_status) empvist ON empvist.id_endosso = spe.id_endosso
                              LEFT JOIN sistema.empresas empproc ON empproc.id = ppe.id_empresa
                              LEFT JOIN seguro.v_nr_endosso ON v_nr_endosso.id_endosso = pp.id_endosso
                              LEFT JOIN seguro.v_id_proposta_mae_completo ON v_id_proposta_mae_completo.id_proposta = pp.id_proposta_mae
                              LEFT JOIN ( SELECT propostas.id_endosso, propostas.id, 
                               propostas_endosso.fl_proposta_vigente
                              FROM seguro.propostas
                              JOIN ( SELECT propostas_parcelas.id_proposta, 
                                       propostas_parcelas.dt_vencimento, propostas_parcelas.vl_segurado
                                      FROM seguro.propostas propostas_1
                                 JOIN seguro.propostas_endosso propostas_endosso_1 ON propostas_endosso_1.id_endosso = propostas_1.id_endosso AND propostas_endosso_1.fl_proposta_vigente = true
                              JOIN seguro.propostas_parcelas ON propostas_parcelas.id_proposta = propostas_1.id
                              LEFT JOIN seguro.proposta_boletos ON proposta_boletos.id_proposta = propostas_endosso_1.id_proposta AND proposta_boletos.nr_parcela = propostas_parcelas.nr_parcela
                             WHERE proposta_boletos.fl_pago = false) propostas_boletos ON propostas_boletos.id_proposta = propostas.id
                              JOIN seguro.propostas_endosso ON propostas_endosso.id_endosso = propostas.id_endosso AND propostas_endosso.fl_proposta_vigente = true
                             WHERE propostas_boletos.vl_segurado > 0::numeric AND propostas_boletos.dt_vencimento < to_char(now(), 'YYYY-mm-dd'::text)::date
                             GROUP BY propostas.id_endosso, propostas.id, propostas_endosso.fl_proposta_vigente) v_tem_parcela_vencida ON v_tem_parcela_vencida.id_endosso = pp.id_endosso
                             WHERE 1 = 1
                             ORDER BY a.id;
         ");

        $this->execute("ALTER TABLE produto.produtos_geral DROP column id_epoca_cultivo");
    }
}