#! /bin/bash

# Copyright (C) 2026 Ján Máté <jan.mate@inf-it.com>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.

set -u
trap '' PIPE	# a dead psql then makes run_sql perform exit 9 instead of killing us (such as set -o pipefail)
umask 0007

IFS=$'\n'

LOCK_FILE='/var/lock/.dovecot_spam.exclusivelock'
CONF_FILE="$(dirname -- "${0}")/dovecot_spam.conf"

if [ ! -r "${CONF_FILE}" ]; then
	echo "Error: missing configuration file: '${CONF_FILE}'" > /dev/stderr
	exit 1
fi

# PosgreSQL configuration + schema information
source "${CONF_FILE}"

{ stat --printf='%A' -- "${CONF_FILE}" | cut -c 8- | grep -qE -- '^---$' ; } 2> /dev/null
if [ "$?" -ne 0 ]; then
	echo "Error: invalid configuration file permissions" > /dev/stderr
	echo "     - you must remove (at least) all permissions for others: chmod o= -- '${CONF_FILE}'" > /dev/stderr
	echo "     - it is also recommended to check the group of the file, and consider 'go=' permission if possible" > /dev/stderr
	exit 2
fi

# make sure the script runs under the expected user (auto-switch when started as root)
IFS=':' read -r EXEC_USER EXEC_GROUP <<< "${CONF_PROCESS_USER_GROUP}"
if [ "$(id -un)" != "${EXEC_USER}" ]; then
	if [ "${EUID}" -eq 0 ]; then
		# in case of per-user libpam-tmpdir (e.g. /tmp/user/0) the script will fail because it inherits the variables
		unset TMPDIR TMP
		if [ -n "${EXEC_GROUP:-}" ]; then
			exec runuser -u "${EXEC_USER}" -g "${EXEC_GROUP}" -- "${0}" "$@"
		else
			exec runuser -u "${EXEC_USER}" -- "${0}" "$@"
		fi
	fi
	echo "Error: this script must run as '${CONF_PROCESS_USER_GROUP}' (or as root)" > /dev/stderr
	exit 3
fi

# go to the user home directory or / (prevent problems related to the current/working directory and permissions)
cd -- "${HOME:-/}" 2>/dev/null || cd -- /

