<?php

namespace App\Domain\Financial\Services;

use App\Domain\Financial\Support\BundledProductLine;
use App\Domain\Financial\Support\SubscriptionLine;
use App\Domain\Participant\Models\Participant;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

/**
 * How much a participant has actually paid toward one part of their bill.
 *
 * Invoices here are routinely bundled — a subscription and a registration card
 * on the same invoice, sometimes several products — and a payment lands on the
 * invoice, never on a line. So "how much did they pay for the card" can only be
 * answered by allocating each payment across the lines in proportion to their
 * share of the invoice.
 *
 * The denominator is subtotal_amount (the sum of the line totals), not
 * total_amount: Invoice::recalculateTotals() defines
 * total = subtotal - discount + tax + service_fee, so dividing by total makes
 * the shares exceed 1 whenever a header discount exists.
 *
 * All amounts are piastres. Integer arithmetic only.
 */
class ParticipantBillingService
{
    /**
     * Piastres paid toward programme subscription lines over ALL TIME, keyed by
     * participant id.
     *
     * This is a lifetime total and answers "how much has this person ever paid
     * us for subscriptions" — it is NOT the current month. A roster showing it
     * beside a monthly bill reads as though someone paid 2,500 for one month.
     * For anything cycle-shaped, use subscriptionForPeriod().
     *
     * A subscription line is one with no itemable — products and kits carry a
     * morph. (POS sales historically left that null, which is what made product
     * sales read as subscription revenue; see the 2026_09_01 backfill.)
     */
    public function subscriptionPaid(array $participantIds, ?int $branchId = null): array
    {
        if (empty($participantIds)) {
            return [];
        }

        return $this->allocate($participantIds, function ($q) {
            $q->whereNull('invoice_items.itemable_type');
        }, $branchId);
    }

