"""
Timetable generation engine — multi-tenant version.
All queries scoped to school_id via config_id → school_id chain.
"""
import random
from collections import defaultdict
from database import get_db

DAYS_ORDER = ["Mon", "Tue", "Wed", "Thu", "Fri", "Sat"]

def _working_days(config_days: str) -> list[str]:
    return [d for d in DAYS_ORDER if d in config_days.split(",")]

def generate_timetable(config_id: int) -> dict:
    db = get_db()
    try:
        cfg = db.execute("SELECT * FROM timetable_config WHERE id=?", (config_id,)).fetchone()
        if not cfg:
            return {"success": False, "message": "Config not found", "conflicts": []}

        school_id = cfg["school_id"]
        days      = _working_days(cfg["working_days"])
        n_periods = cfg["periods_per_day"]
        break_after = [int(x) for x in cfg["break_after"].split(",") if x.strip()]
        lab_days  = cfg["lab_days"].split(",")
        pt_days   = cfg["pt_days"].split(",")

        requirements = db.execute(
            """SELECT csr.*, s.subject_type, s.name as subj_name,
                      t.name as teacher_name, cs.grade, cs.section
               FROM class_subject_requirement csr
               JOIN subject s       ON s.id  = csr.subject_id
               JOIN teacher t       ON t.id  = csr.teacher_id
               JOIN class_section cs ON cs.id = csr.class_id
               WHERE cs.school_id = ?""",
            (school_id,)
        ).fetchall()

        if not requirements:
            return {"success": False, "message": "No subject requirements configured.", "conflicts": []}

        classes  = list({r["class_id"] for r in requirements})
        all_slots   = [(d, p) for d in days for p in range(1, n_periods+1) if p not in break_after]
        lab_slots   = [(d, p) for d, p in all_slots if d in lab_days]
        pt_slots    = [(d, p) for d, p in all_slots if d in pt_days]

        teacher_occ:   dict[int, dict[tuple, int]] = defaultdict(dict)
        timetable_grid: dict[int, dict[tuple, dict]] = {c: {} for c in classes}
        conflicts = []

        class_reqs: dict[int, list] = defaultdict(list)
        for r in requirements:
            class_reqs[r["class_id"]].append(dict(r))

        random.shuffle(classes)
        for class_id in classes:
            reqs = class_reqs[class_id]
            reqs.sort(key=lambda r: 0 if r["subject_type"]=="lab" else 1 if r["subject_type"]=="PT" else 2)

            for req in reqs:
                s_type    = req["subject_type"]
                teacher_id = req["teacher_id"]
                subject_id = req["subject_id"]
                needed    = req["periods_per_week"]

                if s_type == "lab":
                    candidates = lab_slots.copy()
                elif s_type in ("PT", "activity"):
                    candidates = pt_slots.copy()
                else:
                    candidates = all_slots.copy()

                candidates = [s for s in candidates if s not in timetable_grid[class_id]]
                candidates = [s for s in candidates if s not in teacher_occ[teacher_id]]

                placed = 0
                days_used: dict[str, int] = defaultdict(int)
                random.shuffle(candidates)
                candidates.sort(key=lambda s: (days_used.get(s[0], 0), s[0]))

                for slot in candidates:
                    if placed >= needed:
                        break
                    day, period = slot
                    max_per_day = 1 if s_type in ("lab", "PT", "activity") else 2
                    if days_used[day] >= max_per_day:
                        continue
                    timetable_grid[class_id][slot] = {"subject_id": subject_id, "teacher_id": teacher_id}
                    teacher_occ[teacher_id][slot]  = class_id
                    days_used[day] += 1
                    placed += 1

                if placed < needed:
                    conflicts.append(
                        f"Only {placed}/{needed} periods for {req['subj_name']} "
                        f"in Grade {req['grade']}-{req['section']} (teacher: {req['teacher_name']})"
                    )

        db.execute("DELETE FROM timetable WHERE config_id=?", (config_id,))
        rows = []
        for class_id in classes:
            for day in days:
                for period_no in range(1, n_periods+1):
                    is_break = 1 if period_no in break_after else 0
                    slot  = (day, period_no)
                    entry = timetable_grid[class_id].get(slot, {})
                    rows.append((
                        config_id, class_id, day, period_no,
                        entry.get("subject_id"), entry.get("teacher_id"),
                        1 if (not is_break and not entry) else 0, is_break,
                    ))

        db.executemany(
            """INSERT OR REPLACE INTO timetable
               (config_id,class_id,day,period_no,subject_id,teacher_id,is_free,is_break)
               VALUES (?,?,?,?,?,?,?,?)""", rows
        )
        db.commit()
        msg = "Timetable generated successfully!"
        if conflicts:
            msg += f" ({len(conflicts)} constraint(s) could not be fully satisfied)"
        return {"success": True, "message": msg, "conflicts": conflicts}
    finally:
        db.close()

def detect_clashes(config_id: int) -> list[dict]:
    db = get_db()
    try:
        rows = db.execute(
            """SELECT t.day, t.period_no, t.teacher_id,
                      tc.name as teacher_name, COUNT(*) as cnt
               FROM timetable t
               JOIN teacher tc ON tc.id=t.teacher_id
               WHERE t.config_id=? AND t.teacher_id IS NOT NULL AND t.is_break=0
               GROUP BY t.day, t.period_no, t.teacher_id HAVING cnt>1""",
            (config_id,)
        ).fetchall()
        return [dict(r) for r in rows]
    finally:
        db.close()

def get_substitute_suggestions(config_id: int, absent_teacher_id: int, day: str) -> list[dict]:
    db = get_db()
    try:
        periods = db.execute(
            """SELECT t.period_no, t.class_id, t.subject_id,
                      s.name as subject_name, cs.grade, cs.section
               FROM timetable t
               JOIN subject s        ON s.id  = t.subject_id
               JOIN class_section cs ON cs.id = t.class_id
               WHERE t.config_id=? AND t.teacher_id=? AND t.day=? AND t.is_break=0""",
            (config_id, absent_teacher_id, day)
        ).fetchall()

        suggestions = []
        for p in periods:
            capable = db.execute(
                """SELECT t.id, t.name FROM teacher t
                   JOIN teacher_subject ts ON ts.teacher_id=t.id
                   WHERE ts.subject_id=? AND t.id!=? AND t.is_active=1""",
                (p["subject_id"], absent_teacher_id)
            ).fetchall()
            free = []
            for tc in capable:
                clash = db.execute(
                    """SELECT 1 FROM timetable
                       WHERE config_id=? AND teacher_id=? AND day=? AND period_no=? AND is_break=0""",
                    (config_id, tc["id"], day, p["period_no"])
                ).fetchone()
                if not clash:
                    free.append({"id": tc["id"], "name": tc["name"]})
            suggestions.append({
                "period_no": p["period_no"], "class_id": p["class_id"],
                "grade": p["grade"], "section": p["section"],
                "subject_name": p["subject_name"], "subject_id": p["subject_id"],
                "absent_teacher_id": absent_teacher_id,
                "substitutes": free,
            })
        return suggestions
    finally:
        db.close()