"""
KeePass database analyzer: catalogs all .kdbx files across 435 dumps,
deduplicates copies, parses KeePassXC/KeePass2 configs for key file info,
reads KDBX headers (version, cipher, KDF, rounds), extracts keepass2john-
compatible hashes, and cross-references with corporate/credential data.

Produces:
  - data/keepass_analysis.json
  - docs/keepass_report.md
"""

import csv
import hashlib
import json
import re
import struct
import time
import xml.etree.ElementTree as ET
from collections import defaultdict
from pathlib import Path

ROOT_DIR = Path(__file__).resolve().parents[2]
LEGACY_ROOT = ROOT_DIR / "findings" / "legacy_dump_assessment"
DUMPS_DIR = LEGACY_ROOT / "dumps"
DATA_DIR = ROOT_DIR / "findings" / "data"
DOCS_DIR = ROOT_DIR / "docs" / "processing" / "legacy_dump_assessment"

CRITICAL_CSV = DATA_DIR / "legacy_critical_findings.csv"
HOSTS_CSV = DATA_DIR / "legacy_dump_hosts.csv"
EXTRACTED_JSON = DATA_DIR / "legacy_extracted_credentials.json"
CORPORATE_JSON = DATA_DIR / "legacy_corporate_access.json"

OUTPUT_JSON = DATA_DIR / "legacy_keepass_analysis.json"
OUTPUT_REPORT = DOCS_DIR / "keepass_report.md"

# ---------------------------------------------------------------------------
# KDBX header constants
# ---------------------------------------------------------------------------
KDBX_SIG1 = 0x9AA2D903
KDBX_SIG2_KDBX = 0xB54BFB67

CIPHER_IDS = {
    "61ab05a1946441c38d743a563df8dd35": "AES128-CBC",
    "31c1f2e6bf714350be5805216afc5aff": "AES256-CBC",
    "ad68f29f576f4bb9a36ad47af965346c": "Twofish-CBC",
    "d6038a2b8b6f4cb5a524339a31dbb59a": "ChaCha20",
}

KDF_UUIDS = {
    "c9d9f39a628a4460bf740d08c18a4fea": "AES-KDF",
    "ef636ddf8c29444b91f7a9a403e30a0c": "Argon2d",
    "9e298b1956db4773b23dfc3ec6f0a1e6": "Argon2id",
}

HEADER_FIELD_NAMES = {
    0: "EndOfHeader",
    1: "Comment",
    2: "CipherID",
    3: "CompressionFlags",
    4: "MasterSeed",
    5: "TransformSeed",    # KDBX 3.x AES-KDF
    6: "TransformRounds",  # KDBX 3.x AES-KDF
    7: "EncryptionIV",
    8: "ProtectedStreamKey",
    9: "StreamStartBytes",
    10: "InnerRandomStreamID",
    11: "KdfParameters",   # KDBX 4.x
    12: "PublicCustomData",
}


def resolve_dump_path(dump_id: int) -> Path:
    folder = "dump" if dump_id == 0 else f"dump ({dump_id})"
    return DUMPS_DIR / folder


def load_hosts() -> dict:
    """Load hosts.csv into {dump_id: {computer_name, username, ...}}."""
    hosts = {}
    if not HOSTS_CSV.exists():
        return hosts
    with open(HOSTS_CSV, encoding="utf-8") as f:
        for row in csv.DictReader(f):
            did = int(row["dump_id"])
            hosts[did] = row
    return hosts


def load_corporate_scores() -> dict:
    """Load corporate access classification from corporate_access.json."""
    scores = {}
    if not CORPORATE_JSON.exists():
        return scores
    with open(CORPORATE_JSON, encoding="utf-8") as f:
        data = json.load(f)
    if isinstance(data, dict):
        data = data.get("hosts", [])
    for entry in data:
        did = entry.get("dump_id")
        if did is not None:
            scores[did] = {
                "score": entry.get("score", 0),
                "classification": entry.get("classification", ""),
            }
    return scores


