"""
Move PartKeepr attachment files into PartsInventory upload layout.

Behavior:
1) Reads image attachment metadata from PartKeepr.PartAttachment.
2) Recursively searches a source root (default: /var/www/html/Server/files)
   for matching filenames.
3) Moves files to destination upload tree:
     <dest-root>/parts/<part_id>/<filename>[.<extension>]
4) Inserts/updates parts_inventory.part_images rows for moved files.

This script assumes parts_inventory.parts.id == PartKeepr.Part.id
(which matches the rebuild SQL workflow that preserves IDs).

Usage example:
  python scripts/move_partkeepr_attachments_from_files.py \
    --host 127.0.0.1 --port 3306 --user root --password secret \
    --source-db PartKeepr --target-db parts_inventory \
    --source-root /var/www/html/Server/files \
    --dest-root /var/www/html/Server/partsinventory_uploads \
    --dry-run
"""
from __future__ import annotations

import argparse
import shutil
from pathlib import Path
from typing import Dict, Iterable, List

import pymysql
from pymysql.cursors import DictCursor

IMAGE_EXTS = {".png", ".jpg", ".jpeg", ".gif", ".webp", ".bmp", ".tiff", ".tif", ".svg"}


def parse_args():
    parser = argparse.ArgumentParser(description="Move PartKeepr attachment files to PartsInventory upload layout.")
    parser.add_argument("--host", required=True)
    parser.add_argument("--port", type=int, default=3306)
    parser.add_argument("--user", required=True)
    parser.add_argument("--password", required=True)
    parser.add_argument("--source-db", default="PartKeepr")
    parser.add_argument("--target-db", default="parts_inventory")
    parser.add_argument("--source-root", default="/var/www/html/Server/files")
    parser.add_argument("--dest-root", default="/var/www/html/Server/partsinventory_uploads")
    parser.add_argument("--dry-run", action="store_true")
    return parser.parse_args()


def connect(host: str, port: int, user: str, password: str, database: str):
    return pymysql.connect(
        host=host,
        port=port,
        user=user,
        password=password,
        database=database,
        cursorclass=DictCursor,
        autocommit=False,
    )


def is_image_attachment(row: dict) -> bool:
    if row.get("isImage") == 1:
        return True
    mimetype = (row.get("mimetype") or "").lower()
    if mimetype.startswith("image/"):
        return True
    extension = (row.get("extension") or "").strip().lower()
    if extension and f".{extension.lstrip('.')}" in IMAGE_EXTS:
        return True
    original = (row.get("originalname") or "").lower()
    return any(original.endswith(ext) for ext in IMAGE_EXTS)


def build_file_index(source_root: Path) -> tuple[Dict[str, List[Path]], Dict[str, List[Path]]]:
    by_name: Dict[str, List[Path]] = {}
    by_stem: Dict[str, List[Path]] = {}
    for p in source_root.rglob("*"):
        if not p.is_file():
            continue
        by_name.setdefault(p.name.lower(), []).append(p)
        by_stem.setdefault(p.stem.lower(), []).append(p)
    return by_name, by_stem


def candidate_names(filename: str, extension: str | None) -> Iterable[str]:
    cleaned = (filename or "").strip()
    if not cleaned:
        return []
    names = {cleaned}
    ext = (extension or "").strip().lstrip(".")
    if ext:
        if not cleaned.lower().endswith(f".{ext.lower()}"):
            names.add(f"{cleaned}.{ext}")
    return names


def pick_source_file(
    by_name: Dict[str, List[Path]],
    by_stem: Dict[str, List[Path]],
    filename: str,
    extension: str | None,
    used_sources: set[Path],
) -> Path | None:
    matches: List[Path] = []
    for name in candidate_names(filename, extension):
        matches.extend(by_name.get(name.lower(), []))
    if not matches:
        # Fallback: ignore extension and match by stem only.
        stem = Path((filename or "").strip()).stem.lower()
        if stem:
            matches.extend(by_stem.get(stem, []))
    if not matches:
        return None
    # Prefer existing, not-yet-used files, shortest path first.
    unique = []
    seen = set()
    for p in matches:
        key = str(p)
        if key in seen:
            continue
        seen.add(key)
        unique.append(p)
    unique.sort(key=lambda p: (len(p.parts), str(p)))
    for p in unique:
        if p in used_sources:
            continue
        if p.exists():
            return p
    return None


def ensure_part_exists(dst_cur, part_id: int) -> bool:
    dst_cur.execute("SELECT id FROM parts WHERE id = %s LIMIT 1", (part_id,))
    return dst_cur.fetchone() is not None


