#!/usr/bin/env python3
"""Preview gPodder's separate SQL episode purge using a closed database copy.

SPDX-License-Identifier: GPL-3.0-or-later
Original diagnostic helper by brodyga45's AI agent, 2026.
SQL behaviour reference: gpodder/gpodder, tag 3.11.4, dbsqlite.Database.purge.
This reports that SQL path only, not queued/paused status or partial files.
"""
import argparse
import json
from pathlib import Path
import sqlite3
import sys


def preview(database_copy, limit, podcast_id=None):
    if limit < 0:
        raise ValueError("limit must be non-negative (0 disables this SQL purge)")
    source = Path(database_copy).resolve(strict=True)
    con = sqlite3.connect(source.as_uri() + "?mode=ro", uri=True, timeout=3)
    try:
        con.execute("PRAGMA query_only = ON")
        con.execute("PRAGMA trusted_schema = OFF")
        con.execute("BEGIN")
        tables = {r[0] for r in con.execute("SELECT name FROM sqlite_master WHERE type='table'")}
        if "episode" not in tables:
            raise ValueError("not a supported gPodder episode table")
        columns = {r[1] for r in con.execute("PRAGMA table_info(episode)")}
        if not {"id", "podcast_id", "published", "state", "archive"} <= columns:
            raise ValueError("episode schema lacks required columns")
        ids = [r[0] for r in con.execute("SELECT DISTINCT podcast_id FROM episode ORDER BY podcast_id")]
        if podcast_id is not None:
            ids = [p for p in ids if p == podcast_id]
            if not ids:
                raise ValueError("no episodes found for that podcast_id")
        report = []
        for pid in ids:
            total = con.execute("SELECT count(*) FROM episode WHERE podcast_id=?", (pid,)).fetchone()[0]
            counts = []
            if limit:
                # Read-only projection of the upstream DELETE predicate.
                counts = con.execute("""
                    SELECT state, count(*), sum(CASE WHEN archive <> 0 THEN 1 ELSE 0 END)
                    FROM episode WHERE podcast_id=? AND state <> 1 AND id NOT IN
                    (SELECT id FROM episode WHERE podcast_id=? ORDER BY published DESC LIMIT ?)
                    GROUP BY state ORDER BY state
                """, (pid, pid, limit)).fetchall()
            removed = sum(r[1] for r in counts)
            boundary = con.execute("""SELECT published FROM episode WHERE podcast_id=?
                ORDER BY published DESC LIMIT 1 OFFSET ?""", (pid, max(0, limit - 1))).fetchone() if limit else None
            ties = con.execute("SELECT count(*) FROM episode WHERE podcast_id=? AND published IS ?",
                               (pid, boundary[0])).fetchone()[0] if boundary else 0
            report.append({"podcast_id": pid, "stored_rows": total,
                           "sql_purge_candidates": removed,
                           "candidates_by_database_state": {str(r[0]): r[1] for r in counts},
                           "candidates_with_archive_flag": sum(r[2] for r in counts),
                           "equal_timestamps_at_limit": ties > 1})
        return {"effective_limit_assumed_for_each_selected_podcast": limit,
                "sql_purge_disabled": limit == 0,
                "database_state_legend": {"0": "normal", "1": "downloaded", "2": "deleted"},
                "scope": "Separate SQL purge only. Queue/paused state, feed reconciliation and partial files are not checked. Equal timestamps can make the upstream cutoff choice unspecified.",
                "podcasts": report}
    finally:
        con.close()


def main():
    parser = argparse.ArgumentParser(description=__doc__)
    parser.add_argument("database_copy", help="Copy made while gPodder is closed; never upload it")
    parser.add_argument("limit", type=int, help="Effective max_episodes value to examine; 0 disables this SQL path")
    parser.add_argument("--podcast-id", type=int)
    args = parser.parse_args()
    try:
        result = preview(args.database_copy, args.limit, args.podcast_id)
    except (OSError, ValueError, sqlite3.Error) as exc:
        print("No preview produced: " + str(exc), file=sys.stderr)
        return 2
    print(json.dumps(result, indent=2))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())