<?php

namespace App\Controllers\View;

use App\Models\ProjectModel;
use App\Models\StudentModel;
use App\Models\StudentClassModel;
use App\Controllers\BaseController;
use App\Models\TeacherClassModel;
use App\Models\ConfigurationModel;
use RuntimeException;
use App\Services\SemesterScoreService;
use Config\Services;
use App\Models\GradingLockModel;
use App\Models\MissingScoreOverrideModel;


class ProjectController extends BaseController
{
    protected $db;
    protected ConfigurationModel $configModel;
    protected string $schoolYear;
    protected $semesterScoreService;
    protected $gradingLockModel;
    protected $missingScoreOverrideModel;

    public function __construct()
    {
        $this->semesterScoreService = service('semesterScoreService');
        $this->db = \Config\Database::connect();
        $this->configModel = new ConfigurationModel();
        $this->schoolYear = (string) ($this->configModel->getConfig('school_year') ?? '');
        $this->gradingLockModel = new GradingLockModel();
        $this->missingScoreOverrideModel = new MissingScoreOverrideModel();
    }

    public function addProject()
    {
        $updatedBy = session()->get('user_id');
        $teacherClassModel = new TeacherClassModel();
        $studentClassModel = new StudentClassModel();
        $studentModel = new StudentModel();
        $projectModel = new ProjectModel();

        $semester = $this->getTeacherSelectedSemester();
        $schoolYear = $this->getTeacherSchoolYear();
        $classSectionId = (int) (
            $this->request->getGet('class_section_id')
            ?? $this->request->getPost('class_section_id')
            ?? 0
        );
        if ($classSectionId <= 0) {
            $teacherClass = $teacherClassModel
                ->where('teacher_id', $updatedBy)
                ->where('school_year', $schoolYear)
                ->where('semester', $semester)
                ->first();
            if (!$teacherClass) {
                return redirect()->back()->with('status', 'No class section found for the current teacher.');
            }
            $classSectionId = (int) $teacherClass['class_section_id'];
        }

        session()->set('class_section_id', $classSectionId);

        $projectRows = $projectModel
            ->select('id, project_index')
            ->where('class_section_id', $classSectionId)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->orderBy('project_index', 'ASC')
            ->findAll();

        $projectHeaders = [];
        foreach ($projectRows as $row) {
            $projectHeaders[$row['project_index']][] = $row['id'];
        }

        $headerLabels = array_keys($projectHeaders);

        $studentsClasses = $studentClassModel
            ->active()
            ->where('student_class.class_section_id', $classSectionId)
            ->where('student_class.school_year', $schoolYear)
            ->findAll();
        $students = [];

        foreach ($studentsClasses as $studentClass) {
            $studentId = $studentClass['student_id'];
            $student = $studentModel
                ->where('id', $studentId)
                ->where('is_active', 1)
                ->first();

            if ($student) {
                $scores = [];
                foreach ($projectHeaders as $index => $ids) {
                    $entry = $projectModel
                        ->where('student_id', $studentId)
                        ->where('project_index', $index)
                        ->where('class_section_id', $classSectionId)
                        ->where('semester', $semester)
                        ->where('school_year', $schoolYear)
                        ->first();

                    $scores[$index] = $entry['score'] ?? '';
                }

                $students[] = [
                    'student_id' => $studentId,
                    'firstname'  => $student['firstname'],
                    'lastname'   => $student['lastname'],
                    'scores'     => $scores
                ];
            }
        }

        usort($students, fn($a, $b) => strcmp($a['lastname'], $b['lastname']) ?: strcmp($a['firstname'], $b['firstname']));

        return view('/teacher/add_project', [
            'students' => $students,
            'projectHeaders' => $headerLabels,
            'semester' => $semester,
            'schoolYear' => $schoolYear,
            'missingOkMap' => $this->missingScoreOverrideModel->getOverridesMap($classSectionId, $semester, $schoolYear, 'project'),
        ]);
    }

