-- AI assistant chat sessions (server side store, one row per session)

	-- Get one session with full messages

	CREATE PROCEDURE get(
		IN uuid CHAR,
		IN site_id INT,
		IN admin_id INT,
		OUT fetch_row
	)
	BEGIN
		-- ai_session
		SELECT *
			FROM ai_session as _ -- (underscore) _ means that data will be kept in main array
		WHERE uuid = :uuid AND site_id = :site_id AND admin_id = :admin_id LIMIT 1;
	END

	-- Add new session

	CREATE PROCEDURE add(
		IN session ARRAY,
		OUT insert_id
	)
	BEGIN

		-- allow only table fields and set defaults for missing values
		:session  = @FILTER(:session, ai_session)

		INSERT INTO ai_session

			( @KEYS(:session) )

		VALUES ( :session );

	END

	-- Edit session (scoped by uuid + site + admin)

	CREATE PROCEDURE edit(
		IN session ARRAY,
		IN uuid CHAR,
		IN site_id INT,
		IN admin_id INT,
		OUT affected_rows
	)
	BEGIN
		-- allow only table fields and set defaults for missing values
		@FILTER(:session, ai_session)

		UPDATE ai_session

			SET @LIST(:session)

		WHERE uuid = :uuid AND site_id = :site_id AND admin_id = :admin_id
	END

	-- List sessions, metadata only (no messages payload), newest first

	CREATE PROCEDURE getAll(

		-- variables
		IN  site_id INT,
		IN  admin_id INT,

		-- pagination
		IN  start INT,
		IN  limit INT,

		-- return array of sessions for sessions query
		OUT fetch_all,
		-- return sessions count for count query
		OUT fetch_one
	)
	BEGIN

		SELECT session_id, uuid, title, message_count, created_at, updated_at
			FROM ai_session
		WHERE site_id = :site_id AND admin_id = :admin_id

		ORDER BY updated_at DESC

			@SQL_LIMIT(:start, :limit);

		SELECT count(*) FROM (

			@SQL_COUNT(ai_session.session_id, ai_session) -- this takes previous query removes limit and replaces select columns with parameter session_id

		) as count;


	END


	-- Delete sessions

	CREATE PROCEDURE delete(
		IN  uuid ARRAY,
		IN  site_id INT,
		IN  admin_id INT,
		OUT affected_rows
	)
	BEGIN

		DELETE FROM ai_session WHERE uuid IN (:uuid) AND site_id = :site_id AND admin_id = :admin_id

	END
