alrahma_sunday_school/app/Commands/WithdrawalInventoryAudit.php

<?php

namespace App\Commands;

use CodeIgniter\CLI\BaseCommand;
use CodeIgniter\CLI\CLI;

/** Read-only audit for withdrawal refund posting prerequisites. */
class WithdrawalInventoryAudit extends BaseCommand
{
    protected $group = 'Financial';
    protected $name = 'financial:withdrawal-inventory-audit';
    protected $description = 'Audit withdrawal policy, book inventory evidence, invoices, and open refunds without changing data.';
    protected $usage = 'php spark financial:withdrawal-inventory-audit --school-year=2025-2026 [--json]';
    protected $options = ['--school-year' => 'Required school year.', '--json' => 'Print JSON.'];

    public function run(array $params)
    {
        $db = \Config\Database::connect();
        $year = trim((string) (CLI::getOption('school-year') ?? ''));
        if ($year === '') {
            CLI::error('--school-year is required; the audit will not guess an active year.');
            return;
        }
        $checks = [];
        $policy = $db->table('school_years')->where('name', $year)->get(1)->getRowArray();
        $configWeeksRow = $db->table('configuration')
            ->select('config_value')
            ->where('config_key', 'total_instructional_weeks')
            ->orderBy('id', 'DESC')
            ->get(1)
            ->getRowArray();
        $configWeeks = filter_var($configWeeksRow['config_value'] ?? null, FILTER_VALIDATE_INT);
        $checks['policy'] = $policy !== null
            && $configWeeks !== false
            && $configWeeks > 0
            && (int) ($policy['annual_fee_includes_books'] ?? 0) === 1
            ? $this->check('pass', 'School-year refund inputs are present.')
            : $this->check('blocking', 'Configuration total_instructional_weeks or book-inclusive policy is missing.');

        $missingPrices = $db->table('inventory_item_years iy')
            ->join('inventory_items i', 'i.id = iy.inventory_item_id', 'inner')
            ->where('iy.school_year', $year)->where('i.type', 'book')
            ->groupStart()->where('iy.charge_price_cents <=', 0)->orWhere('iy.price_confirmed !=', 1)->groupEnd()
            ->countAllResults();
        $checks['book_prices'] = $missingPrices === 0 ? $this->check('pass', 'All book-year prices are confirmed.') : $this->check('blocking', $missingPrices . ' book-year price(s) are missing or unconfirmed.');

        $catalogRows = $db->table('inventory_items')->select('id, name, isbn, edition, sku, author')->where('type', 'book')->get()->getResultArray();
        $identityCounts = [];
        $skuCounts = [];
        foreach ($catalogRows as $catalogRow) {
            $isbn = strtoupper((string) preg_replace('/[^0-9X]/i', '', (string) ($catalogRow['isbn'] ?? '')));
            $edition = strtolower(trim((string) ($catalogRow['edition'] ?? '')));
            $sku = strtolower(trim((string) ($catalogRow['sku'] ?? '')));
            if ($isbn !== '') {
                $identityCounts[$isbn . '|' . $edition] = ($identityCounts[$isbn . '|' . $edition] ?? 0) + 1;
            }
            if ($sku !== '') {
                $skuCounts[$sku] = ($skuCounts[$sku] ?? 0) + 1;
            }
        }
        $duplicateCount = count(array_filter($identityCounts, static fn (int $count): bool => $count > 1))
            + count(array_filter($skuCounts, static fn (int $count): bool => $count > 1));
        $checks['duplicate_catalog'] = $duplicateCount === 0 ? $this->check('pass', 'No duplicate normalized ISBN/edition or SKU keys found.') : $this->check('blocking', $duplicateCount . ' duplicate normalized ISBN/edition or SKU group(s) require reconciliation.');

        $missingAuthorCount = count(array_filter($catalogRows, static fn (array $row): bool => trim((string) ($row['author'] ?? '')) === ''));
        $checks['catalog_author'] = $missingAuthorCount === 0
            ? $this->check('pass', 'All book catalog rows have an author.')
            : $this->check('warning', $missingAuthorCount . ' book catalog row(s) have no author.');
        $unassignedBooks = $db->table('inventory_items i')
            ->join('inventory_book_class_assignments a', 'a.inventory_item_id = i.id AND a.school_year = ' . $db->escape($year), 'left')
            ->where('i.type', 'book')->where('i.is_active', 1)->where('a.id', null)->countAllResults();
        $checks['book_class_assignments'] = $unassignedBooks === 0
            ? $this->check('pass', 'All active books have explicit year/class assignments.')
            : $this->check('warning', $unassignedBooks . ' active book(s) rely on category fallback instead of explicit class assignments.');

        $malformedYearTags = $db->table('inventory_movements')->where('school_year IS NOT NULL', null, false)
            ->where("school_year NOT REGEXP '^[0-9]{4}-[0-9]{4}$'", null, false)->countAllResults();
        $checks['movement_year_tags'] = $malformedYearTags === 0
            ? $this->check('pass', 'Inventory movement year tags are well formed.')
            : $this->check('warning', $malformedYearTags . ' inventory movement(s) have malformed school-year tags.');

        $legacyDistributions = $db->table('inventory_movements im')
            ->join('student_book_issues sbi', 'sbi.distribution_movement_id = im.id', 'left')
            ->where('im.school_year', $year)->where('im.movement_type', 'distribution')->where('sbi.id', null)
            ->countAllResults();
        $checks['legacy_distributions'] = $legacyDistributions === 0 ? $this->check('pass', 'Every distribution has a price-snapshotted issue.') : $this->check('blocking', $legacyDistributions . ' distribution movement(s) lack student issue evidence and cannot be priced automatically.');

        $positiveStudentMovements = $db->table('inventory_movements')
            ->where('school_year', $year)->where('student_id IS NOT NULL', null, false)->where('qty_change >', 0)
            ->groupStart()->where('source_type !=', 'student_book_issue_reversal')->orWhere('source_type', null)->groupEnd()->countAllResults();
        $checks['possible_returns'] = $positiveStudentMovements === 0 ? $this->check('pass', 'No unclassified positive student book movements found.') : $this->check('blocking', $positiveStudentMovements . ' positive student movement(s) look like legacy returns/corrections and require manual classification.');

        $duplicateOpenings = $db->table('inventory_movements')
            ->select('item_id')->where('school_year', $year)->where('movement_type', 'initial')->groupBy('item_id')->having('COUNT(*) > 1', null, false)->countAllResults();
        $checks['duplicate_openings'] = $duplicateOpenings === 0 ? $this->check('pass', 'No duplicate legacy opening movements found.') : $this->check('blocking', $duplicateOpenings . ' book/item(s) have duplicate opening movements.');

        $quantityMismatches = $db->query(
            "SELECT COUNT(*) AS total FROM (
                SELECT i.id
                FROM inventory_items i
                JOIN inventory_item_years iy ON iy.inventory_item_id = i.id AND iy.school_year = ?
                LEFT JOIN inventory_movements im ON im.item_year_id = iy.id AND im.status IN ('posted','reversed')
                WHERE i.type = 'book'
                GROUP BY i.id, i.quantity, iy.opening_quantity
                HAVING i.quantity != iy.opening_quantity + COALESCE(SUM(im.qty_change), 0)
            ) mismatches",
            [$year]
        )->getRowArray();
        $quantityMismatchCount = (int) ($quantityMismatches['total'] ?? 0);
        $checks['quantity_projection'] = $quantityMismatchCount === 0
            ? $this->check('pass', 'Book catalog quantities match the year movement ledger.')
            : $this->check('blocking', $quantityMismatchCount . ' book quantity projection(s) disagree with the year movement ledger.');

