Login / Registrierung von Usern

tbl user -- successfully registered users
id                 int     primary key auto increment
email            string     unique email
password_hash    string     the hashed password
nickname        string     unique nickname
is_active        bool     indicates if th user is active
created_at        ...
updated_at         ...
-- uidx email; uidx nickname

-- note: would use tbl user_login to store logins (id, user_id, password_hash, is_active, created_at, updated_at)
-- note: missing user email confirmation at all

user_invitation -- users invite new users (1:n)
id                     int             primary key auto increment
user_id                int             fk (foreign key) user.id
invitation_token    string             the unique invitation token|hash
is_active            bool             indicates if th user-invitation is active
valid_until            datetime        the datetime string until the invitation is valid
redeemed_at            null|datetime    the datetime string the invitation has been successfully accepted
created_at            ...
updated_at             ...
-- uidx invitation_token


-----

INPUT
data = // POST data of new user to create 
invitationToken = // POST data of invitation token provided

-----

// CHECK INPUT

if !strlen(invitationToken)                            throw invalid invitation exception --invalid token

// todo: check new user data ... like email validation, already exists by nick and email, ....

-----

CHECK INVITATION

invitation = ... SELECT * FROM user_invitation WHERE invitation_token = :invitation_token LIMIT 1; 
// limit 1 to load the 1st always in case of duplicates (duplicates gets ignored that way)

// check if invitation actually exists
if !is_array(invitation) or !invitation.id             throw invalid invitation exception --not exist

// check if invitation is active
if !(bool)invitation.is_active                        throw invalid invitation exception --disabled

// check if invitation is still valid
if invitation.valid_until < NOW                     throw invalid invitation exception --expired

// check if invitation has been already redeemed
if strlen(invitation.redeemed)                        throw invalid invitation exception --already redeemed

---

hostUserId = invitation.user_id

---

CHECK HOST

// check if user who invited the new user is valid
host = ... SELECT * FROM user WHERE user_id = :host_user_id LIMIT 1; 
//limit 1 to load the 1st always in case of duplicates (duplicates gets ignored that way)

// check if host actually exists
if !is_array(host) or !host.id                         throw invalid invitation exception --host not exist

// check if host is active
if !(bool)host.is_active                            throw invalid invitation exception --host disabled

// todo: check if host (user) is allowed to invite new users

---

REGISTER NEW USER

try 
    // insert new user
    INSERT INTO ... 
    
    // set redeemed_at datetime to now
    UPDATE user_invitation SET redeemed_at = NOW WHERE invitation_token = :invitation_token; 
    // no limit 1 - on dublicates all get redeemed on purpose

    // todo: would start new user email confirmation process here

    return success

catch 
    error handling ...
    return failure