import os,sqlite3,secrets
from datetime import datetime
from flask import Flask,request,session,redirect,url_for,render_template,jsonify,flash
app=Flask(__name__); app.secret_key=os.environ.get("SECRET_KEY","CHANGE_ME")
BASE=os.path.dirname(__file__); DB=os.path.join(BASE,"data","cbt.db"); UP=os.path.join(BASE,"static","uploads")
os.makedirs(os.path.dirname(DB),exist_ok=True); os.makedirs(UP,exist_ok=True)
def db():
 c=sqlite3.connect(DB); c.row_factory=sqlite3.Row; return c
def init():
 c=db(); c.executescript("""CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY AUTOINCREMENT,username TEXT UNIQUE,password TEXT,role TEXT,name TEXT,subject TEXT);
CREATE TABLE IF NOT EXISTS classes(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE);
CREATE TABLE IF NOT EXISTS subjects(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT UNIQUE);
CREATE TABLE IF NOT EXISTS students(id INTEGER PRIMARY KEY AUTOINCREMENT,nis TEXT UNIQUE,name TEXT,class_id INTEGER,username TEXT UNIQUE,password TEXT);
CREATE TABLE IF NOT EXISTS questions(id INTEGER PRIMARY KEY AUTOINCREMENT,class_id INTEGER,subject_id INTEGER,question TEXT,image TEXT,a TEXT,b TEXT,c TEXT,d TEXT,e TEXT,answer TEXT);
CREATE TABLE IF NOT EXISTS exams(id INTEGER PRIMARY KEY AUTOINCREMENT,title TEXT,class_id INTEGER,subject_id INTEGER,start_at TEXT,end_at TEXT,token TEXT UNIQUE);
CREATE TABLE IF NOT EXISTS answers(id INTEGER PRIMARY KEY AUTOINCREMENT,exam_id INTEGER,student_id INTEGER,question_id INTEGER,answer TEXT,updated_at TEXT,UNIQUE(exam_id,student_id,question_id));
CREATE TABLE IF NOT EXISTS results(id INTEGER PRIMARY KEY AUTOINCREMENT,exam_id INTEGER,student_id INTEGER,score REAL,submitted_at TEXT,UNIQUE(exam_id,student_id));""")
 c.execute("INSERT OR IGNORE INTO users(username,password,role,name) VALUES('admin','admin123','admin','Administrator')")
 c.execute("INSERT OR IGNORE INTO users(username,password,role,name) VALUES('guru','guru123','guru','Guru Demo')"); c.commit(); c.close()
init()
def need(role=None):
 if not session.get("user_id"): return redirect("/login")
 if role and session.get("role")!=role: return redirect("/")
@app.route("/login",methods=["GET","POST"])
def login():
 if request.method=="POST":
  u,p=request.form.get("username"),request.form.get("password"); c=db()
  x=c.execute("SELECT * FROM users WHERE username=? AND password=?",(u,p)).fetchone()
  role="siswa"
  if not x: x=c.execute("SELECT id,username,password,'siswa' role,name,'' subject FROM students WHERE username=? AND password=?",(u,p)).fetchone()
  c.close()
  if x: session.update(user_id=x["id"],username=x["username"],role=x["role"],name=x["name"]); return redirect("/")
  flash("Username/password salah")
 return render_template("login.html")
@app.get("/logout")
def logout(): session.clear(); return redirect("/login")
@app.get("/")
def home():
 r=need()
 return r or render_template("home.html")
@app.get("/admin")
def admin():
 r=need("admin")
 if r:return r
 c=db(); d={k:c.execute(q).fetchone()[0] for k,q in {"Siswa":"SELECT count(*) FROM students","Guru":"SELECT count(*) FROM users WHERE role='guru'","Soal":"SELECT count(*) FROM questions","Ujian":"SELECT count(*) FROM exams","Nilai":"SELECT count(*) FROM results"}.items()}; c.close()
 return render_template("admin.html",d=d)
