-- --------------------------------------------------------
-- Хост:                         127.0.0.1
-- Версия сервера:               10.4.32-MariaDB - mariadb.org binary distribution
-- Операционная система:         Win64
-- HeidiSQL Версия:              12.15.0.7171
-- --------------------------------------------------------

/*!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 */;


-- Дамп структуры базы данных db_login_project
CREATE DATABASE IF NOT EXISTS `db_login_project` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;
USE `db_login_project`;

-- Дамп структуры для функция db_login_project.fn_check_session
DELIMITER //
CREATE FUNCTION `fn_check_session`(p_session_token VARCHAR(64)) RETURNS varchar(50) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
    READS SQL DATA
    DETERMINISTIC
BEGIN
    DECLARE v_username VARCHAR(50);
    DECLARE v_last_activity TIMESTAMP;
    DECLARE v_timeout_minutes INT DEFAULT 30;
    SELECT u.username, s.last_activity INTO v_username, v_last_activity FROM sessions s JOIN users u ON s.user_id = u.user_id WHERE s.session_token = p_session_token;
    IF v_username IS NULL THEN RETURN NULL; END IF;
    IF TIMESTAMPDIFF(MINUTE, v_last_activity, CURRENT_TIMESTAMP) > v_timeout_minutes THEN RETURN NULL; END IF;
    RETURN v_username;
END//
DELIMITER ;