    /**
     * What each participant was billed and has paid for programme subscription
     * within ONE billing cycle, plus why the figure is what it is.
     *
     * `billed` is what the subscription lines came to; `due` is that share of
     * the invoice total, so a header discount lowers what they actually owe
     * while `billed` still shows the undiscounted price. Comparing the two is
     * what lets the roster say "650 of 900, because an admin discounted it"
     * rather than just "650".
     *
     * $periodStart is inclusive, $periodEnd exclusive, both Y-m-d.
     *
     * @return array<int, array{billed:int, due:int, paid:int, prorated:bool,
     *     proration_label:?string, admin_override:?array, price_override:?array,
     *     has_plan:bool, plan:?array, invoice_numbers:array<int,string>}>
     */
    public function subscriptionForPeriod(
        array $participantIds,
        string $periodStart,
        string $periodEnd,
        ?int $branchId = null
    ): array
    {
        if (empty($participantIds)) {
            return [];
        }

        // An invoice can name a month it was not issued in — a July catch-up
        // typed up in September, or next term paid in advance. The scan is
        // widened a year either side so those are visible at all; which of them
        // actually count is decided per line, below.
        $scanFrom = date('Y-m-d', strtotime($periodStart . ' -12 months'));
        $scanTo = date('Y-m-d', strtotime($periodEnd . ' +12 months'));

        // DB::table() bypasses every Eloquent global scope, the branch one
        // included, so the branch has to be named here. Without it a member who
        // moved branches drags the invoices raised at the old branch into the
        // new branch's billed/due/paid columns. Everything below is keyed off
        // this invoice id list, so filtering here filters the whole method.
        $invoices = DB::table('invoices')
            ->where('billable_type', Participant::class)
            ->whereIn('billable_id', $participantIds)
            ->when($branchId, fn ($q) => $q->where('invoices.branch_id', $branchId))
            ->whereNull('deleted_at')
            ->where('status', '!=', 'cancelled')
            ->where('subtotal_amount', '>', 0)
            ->where('issue_date', '>=', $scanFrom)
            ->where('issue_date', '<', $scanTo)
            ->get(['id', 'number', 'billable_id', 'academy_id', 'subtotal_amount', 'total_amount', 'issue_date', 'notes', 'metadata']);

        if ($invoices->isEmpty()) {
            return [];
        }

        $invoiceIds = $invoices->pluck('id')->all();
        $windowMonths = $this->monthsIn($periodStart, $periodEnd);
        $vocabulary = $this->academyVocabulary($invoices->pluck('academy_id')->filter()->unique()->all());

        // Hand-typed lines only — anything carrying a morph is a product or a
        // kit and belongs in its own column. What is left still has to be read:
        // a registration fee or a shirt typed into the same box is not
        // subscription money either.
        $items = DB::table('invoice_items')
            ->whereIn('invoice_id', $invoiceIds)
            ->whereNull('itemable_type')
            ->get(['invoice_id', 'description', 'total_amount', 'metadata'])
            ->groupBy('invoice_id');

        $payments = DB::table('payments')
            ->whereIn('invoice_id', $invoiceIds)
            ->where('status', 'confirmed')
            ->where('direction', 'inbound')
            ->whereNull('deleted_at')
            ->groupBy('invoice_id')
            ->select('invoice_id', DB::raw('SUM(amount) as paid'))
            ->pluck('paid', 'invoice_id');

        $plans = DB::table('payment_plans')
            ->whereIn('invoice_id', $invoiceIds)
            ->whereIn('status', ['active', 'partial'])
            ->get(['invoice_id', 'total_installments', 'paid_installments', 'installment_amount'])
            ->keyBy('invoice_id');

        $out = [];

        $issuedInPeriod = fn (string $issueDate) => $issueDate >= $periodStart && $issueDate < $periodEnd;

        foreach ($invoices as $invoice) {
            $lines = $items[$invoice->id] ?? collect();
            $issueDate = substr((string) $invoice->issue_date, 0, 10);
            $invoiceMeta = $this->decodeMetadata($invoice->metadata);
            $names = $vocabulary[(int) $invoice->academy_id] ?? ['programs' => [], 'products' => []];

            // Which of this invoice's money belongs to the cycle being asked
            // about, line by line.
            $billed = 0;
            $contributing = [];

            foreach ($lines as $line) {
                if (! SubscriptionLine::isSubscription($line->description, $names['programs'], $names['products'])) {
                    continue;
                }

                $share = $this->shareForWindow(
                    (int) $line->total_amount,
                    SubscriptionLine::explicitMonths($line->description, $invoice->notes, $invoiceMeta, $issueDate),
                    $windowMonths,
                    $issuedInPeriod($issueDate),
                );

                if ($share > 0) {
                    $billed += $share;
                    $contributing[] = $line;
                }
            }

            if ($billed <= 0) {
                continue;
            }

            $subtotal = (int) $invoice->subtotal_amount;
            $total = (int) $invoice->total_amount;
            $paidOnInvoice = (int) ($payments[$invoice->id] ?? 0);

            // Round down throughout; an unallocated remainder is honest, money
            // conjured by rounding up is not.
            $due = min(intdiv($total * $billed, $subtotal), $billed);
            $paid = min(intdiv($paidOnInvoice * $billed, $subtotal), $billed);

            $pid = (int) $invoice->billable_id;
            $row = $out[$pid] ?? [
                'billed' => 0,
                'due' => 0,
                'paid' => 0,
                'prorated' => false,
                'proration_label' => null,
                'admin_override' => null,
                'price_override' => null,
                'has_plan' => false,
                'plan' => null,
                'invoice_numbers' => [],
            ];

            $row['billed'] += $billed;
            $row['due'] += $due;
            $row['paid'] += $paid;
            $row['invoice_numbers'][] = $invoice->number;

            foreach ($contributing as $line) {
                // Proration is recorded only in the line text the pricing engine
                // wrote ("متناسب: 13 من 30 يوم"), so that is where it is read from.
                if ($line->description && str_contains($line->description, 'متناسب')) {
                    $row['prorated'] = true;
                    $row['proration_label'] ??= $line->description;
                }

                $meta = $this->decodeMetadata($line->metadata);
                if (! empty($meta['overridden_by'])) {
                    $row['price_override'] ??= [
                        'by' => $meta['overridden_by'],
                        'reason' => $meta['override_reason'] ?? null,
                        'original' => (int) ($meta['original_price'] ?? 0),
                        'final' => (int) ($meta['overridden_price'] ?? 0),
                    ];
                }
            }

            if (! empty($invoiceMeta['admin_override'])) {
                $o = $invoiceMeta['admin_override'];
                $row['admin_override'] ??= [
                    'by' => $o['applied_by_name'] ?? null,
                    'reason' => $o['reason'] ?? null,
                    'discount' => (int) ($o['discount_piasters'] ?? 0),
                    'at' => $o['applied_at'] ?? null,
                ];
            }

            if ($plan = $plans[$invoice->id] ?? null) {
                $row['has_plan'] = true;
                $row['plan'] ??= [
                    'paid' => (int) $plan->paid_installments,
                    'total' => (int) $plan->total_installments,
                    'amount' => (int) $plan->installment_amount,
                ];
            }

            $out[$pid] = $row;
        }

        return $out;
    }