(flock -x -w 2 666 || { echo >&2 'Error: "'"${0}"'": unable to aquire lock (multiple instances running?). Aborting ...'; exit 4; }
	# psql stdin fifo name
	CONF_FIFO_IN="$(mktemp --dry-run)" || { echo >&2 'Error: "'"${0}"'": unable to get fifo name for psql stdin. Aborting ...'; exit 5; }
	# psql stdout fifo name
	CONF_FIFO_OUT="$(mktemp --dry-run)" || { echo >&2 'Error: "'"${0}"'": unable to get fifo name for psql stdout. Aborting ...'; exit 6; }
	# create the psql stdin fifo
	[ -p "${CONF_FIFO_IN}"  ] || mkfifo --mode 0600 -- "${CONF_FIFO_IN}" || { echo >&2 'Error: "'"${0}"'": unable to create fifo for psql stdin. Aborting ...'; exit 7; }
	# create the psql stdout fifo
	[ -p "${CONF_FIFO_OUT}" ] || mkfifo --mode 0600 -- "${CONF_FIFO_OUT}"|| { echo >&2 'Error: "'"${0}"'": unable to create fifo for psql stdout. Aborting ...'; rm --preserve-root --force -- "${CONF_FIFO_IN}" &> /dev/null;  exit 8; }

	PGPASSWORD="${CONF_PG_PASS}" \
	psql \
		--no-psqlrc \
		--no-readline \
		--quiet --tuples-only --no-align \
		--host="${CONF_PG_HOST}" \
		--username="${CONF_PG_USER}" \
		--no-password \
		-- "${CONF_PG_NAME}" \
		< "${CONF_FIFO_IN}" \
		> "${CONF_FIFO_OUT}" \
		&
	PG_PID="$!"

	# the order must mirror psql's redirections (first < then >) to prevent deadlock
	exec 3>"${CONF_FIFO_IN}"
	exec 4<"${CONF_FIFO_OUT}"

	cleanup () {
		exec 3>&- 4<&-
		rm --preserve-root --force -- "${CONF_FIFO_IN}" "${CONF_FIFO_OUT}" &> /dev/null
		kill -SIGTERM "${PG_PID}" &> /dev/null
	}
	trap 'cleanup' EXIT

	# run_sql usage:
	#	run_sql 'SQL statement;' 
	#	run_sql <<-'EOF' SQL statement1; SQL statement2; ...; EOF
	# result + status:
	#	SQL_OUT		SQL result delimited by the \n character
	#	SQL_ERROR	true/false
	#	SQL_STATE	SQLSTATE of the statement (00000 = success)
	#	SQL_ROWS	rows returned/affected
	#	SQL_ERRMSG	primary error message of the last failure (useful if the SQL statement failed)
	# return codes:
	#	0	success
	#	1	statement failed
	# script exit codes:
	#	9	database connection lost
	run_sql () {
		# end of the result from the current SQL statement
		local EOR="__DONE_${RANDOM}${RANDOM}__" line sql
		# if the SQL is not passed as the function argument read it from STDIN
		[ "$#" -gt 0 ] && sql="${1}" || sql="$(cat)"
		# send the SQL command + EOR and requested variables to the DB
		printf '%s\n' "${sql}" '\echo '"${EOR}"' :ERROR :SQLSTATE :ROW_COUNT :LAST_ERROR_MESSAGE' >&3 2>/dev/null
		# clear the variables
		SQL_OUT='' SQL_ERROR='' SQL_STATE='' SQL_ROWS='' SQL_ERRMSG=''
		# read the DB output
		while read -r line <&4; do
			case "${line}" in
				# EOR and requested variables
				"${EOR} "*)
					IFS=' ' read -r SQL_ERROR SQL_STATE SQL_ROWS SQL_ERRMSG <<< "${line#"${EOR} "}"
					[ "${SQL_ERROR}" = 'false' ] && return 0
					# statement failed
					return 1
					;;
				# DB data (result)
				*)
					SQL_OUT+="${line}"$'\n'
					;;
			esac
		done
		printf 'Connection to the database lost\n' >&2
		exit 9
	}

	EXIT_CODE=0
	while :; do
		# get the next message for processing
		run_sql <<-'EOF'
			BEGIN;
			WITH "_" AS (
				SELECT
					"_id",
					"message_file",
					"type"
				FROM "public"."reporting"
				WHERE
					"status"='waiting'
				ORDER BY
					"_ts_created" ASC
				LIMIT 1
			)
			UPDATE "public"."reporting" "_u"
			SET
				"status"='processing'
			FROM "_"
			WHERE
				"_u"."_id"="_"."_id"
			RETURNING
				"_u"."_id",
				"_u"."message_file",
				"_u"."type"
			;
		EOF

		if [ "${SQL_ERROR}" = 'true' ]; then
			printf 'SQL failed: SQLSTATE=%s %s\n' "${SQL_STATE}" "${SQL_ERRMSG}" >&2
			run_sql 'ROLLBACK;'
			EXIT_CODE=10
			break
		elif [ "${SQL_ROWS}" -ne 1 ]; then
			# nothing to process, exit (will re-run from the cron later)
			run_sql 'ROLLBACK;'
			break
		else
			# process the selected message
			IFS='|' read -r ID FILE TYPE <<< "${SQL_OUT}"
			FILE_PATH="${CONF_CACHE_DIR}/${FILE}"

			# File not found: DB / filesystem inconsitency
			if [ ! -f "${FILE_PATH}" ]; then
				run_sql <<-EOF
					UPDATE "public"."reporting"
					SET
						"status"='missing'
					WHERE
						"_id"=${ID}
					;
				EOF

				if [ "${SQL_ERROR}" = 'true' ]; then
					printf 'SQL failed: SQLSTATE=%s %s\n' "${SQL_STATE}" "${SQL_ERRMSG}" >&2
					run_sql 'ROLLBACK;'
					EXIT_CODE=10
					break
				else
					run_sql 'COMMIT;'
				fi

			# File exists, perform the sa-learn ...
			else
				# sa-learn will fail with messages larger than allowed by configuration, we need to skip them
				FILE_SIZE="$(stat --format='%s' -- "${FILE_PATH}")"

				if [ "${FILE_SIZE}" -ge "${CONF_MAX_MESSAGE_SIZE}" ]; then
					run_sql <<-EOF
						UPDATE "public"."reporting"
						SET
							"status"='skipped'
						WHERE
							"_id"=${ID}
						;
					EOF

					if [ "${SQL_ERROR}" = 'true' ]; then
						printf 'SQL failed: SQLSTATE=%s %s\n' "${SQL_STATE}" "${SQL_ERRMSG}" >&2
						run_sql 'ROLLBACK;'
						EXIT_CODE=10
						break
					else
						run_sql 'COMMIT;'
					fi

					continue
				fi

				# sa-learn: learn "--spam" and "--ham" without DB / journal synchronization (see below)
				# note: the command is executed as amavis user (cron settings)
				sa-learn --no-sync "--${TYPE}" -- "${FILE_PATH}" >/dev/null

				if [ "$?" -ne 0 ]; then
					printf 'sa-learn --no-sync ... failed: ending all further processing now!\n' >&2
					run_sql 'ROLLBACK;'
					EXIT_CODE=10
					break
				else
					run_sql <<-EOF
						UPDATE "public"."reporting"
						SET
							"status"='processed'
						WHERE
							"_id"=${ID}
						;
					EOF

					if [ "${SQL_ERROR}" = 'true' ]; then
						printf 'SQL failed: SQLSTATE=%s %s\n' "${SQL_STATE}" "${SQL_ERRMSG}" >&2
						run_sql 'ROLLBACK;'
						EXIT_CODE=10
						break
					else
						run_sql 'COMMIT;'
					fi
				fi
			fi
		fi
	done

	# delete DB entries after $CONG_KEEP_DATA_N_DAYS days (including the waiting entries, because the files will be deleted soon)
	run_sql <<-EOF
		DELETE FROM "public"."reporting"
		WHERE
			"_ts_created"<now()-'${CONG_KEEP_DATA_N_DAYS} days'::interval
		;
	EOF

	exec 3>&-
	wait "${PG_PID}"

	if [ "${EXIT_CODE}" -eq 0 ]; then
		# sa-learn: synchronize the database and the journal if needed
		# note: the command is executed as amavis user (cron settings)
		sa-learn --sync >/dev/null
		if [ "$?" -ne 0 ]; then
			printf 'sa-learn --sync failed!\n' >&2
			EXIT_CODE=11
		fi

		# remove > $CONG_KEEP_DATA_N_DAYS old files from the cache (1 day more than from the DB)
		find "${CONF_CACHE_DIR}" -mindepth 1 -maxdepth 1 -type f -mtime "+${CONG_KEEP_DATA_N_DAYS}" -exec rm --preserve-root --force -- '{}' \; &> /dev/null
	fi

	exit "${EXIT_CODE}"
) 666> "${LOCK_FILE}"
INNER_EXIT_CODE="$?"

exit "${INNER_EXIT_CODE}"
