from __future__ import annotations

import json
import subprocess
from collections import Counter, defaultdict
from datetime import datetime
from pathlib import Path
from sqlite3 import Connection
from typing import Any
from urllib.parse import urlencode

from jarvis_finance.audit.log import record_audit_event
from jarvis_finance.services.budget_common import new_id, now, row_to_dict
from jarvis_finance.services.budget_csv_imports import detect_import_profile, import_budget_csv_text, parse_budget_csv_text
from jarvis_finance.services.budget_imports import _normalise, _normalized_merchant_name, candidate_user_count_summary, create_budget_rule

OPEN_STATUSES = {"pending", "auto_categorized", "ready_for_confirm", "needs_review", "duplicate_candidate", "transfer_candidate"}
REVIEW_HIDDEN_STATUSES = {"confirmed", "ignored", "covered_by_migros", "covered_by_source", "auto_ignored_duplicate", "superseded", "reference_2025", "archived_reference"}


def _safe_file_name(name: str) -> str:
    return Path(str(name or "budget.csv")).name[:180]


def _source_from_profile(profile: str | None, name: str = "") -> str:
    text = f"{profile or ''} {name}".lower()
    if "visa" in text:
        return "VISA"
    if "migros" in text or "cumulus" in text:
        return "Migros"
    if "raiffeisen" in text:
        return "Raiffeisen"
    if "akb" in text or "kantonal" in text:
        return "AKB"
    return "unknown"


def _file_text(file: dict[str, Any]) -> str:
    if "text" in file:
        return str(file.get("text") or "")
    path = file.get("path") or file.get("local_path")
    if path:
        return Path(path).read_text(encoding=file.get("encoding") or "utf-8-sig", errors="replace")
    return ""


def scan_budget_drive_files(files: list[dict[str, Any]] | None = None) -> dict[str, Any]:
    """Classify already discovered Drive file metadata/text. No writes."""
    scanned = []
    for f in files or []:
        name = _safe_file_name(str(f.get("name") or f.get("filename") or "budget.csv"))
        profile = None
        rows_total = 0
        errors: list[str] = []
        text = _file_text(f)
        try:
            if text.strip():
                parsed = parse_budget_csv_text(text)
                profile = parsed["profile"]
                rows_total = len(parsed["rows"])
            else:
                lowered = name.lower()
                if "visa" in lowered:
                    profile = "visa_credit_card"
                elif "migros" in lowered or "cumulus" in lowered:
                    profile = "migros_receipts"
                elif "raiffeisen" in lowered:
                    profile = "raiffeisen_bank"
                elif "akb" in lowered or "kantonal" in lowered:
                    profile = "akb_bank"
        except Exception as exc:  # malformed file remains visible, not fatal
            errors.append(type(exc).__name__)
        scanned.append({
            "file_id": str(f.get("id") or f.get("file_id") or ""),
            "name": name,
            "modifiedTime": f.get("modifiedTime") or f.get("modified_time"),
            "profile": profile,
            "source": _source_from_profile(profile, name),
            "rows_total": rows_total,
            "status": "recognized" if profile else "unsupported",
            "errors": errors,
        })
    return {"purpose": "budget_monthly_import_drive_scan", "folder": "Finanzen/03 Budget", "files": scanned, "recognized_count": sum(1 for f in scanned if f["profile"])}


def scan_google_drive_budget_folder() -> dict[str, Any]:
    """Read-only metadata scan via gog if available. It never downloads/imports contents."""
    cmd = ["gog", "-a", "friday.uplink@gmail.com", "drive", "search", "Finanzen/03 Budget"]
    try:
        proc = subprocess.run(cmd, text=True, capture_output=True, timeout=45, check=False)
        return {"purpose": "budget_monthly_import_drive_scan", "folder": "Finanzen/03 Budget", "status": "available" if proc.returncode == 0 else "unavailable", "raw_count_hint": len(proc.stdout.splitlines()), "files": []}
    except Exception as exc:
        return {"purpose": "budget_monthly_import_drive_scan", "folder": "Finanzen/03 Budget", "status": "unavailable", "error": type(exc).__name__, "files": []}


def _review_url(source: str | None = None, session_id: str | None = None, status: str | None = None) -> str:
    params = {"source": source or "", "import_session": session_id or "", "status": status or ""}
    return "/planning/budget/expenses/review?" + urlencode({k: v for k, v in params.items() if v})


