<?php

namespace App\Services;

use App\Models\SchoolYearClosingBatchModel;
use App\Models\SchoolYearClosingItemModel;
use App\Models\SchoolYearModel;
use App\Models\InvoiceModel;
use App\Support\Enrollment\DeliberationDecision;
use App\Support\SchoolYear\SchoolYearStatus;
use CodeIgniter\Database\BaseConnection;
use InvalidArgumentException;
use RuntimeException;

final class SchoolYearClosingService
{
    public function __construct(
        private readonly SchoolYearModel $schoolYearModel,
        private readonly SchoolYearClosingBatchModel $batchModel,
        private readonly SchoolYearClosingItemModel $itemModel,
        private readonly SchoolYearManagementService $managementService,
        private readonly BaseConnection $db,
    ) {
    }

    public function preview(int $sourceYearId, ?int $targetYearId = null): array
    {
        $source = $this->requireYear($sourceYearId);
        $target = $targetYearId !== null ? $this->schoolYearModel->find($targetYearId) : $this->nextDraftYear((string) $source['name']);
        $sourceName = (string) $source['name'];

        $finance = $this->financialSummary($sourceName);
        $overview = [
            'students' => $this->countBySchoolYear('student_class', $sourceName, 'student_id'),
            'families' => $this->countFamilies($sourceName),
            'classes' => $this->countBySchoolYear('classSection', $sourceName, 'class_id'),
            'teachers' => $this->countBySchoolYear('teacher_class', $sourceName, 'teacher_id'),
            'invoices' => $finance['invoice_count'],
            'total_invoiced' => $finance['total_invoiced'],
            'total_paid' => $finance['total_paid'],
            'total_outstanding' => $finance['total_outstanding'],
        ];

        $findings = [];
        if ($target === null) {
            $findings[] = $this->finding('blocking', 'Missing target year', 'Create a draft target school year before starting closing.');
        } elseif (! in_array((string) $target['status'], [SchoolYearStatus::DRAFT, SchoolYearStatus::ACTIVE], true)) {
            $findings[] = $this->finding('blocking', 'Invalid target year', 'The target school year must be draft or active.');
        }

        foreach ($this->unpaidInvoiceFindings($sourceName) as $finding) {
            $findings[] = $finding;
        }

        $promotion = $this->promotionPreview($sourceName, $target !== null ? (string) ($target['name'] ?? '') : null);
        if (($promotion['summary']['missing_decision'] ?? 0) > 0) {
            $findings[] = $this->finding(
                'blocking',
                'Students missing promotion decisions',
                $promotion['summary']['missing_decision'] . ' active student(s) do not have a saved promotion decision for this school year.'
            );
        }
        if (($promotion['summary']['pending_decision'] ?? 0) > 0) {
            $findings[] = $this->finding(
                'blocking',
                'Students with pending promotion decisions',
                $promotion['summary']['pending_decision'] . ' active student(s) still have pending promotion decisions.'
            );
        }
        if (($promotion['summary']['missing_queue'] ?? 0) > 0) {
            $findings[] = $this->finding(
                'warning',
                'Passed students missing promotion queue',
                $promotion['summary']['missing_queue'] . ' passed student(s) are not queued for the target school year. This will not block closing.'
            );
        }

        if ($this->countMissingSchoolYearRows('invoices') > 0) {
            $findings[] = $this->finding('blocking', 'Invoices missing school year', 'Some invoice records are not assigned to a school year.');
        }

        $inventory = $this->inventoryClosingPreview($sourceName, $target !== null ? (string) ($target['name'] ?? '') : '');
        foreach ($inventory['findings'] as $finding) {
            $findings[] = $finding;
        }

        $carryForward = $this->carryForwardFamilies($sourceName);
        $warnings = array_values(array_filter($findings, static fn (array $f): bool => $f['severity'] === 'warning'));
        $blockers = array_values(array_filter($findings, static fn (array $f): bool => $f['severity'] === 'blocking'));

        $result = [
            'source' => $source,
            'target' => $target,
            'overview' => $overview,
            'finance' => $finance,
            'promotion' => $promotion,
            'inventory' => $inventory,
            'findings' => $findings,
            'blockers' => $blockers,
            'warnings' => $warnings,
            'carry_forward' => $carryForward,
            'generated_at' => date('Y-m-d H:i:s'),
        ];
        $result['hash'] = $this->hashPreview($result);

        return $result;
    }

    public function start(int $sourceYearId, int $targetYearId, ?int $userId = null): void
    {
        $this->assertClosingTablesExist();

        $source = $this->requireYear($sourceYearId);

        if (($source['status'] ?? '') !== SchoolYearStatus::ACTIVE) {
            throw new InvalidArgumentException('Only an active school year can begin closing.');
        }

        $preview = $this->preview($sourceYearId, $targetYearId);
        if ($preview['blockers'] !== []) {
            throw new InvalidArgumentException('Resolve blocking closing issues before starting closing.');
        }

        $this->db->transStart();
        $now = date('Y-m-d H:i:s');
        $batchId = $this->batchModel->insert([
            'source_school_year_id' => $sourceYearId,
            'target_school_year_id' => $targetYearId,
            'status' => 'started',
            'preview_hash' => $preview['hash'],
            'total_families' => count($preview['carry_forward']),
            'total_positive_balance' => $this->sumCarryForwardBalances($preview['carry_forward'], true),
            'total_credit_balance' => $this->sumCarryForwardBalances($preview['carry_forward'], false),
            'started_by' => $userId,
            'started_at' => $now,
        ], true);

        foreach ($preview['carry_forward'] as $row) {
            $this->itemModel->insert([
                'closing_batch_id' => $batchId,
                'family_id' => (int) $row['family_id'],
                'source_balance' => $row['source_balance'],
                'credit_amount' => $row['credit_amount'],
                'carry_forward_amount' => $row['carry_forward_amount'],
                'status' => 'pending',
            ]);
        }

        $this->schoolYearModel->update($sourceYearId, [
            'status' => SchoolYearStatus::CLOSING,
            'closing_started_at' => $now,
            'updated_by' => $userId,
        ]);
        $this->managementService->log($sourceYearId, SchoolYearStatus::ACTIVE, SchoolYearStatus::CLOSING, 'closing_start', $userId, [
            'target_school_year_id' => $targetYearId,
            'closing_batch_id' => $batchId,
            'preview_hash' => $preview['hash'],
        ]);
        $this->db->transComplete();

        if ($this->db->transStatus() === false) {
            throw new RuntimeException('Unable to start school year closing.');
        }
    }

