<?php

namespace App\Services\Report;

use Carbon\Carbon;
use App\Models\Order;
use App\Models\OrderDetail;
use App\Models\PurchaseItem;
use App\Models\BusinessExpense;
use Illuminate\Http\Request;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Pagination\LengthAwarePaginator;

class FinancialReportService
{
    public function incomeStatement(Request $request): array
    {
        $filters = $this->resolveFilters($request);
        $completedOrders = $this->completedOrders($filters);
        $returnedOrders = $this->returnedOrders($filters);
        $averagePurchaseCosts = $this->averagePurchaseCostMap($filters);

        $totals = [
            'sales_amount' => 0.0,
            'purchase_cost' => 0.0,
            'actual_delivery_cost' => 0.0,
            'actual_return_cost' => 0.0,
            'actual_cod_charge' => 0.0,
            'gross_profit' => 0.0,
            'operating_expenses' => 0.0,
            'net_profit_or_loss' => 0.0,
            'orders_missing_delivery_cost' => 0,
            'orders_missing_return_cost' => 0,
            'orders_missing_cod_charge' => 0,
            'items_missing_purchase_cost_count' => 0,
        ];

        $itemsMissingPurchaseCost = [];

        foreach ($completedOrders as $order) {
            $detailRows = $this->buildDetailRows($order->details, true);
            $deliveryCost = (float) ($order->courierShipment?->actual_delivery_cost ?? 0);
            $codCharge = (float) ($order->courierShipment?->actual_cod_charge ?? 0);
            $collectableAmount = (float) ($order->courierShipment?->collectable_amount ?? 0);

            if ($deliveryCost <= 0) {
                $totals['orders_missing_delivery_cost']++;
            }

            if ($collectableAmount > 0 && $codCharge <= 0) {
                $totals['orders_missing_cod_charge']++;
            }

            $totals['actual_delivery_cost'] += $deliveryCost;
            $totals['actual_cod_charge'] += $codCharge;

            foreach ($detailRows as $row) {
                $averageCost = $averagePurchaseCosts[$row['item_id']] ?? null;
                if ($averageCost === null) {
                    $itemsMissingPurchaseCost[$row['item_id']] = true;
                }

                $totals['sales_amount'] += $row['net_line_amount'];
                $totals['purchase_cost'] += ($averageCost ?? 0) * $row['quantity'];
            }
        }

        foreach ($returnedOrders as $order) {
            $detailRows = $this->buildDetailRows($order->details, false);
            $returnCost = (float) ($order->courierShipment?->actual_return_cost ?? 0);

            if ($returnCost <= 0) {
                $totals['orders_missing_return_cost']++;
            }

            $totals['actual_return_cost'] += $returnCost;

            foreach ($detailRows as $row) {
            }
        }

        $expenseBreakdown = BusinessExpense::query()
            ->when($filters['start_at'] && $filters['end_at'], function ($query) use ($filters) {
                $query->whereBetween('expense_date', [
                    $filters['start_at']->toDateString(),
                    $filters['end_at']->toDateString(),
                ]);
            })
            ->select('category', DB::raw('SUM(amount) as total_amount'))
            ->groupBy('category')
            ->orderBy('category')
            ->get()
            ->map(function ($row) {
                return [
                    'category' => $row->category,
                    'label' => BusinessExpense::categoryOptions()[$row->category] ?? $row->category,
                    'amount' => (float) $row->total_amount,
                ];
            });

        $totals['items_missing_purchase_cost_count'] = count($itemsMissingPurchaseCost);
        $totals['operating_expenses'] = $expenseBreakdown->sum('amount');
        $totals['gross_profit'] = $totals['sales_amount']
            - $totals['purchase_cost']
            - $totals['actual_delivery_cost']
            - $totals['actual_return_cost']
            - $totals['actual_cod_charge'];
        $totals['net_profit_or_loss'] = $totals['gross_profit'] - $totals['operating_expenses'];

        return [
            'filters' => $filters,
            'totals' => $totals,
            'expense_breakdown' => $expenseBreakdown,
        ];
    }