    public function updateProjectScores(?array $scores = null, ?int $updatedBy = null, ?int $classSectionId = null)
    {
        $scores = $this->request->getPost('scores');
        $projectModel = new ProjectModel();
        $studentModel = new StudentModel();

        if ($updatedBy === null) {
            $updatedBy = session()->get('user_id');
        }
        $classSectionId = session()->get('class_section_id');
        $semester = $this->getTeacherSelectedSemester();
        $schoolYear = $this->getTeacherSchoolYear();
        $this->assertSchoolYearWritable($this->resolveSchoolYearContext());
        $classSectionId = (int) ($classSectionId ?? 0);
        if ($classSectionId > 0 && $this->isScoresLocked($classSectionId, $semester, $schoolYear)) {
            return redirect()->back()->with('error', 'Scores are locked for this class. Unlock to edit.');
        }

        if (!is_array($scores)) {
            return redirect()->back()->with('error', 'No project scores submitted.');
        }

        $missingOk = $this->request->getPost('missing_ok') ?? [];
        $studentIds = array_keys($scores);
        $indexSet = [];
        foreach ($scores as $studentId => $projects) {
            if (!is_array($projects)) {
                continue;
            }
            foreach ($projects as $index => $score) {
                if (is_numeric($index)) {
                    $indexSet[(int)$index] = true;
                }
            }
        }
        $checkedItems = [];
        if (is_array($missingOk)) {
            foreach ($missingOk as $studentId => $indexes) {
                if (!is_array($indexes)) {
                    continue;
                }
                foreach ($indexes as $index => $flag) {
                    if (!is_numeric($index)) {
                        continue;
                    }
                    $checkedItems[] = [
                        'student_id' => (int) $studentId,
                        'item_index' => (int) $index,
                    ];
                }
            }
        }
        $this->missingScoreOverrideModel->replaceOverrides(
            $classSectionId,
            (string) $semester,
            (string) $schoolYear,
            'project',
            $studentIds,
            array_keys($indexSet),
            $checkedItems,
            $updatedBy
        );

        foreach ($scores as $studentId => $projects) {
            if (!is_numeric($studentId)) continue;

            $student = $studentModel->find($studentId);
            if (!$student) continue;

            foreach ($projects as $index => $score) {
                $normalizedScore = is_numeric($score) ? (float) $score : null;

                $existing = $projectModel->where([
                    'student_id' => $studentId,
                    'project_index' => $index,
                    'class_section_id' => $classSectionId,
                    'semester' => $semester,
                    'school_year' => $schoolYear,
                ])->first();

                $data = [
                    'student_id' => $studentId,
                    'school_id' => $student['school_id'],
                    'class_section_id' => $classSectionId,
                    'updated_by' => $updatedBy,
                    'project_index' => $index,
                    'score' => $normalizedScore,
                    'semester' => $semester,
                    'school_year' => $schoolYear,
                    'updated_at' => utc_now(),
                ];

                if ($existing) {
                    $projectModel->update($existing['id'], $data);
                } else {
                    $data['created_at'] = utc_now();
                    $projectModel->insert($data);
                }
            }
        }

        $studentTeacherInfo = $studentModel->getStudentInfoByClassSectionId($classSectionId, $semester, $schoolYear);
        // Call the updateScoresForStudents method
        try {

            $this->semesterScoreService->updateScoresForStudents($studentTeacherInfo, $semester, $schoolYear);
        } catch (RuntimeException $e) {
            // Handle error
        }
        return redirect()->to(base_url('/teacher/addProject'))->with('status', 'Project scores updated successfully.');
    }

    public function addNextProjectColumn()
    {
        $updatedBy = session()->get('user_id');
        $classSectionId = session()->get('class_section_id');
        $semester = $this->getTeacherSelectedSemester();
        $schoolYear = $this->getTeacherSchoolYear();
        $this->assertSchoolYearWritable($this->resolveSchoolYearContext());

        $projectModel = new ProjectModel();
        $studentClassModel = new StudentClassModel();

        $existingIndexes = $projectModel
            ->select('project_index')
            ->where('class_section_id', $classSectionId)
            ->where('updated_by', $updatedBy)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->groupBy('project_index')
            ->orderBy('project_index', 'DESC')
            ->findAll();

        $maxIndex = 0;
        foreach ($existingIndexes as $row) {
            if (isset($row['project_index']) && is_numeric($row['project_index'])) {
                $maxIndex = max($maxIndex, (int)$row['project_index']);
            }
        }

        $nextIndex = $maxIndex + 1;

        $students = $studentClassModel
            ->active()
            ->where('student_class.class_section_id', $classSectionId)
            ->where('student_class.school_year', $schoolYear)
            ->findAll();

        foreach ($students as $student) {
            $exists = $projectModel->where([
                'student_id' => $student['student_id'],
                'project_index' => $nextIndex,
                'class_section_id' => $classSectionId,
                'semester' => $semester,
                'school_year' => $schoolYear,
            ])->first();

            if (!$exists) {
                $projectModel->insert([
                    'student_id' => $student['student_id'],
                    'class_section_id' => $classSectionId,
                    'updated_by' => $updatedBy,
                    'project_index' => $nextIndex,
                    'score' => null,
                    'semester' => $semester,
                    'school_year' => $schoolYear,
                    'created_at' => utc_now(),
                    'updated_at' => utc_now()
                ]);
            }
        }

        return $this->response->setJSON([
            'status' => 'success',
            'project_index' => $nextIndex
        ]);
    }

