CREATE DATABASE IF NOT EXISTS school_project CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE school_project;

-- =========================
-- 1) WEB moduļa darbs
-- =========================

CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) UNIQUE NOT NULL
);

CREATE TABLE tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(120) NOT NULL,
  description TEXT,
  priority TINYINT NOT NULL DEFAULT 3,
  due_date DATE NULL,
  status ENUM('NEW','DONE') NOT NULL DEFAULT 'NEW',
  category_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
);

INSERT INTO categories(name) VALUES ('Bez kategorijas');

-- =========================
-- 2) DB projekts (LOGIN tikai ar FUNCTION)
-- =========================

CREATE TABLE sessions (
  user_id INT PRIMARY KEY,
  is_logged_in TINYINT(1) NOT NULL DEFAULT 0,
  last_action_at DATETIME NULL,
  timeout_sec INT NOT NULL DEFAULT 600,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

DROP FUNCTION IF EXISTS fn_create_user;
DROP FUNCTION IF EXISTS fn_delete_user;
DROP FUNCTION IF EXISTS fn_update_user;
DROP FUNCTION IF EXISTS fn_check_login;
DROP FUNCTION IF EXISTS fn_is_logged_in;
DROP FUNCTION IF EXISTS fn_logout;
DROP FUNCTION IF EXISTS fn_do_action;

DELIMITER $$

CREATE FUNCTION fn_create_user(p_username VARCHAR(60), p_password VARCHAR(255))
RETURNS INT
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
  DECLARE v_exists INT DEFAULT 0;
  DECLARE v_salt CHAR(16);
  DECLARE v_hash CHAR(64);
  DECLARE v_user_id INT;

  IF p_username IS NULL OR p_password IS NULL OR LENGTH(TRIM(p_username)) = 0 OR LENGTH(p_password) < 3 THEN
    RETURN 0;
  END IF;
  IF v_exists > 0 THEN
    RETURN 0;
  END IF;

  SET v_salt = SUBSTRING(REPLACE(UUID(),'-',''),1,16);
  SET v_hash = SHA2(CONCAT(v_salt, p_password), 256);
  SET v_user_id = LAST_INSERT_ID();

  INSERT INTO sessions(user_id, is_logged_in, last_action_at, timeout_sec)
  VALUES (v_user_id, 0, NULL, 600);

  RETURN 1;
END$$

CREATE FUNCTION fn_delete_user(p_username VARCHAR(60))
RETURNS INT
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
  DECLARE v_user_id INT;
  IF v_user_id IS NULL THEN
    RETURN 0;
  END IF;
  RETURN 1;
END$$

CREATE FUNCTION fn_update_user(p_old_username VARCHAR(60), p_new_username VARCHAR(60), p_new_password VARCHAR(255))
RETURNS INT
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
  DECLARE v_user_id INT;
  DECLARE v_exists INT DEFAULT 0;
  DECLARE v_salt CHAR(16);
  DECLARE v_hash CHAR(64);
  IF v_user_id IS NULL THEN
    RETURN 0;
  END IF;
    IF v_exists > 0 THEN
      RETURN 0;
    END IF;
  END IF;

  IF p_new_password IS NOT NULL AND LENGTH(p_new_password) >= 3 THEN
    SET v_salt = SUBSTRING(REPLACE(UUID(),'-',''),1,16);
    SET v_hash = SHA2(CONCAT(v_salt, p_new_password), 256);
  END IF;

  RETURN 1;
END$$

CREATE FUNCTION fn_check_login(p_username VARCHAR(60), p_password VARCHAR(255))
RETURNS INT
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
  DECLARE v_user_id INT;
  DECLARE v_salt CHAR(16);
  DECLARE v_hash CHAR(64);
  DECLARE v_ok INT DEFAULT 0;

  IF v_user_id IS NULL THEN
    RETURN 0;
  END IF;

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

  IF v_ok = 1 THEN
    UPDATE sessions SET is_logged_in = 1, last_action_at = NOW() WHERE user_id = v_user_id;
    RETURN 1;
  END IF;

  RETURN 0;
END$$

CREATE FUNCTION fn_is_logged_in(p_username VARCHAR(60))
RETURNS INT
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
  DECLARE v_user_id INT;
  DECLARE v_logged INT DEFAULT 0;
  DECLARE v_last DATETIME;
  DECLARE v_timeout INT DEFAULT 600;
  IF v_user_id IS NULL THEN
    RETURN 0;
  END IF;

  SELECT is_logged_in, last_action_at, timeout_sec
    INTO v_logged, v_last, v_timeout
  FROM sessions WHERE user_id = v_user_id LIMIT 1;

  IF v_logged = 0 THEN
    RETURN 0;
  END IF;

  IF v_last IS NULL THEN
    UPDATE sessions SET is_logged_in = 0 WHERE user_id = v_user_id;
    RETURN 0;
  END IF;

  IF TIMESTAMPDIFF(SECOND, v_last, NOW()) > v_timeout THEN
    UPDATE sessions SET is_logged_in = 0 WHERE user_id = v_user_id;
    RETURN 0;
  END IF;

  RETURN 1;
END$$

CREATE FUNCTION fn_logout(p_username VARCHAR(60))
RETURNS INT
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
  DECLARE v_user_id INT;
  IF v_user_id IS NULL THEN
    RETURN 0;
  END IF;

  UPDATE sessions SET is_logged_in = 0 WHERE user_id = v_user_id;
  RETURN 1;
END$$

CREATE FUNCTION fn_do_action(p_username VARCHAR(60))
RETURNS INT
NOT DETERMINISTIC
MODIFIES SQL DATA
BEGIN
  DECLARE v_user_id INT;

  IF fn_is_logged_in(p_username) = 0 THEN
    RETURN 0;
  END IF;
  UPDATE sessions SET last_action_at = NOW() WHERE user_id = v_user_id;

  RETURN 1;
END$$

DELIMITER ;
