<?php
declare(strict_types=1);

function schema_table_has_column(PDO $pdo, string $table, string $column): bool
{
    $stmt = $pdo->prepare(
        'SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table_name AND COLUMN_NAME = :column_name'
    );
    $stmt->execute([
        ':table_name' => $table,
        ':column_name' => $column,
    ]);

    return (int) $stmt->fetchColumn() > 0;
}

function schema_table_has_index(PDO $pdo, string $table, string $index): bool
{
    $stmt = $pdo->prepare(
        'SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table_name AND INDEX_NAME = :index_name'
    );
    $stmt->execute([
        ':table_name' => $table,
        ':index_name' => $index,
    ]);

    return (int) $stmt->fetchColumn() > 0;
}

function schema_table_has_foreign_key(PDO $pdo, string $table, string $constraint): bool
{
    $stmt = $pdo->prepare(
        'SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table_name AND CONSTRAINT_NAME = :constraint_name AND CONSTRAINT_TYPE = "FOREIGN KEY"'
    );
    $stmt->execute([
        ':table_name' => $table,
        ':constraint_name' => $constraint,
    ]);

    return (int) $stmt->fetchColumn() > 0;
}

function ensure_school_cycle_schema(PDO $pdo): void
{
    static $done = false;
    if ($done) {
        return;
    }

    $migrationPath = __DIR__ . '/../db/migrations/20260416_school_cycles.sql';
    if (is_file($migrationPath)) {
        $sql = file_get_contents($migrationPath);
        if (is_string($sql) && trim($sql) !== '') {
            $pdo->exec($sql);
        }
    }

    $tables = [
        'school_students' => [
            'index' => 'idx_school_students_cycle',
            'foreign_key' => 'fk_school_students_cycle',
        ],
        'school_teachers' => [
            'index' => 'idx_school_teachers_cycle',
            'foreign_key' => 'fk_school_teachers_cycle',
        ],
        'school_assessment_types' => [
            'index' => 'idx_school_assessments_cycle',
            'foreign_key' => 'fk_school_assessments_cycle',
        ],
        'school_attendance_records' => [
            'index' => 'idx_school_attendance_cycle',
            'foreign_key' => 'fk_school_attendance_cycle',
        ],
    ];

    foreach ($tables as $table => $meta) {
        if (!schema_table_has_column($pdo, $table, 'cycle_id')) {
            $pdo->exec('ALTER TABLE ' . $table . ' ADD COLUMN cycle_id INT UNSIGNED NULL AFTER center_application_id');
        }

        if (!schema_table_has_index($pdo, $table, $meta['index'])) {
            $pdo->exec('ALTER TABLE ' . $table . ' ADD INDEX ' . $meta['index'] . ' (cycle_id)');
        }
    }

    ensure_school_cycle_backfill($pdo);

    if (schema_table_has_index($pdo, 'school_students', 'uniq_school_student_code') && !schema_table_has_index($pdo, 'school_students', 'uniq_school_student_cycle_code')) {
        $pdo->exec('ALTER TABLE school_students DROP INDEX uniq_school_student_code');
    }
    if (!schema_table_has_index($pdo, 'school_students', 'uniq_school_student_cycle_code')) {
        $pdo->exec('ALTER TABLE school_students ADD UNIQUE KEY uniq_school_student_cycle_code (center_application_id, cycle_id, student_code)');
    }

    foreach ($tables as $table => $meta) {
        if (!schema_table_has_foreign_key($pdo, $table, $meta['foreign_key'])) {
            $pdo->exec(
                'ALTER TABLE ' . $table . ' ADD CONSTRAINT ' . $meta['foreign_key'] . ' FOREIGN KEY (cycle_id) REFERENCES school_cycles(id) ON DELETE CASCADE'
            );
        }
    }

    $done = true;
}

function ensure_school_assessment_score_schema(PDO $pdo): void
{
    static $done = false;
    if ($done) {
        return;
    }

    $migrationPath = __DIR__ . '/../db/migrations/20260417_school_assessment_scores.sql';
    if (is_file($migrationPath)) {
        $sql = file_get_contents($migrationPath);
        if (is_string($sql) && trim($sql) !== '') {
            $pdo->exec($sql);
        }
    }

    $done = true;
}

function ensure_school_assessment_criteria_schema(PDO $pdo): void
{
    static $done = false;
    if ($done) {
        return;
    }

    $migrationPath = __DIR__ . '/../db/migrations/20260417_school_assessment_criteria.sql';
    if (is_file($migrationPath)) {
        $sql = file_get_contents($migrationPath);
        if (is_string($sql) && trim($sql) !== '') {
            $pdo->exec($sql);
        }
    }

    $done = true;
}


function ensure_center_assessment_schema(PDO $pdo): void
{
    static $done = false;
    if ($done) {
        return;
    }

    $migrationPath = __DIR__ . '/../db/migrations/20260417_center_assessment_system.sql';
    if (is_file($migrationPath)) {
        $sql = file_get_contents($migrationPath);
        if (is_string($sql) && trim($sql) !== '') {
            $pdo->exec($sql);
        }
    }

    $done = true;
}

function ensure_global_center_assessment_schema(PDO $pdo): void
{
    static $done = false;
    if ($done) {
        return;
    }

    $migrationPath = __DIR__ . '/../db/migrations/20260417_global_center_assessments.sql';
    if (is_file($migrationPath)) {
        $sql = file_get_contents($migrationPath);
        if (is_string($sql) && trim($sql) !== '') {
            $pdo->exec($sql);
        }
    }

    $done = true;
}

function ensure_school_cycle_backfill(PDO $pdo): void
{
    $applicationRows = $pdo->query(
        "SELECT * FROM center_applications WHERE status = 'approved' OR id IN (
            SELECT center_application_id FROM school_students
            UNION SELECT center_application_id FROM school_teachers
            UNION SELECT center_application_id FROM school_assessment_types
            UNION SELECT center_application_id FROM school_attendance_records
        ) ORDER BY id ASC"
    )->fetchAll();

    foreach ($applicationRows as $application) {
        $applicationId = (int) ($application['id'] ?? 0);
        if ($applicationId <= 0) {
            continue;
        }

        $cycleId = ensure_default_school_cycle_record($pdo, $application);
        foreach (['school_students', 'school_teachers', 'school_assessment_types', 'school_attendance_records'] as $table) {
            if (!schema_table_has_column($pdo, $table, 'cycle_id')) {
                continue;
            }

            $stmt = $pdo->prepare('UPDATE ' . $table . ' SET cycle_id = :cycle_id WHERE center_application_id = :center_application_id AND cycle_id IS NULL');
            $stmt->execute([
                ':cycle_id' => $cycleId,
                ':center_application_id' => $applicationId,
            ]);
        }
    }
}

function school_cycle_season_map(): array
{
    return [
        'summer' => ['label' => 'Summer', 'label_ar' => 'صيف'],
        'winter' => ['label' => 'Winter', 'label_ar' => 'شتاء'],
    ];
}

function school_cycle_status_map(): array
{
    return [
        'active' => ['label' => 'نشطة', 'class' => 'status-approved'],
        'upcoming' => ['label' => 'قادمة', 'class' => 'status-review'],
        'archived' => ['label' => 'مؤرشفة', 'class' => 'status-muted'],
    ];
}

function school_cycle_status_badge(string $status): string
{
    $map = school_cycle_status_map();
    $meta = $map[$status] ?? ['label' => 'غير محدد', 'class' => 'status-muted'];
    return '<span class="status-badge ' . e($meta['class']) . '">' . e($meta['label']) . '</span>';
}

function school_cycle_season_options(): array
{
    return school_cycle_season_map();
}

function detect_school_cycle_season(?string $date = null): string
{
    $ts = $date ? strtotime($date) : time();
    if ($ts === false) {
        $ts = time();
    }

    $month = (int) date('n', $ts);
    return $month >= 5 && $month <= 9 ? 'summer' : 'winter';
}

function format_school_cycle_name(string $season, int $year): string
{
    $map = school_cycle_season_map();
    $label = $map[$season]['label'] ?? 'Cycle';
    return $label . ' ' . $year;
}

function school_cycle_defaults(?array $application = null): array
{
    $season = detect_school_cycle_season((string) ($application['start_date'] ?? ''));
    $year = (int) date('Y', strtotime((string) ($application['start_date'] ?? 'now')) ?: time());
    $startDate = clean_text((string) ($application['start_date'] ?? date('Y-m-d')), 20);
    $endDate = clean_text((string) ($application['end_date'] ?? date('Y-m-d', strtotime('+90 days'))), 20);

    return [
        'season' => $season,
        'year' => (string) $year,
        'start_date' => $startDate,
        'end_date' => $endDate,
        'status' => 'active',
    ];
}

function validate_school_cycle_input(array $input, ?array $application = null): array
{
    $defaults = school_cycle_defaults($application);
    $data = $defaults;
    $data['status'] = clean_text((string) ($input['status'] ?? 'active'), 20);
    $data['global_cycle_id'] = filter_var($input['global_cycle_id'] ?? null, FILTER_VALIDATE_INT) ?: null;

    $errors = [];
    if (empty($data['global_cycle_id'])) {
        $errors['global_cycle_id'] = 'يرجى اختيار الدورة.';
    } else {
        try {
            $stmt = db_connection()->prepare('SELECT * FROM global_cycles WHERE id = ?');
            $stmt->execute([$data['global_cycle_id']]);
            if ($cycle = $stmt->fetch(PDO::FETCH_ASSOC)) {
                $data['cycle_name'] = $cycle['cycle_name'];
                $data['start_date'] = $cycle['start_date'];
                $data['end_date'] = $cycle['end_date'];
                $data['season'] = null; // No longer used
                $data['year'] = null; // No longer used
            } else {
                $errors['global_cycle_id'] = 'الدورة غير صالحة';
            }
        } catch (Throwable $e) {}
    }

    if (!in_array($data['status'], ['active', 'upcoming'], true)) {
        $errors['status'] = 'يرجى اختيار حالة تشغيل صحيحة للدورة.';
    }

    return [$data, $errors];
}

function normalize_school_cycle_row(array $row): array
{
    $row['id'] = (int) ($row['id'] ?? 0);
    $row['center_application_id'] = (int) ($row['center_application_id'] ?? 0);
    $row['year'] = (int) ($row['year'] ?? 0);
    $row['cycle_name'] = (string) ($row['cycle_name'] ?? format_school_cycle_name((string) ($row['season'] ?? ''), (int) ($row['year'] ?? 0)));
    $row['status'] = (string) ($row['status'] ?? 'upcoming');
    return $row;
}

function ensure_default_school_cycle_record(PDO $pdo, array $application): int
{
    $applicationId = (int) ($application['id'] ?? 0);
    if ($applicationId <= 0) {
        throw new InvalidArgumentException('Missing application id for school cycle.');
    }

    $stmt = $pdo->prepare('SELECT * FROM school_cycles WHERE center_application_id = :center_application_id ORDER BY FIELD(status, "active", "upcoming", "archived"), start_date DESC, id DESC LIMIT 1');
    $stmt->execute([':center_application_id' => $applicationId]);
    $existing = $stmt->fetch();
    if ($existing) {
        return (int) $existing['id'];
    }

    $season = null;
    $year = null;
    $startDate = (string) ($application['start_date'] ?? date('Y-m-d'));
    $endDate = (string) ($application['end_date'] ?? $startDate);
    $cycleName = 'الدورة الأساسية';
    $globalCycleId = $application['global_cycle_id'] ?? null;
    
    if ($globalCycleId) {
        $gcStmt = $pdo->prepare('SELECT * FROM global_cycles WHERE id = ?');
        $gcStmt->execute([$globalCycleId]);
        if ($gc = $gcStmt->fetch()) {
            $cycleName = $gc['cycle_name'];
            $startDate = $gc['start_date'];
            $endDate = $gc['end_date'];
        }
    }

    $status = ((string) ($application['status'] ?? '') === 'approved') ? 'active' : 'upcoming';

    $insert = $pdo->prepare(
        'INSERT INTO school_cycles (
            center_application_id, season, year, cycle_name, start_date, end_date,
            status, archived_at, created_at, updated_at, global_cycle_id
        ) VALUES (
            :center_application_id, :season, :year, :cycle_name, :start_date, :end_date,
            :status, NULL, NOW(), NOW(), :global_cycle_id
        )'
    );
    $insert->execute([
        ':center_application_id' => $applicationId,
        ':season' => $season,
        ':year' => $year,
        ':cycle_name' => $cycleName,
        ':start_date' => $startDate,
        ':end_date' => $endDate,
        ':status' => $status,
        ':global_cycle_id' => $globalCycleId
    ]);

    return (int) $pdo->lastInsertId();
}


function update_teacher_in_cycle(int $centerApplicationId, int $cycleId, int $id, array $data): void
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'UPDATE school_teachers SET 
            full_name = :full_name, role_title = :role_title, specialization = :specialization, subject_ids = :subject_ids,
            phone = :phone, email = :email, employment_status = :employment_status, notes = :notes,
            updated_at = NOW()
        WHERE id = :id AND center_application_id = :center_application_id AND cycle_id = :cycle_id'
    );
    $stmt->execute([
        ':id' => $id,
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':full_name' => $data['full_name'],
        ':role_title' => $data['role_title'],
        ':specialization' => $data['specialization'] !== '' ? $data['specialization'] : null,
        ':subject_ids' => !empty($data['subject_ids']) ? json_encode(array_values($data['subject_ids'])) : null,
        ':phone' => $data['phone'] !== '' ? $data['phone'] : null,
        ':email' => $data['email'] !== '' ? $data['email'] : null,
        ':employment_status' => $data['employment_status'],
        ':notes' => $data['notes'] !== '' ? $data['notes'] : null,
    ]);
}


function list_school_cycles(int $centerApplicationId): array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare('SELECT * FROM school_cycles WHERE center_application_id = :center_application_id ORDER BY start_date DESC, id DESC');
    $stmt->execute([':center_application_id' => $centerApplicationId]);
    $rows = $stmt->fetchAll();

    return array_map('normalize_school_cycle_row', $rows);
}

function get_school_cycle(int $centerApplicationId, int $cycleId): ?array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare('SELECT * FROM school_cycles WHERE center_application_id = :center_application_id AND id = :id LIMIT 1');
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':id' => $cycleId,
    ]);
    $row = $stmt->fetch();

    return $row ? normalize_school_cycle_row($row) : null;
}


function school_cycle_rollover_defaults(?array $selectedCycle = null): array
{
    return [
        'source_cycle_id' => $selectedCycle ? (int) ($selectedCycle['id'] ?? 0) : 0,
        'copy_teachers' => true,
        'copy_assessments' => true,
        'copy_students' => false,
    ];
}

function school_cycle_rollover_input(int $centerApplicationId, array $input, ?array $selectedCycle = null): array
{
    $defaults = school_cycle_rollover_defaults($selectedCycle);
    $sourceCycleId = isset($input['source_cycle_id']) ? (int) $input['source_cycle_id'] : (int) $defaults['source_cycle_id'];
    $sourceCycle = $sourceCycleId > 0 ? get_school_cycle($centerApplicationId, $sourceCycleId) : null;

    return [
        'source_cycle_id' => $sourceCycle ? (int) ($sourceCycle['id'] ?? 0) : 0,
        'source_cycle' => $sourceCycle,
        'copy_teachers' => isset($input['copy_teachers']) ? (bool) $input['copy_teachers'] : false,
        'copy_assessments' => isset($input['copy_assessments']) ? (bool) $input['copy_assessments'] : false,
        'copy_students' => isset($input['copy_students']) ? (bool) $input['copy_students'] : false,
    ];
}