    /**
     * The months a cycle covers, as 'YYYY-MM' — one per month of its length,
     * counted from the month the cycle opens in.
     *
     * A cycle that does not start on the 1st still belongs to the month it
     * opens in: billing from the 15th to the 15th is "August's subscription"
     * to everyone who works here, and calling it half of two months would put
     * one payment under two headings.
     *
     * @return array<int, string>
     */
    private function monthsIn(string $periodStart, string $periodEnd): array
    {
        $start = strtotime($periodStart);
        $end = strtotime($periodEnd);

        $length = max(1, (int) round(($end - $start) / 2629800)); // avg month, seconds

        $months = [];
        for ($i = 0; $i < $length; $i++) {
            $months[] = date('Y-m', strtotime($periodStart . " +{$i} months"));
        }

        return $months;
    }

    /**
     * How much of one line's total belongs to the cycle being asked about.
     *
     * With months named on the invoice the amount is split evenly across them —
     * one invoice for July and August pays half of each — and only the months
     * inside the cycle are counted. Rounding down leaves the remainder on the
     * last month, so the parts can never add up to more than was billed.
     *
     * With nothing named, the invoice's own date is the only evidence there is,
     * and the line counts in full or not at all.
     *
     * @param array<int, string> $lineMonths
     * @param array<int, string> $windowMonths
     */
    private function shareForWindow(int $lineTotal, array $lineMonths, array $windowMonths, bool $issuedInPeriod): int
    {
        if ($lineMonths === []) {
            return $issuedInPeriod ? $lineTotal : 0;
        }

        $count = count($lineMonths);
        $per = intdiv($lineTotal, $count);
        $remainder = $lineTotal - ($per * $count);

        $share = 0;
        foreach ($lineMonths as $i => $month) {
            if (in_array($month, $windowMonths, true)) {
                $share += $per + ($i === $count - 1 ? $remainder : 0);
            }
        }

        return $share;
    }

    /**
     * Every programme and product name in the given academies, so a hand-typed
     * line can be checked against what this academy actually sells rather than
     * against a guess.
     *
     * @param array<int, mixed> $academyIds
     * @return array<int, array{programs: array<int, string>, products: array<int, string>}>
     */
    private function academyVocabulary(array $academyIds): array
    {
        $out = [];
        foreach ($academyIds as $id) {
            $out[(int) $id] = ['programs' => [], 'products' => []];
        }

        if ($out === []) {
            return [];
        }

        foreach ([['training_programs', 'programs'], ['products', 'products']] as [$table, $key]) {
            if (! Schema::hasTable($table)) {
                continue;
            }

            $rows = DB::table($table)
                ->whereIn('academy_id', array_keys($out))
                ->whereNull('deleted_at')
                ->get(['academy_id', 'name_ar']);

            foreach ($rows as $row) {
                if ($row->name_ar) {
                    $out[(int) $row->academy_id][$key][] = $row->name_ar;
                }
            }
        }

        return $out;
    }

