"""
Inspect and repair parts_inventory.part_images using on-disk file existence.

Use this when parts show broken image entries that point to missing files.

Features:
- Inspect one location (e.g. BA001-A2) or all parts
- Detect broken image rows (file_path missing on disk, with no URL)
- Optionally delete broken rows
- Recompute is_primary so each part has one valid primary image

Examples:
  # Inspect one part by location only
  python scripts/repair_part_images_from_disk.py --host 127.0.0.1 --user bill --password 5074367 --location BA001-A2

  # Apply repair for one location
  python scripts/repair_part_images_from_disk.py --host 127.0.0.1 --user bill --password 5074367 --location BA001-A2 --apply

  # Apply repair for all parts
  python scripts/repair_part_images_from_disk.py --host 127.0.0.1 --user bill --password 5074367 --all --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 part_images rows by validating image files on disk.")
    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("--location", help="Specific storage location name (e.g. BA001-A2)")
    p.add_argument("--all", action="store_true", help="Process all parts instead of a single location.")
    p.add_argument("--apply", action="store_true", help="Apply fixes. Without this, script only reports.")
    return p.parse_args()


def file_exists(upload_root: Path, file_path: str | None) -> bool:
    if not file_path:
        return False
    return (upload_root / file_path).exists()


def main():
    args = parse_args()
    if not args.all and not args.location:
        raise SystemExit("Provide --location <name> or --all")

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

    deleted_rows = 0
    fixed_primary_parts = 0

    try:
        with conn.cursor() as cur:
            if args.all:
                cur.execute("SELECT id,name FROM parts ORDER BY id")
            else:
                cur.execute(
                    """
                    SELECT p.id, p.name
                    FROM parts p
                    JOIN storage_locations s ON s.id = p.storage_location_id
                    WHERE s.name = %s
                    ORDER BY p.id
                    """,
                    (args.location,),
                )
            parts = cur.fetchall()

            if not parts:
                print("No matching parts found.")
                return

            for part in parts:
                part_id = part["id"]
                cur.execute(
                    """
                    SELECT id, file_path, url, is_primary, original_filename, source, created_at
                    FROM part_images
                    WHERE part_id = %s
                    ORDER BY is_primary DESC, id
                    """,
                    (part_id,),
                )
                images = cur.fetchall()
                if not images:
                    continue

                broken_ids = []
                valid_ids = []
                print(f"\nPart {part_id} ({part['name']}):")
                for img in images:
                    has_url = bool((img.get("url") or "").strip())
                    exists = has_url or file_exists(upload_root, img.get("file_path"))
                    status = "OK" if exists else "MISSING"
                    print(
                        f"  id={img['id']} primary={img['is_primary']} status={status} "
                        f"file_path={img.get('file_path')} url={img.get('url')}"
                    )
                    if exists:
                        valid_ids.append(img["id"])
                    else:
                        broken_ids.append(img["id"])

                if args.apply and broken_ids:
                    cur.execute(
                        f"DELETE FROM part_images WHERE id IN ({','.join(['%s'] * len(broken_ids))})",
                        tuple(broken_ids),
                    )
                    deleted_rows += cur.rowcount

                if args.apply:
                    # Re-read after possible delete and normalize primary flags.
                    cur.execute("SELECT id FROM part_images WHERE part_id = %s ORDER BY id", (part_id,))
                    remaining = [r["id"] for r in cur.fetchall()]
                    if remaining:
                        cur.execute("UPDATE part_images SET is_primary = 0 WHERE part_id = %s", (part_id,))
                        cur.execute("UPDATE part_images SET is_primary = 1 WHERE id = %s", (remaining[0],))
                        fixed_primary_parts += 1

        if args.apply:
            conn.commit()
            print(
                f"\nRepair complete: deleted_rows={deleted_rows} "
                f"fixed_primary_parts={fixed_primary_parts}"
            )
        else:
            conn.rollback()
            print("\nDry run complete (no changes applied). Re-run with --apply to commit fixes.")
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()


if __name__ == "__main__":
    main()