def load_existing_findings() -> dict:
    """Load extracted_credentials.json, index by dump_id."""
    findings = defaultdict(list)
    if not EXTRACTED_JSON.exists():
        return findings
    with open(EXTRACTED_JSON, encoding="utf-8") as f:
        data = json.load(f)
    for entry in data:
        did = entry.get("dump_id")
        findings[did].append(entry)
    return findings


# ---------------------------------------------------------------------------
# Collect .kdbx entries from critical_findings.csv
# ---------------------------------------------------------------------------
def collect_kdbx_entries() -> list[dict]:
    entries = []
    if not CRITICAL_CSV.exists():
        return entries
    with open(CRITICAL_CSV, encoding="utf-8") as f:
        for row in csv.DictReader(f):
            if row.get("extension") == ".kdbx":
                entries.append(row)
    return entries


# ---------------------------------------------------------------------------
# Parse KeePassXC .ini config files
# ---------------------------------------------------------------------------
def parse_keepassxc_ini(ini_path: Path) -> dict:
    """Extract DB paths, key file refs, and settings from keepassxc.ini."""
    result = {
        "last_databases": [],
        "last_key_files": None,
        "key_file_used": False,
        "challenge_response": False,
        "browser_integration": False,
        "ssh_agent": False,
        "signer": None,
    }
    try:
        text = ini_path.read_text(encoding="utf-8", errors="replace")
    except Exception:
        return result

    for line in text.splitlines():
        line = line.strip()
        if line.startswith("LastDatabases="):
            val = line.split("=", 1)[1].strip()
            result["last_databases"] = [
                db.strip() for db in val.split(",") if db.strip()
            ]
        elif line.startswith("LastActiveDatabase="):
            val = line.split("=", 1)[1].strip()
            if val and val not in result["last_databases"]:
                result["last_databases"].insert(0, val)
        elif line.startswith("LastKeyFiles="):
            val = line.split("=", 1)[1].strip()
            result["last_key_files"] = val
            null_variants = [
                "@Variant(\\0\\0\\0\\x1c\\0\\0\\0\\0)",
                "@Variant(\\0\\0\\0\\x1c\\0\\0\\0\\x0)",
                "@Invalid()",
                "",
            ]
            if val not in null_variants:
                result["key_file_used"] = True
        elif line.startswith("LastChallengeResponse="):
            val = line.split("=", 1)[1].strip()
            null_variants = [
                "@Variant(\\0\\0\\0\\x1c\\0\\0\\0\\0)",
                "@Invalid()",
                "",
            ]
            if val not in null_variants:
                result["challenge_response"] = True
        elif line.startswith("Enabled=true") and "Browser" in text[:text.index(line)].split("[")[-1]:
            result["browser_integration"] = True

    signer_match = re.search(r"<Signer>([^<]+)</Signer>", text)
    if signer_match:
        result["signer"] = signer_match.group(1)

    return result


# ---------------------------------------------------------------------------
# Parse KeePass 2.x config XML
# ---------------------------------------------------------------------------
def parse_keepass2_xml(xml_path: Path) -> dict:
    """Extract DB paths and key source info from KeePass.config.xml."""
    result = {
        "last_databases": [],
        "key_file_used": False,
        "password_used": False,
        "user_account_used": False,
        "db_paths": [],
    }
    try:
        text = xml_path.read_text(encoding="utf-8", errors="replace")
        root = ET.fromstring(text)
    except Exception:
        return result

    for path_el in root.iter("Path"):
        p = (path_el.text or "").strip()
        if p and p.endswith(".kdbx") and p not in result["db_paths"]:
            result["db_paths"].append(p)

    result["last_databases"] = result["db_paths"]

    for assoc in root.iter("Association"):
        db_path = ""
        pw = False
        key = False
        ua = False
        for child in assoc:
            if child.tag == "DatabasePath":
                db_path = (child.text or "").strip()
            elif child.tag == "Password" and (child.text or "").strip().lower() == "true":
                pw = True
            elif child.tag == "KeyFilePath" and (child.text or "").strip():
                key = True
            elif child.tag == "UserAccount" and (child.text or "").strip().lower() == "true":
                ua = True
        if pw:
            result["password_used"] = True
        if key:
            result["key_file_used"] = True
        if ua:
            result["user_account_used"] = True

    return result


