alrahma_sunday_school/app/Commands/SendMonthlyPaymentNotifications.php

<?php

namespace App\Commands;

use CodeIgniter\CLI\BaseCommand;
use CodeIgniter\CLI\CLI;
use App\Models\ConfigurationModel;
use App\Models\InvoiceModel;
use App\Models\PaymentModel;
use App\Models\PaymentNotificationLogModel;
use App\Models\UserModel;
use App\Models\UserRoleModel;
use App\Models\FamilyGuardianModel;
use App\Services\EmailService;
use App\Services\NotificationService;

class SendMonthlyPaymentNotifications extends BaseCommand
{
    protected $group       = 'Payments';
    protected $name        = 'payments:monthly-reminder';
    protected $description = 'Send monthly payment reminders on the first Saturday of every month.';

    protected EmailService $emailService;
    protected ConfigurationModel $configModel;
    protected InvoiceModel $invoiceModel;
    protected PaymentModel $paymentModel;
    protected PaymentNotificationLogModel $logModel;
    protected UserModel $userModel;
    protected FamilyGuardianModel $familyGuardianModel;

    public function run(array $params)
    {
        // Lazy init to avoid BaseCommand constructor issues during discovery
        $this->emailService        = new EmailService();
        $this->configModel         = new ConfigurationModel();
        $this->invoiceModel        = new InvoiceModel();
        $this->paymentModel        = new PaymentModel();
        $this->logModel            = new PaymentNotificationLogModel();
        $this->userModel           = new UserModel();
        $this->familyGuardianModel = new FamilyGuardianModel();

        // Parse params: --force, --email=addr, --type=no_payment|installment
        $force = false;
        $targetEmail = null;
        $targetType  = null;
        foreach ($params as $p) {
            if ($p === '--force') { $force = true; continue; }
            if (strpos($p, '--email=') === 0) { $targetEmail = trim(substr($p, 8)); continue; }
            if (strpos($p, '--type=') === 0)  { $targetType  = trim(substr($p, 7)); continue; }
        }
        // Also support CI4 options parser
        $optEmail = CLI::getOption('email');
        if ($optEmail !== null) $targetEmail = $optEmail;
        $optType  = CLI::getOption('type');
        if ($optType !== null)  $targetType  = $optType;
        if (CLI::getOption('force') !== null) $force = true;

        $tzName = (string) (config('School')->attendance['timezone'] ?? 'UTC');
        $tz = new \DateTimeZone($tzName);
        $now = new \DateTime('now', $tz);
        $year = (int) $now->format('Y');
        $month = (int) $now->format('n');

        if (!$targetEmail && !$force && !$this->isFirstSaturday($now)) {
            CLI::write('Not the first Saturday of the month. Use --force to override.', 'yellow');
            return;
        }

        $schoolYear = (string) ($this->configModel->getConfig('school_year') ?? $year);

        // Targeted test mode: only send to a specific email
        if ($targetEmail) {
            if (!filter_var($targetEmail, FILTER_VALIDATE_EMAIL)) {
                CLI::write('Invalid --email provided', 'red');
                return;
            }

            $userRow  = $this->userModel->where('email', $targetEmail)->first();
            $parentId = (int)($userRow['id'] ?? 0);

            $invRows = $parentId ? $this->invoiceModel->getAllInvoicesByUserIds([$parentId], $schoolYear) : [];
            $totalBalance = 0.0;
            $latestInvoiceId = null;
            foreach ($invRows as $ir) {
                $totalBalance += (float)($ir['balance'] ?? 0);
                if ($latestInvoiceId === null) {
                    $latestInvoiceId = (int) ($ir['id'] ?? 0);
                }
            }

            $type = in_array($targetType, ['no_payment','installment'], true) ? $targetType : 'no_payment';
            $ccEmail = $parentId ? $this->getSecondaryGuardianEmail($parentId) : null;

            [$subject, $body] = $this->composeEmail($parentId ?: 0, $schoolYear, $type, $totalBalance, $now);

            $sentOk = $this->emailService->send($targetEmail, $subject, $body, 'finance');
            if ($sentOk && $ccEmail && strcasecmp($ccEmail, $targetEmail) !== 0) {
                $this->emailService->send($ccEmail, $subject, $body, 'finance');
            }

            // Upsert log for this month
            $existing = $this->logModel->where('parent_id', $parentId)
                ->where('period_year', $year)
                ->where('period_month', $month)
                ->where('type', $type)
                ->first();

            $payload = [
                'parent_id'        => $parentId,
                'invoice_id'       => $latestInvoiceId,
                'school_year'      => $schoolYear,
                'period_year'      => $year,
                'period_month'     => $month,
                'type'             => $type,
                'to_email'         => $targetEmail,
                'cc_email'         => $ccEmail,
                'head_fa_notified' => 0,
                'subject'          => $subject,
                'body'             => $body,
                'status'           => $sentOk ? 'sent' : 'failed',
                'error_message'    => $sentOk ? null : 'Email send failed (see logs)',
                'balance_snapshot' => $totalBalance,
                'sent_at'          => $now->format('Y-m-d H:i:s'),
            ];

            if ($existing) {
                $this->logModel->update($existing['id'], $payload);
            } else {
                $this->logModel->insert($payload);
            }

            CLI::write(($sentOk ? 'Sent' : 'Failed') . " test reminder to {$targetEmail}", $sentOk ? 'green' : 'red');
            return;
        }

        // Get all parents with invoices for the current school year
        $db = \Config\Database::connect();
        $rows = $db->table('invoices')
            ->select('parent_id')
            ->where('school_year', $schoolYear)
            ->groupBy('parent_id')
            ->get()
            ->getResultArray();

        $parentIds = array_values(array_unique(array_map(static fn($r) => (int) $r['parent_id'], $rows)));
        if (empty($parentIds)) {
            CLI::write('No invoices found for current school year. Nothing to do.', 'yellow');
            return;
        }

        $sentCount = 0;
        foreach ($parentIds as $parentId) {
            // Compute total balance across invoices for this year
            $invRows = $this->invoiceModel->getAllInvoicesByUserIds([$parentId], $schoolYear);
            $totalBalance = 0.0;
            $latestInvoiceId = null;
            foreach ($invRows as $ir) {
                $totalBalance += (float)($ir['balance'] ?? 0);
                if ($latestInvoiceId === null) {
                    $latestInvoiceId = (int) ($ir['id'] ?? 0);
                }
            }

            if ($totalBalance <= 0.0) {
                continue; // up to date
            }

            // Determine if parent has any payments this school year
            $hasPayments = $db->table('payments')
                ->where('parent_id', $parentId)
                ->where('school_year', $schoolYear)
                ->countAllResults() > 0;

            $type = $hasPayments ? 'installment' : 'no_payment';

            // Idempotency guard: skip if already sent this period for this type
            if ($this->logModel->existsForPeriod($parentId, $year, $month, $type)) {
                continue;
            }

            // Recipient emails: primary guardian (current parent), CC second guardian in same family
            $toEmail = $this->getUserEmail($parentId);
            $ccEmail = $this->getSecondaryGuardianEmail($parentId);

            if (!$toEmail) {
                // Log failed attempt due to missing email and continue
                $this->logModel->insert([
                    'parent_id'        => $parentId,
                    'invoice_id'       => $latestInvoiceId,
                    'school_year'      => $schoolYear,
                    'period_year'      => $year,
                    'period_month'     => $month,
                    'type'             => $type,
                    'to_email'         => null,
                    'cc_email'         => $ccEmail,
                    'subject'          => 'Monthly Tuition Reminder',
                    'body'             => null,
                    'status'           => 'failed',
                    'error_message'    => 'No primary email on file',
                    'balance_snapshot' => $totalBalance,
                ]);
                CLI::write("Skipped parent {$parentId} due to missing email", 'yellow');
                continue;
            }

            // Compose email
            [$subject, $body] = $this->composeEmail($parentId, $schoolYear, $type, $totalBalance, $now);

            $sentOk = $this->emailService->send($toEmail, $subject, $body, 'finance');
            if ($sentOk && $ccEmail && strcasecmp($ccEmail, $toEmail) !== 0) {
                // Send a separate copy to the secondary guardian
                $this->emailService->send($ccEmail, $subject, $body, 'finance');
            }

            // Notify Head of Finance (in-app)
            $headFaUsers = $this->getHeadOfFinanceUsers();
            foreach ($headFaUsers as $u) {
                NotificationService::toUser((int)$u['id'], 'Payment Reminder Sent',
                    sprintf('A %s reminder was sent to parent #%d (balance: $%0.2f).', $type, $parentId, $totalBalance),
                    ['in_app']
                );
            }

            $this->logModel->insert([
                'parent_id'        => $parentId,
                'invoice_id'       => $latestInvoiceId,
                'school_year'      => $schoolYear,
                'period_year'      => $year,
                'period_month'     => $month,
                'type'             => $type,
                'to_email'         => $toEmail,
                'cc_email'         => $ccEmail,
                'head_fa_notified' => !empty($headFaUsers) ? 1 : 0,
                'subject'          => $subject,
                'body'             => $body,
                'status'           => $sentOk ? 'sent' : 'failed',
                'error_message'    => $sentOk ? null : 'Email send failed (see logs)',
                'balance_snapshot' => $totalBalance,
                'sent_at'          => $now->format('Y-m-d H:i:s'),
            ]);

            if ($sentOk) {
                $sentCount++;
                CLI::write("Reminder sent to {$toEmail}" . ($ccEmail ? ", CC {$ccEmail}" : ''), 'green');
            } else {
                CLI::write("Failed to send to {$toEmail}", 'red');
            }
        }

        // Summary to head of finance (in-app broadcast)
        $headFaUsers = $this->getHeadOfFinanceUsers();
        foreach ($headFaUsers as $u) {
            NotificationService::toUser((int)$u['id'], 'Monthly Payment Reminders Summary',
                sprintf('Total reminders sent this run: %d (School Year: %s).', $sentCount, $schoolYear),
                ['in_app']
            );
        }

        CLI::write("Done. Total sent: {$sentCount}", 'green');
    }

