#!/usr/bin/env python3
"""Read-only PocketBase SQLite exporter.

This script is intentionally small and dependency-free so it works on Windows
developer machines without compiling a Node SQLite native module. The TypeScript
wrapper owns CLI ergonomics; this script owns the SQLite cursor work.
"""

from __future__ import annotations

import argparse
import hashlib
import json
import mimetypes
import os
import pathlib
import sqlite3
import sys
from datetime import datetime, timezone
from typing import Any


SYSTEM_COLLECTIONS = {
    "_authOrigins",
    "_externalAuths",
    "_mfas",
    "_otps",
    "_params",
    "_superusers",
}

SYSTEM_TABLE_PREFIXES = ("sqlite_",)

SENSITIVE_KEY_PARTS = (
    "password",
    "token",
    "secret",
    "privatekey",
    "sessionkey",
    "accesskey",
    "appkey",
    "apikey",
    "api_v3_key",
    "notifytoken",
    "aeskey",
    "wxaccesstoken",
    "wechatsessionkey",
    "smscode",
    "verifycode",
    "verificationcode",
    "captcha",
)

OPTIONAL_IDENTITY_KEY_PARTS = (
    "openid",
    "unionid",
)

JSONISH_STARTS = ("{", "[")
SKIP_FIELD = object()


def utc_now_iso() -> str:
    return datetime.now(timezone.utc).isoformat().replace("+00:00", "Z")


def parse_args() -> argparse.Namespace:
    parser = argparse.ArgumentParser(description="Export PocketBase SQLite collections to JSON.")
    parser.add_argument("--data-db", required=True, help="Path to PocketBase data.db")
    parser.add_argument("--aux-db", default="", help="Optional path to PocketBase auxiliary.db")
    parser.add_argument("--storage-dir", default="", help="Optional path to PocketBase storage directory")
    parser.add_argument("--export-dir", required=True, help="Output directory for collection JSON files")
    parser.add_argument("--collections", default="", help="Comma-separated collection allowlist")
    parser.add_argument("--include-system", action="store_true", help="Include PocketBase system collections")
    parser.add_argument(
        "--include-sensitive-identities",
        action="store_true",
        help="Keep openid/unionid fields in collection JSON. Use only in an encrypted migration workspace.",
    )
    parser.add_argument(
        "--include-aux-logs",
        action="store_true",
        help="Export auxiliary _logs summary and optional samples. Logs are not exported as full records by default.",
    )
    parser.add_argument("--batch-size", type=int, default=1000, help="SQLite fetch batch size")
    return parser.parse_args()


def connect_readonly(db_path: pathlib.Path) -> sqlite3.Connection:
    if not db_path.exists():
        raise FileNotFoundError(f"SQLite database does not exist: {db_path}")
    uri = db_path.resolve().as_posix()
    con = sqlite3.connect(f"file:{uri}?mode=ro", uri=True)
    con.row_factory = sqlite3.Row
    return con


def json_loads(value: Any, fallback: Any) -> Any:
    if value is None:
        return fallback
    if isinstance(value, (dict, list)):
        return value
    if isinstance(value, (bytes, bytearray)):
        try:
            value = value.decode("utf-8")
        except UnicodeDecodeError:
            return fallback
    if not isinstance(value, str):
        return fallback
    text = value.strip()
    if not text or not text.startswith(JSONISH_STARTS):
        return fallback
    try:
        return json.loads(text)
    except json.JSONDecodeError:
        return fallback


def normalize_sqlite_value(value: Any) -> Any:
    if isinstance(value, bytes):
        return {"__sqliteBlobSha256": hashlib.sha256(value).hexdigest(), "__sqliteBlobSize": len(value)}
    if isinstance(value, str):
        stripped = value.strip()
        if stripped.startswith(JSONISH_STARTS):
            try:
                return json.loads(stripped)
            except json.JSONDecodeError:
                return value
    return value


def key_has_part(key: str, parts: tuple[str, ...]) -> bool:
    lowered = key.lower().replace("_", "").replace("-", "")
    return any(part in lowered for part in parts)


def sanitize_value(value: Any, path: list[str], include_sensitive_identities: bool, redactions: list[dict[str, str]]) -> Any:
    key = path[-1] if path else ""
    is_secret = key_has_part(key, SENSITIVE_KEY_PARTS)
    is_optional_identity = key_has_part(key, OPTIONAL_IDENTITY_KEY_PARTS)

    if (is_secret or (is_optional_identity and not include_sensitive_identities)) and value not in (None, ""):
        redactions.append(
            {
                "fieldPath": ".".join(path),
                "reason": "secret" if is_secret else "optional_identity",
            }
        )
        return SKIP_FIELD

    if isinstance(value, dict):
        clean: dict[str, Any] = {}
        for child_key, child_value in value.items():
            sanitized = sanitize_value(child_value, [*path, child_key], include_sensitive_identities, redactions)
            if sanitized is not SKIP_FIELD:
                clean[child_key] = sanitized
        return clean
    if isinstance(value, list):
        clean_list = []
        for index, child_value in enumerate(value):
            sanitized = sanitize_value(child_value, [*path, str(index)], include_sensitive_identities, redactions)
            if sanitized is not SKIP_FIELD:
                clean_list.append(sanitized)
        return clean_list
    return value