    public function execute(int $sourceYearId, ?int $userId = null): void
    {
        $this->assertClosingTablesExist();

        $batch = $this->latestOpenBatch($sourceYearId);
        if ($batch === null) {
            throw new InvalidArgumentException('No open closing batch exists.');
        }

        $preview = $this->preview($sourceYearId, (int) $batch['target_school_year_id']);
        if ($preview['hash'] !== (string) $batch['preview_hash']) {
            if (($batch['status'] ?? '') === 'started') {
                $this->refreshBatchFromPreview($batch, $preview);
                $batch = $this->latestOpenBatch($sourceYearId) ?? $batch;
            } elseif (in_array((string) ($batch['status'] ?? ''), ['executed', 'completed'], true) && $this->countExistingTargetInvoices($batch) === 0) {
                $this->refreshBatchFromPreview($batch, $preview);
                $batch = $this->latestOpenBatch($sourceYearId) ?? $batch;
            } elseif (! in_array((string) ($batch['status'] ?? ''), ['executed', 'completed'], true)) {
                throw new InvalidArgumentException('Closing preview has changed. Refresh and restart closing before executing carry-forward.');
            }
        }

        $source = $this->requireYear($sourceYearId);
        $target = $this->requireYear((int) $batch['target_school_year_id']);
        $sourceName = (string) ($source['name'] ?? '');
        $targetName = (string) ($target['name'] ?? '');

        $this->db->transStart();
        $items = $this->itemModel->where('closing_batch_id', (int) $batch['id'])->findAll();
        foreach ($items as $item) {
            if (($item['status'] ?? '') === 'completed' && (int) ($item['target_invoice_id'] ?? 0) > 0) {
                continue;
            }

            $targetInvoiceId = $this->createCarryForwardInvoice($item, $sourceName, $targetName, $userId);
            $this->itemModel->update((int) $item['id'], [
                'target_invoice_id' => $targetInvoiceId,
                'status' => 'completed',
                'error_message' => null,
            ]);
        }
        $this->executeInventoryCarryForward($source, $target, (int) $batch['id'], $userId);
        if (($batch['status'] ?? '') !== 'completed') {
            $this->batchModel->update((int) $batch['id'], ['status' => 'executed']);
        }
        $currentStatus = (string) ($source['status'] ?? SchoolYearStatus::CLOSING);
        $this->managementService->log($sourceYearId, $currentStatus, $currentStatus, 'carry_forward_execute', $userId, [
            'closing_batch_id' => (int) $batch['id'],
            'target_school_year_id' => (int) $batch['target_school_year_id'],
            'note' => 'Created target-year opening balance invoices for previewed carry-forward items.',
        ]);
        $this->db->transComplete();

        if ($this->db->transStatus() === false) {
            throw new RuntimeException('Unable to execute carry-forward.');
        }
    }

    public function complete(int $sourceYearId, ?int $userId = null): void
    {
        $this->assertClosingTablesExist();

        $batch = $this->latestOpenBatch($sourceYearId);
        if ($batch === null || ($batch['status'] ?? '') !== 'executed') {
            throw new InvalidArgumentException('Carry-forward must be executed before completing closing.');
        }

        $pending = $this->itemModel
            ->where('closing_batch_id', (int) $batch['id'])
            ->where('status !=', 'completed')
            ->countAllResults();
        if ($pending > 0) {
            throw new InvalidArgumentException('All closing batch items must complete before the year can be closed.');
        }

        $targetYearId = (int) ($batch['target_school_year_id'] ?? 0);
        $target = $targetYearId > 0 ? $this->schoolYearModel->find($targetYearId) : null;
        if ($target === null) {
            throw new InvalidArgumentException('A target school year is required before completing closing.');
        }

        $this->db->transStart();
        $now = date('Y-m-d H:i:s');
        if ($this->db->tableExists('inventory_item_years')) {
            $this->db->table('inventory_item_years')
                ->where('school_year', (string) ($target['name'] ?? ''))
                ->where('source_item_year_id IS NOT NULL', null, false)
                ->where('closing_batch_id', (int) $batch['id'])
                ->where('status', 'carried')
                ->update(['status' => 'open', 'updated_at' => $now, 'updated_by' => $userId]);
            $this->db->table('inventory_item_years')
                ->where('school_year', (string) ($this->requireYear($sourceYearId)['name'] ?? ''))
                ->where('closing_batch_id', (int) $batch['id'])
                ->where('status', 'carried')
                ->update(['status' => 'closed', 'updated_at' => $now, 'updated_by' => $userId]);
        }
        $this->batchModel->update((int) $batch['id'], [
            'status' => 'completed',
            'completed_by' => $userId,
            'completed_at' => $now,
        ]);
        $this->schoolYearModel->update($sourceYearId, [
            'status' => SchoolYearStatus::CLOSED,
            'closed_at' => $now,
            'updated_by' => $userId,
        ]);
        $this->schoolYearModel->update($targetYearId, [
            'status' => SchoolYearStatus::ACTIVE,
            'previous_school_year_id' => $sourceYearId,
            'activated_at' => $target['activated_at'] ?? $now,
            'updated_by' => $userId,
        ]);
        $this->schoolYearModel->update($sourceYearId, [
            'next_school_year_id' => $targetYearId,
        ]);
        $targetName = (string) ($target['name'] ?? '');
        $sourceName = (string) ($this->requireYear($sourceYearId)['name'] ?? '');
        service('studentYearStatus')->markReturningStudentsForTargetYear($sourceName, $targetName);
        $this->managementService->syncConfigurationForYear($targetYearId);
        $this->syncActiveYearSession($targetName);
        $this->managementService->log($sourceYearId, SchoolYearStatus::CLOSING, SchoolYearStatus::CLOSED, 'closing_complete', $userId, [
            'closing_batch_id' => (int) $batch['id'],
            'activated_school_year_id' => $targetYearId,
        ]);
        $this->managementService->log($targetYearId, (string) ($target['status'] ?? SchoolYearStatus::DRAFT), SchoolYearStatus::ACTIVE, 'activate_after_closing', $userId, [
            'closed_school_year_id' => $sourceYearId,
            'closing_batch_id' => (int) $batch['id'],
        ]);
        $this->db->transComplete();

        if ($this->db->transStatus() === false) {
            throw new RuntimeException('Unable to complete closing.');
        }
    }

