<?php
declare(strict_types=1);

$config = load_rpi_config();

function load_rpi_config(): array
{
    $candidates = [
        __DIR__ . '/../../rpi_private_v2/config.php', // cPanel: public_html/rpionlinev2 + /home/user/rpi_private_v2
        __DIR__ . '/../rpi_private_v2/config.php',    // Laragon/local fallback
        __DIR__ . '/config.php',                      // fallback sementara sahaja
    ];
    foreach ($candidates as $file) {
        if (is_file($file)) {
            $config = require $file;
            if (!is_array($config)) {
                throw new RuntimeException('Fail config.php tidak return array.');
            }
            return $config;
        }
    }
    throw new RuntimeException('config.php tidak dijumpai. Letak config.php dalam folder rpi_private_v2.');
}

try {
    route($config);
} catch (Throwable $error) {
    $status = (int) ($error->getCode() ?: 500);
    if ($status < 400 || $status > 599) {
        $status = 500;
    }
    json_response(['error' => public_error_message($error, $config)], $status);
}

function public_error_message(Throwable $error, array $config): string
{
    if (!empty($config['debug_errors'])) {
        return $error->getMessage();
    }

    error_log('[RPI Online] ' . $error->getMessage());

    if ($error instanceof InvalidArgumentException) {
        return $error->getMessage();
    }

    if ($error instanceof PDOException) {
        $step = $GLOBALS['RPI_SAVE_STEP'] ?? 'proses simpan';
        return 'Ralat MySQL semasa ' . $step . '. ' . safe_error_detail($error->getMessage());
    }

    $status = (int) ($error->getCode() ?: 500);
    if ($status >= 400 && $status < 500) {
        return $error->getMessage();
    }

    return 'Ralat server. Sila cuba semula atau hubungi Admin Sistem.';
}

function safe_error_detail(string $message): string
{
    $message = preg_replace('/\s+/', ' ', trim($message));
    $message = preg_replace('/(password|passwd|pwd)=([^;\s]+)/i', '$1=***', $message);
    return mb_substr($message, 0, 260);
}

function route(array $config): void
{
    $method = $_SERVER['REQUEST_METHOD'] ?? 'GET';
    $path = parse_url($_SERVER['REQUEST_URI'] ?? '/', PHP_URL_PATH) ?: '/';
    $apiPosition = strpos($path, '/api/');
    if ($apiPosition !== false) {
        $path = substr($path, $apiPosition);
    } elseif (substr($path, -4) === '/api') {
        $path = '/api';
    }

    if ($method === 'GET' && $path === '/api/health') {
        json_response(['ok' => true, 'app' => 'RPI Online', 'time' => gmdate('c')]);
    }

    require_basic_access($config);

    if ($method === 'GET' && $path === '/api/db/status') {
        json_response(mysql_status($config));
    }

    if ($method === 'GET' && $path === '/api/audit-log/load') {
        json_response(load_audit_logs($_GET, $config));
    }

    if ($method === 'POST' && $path === '/api/audit-log') {
        json_response(append_audit_log(read_json(), $config));
    }

    if ($method === 'POST' && $path === '/api/audit-log/test') {
        json_response(create_audit_test_log(read_json(), $config));
    }

    if ($method === 'GET' && $path === '/api/system-health/status') {
        json_response(system_health_status($config));
    }

    if ($method === 'GET' && $path === '/api/backup/status') {
        json_response(auto_backup_status($config));
    }

    if ($method === 'POST' && $path === '/api/backup/run') {
        json_response(run_auto_backup_endpoint(read_json(), $config));
    }

    if ($method === 'POST' && $path === '/api/generate-rpi') {
        json_response(generate_rpi(read_json(), $config));
    }

    if ($method === 'POST' && $path === '/api/achievement/create') {
        json_response(save_achievement_action(read_json(), $config, 'create'));
    }

    if ($method === 'POST' && $path === '/api/achievement/update') {
        json_response(save_achievement_action(read_json(), $config, 'update'));
    }

    if ($method === 'POST' && $path === '/api/achievement/delete') {
        json_response(save_achievement_action(read_json(), $config, 'delete'));
    }

    if ($method === 'POST' && $path === '/api/achievement/restore') {
        json_response(save_achievement_action(read_json(), $config, 'restore'));
    }

    if ($method === 'POST' && $path === '/api/achievement/hard-delete') {
        json_response(save_achievement_action(read_json(), $config, 'hard-delete'));
    }

    if ($method === 'POST' && $path === '/api/achievement-standard') {
        json_response(generate_achievement(read_json(), $config));
    }

    if ($method === 'POST' && $path === '/api/evidence-template') {
        json_response(generate_evidence_template(read_json(), $config));
    }

    if ($method === 'POST' && $path === '/api/evidence-skill') {
        json_response(generate_evidence_skill(read_json(), $config));
    }

    if ($method === 'POST' && $path === '/api/worksheet-template') {
        json_response(generate_worksheet_template(read_json(), $config));
    }

    if ($method === 'POST' && $path === '/api/developer-login') {
        $body = read_json();
        try {
            $result = developer_login($body, $config);
            $developer = is_array($result['developer'] ?? null) ? $result['developer'] : [];
            system_append_audit_log('SYSTEM', [
                'actor' => [
                    'id' => $developer['email'] ?? normalize_staff_username($body['username'] ?? ''),
                    'name' => $developer['name'] ?? 'Admin Sistem',
                    'role' => 'developer',
                ],
                'action' => 'developer_login_success',
                'actionLabel' => 'Login Developer Berjaya',
                'entityType' => 'developer_session',
                'entityId' => normalize_staff_username($body['username'] ?? ''),
                'entityName' => 'Akaun Developer',
                'status' => 'success',
            ], $config);
            json_response($result);
        } catch (Throwable $error) {
            system_append_audit_log('SYSTEM', [
                'actor' => [
                    'id' => normalize_staff_username($body['username'] ?? ''),
                    'name' => normalize_staff_username($body['username'] ?? ''),
                    'role' => 'developer',
                ],
                'action' => 'developer_login_failed',
                'actionLabel' => 'Login Developer Gagal',
                'entityType' => 'developer_session',
                'entityId' => normalize_staff_username($body['username'] ?? ''),
                'entityName' => 'Akaun Developer',
                'status' => 'error',
                'detail' => ['message' => safe_error_detail($error->getMessage())],
            ], $config);
            throw $error;
        }
    }

    if ($method === 'POST' && $path === '/api/auth/login') {
        $body = read_json();
        $loginSchoolId = normalize_school_id($body['schoolId'] ?? null);
        try {
            $result = auth_login($body, $config);
            $sessionInfo = is_array($result['session'] ?? null) ? $result['session'] : [];
            system_append_audit_log($loginSchoolId, [
                'actor' => [
                    'id' => $sessionInfo['email'] ?? ($sessionInfo['id'] ?? normalize_staff_username($body['username'] ?? '')),
                    'name' => $sessionInfo['name'] ?? normalize_staff_username($body['username'] ?? ''),
                    'role' => $sessionInfo['role'] ?? ($result['type'] ?? 'user'),
                ],
                'action' => 'user_login_success',
                'actionLabel' => 'Login Pengguna Berjaya',
                'entityType' => 'user_session',
                'entityId' => $sessionInfo['id'] ?? normalize_staff_username($body['username'] ?? ''),
                'entityName' => $sessionInfo['name'] ?? normalize_staff_username($body['username'] ?? ''),
                'status' => 'success',
                'detail' => ['type' => $result['type'] ?? ''],
            ], $config);
            json_response($result);
        } catch (Throwable $error) {
            system_append_audit_log($loginSchoolId, [
                'actor' => [
                    'id' => normalize_staff_username($body['username'] ?? ''),
                    'name' => normalize_staff_username($body['username'] ?? ''),
                    'role' => 'unknown',
                ],
                'action' => 'user_login_failed',
                'actionLabel' => 'Login Pengguna Gagal',
                'entityType' => 'user_session',
                'entityId' => normalize_staff_username($body['username'] ?? ''),
                'entityName' => normalize_staff_username($body['username'] ?? ''),
                'status' => 'error',
                'detail' => ['message' => safe_error_detail($error->getMessage())],
            ], $config);
            throw $error;
        }
    }

    if ($method === 'GET' && $path === '/api/db/load') {
        json_response(load_mysql_state($_GET['schoolId'] ?? null, $config));
    }

    if ($method === 'POST' && $path === '/api/db/save') {
        json_response(save_mysql_state(read_json(), $config));
    }

    if ($method === 'POST' && $path === '/api/db/duplicate-rpi/scan') {
        json_response(scan_duplicate_rpi(read_json(), $config));
    }

    if ($method === 'POST' && $path === '/api/db/duplicate-rpi/archive-bulk') {
        json_response(archive_duplicate_rpi_bulk(read_json(), $config));
    }

    if ($method === 'POST' && $path === '/api/db/duplicate-rpi/archive') {
        json_response(archive_duplicate_rpi(read_json(), $config));
    }

    if ($method === 'POST' && $path === '/api/document-log') {
        json_response(append_document_log(read_json(), $config));
    }

    if ($method === 'GET' && $path === '/api/subscribers/load') {
        json_response(load_subscribers($config));
    }

    if ($method === 'POST' && $path === '/api/subscribers/save') {
        json_response(save_subscribers(read_json(), $config));
    }

    json_response(['error' => 'Kaedah tidak disokong.'], 405);
}

function require_basic_access(array $config): void
{
    $password = trim((string) ($config['app_access_password'] ?? ''));
    if ($password === '') {
        return;
    }

    $user = (string) ($config['app_access_user'] ?? 'rpi');
    $givenUser = $_SERVER['PHP_AUTH_USER'] ?? '';
    $givenPassword = $_SERVER['PHP_AUTH_PW'] ?? '';

    if (hash_equals($user, $givenUser) && hash_equals($password, $givenPassword)) {
        return;
    }

    header('WWW-Authenticate: Basic realm="RPI Online"');
    json_response(['error' => 'Akses RPI Online memerlukan kata laluan.'], 401);
}

function developer_login(array $body, array $config): array
{
    $configuredUser = strtolower(trim((string) ($config['developer_user'] ?? '')));
    $configuredPassword = (string) ($config['developer_password'] ?? '');
    if ($configuredUser === '' || $configuredPassword === '') {
        throw new RuntimeException('Akaun Admin Sistem belum diset dalam config.php.', 503);
    }

    $username = normalize_staff_username($body['username'] ?? '');
    $password = (string) ($body['password'] ?? '');
    if (!hash_equals($configuredUser, $username) || !hash_equals($configuredPassword, $password)) {
        throw new RuntimeException('Login Admin Sistem gagal.', 401);
    }

    return [
        'ok' => true,
        'developer' => [
            'id' => 'system-developer',
            'name' => trim((string) ($config['developer_name'] ?? 'Admin Sistem')),
            'email' => $configuredUser,
            'role' => 'developer',
            'active' => true,
        ],
    ];
}

function auth_login(array $body, array $config): array
{
    $schoolId = normalize_school_id($body['schoolId'] ?? null);
    $username = normalize_staff_username($body['username'] ?? '');
    $password = (string) ($body['password'] ?? '');

    if ($schoolId === '' || $username === '' || $password === '') {
        throw new InvalidArgumentException('Kod database, username dan kata laluan diperlukan.', 400);
    }

    $db = pdo($config);
    ensure_schema_compatibility($db);

    $schoolStatement = $db->prepare('select * from rpi_schools where school_id = ? limit 1');
    $schoolStatement->execute([$schoolId]);
    $school = $schoolStatement->fetch();
    if (!$school) {
        throw new RuntimeException('Data sekolah belum wujud di MySQL. Sila setup database dahulu.', 404);
    }

    $schoolSettings = decode_db_json($school['settings_json'] ?? null);
    $subscriptionStatus = strtolower(trim((string) ($schoolSettings['subscriptionStatus'] ?? '')));
    $subscriptionEnds = nullable_date($school['subscription_ends'] ?? ($schoolSettings['subscriptionEnds'] ?? null));
    if (in_array($subscriptionStatus, ['sekat', 'suspend', 'suspended'], true)) {
        throw new RuntimeException('Akses akaun ini sedang disekat. Sila hubungi Admin Sistem untuk pengaktifan semula.', 403);
    }
    if ($subscriptionStatus === 'tamat' || ($subscriptionEnds && $subscriptionEnds < date('Y-m-d'))) {
        throw new RuntimeException('Langganan akaun ini telah tamat. Sila hubungi Admin Sistem untuk pembaharuan.', 403);
    }

    try {
        return auth_staff_login($schoolId, $username, $password, $db);
    } catch (RuntimeException $staffError) {
        $staffCode = (int) $staffError->getCode();
        if ($staffCode !== 401) {
            throw $staffError;
        }
    }

    return auth_parent_login($schoolId, $username, $password, $db);
}

function auth_staff_login(string $schoolId, string $username, string $password, PDO $db): array
{
    $statement = $db->prepare('select * from rpi_teachers where school_id = ? and email = ? limit 1');
    $statement->execute([$schoolId, $username]);
    $row = $statement->fetch();
    if (!$row) {
        throw new RuntimeException('Login guru/admin gagal. Akaun tidak ditemui untuk kod database ini.', 401);
    }

    $teacher = decode_db_json($row['teacher_json'] ?? null);
    if (($teacher['active'] ?? true) === false) {
        throw new RuntimeException('Akaun ini sedang disekat atau belum diaktifkan. Sila hubungi Admin Sistem.', 403);
    }

    $stored = (string) ($row['password'] ?? '');
    if (!verify_teacher_password($password, $stored)) {
        throw new RuntimeException('Login guru/admin gagal. Username atau kata laluan tidak tepat.', 401);
    }

    $secureHash = password_hash($password, PASSWORD_DEFAULT);
    $teacher['password'] = '';
    $teacher['passwordHash'] = '';
    $teacher['passwordUpdatedAt'] = $teacher['passwordUpdatedAt'] ?? gmdate('c');
    $teacher['lastLoginAt'] = gmdate('c');
    $teacher['loginCount'] = (int) ($teacher['loginCount'] ?? ($row['login_count'] ?? 0)) + 1;

    $db->prepare('update rpi_teachers set password = ?, last_login_at = ?, login_count = ?, teacher_json = ? where school_id = ? and teacher_id = ?')
        ->execute([
            $secureHash,
            $teacher['lastLoginAt'],
            $teacher['loginCount'],
            encode_db_json(sanitize_teacher_for_client($teacher)),
            $schoolId,
            $row['teacher_id'],
        ]);

    return [
        'ok' => true,
        'type' => 'staff',
        'session' => [
            'id' => $row['teacher_id'],
            'name' => $row['name'] ?: ($teacher['name'] ?? 'Guru'),
            'email' => $row['email'],
            'role' => normalize_role($row['role'] ?: ($teacher['role'] ?? 'teacher')),
            'active' => true,
        ],
    ];
}

function auth_parent_login(string $schoolId, string $username, string $password, PDO $db): array
{
    $userToken = normalize_ic($username);
    $passToken = normalize_ic($password);
    if ($userToken === '' || $userToken !== $passToken) {
        throw new RuntimeException('Login ibu bapa gagal. Username dan kata laluan mesti nombor MyKid anak yang sama tanpa simbol "-".', 401);
    }

    $statement = $db->prepare('select * from rpi_students where school_id = ?');
    $statement->execute([$schoolId]);
    foreach ($statement->fetchAll() as $row) {
        if (normalize_ic($row['mykid'] ?? '') !== $userToken) {
            continue;
        }
        $student = decode_db_json($row['student_json'] ?? null);
        $student['parentLastLoginAt'] = gmdate('c');
        $student['parentLoginCount'] = (int) ($student['parentLoginCount'] ?? 0) + 1;
        $db->prepare('update rpi_students set student_json = ? where school_id = ? and student_id = ?')
            ->execute([encode_db_json($student), $schoolId, $row['student_id']]);

        return [
            'ok' => true,
            'type' => 'parent',
            'session' => [
                'id' => 'parent-' . $row['student_id'],
                'role' => 'parent',
                'name' => $student['guardian'] ?? 'Ibu Bapa / Penjaga',
                'studentId' => $row['student_id'],
                'studentMykid' => $userToken,
            ],
        ];
    }

    throw new RuntimeException('Login ibu bapa gagal. Nombor MyKid anak tidak sepadan dengan rekod murid dalam sistem.', 401);
}