    public function showProjectMngt()
    {
        // Accept POST first, then GET (query string), then session fallback
        $incomingId = $this->request->getPost('class_section_id');
        if (!$incomingId) {
            $incomingId = $this->request->getGet('class_section_id');
        }

        $classSectionId = (int) ($incomingId ?: (session()->get('class_section_id') ?? 0));

        if ($classSectionId <= 0) {
            return redirect()->back()->with('status', 'No class section found for the current teacher or selection.');
        }

        session()->set('class_section_id', $classSectionId);

        $semester   = $this->getTeacherSelectedSemester();
        $schoolYear = $this->getTeacherSchoolYear();
        $projectScores = $this->getProjectScoresByStudent($classSectionId, $semester, $schoolYear);
        $students = $this->getStudentsByClassSectionAndYear($classSectionId, $schoolYear);
        $projectHeaders = $this->getProjectHeaders($classSectionId, $semester, $schoolYear);
        $scoresLocked = $this->isScoresLocked($classSectionId, $semester, $schoolYear);

        return view('grading/project', [
            'projectScores'     => $projectScores,
            'students'          => $students,
            'semester'          => $semester,
            'schoolYear'        => $schoolYear,
            'projectHeaders'    => $projectHeaders,
            'classSectionId'    => $classSectionId,
            'scoresLocked'      => $scoresLocked,
        ]);
    }

    public function updateProject()
    {
        $studentModel = new StudentModel();
        $scores       = $this->request->getPost('scores');
        $studentIds   = $this->request->getPost('student_ids');
        $semester     = $this->request->getPost('semester') ?? $this->getTeacherSelectedSemester();
        $schoolYear   = $this->request->getPost('school_year') ?? $this->getTeacherSchoolYear();

        $updatedBy = $this->request->getPost('teacher_id') ?? session()->get('user_id');
        $classSectionId = $this->request->getPost('class_section_id') ?? session()->get('class_section_id'); // already stored in showProjectMngt
        $classSectionId = (int) ($classSectionId ?? 0);
        if ($classSectionId > 0 && $this->isScoresLocked($classSectionId, $semester, $schoolYear)) {
            return redirect()->back()->with('error', 'Scores are locked for this class. Unlock to edit.');
        }

        if ($scores && $studentIds && $semester && $schoolYear && $classSectionId) {
            $this->updateProjectScores($scores, $updatedBy, $classSectionId);
            session()->setFlashdata('status', 'Project scores updated successfully.');

            // ⬇️ Directly call the method and return its view
            return $this->showProjectMngt($updatedBy);
        }

        $studentTeacherInfo = $studentModel->getStudentInfoByClassSectionId($classSectionId, $semester, $schoolYear);
        // Call the updateScoresForStudents method
        try {

            $this->semesterScoreService->updateScoresForStudents($studentTeacherInfo, $semester, $schoolYear);
        } catch (RuntimeException $e) {
            // Handle error
        }
        return redirect()->back()->with('status', 'Failed to update scores.');
    }

    private function isScoresLocked(int $classSectionId, string $semester, string $schoolYear): bool
    {
        return $this->gradingLockModel->isLocked($classSectionId, $semester, $schoolYear);
    }

    /**____________________helpers________________________ */

