from __future__ import annotations

from sqlite3 import Connection

from jarvis_finance.audit.log import record_audit_event
from jarvis_finance.services.budget_common import now

UAT12_PREFIX = "UAT12-20260516231955"


def _ids(conn: Connection, table: str, id_col: str, where: str, params: tuple = ()) -> list[str]:
    return [str(r[id_col]) for r in conn.execute(f"SELECT {id_col} FROM {table} WHERE {where}", params).fetchall()]


def cleanup_uat12_budget_data(conn: Connection, *, dry_run: bool = True) -> dict[str, int | bool]:
    account_ids = _ids(conn, "budget_accounts", "budget_account_id", "name LIKE ?", (f"{UAT12_PREFIX}%",))
    category_ids = _ids(conn, "budget_categories", "category_id", "name LIKE ?", (f"{UAT12_PREFIX}%",))
    tag_ids = _ids(conn, "budget_tags", "tag_id", "name LIKE ?", (f"{UAT12_PREFIX}%",))
    tx_where = "description LIKE ? OR COALESCE(payee,'') LIKE ? OR COALESCE(notes,'') LIKE ?"
    tx_params: list[str] = [f"{UAT12_PREFIX}%", f"{UAT12_PREFIX}%", f"%{UAT12_PREFIX}%"]
    if account_ids:
        tx_where += " OR account_id IN (" + ",".join("?" for _ in account_ids) + ")"
        tx_params.extend(account_ids)
    if category_ids:
        tx_where += " OR category_id IN (" + ",".join("?" for _ in category_ids) + ")"
        tx_params.extend(category_ids)
    transaction_ids = _ids(conn, "budget_transactions", "budget_transaction_id", tx_where, tuple(tx_params))
    cand_where = "description LIKE ? OR COALESCE(merchant,'') LIKE ? OR COALESCE(notes,'') LIKE ?"
    cand_params: list[str] = [f"{UAT12_PREFIX}%", f"{UAT12_PREFIX}%", f"%{UAT12_PREFIX}%"]
    if category_ids:
        cand_where += " OR proposed_category_id IN (" + ",".join("?" for _ in category_ids) + ")"
        cand_params.extend(category_ids)
    candidate_ids = _ids(conn, "budget_transaction_candidates", "transaction_candidate_id", cand_where, tuple(cand_params))
    summary = {"dry_run": dry_run, "accounts": len(account_ids), "categories": len(category_ids), "transactions": len(transaction_ids), "tags": len(tag_ids), "candidates": len(candidate_ids), "archived_transactions": 0}
    if dry_run:
        return summary
    ts = now()
    for txid in transaction_ids:
        conn.execute("UPDATE budget_transactions SET status='archived', updated_at=? WHERE budget_transaction_id=?", (ts, txid))
    for aid in account_ids:
        conn.execute("UPDATE budget_accounts SET is_active=0, archived_at=?, updated_at=? WHERE budget_account_id=?", (ts, ts, aid))
    for cid in category_ids:
        conn.execute("UPDATE budget_categories SET is_active=0, updated_at=? WHERE category_id=?", (ts, cid))
    for tid in tag_ids:
        conn.execute("UPDATE budget_tags SET is_active=0, updated_at=? WHERE tag_id=?", (ts, tid))
    for cand in candidate_ids:
        conn.execute("UPDATE budget_transaction_candidates SET status='ignored', notes=COALESCE(notes,'') || ' | UAT12 cleanup archived', updated_at=? WHERE transaction_candidate_id=?", (ts, cand))
    audit_id = record_audit_event(
        conn,
        source="runtime_cleanup",
        action="uat12_budget_cleanup",
        entity_type="budget_runtime_cleanup",
        entity_id=UAT12_PREFIX,
        new_values={k: v for k, v in summary.items() if k != "dry_run"},
        user_text_note="Archived UAT12 budget test data after verified runtime backup.",
        created_by="user",
    )
    conn.commit()
    summary["archived_transactions"] = len(transaction_ids)
    summary["audit_events"] = 1 if audit_id else 0
    return summary