db.py

"""
File-System Database Layer

Replaces MySQL with JSON file storage for students, rooms, and allocations.
Mirrors the original MySQL schema:
  - main table  → data/students.json
  - RoomNum     → data/rooms.json
  - results     → data/allocations.json
"""

import json
import os
from typing import List, Optional

# ─── Paths ────────────────────────────────────────────────────────────────────

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DATA_DIR = os.path.join(BASE_DIR, "data")

STUDENTS_FILE = os.path.join(DATA_DIR, "students.json")
ROOMS_FILE = os.path.join(DATA_DIR, "rooms.json")
ALLOCATIONS_FILE = os.path.join(DATA_DIR, "allocations.json")


def _ensure_data_dir():
    """Create data directory if it doesn't exist."""
    os.makedirs(DATA_DIR, exist_ok=True)


def _read_json(filepath: str) -> list:
    """Read a JSON file and return the parsed list."""
    _ensure_data_dir()
    if not os.path.exists(filepath):
        with open(filepath, "w") as f:
            json.dump([], f)
        return []
    try:
        with open(filepath, "r") as f:
            data = json.load(f)
        return data if isinstance(data, list) else []
    except (json.JSONDecodeError, IOError):
        return []


def _write_json(filepath: str, data: list):
    """Write a list to a JSON file."""
    _ensure_data_dir()
    with open(filepath, "w") as f:
        json.dump(data, f, indent=2)


# ─── Student Operations ──────────────────────────────────────────────────────


def get_all_students() -> List[dict]:
    """Get all students from the database."""
    return _read_json(STUDENTS_FILE)


def get_student_by_id(student_id: int) -> Optional[dict]:
    """Get a specific student by ID."""
    students = get_all_students()
    for s in students:
        if s["id"] == student_id:
            return s
    return None


def add_student(student: dict) -> bool:
    """
    Add a new student.

    student dict should contain:
      - id: int
      - name: str
      - cgpa: float
      - pref_roommate: List[int]  (ordered list of preferred roommate IDs)
      - pref_room: List[int]      (ordered list of preferred room IDs)
    """
    students = get_all_students()
    # Check for duplicate ID
    for s in students:
        if s["id"] == student["id"]:
            return False
    students.append(student)
    _write_json(STUDENTS_FILE, students)
    return True


def update_student(student_id: int, updated_data: dict) -> bool:
    """Update an existing student's data."""
    students = get_all_students()
    for i, s in enumerate(students):
        if s["id"] == student_id:
            students[i].update(updated_data)
            _write_json(STUDENTS_FILE, students)
            return True
    return False


def delete_student(student_id: int) -> bool:
    """Delete a student by ID."""
    students = get_all_students()
    new_students = [s for s in students if s["id"] != student_id]
    if len(new_students) == len(students):
        return False
    _write_json(STUDENTS_FILE, new_students)
    return True


def clear_all_students():
    """Remove all students."""
    _write_json(STUDENTS_FILE, [])


def save_all_students(students: List[dict]):
    """Overwrite the entire students database."""
    _write_json(STUDENTS_FILE, students)


def get_student_count() -> int:
    """Get the total number of students."""
    return len(get_all_students())


# ─── Room Operations ─────────────────────────────────────────────────────────


def get_all_rooms() -> List[dict]:
    """Get all rooms from the database."""
    return _read_json(ROOMS_FILE)


def add_room(room: dict) -> bool:
    """
    Add a new room.

    room dict should contain:
      - room_id: int
      - room_number: str (e.g., 'A101')
    """
    rooms = get_all_rooms()
    for r in rooms:
        if r["room_id"] == room["room_id"]:
            return False
    rooms.append(room)
    _write_json(ROOMS_FILE, rooms)
    return True


def delete_room(room_id: int) -> bool:
    """Delete a room by ID."""
    rooms = get_all_rooms()
    new_rooms = [r for r in rooms if r["room_id"] != room_id]
    if len(new_rooms) == len(rooms):
        return False
    _write_json(ROOMS_FILE, new_rooms)
    return True


def clear_all_rooms():
    """Remove all rooms."""
    _write_json(ROOMS_FILE, [])


def save_all_rooms(rooms: List[dict]):
    """Overwrite the entire rooms database."""
    _write_json(ROOMS_FILE, rooms)


def get_room_count() -> int:
    """Get the total number of rooms."""
    return len(get_all_rooms())


# ─── Allocation Operations ───────────────────────────────────────────────────


def get_all_allocations() -> List[dict]:
    """Get all allocation results."""
    return _read_json(ALLOCATIONS_FILE)


def save_allocations(allocations: List[dict]):
    """Save allocation results (overwrites previous results)."""
    _write_json(ALLOCATIONS_FILE, allocations)


def clear_allocations():
    """Clear all allocation results."""
    _write_json(ALLOCATIONS_FILE, [])


# ─── Bulk Import from CSV ────────────────────────────────────────────────────


def import_students_from_csv(csv_content: str) -> tuple:
    """
    Import students from CSV content.

    Expected columns: id, name, cgpa, pref_roommate, pref_room
    pref_roommate and pref_room should be space-separated integers.

    Returns:
        (success_count, error_messages)
    """
    import csv
    import io

    reader = csv.DictReader(io.StringIO(csv_content))
    students = []
    errors = []

    for row_num, row in enumerate(reader, start=2):
        try:
            student = {
                "id": int(row["id"].strip()),
                "name": row["name"].strip(),
                "cgpa": float(row["cgpa"].strip()),
                "pref_roommate": [
                    int(x) for x in row["pref_roommate"].strip().split()
                ],
                "pref_room": [
                    int(x) for x in row["pref_room"].strip().split()
                ],
            }
            students.append(student)
        except (KeyError, ValueError) as e:
            errors.append(f"Row {row_num}: {str(e)}")

    if students:
        save_all_students(students)

    return len(students), errors


def import_rooms_from_csv(csv_content: str) -> tuple:
    """
    Import rooms from CSV content.

    Expected columns: room_id, room_number

    Returns:
        (success_count, error_messages)
    """
    import csv
    import io

    reader = csv.DictReader(io.StringIO(csv_content))
    rooms = []
    errors = []

    for row_num, row in enumerate(reader, start=2):
        try:
            room = {
                "room_id": int(row["room_id"].strip()),
                "room_number": row["room_number"].strip(),
            }
            rooms.append(room)
        except (KeyError, ValueError) as e:
            errors.append(f"Row {row_num}: {str(e)}")

    if rooms:
        save_all_rooms(rooms)

    return len(rooms), errors