    private function isFirstSaturday(\DateTimeInterface $dt): bool
    {
        // Saturday = 6 (PHP: 0 Sun .. 6 Sat)
        $isSaturday = ((int)$dt->format('w')) === 6;
        $isFirstWeek = ((int)$dt->format('j')) <= 7;
        return $isSaturday && $isFirstWeek;
    }

    private function getUserEmail(int $userId): ?string
    {
        $u = $this->userModel->select('email')->find($userId);
        $email = $u['email'] ?? null;
        return $email && filter_var($email, FILTER_VALIDATE_EMAIL) ? $email : null;
    }

    private function getSecondaryGuardianEmail(int $primaryGuardianUserId): ?string
    {
        // Find family of primary guardian
        $row = $this->familyGuardianModel->where('user_id', $primaryGuardianUserId)->first();
        if (!$row || empty($row['family_id'])) {
            return null;
        }
        $familyId = (int)$row['family_id'];

        $others = $this->familyGuardianModel
            ->where('family_id', $familyId)
            ->where('user_id !=', $primaryGuardianUserId)
            ->where('receive_emails', 1)
            ->findAll();

        foreach ($others as $g) {
            $email = $this->getUserEmail((int)$g['user_id']);
            if ($email) return $email;
        }
        return null;
    }

    private function composeEmail(int $parentId, string $schoolYear, string $type, float $balance, \DateTimeInterface $now): array
    {
        $parent = $this->userModel->find($parentId) ?: [];
        $parentName = trim(($parent['firstname'] ?? '') . ' ' . ($parent['lastname'] ?? ''));
        $monthYear = $now->format('F Y');

        $subject = sprintf('Monthly Tuition Reminder — %s', $schoolYear);
        $greeting = $parentName !== '' ? "Dear {$parentName}," : 'Dear Parent,';
        $balanceFmt = '$' . number_format($balance, 2);

        if ($type === 'no_payment') {
            $intro = "We noticed there are outstanding tuition charges for {$schoolYear} but no payment has been recorded yet.";
        } else {
            $intro = "This is your monthly installment reminder for {$schoolYear}.";
        }

        // Compute remaining and max installments similar to Manual Payment UI
        $installmentEndRaw = (string)$this->configModel->getConfig('installment_date');
        $tz = new \DateTimeZone($tzName);
        $today = new \DateTimeImmutable('today', $tz);
        $end = null; try { if ($installmentEndRaw) { $end = new \DateTimeImmutable($installmentEndRaw, $tz); } } catch (\Throwable $e) { $end = null; }
        $remMonths = 0;
        if ($end) {
            $y = (int)$end->format('Y') - (int)$today->format('Y');
            $m = (int)$end->format('n') - (int)$today->format('n');
            $remMonths = $y * 12 + $m;
            if ((int)$end->format('j') > (int)$today->format('j')) $remMonths += 1;
            if ($remMonths < 0) $remMonths = 0;
        }
        $remainingInst = max(1, $remMonths);
        $maxInst = max(($remMonths ?: 0), ($balance > 0 ? 2 : 0));
        $instDueFmt = '$' . number_format($remainingInst > 0 ? ($balance / $remainingInst) : 0, 2);

        $bodyHtml = <<<HTML
            <div style="font-family:Arial,Helvetica,sans-serif; font-size:14px; color:#212529; line-height:1.5;">
              <h2 style="margin:0 0 10px; font-size:18px;">Monthly Tuition Reminder</h2>
              <p>{$greeting}</p>
              <p>{$intro}</p>
              <p><strong>Current Outstanding Balance:</strong> {$balanceFmt}</p>
              <ul>
                <li><strong>Remaining installments:</strong> {$remainingInst}</li>
                <li><strong>Suggested installment this month:</strong> {$instDueFmt}</li>
                <li><strong>Maximum installments available:</strong> {$maxInst}</li>
              </ul>
              <p>
                As a friendly reminder, our tuition installments are due at the beginning of each month.
                You can review your invoice and payment options by logging in to your parent portal.
              </p>
              <p>
                If you have any questions or need to arrange a different plan, please reply to this email.
              </p>
              <p>Thank you for your prompt attention.</p>
              <p><em>Reminder Period:</em> {$monthYear}</p>
            </div>
        HTML;

        $body = view('emails/_wrap_layout', [
            'title'     => 'Monthly Tuition Reminder',
            'body_html' => $bodyHtml,
        ], ['saveData' => true]);

        return [$subject, $body];
    }

    private function getHeadOfFinanceUsers(): array
    {
        // Try the explicit role label used in the UI first
        $heads = $this->userModel->getUsersByRole('head of department (finance)');
        if (!empty($heads)) return $heads;
        // fallback to accountant role if needed
        $acct = $this->userModel->getUsersByRole('accountant');
        return $acct ?: [];
    }
}