<?php
/**
 * Subject Management Class
 * Handles structured subject selection and combination management
 */

class SubjectManager {
    private $conn;
    private $school_id;
    
    public function __construct($database_connection, $school_id) {
        $this->conn = $database_connection;
        $this->school_id = $school_id;
    }
    
    /**
     * Get all subjects for a school with optional level filter
     */
    public function getSubjects($level = null, $include_papers = false) {
        $sql = "SELECT s.*, 
                (SELECT COUNT(*) FROM subject_papers sp WHERE sp.subject_id = s.subject_id) as paper_count
                FROM subjects s 
                WHERE s.school_id = ?";
        
        $params = [$this->school_id];
        
        if ($level) {
            // Check if subject is applicable for the requested level
            $sql .= " AND (s.applicable_levels LIKE ? OR s.applicable_levels IS NULL)";
            $level_param = "%$level%";
            $params[] = $level_param;
        }
        
        $sql .= " ORDER BY s.subject_code";
        
        if ($stmt = $this->conn->prepare($sql)) {
            $stmt->bind_param("i" . str_repeat("s", count($params) - 1), ...$params);
            $stmt->execute();
            $result = $stmt->get_result();
            
            $subjects = [];
            while ($row = $result->fetch_assoc()) {
                if ($include_papers) {
                    $row['papers'] = $this->getSubjectPapers($row['subject_id']);
                }
                $subjects[] = $row;
            }
            return $subjects;
        }
        return [];
    }
    
    /**
     * Get papers for a specific subject
     */
    public function getSubjectPapers($subject_id) {
        $sql = "SELECT * FROM subject_papers 
                WHERE subject_id = ? 
                ORDER BY paper_name";
        
        if ($stmt = $this->conn->prepare($sql)) {
            $stmt->bind_param("i", $subject_id);
            $stmt->execute();
            $result = $stmt->get_result();
            
            $papers = [];
            while ($row = $result->fetch_assoc()) {
                $papers[] = $row;
            }
            return $papers;
        }
        return [];
    }
    
    /**
     * Get subject combinations templates for the school
     */
    public function getCombinationTemplates($is_a_level = true) {
        // Check if table has is_active column, if not, select all
        $sql = "SHOW COLUMNS FROM subject_combination_templates LIKE 'is_active'";
        $result = $this->conn->query($sql);
        
        if ($result->num_rows > 0) {
            $sql = "SELECT * FROM subject_combination_templates 
                    WHERE (school_id = ? OR school_id IS NULL) AND is_active = 1
                    ORDER BY combination_code";
            if ($stmt = $this->conn->prepare($sql)) {
                $stmt->bind_param("i", $this->school_id);
                $stmt->execute();
                $result = $stmt->get_result();
                
                $templates = [];
                while ($row = $result->fetch_assoc()) {
                    // Parse subject codes to get subject IDs for backward compatibility
                    if ($row['subject_codes']) {
                        $subject_ids = $this->parseSubjectCombination($row['subject_codes']);
                        $row['subject_ids_array'] = $subject_ids;
                    } else {
                        $row['subject_ids_array'] = [];
                    }
                    $templates[] = $row;
                }
                return $templates;
            }
        } else {
            // Table doesn't have is_active column, select all
            $sql = "SELECT * FROM subject_combination_templates 
                    WHERE (school_id = ? OR school_id IS NULL) 
                    ORDER BY combination_code";
            if ($stmt = $this->conn->prepare($sql)) {
                $stmt->bind_param("i", $this->school_id);
                $stmt->execute();
                $result = $stmt->get_result();
                
                $templates = [];
                while ($row = $result->fetch_assoc()) {
                    // Parse subject codes to get subject IDs for backward compatibility
                    if ($row['subject_codes']) {
                        $subject_ids = $this->parseSubjectCombination($row['subject_codes']);
                        $row['subject_ids_array'] = $subject_ids;
                    } else {
                        $row['subject_ids_array'] = [];
                    }
                    $templates[] = $row;
                }
                return $templates;
            }
        }
        return [];
    }
    
    /**
     * Parse subject combination code and return subject details
     */
    public function parseCombinationCode($combination_code) {
        $subjects = [];
        $codes = str_split(strtoupper(trim($combination_code)));
        
        foreach ($codes as $code) {
            // Handle special cases and multi-character codes
            if ($code === '_' || $code === '-') continue;
            
            // Try exact match first
            $subject = $this->getSubjectByCode($code);
            
            // If not found, try fuzzy matching for known combinations
            if (!$subject) {
                $subject = $this->getSubjectByFuzzyCode($code);
            }
            
            if ($subject) {
                $subjects[] = [
                    'subject' => $subject,
                    'code' => $code,
                    'papers' => $this->getSubjectPapers($subject['subject_id'])
                ];
            }
        }
        
        return $subjects;
    }
    
