"""Read-only audit of every image referenced by a question."""
import collections, mimetypes, sys
sys.path.insert(0, "/app")
from app.database import SessionLocal
from sqlalchemy import text
from app.services import storage_service, vision_service

db = SessionLocal()
rows = db.execute(text("""
    select id, 'stem' role, image_path path from questions where image_path is not null
    union all
    select id, 'expl', explanation_image_path from questions where explanation_image_path is not null
    order by 1
""")).fetchall()

MAGIC = [
 (b"\xff\xd8\xff", "jpeg"), (b"\x89PNG\r\n\x1a\n", "png"),
 (b"GIF8", "gif"), (b"RIFF", "webp?"),
 (b"\x00\x00\x00\x0cjP  ", "jp2"), (b"\xff\x4f\xff\x51", "j2k-codestream"),
 (b"BM", "bmp"), (b"II*\x00", "tiff"), (b"MM\x00*", "tiff"),
 (b"<?xml", "svg/xml"), (b"<svg", "svg"), (b"%PDF", "pdf"),
]
def sniff(d):
    for sig, name in MAGIC:
        if d.startswith(sig):
            if name == "webp?":
                return "webp" if d[8:12] == b"WEBP" else "riff"
            return name
    return "UNKNOWN:" + d[:8].hex()

missing, mismatch, undecodable, ok = [], [], [], 0
by_pair = collections.Counter()
for qid, role, path in rows:
    key = path.removeprefix("/uploads/")
    data = storage_service.s3_object(key) or storage_service.load(key)
    if not data:
        missing.append((qid, role, path)); continue
    actual = sniff(data)
    guessed = (mimetypes.guess_type(key)[0] or "?")
    by_pair[(key.rsplit(".",1)[-1].lower(), guessed, actual)] += 1
    if guessed.split("/")[-1] != actual and not (guessed=="image/jpeg" and actual=="jpeg"):
        mismatch.append((qid, role, path, guessed, actual, len(data)))
    img = vision_service.prepare(data, guessed)
    if img is None:
        undecodable.append((qid, role, path, actual, len(data)))
    else:
        ok += 1

print(f"referenced rows: {len(rows)}   loaded+prepared OK: {ok}")
print(f"\nMISSING FILES ({len(missing)}):")
for m in missing[:40]: print("  ", m)
print(f"\nprepare() returned None ({len(undecodable)}):")
for m in undecodable[:40]: print("  ", m)
print(f"\next / mimetypes.guess / actual magic  -> count")
for (e,g,a),c in sorted(by_pair.items(), key=lambda x:-x[1]): print(f"   .{e:6} {g:24} {a:18} {c}")
print(f"\nname-vs-content mismatches: {len(mismatch)}")
for m in mismatch[:15]: print("  ", m)

# What prepare() turns each format into
print("\n--- prepare() output media_type by input ext ---")
out = collections.Counter()
for qid, role, path in rows:
    key = path.removeprefix("/uploads/")
    data = storage_service.s3_object(key) or storage_service.load(key)
    if not data: continue
    img = vision_service.prepare(data, mimetypes.guess_type(key)[0])
    out[(key.rsplit(".",1)[-1].lower(), img.media_type if img else "NONE")] += 1
for k,v in sorted(out.items()): print("  ", k, v)