def read_collections(con: sqlite3.Connection) -> list[dict[str, Any]]:
    row = con.execute("select name from sqlite_master where type='table' and name='_collections'").fetchone()
    if not row:
        return []
    rows = con.execute('select * from "_collections" order by name').fetchall()
    collections: list[dict[str, Any]] = []
    for item in rows:
        raw = dict(item)
        raw["system"] = bool(raw.get("system"))
        raw["fields"] = json_loads(raw.get("fields"), [])
        raw["indexes"] = json_loads(raw.get("indexes"), [])
        raw["options"] = json_loads(raw.get("options"), {})
        collections.append(raw)
    return collections


def sqlite_tables(con: sqlite3.Connection) -> set[str]:
    return {
        row["name"]
        for row in con.execute("select name from sqlite_master where type='table'").fetchall()
        if not any(str(row["name"]).startswith(prefix) for prefix in SYSTEM_TABLE_PREFIXES)
    }


def collection_should_export(collection: dict[str, Any], allowlist: set[str] | None, include_system: bool, tables: set[str]) -> bool:
    name = str(collection.get("name") or "")
    if not name or name not in tables:
        return False
    if allowlist is not None and name not in allowlist:
        return False
    if not include_system and (name in SYSTEM_COLLECTIONS or bool(collection.get("system")) or name.startswith("_")):
        return False
    return True


def table_count(con: sqlite3.Connection, table: str) -> int:
    return int(con.execute(f'select count(*) as c from "{table}"').fetchone()["c"])


def write_collection(
    con: sqlite3.Connection,
    collection: dict[str, Any],
    out_path: pathlib.Path,
    batch_size: int,
    include_sensitive_identities: bool,
) -> dict[str, Any]:
    name = str(collection["name"])
    count = table_count(con, name)
    field_names = [row["name"] for row in con.execute(f'pragma table_info("{name}")').fetchall()]
    file_fields = [field.get("name") for field in collection.get("fields", []) if field.get("type") == "file"]
    redaction_counts: dict[str, int] = {}

    out_path.parent.mkdir(parents=True, exist_ok=True)
    with out_path.open("w", encoding="utf-8", newline="\n") as handle:
        handle.write("[\n")
        cursor = con.execute(f'select * from "{name}" order by id')
        written = 0
        while True:
            rows = cursor.fetchmany(max(1, batch_size))
            if not rows:
                break
            for row in rows:
                record = {field: normalize_sqlite_value(row[field]) for field in field_names}
                redactions: list[dict[str, str]] = []
                safe_record = sanitize_value(record, [], include_sensitive_identities, redactions)
                for redaction in redactions:
                    key = redaction["fieldPath"]
                    redaction_counts[key] = redaction_counts.get(key, 0) + 1
                if written:
                    handle.write(",\n")
                handle.write(json.dumps(safe_record, ensure_ascii=False, separators=(",", ":")))
                written += 1
        handle.write("\n]\n")

    return {
        "name": name,
        "collectionId": collection.get("id"),
        "type": collection.get("type"),
        "system": bool(collection.get("system")),
        "recordCount": count,
        "fieldCount": len(field_names),
        "fields": field_names,
        "fileFields": [field for field in file_fields if field],
        "outputFile": out_path.name,
        "redactedFields": [{"fieldPath": key, "count": value} for key, value in sorted(redaction_counts.items())],
    }


def build_storage_manifest(storage_dir: pathlib.Path, collections: list[dict[str, Any]]) -> dict[str, Any] | None:
    if not storage_dir or not storage_dir.exists():
        return None

    collection_by_id = {str(item.get("id")): str(item.get("name")) for item in collections}
    files: list[dict[str, Any]] = []
    total_size = 0
    extension_counts: dict[str, int] = {}
    collection_counts: dict[str, int] = {}

    for file_path in sorted(storage_dir.rglob("*")):
        if not file_path.is_file():
            continue
        relative = file_path.relative_to(storage_dir).as_posix()
        if relative == ".DS_Store" or relative.endswith(".attrs") or "/thumbs_" in relative or relative.startswith("thumbs_"):
            continue
        parts = pathlib.PurePosixPath(relative).parts
        collection_id = parts[0] if len(parts) >= 3 else ""
        record_id = parts[1] if len(parts) >= 3 else ""
        file_name = parts[-1]
        size = file_path.stat().st_size
        mime, _ = mimetypes.guess_type(file_name)
        ext = file_path.suffix.lower() or "<none>"
        total_size += size
        extension_counts[ext] = extension_counts.get(ext, 0) + 1
        collection_name = collection_by_id.get(collection_id, collection_id or "<unknown>")
        collection_counts[collection_name] = collection_counts.get(collection_name, 0) + 1
        files.append(
            {
                "collectionId": collection_id,
                "collectionName": collection_name,
                "recordId": record_id,
                "fileName": file_name,
                "relativePath": relative,
                "size": size,
                "mimeType": mime or "application/octet-stream",
                "sha256": sha256_file(file_path),
            }
        )

    return {
        "storageDir": str(storage_dir),
        "fileCount": len(files),
        "totalSize": total_size,
        "extensionCounts": dict(sorted(extension_counts.items())),
        "collectionCounts": dict(sorted(collection_counts.items())),
        "files": files,
    }