    /**
     * Get subject by exact code
     */
    public function getSubjectByCode($code) {
        $sql = "SELECT * FROM subjects 
                WHERE subject_code = ? AND school_id = ?";
        
        if ($stmt = $this->conn->prepare($sql)) {
            $stmt->bind_param("si", $code, $this->school_id);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($row = $result->fetch_assoc()) {
                return $row;
            }
        }
        return null;
    }
    
    /**
     * Fuzzy matching for subject codes
     */
    private function getSubjectByFuzzyCode($code) {
        // Common substitutions and mappings
        $mappings = [
            'P' => 'PHY_A', // Physics
            'M' => 'MATH_A', // Mathematics  
            'C' => 'CHEM_A', // Chemistry
            'B' => 'BIO_A', // Biology
            'H' => 'HIST_A', // History
            'E' => 'ECON', // Economics
            'G' => 'GEO_A', // Geography
            'L' => 'LIT_A' // Literature
        ];
        
        $mapped_code = $mappings[$code] ?? $code;
        return $this->getSubjectByCode($mapped_code);
    }
    
    /**
     * Assign subjects to a student using student_subject_choices table
     */
    public function assignSubjectsToStudent($student_adm, $subject_ids, $class_id, $stream_id, $academic_year, $term) {
        try {
            $this->conn->begin_transaction();
            
            // Remove existing subject assignments for this student
            $sql_delete = "DELETE FROM student_subject_choices WHERE student_ADM = ? AND school_id = ?";
            if ($stmt = $this->conn->prepare($sql_delete)) {
                $stmt->bind_param("si", $student_adm, $this->school_id);
                $stmt->execute();
            }
            
            // Insert new subject assignments
            $sql_insert = "INSERT INTO student_subject_choices (student_ADM, subject_id, class_id, stream_id, school_id, academic_year, term) 
                          VALUES (?, ?, ?, ?, ?, ?, ?)";
            
            if ($stmt = $this->conn->prepare($sql_insert)) {
                foreach ($subject_ids as $subject_id) {
                    $stmt->bind_param("siiiiss", $student_adm, $subject_id, $class_id, $stream_id, $this->school_id, $academic_year, $term);
                    if (!$stmt->execute()) {
                        throw new Exception("Failed to assign subject ID: $subject_id");
                    }
                }
            }
            
            $this->conn->commit();
            return true;
            
        } catch (Exception $e) {
            $this->conn->rollback();
            error_log("Subject Assignment Error: " . $e->getMessage());
            return false;
        }
    }
    
    /**
     * Get student's subjects with papers for report generation
     */
    public function getStudentSubjectsForReport($student_adm) {
        $sql = "SELECT 
                    s.subject_id,
                    s.subject_code,
                    s.subject_name,
                    s.applicable_levels,
                    s.subject_category,
                    ss.date_assigned
                FROM student_subject_choices ss
                JOIN subjects s ON ss.subject_id = s.subject_id
                LEFT JOIN subject_papers p ON s.subject_id = p.subject_id
                WHERE ss.student_ADM = ? AND ss.school_id = ?
                GROUP BY s.subject_id, s.subject_code, s.subject_name, s.applicable_levels, s.subject_category, ss.date_assigned
                ORDER BY s.subject_code";
        
        if ($stmt = $this->conn->prepare($sql)) {
            $stmt->bind_param("si", $student_adm, $this->school_id);
            $stmt->execute();
            $result = $stmt->get_result();
            
            $subjects = [];
            while ($row = $result->fetch_assoc()) {
                // Get papers for this subject
                $row['papers'] = $this->getSubjectPapers($row['subject_id']);
                $subjects[] = $row;
            }
            return $subjects;
        }
        return [];
    }
    
    /**
     * Generate subject combination from selected subjects
     */
    public function generateCombinationCode($subject_ids) {
        $codes = [];
        
        // Get subject codes for selected subjects
        $placeholders = str_repeat('?,', count($subject_ids) - 1) . '?';
        $sql = "SELECT subject_code FROM subjects 
                WHERE subject_id IN ($placeholders) AND school_id = ?";
        
        if ($stmt = $this->conn->prepare($sql)) {
            $params = array_merge($subject_ids, [$this->school_id]);
            $stmt->bind_param(str_repeat('i', count($subject_ids)) . 'i', ...$params);
            $stmt->execute();
            $result = $stmt->get_result();
            
            while ($row = $result->fetch_assoc()) {
                $codes[] = $row['subject_code'];
            }
        }
        
        // Convert to combination format (e.g., PHY_A, MATH_A, CHEM_A -> PCM)
        return $this->convertToCombinationFormat($codes);
    }
    
