<?php

namespace App\Services\Finance;

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

class FinancialSummaryService
{
    public function __construct(
        private FinancialDonationRecipientService $donationRecipients
    ) {}

    public function getSummary(?string $dateFrom, ?string $dateTo, ?string $schoolYear): array
    {
        $schoolYear = $schoolYear ?: (Configuration::getConfig('school_year') ?? date('Y'));
        $derivedDateFrom = null;
        $derivedDateTo = null;

        if (! empty($schoolYear) && empty($dateFrom) && empty($dateTo)) {
            if (preg_match('/^(\\d{4})[^\\d]?(\\d{4})$/', $schoolYear, $m)) {
                $derivedDateFrom = $m[1].'-01-01';
                $derivedDateTo = $m[2].'-12-31';
            }
        }

        $invoiceDateFrom = $dateFrom ?: $derivedDateFrom;
        $invoiceDateTo = $dateTo ?: $derivedDateTo;

        $invoiceQuery = DB::table('invoices')->where('school_year', $schoolYear);
        if (! empty($invoiceDateFrom)) {
            $invoiceQuery->whereRaw('DATE(COALESCE(issue_date, created_at)) >= ?', [$invoiceDateFrom]);
        }
        if (! empty($invoiceDateTo)) {
            $invoiceQuery->whereRaw('DATE(COALESCE(issue_date, created_at)) <= ?', [$invoiceDateTo]);
        }
        $invoices = $invoiceQuery->get()->map(fn ($row) => (array) $row)->all();
        $totalCharges = array_sum(array_map(static fn ($row) => (float) ($row['total_amount'] ?? 0), $invoices));

        $invoiceIds = array_values(array_unique(array_filter(array_map(static function ($row) {
            $id = (int) ($row['id'] ?? 0);

            return $id > 0 ? $id : null;
        }, $invoices))));

        $totalExtraCharges = $this->sumAdditionalCharges($schoolYear, $dateFrom, $dateTo);
        $extraByParent = $this->extraChargesByParent($schoolYear, $dateFrom, $dateTo);
        $extraChargesUnapplied = array_sum($extraByParent);
        $totalCharges += $extraChargesUnapplied;

        $totalPaid = $this->sumPayments($schoolYear, $invoiceDateFrom, $invoiceDateTo);

        $paidByInvoice = [];
        $discountByInvoice = [];
        $refundByInvoice = [];
        if (! empty($invoiceIds)) {
            $paidByInvoice = $this->paymentsByInvoiceIds($invoiceIds, $schoolYear, $invoiceDateFrom, $invoiceDateTo);
            $discountByInvoice = $this->discountsByInvoiceIds($invoiceIds, $dateFrom, $dateTo);
            $refundByInvoice = $this->refundsByInvoiceIds($invoiceIds, $dateFrom, $dateTo);
        }

        $totalExpenses = $this->sumExpenses($schoolYear, $invoiceDateFrom, $invoiceDateTo);
        $totalReimbursements = $this->sumReimbursements($schoolYear, $invoiceDateFrom, $invoiceDateTo);

        $donationToSchool = $this->sumDonationToSchool($schoolYear, $invoiceDateFrom, $invoiceDateTo);
        $totalReimbursements = max(0.0, $totalReimbursements - $donationToSchool['donationReimb']);

        $totalRefunds = $this->sumRefunds($schoolYear, $dateFrom, $dateTo);
        $totalDiscounts = $this->sumDiscounts($schoolYear, $dateFrom, $dateTo);

        $totalUnpaid = $this->sumUnpaidBalances($invoices, $paidByInvoice, $discountByInvoice, $refundByInvoice, $extraByParent);

        $amountCollected = $totalPaid;
        $netAmount = $totalCharges - $totalDiscounts - $totalRefunds;

        return [
            'schoolYear' => $schoolYear,
            'dateFrom' => $dateFrom,
            'dateTo' => $dateTo,
            'totalCharges' => $totalCharges,
            'totalExtraCharges' => $totalExtraCharges,
            'totalDiscounts' => $totalDiscounts,
            'totalRefunds' => $totalRefunds,
            'totalExpenses' => $totalExpenses,
            'totalReimbursements' => $totalReimbursements,
            'donationToSchool' => $donationToSchool['donationToSchool'],
            'totalPaid' => $totalPaid,
            'amountCollected' => $amountCollected,
            'totalUnpaid' => $totalUnpaid,
            'netAmount' => $netAmount,
        ];
    }