# ---------------------------------------------------------------------------
# Parse KDBX binary header
# ---------------------------------------------------------------------------
def parse_kdbx_header(kdbx_path: Path) -> dict | None:
    """Read KDBX file header to extract version, cipher, KDF, rounds."""
    try:
        raw = kdbx_path.read_bytes()
    except Exception:
        return None

    if len(raw) < 12:
        return None

    sig1, sig2, minor, major = struct.unpack_from("<IIHH", raw, 0)
    if sig1 != KDBX_SIG1 or sig2 != KDBX_SIG2_KDBX:
        return None

    result = {
        "version": f"{major}.{minor}",
        "major": major,
        "cipher": None,
        "compression": None,
        "kdf": None,
        "transform_rounds": None,
        "master_seed": None,
        "transform_seed": None,
        "encryption_iv": None,
        "stream_start_bytes": None,
        "header_size": 0,
    }

    offset = 12

    if major <= 3:
        while offset < len(raw) - 3:
            field_id = raw[offset]
            field_len = struct.unpack_from("<H", raw, offset + 1)[0]
            offset += 3
            field_data = raw[offset:offset + field_len]
            offset += field_len

            if field_id == 0:  # EndOfHeader
                result["header_size"] = offset
                break
            elif field_id == 2 and len(field_data) == 16:  # CipherID
                cid = field_data.hex()
                result["cipher"] = CIPHER_IDS.get(cid, f"unknown({cid})")
            elif field_id == 3 and len(field_data) >= 4:  # Compression
                comp = struct.unpack_from("<I", field_data)[0]
                result["compression"] = "gzip" if comp == 1 else "none"
            elif field_id == 4:  # MasterSeed
                result["master_seed"] = field_data.hex()
            elif field_id == 5:  # TransformSeed (KDBX3)
                result["transform_seed"] = field_data.hex()
            elif field_id == 6 and len(field_data) == 8:  # TransformRounds
                result["transform_rounds"] = struct.unpack_from("<Q", field_data)[0]
                result["kdf"] = "AES-KDF"
            elif field_id == 7:  # EncryptionIV
                result["encryption_iv"] = field_data.hex()
            elif field_id == 9:  # StreamStartBytes
                result["stream_start_bytes"] = field_data.hex()

    elif major >= 4:
        while offset < len(raw) - 5:
            field_id = raw[offset]
            field_len = struct.unpack_from("<I", raw, offset + 1)[0]
            offset += 5
            field_data = raw[offset:offset + field_len]
            offset += field_len

            if field_id == 0:
                result["header_size"] = offset
                break
            elif field_id == 2 and len(field_data) == 16:
                cid = field_data.hex()
                result["cipher"] = CIPHER_IDS.get(cid, f"unknown({cid})")
            elif field_id == 3 and len(field_data) >= 4:
                comp = struct.unpack_from("<I", field_data)[0]
                result["compression"] = "gzip" if comp == 1 else "none"
            elif field_id == 4:
                result["master_seed"] = field_data.hex()
            elif field_id == 7:
                result["encryption_iv"] = field_data.hex()
            elif field_id == 11:  # KdfParameters (VariantDictionary)
                result.update(_parse_kdf_params(field_data))

    return result


