<?php

namespace App\Services;

use App\Models\Account;
use Illuminate\Support\Carbon;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

class DashboardService
{
    public function __construct(
        private LedgerReportService $ledgerReports,
    ) {}

    public function reports(int $companyId): array
    {
        $range = $this->ledgerReports->defaultDateRange($companyId);
        $asOfDate = $range['date_to'];
        $fiscalStart = $range['date_from'];

        $balanceSheet = $this->ledgerReports->balanceSheet($companyId, $asOfDate, $fiscalStart);
        $fundPosition = $this->fundPosition($companyId, $asOfDate);
        $workingCapital = $this->workingCapital($balanceSheet);
        $monthlySeries = $this->monthlyIncomeExpense($companyId, 6);
        $profitRatio = $this->monthlyProfitRatio($monthlySeries);
        $expenseBreakdown = $this->expenseBreakdown($companyId, $fiscalStart, $asOfDate);
        $topCustomerOutstanding = $this->topCustomerOutstanding($companyId, 5);
        $topVendorOutstanding = $this->topVendorOutstanding($companyId, 5);

        return compact(
            'fundPosition',
            'workingCapital',
            'monthlySeries',
            'profitRatio',
            'expenseBreakdown',
            'topCustomerOutstanding',
            'topVendorOutstanding',
            'asOfDate',
        );
    }

    public function kpis(int $companyId, string $asOfDate): array
    {
        $range = $this->ledgerReports->defaultDateRange($companyId);
        $balanceSheet = $this->ledgerReports->balanceSheet($companyId, $asOfDate, $range['date_from']);
        $fund = $this->fundPosition($companyId, $asOfDate);

        return [
            'cash_bank_balance'  => $fund['total'],
            'total_receivables'  => $balanceSheet['groups']['accounts_receivable']['total'] ?? 0,
            'total_payables'     => ($balanceSheet['groups']['accounts_payable']['total'] ?? 0)
                + ($balanceSheet['groups']['credit_card']['total'] ?? 0),
            'pending_approvals'  => (int) DB::table('vouchers')
                ->where('company_id', $companyId)
                ->whereNull('deleted_at')
                ->where('status', 'submitted')
                ->count(),
            'stock_alerts_count' => 0,
        ];
    }

    private function fundPosition(int $companyId, string $asOfDate): array
    {
        $accounts = Account::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->whereNull('deleted_at')
            ->where('is_group', false)
            ->where(fn ($q) => $q->where('is_cash', true)->orWhere('is_bank', true))
            ->get(['id', 'is_cash', 'is_bank']);

        if ($accounts->isEmpty()) {
            return [
                'cash' => 0.0,
                'bank' => 0.0,
                'total' => 0.0,
                'cash_pct' => 0,
                'bank_pct' => 0,
            ];
        }

        $balances = $this->accountBalancesAsOf($companyId, $asOfDate, $accounts->pluck('id'));

        $cash = 0.0;
        $bank = 0.0;
        foreach ($accounts as $account) {
            $amount = (float) ($balances[$account->id] ?? 0);
            if ($account->is_cash) {
                $cash += $amount;
            }
            if ($account->is_bank) {
                $bank += $amount;
            }
        }

        $total = $cash + $bank;
        $cashPct = $total > 0 ? round($cash / $total * 100) : 0;
        $bankPct = $total > 0 ? 100 - $cashPct : 0;

        return [
            'cash' => round($cash, 2),
            'bank' => round($bank, 2),
            'total' => round($total, 2),
            'cash_pct' => $cashPct,
            'bank_pct' => $bankPct,
        ];
    }

