<?php

namespace App\Http\Controllers;

use Illuminate\Http\RedirectResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\View\View;

class UserFinanceController extends Controller
{
    private function nextNumber(string $prefix, string $table): string
    {
        $count = DB::table($table)->whereDate('created_at', today())->count() + 1;
        return $prefix . '-' . now()->format('Ymd') . '-' . str_pad((string) $count, 4, '0', STR_PAD_LEFT);
    }

    public function dashboard(): View
    {
        $user = Auth::user();
        $summary = [
            'claims' => DB::table('expense_claims')->where('user_id', $user->id)->count(),
            'pending_claims' => DB::table('expense_claims')->where('user_id', $user->id)->where('status', 'submitted')->count(),
            'paid_claims' => DB::table('expense_claims')->where('user_id', $user->id)->where('status', 'paid')->selectRaw('COALESCE(SUM(amount + tax_amount), 0) as total')->value('total'),
            'advances' => DB::table('cash_advances')->where('user_id', $user->id)->count(),
        ];
        $claims = DB::table('expense_claims')->where('user_id', $user->id)->latest()->limit(5)->get();
        $advances = DB::table('cash_advances')->where('user_id', $user->id)->latest()->limit(5)->get();
        return view('user.dashboard', compact('summary', 'claims', 'advances'));
    }

    public function profile(): View
    {
        return view('user.profile');
    }

    public function updateProfile(Request $request): RedirectResponse
    {
        $data = $request->validate([
            'name' => ['required', 'string', 'max:255'],
            'phone' => ['nullable', 'string', 'max:40'],
            'department' => ['nullable', 'string', 'max:120'],
            'position' => ['nullable', 'string', 'max:120'],
        ]);

        Auth::user()->update($data);
        return back()->with('success', 'Profile updated.');
    }

    public function claims(): View
    {
        $claims = DB::table('expense_claims')
            ->leftJoin('chart_of_accounts', 'chart_of_accounts.id', '=', 'expense_claims.chart_of_account_id')
            ->where('expense_claims.user_id', Auth::id())
            ->select('expense_claims.*', 'chart_of_accounts.name as account_name')
            ->latest('expense_claims.created_at')->get();
        $expenseAccounts = DB::table('chart_of_accounts')->where('type', 'expense')->orderBy('code')->get();
        return view('user.claims', compact('claims', 'expenseAccounts'));
    }

    public function storeClaim(Request $request): RedirectResponse
    {
        $data = $request->validate([
            'chart_of_account_id' => ['nullable', 'exists:chart_of_accounts,id'],
            'expense_date' => ['required', 'date'],
            'title' => ['required', 'string', 'max:180'],
            'description' => ['nullable', 'string'],
            'amount' => ['required', 'numeric', 'min:1'],
            'tax_amount' => ['nullable', 'numeric', 'min:0'],
            'receipt_reference' => ['nullable', 'string', 'max:160'],
        ]);

        DB::table('expense_claims')->insert(array_merge($data, [
            'claim_number' => $this->nextNumber('CLM', 'expense_claims'),
            'user_id' => Auth::id(),
            'tax_amount' => $data['tax_amount'] ?? 0,
            'status' => 'submitted',
            'created_at' => now(),
            'updated_at' => now(),
        ]));
        return back()->with('success', 'Expense claim submitted.');
    }

    public function advances(): View
    {
        $advances = DB::table('cash_advances')->where('user_id', Auth::id())->latest()->get();
        return view('user.advances', compact('advances'));
    }

    public function storeAdvance(Request $request): RedirectResponse
    {
        $data = $request->validate([
            'needed_date' => ['nullable', 'date'],
            'amount' => ['required', 'numeric', 'min:1'],
            'purpose' => ['required', 'string'],
        ]);

        DB::table('cash_advances')->insert(array_merge($data, [
            'advance_number' => $this->nextNumber('ADV', 'cash_advances'),
            'user_id' => Auth::id(),
            'status' => 'submitted',
            'created_at' => now(),
            'updated_at' => now(),
        ]));
        return back()->with('success', 'Cash advance request submitted.');
    }
}
