#!/usr/bin/env python3
"""Extract embedded standard-product images with their sheet anchors.

This is a read-only OOXML media extractor. It does not edit the source workbook.
"""

from __future__ import annotations

import csv
import posixpath
import re
import zipfile
from pathlib import Path
from xml.etree import ElementTree as ET


SOURCE = Path("/Users/jack/Library/Containers/com.tencent.WeWorkMac/Data/Documents/Profiles/A74DF84611CC2ED79C4C206E40B5D80E/Caches/Files/2026-08/e1bb60652bec0366ac9e4d3283f9acc4/AI营养智慧食堂-标准产品方案2026CPT.xlsx")
OUT_ROOT = Path("/Users/jack/Desktop/智慧食堂28项目案例图册_20260806/02-证据与数据/product-images")
CSV_OUT = Path(__file__).resolve().parents[1] / "data/product-image-catalog-all.csv"

NS = {
    "a": "http://schemas.openxmlformats.org/drawingml/2006/main",
    "r": "http://schemas.openxmlformats.org/officeDocument/2006/relationships",
    "xdr": "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing",
    "s": "http://schemas.openxmlformats.org/spreadsheetml/2006/main",
    "pr": "http://schemas.openxmlformats.org/package/2006/relationships",
}


def col_name(n: int) -> str:
    s = ""
    while n >= 0:
        s = chr(n % 26 + 65) + s
        n = n // 26 - 1
    return s


def rels(z: zipfile.ZipFile, path: str):
    if path not in z.namelist(): return {}
    root = ET.fromstring(z.read(path))
    return {e.attrib["Id"]: e.attrib["Target"] for e in root.findall("pr:Relationship", NS)}


def resolve(base: str, target: str) -> str:
    return posixpath.normpath(posixpath.join(posixpath.dirname(base), target))


def shared_strings(z):
    path = "xl/sharedStrings.xml"
    if path not in z.namelist(): return []
    root = ET.fromstring(z.read(path))
    return ["".join(t.text or "" for t in si.findall(".//s:t", NS)) for si in root.findall("s:si", NS)]


def sheet_cells(z, path, shared):
    root = ET.fromstring(z.read(path)); out = {}
    for c in root.findall(".//s:c", NS):
        ref = c.attrib.get("r", ""); v = c.find("s:v", NS)
        if not ref or v is None: continue
        text = v.text or ""
        if c.attrib.get("t") == "s" and text.isdigit(): text = shared[int(text)]
        out[ref] = text
    return root, out


def nearby(cells, row0, col0):
    vals = []
    for r in range(max(1, row0 - 2), row0 + 5):
        for c in range(max(0, col0 - 3), col0 + 7):
            v = cells.get(f"{col_name(c)}{r}", "").strip()
            if v and v not in vals: vals.append(v)
    return " | ".join(vals)[:1000]


def main():
    OUT_ROOT.mkdir(parents=True, exist_ok=True); CSV_OUT.parent.mkdir(parents=True, exist_ok=True)
    rows = []
    with zipfile.ZipFile(SOURCE) as z:
        shared = shared_strings(z)
        wb = ET.fromstring(z.read("xl/workbook.xml"))
        wb_rels = rels(z, "xl/_rels/workbook.xml.rels")
        for sh in wb.findall("s:sheets/s:sheet", NS):
            sheet_name = sh.attrib["name"]
            sheet_path = resolve("xl/workbook.xml", wb_rels[sh.attrib[f"{{{NS['r']}}}id"]])
            root, cells = sheet_cells(z, sheet_path, shared)
            drawing = root.find("s:drawing", NS)
            if drawing is None: continue
            sheet_rel_path = posixpath.join(posixpath.dirname(sheet_path), "_rels", posixpath.basename(sheet_path) + ".rels")
            sheet_rels = rels(z, sheet_rel_path)
            drawing_path = resolve(sheet_path, sheet_rels[drawing.attrib[f"{{{NS['r']}}}id"]])
            droot = ET.fromstring(z.read(drawing_path))
            drel_path = posixpath.join(posixpath.dirname(drawing_path), "_rels", posixpath.basename(drawing_path) + ".rels")
            d_rels = rels(z, drel_path)
            for i, anchor in enumerate(list(droot), 1):
                frm = anchor.find("xdr:from", NS); blip = anchor.find(".//a:blip", NS)
                if frm is None or blip is None: continue
                row0 = int(frm.find("xdr:row", NS).text) + 1; col0 = int(frm.find("xdr:col", NS).text)
                rid = blip.attrib.get(f"{{{NS['r']}}}embed"); target = d_rels.get(rid, "")
                if not target: continue
                media_path = resolve(drawing_path, target); ext = Path(media_path).suffix.lower() or ".bin"
                safe_sheet = re.sub(r"[^0-9A-Za-z\u4e00-\u9fff_-]+", "-", sheet_name).strip("-")
                fname = f"{safe_sheet}-{col_name(col0)}{row0}-{Path(media_path).stem}{ext}"
                out = OUT_ROOT / fname; out.write_bytes(z.read(media_path))
                rows.append({"sheet": sheet_name, "anchor_cell": f"{col_name(col0)}{row0}", "source_media": media_path,
                             "extracted_file": str(out), "nearby_text": nearby(cells, row0, col0),
                             "source_workbook": str(SOURCE)})
    with CSV_OUT.open("w", encoding="utf-8-sig", newline="") as f:
        w = csv.DictWriter(f, fieldnames=list(rows[0])); w.writeheader(); w.writerows(rows)
    print(f"extracted {len(rows)} images to {OUT_ROOT}")
    print(CSV_OUT)


if __name__ == "__main__": main()