    /**
     * Convert subject codes to combination format
     */
    private function convertToCombinationFormat($codes) {
        $combination_map = [
            'PHY_A' => 'P',
            'MATH_A' => 'M', 
            'CHEM_A' => 'C',
            'BIO_A' => 'B',
            'HIST_A' => 'H',
            'ECON' => 'E',
            'GEO_A' => 'G',
            'LIT_A' => 'L',
            'PHIL' => 'Ph',
            'PSYC' => 'Psy',
            'DIV' => 'D',
            'FREN' => 'F'
        ];
        
        $combination = '';
        foreach ($codes as $code) {
            $combination .= $combination_map[$code] ?? substr($code, 0, 3);
        }
        
        return $combination;
    }
    
    /**
     * Validate subject combination
     */
    public function validateCombination($subject_ids, $level = 'S5') {
        $errors = [];
        
        if ($level === 'S5' || $level === 'S6') {
            // A-Level should have 2-3 subjects
            if (count($subject_ids) < 2 || count($subject_ids) > 3) {
                $errors[] = "A-Level combinations must have 2-3 subjects";
            }
        }
        
        // Check if subjects exist and are available for the level
        foreach ($subject_ids as $subject_id) {
            $subject = $this->getSubjectById($subject_id);
            if (!$subject) {
                $errors[] = "Subject ID $subject_id not found";
            } elseif ($subject['applicable_levels'] && !str_contains($subject['applicable_levels'], $level)) {
                $errors[] = "Subject {$subject['subject_name']} is not available for {$level}";
            }
        }
        
        return $errors;
    }
    
    /**
     * Get subject by ID
     */
    public function getSubjectById($subject_id) {
        $sql = "SELECT * FROM subjects 
                WHERE subject_id = ? AND school_id = ?";
        
        if ($stmt = $this->conn->prepare($sql)) {
            $stmt->bind_param("ii", $subject_id, $this->school_id);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($row = $result->fetch_assoc()) {
                return $row;
            }
        }
        return null;
    }
    
    /**
     * Bulk assign subjects from CSV data
     */
    public function assignSubjectsFromCSV($combination_text, $student_adm, $class_id, $stream_id, $academic_year, $term, $level = 'S5') {
        if (empty(trim($combination_text))) {
            return true; // No combination specified, skip
        }
        
        // Parse combination to get subject IDs
        $parsed_subjects = $this->parseCombinationCode($combination_text);
        $subject_ids = array_column($parsed_subjects, 'subject_id');
        
        if (empty($subject_ids)) {
            return false;
        }
        
        // Validate the combination
        $errors = $this->validateCombination($subject_ids, $level);
        if (!empty($errors)) {
            throw new Exception("Subject combination error: " . implode(', ', $errors));
        }
        
        // Assign subjects to student
        return $this->assignSubjectsToStudent($student_adm, $subject_ids, $class_id, $stream_id, $academic_year, $term);
    }

    /**
     * Parse subject combination string and return subject IDs
     * @param string $combination Subject combination like "PCM"
     * @return array Array of subject IDs
     */
    public function parseSubjectCombination($combination) {
        $subject_ids = [];
        
        // Remove spaces and convert to uppercase
        $combination = strtoupper(str_replace(' ', '', $combination));
        
        if (empty($combination)) {
            return $subject_ids;
        }
        
        // Get all subjects from database
        $sql = "SELECT subject_id, subject_code FROM subjects ORDER BY subject_code";
        $result = $this->conn->query($sql);
        
        if (!$result) {
            return $subject_ids;
        }
        
        $subjects_map = [];
        while ($row = $result->fetch_assoc()) {
            $subjects_map[$row['subject_code']] = $row['subject_id'];
        }
        
        // Parse each character in the combination
        for ($i = 0; $i < strlen($combination); $i++) {
            $code = $combination[$i];
            if (isset($subjects_map[$code])) {
                $subject_ids[] = $subjects_map[$code];
            }
        }
        
        return $subject_ids;
    }
}

/**
 * Subject Parser Class - Simplified version for admin interface compatibility
 */