def _insert_session(conn: Connection, *, file: dict[str, Any], profile: str | None, rows_total: int, result: dict[str, Any], status: str, review_url: str) -> str:
    sid = new_id("bimport")
    ts = now()
    source = _source_from_profile(profile, str(file.get("name") or ""))
    conn.execute(
        """
        INSERT INTO budget_import_sessions(import_session_id,source,file_id,file_name,file_modified_time,file_period_start,file_period_end,profile,rows_total,new_candidates,already_known,duplicate_count,ignored_count,covered_by_source_count,error_count,status,review_url,created_at,updated_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """,
        (
            sid, source, str(file.get("id") or file.get("file_id") or ""), _safe_file_name(str(file.get("name") or "budget.csv")), file.get("modifiedTime") or file.get("modified_time"),
            result.get("period_start"), result.get("period_end"), profile, int(rows_total or 0), int(result.get("candidate_count") or result.get("would_create_candidate_count") or result.get("receipt_candidate_count") or 0), int(result.get("already_processed_count") or 0), int(result.get("possible_duplicate_count") or result.get("duplicate_count") or 0), int(result.get("ignored_count") or 0), int(result.get("covered_by_migros_count") or result.get("covered_by_source_count") or 0), int(result.get("error_count") or 0), status, review_url, ts, ts,
        ),
    )
    return sid


def _sum_totals(items: list[dict[str, Any]]) -> dict[str, int]:
    keys = ["would_create_candidate_count", "candidate_count", "already_processed_count", "possible_duplicate_count", "ignored_count", "covered_by_source_count", "receipt_candidate_count", "line_item_count", "income_candidate_count", "transfer_candidate_count"]
    return {k: sum(int(i.get(k) or 0) for i in items) for k in keys}


def preview_drive_monthly_import(conn: Connection, files: list[dict[str, Any]], *, dry_run: bool = True, default_category_id: str | None = None, income_category_id: str | None = None) -> dict[str, Any]:
    file_results: list[dict[str, Any]] = []
    scan = scan_budget_drive_files(files)
    session_ids: list[str] = []
    for scanned, f in zip(scan["files"], files):
        profile = scanned.get("profile")
        if not profile:
            result = {"profile": None, "error_count": 1, "ignored_count": 1}
            status = "failed"
        else:
            result = import_budget_csv_text(conn, str(profile), _file_text(f), source_file_label=_safe_file_name(str(f.get("name") or "budget.csv")), dry_run=dry_run, default_category_id=default_category_id, income_category_id=income_category_id)
            status = "dry_run" if dry_run else "candidates_created"
        review = _review_url(scanned.get("source"), None, "ready_for_confirm")
        sid = _insert_session(conn, file=f, profile=profile, rows_total=int(scanned.get("rows_total") or result.get("parsed_rows") or 0), result=result, status=status, review_url=review)
        session_ids.append(sid)
        if not dry_run:
            conn.execute("UPDATE budget_transaction_candidates SET import_session_id=? WHERE source_file_label=? AND import_session_id IS NULL", (sid, _safe_file_name(str(f.get("name") or "budget.csv"))))
        result = dict(result) | {"import_session_id": sid, "source": scanned.get("source"), "file_name": scanned.get("name"), "review_url": _review_url(scanned.get("source"), sid, "ready_for_confirm")}
        file_results.append(result)
    conn.commit()
    return {"purpose": "budget_monthly_import_workflow_v1", "status": "dry_run" if dry_run else "candidates_created", "files_total": len(files), "session_ids": session_ids, "files": file_results, "totals": _sum_totals(file_results), "review_url": _review_url(session_id=session_ids[-1] if session_ids else None, status="ready_for_confirm")}


def run_monthly_import_dry_run(conn: Connection, files: list[dict[str, Any]], *, default_category_id: str | None = None, income_category_id: str | None = None) -> dict[str, Any]:
    return preview_drive_monthly_import(conn, files, dry_run=True, default_category_id=default_category_id, income_category_id=income_category_id)


def get_import_history(conn: Connection, *, limit: int = 30) -> list[dict[str, Any]]:
    rows = conn.execute("SELECT * FROM budget_import_sessions ORDER BY created_at DESC, import_session_id DESC LIMIT ?", (limit,)).fetchall()
    return [row_to_dict(r) for r in rows]