    private function sumAdditionalCharges(string $schoolYear, ?string $dateFrom, ?string $dateTo): float
    {
        $query = DB::table('additional_charges as ac')
            ->selectRaw('COALESCE(SUM(ac.amount), 0) AS amount')
            ->where('ac.school_year', $schoolYear)
            ->where('ac.status', '!=', 'void');

        if (! empty($dateFrom)) {
            $query->whereRaw('DATE(ac.due_date) >= ?', [$dateFrom]);
        }
        if (! empty($dateTo)) {
            $query->whereRaw('DATE(ac.due_date) <= ?', [$dateTo]);
        }

        $row = (array) $query->first();

        return (float) ($row['amount'] ?? 0);
    }

    private function extraChargesByParent(string $schoolYear, ?string $dateFrom, ?string $dateTo): array
    {
        $query = DB::table('additional_charges as ac')
            ->selectRaw('ac.parent_id, COALESCE(SUM(ac.amount), 0) AS amount')
            ->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');

        if (! empty($dateFrom)) {
            $query->whereRaw('DATE(ac.due_date) >= ?', [$dateFrom]);
        }
        if (! empty($dateTo)) {
            $query->whereRaw('DATE(ac.due_date) <= ?', [$dateTo]);
        }

        $rows = $query->get()->map(fn ($row) => (array) $row)->all();
        $extraByParent = [];
        foreach ($rows as $row) {
            $parentId = (int) ($row['parent_id'] ?? 0);
            $amount = (float) ($row['amount'] ?? 0);
            if ($parentId > 0 && abs($amount) > 0.00001) {
                $extraByParent[$parentId] = $amount;
            }
        }

        return $extraByParent;
    }

    private function sumPayments(string $schoolYear, ?string $dateFrom, ?string $dateTo): float
    {
        $query = DB::table('payments')->where('school_year', $schoolYear);
        if (! empty($dateFrom)) {
            $query->whereRaw('DATE(payment_date) >= ?', [$dateFrom]);
        }
        if (! empty($dateTo)) {
            $query->whereRaw('DATE(payment_date) <= ?', [$dateTo]);
        }

        $exclude = ['void', 'voided', 'refunded', 'failed', 'chargeback', 'declined', 'reversed', 'canceled', 'cancelled'];
        if (Schema::hasColumn('payments', 'status')) {
            $query->where(function ($q) use ($exclude) {
                $q->whereNotIn('status', $exclude)->orWhereNull('status');
            });
        }
        if (Schema::hasColumn('payments', 'is_void')) {
            $query->where(function ($q) {
                $q->where('is_void', 0)->orWhereNull('is_void');
            });
        }

        $row = (array) $query->selectRaw('COALESCE(SUM(paid_amount), 0) AS paid_amount')->first();

        return (float) ($row['paid_amount'] ?? 0);
    }

    private function paymentsByInvoiceIds(array $invoiceIds, string $schoolYear, ?string $dateFrom, ?string $dateTo): array
    {
        $query = DB::table('payments')
            ->selectRaw('invoice_id, COALESCE(SUM(paid_amount), 0) AS total_paid')
            ->whereIn('invoice_id', $invoiceIds)
            ->where('school_year', $schoolYear);

        if (! empty($dateFrom)) {
            $query->whereRaw('DATE(payment_date) >= ?', [$dateFrom]);
        }
        if (! empty($dateTo)) {
            $query->whereRaw('DATE(payment_date) <= ?', [$dateTo]);
        }

        $exclude = ['void', 'voided', 'refunded', 'failed', 'chargeback', 'declined', 'reversed', 'canceled', 'cancelled'];
        if (Schema::hasColumn('payments', 'status')) {
            $query->where(function ($q) use ($exclude) {
                $q->whereNotIn('status', $exclude)->orWhereNull('status');
            });
        }
        if (Schema::hasColumn('payments', 'is_void')) {
            $query->where(function ($q) {
                $q->where('is_void', 0)->orWhereNull('is_void');
            });
        }

        $rows = $query->groupBy('invoice_id')->get()->map(fn ($row) => (array) $row)->all();
        $map = [];
        foreach ($rows as $row) {
            $invoiceId = (int) ($row['invoice_id'] ?? 0);
            if ($invoiceId > 0) {
                $map[$invoiceId] = (float) ($row['total_paid'] ?? 0);
            }
        }

        return $map;
    }

