face_db.py

"""
Geometry-based face metadata database.

Each face gets a fingerprint from its geometry (surface type, centroid, area,
bounding box dimensions, edge/vertex counts).  Metadata is stored in SQLite.

Matching uses TWO strategies:
  1. Exact hash match (fast, covers same-kernel round-trips)
  2. Fuzzy match with tolerance (covers cross-kernel round-trips like SolidWorks)
     Topology must match exactly (surface type, edge count, vertex count).
     Continuous values (centroid, area, dimensions) use configurable tolerance.
"""

import hashlib
import json
import math
import os
import sqlite3
from datetime import datetime, timezone

from OCP.BRepAdaptor import BRepAdaptor_Surface
from OCP.BRepBndLib import BRepBndLib
from OCP.BRepGProp import BRepGProp
from OCP.Bnd import Bnd_Box
from OCP.GProp import GProp_GProps
from OCP.TopAbs import TopAbs_EDGE, TopAbs_VERTEX
from OCP.TopExp import TopExp_Explorer

# ── Config ───────────────────────────────────────────────────────────────────
DB_PATH = os.path.join(os.path.dirname(os.path.abspath(__file__)), "stepviewer.db")

# Tolerance for fuzzy matching (absolute, in model units — typically mm)
FUZZY_TOL_POSITION = 0.01      # centroid: 10 microns
FUZZY_TOL_AREA     = 0.1       # area: 0.1 mm^2
FUZZY_TOL_DIM      = 0.01      # bbox dimensions: 10 microns


def _get_conn() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH)
    conn.execute("PRAGMA journal_mode=WAL")
    return conn


