"""Rebuild people tab with one Face column (G), images on the correct rows."""
from __future__ import annotations

from pathlib import Path

import openpyxl
from openpyxl.drawing.image import Image as XLImage
from openpyxl.styles import Alignment, Font
from openpyxl.utils import get_column_letter
from openpyxl.utils.cell import range_boundaries
from PIL import Image as PILImage

ROOT = Path(r"C:\Users\c5717\Downloads\modgle-demo-faces")
SRC = Path(r"C:\Users\c5717\Downloads\modgle-demo-data-v2.xlsx")
OUT = Path(r"C:\Users\c5717\Downloads\modgle-demo-data-v3.xlsx")
THUMBS = ROOT / "thumbs"
THUMB_PX = 64


def thumb_for(demo_id: str) -> Path | None:
    src = ROOT / f"{demo_id}.jpg"
    if not src.exists() or src.stat().st_size < 8000:
        return None
    THUMBS.mkdir(parents=True, exist_ok=True)
    dest = THUMBS / f"{demo_id}.jpg"
    if dest.exists() and dest.stat().st_mtime >= src.stat().st_mtime and dest.stat().st_size > 400:
        return dest
    try:
        im = PILImage.open(src)
        im.load()
        im = im.convert("RGB")
    except Exception as e:
        print("skip_bad", src.name, e, flush=True)
        return None
    im.thumbnail((THUMB_PX, THUMB_PX))
    canvas = PILImage.new("RGB", (THUMB_PX, THUMB_PX), (255, 255, 255))
    canvas.paste(im, ((THUMB_PX - im.width) // 2, (THUMB_PX - im.height) // 2))
    canvas.save(dest, "JPEG", quality=70, optimize=True)
    return dest


def main() -> None:
    wb = openpyxl.load_workbook(SRC)
    ws = wb["people"]
    ws._images = []

    headers = [c.value for c in next(ws.iter_rows(min_row=1, max_row=1))]
    for name in ("face", "face_file", "face_kind", "face_web"):
        if name in headers:
            ws.delete_cols(headers.index(name) + 1)
            headers = [c.value for c in next(ws.iter_rows(min_row=1, max_row=1))]

    # Column G: after last_name (F), before handle.
    headers = [c.value for c in next(ws.iter_rows(min_row=1, max_row=1))]
    last_name_col = headers.index("last_name") + 1
    insert_at = last_name_col + 1  # G if last_name is F
    ws.insert_cols(insert_at)
    ws.cell(1, insert_at, "face")
    ws.cell(1, insert_at).font = Font(bold=True)
    face_letter = get_column_letter(insert_at)
    ws.column_dimensions[face_letter].width = 14

    demo_col = headers.index("demo_id") + 1
    placed = 0
    missing = []
    mappings = []
    for row in range(2, ws.max_row + 1):
        demo_id = ws.cell(row, demo_col).value
        if not demo_id:
            continue
        ws.row_dimensions[row].height = 72
        cell = ws.cell(row, insert_at)
        cell.value = None
        cell.alignment = Alignment(horizontal="center", vertical="center")
        thumb = thumb_for(str(demo_id))
        if not thumb:
            missing.append(str(demo_id))
            continue
        img = XLImage(str(thumb))
        img.width = 70
        img.height = 70
        img.anchor = f"{face_letter}{row}"
        ws.add_image(img)
        placed += 1
        if placed <= 5 or str(demo_id) in ("DEMO0010", "DEMO0500", "DEMO0963"):
            mappings.append(f"{demo_id} -> {face_letter}{row}")

    if "faces" in wb.sheetnames:
        del wb["faces"]

    wb.save(OUT)
    wb.close()
    print("saved", OUT)
    print("face_column", face_letter)
    print("placed", placed)
    print("missing", len(missing))
    print("missing_ids", ",".join(missing))
    print("sample", "; ".join(mappings))


if __name__ == "__main__":
    main()