    private function workingCapital(array $balanceSheet): array
    {
        $groups = $balanceSheet['groups'];
        $currentAssets = ($groups['bank']['total'] ?? 0)
            + ($groups['accounts_receivable']['total'] ?? 0)
            + ($groups['other_current_assets']['total'] ?? 0);
        $currentLiabilities = ($groups['accounts_payable']['total'] ?? 0)
            + ($groups['credit_card']['total'] ?? 0)
            + ($groups['other_current_liabilities']['total'] ?? 0);
        $net = $currentAssets - $currentLiabilities;
        $total = $currentAssets + $currentLiabilities;
        $caPct = $total > 0 ? round($currentAssets / $total * 100) : 0;
        $clPct = $total > 0 ? 100 - $caPct : 0;

        return [
            'current_assets' => round($currentAssets, 2),
            'current_liabilities' => round($currentLiabilities, 2),
            'net' => round($net, 2),
            'ca_pct' => $caPct,
            'cl_pct' => $clPct,
        ];
    }

    private function monthlyIncomeExpense(int $companyId, int $months): array
    {
        $end = now()->endOfMonth();
        $start = now()->subMonths($months - 1)->startOfMonth();

        $rows = DB::table('ledger_entries as le')
            ->join('accounts as a', 'a.id', '=', 'le.account_id')
            ->where('le.company_id', $companyId)
            ->whereNull('a.deleted_at')
            ->where('a.is_group', false)
            ->whereIn('a.type', ['income', 'expense'])
            ->whereBetween('le.entry_date', [$start->toDateString(), $end->toDateString()])
            ->selectRaw("DATE_FORMAT(le.entry_date, '%Y-%m') as month_key")
            ->selectRaw('SUM(CASE WHEN a.type = ? THEN le.credit - le.debit ELSE 0 END) as income', ['income'])
            ->selectRaw('SUM(CASE WHEN a.type = ? THEN le.debit - le.credit ELSE 0 END) as expense', ['expense'])
            ->groupBy('month_key')
            ->orderBy('month_key')
            ->get()
            ->keyBy('month_key');

        $labels = [];
        $income = [];
        $expense = [];

        for ($i = 0; $i < $months; $i++) {
            $month = $start->copy()->addMonths($i);
            $key = $month->format('Y-m');
            $row = $rows->get($key);
            $labels[] = $month->format('M');
            $income[] = round((float) ($row->income ?? 0), 2);
            $expense[] = round((float) ($row->expense ?? 0), 2);
        }

        return compact('labels', 'income', 'expense');
    }

    private function monthlyProfitRatio(array $monthlySeries): array
    {
        $ratios = [];
        foreach ($monthlySeries['labels'] as $i => $label) {
            $inc = (float) $monthlySeries['income'][$i];
            $exp = (float) $monthlySeries['expense'][$i];
            $net = $inc - $exp;

            if (abs($inc) < 0.01) {
                $ratios[] = $exp > 0 ? null : 0;
            } else {
                $ratios[] = round($net / $inc * 100, 1);
            }
        }

        return [
            'labels' => $monthlySeries['labels'],
            'ratios' => $ratios,
        ];
    }

    private function expenseBreakdown(int $companyId, string $dateFrom, string $dateTo): array
    {
        $accounts = Account::withoutGlobalScopes()
            ->where('company_id', $companyId)
            ->whereNull('deleted_at')
            ->where('is_group', false)
            ->where('type', 'expense')
            ->get(['id', 'name', 'sub_type', 'detail_type']);

        if ($accounts->isEmpty()) {
            return ['labels' => [], 'amounts' => [], 'total' => 0];
        }

        $amounts = DB::table('ledger_entries')
            ->where('company_id', $companyId)
            ->whereIn('account_id', $accounts->pluck('id'))
            ->whereBetween('entry_date', [$dateFrom, $dateTo])
            ->select('account_id')
            ->selectRaw('COALESCE(SUM(debit),0) - COALESCE(SUM(credit),0) as amount')
            ->groupBy('account_id')
            ->get()
            ->keyBy('account_id');

        $items = $accounts->map(function (Account $account) use ($amounts) {
            $amount = (float) ($amounts->get($account->id)->amount ?? 0);

            return [
                'name' => $account->name,
                'amount' => round($amount, 2),
            ];
        })
            ->filter(fn (array $item) => $item['amount'] > 0.009)
            ->sortByDesc('amount')
            ->values();

        $top = $items->take(8);
        $others = $items->slice(8)->sum('amount');

        $labels = $top->pluck('name')->all();
        $values = $top->pluck('amount')->all();

        if ($others > 0.009) {
            $labels[] = 'Others';
            $values[] = round($others, 2);
        }

        return [
            'labels' => $labels,
            'amounts' => $values,
            'total' => round(array_sum($values), 2),
        ];
    }