def get_monthly_import_dashboard(conn: Connection, *, budget_year: str = "2026") -> dict[str, Any]:
    sessions = get_import_history(conn, limit=100)
    latest_by_source: dict[str, dict[str, Any]] = {}
    for s in sessions:
        latest_by_source.setdefault(str(s.get("source") or "unknown"), s)
    user_counts = candidate_user_count_summary(conn, budget_year=budget_year)
    open_rows = conn.execute("SELECT source_type,status,classification,COUNT(*) AS count FROM budget_transaction_candidates WHERE substr(COALESCE(transaction_date,''),1,4)=? AND status IN ('pending','auto_categorized','needs_review','transfer_candidate') GROUP BY source_type,status,classification", (budget_year,)).fetchall()
    by_source: dict[str, int] = defaultdict(int)
    counts = Counter(user_counts)
    for r in open_rows:
        by_source[str(r["source_type"] or "unknown")] += int(r["count"] or 0)
        if r["status"]:
            counts[str(r["status"])] += int(r["count"] or 0)
        if r["classification"]:
            counts[str(r["classification"])] += int(r["count"] or 0)
    suggestions = conn.execute("SELECT COUNT(*) FROM budget_rule_suggestions WHERE status IN ('suggested','later')").fetchone()[0]
    return {"purpose": "budget_import_dashboard_v2", "workflow": "upload", "latest_import_by_source": latest_by_source, "open_candidates_by_source": dict(by_source), "counts": dict(counts), "user_counts": user_counts, "rule_suggestions_open": int(suggestions or 0), "review_url": _review_url(status="open")}


def _candidate(conn: Connection, candidate_id: str):
    row = conn.execute("SELECT * FROM budget_transaction_candidates WHERE transaction_candidate_id=?", (candidate_id,)).fetchone()
    if not row:
        raise ValueError("candidate not found")
    return row


def _affected_count(conn: Connection, merchant_pattern: str, source_scope: str, exclude_id: str | None = None) -> int:
    source_clause = "" if source_scope in {"", "all"} else " AND lower(source_type) LIKE ?"
    params: list[Any] = [f"%{_normalise(merchant_pattern)}%", f"%{_normalise(merchant_pattern)}%"]
    if source_clause:
        params.append(f"%{source_scope.lower()}%")
    if exclude_id:
        source_clause += " AND transaction_candidate_id<>?"; params.append(exclude_id)
    sql = "SELECT COUNT(*) FROM budget_transaction_candidates WHERE status NOT IN ('confirmed','ignored','covered_by_migros','covered_by_source','auto_ignored_duplicate','superseded') AND (lower(COALESCE(merchant,'')) LIKE ? OR lower(COALESCE(description,'')) LIKE ?)" + source_clause
    return int(conn.execute(sql, params).fetchone()[0] or 0)


def create_rule_suggestion_from_candidate_change(conn: Connection, candidate_id: str, *, category_id: str, source_scope: str = "all", recurring_type: str | None = None) -> dict[str, Any]:
    row = _candidate(conn, candidate_id)
    cat = conn.execute("SELECT name FROM budget_categories WHERE category_id=?", (category_id,)).fetchone()
    merchant = str(row["merchant"] or row["description"] or "").strip()
    pattern = _normalized_merchant_name(merchant).upper() or merchant.upper()
    affected = _affected_count(conn, pattern, source_scope, exclude_id=candidate_id)
    sid = new_id("rulesug")
    ts = now()
    conn.execute("""
        INSERT INTO budget_rule_suggestions(suggestion_id,example_candidate_id,merchant_pattern,description_pattern,match_type,source_scope,category_id,category_name,recurring_type,amount_min_text,amount_max_text,confidence,affected_open_candidate_count,status,notes,created_at,updated_at)
        VALUES (?, ?, ?, ?, 'contains', ?, ?, ?, ?, NULL, NULL, ?, ?, 'suggested', ?, ?, ?)
    """, (sid, candidate_id, pattern, str(row["description"] or "")[:180], source_scope or "all", category_id, cat["name"] if cat else None, recurring_type, "0.84" if affected else "0.70", affected, json.dumps({"trigger": "manual_category_change"}), ts, ts))
    audit_id = record_audit_event(conn, source="vue_dashboard", action="budget_rule_suggestion_created", entity_type="budget_rule_suggestion", entity_id=sid, new_values={"merchant_pattern": pattern, "category_id": category_id, "source_scope": source_scope}, created_by="user")
    conn.commit()
    return row_to_dict(conn.execute("SELECT * FROM budget_rule_suggestions WHERE suggestion_id=?", (sid,)).fetchone()) | {"audit_id": audit_id}


