"""Maintenance commands for the OCR database.

    python -m app.ocr.maintenance clear-merges            # back up the DB, then delete every saved merge
    python -m app.ocr.maintenance clear-judgements [--all] # back up the DB, then delete the old cached Gemini 'no' rows
    python -m app.ocr.maintenance backfill-events --user Fin001  # back up the DB, then log the stored history as that user's events
    python -m app.ocr.maintenance reset-reviews [--no-backup]  # back up, delete every review item, rebuild the extraction ones, mark every merge invalid
    python -m app.ocr.maintenance relabel CODE|--all      # deterministic scope / value kind / unit
    python -m app.ocr.maintenance merge-report CODE       # read-only: what the series linker would do
    python -m app.ocr.maintenance label-audit CODE        # read-only: scope / statement label flip rate

clear-merges keeps extracted metrics, raw CSVs, pipeline runs and user overrides.
merge-report changes nothing and never calls Gemini (Gemini answers are never cached).
"""

import argparse
import copy
import re
import sqlite3
import sys
from collections import Counter
from datetime import datetime
from pathlib import Path

from dotenv import load_dotenv

load_dotenv()

from . import config, database  # noqa: E402
from .services import series_keys as sk  # noqa: E402
from .services import series_linker  # noqa: E402


def _backup() -> Path:
    dest = Path(config.DB_PATH).with_name(f"financial_ocr.backup-{datetime.now():%Y%m%d-%H%M%S}.db")
    src = sqlite3.connect(config.DB_PATH)
    dst = sqlite3.connect(dest)
    src.backup(dst)
    dst.close()
    src.close()
    return dest


def _companies() -> list:
    with database.get_connection() as conn:
        return [r[0] for r in conn.execute("SELECT DISTINCT instrument_code FROM financial_metrics ORDER BY 1")]


def _relabel_in_memory(metrics: list, pages_md: dict) -> list:
    """Same rules as database.relabel_company (metric type and unit), applied to a copy (nothing is written)."""
    metrics = copy.deepcopy(metrics)
    for m in metrics:
        units = [o.get("unit") for o in m["data"]]
        kind = sk.value_kind(f"{m.get('raw_field') or ''} {m.get('field') or ''}", units, m.get("metric_type"),
                             [o.get("numeric_value") for o in m["data"]])
        m["metric_type"] = kind
        for o in m["data"]:
            o["unit"] = sk.unit_for_kind(kind, units)
    return metrics


_ENTITY_SUFFIX_RE = re.compile(r"\s*[-–—]\s*[^-–—]*\b(limited|ltd|plc|bank|inc|corp)\b.*$", re.I)


def label_audit(code: str, show: int = 12) -> None:
    """How consistently the extraction model labels scope and statement type across reports.

    For every section heading printed in 2+ reports (company names stripped), counts how often its
    statement type or scope differs between reports (the flip rate), and lists the metrics whose
    label evidence failed at extraction."""
    code = code.upper().strip()
    metrics = database.get_all_metrics_for_company(code)
    by_heading = {}
    failed = Counter()
    for m in metrics:
        if m.get("verification_flag") == "LABEL_EVIDENCE_FAILED":
            failed[m["fiscal_year"]] += 1
        cat = (m.get("category") or "").strip()
        if not cat:
            continue
        own_entity = bool(_ENTITY_SUFFIX_RE.search(cat))
        key = (sk.label_key(_ENTITY_SUFFIX_RE.sub("", cat)), own_entity)
        by_heading.setdefault(key, {}).setdefault(m["fiscal_year"], set()).add((m["statement_type"], m["scope"]))
    multi = {k: v for k, v in by_heading.items() if len(v) >= 2}
    stmt_flips = [k for k, v in multi.items() if len({s for labels in v.values() for s, _ in labels}) > 1]
    scope_flips = [k for k, v in multi.items() if len({sc for labels in v.values() for _, sc in labels}) > 1]
    n = max(1, len(multi))
    print(f"== {code}: {len(metrics)} metrics, {len(multi)} headings printed in 2+ reports")
    print(f"   statement-type flips: {len(stmt_flips)} ({len(stmt_flips) / n:.0%})   "
          f"scope flips: {len(scope_flips)} ({len(scope_flips) / n:.0%})")
    print(f"   metrics with failed label evidence: {sum(failed.values())} {dict(sorted(failed.items()))}")
    for k in (stmt_flips + [k for k in scope_flips if k not in stmt_flips])[:show]:
        labels = {fy: sorted(v) for fy, v in sorted(multi[k].items())}
        print(f"     '{k[0]}'{' (company-only heading)' if k[1] else ''}: " +
              ", ".join(f"{fy} {'/'.join(f'{s[:5]}:{sc[:4]}' for s, sc in v)}" for fy, v in labels.items()))