function copy_school_cycle_rollover(PDO $pdo, int $centerApplicationId, int $sourceCycleId, int $targetCycleId, array $rollover): array
{
    $summary = [
        'teachers' => 0,
        'assessments' => 0,
        'students' => 0,
    ];

    if (!empty($rollover['copy_teachers'])) {
        $stmt = $pdo->prepare(
            'INSERT INTO school_teachers (
                center_application_id, cycle_id, full_name, role_title, specialization, subject_ids,
                phone, email, employment_status, notes,
                created_at, updated_at
            )
            SELECT
                center_application_id, :target_cycle_id, full_name, role_title, specialization, subject_ids,
                phone, email, employment_status, notes,
                NOW(), NOW()
            FROM school_teachers
            WHERE center_application_id = :center_application_id AND cycle_id = :source_cycle_id'
        );
        $stmt->execute([
            ':target_cycle_id' => $targetCycleId,
            ':center_application_id' => $centerApplicationId,
            ':source_cycle_id' => $sourceCycleId,
        ]);
        $summary['teachers'] = $stmt->rowCount();
    }

    if (!empty($rollover['copy_assessments'])) {
        $sourceStmt = $pdo->prepare(
            'SELECT * FROM school_assessment_types
             WHERE center_application_id = :center_application_id AND cycle_id = :source_cycle_id
             ORDER BY id ASC'
        );
        $sourceStmt->execute([
            ':center_application_id' => $centerApplicationId,
            ':source_cycle_id' => $sourceCycleId,
        ]);
        $sourceAssessments = $sourceStmt->fetchAll();

        $insertAssessmentStmt = $pdo->prepare(
            'INSERT INTO school_assessment_types (
                center_application_id, cycle_id, subject_id, title, category, scale_type,
                max_score, weight_percentage, is_active, notes,
                created_at, updated_at
            ) VALUES (
                :center_application_id, :cycle_id, :subject_id, :title, :category, :scale_type,
                :max_score, :weight_percentage, :is_active, :notes,
                NOW(), NOW()
            )'
        );
        $copyCriteriaStmt = $pdo->prepare(
            'INSERT INTO school_assessment_criteria (
                center_application_id, cycle_id, assessment_type_id, title, max_score,
                sort_order, is_active, notes, created_at, updated_at
            )
            SELECT
                center_application_id, :target_cycle_id, :target_assessment_id, title, max_score,
                sort_order, is_active, notes, NOW(), NOW()
            FROM school_assessment_criteria
            WHERE center_application_id = :center_application_id
              AND cycle_id = :source_cycle_id
              AND assessment_type_id = :source_assessment_id'
        );

        foreach ($sourceAssessments as $sourceAssessment) {
            $insertAssessmentStmt->execute([
                ':center_application_id' => $centerApplicationId,
                ':cycle_id' => $targetCycleId,
                ':subject_id' => !empty($sourceAssessment['subject_id']) ? (int) $sourceAssessment['subject_id'] : null,
                ':title' => (string) ($sourceAssessment['title'] ?? ''),
                ':category' => (string) ($sourceAssessment['category'] ?? ''),
                ':scale_type' => (string) ($sourceAssessment['scale_type'] ?? 'percentage'),
                ':max_score' => (float) ($sourceAssessment['max_score'] ?? 0),
                ':weight_percentage' => (float) ($sourceAssessment['weight_percentage'] ?? 0),
                ':is_active' => (int) ($sourceAssessment['is_active'] ?? 1),
                ':notes' => !empty($sourceAssessment['notes']) ? (string) $sourceAssessment['notes'] : null,
            ]);
            $newAssessmentId = (int) $pdo->lastInsertId();
            if ($newAssessmentId > 0) {
                $copyCriteriaStmt->execute([
                    ':target_cycle_id' => $targetCycleId,
                    ':target_assessment_id' => $newAssessmentId,
                    ':center_application_id' => $centerApplicationId,
                    ':source_cycle_id' => $sourceCycleId,
                    ':source_assessment_id' => (int) ($sourceAssessment['id'] ?? 0),
                ]);
                sync_assessment_total_score_from_criteria($centerApplicationId, $targetCycleId, $newAssessmentId);
            }
        }
        $summary['assessments'] = count($sourceAssessments);
    }

    if (!empty($rollover['copy_students'])) {
        $stmt = $pdo->prepare(
            "INSERT INTO school_students (
                center_application_id, cycle_id, student_code, full_name, gender, grade_level,
                guardian_name, guardian_phone, birth_date, enrollment_status, notes,
                created_at, updated_at
            )
            SELECT
                center_application_id, :target_cycle_id, student_code, full_name, gender, grade_level,
                guardian_name, guardian_phone, birth_date, enrollment_status, notes,
                NOW(), NOW()
            FROM school_students
            WHERE center_application_id = :center_application_id
              AND cycle_id = :source_cycle_id
              AND enrollment_status IN ('active', 'waiting')"
        );
        $stmt->execute([
            ':target_cycle_id' => $targetCycleId,
            ':center_application_id' => $centerApplicationId,
            ':source_cycle_id' => $sourceCycleId,
        ]);
        $summary['students'] = $stmt->rowCount();
    }

    return $summary;
}

function create_school_cycle(int $centerApplicationId, array $data, array $rollover = []): array
{
    $pdo = db_connection();
    $season = $data['season'] ?? null;
    $year = isset($data['year']) ? (int) $data['year'] : null;
    $cycleName = $data['cycle_name'] ?? format_school_cycle_name($season ?? 'summer', $year ?? (int)date('Y'));
    $globalCycleId = $data['global_cycle_id'] ?? null;
    $rollover = array_merge(school_cycle_rollover_defaults(), $rollover);
    $sourceCycleId = (int) ($rollover['source_cycle_id'] ?? 0);

    $pdo->beginTransaction();
    try {
        $sourceCycle = null;
        if ($sourceCycleId > 0) {
            $sourceStmt = $pdo->prepare('SELECT * FROM school_cycles WHERE center_application_id = :center_application_id AND id = :id LIMIT 1');
            $sourceStmt->execute([
                ':center_application_id' => $centerApplicationId,
                ':id' => $sourceCycleId,
            ]);
            $sourceCycleRow = $sourceStmt->fetch();
            if ($sourceCycleRow) {
                $sourceCycle = normalize_school_cycle_row($sourceCycleRow);
            } elseif (!empty($rollover['copy_teachers']) || !empty($rollover['copy_assessments']) || !empty($rollover['copy_students'])) {
                throw new InvalidArgumentException('Source cycle not found for rollover.');
            }
        }

        if ($data['status'] === 'active') {
            $archiveStmt = $pdo->prepare(
                "UPDATE school_cycles
                 SET status = 'archived', archived_at = COALESCE(archived_at, NOW()), updated_at = NOW()
                 WHERE center_application_id = :center_application_id AND status = 'active'"
            );
            $archiveStmt->execute([':center_application_id' => $centerApplicationId]);
        }

        $insert = $pdo->prepare(
            'INSERT INTO school_cycles (
                center_application_id, season, year, cycle_name, start_date, end_date,
                status, archived_at, created_at, updated_at, global_cycle_id
            ) VALUES (
                :center_application_id, :season, :year, :cycle_name, :start_date, :end_date,
                :status, :archived_at, NOW(), NOW(), :global_cycle_id
            )'
        );
        $insert->execute([
            ':center_application_id' => $centerApplicationId,
            ':season' => $season,
            ':year' => $year,
            ':cycle_name' => $cycleName,
            ':start_date' => $data['start_date'],
            ':end_date' => $data['end_date'],
            ':status' => $data['status'],
            ':archived_at' => $data['status'] === 'archived' ? date('Y-m-d H:i:s') : null,
            ':global_cycle_id' => $globalCycleId,
        ]);

        $cycleId = (int) $pdo->lastInsertId();
        $rolloverSummary = ['teachers' => 0, 'assessments' => 0, 'students' => 0];

        if ($sourceCycle && ($rollover['copy_teachers'] || $rollover['copy_assessments'] || $rollover['copy_students'])) {
            $rolloverSummary = copy_school_cycle_rollover($pdo, $centerApplicationId, (int) $sourceCycle['id'], $cycleId, $rollover);
        }

        $pdo->commit();

        return [
            'cycle_id' => $cycleId,
            'cycle_name' => $cycleName,
            'source_cycle_name' => $sourceCycle ? (string) ($sourceCycle['cycle_name'] ?? '') : '',
            'rollover' => $rolloverSummary,
        ];
    } catch (Throwable $exception) {
        if ($pdo->inTransaction()) {
            $pdo->rollBack();
        }
        throw $exception;
    }
}

function archive_school_cycle(int $centerApplicationId, int $cycleId): void
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        "UPDATE school_cycles
         SET status = 'archived', archived_at = COALESCE(archived_at, NOW()), updated_at = NOW()
         WHERE center_application_id = :center_application_id AND id = :id"
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':id' => $cycleId,
    ]);
}

function resolve_school_cycle_context(int $centerApplicationId, ?array $application, int $requestedCycleId = 0): array
{
    if ($application && (string) ($application['status'] ?? '') === 'approved') {
        $pdo = db_connection();
        ensure_default_school_cycle_record($pdo, $application);
    }

    $cycles = list_school_cycles($centerApplicationId);
    $selected = null;
    $active = null;

    foreach ($cycles as $cycle) {
        if ($cycle['status'] === 'active' && $active === null) {
            $active = $cycle;
        }
        if ($requestedCycleId > 0 && (int) $cycle['id'] === $requestedCycleId) {
            $selected = $cycle;
        }
    }

    if ($selected === null) {
        $selected = $active ?? ($cycles[0] ?? null);
    }

    return [
        'cycles' => $cycles,
        'selected' => $selected,
        'active' => $active,
        'read_only' => $selected ? ((string) ($selected['status'] ?? '') === 'archived') : false,
    ];
}

function school_page_url(string $page, int $applicationId, ?int $cycleId = null): string
{
    $url = $page . '?id=' . urlencode((string) $applicationId);
    if ($cycleId !== null && $cycleId > 0) {
        $url .= '&cycle=' . urlencode((string) $cycleId);
    }
    return $url;
}

function next_student_code_for_cycle(int $centerApplicationId, int $cycleId): string
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'SELECT student_code FROM school_students WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id ORDER BY id DESC'
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ]);

    $codes = $stmt->fetchAll(PDO::FETCH_COLUMN) ?: [];
    $prefix = 'ST-';
    $padding = 3;
    $maxNumber = 0;

    foreach ($codes as $codeValue) {
        $code = trim((string) $codeValue);
        if ($code === '') {
            continue;
        }

        if (preg_match('/^(.*?)(\d+)$/', $code, $matches)) {
            if ($prefix === 'ST-' && trim((string) $matches[1]) !== '') {
                $prefix = (string) $matches[1];
            }
            $padding = max($padding, strlen((string) $matches[2]));
            $maxNumber = max($maxNumber, (int) $matches[2]);
        }
    }

    return $prefix . str_pad((string) ($maxNumber + 1), $padding, '0', STR_PAD_LEFT);
}

function create_student_in_cycle(int $centerApplicationId, int $cycleId, array $data): int
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'INSERT INTO school_students (
            center_application_id, cycle_id, student_code, full_name, gender, grade_level,
            guardian_name, guardian_phone, birth_date, enrollment_status, notes,
            created_at, updated_at
        ) VALUES (
            :center_application_id, :cycle_id, :student_code, :full_name, :gender, :grade_level,
            :guardian_name, :guardian_phone, :birth_date, :enrollment_status, :notes,
            NOW(), NOW()
        )'
    );

    $stmt->bindValue(':center_application_id', $centerApplicationId, PDO::PARAM_INT);
    $stmt->bindValue(':cycle_id', $cycleId, PDO::PARAM_INT);
    $stmt->bindValue(':student_code', $data['student_code'], PDO::PARAM_STR);
    $stmt->bindValue(':full_name', $data['full_name'], PDO::PARAM_STR);
    $stmt->bindValue(':gender', $data['gender'], PDO::PARAM_STR);
    $stmt->bindValue(':grade_level', $data['grade_level'], PDO::PARAM_STR);
    $stmt->bindValue(':guardian_name', $data['guardian_name'], PDO::PARAM_STR);
    $stmt->bindValue(':guardian_phone', $data['guardian_phone'], PDO::PARAM_STR);
    $stmt->bindValue(':birth_date', $data['birth_date'] !== '' ? $data['birth_date'] : null, $data['birth_date'] !== '' ? PDO::PARAM_STR : PDO::PARAM_NULL);
    $stmt->bindValue(':enrollment_status', $data['enrollment_status'], PDO::PARAM_STR);
    $stmt->bindValue(':notes', $data['notes'] !== '' ? $data['notes'] : null, $data['notes'] !== '' ? PDO::PARAM_STR : PDO::PARAM_NULL);
    $stmt->execute();

    return (int) $pdo->lastInsertId();
}

function list_school_students_by_cycle(int $centerApplicationId, int $cycleId, string $search = '', int $limit = 0, int $offset = 0, array $filters = []): array
{
    $pdo = db_connection();
    $query = 'SELECT * FROM school_students WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id';
    $params = [
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ];
    
    if ($search !== '') {
        $query .= ' AND (student_code LIKE :search1 OR full_name LIKE :search2 OR guardian_phone LIKE :search3)';
        $params[':search1'] = "%$search%";
        $params[':search2'] = "%$search%";
        $params[':search3'] = "%$search%";
    }
    
    if (!empty($filters["gender"])) { $query .= " AND gender = :gender"; $params[":gender"] = $filters["gender"]; } if (!empty($filters["grade_level"])) { $query .= " AND grade_level = :grade_level"; $params[":grade_level"] = $filters["grade_level"]; } if (!empty($filters["enrollment_status"])) { $query .= " AND enrollment_status = :enrollment_status"; $params[":enrollment_status"] = $filters["enrollment_status"]; } 
    $query .= ' ORDER BY created_at DESC, id DESC';
    
    if ($limit > 0) {
        $query .= ' LIMIT ' . (int)$limit . ' OFFSET ' . (int)$offset;
    }
    
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function count_school_students_by_cycle(int $centerApplicationId, int $cycleId, string $search = '', array $filters = []): int
{
    $pdo = db_connection();
    $query = 'SELECT COUNT(*) FROM school_students WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id';
    $params = [
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ];
    
    if ($search !== '') {
        $query .= ' AND (student_code LIKE :search1 OR full_name LIKE :search2 OR guardian_phone LIKE :search3)';
        $params[':search1'] = "%$search%";
        $params[':search2'] = "%$search%";
        $params[':search3'] = "%$search%";
    }
    
    if (!empty($filters["gender"])) { $query .= " AND gender = :gender"; $params[":gender"] = $filters["gender"]; } if (!empty($filters["grade_level"])) { $query .= " AND grade_level = :grade_level"; $params[":grade_level"] = $filters["grade_level"]; } if (!empty($filters["enrollment_status"])) { $query .= " AND enrollment_status = :enrollment_status"; $params[":enrollment_status"] = $filters["enrollment_status"]; } 
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    return (int)$stmt->fetchColumn();
}

function school_student_metrics_by_cycle(int $centerApplicationId, int $cycleId): array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        "SELECT
            COUNT(*) AS total,
            COALESCE(SUM(gender = 'طالب'), 0) AS boys_count,
            COALESCE(SUM(gender = 'طالبة'), 0) AS girls_count,
            COALESCE(SUM(enrollment_status = 'active'), 0) AS active_count,
            COALESCE(SUM(enrollment_status = 'waiting'), 0) AS waiting_count,
            COALESCE(SUM(enrollment_status = 'withdrawn'), 0) AS withdrawn_count
         FROM school_students
         WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id"
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ]);
    $row = $stmt->fetch() ?: [];

    return [
        'total' => (int) ($row['total'] ?? 0),
        'boys' => (int) ($row['boys_count'] ?? 0),
        'girls' => (int) ($row['girls_count'] ?? 0),
        'active' => (int) ($row['active_count'] ?? 0),
        'waiting' => (int) ($row['waiting_count'] ?? 0),
        'withdrawn' => (int) ($row['withdrawn_count'] ?? 0),
    ];
}

function create_teacher_in_cycle(int $centerApplicationId, int $cycleId, array $data): int
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'INSERT INTO school_teachers (
            center_application_id, cycle_id, full_name, role_title, specialization, subject_ids,
            phone, email, employment_status, notes,
            created_at, updated_at
        ) VALUES (
            :center_application_id, :cycle_id, :full_name, :role_title, :specialization, :subject_ids,
            :phone, :email, :employment_status, :notes,
            NOW(), NOW()
        )'
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':full_name' => $data['full_name'],
        ':role_title' => $data['role_title'],
        ':specialization' => $data['specialization'] !== '' ? $data['specialization'] : null,
        ':subject_ids' => !empty($data['subject_ids']) ? json_encode(array_values($data['subject_ids'])) : null,
        ':phone' => $data['phone'] !== '' ? $data['phone'] : null,
        ':email' => $data['email'] !== '' ? $data['email'] : null,
        ':employment_status' => $data['employment_status'],
        ':notes' => $data['notes'] !== '' ? $data['notes'] : null,
    ]);
    return (int) $pdo->lastInsertId();
}

