DROP PROCEDURE IF EXISTS `getUserByUser`;
CREATE PROCEDURE `getUserByUser`(IN `v_user` VARCHAR(60))
SELECT *
FROM
	`authusers`
WHERE
	`user` =v_user;

DROP PROCEDURE IF EXISTS `insertSession`;
CREATE PROCEDURE `insertSession`(IN `v_sessionUserId` INT,
                                 IN `v_sessionHash` VARCHAR(40),
                                 IN `v_sessionExpireDate` DATETIME,
                                 IN `v_sessionIp` VARCHAR(39),
                                 IN `v_sessionAgent` VARCHAR(200),
                                 IN `v_sessionCookie_crc` VARCHAR(40))
INSERT INTO
	`AuthSessions`
    (
     `sessionUserId`,
     `sessionHash`,
     `sessionExpireDate`,
     `sessionIp`,
     `sessionAgent`,
     `sessionCookie_crc`)
VALUES
	(
     v_sessionUserId,
     v_sessionHash,
     v_sessionExpireDate,
     v_sessionIp,
     v_sessionAgent,
     v_sessionCookie_crc);



DROP PROCEDURE IF EXISTS `updateUserById`;
CREATE PROCEDURE `updateUserById`(IN `v_userName` VARCHAR(100),
                                  IN `v_user` VARCHAR(60),
                                  IN `v_userEmail` VARCHAR(100),
                                  IN `v_isActive` TINYINT(1),
                                  IN `v_roleId` INT,
                                  IN `v_userId` INT)
UPDATE
	`AuthUsers`
SET
	`user`=v_user,
	`userName`=v_userName,
  `userEmail`=v_userEmail,
  `userIsActive`=v_isActive,
  `userRoleId`=v_roleId
WHERE
	`userId`=v_userId ;



DROP PROCEDURE IF EXISTS `updateUserEmailByUserId`;
CREATE PROCEDURE `updateUserEmailByUserId`(IN `v_userId` INT, IN `v_userEmail` VARCHAR(60))
UPDATE
	`AuthUsers`
SET
    `userEmail`=v_userEmail
WHERE
	`userId`=v_userId ;

DROP PROCEDURE IF EXISTS `updateUserPasswordById`;
CREATE PROCEDURE `updateUserPasswordById`(IN `v_userId` INT, IN `v_password` VARCHAR(60))
UPDATE
	`authUsers`
SET
    `userPassword`=v_password
WHERE
	`userId`=v_userId ;

DROP PROCEDURE IF EXISTS `setActiveByUserId`;
CREATE PROCEDURE `setActiveByUserId`(IN `v_isActive` INT, IN `v_userId` VARCHAR(60))
UPDATE
	`AuthUsers`
SET
  userIsActive  = `v_isActive`
WHERE
  userId = `v_userId`;

DROP PROCEDURE IF EXISTS `getUserByEmail`;
CREATE PROCEDURE `getUserByEmail`(IN `v_userEmail` VARCHAR(60))
SELECT *
FROM
	`AuthUsers`
WHERE
	`userEmail` =v_userEmail;

DROP PROCEDURE IF EXISTS `getUserByUser`;
CREATE PROCEDURE `getUserByUser`(IN `v_user` VARCHAR(60))
SELECT *
FROM
	`AuthUsers`
WHERE
	`user` =v_user;

DROP PROCEDURE IF EXISTS `getUserById`;
CREATE PROCEDURE `getUserById`(IN `v_userId` VARCHAR(60))
SELECT *
FROM
	`AuthUsers`
WHERE
	`userId` =v_userId;

DROP PROCEDURE IF EXISTS `getUserIdByEmail`;
CREATE PROCEDURE `getUserIdByEmail`(IN `v_userEmail` VARCHAR(60))
SELECT `userId`
FROM
	`AuthUsers`
WHERE
	`userEmail` =v_userEmail;

DROP PROCEDURE IF EXISTS `deleteSessionByIdUser`;
CREATE PROCEDURE `deleteSessionByIdUser`(IN `v_userId` INT)
DELETE FROM
	`AuthSessions`

WHERE
	`sessionUserId`=v_userId ;

DROP PROCEDURE IF EXISTS `deleteSessionByHash`;
CREATE PROCEDURE `deleteSessionByHash`(IN `v_sessionHash` VARCHAR(40) )
DELETE FROM
	`AuthSessions`

WHERE
	`sessionHash`=v_sessionHash ;

