<?php

namespace App\Http\Controllers\Backend;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class AdminDashboard extends Controller
{
    public function dashboard(Request $request)
    {
        // Use immutable Carbon derived from now()
        $today = now()->toImmutable();
        $from  = $today->subDays(13)->startOfDay(); // last 14 days window
        $to    = $today->endOfDay();

        // Helper: get daily sums for a table/amount column in the 14-day window
        $dailySum = function (string $table, string $amountCol = 'amount') use ($from, $to) {
            return DB::table($table)
                ->selectRaw('DATE(created_at) as d, SUM('.$amountCol.') as total')
                ->whereBetween('created_at', [$from, $to])
                ->groupBy('d')
                ->pluck('total', 'd'); // ['YYYY-MM-DD' => sum]
        };

        // 12 cards (counts/sums/pending)
        $cards = [
            // Deposits
            'total_deposits_count'      => (int)    DB::table('deposits')->count(),
            'pending_deposits_count'    => (int)    DB::table('deposits')->where('status', 'pending')->count(),
            'approved_deposits_sum'     => (float)  DB::table('deposits')->where('status', 'approved')->sum('amount'),

            // Transfers
            'transfers_count'           => (int)    DB::table('transfers')->count(),
            'transfers_sum'             => (float)  DB::table('transfers')->sum('amount'),

            // Pending status buckets
            'purchases_pending'         => (int)    DB::table('purchases')->where('status', 'pending')->count(),
            'recharges_pending'         => (int)    DB::table('recharges')->where('status', 'pending')->count(),
            'bill_payments_pending'     => (int)    DB::table('bill_payments')->where('status', 'pending')->count(),

            // Loans
            'loan_applications_pending' => (int)    DB::table('loan_applications')->where('status', 'pending')->count(),
            'loan_repayments_due'       => (int)    DB::table('loan_repayments')->where('status', 'due')->count(),

            // Mobile / Bank
            'mobile_txn_pending'        => (int)    DB::table('mobile_transactions')->where('status', 'pending')->count(),
            'bank_transfers_pending'    => (int)    DB::table('bank_transfers')->where('status', 'pending')->count(),
        ];

        // Overall module totals (amount-wise) for bar chart
        $moduleTotals = [
            'Deposits'         => (float) DB::table('deposits')->sum('amount'),
            'Transfers'        => (float) DB::table('transfers')->sum('amount'),
            'Purchases'        => (float) DB::table('purchases')->sum('price'),
            'Recharges'        => (float) DB::table('recharges')->sum('amount'),
            'Bill Payments'    => (float) DB::table('bill_payments')->sum('amount'),
            'Mobile Txns'      => (float) DB::table('mobile_transactions')->sum('amount'),
            'Bank Transfers'   => (float) DB::table('bank_transfers')->sum('amount'),
            'Loan Repayments'  => (float) DB::table('loan_repayments')->sum('amount'),
        ];

        // Build 14 daily labels (YYYY-MM-DD)
        $labels = collect(range(0, 13))
            ->map(fn ($i) => $from->addDays($i)->toDateString())
            ->values();

        // Per-module daily series
        $series = [
            'deposits'        => $dailySum('deposits', 'amount'),
            'transfers'       => $dailySum('transfers', 'amount'),
            'purchases'       => DB::table('purchases')->selectRaw('DATE(created_at) as d, SUM(price) as total')
                                   ->whereBetween('created_at', [$from, $to])
                                   ->groupBy('d')->pluck('total', 'd'),
            'recharges'       => $dailySum('recharges', 'amount'),
            'bill_payments'   => $dailySum('bill_payments', 'amount'),
            'mobile_txns'     => $dailySum('mobile_transactions', 'amount'),
            'bank_transfers'  => $dailySum('bank_transfers', 'amount'),
            'loan_repayments' => $dailySum('loan_repayments', 'amount'),
        ];

        // Sum across modules per day for the line chart
        $dailyTotals = $labels->map(function ($d) use ($series) {
            $sum = 0.0;
            foreach ($series as $s) {
                $sum += (float) ($s[$d] ?? 0);
            }
            return round($sum, 2);
        })->values();

        return view('backend.dashboard', [
            'cards'        => $cards,
            'moduleTotals' => $moduleTotals,
            'labels'       => $labels,
            'dailyTotals'  => $dailyTotals,
        ]);
    }
}