function list_school_teachers_by_cycle(int $centerApplicationId, int $cycleId, array $filters = [], int $limit = 0, int $offset = 0): array
{
    $pdo = db_connection();
    $query = 'SELECT * FROM school_teachers WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id';
    $params = [
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ];
    
    $search = $filters['search'] ?? '';
    if ($search !== '') {
        $query .= ' AND (full_name LIKE :search1 OR email LIKE :search2 OR phone LIKE :search3)';
        $params[':search1'] = "%$search%";
        $params[':search2'] = "%$search%";
        $params[':search3'] = "%$search%";
    }

    if (!empty($filters['role_title'])) {
        $query .= ' AND role_title = :role';
        $params[':role'] = $filters['role_title'];
    }

    if (!empty($filters['employment_status'])) {
        $query .= ' AND employment_status = :status';
        $params[':status'] = $filters['employment_status'];
    }
    
    $query .= ' ORDER BY created_at DESC, id DESC';
    
    if ($limit > 0) {
        $query .= ' LIMIT ' . (int)$limit . ' OFFSET ' . (int)$offset;
    }
    
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function count_school_teachers_by_cycle(int $centerApplicationId, int $cycleId, array $filters = []): int
{
    $pdo = db_connection();
    $query = 'SELECT COUNT(*) FROM school_teachers WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id';
    $params = [
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ];
    
    $search = $filters['search'] ?? '';
    if ($search !== '') {
        $query .= ' AND (full_name LIKE :search1 OR email LIKE :search2 OR phone LIKE :search3)';
        $params[':search1'] = "%$search%";
        $params[':search2'] = "%$search%";
        $params[':search3'] = "%$search%";
    }

    if (!empty($filters['role_title'])) {
        $query .= ' AND role_title = :role';
        $params[':role'] = $filters['role_title'];
    }

    if (!empty($filters['employment_status'])) {
        $query .= ' AND employment_status = :status';
        $params[':status'] = $filters['employment_status'];
    }
    
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    return (int)$stmt->fetchColumn();
}

function school_teacher_metrics_by_cycle(int $centerApplicationId, int $cycleId): array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        "SELECT
            COUNT(*) AS total,
            COALESCE(SUM(employment_status = 'active'), 0) AS active_count,
            COALESCE(SUM(employment_status = 'pending'), 0) AS pending_count,
            COALESCE(SUM(employment_status = 'inactive'), 0) AS inactive_count,
            COALESCE(SUM(role_title = 'معلم' OR role_title = 'معلمة'), 0) AS teachers_count,
            COALESCE(SUM(role_title LIKE '%مشرف%' OR role_title LIKE '%منسق%'), 0) AS supervisors_count,
            COALESCE(SUM(email IS NOT NULL AND email <> ''), 0) AS email_ready_count
         FROM school_teachers
         WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id"
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ]);
    $row = $stmt->fetch() ?: [];

    return [
        'total' => (int) ($row['total'] ?? 0),
        'active' => (int) ($row['active_count'] ?? 0),
        'pending' => (int) ($row['pending_count'] ?? 0),
        'inactive' => (int) ($row['inactive_count'] ?? 0),
        'teachers' => (int) ($row['teachers_count'] ?? 0),
        'supervisors' => (int) ($row['supervisors_count'] ?? 0),
        'email_ready' => (int) ($row['email_ready_count'] ?? 0),
    ];
}

function create_assessment_type_in_cycle(int $centerApplicationId, int $cycleId, array $data): int
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'INSERT INTO school_assessment_types (
            center_application_id, cycle_id, subject_id, title, category, scale_type,
            max_score, weight_percentage, is_active, notes,
            created_at, updated_at
        ) VALUES (
            :center_application_id, :cycle_id, :subject_id, :title, :category, :scale_type,
            :max_score, :weight_percentage, :is_active, :notes,
            NOW(), NOW()
        )'
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':subject_id' => !empty($data['subject_id']) ? (int) $data['subject_id'] : null,
        ':title' => $data['title'],
        ':category' => $data['category'],
        ':scale_type' => $data['scale_type'],
        ':max_score' => (float) $data['max_score'],
        ':weight_percentage' => (float) $data['weight_percentage'],
        ':is_active' => (int) $data['is_active'],
        ':notes' => $data['notes'] !== '' ? $data['notes'] : null,
    ]);
    return (int) $pdo->lastInsertId();
}

function list_school_assessments_by_cycle(int $centerApplicationId, int $cycleId, array $filters = [], int $limit = 0, int $offset = 0): array
{
    $pdo = db_connection();
    $query = 'SELECT sat.*,
            (
                SELECT COUNT(*)
                FROM school_assessment_criteria criteria
                WHERE criteria.assessment_type_id = sat.id
                  AND criteria.center_application_id = sat.center_application_id
                  AND criteria.cycle_id = sat.cycle_id
                  AND criteria.is_active = 1
            ) AS criteria_count,
            (
                SELECT COALESCE(SUM(criteria.max_score), 0)
                FROM school_assessment_criteria criteria
                WHERE criteria.assessment_type_id = sat.id
                  AND criteria.center_application_id = sat.center_application_id
                  AND criteria.cycle_id = sat.cycle_id
                  AND criteria.is_active = 1
            ) AS criteria_total_max_score
        FROM school_assessment_types sat
        WHERE sat.center_application_id = :center_application_id AND sat.cycle_id = :cycle_id';
    $params = [
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ];
    
    $search = $filters['search'] ?? '';
    if ($search !== '') {
        $query .= ' AND (sat.title LIKE :search1 OR sat.category LIKE :search2)';
        $params[':search1'] = "%$search%";
        $params[':search2'] = "%$search%";
    }
    
    $subject_id = $filters['subject_id'] ?? '';
    if ($subject_id !== '') {
        $query .= ' AND sat.subject_id = :subject_id';
        $params[':subject_id'] = (int) $subject_id;
    }

    $category = $filters['category'] ?? '';
    if ($category !== '') {
        $query .= ' AND sat.category = :category';
        $params[':category'] = $category;
    }
    
    $query .= ' ORDER BY sat.is_active DESC, sat.created_at DESC, sat.id DESC';
    
    if ($limit > 0) {
        $query .= ' LIMIT ' . (int)$limit . ' OFFSET ' . (int)$offset;
    }
    
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function count_school_assessments_by_cycle(int $centerApplicationId, int $cycleId, array $filters = []): int
{
    $pdo = db_connection();
    $query = 'SELECT COUNT(*) FROM school_assessment_types WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id';
    $params = [
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ];
    
    $search = $filters['search'] ?? '';
    if ($search !== '') {
        $query .= ' AND (title LIKE :search1 OR category LIKE :search2)';
        $params[':search1'] = "%$search%";
        $params[':search2'] = "%$search%";
    }
    
    $subject_id = $filters['subject_id'] ?? '';
    if ($subject_id !== '') {
        $query .= ' AND subject_id = :subject_id';
        $params[':subject_id'] = (int) $subject_id;
    }

    $category = $filters['category'] ?? '';
    if ($category !== '') {
        $query .= ' AND category = :category';
        $params[':category'] = $category;
    }
    
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    return (int)$stmt->fetchColumn();
}

function school_assessment_metrics_by_cycle(int $centerApplicationId, int $cycleId): array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        "SELECT
            COUNT(*) AS total,
            COALESCE(SUM(is_active = 1), 0) AS active_count,
            COALESCE(SUM(is_active = 0), 0) AS inactive_count,
            COALESCE(SUM(weight_percentage), 0) AS total_weight,
            COALESCE(SUM(CASE WHEN is_active = 1 THEN weight_percentage ELSE 0 END), 0) AS active_weight,
            COALESCE(AVG(max_score), 0) AS average_max_score,
            COALESCE(SUM(scale_type = 'percentage'), 0) AS percentage_count,
            COALESCE(SUM(scale_type = 'points'), 0) AS points_count,
            COALESCE(SUM(scale_type = 'rubric'), 0) AS rubric_count
         FROM school_assessment_types
         WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id"
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ]);
    $row = $stmt->fetch() ?: [];

    return [
        'total' => (int) ($row['total'] ?? 0),
        'active' => (int) ($row['active_count'] ?? 0),
        'inactive' => (int) ($row['inactive_count'] ?? 0),
        'total_weight' => (float) ($row['total_weight'] ?? 0),
        'active_weight' => (float) ($row['active_weight'] ?? 0),
        'average_max_score' => (float) ($row['average_max_score'] ?? 0),
        'percentage' => (int) ($row['percentage_count'] ?? 0),
        'points' => (int) ($row['points_count'] ?? 0),
        'rubric' => (int) ($row['rubric_count'] ?? 0),
    ];
}

function assessment_score_status_map(): array
{
    return [
        'present' => ['label' => 'حاضر', 'class' => 'status-approved'],
        'absent' => ['label' => 'غائب', 'class' => 'status-review'],
        'excused' => ['label' => 'بعذر', 'class' => 'status-muted'],
    ];
}

function assessment_score_status_badge(string $status): string
{
    $map = assessment_score_status_map();
    $meta = $map[$status] ?? ['label' => 'غير محدد', 'class' => 'status-muted'];
    return '<span class="status-badge ' . e($meta['class']) . '">' . e($meta['label']) . '</span>';
}

function school_teacher_options_by_cycle(int $centerApplicationId, int $cycleId, bool $onlyActive = false): array
{
    $filters = [];
    if ($onlyActive) {
        $filters['employment_status'] = 'active';
    }

    $teachers = list_school_teachers_by_cycle($centerApplicationId, $cycleId, $filters);
    $options = [];
    foreach ($teachers as $teacher) {
        $teacherId = (int) ($teacher['id'] ?? 0);
        if ($teacherId <= 0) {
            continue;
        }

        $roleTitle = trim((string) ($teacher['role_title'] ?? ''));
        $specialization = trim((string) ($teacher['specialization'] ?? ''));
        $label = trim((string) ($teacher['full_name'] ?? ''));
        if ($roleTitle !== '') {
            $label .= ' — ' . $roleTitle;
        }
        if ($specialization !== '') {
            $label .= ' (' . $specialization . ')';
        }

        $options[$teacherId] = [
            'label' => $label,
            'full_name' => trim((string) ($teacher['full_name'] ?? '')),
            'role_title' => $roleTitle,
            'specialization' => $specialization,
            'employment_status' => (string) ($teacher['employment_status'] ?? ''),
        ];
    }

    return $options;
}


function create_center_assessment_type_in_cycle(int $centerApplicationId, int $cycleId, array $data): int
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'INSERT INTO center_assessment_types (
            center_application_id, cycle_id, title, category, scale_type,
            max_score, weight_percentage, is_active, notes,
            created_at, updated_at
        ) VALUES (
            :center_application_id, :cycle_id, :title, :category, :scale_type,
            :max_score, :weight_percentage, :is_active, :notes,
            NOW(), NOW()
        )'
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':title' => $data['title'],
        ':category' => $data['category'],
        ':scale_type' => $data['scale_type'],
        ':max_score' => (float) $data['max_score'],
        ':weight_percentage' => (float) $data['weight_percentage'],
        ':is_active' => (int) $data['is_active'],
        ':notes' => $data['notes'] !== '' ? $data['notes'] : null,
    ]);

    return (int) $pdo->lastInsertId();
}

function list_center_assessments_by_cycle(int $centerApplicationId, int $cycleId, array $filters = [], int $limit = 0, int $offset = 0): array
{
    $pdo = db_connection();
    $query = 'SELECT cat.*,
            (
                SELECT COUNT(*)
                FROM center_assessment_criteria criteria
                WHERE criteria.assessment_type_id = cat.id
                  AND criteria.center_application_id = cat.center_application_id
                  AND criteria.cycle_id = cat.cycle_id
                  AND criteria.is_active = 1
            ) AS criteria_count,
            (
                SELECT COALESCE(SUM(criteria.max_score), 0)
                FROM center_assessment_criteria criteria
                WHERE criteria.assessment_type_id = cat.id
                  AND criteria.center_application_id = cat.center_application_id
                  AND criteria.cycle_id = cat.cycle_id
                  AND criteria.is_active = 1
            ) AS criteria_total_max_score
        FROM center_assessment_types cat
        WHERE cat.center_application_id = :center_application_id AND cat.cycle_id = :cycle_id';
    $params = [
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ];

    $search = trim((string) ($filters['search'] ?? ''));
    if ($search !== '') {
        $query .= ' AND (cat.title LIKE :search1 OR cat.category LIKE :search2)';
        $params[':search1'] = "%{$search}%";
        $params[':search2'] = "%{$search}%";
    }

    $category = trim((string) ($filters['category'] ?? ''));
    if ($category !== '') {
        $query .= ' AND cat.category = :category';
        $params[':category'] = $category;
    }

    $query .= ' ORDER BY cat.is_active DESC, cat.updated_at DESC, cat.id DESC';

    if ($limit > 0) {
        $query .= ' LIMIT ' . (int) $limit . ' OFFSET ' . (int) $offset;
    }

    $stmt = $pdo->prepare($query);
    $stmt->execute($params);

    return $stmt->fetchAll();
}

function count_center_assessments_by_cycle(int $centerApplicationId, int $cycleId, array $filters = []): int
{
    $pdo = db_connection();
    $query = 'SELECT COUNT(*) FROM center_assessment_types WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id';
    $params = [
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ];

    $search = trim((string) ($filters['search'] ?? ''));
    if ($search !== '') {
        $query .= ' AND (title LIKE :search1 OR category LIKE :search2)';
        $params[':search1'] = "%{$search}%";
        $params[':search2'] = "%{$search}%";
    }

    $category = trim((string) ($filters['category'] ?? ''));
    if ($category !== '') {
        $query .= ' AND category = :category';
        $params[':category'] = $category;
    }

    $stmt = $pdo->prepare($query);
    $stmt->execute($params);

    return (int) $stmt->fetchColumn();
}

function center_assessment_metrics_by_cycle(int $centerApplicationId, int $cycleId): array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        "SELECT
            COUNT(*) AS total,
            COALESCE(SUM(is_active = 1), 0) AS active_count,
            COALESCE(SUM(is_active = 0), 0) AS inactive_count,
            COALESCE(SUM(CASE WHEN is_active = 1 THEN weight_percentage ELSE 0 END), 0) AS active_weight,
            COALESCE(AVG(max_score), 0) AS average_max_score,
            COALESCE(SUM(scale_type = 'percentage'), 0) AS percentage_count,
            COALESCE(SUM(scale_type = 'points'), 0) AS points_count,
            COALESCE(SUM(scale_type LIKE 'rubric_%'), 0) AS rubric_count
         FROM center_assessment_types
         WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id"
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ]);
    $row = $stmt->fetch() ?: [];

    return [
        'total' => (int) ($row['total'] ?? 0),
        'active' => (int) ($row['active_count'] ?? 0),
        'inactive' => (int) ($row['inactive_count'] ?? 0),
        'active_weight' => (float) ($row['active_weight'] ?? 0),
        'average_max_score' => (float) ($row['average_max_score'] ?? 0),
        'percentage' => (int) ($row['percentage_count'] ?? 0),
        'points' => (int) ($row['points_count'] ?? 0),
        'rubric' => (int) ($row['rubric_count'] ?? 0),
    ];
}

function update_center_assessment_type_in_cycle(int $centerApplicationId, int $cycleId, int $assessmentId, array $data): bool
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'UPDATE center_assessment_types SET
            title = :title,
            category = :category,
            scale_type = :scale_type,
            max_score = :max_score,
            weight_percentage = :weight_percentage,
            is_active = :is_active,
            notes = :notes,
            updated_at = NOW()
        WHERE id = :id AND center_application_id = :center_application_id AND cycle_id = :cycle_id'
    );

    return $stmt->execute([
        ':id' => $assessmentId,
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':title' => $data['title'],
        ':category' => $data['category'],
        ':scale_type' => $data['scale_type'],
        ':max_score' => (float) $data['max_score'],
        ':weight_percentage' => (float) $data['weight_percentage'],
        ':is_active' => (int) $data['is_active'],
        ':notes' => $data['notes'] !== '' ? $data['notes'] : null,
    ]);
}

function center_assessment_type_options_by_cycle(int $centerApplicationId, int $cycleId, bool $onlyActive = false): array
{
    $rows = list_center_assessments_by_cycle($centerApplicationId, $cycleId);
    $options = [];
    foreach ($rows as $assessment) {
        $assessmentId = (int) ($assessment['id'] ?? 0);
        if ($assessmentId <= 0) {
            continue;
        }

        $isActive = (int) ($assessment['is_active'] ?? 0) === 1;
        if ($onlyActive && !$isActive) {
            continue;
        }

        $title = trim((string) ($assessment['title'] ?? ''));
        $label = $title !== '' ? $title : 'تقييم غير مسمى';
        $category = trim((string) ($assessment['category'] ?? ''));
        if ($category !== '') {
            $label .= ' — ' . $category;
        }

        $criteriaCount = (int) ($assessment['criteria_count'] ?? 0);
        $criteriaTotal = (float) ($assessment['criteria_total_max_score'] ?? 0);

        $options[$assessmentId] = [
            'id' => $assessmentId,
            'label' => $label,
            'title' => $title,
            'category' => $category,
            'max_score' => (float) ($assessment['max_score'] ?? 0),
            'weight_percentage' => (float) ($assessment['weight_percentage'] ?? 0),
            'criteria_count' => $criteriaCount,
            'criteria_total_max_score' => $criteriaTotal,
            'has_criteria' => $criteriaCount > 0,
            'is_active' => $isActive,
        ];
    }

    return $options;
}

function list_center_assessment_criteria_by_assessment(int $centerApplicationId, int $cycleId, int $assessmentTypeId, bool $onlyActive = false): array
{
    $pdo = db_connection();
    $query = 'SELECT *
        FROM center_assessment_criteria
        WHERE center_application_id = :center_application_id
          AND cycle_id = :cycle_id
          AND assessment_type_id = :assessment_type_id';
    $params = [
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => $assessmentTypeId,
    ];

    if ($onlyActive) {
        $query .= ' AND is_active = 1';
    }

    $query .= ' ORDER BY sort_order ASC, id ASC';
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function center_assessment_criteria_metrics(int $centerApplicationId, int $cycleId, int $assessmentTypeId): array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        "SELECT
            COUNT(*) AS total_count,
            COALESCE(SUM(is_active = 1), 0) AS active_count,
            COALESCE(SUM(CASE WHEN is_active = 1 THEN max_score ELSE 0 END), 0) AS active_max_score
         FROM center_assessment_criteria
         WHERE center_application_id = :center_application_id
           AND cycle_id = :cycle_id
           AND assessment_type_id = :assessment_type_id"
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => $assessmentTypeId,
    ]);
    $row = $stmt->fetch() ?: [];

    return [
        'total' => (int) ($row['total_count'] ?? 0),
        'active' => (int) ($row['active_count'] ?? 0),
        'active_max_score' => (float) ($row['active_max_score'] ?? 0),
    ];
}