def ensure_part_image_row(dst_cur, part_id: int, rel_path: str, original_name: str, is_primary: int, dry_run: bool) -> bool:
    dst_cur.execute(
        """
        SELECT id FROM part_images
        WHERE part_id = %s AND file_path = %s
        LIMIT 1
        """,
        (part_id, rel_path),
    )
    row = dst_cur.fetchone()
    if row:
        return False
    if dry_run:
        return True
    dst_cur.execute(
        """
        INSERT INTO part_images (part_id, file_path, original_filename, source, is_primary)
        VALUES (%s, %s, %s, 'uploaded', %s)
        """,
        (part_id, rel_path, original_name, is_primary),
    )
    return True


def main():
    args = parse_args()
    source_root = Path(args.source_root)
    dest_root = Path(args.dest_root)

    if not source_root.exists():
        raise RuntimeError(f"Source root does not exist: {source_root}")

    src_conn = connect(args.host, args.port, args.user, args.password, args.source_db)
    dst_conn = connect(args.host, args.port, args.user, args.password, args.target_db)

    moved = 0
    inserted_rows = 0
    missing_source_file = 0
    missing_target_part = 0
    already_in_place = 0
    reused_source_match = 0
    found_in_dest = 0

    try:
        src_by_name, src_by_stem = build_file_index(source_root)
        dst_by_name, dst_by_stem = build_file_index(dest_root) if dest_root.exists() else ({}, {})
        used_sources: set[Path] = set()

        with src_conn.cursor() as src, dst_conn.cursor() as dst:
            src.execute(
                """
                SELECT id, part_id, filename, originalname, extension, isImage, mimetype
                FROM PartAttachment
                ORDER BY id
                """
            )
            attachments = [row for row in src.fetchall() if is_image_attachment(row)]

            for row in attachments:
                part_id = int(row["part_id"])
                if not ensure_part_exists(dst, part_id):
                    missing_target_part += 1
                    continue

                filename = row.get("filename") or ""
                extension = row.get("extension")
                original_name = (row.get("originalname") or filename).strip() or filename

                src_file = pick_source_file(src_by_name, src_by_stem, filename, extension, used_sources)
                source_is_dest = False
                if src_file is None:
                    # Fallback: file may already be under destination tree from prior partial run.
                    src_file = pick_source_file(dst_by_name, dst_by_stem, filename, extension, used_sources)
                    if src_file is not None:
                        source_is_dest = True
                        found_in_dest += 1
                if src_file is None:
                    missing_source_file += 1
                    continue

                suffix = src_file.suffix
                if not suffix and extension:
                    suffix = f".{str(extension).lstrip('.')}"
                if not suffix:
                    suffix = ".jpg"

                safe_stem = (Path(filename).stem if filename else src_file.stem) or f"attachment_{row['id']}"
                rel_path = Path("parts") / str(part_id) / f"{safe_stem}{suffix}"
                dest_file = dest_root / rel_path

                if source_is_dest or src_file.resolve() == dest_file.resolve():
                    already_in_place += 1
                else:
                    if src_file in used_sources:
                        reused_source_match += 1
                        missing_source_file += 1
                        continue
                    if not src_file.exists():
                        missing_source_file += 1
                        continue
                    if not args.dry_run:
                        dest_file.parent.mkdir(parents=True, exist_ok=True)
                        if dest_file.exists():
                            idx = 1
                            while True:
                                alt = dest_file.parent / f"{safe_stem}_{idx}{suffix}"
                                if not alt.exists():
                                    dest_file = alt
                                    rel_path = Path("parts") / str(part_id) / alt.name
                                    break
                                idx += 1
                        shutil.move(str(src_file), str(dest_file))
                    moved += 1
                    used_sources.add(src_file)

                dst.execute("SELECT COUNT(*) AS c FROM part_images WHERE part_id = %s", (part_id,))
                is_primary = 1 if int(dst.fetchone()["c"]) == 0 else 0

                inserted = ensure_part_image_row(
                    dst,
                    part_id=part_id,
                    rel_path=str(rel_path).replace("\\", "/"),
                    original_name=original_name,
                    is_primary=is_primary,
                    dry_run=args.dry_run,
                )
                if inserted:
                    inserted_rows += 1

        if args.dry_run:
            dst_conn.rollback()
        else:
            dst_conn.commit()

        print(
            "Attachment move complete:",
            f"moved={moved}",
            f"already_in_place={already_in_place}",
            f"part_images_inserted={inserted_rows}",
            f"missing_source_file={missing_source_file}",
            f"missing_target_part={missing_target_part}",
            f"reused_source_match={reused_source_match}",
            f"found_in_dest={found_in_dest}",
            f"dry_run={args.dry_run}",
        )
    except Exception:
        dst_conn.rollback()
        raise
    finally:
        src_conn.close()
        dst_conn.close()


if __name__ == "__main__":
    main()