from __future__ import annotations

from sqlite3 import Connection
from typing import Any

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

SOURCE_CLAUSES = {
    "Kreditkarten-CSV": "source_type IN ('credit_card_csv','visa_credit_card') OR lower(coalesce(source_file_label,'')) LIKE '%visa%' OR lower(coalesce(source_file_label,'')) LIKE '%kredit%'",
    "Migros-CSV": "source_type IN ('migros_receipt','migros_receipts') OR lower(coalesce(source_file_label,'')) LIKE '%migros%'",
    "AKB-CSV": "source_type IN ('akb_bank') OR lower(coalesce(source_type,'')) LIKE '%akb%' OR lower(coalesce(source_file_label,'')) LIKE '%akb%' OR lower(coalesce(account_source,'')) LIKE '%akb%'",
    "Raiffeisen-CSV": "source_type IN ('raiffeisen_bank') OR lower(coalesce(source_type,'')) LIKE '%raiffeisen%' OR lower(coalesce(source_file_label,'')) LIKE '%raiffeisen%'",
}

STATUS_CLAUSES = {
    "candidates_total": "1=1",
    "open_candidates": "status IN ('pending','auto_categorized','needs_review')",
    "confirmed_candidates": "status='confirmed'",
    "ignored_candidates": "status='ignored'",
    "duplicate_candidate_blocked": "status IN ('duplicate_candidate','duplicate_blocked','possible_duplicate','duplicate')",
    "covered_by_migros": "status='covered_by_migros'",
    "superseded": "status='superseded'",
    "with_category": "coalesce(proposed_category_id,'')<>''",
    "without_category": "coalesce(proposed_category_id,'')=''",
    "review_needed": "requires_review=1 OR status='needs_review'",
    "wrong_year_or_2025_ref": "status IN ('reference_2025','archived_reference') OR substr(coalesce(transaction_date,''),1,4)='2025'",
}

QUALITY_QUERIES = {
    "candidates_without_category": "SELECT COUNT(*) FROM budget_transaction_candidates WHERE coalesce(proposed_category_id,'')='' AND status NOT IN ('confirmed','ignored','superseded','covered_by_migros','reference_2025','archived_reference')",
    "candidates_low_confidence": "SELECT COUNT(*) FROM budget_transaction_candidates WHERE cast(coalesce(confidence,'0') AS real) < 0.6 AND status NOT IN ('confirmed','ignored','superseded','reference_2025','archived_reference')",
    "galaxus_digitec_candidates": "SELECT COUNT(*) FROM budget_transaction_candidates WHERE lower(coalesce(merchant,'')||' '||coalesce(description,'')) LIKE '%galaxus%' OR lower(coalesce(merchant,'')||' '||coalesce(description,'')) LIKE '%digitec%'",
    "migros_receipts_review_required": "SELECT COUNT(*) FROM budget_transaction_candidates WHERE source_type IN ('migros_receipt','migros_receipts') AND (requires_review=1 OR status IN ('pending','needs_review','auto_categorized'))",
    "duplicates": "SELECT COUNT(*) FROM budget_transaction_candidates WHERE status IN ('duplicate_candidate','duplicate_blocked','possible_duplicate','duplicate')",
    "covered_by_migros": "SELECT COUNT(*) FROM budget_transaction_candidates WHERE status='covered_by_migros'",
    "wrong_year_or_2025_candidates": "SELECT COUNT(*) FROM budget_transaction_candidates WHERE status IN ('reference_2025','archived_reference') OR substr(coalesce(transaction_date,''),1,4)='2025'",
    "candidates_without_account": "SELECT COUNT(*) FROM budget_transaction_candidates WHERE coalesce(account_source,'')='' AND status NOT IN ('confirmed','ignored','superseded','reference_2025','archived_reference')",
    "candidates_without_source": "SELECT COUNT(*) FROM budget_transaction_candidates WHERE coalesce(source_type,'')=''",
    "candidates_without_date": "SELECT COUNT(*) FROM budget_transaction_candidates WHERE coalesce(transaction_date,'')=''",
    "confirmed_transactions_without_category": "SELECT COUNT(*) FROM budget_transactions WHERE status='confirmed' AND coalesce(category_id,'')=''",
    "confirmed_transactions_without_account": "SELECT COUNT(*) FROM budget_transactions WHERE status='confirmed' AND coalesce(account_id,'')=''",
    "confirmed_transactions_without_audit": "SELECT COUNT(*) FROM budget_transactions t WHERE status='confirmed' AND NOT EXISTS (SELECT 1 FROM audit_log a WHERE (a.entity_id=t.budget_transaction_id OR a.entity_id=t.source_candidate_id) AND (a.entity_type LIKE 'budget_transaction%' OR a.action LIKE '%budget%'))",
    "open_fixed_cost_candidates": "SELECT COUNT(*) FROM budget_recurring_payments WHERE status='candidate'",
    "active_fixed_costs_without_last_payment": "SELECT COUNT(*) FROM budget_recurring_payments WHERE status='active' AND coalesce(last_seen_date,'')=''",
    "expected_fixed_cost_payment_missing": "SELECT COUNT(*) FROM budget_recurring_payments WHERE status='active' AND coalesce(next_expected_date,'')<>'' AND date(next_expected_date) < date('now','start of month')",
}