def merge_report(code: str) -> None:
    code = code.upper().strip()
    pages_md = database.get_pages_markdown(code)
    metrics = _relabel_in_memory(database.get_all_metrics_for_company(code), pages_md)
    nodes = series_linker.build_nodes(metrics, pages_md, database.get_company_info(code).get("name"), code)
    try:
        overrides = database.load_merge_overrides(code, source="user")
    except sqlite3.OperationalError:  # series tables not created yet (server not restarted)
        overrides = []
    res = series_linker.link_company(nodes, overrides, judge=None)
    series, links, chain_review = series_linker.build_series(nodes, res["links"])
    reports = sorted({n["report"] for n in nodes})
    multi = [s for s in series if len(s["nodes"]) >= 3]
    renamed = [s for s in series if len({n["label"] for n in s["nodes"]}) > 1]
    review = Counter(i["kind"] for i in res["review_items"] + chain_review)
    print(f"== {code}: {len(nodes)} line items in {len(reports)} reports, "
          f"{len({n['partition'] for n in nodes})} partitions (statement x scope x entity x segment)")
    print(f"   links: {len(links)}  by rule: {dict(Counter(l['rule'] for l in links))}")
    print(f"   series: {len(series)}  spanning >=3 reports: {len(multi)}  joining different names: {len(renamed)}")
    print(f"   ambiguous links for Gemini: {res['questions_total']} (not sent by this report; Gemini answers are never cached)")
    print(f"   review items (without Gemini answers): {dict(review)}")
    for s in renamed[:15]:
        names = list(dict.fromkeys(n["fields"][0] for n in s["nodes"] if n["fields"]))
        print(f"     {s['partition'].split('|')[0]:<18} {' -> '.join(names)}")


def backfill_series_cells(code: str = "") -> None:
    """Fills series_cell (printed cell -> series of the last merge) for companies merged before that table existed,
    without a new merge and without Gemini: the line items are rebuilt from the stored metrics and matched to the
    saved series by their members. One company, or every company that has a saved merge."""
    dest = _backup()
    database.init_db()                    # creates series_cell in a database from before it existed
    with database.get_connection() as conn:
        codes = [code.upper().strip()] if code else [r[0] for r in conn.execute("SELECT DISTINCT instrument_code FROM series_member")]
    print(f"Backup written to {dest}")
    for c in codes:
        pages_md = database.get_pages_markdown(c)
        metrics = database.get_all_metrics_for_company(c)
        nodes = series_linker.build_nodes(metrics, pages_md, database.get_company_info(c).get("name"), c)
        print(f"{c}: {database.rebuild_series_cells(c, nodes)} cells from {len(nodes)} line items")


def clear_judgements(everything: bool = False) -> None:
    """Deletes the old cached Gemini verdicts (table pair_judgement, no longer read or written): the permanent "no"
    rows, or every row with everything=True. The database is backed up first."""
    dest = _backup()
    with database.get_connection() as conn:
        where = "" if everything else " WHERE verdict = 0"
        per_company = conn.execute(f"SELECT instrument_code, COUNT(*) FROM pair_judgement{where} GROUP BY 1").fetchall()
        conn.execute(f"DELETE FROM pair_judgement{where}")
        conn.commit()
        left = conn.execute("SELECT COUNT(*) FROM pair_judgement").fetchone()[0]
    print(f"Backup written to {dest}")
    print("Deleted " + ("every" if everything else "the verdict = 0") + " row(s): " +
          (", ".join(f"{c} {n}" for c, n in per_company) or "none") + f". Rows left: {left}.")


