<?php
/**
 * Timetable Management Class
 * Handles all timetable-related operations
 */
class Timetable extends Database {

    /**
     * Create a new timetable entry
     */
    public function createTimetableEntry($data) {
        // Check for conflicts first
        $conflicts = $this->checkConflicts($data);
        if(!empty($conflicts)) {
            return ['success' => false, 'conflicts' => $conflicts];
        }

        $sql = "INSERT INTO timetable 
                (class_id, section_id, subject_id, teacher_id, day_id, period_id, 
                 room_number, academic_year, term, notes, created_by)
                VALUES 
                (:class_id, :section_id, :subject_id, :teacher_id, :day_id, :period_id,
                 :room_number, :academic_year, :term, :notes, :created_by)";

        $this->query($sql);
        $this->bind(':class_id', $data['class_id']);
        $this->bind(':section_id', $data['section_id']);
        $this->bind(':subject_id', $data['subject_id']);
        $this->bind(':teacher_id', $data['teacher_id']);
        $this->bind(':day_id', $data['day_id']);
        $this->bind(':period_id', $data['period_id']);
        $this->bind(':room_number', $data['room_number'] ?? null);
        $this->bind(':academic_year', $data['academic_year'] ?? null);
        $this->bind(':term', $data['term'] ?? null);
        $this->bind(':notes', $data['notes'] ?? null);
        $this->bind(':created_by', $data['created_by'] ?? null);

        if($this->execute()) {
            return ['success' => true, 'id' => $this->lastInserted()];
        }
        return ['success' => false, 'message' => 'Failed to create timetable entry'];
    }

    /**
     * Update timetable entry
     */
    public function updateTimetableEntry($id, $data) {
        // Check for conflicts
        $conflicts = $this->checkConflicts($data, $id);
        if(!empty($conflicts)) {
            return ['success' => false, 'conflicts' => $conflicts];
        }

        $sql = "UPDATE timetable SET
                class_id = :class_id,
                section_id = :section_id,
                subject_id = :subject_id,
                teacher_id = :teacher_id,
                day_id = :day_id,
                period_id = :period_id,
                room_number = :room_number,
                notes = :notes
                WHERE id = :id";

        $this->query($sql);
        $this->bind(':id', $id);
        $this->bind(':class_id', $data['class_id']);
        $this->bind(':section_id', $data['section_id']);
        $this->bind(':subject_id', $data['subject_id']);
        $this->bind(':teacher_id', $data['teacher_id']);
        $this->bind(':day_id', $data['day_id']);
        $this->bind(':period_id', $data['period_id']);
        $this->bind(':room_number', $data['room_number'] ?? null);
        $this->bind(':notes', $data['notes'] ?? null);

        return $this->execute();
    }

    /**
     * Delete timetable entry
     */
    public function deleteTimetableEntry($id) {
        $sql = "DELETE FROM timetable WHERE id = :id";
        $this->query($sql);
        $this->bind(':id', $id);
        return $this->execute();
    }

    /**
     * Get timetable for a class
     */
    public function getClassTimetable($classId, $sectionId, $academicYear = null, $term = null) {
        $sql = "SELECT t.*, 
                s.subject_name,
                tc.name as teacher_name,
                td.day_name,
                tp.period_name, tp.start_time, tp.end_time, tp.is_break
                FROM timetable t
                LEFT JOIN subjects s ON t.subject_id = s.id
                LEFT JOIN teachers tc ON t.teacher_id = tc.id
                LEFT JOIN timetable_days td ON t.day_id = td.id
                LEFT JOIN timetable_periods tp ON t.period_id = tp.id
                WHERE t.class_id = :class_id AND t.section_id = :section_id";

        if($academicYear) {
            $sql .= " AND t.academic_year = :academic_year";
        }
        if($term) {
            $sql .= " AND t.term = :term";
        }

        $sql .= " ORDER BY td.day_number, tp.period_number";

        $this->query($sql);
        $this->bind(':class_id', $classId);
        $this->bind(':section_id', $sectionId);
        if($academicYear) $this->bind(':academic_year', $academicYear);
        if($term) $this->bind(':term', $term);

        return $this->fetchMultiple();
    }

