#!/usr/bin/env python
"""
Extract content from an old WordPress SQL dump (wpht_ prefix) and stage
it under site/src/data/legacy-content.json + site/public/legacy/images/.

Usage:
    python scripts/extract-legacy-content.py [path/to/dump.sql]

This script:
1. Streams the SQL file line-by-line and captures INSERT statements for
   wpht_posts, wpht_postmeta, wpht_options.
2. Parses those INSERTs using a SQL-literal tokenizer that understands
   MySQL backslash escaping, quoted strings, NULLs, and numbers.
3. Filters to published posts/pages with >=400 chars of textual content.
4. Downloads every referenced image to public/legacy/images/ (deduped)
   and rewrites the post HTML to reference /legacy/images/<filename>.
5. Writes the top 40 posts (ranked by text length) to legacy-content.json.

It is intentionally conservative: unknown tables are skipped, binary blobs
are ignored, and any download error is logged and continued past.
"""

from __future__ import annotations

import html as html_lib
import json
import logging
import os
import re
import sys
import time
from dataclasses import dataclass, field
from pathlib import Path
from typing import Iterable, Iterator
from urllib.parse import unquote, urlparse

try:
    import requests
except ImportError:
    print("requests is required: pip install requests", file=sys.stderr)
    raise

# --- paths ---------------------------------------------------------------

SCRIPT_DIR = Path(__file__).resolve().parent
SITE_DIR = SCRIPT_DIR.parent
REPO_DIR = SITE_DIR.parent
DEFAULT_SQL = Path(r"C:\Users\atefa\Downloads\meddyhez_wp657.sql")

DATA_DIR = SITE_DIR / "src" / "data"
IMAGES_DIR = SITE_DIR / "public" / "legacy" / "images"
JSON_OUT = DATA_DIR / "legacy-content.json"
LOG_FILE = SCRIPT_DIR / "extract-legacy-content.log"

DATA_DIR.mkdir(parents=True, exist_ok=True)
IMAGES_DIR.mkdir(parents=True, exist_ok=True)

# --- logging -------------------------------------------------------------

logging.basicConfig(
    filename=str(LOG_FILE),
    filemode="w",
    level=logging.INFO,
    format="%(asctime)s %(levelname)s %(message)s",
)
log = logging.getLogger("extract")

# Also print INFO to stdout so humans watching can see progress.
console = logging.StreamHandler(sys.stdout)
console.setLevel(logging.INFO)
console.setFormatter(logging.Formatter("%(levelname)s %(message)s"))
log.addHandler(console)

# --- SQL parsing ---------------------------------------------------------

INSERT_RE = re.compile(
    r"^INSERT INTO `(?P<table>[^`]+)` \((?P<cols>[^)]*)\) VALUES\s*(?P<rest>.*)$",
    re.IGNORECASE,
)

TARGET_TABLES = {"wpht_posts", "wpht_postmeta", "wpht_options"}


def parse_sql_values(values_blob: str) -> Iterator[list]:
    """Yield each row tuple from a VALUES(...)[,(...)] blob.

    Supports MySQL style backslash escaping inside single-quoted strings.
    Handles NULL, numbers, and strings. Stops at the trailing semicolon.
    """
    i = 0
    n = len(values_blob)
    while i < n:
        # Skip whitespace and commas between rows.
        while i < n and values_blob[i] in " \t\r\n,":
            i += 1
        if i >= n or values_blob[i] == ";":
            return
        if values_blob[i] != "(":
            # Malformed, bail on this row
            return
        i += 1  # consume (
        row: list = []
        while True:
            # Skip whitespace
            while i < n and values_blob[i] in " \t\r\n":
                i += 1
            if i >= n:
                return
            ch = values_blob[i]
            if ch == ")":
                i += 1
                break
            if ch == ",":
                i += 1
                continue
            if ch == "'":
                # Quoted string with backslash escapes.
                i += 1
                out = []
                while i < n:
                    c = values_blob[i]
                    if c == "\\":
                        if i + 1 < n:
                            nxt = values_blob[i + 1]
                            mapping = {
                                "n": "\n",
                                "r": "\r",
                                "t": "\t",
                                "0": "\0",
                                "'": "'",
                                '"': '"',
                                "\\": "\\",
                                "Z": "\x1a",
                            }
                            out.append(mapping.get(nxt, nxt))
                            i += 2
                        else:
                            i += 1
                    elif c == "'":
                        # Handle '' as an escaped quote (MySQL sometimes uses this)
                        if i + 1 < n and values_blob[i + 1] == "'":
                            out.append("'")
                            i += 2
                        else:
                            i += 1
                            break
                    else:
                        out.append(c)
                        i += 1
                row.append("".join(out))
            else:
                # NULL / number / identifier until , or )
                j = i
                while j < n and values_blob[j] not in ",)":
                    j += 1
                tok = values_blob[i:j].strip()
                if tok.upper() == "NULL":
                    row.append(None)
                else:
                    try:
                        if "." in tok:
                            row.append(float(tok))
                        else:
                            row.append(int(tok))
                    except ValueError:
                        row.append(tok)
                i = j
        yield row


