alrahma_sunday_school/app/Database/Migrations/2026-08-20-000100_CreateStudentYearStatus.php

<?php

namespace App\Database\Migrations;

use CodeIgniter\Database\Migration;

class CreateStudentYearStatus extends Migration
{
    public function up(): void
    {
        if ($this->db->tableExists('student_year_status')) {
            $this->backfillExistingStatus();
            return;
        }

        $this->forge->addField([
            'id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
                'auto_increment' => true,
            ],
            'student_id' => [
                'type' => 'INT',
                'constraint' => 11,
                'unsigned' => true,
                'null' => false,
            ],
            'school_year' => [
                'type' => 'VARCHAR',
                'constraint' => 9,
                'null' => false,
            ],
            'is_new' => [
                'type' => 'TINYINT',
                'constraint' => 1,
                'null' => false,
                'default' => 1,
                'comment' => '1 = new student for this school year, 0 = returning',
            ],
            'created_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
            'updated_at' => [
                'type' => 'DATETIME',
                'null' => true,
            ],
        ]);

        $this->forge->addKey('id', true);
        $this->forge->addUniqueKey(['student_id', 'school_year'], 'uq_student_year_status');
        $this->forge->addKey('school_year');
        $this->forge->addKey('student_id');
        $this->forge->createTable('student_year_status', true);

        $this->backfillExistingStatus();
    }

    public function down(): void
    {
        $this->forge->dropTable('student_year_status', true);
    }

    private function backfillExistingStatus(): void
    {
        if (! $this->db->tableExists('student_year_status') || ! $this->db->tableExists('students')) {
            return;
        }

        $now = date('Y-m-d H:i:s');
        $hasStudentSchoolYear = $this->db->fieldExists('school_year', 'students');
        $select = ['id', 'is_new'];
        if ($hasStudentSchoolYear) {
            $select[] = 'school_year';
        }

        $students = $this->db->table('students')
            ->select($select)
            ->get()
            ->getResultArray();

        if ($hasStudentSchoolYear) {
            foreach ($students as $student) {
                $studentId = (int) ($student['id'] ?? 0);
                if ($studentId <= 0) {
                    continue;
                }

                $schoolYear = trim((string) ($student['school_year'] ?? ''));
                if (preg_match('/^\d{4}-\d{4}$/', $schoolYear)) {
                    $this->upsertStatus(
                        $studentId,
                        $schoolYear,
                        (int) ($student['is_new'] ?? 1) === 1 ? 1 : 0,
                        $now
                    );
                }
            }
        }

        $activeYear = $this->configuredSchoolYear();
        if ($activeYear === null) {
            return;
        }

        $activeStart = $this->schoolYearStartYear($activeYear);
        $returningIds = $activeStart === null ? [] : $this->priorYearStudentIds($activeStart);

        foreach ($students as $student) {
            $studentId = (int) ($student['id'] ?? 0);
            if ($studentId <= 0) {
                continue;
            }

            $isNew = isset($returningIds[$studentId]) ? 0 : ((int) ($student['is_new'] ?? 1) === 1 ? 1 : 0);
            $this->upsertStatus($studentId, $activeYear, $isNew, $now);
        }
    }

    private function upsertStatus(int $studentId, string $schoolYear, int $isNew, string $now): void
    {
        $existing = $this->db->table('student_year_status')
            ->where('student_id', $studentId)
            ->where('school_year', $schoolYear)
            ->get(1)
            ->getRowArray();

        if ($existing !== null) {
            return;
        }

        $this->db->table('student_year_status')->insert([
            'student_id' => $studentId,
            'school_year' => $schoolYear,
            'is_new' => $isNew,
            'created_at' => $now,
            'updated_at' => $now,
        ]);
    }

    /**
     * @return array<int, true>
     */
    private function priorYearStudentIds(int $selectedStartYear): array
    {
        $studentIds = [];
        foreach (['enrollments', 'student_class'] as $table) {
            if (! $this->db->tableExists($table) || ! $this->db->fieldExists('school_year', $table)) {
                continue;
            }

            $rows = $this->db->table($table)
                ->select('student_id, school_year')
                ->where('student_id IS NOT NULL', null, false)
                ->where('school_year IS NOT NULL', null, false)
                ->get()
                ->getResultArray();

            foreach ($rows as $row) {
                $rowStartYear = $this->schoolYearStartYear((string) ($row['school_year'] ?? ''));
                $studentId = (int) ($row['student_id'] ?? 0);
                if ($studentId > 0 && $rowStartYear !== null && $rowStartYear < $selectedStartYear) {
                    $studentIds[$studentId] = true;
                }
            }
        }

        return $studentIds;
    }

    private function schoolYearStartYear(string $schoolYear): ?int
    {
        if (! preg_match('/^(\d{4})-\d{4}$/', trim($schoolYear), $matches)) {
            return null;
        }

        return (int) $matches[1];
    }

    private function configuredSchoolYear(): ?string
    {
        if ($this->db->tableExists('school_years')) {
            $row = $this->db->table('school_years')
                ->select('name')
                ->where('status', 'active')
                ->orderBy('id', 'DESC')
                ->get(1)
                ->getRowArray();

            $name = trim((string) ($row['name'] ?? ''));
            if (preg_match('/^\d{4}-\d{4}$/', $name)) {
                return $name;
            }
        }

        if (! $this->db->tableExists('configuration')) {
            return null;
        }

        $row = $this->db->table('configuration')
            ->select('config_value')
            ->where('config_key', 'school_year')
            ->orderBy('id', 'DESC')
            ->get(1)
            ->getRowArray();

        $name = trim((string) ($row['config_value'] ?? ''));

        return preg_match('/^\d{4}-\d{4}$/', $name) ? $name : null;
    }
}