<?php

namespace App\Services;

use App\Models\Company;
use App\Models\CreditNote;
use App\Models\CreditNoteItem;
use App\Models\Customer;
use App\Models\DebitNote;
use App\Models\Expense;
use App\Models\Invoice;
use App\Models\InvoiceItem;
use App\Models\Item;
use App\Models\Payment;
use App\Models\PurchaseBill;
use App\Models\PurchaseBillItem;
use App\Models\Vendor;
use Carbon\Carbon;
use Illuminate\Support\Collection;

class AccountingReportService
{
    public function __construct(
        private readonly SpreadsheetExporter $exporter,
    ) {}

    /**
     * @param  array<string, mixed>  $filters
     * @return array<string, mixed>
     */
    public function build(int $companyId, string $reportKey, array $filters = []): array
    {
        $config = config("accounting.reports.{$reportKey}");
        if (! $config) {
            throw new \InvalidArgumentException("Unknown accounting report: {$reportKey}");
        }

        $dateFrom = $filters['date_from'] ?? now()->startOfMonth()->format('Y-m-d');
        $dateTo = $filters['date_to'] ?? now()->format('Y-m-d');
        $start = Carbon::parse($dateFrom)->startOfDay();
        $end = Carbon::parse($dateTo)->endOfDay();

        $data = match ($reportKey) {
            'cash_book' => $this->cashBook($companyId, $start, $end, $filters),
            'item_stock_ledger' => $this->itemLedger($companyId, $start, $end, (int) ($filters['item_id'] ?? 0), 'stock'),
            'item_amount_ledger' => $this->itemLedger($companyId, $start, $end, (int) ($filters['item_id'] ?? 0), 'amount'),
            'profit_loss' => $this->profitLoss($companyId, $start, $end),
            'inventory_book' => $this->inventoryBook($companyId, $start, $end),
            'client_outstanding', 'invoice_receivable' => $this->partyBalances($companyId, 'client'),
            'vendor_outstanding', 'bill_payable' => $this->partyBalances($companyId, 'vendor'),
            default => ['rows' => []],
        };

        return array_merge($data, [
            'company' => Company::findOrFail($companyId),
            'report_key' => $reportKey,
            'config' => $config,
            'date_from' => $dateFrom,
            'date_to' => $dateTo,
        ]);
    }

    /**
     * @param  array<string, mixed>  $report
     */
    public function export(array $report, string $filename): \Symfony\Component\HttpFoundation\StreamedResponse
    {
        $key = $report['report_key'];
        $sheets = match ($key) {
            'cash_book' => $this->exportCashBook($report),
            'item_stock_ledger', 'item_amount_ledger' => $this->exportItemLedger($report),
            'profit_loss' => $this->exportProfitLoss($report),
            'inventory_book' => $this->exportInventoryBook($report),
            default => $this->exportPartyBalances($report),
        };

        return $this->exporter->download($sheets, $filename);
    }

    /**
     * @param  array<string, mixed>  $filters
     * @return array<string, mixed>
     */
    private function cashBook(int $companyId, Carbon $start, Carbon $end, array $filters): array
    {
        $query = Payment::forCompany($companyId)
            ->with(['customer', 'vendor', 'allocations'])
            ->whereDate('payment_date', '>=', $start)
            ->whereDate('payment_date', '<=', $end)
            ->orderBy('payment_date')
            ->orderBy('id');

        if (! empty($filters['account'])) {
            $term = $filters['account'];
            $query->where(function ($q) use ($term) {
                $q->whereHas('customer', fn ($c) => $c->where('name', 'like', "%{$term}%"))
                    ->orWhereHas('vendor', fn ($v) => $v->where('name', 'like', "%{$term}%"));
            });
        }

        if (! empty($filters['number'])) {
            $query->where('payment_number', 'like', '%'.$filters['number'].'%');
        }

        if (! empty($filters['type']) && $filters['type'] !== 'all') {
            $query->where('kind', $filters['type']);
        }

        $payments = $query->get();
        $balance = 0.0;
        $rows = [];

        foreach ($payments as $payment) {
            $isCredit = in_array($payment->kind, ['received', 'advance_received'], true);
            $amount = (float) $payment->amount;
            $credit = $isCredit ? $amount : 0;
            $debit = $isCredit ? 0 : $amount;
            $balance += $credit - $debit;

            $rows[] = [
                'date' => $payment->payment_date->format('d/m/Y'),
                'date_sort' => $payment->payment_date->format('Y-m-d'),
                'description' => $payment->kindMeta()['label'] ?? $payment->kind,
                'account' => $payment->partyName(),
                'number' => $payment->payment_number,
                'credit' => $credit,
                'debit' => $debit,
                'balance' => round($balance, 2),
            ];
        }

        $totals = [
            'credit' => round(collect($rows)->sum('credit'), 2),
            'debit' => round(collect($rows)->sum('debit'), 2),
            'balance' => round($balance, 2),
        ];

        return ['rows' => $rows, 'totals' => $totals];
    }

