CREATE DATABASE IF NOT EXISTS login_db_mk CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE login_db_mk;

-- Lietotāji

CREATE TABLE IF NOT EXISTS users (

id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(100) NOT NULL UNIQUE,

password_hash CHAR(64) NOT NULL,

salt CHAR(36) NOT NULL,

active TINYINT(1) NOT NULL DEFAULT 1,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB;

-- Sesijas

CREATE TABLE IF NOT EXISTS sessions (

session_id CHAR(36) NOT NULL PRIMARY KEY,

user_id INT UNSIGNED NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

last_active TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

expires_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

) ENGINE=InnoDB;

DELIMITER //

-- 2.1 Izveidot lietotāju

CREATE FUNCTION create_user(p_username VARCHAR(100), p_password VARCHAR(255))

RETURNS INT

BEGIN

DECLARE v_count INT DEFAULT 0;

DECLARE v_salt CHAR(36);

DECLARE v_hash CHAR(64);

SELECT COUNT(*) INTO v_count FROM users WHERE username = p_username;

IF v_count > 0 THEN

RETURN 0; -- Lietotājs jau eksistē

END IF;

SET v_salt = UUID();

SET v_hash = SHA2(CONCAT(v_salt, p_password), 256);

INSERT INTO users(username, password_hash, salt) VALUES (p_username, v_hash, v_salt);

RETURN 1;

END;

//

-- 2.2 Dzēst lietotāju (loģiski)

CREATE FUNCTION delete_user(p_username VARCHAR(100))

RETURNS INT

BEGIN

DECLARE v_count INT DEFAULT 0;

SELECT COUNT(*) INTO v_count FROM users WHERE username = p_username AND active=1;

IF v_count = 0 THEN

RETURN 0; -- Lietotājs nav atrasts

END IF;

UPDATE users SET active=0 WHERE username = p_username;

RETURN 1;

END;

//

-- 2.3 Modificēt lietotāju

CREATE FUNCTION update_user(p_old_username VARCHAR(100), p_new_username VARCHAR(100), p_new_password VARCHAR(255))

RETURNS INT

BEGIN

DECLARE v_count INT DEFAULT 0;

DECLARE v_salt CHAR(36);

DECLARE v_hash CHAR(64);

-- Pārbaudām, vai jaunais lietotājvārds jau nav aizņemts

SELECT COUNT(*) INTO v_count FROM users WHERE username = p_new_username AND username<>p_old_username;

IF v_count > 0 THEN

RETURN -1; -- Jaunais username aizņemts

END IF;

-- Pārbaudām, vai vecais lietotājs eksistē

SELECT COUNT(*) INTO v_count FROM users WHERE username = p_old_username AND active=1;

IF v_count = 0 THEN

RETURN 0; -- Lietotājs nav atrasts

END IF;

SET v_salt = UUID();

SET v_hash = SHA2(CONCAT(v_salt, p_new_password), 256);

UPDATE users SET username = p_new_username, password_hash = v_hash, salt = v_salt WHERE username = p_old_username;

RETURN 1;

END;

//

-- 2.4 Pārbaudīt lietotāju un paroli

CREATE FUNCTION check_login(p_username VARCHAR(100), p_password VARCHAR(255))

RETURNS INT

BEGIN

DECLARE v_hash CHAR(64);

DECLARE v_salt CHAR(36);

DECLARE v_exists INT DEFAULT 0;

SELECT password_hash, salt INTO v_hash, v_salt

FROM users

WHERE username = p_username AND active=1;

IF v_salt IS NULL THEN

RETURN 0; -- Lietotājs nav atrasts

END IF;

IF SHA2(CONCAT(v_salt, p_password), 256) = v_hash THEN

RETURN 1; -- Parole pareiza

ELSE

RETURN 0; -- Parole nepareiza

END IF;

END;

//

-- 3.1 Pieteikties sistēmā (login)

CREATE FUNCTION login_user(p_username VARCHAR(100), p_password VARCHAR(255))

RETURNS CHAR(36)

BEGIN

DECLARE v_ok INT DEFAULT 0;

DECLARE v_userid INT;

DECLARE v_session CHAR(36);

DECLARE v_now TIMESTAMP;

SET v_ok = check_login(p_username, p_password);

IF v_ok = 0 THEN

RETURN NULL;

END IF;

SELECT id INTO v_userid FROM users WHERE username = p_username AND active=1 LIMIT 1;

SET v_session = UUID();

SET v_now = NOW();

INSERT INTO sessions(session_id, user_id, created_at, last_active, expires_at)

VALUES(v_session, v_userid, v_now, v_now, DATE_ADD(v_now, INTERVAL 2 HOUR));

RETURN v_session;

END;

//

-- 3.2 Pārbaudīt, vai esi pieteicies

CREATE FUNCTION is_logged_in(p_session CHAR(36))

RETURNS INT

BEGIN

DECLARE v_count INT DEFAULT 0;

DECLARE v_now TIMESTAMP;

SET v_now = NOW();

-- Pārbaudām, vai sesija eksistē un nav beigusies

SELECT COUNT(*) INTO v_count

FROM sessions

WHERE session_id = p_session AND expires_at > v_now;

IF v_count = 0 THEN

RETURN 0; -- Nav pieteicies vai sesija beigusies

END IF;

-- Atjaunojam pēdējo aktivitāti un pagarinām sesiju

UPDATE sessions

SET last_active = v_now, expires_at = DATE_ADD(v_now, INTERVAL 2 HOUR)

WHERE session_id = p_session;

RETURN 1;

END;

//

-- 3.3 Atteikties no sistēmas (logout)

CREATE FUNCTION logout_user(p_session CHAR(36))

RETURNS INT

BEGIN

DECLARE v_count INT DEFAULT 0;

SELECT COUNT(*) INTO v_count FROM sessions WHERE session_id = p_session;

IF v_count = 0 THEN

RETURN 0; -- Sesija nav atrasta

END IF;

DELETE FROM sessions WHERE session_id = p_session;

RETURN 1;

END;

//

DELIMITER //

CREATE FUNCTION login(p_user VARCHAR(100), p_pass VARCHAR(255))

RETURNS INT

BEGIN

DECLARE uid INT;

SELECT id INTO uid

FROM users

WHERE username = p_user

AND password = p_pass;

IF uid IS NULL THEN

RETURN 0;

END IF;

RETURN uid;

END//

DELIMITER ;