    public function cancel(int $sourceYearId, ?int $userId = null): void
    {
        $this->assertClosingTablesExist();

        $batch = $this->latestOpenBatch($sourceYearId);
        if ($batch !== null && in_array((string) $batch['status'], ['executed', 'completed'], true)) {
            throw new InvalidArgumentException('Closing cannot be cancelled after carry-forward has executed.');
        }

        $this->db->transStart();
        if ($batch !== null) {
            $this->batchModel->update((int) $batch['id'], ['status' => 'cancelled']);
        }
        $this->schoolYearModel->update($sourceYearId, [
            'status' => SchoolYearStatus::ACTIVE,
            'updated_by' => $userId,
        ]);
        $this->managementService->log($sourceYearId, SchoolYearStatus::CLOSING, SchoolYearStatus::ACTIVE, 'closing_cancel', $userId, [
            'closing_batch_id' => $batch['id'] ?? null,
        ]);
        $this->db->transComplete();

        if ($this->db->transStatus() === false) {
            throw new RuntimeException('Unable to cancel closing.');
        }
    }

    public function latestBatch(int $sourceYearId): ?array
    {
        if (! $this->db->tableExists('school_year_closing_batches')) {
            return null;
        }

        return $this->batchModel
            ->where('source_school_year_id', $sourceYearId)
            ->orderBy('id', 'DESC')
            ->first();
    }

    private function requireYear(int $id): array
    {
        $year = $this->schoolYearModel->find($id);
        if ($year === null) {
            throw new InvalidArgumentException('School year not found.');
        }

        return $year;
    }

    private function nextDraftYear(string $sourceName): ?array
    {
        if (! preg_match('/^(\d{4})-(\d{4})$/', $sourceName, $matches)) {
            return null;
        }

        $nextName = $matches[2] . '-' . ((int) $matches[2] + 1);

        return $this->schoolYearModel
            ->where('name', $nextName)
            ->first();
    }

    private function financialSummary(string $schoolYear): array
    {
        $summary = [
            'invoice_count' => 0,
            'total_invoiced' => 0.0,
            'total_paid' => 0.0,
            'total_outstanding' => 0.0,
            'positive_balance' => 0.0,
            'credit_balance' => 0.0,
        ];

        if (! $this->db->tableExists('invoices')) {
            return $summary;
        }

        $row = $this->db->table('invoices')
            ->select('COUNT(*) AS invoice_count')
            ->select('COALESCE(SUM(total_amount), 0) AS total_invoiced')
            ->select('COALESCE(SUM(paid_amount), 0) AS total_paid')
            ->select('COALESCE(SUM(balance), 0) AS total_outstanding')
            ->select('COALESCE(SUM(CASE WHEN balance > 0 THEN balance ELSE 0 END), 0) AS positive_balance', false)
            ->select('COALESCE(SUM(CASE WHEN balance < 0 THEN ABS(balance) ELSE 0 END), 0) AS credit_balance', false)
            ->where('school_year', $schoolYear)
            ->get()
            ->getRowArray() ?? [];

        foreach ($summary as $key => $value) {
            $summary[$key] = $key === 'invoice_count' ? (int) ($row[$key] ?? 0) : round((float) ($row[$key] ?? 0), 2);
        }

        return $summary;
    }

    private function unpaidInvoiceFindings(string $schoolYear): array
    {
        if (! $this->db->tableExists('invoices')) {
            return [];
        }

        $count = $this->db->table('invoices')
            ->where('school_year', $schoolYear)
            ->where('balance >', 0)
            ->where("LOWER(status) IN ('unpaid', 'partially paid')", null, false)
            ->countAllResults();

        return $count > 0
            ? [$this->finding('warning', 'Outstanding balances exist', "{$count} invoice(s) still have a positive balance and will require carry-forward review.")]
            : [];
    }

    private function carryForwardFamilies(string $schoolYear): array
    {
        if (! $this->db->tableExists('invoices')) {
            return [];
        }

        $builder = $this->db->table('invoices i')
            ->select('i.parent_id AS family_id')
            ->select('COALESCE(SUM(i.balance), 0) AS source_balance')
            ->where('i.school_year', $schoolYear)
            ->where('i.balance !=', 0)
            ->where("LOWER(i.status) IN ('unpaid', 'partially paid')", null, false);

        if ($this->db->tableExists('users')) {
            $builder
                ->select('u.firstname AS parent_firstname, u.lastname AS parent_lastname, u.email AS parent_email')
                ->join('users u', 'u.id = i.parent_id', 'left')
                ->groupBy('i.parent_id, u.firstname, u.lastname, u.email');
        } else {
            $builder->groupBy('i.parent_id');
        }

        $rows = $builder
            ->having('source_balance !=', 0)
            ->orderBy('i.parent_id', 'ASC')
            ->get()
            ->getResultArray();

        return array_map(static function (array $row): array {
            $balance = round((float) $row['source_balance'], 2);

            return [
                'family_id' => (int) $row['family_id'],
                'family' => 'Family #' . (int) $row['family_id'],
                'parent' => trim((string) ($row['parent_firstname'] ?? '') . ' ' . (string) ($row['parent_lastname'] ?? '')) ?: 'Parent #' . (int) $row['family_id'],
                'parent_email' => (string) ($row['parent_email'] ?? ''),
                'source_balance' => $balance,
                'credit_amount' => $balance < 0 ? abs($balance) : 0.0,
                'adjustment_amount' => 0.0,
                'carry_forward_amount' => $balance,
            ];
        }, $rows);
    }

    private function refreshBatchFromPreview(array $batch, array $preview): void
    {
        $batchId = (int) ($batch['id'] ?? 0);
        if ($batchId <= 0) {
            throw new InvalidArgumentException('Closing batch was not found.');
        }

        $this->itemModel->where('closing_batch_id', $batchId)->delete();
        foreach ($preview['carry_forward'] as $row) {
            $this->itemModel->insert([
                'closing_batch_id' => $batchId,
                'family_id' => (int) $row['family_id'],
                'source_balance' => $row['source_balance'],
                'credit_amount' => $row['credit_amount'],
                'carry_forward_amount' => $row['carry_forward_amount'],
                'status' => 'pending',
            ]);
        }

        $this->batchModel->update($batchId, [
            'preview_hash' => $preview['hash'],
            'total_families' => count($preview['carry_forward']),
            'total_positive_balance' => $this->sumCarryForwardBalances($preview['carry_forward'], true),
            'total_credit_balance' => $this->sumCarryForwardBalances($preview['carry_forward'], false),
        ]);
    }

