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

/*!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 table login_system.audit_log
CREATE TABLE IF NOT EXISTS `audit_log` (
  `log_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(255) DEFAULT NULL,
  `action_time` datetime DEFAULT current_timestamp(),
  `ip_address` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Data exporting was unselected.

-- Dumping structure for function login_system.fn_check_user_password
DELIMITER //
CREATE FUNCTION `fn_check_user_password`(p_username VARCHAR(50),
    p_password VARCHAR(255)
) RETURNS tinyint(1)
RETURN EXISTS (
    SELECT 1 FROM users
    WHERE username = p_username
      AND password_hash = SHA2(p_password,256)
      AND is_active = TRUE
)//
DELIMITER ;

-- Dumping structure for function login_system.fn_create_user
DELIMITER //
CREATE FUNCTION `fn_create_user`(p_username VARCHAR(50),
    p_password VARCHAR(255),
    p_email VARCHAR(100)
) RETURNS int(11)
BEGIN
    DECLARE new_id INT;

    INSERT INTO users(username, password_hash, email)
    VALUES (p_username, SHA2(p_password,256), p_email);

    SET new_id = LAST_INSERT_ID();
    RETURN new_id;
END//
DELIMITER ;

-- Dumping structure for procedure login_system.fn_delete_user
DELIMITER //
CREATE PROCEDURE `fn_delete_user`(IN p_user_id INT)
UPDATE users SET is_active = FALSE WHERE user_id = p_user_id//
DELIMITER ;

-- Dumping structure for function login_system.fn_is_logged_in
DELIMITER //
CREATE FUNCTION `fn_is_logged_in`(p_session_token VARCHAR(64)) RETURNS tinyint(1)
BEGIN
    DECLARE valid INT;

    SELECT COUNT(*) INTO valid
    FROM sessions
    WHERE session_token = p_session_token
      AND is_active = TRUE
      AND expires_at > NOW();

    IF valid > 0 THEN
        UPDATE sessions
        SET last_activity_at = NOW(),
            expires_at = DATE_ADD(NOW(), INTERVAL 10 MINUTE)
        WHERE session_token = p_session_token;
        RETURN TRUE;
    END IF;

    RETURN FALSE;
END//
DELIMITER ;

-- Dumping structure for function login_system.fn_login
DELIMITER //
CREATE FUNCTION `fn_login`(p_username VARCHAR(50),
    p_password VARCHAR(255)
) RETURNS varchar(64) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
BEGIN
    DECLARE uid INT;
    DECLARE token VARCHAR(64);

    SELECT user_id INTO uid
    FROM users
    WHERE username=p_username
      AND password_hash=SHA2(p_password,256)
      AND is_active=TRUE;

    IF uid IS NULL THEN
        RETURN NULL;
    END IF;

    SET token = UUID();

    INSERT INTO sessions(user_id, session_token, expires_at)
    VALUES (uid, token, DATE_ADD(NOW(), INTERVAL 10 MINUTE));

    RETURN token;
END//
DELIMITER ;

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

-- Dumping structure for procedure login_system.fn_update_user
DELIMITER //
CREATE PROCEDURE `fn_update_user`(
    IN p_user_id INT,
    IN p_username VARCHAR(50),
    IN p_email VARCHAR(100),
    IN p_is_active BOOLEAN
)
UPDATE users
SET username = p_username,
    email = p_email,
    is_active = p_is_active
WHERE user_id = p_user_id//
DELIMITER ;

-- Dumping structure for table login_system.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,
  `created_at` datetime DEFAULT current_timestamp(),
  `last_activity_at` datetime DEFAULT current_timestamp(),
  `expires_at` datetime NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  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`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Data exporting was unselected.

-- Dumping structure for table login_system.users
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=6 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) */;