def _parse_kdf_params(data: bytes) -> dict:
    """Parse KDBX4 VariantDictionary for KDF parameters."""
    result = {}
    if len(data) < 4:
        return result

    version = struct.unpack_from("<H", data, 0)[0]
    offset = 2

    params = {}
    while offset < len(data) - 1:
        vtype = data[offset]
        offset += 1
        if vtype == 0:
            break

        if offset + 4 > len(data):
            break
        name_len = struct.unpack_from("<I", data, offset)[0]
        offset += 4
        if offset + name_len > len(data):
            break
        name = data[offset:offset + name_len].decode("utf-8", errors="replace")
        offset += name_len

        if offset + 4 > len(data):
            break
        val_len = struct.unpack_from("<I", data, offset)[0]
        offset += 4
        if offset + val_len > len(data):
            break
        val_data = data[offset:offset + val_len]
        offset += val_len

        if vtype == 0x04 and val_len == 4:  # UInt32
            params[name] = struct.unpack_from("<I", val_data)[0]
        elif vtype == 0x05 and val_len == 8:  # UInt64
            params[name] = struct.unpack_from("<Q", val_data)[0]
        elif vtype == 0x08:  # Bool
            params[name] = val_data[0] != 0 if val_data else False
        elif vtype == 0x42:  # ByteArray
            params[name] = val_data.hex()
        elif vtype == 0x18:  # String
            params[name] = val_data.decode("utf-8", errors="replace")

    if "$UUID" in params:
        uuid_hex = params["$UUID"]
        result["kdf"] = KDF_UUIDS.get(uuid_hex, f"unknown({uuid_hex})")

    if "R" in params:
        result["transform_rounds"] = params["R"]
    if "I" in params:
        result["argon2_iterations"] = params["I"]
    if "M" in params:
        result["argon2_memory"] = params["M"]
    if "P" in params:
        result["argon2_parallelism"] = params["P"]
    if "S" in params:
        result["transform_seed"] = params["S"]

    return result


# ---------------------------------------------------------------------------
# Generate keepass2john-compatible hash
# ---------------------------------------------------------------------------
def extract_keepass_hash(kdbx_path: Path) -> str | None:
    """
    Extract hash in keepass2john format compatible with hashcat.

    KDBX 3.x -> hashcat mode 13400:
      $keepass$*2*<rounds>*<data_start_offset>*<master_seed>*<transform_seed>*<iv>*<expected_start_bytes>*<first_enc_32bytes>

    KDBX 4.x Argon2 -> hashcat mode 29700:
      $keepass$*2*<rounds>*<data_start_offset>*<master_seed>*<transform_seed>*<iv>*<expected_start_bytes>*<first_enc_32bytes>
    """
    try:
        raw = kdbx_path.read_bytes()
    except Exception:
        return None

    if len(raw) < 12:
        return None

    sig1, sig2, minor, major = struct.unpack_from("<IIHH", raw, 0)
    if sig1 != KDBX_SIG1 or sig2 != KDBX_SIG2_KDBX:
        return None

    header = parse_kdbx_header(kdbx_path)
    if not header:
        return None

    if major <= 3:
        rounds = header.get("transform_rounds")
        master_seed = header.get("master_seed")
        transform_seed = header.get("transform_seed")
        enc_iv = header.get("encryption_iv")
        ssb = header.get("stream_start_bytes")

        if not all([rounds, master_seed, transform_seed, enc_iv, ssb]):
            return None

        hdr_size = header.get("header_size", 0)
        if hdr_size == 0:
            return None

        enc_data = raw[hdr_size:hdr_size + 32]
        if len(enc_data) < 32:
            return None
        enc_data_hex = enc_data.hex()

        return (
            f"$keepass$*2*{rounds}*{hdr_size}*"
            f"{master_seed}*{transform_seed}*{enc_iv}*"
            f"{ssb}*{enc_data_hex}"
        )

    elif major >= 4:
        master_seed = header.get("master_seed")
        transform_seed = header.get("transform_seed")
        enc_iv = header.get("encryption_iv")

        if not all([master_seed, transform_seed, enc_iv]):
            return None

        hdr_size = header.get("header_size", 0)
        if hdr_size == 0 or len(raw) < hdr_size + 32:
            return None

        header_hash = hashlib.sha256(raw[:hdr_size]).hexdigest()

        kdf = header.get("kdf", "")
        if "Argon2" in kdf:
            iters = header.get("argon2_iterations", 0)
            mem = header.get("argon2_memory", 0)
            par = header.get("argon2_parallelism", 0)
            return (
                f"$keepass$*2*{iters}*{mem}*{par}*"
                f"{master_seed}*{transform_seed}*{enc_iv}*{header_hash}"
            )
        else:
            rounds = header.get("transform_rounds", 0)
            enc_data = raw[hdr_size:hdr_size + 32]
            enc_data_hex = enc_data.hex() if len(enc_data) >= 32 else ""
            return (
                f"$keepass$*2*{rounds}*{hdr_size}*"
                f"{master_seed}*{transform_seed}*{enc_iv}*{header_hash}*{enc_data_hex}"
            )

    return None


