-- --------------------------------------------------------
-- Host:                         153.92.1.116
-- Server version:               8.0.45-0ubuntu0.22.04.1 - (Ubuntu)
-- Server OS:                    Linux
-- 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 structure for table gm_login_system.audit_log
CREATE TABLE IF NOT EXISTS `audit_log` (
  `log_id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `action` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `action_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `ip_address` varchar(45) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`log_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Data exporting was unselected.

-- Dumping structure for function gm_login_system.fn_check_user_password
DELIMITER //
CREATE FUNCTION `fn_check_user_password`() RETURNS tinyint(1)
    DETERMINISTIC
    SQL SECURITY INVOKER
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 gm_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
    DETERMINISTIC
    SQL SECURITY INVOKER
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 gm_login_system.fn_delete_user
DELIMITER //
CREATE PROCEDURE `fn_delete_user`(IN p_user_id INT)
BEGIN
    -- Delete related sessions
    DELETE FROM sessions WHERE user_id = p_user_id;

    -- Delete audit logs (optional but clean)
    DELETE FROM audit_log WHERE user_id = p_user_id;

    -- Delete user completely
    DELETE FROM users WHERE user_id = p_user_id;
END//
DELIMITER ;

-- Dumping structure for function gm_login_system.fn_get_user_id
DELIMITER //
CREATE FUNCTION `fn_get_user_id`(p_session_token VARCHAR(64)) RETURNS int
    READS SQL DATA
BEGIN
    DECLARE uid INT;

    SELECT user_id INTO uid
    FROM sessions
    WHERE session_token = p_session_token
      AND is_active = TRUE
      AND expires_at > NOW()
    LIMIT 1;

    RETURN uid;
END//
DELIMITER ;

-- Dumping structure for function gm_login_system.fn_is_logged_in
DELIMITER //
CREATE FUNCTION `fn_is_logged_in`(
	`p_session_token` VARCHAR(64)
) RETURNS tinyint(1)
    DETERMINISTIC
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 2 MINUTE)
        WHERE session_token = p_session_token;

        RETURN TRUE;
    END IF;

    RETURN FALSE;
END//
DELIMITER ;

-- Dumping structure for function gm_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
    DETERMINISTIC
    SQL SECURITY INVOKER
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 2 MINUTE));

    RETURN token;
END//
DELIMITER ;

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

-- Dumping structure for procedure gm_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_password VARCHAR(255) -- NEW
)
BEGIN
    UPDATE users
    SET username = p_username,
        email = p_email,
        password_hash = 
            CASE 
                WHEN p_password IS NOT NULL AND p_password != '' 
                THEN SHA2(p_password, 256)
                ELSE password_hash
            END
    WHERE user_id = p_user_id;
END//
DELIMITER ;

-- Dumping structure for table gm_login_system.sessions
CREATE TABLE IF NOT EXISTS `sessions` (
  `session_id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `session_token` varchar(64) COLLATE utf8mb4_general_ci 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=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Data exporting was unselected.

-- Dumping structure for table gm_login_system.users
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) COLLATE utf8mb4_general_ci NOT NULL,
  `password_hash` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_general_ci 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=18 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) */;