#!/usr/bin/env python3
"""Extract auditable text from selected WeDrive research sources."""

from __future__ import annotations

import argparse
import csv
import subprocess
import tempfile
import zipfile
from xml.etree import ElementTree
from pathlib import Path

from docx import Document
from openpyxl import load_workbook
from pypdf import PdfReader
from pptx import Presentation


def extract_docx(path: Path) -> str:
    document = Document(path)
    blocks: list[str] = []
    for paragraph in document.paragraphs:
        text = paragraph.text.strip()
        if text:
            blocks.append(text)
    for table_number, table in enumerate(document.tables, start=1):
        blocks.append(f"\n## Table {table_number}")
        for row in table.rows:
            values = [cell.text.replace("\n", " ").strip() for cell in row.cells]
            blocks.append(" | ".join(values))
    return "\n\n".join(blocks)


def extract_docx_xml(path: Path) -> str:
    """Recover Word text when a corrupt embedded media file breaks python-docx."""
    with zipfile.ZipFile(path) as archive:
        document_xml = archive.read("word/document.xml")
    root = ElementTree.fromstring(document_xml)
    namespace = "{http://schemas.openxmlformats.org/wordprocessingml/2006/main}"
    blocks: list[str] = []
    for paragraph in root.iter(f"{namespace}p"):
        text = "".join(node.text or "" for node in paragraph.iter(f"{namespace}t")).strip()
        if text:
            blocks.append(text)
    return "\n\n".join(blocks)


def extract_xlsx(path: Path) -> str:
    workbook = load_workbook(path, read_only=True, data_only=True)
    blocks: list[str] = []
    for sheet in workbook.worksheets:
        blocks.append(f"# Sheet: {sheet.title}")
        for row in sheet.iter_rows(values_only=True):
            values = ["" if value is None else str(value).replace("\n", " ").strip() for value in row]
            while values and not values[-1]:
                values.pop()
            if any(values):
                blocks.append(" | ".join(values))
    return "\n".join(blocks)


def extract_pptx(path: Path) -> str:
    presentation = Presentation(path)
    blocks: list[str] = []
    for number, slide in enumerate(presentation.slides, start=1):
        blocks.append(f"# Slide {number}")
        for shape in slide.shapes:
            if hasattr(shape, "text") and shape.text.strip():
                blocks.append(shape.text.strip())
            if getattr(shape, "has_table", False):
                for row in shape.table.rows:
                    blocks.append(" | ".join(cell.text.replace("\n", " ").strip() for cell in row.cells))
    return "\n\n".join(blocks)


def extract_pdf(path: Path) -> str:
    reader = PdfReader(path)
    return "\n\n".join(
        f"# Page {number}\n\n{page.extract_text() or ''}"
        for number, page in enumerate(reader.pages, start=1)
    )


def convert_legacy(path: Path, output_dir: Path) -> Path:
    target_format = "docx" if path.suffix.lower() == ".doc" else "xlsx"
    command = [
        "soffice", "--headless", "--convert-to", target_format,
        "--outdir", str(output_dir), str(path),
    ]
    result = subprocess.run(command, capture_output=True, text=True, timeout=120)
    converted = output_dir / f"{path.stem}.{target_format}"
    if result.returncode != 0 or not converted.exists():
        detail = (result.stderr or result.stdout).strip()
        raise RuntimeError(f"LibreOffice conversion failed: {detail}")
    return converted


def extract(path: Path, legacy_dir: Path) -> str:
    suffix = path.suffix.lower()
    if suffix in {".md", ".txt", ".csv", ".tsv"}:
        return path.read_text(encoding="utf-8-sig", errors="replace")
    if suffix == ".docx":
        try:
            return extract_docx(path)
        except zipfile.BadZipFile:
            return extract_docx_xml(path)
    if suffix == ".xlsx":
        return extract_xlsx(path)
    if suffix == ".pptx":
        return extract_pptx(path)
    if suffix == ".pdf":
        return extract_pdf(path)
    if suffix in {".doc", ".xls"}:
        converted = convert_legacy(path, legacy_dir)
        return extract_docx(converted) if suffix == ".doc" else extract_xlsx(converted)
    raise ValueError(f"unsupported extension: {suffix}")


def main() -> int:
    parser = argparse.ArgumentParser()
    parser.add_argument("--manifest", type=Path, required=True)
    parser.add_argument("--output-dir", type=Path, required=True)
    args = parser.parse_args()

    with args.manifest.open(newline="", encoding="utf-8-sig") as handle:
        rows = list(csv.DictReader(handle))
        fieldnames = list(rows[0].keys()) if rows else []
    args.output_dir.mkdir(parents=True, exist_ok=True)

    with tempfile.TemporaryDirectory(prefix="market-research-legacy-") as temp_dir:
        legacy_dir = Path(temp_dir)
        for row in rows:
            source = Path(row["source_path"])
            target = args.output_dir / f"{row['deep_read_id']}.md"
            try:
                body = extract(source, legacy_dir).strip()
                target.write_text(
                    f"# {row['source_name']}\n\n"
                    f"- deep_read_id: {row['deep_read_id']}\n"
                    f"- project_or_topic: {row['project_or_topic']}\n"
                    f"- source_path: {source}\n\n{body}\n",
                    encoding="utf-8",
                )
                row["extraction_status"] = "ok" if body else "ok_empty"
                row["extracted_path"] = str(target)
            except Exception as exc:  # Keep per-file failures auditable.
                row["extraction_status"] = f"failed:{type(exc).__name__}:{exc}"
                row["extracted_path"] = ""

    with args.manifest.open("w", newline="", encoding="utf-8-sig") as handle:
        writer = csv.DictWriter(handle, fieldnames=fieldnames)
        writer.writeheader()
        writer.writerows(rows)

    ok = sum(row["extraction_status"].startswith("ok") for row in rows)
    failed = len(rows) - ok
    print(f"deep_reads={len(rows)} ok={ok} failed={failed} output={args.output_dir}")
    if failed:
        for row in rows:
            if not row["extraction_status"].startswith("ok"):
                print(f"{row['deep_read_id']} {row['extraction_status']}")
    return 0 if failed == 0 else 2


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