-- --------------------------------------------------------------------------------
-- _060_user Group Routines
-- @__userId    - user id currently logged in
-- @__userName  - user name currently logged in
-- @__userIp    - IP of currently logged in user
-- @__userLanguage - current user's language preference
-- --------------------------------------------------------------------------------
DELIMITER $$$

create function __user_ValidateId(user_id_ int unsigned) returns int unsigned
deterministic reads sql data
begin
    declare user_id int unsigned;
    
    select u.id into user_id 
    from userT u
    join nodeT n on u.id = n.id
    join nodeTypeT nt on n.nodeTypeId = nt.id
    where u.id = user_id_ 
        and nt.internalTypeId = 'user';
    
    if user_id is null then
        call raiseError1('user', 'validate', user_id_, 'user does not exist');
    end if;

    return 1;
end$$$


create function user_CurrentId() returns int unsigned
deterministic no sql
return @__userId$$$


create function user_CurrentName() returns varchar(255)
deterministic no sql
return @__userName$$$


create function user_CurrentIp() returns varchar(40)
deterministic no sql
return @__userIp$$$


create function user_CurrentLanguage() returns char(3)
deterministic no sql
return @__userLanguage$$$


create function user_Create(
    name_ varchar(255), phone_ varchar(50), email_ varchar(255), login_ varchar(255), password_ varchar(100),
    role_id_ varchar(30) binary, enabled_ enum('n', 'y'),
    birthdate_ date, 
    preferred_language_ char(3), 
    token_ varchar(20)
) returns int unsigned
not deterministic modifies sql data
begin
    declare user_id int unsigned;
    
    set user_id = node_LinkDefaults(__node_Create(null, 'user', name_));
    
    insert userT(
        id,
        name, phone, email, login, password,
        roleId, enabled,
        birthDate, 
        preferredLanguage, 
        token,
        phoneConfirmationCode, emailConfirmationCode) 
    select
        user_id,
        trim(name_), trim(phone_), trim(email_), trim(login_), password_,
        coalesce(role_id_, ur.id), enabled_,
        birthdate_, 
        preferred_language_, 
        token_,
        '', ''
    from userRoleT ur
    where ur.isDefault = 'y';
    
    return user_id;
end$$$


create function user_Delete(user_id_ int unsigned) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __user_ValidateId(user_id_);
    return __node_Delete(user_id_);
end$$$


