-- UUIDv7 function
-- https://postgresql.verite.pro/blog/2024/07/15/uuid-v7-pure-sql.html
-- The method used to generate a UUID-v7 is to start from a UUID v4 obtained from the built-in function gen_random_uuid(), 
-- and to overwrite bits at the places of unix_ts_ms and ver with the timestamp and version number.
-- I got the implementation from Kyle Hubert’s “kjmph” gist, and simply converted it from plpgsql to sql.
CREATE OR REPLACE FUNCTION i3s_uuidv7() RETURNS UUID
AS $$
  -- Replace the first 48 bits of a uuidv4 with the current
  -- number of milliseconds since 1970-01-01 UTC
  -- and set the "ver" field to 7 by setting additional bits
  select encode(
    set_bit(
      set_bit(
        overlay(uuid_send(gen_random_uuid()) placing
	  substring(int8send((extract(epoch from clock_timestamp())*1000)::bigint) from 3)
	  from 1 for 6),
	52, 1),
      53, 1), 'hex')::uuid;
$$ LANGUAGE sql volatile;

-- Returns a UUIDv7 as TEXT.
CREATE OR REPLACE FUNCTION i3s_unique_id() RETURNS TEXT
AS $$
BEGIN
	RETURN UPPER(TRIM(CAST(i3s_uuidv7() AS TEXT)));
END;
$$
LANGUAGE plpgsql;

-- This is a user-callable function.
-- Checks password for the given USER ID.
-- Requires the 'pgcrypto' extension.
-- Example: CALL i3s_auth($1::TEXT, $2::TEXT, $3::INTEGER, $4::TEXT, $5::TEXT, $6::TEXT, $7::TEXT, $8::TEXT, $9::TEXT, $10::INTEGER, NULL, NULL)
-- Parameters:
--   userid      - text, not null
--   password    - text, not null
--   clearOthers - integer, not null - 1 = clear other sessions for the user, 2 = do not clear other sessions
--   fcmToken    - FCM token, may be null
--   apnsToken   - APNs token, may be null
--   devos       - device operating system, may be null
--   devver      - operating system version, may be null
--   devmod      - device model
--   clAddr      - client's IP address, may be null
--   clPort      - client's port number, may be null
-- Return values from fetch:
--   $row['session_id'] = Session ID
--   $row['last_login'] = Epoch of last successful login
CREATE OR REPLACE PROCEDURE i3s_auth (
	IN userid TEXT,
	IN plainpass TEXT,
	IN clearOthers INTEGER,
	IN fcmToken TEXT,
	IN apnsToken TEXT,
	IN devos TEXT,
	IN devver TEXT,
	IN devmod TEXT,
	IN clAddr TEXT,
	IN clPort INTEGER,
	OUT session_id TEXT,
	OUT last_login NUMERIC) 
AS $$
DECLARE
	foundid BIGINT := NULL;
	uuid TEXT := NULL;
	lastLogin TIMESTAMP WITH TIME ZONE := NULL;
	allowLogin BOOLEAN := NULL;
BEGIN
	userid := UPPER(TRIM(userid));

	SELECT u.allow_login INTO allowLogin FROM i3s_user u
		INNER JOIN i3s_status s ON s.status_code = u.status_code
		WHERE userid = u.user_id AND s.is_active;
	IF allowLogin IS NOT NULL AND allowLogin <> TRUE THEN
		-- Failure - log it
		INSERT INTO i3s_activity_log (category, severity, cl_addr, cl_port, descript)
			VALUES ('AUTH', 2, clAddr, clPort, 'User ' || userid || ' is not allowed to login');
		RETURN;
	END IF;

	-- Deprecating this in favour of Laravel's authentication mechanism
	-- UPDATE i3s_user SET dt_lastlogin=now() WHERE userid=user_id AND is_active=TRUE
	-- 	AND (user_pass IS NULL OR user_pass=crypt(plainpass, user_pass))
	-- 	RETURNING id, dt_lastlogin INTO foundid, lastLogin;

	UPDATE i3s_user AS u
		SET dt_lastlogin = now()
		FROM i3s_status AS s
		WHERE userid = u.user_id
		AND s.status_code = u.status_code
		AND s.is_active
		RETURNING u.id, u.dt_lastlogin INTO foundid, lastLogin;

	IF foundid IS NOT NULL THEN
		-- Authentication is successful

		-- Clear other sessions?
		IF clearOthers = 1 THEN
			UPDATE i3s_user_session SET is_active=FALSE WHERE user_id=userid;
		END IF;

		-- Add this new session
		uuid := i3s_unique_id();
		INSERT INTO i3s_user_session (user_id, cl_addr, cl_port, session_id, fcm_token, apns_token, dev_os, dev_ver, dev_mod) 
			VALUES (userid, clAddr, clPort, uuid, fcmToken, apnsToken, devos, devver, devmod);

		-- Return success to caller
		session_id := uuid;
		last_login := extract(epoch FROM lastLogin);

		-- Success - log it
		INSERT INTO i3s_activity_log (category, severity, cl_addr, cl_port, descript)
			VALUES ('AUTH', 0, clAddr, clPort, 'User ' || userid || ' logged in successfully');
	ELSE
		-- Failure - log it
		INSERT INTO i3s_activity_log (category, severity, cl_addr, cl_port, descript)
			VALUES ('AUTH', 2, clAddr, clPort, 'User ' || userid || ' failed to login');
	END IF;
END;
$$
LANGUAGE plpgsql;

-- This is a user-callable function.
-- Sets the password for the given USER ID.
-- Requires the 'pgcrypto' extension.
-- Out parameter `success` is TRUE on success, FALSE otherwise.
CREATE OR REPLACE PROCEDURE i3s_setpass(IN userid TEXT, IN oldplain TEXT, IN newplain TEXT, OUT success BOOLEAN) 
AS $$
DECLARE
	foundid BIGINT := NULL;

BEGIN
	userid := UPPER(TRIM(userid));

	-- Using Blowfish for salt, max password length=72
	UPDATE i3s_user SET user_pass = crypt(newplain, gen_salt('bf')) WHERE userid=user_id AND 
		(user_pass IS NULL OR user_pass = crypt(oldplain, user_pass))
		RETURNING id INTO foundid;

	IF id IS NULL THEN
		success := FALSE;
	ELSE
		success := TRUE;
	END IF;
END;
$$
LANGUAGE plpgsql;

-- This is a user-callable function.
-- Call this function with SESSION ID when logging out.
-- Returns TRUE on success, FALSE otherwise.
CREATE OR REPLACE FUNCTION i3s_logout(IN sessionid TEXT) 
RETURNS BOOLEAN 
AS $$
DECLARE
	ok BOOLEAN := FALSE;
	userid TEXT := NULL;

BEGIN
	sessionid := UPPER(TRIM(sessionid));

	UPDATE i3s_user_session SET is_active=FALSE, dt_logout=now() WHERE session_id=sessionid
		RETURNING user_id INTO userid;

	IF userid IS NOT NULL THEN
		ok = TRUE;
	END IF;
	
	RETURN ok;
END;
$$
LANGUAGE plpgsql;