    private function decodeMetadata(mixed $raw): array
    {
        if (is_array($raw)) {
            return $raw;
        }

        if (! is_string($raw) || $raw === '') {
            return [];
        }

        $decoded = json_decode($raw, true);

        return is_array($decoded) ? $decoded : [];
    }

    /**
     * Piastres paid toward one product, keyed by participant id.
     */
    public function productPaid(array $participantIds, int $productId, ?int $branchId = null): array
    {
        if (empty($participantIds)) {
            return [];
        }

        return $this->allocate($participantIds, function ($q) use ($productId) {
            $q->where('invoice_items.itemable_type', \App\Domain\Inventory\Models\Product::class)
                ->where('invoice_items.itemable_id', $productId);
        }, $branchId);
    }

    /**
     * What each participant was billed for one product, keyed by participant id.
     *
     * Billed is not the same as paid — someone paying in instalments is billed
     * the full amount on day one. Both numbers are needed to show progress.
     *
     * @return array<int, array{billed:int, quantity:int}>
     */
    public function productBilled(array $participantIds, int $productId, ?int $branchId = null): array
    {
        if (empty($participantIds)) {
            return [];
        }

        $rows = DB::table('invoice_items')
            ->join('invoices', 'invoices.id', '=', 'invoice_items.invoice_id')
            ->where('invoice_items.itemable_type', \App\Domain\Inventory\Models\Product::class)
            ->where('invoice_items.itemable_id', $productId)
            ->where('invoices.billable_type', Participant::class)
            ->whereIn('invoices.billable_id', $participantIds)
            ->when($branchId, fn ($q) => $q->where('invoices.branch_id', $branchId))
            ->whereNull('invoices.deleted_at')
            ->where('invoices.status', '!=', 'cancelled')
            ->groupBy('invoices.billable_id')
            ->select(
                'invoices.billable_id',
                DB::raw('SUM(invoice_items.total_amount) as billed'),
                DB::raw('SUM(invoice_items.quantity) as quantity')
            )
            ->get();

        $out = [];
        foreach ($rows as $r) {
            $out[(int) $r->billable_id] = [
                'billed' => (int) $r->billed,
                'quantity' => (int) $r->quantity,
            ];
        }

        return $out;
    }