    public function productProfitLoss(Request $request): array
    {
        $filters = $this->resolveFilters($request);
        $completedOrders = $this->completedOrders($filters);
        $returnedOrders = $this->returnedOrders($filters);
        $averagePurchaseCosts = $this->averagePurchaseCostMap($filters);

        $rows = [];
        $missingPurchaseCost = [];
        $missingDeliveryCost = 0;
        $missingReturnCost = 0;
        $missingCodCharge = 0;

        foreach ($completedOrders as $order) {
            $detailRows = $this->buildDetailRows($order->details, true);
            $deliveryCost = (float) ($order->courierShipment?->actual_delivery_cost ?? 0);
            $codCharge = (float) ($order->courierShipment?->actual_cod_charge ?? 0);
            $collectableAmount = (float) ($order->courierShipment?->collectable_amount ?? 0);

            if ($deliveryCost <= 0) {
                $missingDeliveryCost++;
            }

            if ($collectableAmount > 0 && $codCharge <= 0) {
                $missingCodCharge++;
            }

            $baseTotal = collect($detailRows)->sum('allocation_base');

            foreach ($detailRows as $row) {
                $itemId = $row['item_id'];
                $averageCost = $averagePurchaseCosts[$itemId] ?? null;
                if ($averageCost === null) {
                    $missingPurchaseCost[$itemId] = true;
                }

                if (! isset($rows[$itemId])) {
                    $rows[$itemId] = $this->emptyProductRow($row['item_id'], $row['item_name']);
                }

                $rows[$itemId]['total_sold_quantity'] += $row['quantity'];
                $rows[$itemId]['total_selling_amount'] += $row['net_line_amount'];
                $rows[$itemId]['total_purchase_cost'] += ($averageCost ?? 0) * $row['quantity'];
                $rows[$itemId]['allocated_actual_courier_delivery_cost'] += $this->allocateAmount(
                    totalAmount: $deliveryCost,
                    baseTotal: $baseTotal,
                    rowBase: $row['allocation_base']
                );
                $rows[$itemId]['allocated_actual_cod_charge'] += $this->allocateAmount(
                    totalAmount: $codCharge,
                    baseTotal: $baseTotal,
                    rowBase: $row['allocation_base']
                );
            }
        }

        foreach ($returnedOrders as $order) {
            $detailRows = $this->buildDetailRows($order->details, false);
            $returnCost = (float) ($order->courierShipment?->actual_return_cost ?? 0);

            if ($returnCost <= 0) {
                $missingReturnCost++;
            }

            $baseTotal = collect($detailRows)->sum('allocation_base');

            foreach ($detailRows as $row) {
                $itemId = $row['item_id'];

                if (! isset($rows[$itemId])) {
                    $rows[$itemId] = $this->emptyProductRow($row['item_id'], $row['item_name']);
                }

                $rows[$itemId]['allocated_actual_return_charge'] += $this->allocateAmount(
                    totalAmount: $returnCost,
                    baseTotal: $baseTotal,
                    rowBase: $row['allocation_base']
                );
            }
        }

        $rows = collect($rows)->map(function ($row) {
            $row['net_profit_or_loss'] = $row['total_selling_amount']
                - $row['total_purchase_cost']
                - $row['allocated_actual_courier_delivery_cost']
                - $row['allocated_actual_cod_charge']
                - $row['allocated_actual_return_charge'];

            return $row;
        });

        if ($filters['search']) {
            $needle = strtolower($filters['search']);
            $rows = $rows->filter(fn ($row) => str_contains(strtolower($row['product_name']), $needle));
        }

        $rows = $rows->sortBy('net_profit_or_loss')->values();

        $page = max((int) $request->query('page', 1), 1);
        $perPage = (int) config('default_pagination', 25);
        $paginatedRows = new LengthAwarePaginator(
            $rows->forPage($page, $perPage)->values(),
            $rows->count(),
            $perPage,
            $page,
            ['path' => request()->url(), 'query' => request()->query()]
        );

        return [
            'filters' => $filters,
            'rows' => $paginatedRows,
            'summary' => [
                'missing_delivery_cost_orders' => $missingDeliveryCost,
                'missing_return_cost_orders' => $missingReturnCost,
                'missing_cod_charge_orders' => $missingCodCharge,
                'missing_purchase_cost_items' => count($missingPurchaseCost),
            ],
        ];
    }