# ---------------------------------------------------------------------------
# Scan all keepass folders for configs
# ---------------------------------------------------------------------------
def scan_keepass_configs(dump_id: int) -> dict:
    """Scan a dump's keepass folder for config files (.ini, .xml, .reg)."""
    dump_path = resolve_dump_path(dump_id)
    keepass_dir = dump_path / "files" / "keepass"
    if not keepass_dir.exists():
        return {}

    result = {
        "keepassxc_configs": [],
        "keepass2_configs": [],
        "registry_entries": [],
        "key_file_used": False,
        "password_only": True,
        "db_paths_from_config": [],
    }

    for f in sorted(keepass_dir.iterdir()):
        fname_lower = f.name.lower()
        stripped = re.sub(r"^\d{3}_", "", f.name)

        if stripped.lower() == "keepassxc.ini" and f.stat().st_size > 0:
            parsed = parse_keepassxc_ini(f)
            parsed["file"] = f.name
            result["keepassxc_configs"].append(parsed)
            if parsed.get("key_file_used"):
                result["key_file_used"] = True
                result["password_only"] = False
            if parsed.get("challenge_response"):
                result["password_only"] = False
            for db in parsed.get("last_databases", []):
                if db not in result["db_paths_from_config"]:
                    result["db_paths_from_config"].append(db)

        elif stripped.lower() == "keepass.config.xml" and f.stat().st_size > 0:
            parsed = parse_keepass2_xml(f)
            parsed["file"] = f.name
            result["keepass2_configs"].append(parsed)
            if parsed.get("key_file_used"):
                result["key_file_used"] = True
                result["password_only"] = False
            if parsed.get("user_account_used"):
                result["password_only"] = False
            for db in parsed.get("db_paths", []):
                if db not in result["db_paths_from_config"]:
                    result["db_paths_from_config"].append(db)

        elif stripped.lower().endswith(".reg") and f.stat().st_size > 0:
            try:
                text = f.read_text(encoding="utf-8", errors="replace")
                if "keepass" in text.lower():
                    result["registry_entries"].append({"file": f.name, "size": f.stat().st_size})
            except Exception:
                pass

    return result