def stream_inserts(sql_path: Path) -> Iterator[tuple[str, list[str], list]]:
    """Yield (table, columns, row) for INSERTs into target tables.

    WordPress mysqldumps sometimes put a whole multi-row INSERT on one line;
    this can be >50 MB. We accumulate until we find the terminating `;` at
    end of line.
    """
    with sql_path.open("r", encoding="utf-8", errors="replace") as fh:
        buf = ""
        in_insert = False
        current_table = ""
        current_cols: list[str] = []
        for raw in fh:
            line = raw.rstrip("\n")
            if not in_insert:
                m = INSERT_RE.match(line)
                if not m:
                    continue
                table = m.group("table")
                if table not in TARGET_TABLES:
                    continue
                cols = [c.strip("` ") for c in m.group("cols").split(",")]
                rest = m.group("rest")
                current_table = table
                current_cols = cols
                buf = rest
                in_insert = True
            else:
                buf += "\n" + line
            if in_insert and buf.rstrip().endswith(";"):
                blob = buf.rstrip()[:-1]
                for row in parse_sql_values(blob):
                    yield current_table, current_cols, row
                in_insert = False
                buf = ""


# --- extraction ----------------------------------------------------------


@dataclass
class Post:
    id: int
    slug: str
    title: str
    type: str
    date: str
    excerpt: str
    html: str
    guid: str = ""
    thumb_id: int | None = None
    featured_image: str = ""
    images: list[str] = field(default_factory=list)
    text_length: int = 0


IMG_SRC_RE = re.compile(r'src\s*=\s*["\']([^"\']+)["\']', re.IGNORECASE)
IMG_SRCSET_RE = re.compile(r'srcset\s*=\s*["\']([^"\']+)["\']', re.IGNORECASE)
TAG_RE = re.compile(r"<[^>]+>")


def text_length(html: str) -> int:
    return len(TAG_RE.sub(" ", html or "").strip())


def extract_image_urls(html: str) -> list[str]:
    urls: list[str] = []
    for m in IMG_SRC_RE.finditer(html or ""):
        urls.append(m.group(1).strip())
    for m in IMG_SRCSET_RE.finditer(html or ""):
        # srcset entries: "url size, url size"
        for part in m.group(1).split(","):
            u = part.strip().split(" ", 1)[0]
            if u:
                urls.append(u)
    # de-dupe while preserving order
    seen = set()
    out = []
    for u in urls:
        if u not in seen:
            seen.add(u)
            out.append(u)
    return out


def safe_filename(url: str, taken: set[str]) -> str:
    parsed = urlparse(url)
    name = unquote(os.path.basename(parsed.path)) or "image"
    name = re.sub(r"[^A-Za-z0-9._-]+", "-", name)
    if not name or name.startswith("."):
        name = "image" + name
    base, ext = os.path.splitext(name)
    candidate = name
    i = 1
    while candidate in taken:
        candidate = f"{base}-{i}{ext}"
        i += 1
    taken.add(candidate)
    return candidate


def normalize_url(url: str, site_base: str) -> str | None:
    if not url:
        return None
    url = html_lib.unescape(url).strip()
    if url.startswith("data:"):
        return None
    if url.startswith("//"):
        return "https:" + url
    if url.startswith("/") and site_base:
        return site_base.rstrip("/") + url
    if url.startswith("http://") or url.startswith("https://"):
        return url
    return None


MAX_BYTES = 2 * 1024 * 1024  # 2 MB


def download_image(url: str, dest: Path, session: requests.Session) -> tuple[bool, str]:
    try:
        with session.get(url, timeout=10, stream=True, allow_redirects=True) as r:
            if r.status_code != 200:
                return False, f"status={r.status_code}"
            ctype = r.headers.get("content-type", "")
            if "image" not in ctype and not re.search(
                r"\.(jpe?g|png|gif|webp|svg|bmp|tiff?)$", url, re.IGNORECASE
            ):
                return False, f"not image (ctype={ctype})"
            size = 0
            with dest.open("wb") as out:
                for chunk in r.iter_content(64 * 1024):
                    if not chunk:
                        continue
                    size += len(chunk)
                    if size > MAX_BYTES:
                        out.close()
                        dest.unlink(missing_ok=True)
                        return False, f"too big (>{MAX_BYTES} bytes)"
                    out.write(chunk)
            return True, f"ok ({size} bytes)"
    except requests.RequestException as e:
        return False, f"error: {e.__class__.__name__}: {e}"
    except OSError as e:
        return False, f"io: {e}"


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