    private function topCustomerOutstanding(int $companyId, int $limit): Collection
    {
        if (!Schema::hasTable('sales_invoices')) {
            return collect();
        }

        return DB::table('sales_invoices as si')
            ->join('customers as c', 'c.id', '=', 'si.customer_id')
            ->join('vouchers as v', 'v.id', '=', 'si.voucher_id')
            ->where('si.company_id', $companyId)
            ->whereNull('c.deleted_at')
            ->whereNull('v.deleted_at')
            ->where('v.status', 'posted')
            ->where('si.amount_due', '>', 0)
            ->whereIn('si.payment_status', ['unpaid', 'partial', 'overdue'])
            ->groupBy('c.id', 'c.name')
            ->select([
                'c.id as customer_id',
                'c.name',
                DB::raw('SUM(si.amount_due) as amount_due'),
                DB::raw('MIN(COALESCE(si.due_date, v.voucher_date)) as oldest_open_date'),
            ])
            ->orderByDesc('amount_due')
            ->limit($limit)
            ->get()
            ->map(function ($row) {
                $openFrom = Carbon::parse($row->oldest_open_date);

                return [
                    'name' => $row->name,
                    'amount_due' => round((float) $row->amount_due, 2),
                    'days_open' => max(0, $openFrom->diffInDays(now())),
                ];
            });
    }

    private function topVendorOutstanding(int $companyId, int $limit): Collection
    {
        if (!Schema::hasTable('bills')) {
            return collect();
        }

        return DB::table('bills as b')
            ->join('vendors as vnd', 'vnd.id', '=', 'b.vendor_id')
            ->join('vouchers as v', 'v.id', '=', 'b.voucher_id')
            ->where('b.company_id', $companyId)
            ->whereNull('vnd.deleted_at')
            ->whereNull('v.deleted_at')
            ->where('v.status', 'posted')
            ->where('b.amount_due', '>', 0)
            ->whereIn('b.payment_status', ['unpaid', 'partial', 'overdue'])
            ->groupBy('vnd.id', 'vnd.name')
            ->select([
                'vnd.id as vendor_id',
                'vnd.name',
                DB::raw('SUM(b.amount_due) as amount_due'),
                DB::raw('MIN(COALESCE(b.due_date, v.voucher_date)) as oldest_open_date'),
            ])
            ->orderByDesc('amount_due')
            ->limit($limit)
            ->get()
            ->map(function ($row) {
                $openFrom = Carbon::parse($row->oldest_open_date);

                return [
                    'name' => $row->name,
                    'amount_due' => round((float) $row->amount_due, 2),
                    'days_open' => max(0, $openFrom->diffInDays(now())),
                ];
            });
    }

    private function accountBalancesAsOf(int $companyId, string $asOfDate, Collection $accountIds): array
    {
        if ($accountIds->isEmpty()) {
            return [];
        }

        return DB::table('ledger_entries')
            ->where('company_id', $companyId)
            ->whereIn('account_id', $accountIds)
            ->whereDate('entry_date', '<=', $asOfDate)
            ->select('account_id')
            ->selectRaw('COALESCE(SUM(debit),0) - COALESCE(SUM(credit),0) as balance')
            ->groupBy('account_id')
            ->pluck('balance', 'account_id')
            ->map(fn ($v) => (float) $v)
            ->all();
    }
}
