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

/*!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 login_system
CREATE DATABASE IF NOT EXISTS `login_system` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;
USE `login_system`;

-- Dumping structure for procedure login_system.check_login
DELIMITER //
CREATE PROCEDURE `check_login`(
    IN p_username VARCHAR(50)
)
BEGIN
    SELECT id, username, password_hash
    FROM users
    WHERE username = p_username;
END//
DELIMITER ;

-- Dumping structure for procedure login_system.check_session
DELIMITER //
CREATE PROCEDURE `check_session`(
    IN p_token VARCHAR(255)
)
BEGIN
    SELECT u.username, s.last_activity, s.is_active
    FROM sessions s
    JOIN users u ON u.id = s.user_id
    WHERE s.session_token = p_token;
END//
DELIMITER ;

-- Dumping structure for procedure login_system.create_session
DELIMITER //
CREATE PROCEDURE `create_session`(
    IN p_user_id INT,
    IN p_session_token VARCHAR(255)
)
BEGIN
    INSERT INTO sessions (user_id, session_token, last_activity, is_active)
    VALUES (p_user_id, p_session_token, NOW(), 1);
END//
DELIMITER ;

-- Dumping structure for procedure login_system.create_user
DELIMITER //
CREATE PROCEDURE `create_user`(
    IN p_username VARCHAR(50),
    IN p_password_hash VARCHAR(255)
)
BEGIN
    INSERT INTO users (username, password_hash)
    VALUES (p_username, p_password_hash);
END//
DELIMITER ;

-- Dumping structure for procedure login_system.delete_user
DELIMITER //
CREATE PROCEDURE `delete_user`(
    IN p_user_id INT
)
BEGIN
    DELETE FROM users
    WHERE id = p_user_id;
END//
DELIMITER ;

-- Dumping structure for procedure login_system.logout_session
DELIMITER //
CREATE PROCEDURE `logout_session`(
    IN p_session_token VARCHAR(255)
)
BEGIN
    UPDATE sessions
    SET is_active = 0
    WHERE session_token = p_session_token;
END//
DELIMITER ;

-- Dumping structure for procedure login_system.logout_user
DELIMITER //
CREATE PROCEDURE `logout_user`(
    IN p_token VARCHAR(64)
)
UPDATE sessions
SET is_active = 0
WHERE session_token = p_token//
DELIMITER ;

-- Dumping structure for table login_system.sessions
CREATE TABLE IF NOT EXISTS `sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `session_token` varchar(255) DEFAULT NULL,
  `last_activity` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
  KEY `sessions_ibfk_1` (`user_id`),
  CONSTRAINT `sessions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Data exporting was unselected.

-- Dumping structure for procedure login_system.update_session
DELIMITER //
CREATE PROCEDURE `update_session`(IN p_session_token VARCHAR(255))
UPDATE sessions SET last_activity = NOW() WHERE session_token = p_session_token//
DELIMITER ;

-- Dumping structure for procedure login_system.update_user_password
DELIMITER //
CREATE PROCEDURE `update_user_password`(
    IN p_username VARCHAR(50),
    IN p_new_password_hash VARCHAR(255)
)
UPDATE users
SET password_hash = p_new_password_hash
WHERE username = p_username//
DELIMITER ;

-- Dumping structure for table login_system.users
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Data exporting was unselected.

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