database.view_registration_db

  1# =============================================================================
  2#
  3# Copyright 2026 
  4#
  5# Author:   Noel Mensah
  6# GitHub:   https://github.com/LSilver17/CSC212---AI-Agent
  7# 
  8# =============================================================================
  9
 10
 11"""
 12@file view_registration_db.py
 13@brief Interactive utility for querying and inspecting the registration database.
 14
 15@details
 16This module provides an interactive command-line menu for exploring the
 17registration.db SQLite database. It is primarily used for testing and
 18verifying scraped data during development, and for ad-hoc querying of
 19course offerings without writing SQL directly.
 20
 21Available operations:
 22    - Database summary (total sections, open/closed counts, department count)
 23    - List all departments with section counts
 24    - Search by department code
 25    - Show open/reopened courses (all departments or filtered by department)
 26    - Search by instructor last name
 27    - Search by delivery method (Lecture, Online, etc.)
 28    - Search by meeting days (MW, TR, MWF, etc.)
 29    - Export a department's courses to a JSON file
 30
 31@author Data Pipeline — CSC 212 AI Academic Advising Platform
 32@date 2026
 33
 34@par Input
 35    registration.db — SQLite database populated by create_registration_db.py
 36                      or refresh_registration.py
 37
 38@par Dependencies
 39    - sqlite3 (stdlib)
 40    - json (stdlib)
 41
 42@par Usage
 43    python view_registration_db.py
 44
 45@note Run create_registration_db.py or refresh_registration.py first
 46      to populate registration.db before using this viewer.
 47"""
 48
 49import sqlite3
 50import json
 51
 52## @brief Path to the SQLite registration database file.
 53DB_FILE = "registration.db"
 54
 55
 56def get_connection():
 57    """
 58    @brief Opens and returns a connection to the registration database.
 59
 60    @details
 61    Sets row_factory to sqlite3.Row so that query results can be accessed
 62    by column name (e.g. row['course_code']) in addition to by index.
 63
 64    @return A sqlite3.Connection object connected to DB_FILE.
 65    """
 66    conn = sqlite3.connect(DB_FILE)
 67    conn.row_factory = sqlite3.Row
 68    return conn
 69
 70
 71def print_courses(rows, limit=10):
 72    """
 73    @brief Prints a formatted table of course rows to stdout.
 74
 75    @details
 76    Displays each course as a single line with fixed-width columns showing
 77    course code, name, status, seat availability, and instructor.
 78    Output is truncated to the first `limit` rows.
 79
 80    @param rows   An iterable of sqlite3.Row objects from a courses query.
 81    @param limit  Maximum number of rows to display (default 10).
 82
 83    @par Example Output
 84    @code
 85    ACC 101-01     Financial Accounting I                   Reopened   1/24 seats  De Silva, Damindi
 86    @endcode
 87    """
 88    for row in list(rows)[:limit]:
 89        print(f"  {row['course_code']:<14} {row['name']:<40} {row['status']:<10} {row['seats_open']}/{row['seats_total']} seats  {row['instructor']}")
 90
 91
 92# ── Queries ───────────────────────────────────────────────
 93
 94def summary():
 95    """
 96    @brief Prints a summary of the database contents.
 97
 98    @details
 99    Queries the courses table for total section count, counts by status
100    (Open, Reopened, Closed), and the number of distinct departments.
101    Prints results to stdout.
102    """
103    conn = get_connection()
104    total    = conn.execute("SELECT COUNT(*) FROM courses").fetchone()[0]
105    open_    = conn.execute("SELECT COUNT(*) FROM courses WHERE status = 'Open'").fetchone()[0]
106    reopened = conn.execute("SELECT COUNT(*) FROM courses WHERE status = 'Reopened'").fetchone()[0]
107    closed   = conn.execute("SELECT COUNT(*) FROM courses WHERE status = 'Closed'").fetchone()[0]
108    depts    = conn.execute("SELECT COUNT(DISTINCT department) FROM courses").fetchone()[0]
109    conn.close()
110
111    print("\n── Database Summary ──────────────────────────────")
112    print(f"  Total sections : {total}")
113    print(f"  Open           : {open_}")
114    print(f"  Reopened       : {reopened}")
115    print(f"  Closed         : {closed}")
116    print(f"  Departments    : {depts}")
117
118
119def list_departments():
120    """
121    @brief Lists all departments and their section counts in alphabetical order.
122
123    @details
124    Groups the courses table by department and counts sections per department.
125    Results are sorted alphabetically by department code and printed to stdout.
126    """
127    conn = get_connection()
128    rows = conn.execute("""
129        SELECT department, COUNT(*) as count
130        FROM courses
131        GROUP BY department
132        ORDER BY department
133    """).fetchall()
134    conn.close()
135
136    print("\n── Departments ───────────────────────────────────")
137    for row in rows:
138        print(f"  {row['department']:<10} {row['count']} sections")
139
140
141def search_by_department(dept: str):
142    """
143    @brief Searches and displays all course sections for a given department.
144
145    @details
146    Queries the courses table for all sections where department matches
147    the given code (case-insensitive, converted to uppercase). Results
148    are ordered by course number and section, and all matching rows are shown.
149
150    @param dept The department code to search for (e.g. "CSC", "ENG", "MAT").
151    """
152    conn = get_connection()
153    rows = conn.execute("""
154        SELECT * FROM courses
155        WHERE department = ?
156        ORDER BY course_number, section
157    """, (dept.upper(),)).fetchall()
158    conn.close()
159
160    print(f"\n── {dept.upper()} Courses ({len(rows)} sections) ──────────────────")
161    print_courses(rows, limit=len(rows))
162
163
164def search_open_courses(dept: str = None):
165    """
166    @brief Displays all open or reopened course sections, optionally filtered by department.
167
168    @details
169    Queries for courses with status "Open" or "Reopened". If a department
170    code is provided, results are filtered to that department only.
171    Displays up to 20 results.
172
173    @param dept Optional department code to filter by (e.g. "CSC").
174                If None, shows open courses across all departments.
175    """
176    conn = get_connection()
177    if dept:
178        rows = conn.execute("""
179            SELECT * FROM courses
180            WHERE status IN ('Open', 'Reopened') AND department = ?
181            ORDER BY department, course_number
182        """, (dept.upper(),)).fetchall()
183    else:
184        rows = conn.execute("""
185            SELECT * FROM courses
186            WHERE status IN ('Open', 'Reopened')
187            ORDER BY department, course_number
188        """).fetchall()
189    conn.close()
190
191    label = f"{dept.upper()} " if dept else ""
192    print(f"\n── Open {label}Courses ({len(rows)} sections) ─────────────────")
193    print_courses(rows, limit=20)
194
195
196def search_by_instructor(name: str):
197    """
198    @brief Searches and displays all sections taught by a given instructor.
199
200    @details
201    Performs a case-insensitive partial match on the instructor field
202    using SQL LIKE. Useful for finding all sections taught by a particular
203    person when only their last name is known.
204
205    @param name The instructor name or partial name to search for.
206    """
207    conn = get_connection()
208    rows = conn.execute("""
209        SELECT * FROM courses
210        WHERE instructor LIKE ?
211        ORDER BY course_code
212    """, (f"%{name}%",)).fetchall()
213    conn.close()
214
215    print(f"\n── Courses by '{name}' ({len(rows)} sections) ────────────────")
216    print_courses(rows, limit=len(rows))
217
218
219def search_by_method(method: str):
220    """
221    @brief Searches and displays all sections with a given delivery method.
222
223    @details
224    Performs a partial match on the method field. Common values include:
225    "Lecture", "Online Course", "Remote Learning", "In Person Blended", "Lab".
226    Displays up to 20 results.
227
228    @param method The delivery method string or partial string to search for.
229    """
230    conn = get_connection()
231    rows = conn.execute("""
232        SELECT * FROM courses
233        WHERE method LIKE ?
234        ORDER BY department, course_number
235    """, (f"%{method}%",)).fetchall()
236    conn.close()
237
238    print(f"\n── '{method}' Courses ({len(rows)} sections) ──────────────────")
239    print_courses(rows, limit=20)
240
241
242def search_by_days(days: str):
243    """
244    @brief Searches and displays all sections meeting on given days.
245
246    @details
247    Performs a prefix match on the days_time field (e.g. "MW" matches
248    "MW 08:00-09:15AM"). Common day patterns: MW, TR, MWF, F, S.
249    Displays up to 20 results ordered by days_time and course code.
250
251    @param days The day pattern to search for (e.g. "MW", "TR", "F").
252    """
253    conn = get_connection()
254    rows = conn.execute("""
255        SELECT * FROM courses
256        WHERE days_time LIKE ?
257        ORDER BY days_time, course_code
258    """, (f"{days}%",)).fetchall()
259    conn.close()
260
261    print(f"\n── Courses on '{days}' ({len(rows)} sections) ────────────────")
262    print_courses(rows, limit=20)
263
264
265def export_department_json(dept: str):
266    """
267    @brief Exports all courses for a department to a JSON file.
268
269    @details
270    Queries all sections for the given department, converts each row to a
271    dictionary, and writes the result to a JSON file named "{DEPT}_courses.json"
272    in the current directory.
273
274    @param dept The department code to export (e.g. "CSC").
275
276    @par Side Effects
277        Creates a file named "{dept.upper()}_courses.json" in the current directory.
278    """
279    conn = get_connection()
280    rows = conn.execute("""
281        SELECT * FROM courses WHERE department = ?
282        ORDER BY course_number, section
283    """, (dept.upper(),)).fetchall()
284    conn.close()
285
286    data = [dict(row) for row in rows]
287    filename = f"{dept.upper()}_courses.json"
288    with open(filename, "w", encoding="utf-8") as f:
289        json.dump(data, f, indent=2)
290    print(f"\n✅ Exported {len(data)} {dept.upper()} courses to {filename}")
291
292
293# ── Main Menu ─────────────────────────────────────────────
294
295def main():
296    """
297    @brief Entry point — displays the interactive query menu and handles user input.
298
299    @details
300    Presents a numbered menu of query options in a loop until the user
301    selects option 0 to exit. Each option prompts for any required input
302    and calls the corresponding query function.
303
304    Menu options:
305        1. Database summary
306        2. List all departments
307        3. Search by department
308        4. Show open courses (all or by dept)
309        5. Search by instructor
310        6. Search by delivery method
311        7. Search by days (e.g. MW, TR, F)
312        8. Export department to JSON
313        0. Exit
314    """
315    print("\n╔══════════════════════════════════════╗")
316    print("║   QCC Registration DB Viewer         ║")
317    print("╚══════════════════════════════════════╝")
318
319    while True:
320        print("\n── Options ───────────────────────────────────────")
321        print("  1. Database summary")
322        print("  2. List all departments")
323        print("  3. Search by department")
324        print("  4. Show open courses (all or by dept)")
325        print("  5. Search by instructor")
326        print("  6. Search by delivery method")
327        print("  7. Search by days (e.g. MW, TR, F)")
328        print("  8. Export department to JSON")
329        print("  0. Exit")
330
331        choice = input("\nChoose an option: ").strip()
332
333        if choice == "1":
334            summary()
335        elif choice == "2":
336            list_departments()
337        elif choice == "3":
338            dept = input("Enter department code (e.g. CSC, ENG, MAT): ").strip()
339            search_by_department(dept)
340        elif choice == "4":
341            dept = input("Enter department code (or press Enter for all): ").strip()
342            search_open_courses(dept if dept else None)
343        elif choice == "5":
344            name = input("Enter instructor last name: ").strip()
345            search_by_instructor(name)
346        elif choice == "6":
347            print("  Methods: Lecture, Online Course, Remote Learning, In Person Blended, Lab...")
348            method = input("Enter method: ").strip()
349            search_by_method(method)
350        elif choice == "7":
351            print("  Examples: MW, TR, MWF, F, S")
352            days = input("Enter days: ").strip()
353            search_by_days(days)
354        elif choice == "8":
355            dept = input("Enter department code to export: ").strip()
356            export_department_json(dept)
357        elif choice == "0":
358            print("\nGoodbye!")
359            break
360        else:
361            print("  Invalid option, try again.")
362
363
364if __name__ == "__main__":
365    main()