{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "782d203a",
   "metadata": {},
   "source": [
    "# DOCX & MD → Markdown for Open WebUI Knowledge Bases\n",
    "\n",
    "Convert `.docx` and `.md` files into clean Markdown optimized for embedding into **Open WebUI** knowledge bases.\n",
    "\n",
    "**Why this matters for Open WebUI:** Open WebUI supports *Markdown Header Splitting* (H1–H6) when chunking documents for RAG — it splits on headers first, then merges tiny fragments via the *Chunk Min Size Target* setting. So a converter that preserves clean, well-nested heading hierarchy produces far better retrieval chunks than flat text. [Open WebUI RAG docs](https://docs.openwebui.com/features/chat-conversations/rag/)\n",
    "\n",
    "This notebook is designed for the **Colab free tier**:\n",
    "- No GPU needed (CPU only).\n",
    "- Lightweight pip installs (`mammoth`, `python-docx`, `markdownify`, `requests`, `beautifulsoup4`).\n",
    "- Inputs and outputs are mirrored to Google Drive so the work survives Colab wipes.\n",
    "\n",
    "**What this notebook does:**\n",
    "1. Mounts your Google Drive and uses the persistent folder `/drive/MyDrive/to_convert/` to track every file you’ve ever processed.\n",
    "2. Accepts `.docx` and `.md` inputs (uploaded directly, or auto-extracted from a `.tar` archive in Drive).\n",
    "3. **Dedupes by content hash** (SHA-256 of the original bytes) — if you re-run a file with a slightly different filename, the manifest catches it and skips. If the bytes actually differ, it reconverts the new content.\n",
    "4. Converts via the same mammoth pipeline + python-docx fallback as the previous notebook (all heading-structure cleanup preserved).\n",
    "5. Cleans up raw `.md` inputs through the same post-processing pipeline (strip cite markers, collapse HR, promote bold to headings, collapse extra H1s).\n",
    "6. Extracts every `http(s)://` URL from each converted file and appends to `sources.csv` (paired with the source doc’s SHA + stem).\n",
    "7. Optionally bulk-downloads those URLs (PDFs first, then HTML), tracked in `downloads.csv`.\n",
    "\n",
    "**Pipeline (per file):**\n",
    "- DOCX: `mammoth(html) → clean_markdown → strip_masthead → strip_cite_markers → collapse_hr → promote_structure → collapse_extra_h1 → conditional synthetic H1 prepend`\n",
    "- MD: `clean_markdown → strip_masthead → strip_cite_markers → collapse_hr → promote_structure → collapse_extra_h1 → conditional synthetic H1 prepend` (no mammoth pass)\n",
    "\n",
    "> ⚠️ Open WebUI's Temporary Chat mode disables backend parsing for complex DOCX, so it's better to upload **pre-converted Markdown** like the output of this notebook rather than raw DOCX.\n",
    "\n",
    "> **Auto heading promotion:** For Gemini-Notebook-style DOCX files that use bold paragraphs (not Word heading styles) for section titles, the converter promotes those bold lines into real `## / ###` Markdown headings so Open WebUI's Markdown Header Splitter actually has structure to chunk on. It also strips `[cite: N]` Gemini citation artifacts and the redundant in-document title (the filename stem becomes the single `#` H1).\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c00618e1",
   "metadata": {},
   "source": [
    "## 0. Tips before you run\n",
    "\n",
    "- **Heading styles matter.** Mammoth relies on Word *paragraph styles* (e.g. `Heading 1`, `Heading 2`) to produce `#`, `##`. If your document was authored with manual bold/large fonts instead of styles, headings may come out as plain paragraphs. You can remap styles via the `STYLE_MAP` cell below.\n",
    "- **One source Word style → one Markdown heading level.** Keep it linear (Heading 1 → `#`, Heading 2 → `##`, …). Open WebUI's header splitter rewards clean hierarchies.\n",
    "- **Chunk size tuning happens in Open WebUI**, not here. After upload, set `Chunk Size` (~1000–2000 chars) and `Chunk Min Size Target` (e.g. ~half the chunk size) under **Admin → Tools → Documents**. The docs note this can cut chunk counts by >90% while improving retrieval quality.- **If your DOCX uses real Word heading styles** (Heading 1/2/...),\n",
    "  Mammoth emits them as `# / ## / ...` directly and the promotion step is\n",
    "  a no-op for those lines. The promotion only fires on *bold-only* paragraphs,\n",
    "  which is how the Gemini Notebook exports are structured.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "390e295a",
   "metadata": {},
   "source": [
    "## 0. Install dependencies\\n\\nLightweight and CPU-only."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ca2584b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Run once per session. ~5–10 seconds on free tier.\n",
    "%pip -q install mammoth==1.9.1 markdownify python-docx==1.1.2 requests beautifulsoup4"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75bc63c5",
   "metadata": {},
   "source": [
    "## 1. Mount Google Drive and create the folder tree\n",
    "\n",
    "You create the empty folder `to_convert/` on your Google Drive **once** (at `/My Drive/to_convert/`). The notebook then creates the rest of the tree on first run:\n",
    "\n",
    "```\n",
    "/drive/MyDrive/to_convert/\n",
    "├── manifest.csv            ← SHA-256 dedup history (append-only)\n",
    "├── archive/                ← original input bytes, durable across Colab wipes\n",
    "├── markdown_output/        ← converted .md files\n",
    "├── sources.csv             ← doc → URLs (append-only)\n",
    "└── downloads/              ← bulk URL fetch output\n",
    "    ├── pdf/   html/   other/\n",
    "    └── downloads.csv       ← URL → saved file (append-only)\n",
    "```\n",
    "\n",
    "The manifest is the source of truth for \"have I run this file before?\" — it stores the SHA-256 of the *original input bytes*, so renaming a file does NOT cause a reconvert."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0bd89046",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os, shutil, csv, hashlib, re, datetime, time\n",
    "from pathlib import Path\n",
    "from google.colab import drive\n",
    "\n",
    "drive.mount('/content/drive')\n",
    "\n",
    "DRIVE_ROOT     = Path('/content/drive/MyDrive/to_convert')\n",
    "ARCHIVE_DIR    = DRIVE_ROOT / 'archive'\n",
    "MD_OUTPUT_DIR  = DRIVE_ROOT / 'markdown_output'\n",
    "DOWNLOADS_DIR  = DRIVE_ROOT / 'downloads'\n",
    "DOWNLOADS_CSV  = DOWNLOADS_DIR / 'downloads.csv'\n",
    "MANIFEST_PATH  = DRIVE_ROOT / 'manifest.csv'\n",
    "SOURCES_PATH   = DRIVE_ROOT / 'sources.csv'\n",
    "\n",
    "# Create the folder tree (mkdir -p). User just creates /to_convert/ once on Drive.\n",
    "for p in (DRIVE_ROOT, ARCHIVE_DIR, MD_OUTPUT_DIR,\n",
    "          DOWNLOADS_DIR, DOWNLOADS_DIR / 'pdf',\n",
    "          DOWNLOADS_DIR / 'html', DOWNLOADS_DIR / 'other'):\n",
    "    p.mkdir(parents=True, exist_ok=True)\n",
    "\n",
    "# Local scratch input dir (wiped each run so uploads don't accumulate).\n",
    "INPUT_DIR = Path('/content/docx_input')\n",
    "if INPUT_DIR.exists():\n",
    "    shutil.rmtree(INPUT_DIR)\n",
    "INPUT_DIR.mkdir(parents=True, exist_ok=True)\n",
    "\n",
    "print(f'Drive root:  {DRIVE_ROOT}')\n",
    "print(f'Local input: {INPUT_DIR}')\n",
    "print(f'MD output:   {MD_OUTPUT_DIR}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27740394",
   "metadata": {},
   "source": [
    "## 2. Provide `.docx` and/or `.md` inputs\n",
    "\n",
    "Two ways to get inputs into the notebook:\n",
    "\n",
    "1. **Auto-extract from a `.tar`/`.tgz` archive in Drive.** If you keep an archive at `/drive/MyDrive/zzz-new_shit/`, the cell below will copy and extract it automatically. Edit `DRIVE_ARCHIVE_SOURCE_DIR` if your archive lives elsewhere.\n",
    "2. **Upload via the Colab file picker.** If no archive is found (or you comment out the archive path), the cell falls back to `google.colab.files.upload()` so you can hand-pick files.\n",
    "\n",
    "The cell accepts both `.docx` and `.md` extensions and merges them into one `input_files` list."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f12edf78",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os, shutil, tarfile\n",
    "from pathlib import Path\n",
    "from google.colab import files as colab_files\n",
    "\n",
    "# Edit this to point at a Drive folder that holds your archive, or comment it out to use the upload fallback.\n",
    "DRIVE_ARCHIVE_SOURCE_DIR = Path('/content/drive/MyDrive/zzz-new_shit')\n",
    "\n",
    "tar_archive_path = None\n",
    "if DRIVE_ARCHIVE_SOURCE_DIR.exists():\n",
    "    for item in DRIVE_ARCHIVE_SOURCE_DIR.glob('*.tar'):\n",
    "        if item.is_file():\n",
    "            tar_archive_path = item\n",
    "            break\n",
    "    if not tar_archive_path:\n",
    "        for item in DRIVE_ARCHIVE_SOURCE_DIR.glob('*.tgz'):\n",
    "            if item.is_file():\n",
    "                tar_archive_path = item\n",
    "                break\n",
    "\n",
    "if tar_archive_path:\n",
    "    print(f\"Found archive: {tar_archive_path.name}\")\n",
    "    temp_archive_path = Path('/content') / tar_archive_path.name\n",
    "    print(f\"Copying '{tar_archive_path}' to '{temp_archive_path}'...\")\n",
    "    shutil.copy2(tar_archive_path, temp_archive_path)\n",
    "    print(\"Copy complete.\")\n",
    "    print(f\"Extracting '{temp_archive_path}' to '{INPUT_DIR}'...\")\n",
    "    with tarfile.open(temp_archive_path, \"r\") as tar:\n",
    "        tar.extractall(path=INPUT_DIR, filter=\"data\")\n",
    "    print(\"Extraction complete.\")\n",
    "    temp_archive_path.unlink()\n",
    "else:\n",
    "    print(f\"No archive in {DRIVE_ARCHIVE_SOURCE_DIR} — using Colab upload picker.\")\n",
    "    print(\"Pick .docx and/or .md files in the dialog that appears.\")\n",
    "    uploaded = colab_files.upload()\n",
    "    if not uploaded:\n",
    "        raise RuntimeError(\"No files uploaded.\")\n",
    "    for name, data in uploaded.items():\n",
    "        (INPUT_DIR / name).write_bytes(data)\n",
    "    print(f\"Uploaded {len(uploaded)} file(s) into {INPUT_DIR}.\")\n",
    "\n",
    "# Discover inputs (recursively, in case the tar extracted into subdirs).\n",
    "input_files = sorted([*INPUT_DIR.glob('**/*.docx'), *INPUT_DIR.glob('**/*.md')])\n",
    "n_docx = sum(1 for f in input_files if f.suffix.lower() == '.docx')\n",
    "n_md   = sum(1 for f in input_files if f.suffix.lower() == '.md')\n",
    "\n",
    "print(f\"\\nFound {len(input_files)} input file(s): {n_docx} .docx, {n_md} .md\")\n",
    "for f in input_files:\n",
    "    print('  •', f.relative_to(INPUT_DIR))\n",
    "\n",
    "# Snapshot the original upload list so the pre-flight dedup cell (5b) can be\n",
    "# re-run safely without the list shrinking on each pass (idempotent).\n",
    "uploaded_files_master = list(input_files)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f526dc6",
   "metadata": {},
   "source": [
    "## 3. Config & Mammoth style map\n",
    "\n",
    "Configure dedup/archival/download flags here. The style map below is preserved from the previous notebook — it maps Word `Title`/`Heading N` styles to HTML `<hN>` tags so mammoth emits real headings."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "584e5c0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# === Config ===\n",
    "SKIP_ALREADY_SEEN = True     # if True, skip files whose sha256 is already in manifest.csv\n",
    "ARCHIVE_INPUTS    = True     # copy each input's bytes to archive/ for future hash matching\n",
    "DOWNLOAD_SOURCES  = False   # only the explicit downloader cell flips this on\n",
    "RATE_LIMIT_SEC    = 1.0     # politeness delay between URL fetches\n",
    "\n",
    "# Pre-flight near-duplicate detection (cell 5b). Two files count as 'the same doc'\n",
    "# when their overlapping-fragment ratio is >= this threshold. 0.97 means '~95-97%'\n",
    "# chunk overlap leaves room for tiny edits (a single cite marker, a timestamp).\n",
    "# Set to 1.0 to disable fuzzy dedup entirely (then only exact byte-hashes dedup).\n",
    "# Lower to 0.90 to be more aggressive (riskier -- might merge docs that share a lot\n",
    "# of boilerplate but ARE different).\n",
    "DEDUP_SIMILARITY_THRESHOLD = 0.97\n",
    "DEDUP_CHUNK_SIZE = 500        # chars per fragment in the overlapping-fragment signature\n",
    "\n",
    "# Optional Mammoth style map. Maps Word paragraph styles to HTML headings\n",
    "# so mammoth emits real <h1>/<h2>/<h3> instead of generic <p>. The converter's\n",
    "# collapse_extra_h1() then keeps the first H1 (the doc title) and demotes any\n",
    "# additional H1s to H2 so every file has exactly ONE H1 (required by Open WebUI's\n",
    "# Markdown Header Splitter, which treats each H1 as a new document section).\n",
    "CUSTOM_STYLE_MAP = \"\"\"\n",
    "p[style-name='Title'] => h1:fresh\n",
    "p[style-name='Subtitle'] => h2:fresh\n",
    "p[style-name='Heading 1'] => h1:fresh\n",
    "p[style-name='Heading 2'] => h2:fresh\n",
    "p[style-name='Heading 3'] => h3:fresh\n",
    "p[style-name='Heading 4'] => h4:fresh\n",
    "\"\"\"\n",
    "\n",
    "# If True, images are extracted to a per-doc `media/` folder and referenced\n",
    "# in Markdown as !media/<name>. Open WebUI currently indexes text only, so\n",
    "# inlined image tags are harmless but won't add embedding signal.\n",
    "EXTRACT_IMAGES = True"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f947cd2",
   "metadata": {},
   "source": [
    "## 4. Helpers — manifest, hash, URL extraction\n",
    "\n",
    "These functions handle the persistence bookkeeping:\n",
    "\n",
    "- `sha256_of_file` streams the file in 1MB chunks (safe for big docx).\n",
    "- `load_manifest` / `append_manifest_row` keep `manifest.csv` append-only and Sheets-friendly (`newline=''`).\n",
    "- `extract_urls` pulls every full `http(s)://` URL out of converted markdown (no bare-domain guessing — research citations use full URLs).\n",
    "- `classify_url` sorts each URL into `pdf` / `html` / `other` by its path extension. The downloader cell does a HEAD request later for Content-Type confirmation; this lightweight classifier is for the per-run summary."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9eda6cb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv, hashlib, re, datetime\n",
    "from pathlib import Path\n",
    "\n",
    "MANIFEST_COLS  = ['sha256', 'first_seen_filename', 'first_seen_at',\n",
    "                  'last_processed_at', 'input_ext', 'output_path',\n",
    "                  'output_bytes', 'status']\n",
    "SOURCES_COLS   = ['doc_sha256', 'doc_stem', 'url', 'url_type']\n",
    "DOWNLOADS_COLS = ['url', 'sha256_of_bytes', 'saved_path',\n",
    "                  'http_status', 'content_type', 'downloaded_at']\n",
    "\n",
    "def sha256_of_file(path: Path) -> str:\n",
    "    \"\"\"Stream-hash a file in 1MB chunks; returns hex digest.\"\"\"\n",
    "    h = hashlib.sha256()\n",
    "    with open(path, 'rb') as f:\n",
    "        for chunk in iter(lambda: f.read(1024 * 1024), b''):\n",
    "            h.update(chunk)\n",
    "    return h.hexdigest()\n",
    "\n",
    "def _now_iso() -> str:\n",
    "    return datetime.datetime.now(datetime.timezone.utc).isoformat(timespec='seconds')\n",
    "\n",
    "def load_manifest() -> list:\n",
    "    if not MANIFEST_PATH.exists():\n",
    "        return []\n",
    "    with open(MANIFEST_PATH, 'r', newline='') as f:\n",
    "        return list(csv.DictReader(f))\n",
    "\n",
    "def append_manifest_row(row: dict):\n",
    "    exists = MANIFEST_PATH.exists()\n",
    "    with open(MANIFEST_PATH, 'a', newline='') as f:\n",
    "        w = csv.DictWriter(f, fieldnames=MANIFEST_COLS)\n",
    "        if not exists:\n",
    "            w.writeheader()\n",
    "        w.writerow({k: row.get(k, '') for k in MANIFEST_COLS})\n",
    "\n",
    "def is_seen(sha256: str, manifest: list):\n",
    "    for r in manifest:\n",
    "        if r.get('sha256') == sha256:\n",
    "            return r\n",
    "    return None\n",
    "\n",
    "_URL_RE = re.compile(r'https?://[^\\s\\)\\]\\>\\}\\,]+')\n",
    "def extract_urls(text: str) -> list:\n",
    "    \"\"\"Extract full http(s):// URLs, stripping trailing punctuation.\"\"\"\n",
    "    urls, seen = [], set()\n",
    "    for m in _URL_RE.findall(text):\n",
    "        u = m.rstrip('.,);:>]}>')\n",
    "        if u and u not in seen:\n",
    "            seen.add(u)\n",
    "            urls.append(u)\n",
    "    return urls\n",
    "\n",
    "def classify_url(url: str) -> str:\n",
    "    \"\"\"Classify a URL by its path extension (no network call).\"\"\"\n",
    "    path = url.split('?')[0].split('#')[0].lower()\n",
    "    if path.endswith('.pdf'):\n",
    "        return 'pdf'\n",
    "    return 'html'\n",
    "\n",
    "def append_source_rows(doc_sha256: str, doc_stem: str, urls: list):\n",
    "    exists = SOURCES_PATH.exists()\n",
    "    with open(SOURCES_PATH, 'a', newline='') as f:\n",
    "        w = csv.DictWriter(f, fieldnames=SOURCES_COLS)\n",
    "        if not exists:\n",
    "            w.writeheader()\n",
    "        for u in urls:\n",
    "            w.writerow({'doc_sha256': doc_sha256, 'doc_stem': doc_stem,\n",
    "                        'url': u, 'url_type': classify_url(u)})\n",
    "\n",
    "# --- Pre-flight fuzzy-dedup helpers -------------------------------------------\n",
    "# Two files count as \"the same document\" when their overlapping-fragment ratio\n",
    "# is >= DEDUP_SIMILARITY_THRESHOLD. We extract raw text fast (python-docx for\n",
    "# .docx, plain read for .md), normalize away everything that's not 'real text'\n",
    "# (whitespace, punctuation, urls, the gemini masthead, leading '# title'), and\n",
    "# split into DEDUP_CHUNK_SIZE-character sliding windows keyed by sha-256. The\n",
    "# Jaccard similarity of those two sets ~= fraction of overlapping content.\n",
    "DEDUP_DROPS_CSV = DRIVE_ROOT / 'dedup_drops.csv'\n",
    "DEDUP_DROPS_COLS = ['kept_filename', 'kept_sha256', 'dropped_filename',\n",
    "                    'dropped_sha256', 'similarity', 'drop_reason', 'dropped_at']\n",
    "\n",
    "# Lightweight fallback for extracting text from a .docx WITHOUT running mammoth\n",
    "# (which is slow and can crash). Uses python-docx (already installed). Returns\n",
    "# an empty string on any failure so the file is still hashed and compared but\n",
    "# with effectively no content signal.\n",
    "try:\n",
    "    import docx as _pdocx_dedupe\n",
    "except Exception:\n",
    "    _pdocx_dedupe = None\n",
    "\n",
    "def _raw_text_for_dedup(path: Path) -> str:\n",
    "    \"\"\"Get a raw-text signal from a .docx or .md for near-duplicate comparison.\n",
    "\n",
    "    On any failure returns '' - the caller will then fall back to byte-hash dedup.\n",
    "    \"\"\"\n",
    "    ext = path.suffix.lower()\n",
    "    try:\n",
    "        if ext == '.md':\n",
    "            return path.read_text(encoding='utf-8', errors='replace')\n",
    "        if ext == '.docx':\n",
    "            if _pdocx_dedupe is None:\n",
    "                return ''\n",
    "            d = _pdocx_dedupe.Document(str(path))\n",
    "            parts = []\n",
    "            for p in d.paragraphs:\n",
    "                t = p.text\n",
    "                if t:\n",
    "                    parts.append(t)\n",
    "            # also pull table text (some real content lives there)\n",
    "            for tbl in d.tables:\n",
    "                for row in tbl.rows:\n",
    "                    for cell in row.cells:\n",
    "                        for p in cell.paragraphs:\n",
    "                            if p.text:\n",
    "                                parts.append(p.text)\n",
    "            return '\\n'.join(parts)\n",
    "    except Exception:\n",
    "        return ''\n",
    "    return ''\n",
    "\n",
    "_NORM_WS = re.compile(r'\\s+')\n",
    "_NORM_PUNCT = re.compile(r'[^a-z0-9 ]')\n",
    "_URL_STRIP_RE = re.compile(r'https?://\\S+')\n",
    "_MASTHEAD_STRIP_RE = re.compile(r'Created By:.*?DATE:[^\\n]*', re.I | re.S)\n",
    "\n",
    "def _normalize_for_dedup(text: str) -> str:\n",
    "    \"\"\"Aggressively normalize for robust near-duplicate comparison.\n",
    "\n",
    "    Removes: URLs (they make every research doc look 100% like another),\n",
    "    the Gemini masthead block, whitespace, and punctuation. Lowercases.\n",
    "    \"\"\"\n",
    "    if not text:\n",
    "        return ''\n",
    "    text = _URL_STRIP_RE.sub(' ', text)\n",
    "    text = _MASTHEAD_STRIP_RE.sub(' ', text)\n",
    "    text = text.lower()\n",
    "    text = _NORM_PUNCT.sub(' ', text)\n",
    "    text = _NORM_WS.sub(' ', text).strip()\n",
    "    return text\n",
    "\n",
    "def _chunk_set(text: str, chunk_size: int) -> set:\n",
    "    \"\"\"Build a set of sha-256 hex digests, one per non-empty non-overlapping\n",
    "    chunk_size-char window. Used as a fast Jaccard signature.\n",
    "    \"\"\"\n",
    "    if not text:\n",
    "        return set()\n",
    "    chunks = set()\n",
    "    i = 0\n",
    "    n = len(text)\n",
    "    while i < n:\n",
    "        piece = text[i:i + chunk_size].strip()\n",
    "        if piece:\n",
    "            chunks.add(hashlib.sha256(piece.encode('utf-8')).hexdigest())\n",
    "        i += chunk_size\n",
    "    return chunks\n",
    "\n",
    "def _jaccard(a: set, b: set) -> float:\n",
    "    \"\"\"Jaccard overlap ratio. Returns 0.0 for two empty sets (treat as not-same).\"\"\"\n",
    "    if not a or not b:\n",
    "        return 0.0\n",
    "    inter = len(a & b)\n",
    "    union = len(a | b)\n",
    "    return inter / union if union else 0.0\n",
    "\n",
    "def append_dedup_drop_row(row: dict):\n",
    "    exists = DEDUP_DROPS_CSV.exists()\n",
    "    with open(DEDUP_DROPS_CSV, 'a', newline='') as f:\n",
    "        w = csv.DictWriter(f, fieldnames=DEDUP_DROPS_COLS)\n",
    "        if not exists:\n",
    "            w.writeheader()\n",
    "        w.writerow({k: row.get(k, '') for k in DEDUP_DROPS_COLS})\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "209d68fa",
   "metadata": {},
   "source": [
    "## 5. Conversion helpers (mammoth pipeline + cleanup)\n",
    "\n",
    "Preserved verbatim from the tested notebook. These functions chain together to turn a Word doc into clean Markdown with exactly one H1 and good H2/H3 structure. The `convert_one()` function is the docx entry point; `_convert_via_python_docx()` is the fallback when mammoth crashes (e.g. the `_accept0` bug). `clean_md_input()` is new — it runs the same cleanup pipeline on raw uploaded `.md` files (no mammoth pass needed)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4742867",
   "metadata": {},
   "outputs": [],
   "source": [
    "import mammoth\n",
    "from markdownify import markdownify as md\n",
    "import re\n",
    "from pathlib import Path\n",
    "\n",
    "def strip_masthead(text: str) -> str:\n",
    "    \"\"\"Remove the Gemini Notebook authorship block.\n",
    "\n",
    "    Every Gemini-authored DOCX starts with a masthead like:\n",
    "        —\n",
    "        Created By: <author>\n",
    "        APP: Gemini Notebook\n",
    "        DATE: <date>\n",
    "        —\n",
    "    (sometimes bold-wrapped). It is pure authorship metadata with zero retrieval\n",
    "    value; we drop it along with its surrounding standalone em-dash dividers.\n",
    "    Pattern-based so it works regardless of where it sits in the pipeline.\n",
    "    \"\"\"\n",
    "    lines = text.split('\\n')\n",
    "    MASTHEAD_RE = re.compile(r'^(\\*\\*)?(Created By:|APP:\\s*Gemini|DATE:)', re.I)\n",
    "    EMDASH_RE = re.compile(r'^(\\*\\*)?—(\\*\\*)?$')\n",
    "    drop_idx = set()\n",
    "    for i, ln in enumerate(lines):\n",
    "        s = ln.strip()\n",
    "        if MASTHEAD_RE.match(s):\n",
    "            # drop this masthead line, plus adjacent em-dash dividers\n",
    "            drop_idx.add(i)\n",
    "            # scan outward for wrapping em-dashes / blank-bounded dashes\n",
    "            j = i - 1\n",
    "            while j >= 0 and (lines[j].strip() == '' or EMDASH_RE.match(lines[j].strip())):\n",
    "                if EMDASH_RE.match(lines[j].strip()):\n",
    "                    drop_idx.add(j)\n",
    "                    break\n",
    "                j -= 1\n",
    "            j = i + 1\n",
    "            while j < len(lines) and (lines[j].strip() == '' or EMDASH_RE.match(lines[j].strip())):\n",
    "                if EMDASH_RE.match(lines[j].strip()):\n",
    "                    drop_idx.add(j)\n",
    "                    break\n",
    "                j += 1\n",
    "            # also fold in multi-line masthead (e.g. 'Created By: X\\nAPP: Y' wrapped in **)\n",
    "            # by scanning forward through consecutive masthead/em-dash/blank lines\n",
    "            k = i + 1\n",
    "            while k < len(lines):\n",
    "                ks = lines[k].strip()\n",
    "                if MASTHEAD_RE.match(ks) or EMDASH_RE.match(ks) or ks == '':\n",
    "                    if MASTHEAD_RE.match(ks) or EMDASH_RE.match(ks):\n",
    "                        drop_idx.add(k)\n",
    "                    k += 1\n",
    "                else:\n",
    "                    break\n",
    "    if not drop_idx:\n",
    "        return text\n",
    "    out = [ln for idx, ln in enumerate(lines) if idx not in drop_idx]\n",
    "    # collapse any triple+ blanks created by removal\n",
    "    return re.sub(r'\\n{3,}', '\\n\\n', '\\n'.join(out))\n",
    "\n",
    "def _make_converter(extract_images: bool, out_dir: Path, doc_stem: str):\n",
    "    \"\"\"Build a mammoth image converter that saves media to disk.\"\"\"\n",
    "    if not extract_images:\n",
    "        return None\n",
    "    media_dir = out_dir / doc_stem / 'media'\n",
    "    media_dir.mkdir(parents=True, exist_ok=True)\n",
    "    counter = {'n': 0}\n",
    "\n",
    "    def convert_image(image):\n",
    "        counter['n'] += 1\n",
    "        ext = (image.content_type or 'image/png').split('/')[-1].replace('jpeg', 'jpg')\n",
    "        fname = f\"image_{counter['n']:03d}.{ext}\"\n",
    "        with image.open() as f:\n",
    "            (media_dir / fname).write_bytes(f.read())\n",
    "        rel = f\"{doc_stem}/media/{fname}\"\n",
    "        return {'src': rel}\n",
    "    return convert_image\n",
    "\n",
    "def clean_markdown(text: str) -> str:\n",
    "    \"\"\"Remove Word conversion artifacts and normalize whitespace.\"\"\"\n",
    "    text = re.sub(r'<a[^>]*></a>', '', text)\n",
    "    text = re.sub(r'<a\\s+(?:[^>]*?)>', '', text)\n",
    "    text = text.replace('</a>', '')\n",
    "    text = re.sub(r'\\n{3,}', '\\n\\n', text)\n",
    "    text = '\\n'.join(line.rstrip() for line in text.splitlines())\n",
    "    text = re.sub(r'(?m)^#{1,6}\\s*$', '', text)\n",
    "    return text.strip() + '\\n'\n",
    "\n",
    "def strip_cite_markers(text: str) -> str:\n",
    "    \"\"\"Remove Gemini Notebook citation artifacts like '[cite: 1, 2, 3]'.\n",
    "    These reference internal citation numbers that mean nothing to an LLM\n",
    "    or embedding model and just waste tokens / add noise to retrieval.\"\"\"\n",
    "    return re.sub(r'\\s*\\[cite:\\s*[\\d,\\s]+\\]', '', text)\n",
    "\n",
    "def collapse_hr(text: str) -> str:\n",
    "    \"\"\"Replace 20+ dash horizontal-rule lines with a sentinel '<HR>' we can\n",
    "    key section boundaries off of, and drop them as content (a bare HR would\n",
    "    just become dead weight inside Open WebUI chunks).\"\"\"\n",
    "    return re.sub(r'(?m)^-{20,}$', '<HR>', text)\n",
    "\n",
    "def promote_structure(text: str) -> str:\n",
    "    \"\"\"Promote implicit Word structure into real Markdown headings.\n",
    "\n",
    "    These Gemini-authored DOCX files use bold-only paragraphs (NOT Word\n",
    "    'Heading 1/2' styles) for section titles, and a row of dashes as a\n",
    "    top-level section divider. Mammoth therefore emits them as <strong>/bold,\n",
    "    and Open WebUI's Markdown Header Splitter would have nothing to split on.\n",
    "\n",
    "    Rules:\n",
    "      - A standalone line fully wrapped in **...** (and NOT inside a table,\n",
    "        i.e. not starting with '|'), <=160 chars and not a bare '—'\n",
    "        -> candidate heading.\n",
    "      - A bold heading immediately after a former HR -> '## ' (H2 section).\n",
    "      - Any other bold heading -> '### ' (H3 subsection).\n",
    "      - Table-cell bold stays inside '| ... |' rows and is never promoted.\n",
    "      - collapse_extra_h1() (later in the pipeline) is what guarantees\n",
    "        exactly one H1 per file, by keeping the first real H1 (or by\n",
    "        convert_one prepending a synthetic '# <stem>' if none exists).\n",
    "    \"\"\"\n",
    "    lines = text.split('\\n')\n",
    "    out = []\n",
    "    after_hr = False\n",
    "    BOLD_RE = re.compile(r'^\\*\\*(.+?)\\*\\*$')\n",
    "    for ln in lines:\n",
    "        stripped = ln.strip()\n",
    "        if stripped == '<HR>':\n",
    "            after_hr = True\n",
    "            continue\n",
    "        if not stripped:\n",
    "            out.append(ln)\n",
    "            continue\n",
    "        m = BOLD_RE.match(stripped)\n",
    "        if m and not stripped.startswith('|'):\n",
    "            inner = m.group(1).strip()\n",
    "            # collapse adjacent '**A** **B** **C**' style runs into one title\n",
    "            inner = re.sub(r'\\*\\*\\s*\\*\\*', ' ', inner)\n",
    "            inner = re.sub(r'\\s+', ' ', inner).strip()\n",
    "            if len(inner) <= 160 and inner != '—':\n",
    "                # Promote this bold paragraph to a heading. The first bold line is\n",
    "                # NOT special-cased anymore: collapse_extra_h1() ensures exactly one\n",
    "                # H1 per file (the doc's real Title style or the synthetic '# <stem>'),\n",
    "                # so dropping the first bold would just lose a legitimate section\n",
    "                # header like 'Alpha Section'.\n",
    "                level = '##' if after_hr else '###'\n",
    "                out.append(f'{level} {inner}')\n",
    "                after_hr = False\n",
    "                continue\n",
    "        after_hr = False\n",
    "        out.append(ln)\n",
    "    return '\\n'.join(out)\n",
    "\n",
    "def collapse_extra_h1(text: str):\n",
    "    \"\"\"Ensure exactly one H1 per file.\n",
    "\n",
    "    Some source DOCX files use Word's 'Title' or 'Heading 1' paragraph style, which\n",
    "    mammoth converts to real <h1> -> '# ' headings. Combined with the synthetic\n",
    "    '# <stem>' H1 that convert_one prepends, this produces files with 2+ H1s.\n",
    "    Open WebUI's Markdown Header Splitter treats every H1 as the start of a new\n",
    "    document section, so multiple H1s fragment retrieval badly.\n",
    "\n",
    "    Returns (new_text, has_h1): if the doc already has at least one real H1, we keep\n",
    "    the first one and demote any subsequent H1s to H2; the caller then SKIPS prepending\n",
    "    the synthetic '# <stem>' title (since the doc already has a title H1).\n",
    "    \"\"\"\n",
    "    lines = text.split('\\n')\n",
    "    h1_seen = False\n",
    "    out = []\n",
    "    for ln in lines:\n",
    "        m = re.match(r'^(#{1})\\s+(.*)', ln)\n",
    "        if m:\n",
    "            if not h1_seen:\n",
    "                # Keep the first H1 as the document title.\n",
    "                h1_seen = True\n",
    "                out.append(ln)\n",
    "            else:\n",
    "                # Demote every subsequent H1 to H2 so there is exactly one H1.\n",
    "                out.append(f'## {m.group(2)}')\n",
    "        else:\n",
    "            out.append(ln)\n",
    "    return '\\n'.join(out), h1_seen\n",
    "\n",
    "def convert_one(docx_path: Path, out_root: Path, style_map: str, extract_images: bool):\n",
    "    \"\"\"Convert a single .docx to .md. Returns (out_file, warnings).\"\"\"\n",
    "    stem = docx_path.stem\n",
    "    with open(docx_path, 'rb') as f:\n",
    "        kwargs = dict()\n",
    "        if style_map:\n",
    "            kwargs['style_map'] = style_map\n",
    "        if extract_images:\n",
    "            kwargs['convert_image'] = _make_converter(True, out_root, stem)\n",
    "        result = mammoth.convert_to_html(f, **kwargs)\n",
    "\n",
    "    html = result.value\n",
    "    markdown = md(html, heading_style='ATX', bullets='-', strip=['a'])\n",
    "    # Pipeline order matters: strip noise -> collapse HR -> promote structure\n",
    "    markdown = clean_markdown(markdown)\n",
    "    markdown = strip_masthead(markdown)\n",
    "    markdown = strip_cite_markers(markdown)\n",
    "    markdown = collapse_hr(markdown)\n",
    "    markdown = promote_structure(markdown)\n",
    "    markdown, has_h1 = collapse_extra_h1(markdown)\n",
    "\n",
    "    # Synthetic H1 from the source filename stem gives every knowledge-base\n",
    "    # chunk a stable, cite-friendly document title (Open WebUI shows this).\n",
    "    if not has_h1:\n",
    "        # No real H1 in the source -> use the filename stem as the title.\n",
    "        markdown = f\"# {stem}\\n\\n\" + markdown.strip() + \"\\n\"\n",
    "    else:\n",
    "        # Source already has an H1 (e.g. Word Title style); keep it as the doc title\n",
    "        # and do NOT prepend a duplicate synthetic H1 (would re-introduce the 2x-H1 bug).\n",
    "        markdown = markdown.strip() + \"\\n\"\n",
    "\n",
    "    out_file = out_root / f\"{stem}.md\"\n",
    "    out_file.write_text(markdown, encoding='utf-8')\n",
    "    return out_file, result.messages\n",
    "\n",
    "# --- Helper: python-docx fallback for corrupt docx that crashes mammoth ----------\n",
    "try:\n",
    "    import docx as _pdocx  # python-docx\n",
    "except Exception:\n",
    "    _pdocx = None\n",
    "\n",
    "def _convert_via_python_docx(docx_path: Path, out_file: Path, stem: str):\n",
    "    \"\"\"Fallback converter for docx files mammoth crashed on (e.g. the _accept0 bug).\\n   \\n    Extracts paragraph text + structure via python-docx. Differs from mammoth path:\\n      - Recognizes Word Heading 1/2/3/4 + Title styles as real markdown headings.\\n      - Detects code/CLI paragraphs (shell prompts, Python comments, etc.) and fences\\n        them in ``` blocks so leading \"# comment\" lines do NOT become faux H1 markdown\\n        headings (a bug that previously produced files with H1:6 / H1:15 / H1:279).\\n      - Runs collapse_extra_h1() as a safety net so the file ends up with exactly\\n        one H1, matching the contract of the primary convert_one() path.\\n    \"\"\"\n",
    "    if _pdocx is None:\n",
    "        raise RuntimeError('python-docx not installed; cannot fallback')\n",
    "    d = _pdocx.Document(str(docx_path))\n",
    "    lines = []\n",
    "    h1_seen = False\n",
    "    in_code = False  # whether we are currently inside a fenced code block\n",
    "    # Heuristics for detecting a paragraph that is really a code/CLI line, not prose.\n",
    "    # \"Looks like code\" -> preserve verbatim inside a fenced block so neither the\n",
    "    # leading '# ' (Python/shell comment) nor leading shell symbols become faux\n",
    "    # markdown headings.\n",
    "    import re as _re\n",
    "    def _looks_like_code(s: str) -> bool:\n",
    "        if not s:\n",
    "            return False\n",
    "        # Common shell prompt prefixes\n",
    "        if _re.match(r'^(\\$|>#|>|\\$\\s|sudo |apt |pip |git |cd |npm |python|wget |curl |chmod |mkdir |cp |mv |rm |echo |export |systemctl |dpkg |sed |awk |grep |find |tar |unzip |./|npm |npx|docker |kubectl |helm |kubectl )', s):\n",
    "            return True\n",
    "        # Python-style comment (# ...) or shell comment. Excludes legitimate prose\n",
    "        # like '# Topic Idea:' only if it ALSO has code-y characters. To be safe,\n",
    "        # treat a line starting with '# ' followed by uppercase letters OR containing\n",
    "        # common code tokens as a code comment. We DON'T classify every '#'-leading\n",
    "        # line as code (would defeat real H1 detection); we only fence when it is\n",
    "        # clearly not a title. Real titles are short (<80 chars), Capitalized prose.\n",
    "        m = _re.match(r'^# (.{1,160})$', s)\n",
    "        if m and _re.search(r'[;|=&><{}\\\\/]|from \\w|import \\w|def \\w|class \\w|^if |^else|^for |^while |^return |=>|->|name ==', s):\n",
    "            return True\n",
    "        return False\n",
    "    for p in d.paragraphs:\n",
    "        txt = p.text.rstrip()\n",
    "        if not txt.strip():\n",
    "            if in_code:\n",
    "                # blank line inside a code block: keep it (preserves formatting)\n",
    "                lines.append('')\n",
    "            else:\n",
    "                lines.append('')\n",
    "            continue\n",
    "        style = (p.style.name or '').lower() if p.style else ''\n",
    "        # 1. Word heading styles -> real markdown headings\n",
    "        if 'heading 1' in style or style == 'title':\n",
    "            if in_code: lines.append('```'); in_code = False\n",
    "            if not h1_seen: lines.append(f'# {txt.strip()}'); h1_seen = True\n",
    "            else: lines.append(f'## {txt.strip()}')\n",
    "        elif 'heading 2' in style:\n",
    "            if in_code: lines.append('```'); in_code = False\n",
    "            lines.append(f'## {txt.strip()}')\n",
    "        elif 'heading 3' in style:\n",
    "            if in_code: lines.append('```'); in_code = False\n",
    "            lines.append(f'### {txt.strip()}')\n",
    "        elif 'heading 4' in style:\n",
    "            if in_code: lines.append('```'); in_code = False\n",
    "            lines.append(f'#### {txt.strip()}')\n",
    "        else:\n",
    "            # NOT a styled heading. Two sub-cases:\n",
    "            # (a) Bold-only short paragraph in a Normal style -> candidate H3 section header\n",
    "            is_bold = bool(p.runs) and all(r.bold for r in p.runs if r.text.strip())\n",
    "            if is_bold and len(txt.strip()) <= 160 and not txt.lstrip().startswith('#'):\n",
    "                if in_code: lines.append('```'); in_code = False\n",
    "                lines.append(f'### {txt.strip()}')\n",
    "            elif _looks_like_code(txt.strip()):\n",
    "                # (b) code/comment line -> accumulate inside a fenced block.\n",
    "                # Crucial: a '# foo' line inside ``` ``` is NOT a markdown H1.\n",
    "                if not in_code:\n",
    "                    lines.append('```')\n",
    "                    in_code = True\n",
    "                lines.append(txt)\n",
    "            else:\n",
    "                # plain prose paragraph -> close any open code block first\n",
    "                if in_code: lines.append('```'); in_code = False\n",
    "                lines.append(txt.strip())\n",
    "    if in_code: lines.append('```')  # close trailing code block\n",
    "    body = '\\n'.join(lines).strip()\n",
    "    if not h1_seen:\n",
    "        body = f'# {stem}\\n\\n' + body\n",
    "    # Safety net: ensure exactly one H1 (e.g. if doc has multiple 'Title' paragraphs,\n",
    "    # or a '# comment' paragraph slipped past the heuristics above).\n",
    "    body, _ = collapse_extra_h1(body)\n",
    "    out_file.write_text(body + '\\n', encoding='utf-8')\n",
    "    return out_file, []\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "def clean_md_input(path: Path, out_file: Path, stem: str):\n",
    "    \"\"\"Cleanup pipeline for raw uploaded .md files.\n",
    "\n",
    "    Same post-processing as docx, minus mammoth (the file is already markdown):\n",
    "      clean_markdown -> strip_masthead -> strip_cite_markers\n",
    "      -> collapse_hr -> promote_structure -> collapse_extra_h1\n",
    "      -> conditional synthetic '# {stem}' H1 prepend.\n",
    "    \"\"\"\n",
    "    text = path.read_text(encoding='utf-8')\n",
    "    text = clean_markdown(text)\n",
    "    text = strip_masthead(text)\n",
    "    text = strip_cite_markers(text)\n",
    "    text = collapse_hr(text)\n",
    "    text = promote_structure(text)\n",
    "    text, has_h1 = collapse_extra_h1(text)\n",
    "    if not has_h1:\n",
    "        text = f\"# {stem}\\n\\n\" + text.strip() + \"\\n\"\n",
    "    else:\n",
    "        text = text.strip() + \"\\n\"\n",
    "    out_file.write_text(text, encoding='utf-8')\n",
    "    return out_file\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea876def",
   "metadata": {},
   "source": [
    "## 5b. Pre-flight: drop near-duplicates by content similarity\n",
    "\n",
    "Before conversion, look at the inputs in THIS upload batch and drop any file\n",
    "whose normalized text overlaps an already-kept file by more than\n",
    "`DEDUP_SIMILARITY_THRESHOLD` (default 0.97 — catches `(1)`/`(2)`/`Copy of`\n",
    "variants where the bytes differ by a few characters but the document is\n",
    "really the same).\n",
    "\n",
    "**How it works:** extracts raw text (`python-docx` for `.docx`, plain read\n",
    "for `.md`), strips URLs + masthead + whitespace + punctuation, slices into\n",
    "non-overlapping 500-char windows, hashes each window, and computes the Jaccard\n",
    "overlap between every pair. Matches above threshold → drop the second one and\n",
    "log it to `dedup_drops.csv`.\n",
    "\n",
    "The original upload list is snapshotted in cell 2 as `uploaded_files_master`,\n",
    "so this cell is **idempotent**: rerun it as many times as you want, it always\n",
    "rebuilds `input_files` from the snapshot. Threshold tunable in the Config cell."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eefa73ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Start from the snapshot so reruns are idempotent.\n",
    "input_files = list(uploaded_files_master) if 'uploaded_files_master' in globals() \\\n",
    "              else list(input_files)\n",
    "\n",
    "if not DEDUP_SIMILARITY_THRESHOLD or DEDUP_SIMILARITY_THRESHOLD <= 0:\n",
    "    print(f\"Near-duplicate scan: DISABLED (DEDUP_SIMILARITY_THRESHOLD={DEDUP_SIMILARITY_THRESHOLD})\")\n",
    "    print(f\"Proceeding with {len(input_files)} input file(s).\")\n",
    "else:\n",
    "    print(f\"Near-duplicate scan (threshold: {DEDUP_SIMILARITY_THRESHOLD:.2f} chunk overlap)...\")\n",
    "    print(f\"Hashing {len(input_files)} input(s) into normalized text signatures...\")\n",
    "\n",
    "    # Build a normalized signature for each input: raw byte-hash AND a normalized\n",
    "    # chunk set (used for the fuzzy comparison).\n",
    "    signatures = []\n",
    "    for path in input_files:\n",
    "        sha = sha256_of_file(path)\n",
    "        raw = _raw_text_for_dedup(path)\n",
    "        norm = _normalize_for_dedup(raw)\n",
    "        chunks = _chunk_set(norm, DEDUP_CHUNK_SIZE)\n",
    "        signatures.append({'path': path, 'sha': sha, 'chunks': chunks,\n",
    "                           'n_chars': len(norm), 'n_chunks': len(chunks)})\n",
    "\n",
    "    # First pass: group by exact byte hash (instant, free, catches identical uploads).\n",
    "    by_sha = {}\n",
    "    for s in signatures:\n",
    "        by_sha.setdefault(s['sha'], []).append(s)\n",
    "\n",
    "    kept = []            # list of signature dicts we'll keep\n",
    "    dropped_exact = []   # (kept_sig, dup_sig)\n",
    "    for sha, group in by_sha.items():\n",
    "        if len(group) == 1:\n",
    "            kept.append(group[0])\n",
    "        else:\n",
    "            # Keep the first, drop the rest with similarity 1.00 (exact).\n",
    "            keep = group[0]\n",
    "            kept.append(keep)\n",
    "            for dup in group[1:]:\n",
    "                dropped_exact.append((keep, dup))\n",
    "\n",
    "    # Second pass: fuzzy compare across all 'kept' so far. For each candidate,\n",
    "    # compare its chunk set against every already-accepted doc whose chunk count\n",
    "    # is within a sane ratio (10x either way; wildly different doc sizes can't be\n",
    "    # 97% overlapping unless one is mostly empty, which we skip).\n",
    "    final_kept = []\n",
    "    dropped_fuzzy = []   # (kept_sig, dup_sig, similarity)\n",
    "    for cand in kept:\n",
    "        if not cand['chunks']:\n",
    "            # Couldn't extract text (failed docx parse, empty file). Keep it\n",
    "            # unconditionally; byte-hash dedup already handled exact dupes.\n",
    "            final_kept.append(cand)\n",
    "            continue\n",
    "        match = None\n",
    "        for keep in final_kept:\n",
    "            if not keep['chunks']:\n",
    "                continue\n",
    "            nc, nk = cand['n_chunks'], keep['n_chunks']\n",
    "            if nc == 0 or nk == 0:\n",
    "                continue\n",
    "            # Cheap pre-filter: can't be 97% similar if chunk counts are >10x apart.\n",
    "            if nc > 10 * nk or nk > 10 * nc:\n",
    "                continue\n",
    "            sim = _jaccard(cand['chunks'], keep['chunks'])\n",
    "            if sim >= DEDUP_SIMILARITY_THRESHOLD:\n",
    "                match = (keep, sim)\n",
    "                break\n",
    "        if match:\n",
    "            dropped_fuzzy.append((match[0], cand, match[1]))\n",
    "        else:\n",
    "            final_kept.append(cand)\n",
    "\n",
    "    # Build the new input_files list, preserving the original iteration order.\n",
    "    kept_paths = {s['path'] for s in final_kept}\n",
    "    input_files = [p for p in input_files if p in kept_paths]\n",
    "\n",
    "    # Report\n",
    "    print()\n",
    "    print(f\"Exact-hash duplicates dropped: {len(dropped_exact)}\")\n",
    "    for keep, dup in dropped_exact:\n",
    "        print(f\"  Keep   '{keep['path'].name}'\")\n",
    "        print(f\"    ⤷ dup '{dup['path'].name}'  — identical bytes — dropped\")\n",
    "        append_dedup_drop_row({\n",
    "            'kept_filename': keep['path'].name, 'kept_sha256': keep['sha'],\n",
    "            'dropped_filename': dup['path'].name, 'dropped_sha256': dup['sha'],\n",
    "            'similarity': '1.000', 'drop_reason': 'exact_byte_match',\n",
    "            'dropped_at': _now_iso()})\n",
    "\n",
    "    print()\n",
    "    print(f\"Near-duplicate (fuzzy) drops:  {len(dropped_fuzzy)}\")\n",
    "    for keep, dup, sim in dropped_fuzzy:\n",
    "        print(f\"  Keep   '{keep['path'].name}'  ({keep['n_chunks']} chunks)\")\n",
    "        print(f\"    ⤷ dup '{dup['path'].name}'  — {sim*100:.1f}% overlap — dropped\")\n",
    "        append_dedup_drop_row({\n",
    "            'kept_filename': keep['path'].name, 'kept_sha256': keep['sha'],\n",
    "            'dropped_filename': dup['path'].name, 'dropped_sha256': dup['sha'],\n",
    "            'similarity': f\"{sim:.3f}\", 'drop_reason': 'fuzzy_overlap',\n",
    "            'dropped_at': _now_iso()})\n",
    "\n",
    "    print()\n",
    "    print(f\"Deduped: {len(uploaded_files_master)} → {len(input_files)} inputs \"\n",
    "          f\"({len(uploaded_files_master) - len(input_files)} duplicates dropped).\")\n",
    "    print(f\"Pre-flight complete. Cell 6 will now convert {len(input_files)} file(s).\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b60771c",
   "metadata": {},
   "source": [
    "## 6. Convert all inputs (with manifest dedup)\n",
    "\n",
    "For each input file:\n",
    "1. Compute SHA-256 of the **original input bytes** (before any conversion).\n",
    "2. Check the manifest. If the hash is already there and `SKIP_ALREADY_SEEN` is True → print `SKIP` and move on. If you renamed a file, the hash still matches → no duplicate convert. If the bytes *actually* differ → different hash → converts as a new doc.\n",
    "3. Dispatch by extension: `.docx` → `convert_one()` with python-docx fallback; `.md` → `clean_md_input()`.\n",
    "4. Copy the original bytes to `archive/` (so future Colab sessions can still match the hash even after `/content` is wiped).\n",
    "5. Append a row to `manifest.csv` with the status: `converted` / `crashed_fallback_ok` / `crashed_skipped`.\n",
    "\n",
    "The `converted_this_run` list is what the verify and URL-extraction cells iterate over — they only look at files WE converted in this run, not the entire `markdown_output/` folder (so previously-converted files don't drown out the per-run summary)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5095483b",
   "metadata": {},
   "outputs": [],
   "source": [
    "manifest = load_manifest()\n",
    "manifest_seen = {r['sha256'] for r in manifest}\n",
    "converted_this_run = []     # list of (out_path, sha256, stem)\n",
    "skipped_count = 0\n",
    "crashed_count = 0\n",
    "\n",
    "for path in input_files:\n",
    "    sha = sha256_of_file(path)\n",
    "    ext = path.suffix.lower().lstrip('.')\n",
    "    stem = path.stem\n",
    "\n",
    "    # Dedup check against the persistent Drive manifest. Guard against the rare\n",
    "    # race where sha is in manifest_seen (the set) but is_seen() returns None\n",
    "    # (e.g. another process appended to manifest.csv after we cached the set).\n",
    "    if SKIP_ALREADY_SEEN and sha in manifest_seen:\n",
    "        seen_row = is_seen(sha, manifest) or {}\n",
    "        prev_name = seen_row.get('first_seen_filename', '(unknown)')\n",
    "        prev_date = seen_row.get('first_seen_at', '(unknown)')\n",
    "        print(f\"SKIP  {path.name}  (already converted; first seen as \"\n",
    "              f\"'{prev_name}' on {prev_date})\")\n",
    "        skipped_count += 1\n",
    "        continue\n",
    "\n",
    "    # Archive original input bytes (durable across Colab wipes)\n",
    "    if ARCHIVE_INPUTS:\n",
    "        archive_path = ARCHIVE_DIR / f\"{sha}.{ext}\"\n",
    "        if not archive_path.exists():\n",
    "            shutil.copy2(path, archive_path)\n",
    "\n",
    "    out_file = MD_OUTPUT_DIR / f\"{stem}.md\"\n",
    "    status = 'converted'\n",
    "    try:\n",
    "        if ext == 'docx':\n",
    "            try:\n",
    "                out_file, warnings = convert_one(path, MD_OUTPUT_DIR,\n",
    "                                                 CUSTOM_STYLE_MAP, EXTRACT_IMAGES)\n",
    "            except Exception:\n",
    "                out_file, warnings = _convert_via_python_docx(path, out_file, stem)\n",
    "                status = 'crashed_fallback_ok'\n",
    "        elif ext == 'md':\n",
    "            out_file = clean_md_input(path, out_file, stem)\n",
    "            status = 'converted'\n",
    "        else:\n",
    "            print(f\"SKIP {path.name} (unsupported extension .{ext})\")\n",
    "            continue\n",
    "\n",
    "        size = out_file.stat().st_size\n",
    "        rel_out = str(out_file.relative_to(DRIVE_ROOT))\n",
    "        row = {\n",
    "            'sha256': sha,\n",
    "            'first_seen_filename': path.name,\n",
    "            'first_seen_at': _now_iso(),\n",
    "            'last_processed_at': _now_iso(),\n",
    "            'input_ext': ext,\n",
    "            'output_path': rel_out,\n",
    "            'output_bytes': size,\n",
    "            'status': status,\n",
    "        }\n",
    "        append_manifest_row(row)\n",
    "        manifest_seen.add(sha)\n",
    "        converted_this_run.append((out_file, sha, stem))\n",
    "        print(f\"✓  {path.name}  →  {out_file.name}  ({size:,} bytes) [{status}]\")\n",
    "    except Exception as e:\n",
    "        crashed_count += 1\n",
    "        row = {\n",
    "            'sha256': sha,\n",
    "            'first_seen_filename': path.name,\n",
    "            'first_seen_at': _now_iso(),\n",
    "            'last_processed_at': _now_iso(),\n",
    "            'input_ext': ext,\n",
    "            'output_path': '',\n",
    "            'output_bytes': 0,\n",
    "            'status': 'crashed_skipped',\n",
    "        }\n",
    "        append_manifest_row(row)\n",
    "        print(f\"✗  {path.name} FAILED ({type(e).__name__}: {e})\")\n",
    "\n",
    "print(f\"\\nDone. {len(converted_this_run)} converted, \"\n",
    "      f\"{skipped_count} skipped (already in manifest), \"\n",
    "      f\"{crashed_count} crashed.\")\n",
    "print(f\"Manifest now has {len(load_manifest())} total rows.\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ada1c03",
   "metadata": {},
   "source": [
    "## 7. Extract source URLs from converted files\n",
    "\n",
    "For every file converted THIS run, pull every full `http(s)://` URL out of the markdown and append a row to `sources.csv`. Each row records the source doc’s SHA + stem alongside the URL and a lightweight type guess (`pdf` / `html`). This is the list the optional downloader cell consumes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d9beedd",
   "metadata": {},
   "outputs": [],
   "source": [
    "total_urls = 0\n",
    "docs_with_urls = 0\n",
    "for out_path, sha, stem in converted_this_run:\n",
    "    text = out_path.read_text(encoding='utf-8')\n",
    "    urls = extract_urls(text)\n",
    "    if urls:\n",
    "        append_source_rows(sha, stem, urls)\n",
    "        total_urls += len(urls)\n",
    "        docs_with_urls += 1\n",
    "        types = [classify_url(u) for u in urls]\n",
    "        n_pdf   = types.count('pdf')\n",
    "        n_html  = types.count('html')\n",
    "        n_other = types.count('other')\n",
    "        print(f\"  {stem[:70]}: {len(urls)} URLs ({n_pdf} pdf, {n_html} html, {n_other} other)\")\n",
    "    else:\n",
    "        print(f\"  {stem[:70]}: 0 URLs\")\n",
    "\n",
    "print(f\"\\nTotal: {total_urls} URLs across {docs_with_urls} docs \"\n",
    "      f\"({len(converted_this_run) - docs_with_urls} had no URLs).\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6973783c",
   "metadata": {},
   "source": [
    "## 8. Verify heading structure (important for Open WebUI chunking)\n",
    "\n",
    "Open WebUI's **Markdown Header Splitter** chunks on `#`/`##`/`###`. Run this cell to confirm each converted file has **exactly one H1** plus a healthy H2/H3 hierarchy. The audit only runs over files WE converted this run (so reruns aren't drowned by previously-converted files in `markdown_output/`).\n",
    "\n",
    "Flags:\n",
    "- `⚠ multiple H1s!` — would fragment retrieval; collapse_extra_h1 should prevent this.\n",
    "- `⚠ nearly empty` — output is suspiciously small; conversion may have failed silently.\n",
    "- `⚠ NO sub-headings!` — the document would collapse into a single giant chunk."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2c790d68",
   "metadata": {},
   "outputs": [],
   "source": [
    "from collections import Counter\n",
    "import re\n",
    "\n",
    "print(\"Heading structure per converted file (this run only):\\n\")\n",
    "for out_path, sha, stem in converted_this_run:\n",
    "    counts = Counter()\n",
    "    text = out_path.read_text(encoding='utf-8')\n",
    "    for ln in text.splitlines():\n",
    "        m = re.match(r'^(#{1,6})\\s', ln)\n",
    "        if m:\n",
    "            counts[len(m.group(1))] += 1\n",
    "    summary = \", \".join(f\"H{lvl}:{counts[lvl]}\" for lvl in sorted(counts))\n",
    "    flag_no   = \"  ⚠ NO sub-headings!\" if counts.get(2, 0) + counts.get(3, 0) == 0 else \"\"\n",
    "    flag_size = \"  ⚠ nearly empty\" if len(text) < 500 else \"\"\n",
    "    flag_h1   = \"  ⚠ multiple H1s!\" if counts.get(1, 0) > 1 else \"\"\n",
    "    print(f\"  {stem[:70]}\")\n",
    "    print(f\"      {summary}  ({len(text):,} chars){flag_no}{flag_size}{flag_h1}\")\n",
    "\n",
    "print(f\"\\nManifest total: {len(load_manifest())} files tracked since first run.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7d4750ec",
   "metadata": {},
   "source": [
    "## 9. Preview a converted file\n",
    "\n",
    "Quick sanity check. Change the index to preview a different file. Glob is filtered by `is_file()` so we don't trip on `media/` subdirectories."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9647fd57",
   "metadata": {},
   "outputs": [],
   "source": [
    "preview_idx = 0  # change to view another file\n",
    "md_files = sorted(p for p in MD_OUTPUT_DIR.glob('*.md') if p.is_file())\n",
    "assert md_files, \"No .md files produced.\"\n",
    "target = md_files[preview_idx]\n",
    "print(f\"# Preview of: {target.name}\\n\" + \"=\" * 60)\n",
    "content = target.read_text(encoding='utf-8')\n",
    "print(content[:3000])\n",
    "if len(content) > 3000:\n",
    "    print(f\"\\n… ({len(content)-3000:,} more chars)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "515a7730",
   "metadata": {},
   "source": [
    "## 10. Bundle as `.zip` and download\n",
    "\n",
    "Zips the converted `.md` files (and their `media/` folders) into a local archive and triggers a browser download. Useful on free tier where the VM disappears after the session — but since everything is also saved to Drive (`markdown_output/`), you don't strictly need this."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6422bd2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import shutil\n",
    "from google.colab import files as colab_files\n",
    "\n",
    "ZIP_PATH = Path('/content/markdown_for_openwebui.zip')\n",
    "if ZIP_PATH.exists():\n",
    "    ZIP_PATH.unlink()\n",
    "\n",
    "shutil.make_archive('/content/markdown_for_openwebui', 'zip',\n",
    "                    root_dir=str(MD_OUTPUT_DIR))\n",
    "print(f\"Created {ZIP_PATH} ({ZIP_PATH.stat().st_size:,} bytes)\")\n",
    "\n",
    "colab_files.download(str(ZIP_PATH))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27801831",
   "metadata": {},
   "source": [
    "## 11. (Optional) Bulk URL downloader — OFF by default\n",
    "\n",
    "Reads `sources.csv`, skips any URL already in `downloads.csv`, makes a HEAD request to determine the real Content-Type, then saves the response bytes to:\n",
    "\n",
    "- `downloads/pdf/<sha256>.pdf`  if Content-Type is PDF or URL ends in `.pdf`\n",
    "- `downloads/html/<sha256>.html`  if Content-Type is HTML\n",
    "- `downloads/other/<sha256>.bin`  otherwise\n",
    "\n",
    "**Paywalled / auth-locked URLs (HTTP 401/403/etc.) are recorded with an empty `saved_path`** so you can see at a glance what's unreachable. The downloader will not try to bypass paywalls.\n",
    "\n",
    "To actually run the downloader, **flip `RUN_DOWNLOADER = True`** in the cell below. It defaults to `False` so a \"Run all\" doesn't kick off hundreds of HTTP requests."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5edc7ac0",
   "metadata": {},
   "outputs": [],
   "source": [
    "RUN_DOWNLOADER = False   # <-- flip to True to actually run the downloader\n",
    "\n",
    "if RUN_DOWNLOADER:\n",
    "    import requests, csv, hashlib, time\n",
    "    from pathlib import Path\n",
    "\n",
    "    # Make sure downloads.csv has a header if it's new.\n",
    "    if not DOWNLOADS_CSV.exists():\n",
    "        with open(DOWNLOADS_CSV, 'w', newline='') as f:\n",
    "            csv.DictWriter(f, fieldnames=DOWNLOADS_COLS).writeheader()\n",
    "\n",
    "    # Load already-downloaded URLs\n",
    "    already = set()\n",
    "    with open(DOWNLOADS_CSV, 'r', newline='') as f:\n",
    "        for r in csv.DictReader(f):\n",
    "            already.add(r['url'])\n",
    "\n",
    "    if not SOURCES_PATH.exists():\n",
    "        print(\"No sources.csv found. Run the URL extraction cell first.\")\n",
    "    else:\n",
    "        with open(SOURCES_PATH, 'r', newline='') as f:\n",
    "            source_rows = list(csv.DictReader(f))\n",
    "        unique_urls = sorted({r['url'] for r in source_rows})\n",
    "        todo = [u for u in unique_urls if u not in already]\n",
    "        print(f\"{len(unique_urls)} unique URLs in sources.csv; \"\n",
    "              f\"{len(already)} already downloaded; \"\n",
    "              f\"will fetch {len(todo)}.\")\n",
    "\n",
    "        fetched = 0\n",
    "        failed = 0\n",
    "        for url in unique_urls:\n",
    "            if url in already:\n",
    "                continue\n",
    "            try:\n",
    "                # HEAD first to get content-type and final URL.\n",
    "                h = requests.head(url, allow_redirects=True, timeout=15,\n",
    "                                  headers={'User-Agent': 'Mozilla/5.0'})\n",
    "                ctype = h.headers.get('Content-Type', '').lower()\n",
    "                if 'pdf' in ctype or url.lower().endswith('.pdf'):\n",
    "                    kind, ext = 'pdf', 'pdf'\n",
    "                elif 'html' in ctype:\n",
    "                    kind, ext = 'html', 'html'\n",
    "                else:\n",
    "                    kind, ext = 'other', 'bin'\n",
    "\n",
    "                if not (200 <= h.status_code < 300):\n",
    "                    with open(DOWNLOADS_CSV, 'a', newline='') as f:\n",
    "                        csv.DictWriter(f, fieldnames=DOWNLOADS_COLS).writerow({\n",
    "                            'url': url, 'sha256_of_bytes': '',\n",
    "                            'saved_path': '', 'http_status': h.status_code,\n",
    "                            'content_type': ctype, 'downloaded_at': _now_iso()})\n",
    "                    print(f\"✗  {url}  →  HTTP {h.status_code} (skipped)\")\n",
    "                    failed += 1\n",
    "                    time.sleep(RATE_LIMIT_SEC)\n",
    "                    continue\n",
    "\n",
    "                # Fetch the actual bytes.\n",
    "                r = requests.get(url, allow_redirects=True, timeout=30,\n",
    "                                 headers={'User-Agent': 'Mozilla/5.0'})\n",
    "                if not (200 <= r.status_code < 300):\n",
    "                    with open(DOWNLOADS_CSV, 'a', newline='') as f:\n",
    "                        csv.DictWriter(f, fieldnames=DOWNLOADS_COLS).writerow({\n",
    "                            'url': url, 'sha256_of_bytes': '',\n",
    "                            'saved_path': '', 'http_status': r.status_code,\n",
    "                            'content_type': r.headers.get('Content-Type', ''),\n",
    "                            'downloaded_at': _now_iso()})\n",
    "                    print(f\"✗  {url}  →  HTTP {r.status_code}\")\n",
    "                    failed += 1\n",
    "                    time.sleep(RATE_LIMIT_SEC)\n",
    "                    continue\n",
    "\n",
    "                body_sha = hashlib.sha256(r.content).hexdigest()\n",
    "                saved = DOWNLOADS_DIR / kind / f\"{body_sha}.{ext}\"\n",
    "                saved.write_bytes(r.content)\n",
    "                rel = str(saved.relative_to(DRIVE_ROOT))\n",
    "                with open(DOWNLOADS_CSV, 'a', newline='') as f:\n",
    "                    csv.DictWriter(f, fieldnames=DOWNLOADS_COLS).writerow({\n",
    "                        'url': url, 'sha256_of_bytes': body_sha,\n",
    "                        'saved_path': rel, 'http_status': r.status_code,\n",
    "                        'content_type': r.headers.get('Content-Type', ''),\n",
    "                        'downloaded_at': _now_iso()})\n",
    "                fetched += 1\n",
    "                print(f\"✓  {url}  →  {rel}  ({len(r.content):,} bytes)\")\n",
    "                time.sleep(RATE_LIMIT_SEC)\n",
    "            except Exception as e:\n",
    "                print(f\"✗  {url}  →  {type(e).__name__}: {e}\")\n",
    "                failed += 1\n",
    "                time.sleep(RATE_LIMIT_SEC)\n",
    "\n",
    "        print(f\"\\nDone. Fetched {fetched} new, failed/skipped {failed}.\")\n",
    "else:\n",
    "    print(\"Downloader is OFF. Set RUN_DOWNLOADER = True above to run.\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e454beab",
   "metadata": {},
   "source": [
    "## 12. Import into Open WebUI (manual one-time step)\n",
    "\n",
    "After unzipping locally:\n",
    "\n",
    "1. Open your Open WebUI instance → **Workspace → Knowledge**.\n",
    "2. Create or open a knowledge base.\n",
    "3. Upload the `.md` files (image folders are not currently indexed by Open WebUI's text RAG — they're safe to omit unless you want them stored alongside).\n",
    "4. In **Admin Settings → Tools → Documents**, make sure **Markdown Header Splitting** is **enabled**.\n",
    "5. Set sensible chunking values, per the Open WebUI docs:\n",
    "   - **Chunk Size**: ~1000–2000 characters.\n",
    "   - **Chunk Overlap**: ~10–15% of chunk size.\n",
    "   - **Chunk Min Size Target**: ~50% of chunk size (merges tiny header fragments).\n",
    "6. If you change embedding models later, hit **Reindex** so all chunks are re-embedded with the new model. Old embeddings live in a different vector space and won't retrieve well until re-indexed.\n",
    "\n",
    "> These config recommendations come from the Open WebUI RAG documentation [2]; the notebook itself only handles the DOCX→Markdown conversion."
   ]
  }
 ],
 "metadata": {
  "accelerator": "None",
  "colab": {
   "provenance": []
  },
  "kernelspec": {
   "display_name": "Python 3",
   "name": "python3"
  },
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