def init_db():
    """Create/migrate tables."""
    conn = _get_conn()
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS face_meta (
            face_hash   TEXT PRIMARY KEY,
            meta_json   TEXT NOT NULL,
            updated_at  TEXT NOT NULL,
            -- Raw fingerprint values for fuzzy matching
            surf_type   INTEGER,
            cx          REAL,
            cy          REAL,
            cz          REAL,
            area        REAL,
            dx          REAL,
            dy          REAL,
            dz          REAL,
            n_edges     INTEGER,
            n_verts     INTEGER
        );

        -- Index for fuzzy lookups (filter by topology first, then range-check numerics)
        CREATE INDEX IF NOT EXISTS idx_face_topo
            ON face_meta (surf_type, n_edges, n_verts);
    """)
    conn.close()


# ── Face fingerprinting ─────────────────────────────────────────────────────

def _norm(val):
    """Normalize -0.0 to 0.0 for consistent hashing."""
    return val + 0.0


def face_fingerprint_raw(face) -> dict:
    """
    Compute raw fingerprint values for a TopoDS_Face.
    Returns a dict with all the numeric components.
    """
    # Surface type
    surf = BRepAdaptor_Surface(face)
    surf_type = int(surf.GetType())

    # Area + centroid
    props = GProp_GProps()
    BRepGProp.SurfaceProperties_s(face, props)
    area = _norm(props.Mass())
    cm = props.CentreOfMass()
    cx, cy, cz = _norm(cm.X()), _norm(cm.Y()), _norm(cm.Z())

    # Bounding box dimensions (sorted, orientation-independent)
    bbox = Bnd_Box()
    BRepBndLib.Add_s(face, bbox)
    xmin, ymin, zmin, xmax, ymax, zmax = bbox.Get()
    dims = sorted([
        _norm(abs(xmax - xmin)),
        _norm(abs(ymax - ymin)),
        _norm(abs(zmax - zmin)),
    ])

    # Topology counts
    n_edges = 0
    exp_e = TopExp_Explorer(face, TopAbs_EDGE)
    while exp_e.More():
        n_edges += 1
        exp_e.Next()

    n_verts = 0
    exp_v = TopExp_Explorer(face, TopAbs_VERTEX)
    while exp_v.More():
        n_verts += 1
        exp_v.Next()

    return {
        "surf_type": surf_type,
        "cx": cx, "cy": cy, "cz": cz,
        "area": area,
        "dx": dims[0], "dy": dims[1], "dz": dims[2],
        "n_edges": n_edges, "n_verts": n_verts,
    }


def face_fingerprint(face) -> str:
    """
    Compute a 16-char hex hash from face geometry.
    Uses 3-decimal rounding for the hash (exact match within same kernel).
    """
    raw = face_fingerprint_raw(face)
    canonical = (
        f"T{raw['surf_type']}|"
        f"C{round(raw['cx'],3)},{round(raw['cy'],3)},{round(raw['cz'],3)}|"
        f"A{round(raw['area'],3)}|"
        f"D{round(raw['dx'],3)},{round(raw['dy'],3)},{round(raw['dz'],3)}|"
        f"E{raw['n_edges']}V{raw['n_verts']}"
    )
    return hashlib.sha256(canonical.encode()).hexdigest()[:16]


# ── Database operations ──────────────────────────────────────────────────────

def save_face_meta(face_hash: str, meta: dict, raw: dict = None):
    """Upsert metadata + raw fingerprint values for a face hash."""
    conn = _get_conn()
    now = datetime.now(timezone.utc).isoformat()
    if raw:
        conn.execute(
            """INSERT OR REPLACE INTO face_meta
               (face_hash, meta_json, updated_at,
                surf_type, cx, cy, cz, area, dx, dy, dz, n_edges, n_verts)
               VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)""",
            (face_hash, json.dumps(meta), now,
             raw["surf_type"], raw["cx"], raw["cy"], raw["cz"], raw["area"],
             raw["dx"], raw["dy"], raw["dz"], raw["n_edges"], raw["n_verts"]),
        )
    else:
        conn.execute(
            "INSERT OR REPLACE INTO face_meta (face_hash, meta_json, updated_at) VALUES (?,?,?)",
            (face_hash, json.dumps(meta), now),
        )
    conn.commit()
    conn.close()


def lookup_faces_batch(face_hashes: list[str],
                       face_raws: list[dict],
                       tol_pos=FUZZY_TOL_POSITION,
                       tol_area=FUZZY_TOL_AREA,
                       tol_dim=FUZZY_TOL_DIM) -> dict[str, dict]:
    """
    Batch lookup using temp table JOIN + indexed fuzzy queries.
    Returns {hash: meta_dict} for all faces that found a match.

    Strategy 1: Exact hash match via temp table JOIN (no IN clauses).
    Strategy 2: Fuzzy match via indexed SQL per unique topology signature.
    """
    if not face_hashes:
        return {}

    conn = _get_conn()
    result = {}

    # ── 1. Exact hash match (temp table + JOIN) ─────────────────────────
    conn.execute("CREATE TEMP TABLE _lookup_hashes (h TEXT PRIMARY KEY)")
    conn.executemany(
        "INSERT OR IGNORE INTO _lookup_hashes (h) VALUES (?)",
        [(h,) for h in face_hashes]
    )
    rows = conn.execute(
        """SELECT fm.face_hash, fm.meta_json
           FROM face_meta fm
           JOIN _lookup_hashes lh ON fm.face_hash = lh.h"""
    ).fetchall()
    conn.execute("DROP TABLE _lookup_hashes")
    for fh, mj in rows:
        result[fh] = json.loads(mj)

    # ── 2. Fuzzy match for misses (SQL with topology index) ─────────────
    # Group unmatched faces by topology signature to minimize queries
    topo_groups = {}  # (surf_type, n_edges, n_verts) -> [(hash, raw), ...]
    for h, raw in zip(face_hashes, face_raws):
        if h in result or raw is None:
            continue
        key = (raw["surf_type"], raw["n_edges"], raw["n_verts"])
        topo_groups.setdefault(key, []).append((h, raw))

    for (surf_type, n_edges, n_verts), faces in topo_groups.items():
        # One SQL query per topology signature — uses idx_face_topo index
        candidates = conn.execute(
            """SELECT face_hash, meta_json, cx, cy, cz, area, dx, dy, dz
               FROM face_meta
               WHERE surf_type = ? AND n_edges = ? AND n_verts = ?""",
            (surf_type, n_edges, n_verts)
        ).fetchall()

        if not candidates:
            continue

        for h, raw in faces:
            best = None
            best_dist = float("inf")

            for fh, mj, cx, cy, cz, area, dx, dy, dz in candidates:
                if (abs(cx - raw["cx"]) > tol_pos or
                    abs(cy - raw["cy"]) > tol_pos or
                    abs(cz - raw["cz"]) > tol_pos):
                    continue
                if abs(area - raw["area"]) > tol_area:
                    continue
                if (abs(dx - raw["dx"]) > tol_dim or
                    abs(dy - raw["dy"]) > tol_dim or
                    abs(dz - raw["dz"]) > tol_dim):
                    continue

                dist = math.sqrt(
                    (cx - raw["cx"])**2 + (cy - raw["cy"])**2 + (cz - raw["cz"])**2
                    + (area - raw["area"])**2
                )
                if dist < best_dist:
                    best_dist = dist
                    best = (fh, mj)

            if best:
                result[h] = json.loads(best[1])

    conn.close()
    return result


def delete_face_meta(face_hash: str):
    conn = _get_conn()
    conn.execute("DELETE FROM face_meta WHERE face_hash = ?", (face_hash,))
    conn.commit()
    conn.close()


def delete_faces(face_hashes: list[str]):
    """Delete metadata for a list of face hashes."""
    if not face_hashes:
        return
    conn = _get_conn()
    conn.execute("CREATE TEMP TABLE _del_hashes (h TEXT PRIMARY KEY)")
    conn.executemany(
        "INSERT OR IGNORE INTO _del_hashes (h) VALUES (?)",
        [(h,) for h in face_hashes]
    )
    conn.execute(
        "DELETE FROM face_meta WHERE face_hash IN (SELECT h FROM _del_hashes)"
    )
    conn.execute("DROP TABLE _del_hashes")
    conn.commit()
    conn.close()


def clear_database():
    """Delete ALL entries from face_meta (Global Nuke)."""
    conn = _get_conn()
    conn.execute("DELETE FROM face_meta")
    conn.commit()
    conn.close()


def get_db_stats() -> dict:
    conn = _get_conn()
    count = conn.execute("SELECT COUNT(*) FROM face_meta").fetchone()[0]
    conn.close()
    return {"total_faces": count, "db_path": DB_PATH}


# Initialize on import
init_db()