<?php

namespace App\Controllers\View;

use App\Controllers\BaseController;
use App\Models\ConfigurationModel;
use App\Models\ClassSectionModel; 

class ScorePredictor extends BaseController
{
    protected $db;
    protected $configModel;
    protected $semester;
    protected $schoolYear;
    protected $classSectionModel;
    protected $studentClassModel;
    protected $teacherModel;
    protected $userModel;
    protected $targetTrophy;
    protected $targetLow;

    public function __construct()
    {
                // Load the database service
        $this->db = \Config\Database::connect();
        // Check if the database connection is established
        if (!$this->db->connect()) {
            log_message('error', 'Database connection failed.');
            throw new \Exception('Database connection failed.');
        } else {
            log_message('info', 'Database connection successful.');
        }

        // Initialize the config model
        $this->configModel = new ConfigurationModel(); 
        $this->classSectionModel = new ClassSectionModel();

        // Retrieve the configuration values
        $this->semester = $this->configModel->getConfig('semester');
        $this->schoolYear = $this->configModel->getConfig('school_year');
        $this->targetTrophy = $this->configModel->getConfig('trophy_score');
        $this->targetLow = $this->configModel->getConfig('pass_score');
    }

    // Calculate the normal cumulative distribution function (CDF)
    public static function normalCDF($z)
    {
        $t = 1 / (1 + 0.3275911 * abs($z));
        $a1 = 0.254829592;
        $a2 = -0.284496736;
        $a3 = 1.421413741;
        $a4 = -1.453152027;
        $a5 = 1.061405429;
        $erf = 1 - ((((($a5 * $t + $a4) * $t + $a3) * $t + $a2) * $t + $a1) * $t) * exp(-$z * $z);
        $sign = $z < 0 ? -1 : 1;
        return 0.5 * (1 + $sign * $erf);
    }

