#!/usr/bin/env bash
#
# Löscht anonyme Kutt-Links die seit %MAX_AGE_DAYS% Tagen nicht mehr aufgerufen wurden.
# Als Referenzdatum gilt der letzte Visit oder — falls nie aufgerufen — das Erstelldatum.
#
# Nutzung: Per Cron z.B. täglich ausführen.
#   0 3 * * * /home/docker-projekte/kutt/scripts/kutt-links-cleanup.sh
#

set -euo pipefail

CONTAINER="kutt-db"
DB_USER="kutt"
DB_NAME="kutt"
MAX_AGE_DAYS=365
DRY_RUN="${1:-}"

SQL_SELECT="
SELECT l.id, l.address, l.target,
       COALESCE(MAX(v.created_at), l.created_at) AS last_activity
FROM links l
LEFT JOIN visits v ON v.link_id = l.id
WHERE l.user_id IS NULL
GROUP BY l.id
HAVING COALESCE(MAX(v.created_at), l.created_at) < NOW() - INTERVAL '${MAX_AGE_DAYS} days'
ORDER BY last_activity;
"

SQL_DELETE="
DELETE FROM links
WHERE id IN (
    SELECT l.id
    FROM links l
    LEFT JOIN visits v ON v.link_id = l.id
    WHERE l.user_id IS NULL
    GROUP BY l.id
    HAVING COALESCE(MAX(v.created_at), l.created_at) < NOW() - INTERVAL '${MAX_AGE_DAYS} days'
);
"

echo "$(date '+%Y-%m-%d %H:%M:%S') — Cleanup anonymer Links (älter als ${MAX_AGE_DAYS} Tage ohne Zugriff)"

if [ "$DRY_RUN" = "--dry-run" ]; then
    echo "DRY RUN — es wird nichts gelöscht."
    docker exec "$CONTAINER" psql -U "$DB_USER" -d "$DB_NAME" -c "$SQL_SELECT"
else
    CANDIDATES=$(docker exec "$CONTAINER" psql -U "$DB_USER" -d "$DB_NAME" -t -A -c "
        SELECT COUNT(*) FROM links l
        LEFT JOIN visits v ON v.link_id = l.id
        WHERE l.user_id IS NULL
        GROUP BY l.id
        HAVING COALESCE(MAX(v.created_at), l.created_at) < NOW() - INTERVAL '${MAX_AGE_DAYS} days';
    " | wc -l)

    if [ "$CANDIDATES" -eq 0 ]; then
        echo "Keine abgelaufenen anonymen Links gefunden."
        exit 0
    fi

    echo "${CANDIDATES} Link(s) werden gelöscht..."
    docker exec "$CONTAINER" psql -U "$DB_USER" -d "$DB_NAME" -c "$SQL_DELETE"
    echo "Fertig."
fi