-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.32-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win64
-- HeidiSQL Version:             12.6.0.6765
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- Dumping database structure for auth_db
CREATE DATABASE IF NOT EXISTS `auth_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `auth_db`;

-- Dumping structure for function auth_db.check_user_credentials
DELIMITER //
CREATE FUNCTION `check_user_credentials`(p_username VARCHAR(50),
    p_password VARCHAR(255)
) RETURNS tinyint(4)
    DETERMINISTIC
BEGIN
    DECLARE result TINYINT;

    SELECT COUNT(*) INTO result
    FROM users
    WHERE username = p_username
      AND password_hash = SHA2(p_password,256);

    RETURN result;
END//
DELIMITER ;

-- Dumping structure for procedure auth_db.create_user
DELIMITER //
CREATE PROCEDURE `create_user`(
    IN p_username VARCHAR(50),
    IN p_password VARCHAR(255)
)
BEGIN
    INSERT INTO users (username, password_hash, created_at)
    VALUES (p_username, SHA2(p_password,256), NOW());
END//
DELIMITER ;

-- Dumping structure for procedure auth_db.delete_user
DELIMITER //
CREATE PROCEDURE `delete_user`(
    IN p_username VARCHAR(50)
)
BEGIN
    DELETE FROM users
    WHERE username = p_username;
END//
DELIMITER ;

-- Dumping structure for function auth_db.is_user_logged_in
DELIMITER //
CREATE FUNCTION `is_user_logged_in`(p_username VARCHAR(50)) RETURNS tinyint(4)
    DETERMINISTIC
BEGIN
    DECLARE result TINYINT DEFAULT 0;

    SELECT
        CASE
            WHEN last_activity IS NOT NULL
             AND last_activity > NOW() - INTERVAL 5 MINUTE
            THEN 1
            ELSE 0
        END
    INTO result
    FROM users
    WHERE username = p_username;

    RETURN result;
END//
DELIMITER ;

-- Dumping structure for procedure auth_db.login_user
DELIMITER //
CREATE PROCEDURE `login_user`(
    IN p_username VARCHAR(50),
    IN p_password VARCHAR(255)
)
BEGIN
    IF EXISTS (
        SELECT 1 FROM users
        WHERE username = p_username
          AND password_hash = SHA2(p_password,256)
    ) THEN
        UPDATE users
        SET last_login = NOW(),
            last_activity = NOW(),
            last_logout = NULL
        WHERE username = p_username;
    END IF;
END//
DELIMITER ;

-- Dumping structure for procedure auth_db.logout_user
DELIMITER //
CREATE PROCEDURE `logout_user`(
    IN p_username VARCHAR(50)
)
BEGIN
    UPDATE users
    SET last_logout = NOW()
    WHERE username = p_username;
END//
DELIMITER ;

-- Dumping structure for procedure auth_db.refresh_session
DELIMITER //
CREATE PROCEDURE `refresh_session`(
    IN p_username VARCHAR(50)
)
BEGIN
    UPDATE users
    SET last_activity = NOW()
    WHERE username = p_username;
END//
DELIMITER ;

-- Dumping structure for procedure auth_db.update_user_password
DELIMITER //
CREATE PROCEDURE `update_user_password`(
    IN p_username VARCHAR(50),
    IN p_new_password VARCHAR(255)
)
BEGIN
    UPDATE users
    SET password_hash = SHA2(p_new_password,256)
    WHERE username = p_username;
END//
DELIMITER ;

-- Dumping structure for table auth_db.users
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password_hash` char(64) NOT NULL,
  `created_at` datetime NOT NULL,
  `last_login` datetime DEFAULT NULL,
  `last_logout` datetime DEFAULT NULL,
  `last_activity` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table auth_db.users: ~5 rows (approximately)
INSERT INTO `users` (`id`, `username`, `password_hash`, `created_at`, `last_login`, `last_logout`, `last_activity`) VALUES
	(1, '1', '6b86b273ff34fce19d6b804eff5a3f5747ada4eaa22f1d49c01e52ddb7875b4b', '2026-01-24 22:10:43', '2026-01-24 22:25:43', '2026-01-24 22:25:48', '2026-01-24 22:25:43'),
	(3, 'test', 'ba28631c63c1fc39be3b3844853e8d1e5c37ad2b4248f3c36974a10d53e31563', '2026-01-24 22:14:23', NULL, NULL, NULL),
	(4, '4', '4b227777d4dd1fc61c6f884f48641d02b4d121d3fd328cb08b5531fcacdabf8a', '2026-01-24 22:21:36', '2026-01-24 22:21:58', '2026-01-24 22:22:02', '2026-01-24 22:22:00'),
	(8, 'r', '454349e422f05297191ead13e21d3db520e5abef52055e4964b82fb213f593a1', '2026-02-05 10:54:03', '2026-02-05 10:54:50', '2026-02-05 10:54:53', '2026-02-05 10:54:52'),
	(9, 't', 'e3b98a4da31a127d4bde6e43033f66ba274cab0eb7eb1c70ec41402bf6273dd8', '2026-02-05 10:55:01', '2026-02-05 10:55:25', '2026-02-05 10:55:31', '2026-02-05 10:55:29');

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;