    public function combinedReport()
    {
        $request = service('request');
        $currentSchoolYear = $this->currentSchoolYearName((string) ($this->schoolYear ?? ''));

        $selectedYear = $request->getVar('school_year') ?? $currentSchoolYear;
        $classSectionId = $request->getVar('class_section_id');
        $yearEsc = $this->db->escape($selectedYear);
        $acceptedEnrollmentStatuses = ['payment pending', 'enrolled'];
        // Only show class sections that have at least one student in the selected year
        $classSections = $this->db->table('classSection cs')
            ->select('cs.*')
            ->distinct()
            ->join('student_class sc', 'sc.class_section_id = cs.class_section_id', 'inner')
            ->join('enrollments e', 'e.student_id = sc.student_id AND e.school_year = ' . $yearEsc, 'inner')
            ->where('sc.school_year', $selectedYear)
            ->whereIn('e.enrollment_status', $acceptedEnrollmentStatuses)
            ->where('e.admission_status', 'accepted')
            ->groupStart()
                ->where('e.is_withdrawn', 0)
                ->orWhere('e.is_withdrawn', null)
            ->groupEnd()
            ->groupStart()
                ->where('sc.is_event_only', 0)
                ->orWhere('sc.is_event_only', null)
            ->groupEnd()
            ->notLike('cs.class_section_name', 'KG', 'after')
            ->orderBy('cs.class_section_name', 'ASC')
            ->get()
            ->getResultArray();

        $builder = $this->db->table('students s');
        $builder->select('
        s.id as student_id,
        s.school_id,
        s.firstname,
        s.lastname,
        MAX(fall.semester_score) as fall_score,
        MAX(spring.semester_score) as spring_score');
        // Reduce duplication from restored students while keeping a stable class section.
        $builder->select('MAX(sc.class_section_id) as class_section_id');
        $builder->join('student_class sc', 'sc.student_id = s.id AND sc.school_year = ' . $yearEsc, 'inner');
        $builder->join('enrollments e', 'e.student_id = s.id AND e.school_year = ' . $yearEsc, 'inner');
        $builder->join('classSection cs', 'cs.class_section_id = sc.class_section_id', 'left');
        $builder->join('semester_scores fall', 'fall.student_id = s.id AND fall.semester = "fall" AND fall.school_year = ' . $yearEsc, 'left');
        $builder->join('semester_scores spring', 'spring.student_id = s.id AND spring.semester = "spring" AND spring.school_year = ' . $yearEsc, 'left');
        $builder->where('s.is_active', 1);
        $builder->where('sc.class_section_id IS NOT NULL', null, false);
        $builder->whereIn('e.enrollment_status', $acceptedEnrollmentStatuses);
        $builder->where('e.admission_status', 'accepted');
        $builder->groupStart()
            ->where('e.is_withdrawn', 0)
            ->orWhere('e.is_withdrawn', null)
            ->groupEnd();
        $builder->groupStart()
            ->where('sc.is_event_only', 0)
            ->orWhere('sc.is_event_only', null)
            ->groupEnd();
        $builder->groupStart()
            ->where('cs.class_section_name IS NULL')
            ->orNotLike('cs.class_section_name', 'KG', 'after')
            ->groupEnd();

        if (!empty($classSectionId)) {
            $builder->where('sc.class_section_id', $classSectionId);
        }

        $builder->groupBy('s.id');
        $students = $builder->get()->getResultArray();

        // Stats for spring
        $springStatsQuery = $this->db->table('semester_scores')
            ->select('AVG(semester_scores.semester_score) as mean, STDDEV(semester_scores.semester_score) as std')
            ->join('student_class', 'student_class.student_id = semester_scores.student_id AND student_class.school_year = ' . $yearEsc)
            ->join('enrollments e', 'e.student_id = semester_scores.student_id AND e.school_year = ' . $yearEsc, 'inner')
            ->join('classSection cs', 'cs.class_section_id = student_class.class_section_id', 'left')
            ->where('semester_scores.semester', 'spring')
            ->where('semester_scores.school_year', $selectedYear)
            ->where('student_class.class_section_id IS NOT NULL', null, false)
            ->whereIn('e.enrollment_status', $acceptedEnrollmentStatuses)
            ->where('e.admission_status', 'accepted')
            ->groupStart()
                ->where('e.is_withdrawn', 0)
                ->orWhere('e.is_withdrawn', null)
                ->groupEnd()
            ->groupStart()
                ->where('student_class.is_event_only', 0)
                ->orWhere('student_class.is_event_only', null)
                ->groupEnd()
            ->groupStart()
                ->where('cs.class_section_name IS NULL')
                ->orNotLike('cs.class_section_name', 'KG', 'after')
                ->groupEnd();

        if (!empty($classSectionId)) {
            $springStatsQuery->where('student_class.class_section_id', $classSectionId);
        }

        $springStats = $springStatsQuery->get()->getRow();
        $mean = floatval($springStats->mean ?? 70);
        $std = floatval($springStats->std ?? 0);

        $results = [];
        // Use explicit trophy parameters per new requirements
        // Trophy threshold: minimum 94.0; allow higher configured value if set
        $trophyThreshold = max(94.0, (float)($this->targetTrophy ?? 94.0));
        foreach ($students as $s) {
            $fallScore = is_numeric($s['fall_score']) ? (float)$s['fall_score'] : null;
            $springScore = is_numeric($s['spring_score']) ? (float)$s['spring_score'] : null;

            $scoreParts = array_values(array_filter([$fallScore, $springScore], static function ($v) {
                return $v !== null;
            }));
            $finalAvg = !empty($scoreParts) ? round(array_sum($scoreParts) / count($scoreParts), 1) : null;

            // When we already have a spring score, the year is effectively finished for trophy/pass logic.
            $waitingForSpring = ($fallScore !== null && $springScore === null);

            // Compute required spring score for trophy/pass only if spring is missing.
            $requiredHigh = $waitingForSpring ? round(2 * $trophyThreshold - $fallScore, 2) : null;
            $requiredLow = $waitingForSpring ? round(2 * $this->targetLow - $fallScore, 2) : null;

            $zHigh = ($requiredHigh !== null && $std > 0) ? ($requiredHigh - $mean) / $std : null;
            $zLow = ($requiredLow !== null && $std > 0) ? ($requiredLow - $mean) / $std : null;

            $probHigh = $zHigh !== null ? 1 - self::normalCDF($zHigh) : null;
            $probLow = $zLow !== null ? self::normalCDF($zLow) : null;

            // Trophy determination: only award when the student's final/available average meets the threshold.
            $riskHigh = match (true) {
                $finalAvg !== null && !$waitingForSpring && $finalAvg >= $trophyThreshold => 'Achieved Trophy',
                $finalAvg !== null && !$waitingForSpring => 'Unreachable',
                $requiredHigh !== null && $requiredHigh > 100 => 'Unreachable',
                is_null($probHigh) => 'New student',
                $probHigh >= 0.66 => 'Low',
                $probHigh >= 0.33 => 'Medium',
                default => 'High'
            };

            // Fail risk
            $riskLow = match (true) {
                $finalAvg !== null && !$waitingForSpring && $finalAvg < $this->targetLow => 'High',
                $finalAvg !== null && !$waitingForSpring => 'Low',
                $requiredLow !== null && $requiredLow > 100 => 'Unlikely to pass',
                is_null($probLow) => 'New student',
                $probLow >= 0.66 => 'High',
                $probLow >= 0.33 => 'Medium',
                default => 'Low'
            };

            if ($finalAvg === null) {
                $status = 'Undetermined';
            } elseif ($waitingForSpring) {
                $status = in_array($riskLow, ['High', 'Unlikely to pass'], true) ? 'May fail' : 'Can pass';
            } else {
                $status = $finalAvg >= 60 ? 'Passed' : 'Failed';
            }

            $results[] = [
                'student_id' => (int)($s['student_id'] ?? 0),
                'school_id' => $s['school_id'],
                'firstname' => $s['firstname'],
                'lastname' => $s['lastname'],
                'class_section_id' => $s['class_section_id'] ?? null,
                'fall_score' => $s['fall_score'] ?? 'N/A',
                'spring_score' => $s['spring_score'] ?? 'N/A',
                'final_average' => $finalAvg ?? 'N/A',
                'required_spring_score' => $requiredHigh ?? 'N/A',
                'winning_risk' => $riskHigh,
                'required_spring_to_pass' => $requiredLow ?? 'N/A',
                'failure_risk' => $riskLow,
                'status' => $status,
                'trophy_awarded' => ($riskHigh === 'Achieved Trophy'),
                'trophy_reason' => ($riskHigh === 'Achieved Trophy') ? 'threshold' : '',
            ];
        }

        // If a class section has zero threshold trophies, award trophies to top 3 ranked students
        $bySection = [];
        $hasThresholdTrophy = [];
        foreach ($results as $idx => $row) {
            $cid = (string)($row['class_section_id'] ?? '');
            $bySection[$cid][] = $idx;
            if (($row['trophy_reason'] ?? '') === 'threshold') {
                $hasThresholdTrophy[$cid] = true;
            }
        }
        foreach ($bySection as $cid => $indices) {
            if (!empty($hasThresholdTrophy[$cid])) {
                continue;
            }
            $candidates = [];
            foreach ($indices as $idx) {
                $val = $results[$idx]['final_average'] ?? null;
                if (!is_numeric($val)) {
                    $fall = $results[$idx]['fall_score'] ?? null;
                    $spring = $results[$idx]['spring_score'] ?? null;
                    $parts = [];
                    if (is_numeric($fall)) $parts[] = (float)$fall;
                    if (is_numeric($spring)) $parts[] = (float)$spring;
                    if (!empty($parts)) {
                        $val = array_sum($parts) / count($parts);
                    }
                }
                if (is_numeric($val)) {
                    $candidates[] = ['idx' => $idx, 'avg' => (float)$val];
                }
            }
            if (empty($candidates)) {
                continue;
            }
            usort($candidates, static fn($a, $b) => $b['avg'] <=> $a['avg']);
            $top = array_slice($candidates, 0, 3);
            foreach ($top as $entry) {
                $i = $entry['idx'];
                $results[$i]['trophy_awarded'] = true;
                $results[$i]['trophy_reason'] = 'top3';
                $results[$i]['winning_risk'] = 'Top 3 Trophy';
            }
        }

        // Sort students by final average descending
        usort($results, function ($a, $b) {
            return ($b['final_average'] ?? 0) <=> ($a['final_average'] ?? 0);
        });

        return view('score_analysis/score_prediction', [
            'students' => $results,
            'school_year' => $selectedYear,
            'classSections' => $classSections,
            'selectedClassSectionId' => $classSectionId,
            'semester' => $this->semester,
        ]);
    }

}