    /**
     * Get teacher timetable
     */
    public function getTeacherTimetable($teacherId, $academicYear = null, $term = null) {
        $sql = "SELECT t.*,
                s.subject_name,
                c.name as class_name,
                sec.name as section_name,
                td.day_name,
                tp.period_name, tp.start_time, tp.end_time
                FROM timetable t
                LEFT JOIN subjects s ON t.subject_id = s.id
                LEFT JOIN classes c ON t.class_id = c.id
                LEFT JOIN sections sec ON t.section_id = sec.id
                LEFT JOIN timetable_days td ON t.day_id = td.id
                LEFT JOIN timetable_periods tp ON t.period_id = tp.id
                WHERE t.teacher_id = :teacher_id";

        if($academicYear) {
            $sql .= " AND t.academic_year = :academic_year";
        }
        if($term) {
            $sql .= " AND t.term = :term";
        }

        $sql .= " ORDER BY td.day_number, tp.period_number";

        $this->query($sql);
        $this->bind(':teacher_id', $teacherId);
        if($academicYear) $this->bind(':academic_year', $academicYear);
        if($term) $this->bind(':term', $term);

        return $this->fetchMultiple();
    }

    /**
     * Check for scheduling conflicts
     */
    public function checkConflicts($data, $excludeId = null) {
        $conflicts = [];

        // Get settings
        $allowTeacherClash = $this->getSetting('allow_teacher_clash') == '1';
        $allowRoomClash = $this->getSetting('allow_room_clash') == '0';

        // Check teacher conflict
        if(!$allowTeacherClash) {
            $sql = "SELECT t.id, c.name as class_name, sec.name as section_name
                    FROM timetable t
                    LEFT JOIN classes c ON t.class_id = c.id
                    LEFT JOIN sections sec ON t.section_id = sec.id
                    WHERE t.teacher_id = :teacher_id 
                    AND t.day_id = :day_id 
                    AND t.period_id = :period_id";

            if($excludeId) {
                $sql .= " AND t.id != :exclude_id";
            }

            $this->query($sql);
            $this->bind(':teacher_id', $data['teacher_id']);
            $this->bind(':day_id', $data['day_id']);
            $this->bind(':period_id', $data['period_id']);
            if($excludeId) $this->bind(':exclude_id', $excludeId);

            $result = $this->fetchSingle();
            if($result) {
                $conflicts[] = [
                    'type' => 'teacher_clash',
                    'message' => "Teacher is already assigned to {$result->class_name} - {$result->section_name} at this time"
                ];
            }
        }

        // Check room conflict
        if(!$allowRoomClash && !empty($data['room_number'])) {
            $sql = "SELECT t.id, c.name as class_name, sec.name as section_name
                    FROM timetable t
                    LEFT JOIN classes c ON t.class_id = c.id
                    LEFT JOIN sections sec ON t.section_id = sec.id
                    WHERE t.room_number = :room_number 
                    AND t.day_id = :day_id 
                    AND t.period_id = :period_id";

            if($excludeId) {
                $sql .= " AND t.id != :exclude_id";
            }

            $this->query($sql);
            $this->bind(':room_number', $data['room_number']);
            $this->bind(':day_id', $data['day_id']);
            $this->bind(':period_id', $data['period_id']);
            if($excludeId) $this->bind(':exclude_id', $excludeId);

            $result = $this->fetchSingle();
            if($result) {
                $conflicts[] = [
                    'type' => 'room_clash',
                    'message' => "Room is already allocated to {$result->class_name} - {$result->section_name} at this time"
                ];
            }
        }

        return $conflicts;
    }

    /**
     * Get all periods
     */
    public function getAllPeriods() {
        $sql = "SELECT * FROM timetable_periods WHERE is_active = 1 ORDER BY period_number";
        $this->query($sql);
        return $this->fetchMultiple();
    }

    /**
     * Get all active days
     */
    public function getActiveDays() {
        $sql = "SELECT * FROM timetable_days WHERE is_active = 1 ORDER BY day_number";
        $this->query($sql);
        return $this->fetchMultiple();
    }

    /**
     * Get setting value
     */
    public function getSetting($key) {
        $sql = "SELECT setting_value FROM timetable_settings WHERE setting_key = :key";
        $this->query($sql);
        $this->bind(':key', $key);
        return $this->fetchColumn();
    }

    /**
     * Update setting
     */
    public function updateSetting($key, $value, $updatedBy = null) {
        $sql = "UPDATE timetable_settings 
                SET setting_value = :value, updated_by = :updated_by 
                WHERE setting_key = :key";
        $this->query($sql);
        $this->bind(':key', $key);
        $this->bind(':value', $value);
        $this->bind(':updated_by', $updatedBy);
        return $this->execute();
    }