    private function saveProjectScores(array $scores, array $studentIds, string $semester, string $schoolYear, int $updatedBy, int $classSectionId)
    {
        $projectModel = new \App\Models\ProjectModel();
        $now = utc_now();

        foreach ($studentIds as $studentId) {
            if (!isset($scores[$studentId])) {
                continue;
            }

            foreach ($scores[$studentId] as $index => $score) {
                // Check if the record exists
                $existing = $projectModel
                    ->where('student_id', $studentId)
                    ->where('project_index', $index)
                    ->where('semester', $semester)
                    ->where('school_year', $schoolYear)
                    ->where('class_section_id', $classSectionId)
                    ->where('updated_by', $updatedBy)
                    ->first();

                $data = [
                    'student_id'       => $studentId,
                    'class_section_id' => $classSectionId,
                    'updated_by'       => $updatedBy,
                    'project_index'   => $index,
                    'score'            => is_numeric($score) ? floatval($score) : null,
                    'semester'         => $semester,
                    'school_year'      => $schoolYear,
                    'updated_at'       => $now,
                ];

                if ($existing && isset($existing['id'])) {
                    $projectModel->update($existing['id'], $data);
                } else {
                    $data['created_at'] = $now;
                    $data['school_id'] = ''; // Set if needed
                    $projectModel->insert($data);
                }
            }
        }
    }

    private function getProjectHeaders($classSectionId, $semester, $schoolYear)
    {
        $projectModel = new ProjectModel();
        $rows = $projectModel
            ->select('project_index')
            ->where('class_section_id', $classSectionId)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->orderBy('project_index', 'ASC')
            ->findAll();

        $headers = [];

        foreach ($rows as $row) {
            $index = $row['project_index'];
            if (!is_array($index)) {
                $headers[$index] = true;
            }
        }

        ksort($headers);
        return array_keys($headers);
    }
    private function getStudentsByClassSectionAndYear($classSectionId, $schoolYear)
    {
        $studentClassModel = new \App\Models\StudentClassModel();
        $studentModel      = new \App\Models\StudentModel();

        // Step 1: Get student IDs from student_class table
        $studentClassRows = $studentClassModel
            ->select('student_id')
            ->distinct()
            ->where('class_section_id', $classSectionId)
            ->where('school_year', $schoolYear)
            ->findAll();

        $studentIds = array_column($studentClassRows, 'student_id');

        if (empty($studentIds)) {
            return []; // No students found
        }

        // Step 2: Get student data from students table
        $students = $studentModel
            ->whereIn('id', $studentIds)
            ->where('is_active', 1)
            ->orderBy('lastname', 'ASC')
            ->findAll();

        return $students;
    }

    private function getClassSectionIdForTeacher($updatedBy)
    {
        $teacherClassModel = new TeacherClassModel();
        $class = $teacherClassModel->where('teacher_id', $updatedBy)->first();
        return $class['class_section_id'] ?? null;
    }

    private function getProjectScoresByStudent($classSectionId, $semester, $schoolYear)
    {
        $projectModel = new ProjectModel();

        $rows = $projectModel
            ->select('student_id, project_index, score')
            ->where('class_section_id', $classSectionId)
            ->where('semester', $semester)
            ->where('school_year', $schoolYear)
            ->findAll();

        $studentScores = [];
        foreach ($rows as $row) {
            $studentId = $row['student_id'];
            $index = $row['project_index'];
            $score = $row['score'];

            $studentScores[$studentId]['scores'][$index] = $score;
        }

        return $studentScores;
    }

    private function getStudentsWithProjectScores($classSectionId, $projectHeaders)
    {
        $studentClassModel = new StudentClassModel();
        $studentModel = new StudentModel();
        $projectModel = new ProjectModel();

        $studentClasses = $studentClassModel->getClassStudents($classSectionId, $this->schoolYear, null);
        $students = [];

        foreach ($studentClasses as $sc) {
            $student = $studentModel
                ->where('id', $sc['student_id'])
                ->where('is_active', 1)
                ->first();
            if (!$student) continue;

            $scores = [];
            foreach ($projectHeaders as $index => $ids) {
                $entry = $projectModel->where('project_index', $index)
                    ->where('student_id', $sc['student_id'])
                    ->first();
                $scores[$index] = $entry['score'] ?? '';
            }

            $students[] = [
                'student_id' => $sc['student_id'],
                'firstname'  => $student['firstname'],
                'lastname'   => $student['lastname'],
                'scores'     => $scores
            ];
        }

        usort($students, fn($a, $b) => strcmp($a['lastname'], $b['lastname']) ?: strcmp($a['firstname'], $b['firstname']));
        return $students;
    }

    private function getTeacherSelectedSemester(): string
    {
        helper('semester_selection_helper');
        return selected_teacher_semester();
    }

    private function getTeacherSchoolYear(): string
    {
        return $this->currentSchoolYearName((string) ($this->schoolYear ?? ''));
    }
}
