app.py

from flask import Flask, render_template, request, redirect, url_for, flash
import sqlite3
from pathlib import Path

APP_DIR = Path(__file__).resolve().parent
DB_PATH = APP_DIR / "campus.db"
SCHEMA_PATH = APP_DIR / "schema.sql"

app = Flask(__name__)
app.secret_key = "campuspulse_secret_key"  # for flash messages


# ---------------- DB Helpers ----------------
def get_db():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON;")
    return conn


def init_db():
    conn = get_db()
    conn.executescript(SCHEMA_PATH.read_text(encoding="utf-8"))
    conn.close()


def time_overlap(start_a, end_a, start_b, end_b):
    # times as "HH:MM"
    return start_a < end_b and start_b < end_a


def session_conflicts(conn, day, start_time, end_time, classroom_id, faculty_id):
    """Return (room_conflict, faculty_conflict) boolean."""
    rows = conn.execute(
        """
        SELECT id, classroom_id, faculty_id, start_time, end_time
        FROM class_sessions
        WHERE day_of_week = ?
        """,
        (day,),
    ).fetchall()

    room_conflict = False
    faculty_conflict = False

    for r in rows:
        if time_overlap(start_time, end_time, r["start_time"], r["end_time"]):
            if int(r["classroom_id"]) == int(classroom_id):
                room_conflict = True
            if int(r["faculty_id"]) == int(faculty_id):
                faculty_conflict = True

    return room_conflict, faculty_conflict


# ---------------- Analytics Queries ----------------
def get_dashboard_stats(conn):
    stats = {}
    stats["blocks"] = conn.execute("SELECT COUNT(*) c FROM blocks").fetchone()["c"]
    stats["classrooms"] = conn.execute("SELECT COUNT(*) c FROM classrooms").fetchone()["c"]
    stats["courses"] = conn.execute("SELECT COUNT(*) c FROM courses").fetchone()["c"]
    stats["faculty"] = conn.execute("SELECT COUNT(*) c FROM faculty").fetchone()["c"]
    stats["students"] = conn.execute("SELECT COUNT(*) c FROM students").fetchone()["c"]
    stats["sessions"] = conn.execute("SELECT COUNT(*) c FROM class_sessions").fetchone()["c"]

    # warnings: overcapacity sessions
    stats["overcapacity"] = conn.execute(
        """
        SELECT COUNT(*) c
        FROM class_sessions s
        JOIN classrooms c ON c.id = s.classroom_id
        WHERE s.expected_students > c.capacity
        """
    ).fetchone()["c"]

    return stats


def classroom_utilization(conn):
    return conn.execute(
        """
        SELECT
          cr.id AS classroom_id,
          b.name AS block_name,
          cr.room_no,
          cr.capacity,
          COUNT(s.id) AS total_sessions,
          ROUND(COALESCE(AVG(
            CASE WHEN cr.capacity = 0 THEN 0
                 ELSE (1.0 * s.expected_students / cr.capacity) * 100
            END
          ),0), 2) AS avg_util_percent,
          COALESCE(SUM(s.expected_students),0) AS total_expected_students
        FROM classrooms cr
        JOIN blocks b ON b.id = cr.block_id
        LEFT JOIN class_sessions s ON s.classroom_id = cr.id
        GROUP BY cr.id
        ORDER BY avg_util_percent DESC
        """
    ).fetchall()


def block_utilization(conn):
    # ✅ FIXED COLUMN NAME + COALESCE
    return conn.execute(
        """
        SELECT
          b.id AS block_id,
          b.name AS block_name,
          COUNT(s.id) AS total_sessions_in_block,
          ROUND(COALESCE(AVG(
            CASE WHEN cr.capacity = 0 THEN 0
                 ELSE (1.0 * s.expected_students / cr.capacity) * 100
            END
          ),0), 2) AS avg_block_utilization_percent
        FROM blocks b
        JOIN classrooms cr ON cr.block_id = b.id
        LEFT JOIN class_sessions s ON s.classroom_id = cr.id
        GROUP BY b.id
        ORDER BY avg_block_utilization_percent DESC
        """
    ).fetchall()