def sha256_file(file_path: pathlib.Path) -> str:
    digest = hashlib.sha256()
    with file_path.open("rb") as handle:
        for chunk in iter(lambda: handle.read(1024 * 1024), b""):
            digest.update(chunk)
    return digest.hexdigest()


def auxiliary_summary(aux_db: pathlib.Path, include_logs: bool) -> dict[str, Any] | None:
    if not aux_db or not aux_db.exists():
        return None
    con = connect_readonly(aux_db)
    try:
        tables = sqlite_tables(con)
        summary: dict[str, Any] = {"path": str(aux_db), "tables": []}
        for table in sorted(tables):
            item: dict[str, Any] = {"name": table, "recordCount": table_count(con, table)}
            if table == "_logs":
                levels = con.execute('select level, count(*) as count from "_logs" group by level order by level').fetchall()
                item["levelCounts"] = {str(row["level"]): int(row["count"]) for row in levels}
                if include_logs:
                    samples = con.execute('select level, message, created from "_logs" order by created desc limit 25').fetchall()
                    item["recentSamples"] = [dict(sample) for sample in samples]
            summary["tables"].append(item)
        return summary
    finally:
        con.close()


def main() -> int:
    args = parse_args()
    data_db = pathlib.Path(args.data_db)
    aux_db = pathlib.Path(args.aux_db) if args.aux_db else None
    storage_dir = pathlib.Path(args.storage_dir) if args.storage_dir else None
    export_dir = pathlib.Path(args.export_dir)
    export_dir.mkdir(parents=True, exist_ok=True)

    allowlist = {item.strip() for item in args.collections.split(",") if item.strip()} or None
    if args.batch_size < 1:
        raise ValueError("--batch-size must be greater than 0")

    con = connect_readonly(data_db)
    try:
        collections = read_collections(con)
        tables = sqlite_tables(con)
        selected = [
            collection
            for collection in collections
            if collection_should_export(collection, allowlist, args.include_system, tables)
        ]

        collection_reports = []
        for collection in selected:
            collection_reports.append(
                write_collection(
                    con,
                    collection,
                    export_dir / f"{collection['name']}.json",
                    args.batch_size,
                    args.include_sensitive_identities,
                )
            )

        schema_payload = {
            "source": "pocketbase-sqlite",
            "generatedAt": utc_now_iso(),
            "collections": collections,
        }
        (export_dir / "pb_schema.sqlite.json").write_text(
            json.dumps(schema_payload, ensure_ascii=False, indent=2),
            encoding="utf-8",
            newline="\n",
        )

        storage_manifest = build_storage_manifest(storage_dir, collections) if storage_dir else None
        if storage_manifest:
            (export_dir / "storage-manifest.json").write_text(
                json.dumps(storage_manifest, ensure_ascii=False, indent=2),
                encoding="utf-8",
                newline="\n",
            )

        aux = auxiliary_summary(aux_db, args.include_aux_logs) if aux_db else None

        manifest = {
            "source": "pocketbase-sqlite",
            "generatedAt": utc_now_iso(),
            "dataDb": str(data_db),
            "auxDb": str(aux_db) if aux_db else None,
            "storageDir": str(storage_dir) if storage_dir else None,
            "exportDir": str(export_dir),
            "includeSystemCollections": bool(args.include_system),
            "includeSensitiveIdentities": bool(args.include_sensitive_identities),
            "collectionCount": len(collection_reports),
            "recordCount": sum(int(item["recordCount"]) for item in collection_reports),
            "collections": collection_reports,
            "skippedCollections": [
                {"name": collection.get("name"), "system": bool(collection.get("system")), "type": collection.get("type")}
                for collection in collections
                if collection not in selected
            ],
            "storage": None
            if storage_manifest is None
            else {
                "manifestFile": "storage-manifest.json",
                "fileCount": storage_manifest["fileCount"],
                "totalSize": storage_manifest["totalSize"],
                "collectionCounts": storage_manifest["collectionCounts"],
                "extensionCounts": storage_manifest["extensionCounts"],
            },
            "auxiliary": aux,
        }

        (export_dir / "sqlite-export-manifest.json").write_text(
            json.dumps(manifest, ensure_ascii=False, indent=2),
            encoding="utf-8",
            newline="\n",
        )

        print(json.dumps(manifest, ensure_ascii=False))
        return 0
    finally:
        con.close()


if __name__ == "__main__":
    try:
        raise SystemExit(main())
    except Exception as exc:  # noqa: BLE001 - CLI failure should be explicit.
        print(f"[sqlite-exporter] {exc}", file=sys.stderr)
        raise SystemExit(1)
