#!/usr/bin/env python3
"""Books ingest + metadata enrichment.

Builds books.works / books.files from:
  * lists.books rows (the old GUI list)                         -> placeholders
  * Obsidian "Book links.md" md5s resolved via the libgen JSON API -> placeholders
  * ebook files dropped in $BOOKS_ROOT/inbox (sha256-deduped)   -> works + files

Metadata: embedded (EPUB OPF / PDF info + ISBN scan) -> Open Library -> Google Books
(-> arXiv for anything tagged paper). Covers go to $BOOKS_ROOT/covers/<work_id>.jpg.
Files are moved to $BOOKS_ROOT/library/<Author>/<Title>/.

Usage:
  DB_URL=postgresql://... BOOKS_ROOT=~/books \
    python scripts/books-ingest.py --obsidian-json resolved.json [--reset] [--dry-run]

Network calls are cached in $BOOKS_ROOT/.cache/http so reruns are cheap and polite.
Refuses to run against the prod database name ('personal') unless --allow-prod.
"""
from __future__ import annotations

import argparse, difflib, hashlib, html, json, os, re, shutil, sys, time, unicodedata, urllib.parse, urllib.request, zipfile
from dataclasses import dataclass, field
from pathlib import Path
from xml.etree import ElementTree as ET

UA = "stan-books-ingest/0.1 (personal library catalogue; low volume)"
LANG = {"eng": "en", "dut": "nl", "nld": "nl", "ger": "de", "deu": "de", "fre": "fr", "fra": "fr", "spa": "es", "ita": "it",
        "por": "pt", "rus": "ru", "jpn": "ja", "kor": "ko", "chi": "zh", "zho": "zh", "lat": "la", "gre": "el", "swe": "sv"}
BOOK_EXT = {".pdf", ".epub", ".mobi", ".azw3", ".azw", ".lit", ".djvu", ".docx", ".fb2", ".cbz"}

# ---------------------------------------------------------------- helpers

def strip_accents(s: str) -> str:
    s = (s or "").replace("'", "").replace("’", "")
    return "".join(c for c in unicodedata.normalize("NFKD", s) if not unicodedata.combining(c))

def norm_title(t: str) -> str:
    t = strip_accents(html.unescape(t or "")).lower().replace("&", " and ")
    t = re.split(r"\s*[:(\[]\s*|\s+[-–—]\s+|_\s", t, maxsplit=1)[0]  # main title only
    t = re.sub(r"[^a-z0-9]+", " ", t).strip()
    t = re.sub(r"^(the|a|an|de|het|een)\s+", "", t)
    t = re.sub(r"\s+(\d+(st|nd|rd|th)?\s+edition|ed)$", "", t)
    return t

def full_norm(t: str) -> str:
    return re.sub(r"[^a-z0-9]+", " ", strip_accents(t or "").lower()).strip()

def sim(a: str, b: str) -> float:
    na, nb = norm_title(a), norm_title(b)
    a, b = full_norm(a), full_norm(b)
    if not a or not b:
        return 0.0
    if a == b:
        return 1.0
    # volume / part / edition markers must agree ("Finance I" vs "Finance II", "Part 1" vs "Part 2")
    marks = lambda x: {t for t in x.split() if re.fullmatch(r"\d{1,2}|[ivx]{1,4}", t)}
    if marks(a) != marks(b) and marks(a) and marks(b):
        return 0.5
    r = difflib.SequenceMatcher(None, a, b).ratio()
    if na and na == nb and len(na) >= 15:
        r = max(r, 0.93)
    return r

def surnames(authors: list[str]) -> set[str]:
    out = set()
    for a in authors or []:
        a = strip_accents(a).lower()
        if "," in a:  # "King, Michael"
            a = a.split(",")[0]
        parts = re.findall(r"[a-z]{3,}", a)
        if parts:
            out.add(parts[-1] if "," not in a else parts[0])
            out.update(parts)
    return out

JUNK_AUTHORS = {"admin", "administrator", "user", "owner", "unknown", "oreily", "oreilly", "o'reilly", "author", "john", "mr2"}

def clean_authors(names) -> list[str]:
    out = []
    for n in names or []:
        n = re.sub(r"\s+", " ", (n or "").strip(" ,;"))
        low = n.lower()
        if not n or low in JUNK_AUTHORS or re.search(r"\d|@|\.(com|org|net)\b", n) or " " not in n and "," not in n and "." not in n:
            continue
        if n.isupper():
            n = n.title()
        out.append(n)
    return out

def authors_compatible(a: list[str], b: list[str]) -> bool:
    if not a or not b:
        return True
    return bool(surnames(a) & surnames(b))

def isbn_ok(s: str) -> str | None:
    s = re.sub(r"[^0-9Xx]", "", s or "").upper()
    if len(s) == 13 and s.isdigit() and s[:3] in ("978", "979"):
        tot = sum(int(c) * (1 if i % 2 == 0 else 3) for i, c in enumerate(s[:12]))
        return s if (10 - tot % 10) % 10 == int(s[12]) else None
    if len(s) == 10 and s[:9].isdigit():
        tot = sum((10 - i) * int(c) for i, c in enumerate(s[:9]))
        chk = (11 - tot % 11) % 11
        return s if (("X" if chk == 10 else str(chk)) == s[9]) else None
    return None

def to13(s: str) -> str | None:
    s = isbn_ok(s)
    if not s:
        return None
    if len(s) == 13:
        return s
    core = "978" + s[:9]
    tot = sum(int(c) * (1 if i % 2 == 0 else 3) for i, c in enumerate(core))
    return core + str((10 - tot % 10) % 10)