    private function inventoryClosingPreview(string $sourceYear, string $targetYear): array
    {
        $empty = ['rows' => [], 'summary' => ['books' => 0, 'target_opening_quantity' => 0], 'findings' => []];
        if (! $this->db->tableExists('inventory_item_years') || ! $this->db->tableExists('inventory_items')) {
            return $empty;
        }

        $itemYears = $this->db->table('inventory_item_years iy')
            ->select('iy.*, i.name AS item_name, i.isbn, i.edition')
            ->join('inventory_items i', 'i.id = iy.inventory_item_id', 'inner')
            ->where('iy.school_year', $sourceYear)
            ->where('i.type', 'book')
            ->orderBy('i.name', 'ASC')
            ->get()->getResultArray();

        if ($itemYears === []) {
            return $empty;
        }

        $ids = array_map(static fn (array $row): int => (int) $row['id'], $itemYears);
        $movementTotals = [];
        $issueCounts = [];
        if ($this->db->tableExists('inventory_movements')) {
            foreach ($this->db->table('inventory_movements')
                ->select('item_year_id, COALESCE(SUM(qty_change), 0) AS movement_total')
                ->whereIn('item_year_id', $ids)
                ->whereIn('status', ['posted', 'reversed'])
                ->groupBy('item_year_id')
                ->get()->getResultArray() as $row) {
                $movementTotals[(int) $row['item_year_id']] = (int) ($row['movement_total'] ?? 0);
            }
        }
        if ($this->db->tableExists('student_book_issues')) {
            foreach ($this->db->table('student_book_issues')
                ->select('inventory_item_year_id, COUNT(*) AS issue_count')
                ->whereIn('inventory_item_year_id', $ids)
                ->where('school_year', $sourceYear)
                ->groupBy('inventory_item_year_id')
                ->get()->getResultArray() as $row) {
                $issueCounts[(int) $row['inventory_item_year_id']] = (int) ($row['issue_count'] ?? 0);
            }
        }

        $findings = [];
        $rows = [];
        $targetOpeningTotal = 0;
        foreach ($itemYears as $row) {
            $opening = (int) ($row['opening_quantity'] ?? 0);
            $system = $opening + (int) ($movementTotals[(int) $row['id']] ?? 0);
            $hasIssueSnapshots = (int) ($issueCounts[(int) $row['id']] ?? 0) > 0;
            $counted = $row['counted_closing_quantity'];
            $countedInt = $counted === null ? ($hasIssueSnapshots ? null : $system) : (int) $counted;
            $variance = $countedInt === null ? null : $countedInt - $system;
            $price = (int) ($row['charge_price_cents'] ?? 0);
            if ($price <= 0 || (int) ($row['price_confirmed'] ?? 0) !== 1) {
                $findings[] = $this->finding(
                    $hasIssueSnapshots ? 'blocking' : 'warning',
                    'Book price missing',
                    (string) $row['item_name'] . ' has no confirmed charge price for ' . $sourceYear
                    . ($hasIssueSnapshots ? '.' : '; this bootstrap year has no issue price snapshots, so confirm the target-year price before future distribution.')
                );
            }
            if ($system < 0) {
                $findings[] = $this->finding('blocking', 'Negative book stock', (string) $row['item_name'] . ' calculates to negative stock.');
            }
            if ($counted === null && ! $hasIssueSnapshots) {
                $findings[] = $this->finding('warning', 'Physical book count defaulted', (string) $row['item_name'] . ' has no physical count in this bootstrap year; system closing quantity will be carried forward.');
            } elseif ($countedInt === null) {
                $findings[] = $this->finding('blocking', 'Physical book count missing', (string) $row['item_name'] . ' needs a counted closing quantity.');
            } elseif ($variance !== 0) {
                $findings[] = $this->finding('blocking', 'Unresolved book variance', (string) $row['item_name'] . ' has variance ' . $variance . '. Resolve with an audited adjustment before closing.');
            }
            $targetOpening = max(0, $countedInt ?? 0);
            $targetOpeningTotal += $targetOpening;
            $rows[] = [
                'item_year_id' => (int) $row['id'],
                'inventory_item_id' => (int) $row['inventory_item_id'],
                'item_name' => (string) $row['item_name'],
                'isbn' => (string) ($row['isbn'] ?? ''),
                'edition' => (string) ($row['edition'] ?? ''),
                'opening_quantity' => $opening,
                'movement_total' => (int) ($movementTotals[(int) $row['id']] ?? 0),
                'system_closing_quantity' => $system,
                'counted_closing_quantity' => $countedInt,
                'variance_quantity' => $variance,
                'charge_price_cents' => $price,
                'target_school_year' => $targetYear,
                'target_opening_quantity' => $targetOpening,
            ];
        }

        foreach ($this->inventoryEvidenceFindings($sourceYear) as $finding) {
            $findings[] = $finding;
        }

        return [
            'rows' => $rows,
            'summary' => ['books' => count($rows), 'target_opening_quantity' => $targetOpeningTotal],
            'findings' => $findings,
        ];
    }

    private function inventoryEvidenceFindings(string $sourceYear): array
    {
        if (! $this->db->tableExists('inventory_movements') || ! $this->db->tableExists('student_book_issues')) {
            return [];
        }
        $findings = [];
        $hasAnyIssueSnapshots = $this->db->table('student_book_issues')
            ->where('school_year', $sourceYear)
            ->countAllResults() > 0;
        $missingIssues = $this->db->table('inventory_movements m')
            ->join('student_book_issues sbi', 'sbi.distribution_movement_id = m.id', 'left')
            ->where('m.school_year', $sourceYear)
            ->where('m.movement_type', 'distribution')
            ->where('m.status', 'posted')
            ->where('sbi.id IS NULL', null, false)
            ->countAllResults();
        if ($missingIssues > 0) {
            $findings[] = $this->finding(
                $hasAnyIssueSnapshots ? 'blocking' : 'warning',
                'Distribution movement missing issue snapshot',
                $missingIssues . ' legacy book distribution movement(s) have no linked student book issue'
                . ($hasAnyIssueSnapshots ? '.' : '; this bootstrap year will not use those movements as refund price evidence.')
            );
        }

        $missingMovements = $this->db->table('student_book_issues sbi')
            ->join('inventory_movements m', 'm.id = sbi.distribution_movement_id', 'left')
            ->where('sbi.school_year', $sourceYear)
            ->where('sbi.status', 'issued')
            ->where('m.id IS NULL', null, false)
            ->countAllResults();
        if ($missingMovements > 0) {
            $findings[] = $this->finding('blocking', 'Student issue missing stock movement', $missingMovements . ' student book issue(s) have no linked stock movement.');
        }

        return $findings;
    }

