<?php

namespace App\Http\Controllers\Admin\Concerns;

use App\Models\BankAccount;
use App\Models\Currency;
use Illuminate\Support\Facades\DB;

trait BuildsBankTransactionsReport
{
    /**
     * @return array{items: \Illuminate\Support\Collection, total_credit: float, total_debit: float, net: float, currency_hint: string, note: string}
     */
    private function buildBankTransactionsReport(?int $bankAccountId, ?string $dateFrom, ?string $dateTo, string $search): array
    {
        $channelMethods = $this->bankChannelPaymentMethods();
        $defaultCurrency = Currency::getDefaultCode();

        $rows = collect();

        // 1. Customer invoice payments (credit)
        $paymentsQuery = DB::table('payments')
            ->join('invoices', 'payments.invoice_id', '=', 'invoices.id')
            ->join('customers', 'payments.customer_id', '=', 'customers.id')
            ->where('payments.status', 'completed')
            ->whereIn('payments.payment_method', $channelMethods)
            ->select([
                'payments.id',
                DB::raw("'customer_payment' as source_type"),
                'payments.payment_date as transaction_date',
                'payments.amount',
                DB::raw("'credit' as type"),
                DB::raw("CONCAT('Customer payment — Invoice ', invoices.invoice_number) as description"),
                'payments.reference as reference_number',
                'payments.currency',
                'payments.bank_account_id',
                'customers.name as related_party',
            ]);
        if ($bankAccountId) {
            $paymentsQuery->where('payments.bank_account_id', $bankAccountId);
        }
        if ($dateFrom) {
            $paymentsQuery->where('payments.payment_date', '>=', $dateFrom);
        }
        if ($dateTo) {
            $paymentsQuery->where('payments.payment_date', '<=', $dateTo);
        }
        if ($search !== '') {
            $like = '%'.$search.'%';
            $paymentsQuery->where(function ($q) use ($like) {
                $q->where('invoices.invoice_number', 'like', $like)
                    ->orWhere('payments.reference', 'like', $like)
                    ->orWhere('customers.name', 'like', $like);
            });
        }
        $rows = $rows->concat($paymentsQuery->get());

        // 2. Purchases paid from bank (debit) — supplier stored on purchases.supplier_id → customers
        $purchasesQuery = DB::table('purchases')
            ->join('bank_accounts', 'purchases.bank_account_id', '=', 'bank_accounts.id')
            ->leftJoin('customers', 'purchases.supplier_id', '=', 'customers.id')
            ->whereNotNull('purchases.bank_account_id')
            ->whereIn('purchases.payment_method', ['bank_transfer', 'cheque', 'check'])
            ->select([
                'purchases.id',
                DB::raw("'purchase' as source_type"),
                'purchases.purchase_date as transaction_date',
                'purchases.total as amount',
                DB::raw("'debit' as type"),
                DB::raw("CONCAT('Purchase — ', purchases.purchase_number) as description"),
                'purchases.purchase_number as reference_number',
                'bank_accounts.currency',
                'purchases.bank_account_id',
                DB::raw('customers.name as related_party'),
            ]);
        if ($bankAccountId) {
            $purchasesQuery->where('purchases.bank_account_id', $bankAccountId);
        }
        if ($dateFrom) {
            $purchasesQuery->where('purchases.purchase_date', '>=', $dateFrom);
        }
        if ($dateTo) {
            $purchasesQuery->where('purchases.purchase_date', '<=', $dateTo);
        }
        if ($search !== '') {
            $like = '%'.$search.'%';
            $purchasesQuery->where(function ($q) use ($like) {
                $q->where('purchases.purchase_number', 'like', $like)
                    ->orWhere('customers.name', 'like', $like);
            });
        }
        $rows = $rows->concat($purchasesQuery->get());

        // 3. Expenses (bank channel — paid or approved)
        $expensesQuery = DB::table('expenses')
            ->leftJoin('customers', 'expenses.vendor_id', '=', 'customers.id')
            ->whereIn('expenses.payment_method', $channelMethods)
            ->whereIn('expenses.status', ['paid', 'approved'])
            ->select([
                'expenses.id',
                DB::raw("'expense' as source_type"),
                'expenses.expense_date as transaction_date',
                'expenses.total as amount',
                DB::raw("'debit' as type"),
                DB::raw("CONCAT('Expense — ', expenses.expense_number) as description"),
                'expenses.expense_number as reference_number',
                'expenses.currency',
                'expenses.bank_account_id',
                DB::raw('customers.name as related_party'),
            ]);
        if ($bankAccountId) {
            $expensesQuery->where('expenses.bank_account_id', $bankAccountId);
        }
        if ($dateFrom) {
            $expensesQuery->where('expenses.expense_date', '>=', $dateFrom);
        }
        if ($dateTo) {
            $expensesQuery->where('expenses.expense_date', '<=', $dateTo);
        }
        if ($search !== '') {
            $like = '%'.$search.'%';
            $expensesQuery->where(function ($q) use ($like) {
                $q->where('expenses.expense_number', 'like', $like)
                    ->orWhere('expenses.description', 'like', $like)
                    ->orWhere('customers.name', 'like', $like);
            });
        }
        $rows = $rows->concat($expensesQuery->get());

        // 4. Other income (credit)
        $incomeQuery = DB::table('income')
            ->leftJoin('customers', 'income.customer_id', '=', 'customers.id')
            ->where('income.status', 'recorded')
            ->whereIn('income.payment_method', $channelMethods)
            ->select([
                'income.id',
                DB::raw("'income' as source_type"),
                'income.income_date as transaction_date',
                'income.total as amount',
                DB::raw("'credit' as type"),
                DB::raw("CONCAT('Income — ', income.income_number) as description"),
                'income.income_number as reference_number',
                'income.currency',
                'income.bank_account_id',
                DB::raw('customers.name as related_party'),
            ]);
        if ($bankAccountId) {
            $incomeQuery->where('income.bank_account_id', $bankAccountId);
        }
        if ($dateFrom) {
            $incomeQuery->where('income.income_date', '>=', $dateFrom);
        }
        if ($dateTo) {
            $incomeQuery->where('income.income_date', '<=', $dateTo);
        }
        if ($search !== '') {
            $like = '%'.$search.'%';
            $incomeQuery->where(function ($q) use ($like) {
                $q->where('income.income_number', 'like', $like)
                    ->orWhere('income.description', 'like', $like)
                    ->orWhere('customers.name', 'like', $like);
            });
        }
        $rows = $rows->concat($incomeQuery->get());

        // 5. Supplier payments (debit) — bank from payment row or linked purchase
        $supplierPayQuery = DB::table('supplier_payments')
            ->leftJoin('purchases', 'supplier_payments.purchase_id', '=', 'purchases.id')
            ->leftJoin('suppliers', 'supplier_payments.supplier_id', '=', 'suppliers.id')
            ->select([
                'supplier_payments.id',
                DB::raw("'supplier_payment' as source_type"),
                'supplier_payments.payment_date as transaction_date',
                'supplier_payments.paid_amount as amount',
                DB::raw("'debit' as type"),
                DB::raw("CONCAT('Supplier payment — ', supplier_payments.payment_number) as description"),
                'supplier_payments.reference_number',
                DB::raw($this->sqlStringLiteral($defaultCurrency).' as currency'),
                DB::raw('COALESCE(supplier_payments.bank_account_id, purchases.bank_account_id) as bank_account_id'),
                DB::raw('suppliers.name as related_party'),
            ]);
        if ($bankAccountId) {
            $supplierPayQuery->whereRaw('COALESCE(supplier_payments.bank_account_id, purchases.bank_account_id) = ?', [$bankAccountId]);
        }
        if ($dateFrom) {
            $supplierPayQuery->where('supplier_payments.payment_date', '>=', $dateFrom);
        }
        if ($dateTo) {
            $supplierPayQuery->where('supplier_payments.payment_date', '<=', $dateTo);
        }
        if ($search !== '') {
            $like = '%'.$search.'%';
            $supplierPayQuery->where(function ($q) use ($like) {
                $q->where('supplier_payments.payment_number', 'like', $like)
                    ->orWhere('supplier_payments.reference_number', 'like', $like)
                    ->orWhere('suppliers.name', 'like', $like);
            });
        }
        $rows = $rows->concat($supplierPayQuery->get());

        // 6. Petty cash top-ups from bank (debit)
        $pettyQuery = DB::table('petty_cash_transactions as pct')
            ->join('bank_accounts', 'pct.bank_account_id', '=', 'bank_accounts.id')
            ->where('pct.type', 'top_up')
            ->whereNotNull('pct.bank_account_id')
            ->whereIn('pct.status', ['approved', 'pending'])
            ->select([
                'pct.id',
                DB::raw("'petty_cash' as source_type"),
                'pct.transaction_date',
                'pct.amount',
                DB::raw("'debit' as type"),
                DB::raw("CONCAT('Petty cash top-up — ', pct.transaction_number) as description"),
                'pct.transaction_number as reference_number',
                'bank_accounts.currency',
                'pct.bank_account_id',
                DB::raw('NULL as related_party'),
            ]);
        if ($bankAccountId) {
            $pettyQuery->where('pct.bank_account_id', $bankAccountId);
        }
        if ($dateFrom) {
            $pettyQuery->where('pct.transaction_date', '>=', $dateFrom);
        }
        if ($dateTo) {
            $pettyQuery->where('pct.transaction_date', '<=', $dateTo);
        }
        if ($search !== '') {
            $like = '%'.$search.'%';
            $pettyQuery->where(function ($q) use ($like) {
                $q->where('pct.transaction_number', 'like', $like)
                    ->orWhere('pct.description', 'like', $like);
            });
        }
        $rows = $rows->concat($pettyQuery->get());

        $sourceLabels = [
            'customer_payment' => 'Customer payment',
            'purchase' => 'Purchase',
            'expense' => 'Expense',
            'income' => 'Income',
            'supplier_payment' => 'Supplier payment',
            'petty_cash' => 'Petty cash',
        ];

        $bankAccountIds = $rows->pluck('bank_account_id')->filter()->unique()->values();
        $bankAccounts = BankAccount::whereIn('id', $bankAccountIds)->get()->keyBy('id');

        $allTransactions = $rows->map(function ($row) use ($bankAccounts, $sourceLabels, $defaultCurrency) {
            if (empty($row->currency)) {
                $row->currency = $defaultCurrency;
            }
            $row->source_label = $sourceLabels[$row->source_type] ?? $row->source_type;
            if ($row->bank_account_id && isset($bankAccounts[$row->bank_account_id])) {
                $a = $bankAccounts[$row->bank_account_id];
                $row->bank_account_name = $a->bank_name.' — '.$a->account_name;
            } else {
                $row->bank_account_name = null;
            }
            $row->balance = null;

            return $row;
        });

        $sorted = $allTransactions->sort(function ($a, $b) {
            $cmp = strcmp((string) $b->transaction_date, (string) $a->transaction_date);
            if ($cmp !== 0) {
                return $cmp;
            }

            return (int) $b->id <=> (int) $a->id;
        })->values();

        $totalCredit = (float) $sorted->where('type', 'credit')->sum('amount');
        $totalDebit = (float) $sorted->where('type', 'debit')->sum('amount');
        $reportAccount = $bankAccountId ? BankAccount::query()->find($bankAccountId) : null;
        $reportCurrency = $reportAccount?->currency ?? $defaultCurrency;

        return [
            'items' => $sorted,
            'total_credit' => $totalCredit,
            'total_debit' => $totalDebit,
            'net' => $totalCredit - $totalDebit,
            'currency_hint' => $reportCurrency,
            'note' => 'Totals sum all matching rows (use one bank account filter for single-currency reports).',
        ];
    }
}