class SubjectParser {
    private $conn;
    
    public function __construct($database_connection) {
        $this->conn = $database_connection;
    }
    
    /**
     * Parse subject combination string and return subject IDs
     * @param string $combination Subject combination like "PCM"
     * @return array Array of subject IDs
     */
    public function parseSubjectCombination($combination) {
        $subject_ids = [];
        
        // Remove spaces and convert to uppercase
        $combination = strtoupper(str_replace(' ', '', $combination));
        
        if (empty($combination)) {
            return $subject_ids;
        }
        
        // Get all subjects from database
        $sql = "SELECT subject_id, subject_code FROM subjects ORDER BY subject_code";
        $result = $this->conn->query($sql);
        
        if (!$result) {
            return $subject_ids;
        }
        
        $subjects_map = [];
        while ($row = $result->fetch_assoc()) {
            $subjects_map[$row['subject_code']] = $row['subject_id'];
        }
        
        // Parse each character in the combination
        for ($i = 0; $i < strlen($combination); $i++) {
            $code = $combination[$i];
            if (isset($subjects_map[$code])) {
                $subject_ids[] = $subjects_map[$code];
            }
        }
        
        return $subject_ids;
    }
    
    /**
     * Validate subject codes
     * @param string $combination Subject combination string
     * @return array Valid and invalid subject codes
     */
    public function validateSubjectCodes($combination) {
        $valid = [];
        $invalid = [];
        
        $combination = strtoupper(str_replace(' ', '', $combination));
        
        if (empty($combination)) {
            return ['valid' => $valid, 'invalid' => $invalid];
        }
        
        // Get valid subject codes from database
        $sql = "SELECT DISTINCT subject_code FROM subjects WHERE subject_code IS NOT NULL ORDER BY subject_code";
        $result = $this->conn->query($sql);
        
        if (!$result) {
            return ['valid' => $valid, 'invalid' => $invalid];
        }
        
        $valid_codes = [];
        while ($row = $result->fetch_assoc()) {
            $valid_codes[] = $row['subject_code'];
        }
        
        // Check each character in combination
        for ($i = 0; $i < strlen($combination); $i++) {
            $code = $combination[$i];
            if (in_array($code, $valid_codes)) {
                $valid[] = $code;
            } else {
                $invalid[] = $code;
            }
        }
        
        return ['valid' => $valid, 'invalid' => $invalid];
    }
    
    /**
     * Get all subjects for display
     */
    public function getSubjectsForDisplay() {
        $sql = "SELECT subject_id, subject_name, subject_code FROM subjects ORDER BY subject_code";
        $result = $this->conn->query($sql);
        
        $subjects = [];
        while ($row = $result->fetch_assoc()) {
            $subjects[] = [
                'id' => $row['subject_id'],
                'name' => $row['subject_name'],
                'code' => $row['subject_code']
            ];
        }
        
        return $subjects;
    }
    
    /**
     * Get all combination templates
     */
    public function getCombinationTemplates() {
        $sql = "SELECT * FROM subject_combination_templates ORDER BY combination_code";
        $result = $this->conn->query($sql);
        
        $combinations = [];
        while ($row = $result->fetch_assoc()) {
            // Parse subject codes to get subject IDs for backward compatibility
            if ($row['subject_codes']) {
                $subject_ids = $this->parseSubjectCombination($row['subject_codes']);
                $row['subject_ids_array'] = $subject_ids;
            } else {
                $row['subject_ids_array'] = [];
            }
            $combinations[] = $row;
        }
        
        return $combinations;
    }
    
    /**
     * Initialize default combinations
     */
    public function initializeDefaultCombinations() {
        // Common A-Level combinations for Uganda
        $default_combinations = [
            'PCM' => 'Physics, Chemistry, Mathematics',
            'HEG' => 'History, Economics, Geography', 
            'BCG' => 'Biology, Chemistry, Geography',
            'PCG' => 'Physics, Chemistry, Geography',
            'MEG' => 'Mathematics, Economics, Geography',
            'ACG' => 'Art, Chemistry, Geography',
            'PEM' => 'Physics, Economics, Mathematics',
            'AEG' => 'Art, Economics, Geography',
            'PEQ' => 'Physics, Economics, Literature in English'
        ];
        
        foreach ($default_combinations as $code => $name) {
            // Check if this combination already exists
            $check_sql = "SELECT combination_id FROM subject_combination_templates WHERE combination_code = ? AND school_id IS NULL";
            $check_stmt = $this->conn->prepare($check_sql);
            $check_stmt->bind_param("s", $code);
            $check_stmt->execute();
            $check_result = $check_stmt->get_result();
            
            if ($check_result->num_rows == 0) {
                $this->createCombinationTemplate($code, $name, $code, $name, NULL);
            }
        }
        
        return true;
    }
    
