<?php
/**
 * Assignment Management Class
 * Handles all assignment-related operations
 */
class Assignment extends Database
{
    /**
     * Create new assignment
     */
    public function createAssignment($data): array
    {
        try {
            $sql = "INSERT INTO assignments 
                    (title, description, instructions, class_id, section_id, subject_id, 
                     created_by, creator_type, due_date, total_marks, assignment_type, 
                     status, allow_late_submission, late_submission_days, attachment_url)
                    VALUES 
                    (:title, :description, :instructions, :class_id, :section_id, :subject_id,
                     :created_by, :creator_type, :due_date, :total_marks, :assignment_type,
                     :status, :allow_late, :late_days, :attachment_url)";

            $this->query($sql);
            $this->bind(':title', $data['title']);
            $this->bind(':description', $data['description']);
            $this->bind(':instructions', $data['instructions']);
            $this->bind(':class_id', $data['class_id']);
            $this->bind(':section_id', $data['section_id']);
            $this->bind(':subject_id', $data['subject_id']);
            $this->bind(':created_by', $data['created_by']);
            $this->bind(':creator_type', $data['creator_type']);
            $this->bind(':due_date', $data['due_date']);
            $this->bind(':total_marks', $data['total_marks']);
            $this->bind(':assignment_type', $data['assignment_type']);
            $this->bind(':status', $data['status']);
            $this->bind(':allow_late', $data['allow_late_submission']);
            $this->bind(':late_days', $data['late_submission_days']);
            $this->bind(':attachment_url', $data['attachment_url']);


            if ($this->execute()) {
                $assignment_id = $this->lastInserted();

                // Create assignment statistics record
                $stats_sql = "INSERT INTO assignment_statistics (assignment_id, total_students)
                             SELECT :id, COUNT(*) FROM student 
                             WHERE classesID = :class_id AND status = 'active'";
                $this->query($stats_sql);
                $this->bind(':id', $assignment_id);
                $this->bind(':class_id', $data['class_id']);
                $this->execute();

                return [
                    'success' => true,
                    'message' => 'Assignment created successfully',
                    'assignment_id' => $assignment_id
                ];
            }
            return ['success' => false, 'message' => 'Failed to create assignment'];
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    /**
     * Get assignments for class/section
     */
    public function getAssignmentsByClass($class_id, $section_id, $subject_id = null, $status = 'published')
    {
        $sql = "SELECT a.*, s.subject_name, c.name as class_name, sec.name as section_name,
                       COUNT(DISTINCT asub.id) as total_submissions,
                       SUM(CASE WHEN asub.is_graded = 1 THEN 1 ELSE 0 END) as graded_submissions,
                       stats.submitted_count, stats.graded_count, stats.average_marks
                FROM assignments a
                LEFT JOIN subjects s ON a.subject_id = s.id
                LEFT JOIN classes c ON a.class_id = c.id
                LEFT JOIN sections sec ON a.section_id = sec.id
                LEFT JOIN assignment_submissions asub ON a.id = asub.assignment_id
                LEFT JOIN assignment_statistics stats ONa.id = stats.assignment_id
                WHERE a.class_id = :class_id 
                AND a.section_id = :section_id 
                AND a.status = :status";

        if ($subject_id) {
            $sql .= " AND a.subject_id = :subject_id";
        }

        $sql .= " GROUP BYa.id ORDER BY a.due_date DESC";

        $this->query($sql);
        $this->bind(':class_id', $class_id);
        $this->bind(':section_id', $section_id);
        $this->bind(':status', $status);

        if ($subject_id) {
            $this->bind(':subject_id', $subject_id);
        }

        return $this->fetchMultiple();
    }

    /**
     * Get single assignment details
     */
    public function getAssignmentById($assignment_id)
    {
        $sql = "SELECT a.*, s.subject_name, c.name as class_name, sec.name as section_name,
                       stats.total_students, stats.submitted_count, stats.graded_count, stats.average_marks
                FROM assignments a
                LEFT JOIN subjects s ON a.subject_id = s.id
                LEFT JOIN classes c ON a.class_id = c.id
                LEFT JOIN sections sec ON a.section_id = sec.id
                LEFT JOIN assignment_statistics stats ON a.id = stats.assignment_id
                WHERE a.id = :assignment_id";

        $this->query($sql);
        $this->bind(':assignment_id', $assignment_id);
        return $this->fetchSingle();
    }

    /**
     * Update assignment
     */
    public function updateAssignment($assignment_id, $data): array
    {
        $sql = "UPDATE assignments SET 
                title = :title,
                description = :description,
                instructions = :instructions,
                due_date = :due_date,
                total_marks = :total_marks,
                assignment_type = :assignment_type,
                status = :status,
                allow_late_submission = :allow_late,
                late_submission_days = :late_days,
                attachment_url = :attachment_url
                WHERE id = :assignment_id";

        $this->query($sql);
        $this->bind(':assignment_id', $assignment_id);
        $this->bind(':title', $data['title']);
        $this->bind(':description', $data['description']);
        $this->bind(':instructions', $data['instructions']);
        $this->bind(':due_date', $data['due_date']);
        $this->bind(':total_marks', $data['total_marks']);
        $this->bind(':assignment_type', $data['assignment_type']);
        $this->bind(':status', $data['status']);
        $this->bind(':allow_late', $data['allow_late_submission']);
        $this->bind(':late_days', $data['late_submission_days']);
        $this->bind(':attachment_url', $data['attachment_url']);

        if ($this->execute()) {
            return ['success' => true, 'message' => 'Assignment updated successfully'];
        }
        return ['success' => false, 'message' => 'Failed to update assignment'];
    }

   /**
     * Delete assignment
     */
    public function deleteAssignment($assignment_id): array
    {
        try {
            // Delete related records
            $this->query("DELETE FROM assignment_submissions WHERE assignment_id = :id");
            $this->bind(':id', $assignment_id);
            $this->execute();

            $this->query("DELETE FROM assignment_rubrics WHERE assignment_id = :id");
            $this->bind(':id', $assignment_id);
            $this->execute();

            $this->query("DELETE FROM assignment_statistics WHERE assignment_id = :id");
            $this->bind(':id', $assignment_id);
            $this->execute();

            //Delete assignment
            $this->query("DELETE FROM assignments WHERE id = :id");
            $this->bind(':id', $assignment_id);

            if ($this->execute()) {
                return ['success' => true, 'message' => 'Assignment deleted successfully'];
            }
            return ['success' => false,'message' => 'Failed to delete assignment'];
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    /**
     * Get student submissions for assignment
     */
    public function getSubmissions($assignment_id)
    {
       $sql = "SELECT asub.*, st.name as student_name, st.registerNO,
                       COUNT(DISTINCT asf.id) as file_count
                FROM assignment_submissions asub
                LEFT JOIN student st ON asub.student_id = st.studentID
                LEFT JOIN assignment_submission_files asf ON asub.id = asf.submission_id
                WHERE asub.assignment_id = :assignment_id
                GROUP BY asub.id
                ORDER BY asub.submitted_at DESC";

        $this->query($sql);
        $this->bind(':assignment_id', $assignment_id);
        return $this->fetchMultiple();
}

    /**
     * Get student assignment
     */
    public function getStudentAssignment($student_id): array
    {
        $sql = "SELECT classesID, sectionid FROM student WHERE studentID = :student_id";
        $this->query($sql);
        $this->bind(':student_id', $student_id);
        $student = $this->fetchSingle();

        if (!$student) {
            return [];
        }

        $sql = "SELECT a.*, s.subject_name,
                       COALESCE(asub.submission_status, 'not_submitted') as submission_status,
                       asub.submitted_at, asub.marks_obtained, asub.percentage, asub.grade,
                       asub.is_graded, asub.teacher_feedback,
                       CASE 
                           WHEN NOW() > a.due_date AND asub.submission_status = 'not_submitted' THEN 1
                           ELSE 0
                       END as is_overdue FROM assignments a
                LEFT JOIN subjects s ON a.subject_id = s.id
                LEFT JOIN assignment_submissions asub ON a.id = asub.assignment_id AND asub.student_id = :student_id
                WHERE a.class_id = :class_id 
                AND a.section_id = :section_id 
                AND a.status = 'published'
                ORDER BY a.due_date DESC";

        $this->query($sql);
        $this->bind(':student_id', $student_id);
        $this->bind(':class_id', $student->classesID);
        $this->bind(':section_id', $student->sectionid);

        return $this->fetchMultiple();
    }

    /**
     * Submit assignment
     */
    public function submitAssignment($assignment_id, $student_id, $submission_data): array
    {
        try {
            $assignment = $this->getAssignmentById($assignment_id);
            $now = new DateTime();
            $due_date= new DateTime($assignment->due_date);

            $submission_status = 'submitted';
            if ($now > $due_date) {
                $submission_status = 'late_submitted';
            }

            // Check if submission already exists
            $check_sql = "SELECT id FROM assignment_submissions 
                         WHERE assignment_id =:assignment_id AND student_id = :student_id";
            $this->query($check_sql);
            $this->bind(':assignment_id', $assignment_id);
            $this->bind(':student_id', $student_id);
            $existing = $this->fetchSingle();

            if ($existing) {
                // Update existing submission
                $sql = "UPDATE assignment_submissions SET 
                       submission_text = :text,
                       submission_file = :file,
                       submission_status = :status,
                       submitted_at = NOW()
                       WHERE id = :id";

                $this->query($sql);
                $this->bind(':id', $existing->id);
                $submission_id = $existing->id;
            } else {
                // Create new submission
                $sql = "INSERT INTO assignment_submissions 
                       (assignment_id, student_id, submission_text, submission_file, 
                        submission_status, submitted_at)
                       VALUES (:assignment_id, :student_id, :text, :file, :status, NOW())";

                $this->query($sql);
                $this->bind(':assignment_id', $assignment_id);
                $this->bind(':student_id', $student_id);
                $submission_id = null;
            }

            $this->bind(':text',$submission_data['text']);
            $this->bind(':file', $submission_data['file']);
            $this->bind(':status', $submission_status);

            if ($this->execute()) {
                if (!$submission_id) {
                    $submission_id = $this->lastInserted();
                }

                // Update statistics
            $this->updateAssignmentStats($assignment_id);

                return [
                    'success' => true,
                    'message' => $submission_status == 'late_submitted' ?
                        'Assignment submitted late. Late submission is allowed.' :
                        'Assignment submitted successfully',
                    'submission_id' => $submission_id
               ];
            }
            return ['success' => false, 'message' => 'Failed to submit assignment'];
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    /**
     * Grade submission
     */
    public function gradeSubmission($submission_id, $marks, $feedback, $graded_by): array
    {
        try {
            // Get submission and assignment details
            $sql = "SELECT asub.*, a.total_marks FROM assignment_submissions asub
                   JOIN assignments a ON asub.assignment_id = a.id
                   WHERE asub.id = :submission_id";
            $this->query($sql);
            $this->bind(':submission_id', $submission_id);
            $submission = $this->fetchSingle();

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

            $percentage= ($marks / $submission->total_marks) * 100;
            $grade = $this->calculateGrade($percentage);

            $update_sql = "UPDATE assignment_submissions SET 
                          marks_obtained = :marks,
                          percentage = :percentage,
                          grade = :grade,
                          teacher_feedback =:feedback,
                          graded_by = :graded_by,
                          is_graded = 1,
                          graded_at = NOW()
                          WHERE id = :submission_id";

            $this->query($update_sql);
            $this->bind(':submission_id', $submission_id);
            $this->bind(':marks', $marks);
            $this->bind(':percentage', $percentage);
            $this->bind(':grade', $grade);
            $this->bind(':feedback', $feedback);
            $this->bind(':graded_by', $graded_by);

            if ($this->execute()) {
                // Update statistics
                $this->updateAssignmentStats($submission->assignment_id);

                return ['success' => true, 'message' => 'Submission graded successfully'];
            }
            return ['success' => false, 'message' => 'Failed to grade submission'];
        } catch (Exception $e) {
            return ['success' => false, 'message'=> $e->getMessage()];
        }
    }

    /**
     * Get submission details
     */
    public function getSubmissionDetails($submission_id)
    {
        $sql = "SELECT asub.*, a.title as assignment_title, a.total_marks,
                       st.name as student_name, st.registerNO,
                       COUNT(DISTINCT asf.id) as file_count
                FROM assignment_submissions asub
                LEFT JOIN assignments a ON asub.assignment_id = a.id
                LEFT JOIN student st ON asub.student_id = st.studentID
                LEFT JOIN assignment_submission_files asf ON asub.id = asf.submission_id
                WHERE asub.id = :submission_id
                GROUP BY asub.id";

        $this->query($sql);
        $this->bind(':submission_id', $submission_id);
        return $this->fetchSingle();
    }

    /**
     * Calculate grade based on percentage
     */
    private function calculateGrade($percentage): string
    {
        if ($percentage >= 90) return 'A';
       if ($percentage >= 80) return 'B';
        if ($percentage >= 70) return 'C';
        if ($percentage >= 60) return 'D';
        return 'F';
    }

    /**
     * Update assignment statistics
     */
    public function updateAssignmentStats($assignment_id)
{
        $sql = "UPDATE assignment_statistics SET 
               submitted_count = (SELECT COUNT(*) FROM assignment_submissions 
                                 WHERE assignment_id = :assignment_id 
                                 AND submission_status IN ('submitted', 'late_submitted')),
               graded_count = (SELECT COUNT(*) FROM assignment_submissions 
                             WHERE assignment_id =:assignment_id AND is_graded = 1),
               average_marks = (SELECT AVG(marks_obtained) FROM assignment_submissions 
                              WHERE assignment_id = :assignment_id AND is_graded = 1),
               highest_marks = (SELECT MAX(marks_obtained) FROM assignment_submissions 
                              WHERE assignment_id = :assignment_id AND is_graded = 1),
               lowest_marks = (SELECT MIN(marks_obtained) FROM assignment_submissions 
                             WHERE assignment_id = :assignment_id AND is_graded = 1)
               WHERE assignment_id = :assignment_id";

        $this->query($sql);
        $this->bind(':assignment_id', $assignment_id);
        $this->execute();
    }

    /**
     * Get submission files
     */
    public function getSubmissionFiles($submission_id)
    {
        $sql = "SELECT * FROM assignment_submission_files 
               WHERE submission_id = :submission_id 
              ORDER BY uploaded_at DESC";

        $this->query($sql);
        $this->bind(':submission_id', $submission_id);
        return $this->fetchMultiple();
    }

    /**
     * Add submission file
     */
    public function addSubmissionFile($submission_id, $file_path, $file_name,$file_size)
    {
        $sql = "INSERT INTO assignment_submission_files 
               (submission_id, file_path, file_name, file_size)
               VALUES (:submission_id, :file_path, :file_name, :file_size)";

        $this->query($sql);
        $this->bind(':submission_id', $submission_id);
        $this->bind(':file_path', $file_path);
        $this->bind(':file_name', $file_name);
        $this->bind(':file_size', $file_size);

        return $this->execute();
    }

    /**
     * Check if assignment is overdue
     */
public function isOverdue($assignment_id): bool
    {
        $sql = "SELECT due_date FROM assignments WHERE id = :assignment_id";
        $this->query($sql);
        $this->bind(':assignment_id', $assignment_id);
        $assignment = $this->fetchSingle();

        if (!$assignment) {
            return false;
        }

        $due_date = new DateTime($assignment->due_date);
        $now = new DateTime();

        return $now > $due_date;
    }

    /**
     * Get assignment statistics
     */
    public function getAssignmentStatistics($assignment_id)
    {
        $sql= "SELECT * FROM assignment_statistics WHERE assignment_id = :assignment_id";
        $this->query($sql);
        $this->bind(':assignment_id', $assignment_id);
        return $this->fetchSingle();
    }

    /**
     * Get teacher's assignments
     */
    public function getTeacherAssignments($teacher_username)
    {
        $sql = "SELECT a.*, s.subject_name, c.name as class_name, sec.name as section_name,
                       stats.submitted_count, stats.graded_count, stats.average_marks,
                       COUNT(DISTINCT asub.id) as total_submissions
                FROM assignments a
                LEFT JOIN subjects s ON a.subject_id = s.id
                LEFT JOIN classes c ON a.class_id = c.id
                LEFT JOIN sections sec ON a.section_id = sec.id
                LEFT JOIN assignment_statistics stats ON a.id = stats.assignment_id
                LEFT JOIN assignment_submissions asub ON a.id = asub.assignment_id
                WHERE a.created_by = :username
                GROUP BY a.id
                ORDER BY a.due_date DESC";

        $this->query($sql);
        $this->bind(':username', $teacher_username);
        return $this->fetchMultiple();
    }

    /**
     * Request extension
    */
    public function requestExtension($submission_id, $student_id, $extension_days, $reason): array
    {
        try {
            $sql = "INSERT INTO assignment_extensions 
                   (submission_id, student_id, extension_days, reason, requested_by, status)
                   VALUES (:submission_id, :student_id, :days, :reason, :requested_by, 'pending')";

            $this->query($sql);
            $this->bind(':submission_id', $submission_id);
            $this->bind(':student_id', $student_id);
            $this->bind(':days', $extension_days);
            $this->bind(':reason', $reason);
            $this->bind(':requested_by', $student_id);

            if ($this->execute()) {
                return ['success' => true, 'message' => 'Extension request submitted successfully'];
            }
            return ['success' => false, 'message' => 'Failed to request extension'];
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    /**
     * Approve extension
     */
    public function approveExtension($extension_id, $approved_by, $comment = ''): array
    {
        try{
            $sql = "UPDATE assignment_extensions SET 
                   status = 'approved',
                   approved_by = :approved_by,
                   approval_comment = :comment,
                   approved_date = NOW()
                   WHERE id = :extension_id";

            $this->query($sql);
            $this->bind(':extension_id', $extension_id);
            $this->bind(':approved_by', $approved_by);
            $this->bind(':comment', $comment);

            if ($this->execute()) {
                return ['success' => true, 'message' => 'Extension approved successfully'];
            }
            return ['success' => false, 'message' =>'Failed to approve extension'];
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    /**
     * Get pending extensions
     */
    public function getPendingExtensions($assignment_id = null)
    {
        $sql = "SELECT ae.*, st.name as student_name, st.registerNO,
                       a.title as assignment_title, a.due_date
                FROM assignment_extensions ae
                LEFT JOIN student st ON ae.student_id = st.studentID
                LEFT JOIN assignment_submissions asub ON ae.submission_id = asub.id
                LEFT JOIN assignments a ON asub.assignment_id = a.id
                WHERE ae.status = 'pending'";

        if ($assignment_id) {
            $sql .= " AND asub.assignment_id = :assignment_id";
        }

        $sql .= " ORDER BY ae.requested_date DESC";

        $this->query($sql);

        if ($assignment_id) {
            $this->bind(':assignment_id', $assignment_id);
        }

        return $this->fetchMultiple();
    }

    public function checkUserType($username){
        //fetch user type
        $sql = "SELECT type FROM admin WHERE username = :username";
        $this->query($sql);
        $this->bind(':username', $username);
        return $this->fetchColumn();
    }
}