database.py

"""
database.py
------------
This file handles everything related to our SQLite database.
SQLite is just a single file on disk (helpdesk.db) - no server needed.

It does 3 jobs:
1. Create the 'faqs' table (stores question + answer pairs)
2. Create the 'logs' table (stores every interaction for evidence/history)
3. Fill the faqs table with sample data (only runs once)
"""

import sqlite3
from pathlib import Path

# Keep the database beside this source file, regardless of the folder from
# which the application is launched.
DB_NAME = Path(__file__).resolve().with_name("helpdesk.db")


def get_connection():
    """
    Opens a connection to our database file.
    If the file doesn't exist yet, SQLite creates it automatically.
    """
    return sqlite3.connect(DB_NAME)


def create_tables():
    """
    Creates the two tables we need, only if they don't already exist.
    """
    conn = get_connection()
    cursor = conn.cursor()

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS faqs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            question TEXT NOT NULL,
            answer TEXT NOT NULL,
            category TEXT
        )
    """)

    cursor.execute("""
        CREATE TABLE IF NOT EXISTS logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            question TEXT NOT NULL,
            answer TEXT,
            source TEXT,
            status TEXT,
            timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
        )
    """)

    conn.commit()
    conn.close()


def seed_faqs():
    """
    Fills the faqs table with sample university helpdesk questions.
    Only inserts data if the table is currently empty.
    """
    conn = get_connection()
    cursor = conn.cursor()

    cursor.execute("SELECT COUNT(*) FROM faqs")
    count = cursor.fetchone()[0]

    if count > 0:
        print(f"faqs table already has {count} rows. Skipping seed.")
        conn.close()
        return

    sample_faqs = [
        ("What are the university's working hours?", "The university office is open Monday to Friday, 8:30 AM to 4:00 PM.", "general"),
        ("When is the last date to pay semester fees?", "Semester fees are due within the first two weeks of each semester. Check the fee voucher on the student portal for your exact date.", "fees"),
        ("How do I reset my student portal password?", "Go to the student portal login page, click 'Forgot Password', and follow the instructions sent to your registered email.", "portal"),
        ("Where is the library located?", "The Central Library is located near the Main Academic Block, Gate 2.", "campus"),
        ("What are the library timings?", "The library is open from 8:00 AM to 10:00 PM on weekdays and 9:00 AM to 5:00 PM on weekends.", "campus"),
        ("How can I apply for a transcript?", "Submit a transcript request form at the Registrar's Office along with the applicable fee. Processing takes 5-7 working days.", "academic"),
        ("What is the minimum attendance requirement?", "Students must maintain at least 75% attendance in each course to be eligible for final exams.", "academic"),
        ("How do I get a hostel room?", "Hostel applications open at the start of each academic year on the student portal under 'Hostel Services'.", "hostel"),
        ("Is transport service available for students?", "Yes, the university provides bus transport on fixed routes. Route details are available at the Transport Office.", "transport"),
        ("How do I contact my academic advisor?", "Your academic advisor's name and email are listed on your student portal dashboard under 'My Advisor'.", "academic"),
        ("What documents are needed for admission?", "You need your matric/intermediate certificates, CNIC/B-form copy, recent photographs, and the completed admission form.", "admissions"),
        ("How do I check my exam result?", "Exam results are published on the student portal under the 'Results' tab within 2-3 weeks of the exam.", "academic"),
        ("Can I change my major after enrollment?", "Yes, major changes are allowed within the first semester, subject to department approval. Contact the Registrar's Office.", "academic"),
        ("How do I apply for a scholarship?", "Scholarship applications are submitted through the Financial Aid Office each semester. Check announcements on the portal.", "fees"),
        ("What should I do if I lose my student ID card?", "Report the loss to Campus Security and apply for a duplicate card at the Registrar's Office with a small reissue fee.", "general"),
        ("How do I book a seat in the computer lab?", "Computer lab seats can be booked through the department office or the online lab booking system, if available.", "campus"),
        ("Who do I contact for internet/Wi-Fi issues?", "Wi-Fi issues should be reported to the IT Helpdesk, located in the Admin Block, or via the IT support email.", "portal"),
        ("What is the process for course withdrawal?", "Submit a withdrawal request through the student portal before the withdrawal deadline shown in the academic calendar.", "academic"),
        ("Are there any student clubs or societies?", "Yes, the university has several societies including AI & ML Society, IEEE Student Branch, and Debating Society. Check the Student Affairs page.", "campus"),
        ("How do I get an official bonafide certificate?", "Bonafide certificates can be requested from the Registrar's Office; processing usually takes 2-3 working days.", "academic"),
        ("What is the fee refund policy?", "Fee refunds are processed only if withdrawal is submitted within the first 10 days of the semester, per university policy.", "fees"),
        ("How can I report harassment or a complaint?", "Complaints can be submitted confidentially through the Anti-Harassment Committee office or via the official complaint email.", "general"),
        ("Is there a shuttle between hostels and campus?", "Yes, a free shuttle service runs between hostels and the main campus every 20-30 minutes during class hours.", "transport"),
        ("How do I get a duplicate degree certificate?", "Apply at the Registrar's Office with a police report of the lost original and the reissue fee; processing takes 4-6 weeks.", "academic"),
        ("What are the office hours of the Registrar?", "The Registrar's Office is open Monday to Friday, 9:00 AM to 3:00 PM, excluding public holidays.", "general"),
    ]

    cursor.executemany(
        "INSERT INTO faqs (question, answer, category) VALUES (?, ?, ?)",
        sample_faqs
    )

    conn.commit()
    conn.close()
    print(f"Inserted {len(sample_faqs)} sample FAQ rows.")


def log_interaction(question, answer, source, status):
    """
    Saves one interaction into the logs table.
    """
    conn = get_connection()
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO logs (question, answer, source, status) VALUES (?, ?, ?, ?)",
        (question, answer, source, status)
    )
    conn.commit()
    conn.close()


if __name__ == "__main__":
    create_tables()
    seed_faqs()
    print("Database setup complete.")