<?php

namespace App\Controllers\View;

use App\Controllers\BaseController;
use App\Models\ScoreCommentModel;
use App\Models\ConfigurationModel;
use App\Models\StudentModel;
use App\Models\SemesterScoreModel;
use App\Models\GradingLockModel;
use App\Models\MissingScoreOverrideModel;

class ScoreCommentController extends BaseController
{
    protected $db;
    protected $configModel;
    protected $semester;
    protected $schoolYear;
    protected $scoreCommentModel;
    protected $studentModel;
    protected $semesterScoreModel;
    protected $gradingLockModel;
    protected $missingScoreOverrideModel;

    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.');
        }
        $this->configModel = new ConfigurationModel(); 
        $this->scoreCommentModel = new ScoreCommentModel();
        $this->studentModel = new StudentModel();
        $this->semesterScoreModel = new SemesterScoreModel();
        $this->gradingLockModel = new GradingLockModel();
        $this->missingScoreOverrideModel = new MissingScoreOverrideModel();
        $this->semester = $this->configModel->getConfig('semester');
        $this->schoolYear = $this->configModel->getConfig('school_year');
    }


    public function saveComments()
    {
        $schoolYearContext = $this->resolveSchoolYearContext();
        $this->assertSchoolYearWritable($schoolYearContext);
        $comments = $this->request->getPost('comments');
        $classSectionId = (int)($this->request->getPost('class_section_id') ?? session()->get('class_section_id') ?? 0);
        $schoolYear = $schoolYearContext->yearName();
        $rawSelectedSemester = $this->request->getPost('selected_semester');
        $normalizedSelected = $this->normalizeSemesterSelection($rawSelectedSemester);
        if ($normalizedSelected !== '') {
            $semester = ucfirst($normalizedSelected);
        } else {
            $semester = session()->get('teacher_scores_selected_semester')
                ?? session()->get('semester')
                ?? $this->semester;
        }
        $teacherId = session()->get('user_id');

        if ($classSectionId <= 0) {
            return redirect()->back()->with('error', 'Missing class section.');
        }
        if ($this->gradingLockModel->isLocked($classSectionId, $semester, $schoolYear)) {
            return redirect()->back()->with('error', 'Scores are locked for this class. Unlock to edit.');
        }

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

        $missingOk = $this->request->getPost('missing_ok') ?? [];

        $studentIds = array_map('intval', array_keys($comments));
        $studentNames = $this->getStudentFirstNames($studentIds);

        $isFall = strtolower((string) $semester) === 'fall';
        $isSpring = strtolower((string) $semester) === 'spring';
        $commentTypes = ['ptap_comment'];
        if ($isFall) {
            $commentTypes[] = 'midterm_comment';
        }
        if ($isSpring) {
            $commentTypes[] = 'final_comment';
        }
        foreach ($commentTypes as $type) {
            $checkedItems = [];
            if (is_array($missingOk)) {
                foreach ($missingOk as $studentId => $types) {
                    if (!is_array($types) || empty($types[$type])) {
                        continue;
                    }
                    $checkedItems[] = [
                        'student_id' => (int) $studentId,
                        'item_index' => null,
                    ];
                }
            }
            $this->missingScoreOverrideModel->replaceOverrides(
                $classSectionId,
                (string) $semester,
                (string) $schoolYear,
                $type,
                $studentIds,
                null,
                $checkedItems,
                $teacherId,
                true
            );
        }

        $errors = [];
        $payload = [];

        foreach ($comments as $studentId => $commentTypes) {
            foreach ($commentTypes as $scoreType => $comment) {
                $normalizedScoreType = strtolower((string)$scoreType);
                $trimmedComment = trim((string)$comment);
                if ($trimmedComment === '') {
                    continue;
                }

                $validationError = $this->validateComment($trimmedComment, (int)$studentId, $normalizedScoreType, $studentNames);
                if ($validationError !== null) {
                    $errors[] = $validationError;
                    log_message(
                        'warning',
                        "ScoreComment validation failed: teacher_id={$teacherId}, student_id={$studentId}, score_type={$normalizedScoreType}, error={$validationError}, length=" . mb_strlen($trimmedComment, 'UTF-8')
                    );
                    continue;
                }

                $payload[] = [
                    'student_id' => $studentId,
                    'class_section_id' => $classSectionId,
                    'score_type' => $normalizedScoreType, // 'ptap', 'midterm', 'final'
                    'semester' => $semester,
                    'school_year' => $schoolYear,
                    'comment' => $trimmedComment,
                    'commented_by' => $teacherId,
                    'created_at' => utc_now(),
                ];
            }
        }

        if (!empty($errors)) {
            return redirect()->back()->with('errors', $errors)->withInput();
        }

        foreach ($payload as $data) {
            $existing = $this->scoreCommentModel
                ->where('student_id', $data['student_id'])
                ->groupStart()
                    ->where('class_section_id', $data['class_section_id'])
                    ->orWhere('class_section_id', null)
                ->groupEnd()
                ->where('score_type', $data['score_type'])
                ->where('semester', $data['semester'])
                ->where('school_year', $data['school_year'])
                ->first();

            if ($existing) {
                $this->scoreCommentModel->update($existing['id'], $data);
            } else {
                $this->scoreCommentModel->insert($data);
            }
        }

        return redirect()->back()->with('status', 'Comments saved successfully.');
    }


    public function viewCommentsMngt()
    {
        $rawSelectedSemester = $this->request->getGet('semester')
            ?? $this->request->getPost('semester')
            ?? session()->get('teacher_scores_selected_semester')
            ?? session()->get('semester')
            ?? $this->semester;
        $normalizedSelected = $this->normalizeSemesterSelection($rawSelectedSemester);
        if ($normalizedSelected !== '') {
            $activeSemester = ucfirst($normalizedSelected);
        } else {
            $activeSemester = $this->semester;
        }

        $incoming = $this->request->getPost('class_section_id')
            ?? $this->request->getGet('class_section_id')
            ?? session()->get('class_section_id');

        $isAllSections = is_string($incoming) && strtolower(trim($incoming)) === 'allsections';
        $classSectionId = $isAllSections ? null : (int) ($incoming ?: 0);

        if (!$isAllSections && $classSectionId <= 0) {
            return redirect()->back()->with('status', 'Missing class section.');
        }
        session()->set('class_section_id', $isAllSections ? 'allsections' : $classSectionId);

        $builder = $this->db->table('student_class sc')
            ->distinct('SELECT s.id AS student_id, s.firstname, s.lastname, s.school_id')
            ->join('students s', 's.id = sc.student_id', 'inner')
            ->where('sc.school_year', $this->schoolYear);

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

        $students = $builder
            ->orderBy('s.lastname', 'ASC')
            ->orderBy('s.firstname', 'ASC')
            ->get()
            ->getResultArray();

    // 3) Load only comments for these students in this term (ptap, midterm, final)
    $comments = [];
    if (!empty($students)) {
        $studentIds = array_map(static fn($r) => (int)$r['student_id'], $students);

        $rawCommentsBuilder = $this->scoreCommentModel
            ->whereIn('student_id', $studentIds)
            ->whereIn('score_type', ['midterm', 'final', 'ptap', 'attendance'])
            ->where('semester', $activeSemester)
            ->where('school_year', $this->schoolYear);

        if (!$isAllSections) {
            $rawCommentsBuilder->groupStart()
                ->where('class_section_id', $classSectionId)
                ->orWhere('class_section_id', null); // legacy rows without class_section_id
            $rawCommentsBuilder->groupEnd();
        }

        if (!$isAllSections && $classSectionId > 0) {
            // Prefer the exact class-section row over legacy NULL class-section rows.
            // Without this, duplicate rows can overwrite each other unpredictably.
            $rawCommentsBuilder
                ->orderBy("CASE WHEN class_section_id = {$classSectionId} THEN 0 ELSE 1 END", '', false)
                ->orderBy('id', 'DESC');
        } else {
            $rawCommentsBuilder->orderBy('id', 'DESC');
        }

        $rawComments = $rawCommentsBuilder->findAll();

        $existingAttendance = [];
        foreach ($rawComments as $c) {
            $sid = (int) $c['student_id'];
            $typ = strtolower(trim((string) $c['score_type']));

            // The query is ordered with the best row first. Do not let legacy/older
            // duplicate rows overwrite the selected comment/review.
            if (isset($comments[$sid][$typ])) {
                continue;
            }

            $comments[$sid][$typ] = [
                'comment'        => $c['comment'] ?? null,
                'comment_review' => $c['comment_review'] ?? null,
                'commented_by'   => $c['commented_by'] ?? null,
                'reviewed_by'    => $c['reviewed_by'] ?? null,
            ];

            if ($typ === 'attendance') {
                $existingAttendance[$sid] = $c;
            }
        }

        helper('attendance_comment');
        // Autogenerate attendance comments for display when missing
        $attendanceScores = [];
        $scoreRows = $this->semesterScoreModel
            ->select(['student_id', 'attendance_score'])
            ->whereIn('student_id', $studentIds)
            ->where('semester', $activeSemester)
            ->where('school_year', $this->schoolYear)
            ->findAll();
        foreach ($scoreRows as $row) {
            $attendanceScores[(int)$row['student_id']] = isset($row['attendance_score'])
                ? (float)$row['attendance_score']
                : null;
        }
        $attendanceInserts = [];
        $attendanceUpdates = [];
        foreach ($students as $student) {
            $sid = (int)$student['student_id'];
            $hasComment = isset($comments[$sid]['attendance']['comment']) && trim((string)$comments[$sid]['attendance']['comment']) !== '';
            if ($hasComment) {
                continue;
            }
            $score = $attendanceScores[$sid] ?? null;
            if ($score === null) {
                continue;
            }
            $auto = attendance_comment_from_score($score, $student['firstname'] ?? '');
            if ($auto !== null) {
                $comments[$sid]['attendance'] = [
                    'comment'        => $auto,
                    'comment_review' => $comments[$sid]['attendance']['comment_review'] ?? null,
                    'commented_by'   => $comments[$sid]['attendance']['commented_by'] ?? null,
                    'reviewed_by'    => $comments[$sid]['attendance']['reviewed_by'] ?? null,
                ];
                if (isset($existingAttendance[$sid])) {
                    $attendanceUpdates[] = [
                        'id'           => $existingAttendance[$sid]['id'],
                        'comment'      => $auto,
                        'commented_by' => $existingAttendance[$sid]['commented_by'] ?? null,
                        'class_section_id' => $classSectionId,
                    ];
                } else {
                    $attendanceInserts[] = [
                        'student_id'  => $sid,
                        'score_type'  => 'attendance',
                        'semester'    => $activeSemester,
                        'school_year' => $this->schoolYear,
                        'comment'     => $auto,
                        'commented_by'=> null,
                        'class_section_id' => $classSectionId,
                        'created_at'  => utc_now(),
                    ];
                }
            }
        }
        if (!empty($attendanceUpdates)) {
            foreach ($attendanceUpdates as $row) {
                $id = $row['id'];
                unset($row['id']);
                $this->scoreCommentModel->update($id, $row);
            }
        }
        if (!empty($attendanceInserts)) {
            $this->scoreCommentModel->insertBatch($attendanceInserts);
        }
    }

        // 4) Render
        return view('grading/comments', [
            'students'       => $students,
            'semester'       => $activeSemester,
            'schoolYear'     => $this->schoolYear,
            'classSectionId' => $isAllSections ? 'allsections' : $classSectionId,
            'comments'       => $comments,
            'scoresLocked'   => (!$isAllSections && $classSectionId > 0)
                ? $this->gradingLockModel->isLocked($classSectionId, $activeSemester, $this->schoolYear)
                : false,
        ]);
    }


    public function updateComments()
    {
        $semester   = $this->request->getPost('semester');
        $schoolYear = $this->request->getPost('school_year');
        $teacherId  = $this->request->getPost('teacher_id') ?? session()->get('user_id');
        $classSectionId = $this->request->getPost('class_section_id') ?? session()->get('class_section_id');
        $comments   = $this->request->getPost('comments') ?? [];
        $reviews    = $this->request->getPost('reviews') ?? [];

        $studentIds = array_unique(array_map(
            'intval',
            array_merge(array_keys($comments), array_keys($reviews))
        ));
        $studentNames = $this->getStudentFirstNames($studentIds);
        $isReviewer = $this->isReviewer();
        $classSectionId = is_numeric($classSectionId) ? (int)$classSectionId : $classSectionId;
        if (is_int($classSectionId) && $classSectionId > 0) {
            if ($this->gradingLockModel->isLocked($classSectionId, $semester, $schoolYear)) {
                return redirect()->back()->with('error', 'Scores are locked for this class. Unlock to edit.');
            }
        }

        // Pull attendance scores once for auto-generated attendance comments
        $attendanceScores = [];
        if (!empty($studentIds)) {
            $attendanceBuilder = $this->semesterScoreModel
                ->select(['student_id', 'attendance_score'])
                ->where('semester', $semester)
                ->where('school_year', $schoolYear)
                ->whereIn('student_id', $studentIds);

            if (is_int($classSectionId) && $classSectionId > 0) {
                $attendanceBuilder->where('class_section_id', $classSectionId);
            }

            $rows = $attendanceBuilder->findAll();
            foreach ($rows as $row) {
                $attendanceScores[(int)$row['student_id']] = isset($row['attendance_score'])
                    ? (float)$row['attendance_score']
                    : null;
            }
        }

        helper('attendance_comment');

        $errors = [];
        $operations = [];

        foreach ($comments as $studentId => $types) {
            foreach ($types as $scoreType => $commentText) {
                $normalizedScoreType = strtolower((string)$scoreType);
                $commentText = trim((string)$commentText);

                // Auto-generate attendance comment when empty based on the student's attendance score
                if ($normalizedScoreType === 'attendance' && $commentText === '') {
                    $attendanceScore = $attendanceScores[$studentId] ?? null;
                    $auto = attendance_comment_from_score($attendanceScore, $studentNames[$studentId] ?? '');
                    if ($auto !== null) {
                        $commentText = $auto;
                    }
                }

                $reviewText = isset($reviews[$studentId][$scoreType])
                    ? trim((string)$reviews[$studentId][$scoreType])
                    : '';
                // Skip empty comment & empty review to avoid null inserts
                if ($commentText === '' && $reviewText === '') {
                    continue;
                }

                if ($commentText !== '') {
                    $validationError = $this->validateComment($commentText, (int)$studentId, $normalizedScoreType, $studentNames);
                    if ($validationError !== null) {
                        $errors[] = $validationError;
                        continue;
                    }
                }

                $operations[] = [
                    'student_id' => $studentId,
                    'class_section_id' => is_int($classSectionId) ? $classSectionId : null,
                    'score_type' => $normalizedScoreType,
                    'comment' => $commentText === '' ? null : $commentText,
                    'review' => $reviewText === '' ? null : $reviewText,
                ];
            }
        }

        if (!empty($errors)) {
            return redirect()->back()->with('errors', $errors)->withInput();
        }

        foreach ($operations as $operation) {
            $existingQuery = $this->scoreCommentModel
                ->where('student_id', $operation['student_id'])
                ->where('score_type', $operation['score_type'])
                ->where('semester', $semester)
                ->where('school_year', $schoolYear);

            if (is_int($classSectionId) && $classSectionId > 0) {
                $existingQuery->groupStart()
                    ->where('class_section_id', $classSectionId)
                    ->orWhere('class_section_id', null); // pick up legacy rows
                $existingQuery->groupEnd();
            }

            if (is_int($classSectionId) && $classSectionId > 0) {
                // Prefer updating the real class-section row. If only a legacy NULL row
                // exists, it will be picked next and migrated to the class section below.
                $existingQuery
                    ->orderBy("CASE WHEN class_section_id = {$classSectionId} THEN 0 ELSE 1 END", '', false)
                    ->orderBy('id', 'DESC');
            } else {
                $existingQuery->orderBy('id', 'DESC');
            }

            $existing = $existingQuery->first();

            $data = [
                'comment' => $operation['comment'],
                'commented_by' => $teacherId,
                'class_section_id' => $operation['class_section_id'],
            ];

            if ($isReviewer && $operation['review'] !== null) {
                $data['comment_review'] = $operation['review'];
                $data['reviewed_by'] = $teacherId;
            }

            if ($existing) {
                // Update
                $this->scoreCommentModel->update($existing['id'], $data);
            } else {
                // Insert
                $this->scoreCommentModel->insert(array_merge([
                    'student_id'   => $operation['student_id'],
                    'score_type'   => $operation['score_type'],
                    'semester'     => $semester,
                    'school_year'  => $schoolYear,
                    'class_section_id' => $operation['class_section_id'],
                ], $data));
            }
        }
        return redirect()->back()->with('status', 'Comments updated successfully!');
    }

    // Helper function to check if current user is a reviewer
    private function isReviewer()
    {
        $currentUserRole = session()->get('role'); // Make sure this matches your session role key
        
        // Get reviewer roles from configuration table
        $reviewerRoles = $this->configModel->getConfig('comment_reviewer');
        
        if (!$reviewerRoles) {
            return false;
        }
        
        // Convert comma-separated string to array and trim whitespace
        $allowedRoles = array_map('trim', explode(',', $reviewerRoles));
        
        // Check if current user role is in the allowed roles
        return in_array($currentUserRole, $allowedRoles);
    }

    private function getStudentFirstNames(array $studentIds): array
    {
        $ids = array_values(array_filter(array_unique(array_map('intval', $studentIds)), static function ($id) {
            return $id > 0;
        }));

        if (empty($ids)) {
            return [];
        }

        $rows = $this->studentModel
            ->select(['id', 'firstname'])
            ->whereIn('id', $ids)
            ->findAll();

        $names = [];
        foreach ($rows as $row) {
            $names[(int)($row['id'] ?? 0)] = trim((string)($row['firstname'] ?? ''));
        }

        return $names;
    }

    private function validateComment(string $comment, int $studentId, string $scoreType, array $studentNames): ?string
    {
        $trimmed = trim($comment);
        if ($trimmed === '') {
            return null;
        }

        $firstName = $studentNames[$studentId] ?? '';
        if ($firstName === '') {
            return "Missing student first name for student ID {$studentId}.";
        }

        $length = mb_strlen($trimmed, 'UTF-8');
        if ($length < 100) {
            return "{$firstName}'s {$scoreType} comment must be at least 100 characters.";
        }
        if ($length > 400) {
            return "{$firstName}'s {$scoreType} comment must be at most 400 characters.";
        }

        $pattern = '/^' . preg_quote($firstName, '/') . '\\b/i';
        if (!preg_match($pattern, $trimmed)) {
            return "{$firstName}'s {$scoreType} comment must start with the student's first name.";
        }

        return null;
    }

    private function normalizeSemesterSelection(?string $input): string
    {
        $value = strtolower(trim((string)$input));
        if ($value === 'fall' || str_contains($value, '1')) {
            return 'fall';
        }
        if ($value === 'spring' || str_contains($value, '2')) {
            return 'spring';
        }
        return '';
    }

}
