#!/usr/bin/env python3
import json, sqlite3
from pathlib import Path
ROOT=Path(__file__).resolve().parents[1]
DB=ROOT/"runtime"/"academy_index.sqlite3"
DB.parent.mkdir(parents=True,exist_ok=True)
if DB.exists(): DB.unlink()
con=sqlite3.connect(DB)
con.execute("CREATE TABLE skills(skill_id TEXT PRIMARY KEY,domain_id TEXT,name TEXT,level INTEGER,status TEXT,forge_allowed INTEGER,description TEXT)")
con.execute("CREATE VIRTUAL TABLE cards USING fts5(skill_id,domain_id,skill_name,card_type,question,answer)")
cat=json.loads((ROOT/"curriculum"/"catalog.json").read_text())
con.executemany("INSERT INTO skills VALUES(?,?,?,?,?,?,?)",
 [(x["skill_id"],x["domain_id"],x["name"],x["level"],x["status"],int(x["forge_allowed"]),x["description"]) for x in cat])
rows=[json.loads(x) for x in (ROOT/"knowledge"/"knowledge_cards.jsonl").read_text().splitlines() if x.strip()]
con.executemany("INSERT INTO cards VALUES(?,?,?,?,?,?)",
 [(x["skill_id"],x["domain_id"],x["skill_name"],x["card_type"],x["question"],x["answer"]) for x in rows])
con.commit(); con.close()
print(f"Built {DB} with {len(cat)} skills and {len(rows)} cards.")