def to10(s13: str) -> str | None:
    if not s13 or not s13.startswith("978"):
        return None
    core = s13[3:12]
    tot = sum((10 - i) * int(c) for i, c in enumerate(core))
    chk = (11 - tot % 11) % 11
    return core + ("X" if chk == 10 else str(chk))

ISBN_RE = re.compile(r"ISBN(?:-1[03])?[:\s]*((?:97[89][\s-]?)?(?:\d[\s-]?){9}[\dXx])", re.I)

def find_isbns(text: str) -> list[str]:
    out = []
    for m in ISBN_RE.finditer(text or ""):
        i = to13(m.group(1))
        if i and i not in out:
            out.append(i)
    return out

def safe_name(s: str, n: int = 80) -> str:
    s = strip_accents(s or "").replace("/", "-").replace("\\", "-")
    s = re.sub(r'[<>:"|?*\x00-\x1f]', "", s)
    s = re.sub(r"\s+", " ", s).strip(" .")
    return (s[:n].rstrip(" .") or "Untitled")

def year_of(v) -> int | None:
    m = re.search(r"(1[5-9]\d\d|20\d\d)", str(v or ""))
    return int(m.group(1)) if m else None

# ---------------------------------------------------------------- http with cache

class Http:
    def __init__(self, cache: Path, delay: float = 1.0):
        self.cache, self.delay, self.last = cache, delay, 0.0
        cache.mkdir(parents=True, exist_ok=True)
        self.calls = 0
        self.dead_hosts: set[str] = set()

    def _get(self, url: str, binary=False):
        host = urllib.parse.urlparse(url).netloc
        if host in self.dead_hosts:  # daily quota hit (Google Books): stop hammering it this run
            return 429, b""
        wait = self.delay - (time.time() - self.last)
        if wait > 0:
            time.sleep(wait)
        self.last = time.time()
        self.calls += 1
        req = urllib.request.Request(url, headers={"User-Agent": UA, "Accept": "*/*"})
        for attempt in range(3):
            try:
                with urllib.request.urlopen(req, timeout=30) as r:
                    return r.status, r.read()
            except urllib.error.HTTPError as e:
                if e.code == 429 and "googleapis" in host:
                    self.dead_hosts.add(host)
                    log(f"  {host}: quota exhausted (429), skipping it for the rest of this run")
                    return 429, b""
                if e.code in (429, 503) and attempt < 2:
                    time.sleep(5 * (attempt + 1))
                    continue
                return e.code, b""
            except Exception:
                if attempt < 2:
                    time.sleep(3)
                    continue
                return 0, b""
        return 0, b""

    def json(self, url: str):
        key = self.cache / (hashlib.sha1(url.encode()).hexdigest() + ".json")
        if key.exists():
            return json.loads(key.read_text())
        status, body = self._get(url)
        data = None
        if status == 200:
            try:
                data = json.loads(body)
            except Exception:
                data = None
        if status in (200, 404):  # don't cache transient errors
            key.write_text(json.dumps(data))
        return data

    def text(self, url: str):
        key = self.cache / (hashlib.sha1(url.encode()).hexdigest() + ".txt")
        if key.exists():
            return key.read_text()
        status, body = self._get(url)
        if status == 200:
            key.write_text(body.decode("utf-8", "replace"))
            return body.decode("utf-8", "replace")
        return None

    def download(self, url: str, dest: Path) -> bool:
        status, body = self._get(url, binary=True)
        if status == 200 and len(body) > 1500:  # skip 1x1 placeholders
            dest.write_bytes(body)
            return True
        return False

# ---------------------------------------------------------------- model

@dataclass
class Work:
    title: str
    authors: list[str] = field(default_factory=list)
    subtitle: str | None = None
    isbns: list[str] = field(default_factory=list)  # isbn13s, best first
    publisher: str | None = None
    year: int | None = None
    language: str | None = None
    page_count: int | None = None
    description: str | None = None
    tags: list[str] = field(default_factory=list)
    cover_url: str | None = None
    cover_path: str | None = None
    ol_work: str | None = None
    ol_edition: str | None = None
    gb_id: str | None = None
    md5s: list[str] = field(default_factory=list)
    refs: dict = field(default_factory=dict)
    status: str = "pending"
    source: str | None = None
    notes: str | None = None
    files: list = field(default_factory=list)
    shelves: set = field(default_factory=set)
    origins: set = field(default_factory=set)
    db_id: int | None = None

    def add_ref(self, k, v):
        self.refs.setdefault(k, [])
        if v not in self.refs[k]:
            self.refs[k].append(v)

@dataclass
class FileRec:
    path: Path
    rel_inbox: str
    sha256: str
    md5: str
    size: int
    fmt: str
    title: str | None = None
    authors: list[str] = field(default_factory=list)
    isbns: list[str] = field(default_factory=list)
    year: int | None = None
    publisher: str | None = None
    language: str | None = None
    pages: int | None = None
    is_paper: bool = False
    broken: str | None = None
    dest_rel: str | None = None

# ---------------------------------------------------------------- embedded metadata