# ---------------------------------------------------------------------------
# Main analysis
# ---------------------------------------------------------------------------
def run():
    t0 = time.time()
    print("=== KeePass Database Analyzer ===\n")

    hosts = load_hosts()
    corp_scores = load_corporate_scores()
    cred_findings = load_existing_findings()

    kdbx_rows = collect_kdbx_entries()
    print(f"Found {len(kdbx_rows)} .kdbx entries in critical_findings.csv")

    # Group by dump_id
    by_dump = defaultdict(list)
    for row in kdbx_rows:
        did = int(row["dump_id"])
        by_dump[did].append(row)

    print(f"Across {len(by_dump)} unique hosts\n")

    # Analyze each host
    host_results = []

    for dump_id in sorted(by_dump.keys()):
        rows = by_dump[dump_id]
        host_info = hosts.get(dump_id, {})
        corp = corp_scores.get(dump_id, {})

        print(f"[{dump_id}] {host_info.get('computer_name', '?')} / {host_info.get('username', '?')} "
              f"-- {len(rows)} kdbx entries...")

        configs = scan_keepass_configs(dump_id)

        # Process kdbx files -- deduplicate by size+hash
        kdbx_files = []
        seen_hashes = {}
        unique_dbs = []
        duplicate_count = 0

        for row in rows:
            dump_path = resolve_dump_path(dump_id)
            fpath = dump_path / row["relative_path"]
            size = int(row.get("size_bytes", 0))
            stripped_name = re.sub(r"^\d{3}_", "", row.get("filename", ""))

            entry = {
                "file": row["relative_path"],
                "filename": stripped_name,
                "size_bytes": size,
                "exists": fpath.exists(),
                "header": None,
                "hash_sha256": None,
                "keepass_hash": None,
                "is_duplicate": False,
                "duplicate_of": None,
            }

            if fpath.exists() and size > 0:
                try:
                    content = fpath.read_bytes()
                    sha = hashlib.sha256(content).hexdigest()
                    entry["hash_sha256"] = sha

                    if sha in seen_hashes:
                        entry["is_duplicate"] = True
                        entry["duplicate_of"] = seen_hashes[sha]
                        duplicate_count += 1
                    else:
                        seen_hashes[sha] = stripped_name

                        hdr = parse_kdbx_header(fpath)
                        if hdr:
                            clean_hdr = {
                                k: v for k, v in hdr.items()
                                if k not in ("master_seed", "transform_seed",
                                             "encryption_iv", "stream_start_bytes")
                                and v is not None
                            }
                            entry["header"] = clean_hdr

                        khash = extract_keepass_hash(fpath)
                        if khash:
                            entry["keepass_hash"] = khash

                        unique_dbs.append(entry)
                except Exception as e:
                    entry["error"] = str(e)[:200]

            kdbx_files.append(entry)

        # Estimate DB value by size
        EMPTY_THRESHOLD = 1500  # ~1.5KB = empty KeePassXC DB
        non_empty = [db for db in unique_dbs if db["size_bytes"] > EMPTY_THRESHOLD]
        empty_count = len(unique_dbs) - len(non_empty)

        # Check for password hints in vault/credential findings
        password_hints = []
        for cred_entry in cred_findings.get(dump_id, []):
            for finding in cred_entry.get("findings", []):
                ftype = finding.get("type", "")
                detail = finding.get("detail", "")
                if ftype == "password" and detail:
                    password_hints.append({
                        "source_file": cred_entry.get("filename", ""),
                        "password": detail,
                    })

        # Priority scoring
        priority_score = 0
        priority_reasons = []

        total_size = sum(db["size_bytes"] for db in unique_dbs)
        if total_size > 100_000:
            priority_score += 50
            priority_reasons.append(f"large total DB size ({total_size:,} bytes)")
        elif total_size > 10_000:
            priority_score += 30
            priority_reasons.append(f"medium total DB size ({total_size:,} bytes)")

        if non_empty:
            priority_score += len(non_empty) * 10
            priority_reasons.append(f"{len(non_empty)} non-empty database(s)")

        if corp.get("classification") in ("CORPORATE", "INFRASTRUCTURE"):
            priority_score += 40
            priority_reasons.append(f"corporate classification: {corp.get('classification')}")
        elif corp.get("classification") == "POSSIBLE":
            priority_score += 15
            priority_reasons.append("possible corporate access")

        if configs.get("password_only"):
            priority_score += 20
            priority_reasons.append("password-only (no key file)")

        if password_hints:
            priority_score += 25
            priority_reasons.append(f"{len(password_hints)} password(s) found elsewhere on host")

        named_dbs = [
            db for db in unique_dbs
            if db["filename"].lower() not in ("c.kdbx", "database.kdbx")
        ]
        if named_dbs:
            priority_score += 15
            priority_reasons.append(
                f"named DBs: {', '.join(db['filename'] for db in named_dbs)}"
            )

        result = {
            "dump_id": dump_id,
            "computer_name": host_info.get("computer_name", ""),
            "username": host_info.get("username", ""),
            "corporate_classification": corp.get("classification", ""),
            "corporate_score": corp.get("score", 0),
            "priority_score": priority_score,
            "priority_reasons": priority_reasons,
            "total_kdbx_entries": len(rows),
            "unique_databases": len(unique_dbs),
            "non_empty_databases": len(non_empty),
            "empty_databases": empty_count,
            "duplicate_copies": duplicate_count,
            "total_unique_size": total_size,
            "password_only": configs.get("password_only", True),
            "key_file_used": configs.get("key_file_used", False),
            "db_paths_from_config": configs.get("db_paths_from_config", []),
            "keepassxc_configs_count": len(configs.get("keepassxc_configs", [])),
            "keepass2_configs_count": len(configs.get("keepass2_configs", [])),
            "password_hints": password_hints[:10],
            "databases": [db for db in kdbx_files if not db["is_duplicate"]],
            "hashes": [
                {
                    "filename": db["filename"],
                    "hash": db["keepass_hash"],
                    "version": db.get("header", {}).get("version", "?") if db.get("header") else "?",
                    "kdf": db.get("header", {}).get("kdf", "?") if db.get("header") else "?",
                }
                for db in unique_dbs
                if db.get("keepass_hash")
            ],
        }

        host_results.append(result)

    host_results.sort(key=lambda x: -x["priority_score"])

    # Summary stats
    total_unique = sum(r["unique_databases"] for r in host_results)
    total_non_empty = sum(r["non_empty_databases"] for r in host_results)
    total_empty = sum(r["empty_databases"] for r in host_results)
    total_dupes = sum(r["duplicate_copies"] for r in host_results)
    total_hashes = sum(len(r["hashes"]) for r in host_results)
    pw_only = sum(1 for r in host_results if r["password_only"])
    with_hints = sum(1 for r in host_results if r["password_hints"])

    summary = {
        "total_hosts_with_keepass": len(host_results),
        "total_kdbx_entries": len(kdbx_rows),
        "total_unique_databases": total_unique,
        "total_non_empty": total_non_empty,
        "total_empty": total_empty,
        "total_duplicate_copies": total_dupes,
        "total_hashes_extracted": total_hashes,
        "password_only_hosts": pw_only,
        "hosts_with_password_hints": with_hints,
    }

    output = {
        "generated": time.strftime("%Y-%m-%d %H:%M:%S"),
        "summary": summary,
        "hosts": host_results,
    }

    # Write JSON
    with open(OUTPUT_JSON, "w", encoding="utf-8") as f:
        json.dump(output, f, indent=2, ensure_ascii=False)
    print(f"\nJSON output: {OUTPUT_JSON}")

    # Write report
    generate_report(output)
    print(f"Report: {OUTPUT_REPORT}")

    elapsed = time.time() - t0
    print(f"\nDone in {elapsed:.1f}s")
    print(f"\n=== Summary ===")
    print(f"Hosts with KeePass: {len(host_results)}")
    print(f"Unique databases:   {total_unique}")
    print(f"  Non-empty:        {total_non_empty}")
    print(f"  Empty/minimal:    {total_empty}")
    print(f"  Duplicate copies: {total_dupes}")
    print(f"Hashes extracted:   {total_hashes}")
    print(f"Password-only:      {pw_only}/{len(host_results)}")
    print(f"With password hints: {with_hints}")


