#!/bin/sh

# wctidy - delete orphan attachments (message_id IS NULL) older than threshold
# Usage: ./wctidy [minutes] [db_path]
# Default: 30 minutes, fanr.sqlite

set -eu

# Default values
DEFAULT_MINUTES=30
DEFAULT_DB="fanr.sqlite"

# Parse arguments
MINUTES="${1:-$DEFAULT_MINUTES}"
DB="${2:-$DEFAULT_DB}"

# Validate minutes is a positive integer
case "$MINUTES" in
    ''|*[!0-9]*)
        echo "Error: minutes must be a positive integer" >&2
        echo "Usage: $0 [older than (minutes)] [db_path]" >&2
        echo "  defaults to deleting orphans older than ${DEFAULT_MINUTES} minutes, from db: [${DEFAULT_DB}]" >&2
        exit 1
        ;;
esac

# Check if database exists
if [ ! -f "$DB" ]; then
    echo "Error: Database '$DB' not found" >&2
    exit 1
fi

# Check if sqlite3 is available
command -v sqlite3 >/dev/null 2>&1 || {
    echo "Error: sqlite3 not found" >&2
    exit 1
}

# Calculate cutoff time (unix epoch seconds)
CUTOFF=$(($(date +%s) - MINUTES * 60))
CUTOFF_MS=$(($CUTOFF * 1000))
human_cutoff=`date +'%F %T' -d "@${CUTOFF}"`
human_now=`date +'%F %T'`
echo "database: ${DB} cutoff: ${human_cutoff} (now ${human_now})"

# Count orphan attachments older than cutoff
COUNT=$(sqlite3 "$DB" <<EOF
SELECT COUNT(*) FROM attachments
WHERE message_id IS NULL
AND created_at < $CUTOFF_MS;
EOF
)

if [ "$COUNT" -eq 0 ]; then
    echo "No orphan attachments older than $MINUTES minutes found."
    exit 0
fi

echo "Found $COUNT orphan attachment(s) older than $MINUTES minutes."

# Delete orphan attachments older than cutoff
sqlite3 "$DB" <<EOF
DELETE FROM attachments
WHERE message_id IS NULL
AND created_at < $CUTOFF;
EOF

echo "Deleted $COUNT orphan attachment(s)."