DROP PROCEDURE IF EXISTS `getSessionByHash`;
CREATE PROCEDURE `getSessionByHash`(IN `v_sessionHash` VARCHAR(40) )
SELECT * FROM `AuthSessions`
WHERE `sessionHash`=v_sessionHash ;

DROP PROCEDURE IF EXISTS `countUserByEmail`;
CREATE PROCEDURE `countUserByEmail`(IN `v_userEmail` VARCHAR(40) )
SELECT count (*) FROM `AuthUsers`
WHERE `userEmail`=v_userEmail;

DELIMITER ;;
DROP PROCEDURE IF EXISTS `insertEmptyUser`;;
CREATE PROCEDURE `insertEmptyUser`(OUT `v_lastId` INT)
BEGIN
	INSERT INTO `AuthUsers` VALUES();
	SET @v_lastId = LAST_INSERT_ID();
	SELECT @v_lastId;
END ;;
DELIMITER ;

DROP PROCEDURE IF EXISTS `deleteUserById`;
CREATE PROCEDURE `deleteUserById`(IN `v_userId` INT)
DELETE FROM
	`AuthUser`

WHERE
	`userId`=v_userId ;

DROP PROCEDURE IF EXISTS `deleteRequestByIdUser`;
CREATE PROCEDURE `deleteRequestByIdUser`(IN `v_userId` INT)
DELETE FROM
	`AuthRequests`

WHERE
	`requestUserId`=v_userId ;

DROP PROCEDURE  IF EXISTS `getRequestIdByUserType`;
CREATE PROCEDURE `getRequestIdByUserType`(IN `v_userId` INT, IN `v_requestType` VARCHAR(20))
SELECT * FROM `AuthRequests`
WHERE `requestUserId` = `v_userId` AND `requestType` = `v_requestType`;

DROP PROCEDURE  IF EXISTS `insertRequest`;
CREATE PROCEDURE `insertRequest`(IN `v_requestUserId` INT,
                                 IN `v_requestRkey` VARCHAR(20),
                                 IN `v_requestExpireDate` DATETIME,
                                 IN `v_requestType` VARCHAR(20))
INSERT INTO `AuthRequests`
(
  `requestUserId`,
  `requestRkey`,
  `requestExpireDate`,
  `requestType`
)
VALUES (
  `v_requestUserId`,
  `v_requestRkey`,
  `v_requestExpireDate`,
  `v_requestType`
);

DROP PROCEDURE  IF EXISTS `getRequestByKeyType`;
CREATE PROCEDURE `getRequestByKeyType`(IN `v_requestRkey` INT, IN `v_requestType` VARCHAR(20))
SELECT * FROM `AuthRequests`
WHERE `requestRkey` = `v_requestRkey` AND `requestType` = `v_requestType`;

DROP PROCEDURE IF EXISTS `deleteRequestByRequestId`;
CREATE PROCEDURE `deleteRequestByRequestId`(IN `v_requestId` INT)
DELETE FROM
	`AuthRequests`

WHERE
	`requestId`=v_requestId ;

DROP PROCEDURE IF EXISTS `countAttemptsByIP`;
CREATE PROCEDURE `countAttemptsByIP`(IN `v_attemptIp` VARCHAR(40) )
SELECT count (*) FROM `AuthUsers`
WHERE `attemptIp`=v_attemptIp;

DROP PROCEDURE IF EXISTS `insertAttempt`;
CREATE PROCEDURE `insertAttempt`(IN `v_attemptIp` VARCHAR(39), IN `v_attemptExpireDate` DATETIME)
INSERT INTO AuthAttempts (attemptIp, attemptExpiredate) VALUES (v_attemptIp, v_attemptExpireDate);

DROP PROCEDURE IF EXISTS `deleteAttemptByAttemptId`;
CREATE PROCEDURE `deleteAttemptByAttemptId`(IN `v_attemptId` INT)
DELETE FROM
	`AuthAttempts`

WHERE
	`attemptId`=v_attemptId ;

DROP PROCEDURE IF EXISTS `getAttemptByIp`;
CREATE PROCEDURE `getAttemptByIp`(IN `v_attemptIp` INT)
SELECT * FROM AuthAttempts
WHERE attemptIp = v_attemptIp;

DROP PROCEDURE IF EXISTS `deleteAttemptByAttemptId`;
CREATE PROCEDURE `deleteAttemptByAttemptId`(IN `v_attemptId` INT)
DELETE FROM
	`AuthRequests`

WHERE
	`attemptId`=v_attemptId ;