def _count(conn: Connection, sql: str, params: tuple[Any, ...] = ()) -> int:
    return int(conn.execute(sql, params).fetchone()[0] or 0)


def _table_exists(conn: Connection, table_name: str) -> bool:
    return conn.execute("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?", (table_name,)).fetchone() is not None


def _other_source_clause() -> str:
    return "NOT (" + " OR ".join(f"({clause})" for clause in SOURCE_CLAUSES.values()) + ")"


def _alias_clause(clause: str, alias: str) -> str:
    for column in ("source_type", "source_file_label", "account_source"):
        clause = clause.replace(column, f"{alias}.{column}")
    return clause


def _source_summary(conn: Connection, label: str, clause: str) -> dict[str, int]:
    summary: dict[str, int] = {
        "import_sessions": _count(conn, f"SELECT COUNT(DISTINCT coalesce(import_session_id, source_file_label, source_type)) FROM budget_transaction_candidates WHERE {clause}"),
    }
    for key, status_clause in STATUS_CLAUSES.items():
        summary[key] = _count(conn, f"SELECT COUNT(*) FROM budget_transaction_candidates WHERE ({clause}) AND ({status_clause})")

    aliased = _alias_clause(clause, "c")
    summary["effective_budget_transactions"] = _count(
        conn,
        "SELECT COUNT(*) FROM budget_transactions t WHERE EXISTS "
        f"(SELECT 1 FROM budget_transaction_candidates c WHERE c.transaction_candidate_id=t.source_candidate_id AND ({aliased}))",
    )
    if _table_exists(conn, "budget_import_line_items"):
        summary["line_items"] = _count(
            conn,
            "SELECT COUNT(*) FROM budget_import_line_items li "
            "JOIN budget_transaction_candidates c ON c.transaction_candidate_id=li.transaction_candidate_id "
            f"WHERE {aliased}",
        )
    else:
        summary["line_items"] = 0
    return summary


def _source_token_summary(conn: Connection, token: str) -> dict[str, int]:
    like = f"%{token.lower()}%"
    match = "lower(coalesce(source_type,'')||' '||coalesce(source_file_label,'')||' '||coalesce(account_source,'')) LIKE ?"
    return {
        "candidates": _count(conn, f"SELECT COUNT(*) FROM budget_transaction_candidates WHERE {match}", (like,)),
        "confirmed_candidates": _count(conn, f"SELECT COUNT(*) FROM budget_transaction_candidates WHERE status='confirmed' AND {match}", (like,)),
        "effective_transactions": _count(
            conn,
            "SELECT COUNT(*) FROM budget_transactions t WHERE EXISTS "
            f"(SELECT 1 FROM budget_transaction_candidates c WHERE c.transaction_candidate_id=t.source_candidate_id AND {match})",
            (like,),
        ),
    }