    public function trialBalance(Request $request): array
    {
        $filters = $this->resolveFilters($request);
        $rows = $this->accountBalanceRows($filters, false)->map(function ($row) {
            $row['balance'] = round($row['debit'] - $row['credit'], 8);

            return $row;
        });

        $totalDebit = round((float) $rows->sum('debit'), 8);
        $totalCredit = round((float) $rows->sum('credit'), 8);

        return [
            'filters' => $filters,
            'rows' => $rows,
            'totals' => [
                'debit' => $totalDebit,
                'credit' => $totalCredit,
                'difference' => round($totalDebit - $totalCredit, 8),
                'is_balanced' => abs($totalDebit - $totalCredit) <= 0.00001,
            ],
        ];
    }

    public function balanceSheet(Request $request): array
    {
        $filters = $this->resolveFilters($request);
        $filters['end_at'] = $filters['end_at'] ?: now()->endOfDay();
        $rows = $this->accountBalanceRows($filters, true);

        $assets = $this->balanceSheetRows($rows, 'asset', true);
        $liabilities = $this->balanceSheetRows($rows, 'liability', false);
        $equity = $this->balanceSheetRows($rows, 'equity', false);

        $incomeBalance = $rows
            ->where('type', 'income')
            ->sum(fn ($row) => $row['credit'] - $row['debit']);
        $expenseBalance = $rows
            ->where('type', 'expense')
            ->sum(fn ($row) => $row['debit'] - $row['credit']);
        $retainedEarnings = round($incomeBalance - $expenseBalance, 8);

        $totalAssets = round((float) $assets->sum('balance'), 8);
        $totalLiabilities = round((float) $liabilities->sum('balance'), 8);
        $totalEquity = round((float) $equity->sum('balance'), 8);
        $liabilitiesAndEquity = round($totalLiabilities + $totalEquity + $retainedEarnings, 8);

        return [
            'filters' => $filters,
            'as_of' => $filters['end_at'],
            'assets' => $assets,
            'liabilities' => $liabilities,
            'equity' => $equity,
            'retained_earnings' => $retainedEarnings,
            'income_balance' => round((float) $incomeBalance, 8),
            'expense_balance' => round((float) $expenseBalance, 8),
            'totals' => [
                'assets' => $totalAssets,
                'liabilities' => $totalLiabilities,
                'equity' => $totalEquity,
                'liabilities_and_equity' => $liabilitiesAndEquity,
                'difference' => round($totalAssets - $liabilitiesAndEquity, 8),
                'is_balanced' => abs($totalAssets - $liabilitiesAndEquity) <= 0.00001,
            ],
        ];
    }

    private function accountBalanceRows(array $filters, bool $asOf): Collection
    {
        $lineTotals = DB::table('accounting_voucher_lines as lines')
            ->join('accounting_vouchers as vouchers', 'vouchers.id', '=', 'lines.voucher_id')
            ->where('vouchers.status', 'posted')
            ->when($asOf && $filters['end_at'], function ($query) use ($filters) {
                $query->whereDate('vouchers.voucher_date', '<=', $filters['end_at']->toDateString());
            })
            ->when(! $asOf && $filters['start_at'] && $filters['end_at'], function ($query) use ($filters) {
                $query->whereBetween('vouchers.voucher_date', [
                    $filters['start_at']->toDateString(),
                    $filters['end_at']->toDateString(),
                ]);
            })
            ->select([
                'lines.account_id',
                DB::raw('SUM(lines.debit) as total_debit'),
                DB::raw('SUM(lines.credit) as total_credit'),
            ])
            ->groupBy('lines.account_id');

        return DB::table('accounting_accounts as accounts')
            ->leftJoinSub($lineTotals, 'totals', 'totals.account_id', '=', 'accounts.id')
            ->select([
                'accounts.id',
                'accounts.code',
                'accounts.name',
                'accounts.type',
                DB::raw('COALESCE(totals.total_debit, 0) as debit'),
                DB::raw('COALESCE(totals.total_credit, 0) as credit'),
            ])
            ->orderBy('accounts.code')
            ->get()
            ->map(fn ($row) => [
                'id' => (int) $row->id,
                'code' => $row->code,
                'name' => $row->name,
                'type' => $row->type,
                'debit' => round((float) $row->debit, 8),
                'credit' => round((float) $row->credit, 8),
            ]);
    }

