#!/usr/bin/env python3
"""
Overlay the production database dump onto the crawl manifest.

The crawl (scripts/legacy_crawl.py) sees only what the public pages render.
The Joomla dump (legacy-import/backup/sho22815_shodhnet.sql.gz) additionally
knows, per phocadownload file: the clean author field, upload date, download
hits, and the published flag — plus files that never appeared on any page.

Reads:
  - legacy-import/manifest.json                 (crawl output)
  - legacy-import/backup/sho22815_shodhnet.sql.gz
Writes:
  - legacy-import/manifest-enriched.json        (same shape, ready for import)
  - legacy-import/db-overlay-report.md          (what changed / what's new)

The enriched manifest keeps the crawl's bilingual title/abstract parsing and
pdf keys, and adds: downloadCount (=hits), authors where the crawl found none,
per-issue publishDate (earliest file upload date in the issue — approximate).
DB-only files are reported, not imported (they are unpublished or orphaned).
"""

import gzip
import json
import re
from datetime import date
from pathlib import Path

REPO_ROOT = Path(__file__).resolve().parent.parent
OUT_DIR = REPO_ROOT / "legacy-import"
DUMP = OUT_DIR / "backup" / "sho22815_shodhnet.sql.gz"

DEVANAGARI_RE = re.compile(r"[ऀ-ॿ]")


def parse_tuples(sql: str, table: str):
    """Yields value-tuples from INSERT statements for `table` (handles quotes/escapes)."""
    for m in re.finditer(r"INSERT INTO `%s`[^;]*?VALUES\s*" % re.escape(table), sql):
        i = m.end()
        while i < len(sql) and sql[i] == "(":
            values, buf, in_str, esc = [], [], False, False
            i += 1
            while i < len(sql):
                c = sql[i]
                if esc:
                    buf.append({"n": "\n", "t": "\t", "r": "\r", "0": "\0"}.get(c, c))
                    esc = False
                elif in_str:
                    if c == "\\":
                        esc = True
                    elif c == "'":
                        in_str = False
                    else:
                        buf.append(c)
                elif c == "'":
                    in_str = True
                elif c == ",":
                    values.append("".join(buf)); buf = []
                elif c == ")":
                    values.append("".join(buf))
                    yield values
                    i += 1
                    break
                else:
                    buf.append(c)
                i += 1
            # skip ",(" between tuples or ";" at end
            while i < len(sql) and sql[i] in ", \n\r\t":
                i += 1


def is_hindi(text):
    return bool(text) and len(DEVANAGARI_RE.findall(text)) > len(text) * 0.2


def main():
    sql = gzip.open(DUMP, "rt", encoding="utf-8", errors="replace").read()
    manifest = json.loads((OUT_DIR / "manifest.json").read_text(encoding="utf-8"))

    # jos_phocadownload columns (from the dump's CREATE TABLE):
    # 0 id, 1 catid, ... 5 title, ... 7 filename, 8 filesize, ... 11 author,
    # 12 author_email, ... 21 description, ... 24 date, ... 27 hits, ... 29 published
    files = {}
    for v in parse_tuples(sql, "jos_phocadownload"):
        files[int(v[0])] = {
            "catid": int(v[1]), "title": v[5].strip(), "filename": v[7].strip(),
            "author": v[11].strip() or None, "authorEmail": v[12].strip() or None,
            "description": v[21].strip() or None,
            "date": v[24][:10] if v[24] and not v[24].startswith("0000") else None,
            "hits": int(v[27] or 0), "published": v[29] == "1",
        }

    crawled_ids = set()
    enriched_authors = counts_set = dates_set = 0

    for issue in manifest["issues"]:
        issue_dates = []
        for art in issue["articles"]:
            fid = art["legacyFileId"]
            crawled_ids.add(fid)
            db = files.get(fid)
            if not db:
                continue
            art["downloadCount"] = db["hits"]
            counts_set += 1
            if db["date"]:
                issue_dates.append(db["date"])
            if db["author"] and not (art.get("authorsHi") or art.get("authorsEn")):
                if is_hindi(db["author"]):
                    art["authorsHi"] = db["author"]
                else:
                    art["authorsEn"] = db["author"]
                enriched_authors += 1
        if issue_dates:
            issue["publishDate"] = min(issue_dates)
            dates_set += 1

    db_only = {fid: f for fid, f in files.items() if fid not in crawled_ids}

    (OUT_DIR / "manifest-enriched.json").write_text(
        json.dumps(manifest, ensure_ascii=False, indent=1), encoding="utf-8")

    lines = [
        "# DB overlay report",
        f"phocadownload rows in dump: {len(files)}  |  crawled files: {len(crawled_ids)}",
        f"download counts applied: {counts_set}  |  authors filled from DB: {enriched_authors}"
        f"  |  issue publish dates set: {dates_set}",
        "",
        f"## Files in DB but never seen by the crawl ({len(db_only)}) — NOT imported",
        "| id | cat | published | hits | title |",
        "|---|---|---|---|---|",
    ]
    for fid, f in sorted(db_only.items()):
        lines.append(f"| {fid} | {f['catid']} | {'yes' if f['published'] else 'NO'} "
                     f"| {f['hits']} | {f['title'][:60]} |")
    (OUT_DIR / "db-overlay-report.md").write_text("\n".join(lines), encoding="utf-8")

    print(f"files in dump: {len(files)} | crawled: {len(crawled_ids)} | db-only: {len(db_only)}")
    print(f"counts applied: {counts_set} | authors filled: {enriched_authors} | dates set: {dates_set}")


if __name__ == "__main__":
    main()