def backfill_events(user: str) -> None:
    """Records the history already stored (uploads, runs, overrides, review decisions, line names) as events of one user."""
    dest = _backup()
    counts = database.backfill_events(user)
    print(f"Backup written to {dest}")
    print(f"Recorded {sum(counts.values())} event(s) for {user}: {counts}" if counts
          else "Nothing recorded (backfill events already exist, or there is no history).")


def reset_reviews(backup: bool = True) -> None:
    """Backs up the database, deletes every review item (all statuses), makes the extraction review items again from the stored
    metrics and marks every saved merge invalid (`merged_stale` is true until the company is merged again)."""
    if backup:
        print(f"Backup written to {_backup()}")
    with database.get_connection() as conn:
        removed = conn.execute("DELETE FROM merge_review_item").rowcount
        conn.commit()
    made = database.rebuild_extraction_review_items()
    now = datetime.utcnow().isoformat()
    with database.get_connection() as conn:
        merged = conn.execute("""UPDATE company_results SET merge_invalidated_at = ?
                                 WHERE merged_rows_json IS NOT NULL AND merged_rows_json != ''""", (now,)).rowcount
        conn.commit()
    print(f"Removed {removed} review item(s); made {made} extraction review item(s); marked {merged} merge(s) invalid.")


def main(argv=None) -> int:
    ap = argparse.ArgumentParser(prog="python -m app.ocr.maintenance", description=__doc__,
                                 formatter_class=argparse.RawDescriptionHelpFormatter)
    sub = ap.add_subparsers(dest="cmd", required=True)
    p_sc = sub.add_parser("backfill-series-cells", help="back up the database, then fill series_cell for merged companies (no Gemini)")
    p_sc.add_argument("--code", default="", help="one company (default: every merged company)")
    sub.add_parser("clear-merges", help="back up the database, then delete every saved merge")
    p_cj = sub.add_parser("clear-judgements", help="back up the database, then delete the old cached Gemini verdicts")
    p_cj.add_argument("--all", action="store_true", help="delete positive verdicts too (default: only the 'no' rows)")
    p_bf = sub.add_parser("backfill-events", help="back up the database, then record the stored history as events of one user")
    p_bf.add_argument("--user", required=True, help="username to attribute the history to (e.g. Fin001)")
    p_rr = sub.add_parser("reset-reviews", help="back up the database, delete every review item, rebuild the extraction ones, mark every merge invalid")
    p_rr.add_argument("--no-backup", action="store_true", help="skip the backup (a backup was just made)")
    p_rel = sub.add_parser("relabel", help="deterministic scope / value kind / unit")
    p_rel.add_argument("code", nargs="?", help="instrument code (or --all)")
    p_rel.add_argument("--all", action="store_true")
    p_rep = sub.add_parser("merge-report", help="read-only evaluation of the series linker")
    p_rep.add_argument("codes", nargs="+")
    p_aud = sub.add_parser("label-audit", help="read-only: scope / statement-type consistency of the model's labels")
    p_aud.add_argument("codes", nargs="+")
    args = ap.parse_args(argv)

    if args.cmd == "clear-judgements":
        database.init_db()
        clear_judgements(args.all)
        return 0
    if args.cmd == "backfill-series-cells":
        backfill_series_cells(args.code)
        return 0
    if args.cmd == "reset-reviews":
        database.init_db()
        reset_reviews(not args.no_backup)
        return 0
    if args.cmd == "backfill-events":
        database.init_db()
        backfill_events(args.user)
        return 0
    if args.cmd == "label-audit":
        for code in args.codes:
            label_audit(code)
        return 0
    if args.cmd != "merge-report":
        database.init_db()
    if args.cmd == "clear-merges":
        dest = _backup()
        n = database.clear_all_merges()
        print(f"Backup written to {dest}\nCleared saved merges of {n} company result row(s).")
    elif args.cmd == "relabel":
        codes = _companies() if args.all else [args.code] if args.code else []
        if not codes:
            ap.error("give an instrument code or --all")
        for code in codes:
            print(code, database.relabel_company(code))
    elif args.cmd == "merge-report":
        for code in args.codes:
            merge_report(code)
    return 0


if __name__ == "__main__":
    sys.exit(main())