    /**
     * Create period
     */
    public function createPeriod($data) {
        $sql = "INSERT INTO timetable_periods 
                (period_name, period_number, start_time, end_time, duration, is_break)
                VALUES 
                (:period_name, :period_number, :start_time, :end_time, :duration, :is_break)";

        $this->query($sql);
        $this->bind(':period_name', $data['period_name']);
        $this->bind(':period_number', $data['period_number']);
        $this->bind(':start_time', $data['start_time']);
        $this->bind(':end_time', $data['end_time']);
        $this->bind(':duration', $data['duration']);
        $this->bind(':is_break', $data['is_break'] ?? 0);

        return $this->execute();
    }

    /**
     * Update period
     */
    public function updatePeriod($id, $data) {
        $sql = "UPDATE timetable_periods SET
                period_name = :period_name,
                start_time = :start_time,
                end_time = :end_time,
                duration = :duration,
                is_break = :is_break
                WHERE id = :id";

        $this->query($sql);
        $this->bind(':id', $id);
        $this->bind(':period_name', $data['period_name']);
        $this->bind(':start_time', $data['start_time']);
        $this->bind(':end_time', $data['end_time']);
        $this->bind(':duration', $data['duration']);
        $this->bind(':is_break', $data['is_break'] ?? 0);

        return $this->execute();
    }

    /**
     * Delete period
     */
    public function deletePeriod($id) {
        $sql = "DELETE FROM timetable_periods WHERE id = :id";
        $this->query($sql);
        $this->bind(':id', $id);
        return $this->execute();
    }

    /**
     * Bulk create timetable from template
     */
    public function createFromTemplate($templateId, $academicYear, $term, $createdBy) {
        // Get template details
        $sql = "SELECT * FROM timetable_templates WHERE id = :id";
        $this->query($sql);
        $this->bind(':id', $templateId);
        $template = $this->fetchSingle();

        if(!$template) {
            return ['success' => false, 'message' => 'Template not found'];
        }

        // Get template entries
        $sql = "SELECT * FROM timetable_template_details WHERE template_id = :template_id";
        $this->query($sql);
        $this->bind(':template_id', $templateId);
        $entries = $this->fetchMultiple();

        $created = 0;
        $errors = [];

        foreach($entries as $entry) {
            $data = [
                'class_id' => $template->class_id,
                'section_id' => $template->section_id,
                'subject_id' => $entry->subject_id,
                'teacher_id' => $entry->teacher_id,
                'day_id' => $entry->day_id,
                'period_id' => $entry->period_id,
                'room_number' => $entry->room_number,
                'academic_year' => $academicYear,
                'term' => $term,
                'created_by' => $createdBy
            ];

            $result = $this->createTimetableEntry($data);
            if($result['success']) {
                $created++;
            } else {
                $errors[] = $result;
            }
        }

        return [
            'success' => true,
            'created' => $created,
            'total' => count($entries),
            'errors' => $errors
        ];
    }

    /**
     * Get teacher workload
     */
    public function getTeacherWorkload($teacherId, $academicYear = null, $term = null) {
        $sql = "SELECT COUNT(*) as total_periods,
                COUNT(DISTINCT day_id) as teaching_days,
                COUNT(DISTINCT CONCAT(class_id, '-', section_id)) as classes_taught
                FROM timetable
                WHERE teacher_id = :teacher_id";

        if($academicYear) {
            $sql .= " AND academic_year = :academic_year";
        }
        if($term) {
            $sql .= " AND term = :term";
        }

        $this->query($sql);
        $this->bind(':teacher_id', $teacherId);
        if($academicYear) $this->bind(':academic_year', $academicYear);
        if($term) $this->bind(':term', $term);

        return $this->fetchSingle();
    }

    /**
     * Get room allocations
     */
    public function getRoomAllocations() {
        $sql = "SELECT * FROM timetable_room_allocations WHERE is_available = 1 ORDER BY room_number";
        $this->query($sql);
        return $this->fetchMultiple();
    }

    /**
     * Create substitution
     */
    public function createSubstitution($data) {
        $sql = "INSERT INTO timetable_substitutions
                (timetable_id, original_teacher_id, substitute_teacher_id, 
                 substitution_date, reason, status, created_by)
                VALUES
                (:timetable_id, :original_teacher_id, :substitute_teacher_id,
                 :substitution_date, :reason, :status, :created_by)";

        $this->query($sql);
        $this->bind(':timetable_id', $data['timetable_id']);
        $this->bind(':original_teacher_id', $data['original_teacher_id']);
        $this->bind(':substitute_teacher_id', $data['substitute_teacher_id']);
        $this->bind(':substitution_date', $data['substitution_date']);
        $this->bind(':reason', $data['reason']);
        $this->bind(':status', $data['status'] ?? 'pending');
        $this->bind(':created_by', $data['created_by']);

        return $this->execute();
    }
}