    private function discountsByInvoiceIds(array $invoiceIds, ?string $dateFrom, ?string $dateTo): array
    {
        $query = DB::table('discount_usages')
            ->selectRaw('invoice_id, COALESCE(SUM(discount_amount),0) AS total_disc')
            ->whereIn('invoice_id', $invoiceIds);

        if (! empty($dateFrom)) {
            $query->whereRaw('DATE(COALESCE(used_at, created_at)) >= ?', [$dateFrom]);
        }
        if (! empty($dateTo)) {
            $query->whereRaw('DATE(COALESCE(used_at, created_at)) <= ?', [$dateTo]);
        }

        $rows = $query->groupBy('invoice_id')->get()->map(fn ($row) => (array) $row)->all();
        $map = [];
        foreach ($rows as $row) {
            $invoiceId = (int) ($row['invoice_id'] ?? 0);
            if ($invoiceId > 0) {
                $map[$invoiceId] = (float) ($row['total_disc'] ?? 0);
            }
        }

        return $map;
    }

    private function refundsByInvoiceIds(array $invoiceIds, ?string $dateFrom, ?string $dateTo): array
    {
        $query = DB::table('refunds')
            ->selectRaw('invoice_id, COALESCE(SUM(refund_paid_amount),0) AS total_refund')
            ->whereIn('invoice_id', $invoiceIds)
            ->whereIn('status', ['Partial', 'Paid']);

        if (! empty($dateFrom)) {
            $query->whereRaw('DATE(COALESCE(refunded_at, created_at)) >= ?', [$dateFrom]);
        }
        if (! empty($dateTo)) {
            $query->whereRaw('DATE(COALESCE(refunded_at, created_at)) <= ?', [$dateTo]);
        }

        $rows = $query->groupBy('invoice_id')->get()->map(fn ($row) => (array) $row)->all();
        $map = [];
        foreach ($rows as $row) {
            $invoiceId = (int) ($row['invoice_id'] ?? 0);
            if ($invoiceId > 0) {
                $map[$invoiceId] = (float) ($row['total_refund'] ?? 0);
            }
        }

        return $map;
    }

    private function sumExpenses(string $schoolYear, ?string $dateFrom, ?string $dateTo): float
    {
        $query = DB::table('expenses')->where('school_year', $schoolYear);
        if (! empty($dateFrom)) {
            $query->whereRaw('DATE(created_at) >= ?', [$dateFrom]);
        }
        if (! empty($dateTo)) {
            $query->whereRaw('DATE(created_at) <= ?', [$dateTo]);
        }

        $row = (array) $query->selectRaw('COALESCE(SUM(amount), 0) AS amount')->first();

        return (float) ($row['amount'] ?? 0);
    }