def build_import_status_audit(conn: Connection) -> dict[str, Any]:
    """Return a read-only, amount-free import/review/confirm status audit."""
    sources = {label: _source_summary(conn, label, clause) for label, clause in SOURCE_CLAUSES.items()}
    sources["sonstige Quellen"] = _source_summary(conn, "sonstige Quellen", _other_source_clause())
    sources["manuelle Buchungen"] = {
        "import_sessions": 0,
        "candidates_total": 0,
        "open_candidates": 0,
        "confirmed_candidates": 0,
        "ignored_candidates": 0,
        "duplicate_candidate_blocked": 0,
        "covered_by_migros": 0,
        "superseded": 0,
        "with_category": 0,
        "without_category": 0,
        "review_needed": 0,
        "wrong_year_or_2025_ref": 0,
        "effective_budget_transactions": _count(conn, "SELECT COUNT(*) FROM budget_transactions WHERE source_type='manual'"),
        "line_items": 0,
    }
    quality = {key: _count(conn, query) for key, query in QUALITY_QUERIES.items()}
    akb_raiffeisen = {
        "AKB": _source_token_summary(conn, "akb"),
        "Raiffeisen": _source_token_summary(conn, "raiffeisen"),
        "csv_calculated_accounts": _count(conn, "SELECT COUNT(*) FROM accounts WHERE balance_mode='csv_calculated'") if _table_exists(conn, "accounts") else 0,
        "csv_anchor_snapshots": _count(conn, "SELECT COUNT(*) FROM cash_account_snapshots WHERE snapshot_type='csv_anchor_balance'") if _table_exists(conn, "cash_account_snapshots") else 0,
    }
    return {"sources": sources, "quality": quality, "akb_raiffeisen": akb_raiffeisen}

BLOCKED_CONFIRM_STATUSES = {
    "confirmed",
    "ignored",
    "covered_by_migros",
    "covered_by_source",
    "auto_ignored_duplicate",
    "duplicate",
    "duplicate_candidate",
    "possible_duplicate",
    "duplicate_blocked",
    "superseded",
    "reference_2025",
    "archived_reference",
    "already_processed",
}
SUBSCRIPTION_TERMS = ("apple", "google", "netflix", "disney", "amazon prime", "openai", "chatgpt")


def _safe_candidate_where(extra: str = "") -> str:
    blocked = ",".join(f"'{s}'" for s in sorted(BLOCKED_CONFIRM_STATUSES))
    where = f"status NOT IN ({blocked}) AND substr(coalesce(transaction_date,''),1,4)='2026'"
    if extra:
        where += f" AND ({extra})"
    return where


def _candidate_ids(conn: Connection, where: str, limit: int = 200) -> list[str]:
    return [str(r[0]) for r in conn.execute(f"SELECT transaction_candidate_id FROM budget_transaction_candidates WHERE {where} ORDER BY transaction_date, created_at LIMIT ?", (limit,)).fetchall()]


def _category_distribution(conn: Connection, where: str) -> dict[str, int]:
    rows = conn.execute(
        f"""
        SELECT coalesce(proposed_category_name, 'ohne Kategorie') AS category, COUNT(*) AS count
        FROM budget_transaction_candidates
        WHERE {where}
        GROUP BY coalesce(proposed_category_name, 'ohne Kategorie')
        ORDER BY count DESC, category ASC
        """
    ).fetchall()
    return {str(r["category"]): int(r["count"]) for r in rows}


def _count_where(conn: Connection, where: str) -> int:
    return _count(conn, f"SELECT COUNT(*) FROM budget_transaction_candidates WHERE {where}")