        $legacyRefunds = $db->table('refunds')->where('school_year', $year)
            ->whereIn('status', ['Pending', 'pending', 'requested', 'Approved', 'approved', 'Partial', 'partial', 'partially_paid'])
            ->groupStart()->where('withdrawal_calculation_id', null)->orWhere('withdrawal_calculation_id', 0)->groupEnd()
            ->groupStart()->where('source_type', 'tuition_withdrawal')->orLike('reason', 'Withdrawal')->groupEnd()->countAllResults();
        $checks['legacy_refunds'] = $legacyRefunds === 0 ? $this->check('pass', 'Open withdrawal refunds have calculation links.') : $this->check('blocking', $legacyRefunds . ' open withdrawal refund(s) have no versioned calculation.');

        $comparisonRows = $db->query(
            "SELECT r.id AS refund_id, r.invoice_id, r.refund_amount,
                    r.requested_amount_cents AS stored_requested_cents,
                    wfc.id AS calculation_id, wfc.new_refund_request_cents AS calculated_requested_cents,
                    wfc.status AS calculation_status
               FROM refunds r
          LEFT JOIN withdrawal_financial_calculations wfc ON wfc.id = r.withdrawal_calculation_id
              WHERE r.school_year = ?
                AND (r.source_type = 'tuition_withdrawal' OR r.reason LIKE '%Withdrawal%')
                AND LOWER(COALESCE(r.status,'')) IN ('pending','requested','approved','partial','partially_paid')
           ORDER BY r.id",
            [$year]
        )->getResultArray();
        $comparisonDifferences = 0;
        foreach ($comparisonRows as &$comparison) {
            $storedCents = (int) ($comparison['stored_requested_cents'] ?? 0);
            if ($storedCents === 0) {
                $storedCents = (int) round(((float) ($comparison['refund_amount'] ?? 0)) * 100, 0, PHP_ROUND_HALF_UP);
            }
            $comparison['stored_requested_cents'] = $storedCents;
            $comparison['difference_cents'] = $comparison['calculation_id'] === null
                ? null
                : $storedCents - (int) ($comparison['calculated_requested_cents'] ?? 0);
            if ($comparison['difference_cents'] !== null && $comparison['difference_cents'] !== 0) {
                $comparisonDifferences++;
            }
        }
        unset($comparison);
        $checks['refund_comparison'] = $comparisonDifferences === 0
            ? $this->check('pass', 'Linked open refund requests match their versioned calculations.')
            : $this->check('blocking', $comparisonDifferences . ' linked open refund request(s) differ from their calculation snapshot.');