    private function executeInventoryCarryForward(array $source, array $target, int $batchId, ?int $userId): void
    {
        if (! $this->db->tableExists('inventory_item_years')) {
            return;
        }
        $preview = $this->inventoryClosingPreview((string) $source['name'], (string) $target['name']);
        $blockers = array_values(array_filter($preview['findings'], static fn (array $finding): bool => ($finding['severity'] ?? '') === 'blocking'));
        if ($blockers !== []) {
            throw new InvalidArgumentException('Resolve inventory closing blockers before executing carry-forward.');
        }
        $now = date('Y-m-d H:i:s');
        foreach ($preview['rows'] as $row) {
            $targetOpening = (int) ($row['target_opening_quantity'] ?? 0);
            if ($targetOpening <= 0) {
                continue;
            }
            $exists = $this->db->table('inventory_item_years')
                ->where('inventory_item_id', (int) $row['inventory_item_id'])
                ->where('school_year', (string) $target['name'])
                ->get(1)->getRowArray();
            if ($exists === null) {
                $this->db->table('inventory_item_years')->insert([
                    'inventory_item_id' => (int) $row['inventory_item_id'],
                    'school_year_id' => (int) $target['id'],
                    'school_year' => (string) $target['name'],
                    'opening_quantity' => $targetOpening,
                    'charge_price_cents' => (int) $row['charge_price_cents'],
                    'currency' => 'USD',
                    'price_confirmed' => 0,
                    'status' => 'carried',
                    'source_item_year_id' => (int) $row['item_year_id'],
                    'closing_batch_id' => $batchId,
                    'created_by' => $userId,
                    'updated_by' => $userId,
                    'created_at' => $now,
                    'updated_at' => $now,
                ]);
            }
            $this->db->table('inventory_item_years')->where('id', (int) $row['item_year_id'])->update([
                'system_closing_quantity' => (int) $row['system_closing_quantity'],
                'variance_quantity' => 0,
                'status' => 'carried',
                'closing_batch_id' => $batchId,
                'updated_by' => $userId,
                'updated_at' => $now,
            ]);
        }
    }

    private function countExistingTargetInvoices(array $batch): int
    {
        $batchId = (int) ($batch['id'] ?? 0);
        if ($batchId <= 0 || ! $this->db->tableExists('invoices')) {
            return 0;
        }

        $items = $this->itemModel
            ->select('target_invoice_id')
            ->where('closing_batch_id', $batchId)
            ->where('target_invoice_id IS NOT NULL', null, false)
            ->where('target_invoice_id >', 0)
            ->findAll();

        $invoiceIds = array_values(array_unique(array_map(static fn (array $row): int => (int) ($row['target_invoice_id'] ?? 0), $items)));
        if ($invoiceIds === []) {
            return 0;
        }

        return $this->db->table('invoices')
            ->whereIn('id', $invoiceIds)
            ->countAllResults();
    }

    private function sumCarryForwardBalances(array $rows, bool $positive): float
    {
        $total = 0.0;
        foreach ($rows as $row) {
            $amount = (float) ($row['carry_forward_amount'] ?? 0);
            if ($positive && $amount > 0) {
                $total += $amount;
            } elseif (! $positive && $amount < 0) {
                $total += abs($amount);
            }
        }

        return round($total, 2);
    }

    private function createCarryForwardInvoice(array $item, string $sourceYear, string $targetYear, ?int $userId): int
    {
        $existingId = (int) ($item['target_invoice_id'] ?? 0);
        if ($existingId > 0) {
            $existing = $this->db->table('invoices')->where('id', $existingId)->get()->getRowArray();
            if ($existing !== null) {
                return $existingId;
            }
        }

        $amount = round((float) ($item['carry_forward_amount'] ?? 0), 2);
        if (abs($amount) < 0.005) {
            return 0;
        }

        $parentId = (int) ($item['family_id'] ?? 0);
        $invoiceNumber = $this->carryForwardInvoiceNumber((int) ($item['id'] ?? 0), $parentId, $sourceYear, $targetYear);
        $existing = $this->db->table('invoices')
            ->select('id')
            ->where('invoice_number', $invoiceNumber)
            ->get()
            ->getRowArray();
        if ($existing !== null) {
            return (int) $existing['id'];
        }

        $now = function_exists('utc_now') ? utc_now() : gmdate('Y-m-d H:i:s');
        $payload = [
            'parent_id' => $parentId,
            'invoice_number' => $invoiceNumber,
            'total_amount' => $amount,
            'paid_amount' => 0,
            'balance' => $amount,
            'school_year' => $targetYear,
            'semester' => 'Opening Balance',
            'issue_date' => $now,
            'due_date' => null,
            'status' => $amount > 0 ? 'Unpaid' : 'Credit',
            'created_at' => $now,
            'updated_at' => $now,
            'updated_by' => $userId,
        ];

        if ($this->db->fieldExists('description', 'invoices')) {
            $payload['description'] = $amount >= 0
                ? "Carry over balance from last year {$sourceYear}"
                : "Credit carry over from last year {$sourceYear}";
        }

        $invoiceModel = new InvoiceModel();
        $invoiceId = $invoiceModel->insert($payload, true);
        if (! $invoiceId) {
            throw new RuntimeException('Unable to create carry-forward invoice: ' . json_encode($invoiceModel->errors()));
        }

        $invoiceId = (int) $invoiceId;
        $description = (string) ($payload['description'] ?? "Carry over balance from last year {$sourceYear}");
        try {
            $ledgerService = new \App\Libraries\InvoiceLedgerService();
            $ledgerService->issueCarryForwardInvoiceLine($invoiceId, $amount, $description);
            $ledgerService->recalculateInvoice($invoiceId);
        } catch (\Throwable $e) {
            log_message('error', 'Carry-forward invoice line issuance failed for invoice ' . $invoiceId . ': ' . $e->getMessage());
        }

        return $invoiceId;
    }

    private function carryForwardInvoiceNumber(int $itemId, int $parentId, string $sourceYear, string $targetYear): string
    {
        $source = preg_replace('/[^0-9A-Za-z]/', '', $sourceYear);
        $target = preg_replace('/[^0-9A-Za-z]/', '', $targetYear);

        return sprintf('CF-%s-%s-P%d-I%d', $source, $target, $parentId, $itemId);
    }