-- Дамп структуры для таблица db_login_project.sessions
CREATE TABLE IF NOT EXISTS `sessions` (
  `session_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `session_token` varchar(64) NOT NULL,
  `last_activity` timestamp NOT NULL DEFAULT current_timestamp(),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`session_id`),
  UNIQUE KEY `session_token` (`session_token`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `sessions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Дамп данных таблицы db_login_project.sessions: ~0 rows (приблизительно)

-- Дамп структуры для процедура db_login_project.sp_delete_user
DELIMITER //
CREATE PROCEDURE `sp_delete_user`(
    IN p_username VARCHAR(50),
    OUT result_msg VARCHAR(100)
)
BEGIN
    DECLARE v_user_id INT;
    SELECT user_id INTO v_user_id FROM users WHERE username = p_username;
    IF v_user_id IS NULL THEN
        SET result_msg = 'ERROR_USER_NOT_FOUND';
    ELSE
        DELETE FROM users WHERE user_id = v_user_id;
        SET result_msg = 'SUCCESS';
    END IF;
END//
DELIMITER ;

-- Дамп структуры для процедура db_login_project.sp_login
DELIMITER //
CREATE PROCEDURE `sp_login`(
    IN p_username VARCHAR(50),
    IN p_password VARCHAR(255),
    OUT session_token VARCHAR(64),
    OUT result_msg VARCHAR(100)
)
BEGIN
    DECLARE v_user_id INT;
    DECLARE v_password_hash VARCHAR(64);
    DECLARE v_token VARCHAR(64);
    SELECT user_id, password_hash INTO v_user_id, v_password_hash FROM users WHERE username = p_username;
    IF v_user_id IS NULL THEN
        SET result_msg = 'ERROR_INVALID_CREDENTIALS';
        SET session_token = NULL;
    ELSEIF v_password_hash != SHA2(p_password, 256) THEN
        SET result_msg = 'ERROR_INVALID_CREDENTIALS';
        SET session_token = NULL;
    ELSE
        DELETE FROM sessions WHERE user_id = v_user_id;
        SET v_token = SHA2(CONCAT(v_user_id, UNIX_TIMESTAMP(), RAND()), 256);
        INSERT INTO sessions (user_id, session_token, last_activity) VALUES (v_user_id, v_token, CURRENT_TIMESTAMP);
        SET session_token = v_token;
        SET result_msg = 'SUCCESS';
    END IF;
END//
DELIMITER ;

-- Дамп структуры для процедура db_login_project.sp_logout
DELIMITER //
CREATE PROCEDURE `sp_logout`(
    IN p_session_token VARCHAR(64),
    OUT result_msg VARCHAR(100)
)
BEGIN
    DECLARE rows_affected INT;
    DELETE FROM sessions WHERE session_token = p_session_token;
    SET rows_affected = ROW_COUNT();
    IF rows_affected > 0 THEN
        SET result_msg = 'SUCCESS';
    ELSE
        SET result_msg = 'ERROR_SESSION_NOT_FOUND';
    END IF;
END//
DELIMITER ;

-- Дамп структуры для процедура db_login_project.sp_register_user
DELIMITER //
CREATE PROCEDURE `sp_register_user`(
    IN p_username VARCHAR(50),
    IN p_password VARCHAR(255),
    OUT result_msg VARCHAR(100)
)
BEGIN
    DECLARE user_exists INT DEFAULT 0;
    SELECT COUNT(*) INTO user_exists FROM users WHERE username = p_username;
    IF user_exists > 0 THEN
        SET result_msg = 'ERROR_USER_EXISTS';
    ELSE
        INSERT INTO users (username, password_hash) VALUES (p_username, SHA2(p_password, 256));
        SET result_msg = 'SUCCESS';
    END IF;
END//
DELIMITER ;

-- Дамп структуры для процедура db_login_project.sp_update_activity
DELIMITER //
CREATE PROCEDURE `sp_update_activity`(
    IN p_session_token VARCHAR(64),
    OUT result_msg VARCHAR(100)
)
BEGIN
    DECLARE v_last_activity TIMESTAMP;
    DECLARE v_timeout_minutes INT DEFAULT 30;
    SELECT last_activity INTO v_last_activity FROM sessions WHERE session_token = p_session_token;
    IF v_last_activity IS NULL THEN
        SET result_msg = 'ERROR_SESSION_NOT_FOUND';
    ELSEIF TIMESTAMPDIFF(MINUTE, v_last_activity, CURRENT_TIMESTAMP) > v_timeout_minutes THEN
        DELETE FROM sessions WHERE session_token = p_session_token;
        SET result_msg = 'ERROR_SESSION_EXPIRED';
    ELSE
        UPDATE sessions SET last_activity = CURRENT_TIMESTAMP WHERE session_token = p_session_token;
        SET result_msg = 'SUCCESS';
    END IF;
END//
DELIMITER ;

-- Дамп структуры для процедура db_login_project.sp_update_user
DELIMITER //
CREATE PROCEDURE `sp_update_user`(
    IN p_old_username VARCHAR(50),
    IN p_new_username VARCHAR(50),
    IN p_new_password VARCHAR(255),
    OUT result_msg VARCHAR(100)
)
BEGIN
    DECLARE v_user_id INT;
    DECLARE username_exists INT DEFAULT 0;
    SELECT user_id INTO v_user_id FROM users WHERE username = p_old_username;
    IF v_user_id IS NULL THEN
        SET result_msg = 'ERROR_USER_NOT_FOUND';
    ELSE
        IF p_new_username IS NOT NULL AND p_new_username != p_old_username THEN
            SELECT COUNT(*) INTO username_exists FROM users WHERE username = p_new_username;
            IF username_exists > 0 THEN
                SET result_msg = 'ERROR_USERNAME_TAKEN';
            ELSE
                UPDATE users SET username = p_new_username, password_hash = CASE WHEN p_new_password IS NOT NULL THEN SHA2(p_new_password, 256) ELSE password_hash END WHERE user_id = v_user_id;
                SET result_msg = 'SUCCESS';
            END IF;
        ELSE
            UPDATE users SET password_hash = CASE WHEN p_new_password IS NOT NULL THEN SHA2(p_new_password, 256) ELSE password_hash END WHERE user_id = v_user_id;
            SET result_msg = 'SUCCESS';
        END IF;
    END IF;
END//
DELIMITER ;

-- Дамп структуры для таблица db_login_project.users
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(64) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Дамп данных таблицы db_login_project.users: ~0 rows (приблизительно)

/*!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) */;