        $duplicateInvoices = $db->query(
            "SELECT COUNT(*) AS total FROM (
                SELECT parent_id FROM invoices WHERE school_year = ?
                AND LOWER(COALESCE(status,'')) NOT IN ('void','voided','cancelled','canceled')
                GROUP BY parent_id HAVING COUNT(*) > 1
            ) duplicates",
            [$year]
        )->getRowArray();
        $duplicateInvoiceCount = (int) ($duplicateInvoices['total'] ?? 0);
        $checks['duplicate_invoices'] = $duplicateInvoiceCount === 0 ? $this->check('pass', 'No parent has multiple active invoices.') : $this->check('blocking', $duplicateInvoiceCount . ' parent(s) have multiple active invoices and require review.');

        $blocking = count(array_filter($checks, static fn (array $check): bool => $check['severity'] === 'blocking'));
        $report = ['school_year' => $year, 'generated_at' => date('Y-m-d H:i:s'), 'ready_to_enable' => $blocking === 0, 'blocking_count' => $blocking, 'checks' => $checks, 'open_refund_comparison' => $comparisonRows];
        if (CLI::getOption('json') !== null) {
            CLI::write(json_encode($report, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES));
            return;
        }
        CLI::write('Withdrawal and inventory audit for ' . $year, 'yellow');
        foreach ($checks as $name => $check) {
            $color = $check['severity'] === 'blocking' ? 'red' : ($check['severity'] === 'warning' ? 'yellow' : 'green');
            CLI::write(strtoupper($check['severity']) . ' ' . $name . ': ' . $check['message'], $color);
        }
        CLI::write($blocking === 0 ? 'READY TO ENABLE' : 'DO NOT ENABLE — blockers remain', $blocking === 0 ? 'green' : 'red');
    }

    private function check(string $severity, string $message): array
    {
        return ['severity' => $severity, 'message' => $message];
    }
}