    /**
     * @return array<string, mixed>
     */
    private function itemLedger(int $companyId, Carbon $start, Carbon $end, int $itemId, string $mode): array
    {
        if ($itemId <= 0) {
            return ['rows' => [], 'item' => null, 'totals' => [], 'requires_item' => true];
        }

        $item = Item::where('company_id', $companyId)->findOrFail($itemId);
        $opening = $this->itemOpeningBalance($companyId, $item, $start, $mode);
        $rows = [];
        $balance = $opening;

        if ($mode === 'stock') {
            $rows[] = [
                'date' => $start->format('d-M-Y'),
                'particular' => 'Opening Quantity',
                'voucher_type' => '',
                'voucher_number' => '',
                'sale_dr' => '',
                'purchase_cr' => '',
                'balance' => $opening,
                'is_opening' => true,
            ];
        }

        $entries = $this->itemLedgerEntries($companyId, $item, $start, $end, $mode);
        foreach ($entries as $entry) {
            if ($mode === 'stock') {
                $balance += (float) ($entry['purchase_cr'] ?? 0) - (float) ($entry['sale_dr'] ?? 0);
            } else {
                $balance += (float) ($entry['purchase_cr'] ?? 0) - (float) ($entry['sale_dr'] ?? 0);
            }
            $entry['balance'] = round($balance, $mode === 'stock' ? 3 : 2);
            $rows[] = $entry;
        }

        $totals = [
            'sale_dr' => round(collect($rows)->sum(fn ($r) => (float) ($r['sale_dr'] ?: 0)), $mode === 'stock' ? 3 : 2),
            'purchase_cr' => round(collect($rows)->sum(fn ($r) => (float) ($r['purchase_cr'] ?: 0)), $mode === 'stock' ? 3 : 2),
            'balance' => round($balance, $mode === 'stock' ? 3 : 2),
        ];

        return [
            'rows' => $rows,
            'item' => $item,
            'totals' => $totals,
            'requires_item' => false,
            'ledger_mode' => $mode,
        ];
    }

    /**
     * @return array<string, mixed>
     */
    private function profitLoss(int $companyId, Carbon $start, Carbon $end): array
    {
        $sales = (float) Invoice::forCompany($companyId)
            ->whereNotIn('status', ['draft', 'cancelled'])
            ->whereDate('invoice_date', '>=', $start)
            ->whereDate('invoice_date', '<=', $end)
            ->sum('grand_total');

        $purchases = (float) PurchaseBill::forCompany($companyId)
            ->whereNotIn('status', ['draft', 'void'])
            ->whereDate('bill_date', '>=', $start)
            ->whereDate('bill_date', '<=', $end)
            ->sum('grand_total');

        $expenses = (float) Expense::where('company_id', $companyId)
            ->whereNotIn('status', ['draft', 'void', 'cancelled'])
            ->whereDate('expense_date', '>=', $start)
            ->whereDate('expense_date', '<=', $end)
            ->sum('grand_total');

        $creditNotes = (float) CreditNote::forCompany($companyId)
            ->whereNotIn('status', ['draft', 'void'])
            ->whereDate('credit_note_date', '>=', $start)
            ->whereDate('credit_note_date', '<=', $end)
            ->sum('grand_total');

        $debitNotes = (float) DebitNote::forCompany($companyId)
            ->whereNotIn('status', ['draft', 'void'])
            ->whereDate('debit_note_date', '>=', $start)
            ->whereDate('debit_note_date', '<=', $end)
            ->sum('grand_total');

        $gstTax = (float) Invoice::forCompany($companyId)
            ->whereNotIn('status', ['draft', 'cancelled'])
            ->whereDate('invoice_date', '>=', $start)
            ->whereDate('invoice_date', '<=', $end)
            ->sum('total_tax');

        $tds = (float) Payment::forCompany($companyId)
            ->where('payment_method', 'tds')
            ->whereDate('payment_date', '>=', $start)
            ->whereDate('payment_date', '<=', $end)
            ->sum('amount');

        $tcs = (float) Payment::forCompany($companyId)
            ->where('payment_method', 'tcs')
            ->whereDate('payment_date', '>=', $start)
            ->whereDate('payment_date', '<=', $end)
            ->sum('amount');

        $lines = [
            ['particular' => 'Sales (+)', 'amount' => round($sales, 2), 'sign' => '+'],
            ['particular' => 'Purchase (-)', 'amount' => round($purchases, 2), 'sign' => '-'],
            ['particular' => 'Expense (-)', 'amount' => round($expenses, 2), 'sign' => '-'],
            ['particular' => 'Credit Note (-)', 'amount' => round($creditNotes, 2), 'sign' => '-'],
            ['particular' => 'Debit Note (+)', 'amount' => round($debitNotes, 2), 'sign' => '+'],
            ['particular' => 'GST / Tax (-)', 'amount' => round($gstTax, 2), 'sign' => '-'],
            ['particular' => 'TDS (+)', 'amount' => round($tds, 2), 'sign' => '+'],
            ['particular' => 'TCS (-)', 'amount' => round($tcs, 2), 'sign' => '-'],
        ];

        $profit = round($sales + $debitNotes + $tds - $purchases - $expenses - $creditNotes - $gstTax - $tcs, 2);
        $lines[] = ['particular' => 'Profit', 'amount' => $profit, 'sign' => '=', 'is_total' => true];

        return ['rows' => $lines, 'profit' => $profit];
    }