    /**
     * What each participant was billed and has paid toward one product the
     * programme requires — the registration card, the kit — counting the money
     * however it was typed.
     *
     * productPaid()/productBilled() see only lines carrying the product's
     * itemable morph. That is how the POS writes a sale, and it misses every
     * receptionist who typed "القسط الاول من القيد" into a free-text line. Those
     * players had paid; the roster called them non-buyers. This method reads the
     * lines as text as well (BundledProductLine), and allocates each payment
     * across the invoice the same way subscriptions are allocated, so a card
     * paid on the same invoice as a month's training is still counted.
     *
     * `billed` is what the matched lines came to — for an instalment typed by
     * hand that is the instalment, not the card's price, so the caller decides
     * what to compare it against. `from_product_lines` says whether the match
     * came from a real product sale (its price is the frozen, agreed one) or
     * from text (it is only whatever has been typed so far).
     *
     * @param array<int, int> $participantIds
     * @return array<int, array{billed:int, paid:int, quantity:int,
     *     from_product_lines:bool, from_text:bool, inferred:bool,
     *     has_plan:bool, plan:?array}>
     */
    public function bundledProductForParticipants(
        array $participantIds,
        int $productId,
        string $productName,
        ?int $branchId = null,
        bool $inferBareInstalments = false
    ): array {
        if (empty($participantIds)) {
            return [];
        }

        // DB::table() carries no global scope, so the branch is named here or
        // not at all — a card bought at the branch this player transferred from
        // is not this branch's money. Everything below keys off these invoices.
        $invoices = DB::table('invoices')
            ->where('billable_type', Participant::class)
            ->whereIn('billable_id', $participantIds)
            ->when($branchId, fn ($q) => $q->where('invoices.branch_id', $branchId))
            ->whereNull('deleted_at')
            ->where('status', '!=', 'cancelled')
            ->where('subtotal_amount', '>', 0)
            ->get(['id', 'billable_id', 'academy_id', 'subtotal_amount', 'total_amount']);

        if ($invoices->isEmpty()) {
            return [];
        }

        $invoiceIds = $invoices->pluck('id')->all();
        $vocabulary = $this->academyVocabulary($invoices->pluck('academy_id')->filter()->unique()->all());

        $items = DB::table('invoice_items')
            ->whereIn('invoice_id', $invoiceIds)
            ->get(['invoice_id', 'itemable_type', 'itemable_id', 'description', 'quantity', 'total_amount'])
            ->groupBy('invoice_id');

        $payments = DB::table('payments')
            ->whereIn('invoice_id', $invoiceIds)
            ->where('status', 'confirmed')
            ->where('direction', 'inbound')
            ->whereNull('deleted_at')
            ->groupBy('invoice_id')
            ->select('invoice_id', DB::raw('SUM(amount) as paid'))
            ->pluck('paid', 'invoice_id');

        $plans = DB::table('payment_plans')
            ->whereIn('invoice_id', $invoiceIds)
            ->whereIn('status', ['active', 'partial', 'completed'])
            ->get(['invoice_id', 'total_installments', 'paid_installments', 'installment_amount'])
            ->keyBy('invoice_id');

        $productClass = \App\Domain\Inventory\Models\Product::class;
        $identifyingCache = [];
        $out = [];

        foreach ($invoices as $invoice) {
            $lines = $items[$invoice->id] ?? collect();
            if ($lines->isEmpty()) {
                continue;
            }

            $academyId = (int) $invoice->academy_id;
            $programNames = $vocabulary[$academyId]['programs'] ?? [];
            $identifyingCache[$academyId] ??= BundledProductLine::identifyingWords($productName, $programNames);
            $identifying = $identifyingCache[$academyId];

            // A bare "القسط الاول" says nothing about what is being paid off.
            // It can only be read as this product's instalment when the invoice
            // is not also paying for training — otherwise the instalment could
            // just as easily be part of the subscription on the line above it.
            $invoiceNamesProgram = $lines->contains(
                fn ($line) => $line->itemable_type === null
                    && BundledProductLine::namesProgram($line->description, $programNames)
            );

            $matched = 0;
            $quantity = 0;
            $fromProductLines = false;
            $fromText = false;
            $inferred = false;

            foreach ($lines as $line) {
                if ($line->itemable_type === $productClass && (int) $line->itemable_id === $productId) {
                    $matched += (int) $line->total_amount;
                    $quantity += (int) $line->quantity;
                    $fromProductLines = true;
                    continue;
                }

                // A morph pointing anywhere else is another product's money.
                if ($line->itemable_type !== null) {
                    continue;
                }

                if (BundledProductLine::matches($line->description, $productName, $identifying)) {
                    $matched += (int) $line->total_amount;
                    $quantity += 1;
                    $fromText = true;
                    continue;
                }

                if ($inferBareInstalments
                    && ! $invoiceNamesProgram
                    && BundledProductLine::isBareInstalment($line->description)) {
                    $matched += (int) $line->total_amount;
                    $quantity += 1;
                    $fromText = true;
                    $inferred = true;
                }
            }

            if ($matched <= 0) {
                continue;
            }

            $subtotal = (int) $invoice->subtotal_amount;
            $paidOnInvoice = (int) ($payments[$invoice->id] ?? 0);

            // Round down, and never allocate more toward a line than it cost:
            // a payment settles total_amount (tax and fees included) while the
            // share is of subtotal_amount, so paying in full would otherwise
            // read as over 100%.
            $paid = min(intdiv($paidOnInvoice * $matched, $subtotal), $matched);

            $pid = (int) $invoice->billable_id;
            $row = $out[$pid] ?? [
                'billed' => 0,
                'paid' => 0,
                'quantity' => 0,
                'from_product_lines' => false,
                'from_text' => false,
                'inferred' => false,
                'has_plan' => false,
                'plan' => null,
            ];

            $row['billed'] += $matched;
            $row['paid'] += $paid;
            $row['quantity'] += $quantity;
            $row['from_product_lines'] = $row['from_product_lines'] || $fromProductLines;
            $row['from_text'] = $row['from_text'] || $fromText;
            $row['inferred'] = $row['inferred'] || $inferred;

            if ($plan = $plans[$invoice->id] ?? null) {
                $row['has_plan'] = true;
                $row['plan'] ??= [
                    'paid' => (int) $plan->paid_installments,
                    'total' => (int) $plan->total_installments,
                    'amount' => (int) $plan->installment_amount,
                ];
            }

            $out[$pid] = $row;
        }

        return $out;
    }

