"""Check all volumes via direct SQLite query + spot-check via API."""
import sqlite3
import json
import urllib.request
import re

DB = r"F:\_Ai\Tipitaka-AI-Expert\RAG\tipitaka_mcu.db"
API = "http://localhost:8000/api/pages"

db = sqlite3.connect(DB)
db.row_factory = sqlite3.Row
cur = db.cursor()

# 1. Volume-by-volume footnote counts
cur.execute("""
    SELECT v.volume_number, v.title, COUNT(r.id) as fn_count
    FROM volumes v
    LEFT JOIN reference_markers r ON r.volume_num = v.volume_number AND r.type = 'footnote'
    GROUP BY v.volume_number
    ORDER BY v.volume_number
""")
rows = cur.fetchall()

print(f"{'Vol':>3} {'FN':>5}  Title")
print("=" * 55)
total_fn = 0
v_fn = 0
v_no = 0
for r in rows:
    cnt = r["fn_count"]
    total_fn += cnt
    if cnt > 0: v_fn += 1
    else: v_no += 1
    print(f"{r['volume_number']:>3} {cnt:>5}  {r['title'][:40]}")

print(f"\nWith FNs: {v_fn}, Without: {v_no}, Total: {total_fn}")

# 2. Distinct marker IDs per volume
print()
print("Unique markers per volume:")
cur.execute("""
    SELECT volume_num, COUNT(DISTINCT marker_id) as unique_ids
    FROM reference_markers WHERE type = 'footnote'
    GROUP BY volume_num ORDER BY volume_num
""")
for r in cur.fetchall():
    print(f"  Vol {r['volume_num']}: {r['unique_ids']} unique marker IDs")

# 3. Pages with most footnotes
print()
print("Top pages (most footnotes):")
cur.execute("""
    SELECT volume_num, page_num, COUNT(*) as cnt
    FROM reference_markers WHERE type = 'footnote'
    GROUP BY volume_num, page_num ORDER BY cnt DESC LIMIT 10
""")
for r in cur.fetchall():
    print(f"  Vol {r['volume_num']:>2} P{r['page_num']:>3}: {r['cnt']}")

db.close()

# 4. Spot-check via API (first 15 volumes + heavy ones)
print("\n\n=== API Spot Check ===")
def get_json(url, timeout=8):
    try:
        resp = urllib.request.urlopen(url, timeout=timeout)
        return json.loads(resp.read())
    except Exception as e:
        return {"_error": str(e)}

# Check first 15 volumes for sup tags
for vol in [1,2,3,5,10,14,15,20,25,30,35,40,45]:
    data = get_json(f"{API}/{vol}/1")
    if "_error" in data:
        print(f"Vol {vol}: API ERROR - {data['_error']}")
        continue
    fns = data.get("footnotes", [])
    html = data.get("content_html_formatted", "")
    sups = re.findall(r'<sup[^>]*>.*?</sup>', html)
    blank = data.get("is_blank", False)
    fn_str = f"{len(fns)} FN"
    sup_str = f"{len(sups)} Sup"
    status = "✅"
    if len(fns) > 0 and len(sups) == 0:
        status = "⚠️ NOSUP"
    elif blank:
        status = "⬜ blank"
    print(f"Vol {vol:>2} P1: {fn_str:>7}, {sup_str:>7}  {status}")

# Check pages with most footnotes
print()
heavy = [(5,34), (11,172), (11,180)]  # from top10
for vol, pg in heavy:
    data = get_json(f"{API}/{vol}/{pg}")
    if "_error" not in data:
        fns = data.get("footnotes", [])
        html = data.get("content_html_formatted", "")
        sups = re.findall(r'<sup[^>]*>.*?</sup>', html)
        print(f"Vol {vol:>2} P{pg:>3}: {len(fns)} FN, {len(sups)} Sup  {'✅' if len(fns)==len(sups) or len(sups)>0 else '⚠️'}")