"""Retention enforcement. Deletes plate reads past the cutoff together with the rows that

reference them (sightings, watchlist hits) so the delete doesn't violate foreign keys.

Shared by the admin endpoint and the scheduled Celery task."""

from __future__ import annotations

from datetime import datetime

from sqlalchemy import delete, func, select

from sqlalchemy.orm import Session

from app.models import PlateRead, Sighting, WatchlistHit

def count_expired(db: Session, cutoff: datetime) -> int:

    return db.scalar(select(func.count()).select_from(PlateRead)

                     .where(PlateRead.read_ts < cutoff)) or 0

def purge_expired(db: Session, cutoff: datetime) -> int:

    """Delete reads older than cutoff and their dependents. Returns reads deleted."""

    old_ids = [r[0] for r in db.execute(

        select(PlateRead.id).where(PlateRead.read_ts < cutoff)).all()]

    if not old_ids:

        return 0

    db.execute(delete(WatchlistHit).where(WatchlistHit.plate_read_id.in_(old_ids)))

    db.execute(delete(Sighting).where(Sighting.plate_read_id.in_(old_ids)))

    result = db.execute(delete(PlateRead).where(PlateRead.id.in_(old_ids)))

    return result.rowcount