<?php

namespace App\Services\Finance;

use App\Models\Configuration;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class FinancialUnpaidParentsService
{
    public function __construct(private FinancialSchoolYearService $schoolYears) {}

    public function getUnpaidParents(?string $schoolYear): array
    {
        $schoolYear = trim((string) ($schoolYear ?? ''));
        if ($schoolYear === '') {
            $schoolYear = (string) (Configuration::getConfig('school_year') ?? date('Y'));
        }

        $schoolYears = $this->schoolYears->listYears($schoolYear);

        $invRows = DB::table('invoices as i')
            ->select('i.id', 'i.parent_id', 'i.total_amount', 'u.firstname', 'u.lastname', 'u.email')
            ->join('users as u', 'u.id', '=', 'i.parent_id')
            ->where('i.school_year', $schoolYear)
            ->orderBy('i.parent_id', 'ASC')
            ->orderBy('i.id', 'ASC')
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();

        $byParent = [];
        $hasStatus = Schema::hasColumn('payments', 'status');
        $hasVoid = Schema::hasColumn('payments', 'is_void');

        foreach ($invRows as $row) {
            $invoiceId = (int) ($row['id'] ?? 0);
            $parentId = (int) ($row['parent_id'] ?? 0);
            if ($invoiceId <= 0 || $parentId <= 0) {
                continue;
            }

            $paidQuery = DB::table('payments')
                ->selectRaw('COALESCE(SUM(paid_amount),0) AS tot')
                ->where('invoice_id', $invoiceId);
            if ($hasStatus) {
                $paidQuery->where(function ($q) {
                    $q->whereNotIn('status', [
                        'void', 'voided', 'refunded', 'failed', 'chargeback', 'declined', 'reversed', 'canceled', 'cancelled',
                    ])->orWhereNull('status');
                });
            }
            if ($hasVoid) {
                $paidQuery->where(function ($q) {
                    $q->where('is_void', 0)->orWhereNull('is_void');
                });
            }
            $paidRow = (array) $paidQuery->first();
            $paidSum = (float) ($paidRow['tot'] ?? 0);

            $discRow = (array) DB::table('discount_usages')
                ->selectRaw('COALESCE(SUM(discount_amount),0) AS tot')
                ->where('invoice_id', $invoiceId)
                ->first();
            $discSum = (float) ($discRow['tot'] ?? 0);

            $refRow = (array) DB::table('refunds')
                ->selectRaw('COALESCE(SUM(refund_paid_amount),0) AS tot')
                ->where('invoice_id', $invoiceId)
                ->whereIn('status', ['Partial', 'Paid'])
                ->first();
            $refSum = (float) ($refRow['tot'] ?? 0);

            $total = (float) ($row['total_amount'] ?? 0);
            $balance = max(0.0, round($total - $discSum - $paidSum - $refSum, 2));

            if (! isset($byParent[$parentId])) {
                $byParent[$parentId] = [
                    'parent_id' => $parentId,
                    'firstname' => (string) ($row['firstname'] ?? ''),
                    'lastname' => (string) ($row['lastname'] ?? ''),
                    'email' => (string) ($row['email'] ?? ''),
                    'total_invoice' => 0.0,
                    'total_balance' => 0.0,
                    'total_paid' => 0.0,
                    'total_discount' => 0.0,
                ];
            }

            $byParent[$parentId]['total_invoice'] += $total;
            $byParent[$parentId]['total_balance'] += $balance;
            $byParent[$parentId]['total_paid'] += $paidSum;
            $byParent[$parentId]['total_discount'] += $discSum;
        }

        $extraRows = DB::table('additional_charges as ac')
            ->selectRaw('ac.parent_id, u.firstname, u.lastname, u.email, COALESCE(SUM(ac.amount), 0) AS total_extra')
            ->leftJoin('users as u', 'u.id', '=', 'ac.parent_id')
            ->where('ac.school_year', $schoolYear)
            ->where('ac.status', '!=', 'void')
            ->where(function ($q) {
                $q->whereNull('ac.invoice_id')
                    ->orWhere('ac.invoice_id', 0)
                    ->orWhere('ac.status', '!=', 'applied');
            })
            ->groupBy('ac.parent_id')
            ->get()
            ->map(fn ($row) => (array) $row)
            ->all();

        foreach ($extraRows as $row) {
            $parentId = (int) ($row['parent_id'] ?? 0);
            if ($parentId <= 0) {
                continue;
            }
            $extra = (float) ($row['total_extra'] ?? 0);
            if (abs($extra) < 0.00001) {
                continue;
            }
            if (! isset($byParent[$parentId])) {
                $byParent[$parentId] = [
                    'parent_id' => $parentId,
                    'firstname' => (string) ($row['firstname'] ?? ''),
                    'lastname' => (string) ($row['lastname'] ?? ''),
                    'email' => (string) ($row['email'] ?? ''),
                    'total_invoice' => 0.0,
                    'total_balance' => 0.0,
                    'total_paid' => 0.0,
                    'total_discount' => 0.0,
                ];
            }
            $byParent[$parentId]['total_invoice'] += $extra;
            $byParent[$parentId]['total_balance'] += $extra;
        }

        $rows = [];
        $installmentEndRaw = (string) (Configuration::getConfig('installment_date') ?? '');
        $tzName = function_exists('user_timezone') ? (string) user_timezone() : config('app.timezone', 'UTC');
        $tz = new \DateTimeZone($tzName);
        $today = new \DateTimeImmutable('today', $tz);

        $endDate = null;
        if ($installmentEndRaw !== '') {
            try {
                $endDate = new \DateTimeImmutable($installmentEndRaw, $tz);
            } catch (\Throwable $e) {
                $endDate = null;
            }
        }

        $monthsUntil = static function (?\DateTimeImmutable $end) use ($today): int {
            if (! $end) {
                return 0;
            }
            $y = (int) $end->format('Y') - (int) $today->format('Y');
            $m = (int) $end->format('n') - (int) $today->format('n');
            $months = $y * 12 + $m;
            if ((int) $end->format('j') > (int) $today->format('j')) {
                $months += 1;
            }

            return max(0, $months);
        };

        foreach ($byParent as $agg) {
            $balance = (float) ($agg['total_balance'] ?? 0);
            if ($balance <= 0.00001) {
                continue;
            }
            $remMonths = $monthsUntil($endDate);
            $remainingInstallments = max(1, $remMonths);
            $instAmount = round($balance / $remainingInstallments, 2);

            $rows[] = [
                'parent_id' => (int) ($agg['parent_id'] ?? 0),
                'firstname' => (string) ($agg['firstname'] ?? ''),
                'lastname' => (string) ($agg['lastname'] ?? ''),
                'email' => (string) ($agg['email'] ?? ''),
                'total_invoice' => (float) ($agg['total_invoice'] ?? 0),
                'total_balance' => $balance,
                'total_discount' => (float) ($agg['total_discount'] ?? 0),
                'remaining_installments' => $remainingInstallments,
                'installment_amount' => $instAmount,
            ];
        }

        usort($rows, static fn ($a, $b) => $b['total_balance'] <=> $a['total_balance']);

        $nextInstallment = (new \DateTime('now', $tz))
            ->modify('first day of next month')
            ->format('Y-m-d');

        $parentIds = array_values(array_unique(array_map(static fn ($row) => (int) ($row['parent_id'] ?? 0), $rows)));
        $hasPayments = [];
        $paidTotals = [];
        $paymentCounts = [];
        if (! empty($parentIds)) {
            $pRows = DB::table('payments')
                ->selectRaw('parent_id, SUM(paid_amount) AS total_paid, COUNT(*) AS payment_count')
                ->whereIn('parent_id', $parentIds)
                ->where('school_year', $schoolYear)
                ->groupBy('parent_id')
                ->get()
                ->map(fn ($row) => (array) $row)
                ->all();

            foreach ($pRows as $row) {
                $parentId = (int) ($row['parent_id'] ?? 0);
                if ($parentId > 0) {
                    $hasPayments[$parentId] = true;
                    $paidTotals[$parentId] = (float) ($row['total_paid'] ?? 0);
                    $paymentCounts[$parentId] = (int) ($row['payment_count'] ?? 0);
                }
            }
        }

        $results = array_map(function (array $row) use ($hasPayments, $paidTotals, $paymentCounts, $nextInstallment) {
            $parentId = (int) ($row['parent_id'] ?? 0);
            $name = trim((string) ($row['firstname'] ?? '').' '.(string) ($row['lastname'] ?? ''));

            return [
                'parent_id' => $parentId,
                'parent_name' => $name,
                'email' => (string) ($row['email'] ?? ''),
                'total_invoice' => (float) ($row['total_invoice'] ?? 0),
                'total_balance' => (float) ($row['total_balance'] ?? 0),
                'total_discount' => (float) ($row['total_discount'] ?? 0),
                'remaining_installments' => (int) ($row['remaining_installments'] ?? 0),
                'installment_amount' => (float) ($row['installment_amount'] ?? 0),
                'type' => isset($hasPayments[$parentId]) ? 'installment' : 'no_payment',
                'total_paid' => isset($paidTotals[$parentId]) ? (float) $paidTotals[$parentId] : 0.0,
                'payment_count' => isset($paymentCounts[$parentId]) ? (int) $paymentCounts[$parentId] : 0,
                'has_installment' => isset($hasPayments[$parentId]) ? 1 : 0,
                'next_installment' => $nextInstallment,
            ];
        }, $rows);

        return [
            'schoolYear' => $schoolYear,
            'schoolYears' => $schoolYears,
            'results' => $results,
        ];
    }
}