"""
Repair stale parts_inventory.part_images.file_path values by matching files on disk.

Why:
- Legacy migrated rows may point to missing files even when the actual file exists
  elsewhere under the uploads tree.
- New uploads work because they write correct file_path values.

What it does:
1) Reads part_images rows with file_path (and optional original_filename).
2) If file_path does not exist on disk, searches for candidates under:
     <upload_root>/parts/<part_id>/
   by stem (filename without extension) and original_filename.
3) If exactly one candidate is found, updates file_path to that relative path.
4) Optional cleanup: delete rows still unresolved and file-missing.

Examples:
  # Dry run
  python scripts/repair_part_image_paths.py --host 127.0.0.1 --user bill --password 5074367 --dry-run

  # Apply fixes
  python scripts/repair_part_image_paths.py --host 127.0.0.1 --user bill --password 5074367 --apply
"""
from __future__ import annotations

import argparse
from pathlib import Path

import pymysql
from pymysql.cursors import DictCursor


def parse_args():
    p = argparse.ArgumentParser(description="Repair parts_inventory.part_images.file_path values from on-disk files.")
    p.add_argument("--host", default="127.0.0.1")
    p.add_argument("--port", type=int, default=3306)
    p.add_argument("--user", required=True)
    p.add_argument("--password", required=True)
    p.add_argument("--database", default="parts_inventory")
    p.add_argument("--upload-root", default="/var/www/html/Server/partsinventory_uploads")
    p.add_argument("--apply", action="store_true", help="Persist changes. Default is dry-run.")
    p.add_argument("--delete-unresolved-missing", action="store_true", help="Delete rows still unresolved and missing.")
    p.add_argument("--limit", type=int, default=0, help="Optional max rows to inspect (0=all).")
    return p.parse_args()


def stem_of_name(name: str | None) -> str:
    if not name:
        return ""
    return Path(name).stem.strip().lower()


def build_candidate_map(part_dir: Path) -> tuple[dict[str, list[Path]], dict[str, list[Path]]]:
    by_stem: dict[str, list[Path]] = {}
    by_name: dict[str, list[Path]] = {}
    if not part_dir.exists():
        return by_stem, by_name
    for fp in part_dir.rglob("*"):
        if not fp.is_file():
            continue
        by_stem.setdefault(fp.stem.lower(), []).append(fp)
        by_name.setdefault(fp.name.lower(), []).append(fp)
    return by_stem, by_name


def main():
    args = parse_args()
    upload_root = Path(args.upload_root)

    conn = pymysql.connect(
        host=args.host,
        port=args.port,
        user=args.user,
        password=args.password,
        database=args.database,
        cursorclass=DictCursor,
        autocommit=False,
    )

    inspected = 0
    already_ok = 0
    fixed = 0
    unresolved = 0
    deleted = 0

    try:
        with conn.cursor() as cur:
            sql = """
                SELECT id, part_id, file_path, original_filename, url, is_primary
                FROM part_images
                ORDER BY id
            """
            if args.limit > 0:
                sql += " LIMIT %s"
                cur.execute(sql, (args.limit,))
            else:
                cur.execute(sql)
            rows = cur.fetchall()

            for row in rows:
                inspected += 1
                image_id = row["id"]
                part_id = row["part_id"]
                file_path = (row.get("file_path") or "").strip()
                url = (row.get("url") or "").strip()
                if url:
                    already_ok += 1
                    continue
                if not file_path:
                    unresolved += 1
                    continue

                full_path = upload_root / file_path
                if full_path.exists():
                    already_ok += 1
                    continue

                part_dir = upload_root / "parts" / str(part_id)
                by_stem, by_name = build_candidate_map(part_dir)

                candidates: list[Path] = []
                fp_name = Path(file_path).name.lower()
                if fp_name in by_name:
                    candidates.extend(by_name[fp_name])

                fp_stem = stem_of_name(file_path)
                if fp_stem in by_stem:
                    candidates.extend(by_stem[fp_stem])

                orig_name = (row.get("original_filename") or "").strip().lower()
                if orig_name and orig_name in by_name:
                    candidates.extend(by_name[orig_name])
                orig_stem = stem_of_name(orig_name)
                if orig_stem and orig_stem in by_stem:
                    candidates.extend(by_stem[orig_stem])

                # Unique by resolved path.
                uniq = []
                seen = set()
                for c in candidates:
                    key = str(c.resolve())
                    if key in seen:
                        continue
                    seen.add(key)
                    uniq.append(c)

                if len(uniq) == 1:
                    rel = uniq[0].relative_to(upload_root).as_posix()
                    print(f"FIX image_id={image_id} part_id={part_id}: {file_path} -> {rel}")
                    if args.apply:
                        cur.execute("UPDATE part_images SET file_path = %s WHERE id = %s", (rel, image_id))
                    fixed += 1
                else:
                    unresolved += 1
                    if args.delete_unresolved_missing and args.apply:
                        print(f"DELETE image_id={image_id} part_id={part_id}: unresolved missing path={file_path}")
                        cur.execute("DELETE FROM part_images WHERE id = %s", (image_id,))
                        deleted += 1

            # Normalize primary image after deletions/repairs.
            if args.apply:
                cur.execute("UPDATE part_images SET is_primary = 0")
                cur.execute(
                    """
                    UPDATE part_images pi
                    JOIN (
                      SELECT part_id, MIN(id) AS min_id
                      FROM part_images
                      GROUP BY part_id
                    ) x ON x.min_id = pi.id
                    SET pi.is_primary = 1
                    """
                )

        if args.apply:
            conn.commit()
        else:
            conn.rollback()

        print(
            "Repair summary:",
            f"inspected={inspected}",
            f"already_ok={already_ok}",
            f"fixed={fixed}",
            f"unresolved={unresolved}",
            f"deleted={deleted}",
            f"applied={args.apply}",
        )
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


if __name__ == "__main__":
    main()