<?php
/**
 * Shared data layer for terminal report rendering.
 *
 * Both the on-screen print view (print_report.php) and the PDF export
 * (report_pdf.php) build the exact same dataset. This function is the single
 * source of truth so the two outputs can never drift apart, and so any template
 * can be rendered from a uniform $data array.
 */

if (!function_exists('getPositionSuffix')) {
    function getPositionSuffix($pos)
    {
        if ($pos % 100 >= 11 && $pos % 100 <= 13) return $pos . 'TH';
        switch ($pos % 10) {
            case 1: return $pos . 'ST';
            case 2: return $pos . 'ND';
            case 3: return $pos . 'RD';
            default: return $pos . 'TH';
        }
    }
}

/**
 * Gather everything needed to render a single student's terminal report.
 *
 * Does not close $conn — the caller owns the connection lifecycle.
 *
 * @return array Uniform dataset consumed by every report template.
 */
function getReportData(mysqli $conn, int $student_id, string $term, string $academic_year): array
{
    // School settings
    $settings = [];
    $result = $conn->query("SELECT * FROM settings");
    if ($result) {
        while ($row = $result->fetch_assoc()) {
            $settings[$row['setting_key']] = $row['setting_value'];
        }
    }

    // Student
    $stmt = $conn->prepare("SELECT * FROM students WHERE id = ?");
    $stmt->bind_param("i", $student_id);
    $stmt->execute();
    $student = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    if (!$student) {
        return ['student' => null];
    }

    // Class size (NO ON ROLL)
    $classSize = 0;
    $stmt = $conn->prepare("SELECT COUNT(*) as total FROM students WHERE class = ? AND status = 'active'");
    $stmt->bind_param("s", $student['class']);
    $stmt->execute();
    $result = $stmt->get_result();
    if ($row = $result->fetch_assoc()) {
        $classSize = (int) ($row['total'] ?? 0);
    }
    $stmt->close();

    // Subjects for this class
    $subjects = [];
    $stmt = $conn->prepare("
        SELECT cs.*, s.subject_name, s.subject_code
        FROM class_subjects cs
        JOIN subjects s ON cs.subject_id = s.id
        WHERE cs.class_name = ?
        ORDER BY cs.display_order, s.subject_name
    ");
    $stmt->bind_param("s", $student['class']);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        $subjects[] = $row;
    }
    $stmt->close();

    // Scores (keyed by subject_id)
    $scores = [];
    $stmt = $conn->prepare("SELECT * FROM scores WHERE student_id = ? AND term = ? AND academic_year = ?");
    $stmt->bind_param("iss", $student_id, $term, $academic_year);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        $scores[$row['subject_id']] = $row;
    }
    $stmt->close();

    // Total marks
    $totalMarks = 0;
    foreach ($scores as $score) {
        $totalMarks += $score['total_score'];
    }

    // Position in class
    $position = 1;
    $stmt = $conn->prepare("
        SELECT s.id, COALESCE(SUM(sc.total_score), 0) as total
        FROM students s
        LEFT JOIN scores sc ON s.id = sc.student_id AND sc.term = ? AND sc.academic_year = ?
        WHERE s.class = ? AND s.status = 'active'
        GROUP BY s.id
        ORDER BY total DESC
    ");
    $stmt->bind_param("sss", $term, $academic_year, $student['class']);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($row = $result->fetch_assoc()) {
        if ($row['id'] == $student_id) break;
        $position++;
    }
    $stmt->close();

    // Per-subject class statistics: average, highest/lowest and this student's
    // rank within each subject. GES terminal reports show position per subject,
    // not just an overall position.
    $subjectStats = [];
    $stmt = $conn->prepare("
        SELECT sc.subject_id, sc.student_id, sc.total_score
        FROM scores sc
        JOIN students s ON s.id = sc.student_id
        WHERE s.class = ? AND s.status = 'active'
          AND sc.term = ? AND sc.academic_year = ?
    ");
    $stmt->bind_param("sss", $student['class'], $term, $academic_year);
    $stmt->execute();
    $result = $stmt->get_result();
    $bySubject = [];
    while ($row = $result->fetch_assoc()) {
        $bySubject[$row['subject_id']][] = [
            'student_id' => (int) $row['student_id'],
            'score'      => (float) $row['total_score'],
        ];
    }
    $stmt->close();

    foreach ($bySubject as $subjectId => $rows) {
        $all  = array_column($rows, 'score');
        $mine = null;
        foreach ($rows as $r) {
            if ($r['student_id'] === $student_id) { $mine = $r['score']; break; }
        }
        $subjectPosition = null;
        if ($mine !== null) {
            $subjectPosition = 1;
            foreach ($all as $s) {
                if ($s > $mine) $subjectPosition++;
            }
        }
        $subjectStats[$subjectId] = [
            'count'    => count($all),
            'average'  => $all ? array_sum($all) / count($all) : 0.0,
            'highest'  => $all ? max($all) : 0.0,
            'lowest'   => $all ? min($all) : 0.0,
            'position' => $subjectPosition,
        ];
    }

    // Report details
    $stmt = $conn->prepare("SELECT * FROM report_details WHERE student_id = ? AND term = ? AND academic_year = ?");
    $stmt->bind_param("iss", $student_id, $term, $academic_year);
    $stmt->execute();
    $reportDetails = $stmt->get_result()->fetch_assoc();
    $stmt->close();

    $closingDate   = $reportDetails['closing_date']      ?? date('Y-m-d');
    $reopeningDate = $reportDetails['reopening_date']    ?? date('Y-m-d', strtotime('+30 days'));
    $promotedTo    = $reportDetails['promoted_to']       ?? $student['class'];
    $conduct       = $reportDetails['conduct']           ?? 'SATISFACTORY';
    $interest      = $reportDetails['interest']          ?? 'FOCUSED ON STUDIES';
    $teacherRemark = $reportDetails['teacher_remark']    ?? 'GOOD PERFORMANCE';
    $headteacherRemark = $reportDetails['headteacher_remark'] ?? '';
    $schoolStamp       = $reportDetails['school_stamp']       ?? '';

    // Attendance — mirrors the logic the on-screen editor already uses, so the
    // printed report and generate_report.php can never disagree.
    // Days present: saved value first, else derived from the attendance table.
    // Both helpers query `school_terms` / `attendance`, which some installs do
    // not have. Under PHP 8 mysqli throws rather than returning false, so their
    // internal guards never fire — catch here and degrade instead of failing the
    // whole report.
    $savedAttendance = $reportDetails['attendance_out_of'] ?? null;
    if ($savedAttendance !== null && (int) $savedAttendance > 0) {
        $attendance = (int) $savedAttendance;
    } else {
        $attendance = '_____';
        if (function_exists('getStudentTermDaysPresent')) {
            try {
                $days = getStudentTermDaysPresent($conn, $student_id, $academic_year, $term);
                if ((int) $days > 0) $attendance = (int) $days;
            } catch (Throwable $e) {
                // attendance table missing — leave the blank rule for hand entry
            }
        }
    }

    // Days the school met: saved value first, else from the term calendar.
    $savedTotalDays = isset($reportDetails['attendance_total_days']) ? (int) $reportDetails['attendance_total_days'] : 0;
    $attendanceTotal = $savedTotalDays;
    if ($attendanceTotal <= 0 && function_exists('getTermSchoolDays')) {
        try {
            $attendanceTotal = (int) getTermSchoolDays($conn, $academic_year, $term, 0);
        } catch (Throwable $e) {
            $attendanceTotal = 0; // no term calendar — template omits the denominator
        }
    }

    // Signatures: per-report saved first, then persistent fallback.
    // NOTE: a per-report path that points at a deleted/missing file must NOT
    // shadow the persistent signature, or the report renders a blank line.
    $directorSignature     = $reportDetails['director_signature']      ?? '';
    $headteacherSignature  = $reportDetails['headteacher_signature']   ?? '';
    $classTeacherSignature = $reportDetails['class_teacher_signature'] ?? '';

    if (reportAssetMissing($directorSignature)) {
        $stmt = $conn->prepare("SELECT signature_path FROM users WHERE role = 'director' AND signature_path IS NOT NULL AND signature_path != '' LIMIT 1");
        $stmt->execute();
        $r = $stmt->get_result()->fetch_assoc();
        if ($r) $directorSignature = $r['signature_path'];
        $stmt->close();
    }
    if (reportAssetMissing($headteacherSignature)) {
        $stmt = $conn->prepare("SELECT signature_path FROM users WHERE role = 'headteacher' AND signature_path IS NOT NULL AND signature_path != '' LIMIT 1");
        $stmt->execute();
        $r = $stmt->get_result()->fetch_assoc();
        if ($r) $headteacherSignature = $r['signature_path'];
        $stmt->close();
    }
    if (reportAssetMissing($classTeacherSignature)) {
        $stmt = $conn->prepare("
            SELECT st.signature_path FROM staff st
            JOIN classes c ON c.class_teacher_id = st.id
            WHERE c.class_name = ? AND c.is_active = 1
              AND st.signature_path IS NOT NULL AND st.signature_path != ''
            LIMIT 1
        ");
        $stmt->bind_param('s', $student['class']);
        $stmt->execute();
        $r = $stmt->get_result()->fetch_assoc();
        if ($r) $classTeacherSignature = $r['signature_path'];
        $stmt->close();
    }

    // Grade boundaries (class-specific, fallback to ALL)
    $gradeBoundaries = [];
    $gbClass = $student['class'] ?? 'ALL';
    $gbStmt = $conn->prepare(
        "SELECT * FROM grade_boundaries WHERE class_name = ?
         UNION
         SELECT * FROM grade_boundaries WHERE class_name = 'ALL'
           AND grade NOT IN (SELECT grade FROM grade_boundaries WHERE class_name = ?)
         ORDER BY CASE grade WHEN 'A' THEN 1 WHEN 'B' THEN 2 WHEN 'C' THEN 3
                             WHEN 'D' THEN 4 WHEN 'E' THEN 5 WHEN 'W' THEN 6 ELSE 7 END"
    );
    $gbStmt->bind_param('ss', $gbClass, $gbClass);
    $gbStmt->execute();
    $gbResult = $gbStmt->get_result();
    while ($gbRow = $gbResult->fetch_assoc()) { $gradeBoundaries[] = $gbRow; }
    $gbStmt->close();
    if (empty($gradeBoundaries)) {
        $gradeBoundaries = [
            ['grade' => 'A', 'min_score' => 90, 'max_score' => 100, 'remark' => 'EXCELLENT'],
            ['grade' => 'B', 'min_score' => 80, 'max_score' => 89,  'remark' => 'VERY GOOD'],
            ['grade' => 'C', 'min_score' => 70, 'max_score' => 79,  'remark' => 'GOOD'],
            ['grade' => 'D', 'min_score' => 60, 'max_score' => 69,  'remark' => 'SATISFACTORY'],
            ['grade' => 'E', 'min_score' => 50, 'max_score' => 59,  'remark' => 'PASS'],
            ['grade' => 'W', 'min_score' => 0,  'max_score' => 49,  'remark' => 'WEAK'],
        ];
    }

    // Format term
    $termFormatted = strtoupper($term);
    if ($term == 'First Term')      $termFormatted = 'ONE(1)';
    elseif ($term == 'Second Term') $termFormatted = 'TWO(2)';
    elseif ($term == 'Third Term')  $termFormatted = 'THREE(3)';

    // Admission / index number — column naming varies between installs.
    // `student_id` is the school-facing code (e.g. "STU002"), distinct from
    // the numeric primary key `id`, and is the column this schema actually uses.
    $admissionNo = '';
    foreach (['student_id', 'admission_no', 'admission_number', 'index_number', 'index_no', 'reg_no', 'registration_number', 'student_code'] as $col) {
        if (isset($student[$col]) && trim((string) $student[$col]) !== '') {
            $admissionNo = (string) $student[$col];
            break;
        }
    }

    // Age as at the closing date, when a date of birth is on file.
    $age = '';
    if (!empty($student['date_of_birth']) && $student['date_of_birth'] !== '0000-00-00') {
        $dob = date_create((string) $student['date_of_birth']);
        $ref = date_create($closingDate) ?: date_create();
        if ($dob && $ref && $dob <= $ref) {
            $age = (string) date_diff($dob, $ref)->y;
        }
    }

    // Average across subjects that actually carry a score.
    $scoredCount = count($scores);
    $average = $scoredCount > 0 ? $totalMarks / $scoredCount : 0.0;

    return [
        'settings'      => $settings,
        'student'       => $student,
        'admissionNo'   => $admissionNo,
        'classSize'     => $classSize,
        'subjects'      => $subjects,
        'scores'        => $scores,
        'subjectStats'  => $subjectStats,
        'totalMarks'    => $totalMarks,
        'average'       => $average,
        'scoredCount'   => $scoredCount,
        'position'      => $position,
        'positionLabel' => getPositionSuffix($position),
        'closingDate'   => $closingDate,
        'reopeningDate' => $reopeningDate,
        'attendance'      => $attendance,
        'attendanceTotal' => $attendanceTotal,
        'promotedTo'      => $promotedTo,
        'conduct'         => $conduct,
        'interest'        => $interest,
        'teacherRemark'   => $teacherRemark,
        'headteacherRemark' => $headteacherRemark,
        'schoolStamp'     => $schoolStamp,
        'sex'             => (string) ($student['sex'] ?? ''),
        'age'             => $age,
        'directorSignature'     => $directorSignature,
        'headteacherSignature'  => $headteacherSignature,
        'classTeacherSignature' => $classTeacherSignature,
        'gradeBoundaries' => $gradeBoundaries,
        'term'          => $term,
        'academicYear'  => $academic_year,
        'termFormatted' => $termFormatted,
        'schoolName'    => $settings['school_name']    ?? 'SCHOOL NAME',
        'schoolAddress' => $settings['school_address'] ?? '',
        'schoolPhone'   => $settings['school_phone']   ?? '',
        'schoolEmail'   => $settings['school_email']   ?? '',
        'logoPath'      => $settings['school_logo']    ?? 'uploads/logos/logo.png',
    ];
}

/**
 * Absolute filesystem path for a repo-relative asset (e.g. "uploads/x.png").
 * Single source of truth for asset resolution, shared by the helpers below.
 */
function reportAssetPath(string $relPath): string
{
    return __DIR__ . '/../../../' . ltrim($relPath, '/'); // lib/ -> reports -> modules -> app root
}

/**
 * True when a stored signature/stamp path is unusable — either blank, or it
 * references a file that no longer exists on disk. Callers use this to decide
 * whether to fall back to a persistent (per-user/per-staff) signature, so that
 * a stale row in report_details cannot silently blank out a valid signature.
 */
function reportAssetMissing(?string $relPath): bool
{
    $relPath = trim((string) $relPath);
    return $relPath === '' || !file_exists(reportAssetPath($relPath));
}
/**
 * Resolve a repo-relative asset path (e.g. "uploads/signatures/x.png") into an
 * <img> src usable in the current render mode.
 *
 *  - 'print' → "../../<path>" URL (browser fetches it live), or '' if missing.
 *  - 'pdf'   → base64 data URI (DOMPDF runs with isRemoteEnabled=false).
 *
 * Paths are resolved relative to the reports module dir (modules/reports/).
 */
function reportImageSrc(string $relPath, string $mode): string
{
    $relPath = ltrim($relPath, '/');
    if ($relPath === '') return '';
    $abs = reportAssetPath($relPath);
    if (!file_exists($abs)) return '';

    if ($mode === 'pdf') {
        $ext  = strtolower(pathinfo($abs, PATHINFO_EXTENSION));
        $mime = in_array($ext, ['jpg', 'jpeg']) ? 'image/jpeg' : 'image/png';
        $b64  = base64_encode((string) file_get_contents($abs));
        return 'data:' . $mime . ';base64,' . $b64;
    }

    // Cache-bust so a re-uploaded logo or re-signed signature shows immediately
    // instead of the browser serving the previous file from cache.
    $mtime = @filemtime($abs);
    return '../../' . $relPath . ($mtime ? '?v=' . $mtime : '');
}
