<?php
/**
 * E-Learning Management Class
 * Handles all e-learning material operations
 */
//require_once 'includes/database.php';
class ELearning extends Database
{
    /**
     * Add new learning material
     */
    public function addMaterial($data): array
    {
        try {
            $sql = "INSERT INTO learning_materials 
                    (title, description, material_type, file_path, external_link, 
                     class_id, section_id, subject_id, term_id, uploaded_by, 
                     uploader_type, file_size, duration, status) 
                    VALUES 
                    (:title, :description, :material_type, :file_path, :external_link, 
                     :class_id, :section_id, :subject_id, :term_id, :uploaded_by, 
                     :uploader_type, :file_size, :duration, :status)";

            $this->query($sql);
            $this->bind(':title', $data['title']);
            $this->bind(':description', $data['description']);
            $this->bind(':material_type', $data['material_type']);
            $this->bind(':file_path', $data['file_path']);
            $this->bind(':external_link', $data['external_link']);
            $this->bind(':class_id', $data['class_id']);
            $this->bind(':section_id', $data['section_id']);
            $this->bind(':subject_id', $data['subject_id']);
            $this->bind(':term_id', $data['term_id']);
            $this->bind(':uploaded_by', $data['uploaded_by']);
            $this->bind(':uploader_type', $data['uploader_type']);
            $this->bind(':file_size', $data['file_size']);
            $this->bind(':duration', $data['duration']);
            $this->bind(':status', $data['status']);

            if ($this->execute()) {
                return [
                    'success' => true,
                    'message' => 'Material added successfully',
                    'material_id' => $this->lastInserted()
                ];
            }
            return ['success' => false, 'message' => 'Failed to add material'];
        } catch (Exception $e) {
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    /**
     * Get materials for specific class, section, and term
     */
    public function getMaterialsByClass($class_id, $section_id, $term_id, $subject_id = null)
    {
        $sql = "SELECT lm.*, s.subject_name, c.name as class_name, sec.name as section_name,
                       (SELECT COUNT(*) FROM material_comments WHERE material_id = lm.id AND status = 'active') as comments_count,
                       (SELECT AVG(rating) FROM material_ratings WHERE material_id = lm.id) as avg_rating
                FROM learning_materials lm
                LEFT JOIN subjects s ON lm.subject_id = s.id
                LEFT JOIN classes c ON lm.class_id = c.id
                LEFT JOIN sections sec ON lm.section_id = sec.id
                WHERE lm.class_id = :class_id 
                AND lm.section_id = :section_id 
                AND lm.term_id = :term_id 
                AND lm.status = 'active'";

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

        $sql .= " ORDER BY lm.created_at DESC";

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

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

        return $this->fetchMultiple();
    }

    /**
     * Get student's materials with progress
     */
    public function getStudentMaterials($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 [];
        }

        //fetch current term from general_settings
        $sql = "SELECT active_term FROM general_settings";
        $this->query($sql);
        $activeTerm = $this->fetchColumn();

        // Get current term
        $term_sql = "SELECT id FROM terms WHERE name = :active_term LIMIT 1";
        $this->query($term_sql);
        $this->bind(':active_term', $activeTerm);
        $term = $this->fetchSingle();

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

        $sql = "SELECT lm.*, s.subject_name, 
                       COALESCE(smp.status, 'not_started') as progress_status,
                       COALESCE(smp.progress_percentage, 0) as progress,
                       smp.last_accessed,
                       (SELECT AVG(rating) FROM material_ratings WHERE material_id = lm.id) as avg_rating,
                       (SELECT COUNT(*) FROM material_comments WHERE material_id = lm.id AND status = 'active') as comments_count
                FROM learning_materials lm
                LEFT JOIN subjects s ON lm.subject_id = s.id
                LEFT JOIN student_material_progress smp ON lm.id = smp.material_id AND smp.student_id = :student_id
                WHERE lm.class_id = :class_id 
                AND lm.section_id = :section_id 
                AND lm.term_id = :term_id
                AND lm.status = 'active'
                ORDER BY lm.subject_id, lm.created_at DESC";

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

        return $this->fetchMultiple();
    }

    /**
     * Update student material progress
     */
    public function updateProgress($student_id, $material_id, $progress_data): array
    {
        // Check if progress record exists
        $check_sql = "SELECT id FROM student_material_progress 
                      WHERE student_id = :student_id AND material_id = :material_id";
        $this->query($check_sql);
        $this->bind(':student_id', $student_id);
        $this->bind(':material_id', $material_id);
        $existing = $this->fetchSingle();

        if ($existing) {
            // Update existing record
            $sql = "UPDATE student_material_progress 
                    SET status = :status, 
                        progress_percentage = :progress, 
                        time_spent = time_spent + :time_spent,
                        last_accessed = NOW(),
                        completed_at = :completed_at
                    WHERE student_id = :student_id AND material_id = :material_id";
        } else {
            // Insert new record
            $sql = "INSERT INTO student_material_progress 
                    (student_id, material_id, status, progress_percentage, time_spent, last_accessed, completed_at)
                    VALUES (:student_id, :material_id, :status, :progress, :time_spent, NOW(), :completed_at)";
        }

        $this->query($sql);
        $this->bind(':student_id', $student_id);
        $this->bind(':material_id', $material_id);
        $this->bind(':status', $progress_data['status']);
        $this->bind(':progress', $progress_data['progress_percentage']);
        $this->bind(':time_spent', $progress_data['time_spent']);
        $this->bind(':completed_at', $progress_data['status'] == 'completed' ? date('Y-m-d H:i:s') : null);

        if ($this->execute()) {
            // Update view count
            $update_views = "UPDATE learning_materials SET views_count = views_count + 1 WHERE id = :material_id";
            $this->query($update_views);
            $this->bind(':material_id', $material_id);
            $this->execute();

            return ['success' => true, 'message' => 'Progress updated successfully'];
        }

        return ['success' => false, 'message' => 'Failed to update progress'];
    }

    /**
     * Get material details by ID
     */
    public function getMaterialById($material_id)
    {
        $sql = "SELECT lm.*, s.subject_name, c.name as class_name, sec.name as section_name, t.name as term_name,
                       (SELECT AVG(rating) FROM material_ratings WHERE material_id = lm.id) as avg_rating,
                       (SELECT COUNT(*) FROM material_ratings WHERE material_id = lm.id) as ratings_count
                FROM learning_materials lm
                LEFT JOIN subjects s ON lm.subject_id = s.id
                LEFT JOIN classes c ON lm.class_id = c.id
                LEFT JOIN sections sec ON lm.section_id = sec.id
                LEFT JOIN terms t ON lm.term_id = t.id
                WHERE lm.id = :material_id";

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

    /**
     * Delete material
     */
    public function deleteMaterial($material_id, $file_path = null): array
    {
        try {
            // Delete file if exists
            if ($file_path && file_exists($file_path)) {
                unlink($file_path);
            }

            $sql = "DELETE FROM learning_materials WHERE id = :material_id";
            $this->query($sql);
            $this->bind(':material_id', $material_id);

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

    /**
     * Update material
     */
    public function updateMaterial($material_id, $data): array
    {
        $sql = "UPDATE learning_materials 
                SET title = :title, 
                    description = :description, 
                    material_type = :material_type,
                    external_link = :external_link,
                    subject_id = :subject_id,
                    term_id = :term_id,
                    status = :status,
                    duration = :duration
                WHERE id = :material_id";

        $this->query($sql);
        $this->bind(':material_id', $material_id);
        $this->bind(':title', $data['title']);
        $this->bind(':description', $data['description']);
        $this->bind(':material_type', $data['material_type']);
        $this->bind(':external_link', $data['external_link']);
        $this->bind(':subject_id', $data['subject_id']);
        $this->bind(':term_id', $data['term_id']);
        $this->bind(':status', $data['status']);
        $this->bind(':duration', $data['duration']);

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

    /**
     * Add material comment
     */
    public function addComment($material_id, $user_id, $user_type, $comment_text, $parent_id = null): array
    {
        $sql = "INSERT INTO material_comments (material_id, user_id, user_type, comment_text, parent_comment_id)
                VALUES (:material_id, :user_id, :user_type, :comment_text, :parent_id)";

        $this->query($sql);
        $this->bind(':material_id', $material_id);
        $this->bind(':user_id', $user_id);
        $this->bind(':user_type', $user_type);
        $this->bind(':comment_text', $comment_text);
        $this->bind(':parent_id', $parent_id);

        if ($this->execute()) {
            return ['success' => true, 'message' => 'Comment added successfully'];
        }
        return ['success' => false, 'message' => 'Failed to add comment'];
    }

    /**
     * Get material comments
     */
    public function getMaterialComments($material_id)
    {
        $sql = "SELECT mc.*, 
                       CASE 
                           WHEN mc.user_type = 'student' THEN s.name
                           WHEN mc.user_type = 'teacher' THEN t.name
                           ELSE a.username
                       END as user_name,
                       mc.user_type
                FROM material_comments mc
                LEFT JOIN student s ON mc.user_id = s.studentID AND mc.user_type = 'student'
                LEFT JOIN teachers t ON mc.user_id = t.id AND mc.user_type = 'teacher'
                LEFT JOIN admin a ON mc.user_id = a.id AND mc.user_type = 'admin'
                WHERE mc.material_id = :material_id AND mc.status = 'active'
                ORDER BY mc.created_at DESC";

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

    /**
     * Rate material
     */
    public function rateMaterial($material_id, $student_id, $rating, $review_text = ''): array
    {
        // Check if rating exists
        $check_sql = "SELECT id FROM material_ratings WHERE material_id = :material_id AND student_id = :student_id";
        $this->query($check_sql);
        $this->bind(':material_id', $material_id);
        $this->bind(':student_id', $student_id);
        $existing = $this->fetchSingle();

        if ($existing) {
            $sql = "UPDATE material_ratings SET rating = :rating, review_text = :review_text WHERE id = :id";
            $this->query($sql);
            $this->bind(':id', $existing->id);
        } else {
            $sql = "INSERT INTO material_ratings (material_id, student_id, rating, review_text)
                    VALUES (:material_id, :student_id, :rating, :review_text)";
            $this->query($sql);
            $this->bind(':material_id', $material_id);
            $this->bind(':student_id', $student_id);
        }

        $this->bind(':rating', $rating);
        $this->bind(':review_text', $review_text);

        if ($this->execute()) {
            return ['success' => true, 'message' => 'Rating submitted successfully'];
        }
        return ['success' => false, 'message' => 'Failed to submit rating'];
    }

    /**
     * Get teacher's uploaded materials
     */
    public function getTeacherMaterials($teacher_username)
    {
        $sql = "SELECT lm.*, s.subject_name, c.name as class_name, sec.name as section_name,
                       COUNT(DISTINCT smp.student_id) as students_accessed,
                       AVG(smp.progress_percentage) as avg_progress
                FROM learning_materials lm
                LEFT JOIN subjects s ON lm.subject_id = s.id
                LEFT JOIN classes c ON lm.class_id = c.id
                LEFT JOIN sections sec ON lm.section_id = sec.id
                LEFT JOIN student_material_progress smp ON lm.id = smp.material_id
                WHERE lm.uploaded_by = :username
                GROUP BY lm.id
                ORDER BY lm.created_at DESC";

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

    /**
     * Get material statistics for admin dashboard
     */
    public function getMaterialStatistics()
    {
        $sql = "SELECT 
                    COUNT(*) as total_materials,
                    SUM(CASE WHEN material_type = 'video' THEN 1 ELSE 0 END) as videos,
                    SUM(CASE WHEN material_type = 'pdf' THEN 1 ELSE 0 END) as pdfs,
                    SUM(CASE WHEN material_type = 'document' THEN 1 ELSE 0 END) as documents,
                    SUM(views_count) as total_views,
                    (SELECT COUNT(DISTINCT student_id) FROM student_material_progress) as students_engaged
                FROM learning_materials WHERE status = 'active'";

        $this->query($sql);
        return $this->fetchSingle();
    }

    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();
    }
}