import re, sys; sys.path.insert(0,"/app")
from collections import Counter
from app.database import SessionLocal
from sqlalchemy import text as sa_text
db = SessionLocal()
rows = db.execute(sa_text("""SELECT id, image_path, explanation_image_path, question_text, explanation
                             FROM questions WHERE image_path IS NOT NULL AND image_path <> ''""")).fetchall()
print("stem images:", len(rows))
print("with explanation_image_path:", sum(1 for r in rows if r[2]))
print("total explanation_image_path in bank:", db.execute(sa_text("SELECT COUNT(*) FROM questions WHERE explanation_image_path IS NOT NULL AND explanation_image_path <> ''")).scalar())
CUE = re.compile(r"figure|photograph|photo\b|image|shown below|pictured|radiograph|"
                 r"x-ray|xray|ecg|electrocardiogram|shown here|depicted|illustrat", re.I)
b = Counter()
for qid, ip, eip, q, e in rows:
    s = bool(CUE.search(q or "")); x = bool(CUE.search(e or ""))
    b[(s,x)] += 1
print("stem-cue, expl-cue -> count:", dict(b))
print("  stem mentions (any):", b[(True,True)]+b[(True,False)])
print("  ONLY explanation   :", b[(False,True)])
print("  neither            :", b[(False,False)])
ext = Counter(str(r[1]).rsplit(".",1)[-1].lower() for r in rows)
print("extensions:", dict(ext))
print("sample paths:", [r[1] for r in rows[:4]])