function verify_teacher_password(string $password, string $stored): bool
{
    if ($stored === '') {
        return false;
    }
    $info = password_get_info($stored);
    if (($info['algo'] ?? 0) !== 0) {
        return password_verify($password, $stored);
    }
    if (str_starts_with($stored, 'rpi-sha256-v1:')) {
        return hash_equals($stored, 'rpi-sha256-v1:' . hash('sha256', 'rpi-sha256-v1:' . $password));
    }
    if (str_starts_with($stored, 'rpi-simple-v1:')) {
        return hash_equals($stored, 'rpi-simple-v1:' . rpi_simple_text_hash('rpi-simple-v1:' . $password));
    }
    return hash_equals($stored, $password);
}

function rpi_imul32(int $a, int $b): int
{
    $a &= 0xffffffff;
    $b &= 0xffffffff;
    $al = $a & 0xffff;
    $ah = ($a >> 16) & 0xffff;
    $bl = $b & 0xffff;
    $bh = ($b >> 16) & 0xffff;
    return ($al * $bl + (($ah * $bl + $al * $bh) << 16)) & 0xffffffff;
}

function rpi_simple_text_hash(string $text): string
{
    $h1 = 0xdeadbeef;
    $h2 = 0x41c6ce57;
    $length = strlen($text);
    for ($i = 0; $i < $length; $i++) {
        $ch = ord($text[$i]);
        $h1 = rpi_imul32(($h1 ^ $ch), 2654435761);
        $h2 = rpi_imul32(($h2 ^ $ch), 1597334677);
    }
    $h1 = (rpi_imul32(($h1 ^ (($h1 & 0xffffffff) >> 16)), 2246822507) ^ rpi_imul32(($h2 ^ (($h2 & 0xffffffff) >> 13)), 3266489909)) & 0xffffffff;
    $h2 = (rpi_imul32(($h2 ^ (($h2 & 0xffffffff) >> 16)), 2246822507) ^ rpi_imul32(($h1 ^ (($h1 & 0xffffffff) >> 13)), 3266489909)) & 0xffffffff;
    return sprintf('%08x%08x', $h2 & 0xffffffff, $h1 & 0xffffffff);
}

function sanitize_teacher_for_client(array $teacher): array
{
    unset($teacher['password'], $teacher['passwordHash']);
    return $teacher;
}

function pdo(array $config): PDO
{
    static $pdo = null;
    if ($pdo instanceof PDO) {
        return $pdo;
    }

    $charset = $config['db_charset'] ?? 'utf8mb4';
    $dsn = sprintf('mysql:host=%s;dbname=%s;charset=%s', $config['db_host'], $config['db_name'], $charset);
    $pdo = new PDO($dsn, $config['db_user'], $config['db_pass'], [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]);

    return $pdo;
}

function load_mysql_state(?string $schoolId, array $config): array
{
    $id = normalize_school_id($schoolId);
    $db = pdo($config);
    ensure_schema_compatibility($db);
    $statement = $db->prepare('select * from rpi_schools where school_id = ? limit 1');
    $statement->execute([$id]);
    $school = $statement->fetch();

    if (!$school) {
        json_response(['error' => 'Data sekolah belum wujud di MySQL.'], 404);
    }

    $settings = decode_db_json($school['settings_json'] ?? null);
    $settings = array_merge($settings, [
        'schoolDatabaseId' => $id,
        'schoolCode' => $school['school_code'] ?: $id,
        'schoolName' => $school['school_name'] ?: ($settings['schoolName'] ?? ''),
        'rpiYear' => $school['rpi_year'] ?: ($settings['rpiYear'] ?? '2026'),
        'defaultProgram' => $school['default_program'] ?: ($settings['defaultProgram'] ?? ''),
        'subscriptionEnds' => $school['subscription_ends'] ?: ($settings['subscriptionEnds'] ?? ''),
    ]);

    $teachers = array_map('sanitize_teacher_for_client', fetch_json_rows(
        'select teacher_json from rpi_teachers where school_id = ? order by role, name',
        [$id],
        'teacher_json',
        $db
    ));

    $students = [];
    $studentRows = $db->prepare('select * from rpi_students where school_id = ? order by student_name');
    $studentRows->execute([$id]);
    foreach ($studentRows->fetchAll() as $studentRow) {
        $student = decode_db_json($studentRow['student_json'] ?? null);
        $student = array_merge($student, [
            'id' => $studentRow['student_id'],
            'teacherId' => $studentRow['teacher_id'],
            'name' => $studentRow['student_name'],
            'mykid' => $studentRow['mykid'],
            'birthDate' => $studentRow['birth_date'],
            'className' => $studentRow['class_name'],
            'category' => $studentRow['category'],
            'diagnosis' => $studentRow['diagnosis'],
            'guardian' => $studentRow['guardian'],
            'phone' => $studentRow['phone'],
            'year' => $studentRow['school_year'],
            'archived' => (bool) $studentRow['archived'],
            'goals' => [],
        ]);

        $goalRows = $db->prepare('select * from rpi_goals where school_id = ? and student_id = ? order by created_at, goal_id');
        $goalRows->execute([$id, $studentRow['student_id']]);
        foreach ($goalRows->fetchAll() as $goalRow) {
            $goal = decode_db_json($goalRow['goal_json'] ?? null);
            $goal = merge_goal_columns($goal, [
                'id' => $goalRow['goal_id'],
                'domain' => $goalRow['domain'],
                'mainChallenge' => $goalRow['main_challenge'],
                'goal' => $goalRow['goal_text'],
                'longTermObjective' => $goalRow['long_term_objective'],
                'shortTermObjective' => $goalRow['short_term_objective'],
                'strategy' => $goalRow['strategy'],
                'intervention' => $goalRow['intervention'],
                'materials' => $goalRow['materials'],
                'assessment' => $goalRow['assessment'],
                'startDate' => $goalRow['start_date'],
                'reviewDate' => $goalRow['review_date'],
                'progress' => $goalRow['progress'] === null ? null : (int) $goalRow['progress'],
                'achievements' => [],
            ]);

            $achievementRows = $db->prepare('select achievement_json from rpi_achievements where school_id = ? and student_id = ? and goal_id = ? order by achievement_date, created_at');
            $achievementRows->execute([$id, $studentRow['student_id'], $goalRow['goal_id']]);
            $goal['achievements'] = fetch_prepared_json_rows($achievementRows, 'achievement_json');
            $student['goals'][] = $goal;
        }

        $students[] = $student;
    }

    $assignmentLogs = fetch_json_rows(
        'select log_json from rpi_assignment_logs where school_id = ? order by created_at',
        [$id],
        'log_json',
        $db
    );

    $state = [
        'settings' => $settings,
        'teachers' => $teachers,
        'students' => $students,
        'assignmentLogs' => $assignmentLogs,
    ];

    return ['schoolId' => $id, 'state' => $state];
}

function merge_goal_columns(array $goal, array $columns): array
{
    foreach ($columns as $key => $value) {
        if ($key === 'progress') {
            if ($value !== null) {
                $goal[$key] = $value;
            }
            continue;
        }
        if ($value !== null && rpi_value_is_not_empty($value)) {
            $goal[$key] = $value;
        } elseif (!array_key_exists($key, $goal)) {
            $goal[$key] = $value;
        }
    }
    $goal['shortTermObjective'] = non_empty_string($goal['shortTermObjective'] ?? '') ?: non_empty_string($goal['shortGoal'] ?? '');
    $goal['shortGoal'] = non_empty_string($goal['shortGoal'] ?? '') ?: non_empty_string($goal['shortTermObjective'] ?? '');
    $goal['longTermObjective'] = non_empty_string($goal['longTermObjective'] ?? '') ?: non_empty_string($goal['annualGoal'] ?? '') ?: non_empty_string($goal['goal'] ?? '');
    $goal['annualGoal'] = non_empty_string($goal['annualGoal'] ?? '') ?: non_empty_string($goal['longTermObjective'] ?? '');
    return normalize_goal_workflow_aliases($goal);
}


function rpi_value_is_not_empty($value): bool
{
    if ($value === null) return false;
    if (is_string($value)) return trim($value) !== '';
    if (is_int($value) || is_float($value) || is_bool($value)) return true;
    if (is_array($value)) return count($value) > 0;
    if (is_object($value)) return true;
    return trim((string) $value) !== '';
}

function first_non_empty_value(array $row, array $keys): string
{
    foreach ($keys as $key) {
        $value = non_empty_string($row[$key] ?? '');
        if ($value !== '') return $value;
    }
    return '';
}

function normalize_goal_workflow_aliases(array $goal): array
{
    $teacherFirstDate = first_non_empty_value($goal, ['teacherFirstSignedDate', 'teacherSignedDate', 'firstTeacherSignedDate', 'ttGuruSidangDate']);
    $teacherFirstName = first_non_empty_value($goal, ['teacherFirstSignedName', 'teacherSignedName', 'firstTeacherSignedName', 'ttGuruSidangName']);
    $teacherReviewDate = first_non_empty_value($goal, ['teacherReviewSignedDate', 'teacherEvaluationSignedDate', 'reviewTeacherSignedDate', 'ttGuruPenilaianDate']);
    $teacherReviewName = first_non_empty_value($goal, ['teacherReviewSignedName', 'teacherEvaluationSignedName', 'reviewTeacherSignedName', 'ttGuruPenilaianName']);
    $gpkDate = first_non_empty_value($goal, ['reviewedByGpkDate', 'reviewedDate', 'gpkReviewDate', 'gpkSemakDate']);
    $gpkName = first_non_empty_value($goal, ['reviewedByGpkName', 'reviewedName', 'gpkReviewName', 'gpkName']);
    $gbDate = first_non_empty_value($goal, ['approvedByHeadDate', 'approvedByGbDate', 'approvedDate', 'gbApprovedDate', 'gbSahkanDate']);
    $gbName = first_non_empty_value($goal, ['approvedByHeadName', 'approvedByGbName', 'approvedName', 'gbApprovedName', 'gbName']);

    foreach ([
        'teacherFirstSignedDate' => $teacherFirstDate,
        'teacherSignedDate' => $teacherFirstDate,
        'teacherFirstSignedName' => $teacherFirstName,
        'teacherSignedName' => $teacherFirstName,
        'teacherReviewSignedDate' => $teacherReviewDate,
        'teacherEvaluationSignedDate' => $teacherReviewDate,
        'teacherReviewSignedName' => $teacherReviewName,
        'teacherEvaluationSignedName' => $teacherReviewName,
        'reviewedByGpkDate' => $gpkDate,
        'reviewedDate' => $gpkDate,
        'reviewedByGpkName' => $gpkName,
        'reviewedName' => $gpkName,
        'approvedByHeadDate' => $gbDate,
        'approvedByGbDate' => $gbDate,
        'approvedDate' => $gbDate,
        'approvedByHeadName' => $gbName,
        'approvedByGbName' => $gbName,
        'approvedName' => $gbName,
    ] as $key => $value) {
        if ($value !== '' && is_blank_workflow_value($goal[$key] ?? '')) {
            $goal[$key] = $value;
        }
    }
    return $goal;
}

function non_empty_string($value): string
{
    $text = trim((string) ($value ?? ''));
    return $text === '' ? '' : $text;
}


function normalize_rpi_fingerprint_text($value): string
{
    $text = mb_strtolower(trim((string) ($value ?? '')), 'UTF-8');
    $text = preg_replace('/<[^>]*>/', ' ', $text);
    $text = preg_replace('/[^\p{L}\p{N}]+/u', ' ', $text);
    $text = preg_replace('/\s+/u', ' ', trim((string) $text));
    return $text;
}

function rpi_goal_stable_key(array $student, array $goal): string
{
    $existing = trim((string) ($goal['rpiStableKey'] ?? ''));
    if ($existing !== '') {
        return $existing;
    }
    $parts = [
        trim((string) ($student['id'] ?? '')),
        trim((string) ($goal['rpiSeriesNumber'] ?? '')),
        normalize_rpi_fingerprint_text($goal['domain'] ?? ''),
        normalize_rpi_fingerprint_text($goal['mainChallenge'] ?? ''),
        normalize_rpi_fingerprint_text($goal['goal'] ?? ($goal['annualGoal'] ?? ($goal['longTermObjective'] ?? ''))),
        normalize_rpi_fingerprint_text($goal['longTermObjective'] ?? ($goal['annualGoal'] ?? ($goal['goal'] ?? ''))),
        normalize_rpi_fingerprint_text($goal['shortTermObjective'] ?? ($goal['shortGoal'] ?? '')),
        trim((string) ($goal['startDate'] ?? '')),
        trim((string) ($goal['reviewDate'] ?? '')),
    ];
    return 'rpi-stable-' . sha1(implode('|', $parts));
}

function rpi_goal_data_score(array $goal): int
{
    $achievements = is_array($goal['achievements'] ?? null) ? count($goal['achievements']) : 0;
    $evidence = is_array($goal['evidenceRecords'] ?? null) ? count($goal['evidenceRecords']) : 0;
    $worksheets = is_array($goal['worksheets'] ?? null) ? count($goal['worksheets']) : 0;
    $workflowFields = ['teacherFirstSignedDate','teacherReviewSignedDate','reviewedByGpkDate','approvedByHeadDate','parentConsentDate','parentReviewConsentDate','parentDiscussionDate'];
    $textFields = ['mainChallenge','goal','annualGoal','longTermObjective','shortTermObjective','strategy','intervention','assessment','review'];
    $workflowScore = 0;
    foreach ($workflowFields as $field) {
        if (trim((string) ($goal[$field] ?? '')) !== '') $workflowScore++;
    }
    $textScore = 0;
    foreach ($textFields as $field) {
        if (trim((string) ($goal[$field] ?? '')) !== '') $textScore++;
    }
    return $achievements * 1000 + $evidence * 800 + $worksheets * 250 + $workflowScore * 120 + $textScore * 10 + (int) ($goal['progress'] ?? 0);
}

function merge_unique_json_items(array $primary, array $secondary): array
{
    $merged = [];
    $seen = [];
    foreach (array_merge($primary, $secondary) as $index => $item) {
        if (!is_array($item)) continue;
        $id = trim((string) ($item['id'] ?? ($item['achievementId'] ?? ($item['fileId'] ?? ''))));
        if ($id === '') {
            $id = sha1(json_encode($item, JSON_UNESCAPED_UNICODE) . '|' . $index);
        }
        if (isset($seen[$id])) continue;
        $seen[$id] = true;
        $merged[] = $item;
    }
    return $merged;
}

function merge_duplicate_rpi_goal(array $primary, array $duplicate, array $student): array
{
    $base = rpi_goal_data_score($primary) >= rpi_goal_data_score($duplicate) ? $primary : $duplicate;
    $other = $base === $primary ? $duplicate : $primary;
    $merged = $base;
    foreach ($other as $key => $value) {
        $current = $merged[$key] ?? null;
        $blank = $current === null || (is_string($current) && trim($current) === '') || (is_array($current) && count($current) === 0);
        if ($blank && $value !== null && !(is_string($value) && trim($value) === '') && !(is_array($value) && count($value) === 0)) {
            $merged[$key] = $value;
        }
    }
    $merged['achievements'] = merge_unique_json_items(
        is_array($base['achievements'] ?? null) ? $base['achievements'] : [],
        is_array($other['achievements'] ?? null) ? $other['achievements'] : []
    );
    $merged['evidenceRecords'] = merge_unique_json_items(
        is_array($base['evidenceRecords'] ?? null) ? $base['evidenceRecords'] : [],
        is_array($other['evidenceRecords'] ?? null) ? $other['evidenceRecords'] : []
    );
    $merged['worksheets'] = merge_unique_json_items(
        is_array($base['worksheets'] ?? null) ? $base['worksheets'] : [],
        is_array($other['worksheets'] ?? null) ? $other['worksheets'] : []
    );
    $sourceIds = is_array($merged['duplicateSourceIds'] ?? null) ? $merged['duplicateSourceIds'] : [];
    if (!empty($other['id'])) $sourceIds[] = (string) $other['id'];
    if (is_array($other['duplicateSourceIds'] ?? null)) $sourceIds = array_merge($sourceIds, $other['duplicateSourceIds']);
    $merged['duplicateSourceIds'] = array_values(array_unique(array_filter($sourceIds)));
    $merged['duplicateMergedAt'] = $merged['duplicateMergedAt'] ?? gmdate('c');
    $merged['rpiStableKey'] = rpi_goal_stable_key($student, $merged);
    $merged['rpiDraftSaveToken'] = trim((string) ($merged['rpiDraftSaveToken'] ?? '')) ?: ('legacy:' . ($merged['id'] ?? uniqid('rpi_', true)));
    return $merged;
}

