#!/usr/bin/env python3
"""
Nexora — nexora.db ke image references ko disk par mojood files se dobara match karta hai.

Yeh script tab chalao jab repair_image_extensions.py ne files rename to kar di
hon magar database update na hua ho (rowid wala crash).

Yeh disk ko sach maanta hai: har DB reference ke liye dekhta hai ke file mojood
hai ya nahi. Agar nahi, to usi folder mein wahi naam magar doosri extension ke
saath dhoondta hai aur DB ko update kar deta hai.

Chalane ka tareeqa (cPanel -> Setup Python App -> Execute python script):
    fix_db_references.py --dry-run      # pehle sirf dekho
    fix_db_references.py                # phir actually theek karo

Uske baad app ko Restart karo.
"""

import argparse
import shutil
import sqlite3
import sys
from datetime import datetime
from pathlib import Path

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

EXTS = [".webp", ".png", ".jpg", ".jpeg", ".jfif", ".gif", ".avif", ".bmp"]

# (table, column) — jahan jahan image ka path store hota hai
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"),
]


def clean(ref):
    """DB value ko static/-relative path banata hai (app.py jaisa hi logic)."""
    v = (ref or "").strip().replace("\\", "/").lstrip("/")
    while v.lower().startswith("static/"):
        v = v[7:].lstrip("/")
    if not v:
        return ""
    low = v.lower()
    if not (low.startswith("images/") or low.startswith("uploads/")):
        v = "uploads/" + v
    return v


def exists(ref):
    return ref and (STATIC_DIR / ref).is_file()


def find_replacement(ref):
    """Same folder, same naam, magar koi doosri extension."""
    p = STATIC_DIR / ref
    parent, stem = p.parent, p.stem
    if parent.is_dir():
        for ext in EXTS:
            cand = parent / (stem + ext)
            if cand.is_file():
                return cand.relative_to(STATIC_DIR).as_posix()
        # case-insensitive fallback (Linux case-sensitive hai, Windows nahi)
        for child in parent.iterdir():
            if child.is_file() and child.stem.lower() == stem.lower():
                return child.relative_to(STATIC_DIR).as_posix()
    # poori static/ mein aakhri koshish
    for root in (STATIC_DIR / "Images", STATIC_DIR / "uploads"):
        if not root.is_dir():
            continue
        for found in root.rglob("*"):
            if found.is_file() and found.stem.lower() == stem.lower():
                return found.relative_to(STATIC_DIR).as_posix()
    return None


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

    if not DB_PATH.is_file():
        sys.exit(f"nexora.db nahi mili: {DB_PATH}")
    if not STATIC_DIR.is_dir():
        sys.exit(f"static/ folder nahi mila: {STATIC_DIR}")

    if not args.dry_run:
        backup = DB_PATH.with_name(f"nexora.db.bak-{datetime.now():%Y%m%d%H%M%S}")
        shutil.copy2(DB_PATH, backup)
        print(f"DB backup: {backup.name}\n")

    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'")}

    ok = fixed = missing = 0
    planned = []

    for table, column in DB_REFS:
        if table not in tables:
            continue
        try:
            # rowid ko explicit alias do — SQLite ise "id" naam se wapas karta hai
            rows = conn.execute(f"SELECT rowid AS _rid, {column} AS _val FROM {table}").fetchall()
        except sqlite3.OperationalError:
            continue

        for row in rows:
            raw = row["_val"]
            if not raw:
                continue
            ref = clean(raw)
            if exists(ref):
                ok += 1
                continue
            new_ref = find_replacement(ref)
            if new_ref:
                planned.append((table, column, row["_rid"], raw, new_ref))
                print(f"  FIX  {table}.{column} #{row['_rid']}")
                print(f"       {raw}")
                print(f"    -> {new_ref}")
                fixed += 1
            else:
                print(f"  MISSING  {table}.{column} #{row['_rid']}  ->  {raw}")
                print(f"           (yeh file disk par hai hi nahi — admin se dobara upload karo)")
                missing += 1

    print()
    print(f"  theek hain      : {ok}")
    print(f"  fix honge       : {fixed}")
    print(f"  file gayab      : {missing}")

    if args.dry_run:
        print("\n--dry-run: kuch change nahi kiya gaya.")
        conn.close()
        return

    for table, column, rid, _old, new_ref in planned:
        conn.execute(f"UPDATE {table} SET {column} = ? WHERE rowid = ?", (new_ref, rid))
    conn.commit()
    conn.close()

    print(f"\n{fixed} reference update ho gaye.")
    print("Ab app ko Restart karo (Setup Python App -> Restart).")


if __name__ == "__main__":
    main()