    private function balanceSheetRows(Collection $rows, string $type, bool $debitNormal): Collection
    {
        return $rows
            ->where('type', $type)
            ->map(function ($row) use ($debitNormal) {
                $row['balance'] = $debitNormal
                    ? round($row['debit'] - $row['credit'], 8)
                    : round($row['credit'] - $row['debit'], 8);

                return $row;
            })
            ->filter(fn ($row) => abs((float) $row['balance']) > 0.00001)
            ->values();
    }

    private function completedOrders(array $filters): Collection
    {
        return Order::query()
            ->with(['details', 'courierShipment'])
            ->when($filters['module_id'], fn ($query) => $query->where('module_id', $filters['module_id']))
            ->when($filters['store_id'], fn ($query) => $query->where('store_id', $filters['store_id']))
            ->whereIn('order_status', ['delivered', 'refund_requested', 'refund_request_canceled'])
            ->when($filters['start_at'] && $filters['end_at'], function ($query) use ($filters) {
                $query->whereBetween(
                    DB::raw('COALESCE(delivered, created_at)'),
                    [$filters['start_at']->format('Y-m-d H:i:s'), $filters['end_at']->format('Y-m-d H:i:s')]
                );
            })
            ->get();
    }

    private function returnedOrders(array $filters): Collection
    {
        return Order::query()
            ->with(['details', 'courierShipment'])
            ->when($filters['module_id'], fn ($query) => $query->where('module_id', $filters['module_id']))
            ->when($filters['store_id'], fn ($query) => $query->where('store_id', $filters['store_id']))
            ->where('order_status', 'returned')
            ->when($filters['start_at'] && $filters['end_at'], function ($query) use ($filters) {
                $query->whereBetween(
                    DB::raw('COALESCE(returned, updated_at, created_at)'),
                    [$filters['start_at']->format('Y-m-d H:i:s'), $filters['end_at']->format('Y-m-d H:i:s')]
                );
            })
            ->get();
    }

    private function averagePurchaseCostMap(array $filters): array
    {
        $rows = PurchaseItem::query()
            ->join('purchases', 'purchases.id', '=', 'purchase_items.purchase_id')
            ->select([
                'purchase_items.item_id',
                DB::raw('SUM(purchase_items.quantity) as total_quantity'),
                DB::raw('SUM(purchase_items.total_amount) as total_amount'),
            ])
            ->when($filters['module_id'], fn ($query) => $query->where('purchases.module_id', $filters['module_id']))
            ->when($filters['store_id'], fn ($query) => $query->where('purchases.store_id', $filters['store_id']))
            ->when($filters['end_at'], fn ($query) => $query->whereDate('purchases.purchase_date', '<=', $filters['end_at']->toDateString()))
            ->groupBy('purchase_items.item_id')
            ->get();

        $costMap = [];
        foreach ($rows as $row) {
            $totalQuantity = (float) $row->total_quantity;
            if ($totalQuantity <= 0) {
                continue;
            }

            $costMap[(int) $row->item_id] = (float) $row->total_amount / $totalQuantity;
        }

        return $costMap;
    }