def main() -> int:
    sql_path = Path(sys.argv[1]) if len(sys.argv) > 1 else DEFAULT_SQL
    if not sql_path.exists():
        log.error("SQL file not found: %s", sql_path)
        return 1

    log.info("Reading %s (%.1f MB)", sql_path, sql_path.stat().st_size / 1e6)

    posts_by_id: dict[int, Post] = {}
    thumbs: dict[int, int] = {}  # post_id -> thumbnail attachment id
    attachments: dict[int, str] = {}  # attachment id -> guid url
    options: dict[str, str] = {}
    type_counts: dict[str, int] = {}

    insert_count = 0
    for table, cols, row in stream_inserts(sql_path):
        insert_count += 1
        if insert_count % 500 == 0:
            log.info("  parsed %d rows...", insert_count)
        try:
            rec = dict(zip(cols, row))
        except Exception:
            continue

        if table == "wpht_options":
            name = rec.get("option_name")
            if name in ("siteurl", "home"):
                options[name] = str(rec.get("option_value") or "")

        elif table == "wpht_posts":
            status = rec.get("post_status")
            ptype = rec.get("post_type") or ""
            type_counts[ptype] = type_counts.get(ptype, 0) + 1
            if ptype == "attachment":
                try:
                    aid = int(rec.get("ID"))
                    attachments[aid] = str(rec.get("guid") or "")
                except (TypeError, ValueError):
                    pass
                continue
            if status != "publish":
                continue
            if ptype not in ("post", "page"):
                continue
            try:
                pid = int(rec.get("ID"))
            except (TypeError, ValueError):
                continue
            content = rec.get("post_content") or ""
            tlen = text_length(content)
            if tlen < 400:
                continue
            title = (rec.get("post_title") or "").strip()
            slug = (rec.get("post_name") or "").strip() or f"post-{pid}"
            date = str(rec.get("post_date") or "")
            excerpt = (rec.get("post_excerpt") or "").strip()
            posts_by_id[pid] = Post(
                id=pid,
                slug=slug,
                title=title,
                type=ptype,
                date=date,
                excerpt=excerpt,
                html=content,
                guid=str(rec.get("guid") or ""),
                text_length=tlen,
            )

        elif table == "wpht_postmeta":
            if rec.get("meta_key") == "_thumbnail_id":
                try:
                    pid = int(rec.get("post_id"))
                    tid = int(rec.get("meta_value"))
                    thumbs[pid] = tid
                except (TypeError, ValueError):
                    pass

    log.info("Parsed %d target rows. Found %d candidate posts.", insert_count, len(posts_by_id))
    log.info("Post type counts: %s", type_counts)
    log.info("Options: siteurl=%s home=%s", options.get("siteurl"), options.get("home"))

    site_base = options.get("home") or options.get("siteurl") or ""

    # Rank posts by length; keep top 40.
    ranked = sorted(posts_by_id.values(), key=lambda p: p.text_length, reverse=True)
    top_posts = ranked[:40]
    log.info("Selected top %d posts by text length.", len(top_posts))

    # Gather all image URLs (raw), plus featured image attachments.
    all_urls: dict[str, str] = {}  # normalized url -> filename
    taken_names: set[str] = set()

    for p in top_posts:
        raw_urls = extract_image_urls(p.html)
        tid = thumbs.get(p.id)
        if tid and tid in attachments:
            fg = attachments[tid]
            if fg:
                raw_urls.insert(0, fg)
        for raw in raw_urls:
            norm = normalize_url(raw, site_base)
            if not norm:
                continue
            if norm not in all_urls:
                all_urls[norm] = safe_filename(norm, taken_names)

    log.info("Discovered %d unique image URLs to fetch.", len(all_urls))

    # Download.
    session = requests.Session()
    session.headers.update(
        {
            "User-Agent": (
                "OrthoGlobe-LegacyImport/1.0 (+https://orthoglobe.org) "
                "python-requests"
            )
        }
    )

    downloaded: dict[str, str] = {}  # original_url -> local_filename
    failed: list[tuple[str, str]] = []
    ok_count = 0
    for url, fname in all_urls.items():
        dest = IMAGES_DIR / fname
        if dest.exists() and dest.stat().st_size > 0:
            downloaded[url] = fname
            ok_count += 1
            continue
        ok, msg = download_image(url, dest, session)
        if ok:
            downloaded[url] = fname
            ok_count += 1
            log.info("GOT %s -> %s (%s)", url, fname, msg)
        else:
            failed.append((url, msg))
            log.warning("SKIP %s (%s)", url, msg)
        time.sleep(0.05)

    log.info("Downloaded %d images; skipped %d.", ok_count, len(failed))

    # Rewrite HTML in each post to use /legacy/images/<filename>.
    def rewrite(html: str) -> tuple[str, list[str]]:
        used: list[str] = []

        def sub_src(m: re.Match) -> str:
            original = html_lib.unescape(m.group(1).strip())
            norm = normalize_url(original, site_base)
            if norm and norm in downloaded:
                fname = downloaded[norm]
                used.append(fname)
                return f'src="/legacy/images/{fname}"'
            return m.group(0)

        def sub_srcset(m: re.Match) -> str:
            parts_out = []
            changed = False
            for part in m.group(1).split(","):
                part = part.strip()
                if not part:
                    continue
                bits = part.split(" ", 1)
                u = html_lib.unescape(bits[0])
                size = f" {bits[1]}" if len(bits) > 1 else ""
                norm = normalize_url(u, site_base)
                if norm and norm in downloaded:
                    fname = downloaded[norm]
                    used.append(fname)
                    parts_out.append(f"/legacy/images/{fname}{size}")
                    changed = True
                else:
                    parts_out.append(f"{u}{size}")
            if changed:
                return f'srcset="{", ".join(parts_out)}"'
            return m.group(0)

        html = IMG_SRC_RE.sub(sub_src, html)
        html = IMG_SRCSET_RE.sub(sub_srcset, html)
        # de-dupe used
        seen: set[str] = set()
        uniq = []
        for u in used:
            if u not in seen:
                seen.add(u)
                uniq.append(u)
        return html, uniq

    out_records = []
    for p in top_posts:
        new_html, used_images = rewrite(p.html)
        p.html = new_html
        p.images = used_images
        # Featured image
        tid = thumbs.get(p.id)
        if tid and tid in attachments:
            norm = normalize_url(attachments[tid], site_base)
            if norm and norm in downloaded:
                p.featured_image = f"/legacy/images/{downloaded[norm]}"
        if not p.featured_image and used_images:
            p.featured_image = f"/legacy/images/{used_images[0]}"

        out_records.append(
            {
                "id": p.id,
                "slug": p.slug,
                "title": p.title,
                "type": p.type,
                "date": p.date,
                "excerpt": p.excerpt,
                "featuredImage": p.featured_image,
                "images": p.images,
                "html": p.html,
            }
        )

    payload = {
        "source": {
            "sqlFile": str(sql_path),
            "siteurl": options.get("siteurl", ""),
            "home": options.get("home", ""),
            "generatedAt": time.strftime("%Y-%m-%dT%H:%M:%S%z"),
        },
        "stats": {
            "candidatePosts": len(posts_by_id),
            "exportedPosts": len(out_records),
            "imagesFound": len(all_urls),
            "imagesDownloaded": ok_count,
            "imagesSkipped": len(failed),
            "typeCounts": type_counts,
        },
        "posts": out_records,
    }

    JSON_OUT.write_text(json.dumps(payload, indent=2, ensure_ascii=False), encoding="utf-8")
    size_mb = JSON_OUT.stat().st_size / 1e6
    log.info("Wrote %s (%.2f MB)", JSON_OUT, size_mb)

    if size_mb > 5.0:
        log.warning("JSON exceeds 5 MB — truncating to top 20 posts.")
        payload["posts"] = out_records[:20]
        payload["stats"]["exportedPosts"] = 20
        payload["stats"]["truncatedTo"] = 20
        JSON_OUT.write_text(
            json.dumps(payload, indent=2, ensure_ascii=False), encoding="utf-8"
        )
        log.info("Re-wrote %s (%.2f MB)", JSON_OUT, JSON_OUT.stat().st_size / 1e6)

    # Brief summary to stdout for the caller to pipe back.
    print("---SUMMARY---")
    print(f"candidatePosts={len(posts_by_id)}")
    print(f"exportedPosts={len(payload['posts'])}")
    print(f"imagesDownloaded={ok_count}")
    print(f"imagesSkipped={len(failed)}")
    print(f"siteurl={options.get('siteurl', '')}")
    print(f"home={options.get('home', '')}")
    print("typeCounts=" + json.dumps(type_counts))
    print("sampleTitles:")
    for p in payload["posts"][:10]:
        print(f"  - [{p['type']}] {p['title']}  ({p['slug']})")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())