    private function promotionPreview(string $schoolYear, ?string $targetSchoolYear): array
    {
        $summary = [
            'total_students' => 0,
            'with_decision' => 0,
            'missing_decision' => 0,
            'pending_decision' => 0,
            'pass' => 0,
            'other_decision' => 0,
            'queued' => 0,
            'assigned' => 0,
            'applied' => 0,
            'missing_queue' => 0,
        ];

        if (! $this->db->tableExists('student_class') || ! $this->db->tableExists('students')) {
            return [
                'summary' => $summary,
                'rows' => [],
            ];
        }

        $hasEventOnly = $this->db->fieldExists('is_event_only', 'student_class');
        $hasActive = $this->db->fieldExists('is_active', 'students');
        $hasEnrollments = $this->db->tableExists('enrollments');
        $hasEnrollmentStatus = $hasEnrollments && $this->db->fieldExists('enrollment_status', 'enrollments');
        $hasEnrollmentWithdrawn = $hasEnrollments && $this->db->fieldExists('is_withdrawn', 'enrollments');
        $hasDob = $this->db->fieldExists('dob', 'students');
        $hasRegistrationGrade = $this->db->fieldExists('registration_grade', 'students');

        $builder = $this->db->table('student_class sc')
            ->select('sc.student_id, sc.class_section_id, sc.created_at, sc.updated_at')
            ->select('s.school_id, s.firstname, s.lastname')
            ->select('cs.class_section_name, cs.class_id, c.class_name')
            ->join('students s', 's.id = sc.student_id', 'inner')
            ->join('classSection cs', 'cs.class_section_id = sc.class_section_id', 'left')
            ->join('classes c', 'c.id = cs.class_id', 'left')
            ->where('sc.school_year', $schoolYear)
            ->where('sc.class_section_id IS NOT NULL', null, false);

        if ($hasDob) {
            $builder->select('s.dob');
        }

        if ($hasRegistrationGrade) {
            $builder->select('s.registration_grade');
        }

        if ($hasEventOnly) {
            $builder->groupStart()
                ->where('sc.is_event_only', 0)
                ->orWhere('sc.is_event_only', null)
                ->groupEnd();
        }

        if ($hasActive) {
            $builder->where('s.is_active', 1);
        }

        $this->excludeWithdrawnStudentsFromClosingBlockers(
            $builder,
            $schoolYear,
            $hasEnrollmentStatus,
            $hasEnrollmentWithdrawn
        );

        $assignmentRows = $builder
            ->orderBy('sc.student_id', 'ASC')
            ->orderBy('sc.updated_at', 'DESC')
            ->orderBy('sc.created_at', 'DESC')
            ->get()
            ->getResultArray();

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

            $students[$studentId] = [
                'student_id' => $studentId,
                'school_id' => (string) ($row['school_id'] ?? ''),
                'student_name' => trim((string) ($row['firstname'] ?? '') . ' ' . (string) ($row['lastname'] ?? '')),
                'class_section_id' => (int) ($row['class_section_id'] ?? 0),
                'class_id' => (int) ($row['class_id'] ?? 0),
                'class_name' => (string) ($row['class_name'] ?? ''),
                'class_section_name' => (string) ($row['class_section_name'] ?? ''),
                'dob' => (string) ($row['dob'] ?? ''),
                'registration_grade' => (string) ($row['registration_grade'] ?? ''),
                'auto_kg_pass' => false,
                'year_score' => null,
                'decision' => '',
                'normalized_decision' => null,
                'source' => 'missing',
                'notes' => '',
                'status' => 'missing',
                'queue_status' => '',
                'target_class' => '',
                'target_section' => '',
            ];
        }

        if ($students === []) {
            return [
                'summary' => $summary,
                'rows' => [],
            ];
        }

        $decisions = $this->promotionDecisionRows(array_keys($students), $schoolYear);
        $queueRows = $this->promotionQueueRows(array_keys($students), $schoolYear, $targetSchoolYear);
        foreach ($students as $studentId => &$student) {
            $decision = $decisions[$studentId] ?? null;
            if ($decision !== null) {
                $student['year_score'] = $decision['year_score'];
                $student['decision'] = $decision['decision'];
                $student['normalized_decision'] = $decision['normalized_decision'];
                $student['source'] = $decision['source'];
                $student['notes'] = $decision['notes'];
                $student['status'] = $decision['status'];
                if (($decision['class_section_name'] ?? '') !== '') {
                    $student['class_section_name'] = $decision['class_section_name'];
                }
            }

            if ($decision === null && $this->isKgStudent($student)) {
                $kgAgeStatus = $this->kgAgeStatusByTargetYearStartCutoff((string) $student['dob'], $targetSchoolYear);
                if ($kgAgeStatus === 'pass') {
                    $student['decision'] = 'Pass';
                    $student['normalized_decision'] = DeliberationDecision::PASSED;
                    $student['source'] = 'automatic_kg_age';
                    $student['notes'] = 'Auto-pass KG student: age 6 or older by Sep 1 of the next school year.';
                    $student['status'] = 'decided';
                    $student['auto_kg_pass'] = true;
                } elseif ($kgAgeStatus === 'keep_kg') {
                    $student['decision'] = 'Keep KG';
                    $student['normalized_decision'] = DeliberationDecision::REPEAT_CLASS;
                    $student['source'] = 'automatic_kg_age';
                    $student['notes'] = 'Auto-keep KG student: younger than 6 by Sep 1 of the next school year.';
                    $student['status'] = 'decided';
                }
            }

            $queue = $queueRows[$studentId] ?? null;
            if ($queue !== null) {
                $student['queue_status'] = $queue['status'];
                $student['target_class'] = $queue['target_class'];
                $student['target_section'] = $queue['target_section'];
            }

            if ($this->isKgStudent($student)) {
                $kgPlacement = $this->kgPlacementLabelByTargetYearStartCutoff((string) ($student['dob'] ?? ''), $targetSchoolYear);
                if ($kgPlacement !== '') {
                    $student['target_section'] = '';
                    $student['target_class'] = $kgPlacement;
                }
            }

            $summary['total_students']++;
            if ($student['status'] === 'missing') {
                $summary['missing_decision']++;
            } elseif ($student['status'] === 'pending') {
                $summary['pending_decision']++;
            } else {
                $summary['with_decision']++;
                if (($student['normalized_decision'] ?? null) === DeliberationDecision::PASSED) {
                    $summary['pass']++;
                    if ($student['target_class'] === '') {
                        $student['target_class'] = $this->nextClassLabelForPromotionPreview(
                            (string) ($student['class_name'] ?: $student['class_section_name']),
                            $targetSchoolYear
                        );
                    }
                    if ($queue === null && $student['auto_kg_pass'] !== true) {
                        $summary['missing_queue']++;
                    }
                } else {
                    if (($student['normalized_decision'] ?? null) === DeliberationDecision::REPEAT_CLASS) {
                        $student['target_section'] = '';
                        $student['target_class'] = $student['target_class'] !== ''
                                ? $student['target_class']
                                : $this->classOnlyLabel((string) ($student['class_name'] ?: $student['class_section_name']));
                    }
                    $summary['other_decision']++;
                }
            }

            if ($queue !== null && isset($summary[$queue['status']])) {
                $summary[$queue['status']]++;
            }
        }
        unset($student);

