No OneTemporary
Actions

Authored By

Unknown

Size

52 KB

Referenced Files

None

Subscribers

None

View Options

	diff --git a/Dockerfile b/Dockerfile
	index 44bcebd..6b00e07 100644
	--- a/Dockerfile
	+++ b/Dockerfile
	@@ -1,65 +1,68 @@
	# mattata v2.0 - Multi-stage Docker build
	# Builder stage: compile Lua and dependencies
	FROM ubuntu:22.04 AS builder

	ENV DEBIAN_FRONTEND=noninteractive

	RUN apt-get update && apt-get install -y \
	build-essential \
	wget \
	unzip \
	libreadline-dev \
	libssl-dev \
	git \
	&& rm -rf /var/lib/apt/lists/*

	# Install Lua 5.3
	RUN cd /tmp && \
	wget -q https://www.lua.org/ftp/lua-5.3.6.tar.gz && \
	tar xzf lua-5.3.6.tar.gz && \
	cd lua-5.3.6 && \
	make linux && \
	make install && \
	cd / && rm -rf /tmp/lua-5.3.6*

	# Install LuaRocks
	RUN cd /tmp && \
	wget -q https://luarocks.org/releases/luarocks-3.9.2.tar.gz && \
	tar xzf luarocks-3.9.2.tar.gz && \
	cd luarocks-3.9.2 && \
	./configure --with-lua=/usr/local && \
	make && make install && \
	cd / && rm -rf /tmp/luarocks-3.9.2*

	-# Install Lua dependencies
	-RUN luarocks install telegram-bot-lua && \
	- luarocks install pgmoon && \
	+# Install telegram-bot-lua v3.0 from source (v3.0 not yet published to luarocks)
	+RUN cd /tmp && \
	+ git clone --depth 1 https://github.com/wrxck/telegram-bot-lua.git && \
	+ cd telegram-bot-lua && \
	+ luarocks make telegram-bot-lua-3.0-0.rockspec && \
	+ cd / && rm -rf /tmp/telegram-bot-lua
	+
	+# Install remaining Lua dependencies
	+RUN luarocks install pgmoon && \
	luarocks install redis-lua && \
	- luarocks install dkjson && \
	- luarocks install luasocket && \
	- luarocks install luasec && \
	- luarocks install luautf8
	+ luarocks install luaossl

	# Runtime stage
	FROM ubuntu:22.04

	ENV DEBIAN_FRONTEND=noninteractive

	RUN apt-get update && apt-get install -y \
	libreadline8 \
	libssl3 \
	ca-certificates \
	&& rm -rf /var/lib/apt/lists/*

	# Copy Lua installation from builder
	COPY --from=builder /usr/local /usr/local

	# Set up app directory
	WORKDIR /app
	COPY . /app

	# Run as non-root
	RUN useradd -m mattata
	USER mattata

	CMD ["lua", "main.lua"]
	diff --git a/src/core/database.lua b/src/core/database.lua
	index 12e264e..d7fc9c8 100644
	--- a/src/core/database.lua
	+++ b/src/core/database.lua
	@@ -1,347 +1,369 @@
	--[[
	mattata v2.1 - PostgreSQL Database Module
	Uses pgmoon for async-compatible PostgreSQL connections.
	Implements connection pooling with copas semaphore guards,
	automatic reconnection, and transaction helpers.
	]]

	local database = {}

	local pgmoon = require('pgmoon')
	local config = require('src.core.config')
	local logger = require('src.core.logger')
	local copas_sem = require('copas.semaphore')

	local pool = {}
	local pool_size = 10
	local pool_timeout = 30000
	local pool_semaphore = nil
	local db_config = nil

	-- Initialise pool configuration
	local function get_config()
	if not db_config then
	db_config = config.database()
	end
	return db_config
	end

	-- Create a new pgmoon connection
	local function create_connection()
	local cfg = get_config()
	local pg = pgmoon.new({
	host = cfg.host,
	port = cfg.port,
	database = cfg.database,
	user = cfg.user,
	password = cfg.password
	})
	local ok, err = pg:connect()
	if not ok then
	return nil, err
	end
	pg:settimeout(pool_timeout)
	return pg
	end

	function database.connect()
	local cfg = get_config()
	pool_size = config.get_number('DATABASE_POOL_SIZE', 10)
	pool_timeout = config.get_number('DATABASE_TIMEOUT', 30000)

	-- Create initial connection to validate credentials
	local pg, err = create_connection()
	if not pg then
	logger.error('Failed to connect to PostgreSQL: %s', tostring(err))
	return false, err
	end
	table.insert(pool, pg)

	-- Create semaphore to guard concurrent pool access
	-- max = pool_size, start = pool_size (all permits available), timeout = 30s
	pool_semaphore = copas_sem.new(pool_size, pool_size, 30)

	logger.info('Connected to PostgreSQL at %s:%d/%s (pool size: %d)', cfg.host, cfg.port, cfg.database, pool_size)
	return true
	end

	-- Acquire a connection from the pool
	function database.acquire()
	-- Take a semaphore permit (blocks coroutine if pool exhausted, 30s timeout)
	if pool_semaphore then
	local ok, err = pool_semaphore:take(1, 30)
	if not ok then
	logger.error('Failed to acquire pool permit: %s', tostring(err))
	return nil, 'Pool exhausted (semaphore timeout)'
	end
	end
	if #pool > 0 then
	return table.remove(pool)
	end
	-- Pool exhausted — create a new connection
	local pg, err = create_connection()
	if not pg then
	logger.error('Failed to create new connection: %s', tostring(err))
	-- Return the permit since we failed to use it
	if pool_semaphore then pool_semaphore:give(1) end
	return nil, err
	end
	return pg
	end

	-- Release a connection back to the pool
	function database.release(pg)
	if not pg then return end
	if #pool < pool_size then
	table.insert(pool, pg)
	else
	pcall(function() pg:disconnect() end)
	end
	-- Return the semaphore permit
	if pool_semaphore then pool_semaphore:give(1) end
	end

	-- Execute a raw SQL query with automatic connection management
	function database.query(sql, ...)
	local pg, err = database.acquire()
	if not pg then
	logger.error('Database not connected')
	return nil, 'Database not connected'
	end
	local result, query_err, _, _ = pg:query(sql)
	if not result then
	-- Check for connection loss and attempt reconnect
	if query_err and (query_err:match('closed') or query_err:match('broken') or query_err:match('timeout')) then
	logger.warn('Connection lost, attempting reconnect...')
	pcall(function() pg:disconnect() end)
	-- Release the dead connection's permit before reconnect
	if pool_semaphore then pool_semaphore:give(1) end
	pg, err = create_connection()
	if pg then
	-- Re-acquire a permit for the new connection
	if pool_semaphore then
	local ok, sem_err = pool_semaphore:take(1, 30)
	if not ok then
	pcall(function() pg:disconnect() end)
	logger.error('Reconnect semaphore acquire failed: %s', tostring(sem_err))
	return nil, 'Pool exhausted during reconnect'
	end
	end
	result, query_err = pg:query(sql)
	if result then
	database.release(pg)
	return result
	end
	database.release(pg)
	end
	logger.error('Reconnect failed for query: %s', tostring(query_err or err))
	return nil, query_err or err
	end
	logger.error('Query failed: %s\nSQL: %s', tostring(query_err), sql)
	database.release(pg)
	return nil, query_err
	end
	database.release(pg)
	return result
	end

	-- Execute a parameterized query (manually escape values)
	function database.execute(sql, params)
	local pg, _ = database.acquire()
	if not pg then
	return nil, 'Database not connected'
	end
	if params then
	local escaped = {}
	for i, v in ipairs(params) do
	if v == nil then
	escaped[i] = 'NULL'
	elseif type(v) == 'number' then
	escaped[i] = tostring(v)
	elseif type(v) == 'boolean' then
	escaped[i] = v and 'TRUE' or 'FALSE'
	else
	escaped[i] = pg:escape_literal(tostring(v))
	end
	end
	-- Replace $1, $2, etc. with escaped values
	sql = sql:gsub('%$(%d+)', function(n)
	return escaped[tonumber(n)] or '$' .. n
	end)
	end
	local result, query_err = pg:query(sql)
	if not result then
	-- Attempt reconnect on connection failure
	if query_err and (query_err:match('closed') or query_err:match('broken') or query_err:match('timeout')) then
	logger.warn('Connection lost during execute, reconnecting...')
	pcall(function() pg:disconnect() end)
	-- Release the dead connection's permit before reconnect
	if pool_semaphore then pool_semaphore:give(1) end
	local new_pg
	new_pg, _ = create_connection()
	if new_pg then
	-- Re-acquire a permit for the new connection
	if pool_semaphore then
	local ok, sem_err = pool_semaphore:take(1, 30)
	if not ok then
	pcall(function() new_pg:disconnect() end)
	logger.error('Reconnect semaphore acquire failed: %s', tostring(sem_err))
	return nil, 'Pool exhausted during reconnect'
	end
	end
	result, query_err = new_pg:query(sql)
	if result then
	database.release(new_pg)
	return result
	end
	database.release(new_pg)
	end
	else
	database.release(pg)
	end
	logger.error('Query failed: %s\nSQL: %s', tostring(query_err), sql)
	return nil, query_err
	end
	database.release(pg)
	return result
	end

	-- Run a function inside a transaction (BEGIN / COMMIT / ROLLBACK)
	function database.transaction(fn)
	local pg, _ = database.acquire()
	if not pg then
	return nil, 'Database not connected'
	end
	local ok, begin_err = pg:query('BEGIN')
	if not ok then
	database.release(pg)
	return nil, begin_err
	end
	-- Build a scoped query function for this connection
	local function scoped_query(sql)
	return pg:query(sql)
	end
	local function scoped_execute(sql, params)
	if params then
	local escaped = {}
	for i, v in ipairs(params) do
	if v == nil then
	escaped[i] = 'NULL'
	elseif type(v) == 'number' then
	escaped[i] = tostring(v)
	elseif type(v) == 'boolean' then
	escaped[i] = v and 'TRUE' or 'FALSE'
	else
	escaped[i] = pg:escape_literal(tostring(v))
	end
	end
	sql = sql:gsub('%$(%d+)', function(n)
	return escaped[tonumber(n)] or '$' .. n
	end)
	end
	return pg:query(sql)
	end
	local success, result = pcall(fn, scoped_query, scoped_execute)
	if success then
	pg:query('COMMIT')
	database.release(pg)
	return result
	else
	pg:query('ROLLBACK')
	database.release(pg)
	logger.error('Transaction failed: %s', tostring(result))
	return nil, result
	end
	end

	-- Convenience: insert and return the row
	function database.insert(table_name, data)
	local columns = {}
	local values = {}
	local params = {}
	local i = 1
	for k, v in pairs(data) do
	table.insert(columns, k)
	table.insert(values, '$' .. i)
	table.insert(params, v)
	i = i + 1
	end
	local sql = string.format(
	'INSERT INTO %s (%s) VALUES (%s) RETURNING *',
	table_name,
	table.concat(columns, ', '),
	table.concat(values, ', ')
	)
	return database.execute(sql, params)
	end

	-- Convenience: upsert (INSERT ON CONFLICT UPDATE)
	function database.upsert(table_name, data, conflict_keys, update_keys)
	local columns = {}
	local values = {}
	local params = {}
	local i = 1
	for k, v in pairs(data) do
	table.insert(columns, k)
	table.insert(values, '$' .. i)
	table.insert(params, v)
	i = i + 1
	end
	local updates = {}
	for _, k in ipairs(update_keys) do
	table.insert(updates, k .. ' = EXCLUDED.' .. k)
	end
	local sql = string.format(
	'INSERT INTO %s (%s) VALUES (%s) ON CONFLICT (%s) DO UPDATE SET %s RETURNING *',
	table_name,
	table.concat(columns, ', '),
	table.concat(values, ', '),
	table.concat(conflict_keys, ', '),
	table.concat(updates, ', ')
	)
	return database.execute(sql, params)
	end

	--- call a stored procedure: SELECT * FROM func_name($1, $2, ...)
	+-- call a stored procedure: SELECT * FROM func_name(arg1, arg2, ...)
	-- func_name is validated to contain only safe characters (alphanumeric + underscore)
	-function database.call(func_name, params)
	+-- nil values are inlined as NULL; non-nil values are escaped inline
	+function database.call(func_name, params, nparams)
	if not func_name:match('^[%w_]+$') then
	logger.error('Invalid stored procedure name: %s', func_name)
	return nil, 'Invalid stored procedure name'
	end
	params = params or {}
	- local placeholders = {}
	- for i = 1, #params do
	- placeholders[i] = '$' .. i
	+ nparams = nparams or params.n or #params
	+ local pg, err = database.acquire()
	+ if not pg then
	+ return nil, 'Database not connected'
	+ end
	+ local args = {}
	+ for i = 1, nparams do
	+ local v = params[i]
	+ if v == nil then
	+ args[i] = 'NULL'
	+ elseif type(v) == 'number' then
	+ args[i] = tostring(v)
	+ elseif type(v) == 'boolean' then
	+ args[i] = v and 'TRUE' or 'FALSE'
	+ else
	+ args[i] = pg:escape_literal(tostring(v))
	+ end
	end
	local sql = string.format(
	'SELECT * FROM %s(%s)',
	func_name,
	- table.concat(placeholders, ', ')
	+ table.concat(args, ', ')
	)
	- return database.execute(sql, params)
	+ local result, query_err = pg:query(sql)
	+ if not result then
	+ logger.error('Query failed: %s\nSQL: %s', tostring(query_err), sql)
	+ database.release(pg)
	+ return nil, query_err
	+ end
	+ database.release(pg)
	+ return result
	end

	-- get the raw pgmoon connection for advanced usage
	function database.connection()
	return database.acquire()
	end

	-- Get current pool stats
	function database.pool_stats()
	return {
	available = #pool,
	max_size = pool_size
	}
	end

	function database.disconnect()
	for _, pg in ipairs(pool) do
	pcall(function() pg:disconnect() end)
	end
	pool = {}
	pool_semaphore = nil
	logger.info('Disconnected from PostgreSQL (pool drained)')
	end

	return database
	diff --git a/src/db/init.lua b/src/db/init.lua
	index 9d7b368..a65c6d7 100644
	--- a/src/db/init.lua
	+++ b/src/db/init.lua
	@@ -1,86 +1,108 @@
	--[[
	mattata v2.0 - Migration Runner
	Runs pending SQL migrations in order, wrapped in transactions.
	Supports migrations from src/db/migrations/ AND plugin.migration fields.
	]]

	local migrations = {}

	local logger = require('src.core.logger')

	local migration_files = {
	{ name = '001_initial_schema', path = 'src.db.migrations.001_initial_schema' },
	{ name = '002_federation_tables', path = 'src.db.migrations.002_federation_tables' },
	{ name = '003_statistics_tables', path = 'src.db.migrations.003_statistics_tables' },
	{ name = '004_performance_indexes', path = 'src.db.migrations.004_performance_indexes' },
	{ name = '005_stored_procedures', path = 'src.db.migrations.005_stored_procedures' }
	}

	function migrations.run(db)
	-- Create migrations tracking table
	db.query([[
	CREATE TABLE IF NOT EXISTS schema_migrations (
	name VARCHAR(255) PRIMARY KEY,
	applied_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
	)
	]])

	-- Run each migration if not already applied
	for _, mig in ipairs(migration_files) do
	local applied = db.execute(
	'SELECT 1 FROM schema_migrations WHERE name = $1',
	{ mig.name }
	)
	if not applied or #applied == 0 then
	logger.info('Running migration: %s', mig.name)
	local ok, mod = pcall(require, mig.path)
	if ok and type(mod) == 'table' and mod.up then
	-- Wrap migration in a transaction
	local begin_ok, begin_err = db.query('BEGIN')
	if not begin_ok and begin_err then
	logger.error('Failed to begin transaction for migration %s: %s', mig.name, tostring(begin_err))
	os.exit(1)
	end

	local sql = mod.up()
	local migration_ok = true
	local migration_err = nil
	- -- Split on semicolons and execute each statement
	- for statement in sql:gmatch('[^;]+') do
	- statement = statement:match('^%s(.-)%s$')
	- if statement ~= '' then
	- local result, err = db.query(statement)
	- if not result and err then
	- migration_ok = false
	- migration_err = err
	- break
	+ -- Split on semicolons, respecting $$-delimited blocks
	+ local statements = {}
	+ local current = ''
	+ local in_dollar = false
	+ local i = 1
	+ while i <= #sql do
	+ if sql:sub(i, i + 1) == '$$' then
	+ in_dollar = not in_dollar
	+ current = current .. '$$'
	+ i = i + 2
	+ elseif sql:sub(i, i) == ';' and not in_dollar then
	+ local trimmed = current:match('^%s(.-)%s$')
	+ if trimmed ~= '' then
	+ statements[#statements + 1] = trimmed
	end
	+ current = ''
	+ i = i + 1
	+ else
	+ current = current .. sql:sub(i, i)
	+ i = i + 1
	+ end
	+ end
	+ local trimmed = current:match('^%s(.-)%s$')
	+ if trimmed ~= '' then
	+ statements[#statements + 1] = trimmed
	+ end
	+ for _, statement in ipairs(statements) do
	+ local result, err = db.query(statement)
	+ if not result and err then
	+ migration_ok = false
	+ migration_err = err
	+ break
	end
	end

	if not migration_ok then
	logger.error('Migration %s failed: %s — rolling back', mig.name, tostring(migration_err))
	db.query('ROLLBACK')
	os.exit(1)
	end

	-- Record migration as applied using parameterized query
	db.execute(
	'INSERT INTO schema_migrations (name) VALUES ($1)',
	{ mig.name }
	)
	db.query('COMMIT')
	logger.info('Migration %s applied successfully', mig.name)
	else
	logger.error('Failed to load migration %s: %s', mig.name, tostring(mod))
	os.exit(1)
	end
	else
	logger.debug('Migration %s already applied', mig.name)
	end
	end

	logger.info('All migrations up to date')
	end

	return migrations
	diff --git a/src/db/migrations/005_stored_procedures.lua b/src/db/migrations/005_stored_procedures.lua
	index 6852f27..b14023d 100644
	--- a/src/db/migrations/005_stored_procedures.lua
	+++ b/src/db/migrations/005_stored_procedures.lua
	@@ -1,918 +1,918 @@
	--[[
	Migration 005 - Stored Procedures
	Creates PostgreSQL functions for all database operations.
	All user-facing queries go through typed stored procedures to prevent SQL injection.
	Parameters are strongly typed (BIGINT, TEXT, UUID, etc.) providing server-side validation.
	]]

	local migration = {}

	function migration.up()
	return [[

	-- ============================================================
	-- USER / CHAT MANAGEMENT
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_upsert_user(
	p_user_id BIGINT,
	p_username TEXT,
	p_first_name TEXT,
	p_last_name TEXT,
	p_language_code TEXT,
	p_is_bot BOOLEAN,
	p_last_seen TIMESTAMP WITH TIME ZONE
	) RETURNS void AS $$
	INSERT INTO users (user_id, username, first_name, last_name, language_code, is_bot, last_seen)
	VALUES (p_user_id, p_username, p_first_name, p_last_name, p_language_code, p_is_bot, p_last_seen)
	ON CONFLICT (user_id) DO UPDATE SET
	username = EXCLUDED.username,
	first_name = EXCLUDED.first_name,
	last_name = EXCLUDED.last_name,
	language_code = EXCLUDED.language_code,
	last_seen = EXCLUDED.last_seen;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_upsert_chat(
	p_chat_id BIGINT,
	p_title TEXT,
	p_chat_type TEXT,
	p_username TEXT
	) RETURNS void AS $$
	INSERT INTO chats (chat_id, title, chat_type, username)
	VALUES (p_chat_id, p_title, p_chat_type, p_username)
	ON CONFLICT (chat_id) DO UPDATE SET
	title = EXCLUDED.title,
	chat_type = EXCLUDED.chat_type,
	username = EXCLUDED.username;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_upsert_chat_member(
	p_chat_id BIGINT,
	p_user_id BIGINT,
	p_last_seen TIMESTAMP WITH TIME ZONE
	) RETURNS void AS $$
	INSERT INTO chat_members (chat_id, user_id, last_seen)
	VALUES (p_chat_id, p_user_id, p_last_seen)
	ON CONFLICT (chat_id, user_id) DO UPDATE SET
	last_seen = EXCLUDED.last_seen;
	$$ LANGUAGE sql;

	-- ============================================================
	-- PERMISSIONS
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_check_group_moderator(
	p_chat_id BIGINT,
	p_user_id BIGINT
	) RETURNS TABLE(exists_flag INTEGER) AS $$
	SELECT 1 FROM chat_members
	WHERE chat_id = p_chat_id AND user_id = p_user_id AND role = 'moderator';
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_check_trusted_user(
	p_chat_id BIGINT,
	p_user_id BIGINT
	) RETURNS TABLE(exists_flag INTEGER) AS $$
	SELECT 1 FROM chat_members
	WHERE chat_id = p_chat_id AND user_id = p_user_id AND role = 'trusted';
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_moderators(
	p_chat_id BIGINT
	) RETURNS TABLE(user_id BIGINT) AS $$
	SELECT cm.user_id FROM chat_members cm
	WHERE cm.chat_id = p_chat_id AND cm.role = 'moderator';
	$$ LANGUAGE sql;

	-- ============================================================
	-- CHAT SETTINGS
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_get_chat_setting(
	p_chat_id BIGINT,
	p_key TEXT
	) RETURNS TABLE(value TEXT) AS $$
	SELECT cs.value FROM chat_settings cs
	WHERE cs.chat_id = p_chat_id AND cs.key = p_key;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_upsert_chat_setting(
	p_chat_id BIGINT,
	p_key TEXT,
	p_value TEXT
	) RETURNS void AS $$
	INSERT INTO chat_settings (chat_id, key, value)
	VALUES (p_chat_id, p_key, p_value)
	ON CONFLICT (chat_id, key) DO UPDATE SET
	value = EXCLUDED.value;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_disable_chat_setting(
	p_chat_id BIGINT,
	p_key TEXT
	) RETURNS void AS $$
	UPDATE chat_settings SET value = 'false'
	WHERE chat_id = p_chat_id AND key = p_key;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_delete_chat_setting(
	p_chat_id BIGINT,
	p_key TEXT
	) RETURNS void AS $$
	DELETE FROM chat_settings
	WHERE chat_id = p_chat_id AND key = p_key;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_chat_settings_like(
	p_chat_id BIGINT,
	p_key_pattern TEXT
	) RETURNS TABLE(key TEXT, value TEXT) AS $$
	SELECT cs.key, cs.value FROM chat_settings cs
	WHERE cs.chat_id = p_chat_id AND cs.key LIKE p_key_pattern;
	$$ LANGUAGE sql;

	-- ============================================================
	-- ROLE MANAGEMENT
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_set_member_role(
	p_chat_id BIGINT,
	p_user_id BIGINT,
	p_role TEXT
	) RETURNS void AS $$
	INSERT INTO chat_members (chat_id, user_id, role)
	VALUES (p_chat_id, p_user_id, p_role)
	ON CONFLICT (chat_id, user_id) DO UPDATE SET
	role = EXCLUDED.role;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_reset_member_role(
	p_chat_id BIGINT,
	p_user_id BIGINT
	) RETURNS void AS $$
	UPDATE chat_members SET role = 'member'
	WHERE chat_id = p_chat_id AND user_id = p_user_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_remove_allowlisted(
	p_chat_id BIGINT,
	p_user_id BIGINT
	) RETURNS void AS $$
	UPDATE chat_members SET role = 'member'
	WHERE chat_id = p_chat_id AND user_id = p_user_id AND role = 'allowlisted';
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_allowlisted_users(
	p_chat_id BIGINT
	) RETURNS TABLE(user_id BIGINT) AS $$
	SELECT cm.user_id FROM chat_members cm
	WHERE cm.chat_id = p_chat_id AND cm.role = 'allowlisted';
	$$ LANGUAGE sql;

	-- ============================================================
	-- BANS / WARNINGS / ADMIN ACTIONS
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_insert_ban(
	p_chat_id BIGINT,
	p_user_id BIGINT,
	p_banned_by BIGINT,
	p_reason TEXT
	) RETURNS void AS $$
	INSERT INTO bans (chat_id, user_id, banned_by, reason)
	VALUES (p_chat_id, p_user_id, p_banned_by, p_reason);
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_insert_tempban(
	p_chat_id BIGINT,
	p_user_id BIGINT,
	p_banned_by BIGINT,
	p_expires_at TIMESTAMP WITH TIME ZONE
	) RETURNS void AS $$
	INSERT INTO bans (chat_id, user_id, banned_by, expires_at)
	VALUES (p_chat_id, p_user_id, p_banned_by, p_expires_at);
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_insert_warning(
	p_chat_id BIGINT,
	p_user_id BIGINT,
	p_warned_by BIGINT,
	p_reason TEXT
	) RETURNS void AS $$
	INSERT INTO warnings (chat_id, user_id, warned_by, reason)
	VALUES (p_chat_id, p_user_id, p_warned_by, p_reason);
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_log_admin_action(
	p_chat_id BIGINT,
	p_admin_id BIGINT,
	p_target_id BIGINT,
	p_action TEXT,
	p_reason TEXT
	) RETURNS void AS $$
	INSERT INTO admin_actions (chat_id, admin_id, target_id, action, reason)
	VALUES (p_chat_id, p_admin_id, p_target_id, p_action, p_reason);
	$$ LANGUAGE sql;

	-- ============================================================
	-- BLOCKLIST
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_get_blocklist(
	p_chat_id BIGINT
	) RETURNS TABLE(user_id BIGINT, reason TEXT, created_at TIMESTAMP WITH TIME ZONE) AS $$
	SELECT gb.user_id, gb.reason, gb.created_at FROM group_blocklist gb
	WHERE gb.chat_id = p_chat_id ORDER BY gb.created_at DESC;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_upsert_blocklist_entry(
	p_chat_id BIGINT,
	p_user_id BIGINT,
	p_reason TEXT
	) RETURNS void AS $$
	INSERT INTO group_blocklist (chat_id, user_id, reason)
	VALUES (p_chat_id, p_user_id, p_reason)
	ON CONFLICT (chat_id, user_id) DO UPDATE SET
	reason = EXCLUDED.reason;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_delete_blocklist_entry(
	p_chat_id BIGINT,
	p_user_id BIGINT
	) RETURNS void AS $$
	DELETE FROM group_blocklist
	WHERE chat_id = p_chat_id AND user_id = p_user_id;
	$$ LANGUAGE sql;

	-- ============================================================
	-- FILTERS
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_get_filter(
	p_chat_id BIGINT,
	p_pattern TEXT
	) RETURNS TABLE(id INTEGER) AS $$
	SELECT f.id FROM filters f
	WHERE f.chat_id = p_chat_id AND f.pattern = p_pattern;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_filters(
	p_chat_id BIGINT
	) RETURNS TABLE(pattern TEXT, action VARCHAR(20)) AS $$
	SELECT f.pattern, f.action FROM filters f
	WHERE f.chat_id = p_chat_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_filters_ordered(
	p_chat_id BIGINT
	) RETURNS TABLE(id INTEGER, pattern TEXT, action VARCHAR(20), created_at TIMESTAMP WITH TIME ZONE) AS $$
	SELECT f.id, f.pattern, f.action, f.created_at FROM filters f
	WHERE f.chat_id = p_chat_id ORDER BY f.created_at;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_update_filter_action(
	p_action TEXT,
	p_chat_id BIGINT,
	p_pattern TEXT
	) RETURNS void AS $$
	UPDATE filters SET action = p_action
	WHERE chat_id = p_chat_id AND pattern = p_pattern;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_insert_filter(
	p_chat_id BIGINT,
	p_pattern TEXT,
	p_action TEXT,
	p_created_by BIGINT
	) RETURNS void AS $$
	INSERT INTO filters (chat_id, pattern, action, created_by)
	VALUES (p_chat_id, p_pattern, p_action, p_created_by);
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_delete_filter_by_pattern(
	p_chat_id BIGINT,
	p_pattern TEXT
	) RETURNS BIGINT AS $$
	WITH deleted AS (
	DELETE FROM filters
	WHERE chat_id = p_chat_id AND pattern = p_pattern
	RETURNING id
	)
	SELECT COUNT(*) FROM deleted;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_delete_filter_by_id(
	p_id INTEGER
	) RETURNS void AS $$
	DELETE FROM filters WHERE id = p_id;
	$$ LANGUAGE sql;

	-- ============================================================
	-- TRIGGERS (auto-response)
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_get_triggers(
	p_chat_id BIGINT
	) RETURNS TABLE(pattern TEXT, response TEXT, is_media BOOLEAN, file_id TEXT) AS $$
	SELECT t.pattern, t.response, t.is_media, t.file_id FROM triggers t
	WHERE t.chat_id = p_chat_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_triggers_full(
	p_chat_id BIGINT
	) RETURNS TABLE(id INTEGER, pattern TEXT, response TEXT, created_by BIGINT, created_at TIMESTAMP WITH TIME ZONE) AS $$
	SELECT t.id, t.pattern, t.response, t.created_by, t.created_at FROM triggers t
	WHERE t.chat_id = p_chat_id ORDER BY t.created_at;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_triggers_ordered(
	p_chat_id BIGINT
	) RETURNS TABLE(id INTEGER, pattern TEXT) AS $$
	SELECT t.id, t.pattern FROM triggers t
	WHERE t.chat_id = p_chat_id ORDER BY t.created_at;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_check_trigger_exists(
	p_chat_id BIGINT,
	p_pattern TEXT
	) RETURNS TABLE(id INTEGER) AS $$
	SELECT t.id FROM triggers t
	WHERE t.chat_id = p_chat_id AND t.pattern = p_pattern;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_update_trigger_response(
	p_response TEXT,
	p_chat_id BIGINT,
	p_pattern TEXT
	) RETURNS void AS $$
	UPDATE triggers SET response = p_response
	WHERE chat_id = p_chat_id AND pattern = p_pattern;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_insert_trigger(
	p_chat_id BIGINT,
	p_pattern TEXT,
	p_response TEXT,
	p_created_by BIGINT
	) RETURNS void AS $$
	INSERT INTO triggers (chat_id, pattern, response, created_by)
	VALUES (p_chat_id, p_pattern, p_response, p_created_by);
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_delete_trigger_by_id(
	p_id INTEGER
	) RETURNS void AS $$
	DELETE FROM triggers WHERE id = p_id;
	$$ LANGUAGE sql;

	-- ============================================================
	-- WELCOME MESSAGES
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_get_welcome_message(
	p_chat_id BIGINT
	) RETURNS TABLE(message TEXT) AS $$
	SELECT wm.message FROM welcome_messages wm
	WHERE wm.chat_id = p_chat_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_upsert_welcome_message(
	p_chat_id BIGINT,
	p_message TEXT
	) RETURNS void AS $$
	INSERT INTO welcome_messages (chat_id, message)
	VALUES (p_chat_id, p_message)
	ON CONFLICT (chat_id) DO UPDATE SET
	message = EXCLUDED.message;
	$$ LANGUAGE sql;

	-- ============================================================
	-- RULES
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_get_rules(
	p_chat_id BIGINT
	) RETURNS TABLE(rules_text TEXT) AS $$
	SELECT r.rules_text FROM rules r
	WHERE r.chat_id = p_chat_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_upsert_rules(
	p_chat_id BIGINT,
	p_rules_text TEXT
	) RETURNS void AS $$
	INSERT INTO rules (chat_id, rules_text)
	VALUES (p_chat_id, p_rules_text)
	ON CONFLICT (chat_id) DO UPDATE SET
	rules_text = EXCLUDED.rules_text,
	updated_at = NOW();
	$$ LANGUAGE sql;

	-- ============================================================
	-- ALLOWED LINKS
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_get_allowed_links(
	p_chat_id BIGINT
	) RETURNS TABLE(link VARCHAR(255)) AS $$
	SELECT al.link FROM allowed_links al
	WHERE al.chat_id = p_chat_id ORDER BY al.link;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_check_allowed_link(
	p_chat_id BIGINT,
	p_link TEXT
	) RETURNS TABLE(exists_flag INTEGER) AS $$
	SELECT 1 FROM allowed_links
	WHERE chat_id = p_chat_id AND link = p_link;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_insert_allowed_link(
	p_chat_id BIGINT,
	p_link TEXT
	) RETURNS void AS $$
	INSERT INTO allowed_links (chat_id, link)
	VALUES (p_chat_id, p_link);
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_delete_allowed_link(
	p_chat_id BIGINT,
	p_link TEXT
	) RETURNS void AS $$
	DELETE FROM allowed_links
	WHERE chat_id = p_chat_id AND link = p_link;
	$$ LANGUAGE sql;

	-- ============================================================
	-- NICKNAME
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_get_nickname(
	p_user_id BIGINT
	) RETURNS TABLE(nickname VARCHAR(128)) AS $$
	SELECT u.nickname FROM users u
	WHERE u.user_id = p_user_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_set_nickname(
	p_user_id BIGINT,
	p_nickname TEXT
	) RETURNS void AS $$
	UPDATE users SET nickname = p_nickname
	WHERE user_id = p_user_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_clear_nickname(
	p_user_id BIGINT
	) RETURNS void AS $$
	UPDATE users SET nickname = NULL
	WHERE user_id = p_user_id;
	$$ LANGUAGE sql;

	-- ============================================================
	-- USER LOCATIONS
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_get_user_location(
	p_user_id BIGINT
	) RETURNS TABLE(latitude DOUBLE PRECISION, longitude DOUBLE PRECISION, address TEXT) AS $$
	SELECT ul.latitude, ul.longitude, ul.address FROM user_locations ul
	WHERE ul.user_id = p_user_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_upsert_user_location(
	p_user_id BIGINT,
	p_latitude DOUBLE PRECISION,
	p_longitude DOUBLE PRECISION,
	p_address TEXT
	) RETURNS void AS $$
	INSERT INTO user_locations (user_id, latitude, longitude, address, updated_at)
	VALUES (p_user_id, p_latitude, p_longitude, p_address, NOW())
	ON CONFLICT (user_id) DO UPDATE SET
	latitude = EXCLUDED.latitude,
	longitude = EXCLUDED.longitude,
	address = EXCLUDED.address,
	updated_at = NOW();
	$$ LANGUAGE sql;

	-- ============================================================
	-- SAVED NOTES
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_list_notes(
	p_chat_id BIGINT
	) RETURNS TABLE(note_name VARCHAR(64)) AS $$
	SELECT sn.note_name FROM saved_notes sn
	WHERE sn.chat_id = p_chat_id ORDER BY sn.note_name;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_note(
	p_chat_id BIGINT,
	p_note_name TEXT
	) RETURNS TABLE(content TEXT, content_type VARCHAR(20), file_id TEXT) AS $$
	SELECT sn.content, sn.content_type, sn.file_id FROM saved_notes sn
	WHERE sn.chat_id = p_chat_id AND sn.note_name = p_note_name;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_upsert_note(
	p_chat_id BIGINT,
	p_note_name TEXT,
	p_content TEXT,
	p_content_type TEXT,
	p_file_id TEXT,
	p_created_by BIGINT
	) RETURNS void AS $$
	INSERT INTO saved_notes (chat_id, note_name, content, content_type, file_id, created_by)
	VALUES (p_chat_id, p_note_name, p_content, p_content_type, p_file_id, p_created_by)
	ON CONFLICT (chat_id, note_name) DO UPDATE SET
	content = EXCLUDED.content,
	content_type = EXCLUDED.content_type,
	file_id = EXCLUDED.file_id,
	created_by = EXCLUDED.created_by;
	$$ LANGUAGE sql;

	-- ============================================================
	-- STATISTICS
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_flush_message_stats(
	p_chat_id BIGINT,
	p_user_id BIGINT,
	p_date DATE,
	p_count INTEGER
	) RETURNS void AS $$
	INSERT INTO message_stats (chat_id, user_id, date, message_count)
	VALUES (p_chat_id, p_user_id, p_date, p_count)
	ON CONFLICT (chat_id, user_id, date) DO UPDATE SET
	message_count = message_stats.message_count + p_count;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_flush_command_stats(
	p_chat_id BIGINT,
	p_command TEXT,
	p_date DATE,
	p_count INTEGER
	) RETURNS void AS $$
	INSERT INTO command_stats (chat_id, command, date, use_count)
	VALUES (p_chat_id, p_command, p_date, p_count)
	ON CONFLICT (chat_id, command, date) DO UPDATE SET
	use_count = command_stats.use_count + p_count;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_top_users(
	p_chat_id BIGINT
	) RETURNS TABLE(user_id BIGINT, total BIGINT, first_name VARCHAR(255), last_name VARCHAR(255), username VARCHAR(255)) AS $$
	SELECT ms.user_id, SUM(ms.message_count)::BIGINT AS total,
	u.first_name, u.last_name, u.username
	FROM message_stats ms
	LEFT JOIN users u ON ms.user_id = u.user_id
	WHERE ms.chat_id = p_chat_id
	GROUP BY ms.user_id, u.first_name, u.last_name, u.username
	ORDER BY total DESC
	LIMIT 10;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_total_messages(
	p_chat_id BIGINT
	) RETURNS TABLE(total BIGINT) AS $$
	SELECT SUM(message_count)::BIGINT AS total FROM message_stats
	WHERE chat_id = p_chat_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_unique_users(
	p_chat_id BIGINT
	) RETURNS TABLE(total BIGINT) AS $$
	SELECT COUNT(DISTINCT user_id)::BIGINT AS total FROM message_stats
	WHERE chat_id = p_chat_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_reset_message_stats(
	p_chat_id BIGINT
	) RETURNS void AS $$
	DELETE FROM message_stats WHERE chat_id = p_chat_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_top_commands(
	p_chat_id BIGINT
	) RETURNS TABLE(command VARCHAR(64), total BIGINT) AS $$
	SELECT cs.command, SUM(cs.use_count)::BIGINT AS total
	FROM command_stats cs
	WHERE cs.chat_id = p_chat_id
	GROUP BY cs.command
	ORDER BY total DESC
	LIMIT 10;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_reset_command_stats(
	p_chat_id BIGINT
	) RETURNS void AS $$
	DELETE FROM command_stats WHERE chat_id = p_chat_id;
	$$ LANGUAGE sql;

	-- ============================================================
	-- GROUPS
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_list_groups()
	RETURNS TABLE(chat_id BIGINT, title VARCHAR(255), username VARCHAR(255)) AS $$
	SELECT c.chat_id, c.title, c.username FROM chats c
	WHERE c.chat_type IN ('group', 'supergroup')
	ORDER BY c.title LIMIT 50;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_search_groups(
	p_search TEXT
	) RETURNS TABLE(chat_id BIGINT, title VARCHAR(255), username VARCHAR(255)) AS $$
	SELECT c.chat_id, c.title, c.username FROM chats c
	WHERE c.chat_type IN ('group', 'supergroup')
	AND LOWER(c.title) LIKE p_search
	ORDER BY c.title LIMIT 50;
	$$ LANGUAGE sql;

	-- ============================================================
	-- INFO / COUNTS
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_count_users()
	RETURNS TABLE(count BIGINT) AS $$
	SELECT COUNT(*)::BIGINT FROM users;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_count_chats()
	RETURNS TABLE(count BIGINT) AS $$
	SELECT COUNT(*)::BIGINT FROM chats;
	$$ LANGUAGE sql;

	-- ============================================================
	-- FEDERATION - CORE
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_get_chat_federation_id(
	p_chat_id BIGINT
	) RETURNS TABLE(federation_id UUID) AS $$
	SELECT fc.federation_id FROM federation_chats fc
	WHERE fc.chat_id = p_chat_id LIMIT 1;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_chat_federation(
	p_chat_id BIGINT
	) RETURNS TABLE(id UUID, name VARCHAR(255), owner_id BIGINT) AS $$
	SELECT f.id, f.name, f.owner_id
	FROM federations f
	JOIN federation_chats fc ON f.id = fc.federation_id
	WHERE fc.chat_id = p_chat_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_federation(
	p_federation_id UUID
	) RETURNS TABLE(id UUID, name VARCHAR(255), owner_id BIGINT, created_at TIMESTAMP WITH TIME ZONE) AS $$
	SELECT f.id, f.name, f.owner_id, f.created_at FROM federations f
	WHERE f.id = p_federation_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_federation_basic(
	p_federation_id UUID
	) RETURNS TABLE(id UUID, name VARCHAR(255)) AS $$
	SELECT f.id, f.name FROM federations f
	WHERE f.id = p_federation_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_federation_owner(
	p_federation_id UUID
	) RETURNS TABLE(name VARCHAR(255), owner_id BIGINT) AS $$
	SELECT f.name, f.owner_id FROM federations f
	WHERE f.id = p_federation_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_count_user_federations(
	p_user_id BIGINT
	) RETURNS TABLE(count BIGINT) AS $$
	SELECT COUNT(*)::BIGINT FROM federations
	WHERE owner_id = p_user_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_create_federation(
	p_name TEXT,
	p_owner_id BIGINT
	) RETURNS TABLE(id UUID) AS $$
	INSERT INTO federations (name, owner_id)
	VALUES (p_name, p_owner_id)
	RETURNING id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_delete_federation(
	p_federation_id UUID
	) RETURNS void AS $$
	DELETE FROM federations WHERE id = p_federation_id;
	$$ LANGUAGE sql;

	-- ============================================================
	-- FEDERATION - CHATS
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_get_chat_federation_joined(
	p_chat_id BIGINT
	) RETURNS TABLE(id UUID, name VARCHAR(255)) AS $$
	SELECT f.id, f.name
	FROM federations f
	JOIN federation_chats fc ON f.id = fc.federation_id
	WHERE fc.chat_id = p_chat_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_join_federation(
	p_federation_id UUID,
	p_chat_id BIGINT,
	p_joined_by BIGINT
	) RETURNS void AS $$
	INSERT INTO federation_chats (federation_id, chat_id, joined_by)
	VALUES (p_federation_id, p_chat_id, p_joined_by);
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_leave_federation(
	p_federation_id UUID,
	p_chat_id BIGINT
	) RETURNS void AS $$
	DELETE FROM federation_chats
	WHERE federation_id = p_federation_id AND chat_id = p_chat_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_federation_chats(
	p_federation_id UUID
	) RETURNS TABLE(chat_id BIGINT) AS $$
	SELECT fc.chat_id FROM federation_chats fc
	WHERE fc.federation_id = p_federation_id;
	$$ LANGUAGE sql;

	-- ============================================================
	-- FEDERATION - BANS
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_check_federation_ban(
	p_federation_id UUID,
	p_user_id BIGINT
	) RETURNS TABLE(reason TEXT) AS $$
	SELECT fb.reason FROM federation_bans fb
	WHERE fb.federation_id = p_federation_id AND fb.user_id = p_user_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_check_federation_ban_exists(
	p_federation_id UUID,
	p_user_id BIGINT
	) RETURNS TABLE(exists_flag INTEGER) AS $$
	SELECT 1 FROM federation_bans
	WHERE federation_id = p_federation_id AND user_id = p_user_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_insert_federation_ban(
	p_federation_id UUID,
	p_user_id BIGINT,
	p_reason TEXT,
	p_banned_by BIGINT
	) RETURNS void AS $$
	INSERT INTO federation_bans (federation_id, user_id, reason, banned_by)
	VALUES (p_federation_id, p_user_id, p_reason, p_banned_by);
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_update_federation_ban(
	p_reason TEXT,
	p_banned_by BIGINT,
	p_federation_id UUID,
	p_user_id BIGINT
	) RETURNS void AS $$
	UPDATE federation_bans SET reason = p_reason, banned_by = p_banned_by, banned_at = NOW()
	WHERE federation_id = p_federation_id AND user_id = p_user_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_delete_federation_ban(
	p_federation_id UUID,
	p_user_id BIGINT
	) RETURNS void AS $$
	DELETE FROM federation_bans
	WHERE federation_id = p_federation_id AND user_id = p_user_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_fban_info_group(
	p_user_id BIGINT,
	p_chat_id BIGINT
	) RETURNS TABLE(reason TEXT, banned_by BIGINT, banned_at TIMESTAMP WITH TIME ZONE, name VARCHAR(255), id UUID) AS $$
	SELECT fb.reason, fb.banned_by, fb.banned_at, f.name, f.id
	FROM federation_bans fb
	JOIN federations f ON fb.federation_id = f.id
	JOIN federation_chats fc ON f.id = fc.federation_id
	WHERE fb.user_id = p_user_id AND fc.chat_id = p_chat_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_fban_info_all(
	p_user_id BIGINT
	) RETURNS TABLE(reason TEXT, banned_by BIGINT, banned_at TIMESTAMP WITH TIME ZONE, name VARCHAR(255), id UUID) AS $$
	SELECT fb.reason, fb.banned_by, fb.banned_at, f.name, f.id
	FROM federation_bans fb
	JOIN federations f ON fb.federation_id = f.id
	WHERE fb.user_id = p_user_id;
	$$ LANGUAGE sql;

	-- ============================================================
	-- FEDERATION - ADMINS
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_check_federation_admin(
	p_federation_id UUID,
	p_user_id BIGINT
	) RETURNS TABLE(exists_flag INTEGER) AS $$
	SELECT 1 FROM federation_admins
	WHERE federation_id = p_federation_id AND user_id = p_user_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_insert_federation_admin(
	p_federation_id UUID,
	p_user_id BIGINT,
	p_promoted_by BIGINT
	) RETURNS void AS $$
	INSERT INTO federation_admins (federation_id, user_id, promoted_by)
	VALUES (p_federation_id, p_user_id, p_promoted_by);
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_delete_federation_admin(
	p_federation_id UUID,
	p_user_id BIGINT
	) RETURNS void AS $$
	DELETE FROM federation_admins
	WHERE federation_id = p_federation_id AND user_id = p_user_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_federation_admins(
	p_federation_id UUID
	) RETURNS TABLE(user_id BIGINT, promoted_at TIMESTAMP WITH TIME ZONE) AS $$
	SELECT fa.user_id, fa.promoted_at FROM federation_admins fa
	WHERE fa.federation_id = p_federation_id ORDER BY fa.promoted_at ASC;
	$$ LANGUAGE sql;

	-- ============================================================
	-- FEDERATION - ALLOWLIST
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_check_federation_allowlist(
	p_federation_id UUID,
	p_user_id BIGINT
	) RETURNS TABLE(exists_flag INTEGER) AS $$
	SELECT 1 FROM federation_allowlist
	WHERE federation_id = p_federation_id AND user_id = p_user_id;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_insert_federation_allowlist(
	p_federation_id UUID,
	p_user_id BIGINT
	) RETURNS void AS $$
	INSERT INTO federation_allowlist (federation_id, user_id)
	VALUES (p_federation_id, p_user_id);
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_delete_federation_allowlist(
	p_federation_id UUID,
	p_user_id BIGINT
	) RETURNS void AS $$
	DELETE FROM federation_allowlist
	WHERE federation_id = p_federation_id AND user_id = p_user_id;
	$$ LANGUAGE sql;

	-- ============================================================
	-- FEDERATION - COUNTS / LISTING
	-- ============================================================

	CREATE OR REPLACE FUNCTION sp_get_federation_counts(
	p_federation_id UUID
	) RETURNS TABLE(admin_count BIGINT, chat_count BIGINT, ban_count BIGINT) AS $$
	SELECT
	(SELECT COUNT(*) FROM federation_admins WHERE federation_id = p_federation_id)::BIGINT,
	(SELECT COUNT(*) FROM federation_chats WHERE federation_id = p_federation_id)::BIGINT,
	(SELECT COUNT(*) FROM federation_bans WHERE federation_id = p_federation_id)::BIGINT;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_owned_federations(
	p_user_id BIGINT
	) RETURNS TABLE(id UUID, name VARCHAR(255), chat_count BIGINT, ban_count BIGINT) AS $$
	SELECT f.id, f.name,
	(SELECT COUNT(*) FROM federation_chats WHERE federation_id = f.id)::BIGINT AS chat_count,
	(SELECT COUNT(*) FROM federation_bans WHERE federation_id = f.id)::BIGINT AS ban_count
	FROM federations f
	WHERE f.owner_id = p_user_id
	ORDER BY f.created_at ASC;
	$$ LANGUAGE sql;

	CREATE OR REPLACE FUNCTION sp_get_admin_federations(
	p_user_id BIGINT
	) RETURNS TABLE(id UUID, name VARCHAR(255), owner_id BIGINT, chat_count BIGINT, ban_count BIGINT) AS $$
	SELECT f.id, f.name, f.owner_id,
	(SELECT COUNT(*) FROM federation_chats WHERE federation_id = f.id)::BIGINT AS chat_count,
	(SELECT COUNT(*) FROM federation_bans WHERE federation_id = f.id)::BIGINT AS ban_count
	FROM federations f
	JOIN federation_admins fa ON f.id = fa.federation_id
	WHERE fa.user_id = p_user_id AND f.owner_id != p_user_id
	ORDER BY fa.promoted_at ASC;
	-$$ LANGUAGE sql
	+$$ LANGUAGE sql;

	]]
	end

	return migration
	diff --git a/src/middleware/user_tracker.lua b/src/middleware/user_tracker.lua
	index c958b20..22bcdb0 100644
	--- a/src/middleware/user_tracker.lua
	+++ b/src/middleware/user_tracker.lua
	@@ -1,76 +1,76 @@
	--[[
	mattata v2.0 - User Tracker Middleware
	Upserts user and chat information to PostgreSQL with Redis-based debouncing.
	Uses a 60s dedup key per user+chat to reduce DB writes by ~95%.
	]]

	local user_tracker = {}
	user_tracker.name = 'user_tracker'

	function user_tracker.run(ctx, message)
	if not message.from then
	return ctx, true
	end

	local user = message.from
	local user_id = user.id
	local chat_id = message.chat and message.chat.id

	-- Debounce: skip DB upserts if we've seen this user+chat in the last 60s
	local dedup_key = string.format('seen:%s:%s', user_id, chat_id or 'private')
	local already_seen = ctx.redis.exists(dedup_key)
	if already_seen == 1 or already_seen == true then
	-- Still update username->id mapping (cheap Redis SET)
	if user.username then
	ctx.redis.set('username:' .. user.username:lower(), user_id)
	end
	return ctx, true
	end

	-- Set dedup key with 60s TTL
	ctx.redis.setex(dedup_key, 60, '1')

	-- upsert user to postgresql
	local now = os.date('!%Y-%m-%d %H:%M:%S')
	pcall(function()
	- ctx.db.call('sp_upsert_user', {
	+ ctx.db.call('sp_upsert_user', table.pack(
	user_id,
	user.username and user.username:lower() or nil,
	user.first_name,
	user.last_name,
	user.language_code,
	user.is_bot or false,
	now
	- })
	+ ))
	end)

	-- upsert chat to postgresql (for groups)
	if chat_id and message.chat.type ~= 'private' then
	pcall(function()
	- ctx.db.call('sp_upsert_chat', {
	+ ctx.db.call('sp_upsert_chat', table.pack(
	chat_id,
	message.chat.title,
	message.chat.type,
	message.chat.username and message.chat.username:lower() or nil
	- })
	+ ))
	end)

	-- track user<->chat membership
	pcall(function()
	ctx.db.call('sp_upsert_chat_member', {
	chat_id,
	user_id,
	now
	})
	end)
	end

	-- Keep Redis username->id mapping for quick lookups
	if user.username then
	ctx.redis.set('username:' .. user.username:lower(), user_id)
	end

	return ctx, true
	end

	return user_tracker

File Metadata

Mime Type: text/x-diff
Expires: Sun, May 17, 2:53 AM (1 d, 17 h)
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 63587
Default Alt Text: (52 KB)

No OneTemporaryActions

View Options

File Metadata

Event Timeline

No OneTemporary
Actions