function validate_center_assessment_criteria_input(int $centerApplicationId, int $cycleId, int $assessmentTypeId, array $input): array
{
    $data = ['criteria' => []];
    $errors = [];
    $assessmentOptions = center_assessment_type_options_by_cycle($centerApplicationId, $cycleId, false);
    if (!array_key_exists($assessmentTypeId, $assessmentOptions)) {
        return [$data, ['form' => 'يرجى اختيار تقييم مركز صحيح من نفس الدورة.']];
    }

    $postedRows = $input['criteria'] ?? [];
    if (!is_array($postedRows)) {
        $postedRows = [];
    }

    $position = 1;
    $activeCount = 0;
    foreach ($postedRows as $rowKey => $row) {
        if (!is_array($row)) {
            continue;
        }

        $criterionId = (int) ($row['id'] ?? 0);
        $title = clean_text((string) ($row['title'] ?? ''), 150);
        $maxScoreRaw = str_replace(',', '.', clean_text((string) ($row['max_score'] ?? ''), 30));
        $notes = clean_text((string) ($row['notes'] ?? ''), 500);
        $isActive = ((string) ($row['is_active'] ?? '1')) === '1' ? 1 : 0;

        if ($criterionId <= 0 && $title === '' && $maxScoreRaw === '' && $notes === '') {
            continue;
        }

        $rowErrors = [];
        if ($title === '') {
            $rowErrors[] = 'اسم البند مطلوب.';
        }

        $maxScore = null;
        if ($maxScoreRaw === '' || !is_numeric($maxScoreRaw)) {
            $rowErrors[] = 'أدخل درجة رقمية للبند.';
        } else {
            $maxScore = round((float) $maxScoreRaw, 2);
            if ($maxScore <= 0 || $maxScore > 1000) {
                $rowErrors[] = 'درجة البند يجب أن تكون بين 0.01 و1000.';
            }
        }

        if ($rowErrors !== []) {
            $errors['criteria_' . $rowKey] = implode(' ', $rowErrors);
        }

        $data['criteria'][] = [
            'id' => $criterionId,
            'title' => $title,
            'max_score' => $maxScore !== null ? number_format($maxScore, 2, '.', '') : '',
            'notes' => $notes,
            'is_active' => (string) $isActive,
            'sort_order' => $position,
        ];

        if ($isActive === 1) {
            $activeCount++;
        }
        $position++;
    }

    if ($data['criteria'] === []) {
        $errors['form'] = 'أضف بند تقييم واحداً على الأقل قبل الحفظ.';
    } elseif ($activeCount === 0) {
        $errors['form'] = 'فعّل بنداً واحداً على الأقل ليظهر في رصد تقييم المركز.';
    }

    return [$data, $errors];
}

function sync_center_assessment_total_score_from_criteria(int $centerApplicationId, int $cycleId, int $assessmentTypeId): void
{
    $pdo = db_connection();
    $criteria = list_center_assessment_criteria_by_assessment($centerApplicationId, $cycleId, $assessmentTypeId, true);
    if ($criteria === []) {
        return;
    }

    $totalMaxScore = 0.0;
    foreach ($criteria as $criterion) {
        $totalMaxScore += (float) ($criterion['max_score'] ?? 0);
    }
    $totalMaxScore = round($totalMaxScore, 2);

    $assessmentStmt = $pdo->prepare(
        'UPDATE center_assessment_types
         SET max_score = :max_score, updated_at = NOW()
         WHERE id = :id AND center_application_id = :center_application_id AND cycle_id = :cycle_id'
    );
    $assessmentStmt->execute([
        ':max_score' => $totalMaxScore,
        ':id' => $assessmentTypeId,
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ]);

    $itemStmt = $pdo->prepare(
        'UPDATE center_assessment_score_items items
         INNER JOIN center_assessment_criteria criteria ON criteria.id = items.criterion_id
         SET items.max_score = criteria.max_score,
             items.updated_at = NOW()
         WHERE items.center_application_id = :center_application_id
           AND items.cycle_id = :cycle_id
           AND items.assessment_type_id = :assessment_type_id'
    );
    $itemStmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => $assessmentTypeId,
    ]);

    $scoreStmt = $pdo->prepare(
        'UPDATE center_assessment_scores
         SET max_score = :max_score,
             updated_at = NOW()
         WHERE center_application_id = :center_application_id
           AND cycle_id = :cycle_id
           AND assessment_type_id = :assessment_type_id'
    );
    $scoreStmt->execute([
        ':max_score' => $totalMaxScore,
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => $assessmentTypeId,
    ]);
}

function save_center_assessment_criteria_in_cycle(int $centerApplicationId, int $cycleId, int $assessmentTypeId, array $data): int
{
    $pdo = db_connection();
    $existingStmt = $pdo->prepare(
        'SELECT id FROM center_assessment_criteria
         WHERE center_application_id = :center_application_id
           AND cycle_id = :cycle_id
           AND assessment_type_id = :assessment_type_id'
    );
    $existingStmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => $assessmentTypeId,
    ]);
    $existingIds = array_map('intval', $existingStmt->fetchAll(PDO::FETCH_COLUMN));
    $existingMap = array_fill_keys($existingIds, true);

    $insertStmt = $pdo->prepare(
        'INSERT INTO center_assessment_criteria (
            center_application_id, cycle_id, assessment_type_id, title, max_score,
            sort_order, is_active, notes, created_at, updated_at
        ) VALUES (
            :center_application_id, :cycle_id, :assessment_type_id, :title, :max_score,
            :sort_order, :is_active, :notes, NOW(), NOW()
        )'
    );
    $updateStmt = $pdo->prepare(
        'UPDATE center_assessment_criteria
         SET title = :title,
             max_score = :max_score,
             sort_order = :sort_order,
             is_active = :is_active,
             notes = :notes,
             updated_at = NOW()
         WHERE id = :id
           AND center_application_id = :center_application_id
           AND cycle_id = :cycle_id
           AND assessment_type_id = :assessment_type_id'
    );

    $saved = 0;
    foreach ($data['criteria'] as $criterion) {
        $params = [
            ':center_application_id' => $centerApplicationId,
            ':cycle_id' => $cycleId,
            ':assessment_type_id' => $assessmentTypeId,
            ':title' => (string) ($criterion['title'] ?? ''),
            ':max_score' => (float) ($criterion['max_score'] ?? 0),
            ':sort_order' => (int) ($criterion['sort_order'] ?? 0),
            ':is_active' => ((string) ($criterion['is_active'] ?? '1')) === '1' ? 1 : 0,
            ':notes' => !empty($criterion['notes']) ? (string) $criterion['notes'] : null,
        ];

        $criterionId = (int) ($criterion['id'] ?? 0);
        if ($criterionId > 0 && isset($existingMap[$criterionId])) {
            $updateStmt->execute($params + [':id' => $criterionId]);
        } else {
            $insertStmt->execute($params);
        }
        $saved++;
    }

    sync_center_assessment_total_score_from_criteria($centerApplicationId, $cycleId, $assessmentTypeId);
    return $saved;
}

function school_assessment_type_options_by_cycle(int $centerApplicationId, int $cycleId, bool $onlyActive = false): array
{
    $rows = list_school_assessments_by_cycle($centerApplicationId, $cycleId);
    $subjects = [];
    foreach (get_enabled_subjects() as $subject) {
        $subjects[(int) ($subject['id'] ?? 0)] = (string) ($subject['name'] ?? '');
    }

    $options = [];
    foreach ($rows as $assessment) {
        $assessmentId = (int) ($assessment['id'] ?? 0);
        if ($assessmentId <= 0) {
            continue;
        }

        $isActive = (int) ($assessment['is_active'] ?? 0) === 1;
        if ($onlyActive && !$isActive) {
            continue;
        }

        $subjectId = (int) ($assessment['subject_id'] ?? 0);
        $subjectLabel = $subjectId > 0 ? ($subjects[$subjectId] ?? '') : '';
        $title = trim((string) ($assessment['title'] ?? ''));
        $label = $title !== '' ? $title : 'تقييم غير مسمى';
        if ($subjectLabel !== '') {
            $label .= ' — ' . $subjectLabel;
        }

        $criteriaCount = (int) ($assessment['criteria_count'] ?? 0);
        $criteriaTotal = (float) ($assessment['criteria_total_max_score'] ?? 0);

        $options[$assessmentId] = [
            'id' => $assessmentId,
            'label' => $label,
            'title' => $title,
            'subject_label' => $subjectLabel,
            'category' => (string) ($assessment['category'] ?? ''),
            'max_score' => (float) ($assessment['max_score'] ?? 0),
            'weight_percentage' => (float) ($assessment['weight_percentage'] ?? 0),
            'criteria_count' => $criteriaCount,
            'criteria_total_max_score' => $criteriaTotal,
            'has_criteria' => $criteriaCount > 0,
            'is_active' => $isActive,
        ];
    }

    return $options;
}

function list_assessment_criteria_by_assessment(int $centerApplicationId, int $cycleId, int $assessmentTypeId, bool $onlyActive = false): array
{
    $pdo = db_connection();
    $query = 'SELECT *
        FROM school_assessment_criteria
        WHERE center_application_id = :center_application_id
          AND cycle_id = :cycle_id
          AND assessment_type_id = :assessment_type_id';
    $params = [
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => $assessmentTypeId,
    ];

    if ($onlyActive) {
        $query .= ' AND is_active = 1';
    }

    $query .= ' ORDER BY sort_order ASC, id ASC';
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function school_assessment_criteria_metrics(int $centerApplicationId, int $cycleId, int $assessmentTypeId): array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        "SELECT
            COUNT(*) AS total_count,
            COALESCE(SUM(is_active = 1), 0) AS active_count,
            COALESCE(SUM(CASE WHEN is_active = 1 THEN max_score ELSE 0 END), 0) AS active_max_score
         FROM school_assessment_criteria
         WHERE center_application_id = :center_application_id
           AND cycle_id = :cycle_id
           AND assessment_type_id = :assessment_type_id"
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => $assessmentTypeId,
    ]);
    $row = $stmt->fetch() ?: [];

    return [
        'total' => (int) ($row['total_count'] ?? 0),
        'active' => (int) ($row['active_count'] ?? 0),
        'active_max_score' => (float) ($row['active_max_score'] ?? 0),
    ];
}

function validate_assessment_criteria_input(int $centerApplicationId, int $cycleId, int $assessmentTypeId, array $input): array
{
    $data = ['criteria' => []];
    $errors = [];
    $assessmentOptions = school_assessment_type_options_by_cycle($centerApplicationId, $cycleId, false);
    if (!array_key_exists($assessmentTypeId, $assessmentOptions)) {
        return [$data, ['form' => 'يرجى اختيار تقييم صحيح من نفس الدورة.']];
    }

    $postedRows = $input['criteria'] ?? [];
    if (!is_array($postedRows)) {
        $postedRows = [];
    }

    $position = 1;
    $activeCount = 0;
    foreach ($postedRows as $rowKey => $row) {
        if (!is_array($row)) {
            continue;
        }

        $criterionId = (int) ($row['id'] ?? 0);
        $title = clean_text((string) ($row['title'] ?? ''), 150);
        $maxScoreRaw = str_replace(',', '.', clean_text((string) ($row['max_score'] ?? ''), 30));
        $notes = clean_text((string) ($row['notes'] ?? ''), 500);
        $isActive = ((string) ($row['is_active'] ?? '1')) === '1' ? 1 : 0;

        if ($criterionId <= 0 && $title === '' && $maxScoreRaw === '' && $notes === '') {
            continue;
        }

        $rowErrors = [];
        if ($title === '') {
            $rowErrors[] = 'اسم البند مطلوب.';
        }

        $maxScore = null;
        if ($maxScoreRaw === '' || !is_numeric($maxScoreRaw)) {
            $rowErrors[] = 'أدخل درجة رقمية للبند.';
        } else {
            $maxScore = round((float) $maxScoreRaw, 2);
            if ($maxScore <= 0 || $maxScore > 1000) {
                $rowErrors[] = 'درجة البند يجب أن تكون بين 0.01 و1000.';
            }
        }

        if ($rowErrors !== []) {
            $errors['criteria_' . $rowKey] = implode(' ', $rowErrors);
        }

        $data['criteria'][] = [
            'id' => $criterionId,
            'title' => $title,
            'max_score' => $maxScore !== null ? number_format($maxScore, 2, '.', '') : '',
            'notes' => $notes,
            'is_active' => (string) $isActive,
            'sort_order' => $position,
        ];

        if ($isActive === 1) {
            $activeCount++;
        }
        $position++;
    }

    if ($data['criteria'] === []) {
        $errors['form'] = 'أضف بند تقييم واحد على الأقل قبل الحفظ.';
    } elseif ($activeCount === 0) {
        $errors['form'] = 'فعّل بنداً واحداً على الأقل ليظهر في ورقة الرصد.';
    }

    return [$data, $errors];
}

function sync_assessment_total_score_from_criteria(int $centerApplicationId, int $cycleId, int $assessmentTypeId): void
{
    $pdo = db_connection();
    $criteria = list_assessment_criteria_by_assessment($centerApplicationId, $cycleId, $assessmentTypeId, true);
    if ($criteria === []) {
        return;
    }

    $totalMaxScore = 0.0;
    foreach ($criteria as $criterion) {
        $totalMaxScore += (float) ($criterion['max_score'] ?? 0);
    }
    $totalMaxScore = round($totalMaxScore, 2);

    $assessmentStmt = $pdo->prepare(
        'UPDATE school_assessment_types
         SET max_score = :max_score, updated_at = NOW()
         WHERE id = :id AND center_application_id = :center_application_id AND cycle_id = :cycle_id'
    );
    $assessmentStmt->execute([
        ':max_score' => $totalMaxScore,
        ':id' => $assessmentTypeId,
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ]);

    $itemStmt = $pdo->prepare(
        'UPDATE school_assessment_score_items items
         INNER JOIN school_assessment_criteria criteria ON criteria.id = items.criterion_id
         SET items.max_score = criteria.max_score,
             items.updated_at = NOW()
         WHERE items.center_application_id = :center_application_id
           AND items.cycle_id = :cycle_id
           AND items.assessment_type_id = :assessment_type_id'
    );
    $itemStmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => $assessmentTypeId,
    ]);

    $scoreStmt = $pdo->prepare(
        'UPDATE school_assessment_scores
         SET max_score = :max_score,
             updated_at = NOW()
         WHERE center_application_id = :center_application_id
           AND cycle_id = :cycle_id
           AND assessment_type_id = :assessment_type_id'
    );
    $scoreStmt->execute([
        ':max_score' => $totalMaxScore,
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => $assessmentTypeId,
    ]);
}

function save_assessment_criteria_in_cycle(int $centerApplicationId, int $cycleId, int $assessmentTypeId, array $data): int
{
    $pdo = db_connection();
    $existingStmt = $pdo->prepare(
        'SELECT id FROM school_assessment_criteria
         WHERE center_application_id = :center_application_id
           AND cycle_id = :cycle_id
           AND assessment_type_id = :assessment_type_id'
    );
    $existingStmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => $assessmentTypeId,
    ]);
    $existingIds = array_map('intval', $existingStmt->fetchAll(PDO::FETCH_COLUMN));
    $existingMap = array_fill_keys($existingIds, true);

    $insertStmt = $pdo->prepare(
        'INSERT INTO school_assessment_criteria (
            center_application_id, cycle_id, assessment_type_id, title, max_score,
            sort_order, is_active, notes, created_at, updated_at
        ) VALUES (
            :center_application_id, :cycle_id, :assessment_type_id, :title, :max_score,
            :sort_order, :is_active, :notes, NOW(), NOW()
        )'
    );
    $updateStmt = $pdo->prepare(
        'UPDATE school_assessment_criteria
         SET title = :title,
             max_score = :max_score,
             sort_order = :sort_order,
             is_active = :is_active,
             notes = :notes,
             updated_at = NOW()
         WHERE id = :id
           AND center_application_id = :center_application_id
           AND cycle_id = :cycle_id
           AND assessment_type_id = :assessment_type_id'
    );

    $saved = 0;
    foreach ($data['criteria'] as $criterion) {
        $params = [
            ':center_application_id' => $centerApplicationId,
            ':cycle_id' => $cycleId,
            ':assessment_type_id' => $assessmentTypeId,
            ':title' => (string) ($criterion['title'] ?? ''),
            ':max_score' => (float) ($criterion['max_score'] ?? 0),
            ':sort_order' => (int) ($criterion['sort_order'] ?? 0),
            ':is_active' => ((string) ($criterion['is_active'] ?? '1')) === '1' ? 1 : 0,
            ':notes' => !empty($criterion['notes']) ? (string) $criterion['notes'] : null,
        ];

        $criterionId = (int) ($criterion['id'] ?? 0);
        if ($criterionId > 0 && isset($existingMap[$criterionId])) {
            $updateStmt->execute($params + [':id' => $criterionId]);
        } else {
            $insertStmt->execute($params);
        }
        $saved++;
    }

    sync_assessment_total_score_from_criteria($centerApplicationId, $cycleId, $assessmentTypeId);
    return $saved;
}