        $rows = array_values($students);
        usort($rows, static function (array $a, array $b): int {
            return [$a['class_section_name'], $a['student_name'], $a['student_id']]
                <=> [$b['class_section_name'], $b['student_name'], $b['student_id']];
        });

        return [
            'summary' => $summary,
            'rows' => $rows,
        ];
    }

    private function excludeWithdrawnStudentsFromClosingBlockers(
        $builder,
        string $schoolYear,
        bool $hasEnrollmentStatus,
        bool $hasEnrollmentWithdrawn
    ): void {
        if (! $hasEnrollmentStatus && ! $hasEnrollmentWithdrawn) {
            return;
        }

        $conditions = [];
        if ($hasEnrollmentStatus) {
            $inactiveStatuses = implode(',', array_map([$this->db, 'escape'], EnrollmentStatusService::INACTIVE_STATUSES));
            $conditions[] = 'LOWER(TRIM(e.enrollment_status)) IN (' . $inactiveStatuses . ')';
        }

        if ($hasEnrollmentWithdrawn) {
            $conditions[] = 'e.is_withdrawn = 1';
        }

        $builder->where(
            'NOT EXISTS (
                SELECT 1
                FROM enrollments e
                WHERE e.student_id = sc.student_id
                  AND e.school_year = ' . $this->db->escape($schoolYear) . '
                  AND (' . implode(' OR ', $conditions) . ')
            )',
            null,
            false
        );
    }

    private function isKgStudent(array $student): bool
    {
        foreach (['class_name', 'class_section_name'] as $field) {
            $value = strtoupper(trim((string) ($student[$field] ?? '')));
            if (preg_match('/(^|[^A-Z0-9])KG([^A-Z0-9]|$)/', $value) === 1 || str_contains($value, 'KINDERGARTEN')) {
                return true;
            }
        }

        return false;
    }

    private function kgAgeStatusByTargetYearStartCutoff(string $dob, ?string $targetSchoolYear): string
    {
        $dob = trim($dob);
        if ($dob === '') {
            return '';
        }

        try {
            $birthDate = new \DateTimeImmutable($dob);
            $cutoff = $this->septemberFirstCutoff($targetSchoolYear);
        } catch (\Throwable) {
            return '';
        }

        if ($birthDate > $cutoff) {
            return '';
        }

        $age = $birthDate->diff($cutoff)->y;
        if ($age >= 6) {
            return 'pass';
        }

        return 'keep_kg';
    }

    private function kgPlacementLabelByTargetYearStartCutoff(string $dob, ?string $targetSchoolYear): string
    {
        return match ($this->kgAgeStatusByTargetYearStartCutoff($dob, $targetSchoolYear)) {
            'pass' => '1',
            'keep_kg' => 'KG',
            default => '',
        };
    }

    private function septemberFirstCutoff(?string $targetSchoolYear): \DateTimeImmutable
    {
        if ($targetSchoolYear !== null && preg_match('/^(\d{4})-\d{4}$/', $targetSchoolYear, $matches) === 1) {
            return new \DateTimeImmutable($matches[1] . '-09-01');
        }

        $today = new \DateTimeImmutable('today');
        $cutoff = new \DateTimeImmutable($today->format('Y') . '-09-01');

        return $today <= $cutoff ? $cutoff : $cutoff->modify('+1 year');
    }

    private function promotionDecisionRows(array $studentIds, string $schoolYear): array
    {
        if (! $this->db->tableExists('student_decisions')) {
            return [];
        }

        $select = ['student_id', 'class_section_name', 'decision', 'source', 'notes'];
        $scoreField = null;
        if ($this->db->fieldExists('year_score', 'student_decisions')) {
            $scoreField = 'year_score';
        } elseif ($this->db->fieldExists('semester_score', 'student_decisions')) {
            $scoreField = 'semester_score';
        }

        if ($scoreField !== null) {
            $select[] = $scoreField . ' AS year_score';
        }

        $rows = $this->db->table('student_decisions')
            ->select($select)
            ->where('school_year', $schoolYear)
            ->whereIn('student_id', $studentIds)
            ->orderBy('updated_at', 'DESC')
            ->orderBy('id', 'DESC')
            ->get()
            ->getResultArray();

        $decisions = [];
        foreach ($rows as $row) {
            $studentId = (int) ($row['student_id'] ?? 0);
            if ($studentId <= 0 || isset($decisions[$studentId])) {
                continue;
            }

            $decision = trim((string) ($row['decision'] ?? ''));
            $source = trim((string) ($row['source'] ?? ''));
            $status = $decision === '' || $source === 'pending' ? 'pending' : 'decided';
            $normalizedDecision = DeliberationDecision::normalize($decision);

            $decisions[$studentId] = [
                'class_section_name' => (string) ($row['class_section_name'] ?? ''),
                'year_score' => is_numeric($row['year_score'] ?? null) ? round((float) $row['year_score'], 2) : null,
                'decision' => $decision,
                'normalized_decision' => $normalizedDecision,
                'source' => $source !== '' ? $source : ($status === 'pending' ? 'pending' : 'manual'),
                'notes' => (string) ($row['notes'] ?? ''),
                'status' => $status,
            ];
        }

        return $decisions;
    }

    private function promotionQueueRows(array $studentIds, string $sourceSchoolYear, ?string $targetSchoolYear): array
    {
        if (
            $targetSchoolYear === null
            || $targetSchoolYear === ''
            || ! $this->db->tableExists('promotion_queue')
        ) {
            return [];
        }

        $builder = $this->db->table('promotion_queue pq')
            ->select('pq.student_id, pq.to_class_id, pq.to_class_section_id, pq.status')
            ->select('c.class_name AS target_class')
            ->select('cs.class_section_name AS target_section')
            ->join('classes c', 'c.id = pq.to_class_id', 'left')
            ->join('classSection cs', 'cs.class_section_id = pq.to_class_section_id', 'left')
            ->where('pq.school_year_from', $sourceSchoolYear)
            ->where('pq.school_year_to', $targetSchoolYear)
            ->whereIn('pq.student_id', $studentIds)
            ->orderBy('pq.updated_at', 'DESC')
            ->orderBy('pq.id', 'DESC');

        $rows = $builder->get()->getResultArray();

        $queueRows = [];
        foreach ($rows as $row) {
            $studentId = (int) ($row['student_id'] ?? 0);
            if ($studentId <= 0 || isset($queueRows[$studentId])) {
                continue;
            }

            $targetClass = trim((string) ($row['target_class'] ?? ''));
            if ($targetClass === '' && (int) ($row['to_class_id'] ?? 0) > 0) {
                $targetClass = 'Class #' . (int) $row['to_class_id'];
            }

            $targetSection = trim((string) ($row['target_section'] ?? ''));
            if ($targetSection === '' && (int) ($row['to_class_section_id'] ?? 0) > 0) {
                $targetSection = 'Section #' . (int) $row['to_class_section_id'];
            }

            $queueRows[$studentId] = [
                'status' => (string) ($row['status'] ?? ''),
                'target_class' => $targetClass,
                'target_section' => $targetSection,
            ];
        }

        return $queueRows;
    }

    private function nextClassLabelForPromotionPreview(string $sourceClassName, ?string $targetSchoolYear): string
    {
        $base = $this->classBaseName($sourceClassName);
        $target = match (true) {
            $base === 'KG' || str_contains($base, 'KINDERGARTEN') => '1',
            ctype_digit($base) => (string) ((int) $base + 1),
            $base === 'YOUTH' => 'YOUTH',
            default => '',
        };

        if ($target === '') {
            return '';
        }

        if ($targetSchoolYear !== null && $targetSchoolYear !== '' && $this->db->tableExists('classes')) {
            $targetClass = $this->classByNameForPromotionPreview($target, $targetSchoolYear);
            if ($targetClass !== null) {
                return (string) ($targetClass['class_name'] ?? $target);
            }

            if (ctype_digit($base) && (int) $base >= 9) {
                $youthClass = $this->classByNameForPromotionPreview('YOUTH', $targetSchoolYear);
                if ($youthClass !== null) {
                    return (string) ($youthClass['class_name'] ?? 'YOUTH');
                }
            }
        }

        return $target;
    }

    private function classOnlyLabel(string $className): string
    {
        return trim((string) preg_replace('/-.+$/', '', $className));
    }

    private function classBaseName(string $className): string
    {
        $base = strtoupper(trim((string) preg_replace('/-.+$/', '', $className)));
        $base = preg_replace('/\b(CLASS|GRADE)\b/i', '', $base) ?? $base;
        $base = trim(preg_replace('/\s+/', ' ', $base) ?? $base);

        if (str_contains($base, 'KINDERGARTEN')) {
            return 'KG';
        }

        if (preg_match('/(^|[^A-Z0-9])KG([^A-Z0-9]|$)/', $base) === 1) {
            return 'KG';
        }

        if (preg_match('/\d+/', $base, $matches) === 1) {
            return (string) (int) $matches[0];
        }

        return $base;
    }

    private function classByNameForPromotionPreview(string $className, string $schoolYear): ?array
    {
        $builder = $this->db->table('classes')->where('UPPER(class_name)', strtoupper($className));
        if ($this->db->fieldExists('school_year', 'classes')) {
            $builder->where('school_year', $schoolYear);
        }

        $row = $builder->orderBy('id', 'DESC')->limit(1)->get()->getRowArray();
        if ($row !== null || ! $this->db->fieldExists('school_year', 'classes')) {
            return $row ?: null;
        }

        return $this->db->table('classes')
            ->where('UPPER(class_name)', strtoupper($className))
            ->orderBy('id', 'DESC')
            ->limit(1)
            ->get()
            ->getRowArray() ?: null;
    }

    private function countBySchoolYear(string $table, string $schoolYear, string $distinctField): int
    {
        if (! $this->db->tableExists($table) || ! $this->db->fieldExists('school_year', $table)) {
            return 0;
        }

        $row = $this->db->table($table)
            ->select("COUNT(DISTINCT {$distinctField}) AS total", false)
            ->where('school_year', $schoolYear)
            ->get()
            ->getRowArray();

        return (int) ($row['total'] ?? 0);
    }

    private function countFamilies(string $schoolYear): int
    {
        if ($this->db->tableExists('invoices')) {
            $row = $this->db->table('invoices')
                ->select('COUNT(DISTINCT parent_id) AS total', false)
                ->where('school_year', $schoolYear)
                ->get()
                ->getRowArray();

            return (int) ($row['total'] ?? 0);
        }

        return 0;
    }

    private function countMissingSchoolYearRows(string $table): int
    {
        if (! $this->db->tableExists($table) || ! $this->db->fieldExists('school_year', $table)) {
            return 0;
        }

        return $this->db->table($table)
            ->groupStart()
            ->where('school_year', null)
            ->orWhere('school_year', '')
            ->groupEnd()
            ->countAllResults();
    }

    private function latestOpenBatch(int $sourceYearId): ?array
    {
        return $this->batchModel
            ->where('source_school_year_id', $sourceYearId)
            ->whereIn('status', ['started', 'executed', 'completed'])
            ->orderBy('id', 'DESC')
            ->first();
    }

    private function assertClosingTablesExist(): void
    {
        foreach (['school_year_closing_batches', 'school_year_closing_items', 'school_year_transition_logs'] as $table) {
            if (! $this->db->tableExists($table)) {
                throw new RuntimeException('School year lifecycle tables are missing. Run database migrations before closing a school year.');
            }
        }
    }

    private function finding(string $severity, string $title, string $detail): array
    {
        return [
            'severity' => $severity,
            'title' => $title,
            'detail' => $detail,
        ];
    }

    private function hashPreview(array $preview): string
    {
        return hash('sha256', json_encode([
            'source_id' => $preview['source']['id'] ?? null,
            'target_id' => $preview['target']['id'] ?? null,
            'finance' => $preview['finance'],
            'promotion' => $preview['promotion'],
            'inventory' => $preview['inventory'] ?? [],
            'carry_forward' => $preview['carry_forward'],
            'blockers' => $preview['blockers'],
        ], JSON_UNESCAPED_SLASHES));
    }

    private function syncActiveYearSession(string $schoolYearName): void
    {
        if (session_status() !== PHP_SESSION_ACTIVE) {
            return;
        }

        session()->set('school_year', $schoolYearName);
        session()->remove('selected_school_year_id');
        session()->remove('selected_school_year');
    }
}