-- --------------------------------------------------------
-- Server OS:                    Linux
-- HeidiSQL Version:             12.10.0.7000
-- --------------------------------------------------------


-- 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;

-- 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;

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

    -- Delete audit logs (optional but clean)
    DELETE FROM audit_log 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;

    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;

        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);

    IF uid IS NULL THEN
        RETURN NULL;
    END IF;

    SET token = UUID();

    RETURN token;
END//
DELIMITER ;
END//
DELIMITER ;

-- Data exporting was unselected.