    private function buildDetailRows(Collection $details, bool $includeRevenue): Collection
    {
        $prepared = $details->map(function (OrderDetail $detail) {
            $grossAmount = (float) $detail->price * (int) $detail->quantity;
            $itemDiscount = (float) $detail->discount_on_item * (int) $detail->quantity;
            $subtotalAfterItemDiscount = max($grossAmount - $itemDiscount, 0);

            $itemSnapshot = $this->itemSnapshot($detail);

            return [
                'item_id' => (int) $detail->item_id,
                'item_name' => $itemSnapshot['name'] ?? ($detail->item?->name ?? 'Deleted Item'),
                'quantity' => (int) $detail->quantity,
                'subtotal_after_item_discount' => $subtotalAfterItemDiscount,
            ];
        });

        $orderBase = $prepared->sum('subtotal_after_item_discount');
        $couponDiscount = (float) ($details->first()?->order?->coupon_discount_amount ?? 0);

        return $prepared->map(function ($row) use ($orderBase, $couponDiscount, $includeRevenue) {
            $couponAllocation = $orderBase > 0
                ? ($couponDiscount * ($row['subtotal_after_item_discount'] / $orderBase))
                : 0;

            $row['allocation_base'] = $row['subtotal_after_item_discount'] > 0
                ? $row['subtotal_after_item_discount']
                : max($row['quantity'], 1);
            $row['coupon_allocation'] = $couponAllocation;
            $row['net_line_amount'] = $includeRevenue ? max($row['subtotal_after_item_discount'] - $couponAllocation, 0) : 0;

            return $row;
        });
    }

    private function itemSnapshot(OrderDetail $detail): array
    {
        $snapshot = $detail->item_details;
        if (is_string($snapshot)) {
            $snapshot = json_decode($snapshot, true);
        }

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

    private function allocateAmount(float $totalAmount, float $baseTotal, float $rowBase): float
    {
        if ($totalAmount <= 0 || $baseTotal <= 0 || $rowBase <= 0) {
            return 0;
        }

        return $totalAmount * ($rowBase / $baseTotal);
    }

    private function emptyProductRow(int $itemId, string $productName): array
    {
        return [
            'item_id' => $itemId,
            'product_name' => $productName,
            'total_sold_quantity' => 0,
            'total_selling_amount' => 0.0,
            'total_purchase_cost' => 0.0,
            'allocated_actual_courier_delivery_cost' => 0.0,
            'allocated_actual_cod_charge' => 0.0,
            'allocated_actual_return_charge' => 0.0,
            'net_profit_or_loss' => 0.0,
        ];
    }

    private function resolveFilters(Request $request): array
    {
        [$startAt, $endAt] = $this->resolveDateRange($request);

        return [
            'module_id' => is_numeric($request->query('module_id')) ? (int) $request->query('module_id') : (config('module.current_module_id') ?: null),
            'store_id' => is_numeric($request->query('store_id')) ? (int) $request->query('store_id') : null,
            'search' => trim((string) $request->query('search', '')),
            'filter' => $request->query('filter', 'all_time'),
            'start_at' => $startAt,
            'end_at' => $endAt,
        ];
    }

    private function resolveDateRange(Request $request): array
    {
        return match ($request->query('filter', 'all_time')) {
            'this_year' => [now()->startOfYear(), now()->endOfYear()],
            'previous_year' => [now()->subYear()->startOfYear(), now()->subYear()->endOfYear()],
            'this_month' => [now()->startOfMonth(), now()->endOfMonth()],
            'this_week' => [now()->startOfWeek(), now()->endOfWeek()],
            'custom' => $this->resolveCustomDates($request),
            default => [null, null],
        };
    }

    private function resolveCustomDates(Request $request): array
    {
        $from = $request->query('from');
        $to = $request->query('to');

        if (blank($from) || blank($to)) {
            return [null, null];
        }

        return [
            Carbon::parse($from)->startOfDay(),
            Carbon::parse($to)->endOfDay(),
        ];
    }
}
