<?php

namespace App\Commands;

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

class RefreshEnrollmentAdminTables extends BaseCommand
{
    protected $group = 'Registration';
    protected $name = 'registration:refresh-admin-tables';
    protected $description = 'Refresh enrollment admin dashboard data from the transition service.';
    protected $usage = 'php spark registration:refresh-admin-tables [--school-year=2026-2027] [--limit=50] [--dry-run]';
    protected $options = [
        '--school-year' => 'Target school year name. Defaults to the active/current configured year.',
        '--limit' => 'Maximum number of source-year students to process.',
        '--dry-run' => 'Preview target/source years and source student count without writing.',
    ];

    private BaseConnection $db;

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

        $sourceYear = $this->previousSchoolYearName($schoolYear);
        if ($schoolYear === '' || $sourceYear === null) {
            CLI::error('Unable to determine target/source school year.');
            return;
        }

        $limit = (int) ($options['limit'] ?? 0);
        $dryRun = ! empty($options['dry-run']);
        $students = $this->sourceStudents($sourceYear, $limit);

        CLI::write('Enrollment admin refresh', 'cyan');
        CLI::write('Target year: ' . $schoolYear);
        CLI::write('Source year: ' . $sourceYear);
        CLI::write('Source students: ' . count($students));

        $before = $this->dashboardCounts($schoolYear);
        $this->printCounts('Before', $before);

        if ($dryRun) {
            CLI::write('Dry-run complete. Re-run without --dry-run to write updates.', 'yellow');
            return;
        }

        $processed = 0;
        $eligible = 0;
        $blocked = 0;
        $errors = 0;
        $transitionService = service('enrollmentTransition');

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

            try {
                $evaluation = $transitionService->applyInitialTransition(
                    $studentId,
                    $sourceYear,
                    $schoolYear,
                    is_numeric($student['parent_id'] ?? null) ? (int) $student['parent_id'] : null,
                    null,
                    'admin'
                );

                $processed++;
                if (! empty($evaluation['academic_eligible']) && ($evaluation['blockers'] ?? []) === []) {
                    $eligible++;
                } else {
                    $blocked++;
                }
            } catch (Throwable $e) {
                $errors++;
                CLI::error('Student #' . $studentId . ' failed: ' . $e->getMessage());
            }
        }

        $after = $this->dashboardCounts($schoolYear);
        $this->printCounts('After', $after);
        CLI::write('Processed: ' . $processed, 'white');
        CLI::write('Eligible/applied: ' . $eligible, 'green');
        CLI::write('Blocked/flagged: ' . $blocked, $blocked > 0 ? 'yellow' : 'white');
        CLI::write('Errors: ' . $errors, $errors > 0 ? 'red' : 'white');
    }

    private function parseOptions(array $params): array
    {
        $options = [
            'school-year' => '',
            'limit' => 0,
            'dry-run' => false,
        ];

        $rawParams = array_merge($params, array_slice($_SERVER['argv'] ?? [], 2));

        foreach ($rawParams as $param) {
            $value = trim((string) $param);
            if ($value === '--dry-run') {
                $options['dry-run'] = true;
                continue;
            }

            if (! str_starts_with($value, '--') || ! str_contains($value, '=')) {
                continue;
            }

            [$key, $raw] = explode('=', substr($value, 2), 2);
            if (array_key_exists($key, $options)) {
                $options[$key] = $raw;
            }
        }

        return $options;
    }

    private function sourceStudents(string $sourceYear, int $limit): array
    {
        if (! $this->db->tableExists('student_class')) {
            return [];
        }

        $builder = $this->db->table('student_class sc')
            ->select('sc.student_id, s.parent_id')
            ->join('students s', 's.id = sc.student_id', 'left')
            ->where('sc.school_year', $sourceYear)
            ->groupBy('sc.student_id, s.parent_id')
            ->orderBy('sc.student_id', 'ASC');

        if ($limit > 0) {
            $builder->limit($limit);
        }

        return $builder->get()->getResultArray();
    }

    private function dashboardCounts(string $schoolYear): array
    {
        return [
            'enrollments' => $this->countRows('enrollments', ['school_year' => $schoolYear]),
            'open_flags' => $this->countRows('enrollment_flags', ['school_year' => $schoolYear, 'status' => 'open']),
            'audits' => $this->countRows('enrollment_transition_audits', ['school_year' => $schoolYear]),
            'exceptions' => $this->countRows('enrollment_exceptions', ['school_year' => $schoolYear]),
            'email_records' => $this->countRows('enrollment_email_records', ['school_year' => $schoolYear]),
        ];
    }

    private function countRows(string $table, array $where): int
    {
        if (! $this->db->tableExists($table)) {
            return 0;
        }

        $builder = $this->db->table($table);
        foreach ($where as $field => $value) {
            $builder->where($field, $value);
        }

        return $builder->countAllResults();
    }

    private function printCounts(string $label, array $counts): void
    {
        CLI::write($label . ' counts:', 'white');
        foreach ($counts as $key => $value) {
            CLI::write('  ' . $key . ': ' . $value);
        }
    }

    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;
    }
}