    /**
     * @return array<string, mixed>
     */
    private function inventoryBook(int $companyId, Carbon $start, Carbon $end): array
    {
        $items = Item::where('company_id', $companyId)
            ->where('track_inventory', true)
            ->orderBy('name')
            ->get();

        $rows = [];
        foreach ($items as $item) {
            $movements = $this->itemPeriodMovements($companyId, $item, $start, $end);
            $openingQty = $this->itemOpeningBalance($companyId, $item, $start, 'stock');
            $closingQty = $openingQty + $movements['qty_in'] - $movements['qty_out'];

            $rows[] = [
                'item' => $item->name,
                'qty_in' => $movements['qty_in'],
                'qty_out' => $movements['qty_out'],
                'pur_qty' => $movements['pur_qty'],
                'pur_amt' => $movements['pur_amt'],
                'sale_qty' => $movements['sale_qty'],
                'sale_amt' => $movements['sale_amt'],
                'closing_qty' => round($closingQty, 3),
                'closing_amt' => round($closingQty * (float) $item->purchase_rate, 2),
            ];
        }

        return ['rows' => $rows];
    }

    /**
     * @return array<string, mixed>
     */
    private function partyBalances(int $companyId, string $party): array
    {
        $rows = [];
        $today = now()->format('d/m/Y');

        if ($party === 'client') {
            $parties = Customer::where('company_id', $companyId)->orderBy('name')->get();
            foreach ($parties as $p) {
                $balance = $p->currentBalance();
                $rows[] = $this->partyRow($p, $today, $balance);
            }
        } else {
            $parties = Vendor::where('company_id', $companyId)->orderBy('name')->get();
            foreach ($parties as $p) {
                $balance = $p->currentBalance();
                $rows[] = $this->partyRow($p, $today, $balance);
            }
        }

        return ['rows' => $rows, 'party' => $party];
    }

    /**
     * @return array<string, mixed>
     */
    private function partyRow(Customer|Vendor $party, string $date, float $balance): array
    {
        return [
            'date' => $date,
            'name' => $party->name,
            'email' => $party->email ?? $party->contact_email ?? '',
            'phone' => $party->phone ?? $party->contact_phone ?? '',
            'city' => $party->city ?? '',
            'state' => $party->state ?? '',
            'balance' => round($balance, 2),
        ];
    }

    private function itemOpeningBalance(int $companyId, Item $item, Carbon $beforeEnd, string $mode): float
    {
        $qty = (float) ($item->opening_quantity ?? 0);
        $amount = $qty * (float) $item->purchase_rate;

        $movements = $this->itemLedgerEntries($companyId, $item, Carbon::parse('2000-01-01'), $beforeEnd->copy()->subDay(), $mode);
        foreach ($movements as $m) {
            if ($mode === 'stock') {
                $qty += (float) ($m['purchase_cr'] ?: 0) - (float) ($m['sale_dr'] ?: 0);
            } else {
                $amount += (float) ($m['purchase_cr'] ?: 0) - (float) ($m['sale_dr'] ?: 0);
            }
        }

        return round($mode === 'stock' ? $qty : $amount, $mode === 'stock' ? 3 : 2);
    }