def _merchant_groups(conn: Connection, where: str, limit: int = 20) -> list[dict[str, Any]]:
    rows = conn.execute(
        f"""
        SELECT lower(coalesce(merchant, description, 'Unbekannt')) AS merchant_key,
               coalesce(merchant, description, 'Unbekannt') AS merchant_name,
               COUNT(*) AS count,
               group_concat(DISTINCT source_type) AS sources,
               group_concat(transaction_candidate_id) AS candidate_ids
        FROM budget_transaction_candidates
        WHERE {where}
        GROUP BY lower(coalesce(merchant, description, 'Unbekannt'))
        ORDER BY count DESC, max(CAST(coalesce(amount_original,'0') AS REAL)) DESC, merchant_name ASC
        LIMIT ?
        """,
        (limit,),
    ).fetchall()
    groups: list[dict[str, Any]] = []
    for row in rows:
        groups.append(
            {
                "merchant_key": str(row["merchant_key"]),
                "merchant_name": str(row["merchant_name"]),
                "count": int(row["count"]),
                "sources": [s for s in str(row["sources"] or "").split(",") if s],
                "candidate_ids": [s for s in str(row["candidate_ids"] or "").split(",") if s][:100],
            }
        )
    return groups


def mark_2025_candidates_as_reference(conn: Connection) -> dict[str, Any]:
    """Explicit, audited mutation for moving 2025 candidates out of active review."""
    rows = conn.execute(
        """
        SELECT transaction_candidate_id, status FROM budget_transaction_candidates
        WHERE substr(coalesce(transaction_date,''),1,4)='2025'
          AND status NOT IN ('confirmed','ignored','superseded','covered_by_migros','covered_by_source','reference_2025','archived_reference')
        """
    ).fetchall()
    if not rows:
        return {"marked_count": 0, "audit_id": None}
    ts = now()
    ids = [str(r["transaction_candidate_id"]) for r in rows]
    conn.executemany(
        "UPDATE budget_transaction_candidates SET status='reference_2025', requires_review=0, updated_at=? WHERE transaction_candidate_id=?",
        [(ts, cid) for cid in ids],
    )
    audit_id = record_audit_event(
        conn,
        source="vue_dashboard",
        action="mark_2025_candidates_reference",
        entity_type="budget_transaction_candidate",
        entity_id="review_backlog_2025_reference",
        old_values={"candidate_count": len(ids)},
        new_values={"status": "reference_2025", "candidate_ids": ids[:50]},
        created_by="user",
    )
    conn.commit()
    return {"marked_count": len(ids), "audit_id": audit_id}


