database.py

# database.py
import sqlite3
import bcrypt # For password hashing

DATABASE_NAME = "timetable.db"

def get_db_connection():
    conn = sqlite3.connect(DATABASE_NAME)
    conn.row_factory = sqlite3.Row  # This allows accessing columns by name
    return conn

def init_db():
    conn = get_db_connection()
    cursor = conn.cursor()

    # Create tables
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS departments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT UNIQUE NOT NULL
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS semesters (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            semester_number INTEGER UNIQUE NOT NULL
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS faculty (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            emp_id TEXT UNIQUE NOT NULL,
            department_id INTEGER,
            FOREIGN KEY (department_id) REFERENCES departments (id)
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS courses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            code TEXT UNIQUE NOT NULL,
            name TEXT NOT NULL,
            hours_per_week INTEGER NOT NULL,
            type TEXT NOT NULL CHECK (type IN ('theory', 'lab'))
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS theory_mappings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            semester_id INTEGER NOT NULL,
            course_id INTEGER NOT NULL,
            faculty_id INTEGER NOT NULL,
            UNIQUE (semester_id, course_id), -- A course in a semester is taught by one faculty
            FOREIGN KEY (semester_id) REFERENCES semesters (id),
            FOREIGN KEY (course_id) REFERENCES courses (id),
            FOREIGN KEY (faculty_id) REFERENCES faculty (id)
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS lab_mappings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            semester_id INTEGER NOT NULL,
            lab_course_id INTEGER NOT NULL,
            faculty_id_1 INTEGER NOT NULL,
            faculty_id_2 INTEGER NOT NULL,
            -- A lab course in a semester is taught by two specific faculty
            UNIQUE (semester_id, lab_course_id),
            FOREIGN KEY (semester_id) REFERENCES semesters (id),
            FOREIGN KEY (lab_course_id) REFERENCES courses (id),
            FOREIGN KEY (faculty_id_1) REFERENCES faculty (id),
            FOREIGN KEY (faculty_id_2) REFERENCES faculty (id)
        )
    ''')
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS faculty_preferences (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            faculty_id INTEGER NOT NULL,
            day TEXT NOT NULL, -- e.g., 'Monday', 'Tuesday'
            period_start INTEGER NOT NULL, -- 1 to 6
            period_end INTEGER NOT NULL,   -- 1 to 6
            preference_type TEXT NOT NULL CHECK (preference_type IN ('blocked', 'preferred')), -- 'blocked' or 'preferred'
            UNIQUE (faculty_id, day, period_start, period_end), -- Prevent duplicate preferences
            FOREIGN KEY (faculty_id) REFERENCES faculty (id)
        )
    ''')

    conn.commit()
    conn.close()

# --- User Management Functions ---
def hash_password(password):
    return bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt()).decode('utf-8')

def check_password(password, hashed_password):
    return bcrypt.checkpw(password.encode('utf-8'), hashed_password.encode('utf-8'))

def add_user(username, password):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        hashed_pass = hash_password(password)
        cursor.execute("INSERT INTO users (username, password_hash) VALUES (?, ?)", (username, hashed_pass))
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False # Username already exists
    finally:
        conn.close()

def get_user(username):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users WHERE username = ?", (username,))
    user = cursor.fetchone()
    conn.close()
    return user

# --- CRUD Functions for Departments ---
def add_department(name):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("INSERT INTO departments (name) VALUES (?)", (name,))
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False # Department with this name already exists
    finally:
        conn.close()

def get_departments():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM departments")
    departments = cursor.fetchall()
    conn.close()
    return departments

def update_department(dept_id, new_name):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("UPDATE departments SET name = ? WHERE id = ?", (new_name, dept_id))
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False
    finally:
        conn.close()