function validate_assessment_scores_batch_input(int $centerApplicationId, int $cycleId, array $input): array
{
    $data = [
        'assessment_type_id' => (string) ((int) ($input['assessment_type_id'] ?? 0)),
        'teacher_id' => (string) ((int) ($input['teacher_id'] ?? 0)),
        'assessed_on' => clean_text((string) ($input['assessed_on'] ?? date('Y-m-d')), 20),
        'assessment_max_score' => 0.0,
        'has_criteria' => false,
        'criteria' => [],
        'entries' => [],
    ];

    $errors = [];
    $assessmentOptions = school_assessment_type_options_by_cycle($centerApplicationId, $cycleId, false);
    $teacherOptions = school_teacher_options_by_cycle($centerApplicationId, $cycleId, false);
    $studentOptions = school_student_options_by_cycle($centerApplicationId, $cycleId);
    $statusMap = assessment_score_status_map();

    $assessmentId = (int) $data['assessment_type_id'];
    $selectedAssessment = $assessmentOptions[$assessmentId] ?? null;
    if ($selectedAssessment === null) {
        $errors['assessment_type_id'] = 'يرجى اختيار تقييم صحيح من نفس الدورة.';
    }

    $criteriaRows = $assessmentId > 0
        ? list_assessment_criteria_by_assessment($centerApplicationId, $cycleId, $assessmentId, true)
        : [];
    $criteriaMap = [];
    foreach ($criteriaRows as $criterion) {
        $criteriaId = (int) ($criterion['id'] ?? 0);
        if ($criteriaId <= 0) {
            continue;
        }
        $criteriaMap[$criteriaId] = $criterion;
        $data['assessment_max_score'] += (float) ($criterion['max_score'] ?? 0);
    }
    $data['assessment_max_score'] = round($data['assessment_max_score'], 2);
    $data['has_criteria'] = $criteriaMap !== [];
    $data['criteria'] = $criteriaRows;

    if (!$data['has_criteria'] && $selectedAssessment !== null) {
        $data['assessment_max_score'] = (float) ($selectedAssessment['max_score'] ?? 0);
    }

    $teacherId = (int) $data['teacher_id'];
    if ($teacherId > 0 && !array_key_exists($teacherId, $teacherOptions)) {
        $errors['teacher_id'] = 'يرجى اختيار معلم صحيح من نفس الدورة.';
    }

    if ($data['assessed_on'] === '' || strtotime($data['assessed_on']) === false) {
        $errors['assessed_on'] = 'يرجى إدخال تاريخ تقييم صحيح.';
    }

    $postedEntries = $input['entries'] ?? [];
    if (!is_array($postedEntries)) {
        $postedEntries = [];
    }

    $hasSaveableRow = false;
    foreach ($postedEntries as $studentKey => $row) {
        if (!is_array($row)) {
            continue;
        }

        $studentId = (int) $studentKey;
        if ($studentId <= 0 || !array_key_exists($studentId, $studentOptions)) {
            continue;
        }

        $status = clean_text((string) ($row['status'] ?? 'present'), 20);
        if (!array_key_exists($status, $statusMap)) {
            $status = 'present';
        }

        $notes = clean_text((string) ($row['notes'] ?? ''), 1000);
        $criteriaScores = [];
        $score = null;
        $scoreRaw = '';
        $hasCriteriaInput = false;

        if ($data['has_criteria']) {
            $postedCriterionScores = $row['criteria'] ?? [];
            if (!is_array($postedCriterionScores)) {
                $postedCriterionScores = [];
            }

            $missingCriteria = [];
            $totalScore = 0.0;
            foreach ($criteriaMap as $criterionId => $criterion) {
                $rawValue = str_replace(',', '.', clean_text((string) ($postedCriterionScores[$criterionId] ?? ''), 30));
                $criteriaScores[$criterionId] = [
                    'criterion_id' => $criterionId,
                    'score' => null,
                    'score_raw' => $rawValue,
                    'max_score' => (float) ($criterion['max_score'] ?? 0),
                ];

                if ($rawValue === '') {
                    $missingCriteria[] = (string) ($criterion['title'] ?? '');
                    continue;
                }

                $hasCriteriaInput = true;
                if (!is_numeric($rawValue)) {
                    $errors['entries_' . $studentId] = 'كل بند يجب أن يحتوي على درجة رقمية صحيحة.';
                    continue;
                }

                $criterionScore = round((float) $rawValue, 2);
                $criterionMax = (float) ($criterion['max_score'] ?? 0);
                if ($criterionScore < 0 || $criterionScore > $criterionMax) {
                    $errors['entries_' . $studentId] = 'درجة البند يجب أن تكون بين 0 و ' . rtrim(rtrim(number_format($criterionMax, 2, '.', ''), '0'), '.') . '.';
                    continue;
                }

                $criteriaScores[$criterionId]['score'] = $criterionScore;
                $totalScore += $criterionScore;
            }

            $shouldSave = $status !== 'present' || $hasCriteriaInput || $notes !== '';
            if ($status === 'present' && $shouldSave && $missingCriteria !== []) {
                $errors['entries_' . $studentId] = 'للطالب الحاضر يجب تعبئة جميع البنود النشطة قبل الحفظ.';
            }
            if ($status === 'present' && $missingCriteria === [] && !isset($errors['entries_' . $studentId])) {
                $score = round($totalScore, 2);
                $scoreRaw = number_format($score, 2, '.', '');
            }

            if ($status !== 'present') {
                foreach ($criteriaScores as $criterionId => $criterionScoreData) {
                    $criteriaScores[$criterionId]['score'] = null;
                    $criteriaScores[$criterionId]['score_raw'] = '';
                }
                $score = null;
                $scoreRaw = '';
            }
        } else {
            $scoreRaw = clean_text((string) ($row['score'] ?? ''), 30);
            $shouldSave = $status !== 'present' || $scoreRaw !== '' || $notes !== '';
            if ($scoreRaw !== '') {
                if (!is_numeric($scoreRaw)) {
                    $errors['entries_' . $studentId] = 'الدرجة يجب أن تكون رقماً صحيحاً أو عشرياً.';
                } else {
                    $score = round((float) $scoreRaw, 2);
                    if ($selectedAssessment !== null && ($score < 0 || $score > (float) $data['assessment_max_score'])) {
                        $errors['entries_' . $studentId] = 'الدرجة يجب أن تكون بين 0 و ' . rtrim(rtrim(number_format((float) $data['assessment_max_score'], 2, '.', ''), '0'), '.');
                    }
                }
            }

            if ($status === 'present' && $shouldSave && $scoreRaw === '') {
                $errors['entries_' . $studentId] = 'أدخل الدرجة أو غيّر الحالة إلى غائب أو بعذر.';
            }

            if ($status !== 'present') {
                $score = null;
                $scoreRaw = '';
            }
        }

        if ($shouldSave) {
            $hasSaveableRow = true;
        }

        $data['entries'][$studentId] = [
            'student_id' => $studentId,
            'status' => $status,
            'score' => $score,
            'score_raw' => $scoreRaw,
            'total_score' => $score,
            'notes' => $notes,
            'criteria_scores' => $criteriaScores,
            'should_save' => $shouldSave,
        ];
    }

    if (!$hasSaveableRow && $errors === []) {
        $errors['form'] = $data['has_criteria']
            ? 'أدخل درجات البنود أو حدّد حالة طالب واحد على الأقل قبل الحفظ.'
            : 'أدخل درجات أو حدّد حالة طالب واحد على الأقل قبل الحفظ.';
    }

    return [$data, $errors, $selectedAssessment];
}

function save_assessment_scores_in_cycle(int $centerApplicationId, int $cycleId, array $data): int
{
    $pdo = db_connection();
    $criteriaRows = !empty($data['has_criteria'])
        ? list_assessment_criteria_by_assessment($centerApplicationId, $cycleId, (int) $data['assessment_type_id'], true)
        : [];
    $criteriaMap = [];
    foreach ($criteriaRows as $criterion) {
        $criteriaId = (int) ($criterion['id'] ?? 0);
        if ($criteriaId > 0) {
            $criteriaMap[$criteriaId] = $criterion;
        }
    }

    $stmt = $pdo->prepare(
        'INSERT INTO school_assessment_scores (
            center_application_id, cycle_id, assessment_type_id, student_id, teacher_id,
            score, max_score, status, notes, assessed_on, created_at, updated_at
        ) VALUES (
            :center_application_id, :cycle_id, :assessment_type_id, :student_id, :teacher_id,
            :score, :max_score, :status, :notes, :assessed_on, NOW(), NOW()
        )
        ON DUPLICATE KEY UPDATE
            id = LAST_INSERT_ID(id),
            teacher_id = VALUES(teacher_id),
            score = VALUES(score),
            max_score = VALUES(max_score),
            status = VALUES(status),
            notes = VALUES(notes),
            assessed_on = VALUES(assessed_on),
            updated_at = NOW()'
    );
    $deleteItemsStmt = $pdo->prepare('DELETE FROM school_assessment_score_items WHERE assessment_score_id = :assessment_score_id');
    $itemStmt = $pdo->prepare(
        'INSERT INTO school_assessment_score_items (
            center_application_id, cycle_id, assessment_score_id, assessment_type_id, criterion_id,
            student_id, score, max_score, created_at, updated_at
        ) VALUES (
            :center_application_id, :cycle_id, :assessment_score_id, :assessment_type_id, :criterion_id,
            :student_id, :score, :max_score, NOW(), NOW()
        )'
    );

    $saved = 0;
    foreach ($data['entries'] as $entry) {
        if (empty($entry['should_save'])) {
            continue;
        }

        $stmt->execute([
            ':center_application_id' => $centerApplicationId,
            ':cycle_id' => $cycleId,
            ':assessment_type_id' => (int) $data['assessment_type_id'],
            ':student_id' => (int) $entry['student_id'],
            ':teacher_id' => !empty($data['teacher_id']) ? (int) $data['teacher_id'] : null,
            ':score' => $entry['score'],
            ':max_score' => (float) ($data['assessment_max_score'] ?? 0),
            ':status' => $entry['status'],
            ':notes' => $entry['notes'] !== '' ? $entry['notes'] : null,
            ':assessed_on' => $data['assessed_on'],
        ]);

        $assessmentScoreId = (int) $pdo->lastInsertId();
        if ($assessmentScoreId > 0) {
            $deleteItemsStmt->execute([':assessment_score_id' => $assessmentScoreId]);
            if ($criteriaMap !== [] && (string) ($entry['status'] ?? 'present') === 'present') {
                foreach ($entry['criteria_scores'] as $criterionId => $criterionScoreData) {
                    if (!array_key_exists((int) $criterionId, $criteriaMap) || ($criterionScoreData['score'] ?? null) === null) {
                        continue;
                    }

                    $itemStmt->execute([
                        ':center_application_id' => $centerApplicationId,
                        ':cycle_id' => $cycleId,
                        ':assessment_score_id' => $assessmentScoreId,
                        ':assessment_type_id' => (int) $data['assessment_type_id'],
                        ':criterion_id' => (int) $criterionId,
                        ':student_id' => (int) $entry['student_id'],
                        ':score' => (float) $criterionScoreData['score'],
                        ':max_score' => (float) ($criteriaMap[(int) $criterionId]['max_score'] ?? 0),
                    ]);
                }
            }
        }

        $saved++;
    }

    return $saved;
}

function list_assessment_scores_by_assessment(int $centerApplicationId, int $cycleId, int $assessmentTypeId): array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'SELECT scores.*, teachers.full_name AS teacher_name
         FROM school_assessment_scores scores
         LEFT JOIN school_teachers teachers ON teachers.id = scores.teacher_id
         WHERE scores.center_application_id = :center_application_id
           AND scores.cycle_id = :cycle_id
           AND scores.assessment_type_id = :assessment_type_id
         ORDER BY scores.assessed_on DESC, scores.updated_at DESC, scores.id DESC'
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => $assessmentTypeId,
    ]);

    return $stmt->fetchAll();
}

function list_assessment_score_items_by_assessment(int $centerApplicationId, int $cycleId, int $assessmentTypeId): array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'SELECT items.*
         FROM school_assessment_score_items items
         WHERE items.center_application_id = :center_application_id
           AND items.cycle_id = :cycle_id
           AND items.assessment_type_id = :assessment_type_id
         ORDER BY items.assessment_score_id ASC, items.id ASC'
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => $assessmentTypeId,
    ]);
    return $stmt->fetchAll();
}

function school_assessment_score_map_by_assessment(int $centerApplicationId, int $cycleId, int $assessmentTypeId): array
{
    $map = [];
    foreach (list_assessment_scores_by_assessment($centerApplicationId, $cycleId, $assessmentTypeId) as $row) {
        $studentId = (int) ($row['student_id'] ?? 0);
        if ($studentId <= 0 || array_key_exists($studentId, $map)) {
            continue;
        }
        $row['criteria_scores'] = [];
        $map[$studentId] = $row;
    }

    foreach (list_assessment_score_items_by_assessment($centerApplicationId, $cycleId, $assessmentTypeId) as $item) {
        $studentId = (int) ($item['student_id'] ?? 0);
        $criterionId = (int) ($item['criterion_id'] ?? 0);
        if ($studentId <= 0 || $criterionId <= 0 || !isset($map[$studentId])) {
            continue;
        }
        $map[$studentId]['criteria_scores'][$criterionId] = $item;
    }

    return $map;
}

function school_assessment_score_metrics_by_cycle(int $centerApplicationId, int $cycleId, int $assessmentTypeId = 0): array
{
    $pdo = db_connection();
    $query = "SELECT
            COUNT(*) AS total,
            COALESCE(SUM(status = 'present'), 0) AS present_count,
            COALESCE(SUM(status = 'absent'), 0) AS absent_count,
            COALESCE(SUM(status = 'excused'), 0) AS excused_count,
            COALESCE(AVG(CASE WHEN status = 'present' THEN score END), 0) AS average_score,
            MAX(assessed_on) AS latest_date
         FROM school_assessment_scores
         WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id";
    $params = [
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ];

    if ($assessmentTypeId > 0) {
        $query .= ' AND assessment_type_id = :assessment_type_id';
        $params[':assessment_type_id'] = $assessmentTypeId;
    }

    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    $row = $stmt->fetch() ?: [];

    return [
        'total' => (int) ($row['total'] ?? 0),
        'present' => (int) ($row['present_count'] ?? 0),
        'absent' => (int) ($row['absent_count'] ?? 0),
        'excused' => (int) ($row['excused_count'] ?? 0),
        'average_score' => (float) ($row['average_score'] ?? 0),
        'latest_date' => (string) ($row['latest_date'] ?? ''),
    ];
}


function center_assessment_normalize_status(string $status): string
{
    return match ($status) {
        'present' => 'completed',
        'absent', 'draft' => 'pending',
        'excused' => 'waived',
        'completed', 'pending', 'waived' => $status,
        default => 'pending',
    };
}

function center_assessment_status_map(): array
{
    return [
        'completed' => ['label' => 'مكتمل', 'class' => 'status-approved'],
        'pending' => ['label' => 'مؤجل', 'class' => 'status-review'],
        'waived' => ['label' => 'معفى', 'class' => 'status-muted'],
    ];
}

function center_assessment_status_badge(string $status): string
{
    $normalizedStatus = center_assessment_normalize_status($status);
    $map = center_assessment_status_map();
    $meta = $map[$normalizedStatus] ?? ['label' => 'غير محدد', 'class' => 'status-muted'];
    return '<span class="status-badge ' . e($meta['class']) . '">' . e($meta['label']) . '</span>';
}

