/*
From the 'accounts' table
username - id
account_1_username - account_1_id
account_2_username - account_2_id
*/

/*
Set the number of days info to retain in:
public."mentions"
public."statuses"
public."status_faves"
*/

/* Tidy (DELETE FROM) the gotosocial db mentions table */
DELETE
FROM public."mentions" m
	/* Delete all mentions over n days old */
	WHERE m."created_at"::date <= (CURRENT_DATE - interval '5' DAY);

/* General (DELETE FROM) the gotosocial db statuses table */
/* Per account deletes will be done later */
DELETE
FROM public."statuses" s
	/* Delete all statuses over n days old */
	WHERE s."created_at"::date <= (CURRENT_DATE - interval '10' DAY)

	AND NOT EXISTS
	/* Don't remove any statuses from any accounts in the 'users' table */
	(SELECT account_id FROM public."users" u
		WHERE u."account_id" = s."account_id");

/* Per account (DELETE FROM) the gotosocial db statuses table */
DELETE
FROM public."statuses" s
	/* Delete all statuses over n days old */
	/* Probably makes sense to keep this in line with the 'General' setting */
	WHERE s."created_at"::date <= (CURRENT_DATE - interval '10' DAY)

	AND
	/* Delete from: account_1_username account */
	(s."account_id" = 'account_1_id')

	AND
	/* Retain pinned posts */
	(s."pinned_at" IS NULL);

/* Per account (DELETE FROM) the gotosocial db statuses table */
DELETE
FROM public."statuses" s
	/* Delete all statuses over n days old */
	/* Probably makes sense to keep account_2_username posts for longer */
	WHERE s."created_at"::date <= (CURRENT_DATE - interval '20' DAY)

	AND
	/* Delete from: account_2_username account */
	(s."account_id" = 'account_2_id')

	AND
	/* Retain pinned posts */
	(s."pinned_at" IS NULL);

/* Tidy (DELETE FROM) the gotosocial db status_faves table */
DELETE
FROM public."status_faves" sf
	/* Delete all status_faves over n days old */
	WHERE sf."created_at"::date <= (CURRENT_DATE - interval '10' DAY);

/* Tidy (ANALYZE) any table that has had a DELETE performed upon it
public."mentions
public."statuses"
public."status_faves
*/
ANALYZE public."mentions", public."statuses", public."status_faves";