def epub_meta(p: Path) -> dict:
    out: dict = {}
    try:
        with zipfile.ZipFile(p) as z:
            cont = ET.fromstring(z.read("META-INF/container.xml"))
            opf_path = next(e.get("full-path") for e in cont.iter() if e.tag.endswith("rootfile"))
            opf = ET.fromstring(z.read(opf_path))
            md = next((e for e in opf.iter() if e.tag.endswith("metadata")), None)
            if md is not None:
                for e in md:
                    tag = e.tag.split("}")[-1]
                    txt = (e.text or "").strip()
                    if not txt:
                        continue
                    if tag == "title" and "title" not in out:
                        out["title"] = txt
                    elif tag == "creator":
                        out.setdefault("authors", []).append(txt)
                    elif tag == "identifier":
                        i = to13(txt.replace("urn:isbn:", ""))
                        if i:
                            out.setdefault("isbns", []).append(i)
                    elif tag == "publisher":
                        out.setdefault("publisher", txt)
                    elif tag == "date":
                        out.setdefault("year", year_of(txt))
                    elif tag == "language":
                        out.setdefault("language", txt[:2].lower())
            # scan the first few content docs (copyright page) for ISBNs
            names = [n for n in z.namelist() if n.lower().endswith((".xhtml", ".html", ".htm"))][:12]
            for n in names:
                for i in find_isbns(re.sub(r"<[^>]+>", " ", z.read(n).decode("utf-8", "ignore"))):
                    out.setdefault("isbns", [])
                    if i not in out["isbns"]:
                        out["isbns"].append(i)
    except Exception as e:  # noqa
        out["error"] = f"epub: {e.__class__.__name__}"
    return out

def pdf_meta(p: Path) -> dict:
    out: dict = {}
    try:
        from pypdf import PdfReader
        import logging
        logging.getLogger("pypdf").setLevel(logging.ERROR)
        r = PdfReader(str(p), strict=False)
        out["pages"] = len(r.pages)
        info = r.metadata or {}
        t = (info.get("/Title") or "").strip() if info else ""
        a = (info.get("/Author") or "").strip() if info else ""
        if t:
            out["title"] = t
        if a:
            out["authors"] = clean_authors(re.split(r";|\band\b|&", a))
        text = ""
        for i in range(min(len(r.pages), 10)):
            try:
                text += (r.pages[i].extract_text() or "") + "\n"
            except Exception:
                pass
        out["isbns"] = find_isbns(text)
        if re.search(r"arXiv:\d{4}\.\d{4,5}", text):
            out["arxiv"] = re.search(r"arXiv:(\d{4}\.\d{4,5})", text).group(1)
    except Exception as e:  # noqa
        out["error"] = f"pdf: {e.__class__.__name__}"
    return out

ANNAS_RE = re.compile(r"^(?P<title>.+?) -- (?P<author>.+?) -- (?P<rest>.+?) -- (?P<md5>[0-9a-f]{32}) -- Anna’s Archive$")

def filename_meta(stem: str) -> dict:
    out: dict = {}
    m = ANNAS_RE.match(stem)
    if m:
        out["title"] = m["title"].replace("_ ", ": ").replace("_", " ")
        out["authors"] = [a.strip().replace("_", ".") for a in m["author"].split(",") if a.strip()]
        out["md5"] = m["md5"]
        for part in m["rest"].split(" -- "):
            i = to13(part)
            if i:
                out.setdefault("isbns", []).append(i)
            elif year_of(part) and "year" not in out:
                out["year"] = year_of(part)
        return out
    s = re.sub(r"\s*-\s*libgen\.(li|rs|is)$", "", stem)
    s = re.sub(r"\s*\((z-library|z-lib\.org|1lib\.\w+)\)", "", s, flags=re.I)
    s = re.sub(r"\s+(penguin (modern )?classics)$", "", s, flags=re.I)
    s = re.sub(r"\s+0$", "", s)
    mm = re.match(r"^([^-]+?) : ([^-]+?) - (.+)$", s)  # "A : B - Title" (libgen multi-author)
    if mm:
        out["authors"] = [mm[1].strip(), mm[2].strip()]
        s = mm[3]
    pm = re.search(r"\s*\((\d{4}),\s*([^)]+)\)\s*$", s)
    if pm:
        out["year"], out["publisher"] = int(pm[1]), pm[2].strip()
        s = s[: pm.start()]
    # "Author - Title" (only when the left part looks like a person name)
    mm = re.match(r"^([A-Z][a-z]+(?: [A-Z][a-z]+){1,2}) - (.+)$", s)
    if mm:
        out["authors"] = [mm[1]]
        s = mm[2]
    mm = re.match(r"^(.+?) by ([A-Z][\w.]+(?: [A-Z][\w.]+){1,2})$", s)
    if mm:
        s, out["authors"] = mm[1], [mm[2]]
    if s.count("-") >= 4 and " " not in s:  # The-Lean-Startup_-How-...-Eric-Ries-2011-Crown-Business-030788791X-
        for tok in s.split("-"):
            i = to13(tok)
            if i:
                out.setdefault("isbns", []).append(i)
        s = s.split("_")[0].replace("-", " ")
    s = s.replace("_ ", ": ").replace("_", " ")
    if s.count(".") >= 3 and s.count(" ") <= 1:  # Sipser_Introduction.to.the.Theory.of.Computation.3E
        s = s.replace(".", " ")
    s = re.sub(r"\s+", " ", s).strip(" -")
    out["title"] = s
    return out

def good_embedded_title(t: str | None, fname_title: str) -> bool:
    if not t or len(t) < 4:
        return False
    bad = ("untitled", "microsoft word", ".doc", ".pdf", ".indd", "layout 1", "unknown", "title", "cover")
    tl = t.lower()
    if any(b in tl for b in bad) or re.fullmatch(r"[\w-]+\.\w+", t) or re.fullmatch(r"[0-9a-f\-_ ]+", tl):
        return False
    return sim(t, fname_title) > 0.55