function validate_center_assessment_score_input(int $centerApplicationId, int $cycleId, array $input): array
{
    $data = [
        'assessment_type_id' => (string) ((int) ($input['assessment_type_id'] ?? 0)),
        'assessed_on' => clean_text((string) ($input['assessed_on'] ?? date('Y-m-d')), 20),
        'status' => center_assessment_normalize_status(clean_text((string) ($input['status'] ?? 'completed'), 20)),
        'assessment_max_score' => 0.0,
        'has_criteria' => false,
        'criteria' => [],
        'criteria_scores' => [],
        'score' => null,
        'score_raw' => clean_text((string) ($input['score'] ?? ''), 30),
        'notes' => clean_text((string) ($input['notes'] ?? ''), 1000),
        'should_save' => false,
    ];

    $errors = [];
    $assessmentOptions = center_assessment_type_options_by_cycle($centerApplicationId, $cycleId, false);
    $statusMap = center_assessment_status_map();

    $assessmentId = (int) $data['assessment_type_id'];
    $selectedAssessment = $assessmentOptions[$assessmentId] ?? null;
    if ($selectedAssessment === null) {
        $errors['assessment_type_id'] = 'يرجى اختيار تقييم مركز صحيح من نفس الدورة.';
    }

    if (!array_key_exists($data['status'], $statusMap)) {
        $data['status'] = 'completed';
    }

    if ($data['assessed_on'] === '' || strtotime($data['assessed_on']) === false) {
        $errors['assessed_on'] = 'يرجى إدخال تاريخ تقييم صحيح.';
    }

    $criteriaRows = $assessmentId > 0
        ? list_center_assessment_criteria_by_assessment($centerApplicationId, $cycleId, $assessmentId, true)
        : [];
    $criteriaMap = [];
    foreach ($criteriaRows as $criterion) {
        $criterionId = (int) ($criterion['id'] ?? 0);
        if ($criterionId <= 0) {
            continue;
        }
        $criteriaMap[$criterionId] = $criterion;
        $data['assessment_max_score'] += (float) ($criterion['max_score'] ?? 0);
    }
    $data['assessment_max_score'] = round($data['assessment_max_score'], 2);
    $data['has_criteria'] = $criteriaMap !== [];
    $data['criteria'] = $criteriaRows;

    if (!$data['has_criteria'] && $selectedAssessment !== null) {
        $data['assessment_max_score'] = (float) ($selectedAssessment['max_score'] ?? 0);
    }

    if ($data['has_criteria']) {
        $postedCriterionScores = $input['criteria'] ?? [];
        if (!is_array($postedCriterionScores)) {
            $postedCriterionScores = [];
        }

        $missingCriteria = [];
        $totalScore = 0.0;
        $hasCriteriaInput = false;

        foreach ($criteriaMap as $criterionId => $criterion) {
            $rawValue = str_replace(',', '.', clean_text((string) ($postedCriterionScores[$criterionId] ?? ''), 30));
            $data['criteria_scores'][$criterionId] = [
                'criterion_id' => $criterionId,
                'score' => null,
                'score_raw' => $rawValue,
                'max_score' => (float) ($criterion['max_score'] ?? 0),
            ];

            if ($rawValue == '') {
                $missingCriteria[] = (string) ($criterion['title'] ?? '');
                continue;
            }

            $hasCriteriaInput = true;
            if (!is_numeric($rawValue)) {
                $errors['score'] = 'كل بند يجب أن يحتوي على درجة رقمية صحيحة.';
                continue;
            }

            $criterionScore = round((float) $rawValue, 2);
            $criterionMax = (float) ($criterion['max_score'] ?? 0);
            if ($criterionScore < 0 || $criterionScore > $criterionMax) {
                $errors['score'] = 'درجة كل بند يجب أن تكون بين 0 و ' . rtrim(rtrim(number_format($criterionMax, 2, '.', ''), '0'), '.') . '.';
                continue;
            }

            $data['criteria_scores'][$criterionId]['score'] = $criterionScore;
            $totalScore += $criterionScore;
        }

        $data['should_save'] = $data['status'] !== 'completed' || $hasCriteriaInput || $data['notes'] !== '';
        if ($data['status'] === 'completed' && $data['should_save'] && $missingCriteria !== []) {
            $errors['score'] = 'عند اعتماد التقييم كمكتمل يجب تعبئة جميع البنود النشطة.';
        }

        if ($data['status'] === 'completed' && $missingCriteria === [] && !isset($errors['score'])) {
            $data['score'] = round($totalScore, 2);
            $data['score_raw'] = number_format($data['score'], 2, '.', '');
        }

        if ($data['status'] !== 'completed') {
            foreach ($data['criteria_scores'] as $criterionId => $criterionScoreData) {
                $data['criteria_scores'][$criterionId]['score'] = null;
                $data['criteria_scores'][$criterionId]['score_raw'] = '';
            }
            $data['score'] = null;
            $data['score_raw'] = '';
        }
    } else {
        $scoreRaw = str_replace(',', '.', clean_text((string) ($input['score'] ?? ''), 30));
        $data['score_raw'] = $scoreRaw;
        $data['should_save'] = $data['status'] !== 'completed' || $scoreRaw !== '' || $data['notes'] !== '';

        if ($scoreRaw !== '') {
            if (!is_numeric($scoreRaw)) {
                $errors['score'] = 'الدرجة يجب أن تكون رقماً صحيحاً أو عشرياً.';
            } else {
                $data['score'] = round((float) $scoreRaw, 2);
                if ($selectedAssessment !== null && ($data['score'] < 0 || $data['score'] > (float) $data['assessment_max_score'])) {
                    $errors['score'] = 'الدرجة يجب أن تكون بين 0 و ' . rtrim(rtrim(number_format((float) $data['assessment_max_score'], 2, '.', ''), '0'), '.');
                }
            }
        }

        if ($data['status'] === 'completed' && $data['should_save'] && $scoreRaw === '') {
            $errors['score'] = 'أدخل الدرجة أو غيّر الحالة إلى مؤجل أو معفى.';
        }

        if ($data['status'] !== 'completed') {
            $data['score'] = null;
            $data['score_raw'] = '';
        }
    }

    if (!$data['should_save'] && $errors === []) {
        $errors['form'] = $data['has_criteria']
            ? 'أدخل درجات البنود أو حدّد حالة التقييم قبل الحفظ.'
            : 'أدخل الدرجة أو حدّد حالة التقييم قبل الحفظ.';
    }

    return [$data, $errors, $selectedAssessment];
}

function save_center_assessment_score_in_cycle(int $centerApplicationId, int $cycleId, array $data): int
{
    $pdo = db_connection();
    $criteriaRows = !empty($data['has_criteria'])
        ? list_center_assessment_criteria_by_assessment($centerApplicationId, $cycleId, (int) $data['assessment_type_id'], true)
        : [];
    $criteriaMap = [];
    foreach ($criteriaRows as $criterion) {
        $criteriaId = (int) ($criterion['id'] ?? 0);
        if ($criteriaId > 0) {
            $criteriaMap[$criteriaId] = $criterion;
        }
    }

    $stmt = $pdo->prepare(
        'INSERT INTO center_assessment_scores (
            center_application_id, cycle_id, assessment_type_id,
            score, max_score, status, notes, assessed_on, created_at, updated_at
        ) VALUES (
            :center_application_id, :cycle_id, :assessment_type_id,
            :score, :max_score, :status, :notes, :assessed_on, NOW(), NOW()
        )
        ON DUPLICATE KEY UPDATE
            id = LAST_INSERT_ID(id),
            score = VALUES(score),
            max_score = VALUES(max_score),
            status = VALUES(status),
            notes = VALUES(notes),
            assessed_on = VALUES(assessed_on),
            updated_at = NOW()'
    );
    $deleteItemsStmt = $pdo->prepare('DELETE FROM center_assessment_score_items WHERE assessment_score_id = :assessment_score_id');
    $itemStmt = $pdo->prepare(
        'INSERT INTO center_assessment_score_items (
            center_application_id, cycle_id, assessment_score_id, assessment_type_id, criterion_id,
            score, max_score, created_at, updated_at
        ) VALUES (
            :center_application_id, :cycle_id, :assessment_score_id, :assessment_type_id, :criterion_id,
            :score, :max_score, NOW(), NOW()
        )'
    );

    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => (int) $data['assessment_type_id'],
        ':score' => $data['score'],
        ':max_score' => (float) ($data['assessment_max_score'] ?? 0),
        ':status' => center_assessment_normalize_status((string) ($data['status'] ?? 'completed')),
        ':notes' => $data['notes'] !== '' ? $data['notes'] : null,
        ':assessed_on' => $data['assessed_on'],
    ]);

    $assessmentScoreId = (int) $pdo->lastInsertId();
    if ($assessmentScoreId > 0) {
        $deleteItemsStmt->execute([':assessment_score_id' => $assessmentScoreId]);
        if ($criteriaMap !== [] && center_assessment_normalize_status((string) ($data['status'] ?? 'completed')) === 'completed') {
            foreach ($data['criteria_scores'] as $criterionId => $criterionScoreData) {
                if (!array_key_exists((int) $criterionId, $criteriaMap) || ($criterionScoreData['score'] ?? null) === null) {
                    continue;
                }

                $itemStmt->execute([
                    ':center_application_id' => $centerApplicationId,
                    ':cycle_id' => $cycleId,
                    ':assessment_score_id' => $assessmentScoreId,
                    ':assessment_type_id' => (int) $data['assessment_type_id'],
                    ':criterion_id' => (int) $criterionId,
                    ':score' => (float) $criterionScoreData['score'],
                    ':max_score' => (float) ($criteriaMap[(int) $criterionId]['max_score'] ?? 0),
                ]);
            }
        }
    }

    return 1;
}

function center_assessment_score_by_assessment(int $centerApplicationId, int $cycleId, int $assessmentTypeId): ?array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'SELECT scores.*
         FROM center_assessment_scores scores
         WHERE scores.center_application_id = :center_application_id
           AND scores.cycle_id = :cycle_id
           AND scores.assessment_type_id = :assessment_type_id
         LIMIT 1'
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => $assessmentTypeId,
    ]);
    $row = $stmt->fetch();

    return $row ?: null;
}

function list_center_assessment_score_items_by_assessment(int $centerApplicationId, int $cycleId, int $assessmentTypeId): array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'SELECT items.*, criteria.title AS criterion_title
         FROM center_assessment_score_items items
         LEFT JOIN center_assessment_criteria criteria ON criteria.id = items.criterion_id
         WHERE items.center_application_id = :center_application_id
           AND items.cycle_id = :cycle_id
           AND items.assessment_type_id = :assessment_type_id
         ORDER BY items.id ASC'
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':assessment_type_id' => $assessmentTypeId,
    ]);

    return $stmt->fetchAll();
}

function center_assessment_score_bundle_by_assessment(int $centerApplicationId, int $cycleId, int $assessmentTypeId): array
{
    $score = center_assessment_score_by_assessment($centerApplicationId, $cycleId, $assessmentTypeId);
    $criteriaScores = [];
    foreach (list_center_assessment_score_items_by_assessment($centerApplicationId, $cycleId, $assessmentTypeId) as $item) {
        $criterionId = (int) ($item['criterion_id'] ?? 0);
        if ($criterionId <= 0) {
            continue;
        }
        $criteriaScores[$criterionId] = $item;
    }

    return [
        'score' => $score,
        'criteria_scores' => $criteriaScores,
    ];
}

function center_assessment_score_metrics_by_cycle(int $centerApplicationId, int $cycleId, int $assessmentTypeId = 0): array
{
    $pdo = db_connection();
    $query = "SELECT
            COUNT(*) AS total,
            COALESCE(SUM(status IN ('completed', 'present')), 0) AS completed_count,
            COALESCE(SUM(status IN ('pending', 'absent', 'draft')), 0) AS pending_count,
            COALESCE(SUM(status IN ('waived', 'excused')), 0) AS waived_count,
            COALESCE(AVG(CASE WHEN status IN ('completed', 'present') THEN score END), 0) AS average_score,
            MAX(assessed_on) AS latest_date
         FROM center_assessment_scores
         WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id";
    $params = [
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ];

    if ($assessmentTypeId > 0) {
        $query .= ' AND assessment_type_id = :assessment_type_id';
        $params[':assessment_type_id'] = $assessmentTypeId;
    }

    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    $row = $stmt->fetch() ?: [];

    return [
        'total' => (int) ($row['total'] ?? 0),
        'completed' => (int) ($row['completed_count'] ?? 0),
        'pending' => (int) ($row['pending_count'] ?? 0),
        'waived' => (int) ($row['waived_count'] ?? 0),
        'average_score' => (float) ($row['average_score'] ?? 0),
        'latest_date' => (string) ($row['latest_date'] ?? ''),
    ];
}

function center_assessment_summary_by_cycle(int $centerApplicationId, int $cycleId): array
{
    $summary = [
        'assessments' => [],
        'total_assessments' => 0,
        'active_assessments' => 0,
        'recorded_assessments' => 0,
        'completed_assessments' => 0,
        'pending_assessments' => 0,
        'waived_assessments' => 0,
        'missing_assessments' => 0,
        'overall_percentage' => 0.0,
        'score_total' => 0.0,
        'max_score_total' => 0.0,
        'latest_assessed_on' => '',
        'performance' => student_certificate_performance_meta(0.0),
    ];

    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'SELECT
            assessments.*,
            scores.id AS score_id,
            scores.score,
            scores.max_score AS saved_max_score,
            scores.status AS score_status,
            scores.notes AS score_notes,
            scores.assessed_on,
            (
                SELECT COUNT(*)
                FROM center_assessment_criteria criteria
                WHERE criteria.center_application_id = assessments.center_application_id
                  AND criteria.cycle_id = assessments.cycle_id
                  AND criteria.assessment_type_id = assessments.id
                  AND criteria.is_active = 1
            ) AS criteria_count
         FROM center_assessment_types assessments
         LEFT JOIN center_assessment_scores scores
            ON scores.center_application_id = assessments.center_application_id
           AND scores.cycle_id = assessments.cycle_id
           AND scores.assessment_type_id = assessments.id
         WHERE assessments.center_application_id = :center_application_id
           AND assessments.cycle_id = :cycle_id
         ORDER BY assessments.is_active DESC, assessments.updated_at DESC, assessments.id DESC'
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ]);
    $rows = $stmt->fetchAll();

    foreach ($rows as $row) {
        $summary['total_assessments']++;
        $isActive = (int) ($row['is_active'] ?? 0) === 1;
        if ($isActive) {
            $summary['active_assessments']++;
        }

        $hasSavedScore = !empty($row['score_id']);
        $status = $hasSavedScore ? center_assessment_normalize_status((string) ($row['score_status'] ?? 'pending')) : 'missing';
        $score = isset($row['score']) ? (float) $row['score'] : null;
        $maxScore = $hasSavedScore && (float) ($row['saved_max_score'] ?? 0) > 0
            ? (float) ($row['saved_max_score'] ?? 0)
            : (float) ($row['max_score'] ?? 0);
        $percentage = ($status === 'completed' && $score !== null && $maxScore > 0)
            ? round(($score / $maxScore) * 100, 2)
            : null;

        if ($hasSavedScore) {
            $summary['recorded_assessments']++;
            if ($status === 'completed' && $score !== null && $maxScore > 0) {
                $summary['completed_assessments']++;
                $summary['score_total'] += $score;
                $summary['max_score_total'] += $maxScore;
            } elseif ($status === 'pending') {
                $summary['pending_assessments']++;
            } elseif ($status === 'waived') {
                $summary['waived_assessments']++;
            }

            $assessedOn = (string) ($row['assessed_on'] ?? '');
            if ($assessedOn !== '' && ($summary['latest_assessed_on'] === '' || strtotime($assessedOn) > strtotime($summary['latest_assessed_on']))) {
                $summary['latest_assessed_on'] = $assessedOn;
            }
        } elseif ($isActive) {
            $summary['missing_assessments']++;
        }

        $summary['assessments'][] = [
            'id' => (int) ($row['id'] ?? 0),
            'title' => (string) ($row['title'] ?? ''),
            'category' => (string) ($row['category'] ?? ''),
            'scale_type' => (string) ($row['scale_type'] ?? ''),
            'weight_percentage' => (float) ($row['weight_percentage'] ?? 0),
            'max_score' => (float) ($row['max_score'] ?? 0),
            'score' => $score,
            'saved_max_score' => $maxScore,
            'status' => $status,
            'status_label' => $status === 'missing' ? 'غير مرصود' : (center_assessment_status_map()[$status]['label'] ?? 'غير محدد'),
            'notes' => (string) ($row['score_notes'] ?? ''),
            'assessed_on' => (string) ($row['assessed_on'] ?? ''),
            'criteria_count' => (int) ($row['criteria_count'] ?? 0),
            'is_active' => $isActive,
            'percentage' => $percentage,
            'has_saved_score' => $hasSavedScore,
        ];
    }

    if ($summary['max_score_total'] > 0) {
        $summary['overall_percentage'] = round(($summary['score_total'] / $summary['max_score_total']) * 100, 2);
    }
    $summary['performance'] = student_certificate_performance_meta((float) $summary['overall_percentage']);

    return $summary;
}

function school_student_options_by_cycle(int $centerApplicationId, int $cycleId): array
{
    $students = list_school_students_by_cycle($centerApplicationId, $cycleId);
    $options = [];
    foreach ($students as $student) {
        $studentId = (int) ($student['id'] ?? 0);
        if ($studentId <= 0) {
            continue;
        }

        $options[$studentId] = [
            'label' => trim((string) ($student['full_name'] ?? '')),
            'status' => (string) ($student['enrollment_status'] ?? ''),
            'grade_level' => (string) ($student['grade_level'] ?? ''),
            'guardian_phone' => (string) ($student['guardian_phone'] ?? ''),
        ];
    }

    return $options;
}

function school_student_record_by_cycle(int $centerApplicationId, int $cycleId, int $studentId): ?array
{
    if ($studentId <= 0) {
        return null;
    }

    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'SELECT *
         FROM school_students
         WHERE center_application_id = :center_application_id
           AND cycle_id = :cycle_id
           AND id = :student_id
         LIMIT 1'
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':student_id' => $studentId,
    ]);
    $row = $stmt->fetch();

    return $row ?: null;
}

