#!/usr/bin/env python3
"""
Nexora — one-time repair for images whose file extension does not match their
real content (e.g. PNG bytes saved as "poster.webp").

Those files make the browser receive Content-Type: image/webp for PNG data,
which renders as a blank / black box on the live site.

What it does
------------
1. Walks static/Images and static/uploads.
2. Decodes every image with Pillow to learn its REAL format.
3. Renames each mismatched file to the correct extension.
4. Rewrites every reference in nexora.db so nothing 404s.
5. Reports any file that cannot be decoded at all (truly corrupt).

Usage (on cPanel, from the app root):
    python repair_image_extensions.py --dry-run    # look first
    python repair_image_extensions.py              # actually fix
Then restart the Python app.
"""

import argparse
import os
import sqlite3
import sys
from pathlib import Path

try:
    from PIL import Image
except ImportError:
    sys.exit("Pillow is not installed. Run: pip install Pillow")

BASE_DIR = Path(__file__).resolve().parent
STATIC_DIR = BASE_DIR / "static"
DB_PATH = BASE_DIR / "nexora.db"

SCAN_DIRS = [STATIC_DIR / "Images", STATIC_DIR / "uploads"]
IMAGE_EXTS = {".webp", ".png", ".jpg", ".jpeg", ".jfif", ".gif", ".avif", ".bmp"}

FORMAT_EXT = {
    "JPEG": ".jpg", "MPO": ".jpg",
    "PNG": ".png", "WEBP": ".webp", "GIF": ".gif",
    "BMP": ".bmp", "AVIF": ".avif",
}

# (table, column) pairs that hold an image reference
DB_REFS = [
    ("games", "image"),
    ("media", "image"),
    ("sponsors", "logo"),
    ("leaders", "image"),
    ("team_members", "image"),
    ("prize_items", "image"),
    ("registrations", "payment_proof"),
    ("settings", "site_logo"),
    ("settings", "home_background"),
    ("settings", "payment_qr_image"),
    ("settings", "prize_banner_image"),
    ("settings", "prize_banner_images"),
]


def rel_ref(path: Path) -> str:
    """static/Images/Logo/a.webp -> Images/Logo/a.webp"""
    return path.relative_to(STATIC_DIR).as_posix()


def unique_target(path: Path) -> Path:
    if not path.exists():
        return path
    stem, suffix, n = path.stem, path.suffix, 2
    while True:
        candidate = path.with_name(f"{stem}-{n}{suffix}")
        if not candidate.exists():
            return candidate
        n += 1


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument("--dry-run", action="store_true", help="report only, change nothing")
    args = ap.parse_args()

    renames = []   # (old_ref, new_ref, old_path, new_path)
    broken = []
    black = []

    for root in SCAN_DIRS:
        if not root.is_dir():
            continue
        for path in sorted(root.rglob("*")):
            if not path.is_file() or path.suffix.lower() not in IMAGE_EXTS:
                continue
            try:
                with Image.open(path) as im:
                    im.load()
                    real = (im.format or "").upper()
                    extrema = im.convert("RGB").getextrema()
            except Exception as exc:
                broken.append((rel_ref(path), str(exc)[:80]))
                continue

            if extrema == ((0, 0), (0, 0), (0, 0)):
                black.append(rel_ref(path))

            want = FORMAT_EXT.get(real)
            if not want:
                continue
            have = path.suffix.lower()
            # .jpeg / .jfif are legitimate aliases for JPEG — leave them alone
            if want == ".jpg" and have in (".jpg", ".jpeg", ".jfif"):
                continue
            if have == want:
                continue

            target = unique_target(path.with_suffix(want))
            renames.append((rel_ref(path), rel_ref(target), path, target))

    print(f"Scanned {sum(1 for r in SCAN_DIRS if r.is_dir())} root(s) under {STATIC_DIR}")
    print(f"  mismatched extensions : {len(renames)}")
    print(f"  undecodable files     : {len(broken)}")
    print(f"  fully black images    : {len(black)}")
    print()

    for old_ref, new_ref, _, _ in renames:
        print(f"  RENAME  {old_ref}\n       -> {new_ref}")
    for ref, err in broken:
        print(f"  CORRUPT {ref}  ({err})")
    for ref in black:
        print(f"  BLACK   {ref}  (re-upload this one from the admin panel)")

    if args.dry_run:
        print("\n--dry-run: nothing was changed.")
        return

    if not renames:
        print("Nothing to rename.")
        return

    for _, _, old_path, new_path in renames:
        os.rename(old_path, new_path)
    print(f"\nRenamed {len(renames)} file(s) on disk.")

    if not DB_PATH.is_file():
        print("nexora.db not found — skipped DB update.")
        return

    mapping = {old: new for old, new, _, _ in renames}
    # also map the bare filename, for legacy rows that store only the filename
    for old, new, _, _ in renames:
        mapping.setdefault(os.path.basename(old), os.path.basename(new))

    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    tables = {r[0] for r in conn.execute("SELECT name FROM sqlite_master WHERE type='table'")}
    updated = 0
    for table, column in DB_REFS:
        if table not in tables:
            continue
        try:
            rows = conn.execute(f"SELECT rowid, {column} FROM {table}").fetchall()
        except sqlite3.OperationalError:
            continue
        for row in rows:
            value = row[column]
            if not value:
                continue
            new_value = value
            for old, new in mapping.items():
                if old in new_value:
                    new_value = new_value.replace(old, new)
            if new_value != value:
                conn.execute(f"UPDATE {table} SET {column} = ? WHERE rowid = ?", (new_value, row["rowid"]))
                updated += 1
    conn.commit()
    conn.close()
    print(f"Updated {updated} database reference(s).")
    print("\nDone. Now restart the Python app (touch tmp/restart.txt).")


if __name__ == "__main__":
    main()