def faculty_workload(conn):
    # hours computed from time difference (HH:MM) using sqlite strftime seconds
    return conn.execute(
        """
        SELECT
          f.id AS faculty_id,
          f.name,
          f.department,
          f.max_weekly_hours,
          ROUND(COALESCE(SUM(
            (strftime('%s', '2000-01-01 ' || s.end_time) - strftime('%s', '2000-01-01 ' || s.start_time)) / 3600.0
          ), 0), 2) AS total_hours,
          ROUND(
            CASE WHEN f.max_weekly_hours = 0 THEN 0
                 ELSE (COALESCE(SUM(
                   (strftime('%s', '2000-01-01 ' || s.end_time) - strftime('%s', '2000-01-01 ' || s.start_time)) / 3600.0
                 ),0) / f.max_weekly_hours) * 100
            END
          , 2) AS workload_percent
        FROM faculty f
        LEFT JOIN class_sessions s ON s.faculty_id = f.id
        GROUP BY f.id
        ORDER BY workload_percent DESC
        """
    ).fetchall()


# ---------------- Routes ----------------
@app.route("/")
def dashboard():
    conn = get_db()
    stats = get_dashboard_stats(conn)

    blocks = block_utilization(conn)
    faculty = faculty_workload(conn)

    # ---- SAFE CHART DATA ----
    block_labels, block_values = [], []
    for b in blocks:
        b = dict(b)  # convert sqlite3.Row -> dict
        block_labels.append(b.get("block_name", ""))
        block_values.append(float(b.get("avg_block_utilization_percent") or 0))

    fac_labels, fac_values = [], []
    for f in faculty:
        f = dict(f)
        fac_labels.append(f.get("name", ""))
        fac_values.append(float(f.get("workload_percent") or 0))

    conn.close()

    return render_template(
        "dashboard.html",
        stats=stats,
        blocks=blocks,
        faculty=faculty,
        block_labels=block_labels,
        block_values=block_values,
        fac_labels=fac_labels,
        fac_values=fac_values
    )


@app.route("/blocks", methods=["GET", "POST"])
def blocks():
    conn = get_db()
    if request.method == "POST":
        name = request.form.get("name", "").strip()
        if not name:
            flash("Block name required!", "danger")
        else:
            try:
                conn.execute("INSERT INTO blocks(name) VALUES(?)", (name,))
                conn.commit()
                flash("Block added!", "success")
            except sqlite3.IntegrityError:
                flash("Block already exists!", "warning")
        return redirect(url_for("blocks"))

    rows = conn.execute("SELECT * FROM blocks ORDER BY name").fetchall()
    conn.close()
    return render_template("blocks.html", blocks=rows)


@app.route("/blocks/delete/<int:block_id>")
def delete_block(block_id):
    conn = get_db()
    conn.execute("DELETE FROM blocks WHERE id=?", (block_id,))
    conn.commit()
    conn.close()
    flash("Block deleted!", "info")
    return redirect(url_for("blocks"))


@app.route("/classrooms", methods=["GET", "POST"])
def classrooms():
    conn = get_db()
    blocks_list = conn.execute("SELECT * FROM blocks ORDER BY name").fetchall()

    if request.method == "POST":
        block_id = request.form.get("block_id")
        room_no = request.form.get("room_no", "").strip()
        capacity = request.form.get("capacity", "").strip()
        has_projector = 1 if request.form.get("has_projector") else 0
        has_ac = 1 if request.form.get("has_ac") else 0

        if not block_id or not room_no or not capacity.isdigit():
            flash("Fill all fields correctly!", "danger")
        else:
            try:
                conn.execute(
                    """
                    INSERT INTO classrooms(block_id, room_no, capacity, has_projector, has_ac)
                    VALUES(?,?,?,?,?)
                    """,
                    (block_id, room_no, int(capacity), has_projector, has_ac),
                )
                conn.commit()
                flash("Classroom added!", "success")
            except sqlite3.IntegrityError:
                flash("This room already exists in the block!", "warning")
        return redirect(url_for("classrooms"))

    rooms = conn.execute(
        """
        SELECT cr.*, b.name AS block_name
        FROM classrooms cr JOIN blocks b ON b.id = cr.block_id
        ORDER BY b.name, cr.room_no
        """
    ).fetchall()

    conn.close()
    return render_template("classrooms.html", classrooms=rooms, blocks=blocks_list)