function student_certificate_performance_meta(float $overallPercentage): array
{
    if ($overallPercentage >= 90) {
        return ['key' => 'excellent', 'label' => 'Excellent', 'label_ar' => 'ممتاز'];
    }

    if ($overallPercentage >= 80) {
        return ['key' => 'very_good', 'label' => 'Very Good', 'label_ar' => 'جيد جداً'];
    }

    if ($overallPercentage >= 65) {
        return ['key' => 'good', 'label' => 'Good', 'label_ar' => 'جيد'];
    }

    return ['key' => 'poor', 'label' => 'Poor', 'label_ar' => 'ضعيف'];
}

function student_completion_certificate_honor_meta(float $overallPercentage): array
{
    $performance = student_certificate_performance_meta($overallPercentage);

    return match ($performance['key']) {
        'excellent' => [
            'key' => 'highest_honors',
            'title' => 'With Highest Honors',
            'title_ar' => 'بمرتبة الشرف العليا',
            'completion_line_ar' => 'أتمّ/أتمّت الدورة بتميز استثنائي واستحقاق رفيع.',
        ],
        'very_good' => [
            'key' => 'honors',
            'title' => 'With Honors',
            'title_ar' => 'بمرتبة الشرف',
            'completion_line_ar' => 'أتمّ/أتمّت الدورة بمستوى قوي يبعث على الفخر.',
        ],
        'good' => [
            'key' => 'merit',
            'title' => 'With Merit',
            'title_ar' => 'بتقدير جيد',
            'completion_line_ar' => 'أتمّ/أتمّت الدورة بنجاح وأظهر/أظهرت التزاماً جيداً.',
        ],
        default => [
            'key' => 'completion',
            'title' => 'Successful Completion',
            'title_ar' => 'بإتمام ناجح',
            'completion_line_ar' => 'أتمّ/أتمّت الدورة واستوفى/استوفت متطلباتها الأساسية.',
        ],
    };
}

function school_student_certificate_summary(int $centerApplicationId, int $cycleId, int $studentId): array
{
    $student = school_student_record_by_cycle($centerApplicationId, $cycleId, $studentId);
    $summary = [
        'student' => $student,
        'assessments' => [],
        'has_results' => false,
        'completed_assessments' => 0,
        'active_assessments' => 0,
        'missing_assessments' => 0,
        'absent_assessments' => 0,
        'excused_assessments' => 0,
        'overall_percentage' => 0.0,
        'score_total' => 0.0,
        'max_score_total' => 0.0,
        'latest_assessed_on' => '',
        'performance' => student_certificate_performance_meta(0.0),
    ];

    if ($student === null) {
        return $summary;
    }

    $pdo = db_connection();
    $activeAssessmentsStmt = $pdo->prepare(
        'SELECT COUNT(*)
         FROM school_assessment_types
         WHERE center_application_id = :center_application_id
           AND cycle_id = :cycle_id
           AND is_active = 1'
    );
    $activeAssessmentsStmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ]);
    $summary['active_assessments'] = (int) $activeAssessmentsStmt->fetchColumn();

    $stmt = $pdo->prepare(
        'SELECT
            scores.assessment_type_id,
            scores.score,
            scores.max_score,
            scores.status,
            scores.notes,
            scores.assessed_on,
            assessments.title,
            assessments.category,
            assessments.weight_percentage,
            assessments.is_active,
            assessments.subject_id
         FROM school_assessment_scores scores
         INNER JOIN school_assessment_types assessments ON assessments.id = scores.assessment_type_id
         WHERE scores.center_application_id = :center_application_id
           AND scores.cycle_id = :cycle_id
           AND scores.student_id = :student_id
           AND assessments.is_active = 1
         ORDER BY scores.assessed_on ASC, assessments.weight_percentage DESC, assessments.id ASC'
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':student_id' => $studentId,
    ]);
    $rows = $stmt->fetchAll();

    $subjectBuckets = [];
    $completedAssessments = 0;
    $scoreTotal = 0.0;
    $maxScoreTotal = 0.0;
    $latestAssessedOn = '';

    foreach ($rows as $row) {
        $status = (string) ($row['status'] ?? '');
        if ($status === 'absent') {
            $summary['absent_assessments']++;
            continue;
        }
        if ($status === 'excused') {
            $summary['excused_assessments']++;
            continue;
        }

        $score = isset($row['score']) ? (float) $row['score'] : null;
        $maxScore = (float) ($row['max_score'] ?? 0);
        if ($status !== 'present' || $score === null || $maxScore <= 0) {
            continue;
        }

        $percentage = round(($score / $maxScore) * 100, 2);
        $weight = max(0.0, (float) ($row['weight_percentage'] ?? 0));
        $completedAssessments++;
        $scoreTotal += $score;
        $maxScoreTotal += $maxScore;

        $subjectId = (int) ($row['subject_id'] ?? 0);
        $subjectKey = $subjectId > 0 ? 'subject_' . $subjectId : 'assessment_' . (int) ($row['assessment_type_id'] ?? 0);
        if (!isset($subjectBuckets[$subjectKey])) {
            $subjectBuckets[$subjectKey] = [
                'score_total' => 0.0,
                'max_score_total' => 0.0,
            ];
        }
        $subjectBuckets[$subjectKey]['score_total'] += $score;
        $subjectBuckets[$subjectKey]['max_score_total'] += $maxScore;

        $assessedOn = (string) ($row['assessed_on'] ?? '');
        if ($assessedOn !== '' && ($latestAssessedOn === '' || strtotime($assessedOn) > strtotime($latestAssessedOn))) {
            $latestAssessedOn = $assessedOn;
        }

        $summary['assessments'][] = [
            'assessment_type_id' => (int) ($row['assessment_type_id'] ?? 0),
            'title' => (string) ($row['title'] ?? 'تقييم'),
            'category' => (string) ($row['category'] ?? ''),
            'weight_percentage' => $weight,
            'score' => round($score, 2),
            'max_score' => round($maxScore, 2),
            'percentage' => $percentage,
            'assessed_on' => $assessedOn,
            'notes' => (string) ($row['notes'] ?? ''),
        ];
    }

    $subjectPercentageSum = 0.0;
    $subjectCount = 0;
    foreach ($subjectBuckets as $bucket) {
        $subjectMaxScore = (float) ($bucket['max_score_total'] ?? 0);
        if ($subjectMaxScore <= 0) {
            continue;
        }

        $subjectPercentage = ((float) ($bucket['score_total'] ?? 0) / $subjectMaxScore) * 100;
        $subjectPercentageSum += max(0.0, min(100.0, $subjectPercentage));
        $subjectCount++;
    }

    $overallPercentage = $subjectCount > 0
        ? round($subjectPercentageSum / $subjectCount, 2)
        : 0.0;

    $summary['completed_assessments'] = $completedAssessments;
    $summary['missing_assessments'] = max(0, $summary['active_assessments'] - $summary['completed_assessments'] - $summary['absent_assessments'] - $summary['excused_assessments']);
    $summary['has_results'] = $completedAssessments > 0;
    $summary['overall_percentage'] = $overallPercentage;
    $summary['score_total'] = round($scoreTotal, 2);
    $summary['max_score_total'] = round($maxScoreTotal, 2);
    $summary['latest_assessed_on'] = $latestAssessedOn;
    $summary['performance'] = student_certificate_performance_meta($overallPercentage);

    return $summary;
}

function validate_attendance_input_for_cycle(int $centerApplicationId, int $cycleId, array $input): array
{
    $data = attendance_defaults();
    $data['student_id'] = (string) ((int) ($input['student_id'] ?? 0));
    $data['attendance_status'] = clean_text((string) ($input['attendance_status'] ?? ''), 30);
    $data['absence_reason'] = clean_text((string) ($input['absence_reason'] ?? ''), 190);
    $data['notes'] = clean_text((string) ($input['notes'] ?? ''), 1000);
    $data['attendance_date'] = clean_text((string) ($input['attendance_date'] ?? ''), 20);

    $errors = [];
    $studentId = (int) $data['student_id'];
    $studentOptions = school_student_options_by_cycle($centerApplicationId, $cycleId);

    if ($studentId <= 0 || !array_key_exists($studentId, $studentOptions)) {
        $errors['student_id'] = 'يرجى اختيار طالب صحيح من نفس الدورة الموسمية.';
    }

    $statusMap = attendance_status_map();
    if (!array_key_exists($data['attendance_status'], $statusMap)) {
        $errors['attendance_status'] = 'يرجى اختيار حالة غياب صحيحة.';
    }

    if ($data['attendance_date'] === '' || strtotime($data['attendance_date']) === false) {
        $errors['attendance_date'] = 'يرجى إدخال تاريخ صحيح للسجل اليومي.';
    }

    if ($data['attendance_status'] !== 'late' && $data['absence_reason'] === '') {
        $errors['absence_reason'] = 'يرجى إدخال سبب الغياب أو العذر.';
    }

    return [$data, $errors];
}

function create_attendance_record_in_cycle(int $centerApplicationId, int $cycleId, array $data): int
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'INSERT INTO school_attendance_records (
            center_application_id, cycle_id, student_id, attendance_date, attendance_status,
            absence_reason, notes, created_at, updated_at
        ) VALUES (
            :center_application_id, :cycle_id, :student_id, :attendance_date, :attendance_status,
            :absence_reason, :notes, NOW(), NOW()
        )'
    );

    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':student_id' => (int) $data['student_id'],
        ':attendance_date' => $data['attendance_date'],
        ':attendance_status' => $data['attendance_status'],
        ':absence_reason' => $data['absence_reason'] !== '' ? $data['absence_reason'] : null,
        ':notes' => $data['notes'] !== '' ? $data['notes'] : null,
    ]);

    return (int) $pdo->lastInsertId();
}

function list_school_attendance_records_by_cycle(int $centerApplicationId, int $cycleId, string $search = '', int $limit = 0, int $offset = 0): array
{
    $pdo = db_connection();
    $query = 'SELECT ar.*, s.student_code, s.full_name, s.grade_level, s.guardian_phone
         FROM school_attendance_records ar
         INNER JOIN school_students s ON s.id = ar.student_id
         WHERE ar.center_application_id = :center_application_id AND ar.cycle_id = :cycle_id';
    $params = [
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ];
    
    if ($search !== '') {
        $query .= ' AND (s.full_name LIKE :search1 OR s.student_code LIKE :search2)';
        $params[':search1'] = "%$search%";
        $params[':search2'] = "%$search%";
    }
    
    $query .= ' ORDER BY ar.attendance_date DESC, ar.created_at DESC, ar.id DESC';
    
    if ($limit > 0) {
        $query .= ' LIMIT ' . (int)$limit . ' OFFSET ' . (int)$offset;
    }
    
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function count_school_attendance_records_by_cycle(int $centerApplicationId, int $cycleId, string $search = ''): int
{
    $pdo = db_connection();
    $query = 'SELECT COUNT(*)
         FROM school_attendance_records ar
         INNER JOIN school_students s ON s.id = ar.student_id
         WHERE ar.center_application_id = :center_application_id AND ar.cycle_id = :cycle_id';
    $params = [
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ];
    
    if ($search !== '') {
        $query .= ' AND (s.full_name LIKE :search1 OR s.student_code LIKE :search2)';
        $params[':search1'] = "%$search%";
        $params[':search2'] = "%$search%";
    }
    
    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    return (int)$stmt->fetchColumn();
}

function school_attendance_metrics_by_cycle(int $centerApplicationId, int $cycleId): array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        "SELECT
            COUNT(*) AS total,
            COALESCE(SUM(attendance_status = 'absent'), 0) AS absent_count,
            COALESCE(SUM(attendance_status = 'excused'), 0) AS excused_count,
            COALESCE(SUM(attendance_status = 'late'), 0) AS late_count,
            COUNT(DISTINCT student_id) AS affected_students,
            MAX(attendance_date) AS latest_date,
            COALESCE(SUM(attendance_date = CURDATE()), 0) AS today_count
         FROM school_attendance_records
         WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id"
    );
    $stmt->execute([
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
    ]);
    $row = $stmt->fetch() ?: [];

    return [
        'total' => (int) ($row['total'] ?? 0),
        'absent' => (int) ($row['absent_count'] ?? 0),
        'excused' => (int) ($row['excused_count'] ?? 0),
        'late' => (int) ($row['late_count'] ?? 0),
        'affected_students' => (int) ($row['affected_students'] ?? 0),
        'latest_date' => (string) ($row['latest_date'] ?? ''),
        'today_count' => (int) ($row['today_count'] ?? 0),
    ];
}

function update_student_in_cycle(int $centerApplicationId, int $cycleId, int $studentId, array $data): void
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'UPDATE school_students SET 
            student_code = :student_code, full_name = :full_name, gender = :gender, grade_level = :grade_level,
            guardian_name = :guardian_name, guardian_phone = :guardian_phone, birth_date = :birth_date, 
            enrollment_status = :enrollment_status, notes = :notes, updated_at = NOW()
        WHERE center_application_id = :center_application_id AND cycle_id = :cycle_id AND id = :id'
    );
    $stmt->bindValue(':center_application_id', $centerApplicationId, PDO::PARAM_INT);
    $stmt->bindValue(':cycle_id', $cycleId, PDO::PARAM_INT);
    $stmt->bindValue(':id', $studentId, PDO::PARAM_INT);
    $stmt->bindValue(':student_code', $data['student_code'], PDO::PARAM_STR);
    $stmt->bindValue(':full_name', $data['full_name'], PDO::PARAM_STR);
    $stmt->bindValue(':gender', $data['gender'], PDO::PARAM_STR);
    $stmt->bindValue(':grade_level', $data['grade_level'], PDO::PARAM_STR);
    $stmt->bindValue(':guardian_name', $data['guardian_name'], PDO::PARAM_STR);
    $stmt->bindValue(':guardian_phone', $data['guardian_phone'], PDO::PARAM_STR);
    $stmt->bindValue(':birth_date', $data['birth_date'] !== '' ? $data['birth_date'] : null, $data['birth_date'] !== '' ? PDO::PARAM_STR : PDO::PARAM_NULL);
    $stmt->bindValue(':enrollment_status', $data['enrollment_status'], PDO::PARAM_STR);
    $stmt->bindValue(':notes', $data['notes'] !== '' ? $data['notes'] : null, $data['notes'] !== '' ? PDO::PARAM_STR : PDO::PARAM_NULL);
    $stmt->execute();
}

function update_assessment_type_in_cycle(int $centerApplicationId, int $cycleId, int $assessmentId, array $data): bool
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'UPDATE school_assessment_types SET 
            subject_id = :subject_id,
            title = :title,
            category = :category,
            scale_type = :scale_type,
            max_score = :max_score,
            weight_percentage = :weight_percentage,
            is_active = :is_active,
            notes = :notes
        WHERE id = :id AND center_application_id = :center_application_id AND cycle_id = :cycle_id'
    );
    return $stmt->execute([
        ':id' => $assessmentId,
        ':center_application_id' => $centerApplicationId,
        ':cycle_id' => $cycleId,
        ':subject_id' => !empty($data['subject_id']) ? (int) $data['subject_id'] : null,
        ':title' => $data['title'],
        ':category' => $data['category'],
        ':scale_type' => $data['scale_type'],
        ':max_score' => (float) $data['max_score'],
        ':weight_percentage' => (float) $data['weight_percentage'],
        ':is_active' => (int) $data['is_active'],
        ':notes' => $data['notes'] !== '' ? $data['notes'] : null,
    ]);
}


function get_global_center_assessment_type(int $assessmentId): ?array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare('SELECT * FROM global_center_assessment_types WHERE id = :id LIMIT 1');
    $stmt->execute([':id' => $assessmentId]);
    $row = $stmt->fetch();
    return $row ?: null;
}

function create_global_center_assessment_type(array $data): int
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'INSERT INTO global_center_assessment_types (
            title, category, scale_type, max_score, weight_percentage,
            is_active, notes, created_at, updated_at
        ) VALUES (
            :title, :category, :scale_type, :max_score, :weight_percentage,
            :is_active, :notes, NOW(), NOW()
        )'
    );

    $stmt->execute([
        ':title' => $data['title'],
        ':category' => $data['category'],
        ':scale_type' => $data['scale_type'],
        ':max_score' => (float) $data['max_score'],
        ':weight_percentage' => (float) $data['weight_percentage'],
        ':is_active' => (int) $data['is_active'],
        ':notes' => $data['notes'] !== '' ? $data['notes'] : null,
    ]);

    return (int) $pdo->lastInsertId();
}

function update_global_center_assessment_type(int $assessmentId, array $data): bool
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        'UPDATE global_center_assessment_types SET
            title = :title,
            category = :category,
            scale_type = :scale_type,
            max_score = :max_score,
            weight_percentage = :weight_percentage,
            is_active = :is_active,
            notes = :notes,
            updated_at = NOW()
         WHERE id = :id'
    );

    return $stmt->execute([
        ':id' => $assessmentId,
        ':title' => $data['title'],
        ':category' => $data['category'],
        ':scale_type' => $data['scale_type'],
        ':max_score' => (float) $data['max_score'],
        ':weight_percentage' => (float) $data['weight_percentage'],
        ':is_active' => (int) $data['is_active'],
        ':notes' => $data['notes'] !== '' ? $data['notes'] : null,
    ]);
}

