alrahma_sunday_school/app/Commands/EnrollmentPostLaunchMonitor.php

<?php

namespace App\Commands;

use CodeIgniter\CLI\BaseCommand;
use CodeIgniter\CLI\CLI;
use CodeIgniter\Database\BaseConnection;

class EnrollmentPostLaunchMonitor extends BaseCommand
{
    protected $group = 'Registration';
    protected $name = 'registration:monitor';
    protected $description = 'Monitor post-launch enrollment progress, blocks, flags, and email delivery.';
    protected $usage = 'php spark registration:monitor [--school-year=2026-2027] [--days=7] [--json]';
    protected $options = [
        '--school-year' => 'Target school year name. Defaults to the active/current configured year.',
        '--days' => 'Recent activity window in days. Defaults to 7.',
        '--json' => 'Print machine-readable JSON.',
    ];

    private BaseConnection $db;

    public function run(array $params)
    {
        $this->db = \Config\Database::connect();
        $schoolYear = trim((string) (CLI::getOption('school-year') ?? ''));
        if ($schoolYear === '') {
            $schoolYear = $this->currentSchoolYear();
        }

        $days = max(1, (int) (CLI::getOption('days') ?? 7));
        $report = $this->buildReport($schoolYear, $days);

        if (CLI::getOption('json') !== null) {
            CLI::write(json_encode($report, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES));
            return;
        }

        $this->printReport($report);
    }

    private function buildReport(string $schoolYear, int $days): array
    {
        $previousYear = $this->previousSchoolYearName($schoolYear);
        $expected = $this->expectedReturningStudentCount($previousYear);
        $enrollments = $this->enrollmentSummary($schoolYear);
        $flags = $this->flagSummary($schoolYear, $days);
        $emails = $this->emailSummary($schoolYear);
        $audits = $this->auditSummary($schoolYear, $days);

        $submitted = (int) ($enrollments['submitted'] ?? 0);
        $progressPercent = $expected > 0 ? round(($submitted / $expected) * 100, 1) : null;
        $needsAttention = (int) ($flags['open_total'] ?? 0)
            + (int) ($flags['stale_total'] ?? 0)
            + (int) ($emails['failed'] ?? 0)
            + (int) ($enrollments['blocked_total'] ?? 0);

        return [
            'school_year' => $schoolYear,
            'source_school_year' => $previousYear,
            'generated_at' => date('Y-m-d H:i:s'),
            'activity_window_days' => $days,
            'expected_returning_students' => $expected,
            'submitted_registrations' => $submitted,
            'progress_percent' => $progressPercent,
            'needs_attention_count' => $needsAttention,
            'enrollments' => $enrollments,
            'flags' => $flags,
            'emails' => $emails,
            'audits' => $audits,
        ];
    }

    private function expectedReturningStudentCount(?string $previousYear): int
    {
        if ($previousYear === null || ! $this->db->tableExists('student_class')) {
            return 0;
        }

        return (int) ($this->db->table('student_class')
            ->select('COUNT(DISTINCT student_id) AS total', false)
            ->where('school_year', $previousYear)
            ->get()
            ->getRowArray()['total'] ?? 0);
    }

    private function enrollmentSummary(string $schoolYear): array
    {
        if (! $this->db->tableExists('enrollments')) {
            return [
                'total' => 0,
                'submitted' => 0,
                'confirmed' => 0,
                'blocked_total' => 0,
                'by_status' => [],
                'by_placement_status' => [],
            ];
        }

        $total = $this->countRows('enrollments', ['school_year' => $schoolYear]);
        $submitted = $this->db->fieldExists('registration_submitted_at', 'enrollments')
            ? $this->countRows('enrollments', ['school_year' => $schoolYear], 'registration_submitted_at IS NOT NULL')
            : $total;
        $confirmed = $this->db->fieldExists('registration_confirmed_at', 'enrollments')
            ? $this->countRows('enrollments', ['school_year' => $schoolYear], 'registration_confirmed_at IS NOT NULL')
            : $this->countRows('enrollments', ['school_year' => $schoolYear, 'enrollment_status' => 'enrolled']);

        $blocked = 0;
        if ($this->db->fieldExists('parent_enrollment_allowed', 'enrollments')) {
            $blocked += $this->countRows('enrollments', ['school_year' => $schoolYear, 'parent_enrollment_allowed' => 0]);
        }
        if ($this->db->fieldExists('exception_required', 'enrollments')) {
            $blocked += $this->countRows('enrollments', ['school_year' => $schoolYear, 'exception_required' => 1]);
        }

        return [
            'total' => $total,
            'submitted' => $submitted,
            'confirmed' => $confirmed,
            'blocked_total' => $blocked,
            'by_status' => $this->groupCount('enrollments', 'enrollment_status', ['school_year' => $schoolYear]),
            'by_placement_status' => $this->db->fieldExists('placement_status', 'enrollments')
                ? $this->groupCount('enrollments', 'placement_status', ['school_year' => $schoolYear])
                : [],
        ];
    }

    private function flagSummary(string $schoolYear, int $days): array
    {
        if (! $this->db->tableExists('enrollment_flags')) {
            return [
                'open_total' => 0,
                'stale_total' => 0,
                'by_type' => [],
                'by_priority' => [],
            ];
        }

        $staleBefore = date('Y-m-d H:i:s', strtotime('-' . $days . ' days'));

        return [
            'open_total' => $this->countRows('enrollment_flags', ['school_year' => $schoolYear, 'status' => 'open']),
            'stale_total' => $this->countRows('enrollment_flags', ['school_year' => $schoolYear, 'status' => 'open'], 'created_at < ' . $this->db->escape($staleBefore)),
            'by_type' => $this->groupCount('enrollment_flags', 'flag_type', ['school_year' => $schoolYear, 'status' => 'open']),
            'by_priority' => $this->groupCount('enrollment_flags', 'priority', ['school_year' => $schoolYear, 'status' => 'open']),
        ];
    }