@app.route("/classrooms/delete/<int:room_id>")
def delete_classroom(room_id):
    conn = get_db()
    conn.execute("DELETE FROM classrooms WHERE id=?", (room_id,))
    conn.commit()
    conn.close()
    flash("Classroom deleted!", "info")
    return redirect(url_for("classrooms"))


@app.route("/courses", methods=["GET", "POST"])
def courses():
    conn = get_db()
    if request.method == "POST":
        code = request.form.get("code", "").strip().upper()
        title = request.form.get("title", "").strip()
        dept = request.form.get("department", "").strip()
        credits = request.form.get("credits", "").strip()

        if not code or not title or not credits.isdigit():
            flash("Fill course fields correctly!", "danger")
        else:
            try:
                conn.execute(
                    "INSERT INTO courses(code,title,department,credits) VALUES(?,?,?,?)",
                    (code, title, dept, int(credits)),
                )
                conn.commit()
                flash("Course added!", "success")
            except sqlite3.IntegrityError:
                flash("Course code already exists!", "warning")
        return redirect(url_for("courses"))

    rows = conn.execute("SELECT * FROM courses ORDER BY code").fetchall()
    conn.close()
    return render_template("courses.html", courses=rows)


@app.route("/courses/delete/<int:course_id>")
def delete_course(course_id):
    conn = get_db()
    conn.execute("DELETE FROM courses WHERE id=?", (course_id,))
    conn.commit()
    conn.close()
    flash("Course deleted!", "info")
    return redirect(url_for("courses"))


@app.route("/faculty", methods=["GET", "POST"])
def faculty():
    conn = get_db()
    if request.method == "POST":
        name = request.form.get("name", "").strip()
        dept = request.form.get("department", "").strip()
        max_hours = request.form.get("max_weekly_hours", "").strip()

        if not name or (max_hours and not max_hours.isdigit()):
            flash("Fill faculty fields correctly!", "danger")
        else:
            mh = int(max_hours) if max_hours.isdigit() else 18
            conn.execute(
                "INSERT INTO faculty(name,department,max_weekly_hours) VALUES(?,?,?)",
                (name, dept, mh),
            )
            conn.commit()
            flash("Faculty added!", "success")
        return redirect(url_for("faculty"))

    rows = conn.execute("SELECT * FROM faculty ORDER BY name").fetchall()
    conn.close()
    return render_template("faculty.html", faculty=rows)


@app.route("/faculty/delete/<int:faculty_id>")
def delete_faculty(faculty_id):
    conn = get_db()
    conn.execute("DELETE FROM faculty WHERE id=?", (faculty_id,))
    conn.commit()
    conn.close()
    flash("Faculty deleted!", "info")
    return redirect(url_for("faculty"))


@app.route("/students", methods=["GET", "POST"])
def students():
    conn = get_db()
    if request.method == "POST":
        name = request.form.get("name", "").strip()
        program = request.form.get("program", "").strip()
        year = request.form.get("year", "").strip()

        if not name or (year and not year.isdigit()):
            flash("Fill student fields correctly!", "danger")
        else:
            yr = int(year) if year.isdigit() else None
            conn.execute(
                "INSERT INTO students(name,program,year) VALUES(?,?,?)",
                (name, program, yr),
            )
            conn.commit()
            flash("Student added!", "success")
        return redirect(url_for("students"))

    rows = conn.execute("SELECT * FROM students ORDER BY name").fetchall()
    conn.close()
    return render_template("students.html", students=rows)