@app.route("/admin/classes",methods=["GET","POST"])
def classes():
 r=need("admin")
 if r:return r
 c=db()
 if request.method=="POST": 
  try:c.execute("INSERT INTO classes(name) VALUES(?)",(request.form["name"],));c.commit()
  except sqlite3.IntegrityError:flash("Kelas sudah ada")
 rows=c.execute("SELECT * FROM classes ORDER BY name").fetchall();c.close();return render_template("simple.html",title="Rombel / Kelas",rows=rows,field="name",action="/admin/classes")
@app.route("/admin/subjects",methods=["GET","POST"])
def subjects():
 r=need("admin")
 if r:return r
 c=db()
 if request.method=="POST":
  try:c.execute("INSERT INTO subjects(name) VALUES(?)",(request.form["name"],));c.commit()
  except sqlite3.IntegrityError:flash("Mapel sudah ada")
 rows=c.execute("SELECT * FROM subjects ORDER BY name").fetchall();c.close();return render_template("simple.html",title="Mata Pelajaran",rows=rows,field="name",action="/admin/subjects")
@app.route("/admin/students",methods=["GET","POST"])
def students():
 r=need("admin")
 if r:return r
 c=db()
 if request.method=="POST":
  try:c.execute("INSERT INTO students(nis,name,class_id,username,password) VALUES(?,?,?,?,?)",(request.form["nis"],request.form["name"],request.form["class_id"],request.form["username"],request.form["password"]));c.commit()
  except sqlite3.IntegrityError:flash("NIS/username sudah digunakan")
 rows=c.execute("SELECT s.*,cl.name class_name FROM students s LEFT JOIN classes cl ON cl.id=s.class_id ORDER BY s.name").fetchall();cl=c.execute("SELECT * FROM classes").fetchall();c.close()
 return render_template("students.html",rows=rows,classes=cl)
@app.route("/admin/questions",methods=["GET","POST"])
def questions():
 r=need("admin")
 if r:return r
 c=db()
 if request.method=="POST":
  img="";f=request.files.get("image")
  if f and f.filename: img=secrets.token_hex(6)+"_"+os.path.basename(f.filename).replace(" ","_");f.save(os.path.join(UP,img))
  c.execute("INSERT INTO questions(class_id,subject_id,question,image,a,b,c,d,e,answer) VALUES(?,?,?,?,?,?,?,?,?,?)",(request.form["class_id"],request.form["subject_id"],request.form["question"],img,request.form.get("a"),request.form.get("b"),request.form.get("c"),request.form.get("d"),request.form.get("e"),request.form["answer"]));c.commit()
 rows=c.execute("SELECT q.*,cl.name class_name,s.name subject_name FROM questions q JOIN classes cl ON cl.id=q.class_id JOIN subjects s ON s.id=q.subject_id ORDER BY q.id DESC").fetchall();cl=c.execute("SELECT * FROM classes").fetchall();su=c.execute("SELECT * FROM subjects").fetchall();c.close()
 return render_template("questions.html",rows=rows,classes=cl,subjects=su)
@app.route("/admin/exams",methods=["GET","POST"])
def exams():
 r=need("admin")
 if r:return r
 c=db()
 if request.method=="POST":
  c.execute("INSERT INTO exams(title,class_id,subject_id,start_at,end_at,token) VALUES(?,?,?,?,?,?)",(request.form["title"],request.form["class_id"],request.form["subject_id"],request.form["start_at"],request.form["end_at"],request.form.get("token") or secrets.token_hex(4).upper()));c.commit()
 rows=c.execute("SELECT e.*,cl.name class_name,s.name subject_name FROM exams e JOIN classes cl ON cl.id=e.class_id JOIN subjects s ON s.id=e.subject_id ORDER BY e.id DESC").fetchall();cl=c.execute("SELECT * FROM classes").fetchall();su=c.execute("SELECT * FROM subjects").fetchall();c.close()
 return render_template("exams.html",rows=rows,classes=cl,subjects=su)
@app.post("/admin/exams/<int:eid>/delete")
def delexam(eid):
 r=need("admin")
 if r:return r
 c=db();c.execute("DELETE FROM exams WHERE id=?",(eid,));c.commit();c.close();return redirect("/admin/exams")