create function user_Update(
    user_id_ int unsigned,
    name_ varchar(255), phone_ varchar(50), 
    email_ varchar(255), reset_email_ enum('n', 'y'), 
    login_ varchar(255), password_ varchar(100),
    role_id_ varchar(30) binary, enabled_ enum('n', 'y'),
    birthdate_ date, reset_birthdate_ enum('n', 'y'), 
    preferred_language_ char(3), reset_preferred_language_ enum('n', 'y'),
    token_ varchar(20), reset_token_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    declare current_phone, new_phone varchar(50);
    declare current_email, new_email varchar(255);
    declare name__ varchar(255) default trim(name_);

    set @1 = __user_ValidateId(user_id_);
    
    select 
        phone, email, trim(coalesce(phone_, phone)), if(reset_email_ = 'y', null, trim(coalesce(email_, email)))
    into 
        current_phone, current_email, new_phone, new_email
    from userT
    where id = user_id_;
    
    set @2 = __node_Update(user_id_, left(name__, 50));
    
    update userT set
        name = coalesce(name__, name), 
        phone = new_phone, 
        email = new_email, 
        login = trim(coalesce(login_, login)), 
        password = coalesce(password_, password),
        
        roleId = coalesce(role_id_, roleId), 
        enabled = coalesce(enabled_, enabled),
        
        birthDate = if(reset_birthdate_ = 'y', null, coalesce(birthdate_, birthDate)),
        preferredLanguage = if(reset_preferred_language_ = 'y', null, coalesce(preferred_language_, preferredLanguage)),
        token = if(reset_token_ = 'y', null, coalesce(token_, token)),
        
        phoneConfirmationCode = if(
                                    phoneConfirmationCode is null 
                                        and current_phone != new_phone 
                                        and new_phone is not null, 
                                    '', 
                                    phoneConfirmationCode),
        emailConfirmationCode = if(
                                    emailConfirmationCode is null 
                                        and current_email != new_email
                                        and new_email is not null, 
                                    '', 
                                    emailConfirmationCode)
    where id = user_id_;
    
    return user_id_;
end$$$


create function user_SetConfirmationCode(
    user_id_ int unsigned,
    phone_confirmation_code_ varchar(255),
    phone_confirmation_expiry_ datetime,
    reset_phone_code_ enum('n', 'y'),
    email_confirmation_code_ varchar(255),
    email_confirmation_expiry_ datetime,
    reset_email_code_ enum('n', 'y')
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __user_ValidateId(user_id_);
    
    update userT set
        phoneConfirmationCode = if(reset_phone_code_ = 'y', null, coalesce(phone_confirmation_code_, phoneConfirmationCode)),
        phoneConfirmationExpiry = if(reset_phone_code_ = 'y', null, coalesce(phone_confirmation_expiry_, phoneConfirmationExpiry)),
        emailConfirmationCode = if(reset_email_code_ = 'y', null, coalesce(email_confirmation_code_, emailConfirmationCode)),
        emailConfirmationExpiry = if(reset_email_code_ = 'y', null, coalesce(email_confirmation_expiry_, emailConfirmationExpiry))
    where id = user_id_;
    
    return user_id_;
end$$$


create function user_CheckConfirmationCode(
    user_id_ int unsigned,
    phone_confirmation_code_ varchar(255),
    email_confirmation_code_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    declare res, phone_confirmed, email_confirmed int unsigned;

    set @1 = __user_ValidateId(user_id_);
    
    select 
        case when phone_confirmation_code_ is not null
            and phoneConfirmationCode = phone_confirmation_code_
            and phoneConfirmationExpiry >= now() then 1 else 0 end,
        case when email_confirmation_code_ is not null
            and emailConfirmationCode = email_confirmation_code_
            and emailConfirmationExpiry >= now() then 2 else 0 end
    into
        phone_confirmed, email_confirmed
    from userT
    where id = user_id_;
    
    set res = phone_confirmed | email_confirmed;
    return res;
end$$$


create function user_SetOauth(
    user_id_ int unsigned, 
    provider_id_ varchar(30) binary, 
    identity_ varchar(255)
) returns int unsigned
not deterministic modifies sql data
begin
    set @1 = __user_ValidateId(user_id_);
    
    if identity_ is null then
        delete from userOauthT 
        where userId = user_id_ 
            and oauthProviderId = coalesce(provider_id_, oauthProviderId);
    else
        insert userOauthT(userId, oauthProviderId, identity)
        values(user_id_, provider_id_, identity_)
        on duplicate key update userOauthT.identity = identity_;
    end if;
    
    return user_id_;
end$$$


create function user_Login(user_id_ int unsigned, browser_langid_ char(3), user_ip_ varchar(40)) returns int unsigned
not deterministic
begin
    declare user_id int unsigned;
    declare user_name varchar(255);
    declare language_id char(3);
    
    set @1 = (user_id_ is null) or __user_ValidateId(user_id_);
    
    select 
        u.id, u.preferredLanguage, u.name
    into 
        user_id, language_id, user_name
    from userT u 
    where u.id = user_id_
        and u.enabled = 'y';
    
    set @__userId = user_id;
    set @__userName = user_name;
    set @__userIp = user_ip_;
    set @__userLanguage = coalesce(
        language_id, 
        (select id from languageT l where id = browser_langid_), 
        (select id from languageT l where isDefault = 'y'));
        
    if user_id is null and user_id_ is not null then
        call raiseError1('user', 'login', user_id_, 'user is disabled');
    end if;
    
    return user_id;
end$$$