function stabilize_incoming_rpi_state(array $state): array
{
    if (!isset($state['students']) || !is_array($state['students'])) {
        return $state;
    }
    foreach ($state['students'] as $studentIndex => $student) {
        if (!is_array($student) || !isset($student['goals']) || !is_array($student['goals'])) continue;
        $stableMap = [];
        $finalGoals = [];
        foreach ($student['goals'] as $goal) {
            if (!is_array($goal)) continue;
            $goal['rpiDraftSaveToken'] = trim((string) ($goal['rpiDraftSaveToken'] ?? '')) ?: ('legacy:' . ($goal['id'] ?? uniqid('rpi_', true)));
            $goal['rpiStableKey'] = rpi_goal_stable_key($student, $goal);
            if (!empty($goal['archived']) || !empty($goal['deleted']) || trim((string) ($goal['deletedAt'] ?? '')) !== '') {
                $finalGoals[] = $goal;
                continue;
            }
            $key = $goal['rpiStableKey'];
            if (!isset($stableMap[$key])) {
                $stableMap[$key] = count($finalGoals);
                $finalGoals[] = $goal;
                continue;
            }
            $existingIndex = $stableMap[$key];
            $finalGoals[$existingIndex] = merge_duplicate_rpi_goal($finalGoals[$existingIndex], $goal, $student);
        }
        $state['students'][$studentIndex]['goals'] = $finalGoals;
    }
    return $state;
}


function acquire_mysql_named_lock(PDO $db, string $lockName, int $timeoutSeconds = 10): void
{
    $lockName = mb_substr(preg_replace('/[^a-zA-Z0-9_:\\-]/', '_', $lockName), 0, 120);
    if ($lockName === '') return;
    $statement = $db->prepare('select get_lock(?, ?)');
    $statement->execute([$lockName, $timeoutSeconds]);
    $result = (int) $statement->fetchColumn();
    if ($result !== 1) {
        throw new RuntimeException('Data sedang disimpan oleh pengguna lain. Sila cuba semula selepas beberapa saat.', 423);
    }
}

function release_mysql_named_lock(PDO $db, string $lockName): void
{
    $lockName = mb_substr(preg_replace('/[^a-zA-Z0-9_:\\-]/', '_', $lockName), 0, 120);
    if ($lockName === '') return;
    try {
        $statement = $db->prepare('select release_lock(?)');
        $statement->execute([$lockName]);
    } catch (Throwable $ignored) {
        // Jangan gagalkan response hanya kerana release lock gagal.
    }
}