function list_global_center_assessments(array $filters = [], int $limit = 0, int $offset = 0): array
{
    $pdo = db_connection();
    $query = 'SELECT gcat.*,
            (
                SELECT COUNT(*)
                FROM global_center_assessment_criteria criteria
                WHERE criteria.assessment_type_id = gcat.id
                  AND criteria.is_active = 1
            ) AS criteria_count,
            (
                SELECT COALESCE(SUM(criteria.max_score), 0)
                FROM global_center_assessment_criteria criteria
                WHERE criteria.assessment_type_id = gcat.id
                  AND criteria.is_active = 1
            ) AS criteria_total_max_score
        FROM global_center_assessment_types gcat
        WHERE 1 = 1';
    $params = [];

    $search = trim((string) ($filters['search'] ?? ''));
    if ($search !== '') {
        $query .= ' AND (gcat.title LIKE :search1 OR gcat.category LIKE :search2 OR gcat.notes LIKE :search3)';
        $params[':search1'] = "%{$search}%";
        $params[':search2'] = "%{$search}%";
        $params[':search3'] = "%{$search}%";
    }

    $category = trim((string) ($filters['category'] ?? ''));
    if ($category !== '') {
        $query .= ' AND gcat.category = :category';
        $params[':category'] = $category;
    }

    $query .= ' ORDER BY gcat.is_active DESC, gcat.updated_at DESC, gcat.id DESC';

    if ($limit > 0) {
        $query .= ' LIMIT ' . (int) $limit . ' OFFSET ' . (int) $offset;
    }

    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

function count_global_center_assessments(array $filters = []): int
{
    $pdo = db_connection();
    $query = 'SELECT COUNT(*) FROM global_center_assessment_types WHERE 1 = 1';
    $params = [];

    $search = trim((string) ($filters['search'] ?? ''));
    if ($search !== '') {
        $query .= ' AND (title LIKE :search1 OR category LIKE :search2 OR notes LIKE :search3)';
        $params[':search1'] = "%{$search}%";
        $params[':search2'] = "%{$search}%";
        $params[':search3'] = "%{$search}%";
    }

    $category = trim((string) ($filters['category'] ?? ''));
    if ($category !== '') {
        $query .= ' AND category = :category';
        $params[':category'] = $category;
    }

    $stmt = $pdo->prepare($query);
    $stmt->execute($params);
    return (int) $stmt->fetchColumn();
}

function global_center_assessment_metrics(): array
{
    $pdo = db_connection();
    $stmt = $pdo->query(
        "SELECT
            COUNT(*) AS total_count,
            COALESCE(SUM(is_active = 1), 0) AS active_count,
            COALESCE(SUM(is_active = 0), 0) AS inactive_count,
            COALESCE(SUM(CASE WHEN is_active = 1 THEN weight_percentage ELSE 0 END), 0) AS active_weight,
            COALESCE(AVG(max_score), 0) AS average_max_score,
            COALESCE(SUM(scale_type = 'percentage'), 0) AS percentage_count,
            COALESCE(SUM(scale_type = 'points'), 0) AS points_count,
            COALESCE(SUM(scale_type LIKE 'rubric_%'), 0) AS rubric_count
         FROM global_center_assessment_types"
    );
    $row = $stmt ? ($stmt->fetch() ?: []) : [];

    return [
        'total' => (int) ($row['total_count'] ?? 0),
        'active' => (int) ($row['active_count'] ?? 0),
        'inactive' => (int) ($row['inactive_count'] ?? 0),
        'active_weight' => (float) ($row['active_weight'] ?? 0),
        'average_max_score' => (float) ($row['average_max_score'] ?? 0),
        'percentage' => (int) ($row['percentage_count'] ?? 0),
        'points' => (int) ($row['points_count'] ?? 0),
        'rubric' => (int) ($row['rubric_count'] ?? 0),
    ];
}

function global_center_assessment_type_options(bool $onlyActive = false): array
{
    $rows = list_global_center_assessments();
    $options = [];

    foreach ($rows as $assessment) {
        $assessmentId = (int) ($assessment['id'] ?? 0);
        if ($assessmentId <= 0) {
            continue;
        }

        $isActive = (int) ($assessment['is_active'] ?? 0) === 1;
        if ($onlyActive && !$isActive) {
            continue;
        }

        $title = trim((string) ($assessment['title'] ?? ''));
        $label = $title !== '' ? $title : ('قالب #' . $assessmentId);
        $category = trim((string) ($assessment['category'] ?? ''));
        if ($category !== '') {
            $label .= ' — ' . $category;
        }

        $criteriaCount = (int) ($assessment['criteria_count'] ?? 0);
        $criteriaTotal = (float) ($assessment['criteria_total_max_score'] ?? 0);

        $options[$assessmentId] = [
            'id' => $assessmentId,
            'label' => $label,
            'title' => $title,
            'category' => $category,
            'max_score' => (float) ($assessment['max_score'] ?? 0),
            'weight_percentage' => (float) ($assessment['weight_percentage'] ?? 0),
            'criteria_count' => $criteriaCount,
            'criteria_total_max_score' => $criteriaTotal,
            'has_criteria' => $criteriaCount > 0,
            'is_active' => $isActive ? 1 : 0,
        ];
    }

    return $options;
}

function list_global_center_assessment_criteria_by_assessment(int $assessmentTypeId, bool $onlyActive = false): array
{
    $pdo = db_connection();
    $query = 'SELECT *
        FROM global_center_assessment_criteria
        WHERE assessment_type_id = :assessment_type_id';
    if ($onlyActive) {
        $query .= ' AND is_active = 1';
    }
    $query .= ' ORDER BY sort_order ASC, id ASC';

    $stmt = $pdo->prepare($query);
    $stmt->execute([':assessment_type_id' => $assessmentTypeId]);
    return $stmt->fetchAll();
}

function global_center_assessment_criteria_metrics(int $assessmentTypeId): array
{
    $pdo = db_connection();
    $stmt = $pdo->prepare(
        "SELECT
            COUNT(*) AS total_count,
            COALESCE(SUM(is_active = 1), 0) AS active_count,
            COALESCE(SUM(CASE WHEN is_active = 1 THEN max_score ELSE 0 END), 0) AS active_max_score
         FROM global_center_assessment_criteria
         WHERE assessment_type_id = :assessment_type_id"
    );
    $stmt->execute([':assessment_type_id' => $assessmentTypeId]);
    $row = $stmt->fetch() ?: [];

    return [
        'total' => (int) ($row['total_count'] ?? 0),
        'active' => (int) ($row['active_count'] ?? 0),
        'active_max_score' => (float) ($row['active_max_score'] ?? 0),
    ];
}

function validate_global_center_assessment_criteria_input(int $assessmentTypeId, array $input): array
{
    $data = ['criteria' => []];
    $errors = [];
    $assessment = get_global_center_assessment_type($assessmentTypeId);
    if (!$assessment) {
        return [$data, ['form' => 'يرجى اختيار قالب تقييم صحيح.']];
    }

    $postedRows = $input['criteria'] ?? [];
    if (!is_array($postedRows)) {
        $postedRows = [];
    }

    $position = 1;
    $activeCount = 0;
    foreach ($postedRows as $rowKey => $row) {
        if (!is_array($row)) {
            continue;
        }

        $criterionId = (int) ($row['id'] ?? 0);
        $title = clean_text((string) ($row['title'] ?? ''), 150);
        $maxScoreRaw = str_replace(',', '.', clean_text((string) ($row['max_score'] ?? ''), 30));
        $notes = clean_text((string) ($row['notes'] ?? ''), 500);
        $isActive = ((string) ($row['is_active'] ?? '1')) === '1' ? 1 : 0;

        if ($criterionId <= 0 && $title === '' && $maxScoreRaw === '' && $notes === '') {
            continue;
        }

        $rowErrors = [];
        if ($title === '') {
            $rowErrors[] = 'اسم البند مطلوب.';
        }

        $maxScore = null;
        if ($maxScoreRaw === '' || !is_numeric($maxScoreRaw)) {
            $rowErrors[] = 'أدخل درجة رقمية للبند.';
        } else {
            $maxScore = round((float) $maxScoreRaw, 2);
            if ($maxScore <= 0 || $maxScore > 1000) {
                $rowErrors[] = 'درجة البند يجب أن تكون بين 0.01 و1000.';
            }
        }

        if ($rowErrors !== []) {
            $errors['criteria_' . $rowKey] = implode(' ', $rowErrors);
        }

        $data['criteria'][] = [
            'id' => $criterionId,
            'title' => $title,
            'max_score' => $maxScore !== null ? number_format($maxScore, 2, '.', '') : '',
            'notes' => $notes,
            'is_active' => (string) $isActive,
            'sort_order' => $position,
        ];

        if ($isActive === 1) {
            $activeCount++;
        }
        $position++;
    }

    if ($data['criteria'] === []) {
        $errors['form'] = 'أضف بند تقييم واحداً على الأقل قبل الحفظ.';
    } elseif ($activeCount === 0) {
        $errors['form'] = 'فعّل بنداً واحداً على الأقل ليكون متاحاً للمقيمين.';
    }

    return [$data, $errors];
}

function sync_global_center_assessment_total_score_from_criteria(int $assessmentTypeId): void
{
    $pdo = db_connection();
    $criteria = list_global_center_assessment_criteria_by_assessment($assessmentTypeId, true);
    if ($criteria === []) {
        return;
    }

    $totalMaxScore = 0.0;
    foreach ($criteria as $criterion) {
        $totalMaxScore += (float) ($criterion['max_score'] ?? 0);
    }
    $totalMaxScore = round($totalMaxScore, 2);

    $stmt = $pdo->prepare(
        'UPDATE global_center_assessment_types
         SET max_score = :max_score, updated_at = NOW()
         WHERE id = :id'
    );
    $stmt->execute([
        ':max_score' => $totalMaxScore,
        ':id' => $assessmentTypeId,
    ]);
}

function save_global_center_assessment_criteria(int $assessmentTypeId, array $data): int
{
    $pdo = db_connection();
    $existingStmt = $pdo->prepare(
        'SELECT id FROM global_center_assessment_criteria WHERE assessment_type_id = :assessment_type_id'
    );
    $existingStmt->execute([':assessment_type_id' => $assessmentTypeId]);
    $existingIds = array_map('intval', $existingStmt->fetchAll(PDO::FETCH_COLUMN));
    $existingMap = array_fill_keys($existingIds, true);

    $insertStmt = $pdo->prepare(
        'INSERT INTO global_center_assessment_criteria (
            assessment_type_id, title, max_score, sort_order, is_active, notes, created_at, updated_at
        ) VALUES (
            :assessment_type_id, :title, :max_score, :sort_order, :is_active, :notes, NOW(), NOW()
        )'
    );
    $updateStmt = $pdo->prepare(
        'UPDATE global_center_assessment_criteria
         SET title = :title,
             max_score = :max_score,
             sort_order = :sort_order,
             is_active = :is_active,
             notes = :notes,
             updated_at = NOW()
         WHERE id = :id AND assessment_type_id = :assessment_type_id'
    );

    $saved = 0;
    foreach ($data['criteria'] as $criterion) {
        $params = [
            ':assessment_type_id' => $assessmentTypeId,
            ':title' => (string) ($criterion['title'] ?? ''),
            ':max_score' => (float) ($criterion['max_score'] ?? 0),
            ':sort_order' => (int) ($criterion['sort_order'] ?? 0),
            ':is_active' => ((string) ($criterion['is_active'] ?? '1')) === '1' ? 1 : 0,
            ':notes' => !empty($criterion['notes']) ? (string) $criterion['notes'] : null,
        ];

        $criterionId = (int) ($criterion['id'] ?? 0);
        if ($criterionId > 0 && isset($existingMap[$criterionId])) {
            $updateStmt->execute($params + [':id' => $criterionId]);
            unset($existingMap[$criterionId]);
        } else {
            $insertStmt->execute($params);
        }
        $saved++;
    }

    if ($existingMap !== []) {
        $deleteStmt = $pdo->prepare(
            'DELETE FROM global_center_assessment_criteria
             WHERE assessment_type_id = :assessment_type_id AND id = :id'
        );
        foreach (array_keys($existingMap) as $obsoleteId) {
            $deleteStmt->execute([
                ':assessment_type_id' => $assessmentTypeId,
                ':id' => (int) $obsoleteId,
            ]);
        }
    }

    sync_global_center_assessment_total_score_from_criteria($assessmentTypeId);
    return $saved;
}


function delete_global_center_assessment_type(int $assessmentId): bool
{
    $pdo = db();
    try {
        $pdo->beginTransaction();
        
        $stmt = $pdo->prepare('DELETE FROM global_center_assessment_criteria WHERE assessment_type_id = :assessment_type_id');
        $stmt->bindValue(':assessment_type_id', $assessmentId, PDO::PARAM_INT);
        $stmt->execute();
        
        $stmt = $pdo->prepare('DELETE FROM global_center_assessment_types WHERE id = :id');
        $stmt->bindValue(':id', $assessmentId, PDO::PARAM_INT);
        $stmt->execute();
        
        $pdo->commit();
        return true;
    } catch (PDOException $e) {
        $pdo->rollBack();
        error_log("Failed to delete global_center_assessment_type ID $assessmentId: " . $e->getMessage());
        return false;
    }
}


function import_global_center_assessment_to_center(int $globalAssessmentId, int $centerApplicationId, int $cycleId): int
{
    $pdo = db();
    $globalAssessment = get_global_center_assessment_type($globalAssessmentId);
    if (!$globalAssessment) {
        throw new InvalidArgumentException("Global assessment template not found.");
    }
    
    $globalCriteria = list_global_center_assessment_criteria_by_assessment($globalAssessmentId, true);
    
    try {
        $pdo->beginTransaction();
        
        $stmt = $pdo->prepare('INSERT INTO center_assessment_types 
            (center_application_id, cycle_id, global_template_id, title, category, scale_type, max_score, weight_percentage, is_active, notes)
            VALUES (:center_application_id, :cycle_id, :global_template_id, :title, :category, :scale_type, :max_score, :weight_percentage, :is_active, :notes)');
            
        $stmt->bindValue(':center_application_id', $centerApplicationId, PDO::PARAM_INT);
        $stmt->bindValue(':cycle_id', $cycleId, PDO::PARAM_INT);
        $stmt->bindValue(':global_template_id', $globalAssessmentId, PDO::PARAM_INT);
        $stmt->bindValue(':title', $globalAssessment['title'], PDO::PARAM_STR);
        $stmt->bindValue(':category', $globalAssessment['category'], PDO::PARAM_STR);
        $stmt->bindValue(':scale_type', $globalAssessment['scale_type'], PDO::PARAM_STR);
        $stmt->bindValue(':max_score', $globalAssessment['max_score'], PDO::PARAM_STR);
        $stmt->bindValue(':weight_percentage', $globalAssessment['weight_percentage'], PDO::PARAM_STR);
        $stmt->bindValue(':is_active', $globalAssessment['is_active'], PDO::PARAM_INT);
        $stmt->bindValue(':notes', $globalAssessment['notes'] ?? null, PDO::PARAM_STR);
        
        $stmt->execute();
        $newAssessmentId = (int) $pdo->lastInsertId();
        
        if ($globalCriteria !== []) {
            $stmtCrit = $pdo->prepare('INSERT INTO center_assessment_criteria 
                (center_application_id, cycle_id, assessment_type_id, title, notes, max_score, sort_order, is_active)
                VALUES (:center_application_id, :cycle_id, :assessment_type_id, :title, :notes, :max_score, :sort_order, :is_active)');
                
            foreach ($globalCriteria as $criteria) {
                $stmtCrit->bindValue(':center_application_id', $centerApplicationId, PDO::PARAM_INT);
                $stmtCrit->bindValue(':cycle_id', $cycleId, PDO::PARAM_INT);
                $stmtCrit->bindValue(':assessment_type_id', $newAssessmentId, PDO::PARAM_INT);
                $stmtCrit->bindValue(':title', $criteria['title'], PDO::PARAM_STR);
                $stmtCrit->bindValue(':notes', $criteria['notes'] ?? null, PDO::PARAM_STR);
                $stmtCrit->bindValue(':max_score', $criteria['max_score'], PDO::PARAM_STR);
                $stmtCrit->bindValue(':sort_order', $criteria['sort_order'] ?? 0, PDO::PARAM_INT);
                $stmtCrit->bindValue(':is_active', $criteria['is_active'], PDO::PARAM_INT);
                $stmtCrit->execute();
            }
        }
        
        $pdo->commit();
        return $newAssessmentId;
    } catch (PDOException $e) {
        $pdo->rollBack();
        error_log("Failed to import global center assessment: " . $e->getMessage());
        throw $e;
    }
}