<?php
declare(strict_types=1);

use Phinx\Migration\AbstractMigration;

final class TaskReservas extends AbstractMigration
{
    public function up() : void {
        $this->execute(<<<SQL
DROP VIEW sistema.v_login;
CREATE VIEW sistema.v_login
AS
( SELECT u.fl_ativo,
    u.fl_del,
    u.id,
    u.id_role,
    u.ds_nome_usuario,
    u.ds_senha,
    u.tp_usuario,
    r.ds_chave,
    e.cpf_cnpj,
    e.ds_nome_fantasia,
    d.ds_chave AS ds_departamento,
    d.id AS id_departamento,
    f.fl_responsavel_empresa,
    e.id AS id_empresa,
    case 
        when u.tp_usuario = 'F' then lower(f.ds_email)
        when u.tp_usuario = 'C' then lower(c.ds_email)
        when u.tp_usuario = 'P' then lower(p.ds_email)
        else ''
    end as ds_email
   FROM ((((((sistema.usuarios u
     JOIN sistema.roles r ON ((u.id_role = r.id)))
     LEFT JOIN sistema.corretores c ON ((c.id_usuario = u.id)))
     LEFT JOIN sistema.prepostos p ON ((p.id_usuario = u.id)))
     LEFT JOIN sistema.funcionarios f ON ((f.id_usuario = u.id)))
     LEFT JOIN sistema.empresas e ON ((f.id_empresa = e.id)))
     LEFT JOIN sistema.departamentos d ON ((f.id_departamento = d.id))));
SQL
        );
    }

    public function down() : void {
        $this->execute(<<<SQL
DROP VIEW sistema.v_login;
CREATE VIEW sistema.v_login
AS
( SELECT u.fl_ativo,
    u.fl_del,
    u.id,
    u.id_role,
    u.ds_nome_usuario,
    u.ds_senha,
    u.tp_usuario,
    r.ds_chave,
    e.cpf_cnpj,
    e.ds_nome_fantasia,
    d.ds_chave AS ds_departamento,
    d.id AS id_departamento,
    f.fl_responsavel_empresa,
    e.id AS id_empresa
   FROM ((((((sistema.usuarios u
     JOIN sistema.roles r ON ((u.id_role = r.id)))
     LEFT JOIN sistema.corretores c ON ((c.id_usuario = u.id)))
     LEFT JOIN sistema.prepostos p ON ((p.id_usuario = u.id)))
     LEFT JOIN sistema.funcionarios f ON ((f.id_usuario = u.id)))
     LEFT JOIN sistema.empresas e ON ((f.id_empresa = e.id)))
     LEFT JOIN sistema.departamentos d ON ((f.id_departamento = d.id))));
SQL
        );

    }
}