    /**
     * @return list<array<string, mixed>>
     */
    private function itemLedgerEntries(int $companyId, Item $item, Carbon $start, Carbon $end, string $mode): array
    {
        $entries = [];

        $invoiceItems = InvoiceItem::whereHas('invoice', function ($q) use ($companyId, $start, $end) {
            $q->forCompany($companyId)
                ->whereNotIn('status', ['draft', 'cancelled'])
                ->whereDate('invoice_date', '>=', $start)
                ->whereDate('invoice_date', '<=', $end);
        })->where('item_id', $item->id)->with('invoice')->get();

        foreach ($invoiceItems as $line) {
            $entries[] = $this->ledgerEntryFromSale($line, $mode);
        }

        $creditItems = CreditNoteItem::whereHas('creditNote', function ($q) use ($companyId, $start, $end) {
            $q->forCompany($companyId)
                ->whereNotIn('status', ['draft', 'void'])
                ->whereDate('credit_note_date', '>=', $start)
                ->whereDate('credit_note_date', '<=', $end);
        })->where('item_id', $item->id)->with('creditNote')->get();

        foreach ($creditItems as $line) {
            $entries[] = $this->ledgerEntryFromCredit($line, $mode);
        }

        $purchaseItems = PurchaseBillItem::whereHas('purchaseBill', function ($q) use ($companyId, $start, $end) {
            $q->forCompany($companyId)
                ->whereNotIn('status', ['draft', 'void'])
                ->whereDate('bill_date', '>=', $start)
                ->whereDate('bill_date', '<=', $end);
        })->where('item_id', $item->id)->with('purchaseBill')->get();

        foreach ($purchaseItems as $line) {
            $entries[] = $this->ledgerEntryFromPurchase($line, $mode);
        }

        usort($entries, fn ($a, $b) => strcmp($a['date_sort'] ?? '', $b['date_sort'] ?? ''));

        return $entries;
    }

    /**
     * @return array<string, mixed>
     */
    private function ledgerEntryFromSale(InvoiceItem $line, string $mode): array
    {
        $inv = $line->invoice;
        $val = $mode === 'stock'
            ? (float) $line->quantity
            : $this->lineTaxableAmount($line->quantity, $line->rate, $line->discount_percent);

        return [
            'date' => $inv->invoice_date->format('d/m/Y'),
            'date_sort' => $inv->invoice_date->format('Y-m-d'),
            'particular' => 'Invoice',
            'voucher_type' => 'Sale',
            'voucher_number' => $inv->invoice_number,
            'sale_dr' => $val,
            'purchase_cr' => '',
        ];
    }

    /**
     * @return array<string, mixed>
     */
    private function ledgerEntryFromCredit(CreditNoteItem $line, string $mode): array
    {
        $note = $line->creditNote;
        $val = $mode === 'stock'
            ? (float) $line->quantity
            : $this->lineTaxableAmount($line->quantity, $line->rate, $line->discount_percent);

        return [
            'date' => $note->credit_note_date->format('d/m/Y'),
            'date_sort' => $note->credit_note_date->format('Y-m-d'),
            'particular' => 'Credit Note',
            'voucher_type' => 'Sales Return',
            'voucher_number' => $note->credit_note_number,
            'sale_dr' => '',
            'purchase_cr' => $val,
        ];
    }

    /**
     * @return array<string, mixed>
     */
    private function ledgerEntryFromPurchase(PurchaseBillItem $line, string $mode): array
    {
        $bill = $line->purchaseBill;
        $val = $mode === 'stock'
            ? (float) $line->quantity
            : $this->lineTaxableAmount($line->quantity, $line->rate, $line->discount_percent);

        return [
            'date' => $bill->bill_date->format('d/m/Y'),
            'date_sort' => $bill->bill_date->format('Y-m-d'),
            'particular' => 'Purchase Bill',
            'voucher_type' => 'Purchase',
            'voucher_number' => $bill->bill_number,
            'sale_dr' => '',
            'purchase_cr' => $val,
        ];
    }

