delete from sessions;
delete from logins;
delete from login_failures;

drop procedure login;
drop procedure cookie_session;
delimiter //
create procedure login (IN email1 text, IN password1 text, IN address1 int, OUT cookie varchar(36), 

OUT this_user_id int unsigned)
language SQL
comment 'Returns a cookie UUID if successful'
NOT DETERMINISTIC
BEGIN
  declare all_done INT(1);
  declare saved_password text;
  declare cur_1 CURSOR FOR SELECT id, password FROM users WHERE email = email1;
  declare cur_2 CURSOR FOR SELECT uuid();
  declare continue handler for not found SET all_done = 1;
  SET all_done = 0;
  OPEN cur_1;
  FETCH cur_1 into this_user_id, saved_password;
  IF all_done != 1 THEN
    IF saved_password = password1 THEN
      OPEN cur_2;
      FETCH cur_2 into cookie;
      INSERT INTO logins     (user_id, datetime, ip) VALUES (this_user_id, now(), address1);
      INSERT INTO sessions (user_id, last_access, cookie) VALUES (this_user_id, now(), cookie);
      UPDATE users SET last_login = now() where id = this_user_id;
      CLOSE cur_2;
    ELSE
      INSERT INTO login_failures (user_id, datetime, ip) VALUES (this_user_id, now(), address1);
    END IF;
  END IF;
  CLOSE cur_1;
END ; //

create procedure cookie_session (IN cookie1 varchar(36), OUT this_user_id int unsigned)
language SQL
NOT DETERMINISTIC
comment 'Returns the user_id if successful'
BEGIN
  declare all_done INT(1);
  declare cur_1 CURSOR FOR SELECT user_id from sessions WHERE cookie = cookie1;
  declare continue handler for not found SET all_done = 1;
  SET all_done = 0;
  open cur_1;
  FETCH cur_1 INTO this_user_id;
  IF all_done != 1 THEN
    UPDATE sessions SET last_access = now() WHERE cookie = cookie1;
  END IF;
  CLOSE cur_1;
END ;//

create procedure user_change_password (IN user_id1 int unsigned, IN password_new text, OUT result int)
language SQL
comment 'Returns either true or false'
BEGIN
  declare all_done INT(1);
  declare this_password text;
  declare cur_1 CURSOR FOR SELECT password from users where id = user_id1 and password != 

password_new;
  declare continue handler for not found SET all_done = 1;
  SET all_done = 0;
  SET result = 0;
  OPEN cur_1;
  FETCH cur_1 into this_password;
  IF all_done != 1 THEN
    UPDATE users SET password_previous = this_password, password = password_new, reset_password = 0, 

password_change_time = now() WHERE id = user_id1;
    SET result = 1;
  END IF;
  CLOSE cur_1;
END ; //


create procedure clean_sessions (OUT result int unsigned) 
language SQL
comment 'Returns the number of sessions cleaned'
BEGIN
  declare this_cookie varchar(36);
  declare this_user_id int unsigned;
  declare this_access_time datetime;
  declare all_done int(1);
  declare cur_1 CURSOR FOR SELECT user_id, cookie, last_access from sessions WHERE last_access < now() 

- INTERVAL 600 SECOND;
  declare continue handler for not found SET all_done = 1;
  OPEN cur_1;
  SET result = 0;
  REPEAT 
      FETCH cur_1 into this_user_id, this_cookie, this_access_time;
      if all_done != 1 THEN
      SELECT 'Here', @this_user_id, @this_cookie, @this_access_times;
      UPDATE users SET last_access = this_access_time WHERE id = this_user_id AND last_access < 

this_access_time;
      DELETE FROM sessions where cookie = this_cookie;
      SET result = result+1;
    END IF;
    UNTIL all_done = 1
  END REPEAT;
  CLOSE cur_1;
END ; //

\d ;

call login('james@rcpt.to', 'hello', 2345, @cookie, @local_user_id);
call cookie_session(@cookie, @local_user_id_2);
select * from logins; select * from sessions; select * from login_failures;