@app.route("/students/delete/<int:student_id>")
def delete_student(student_id):
    conn = get_db()
    conn.execute("DELETE FROM students WHERE id=?", (student_id,))
    conn.commit()
    conn.close()
    flash("Student deleted!", "info")
    return redirect(url_for("students"))


@app.route("/sessions", methods=["GET", "POST"])
def sessions():
    conn = get_db()
    courses_list = conn.execute("SELECT * FROM courses ORDER BY code").fetchall()
    faculty_list = conn.execute("SELECT * FROM faculty ORDER BY name").fetchall()
    rooms_list = conn.execute(
        """
        SELECT cr.id, b.name || '-' || cr.room_no AS room_label, cr.capacity
        FROM classrooms cr JOIN blocks b ON b.id = cr.block_id
        ORDER BY b.name, cr.room_no
        """
    ).fetchall()

    if request.method == "POST":
        course_id = request.form.get("course_id")
        faculty_id = request.form.get("faculty_id")
        classroom_id = request.form.get("classroom_id")
        day = request.form.get("day_of_week")
        start_time = request.form.get("start_time")
        end_time = request.form.get("end_time")
        expected = request.form.get("expected_students", "0").strip()

        if not all([course_id, faculty_id, classroom_id, day, start_time, end_time]) or not expected.isdigit():
            flash("Fill session fields correctly!", "danger")
            return redirect(url_for("sessions"))

        if start_time >= end_time:
            flash("End time must be after start time!", "danger")
            return redirect(url_for("sessions"))

        room_conf, fac_conf = session_conflicts(conn, day, start_time, end_time, classroom_id, faculty_id)
        if room_conf:
            flash("Time clash: This classroom already has a session in that time!", "danger")
            return redirect(url_for("sessions"))
        if fac_conf:
            flash("Time clash: This faculty already has a session in that time!", "danger")
            return redirect(url_for("sessions"))

        conn.execute(
            """
            INSERT INTO class_sessions(course_id, faculty_id, classroom_id, day_of_week, start_time, end_time, expected_students)
            VALUES(?,?,?,?,?,?,?)
            """,
            (course_id, faculty_id, classroom_id, day, start_time, end_time, int(expected)),
        )
        conn.commit()

        # Overcapacity warning
        cap = conn.execute("SELECT capacity FROM classrooms WHERE id=?", (classroom_id,)).fetchone()["capacity"]
        if int(expected) > int(cap):
            flash("Session added, but WARNING: expected students exceed capacity!", "warning")
        else:
            flash("Session added!", "success")

        return redirect(url_for("sessions"))

    rows = conn.execute(
        """
        SELECT
          s.*,
          c.code AS course_code,
          c.title AS course_title,
          f.name AS faculty_name,
          b.name AS block_name,
          cr.room_no,
          cr.capacity
        FROM class_sessions s
        JOIN courses c ON c.id = s.course_id
        JOIN faculty f ON f.id = s.faculty_id
        JOIN classrooms cr ON cr.id = s.classroom_id
        JOIN blocks b ON b.id = cr.block_id
        ORDER BY s.day_of_week, s.start_time
        """
    ).fetchall()

    conn.close()
    return render_template("sessions.html",
                           sessions=rows,
                           courses=courses_list,
                           faculty=faculty_list,
                           rooms=rooms_list)


@app.route("/sessions/delete/<int:session_id>")
def delete_session(session_id):
    conn = get_db()
    conn.execute("DELETE FROM class_sessions WHERE id=?", (session_id,))
    conn.commit()
    conn.close()
    flash("Session deleted!", "info")
    return redirect(url_for("sessions"))


@app.route("/analytics")
def analytics():
    conn = get_db()
    class_util = classroom_utilization(conn)
    block_util = block_utilization(conn)
    fac_work = faculty_workload(conn)
    conn.close()
    return render_template("analytics.html",
                           classroom_util=class_util,
                           block_util=block_util,
                           faculty_workload=fac_work)


if __name__ == "__main__":
    init_db()
    app.run(debug=True)