    /**
     * @return array{qty_in: float, qty_out: float, pur_qty: float, pur_amt: float, sale_qty: float, sale_amt: float}
     */
    private function itemPeriodMovements(int $companyId, Item $item, Carbon $start, Carbon $end): array
    {
        $stock = $this->itemLedgerEntries($companyId, $item, $start, $end, 'stock');
        $amount = $this->itemLedgerEntries($companyId, $item, $start, $end, 'amount');

        $qtyOut = collect($stock)->sum(fn ($r) => (float) ($r['sale_dr'] ?: 0));
        $qtyIn = collect($stock)->sum(fn ($r) => (float) ($r['purchase_cr'] ?: 0));

        return [
            'qty_in' => round($qtyIn, 3),
            'qty_out' => round($qtyOut, 3),
            'pur_qty' => round($qtyIn, 3),
            'pur_amt' => round(collect($amount)->sum(fn ($r) => (float) ($r['purchase_cr'] ?: 0)), 2),
            'sale_qty' => round($qtyOut, 3),
            'sale_amt' => round(collect($amount)->sum(fn ($r) => (float) ($r['sale_dr'] ?: 0)), 2),
        ];
    }

    private function lineTaxableAmount($qty, $rate, $discPercent): float
    {
        $qty = (float) $qty;
        $rate = (float) $rate;
        $disc = (float) $discPercent;

        return max(0, ($qty * $rate) - ($qty * $rate * $disc / 100));
    }

    /**
     * @param  array<string, mixed>  $report
     * @return array<string, string>
     */
    private function exportCashBook(array $report): array
    {
        $headers = ['Date', 'Description', 'Account', 'Number', 'Credit', 'Debit', 'Balance'];
        $rows = array_map(fn ($r) => [
            $r['date'], $r['description'], $r['account'], $r['number'],
            $r['credit'], $r['debit'], $r['balance'],
        ], $report['rows']);

        return ['Cash Book' => $this->exporter->sheetFromRows($headers, $rows)];
    }

    /**
     * @param  array<string, mixed>  $report
     * @return array<string, string>
     */
    private function exportItemLedger(array $report): array
    {
        $headers = ['Date', 'Particular', 'Voucher Type', 'Voucher Number', 'Sale / DR', 'Purchase / CR', 'Balance'];
        $rows = array_map(fn ($r) => [
            $r['date'], $r['particular'], $r['voucher_type'], $r['voucher_number'],
            $r['sale_dr'], $r['purchase_cr'], $r['balance'],
        ], $report['rows']);

        $name = ($report['config']['title'] ?? 'Ledger');

        return [$name => $this->exporter->sheetFromRows($headers, $rows)];
    }

    /**
     * @param  array<string, mixed>  $report
     * @return array<string, string>
     */
    private function exportProfitLoss(array $report): array
    {
        $headers = ['Particular', 'Amount'];
        $rows = array_map(fn ($r) => [$r['particular'], $r['amount']], $report['rows']);

        return ['Profit and Loss' => $this->exporter->sheetFromRows($headers, $rows)];
    }

    /**
     * @param  array<string, mixed>  $report
     * @return array<string, string>
     */
    private function exportInventoryBook(array $report): array
    {
        $headers = ['Item', 'Qty. IN', 'Qty. OUT', 'Pur. Qty.', 'Pur. Amt.', 'Sale Qty.', 'Sale Amt.', 'Closing Qty.', 'Closing Amt.'];
        $rows = array_map(fn ($r) => [
            $r['item'], $r['qty_in'], $r['qty_out'], $r['pur_qty'], $r['pur_amt'],
            $r['sale_qty'], $r['sale_amt'], $r['closing_qty'], $r['closing_amt'],
        ], $report['rows']);

        return ['Inventory Book' => $this->exporter->sheetFromRows($headers, $rows)];
    }

    /**
     * @param  array<string, mixed>  $report
     * @return array<string, string>
     */
    private function exportPartyBalances(array $report): array
    {
        $headers = ['On Date', 'Name', 'Email', 'Phone', 'City', 'State', 'Balance Amount'];
        $rows = array_map(fn ($r) => [
            $r['date'], $r['name'], $r['email'], $r['phone'], $r['city'], $r['state'], $r['balance'],
        ], $report['rows']);

        $title = $report['config']['title'] ?? 'Balances';

        return [$title => $this->exporter->sheetFromRows($headers, $rows)];
    }
}