def list_rule_suggestions(conn: Connection, *, status: str | None = None) -> list[dict[str, Any]]:
    if status:
        rows = conn.execute("SELECT * FROM budget_rule_suggestions WHERE status=? ORDER BY created_at DESC", (status,)).fetchall()
    else:
        rows = conn.execute("SELECT * FROM budget_rule_suggestions ORDER BY created_at DESC").fetchall()
    return [row_to_dict(r) for r in rows]


def apply_rule_suggestion(conn: Connection, suggestion_id: str, *, apply_to_open: bool = False) -> dict[str, Any]:
    row = conn.execute("SELECT * FROM budget_rule_suggestions WHERE suggestion_id=?", (suggestion_id,)).fetchone()
    if not row:
        raise ValueError("rule suggestion not found")
    rule = create_budget_rule(conn, {"merchant_contains": row["merchant_pattern"], "source_type": None if row["source_scope"] in {"", "all"} else row["source_scope"], "category_id": row["category_id"], "target_status": "review", "notes": "created_from_rule_suggestion"})
    updated = 0
    if apply_to_open:
        params: list[Any] = [f"%{_normalise(row['merchant_pattern'])}%", f"%{_normalise(row['merchant_pattern'])}%"]
        source_clause = ""
        if row["source_scope"] not in {"", "all", None}:
            source_clause = " AND lower(source_type) LIKE ?"; params.append(f"%{str(row['source_scope']).lower()}%")
        params.extend([row["category_id"], row["category_name"], rule["rule_id"], row["merchant_pattern"], now()])
        sql = """
            UPDATE budget_transaction_candidates
            SET proposed_category_id=?, proposed_category_name=?, rule_id=?, rule_name=?, updated_at=?
            WHERE status NOT IN ('confirmed','ignored','covered_by_migros','covered_by_source','auto_ignored_duplicate','superseded')
              AND (lower(COALESCE(merchant,'')) LIKE ? OR lower(COALESCE(description,'')) LIKE ?)
        """
        # SQLite parameter order: SET params first, WHERE params last
        set_params = [row["category_id"], row["category_name"], rule["rule_id"], row["merchant_pattern"], now()]
        where_params = [f"%{_normalise(row['merchant_pattern'])}%", f"%{_normalise(row['merchant_pattern'])}%"]
        if source_clause:
            where_params.append(f"%{str(row['source_scope']).lower()}%")
        cur = conn.execute(sql + source_clause, set_params + where_params)
        updated = int(cur.rowcount or 0)
    conn.execute("UPDATE budget_rule_suggestions SET status='accepted', updated_at=? WHERE suggestion_id=?", (now(), suggestion_id))
    audit_id = record_audit_event(conn, source="vue_dashboard", action="budget_rule_suggestion_accepted", entity_type="budget_rule_suggestion", entity_id=suggestion_id, new_values={"apply_to_open": apply_to_open, "updated_candidate_count": updated}, created_by="user")
    conn.commit()
    return {"status": "accepted", "suggestion_id": suggestion_id, "rule_id": rule["rule_id"], "updated_candidate_count": updated, "audit_id": audit_id}


def reject_rule_suggestion(conn: Connection, suggestion_id: str, *, later: bool = False) -> dict[str, Any]:
    status = "later" if later else "rejected"
    conn.execute("UPDATE budget_rule_suggestions SET status=?, updated_at=? WHERE suggestion_id=?", (status, now(), suggestion_id))
    audit_id = record_audit_event(conn, source="vue_dashboard", action="budget_rule_suggestion_rejected" if not later else "budget_rule_suggestion_later", entity_type="budget_rule_suggestion", entity_id=suggestion_id, new_values={"status": status}, created_by="user")
    conn.commit()
    return {"status": status, "suggestion_id": suggestion_id, "audit_id": audit_id}