database.create_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@file create_registration_db.py
 12@brief Initializes the SQLite registration database from registration_sections.json.
 13
 14@details
 15This module creates the registration.db SQLite database and populates it with
 16course section data from registration_sections.json. It is intended to be run
 17once to initialize the database. For subsequent refreshes (upsert on conflict),
 18use refresh_registration.py instead.
 19
 20The database schema includes a UNIQUE constraint on course_code, so duplicate
 21entries are silently ignored (INSERT OR IGNORE). Four indexes are created for
 22fast querying by department, course code, status, and instructor.
 23
 24Database schema:
 25@code
 26CREATE TABLE courses (
 27    id            INTEGER PRIMARY KEY AUTOINCREMENT,
 28    course_code   TEXT NOT NULL UNIQUE,
 29    department    TEXT,
 30    course_number TEXT,
 31    section       TEXT,
 32    name          TEXT,
 33    status        TEXT,
 34    seats_open    INTEGER,
 35    seats_total   INTEGER,
 36    credits       TEXT,
 37    instructor    TEXT,
 38    days_time     TEXT,
 39    location      TEXT,
 40    method        TEXT,
 41    begin_date    TEXT,
 42    end_date      TEXT,
 43    scraped_at    TEXT
 44);
 45@endcode
 46
 47@author Data Pipeline — CSC 212 AI Academic Advising Platform
 48@date 2026
 49
 50@par Input
 51    registration_sections.json — scraped course section data
 52
 53@par Output
 54    registration.db — initialized SQLite database
 55
 56@par Dependencies
 57    - sqlite3 (stdlib)
 58    - json (stdlib)
 59    - os (stdlib)
 60
 61@par Usage
 62    python create_registration_db.py
 63
 64@note Run scrape_registration_sections.py or refresh_registration.py first
 65      to generate registration_sections.json before running this script.
 66"""
 67
 68import json
 69import sqlite3
 70import os
 71
 72## @brief Path to the input registration sections JSON file.
 73JSON_FILE = "registration_sections.json"
 74
 75## @brief Path to the output SQLite database file.
 76DB_FILE   = "registration.db"
 77
 78
 79def create_db(json_file: str, db_file: str):
 80    """
 81    @brief Creates the SQLite database and populates it from the JSON file.
 82
 83    @details
 84    Reads all course sections from the JSON file, connects to (or creates)
 85    the SQLite database at db_file, creates the courses table and indexes
 86    if they do not exist, and inserts all course records using INSERT OR IGNORE
 87    to skip duplicates based on the unique course_code constraint.
 88
 89    Indexes created:
 90    - idx_department  on courses(department)
 91    - idx_course_code on courses(course_code)
 92    - idx_status      on courses(status)
 93    - idx_instructor  on courses(instructor)
 94
 95    @param json_file Path to the registration_sections.json input file.
 96    @param db_file   Path to the SQLite database file to create or update.
 97
 98    @par Side Effects
 99        Creates or modifies the SQLite database file at db_file.
100        Prints a summary of inserted and skipped records on completion.
101
102    @throws FileNotFoundError if json_file does not exist (handled by caller).
103    @throws sqlite3.Error for individual record insert failures (logged, not raised).
104    """
105    # Load JSON
106    with open(json_file, "r", encoding="utf-8") as f:
107        courses = json.load(f)
108
109    print(f"Loaded {len(courses)} course sections from {json_file}")
110
111    # Connect to SQLite (creates file if it doesn't exist)
112    conn = sqlite3.connect(db_file)
113    cursor = conn.cursor()
114
115    # Create table and indexes
116    cursor.executescript("""
117        CREATE TABLE IF NOT EXISTS courses (
118            id              INTEGER PRIMARY KEY AUTOINCREMENT,
119            course_code     TEXT NOT NULL,
120            department      TEXT,
121            course_number   TEXT,
122            section         TEXT,
123            name            TEXT,
124            status          TEXT,
125            seats_open      INTEGER,
126            seats_total     INTEGER,
127            credits         TEXT,
128            instructor      TEXT,
129            days_time       TEXT,
130            location        TEXT,
131            method          TEXT,
132            begin_date      TEXT,
133            end_date        TEXT,
134            scraped_at      TEXT,
135            UNIQUE(course_code)
136        );
137
138        CREATE INDEX IF NOT EXISTS idx_department   ON courses(department);
139        CREATE INDEX IF NOT EXISTS idx_course_code  ON courses(course_code);
140        CREATE INDEX IF NOT EXISTS idx_status       ON courses(status);
141        CREATE INDEX IF NOT EXISTS idx_instructor   ON courses(instructor);
142    """)
143
144    # Insert courses
145    inserted = 0
146    skipped  = 0
147
148    for course in courses:
149        try:
150            cursor.execute("""
151                INSERT OR IGNORE INTO courses (
152                    course_code, department, course_number, section,
153                    name, status, seats_open, seats_total, credits,
154                    instructor, days_time, location, method,
155                    begin_date, end_date, scraped_at
156                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
157            """, (
158                course.get("course_code"),
159                course.get("department"),
160                course.get("course_number"),
161                course.get("section"),
162                course.get("name"),
163                course.get("status"),
164                course.get("seats_open"),
165                course.get("seats_total"),
166                course.get("credits"),
167                course.get("instructor"),
168                course.get("days_time"),
169                course.get("location"),
170                course.get("method"),
171                course.get("begin_date"),
172                course.get("end_date"),
173                course.get("scraped_at"),
174            ))
175            if cursor.rowcount > 0:
176                inserted += 1
177            else:
178                skipped += 1
179        except sqlite3.Error as e:
180            print(f"  Error inserting {course.get('course_code')}: {e}")
181
182    conn.commit()
183    conn.close()
184
185    print(f"✅ Database created: {db_file}")
186    print(f"   Inserted: {inserted} courses")
187    print(f"   Skipped:  {skipped} duplicates")
188
189
190if __name__ == "__main__":
191    if not os.path.exists(JSON_FILE):
192        print(f"❌ {JSON_FILE} not found. Run scrape_registration_sections.py first.")
193    else:
194        create_db(JSON_FILE, DB_FILE)