# ---------------------------------------------------------------- enrichment

class Enricher:
    def __init__(self, http: Http):
        self.h = http

    def ol_isbn(self, isbn: str):
        ed = self.h.json(f"https://openlibrary.org/isbn/{isbn}.json")
        if not ed or "title" not in ed:
            return None
        rec = {"title": ed.get("title"), "subtitle": ed.get("subtitle"), "publisher": (ed.get("publishers") or [None])[0],
               "year": year_of(ed.get("publish_date")), "pages": ed.get("number_of_pages"),
               "ol_edition": ed.get("key", "").split("/")[-1] or None, "isbn": isbn,
               "cover": f"https://covers.openlibrary.org/b/id/{ed['covers'][0]}-L.jpg" if ed.get("covers") and ed["covers"][0] > 0 else None,
               "lang": LANG.get(((ed.get("languages") or [{}])[0].get("key") or "").split("/")[-1])}
        wk = (ed.get("works") or [{}])[0].get("key")
        if wk:
            w = self.h.json(f"https://openlibrary.org{wk}.json") or {}
            rec["ol_work"] = wk.split("/")[-1]
            d = w.get("description")
            rec["description"] = d.get("value") if isinstance(d, dict) else d
            rec["subjects"] = (w.get("subjects") or [])[:8]
            names = []
            for a in (w.get("authors") or [])[:4]:
                k = (a.get("author") or {}).get("key")
                if k:
                    aj = self.h.json(f"https://openlibrary.org{k}.json") or {}
                    if aj.get("name"):
                        names.append(aj["name"])
            rec["authors"] = names
            if not rec["cover"] and w.get("covers") and w["covers"][0] > 0:
                rec["cover"] = f"https://covers.openlibrary.org/b/id/{w['covers'][0]}-L.jpg"
        return rec

    def ol_search(self, title: str, authors: list[str]):
        q = {"title": norm_title(title) if len(norm_title(title)) > 3 else title, "limit": 8,
             "fields": "key,title,subtitle,author_name,first_publish_year,isbn,cover_i,cover_edition_key,language,number_of_pages_median,publisher,subject"}
        if authors:
            q["author"] = re.sub(r"[^\w\s.-]", " ", authors[0])
        d = self.h.json("https://openlibrary.org/search.json?" + urllib.parse.urlencode(q))
        docs = (d or {}).get("docs") or []
        if not docs and authors:
            q.pop("author")
            d = self.h.json("https://openlibrary.org/search.json?" + urllib.parse.urlencode(q))
            docs = (d or {}).get("docs") or []
        best, bs = None, 0.0
        for doc in docs:
            t = doc.get("title", "")
            s = max(sim(title, t), sim(title, f"{t}: {doc.get('subtitle', '')}"))
            if norm_title(title) and norm_title(title) == norm_title(t) and (
                    authors and doc.get("author_name") and authors_compatible(authors, doc["author_name"]) or len(norm_title(t)) >= 15):
                s = max(s, 0.92)
            if authors and doc.get("author_name"):
                s += 0.05 if authors_compatible(authors, doc["author_name"]) else -0.25
            if s > bs:
                best, bs = doc, s
        if not best:
            return None, 0.0
        isbns = [to13(i) for i in (best.get("isbn") or [])]
        isbns = [i for i in isbns if i]
        rec = {"title": best.get("title"), "subtitle": best.get("subtitle"), "authors": best.get("author_name") or [],
               "year": best.get("first_publish_year"), "ol_work": best.get("key", "").split("/")[-1] or None,
               "ol_edition": best.get("cover_edition_key"), "pages": best.get("number_of_pages_median"),
               "publisher": (best.get("publisher") or [None])[0],
               "cover": f"https://covers.openlibrary.org/b/id/{best['cover_i']}-L.jpg" if best.get("cover_i") else None,
               "lang": LANG.get((best.get("language") or [""])[0]), "subjects": (best.get("subject") or [])[:8],
               "isbn": next((i for i in isbns if i.startswith("978")), isbns[0] if isbns else None)}
        if rec["ol_work"]:
            w = self.h.json(f"https://openlibrary.org/works/{rec['ol_work']}.json") or {}
            dsc = w.get("description")
            rec["description"] = dsc.get("value") if isinstance(dsc, dict) else dsc
        return rec, min(bs, 1.0)

    def gb(self, title: str | None, authors: list[str], isbn: str | None = None):
        if isbn:
            q = f"isbn:{isbn}"
        else:
            q = f'intitle:"{norm_title(title) or title}"' + (f" inauthor:{surnames(authors[:1]).pop()}" if authors and surnames(authors[:1]) else "")
        d = self.h.json("https://www.googleapis.com/books/v1/volumes?" + urllib.parse.urlencode({"q": q, "maxResults": 8, "printType": "books"}))
        items = (d or {}).get("items") or []
        best, bs = None, 0.0
        for it in items:
            v = it.get("volumeInfo", {})
            if isbn:
                s = 1.0
            else:
                s = max(sim(title, v.get("title", "")), sim(title, f"{v.get('title', '')}: {v.get('subtitle', '')}"))
                if norm_title(title) and norm_title(title) == norm_title(v.get("title", "")) and (
                        authors and v.get("authors") and authors_compatible(authors, v["authors"]) or len(norm_title(title)) >= 15):
                    s = max(s, 0.92)
                if authors and v.get("authors"):
                    s += 0.05 if authors_compatible(authors, v["authors"]) else -0.25
            if s > bs:
                best, bs = it, s
            if isbn:
                break
        if not best:
            return None, 0.0
        v = best["volumeInfo"]
        ids = {x.get("type"): x.get("identifier") for x in v.get("industryIdentifiers", [])}
        img = (v.get("imageLinks") or {})
        cover = img.get("extraLarge") or img.get("large") or img.get("medium") or img.get("thumbnail")
        if cover:
            cover = cover.replace("http://", "https://").replace("&edge=curl", "")
        return {"title": v.get("title"), "subtitle": v.get("subtitle"), "authors": v.get("authors") or [],
                "publisher": v.get("publisher"), "year": year_of(v.get("publishedDate")), "pages": v.get("pageCount"),
                "description": re.sub(r"<[^>]+>", "", v.get("description") or "") or None,
                "lang": (v.get("language") or "")[:2] or None, "gb_id": best.get("id"), "cover": cover,
                "isbn": to13(ids.get("ISBN_13") or ids.get("ISBN_10") or ""), "subjects": v.get("categories") or []}, min(bs, 1.0)

    def arxiv(self, title: str, arxiv_id: str | None = None):
        if arxiv_id:
            url = f"https://export.arxiv.org/api/query?id_list={arxiv_id}"
        else:
            t = re.sub(r"[^\w\s]", " ", title)
            url = "https://export.arxiv.org/api/query?" + urllib.parse.urlencode({"search_query": f'ti:"{t}"', "max_results": 5})
        x = self.h.text(url)
        if not x:
            return None, 0.0
        ns = {"a": "http://www.w3.org/2005/Atom"}
        try:
            root = ET.fromstring(x)
        except ET.ParseError:
            return None, 0.0
        best, bs = None, 0.0
        for e in root.findall("a:entry", ns):
            t = re.sub(r"\s+", " ", e.findtext("a:title", "", ns)).strip()
            if not t or t == "Error":
                continue
            s = 1.0 if arxiv_id else sim(title, t)
            if s > bs:
                best, bs = (e, t), s
        if not best:
            return None, 0.0
        e, t = best
        return {"title": t, "authors": [a.findtext("a:name", "", ns) for a in e.findall("a:author", ns)],
                "year": year_of(e.findtext("a:published", "", ns)),
                "description": re.sub(r"\s+", " ", e.findtext("a:summary", "", ns)).strip(),
                "arxiv": e.findtext("a:id", "", ns).split("/abs/")[-1]}, bs