def build_review_backlog_dashboard(conn: Connection) -> dict[str, Any]:
    """Read-only Review Backlog cockpit: counts, groups and safe confirm previews only."""
    audit = build_import_status_audit(conn)
    quality = audit["quality"]
    total_candidates = _count(conn, "SELECT COUNT(*) FROM budget_transaction_candidates")
    open_candidates = _count(conn, "SELECT COUNT(*) FROM budget_transaction_candidates WHERE status IN ('pending','auto_categorized','needs_review','transfer_candidate') AND substr(coalesce(transaction_date,''),1,4)='2026'")
    header = {
        "candidates_total": total_candidates,
        "open_candidates": open_candidates,
        "candidates_without_category": quality["candidates_without_category"],
        "low_confidence": quality["candidates_low_confidence"],
        "duplicates": quality["duplicates"],
        "covered_by_migros": quality["covered_by_migros"],
        "reference_2025": quality["wrong_year_or_2025_candidates"],
        "galaxus_digitec": quality["galaxus_digitec_candidates"],
        "migros_review": quality["migros_receipts_review_required"],
        "akb": audit["akb_raiffeisen"]["AKB"]["candidates"],
        "raiffeisen": audit["akb_raiffeisen"]["Raiffeisen"]["candidates"],
        "fixed_cost_candidates": quality["open_fixed_cost_candidates"],
    }
    without_category_where = _safe_candidate_where("coalesce(proposed_category_id,'')='' AND status IN ('pending','auto_categorized','needs_review','transfer_candidate')")
    galaxus_where = _safe_candidate_where("lower(coalesce(merchant,'')||' '||coalesce(description,'')) LIKE '%galaxus%' OR lower(coalesce(merchant,'')||' '||coalesce(description,'')) LIKE '%digitec%' OR classification='galaxus_review'")
    migros_where = _safe_candidate_where("source_type IN ('migros_receipt','migros_receipts') AND coalesce(proposed_category_id,'')<>''")
    akb_where = _safe_candidate_where("source_type='akb_bank'")
    raiff_where = _safe_candidate_where("source_type='raiffeisen_bank'")
    dup_where = "status IN ('duplicate','duplicate_candidate','possible_duplicate','duplicate_blocked')"
    ref_2025_where = "status IN ('reference_2025','archived_reference') OR substr(coalesce(transaction_date,''),1,4)='2025'"
    subscription_text = " OR ".join([f"lower(coalesce(merchant,'')||' '||coalesce(description,'')) LIKE '%{term}%'" for term in SUBSCRIPTION_TERMS])
    safe_subscription_where = _safe_candidate_where(f"coalesce(proposed_category_id,'')<>'' AND ({subscription_text})")
    clear_category_where = _safe_candidate_where("coalesce(proposed_category_id,'')<>'' AND CAST(coalesce(confidence,'0') AS REAL) >= 0.85")
    groups = {
        "without_category": {"count": _count_where(conn, without_category_where), "merchant_groups": _merchant_groups(conn, without_category_where)},
        "safe_subscriptions": {"count": _count_where(conn, safe_subscription_where), "merchant_groups": _merchant_groups(conn, safe_subscription_where), "candidate_ids": _candidate_ids(conn, safe_subscription_where), "category_distribution": _category_distribution(conn, safe_subscription_where), "warnings": ["Preview only: kein Confirm ohne expliziten Klick."]},
        "migros_bons": {"count": _count_where(conn, migros_where), "merchant_groups": _merchant_groups(conn, migros_where), "candidate_ids": _candidate_ids(conn, migros_where), "category_distribution": _category_distribution(conn, migros_where), "warnings": ["Nur Bon-Hauptkandidaten; Kreditkarten-Migros bleibt covered_by_migros."]},
        "galaxus_digitec": {"count": _count_where(conn, galaxus_where), "merchant_groups": _merchant_groups(conn, galaxus_where), "suggested_categories": ["Haushalt", "Elektronik", "Kinder/Familie", "Geschenke", "Sonstiges / Administration", "Review nötig"]},
        "akb_raiffeisen": {"akb_count": _count_where(conn, akb_where), "raiffeisen_count": _count_where(conn, raiff_where), "akb_groups": _merchant_groups(conn, akb_where), "raiffeisen_groups": _merchant_groups(conn, raiff_where), "income_count": _count_where(conn, _safe_candidate_where("source_type IN ('akb_bank','raiffeisen_bank') AND classification IN ('income_candidate','possible_income')")), "transfer_count": _count_where(conn, _safe_candidate_where("source_type IN ('akb_bank','raiffeisen_bank') AND (classification LIKE '%transfer%' OR status='transfer_candidate')"))},
        "duplicates": {"count": _count_where(conn, dup_where), "merchant_groups": _merchant_groups(conn, dup_where)},
        "reference_2025": {"count": _count_where(conn, ref_2025_where), "merchant_groups": _merchant_groups(conn, ref_2025_where)},
        "fixkosten": {"count": _count(conn, "SELECT COUNT(*) FROM budget_recurring_payments WHERE status='candidate'"), "candidates": [dict(r) for r in conn.execute("SELECT recurring_id, name, merchant_name, frequency, status, confidence, category_id FROM budget_recurring_payments WHERE status='candidate' ORDER BY confidence DESC, updated_at DESC LIMIT 50").fetchall()]},
    }
    confirm_groups = {
        "safe_subscriptions": groups["safe_subscriptions"],
        "migros_bons": groups["migros_bons"],
        "clear_categories": {"count": _count_where(conn, clear_category_where), "candidate_ids": _candidate_ids(conn, clear_category_where), "category_distribution": _category_distribution(conn, clear_category_where), "warnings": ["Schließt Duplikate, covered, 2025 und Kandidaten ohne Kategorie aus."]},
    }
    return {"purpose": "review_backlog_cleanup_v1", "audit": audit, "header": header, "groups": groups, "confirm_groups": confirm_groups}