    private function sumReimbursements(string $schoolYear, ?string $dateFrom, ?string $dateTo): float
    {
        $normalizedYear = preg_replace('/[^0-9]/', '', (string) $schoolYear);

        $query = DB::table('reimbursements as r')
            ->selectRaw('COALESCE(SUM(r.amount),0) AS amount')
            ->leftJoin('expenses as e', 'e.id', '=', 'r.expense_id');

        if (! empty($schoolYear)) {
            $query->where(function ($q) use ($schoolYear, $normalizedYear) {
                $q->where(function ($q2) use ($schoolYear, $normalizedYear) {
                    $q2->where('r.school_year', $schoolYear)
                        ->orWhereRaw(
                            "REPLACE(REPLACE(REPLACE(REPLACE(r.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '') = ?",
                            [$normalizedYear]
                        );
                })->orWhere(function ($q2) use ($schoolYear, $normalizedYear) {
                    $q2->whereNull('r.school_year')
                        ->where(function ($q3) use ($schoolYear, $normalizedYear) {
                            $q3->where('e.school_year', $schoolYear)
                                ->orWhereRaw(
                                    "REPLACE(REPLACE(REPLACE(REPLACE(e.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '') = ?",
                                    [$normalizedYear]
                                );
                        });
                });
            });
        }

        if (! empty($dateFrom)) {
            $query->whereRaw('DATE(COALESCE(r.created_at, e.created_at)) >= ?', [$dateFrom]);
        }
        if (! empty($dateTo)) {
            $query->whereRaw('DATE(COALESCE(r.created_at, e.created_at)) <= ?', [$dateTo]);
        }

        $row = (array) $query->first();
        $total = (float) ($row['amount'] ?? 0);

        $batchFallback = DB::table('reimbursement_batch_items as bi')
            ->selectRaw('COALESCE(SUM(e.amount),0) AS amount')
            ->join('reimbursement_batches as b', 'b.id', '=', 'bi.batch_id')
            ->join('expenses as e', 'e.id', '=', 'bi.expense_id')
            ->leftJoin('reimbursements as r', 'r.expense_id', '=', 'e.id')
            ->where('b.status', 'closed')
            ->whereNull('bi.unassigned_at')
            ->whereNull('r.id');

        if (! empty($schoolYear)) {
            $batchFallback->where(function ($q) use ($schoolYear, $normalizedYear) {
                $q->where('e.school_year', $schoolYear)
                    ->orWhereRaw(
                        "REPLACE(REPLACE(REPLACE(REPLACE(e.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '') = ?",
                        [$normalizedYear]
                    );
            });
        }

        if (! empty($dateFrom)) {
            $batchFallback->whereRaw('DATE(COALESCE(e.created_at, b.closed_at)) >= ?', [$dateFrom]);
        }
        if (! empty($dateTo)) {
            $batchFallback->whereRaw('DATE(COALESCE(e.created_at, b.closed_at)) <= ?', [$dateTo]);
        }

        $fallbackRow = (array) $batchFallback->first();
        $total += (float) ($fallbackRow['amount'] ?? 0);

        return $total;
    }

    private function sumDonationToSchool(string $schoolYear, ?string $dateFrom, ?string $dateTo): array
    {
        $expenseQuery = DB::table('expenses')
            ->where('school_year', $schoolYear)
            ->where('category', 'Donation');

        if (! empty($dateFrom)) {
            $expenseQuery->whereRaw('DATE(created_at) >= ?', [$dateFrom]);
        }
        if (! empty($dateTo)) {
            $expenseQuery->whereRaw('DATE(created_at) <= ?', [$dateTo]);
        }

        $expenseRow = (array) $expenseQuery->selectRaw('COALESCE(SUM(amount),0) AS amount')->first();
        $donationExpense = (float) ($expenseRow['amount'] ?? 0);

        $donationReimb = 0.0;
        $recipientIds = $this->donationRecipients->recipientIds();
        if (! empty($recipientIds)) {
            $normalizedYear = preg_replace('/[^0-9]/', '', (string) $schoolYear);
            $donationQuery = DB::table('reimbursements as r')
                ->selectRaw('COALESCE(SUM(r.amount),0) AS amount')
                ->leftJoin('expenses as e', 'e.id', '=', 'r.expense_id')
                ->whereIn('r.reimbursed_to', $recipientIds);

            if (! empty($schoolYear)) {
                $donationQuery->where(function ($q) use ($schoolYear, $normalizedYear) {
                    $q->where(function ($q2) use ($schoolYear, $normalizedYear) {
                        $q2->where('r.school_year', $schoolYear)
                            ->orWhereRaw(
                                "REPLACE(REPLACE(REPLACE(REPLACE(r.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '') = ?",
                                [$normalizedYear]
                            );
                    })->orWhere(function ($q2) use ($schoolYear, $normalizedYear) {
                        $q2->whereNull('r.school_year')
                            ->where(function ($q3) use ($schoolYear, $normalizedYear) {
                                $q3->where('e.school_year', $schoolYear)
                                    ->orWhereRaw(
                                        "REPLACE(REPLACE(REPLACE(REPLACE(e.school_year, ' ', ''), '-', ''), '/', ''), '\\\\', '') = ?",
                                        [$normalizedYear]
                                    );
                            });
                    });
                });
            }

            if (! empty($dateFrom)) {
                $donationQuery->whereRaw('DATE(COALESCE(r.created_at, e.created_at)) >= ?', [$dateFrom]);
            }
            if (! empty($dateTo)) {
                $donationQuery->whereRaw('DATE(COALESCE(r.created_at, e.created_at)) <= ?', [$dateTo]);
            }

            $donationRow = (array) $donationQuery->first();
            $donationReimb = (float) ($donationRow['amount'] ?? 0);
        }

        return [
            'donationToSchool' => $donationExpense + $donationReimb,
            'donationReimb' => $donationReimb,
        ];
    }