    private function emailSummary(string $schoolYear): array
    {
        if (! $this->db->tableExists('enrollment_email_records')) {
            return [
                'total' => 0,
                'sent' => 0,
                'failed' => 0,
                'pending' => 0,
                'by_status' => [],
            ];
        }

        return [
            'total' => $this->countRows('enrollment_email_records', ['school_year' => $schoolYear]),
            'sent' => $this->countRows('enrollment_email_records', ['school_year' => $schoolYear, 'delivery_status' => 'sent']),
            'failed' => $this->countRows('enrollment_email_records', ['school_year' => $schoolYear, 'delivery_status' => 'failed']),
            'pending' => $this->countRows('enrollment_email_records', ['school_year' => $schoolYear, 'delivery_status' => 'pending']),
            'by_status' => $this->groupCount('enrollment_email_records', 'delivery_status', ['school_year' => $schoolYear]),
        ];
    }

    private function auditSummary(string $schoolYear, int $days): array
    {
        if (! $this->db->tableExists('enrollment_transition_audits')) {
            return [
                'recent_total' => 0,
                'by_action' => [],
            ];
        }

        $since = date('Y-m-d H:i:s', strtotime('-' . $days . ' days'));

        return [
            'recent_total' => $this->countRows('enrollment_transition_audits', ['school_year' => $schoolYear], 'created_at >= ' . $this->db->escape($since)),
            'by_action' => $this->groupCount('enrollment_transition_audits', 'action', ['school_year' => $schoolYear], 'created_at >= ' . $this->db->escape($since)),
        ];
    }

    private function countRows(string $table, array $where, ?string $rawWhere = null): int
    {
        $builder = $this->db->table($table);
        foreach ($where as $field => $value) {
            $builder->where($field, $value);
        }
        if ($rawWhere !== null) {
            $builder->where($rawWhere, null, false);
        }

        return $builder->countAllResults();
    }

    private function groupCount(string $table, string $field, array $where, ?string $rawWhere = null): array
    {
        if (! $this->db->fieldExists($field, $table)) {
            return [];
        }

        $builder = $this->db->table($table)
            ->select($field . ' AS value, COUNT(*) AS total', false)
            ->groupBy($field)
            ->orderBy('total', 'DESC', false);

        foreach ($where as $whereField => $value) {
            $builder->where($whereField, $value);
        }
        if ($rawWhere !== null) {
            $builder->where($rawWhere, null, false);
        }

        $result = [];
        foreach ($builder->get()->getResultArray() as $row) {
            $key = trim((string) ($row['value'] ?? '')) ?: 'blank';
            $result[$key] = (int) ($row['total'] ?? 0);
        }

        return $result;
    }

    private function currentSchoolYear(): string
    {
        if ($this->db->tableExists('school_years')) {
            $row = $this->db->table('school_years')
                ->select('name')
                ->where('status', 'active')
                ->orderBy('id', 'DESC')
                ->limit(1)
                ->get()
                ->getRowArray();
            if (! empty($row['name'])) {
                return (string) $row['name'];
            }
        }

        if ($this->db->tableExists('configuration')) {
            $row = $this->db->table('configuration')
                ->select('config_value')
                ->where('config_key', 'school_year')
                ->limit(1)
                ->get()
                ->getRowArray();
            if (! empty($row['config_value'])) {
                return (string) $row['config_value'];
            }
        }

        return '';
    }

    private function previousSchoolYearName(string $schoolYear): ?string
    {
        return preg_match('/^(\d{4})-(\d{4})$/', trim($schoolYear), $matches)
            ? ((int) $matches[1] - 1) . '-' . ((int) $matches[2] - 1)
            : null;
    }

    private function printReport(array $report): void
    {
        CLI::write('Registration Post-Launch Monitor: ' . (string) ($report['school_year'] ?? ''), 'cyan');
        CLI::write('Generated at: ' . (string) ($report['generated_at'] ?? ''));
        CLI::write('Expected returning students: ' . (int) ($report['expected_returning_students'] ?? 0));
        CLI::write('Submitted registrations: ' . (int) ($report['submitted_registrations'] ?? 0));
        CLI::write('Progress: ' . (($report['progress_percent'] ?? null) === null ? 'n/a' : (string) $report['progress_percent'] . '%'));
        CLI::write('Needs attention: ' . (int) ($report['needs_attention_count'] ?? 0), ((int) ($report['needs_attention_count'] ?? 0)) > 0 ? 'yellow' : 'green');
        CLI::newLine();

        $this->printSection('Enrollments', $report['enrollments'] ?? []);
        $this->printSection('Flags', $report['flags'] ?? []);
        $this->printSection('Emails', $report['emails'] ?? []);
        $this->printSection('Recent Audits', $report['audits'] ?? []);
    }

    private function printSection(string $title, array $data): void
    {
        CLI::write($title, 'white');
        foreach ($data as $key => $value) {
            if (is_array($value)) {
                CLI::write('  ' . $key . ':');
                foreach ($value as $nestedKey => $nestedValue) {
                    CLI::write('    ' . $nestedKey . ': ' . $nestedValue);
                }
            } else {
                CLI::write('  ' . $key . ': ' . $value);
            }
        }
        CLI::newLine();
    }
}