def delete_department(dept_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM departments WHERE id = ?", (dept_id,))
        conn.commit()
        return True
    except sqlite3.Error as e:
        print(f"Error deleting department: {e}") # Debugging
        return False
    finally:
        conn.close()

# --- CRUD Functions for Semesters ---
def add_semester(sem_num):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("INSERT INTO semesters (semester_number) VALUES (?)", (sem_num,))
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False # Semester already exists
    finally:
        conn.close()

def get_semesters():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM semesters ORDER BY semester_number")
    semesters = cursor.fetchall()
    conn.close()
    return semesters

def delete_semester(sem_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM semesters WHERE id = ?", (sem_id,))
        conn.commit()
        return True
    except sqlite3.Error:
        return False
    finally:
        conn.close()

# --- CRUD Functions for Faculty ---
def add_faculty(name, emp_id, department_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("INSERT INTO faculty (name, emp_id, department_id) VALUES (?, ?, ?)", (name, emp_id, department_id))
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False # Employee ID must be unique
    finally:
        conn.close()

def get_faculty():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
        SELECT f.id, f.name, f.emp_id, d.name as department_name
        FROM faculty f
        JOIN departments d ON f.department_id = d.id
    """)
    faculty = cursor.fetchall()
    conn.close()
    return faculty

def update_faculty(faculty_id, new_name, new_emp_id, new_department_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("UPDATE faculty SET name = ?, emp_id = ?, department_id = ? WHERE id = ?",
                       (new_name, new_emp_id, new_department_id, faculty_id))
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False # Employee ID must be unique
    finally:
        conn.close()

def delete_faculty(faculty_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM faculty WHERE id = ?", (faculty_id,))
        conn.commit()
        return True
    except sqlite3.Error:
        return False
    finally:
        conn.close()

# --- CRUD Functions for Courses ---
def add_course(code, name, hours_per_week, course_type):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("INSERT INTO courses (code, name, hours_per_week, type) VALUES (?, ?, ?, ?)",
                       (code, name, hours_per_week, course_type))
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False # Course code must be unique
    finally:
        conn.close()

def get_courses():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM courses")
    courses = cursor.fetchall()
    conn.close()
    return courses

def get_theory_courses():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM courses WHERE type = 'theory'")
    courses = cursor.fetchall()
    conn.close()
    return courses

def get_lab_courses():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM courses WHERE type = 'lab'")
    courses = cursor.fetchall()
    conn.close()
    return courses

def update_course(course_id, new_code, new_name, new_hours_per_week, new_type):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("UPDATE courses SET code = ?, name = ?, hours_per_week = ?, type = ? WHERE id = ?",
                       (new_code, new_name, new_hours_per_week, new_type, course_id))
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False # Course code must be unique
    finally:
        conn.close()

def delete_course(course_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM courses WHERE id = ?", (course_id,))
        conn.commit()
        return True
    except sqlite3.Error:
        return False
    finally:
        conn.close()

# --- Mapping Functions ---
def add_theory_mapping(semester_id, course_id, faculty_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("INSERT INTO theory_mappings (semester_id, course_id, faculty_id) VALUES (?, ?, ?)",
                       (semester_id, course_id, faculty_id))
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False # (semester_id, course_id) must be unique
    finally:
        conn.close()

def get_theory_mappings():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
        SELECT tm.id, s.semester_number, c.name AS course_name, c.code, f.name AS faculty_name
        FROM theory_mappings tm
        JOIN semesters s ON tm.semester_id = s.id
        JOIN courses c ON tm.course_id = c.id
        JOIN faculty f ON tm.faculty_id = f.id
    """)
    mappings = cursor.fetchall()
    conn.close()
    return mappings

def delete_theory_mapping(mapping_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM theory_mappings WHERE id = ?", (mapping_id,))
        conn.commit()
        return True
    except sqlite3.Error:
        return False
    finally:
        conn.close()

def add_lab_mapping(semester_id, lab_course_id, faculty_id_1, faculty_id_2):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("INSERT INTO lab_mappings (semester_id, lab_course_id, faculty_id_1, faculty_id_2) VALUES (?, ?, ?, ?)",
                       (semester_id, lab_course_id, faculty_id_1, faculty_id_2))
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False # (semester_id, lab_course_id) must be unique
    finally:
        conn.close()

def get_lab_mappings():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
        SELECT lm.id, s.semester_number, c.name AS lab_course_name, c.code, f1.name AS faculty_1_name, f2.name AS faculty_2_name
        FROM lab_mappings lm
        JOIN semesters s ON lm.semester_id = s.id
        JOIN courses c ON lm.lab_course_id = c.id
        JOIN faculty f1 ON lm.faculty_id_1 = f1.id
        JOIN faculty f2 ON lm.faculty_id_2 = f2.id
    """)
    mappings = cursor.fetchall()
    conn.close()
    return mappings

def delete_lab_mapping(mapping_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM lab_mappings WHERE id = ?", (mapping_id,))
        conn.commit()
        return True
    except sqlite3.Error:
        return False
    finally:
        conn.close()

# --- Faculty Preferences Functions ---
def add_faculty_preference(faculty_id, day, period_start, period_end, pref_type):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute(
            "INSERT INTO faculty_preferences (faculty_id, day, period_start, period_end, preference_type) VALUES (?, ?, ?, ?, ?)",
            (faculty_id, day, period_start, period_end, pref_type)
        )
        conn.commit()
        return True
    except sqlite3.IntegrityError:
        return False # Duplicate preference
    finally:
        conn.close()

def get_faculty_preferences():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
        SELECT fp.id, f.name AS faculty_name, fp.day, fp.period_start, fp.period_end, fp.preference_type
        FROM faculty_preferences fp
        JOIN faculty f ON fp.faculty_id = f.id
    """)
    preferences = cursor.fetchall()
    conn.close()
    return preferences

def get_faculty_preferences_by_faculty_id(faculty_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("""
        SELECT day, period_start, period_end, preference_type
        FROM faculty_preferences
        WHERE faculty_id = ?
    """, (faculty_id,))
    preferences = cursor.fetchall()
    conn.close()
    return preferences

def delete_faculty_preference(pref_id):
    conn = get_db_connection()
    cursor = conn.cursor()
    try:
        cursor.execute("DELETE FROM faculty_preferences WHERE id = ?", (pref_id,))
        conn.commit()
        return True
    except sqlite3.Error:
        return False
    finally:
        conn.close()
# database.py (Add these functions at the end of the file, or in a logical grouping)

from models import (
    Department, Semester, Faculty, Course,
    TheoryMapping, LabMapping, FacultyPreference, ScheduledClass,
    Timeslot, Slot # Also import these if you use them in other database functions for structure
)
import sqlite3
import bcrypt

DATABASE_NAME = "timetable.db"

# ... (Previous database.py code remains the same) ...

# --- Functions to load all data into model objects for GA ---

def load_all_data():
    conn = get_db_connection()
    cursor = conn.cursor()

    # 1. Load Departments
    cursor.execute("SELECT id, name FROM departments")
    departments_raw = cursor.fetchall()
    departments = {row['id']: Department(row['id'], row['name']) for row in departments_raw}

    # 2. Load Semesters
    cursor.execute("SELECT id, semester_number FROM semesters")
    semesters_raw = cursor.fetchall()
    semesters = {row['id']: Semester(row['id'], row['semester_number']) for row in semesters_raw}

    # 3. Load Faculty
    cursor.execute("SELECT f.id, f.name, f.emp_id, f.department_id, d.name AS department_name FROM faculty f JOIN departments d ON f.department_id = d.id")
    faculty_raw = cursor.fetchall()
    faculty = {}
    for row in faculty_raw:
        fac_obj = Faculty(row['id'], row['name'], row['emp_id'], row['department_id'], row['department_name'])
        if row['department_id'] in departments:
            fac_obj.set_department(departments[row['department_id']]) # Link actual department object
        faculty[row['id']] = fac_obj

    # 4. Load Courses
    cursor.execute("SELECT id, code, name, hours_per_week, type FROM courses")
    courses_raw = cursor.fetchall()
    courses = {row['id']: Course(row['id'], row['code'], row['name'], row['hours_per_week'], row['type']) for row in courses_raw}

    # 5. Load Theory Mappings
    cursor.execute("SELECT id, semester_id, course_id, faculty_id FROM theory_mappings")
    theory_mappings_raw = cursor.fetchall()
    theory_mappings = []
    for row in theory_mappings_raw:
        tm_obj = TheoryMapping(row['id'], row['semester_id'], row['course_id'], row['faculty_id'])
        tm_obj.semester = semesters.get(row['semester_id'])
        tm_obj.course = courses.get(row['course_id'])
        tm_obj.faculty = faculty.get(row['faculty_id'])
        theory_mappings.append(tm_obj)

    # 6. Load Lab Mappings
    cursor.execute("SELECT id, semester_id, lab_course_id, faculty_id_1, faculty_id_2 FROM lab_mappings")
    lab_mappings_raw = cursor.fetchall()
    lab_mappings = []
    for row in lab_mappings_raw:
        lm_obj = LabMapping(row['id'], row['semester_id'], row['lab_course_id'], row['faculty_id_1'], row['faculty_id_2'])
        lm_obj.semester = semesters.get(row['semester_id'])
        lm_obj.lab_course = courses.get(row['lab_course_id'])
        lm_obj.faculty_1 = faculty.get(row['faculty_id_1'])
        lm_obj.faculty_2 = faculty.get(row['faculty_id_2'])
        lab_mappings.append(lm_obj)


    # 7. Load Faculty Preferences
    cursor.execute("SELECT id, faculty_id, day, period_start, period_end, preference_type FROM faculty_preferences")
    faculty_preferences_raw = cursor.fetchall()
    faculty_preferences = []
    for row in faculty_preferences_raw:
        fp_obj = FacultyPreference(row['id'], row['faculty_id'], row['day'], row['period_start'], row['period_end'], row['preference_type'])
        fp_obj.faculty = faculty.get(row['faculty_id'])
        faculty_preferences.append(fp_obj)

    conn.close()

    return {
        "departments": list(departments.values()),
        "semesters": list(semesters.values()),
        "faculty": list(faculty.values()),
        "courses": list(courses.values()),
        "theory_mappings": theory_mappings,
        "lab_mappings": lab_mappings,
        "faculty_preferences": faculty_preferences,
        # Also return dictionaries for easy lookup by ID
        "departments_by_id": departments,
        "semesters_by_id": semesters,
        "faculty_by_id": faculty,
        "courses_by_id": courses,
    }

# This function creates a list of all classes that *need* to be scheduled.
# This list will form the "pool" of genes for our GA.
def get_classes_to_schedule(all_data):
    classes_to_schedule = []
    
    # Process theory mappings
    for tm in all_data['theory_mappings']:
        course = tm.course
        faculty = tm.faculty
        semester = tm.semester

        if not (course and faculty and semester):
            print(f"Warning: Incomplete data for theory mapping {tm.id}. Skipping.")
            continue # Skip if any linked object is missing

        # Theory courses typically have 1-hour periods.
        # We need to create 'hours_per_week' individual class units for each theory course.
        # Each unit will be 1 hour long.
        for i in range(course.hours_per_week):
            # ScheduledClass(semester_id, course_id, faculty_ids, day, start_period, periods_count, is_lab)
            classes_to_schedule.append(ScheduledClass(
                semester.id, course.id, [faculty.id], None, None, 1, False # Day and period will be assigned by GA
            ))
            # Set object references for convenience
            classes_to_schedule[-1].semester_obj = semester
            classes_to_schedule[-1].course_obj = course
            classes_to_schedule[-1].faculty_objs = [faculty]


    # Process lab mappings
    for lm in all_data['lab_mappings']:
        lab_course = lm.lab_course
        faculty_1 = lm.faculty_1
        faculty_2 = lm.faculty_2
        semester = lm.semester

        if not (lab_course and faculty_1 and faculty_2 and semester):
            print(f"Warning: Incomplete data for lab mapping {lm.id}. Skipping.")
            continue # Skip if any linked object is missing

        # Lab courses are 2 hours continuously.
        # So, each lab mapping represents one unit of 2 hours.
        if lab_course.hours_per_week != 2:
            print(f"Warning: Lab course {lab_course.code} has {lab_course.hours_per_week} hours, but labs are 2 hours continuous. Adjusting.")
            # For robustness, we assume it's one 2-hour block
            classes_to_schedule.append(ScheduledClass(
                semester.id, lab_course.id, [faculty_1.id, faculty_2.id], None, None, 2, True # 2 periods continuous
            ))
            classes_to_schedule[-1].semester_obj = semester
            classes_to_schedule[-1].course_obj = lab_course
            classes_to_schedule[-1].faculty_objs = [faculty_1, faculty_2]
        else:
            classes_to_schedule.append(ScheduledClass(
                semester.id, lab_course.id, [faculty_1.id, faculty_2.id], None, None, 2, True # 2 periods continuous
            ))
            classes_to_schedule[-1].semester_obj = semester
            classes_to_schedule[-1].course_obj = lab_course
            classes_to_schedule[-1].faculty_objs = [faculty_1, faculty_2]

    return classes_to_schedule