function save_mysql_state(array $body, array $config): array
{
    $GLOBALS['RPI_SAVE_STEP'] = 'mula simpan';
    $schoolId = normalize_school_id($body['schoolId'] ?? null);
    if (!isset($body['state']) || !is_array($body['state'])) {
        throw new InvalidArgumentException('State tidak diterima.', 400);
    }

    $state = stabilize_incoming_rpi_state($body['state']);
    $settings = is_array($state['settings'] ?? null) ? $state['settings'] : [];
    $db = pdo($config);
    ensure_schema_compatibility($db);
    $saveLockName = 'rpi_save_' . $schoolId;
    acquire_mysql_named_lock($db, $saveLockName, 15);
    $db->beginTransaction();

    try {
        $GLOBALS['RPI_SAVE_STEP'] = 'simpan maklumat sekolah';
        $schoolCode = normalize_school_id($settings['schoolCode'] ?? $schoolId);
        $schoolName = trim((string) ($settings['schoolName'] ?? 'Akaun Subscriber'));
        $rpiYear = trim((string) ($settings['rpiYear'] ?? '2026'));
        $defaultProgram = trim((string) ($settings['defaultProgram'] ?? ''));
        $subscriptionEnds = nullable_date($settings['subscriptionEnds'] ?? null);

        $db->prepare('insert into rpi_schools
            (school_id, school_code, school_name, rpi_year, default_program, subscription_ends, settings_json)
            values (?, ?, ?, ?, ?, ?, ?)
            on duplicate key update
              school_code = values(school_code),
              school_name = values(school_name),
              rpi_year = values(rpi_year),
              default_program = values(default_program),
              subscription_ends = values(subscription_ends),
              settings_json = values(settings_json),
              updated_at = current_timestamp')
            ->execute([$schoolId, $schoolCode, $schoolName, $rpiYear, $defaultProgram, $subscriptionEnds, encode_db_json($settings)]);

        $existingTeacherPasswords = [];
        $existingPasswordRows = $db->prepare('select teacher_id, email, password from rpi_teachers where school_id = ?');
        $existingPasswordRows->execute([$schoolId]);
        foreach ($existingPasswordRows->fetchAll() as $row) {
            $passwordValue = (string) ($row['password'] ?? '');
            if ($passwordValue === '') continue;
            $existingTeacherPasswords['id:' . $row['teacher_id']] = $passwordValue;
            $existingTeacherPasswords['email:' . normalize_staff_username($row['email'] ?? '')] = $passwordValue;
        }

        $incomingStudents = array_values(array_filter(($state['students'] ?? []), 'is_array'));
        $incomingTeachers = array_values(array_filter(($state['teachers'] ?? []), 'is_array'));
        $countStatement = $db->prepare('select count(*) from rpi_students where school_id = ?');
        $countStatement->execute([$schoolId]);
        $existingStudentCount = (int) $countStatement->fetchColumn();
        if ($existingStudentCount > 0 && count($incomingStudents) === 0 && empty($state['allowEmptyOverwrite'])) {
            throw new RuntimeException('Simpanan kosong disekat untuk melindungi data murid sedia ada. Sila refresh dan sync semula sebelum simpan.', 409);
        }
        $teacherCountStatement = $db->prepare('select count(*) from rpi_teachers where school_id = ?');
        $teacherCountStatement->execute([$schoolId]);
        $existingTeacherCount = (int) $teacherCountStatement->fetchColumn();
        if ($existingTeacherCount > 0 && count($incomingTeachers) === 0 && empty($state['allowEmptyOverwrite'])) {
            throw new RuntimeException('Simpanan kosong disekat untuk melindungi akaun pengguna sedia ada. Sila refresh dan sync semula sebelum simpan.', 409);
        }
        $existingGoalWorkflow = existing_goal_workflow_map($schoolId, $db);
        backup_school_before_save($schoolId, $db, 'before_save');

        // Pastikan backup kekal walaupun proses overwrite selepas ini gagal.
        $db->commit();
        $db->beginTransaction();

        delete_school_children($schoolId, $db);

        $teacherEmails = [];
        $teacherIds = [];
        foreach (($state['teachers'] ?? []) as $teacher) {
            if (!is_array($teacher)) continue;
            $GLOBALS['RPI_SAVE_STEP'] = 'simpan akaun guru';
            $teacherId = trim((string) ($teacher['id'] ?? ''));
            if ($teacherId === '') continue;
            if (isset($teacherIds[$teacherId])) continue;
            $teacherIds[$teacherId] = true;
            $teacherEmail = normalize_staff_username($teacher['email'] ?? '');
            if ($teacherEmail === '') {
                $teacherEmail = normalize_staff_username($teacher['name'] ?? '');
            }
            if ($teacherEmail === '') continue;
            if ($teacherEmail !== '') {
                if (isset($teacherEmails[$teacherEmail])) continue;
                $teacherEmails[$teacherEmail] = true;
            }
            $storedPassword = (string) ($teacher['passwordHash'] ?? ($teacher['password'] ?? ''));
            if ($storedPassword === '') {
                $storedPassword = $existingTeacherPasswords['id:' . $teacherId]
                    ?? $existingTeacherPasswords['email:' . $teacherEmail]
                    ?? '';
            }
            $safeTeacherJson = $teacher;
            $safeTeacherJson['email'] = $teacherEmail;
            $safeTeacherJson = sanitize_teacher_for_client($safeTeacherJson);
            $db->prepare('insert into rpi_teachers
                (school_id, teacher_id, name, email, password, role, last_login_at, login_count, teacher_json)
                values (?, ?, ?, ?, ?, ?, ?, ?, ?)')
                ->execute([
                    $schoolId,
                    $teacherId,
                    trim((string) ($teacher['name'] ?? 'Tanpa Nama')),
                    $teacherEmail,
                    $storedPassword,
                    normalize_role($teacher['role'] ?? 'teacher'),
                    nullable_text($teacher['lastLoginAt'] ?? null),
                    (int) ($teacher['loginCount'] ?? 0),
                    encode_db_json($safeTeacherJson),
                ]);
        }

        $studentIds = [];
        foreach (($state['students'] ?? []) as $student) {
            if (!is_array($student)) continue;
            $GLOBALS['RPI_SAVE_STEP'] = 'simpan data murid';
            $studentId = trim((string) ($student['id'] ?? ''));
            if ($studentId === '') continue;
            if (isset($studentIds[$studentId])) continue;
            $studentIds[$studentId] = true;
            $db->prepare('insert into rpi_students
                (school_id, student_id, teacher_id, student_name, mykid, birth_date, class_name, category, diagnosis, guardian, phone, school_year, archived, student_json)
                values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)')
                ->execute([
                    $schoolId,
                    $studentId,
                    nullable_text($student['teacherId'] ?? null),
                    trim((string) ($student['name'] ?? 'Tanpa Nama')),
                    nullable_text($student['mykid'] ?? null),
                    nullable_text($student['birthDate'] ?? null),
                    nullable_text($student['className'] ?? null),
                    nullable_text($student['category'] ?? null),
                    nullable_text($student['diagnosis'] ?? null),
                    nullable_text($student['guardian'] ?? null),
                    nullable_text($student['phone'] ?? null),
                    nullable_text($student['year'] ?? null),
                    !empty($student['archived']) ? 1 : 0,
                    encode_db_json($student),
                ]);

            $goalIds = [];
            foreach (($student['goals'] ?? []) as $goal) {
                if (!is_array($goal)) continue;
                $GLOBALS['RPI_SAVE_STEP'] = 'simpan RPI murid';
                $goalId = trim((string) ($goal['id'] ?? ''));
                if ($goalId === '') continue;
                if (isset($goalIds[$goalId])) continue;
                $goalIds[$goalId] = true;
                $goal = preserve_goal_workflow_fields($goal, $existingGoalWorkflow[$studentId . '::' . $goalId] ?? []);
                $db->prepare('insert into rpi_goals
                    (school_id, student_id, goal_id, domain, main_challenge, goal_text, long_term_objective,
                     short_term_objective, strategy, intervention, materials, assessment, start_date, review_date, progress, goal_json)
                    values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)')
                    ->execute([
                        $schoolId,
                        $studentId,
                        $goalId,
                        nullable_text($goal['domain'] ?? null),
                        nullable_text($goal['mainChallenge'] ?? null),
                        nullable_text($goal['goal'] ?? ($goal['annualGoal'] ?? null)),
                        nullable_text($goal['longTermObjective'] ?? null),
                        nullable_text($goal['shortTermObjective'] ?? ($goal['shortGoal'] ?? null)),
                        nullable_text($goal['strategy'] ?? null),
                        nullable_text($goal['intervention'] ?? null),
                        nullable_text($goal['materials'] ?? null),
                        nullable_text($goal['assessment'] ?? null),
                        nullable_text($goal['startDate'] ?? null),
                        nullable_text($goal['reviewDate'] ?? null),
                        isset($goal['progress']) ? (int) $goal['progress'] : null,
                        encode_db_json($goal),
                    ]);

                $achievementIds = [];
                foreach (($goal['achievements'] ?? []) as $achievement) {
                    if (!is_array($achievement)) continue;
                    $GLOBALS['RPI_SAVE_STEP'] = 'simpan pencapaian murid';
                    $achievementId = trim((string) ($achievement['id'] ?? ''));
                    if ($achievementId === '') continue;
                    if (isset($achievementIds[$achievementId])) continue;
                    $achievementIds[$achievementId] = true;
                    $db->prepare('insert into rpi_achievements
                        (school_id, student_id, goal_id, achievement_id, achievement_date, teacher_id, teacher_name,
                         teacher_text, standard_text, achievement_json)
                        values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)')
                        ->execute([
                            $schoolId,
                            $studentId,
                            $goalId,
                            $achievementId,
                            nullable_text($achievement['date'] ?? null),
                            nullable_text($achievement['teacherId'] ?? null),
                            nullable_text($achievement['teacherName'] ?? null),
                            nullable_text($achievement['teacherText'] ?? null),
                            nullable_text($achievement['standardText'] ?? null),
                            encode_db_json($achievement),
                        ]);
                }
            }
        }

        $logIds = [];
        foreach (($state['assignmentLogs'] ?? []) as $index => $log) {
            if (!is_array($log)) continue;
            $GLOBALS['RPI_SAVE_STEP'] = 'simpan log tugasan';
            $logId = trim((string) ($log['id'] ?? 'log-' . $index));
            if ($logId === '' || isset($logIds[$logId])) continue;
            $logIds[$logId] = true;
            $db->prepare('insert into rpi_assignment_logs
                (school_id, log_id, log_date, teacher_id, action_text, log_json)
                values (?, ?, ?, ?, ?, ?)')
                ->execute([
                    $schoolId,
                    $logId,
                    nullable_text($log['date'] ?? ($log['createdAt'] ?? null)),
                    nullable_text($log['teacherId'] ?? null),
                    nullable_text($log['action'] ?? ($log['message'] ?? null)),
                    encode_db_json($log),
                ]);
        }

        $db->commit();

        $savedCounts = mysql_school_counts($db, $schoolId);
    } catch (Throwable $error) {
        if ($db->inTransaction()) {
            $db->rollBack();
        }
        release_mysql_named_lock($db, $saveLockName ?? '');
        throw $error;
    }

    $finalCounts = $savedCounts ?? mysql_school_counts($db, $schoolId);
    system_append_audit_log($schoolId, [
        'actor' => is_array($body['actor'] ?? null) ? $body['actor'] : [],
        'action' => 'school_data_save',
        'actionLabel' => 'Simpan Data Sekolah ke MySQL',
        'entityType' => 'school_database',
        'entityId' => $schoolId,
        'entityName' => trim((string) ($settings['schoolName'] ?? $schoolId)),
        'status' => 'success',
        'detail' => ['counts' => $finalCounts],
    ], $config);

    release_mysql_named_lock($db, $saveLockName ?? '');
    return ['ok' => true, 'schoolId' => $schoolId, 'counts' => $finalCounts];
}


function save_achievement_action(array $body, array $config, string $mode): array
{
    $schoolId = normalize_school_id($body['schoolId'] ?? null);
    $studentId = trim((string) ($body['studentId'] ?? ''));
    $goalId = trim((string) ($body['goalId'] ?? ''));
    $achievement = is_array($body['achievement'] ?? null) ? $body['achievement'] : [];
    $achievementId = trim((string) ($body['achievementId'] ?? ($achievement['id'] ?? '')));

    if ($schoolId === '' || $studentId === '' || $goalId === '') {
        throw new InvalidArgumentException('Kod sekolah, murid dan RPI diperlukan untuk simpan pencapaian.', 400);
    }
    if ($achievementId === '') {
        throw new InvalidArgumentException('ID pencapaian diperlukan.', 400);
    }

    $db = pdo($config);
    ensure_schema_compatibility($db);
    $lockName = 'rpi_achievement_' . substr(hash('sha256', $schoolId . '|' . $studentId . '|' . $goalId . '|' . $achievementId), 0, 48);
    $lockAcquired = false;

    try {
        $lockStmt = $db->prepare('select get_lock(?, 8)');
        $lockStmt->execute([$lockName]);
        $lockAcquired = ((int) $lockStmt->fetchColumn()) === 1;
        if (!$lockAcquired) {
            throw new RuntimeException('Rekod pencapaian sedang disimpan oleh proses lain. Sila cuba semula beberapa saat lagi.', 409);
        }
    } catch (Throwable $error) {
        // If GET_LOCK is not available on the hosting, continue with transaction protection.
        if ($error->getCode() === 409) throw $error;
        $lockAcquired = false;
    }

    $db->beginTransaction();
    try {
        $goal = fetch_goal_json_for_update($db, $schoolId, $studentId, $goalId);
        if (!$goal) {
            throw new RuntimeException('Rekod RPI tidak ditemui di MySQL. Sila refresh data sebelum simpan pencapaian.', 404);
        }

        if ($mode === 'hard-delete') {
            $db->prepare('delete from rpi_achievements where school_id = ? and student_id = ? and goal_id = ? and achievement_id = ?')
                ->execute([$schoolId, $studentId, $goalId, $achievementId]);
            $goal = remove_achievement_from_goal_json($goal, $achievementId);
            update_goal_json_only($db, $schoolId, $studentId, $goalId, $goal);
            $db->commit();
            append_achievement_audit($schoolId, $body, $config, 'achievement_hard_delete', 'Delete Kekal Pencapaian', $studentId, $goalId, $achievementId, 'success');
            return ['ok' => true, 'mode' => $mode, 'schoolId' => $schoolId, 'studentId' => $studentId, 'goalId' => $goalId, 'achievementId' => $achievementId];
        }

        $existing = fetch_achievement_json_for_update($db, $schoolId, $studentId, $goalId, $achievementId);
        if ($mode === 'delete') {
            $achievement = $existing ?: $achievement;
            if (!$achievement) {
                throw new RuntimeException('Rekod pencapaian tidak ditemui untuk dipadam.', 404);
            }
            $achievement['deletedAt'] = $achievement['deletedAt'] ?? date('c');
            $achievement['deletedBy'] = $achievement['deletedBy'] ?? (audit_actor_from_body($body)['userId'] ?? '');
        } elseif ($mode === 'restore') {
            $achievement = $existing ?: $achievement;
            if (!$achievement) {
                throw new RuntimeException('Rekod pencapaian tidak ditemui untuk dipulihkan.', 404);
            }
            $achievement['deletedAt'] = '';
            $achievement['deletedBy'] = '';
            $achievement['restoredAt'] = date('c');
        } else {
            if (!$achievement) {
                throw new InvalidArgumentException('Data pencapaian tidak diterima.', 400);
            }
            $achievement['id'] = $achievementId;
            if ($mode === 'create' && !$existing) {
                $achievement['createdAt'] = $achievement['createdAt'] ?? date('c');
            }
            $achievement['updatedAt'] = $achievement['updatedAt'] ?? date('c');
        }

        upsert_achievement_row($db, $schoolId, $studentId, $goalId, $achievementId, $achievement);
        $goal = merge_achievement_into_goal_json($goal, $achievement);
        if (is_array($body['goalPatch'] ?? null)) {
            $goal = apply_safe_goal_patch($goal, $body['goalPatch']);
        }
        update_goal_json_only($db, $schoolId, $studentId, $goalId, $goal);
        $db->commit();

        $actionMap = [
            'create' => ['achievement_create', 'Tambah Pencapaian'],
            'update' => ['achievement_update', 'Kemaskini Pencapaian'],
            'delete' => ['achievement_delete', 'Padam Pencapaian'],
            'restore' => ['achievement_restore', 'Restore Pencapaian'],
        ];
        [$action, $label] = $actionMap[$mode] ?? ['achievement_save', 'Simpan Pencapaian'];
        append_achievement_audit($schoolId, $body, $config, $action, $label, $studentId, $goalId, $achievementId, 'success');

        return [
            'ok' => true,
            'mode' => $mode,
            'schoolId' => $schoolId,
            'studentId' => $studentId,
            'goalId' => $goalId,
            'achievementId' => $achievementId,
            'achievement' => $achievement,
        ];
    } catch (Throwable $error) {
        if ($db->inTransaction()) $db->rollBack();
        append_achievement_audit($schoolId, $body, $config, 'achievement_' . $mode, 'Pencapaian Gagal Disimpan', $studentId, $goalId, $achievementId, 'error', safe_error_detail($error->getMessage()));
        throw $error;
    } finally {
        if ($lockAcquired) {
            try {
                $release = $db->prepare('select release_lock(?)');
                $release->execute([$lockName]);
            } catch (Throwable $ignored) {}
        }
    }
}

function fetch_goal_json_for_update(PDO $db, string $schoolId, string $studentId, string $goalId): array
{
    $stmt = $db->prepare('select goal_json from rpi_goals where school_id = ? and student_id = ? and goal_id = ? for update');
    $stmt->execute([$schoolId, $studentId, $goalId]);
    $row = $stmt->fetch();
    if (!$row) return [];
    $goal = decode_db_json($row['goal_json'] ?? '');
    return is_array($goal) ? $goal : [];
}

function fetch_achievement_json_for_update(PDO $db, string $schoolId, string $studentId, string $goalId, string $achievementId): array
{
    $stmt = $db->prepare('select achievement_json from rpi_achievements where school_id = ? and student_id = ? and goal_id = ? and achievement_id = ? for update');
    $stmt->execute([$schoolId, $studentId, $goalId, $achievementId]);
    $row = $stmt->fetch();
    if (!$row) return [];
    $achievement = decode_db_json($row['achievement_json'] ?? '');
    return is_array($achievement) ? $achievement : [];
}

function upsert_achievement_row(PDO $db, string $schoolId, string $studentId, string $goalId, string $achievementId, array $achievement): void
{
    $db->prepare('insert into rpi_achievements
        (school_id, student_id, goal_id, achievement_id, achievement_date, teacher_id, teacher_name, teacher_text, standard_text, achievement_json)
        values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        on duplicate key update
          achievement_date = values(achievement_date),
          teacher_id = values(teacher_id),
          teacher_name = values(teacher_name),
          teacher_text = values(teacher_text),
          standard_text = values(standard_text),
          achievement_json = values(achievement_json),
          updated_at = current_timestamp')
        ->execute([
            $schoolId,
            $studentId,
            $goalId,
            $achievementId,
            nullable_text($achievement['date'] ?? null),
            nullable_text($achievement['teacherId'] ?? null),
            nullable_text($achievement['teacherName'] ?? null),
            nullable_text($achievement['teacherText'] ?? null),
            nullable_text($achievement['standardText'] ?? null),
            encode_db_json($achievement),
        ]);
}

function merge_achievement_into_goal_json(array $goal, array $achievement): array
{
    $goal['achievements'] = is_array($goal['achievements'] ?? null) ? $goal['achievements'] : [];
    $achievementId = trim((string) ($achievement['id'] ?? ''));
    if ($achievementId === '') return $goal;
    $updated = false;
    foreach ($goal['achievements'] as $index => $item) {
        if (is_array($item) && trim((string) ($item['id'] ?? '')) === $achievementId) {
            $goal['achievements'][$index] = array_replace($item, $achievement);
            $updated = true;
            break;
        }
    }
    if (!$updated) $goal['achievements'][] = $achievement;
    return $goal;
}

function remove_achievement_from_goal_json(array $goal, string $achievementId): array
{
    $goal['achievements'] = array_values(array_filter(is_array($goal['achievements'] ?? null) ? $goal['achievements'] : [], function ($item) use ($achievementId) {
        return !is_array($item) || trim((string) ($item['id'] ?? '')) !== $achievementId;
    }));
    return $goal;
}

function apply_safe_goal_patch(array $goal, array $patch): array
{
    $allowed = ['review', 'progress', 'updatedAt', 'updatedBy'];
    foreach ($allowed as $key) {
        if (array_key_exists($key, $patch)) $goal[$key] = $patch[$key];
    }
    return $goal;
}

function update_goal_json_only(PDO $db, string $schoolId, string $studentId, string $goalId, array $goal): void
{
    $db->prepare('update rpi_goals set goal_json = ?, progress = ?, updated_at = current_timestamp where school_id = ? and student_id = ? and goal_id = ?')
        ->execute([
            encode_db_json($goal),
            isset($goal['progress']) ? (int) $goal['progress'] : null,
            $schoolId,
            $studentId,
            $goalId,
        ]);
}

function append_achievement_audit(string $schoolId, array $body, array $config, string $action, string $label, string $studentId, string $goalId, string $achievementId, string $status = 'success', string $message = ''): void
{
    $detail = [
        'studentId' => $studentId,
        'goalId' => $goalId,
        'achievementId' => $achievementId,
    ];
    if ($message !== '') $detail['message'] = $message;
    system_append_audit_log($schoolId, [
        'actor' => is_array($body['actor'] ?? null) ? $body['actor'] : [],
        'action' => $action,
        'actionLabel' => $label,
        'entityType' => 'achievement',
        'entityId' => $achievementId,
        'entityName' => 'Rekod Pencapaian',
        'status' => $status,
        'detail' => $detail,
    ], $config);
}


function scan_duplicate_rpi(array $body, array $config): array
{
    $schoolId = normalize_school_id($body['schoolId'] ?? null);
    if ($schoolId === '') {
        throw new InvalidArgumentException('Kod database diperlukan.', 400);
    }

    $db = pdo($config);
    ensure_schema_compatibility($db);

    $sql = 'select g.student_id, s.student_name, g.goal_id, g.domain, g.main_challenge, g.goal_text,
                   g.long_term_objective, g.short_term_objective, g.start_date, g.review_date,
                   g.created_at, g.updated_at, g.goal_json
            from rpi_goals g
            left join rpi_students s on s.school_id = g.school_id and s.student_id = g.student_id
            where g.school_id = ?
            order by s.student_name, g.student_id, g.created_at, g.goal_id';
    $statement = $db->prepare($sql);
    $statement->execute([$schoolId]);

    $buckets = [];
    foreach ($statement->fetchAll() as $row) {
        $goal = decode_db_json($row['goal_json'] ?? null);
        if (!is_array($goal)) {
            $goal = [];
        }
        if (is_goal_archived_or_deleted($goal)) {
            continue;
        }

        $signature = duplicate_rpi_signature($row, $goal);
        if ($signature === '') {
            continue;
        }
        $key = ($row['student_id'] ?? '') . '::' . $signature;
        $buckets[$key][] = duplicate_rpi_summary_row($row, $goal, $db, $schoolId);
    }

    $groups = [];
    foreach ($buckets as $items) {
        if (count($items) < 2) {
            continue;
        }
        usort($items, static function ($a, $b) {
            $score = ($b['keepScore'] ?? 0) <=> ($a['keepScore'] ?? 0);
            if ($score !== 0) return $score;
            return strcmp((string) ($a['createdAt'] ?? ''), (string) ($b['createdAt'] ?? ''));
        });
        $first = $items[0];
        $groups[] = [
            'studentId' => $first['studentId'],
            'studentName' => $first['studentName'],
            'items' => $items,
        ];
    }

    usort($groups, static function ($a, $b) {
        return strcmp((string) ($a['studentName'] ?? ''), (string) ($b['studentName'] ?? ''));
    });

    return ['ok' => true, 'schoolId' => $schoolId, 'groups' => $groups];
}


function archive_duplicate_rpi_bulk(array $body, array $config): array
{
    $schoolId = normalize_school_id($body['schoolId'] ?? null);
    $records = $body['records'] ?? [];
    $safeOnly = !empty($body['safeOnly']);
    if ($schoolId === '' || !is_array($records) || count($records) === 0) {
        throw new InvalidArgumentException('Maklumat schoolId dan senarai rekod diperlukan.', 400);
    }
    if (count($records) > 200) {
        throw new InvalidArgumentException('Terlalu banyak rekod untuk satu proses. Had maksimum ialah 200 rekod.', 400);
    }

    $db = pdo($config);
    ensure_schema_compatibility($db);
    $select = $db->prepare('select * from rpi_goals where school_id = ? and student_id = ? and goal_id = ? limit 1');
    $update = $db->prepare('update rpi_goals set goal_json = ?, updated_at = current_timestamp where school_id = ? and student_id = ? and goal_id = ?');

    $archived = [];
    $skipped = [];

    $db->beginTransaction();
    try {
        backup_school_before_save($schoolId, $db, 'before_archive_duplicate_rpi_bulk');

        foreach ($records as $record) {
            if (!is_array($record)) continue;
            $studentId = trim((string) ($record['studentId'] ?? ''));
            $goalId = trim((string) ($record['goalId'] ?? ''));
            if ($studentId === '' || $goalId === '') {
                $skipped[] = ['studentId' => $studentId, 'goalId' => $goalId, 'reason' => 'Maklumat tidak lengkap'];
                continue;
            }

            $select->execute([$schoolId, $studentId, $goalId]);
            $row = $select->fetch();
            if (!$row) {
                $skipped[] = ['studentId' => $studentId, 'goalId' => $goalId, 'reason' => 'Rekod tidak ditemui'];
                continue;
            }

            $goal = decode_db_json($row['goal_json'] ?? null);
            if (!is_array($goal)) {
                $goal = [];
            }
            if (is_goal_archived_or_deleted($goal)) {
                $skipped[] = ['studentId' => $studentId, 'goalId' => $goalId, 'reason' => 'Sudah diarkibkan'];
                continue;
            }

            if ($safeOnly) {
                $counts = duplicate_rpi_related_counts($db, $schoolId, $studentId, $goalId, $goal);
                if (($counts['achievements'] ?? 0) > 0 || ($counts['evidence'] ?? 0) > 0 || ($counts['workflow'] ?? 0) > 0) {
                    $skipped[] = ['studentId' => $studentId, 'goalId' => $goalId, 'reason' => 'Ada pencapaian/eviden/pengesahan'];
                    continue;
                }
            }

            $goal['archived'] = true;
            $goal['archivedAt'] = gmdate('c');
            $goal['archivedReason'] = mb_substr(trim((string) ($record['reason'] ?? ($body['reason'] ?? 'Duplicate RPI'))), 0, 220);
            $goal['duplicateArchived'] = true;
            $goal['duplicateArchivedAt'] = gmdate('c');
            $goal['duplicateOf'] = mb_substr(trim((string) ($record['duplicateOf'] ?? ($body['duplicateOf'] ?? ''))), 0, 120);
            $goal['updatedAt'] = gmdate('c');

            $update->execute([encode_db_json($goal), $schoolId, $studentId, $goalId]);
            $archived[] = ['studentId' => $studentId, 'goalId' => $goalId];
        }

        $db->commit();
    } catch (Throwable $error) {
        if ($db->inTransaction()) {
            $db->rollBack();
        }
        throw $error;
    }

    system_append_audit_log($schoolId, [
        'actor' => is_array($body['actor'] ?? null) ? $body['actor'] : [],
        'action' => 'duplicate_rpi_archive_bulk',
        'actionLabel' => 'Arkib duplicate RPI secara pukal',
        'entityType' => 'rpi',
        'entityId' => 'bulk',
        'entityName' => 'Duplicate RPI',
        'detail' => ['archivedCount' => count($archived), 'skippedCount' => count($skipped)],
    ], $config);

    return [
        'ok' => true,
        'schoolId' => $schoolId,
        'archivedCount' => count($archived),
        'skippedCount' => count($skipped),
        'archived' => $archived,
        'skipped' => $skipped,
    ];
}

function archive_duplicate_rpi(array $body, array $config): array
{
    $schoolId = normalize_school_id($body['schoolId'] ?? null);
    $studentId = trim((string) ($body['studentId'] ?? ''));
    $goalId = trim((string) ($body['goalId'] ?? ''));
    if ($schoolId === '' || $studentId === '' || $goalId === '') {
        throw new InvalidArgumentException('Maklumat schoolId, studentId dan goalId diperlukan.', 400);
    }

    $db = pdo($config);
    ensure_schema_compatibility($db);

    $statement = $db->prepare('select * from rpi_goals where school_id = ? and student_id = ? and goal_id = ? limit 1');
    $statement->execute([$schoolId, $studentId, $goalId]);
    $row = $statement->fetch();
    if (!$row) {
        throw new RuntimeException('Rekod RPI tidak ditemui.', 404);
    }

    $goal = decode_db_json($row['goal_json'] ?? null);
    if (!is_array($goal)) {
        $goal = [];
    }
    if (is_goal_archived_or_deleted($goal)) {
        return ['ok' => true, 'schoolId' => $schoolId, 'studentId' => $studentId, 'goalId' => $goalId, 'alreadyArchived' => true];
    }

    $db->beginTransaction();
    try {
        backup_school_before_save($schoolId, $db, 'before_archive_duplicate_rpi');

        $goal['archived'] = true;
        $goal['archivedAt'] = gmdate('c');
        $goal['archivedReason'] = mb_substr(trim((string) ($body['reason'] ?? 'Duplicate RPI')), 0, 220);
        $goal['duplicateArchived'] = true;
        $goal['duplicateArchivedAt'] = gmdate('c');
        $goal['duplicateOf'] = mb_substr(trim((string) ($body['duplicateOf'] ?? '')), 0, 120);
        $goal['updatedAt'] = gmdate('c');

        $db->prepare('update rpi_goals set goal_json = ?, updated_at = current_timestamp where school_id = ? and student_id = ? and goal_id = ?')
            ->execute([encode_db_json($goal), $schoolId, $studentId, $goalId]);

        $db->commit();
    } catch (Throwable $error) {
        if ($db->inTransaction()) {
            $db->rollBack();
        }
        throw $error;
    }

    system_append_audit_log($schoolId, [
        'actor' => is_array($body['actor'] ?? null) ? $body['actor'] : [],
        'action' => 'duplicate_rpi_archive',
        'actionLabel' => 'Arkib duplicate RPI',
        'entityType' => 'rpi',
        'entityId' => $goalId,
        'entityName' => $goal['rpiName'] ?? ($goal['rpiFullName'] ?? 'RPI'),
        'detail' => ['studentId' => $studentId, 'reason' => $goal['archivedReason'] ?? 'Duplicate RPI'],
    ], $config);

    return ['ok' => true, 'schoolId' => $schoolId, 'studentId' => $studentId, 'goalId' => $goalId, 'archived' => true];
}

function is_goal_archived_or_deleted(array $goal): bool
{
    if (!empty($goal['deletedAt']) || !empty($goal['archivedAt'])) return true;
    if (!empty($goal['deleted']) || !empty($goal['archived'])) return true;
    return false;
}

function duplicate_rpi_signature(array $row, array $goal): string
{
    $parts = [
        $row['student_id'] ?? '',
        $row['domain'] ?? ($goal['domain'] ?? ''),
        $row['main_challenge'] ?? ($goal['mainChallenge'] ?? ''),
        $row['long_term_objective'] ?? ($goal['longTermObjective'] ?? ($goal['annualGoal'] ?? '')),
        $row['short_term_objective'] ?? ($goal['shortTermObjective'] ?? ($goal['shortGoal'] ?? '')),
        $row['start_date'] ?? ($goal['startDate'] ?? ''),
        $row['review_date'] ?? ($goal['reviewDate'] ?? ''),
    ];
    $normalized = array_map('duplicate_rpi_normalize_text', $parts);
    $joined = implode('|', $normalized);
    $joined = trim($joined, '| ');
    if (strlen(str_replace('|', '', $joined)) < 20) {
        return '';
    }
    return hash('sha256', $joined);
}

function duplicate_rpi_normalize_text($value): string
{
    $text = mb_strtolower(trim((string) ($value ?? '')));
    $text = preg_replace('/\s+/u', ' ', $text);
    $text = preg_replace('/[^\p{L}\p{N}\s\-\/\.]/u', '', $text);
    return trim($text);
}

function duplicate_rpi_summary_row(array $row, array $goal, PDO $db, string $schoolId): array
{
    $counts = duplicate_rpi_related_counts($db, $schoolId, (string) $row['student_id'], (string) $row['goal_id'], $goal);
    $workflowScore = $counts['workflow'] ?? 0;
    $keepScore = (($counts['achievements'] ?? 0) * 10) + (($counts['evidence'] ?? 0) * 8) + ($workflowScore * 20);
    $createdAt = (string) ($row['created_at'] ?? ($goal['createdAt'] ?? ''));
    $updatedAt = (string) ($row['updated_at'] ?? ($goal['updatedAt'] ?? ''));
    return [
        'studentId' => (string) ($row['student_id'] ?? ''),
        'studentName' => (string) ($row['student_name'] ?? 'Murid'),
        'goalId' => (string) ($row['goal_id'] ?? ''),
        'rpiName' => (string) ($goal['rpiName'] ?? ($goal['rpiFullName'] ?? ($goal['name'] ?? ''))),
        'domain' => (string) ($row['domain'] ?? ($goal['domain'] ?? '')),
        'mainChallenge' => (string) ($row['main_challenge'] ?? ($goal['mainChallenge'] ?? '')),
        'goalText' => (string) ($row['goal_text'] ?? ($goal['goal'] ?? '')),
        'shortTermObjective' => (string) ($row['short_term_objective'] ?? ($goal['shortTermObjective'] ?? ($goal['shortGoal'] ?? ''))),
        'createdAt' => $createdAt,
        'updatedAt' => $updatedAt,
        'counts' => $counts,
        'keepScore' => $keepScore,
    ];
}

function duplicate_rpi_related_counts(PDO $db, string $schoolId, string $studentId, string $goalId, array $goal): array
{
    $achievementStatement = $db->prepare('select count(*) from rpi_achievements where school_id = ? and student_id = ? and goal_id = ?');
    $achievementStatement->execute([$schoolId, $studentId, $goalId]);
    $achievementCount = (int) $achievementStatement->fetchColumn();

    $evidenceCount = 0;
    foreach (($goal['evidenceRecords'] ?? []) as $item) {
        if (is_array($item) && empty($item['deletedAt'])) {
            $evidenceCount++;
        }
    }

    $workflowFields = [
        'reviewedByGpkDate', 'reviewedDate', 'approvedByHeadDate', 'approvedDate',
        'teacherFirstSignedDate', 'teacherReviewSignedDate',
        'parentConsentDate', 'parentReviewConsentDate'
    ];
    $workflowCount = 0;
    foreach ($workflowFields as $field) {
        if (isset($goal[$field]) && trim((string) $goal[$field]) !== '') {
            $workflowCount++;
        }
    }

    return ['achievements' => $achievementCount, 'evidence' => $evidenceCount, 'workflow' => $workflowCount];
}

function mysql_school_counts(PDO $db, string $schoolId): array
{
    $tables = [
        'schools' => 'rpi_schools',
        'teachers' => 'rpi_teachers',
        'students' => 'rpi_students',
        'goals' => 'rpi_goals',
        'achievements' => 'rpi_achievements',
        'assignmentLogs' => 'rpi_assignment_logs',
    ];
    $counts = [];
    foreach ($tables as $key => $table) {
        $statement = $db->prepare("select count(*) from {$table} where school_id = ?");
        $statement->execute([$schoolId]);
        $counts[$key] = (int) $statement->fetchColumn();
    }
    return $counts;
}

function mysql_status(array $config): array
{
    $db = pdo($config);
    ensure_schema_compatibility($db);
    $statement = $db->query("show tables like 'rpi_schools'");
    $tableExists = (bool) $statement->fetchColumn();
    $count = 0;
    if ($tableExists) {
        $count = (int) $db->query('select count(*) from rpi_schools')->fetchColumn();
    }

    return [
        'ok' => true,
        'database' => $config['db_name'] ?? '',
        'table' => 'rpi_schools',
        'tableExists' => $tableExists,
        'rows' => $count,
    ];
}

function append_document_log(array $body, array $config): array
{
    $schoolId = normalize_school_id($body['schoolId'] ?? null);
    $log = is_array($body['log'] ?? null) ? $body['log'] : [];
    if ($schoolId === '' || empty($log)) {
        throw new InvalidArgumentException('Maklumat log dokumen tidak lengkap.', 400);
    }

    $db = pdo($config);
    ensure_schema_compatibility($db);
    $statement = $db->prepare('select settings_json from rpi_schools where school_id = ? limit 1');
    $statement->execute([$schoolId]);
    $school = $statement->fetch();
    if (!$school) {
        throw new RuntimeException('Data sekolah belum wujud di MySQL.', 404);
    }

    $settings = decode_db_json($school['settings_json'] ?? null);
    $logs = is_array($settings['documentLogs'] ?? null) ? $settings['documentLogs'] : [];
    $safeLog = [
        'id' => trim((string) ($log['id'] ?? uniqid('doclog_', true))),
        'schoolId' => $schoolId,
        'schoolName' => mb_substr(trim((string) ($log['schoolName'] ?? '')), 0, 180),
        'userId' => mb_substr(trim((string) ($log['userId'] ?? '')), 0, 120),
        'userName' => mb_substr(trim((string) ($log['userName'] ?? '')), 0, 180),
        'userRole' => mb_substr(trim((string) ($log['userRole'] ?? '')), 0, 80),
        'documentId' => mb_substr(trim((string) ($log['documentId'] ?? '')), 0, 80),
        'documentTitle' => mb_substr(trim((string) ($log['documentTitle'] ?? '')), 0, 220),
        'action' => 'download',
        'createdAt' => trim((string) ($log['createdAt'] ?? gmdate('c'))),
        'browser' => mb_substr(trim((string) ($log['browser'] ?? '')), 0, 180),
    ];
    $existingIndex = null;
    foreach ($logs as $index => $item) {
        if (
            ($item['schoolId'] ?? '') === $safeLog['schoolId']
            && ($item['userId'] ?? '') === $safeLog['userId']
            && ($item['documentId'] ?? '') === $safeLog['documentId']
            && ($item['action'] ?? '') === 'download'
        ) {
            $existingIndex = $index;
            break;
        }
    }
    if ($existingIndex === null) {
        $logs[] = $safeLog;
    } else {
        $logs[$existingIndex] = array_merge($logs[$existingIndex], [
            'createdAt' => $safeLog['createdAt'],
            'browser' => $safeLog['browser'],
        ]);
    }
    if (count($logs) > 500) {
        $logs = array_slice($logs, -500);
    }
    $settings['documentLogs'] = $logs;

    $db->prepare('update rpi_schools set settings_json = ?, updated_at = current_timestamp where school_id = ?')
        ->execute([encode_db_json($settings), $schoolId]);

    return ['ok' => true, 'count' => count($logs), 'log' => $safeLog];
}

function load_subscribers(array $config): array
{
    $db = pdo($config);
    ensure_schema_compatibility($db);
    $rows = $db->query('select subscriber_json from rpi_subscribers order by updated_at desc, subscriber_name')->fetchAll();
    $subscribers = [];
    foreach ($rows as $row) {
        $subscriber = decode_db_json($row['subscriber_json'] ?? null);
        if (is_array($subscriber) && !empty($subscriber['id'])) {
            $subscribers[] = $subscriber;
        }
    }
    return ['ok' => true, 'subscribers' => $subscribers];
}

function backup_school_before_save(string $schoolId, PDO $db, string $reason): void
{
    $snapshot = [
        'school' => fetch_all_assoc($db, 'select * from rpi_schools where school_id = ?', [$schoolId]),
        'teachers' => fetch_all_assoc($db, 'select * from rpi_teachers where school_id = ?', [$schoolId]),
        'students' => fetch_all_assoc($db, 'select * from rpi_students where school_id = ?', [$schoolId]),
        'goals' => fetch_all_assoc($db, 'select * from rpi_goals where school_id = ?', [$schoolId]),
        'achievements' => fetch_all_assoc($db, 'select * from rpi_achievements where school_id = ?', [$schoolId]),
        'assignmentLogs' => fetch_all_assoc($db, 'select * from rpi_assignment_logs where school_id = ?', [$schoolId]),
    ];
    $rowCount = count($snapshot['teachers']) + count($snapshot['students']) + count($snapshot['goals']) + count($snapshot['achievements']) + count($snapshot['assignmentLogs']);
    if ($rowCount === 0) {
        return;
    }
    $db->prepare('insert into rpi_save_backups (school_id, reason, backup_json) values (?, ?, ?)')
        ->execute([$schoolId, $reason, encode_db_json($snapshot)]);
}

function existing_goal_workflow_map(string $schoolId, PDO $db): array
{
    $rows = fetch_all_assoc($db, 'select student_id, goal_id, goal_json from rpi_goals where school_id = ?', [$schoolId]);
    $map = [];
    foreach ($rows as $row) {
        $studentId = (string) ($row['student_id'] ?? '');
        $goalId = (string) ($row['goal_id'] ?? '');
        if ($studentId === '' || $goalId === '') continue;
        $map[$studentId . '::' . $goalId] = decode_db_json($row['goal_json'] ?? null);
    }
    return $map;
}

function preserve_goal_workflow_fields(array $incoming, array $existing): array
{
    if (!$existing) return $incoming;
    $fields = [
        'reviewedByGpkName',
        'reviewedByGpkDate',
        'reviewedByGpkUser',
        'reviewedByGpkSignature',
        'approvedByHeadName',
        'approvedByHeadDate',
        'approvedByHeadUser',
        'approvedByHeadSignature',
        'teacherFirstSignedDate',
        'teacherFirstSignedName',
        'teacherFirstSignedUser',
        'teacherFirstSignedAt',
        'teacherFirstSignature',
        'teacherReviewSignedDate',
        'teacherReviewSignedName',
        'teacherReviewSignedUser',
        'teacherReviewSignedAt',
        'teacherReviewSignature',
        'parentConsentStatus',
        'parentConsentName',
        'parentConsentIc',
        'parentConsentDate',
        'parentSignedAt',
        'parentReviewConsentStatus',
        'parentReviewConsentName',
        'parentReviewConsentIc',
        'parentReviewConsentDate',
        'parentReviewSignedAt',
        'rpiTeacherId',
        'rpiTeacherName',
    ];
    foreach ($fields as $field) {
        $incomingValue = $incoming[$field] ?? '';
        $existingValue = $existing[$field] ?? '';
        if (is_blank_workflow_value($incomingValue) && !is_blank_workflow_value($existingValue)) {
            $incoming[$field] = $existingValue;
        }
    }
    return $incoming;
}

function is_blank_workflow_value($value): bool
{
    if ($value === null) return true;
    if (is_string($value)) return trim($value) === '';
    if (is_array($value)) return count($value) === 0;
    return $value === false;
}

function ensure_backup_table(PDO $db): void
{
    $db->exec("create table if not exists rpi_save_backups (
      backup_id bigint unsigned not null auto_increment primary key,
      school_id varchar(64) not null,
      reason varchar(80) null,
      backup_json mediumtext not null,
      created_at timestamp not null default current_timestamp,
      key rpi_save_backups_school_index (school_id),
      key rpi_save_backups_created_index (created_at)
    ) character set utf8mb4 collate utf8mb4_unicode_ci");
}

function fetch_all_assoc(PDO $db, string $sql, array $params): array
{
    $statement = $db->prepare($sql);
    $statement->execute($params);
    return $statement->fetchAll();
}

function save_subscribers(array $body, array $config): array
{
    $items = $body['subscribers'] ?? [];
    if (!is_array($items)) {
        throw new InvalidArgumentException('Senarai subscriber tidak sah.', 400);
    }

    $db = pdo($config);
    ensure_schema_compatibility($db);
    $db->beginTransaction();
    try {
        $ids = [];
        foreach ($items as $item) {
            if (!is_array($item)) continue;
            $id = trim((string) ($item['id'] ?? ''));
            if ($id === '' || isset($ids[$id])) continue;
            $ids[$id] = true;
            $db->prepare('insert into rpi_subscribers
                (subscriber_id, database_id, subscriber_name, package_id, status, subscriber_json)
                values (?, ?, ?, ?, ?, ?)
                on duplicate key update
                  database_id = values(database_id),
                  subscriber_name = values(subscriber_name),
                  package_id = values(package_id),
                  status = values(status),
                  subscriber_json = values(subscriber_json),
                  updated_at = current_timestamp')
                ->execute([
                    $id,
                    normalize_school_id($item['databaseId'] ?? $id),
                    nullable_text($item['name'] ?? null),
                    nullable_text($item['packageId'] ?? null),
                    nullable_text($item['status'] ?? null),
                    encode_db_json($item),
                ]);
        }

        if (!empty($ids)) {
            $placeholders = implode(',', array_fill(0, count($ids), '?'));
            $db->prepare("delete from rpi_subscribers where subscriber_id not in ($placeholders)")
                ->execute(array_keys($ids));
        } else {
            $db->exec('delete from rpi_subscribers');
        }
        $db->commit();
    } catch (Throwable $error) {
        $db->rollBack();
        throw $error;
    }

    return ['ok' => true, 'count' => count($ids)];
}

function ensure_schema_compatibility(PDO $db): void
{
    $schoolTable = $db->query("show tables like 'rpi_schools'")->fetchColumn();
    if ($schoolTable) {
        $requiredSchoolColumns = [
            'subscription_ends' => "alter table rpi_schools add column subscription_ends date null after default_program",
        ];

        foreach ($requiredSchoolColumns as $column => $sql) {
            $statement = $db->query("show columns from rpi_schools like " . $db->quote($column));
            if (!$statement->fetch()) {
                $db->exec($sql);
            }
        }
        $db->exec("alter table rpi_schools modify settings_json mediumtext null");
    }

    $db->exec("create table if not exists rpi_subscribers (
      subscriber_id varchar(64) not null primary key,
      database_id varchar(64) not null,
      subscriber_name varchar(180) null,
      package_id varchar(40) null,
      status varchar(40) null,
      subscriber_json mediumtext null,
      created_at timestamp not null default current_timestamp,
      updated_at timestamp not null default current_timestamp on update current_timestamp,
      key rpi_subscribers_database_index (database_id),
      key rpi_subscribers_status_index (status)
    ) character set utf8mb4 collate utf8mb4_unicode_ci");

    ensure_backup_table($db);
    ensure_audit_log_table($db);
    ensure_auto_backup_table($db);
}

function ensure_audit_log_table(PDO $db): void
{
    $db->exec("create table if not exists rpi_audit_logs (
      audit_id bigint unsigned not null auto_increment primary key,
      school_id varchar(64) not null,
      user_id varchar(120) null,
      user_name varchar(180) null,
      user_role varchar(80) null,
      action_key varchar(80) not null,
      action_label varchar(180) null,
      entity_type varchar(80) null,
      entity_id varchar(120) null,
      entity_name varchar(220) null,
      status varchar(40) not null default 'success',
      detail_json mediumtext null,
      ip_address varchar(80) null,
      user_agent varchar(255) null,
      created_at timestamp not null default current_timestamp,
      key rpi_audit_school_created_index (school_id, created_at),
      key rpi_audit_action_index (action_key),
      key rpi_audit_user_index (user_id)
    ) character set utf8mb4 collate utf8mb4_unicode_ci");
}


function audit_actor_from_body(array $body): array
{
    $actor = is_array($body['actor'] ?? null) ? $body['actor'] : [];
    return [
        'userId' => mb_substr(trim((string) ($actor['id'] ?? $body['userId'] ?? '')), 0, 120),
        'userName' => mb_substr(trim((string) ($actor['name'] ?? $body['userName'] ?? '')), 0, 180),
        'userRole' => mb_substr(trim((string) ($actor['role'] ?? $body['userRole'] ?? '')), 0, 80),
    ];
}

function append_audit_log(array $body, array $config): array
{
    $schoolId = normalize_school_id($body['schoolId'] ?? null);
    if ($schoolId === '') {
        throw new InvalidArgumentException('schoolId diperlukan untuk audit log.', 400);
    }
    $actor = audit_actor_from_body($body);
    $detail = is_array($body['detail'] ?? null) ? $body['detail'] : [];
    $db = pdo($config);
    ensure_schema_compatibility($db);
    $db->prepare('insert into rpi_audit_logs
        (school_id, user_id, user_name, user_role, action_key, action_label, entity_type, entity_id, entity_name, status, detail_json, ip_address, user_agent)
        values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)')
        ->execute([
            $schoolId,
            nullable_text($actor['userId'] ?? ''),
            nullable_text($actor['userName'] ?? ''),
            nullable_text($actor['userRole'] ?? ''),
            mb_substr(trim((string) ($body['action'] ?? 'activity')), 0, 80),
            mb_substr(trim((string) ($body['actionLabel'] ?? 'Aktiviti Sistem')), 0, 180),
            nullable_text(mb_substr(trim((string) ($body['entityType'] ?? '')), 0, 80)),
            nullable_text(mb_substr(trim((string) ($body['entityId'] ?? '')), 0, 120)),
            nullable_text(mb_substr(trim((string) ($body['entityName'] ?? '')), 0, 220)),
            mb_substr(trim((string) ($body['status'] ?? 'success')), 0, 40) ?: 'success',
            encode_db_json($detail),
            mb_substr(trim((string) ($_SERVER['REMOTE_ADDR'] ?? '')), 0, 80),
            mb_substr(trim((string) ($_SERVER['HTTP_USER_AGENT'] ?? '')), 0, 255),
        ]);

    return ['ok' => true, 'auditId' => (int) $db->lastInsertId()];
}

function system_append_audit_log(string $schoolId, array $body, array $config): void
{
    try {
        if (normalize_school_id($schoolId) === '') return;
        $body['schoolId'] = $schoolId;
        append_audit_log($body, $config);
    } catch (Throwable $error) {
        error_log('[RPI Audit] ' . $error->getMessage());
    }
}


function create_audit_test_log(array $body, array $config): array
{
    $schoolId = normalize_school_id($body['schoolId'] ?? null);
    if ($schoolId === '') {
        $schoolId = 'SYSTEM';
    }
    $actor = audit_actor_from_body($body);
    $label = $schoolId === 'SYSTEM' ? 'Ujian Audit Log Developer' : 'Ujian Audit Log Sekolah';
    return append_audit_log([
        'schoolId' => $schoolId,
        'actor' => [
            'id' => $actor['userId'] ?: 'system-test',
            'name' => $actor['userName'] ?: 'Ujian Sistem',
            'role' => $actor['userRole'] ?: 'system',
        ],
        'action' => 'audit_log_test',
        'actionLabel' => $label,
        'entityType' => 'audit_log',
        'entityId' => 'test-' . gmdate('YmdHis'),
        'entityName' => 'Rekod Ujian Audit Log',
        'status' => 'success',
        'detail' => [
            'note' => 'Rekod ujian ini dijana untuk mengesahkan Pusat Log Sistem berfungsi.',
            'createdByButton' => true,
        ],
    ], $config);
}

function load_audit_logs(array $query, array $config): array
{
    $schoolId = normalize_school_id($query['schoolId'] ?? null);
    $scope = strtolower(trim((string) ($query['scope'] ?? 'school')));
    $limit = (int) ($query['limit'] ?? 200);
    if ($limit < 20) $limit = 20;
    if ($limit > 1000) $limit = 1000;

    $db = pdo($config);
    ensure_schema_compatibility($db);

    if ($scope === 'developer') {
        // Developer view must show all audit logs, even if a schoolId is accidentally sent by the browser/session.
        $statement = $db->prepare('select * from rpi_audit_logs order by created_at desc, audit_id desc limit ' . $limit);
        $statement->execute();
    } else {
        if ($schoolId === '') {
            throw new InvalidArgumentException('schoolId diperlukan.', 400);
        }
        $statement = $db->prepare('select * from rpi_audit_logs where school_id = ? order by created_at desc, audit_id desc limit ' . $limit);
        $statement->execute([$schoolId]);
    }

    $logs = [];
    foreach ($statement->fetchAll() as $row) {
        $logs[] = [
            'id' => (int) ($row['audit_id'] ?? 0),
            'schoolId' => (string) ($row['school_id'] ?? ''),
            'userId' => (string) ($row['user_id'] ?? ''),
            'userName' => (string) ($row['user_name'] ?? ''),
            'userRole' => (string) ($row['user_role'] ?? ''),
            'action' => (string) ($row['action_key'] ?? ''),
            'actionLabel' => (string) ($row['action_label'] ?? ''),
            'entityType' => (string) ($row['entity_type'] ?? ''),
            'entityId' => (string) ($row['entity_id'] ?? ''),
            'entityName' => (string) ($row['entity_name'] ?? ''),
            'status' => (string) ($row['status'] ?? ''),
            'detail' => decode_db_json($row['detail_json'] ?? null),
            'ipAddress' => (string) ($row['ip_address'] ?? ''),
            'createdAt' => (string) ($row['created_at'] ?? ''),
        ];
    }

    return ['ok' => true, 'logs' => $logs, 'count' => count($logs)];
}


function ensure_auto_backup_table(PDO $db): void
{
    $db->exec("create table if not exists rpi_auto_backup_runs (
      run_id bigint unsigned not null auto_increment primary key,
      backup_type varchar(40) not null,
      status varchar(40) not null default 'success',
      backup_count int not null default 0,
      school_count int not null default 0,
      detail_json mediumtext null,
      started_at timestamp not null default current_timestamp,
      completed_at timestamp null,
      key rpi_auto_backup_type_started_index (backup_type, started_at)
    ) character set utf8mb4 collate utf8mb4_unicode_ci");
}

function auto_backup_reason(string $type, ?DateTimeImmutable $now = null): string
{
    $now = $now ?: new DateTimeImmutable('now');
    $type = strtolower(trim($type));
    if ($type === 'weekly') {
        return 'auto_weekly_' . $now->format('o-\WW');
    }
    return 'auto_daily_' . $now->format('Y-m-d');
}

function latest_auto_backup_run(PDO $db, string $type): ?array
{
    $statement = $db->prepare('select * from rpi_auto_backup_runs where backup_type = ? order by started_at desc, run_id desc limit 1');
    $statement->execute([$type]);
    $row = $statement->fetch();
    if (!$row) {
        return null;
    }
    $detail = decode_db_json($row['detail_json'] ?? null);
    return [
        'runId' => (int) ($row['run_id'] ?? 0),
        'type' => (string) ($row['backup_type'] ?? $type),
        'status' => (string) ($row['status'] ?? ''),
        'schoolCount' => (int) ($row['school_count'] ?? 0),
        'backupCount' => (int) ($row['backup_count'] ?? 0),
        'startedAt' => (string) ($row['started_at'] ?? ''),
        'completedAt' => (string) ($row['completed_at'] ?? ''),
        'detail' => is_array($detail) ? $detail : [],
    ];
}

function school_ids_for_backup(PDO $db): array
{
    $rows = $db->query('select school_id from rpi_schools order by school_id')->fetchAll();
    $ids = [];
    foreach ($rows as $row) {
        $id = normalize_school_id($row['school_id'] ?? '');
        if ($id !== '') $ids[] = $id;
    }
    return array_values(array_unique($ids));
}


function system_health_status(array $config): array
{
    $db = pdo($config);
    ensure_schema_compatibility($db);
    $now = gmdate('c');
    $today = gmdate('Y-m-d');

    $safeCount = static function (string $sql, array $params = []) use ($db): int {
        try {
            $statement = $db->prepare($sql);
            $statement->execute($params);
            return (int) $statement->fetchColumn();
        } catch (Throwable $error) {
            error_log('[RPI Health] count failed: ' . $error->getMessage());
            return 0;
        }
    };

    $schools = $safeCount('select count(*) from rpi_schools');
    $teachers = $safeCount('select count(*) from rpi_teachers');
    $students = $safeCount('select count(*) from rpi_students');
    $achievements = $safeCount('select count(*) from rpi_achievements');
    $subscribers = $safeCount('select count(*) from rpi_subscribers');

    $activeGoals = 0;
    $archivedGoals = 0;
    $unreviewedGoals = 0;
    $unapprovedGoals = 0;
    $goalsWithoutAchievement = 0;
    $duplicateGroups = [];
    $duplicateSchools = [];

    try {
        $goalRows = $db->query('select g.school_id, g.student_id, g.goal_id, g.domain, g.main_challenge, g.goal_text, g.long_term_objective, g.short_term_objective, g.start_date, g.review_date, g.goal_json, s.student_name from rpi_goals g left join rpi_students s on s.school_id = g.school_id and s.student_id = g.student_id')->fetchAll();
        $achievementCounts = [];
        $achievementRows = $db->query('select school_id, student_id, goal_id, count(*) as total from rpi_achievements group by school_id, student_id, goal_id')->fetchAll();
        foreach ($achievementRows as $row) {
            $achievementCounts[($row['school_id'] ?? '') . '::' . ($row['student_id'] ?? '') . '::' . ($row['goal_id'] ?? '')] = (int) ($row['total'] ?? 0);
        }
        $groups = [];
        foreach ($goalRows as $row) {
            $goal = decode_db_json($row['goal_json'] ?? null);
            $archived = !empty($goal['archived']) || !empty($goal['deletedAt']) || !empty($goal['archivedAt']);
            if ($archived) {
                $archivedGoals++;
                continue;
            }
            $activeGoals++;
            $key = ($row['school_id'] ?? '') . '::' . ($row['student_id'] ?? '') . '::' . mb_strtolower(trim(preg_replace('/\s+/', ' ', implode('|', [
                $row['domain'] ?? ($goal['domain'] ?? ''),
                $row['main_challenge'] ?? ($goal['mainChallenge'] ?? ''),
                $row['goal_text'] ?? ($goal['goal'] ?? ''),
                $row['long_term_objective'] ?? ($goal['longTermObjective'] ?? ''),
                $row['short_term_objective'] ?? ($goal['shortTermObjective'] ?? ($goal['shortGoal'] ?? '')),
                $row['start_date'] ?? ($goal['startDate'] ?? ''),
                $row['review_date'] ?? ($goal['reviewDate'] ?? ''),
            ]))));
            if (!isset($groups[$key])) {
                $groups[$key] = [
                    'schoolId' => (string) ($row['school_id'] ?? ''),
                    'studentId' => (string) ($row['student_id'] ?? ''),
                    'studentName' => (string) ($row['student_name'] ?? 'Murid'),
                    'count' => 0,
                ];
            }
            $groups[$key]['count']++;

            $achievementKey = ($row['school_id'] ?? '') . '::' . ($row['student_id'] ?? '') . '::' . ($row['goal_id'] ?? '');
            if (($achievementCounts[$achievementKey] ?? 0) === 0) {
                $goalsWithoutAchievement++;
            }
            $gpkReviewed = trim((string) ($goal['reviewedByGpkDate'] ?? $goal['reviewedDate'] ?? '')) !== '';
            $gbApproved = trim((string) ($goal['approvedByHeadDate'] ?? $goal['approvedByGbDate'] ?? $goal['approvedDate'] ?? '')) !== '';
            if (!$gpkReviewed) $unreviewedGoals++;
            if (!$gbApproved) $unapprovedGoals++;
        }
        foreach ($groups as $group) {
            if (($group['count'] ?? 0) > 1) {
                $duplicateGroups[] = $group;
                if (!empty($group['schoolId'])) $duplicateSchools[$group['schoolId']] = true;
            }
        }
    } catch (Throwable $error) {
        error_log('[RPI Health] goal scan failed: ' . $error->getMessage());
    }

    $backup = auto_backup_status($config);
    $dailyDue = !empty($backup['daily']['due']);
    $weeklyDue = !empty($backup['weekly']['due']);

    $auditToday = 0;
    $auditErrorsToday = 0;
    $loginToday = 0;
    try {
        $auditToday = $safeCount('select count(*) from rpi_audit_logs where date(created_at) = ?', [$today]);
        $auditErrorsToday = $safeCount("select count(*) from rpi_audit_logs where date(created_at) = ? and status <> 'success'", [$today]);
        $loginToday = $safeCount("select count(*) from rpi_audit_logs where date(created_at) = ? and action_key like '%login%'", [$today]);
    } catch (Throwable $error) {
        error_log('[RPI Health] audit summary failed: ' . $error->getMessage());
    }

    $subscriberStatus = [
        'active' => 0,
        'trial' => 0,
        'expired' => 0,
        'endingSoon' => 0,
    ];
    try {
        $rows = $db->query('select status, subscriber_json from rpi_subscribers')->fetchAll();
        $todayDate = date('Y-m-d');
        $soonDate = date('Y-m-d', strtotime('+14 days'));
        foreach ($rows as $row) {
            $subscriber = decode_db_json($row['subscriber_json'] ?? null);
            $status = strtolower(trim((string) ($row['status'] ?? ($subscriber['status'] ?? ($subscriber['subscriptionStatus'] ?? '')))));
            $ends = nullable_date($subscriber['subscriptionEnds'] ?? ($subscriber['trialEnds'] ?? null));
            if (in_array($status, ['aktif', 'active', 'paid', 'berbayar'], true)) $subscriberStatus['active']++;
            if (str_contains($status, 'trial') || str_contains($status, 'percubaan')) $subscriberStatus['trial']++;
            if ($ends && $ends < $todayDate) $subscriberStatus['expired']++;
            elseif ($ends && $ends <= $soonDate) $subscriberStatus['endingSoon']++;
        }
    } catch (Throwable $error) {
        error_log('[RPI Health] subscriber summary failed: ' . $error->getMessage());
    }

    $warnings = [];
    if ($dailyDue) $warnings[] = 'Backup harian belum lengkap untuk semua sekolah.';
    if ($weeklyDue) $warnings[] = 'Backup mingguan belum lengkap untuk semua sekolah.';
    if (count($duplicateGroups) > 0) $warnings[] = 'Terdapat kumpulan RPI duplicate yang perlu semakan.';
    if ($auditErrorsToday > 0) $warnings[] = 'Terdapat log ralat sistem hari ini.';
    if ($subscriberStatus['expired'] > 0) $warnings[] = 'Terdapat langganan yang telah tamat.';

    return [
        'ok' => true,
        'generatedAt' => $now,
        'overallStatus' => count($warnings) === 0 ? 'sihat' : 'perlu_semakan',
        'warnings' => $warnings,
        'database' => [
            'schools' => $schools,
            'subscribers' => $subscribers,
            'teachers' => $teachers,
            'students' => $students,
            'activeGoals' => $activeGoals,
            'archivedGoals' => $archivedGoals,
            'achievements' => $achievements,
        ],
        'backup' => [
            'dailyDue' => $dailyDue,
            'weeklyDue' => $weeklyDue,
            'dailyCompleted' => (int) ($backup['daily']['completedSchools'] ?? 0),
            'weeklyCompleted' => (int) ($backup['weekly']['completedSchools'] ?? 0),
            'schoolCount' => (int) ($backup['schoolCount'] ?? 0),
            'dailyLatestRun' => $backup['daily']['latestRun'] ?? null,
            'weeklyLatestRun' => $backup['weekly']['latestRun'] ?? null,
        ],
        'duplicates' => [
            'schoolCount' => count($duplicateSchools),
            'groupCount' => count($duplicateGroups),
            'recordCount' => array_sum(array_map(static fn($g) => max(0, ((int) ($g['count'] ?? 0)) - 1), $duplicateGroups)),
            'sample' => array_slice($duplicateGroups, 0, 10),
        ],
        'audit' => [
            'today' => $auditToday,
            'errorsToday' => $auditErrorsToday,
            'loginToday' => $loginToday,
        ],
        'subscribers' => $subscriberStatus,
        'dataQuality' => [
            'goalsWithoutAchievement' => $goalsWithoutAchievement,
            'unreviewedGoals' => $unreviewedGoals,
            'unapprovedGoals' => $unapprovedGoals,
        ],
    ];
}

function auto_backup_status(array $config): array
{
    $db = pdo($config);
    ensure_schema_compatibility($db);
    $schoolIds = school_ids_for_backup($db);
    $todayReason = auto_backup_reason('daily');
    $weekReason = auto_backup_reason('weekly');

    $backupSchoolIds = static function (string $reason) use ($db): array {
        $statement = $db->prepare('select distinct school_id from rpi_save_backups where reason = ?');
        $statement->execute([$reason]);
        $ids = [];
        foreach ($statement->fetchAll() as $row) {
            $id = normalize_school_id($row['school_id'] ?? '');
            if ($id !== '') $ids[] = $id;
        }
        return array_values(array_unique($ids));
    };

    $dailyDone = $backupSchoolIds($todayReason);
    $weeklyDone = $backupSchoolIds($weekReason);
    $dailyPending = array_values(array_diff($schoolIds, $dailyDone));
    $weeklyPending = array_values(array_diff($schoolIds, $weeklyDone));

    $dailyLatest = latest_auto_backup_run($db, 'daily');
    $weeklyLatest = latest_auto_backup_run($db, 'weekly');

    return [
        'ok' => true,
        'note' => 'Belum lengkap bermaksud masih ada sekolah/subscriber dalam rpi_schools yang belum mempunyai snapshot untuk label backup hari/minggu semasa. Ini bukan bermaksud data hilang.',
        'schoolCount' => count($schoolIds),
        'daily' => [
            'reason' => $todayReason,
            'completedSchools' => count($dailyDone),
            'pendingSchools' => count($dailyPending),
            'pendingSchoolIds' => array_slice($dailyPending, 0, 30),
            'due' => count($dailyPending) > 0,
            'latestRun' => $dailyLatest,
        ],
        'weekly' => [
            'reason' => $weekReason,
            'completedSchools' => count($weeklyDone),
            'pendingSchools' => count($weeklyPending),
            'pendingSchoolIds' => array_slice($weeklyPending, 0, 30),
            'due' => count($weeklyPending) > 0,
            'latestRun' => $weeklyLatest,
        ],
        'retention' => [
            'daily' => '14 hari terakhir dicadangkan',
            'weekly' => '8 minggu terakhir dicadangkan',
        ],
    ];
}

function run_auto_backup_endpoint(array $body, array $config): array
{
    $type = strtolower(trim((string) ($body['type'] ?? 'daily')));
    if (!in_array($type, ['daily', 'weekly'], true)) {
        throw new InvalidArgumentException('Jenis backup tidak sah.', 400);
    }
    $force = !empty($body['force']);
    $actor = audit_actor_from_body($body);
    return run_auto_backup($type, $config, $force, $actor);
}

function run_auto_backup(string $type, array $config, bool $force = false, array $actor = []): array
{
    $db = pdo($config);
    ensure_schema_compatibility($db);
    $reason = auto_backup_reason($type);
    $schoolIds = school_ids_for_backup($db);
    $startedAt = gmdate('c');
    $backedUp = [];
    $skipped = [];
    $errors = [];

    $db->prepare('insert into rpi_auto_backup_runs (backup_type, status, school_count, detail_json) values (?, ?, ?, ?)')
        ->execute([$type, 'running', count($schoolIds), encode_db_json(['startedAt' => $startedAt, 'reason' => $reason])]);
    $runId = (int) $db->lastInsertId();

    foreach ($schoolIds as $schoolId) {
        try {
            if (!$force) {
                $check = $db->prepare('select backup_id from rpi_save_backups where school_id = ? and reason = ? limit 1');
                $check->execute([$schoolId, $reason]);
                if ($check->fetch()) {
                    $skipped[] = $schoolId;
                    continue;
                }
            }
            backup_school_before_save($schoolId, $db, $reason);
            $backedUp[] = $schoolId;
        } catch (Throwable $error) {
            $errors[] = ['schoolId' => $schoolId, 'error' => safe_error_detail($error->getMessage())];
            error_log('[RPI Auto Backup] ' . $schoolId . ' - ' . $error->getMessage());
        }
    }

    prune_auto_backups($db);

    $status = empty($errors) ? 'success' : (count($backedUp) > 0 ? 'partial' : 'failed');
    $detail = [
        'reason' => $reason,
        'startedAt' => $startedAt,
        'completedAt' => gmdate('c'),
        'backedUpSchools' => $backedUp,
        'skippedSchools' => $skipped,
        'errors' => $errors,
        'force' => $force,
    ];
    $db->prepare('update rpi_auto_backup_runs set status = ?, backup_count = ?, detail_json = ?, completed_at = current_timestamp where run_id = ?')
        ->execute([$status, count($backedUp), encode_db_json($detail), $runId]);

    system_append_audit_log('SYSTEM', [
        'actor' => $actor ?: ['id' => 'system', 'name' => 'Sistem Auto Backup', 'role' => 'system'],
        'action' => 'auto_backup_' . $type,
        'actionLabel' => $type === 'weekly' ? 'Backup Mingguan Sistem' : 'Backup Harian Sistem',
        'entityType' => 'system_backup',
        'entityId' => $reason,
        'entityName' => 'Auto Backup ' . strtoupper($type),
        'status' => $status,
        'detail' => $detail,
    ], $config);

    return [
        'ok' => $status !== 'failed',
        'type' => $type,
        'reason' => $reason,
        'status' => $status,
        'schoolCount' => count($schoolIds),
        'backupCount' => count($backedUp),
        'skippedCount' => count($skipped),
        'errorCount' => count($errors),
        'errors' => $errors,
    ];
}

function prune_auto_backups(PDO $db): void
{
    // Retention ringan: simpan 14 hari backup harian dan 8 minggu backup mingguan.
    // Backup manual/before_save tidak disentuh.
    $db->exec("delete from rpi_save_backups where reason like 'auto_daily_%' and created_at < (current_timestamp - interval 14 day)");
    $db->exec("delete from rpi_save_backups where reason like 'auto_weekly_%' and created_at < (current_timestamp - interval 70 day)");
}

function generate_rpi(array $body, array $config): array
{
    $schema = [
        'type' => 'object',
        'additionalProperties' => false,
        'properties' => [
            'cabaran_utama' => ['type' => 'string'],
            'matlamat' => ['type' => 'string'],
            'cadangan_strategi' => ['type' => 'array', 'items' => ['type' => 'string']],
            'objektif_jangka_panjang' => ['type' => 'array', 'items' => ['type' => 'string']],
            'objektif_jangka_pendek' => ['type' => 'string'],
            'strategi' => ['type' => 'array', 'items' => ['type' => 'string']],
            'strategi_intervensi' => ['type' => 'array', 'items' => ['type' => 'string']],
            'cadangan_bahan' => ['type' => 'array', 'items' => ['type' => 'string']],
            'kaedah_penilaian' => ['type' => 'array', 'items' => ['type' => 'string']],
        ],
        'required' => ['cabaran_utama', 'matlamat', 'cadangan_strategi', 'objektif_jangka_panjang', 'objektif_jangka_pendek', 'strategi', 'strategi_intervensi', 'cadangan_bahan', 'kaedah_penilaian'],
    ];

    $generated = call_openai_json([
        'instructions' => implode("\n", [
            'Anda ialah pakar Rancangan Pendidikan Individu (RPI) Murid Berkeperluan Pendidikan Khas (MBK) di Malaysia.',
            'Tugas anda ialah menganalisis input guru dan profil murid untuk menjana kandungan RPI secara profesional, praktikal, konsisten dan sesuai dengan tahap kefungsian murid.',
            '',
            'SUMBER INPUT',
            'Input guru datang daripada:',
            '1.1 Pengetahuan Sedia Ada Murid',
            '1.2 Keupayaan Murid',
            '1.3 Info Tambahan Pemerhatian Guru',
            'Gunakan profil murid hanya sebagai konteks sokongan jika perlu: nama murid, umur, kategori MBK, diagnosis, kelas, program, kurikulum, fokus RPI, alat sokongan, perkhidmatan sokongan, keperluan perubatan dan maklumat ibu bapa.',
            '',
            'PEMPROSESAN INPUT',
            'Guru mungkin menulis dalam dialek negeri, bahasa pasar, singkatan, short form, ejaan tidak lengkap, atau campuran Bahasa Melayu dan Inggeris.',
            'Tafsirkan maksud guru secara profesional berdasarkan konteks Pendidikan Khas Malaysia.',
            'Jangan menghina dialek guru.',
            'Jangan mengekalkan bahasa pasar dalam output akhir.',
            'Jangan menokok tambah fakta tanpa sokongan input.',
            'Jangan menghasilkan diagnosis baharu.',
            'Jangan menghasilkan maklumat palsu.',
            'Semua output akhir mesti menggunakan Bahasa Melayu rasmi mengikut gaya Dewan Bahasa dan Pustaka (DBP).',
            '',
            'LOGIK UTAMA RPI',
            'Maklumat daripada 1.1 Pengetahuan Sedia Ada, 1.2 Keupayaan Murid dan 1.3 Pemerhatian Guru ialah sumber utama dan wajib dianalisis terlebih dahulu untuk menghasilkan Cabaran Utama.',
            'Untuk Cabaran Utama, beri keutamaan tertinggi kepada pola masalah yang muncul dalam 1.1, 1.2 dan 1.3.',
            'Profil murid hanya boleh digunakan untuk memperjelas konteks, menyesuaikan aras bahasa atau memilih fokus intervensi jika maklumat 1.1, 1.2 dan 1.3 tidak mencukupi.',
            'Jangan jadikan kategori MBK, diagnosis, program, kurikulum atau profil umum sebagai Cabaran Utama jika perkara itu tidak disokong oleh input 1.1, 1.2 atau 1.3.',
            'Jangan gunakan cabaran atau matlamat sedia ada sebagai asas utama Cabaran Utama jika ia bercanggah atau tidak sepadan dengan input 1.1, 1.2 dan 1.3 semasa.',
            'Cabaran Utama ialah masalah dominan murid, masalah paling mengganggu PdPC, masalah yang menjejaskan kefungsian atau pembelajaran murid, dan masalah yang paling perlu diberi intervensi.',
            'Cabaran Utama mesti spesifik, profesional, praktikal, boleh disokong oleh input guru dan konsisten dengan objektif serta intervensi.',
            'Jangan gunakan ayat template berulang untuk Cabaran Utama. Rumuskan masalah dominan berdasarkan gabungan sebenar 1.1, 1.2 dan 1.3 semasa.',
            'Elakkan ayat seperti "Murid sukar menyatakan keperluan secara lisan tanpa menggunakan isyarat" kecuali input guru benar-benar menunjukkan isu utama ialah komunikasi keperluan secara lisan.',
            '',
            'PERATURAN OUTPUT',
            'Gunakan ayat profesional dan praktikal.',
            'Pastikan objektif sesuai dengan tahap kefungsian murid.',
            'Pastikan objektif boleh dicapai dan realistik.',
            'Untuk objektif_jangka_pendek, hasilkan satu objektif sahaja yang paling dominan dan paling perlu diintervensi.',
            'Jika benar-benar perlu dua objektif, pastikan kedua-duanya saling berkait rapat dan tulis dalam format bernombor: "1. ...\n2. ...".',
            'Jangan hasilkan lebih daripada dua objektif jangka pendek kerana guru perlu fokus kepada intervensi yang jelas dan boleh dilaksana.',
            'Jangan gabungkan dua objektif dalam satu ayat menggunakan rangkai kata seperti "dan dapat", "serta dapat" atau "di samping dapat".',
            'Jika ada dua kemahiran sasaran berbeza, pecahkan kepada dua nombor sahaja. Jika tidak saling berkait, pilih satu objektif paling dominan sahaja.',
            'Pastikan semua bahagian saling berkaitan dan konsisten.',
            'Fokus RPI mestilah berdasarkan isu murid: akademik, tingkah laku, komunikasi, sosial, pengurusan diri, emosi, motor halus atau motor kasar.',
            'Pilih hanya fokus yang paling relevan.',
            '',
            'PERATURAN STRATEGI DAN INTERVENSI',
            'Strategi mesti boleh dilaksanakan dalam kelas PPKI, sesuai dengan tahap murid dan realistik untuk guru sekolah.',
            'Intervensi mesti spesifik, praktikal, boleh dibuat secara harian atau mingguan dan bukan terlalu klinikal.',
            'Cadangan bahan mesti mudah diperoleh guru, sesuai dengan PPKI dan realistik.',
            'Kaedah penilaian mesti sesuai untuk RPI sekolah, mudah direkod guru dan praktikal.',
            '',
            'OUTPUT YANG DILARANG',
            'Jangan beri penerangan panjang.',
            'Jangan ulang semula input mentah.',
            'Jangan hasilkan motivasi umum.',
            'Jangan beri diagnosis perubatan.',
            'Jangan guna ayat terlalu umum seperti "murid perlu dibantu", "murid lemah" atau "murid perlu perhatian".',
            'Sebaliknya gunakan tingkah laku spesifik, kemahiran spesifik dan fungsi spesifik.',
            '',
            'FORMAT OUTPUT WAJIB',
            'Output mesti dalam format JSON SAHAJA.',
            'Jangan tambah penerangan lain.',
            'Gunakan struktur berikut: cabaran_utama, matlamat, cadangan_strategi, objektif_jangka_panjang, objektif_jangka_pendek, strategi, strategi_intervensi, cadangan_bahan, kaedah_penilaian.',
            'cadangan_strategi, objektif_jangka_panjang, strategi, strategi_intervensi, cadangan_bahan dan kaedah_penilaian mesti array string dengan 3 item.',
            '',
            'PERATURAN JSON',
            'Pastikan JSON valid.',
            'Jangan gunakan trailing comma.',
            'Jangan gunakan markdown.',
            'Jangan gunakan komen.',
            'Jangan tambah teks di luar JSON.',
            'Semua value mesti string atau array string.',
            'Output mesti terus boleh digunakan dalam API/database.',
            '',
            'GAYA PENULISAN',
            'Gunakan Bahasa Melayu rasmi, gaya profesional guru Pendidikan Khas Malaysia, ayat ringkas dan padat.',
            'Jika perlu gunakan istilah Inggeris, gunakan format HTML italic seperti <i>Applied Behavior Analysis</i>.',
            '',
            'MATLAMAT AKHIR',
            'Hasil output mestilah sesuai dimasukkan terus ke dokumen RPI rasmi, sesuai dimasukkan terus ke database/API, konsisten antara cabaran, objektif, strategi, intervensi dan penilaian, serta membantu guru menghasilkan RPI berkualiti dengan cepat dan praktikal.',
        ]),
        'input' => "Jana kandungan RPI berdasarkan input guru dan profil murid berikut:\n" . json_encode($body, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE),
        'schema' => $schema,
        'name' => 'rpi_suggestion',
    ], $config);

    return [
        'mainChallenge' => text_value($generated['cabaran_utama'] ?? ''),
        'goal' => text_value($generated['matlamat'] ?? ''),
        'suggestedStrategy' => lines_value($generated['cadangan_strategi'] ?? []),
        'longTermObjective' => lines_value($generated['objektif_jangka_panjang'] ?? []),
        'shortTermObjective' => short_term_objective_value($generated['objektif_jangka_pendek'] ?? ''),
        'strategy' => lines_value($generated['strategi'] ?? []),
        'intervention' => lines_value($generated['strategi_intervensi'] ?? []),
        'materials' => lines_value($generated['cadangan_bahan'] ?? []),
        'assessment' => lines_value($generated['kaedah_penilaian'] ?? []),
    ];
}

function generate_achievement(array $body, array $config): array
{
    $schema = [
        'type' => 'object',
        'additionalProperties' => false,
        'properties' => ['standardText' => ['type' => 'string']],
        'required' => ['standardText'],
    ];

    return call_openai_json([
        'instructions' => implode("\n", [
            'Anda pakar menulis penilaian pencapaian RPI dalam Bahasa Melayu rasmi.',
            'Tukar catatan guru kepada satu ayat penilaian pencapaian rasmi sahaja.',
            'Jangan tulis label seperti Ayat Guru atau Ayat Penilai Rasmi.',
            'Jangan ulang keseluruhan objektif jangka pendek.',
            'Ayat mesti ringkas, padat, bermakna dan sesuai untuk borang rasmi RPI.',
        ]),
        'input' => "Catatan dan konteks penilaian:\n" . json_encode($body, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE),
        'schema' => $schema,
        'name' => 'achievement_standard',
    ], $config);
}

function text_value($value): string
{
    if (is_array($value)) {
        return lines_value($value);
    }
    return trim((string) $value);
}

function short_term_objective_value($value): string
{
    $text = preg_replace('/\s+/', ' ', text_value($value));
    $text = trim((string) $text);
    if ($text === '') {
        return '';
    }

    if (preg_match_all('/(?:^|\n|\s)(\d+)\.\s*([^\n]+)/u', $text, $matches, PREG_SET_ORDER)) {
        $items = [];
        foreach ($matches as $match) {
            $item = trim((string) ($match[2] ?? ''));
            if ($item !== '') {
                $items[] = $item;
            }
            if (count($items) >= 2) break;
        }
        if (count($items) >= 2) {
            return numbered_objective_lines($items);
        }
    }

    $splitPatterns = [
        '/\s+dan\s+dapat\s+/iu',
        '/\s+serta\s+dapat\s+/iu',
        '/\s+di\s+samping\s+dapat\s+/iu',
    ];
    foreach ($splitPatterns as $pattern) {
        $parts = preg_split($pattern, $text, 3);
        if (is_array($parts) && count($parts) >= 2) {
            $subject = objective_subject_prefix($parts[0]);
            $first = trim($parts[0]);
            $second = trim($parts[1]);
            if ($subject !== '' && !preg_match('/\bdapat\b/iu', $second)) {
                $second = $subject . ' dapat ' . lcfirst($second);
            } elseif ($subject !== '' && preg_match('/^dapat\b/iu', $second)) {
                $second = $subject . ' ' . lcfirst($second);
            }
            return numbered_objective_lines([$first, $second]);
        }
    }

    return $text;
}

function objective_subject_prefix(string $objective): string
{
    if (preg_match('/^(.+?)\s+dapat\b/iu', trim($objective), $match)) {
        return trim((string) $match[1]);
    }
    if (preg_match('/^(Murid|[A-Z][\p{L}\s\'@.-]{2,80})\s+/u', trim($objective), $match)) {
        return trim((string) $match[1]);
    }
    return 'Murid';
}

function numbered_objective_lines(array $items): string
{
    $clean = array_values(array_filter(array_map(static function ($item): string {
        return trim(preg_replace('/\s+/', ' ', (string) $item));
    }, $items), static function (string $item): bool {
        return $item !== '';
    }));
    $clean = array_slice($clean, 0, 2);
    if (count($clean) < 2) {
        return $clean[0] ?? '';
    }
    return "1. {$clean[0]}\n2. {$clean[1]}";
}

function lines_value($value): string
{
    if (!is_array($value)) {
        return trim((string) $value);
    }
    $items = array_values(array_filter(array_map(static function ($item): string {
        return trim((string) $item);
    }, $value), static function (string $item): bool {
        return $item !== '';
    }));
    return implode("\n", $items);
}

function generate_evidence_template(array $body, array $config): array
{
    $schema = [
        'type' => 'object',
        'additionalProperties' => false,
        'properties' => [
            'title' => ['type' => 'string'],
            'items' => ['type' => 'array', 'items' => ['type' => 'string']],
            'guidance' => ['type' => 'string'],
        ],
        'required' => ['title', 'items', 'guidance'],
    ];

    return call_openai_json([
        'instructions' => implode("\n", [
            'Anda pakar membina eviden penilaian RPI untuk murid berkeperluan pendidikan khas di Malaysia.',
            'Jana item eviden berdasarkan Objektif Jangka Pendek, kaedah penilaian dan jenis eviden yang dipilih.',
            'Gunakan Bahasa Melayu rasmi berpandukan Kamus DBP.',
            'Item mesti boleh diperhati, boleh ditanda guru, ringkas dan sesuai digunakan dalam borang pemantauan.',
            'Jangan ulang objektif penuh sebagai item. Pecahkan kepada tingkah laku atau kriteria kecil yang boleh dinilai.',
            'Jika jenis eviden ialah lembaran kerja, bina kriteria semakan hasil kerja, bukan soalan latihan.',
            'Pulangkan 4 hingga 8 item sahaja.',
        ]),
        'input' => "Jana template eviden RPI berdasarkan data ini:\n" . json_encode($body, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE),
        'schema' => $schema,
        'name' => 'evidence_template',
    ], $config);
}

function generate_evidence_skill(array $body, array $config): array
{
    $schema = [
        'type' => 'object',
        'additionalProperties' => false,
        'properties' => [
            'skill' => ['type' => 'string'],
        ],
        'required' => ['skill'],
    ];

    return call_openai_json([
        'instructions' => implode("\n", [
            'Anda pakar merancang aktiviti PdP untuk RPI murid berkeperluan pendidikan khas di Malaysia.',
            'Pilih satu aktiviti yang guru boleh jalankan untuk mencapai dan menilai Objektif Jangka Pendek.',
            'Jawapan mesti nama aktiviti sahaja.',
            'Contoh jawapan: Latihan arahan visual',
            'Contoh jawapan: Cikgu Kata',
            'Fokus kepada aktiviti guru, bukan menyenaraikan perbuatan murid semata-mata.',
            'Jangan tulis ayat penerangan, jangan tulis sebab, jangan tulis tanda petik, jangan beri senarai dan jangan tulis langkah pelaksanaan.',
            'Hadkan jawapan kepada 2 hingga 5 patah perkataan.',
            'Gunakan Bahasa Melayu rasmi berpandukan Kamus DBP.',
        ]),
        'input' => "Pilih satu aktiviti PdP yang sesuai berdasarkan data ini:\n" . json_encode($body, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE),
        'schema' => $schema,
        'name' => 'evidence_skill',
    ], $config);
}

function generate_worksheet_template(array $body, array $config): array
{
    $schema = [
        'type' => 'object',
        'additionalProperties' => false,
        'properties' => [
            'title' => ['type' => 'string'],
            'template' => ['type' => 'string', 'enum' => ['copyText', 'tracing', 'number', 'focus', 'mixed']],
            'objects' => [
                'type' => 'array',
                'items' => [
                    'type' => 'object',
                    'additionalProperties' => false,
                    'properties' => [
                        'key' => ['type' => 'string', 'enum' => ['rainbowScene', 'flowerScene', 'schoolScene', 'beachScene', 'gardenScene', 'focusScene', 'school', 'chair', 'pencil', 'book', 'ball', 'shirt', 'face', 'cup', 'apple', 'heart', 'path', 'star']],
                        'label' => ['type' => 'string'],
                    ],
                    'required' => ['key', 'label'],
                ],
            ],
            'objective' => ['type' => 'string'],
            'learningObjective' => ['type' => 'string'],
            'instructions' => ['type' => 'array', 'items' => ['type' => 'string']],
            'activities' => ['type' => 'array', 'items' => ['type' => 'string']],
            'materials' => ['type' => 'string'],
            'successCriteria' => ['type' => 'array', 'items' => ['type' => 'string']],
            'answerScheme' => ['type' => 'array', 'items' => ['type' => 'string']],
            'scoring' => ['type' => 'array', 'items' => ['type' => 'string']],
        ],
        'required' => ['title', 'template', 'objects', 'objective', 'learningObjective', 'instructions', 'activities', 'materials', 'successCriteria', 'answerScheme', 'scoring'],
    ];

    return call_openai_json([
        'instructions' => implode("\n", [
            'BINA TERUS KANDUNGAN WORKSHEET A4 PORTRAIT SIAP CETAK YANG SANGAT CANTIK, PREMIUM, MENARIK DAN PROFESIONAL.',
            'GAYA VISUAL: worksheet mesti kelihatan seperti produk premium pendidikan kanak-kanak, sesuai untuk murid Pendidikan Khas, kemas, ceria, tidak terlalu padat, mudah dicetak hitam putih atau warna.',
            'JANGAN TERANGKAN APA-APA.',
            'JANGAN BAGI CADANGAN.',
            'JANGAN BAGI ULASAN.',
            'TERUS HASILKAN WORKSHEET.',
            'Gunakan maklumat murid yang diberikan: umur, tahun atau tingkatan, diagnosis, kategori MBK, tahap kefungsian, masalah utama, pengetahuan sedia ada, objektif pembelajaran, kemahiran yang hendak dibina dan topik.',
            'Kehendak worksheet: saiz A4 portrait, ada tajuk besar, ada ruang Nama, Tarikh dan Markah, ada arahan murid yang pendek dan jelas, ada aktiviti sebenar, ada ruang jawapan yang cukup besar, mesra murid Pendidikan Khas, teks besar dan mudah dibaca, visual comel, jelas dan relevan dengan objektif, susun atur premium, kemas dan profesional, tidak terlalu padat.',
            'Elakkan teks kecil, terlalu banyak arahan dan elemen hiasan yang mengganggu aktiviti.',
            'Bina aktiviti yang betul-betul sesuai dengan profil murid dan objektif pembelajaran.',
            'Aktiviti mesti selaras dengan objektif pembelajaran.',
            'Jika murid tahap rendah, gunakan aktiviti mudah seperti padanan, bulatkan, warnakan, pilih gambar, susun urutan atau tracing ringkas jika sesuai.',
            'Jika murid tahap sederhana, gunakan aktiviti padanan, melengkapkan, mengira, menyalin perkataan mudah atau memilih jawapan.',
            'Jika murid tahap tinggi, gunakan aktiviti membaca ringkas, menulis ayat pendek, menyusun langkah atau menjawab soalan mudah.',
            'Jika objektif bukan motor halus atau menulis, jangan jadikan tracing sebagai aktiviti utama.',
            'Pilih template berdasarkan aktiviti sebenar: mixed untuk padanan/pilih/bulatkan/susun/warnakan, focus untuk aktiviti tumpuan atau tingkah laku pendek, copyText untuk menyalin perkataan atau ayat, number untuk nombor atau kuantiti, tracing hanya untuk motor halus atau menulis.',
            'objects mesti 2 objek visual yang paling relevan dengan topik dan objektif pembelajaran. Pilih daripada rainbowScene, flowerScene, schoolScene, beachScene, gardenScene, focusScene, school, chair, pencil, book, ball, shirt, face, cup, apple, heart, path atau star.',
            'Gunakan objek atau scene besar hanya jika membantu murid memahami aktiviti. Visual mesti relevan dan tidak sekadar hiasan.',
            'instructions mesti 3 item, pendek, jelas dan ditulis sebagai arahan murid.',
            'activities mesti 3 item dan setiap item mestilah tugasan murid yang boleh dibuat pada worksheet.',
            'materials mesti ringkas dan sesuai untuk dicetak.',
            'answerScheme mesti mengandungi panduan jawapan atau kriteria semakan ringkas untuk guru.',
            'successCriteria mesti 3 item yang boleh diperhati.',
            'scoring mesti 5 item untuk skala 0 hingga 4.',
            'Gunakan Bahasa Melayu rasmi. Jika terpaksa menggunakan istilah Inggeris, tulis dalam format HTML italic.',
            'Jangan gunakan tanda soal dalam output.',
            'Jangan hasilkan diagnosis baharu atau fakta yang tidak diberi.',
            'Walaupun kehendak akhir ialah worksheet siap cetak dalam bentuk imej, sistem ini memerlukan output JSON supaya app boleh membina imej/worksheet A4, membenarkan guru edit, simpan dan cetak.',
            'Pulangkan JSON sahaja mengikut schema yang diberi.',
        ]),
        'input' => "Jana lembaran kerja RPI A4 berdasarkan data ini:\n" . json_encode($body, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE),
        'schema' => $schema,
        'name' => 'worksheet_template',
    ], $config);
}

function call_openai_json(array $request, array $config): array
{
    $apiKey = trim((string) ($config['openai_api_key'] ?? ''));
    if (!preg_match('/^sk-[A-Za-z0-9_-]{20,}$/', $apiKey) || preg_match('/letak|sini|your|YOUR/i', $apiKey)) {
        throw new RuntimeException('Kunci OpenAI API dalam config.php belum lengkap atau tidak sah.', 503);
    }

    $payload = [
        'model' => $config['openai_model'] ?? 'gpt-4.1-mini',
        'instructions' => $request['instructions'],
        'input' => $request['input'],
        'text' => [
            'format' => [
                'type' => 'json_schema',
                'name' => $request['name'],
                'strict' => true,
                'schema' => $request['schema'],
            ],
        ],
    ];

    $ch = curl_init('https://api.openai.com/v1/responses');
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_POST => true,
        CURLOPT_HTTPHEADER => [
            'Authorization: Bearer ' . $apiKey,
            'Content-Type: application/json',
        ],
        CURLOPT_POSTFIELDS => json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
        CURLOPT_TIMEOUT => 60,
    ]);

    $raw = curl_exec($ch);
    $status = (int) curl_getinfo($ch, CURLINFO_RESPONSE_CODE);
    $curlError = curl_error($ch);
    curl_close($ch);

    if ($raw === false || $raw === '') {
        throw new RuntimeException($curlError ?: 'OpenAI API gagal dihubungi.', 502);
    }

    $data = json_decode($raw, true);
    if (!is_array($data)) {
        throw new RuntimeException('OpenAI API memulangkan respons tidak sah.', 502);
    }

    if ($status < 200 || $status >= 300) {
        throw new RuntimeException($data['error']['message'] ?? 'OpenAI API gagal menjana jawapan.', $status ?: 502);
    }

    $text = $data['output_text'] ?? find_openai_text($data['output'] ?? []);
    if (!is_string($text) || trim($text) === '') {
        throw new RuntimeException('OpenAI API tidak memulangkan teks jawapan.', 502);
    }

    $decoded = json_decode($text, true);
    if (!is_array($decoded)) {
        throw new RuntimeException('Jawapan OpenAI bukan JSON yang sah.', 502);
    }

    return $decoded;
}

function find_openai_text(array $output): ?string
{
    foreach ($output as $item) {
        foreach (($item['content'] ?? []) as $content) {
            if (isset($content['text'])) {
                return $content['text'];
            }
        }
    }
    return null;
}

function read_json(): array
{
    $raw = file_get_contents('php://input') ?: '{}';
    $data = json_decode($raw, true);
    if (!is_array($data)) {
        throw new InvalidArgumentException('JSON tidak sah.', 400);
    }
    return $data;
}

function delete_school_children(string $schoolId, PDO $db): void
{
    $db->prepare('delete from rpi_assignment_logs where school_id = ?')->execute([$schoolId]);
    $db->prepare('delete from rpi_achievements where school_id = ?')->execute([$schoolId]);
    $db->prepare('delete from rpi_goals where school_id = ?')->execute([$schoolId]);
    $db->prepare('delete from rpi_students where school_id = ?')->execute([$schoolId]);
    $db->prepare('delete from rpi_teachers where school_id = ?')->execute([$schoolId]);
}

function fetch_json_rows(string $sql, array $params, string $column, PDO $db): array
{
    $statement = $db->prepare($sql);
    $statement->execute($params);
    return fetch_prepared_json_rows($statement, $column);
}

function fetch_prepared_json_rows(PDOStatement $statement, string $column): array
{
    $items = [];
    foreach ($statement->fetchAll() as $row) {
        $items[] = decode_db_json($row[$column] ?? null);
    }
    return $items;
}

function encode_db_json(array $value): string
{
    $json = json_encode($value, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    if ($json === false) {
        throw new RuntimeException('Data gagal ditukar kepada JSON.', 500);
    }
    return $json;
}

function decode_db_json($value): array
{
    if (!is_string($value) || trim($value) === '') {
        return [];
    }
    $decoded = json_decode($value, true);
    return is_array($decoded) ? $decoded : [];
}

function nullable_text($value): ?string
{
    $text = trim((string) ($value ?? ''));
    return $text === '' ? null : $text;
}

function nullable_date($value): ?string
{
    $text = trim((string) ($value ?? ''));
    if ($text === '') {
        return null;
    }
    if (preg_match('/^(\d{4})-(\d{2})-(\d{2})/', $text, $match)) {
        return "{$match[1]}-{$match[2]}-{$match[3]}";
    }
    if (preg_match('/^(\d{1,2})[\/.-](\d{1,2})[\/.-](\d{4})$/', $text, $match)) {
        return sprintf('%04d-%02d-%02d', (int) $match[3], (int) $match[2], (int) $match[1]);
    }
    return null;
}

function normalize_role($role): string
{
    $role = strtolower(trim((string) $role));
    return in_array($role, ['admin', 'teacher', 'pkpk', 'gb', 'ppd'], true) ? $role : 'teacher';
}

function normalize_school_id(?string $schoolId): string
{
    $id = strtoupper(trim((string) ($schoolId ?: 'DEMO001')));
    return $id !== '' ? $id : 'DEMO001';
}

function normalize_staff_username($value): string
{
    return strtolower(preg_replace('/\s+/', '', trim((string) $value)));
}

function normalize_ic($value): string
{
    return strtoupper(preg_replace('/[^0-9A-Za-z]/', '', (string) $value));
}

function json_response(array $payload, int $status = 200): void
{
    http_response_code($status);
    header('Content-Type: application/json; charset=utf-8');
    header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
    echo json_encode($payload, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
    exit;
}
