<?php

namespace App\Commands;

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

class EnrollmentReleaseAudit extends BaseCommand
{
    protected $group = 'Registration';
    protected $name = 'registration:release-audit';
    protected $description = 'Audit school-year transition and registration readiness before launch.';
    protected $usage = 'php spark registration:release-audit [--school-year=2026-2027] [--json]';
    protected $options = [
        '--school-year' => 'Target school year name. Defaults to the active/current configured year.',
        '--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();
        }

        $report = $this->buildReport($schoolYear);

        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): array
    {
        $previousYear = $this->previousSchoolYearName($schoolYear);
        $checks = [
            'school_year_configuration' => $this->schoolYearConfigurationCheck($schoolYear),
            'launch_approval' => $this->launchApprovalCheck($schoolYear),
            'deliberation_decisions' => $this->deliberationDecisionCheck($schoolYear, $previousYear),
            'open_enrollment_flags' => $this->openEnrollmentFlagsCheck($schoolYear),
            'failed_registration_emails' => $this->failedEmailCheck($schoolYear),
            'duplicate_or_invalid_contacts' => $this->contactQualityCheck(),
            'email_preview_sample' => $this->emailPreviewCheck($schoolYear),
        ];

        $blocking = 0;
        $warnings = 0;
        foreach ($checks as $check) {
            if (($check['severity'] ?? '') === 'blocking') {
                $blocking++;
            } elseif (($check['severity'] ?? '') === 'warning') {
                $warnings++;
            }
        }

        return [
            'school_year' => $schoolYear,
            'source_school_year' => $previousYear,
            'generated_at' => date('Y-m-d H:i:s'),
            'ready' => $blocking === 0,
            'blocking_count' => $blocking,
            'warning_count' => $warnings,
            'checks' => $checks,
        ];
    }

    private function schoolYearConfigurationCheck(string $schoolYear): array
    {
        if (! $this->db->tableExists('school_years')) {
            return $this->check('blocking', 'school_years table is missing.');
        }

        $row = $this->db->table('school_years')->where('name', $schoolYear)->limit(1)->get()->getRowArray();
        if ($row === null) {
            return $this->check('blocking', 'Target school year record was not found.');
        }

        $missing = [];
        foreach ([
            'registration_starts_on' => 'registration opening date',
            'registration_ends_on' => 'registration deadline',
        ] as $field => $label) {
            if (empty($row[$field])) {
                $missing[] = $label;
            }
        }

        if (! $this->db->tableExists('email_templates')) {
            $missing[] = 'email templates table';
        } elseif ($this->activeTemplateCount('registration_opening') <= 0) {
            $missing[] = 'active registration email template';
        }

        return $missing === []
            ? $this->check('pass', 'Required school-year launch configuration is present.')
            : $this->check('blocking', 'Missing: ' . implode(', ', $missing));
    }

    private function launchApprovalCheck(string $schoolYear): array
    {
        if (! $this->db->tableExists('school_years') || ! $this->db->fieldExists('registration_launch_approved_at', 'school_years')) {
            return $this->check('warning', 'Launch approval fields have not been migrated yet.');
        }

        $row = $this->db->table('school_years')
            ->select('registration_launch_approved_at')
            ->where('name', $schoolYear)
            ->limit(1)
            ->get()
            ->getRowArray();

        return ! empty($row['registration_launch_approved_at'] ?? null)
            ? $this->check('pass', 'Registration launch has been approved.')
            : $this->check('warning', 'Registration launch has not been approved yet.');
    }

    private function deliberationDecisionCheck(string $schoolYear, ?string $previousYear): array
    {
        if ($previousYear === null) {
            return $this->check('warning', 'Previous school year could not be inferred.');
        }
        if (! $this->db->tableExists('student_class') || ! $this->db->tableExists('student_decisions')) {
            return $this->check('warning', 'Student placement or decision tables are missing.');
        }

        $rows = $this->db->table('student_class sc')
            ->select('COUNT(DISTINCT sc.student_id) AS total', false)
            ->join('student_decisions sd', 'sd.student_id = sc.student_id AND sd.school_year = ' . $this->db->escape($previousYear), 'left', false)
            ->where('sc.school_year', $previousYear)
            ->groupStart()
                ->where('sd.id IS NULL')
                ->orWhere('sd.decision IS NULL')
                ->orWhere('TRIM(sd.decision) =', '')
            ->groupEnd()
            ->get()
            ->getRowArray();

        $missing = (int) ($rows['total'] ?? 0);
        return $missing === 0
            ? $this->check('pass', 'All placed source-year students have a recorded deliberation decision.')
            : $this->check('blocking', $missing . ' source-year student(s) are missing deliberation decisions.');
    }

    private function openEnrollmentFlagsCheck(string $schoolYear): array
    {
        if (! $this->db->tableExists('enrollment_flags')) {
            return $this->check('warning', 'Enrollment flags table has not been migrated yet.');
        }

        $count = $this->db->table('enrollment_flags')
            ->where('school_year', $schoolYear)
            ->where('status', 'open')
            ->countAllResults();

        return $count === 0
            ? $this->check('pass', 'No open enrollment follow-up flags remain.')
            : $this->check('warning', $count . ' open enrollment follow-up flag(s) remain.');
    }

    private function failedEmailCheck(string $schoolYear): array
    {
        if (! $this->db->tableExists('enrollment_email_records')) {
            return $this->check('warning', 'Enrollment email record table has not been migrated yet.');
        }

        $failed = $this->db->table('enrollment_email_records')
            ->where('school_year', $schoolYear)
            ->where('delivery_status', 'failed')
            ->countAllResults();

        return $failed === 0
            ? $this->check('pass', 'No failed registration email records found.')
            : $this->check('warning', $failed . ' failed registration email record(s) need retry or review.');
    }

    private function contactQualityCheck(): array
    {
        if (! $this->db->tableExists('users')) {
            return $this->check('warning', 'Users table is missing.');
        }
        if (! $this->db->fieldExists('email', 'users')) {
            return $this->check('warning', 'Users table does not include an email field.');
        }

        $rows = $this->db->query(
            "SELECT LOWER(TRIM(email)) AS normalized_email, COUNT(*) AS total
             FROM users
             WHERE email IS NOT NULL AND email <> ''
             GROUP BY LOWER(TRIM(email))
             HAVING COUNT(*) > 1"
        )->getResultArray();

        $invalid = 0;
        foreach ($this->db->table('users')->select('email')->where('email IS NOT NULL')->where('email !=', '')->get()->getResultArray() as $row) {
            if (! filter_var((string) ($row['email'] ?? ''), FILTER_VALIDATE_EMAIL)) {
                $invalid++;
            }
        }

        $duplicates = count($rows);
        if ($duplicates === 0 && $invalid === 0) {
            return $this->check('pass', 'No duplicate or invalid parent/user email addresses were detected.');
        }

        return $this->check('warning', $duplicates . ' duplicate email value(s) and ' . $invalid . ' invalid email address(es) were detected.');
    }

    private function emailPreviewCheck(string $schoolYear): array
    {
        if (! $this->db->tableExists('students')) {
            return $this->check('warning', 'Students table is missing.');
        }

        $row = $this->db->table('students')
            ->select('parent_id')
            ->where('parent_id IS NOT NULL', null, false)
            ->orderBy('parent_id', 'ASC')
            ->limit(1)
            ->get()
            ->getRowArray();

        $parentId = is_numeric($row['parent_id'] ?? null) ? (int) $row['parent_id'] : 0;
        if ($parentId <= 0) {
            return $this->check('warning', 'No parent with students was found for email preview.');
        }

        try {
            $message = service('enrollmentRegistrationEmail')->previewForParent($schoolYear, $parentId);
        } catch (\Throwable $e) {
            return $this->check('blocking', 'Email preview generation failed: ' . $e->getMessage());
        }

        return $message !== null && trim((string) ($message['body'] ?? '')) !== ''
            ? $this->check('pass', 'A consolidated registration email preview can be generated.')
            : $this->check('blocking', 'No consolidated registration email preview could be generated.');
    }

    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 activeTemplateCount(string $key): int
    {
        $fields = $this->db->getFieldNames('email_templates');
        $keyField = in_array('code', $fields, true) ? 'code' : 'template_key';

        return $this->db->table('email_templates')
            ->where($keyField, $key)
            ->where('is_active', 1)
            ->countAllResults();
    }

    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 check(string $severity, string $message): array
    {
        return ['severity' => $severity, 'message' => $message];
    }

    private function printReport(array $report): void
    {
        CLI::write('Registration Release Audit: ' . (string) ($report['school_year'] ?? ''), ($report['ready'] ?? false) ? 'green' : 'red');
        CLI::write('Generated at: ' . (string) ($report['generated_at'] ?? ''));
        CLI::write('Ready: ' . (($report['ready'] ?? false) ? 'yes' : 'no'));
        CLI::newLine();

        foreach (($report['checks'] ?? []) as $name => $check) {
            $severity = (string) ($check['severity'] ?? 'warning');
            $color = match ($severity) {
                'pass' => 'green',
                'blocking' => 'red',
                default => 'yellow',
            };
            CLI::write(sprintf('[%s] %s: %s', strtoupper($severity), $name, (string) ($check['message'] ?? '')), $color);
        }

        CLI::newLine();
        CLI::write('Blocking: ' . (int) ($report['blocking_count'] ?? 0), ((int) ($report['blocking_count'] ?? 0)) > 0 ? 'red' : 'green');
        CLI::write('Warnings: ' . (int) ($report['warning_count'] ?? 0), ((int) ($report['warning_count'] ?? 0)) > 0 ? 'yellow' : 'green');
    }
}