    /**
     * Create combination template
     */
    public function createCombinationTemplate($code, $name, $subject_codes, $subject_names, $school_id = null) {
        $sql = "INSERT INTO subject_combination_templates (combination_code, combination_name, subject_codes, subject_names, school_id, is_active) VALUES (?, ?, ?, ?, ?, 1)";
        $stmt = $this->conn->prepare($sql);
        $stmt->bind_param("ssssi", $code, $name, $subject_codes, $subject_names, $school_id);
        return $stmt->execute();
    }
}

/**
 * Report Card Generator Class
 */
class ReportCardGenerator {
    private $conn;
    private $school_id;
    
    public function __construct($database_connection, $school_id) {
        $this->conn = $database_connection;
        $this->school_id = $school_id;
    }
    
    /**
     * Generate comprehensive report card data for a student
     */
    public function generateReportCard($student_adm, $term = null, $year = null) {
        $subject_manager = new SubjectManager($this->conn, $this->school_id);
        
        // Get basic student information
        $student_info = $this->getStudentBasicInfo($student_adm);
        if (!$student_info) {
            throw new Exception("Student not found");
        }
        
        // Get student's subjects with papers
        $subjects = $subject_manager->getStudentSubjectsForReport($student_adm);
        
        // Organize subjects by applicable levels
        $o_level_subjects = array_filter($subjects, function($subject) {
            return $subject['applicable_levels'] && str_contains($subject['applicable_levels'], 'S4');
        });
        
        $a_level_subjects = array_filter($subjects, function($subject) {
            return $subject['applicable_levels'] && (str_contains($subject['applicable_levels'], 'S5') || str_contains($subject['applicable_levels'], 'S6'));
        });
        
        return [
            'student_info' => $student_info,
            'o_level_subjects' => $o_level_subjects,
            'a_level_subjects' => $a_level_subjects,
            'total_subjects' => count($subjects),
            'generated_date' => date('Y-m-d H:i:s'),
            'term' => $term,
            'year' => $year
        ];
    }
    
    /**
     * Get basic student information for reports
     */
    private function getStudentBasicInfo($student_adm) {
        $sql = "SELECT 
                    u.id,
                    u.ADM,
                    u.username,
                    u.email,
                    u.phone_number,
                    u.parent_name,
                    u.parent_phone,
                    c.class_name,
                    s.school_name,
                    u.status
                FROM users u
                JOIN classes c ON u.class_id = c.class_id
                JOIN schools s ON u.school_id = s.school_id
                WHERE u.ADM = ? AND u.school_id = ? AND u.role = 'student'";
        
        if ($stmt = $this->conn->prepare($sql)) {
            $stmt->bind_param("si", $student_adm, $this->school_id);
            $stmt->execute();
            $result = $stmt->get_result();
            
            if ($row = $result->fetch_assoc()) {
                return $row;
            }
        }
        return null;
    }
    
    /**
     * Generate subject-wise report data with grades
     */
    public function getSubjectGrades($student_adm, $term = null, $year = null) {
        // This would integrate with your existing grades/exams system
        // For now, return structure for grades data
        
        $subject_manager = new SubjectManager($this->conn, $this->school_id);
        $subjects = $subject_manager->getStudentSubjectsForReport($student_adm);
        
        $subject_grades = [];
        foreach ($subjects as $subject) {
            $subject_grades[] = [
                'subject' => $subject,
                'grades' => $this->getGradesForSubject($subject['subject_id'], $student_adm, $term, $year)
            ];
        }
        
        return $subject_grades;
    }
    
    /**
     * Get grades for a specific subject (placeholder - integrate with existing system)
     */
    private function getGradesForSubject($subject_id, $student_adm, $term, $year) {
        // This would query your existing grades table
        // Returning structure for now
        return [
            'overall_grade' => 'A',
            'grade_points' => 5.0,
            'papers' => [
                [
                    'paper_name' => 'Subject Paper 1',
                    'score' => 85,
                    'max_marks' => 100,
                    'grade' => 'A'
                ],
                [
                    'paper_name' => 'Subject Paper 2', 
                    'score' => 88,
                    'max_marks' => 100,
                    'grade' => 'A'
                ]
            ]
        ];
    }
}

?>