# ---------------------------------------------------------------- main

def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--obsidian-json", help="md5 -> libgen record map from the resolver")
    ap.add_argument("--extra", action="append", default=[], help='extra placeholder "Title|Author|isbn13|origin"')
    ap.add_argument("--reset", action="store_true", help="truncate books.works/files first (scratch DBs only)")
    ap.add_argument("--dry-run", action="store_true", help="no DB writes, no file moves; write plan json")
    ap.add_argument("--no-network", action="store_true")
    ap.add_argument("--allow-prod", action="store_true")
    ap.add_argument("--source", default="gdrive")
    args = ap.parse_args()

    root = Path(os.path.expanduser(os.environ.get("BOOKS_ROOT", "/srv/books")))
    inbox, library, covers = root / "inbox", root / "library", root / "covers"
    for d in (inbox, library, covers):
        d.mkdir(parents=True, exist_ok=True)
    db_url = os.environ.get("DB_URL")
    if not db_url:
        sys.exit("DB_URL required")
    dbname = urllib.parse.urlparse(db_url).path.lstrip("/")
    if dbname == "personal" and not args.allow_prod:
        sys.exit("refusing to run against prod DB 'personal' (use --allow-prod)")

    import psycopg
    conn = psycopg.connect(db_url)
    http = Http(root / ".cache" / "http", delay=0.0 if args.no_network else 1.0)
    en = Enricher(http)
    works: list[Work] = []
    log = lambda *a: print(*a, flush=True)  # noqa

    # ---- 1. placeholders: lists.books
    with conn.cursor() as cur:
        cur.execute("select to_regclass('lists.books')")
        has_lists = cur.fetchone()[0] is not None
        rows = []
        if has_lists:
            cur.execute("select id, name, author, notes, done_at from lists.books order by sort_order nulls last, id")
            rows = cur.fetchall()
    for bid, name, author, notes, done_at in rows:
        w = Work(title=name.strip(), authors=[author.strip()] if author else [], notes=notes or None)
        w.add_ref("lists_books_id", bid)
        w.origins.add("lists")
        w.shelves.add("done" if done_at else "to-read")
        if done_at:
            w.refs["done_at"] = done_at.isoformat()
        works.append(w)
    n_lists = len(rows)

    def find_work(title, authors, isbns, md5=None):
        for w in works:
            if md5 and md5 in w.md5s:
                return w
            if isbns and set(isbns) & set(w.isbns) and (not title or not w.title or sim(title, w.title) >= 0.85):
                return w
        best, bs = None, 0.0
        for w in works:
            s = sim(title, w.title)
            if s >= 0.9 and authors_compatible(authors, w.authors) and s > bs:
                best, bs = w, s
        return best

    def merge(w: Work, title, authors, isbns, **kw):
        if authors and not w.authors:
            w.authors = authors
        for i in isbns or []:
            if i not in w.isbns:
                w.isbns.append(i)
        for k in ("year", "publisher", "language"):
            if kw.get(k) and not getattr(w, k):
                setattr(w, k, kw[k])
        # prefer the longer, properly-cased title when the list name is a lowercase shorthand
        if title and (w.title.islower() or len(norm_title(w.title)) < len(norm_title(title)) and sim(w.title, title) > 0.9 and w.title == w.title.lower()):
            w.title = title

    # ---- 2. placeholders: Obsidian md5s
    obs_total = obs_resolved = obs_overlap = 0
    if args.obsidian_json:
        data = json.load(open(args.obsidian_json))
        for md5, rec in data.items():
            obs_total += 1
            eds = (rec or {}).get("editions") or []
            if not eds:
                continue
            obs_resolved += 1
            ed = next((e for e in eds if e.get("isbns")), eds[0])
            isbns = []
            for e in eds:
                for i in e.get("isbns") or []:
                    i13 = to13(i)
                    if i13 and i13 not in isbns:
                        isbns.append(i13)
            title = (ed.get("title") or rec.get("locator") or md5).strip()
            authors = [a.strip() for a in re.split(r";|, (?=[A-Z][a-z]+ [A-Z])", ed.get("author") or "") if a.strip()]
            lang = LANG.get((ed.get("lang") or [""])[0], None) if isinstance(ed.get("lang"), list) else None
            w = find_work(title, authors, isbns, md5)
            if w:
                if "obsidian" not in w.origins:
                    obs_overlap += 1
                merge(w, title, authors, isbns, year=year_of(ed.get("year")), publisher=ed.get("publisher") or None, language=lang)
            else:
                w = Work(title=title, authors=authors, isbns=isbns, year=year_of(ed.get("year")), publisher=ed.get("publisher") or None, language=lang)
                works.append(w)
                w.shelves.add("to-read")
            w.origins.add("obsidian")
            if md5 not in w.md5s:
                w.md5s.append(md5)
            w.add_ref("obsidian", {"md5": md5, "ext": rec.get("ext"), "filesize": rec.get("filesize")})
    for ex in args.extra:
        t, a, i, origin = (ex.split("|") + ["", "", "", ""])[:4]
        isbns = [to13(i)] if to13(i) else []
        authors = [a] if a else []
        w = find_work(t, authors, isbns)
        if w:
            merge(w, t, authors, isbns)
        else:
            w = Work(title=t, authors=authors, isbns=isbns)
            works.append(w)
            w.shelves.add("to-read")
        w.origins.add("obsidian")
        w.add_ref("obsidian_link", origin)
    n_placeholders = len(works)

    # ---- 3. files in inbox
    files: list[FileRec] = []
    for p in sorted(inbox.rglob("*")):
        if not p.is_file() or p.name.startswith("."):
            continue
        h256, h5 = hashlib.sha256(), hashlib.md5()
        with open(p, "rb") as f:
            for chunk in iter(lambda: f.read(1 << 20), b""):
                h256.update(chunk)
                h5.update(chunk)
        rel = str(p.relative_to(inbox))
        ext = p.suffix.lower()
        with open(p, "rb") as hf:
            head = hf.read(8)
        fmt = ext.lstrip(".") or "bin"
        if head.startswith(b"%PDF"):
            fmt = "pdf"
        elif head.startswith(b"PK") and ext in (".epub", ""):
            fmt = "epub"
        fr = FileRec(path=p, rel_inbox=rel, sha256=h256.hexdigest(), md5=h5.hexdigest(), size=p.stat().st_size, fmt=fmt)
        fr.is_paper = rel.split("/")[0].lower() == "papers"
        if ext == ".crdownload":
            fr.broken = "partial Chrome download (.crdownload)"
            fr.fmt = "pdf" if head.startswith(b"%PDF") else "partial"
        files.append(fr)
    by_sha: dict[str, FileRec] = {}
    dups: list[tuple[FileRec, FileRec]] = []
    uniq: list[FileRec] = []
    for fr in files:
        if fr.sha256 in by_sha:
            dups.append((fr, by_sha[fr.sha256]))
        else:
            by_sha[fr.sha256] = fr
            uniq.append(fr)
    log(f"files: {len(files)} total, {len(uniq)} unique, {len(dups)} sha256 duplicates")

    # already-ingested shas (rerun without --reset)
    with conn.cursor() as cur:
        cur.execute("select sha256 from books.files")
        known = {r[0] for r in cur.fetchall()} if not args.reset else set()

    matched_existing = 0
    for fr in uniq:
        if fr.sha256 in known:
            continue
        stem = Path(fr.rel_inbox).name
        stem = stem[: -len(Path(stem).suffix)] if Path(stem).suffix.lower() in BOOK_EXT | {".crdownload"} else stem
        fm = filename_meta(stem)
        em = epub_meta(fr.path) if fr.fmt == "epub" else pdf_meta(fr.path) if fr.fmt == "pdf" and not fr.broken else {}
        fr.title = fm.get("title") or stem
        if fr.fmt == "epub" and good_embedded_title(em.get("title"), fr.title):
            fr.title = em["title"]
        fr.authors = fm.get("authors") or [a for a in em.get("authors", []) if len(a) > 2 and "unknown" not in a.lower()][:4]
        fr.isbns = list(dict.fromkeys((fm.get("isbns") or []) + (em.get("isbns") or [])))[:6]
        fr.year = fm.get("year") or em.get("year")
        fr.publisher = fm.get("publisher") or em.get("publisher")
        fr.language = em.get("language")
        fr.pages = em.get("pages")
        md5 = fm.get("md5") or fr.md5
        w = find_work(fr.title, fr.authors, fr.isbns[:2], md5) or (find_work(fr.title, fr.authors, [], fr.md5))
        if w and w.files == [] and w.origins & {"lists", "obsidian"} and "drive" not in w.origins:
            matched_existing += 1
        if w:
            merge(w, fr.title, fr.authors, fr.isbns[:2], year=fr.year, publisher=fr.publisher, language=fr.language)
        else:
            w = Work(title=fr.title, authors=fr.authors, isbns=fr.isbns[:2], year=fr.year, publisher=fr.publisher, language=fr.language, page_count=fr.pages)
            works.append(w)
        if fr.is_paper and "paper" not in w.tags:
            w.tags.append("paper")
        if fr.md5 not in w.md5s and fm.get("md5"):
            w.md5s.append(fm["md5"])
        w.origins.add("drive")
        w.add_ref("drive", fr.rel_inbox)
        if em.get("arxiv"):
            w.refs["arxiv"] = em["arxiv"]
        if fr.broken:
            w.notes = ((w.notes + "\n") if w.notes else "") + f"Drive copy is broken: {fr.broken}; needs a fresh copy."
            w.add_ref("broken_files", fr.rel_inbox)
        else:
            w.files.append(fr)
    for d, orig in dups:
        for w in works:
            if orig in w.files:
                w.add_ref("drive_duplicates", d.rel_inbox)
    log(f"works: {len(works)} ({n_placeholders} placeholders before files; {matched_existing} placeholders got files)")

    # ---- 4. enrichment
    stats = {"matched": 0, "low_confidence": 0, "not_found": 0}
    for n, w in enumerate(works, 1):
        if args.no_network:
            break
        rec, score, src = None, 0.0, None
        if "paper" in w.tags:
            rec, score = en.arxiv(w.title, w.refs.get("arxiv"))
            src = "arxiv"
        else:
            for isbn in w.isbns[:3]:
                rec = en.ol_isbn(isbn)
                if rec and (sim(w.title, rec["title"]) > 0.6 or sim(w.title, f"{rec['title']}: {rec.get('subtitle') or ''}") > 0.6 or "drive" not in w.origins):
                    score, src = 1.0, "openlibrary"
                    break
                rec = None
            if not rec:
                rec, score = en.ol_search(w.title, w.authors)
                src = "openlibrary"
                if score < 0.9:
                    g, gs = en.gb(None, [], w.isbns[0]) if w.isbns else (None, 0.0)
                    if g and sim(w.title, g["title"] or "") < 0.6 and sim(w.title, f"{g['title']}: {g.get('subtitle') or ''}") < 0.6:
                        g, gs = None, 0.0
                    if not g:
                        g, gs = en.gb(w.title, w.authors)
                    if g and gs > score:
                        rec, score, src = g, gs, "googlebooks"
        if rec and score >= 0.9:
            w.status = "matched"
        elif rec and score >= 0.75:
            w.status = "low_confidence"
        else:
            w.status = "not_found"
        stats[w.status] += 1
        if rec:
            w.refs["match"] = {"source": src, "score": round(score, 3), "title": rec.get("title"), "authors": rec.get("authors")}
        if w.status == "matched":
            w.source = src
            if src == "openlibrary" or src == "googlebooks":
                # keep Stan's/the file's title wording unless it was a lowercase list shorthand or a filename
                # keep Stan's own list wording; otherwise take the catalogue's properly-cased title
                if rec.get("title") and ("lists" not in w.origins or w.title.islower()):
                    w.title = rec["title"]
                w.subtitle = w.subtitle or rec.get("subtitle")
            elif src == "arxiv":
                w.title = rec["title"]
            if rec.get("authors"):
                w.authors = rec["authors"][:6]
            w.year = w.year or rec.get("year")
            if src == "openlibrary" and rec.get("year") and w.year and rec["year"] < w.year:
                w.year = w.year  # edition year stays; first-publish year recorded in refs
                w.refs["first_publish_year"] = rec["year"]
            w.publisher = w.publisher or rec.get("publisher")
            w.page_count = w.page_count or rec.get("pages")
            w.language = w.language or rec.get("lang")
            w.description = w.description or rec.get("description")
            if rec.get("isbn") and rec["isbn"] not in w.isbns:
                w.isbns.insert(0 if not w.isbns else len(w.isbns), rec["isbn"])
            w.ol_work = w.ol_work or rec.get("ol_work")
            w.ol_edition = w.ol_edition or rec.get("ol_edition")
            w.gb_id = w.gb_id or rec.get("gb_id")
            w.cover_url = w.cover_url or rec.get("cover")
            if rec.get("arxiv"):
                w.refs["arxiv"] = rec["arxiv"]
            for s in (rec.get("subjects") or [])[:5]:
                s = s.lower().strip()
                if 2 < len(s) < 40 and s not in w.tags:
                    w.tags.append(s)
        if n % 25 == 0:
            log(f"  enriched {n}/{len(works)} ({http.calls} http calls)")
    if args.no_network:
        stats = {"skipped": len(works)}
    log("metadata:", stats)

    # cover fallback for matched works lacking one: Google Books by isbn
    if not args.no_network:
        for w in works:
            if not w.cover_url and w.status == "matched" and "paper" not in w.tags and w.isbns:
                g, _ = en.gb(None, [], w.isbns[0])
                if g and g.get("cover"):
                    w.cover_url = g["cover"]
                    w.gb_id = w.gb_id or g.get("gb_id")

    # ---- 5. plan destinations
    used: set[str] = set()
    for w in works:
        author_dir = safe_name(w.authors[0] if w.authors else "Unknown", 60)
        title_dir = safe_name(w.title, 90)
        for fr in w.files:
            base = f"{author_dir}/{title_dir}/{title_dir}"
            rel = f"{base}.{fr.fmt}"
            k = 2
            while rel in used or (library / rel).exists() and not args.reset:
                rel = f"{base} ({k}).{fr.fmt}"
                k += 1
            used.add(rel)
            fr.dest_rel = rel

    plan = [{"title": w.title, "authors": w.authors, "isbns": w.isbns, "status": w.status, "origins": sorted(w.origins),
             "files": [{"from": f.rel_inbox, "to": f.dest_rel, "sha256": f.sha256} for f in w.files], "refs": w.refs,
             "cover": w.cover_url} for w in works]
    (root / ".cache").mkdir(exist_ok=True)
    (root / ".cache" / "ingest-plan.json").write_text(json.dumps(plan, indent=1, default=str))
    if args.dry_run:
        log("dry run: plan written to", root / ".cache" / "ingest-plan.json")
        return

    # ---- 6. write DB
    def isbn13(w):
        return next((i for i in w.isbns if i.startswith("978") or i.startswith("979")), None)
    seen13 = set()
    from datetime import datetime, timezone
    now = datetime.now(timezone.utc)
    with conn.transaction():
        cur = conn.cursor()
        if args.reset:
            cur.execute("truncate books.notes, books.progress, books.shelf_works, books.files, books.works restart identity cascade")
        cur.execute("select slug, id from books.shelves")
        shelves = dict(cur.fetchall())
        for w in works:
            i13 = isbn13(w)
            if i13 in seen13:
                w.refs["isbn13_shared_with_other_work"] = i13
                i13 = None
            if i13:
                seen13.add(i13)
            refs = dict(w.refs)
            refs["origins"] = sorted(w.origins)
            if len(w.isbns) > 1:
                refs["all_isbns"] = w.isbns
            cur.execute(
                """insert into books.works (title, subtitle, authors, isbn10, isbn13, publisher, year, language, page_count,
                       description, tags, cover_url, openlibrary_work_id, openlibrary_edition_id, google_books_id, annas_md5,
                       source_refs, metadata_status, metadata_source, metadata_fetched_at, notes)
                   values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) returning id""",
                (w.title, w.subtitle, w.authors, to10(i13) if i13 else None, i13, w.publisher,
                 w.year if w.year and 1400 < w.year < 2100 else None, w.language, w.page_count, w.description, w.tags,
                 w.cover_url, w.ol_work, w.ol_edition, w.gb_id, w.md5s, json.dumps(refs, default=str), w.status,
                 w.source, now if w.status != "pending" else None, w.notes))
            w.db_id = cur.fetchone()[0]
            for fr in w.files:
                cur.execute("insert into books.files (work_id, path, format, size_bytes, sha256, md5, source) values (%s,%s,%s,%s,%s,%s,%s)",
                            (w.db_id, fr.dest_rel, fr.fmt, fr.size, fr.sha256, fr.md5, args.source))
            for s in sorted(w.shelves):
                if s in shelves:
                    cur.execute("insert into books.shelf_works (shelf_id, work_id) values (%s,%s) on conflict do nothing", (shelves[s], w.db_id))
            if "done" in w.shelves and w.refs.get("done_at"):
                cur.execute("insert into books.progress (work_id, percent, finished_at) values (%s, 100, %s) on conflict do nothing", (w.db_id, w.refs["done_at"]))

    # ---- 7. covers
    got = 0
    if not args.no_network:
        for w in works:
            if not w.cover_url:
                continue
            dest = covers / f"{w.db_id}.jpg"
            if dest.exists() or http.download(w.cover_url, dest):
                got += 1
                with conn.cursor() as cur:
                    cur.execute("update books.works set cover_path=%s where id=%s", (dest.name, w.db_id))
        conn.commit()

    # ---- 8. move files (inbox -> library); write undo manifest
    manifest = root / ".cache" / f"moves-{time.strftime('%Y%m%d-%H%M%S')}.tsv"
    dupdir = root / "duplicates"
    qdir = root / "quarantine"
    with open(manifest, "w") as mf:
        for w in works:
            for fr in w.files:
                dest = library / fr.dest_rel
                dest.parent.mkdir(parents=True, exist_ok=True)
                shutil.move(str(fr.path), dest)
                mf.write(f"{fr.path}\t{dest}\n")
        for d, _ in dups:
            dest = dupdir / d.rel_inbox
            dest.parent.mkdir(parents=True, exist_ok=True)
            shutil.move(str(d.path), dest)
            mf.write(f"{d.path}\t{dest}\n")
        for fr in uniq:
            if fr.broken and fr.path.exists():
                dest = qdir / fr.rel_inbox
                dest.parent.mkdir(parents=True, exist_ok=True)
                shutil.move(str(fr.path), dest)
                mf.write(f"{fr.path}\t{dest}\n")
    for d in sorted(inbox.rglob("*"), reverse=True):
        if d.is_dir() and not any(d.iterdir()):
            d.rmdir()

    summary = {
        "lists_rows": n_lists, "obsidian_md5s": obs_total, "obsidian_resolved": obs_resolved,
        "obsidian_overlapping_lists": obs_overlap, "placeholders": n_placeholders,
        "files_total": len(files), "files_unique": len(uniq), "sha256_duplicates": len(dups),
        "placeholders_with_files": matched_existing, "works_total": len(works),
        "works_with_files": sum(1 for w in works if w.files), "metadata": stats, "covers": got,
        "papers": sum(1 for w in works if "paper" in w.tags), "http_calls": http.calls, "manifest": str(manifest),
    }
    (root / ".cache" / "ingest-summary.json").write_text(json.dumps(summary, indent=1))
    print(json.dumps(summary, indent=1))

if __name__ == "__main__":
    main()