@app.get("/admin/results")
def results():
 r=need("admin")
 if r:return r
 c=db();rows=c.execute("SELECT r.score,r.submitted_at,e.title,cl.name class_name,s.name subject_name,st.name student_name FROM results r JOIN exams e ON e.id=r.exam_id JOIN classes cl ON cl.id=e.class_id JOIN subjects s ON s.id=e.subject_id JOIN students st ON st.id=r.student_id ORDER BY cl.name,s.name,st.name").fetchall();c.close();return render_template("results.html",rows=rows)
@app.get("/guru")
def guru():
 r=need("guru")
 if r:return r
 c=db();rows=c.execute("SELECT r.score,r.submitted_at,e.title,cl.name class_name,s.name subject_name,st.name student_name FROM results r JOIN exams e ON e.id=r.exam_id JOIN classes cl ON cl.id=e.class_id JOIN subjects s ON s.id=e.subject_id JOIN students st ON st.id=r.student_id ORDER BY r.submitted_at DESC").fetchall();c.close();return render_template("results.html",rows=rows)
@app.route("/exam",methods=["GET","POST"])
def exam():
 r=need("siswa")
 if r:return r
 c=db();st=c.execute("SELECT * FROM students WHERE id=?",(session["user_id"],)).fetchone()
 if request.method=="POST":
  ex=c.execute("SELECT * FROM exams WHERE token=? AND class_id=?",(request.form["token"],st["class_id"])).fetchone()
  if not ex:c.close();flash("Token tidak valid");return render_template("start.html")
  session["exam_id"]=ex["id"]
 eid=session.get("exam_id")
 if not eid:c.close();return render_template("start.html")
 ex=c.execute("SELECT * FROM exams WHERE id=?",(eid,)).fetchone();qs=c.execute("SELECT * FROM questions WHERE class_id=? AND subject_id=? ORDER BY id",(st["class_id"],ex["subject_id"])).fetchall();aa=c.execute("SELECT question_id,answer FROM answers WHERE exam_id=? AND student_id=?",(eid,st["id"])).fetchall();c.close()
 return render_template("exam.html",exam=ex,questions=qs,answers={x["question_id"]:x["answer"] for x in aa})
@app.post("/api/answer")
def answer():
 if session.get("role")!="siswa":return jsonify(ok=False),401
 try:
  d=request.get_json(silent=True) or {};c=db();c.execute("INSERT INTO answers(exam_id,student_id,question_id,answer,updated_at) VALUES(?,?,?,?,?) ON CONFLICT(exam_id,student_id,question_id) DO UPDATE SET answer=excluded.answer,updated_at=excluded.updated_at",(int(d["exam_id"]),session["user_id"],int(d["question_id"]),d.get("answer",""),datetime.now().isoformat()));c.commit();c.close();return jsonify(ok=True)
 except Exception as e:return jsonify(ok=False,error=str(e)),500
@app.post("/api/submit")
def submit():
 if session.get("role")!="siswa":return jsonify(ok=False),401
 try:
  d=request.get_json(silent=True) or {};eid=int(d["exam_id"]);c=db();st=c.execute("SELECT * FROM students WHERE id=?",(session["user_id"],)).fetchone();ex=c.execute("SELECT * FROM exams WHERE id=?",(eid,)).fetchone();qs=c.execute("SELECT * FROM questions WHERE class_id=? AND subject_id=?",(st["class_id"],ex["subject_id"])).fetchall();aa={x["question_id"]:x["answer"] for x in c.execute("SELECT question_id,answer FROM answers WHERE exam_id=? AND student_id=?",(eid,st["id"])).fetchall()};score=round(sum(aa.get(q["id"],"").upper()==q["answer"].upper() for q in qs)*100/len(qs),2) if qs else 0;c.execute("INSERT OR REPLACE INTO results(exam_id,student_id,score,submitted_at) VALUES(?,?,?,?)",(eid,st["id"],score,datetime.now().isoformat()));c.commit();c.close();session.pop("exam_id",None);return jsonify(ok=True,score=score)
 except Exception as e:return jsonify(ok=False,error=str(e)),500
@app.get("/api/health")
def health():
 try:c=db();c.execute("select 1");c.close();return jsonify(ok=True,version="V21 Production")
 except Exception as e:return jsonify(ok=False,error=str(e)),500
if __name__=="__main__":app.run(host="0.0.0.0",port=int(os.environ.get("PORT",5000)))