    private function sumRefunds(string $schoolYear, ?string $dateFrom, ?string $dateTo): float
    {
        $query = DB::table('refunds')
            ->where('school_year', $schoolYear)
            ->whereIn('status', ['Partial', 'Paid'])
            ->whereNotNull('refund_paid_amount');

        if (! empty($dateFrom)) {
            $query->whereRaw('DATE(COALESCE(refunded_at, created_at)) >= ?', [$dateFrom]);
        }
        if (! empty($dateTo)) {
            $query->whereRaw('DATE(COALESCE(refunded_at, created_at)) <= ?', [$dateTo]);
        }

        $row = (array) $query->selectRaw('COALESCE(SUM(refund_paid_amount),0) AS refund_paid_amount')->first();

        return (float) ($row['refund_paid_amount'] ?? 0);
    }

    private function sumDiscounts(string $schoolYear, ?string $dateFrom, ?string $dateTo): float
    {
        $query = DB::table('discount_usages as du')
            ->join('invoices as i', 'i.id', '=', 'du.invoice_id')
            ->where('i.school_year', $schoolYear);

        if (! empty($dateFrom)) {
            $query->whereRaw('DATE(COALESCE(du.used_at, du.created_at)) >= ?', [$dateFrom]);
        }
        if (! empty($dateTo)) {
            $query->whereRaw('DATE(COALESCE(du.used_at, du.created_at)) <= ?', [$dateTo]);
        }

        $row = (array) $query->selectRaw('COALESCE(SUM(du.discount_amount),0) AS discount_amount')->first();

        return (float) ($row['discount_amount'] ?? 0);
    }

    private function sumUnpaidBalances(array $invoices, array $paidByInvoice, array $discountByInvoice, array $refundByInvoice, array $extraByParent): float
    {
        $parentBalances = [];

        foreach ($invoices as $inv) {
            $invoiceId = (int) ($inv['id'] ?? 0);
            $parentId = (int) ($inv['parent_id'] ?? 0);
            if ($invoiceId <= 0 || $parentId <= 0) {
                continue;
            }
            $total = (float) ($inv['total_amount'] ?? 0);
            $paid = (float) ($paidByInvoice[$invoiceId] ?? 0);
            $disc = (float) ($discountByInvoice[$invoiceId] ?? 0);
            $ref = (float) ($refundByInvoice[$invoiceId] ?? 0);
            $balance = max(0.0, round($total - $disc - $paid - $ref, 2));

            if (! isset($parentBalances[$parentId])) {
                $parentBalances[$parentId] = 0.0;
            }
            $parentBalances[$parentId] += $balance;
        }

        foreach ($extraByParent as $parentId => $extra) {
            if (! isset($parentBalances[$parentId])) {
                $parentBalances[$parentId] = 0.0;
            }
            $parentBalances[$parentId] += (float) $extra;
        }

        $totalUnpaid = 0.0;
        foreach ($parentBalances as $balance) {
            if ($balance > 0.00001) {
                $totalUnpaid += $balance;
            }
        }

        return $totalUnpaid;
    }
}