    /**
     * Allocate confirmed inbound payments across the lines matched by $filter,
     * in proportion to those lines' share of each invoice.
     *
     * @return array<int, int> participant id => piastres
     */
    private function allocate(array $participantIds, callable $filter, ?int $branchId = null): array
    {
        // The join means the branch has to be named on the driving table: a
        // query-builder call carries no global scope, so without this an
        // invoice raised at another branch for the same member inflates this
        // branch's "paid so far" column. The payments query below is keyed off
        // the invoice ids this one returns, so filtering here covers both.
        // Share of each invoice represented by the lines we care about.
        $shares = DB::table('invoice_items')
            ->join('invoices', 'invoices.id', '=', 'invoice_items.invoice_id')
            ->where('invoices.billable_type', Participant::class)
            ->whereIn('invoices.billable_id', $participantIds)
            ->when($branchId, fn ($q) => $q->where('invoices.branch_id', $branchId))
            ->whereNull('invoices.deleted_at')
            ->where('invoices.status', '!=', 'cancelled')
            ->where('invoices.subtotal_amount', '>', 0)
            ->tap($filter)
            ->groupBy('invoice_items.invoice_id')
            ->select('invoice_items.invoice_id', DB::raw('SUM(invoice_items.total_amount) as part_total'))
            ->get()
            ->keyBy('invoice_id');

        if ($shares->isEmpty()) {
            return [];
        }

        $rows = DB::table('payments')
            ->join('invoices', 'invoices.id', '=', 'payments.invoice_id')
            ->whereIn('payments.invoice_id', $shares->keys()->all())
            ->where('payments.status', 'confirmed')
            ->where('payments.direction', 'inbound')
            ->whereNull('payments.deleted_at')
            ->groupBy('invoices.billable_id', 'payments.invoice_id', 'invoices.subtotal_amount')
            ->select(
                'invoices.billable_id',
                'payments.invoice_id',
                'invoices.subtotal_amount',
                DB::raw('SUM(payments.amount) as paid')
            )
            ->get();

        $out = [];
        foreach ($rows as $r) {
            $part = (int) ($shares[$r->invoice_id]->part_total ?? 0);
            $subtotal = (int) $r->subtotal_amount;

            if ($part <= 0 || $subtotal <= 0) {
                continue;
            }

            // Round down; the remainder stays unallocated rather than inventing
            // money that was never paid.
            $allocated = intdiv((int) $r->paid * $part, $subtotal);

            // The share is of subtotal_amount, but a payment settles
            // total_amount — which also carries tax and service fees. Paying an
            // invoice in full therefore allocates slightly more than the line
            // was billed, and a progress bar would read over 100%. Nobody can
            // have paid more toward a line than it cost.
            $allocated = min($allocated, $part);

            $pid = (int) $r->billable_id;
            $out[$pid] = ($out[$pid] ?? 0) + $allocated;
        }

        return $out;
    }
}