# ---------------------------------------------------------------------------
# Report generation
# ---------------------------------------------------------------------------
def generate_report(data: dict):
    s = data["summary"]
    hosts = data["hosts"]

    lines = [
        "# KeePass Database Analysis Report",
        "",
        f"Generated: {data['generated']}",
        "",
        "## Summary",
        "",
        f"| Metric | Value |",
        f"|--------|-------|",
        f"| Hosts with KeePass DBs | {s['total_hosts_with_keepass']} |",
        f"| Total .kdbx entries (incl. duplicates) | {s['total_kdbx_entries']} |",
        f"| Unique databases | {s['total_unique_databases']} |",
        f"| Non-empty databases | {s['total_non_empty']} |",
        f"| Empty/minimal databases | {s['total_empty']} |",
        f"| Duplicate copies | {s['total_duplicate_copies']} |",
        f"| Hashes extracted | {s['total_hashes_extracted']} |",
        f"| Password-only (no key file) | {s['password_only_hosts']}/{s['total_hosts_with_keepass']} |",
        f"| Hosts with password hints | {s['hosts_with_password_hints']} |",
        "",
    ]

    # Priority targets
    lines += [
        "## Priority Targets (ranked by score)",
        "",
    ]

    for i, h in enumerate(hosts):
        if h["priority_score"] == 0:
            continue

        corp_tag = ""
        if h["corporate_classification"]:
            corp_tag = f" [{h['corporate_classification']}]"

        lines += [
            f"### {i+1}. [{h['dump_id']}] {h['computer_name']} / {h['username']}"
            f" (priority: {h['priority_score']}){corp_tag}",
            "",
            f"| Property | Value |",
            f"|----------|-------|",
            f"| Unique DBs | {h['unique_databases']} ({h['non_empty_databases']} non-empty, {h['empty_databases']} empty) |",
            f"| Duplicate copies | {h['duplicate_copies']} |",
            f"| Total unique size | {h['total_unique_size']:,} bytes |",
            f"| Key file required | {'Yes' if h['key_file_used'] else 'No (password only)'} |",
            f"| Password hints available | {len(h['password_hints'])} |",
            "",
        ]

        if h["priority_reasons"]:
            lines.append("**Why this is interesting:**")
            for reason in h["priority_reasons"]:
                lines.append(f"- {reason}")
            lines.append("")

        # Database details
        non_dup = [db for db in h["databases"] if not db.get("is_duplicate")]
        if non_dup:
            lines += [
                "**Databases:**",
                "",
                "| Filename | Size | Version | Cipher | KDF |",
                "|----------|------|---------|--------|-----|",
            ]
            for db in non_dup:
                hdr = db.get("header") or {}
                lines.append(
                    f"| {db['filename']} | {db['size_bytes']:,} | "
                    f"{hdr.get('version', '?')} | {hdr.get('cipher', '?')} | "
                    f"{hdr.get('kdf', '?')} |"
                )
            lines.append("")

        if h["db_paths_from_config"]:
            lines.append("**Original paths from config:**")
            for p in h["db_paths_from_config"]:
                lines.append(f"- `{p}`")
            lines.append("")

        if h["password_hints"]:
            lines.append("**Password hints found on this host:**")
            for hint in h["password_hints"]:
                lines.append(f"- From `{hint['source_file']}`: `{hint['password']}`")
            lines.append("")

    # Hash extraction summary
    all_hashes = []
    for h in hosts:
        for hinfo in h["hashes"]:
            all_hashes.append({
                "dump_id": h["dump_id"],
                "computer_name": h["computer_name"],
                "username": h["username"],
                **hinfo,
            })

    if all_hashes:
        lines += [
            "## Extracted Hashes for Cracking",
            "",
            f"Total hashes extracted: {len(all_hashes)}",
            "",
            "| Dump | Host | DB | Version | KDF | Hash (truncated) |",
            "|------|------|----|---------|-----|------------------|",
        ]
        for h in all_hashes:
            trunc = h["hash"][:60] + "..." if len(h["hash"]) > 60 else h["hash"]
            lines.append(
                f"| {h['dump_id']} | {h['computer_name']}/{h['username']} | "
                f"{h['filename']} | {h['version']} | {h['kdf']} | `{trunc}` |"
            )
        lines.append("")

        lines += [
            "### Hashcat Usage",
            "",
            "For KDBX 3.x (AES-KDF):",
            "```",
            "hashcat -m 13400 hashes.txt wordlist.txt",
            "```",
            "",
            "For KDBX 4.x (Argon2):",
            "```",
            "hashcat -m 29700 hashes.txt wordlist.txt",
            "```",
            "",
            "All hashes are saved in `data/keepass_hashes.txt` (one per line).",
            "",
        ]

        # Also write raw hashes file
        hash_file = DATA_DIR / "legacy_keepass_hashes.txt"
        with open(hash_file, "w", encoding="utf-8") as f:
            for h in all_hashes:
                label = f"{h['computer_name']}_{h['username']}_{h['filename']}"
                f.write(f"{label}:{h['hash']}\n")

    # Appendix: all hosts
    lines += [
        "## Appendix: All Hosts with KeePass",
        "",
        "| # | Dump | Computer | User | DBs (unique) | Non-empty | Size | Password Only | Priority |",
        "|---|------|----------|------|-------------|-----------|------|---------------|----------|",
    ]
    for i, h in enumerate(hosts):
        lines.append(
            f"| {i+1} | {h['dump_id']} | {h['computer_name']} | {h['username']} | "
            f"{h['unique_databases']} | {h['non_empty_databases']} | "
            f"{h['total_unique_size']:,} | "
            f"{'Yes' if h['password_only'] else 'No'} | {h['priority_score']} |"
        )
    lines.append("")

    DOCS_DIR.mkdir(exist_ok=True)
    with open(OUTPUT_REPORT, "w", encoding="utf-8") as f:
        f.write("\n".join(lines))


if __name__ == "__main__":
    run()