<?php

namespace App\Http\Controllers;

use App\Models\Loan;
use App\Models\Member;
use App\Models\User;
use App\Models\Income;
use App\Models\LoanInstallment;
use App\Models\AuditLog;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\DB;

class LoanController extends Controller
{
    /**
     * Constructor - Increase memory limit for this controller
     */
    public function __construct()
    {
        // Increase memory limit for this controller
        @ini_set('memory_limit', env('MEMORY_LIMIT', '256M'));
        @ini_set('max_execution_time', 300);
    }
    
    /**
     * Display a listing of the resource.
     */
    public function index(Request $request)
    {
        $query = Loan::with(['member', 'guarantor1', 'guarantor2']);
        
        // ফিল্টার
        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }
        
        if ($request->filled('member_id')) {
            $query->where('member_id', $request->member_id);
        }
        
        if ($request->filled('loan_type')) {
            $query->where('loan_type', $request->loan_type);
        }
        
        if ($request->filled('from_date')) {
            $query->whereDate('start_date', '>=', $request->from_date);
        }
        
        if ($request->filled('to_date')) {
            $query->whereDate('start_date', '<=', $request->to_date);
        }
        
        if ($request->filled('search')) {
            $search = $request->search;
            $query->where(function($q) use ($search) {
                $q->where('loan_number', 'like', "%{$search}%")
                  ->orWhereHas('member', function($q) use ($search) {
                      $q->where('name', 'like', "%{$search}%")
                        ->orWhere('member_id', 'like', "%{$search}%")
                        ->orWhere('phone', 'like', "%{$search}%");
                  });
            });
        }
        
        $loans = $query->orderBy('created_at', 'desc')->paginate(20);
        $members = Member::active()->approved()->get();
        
        // ঋণের সারাংশ
        $totalLoans = $query->count();
        $totalLoanAmount = $query->sum('loan_amount');
        $totalPayable = $query->sum('total_payable');
        $totalPaid = $query->sum('paid_amount');
        
        return view('loans.index', compact('loans', 'members', 'totalLoans', 'totalLoanAmount', 'totalPayable', 'totalPaid'));
    }

    /**
     * Show the form for creating a new resource.
     */
    public function create()
    {
        $members = Member::active()->approved()
            ->with(['loans' => function($query) {
                $query->whereIn('status', ['approved', 'disbursed', 'active']);
            }])
            ->get();
        
        $guarantors = Member::active()->approved()->get();
        
        return view('loans.create', compact('members', 'guarantors'));
    }

    /**
     * Store a newly created resource in storage.
     */
    public function store(Request $request)
    {
        \Log::info('Loan Store Request:', $request->all());
        
        // আপনার ব্যবসায়িক নিয়ম অনুযায়ী validation rules
        $validator = Validator::make($request->all(), [
            'member_id' => 'required|exists:members,member_id',
            'loan_type' => 'required|in:regular,emergency,business,agriculture,education,medical,housing,vehicle',
            'loan_amount' => 'required|numeric|min:1000|max:500000',
            'duration_months' => 'required|integer|min:3|max:3',
            'purpose' => 'required|string|max:500',
            'loan_date' => 'required|date',
            'guarantor1_id' => 'required|exists:members,member_id',
            'guarantor2_id' => 'required|exists:members,member_id|different:guarantor1_id',
            'notes' => 'nullable|string|max:1000',
        ], [
            'guarantor2_id.different' => 'একই ব্যক্তিকে দুটি গ্যারান্টর হিসেবে নির্বাচন করা যাবে না।',
            'duration_months.min' => 'সকল ঋণ ৩ মাসের জন্য',
            'duration_months.max' => 'সকল ঋণ ৩ মাসের জন্য',
        ]);

        if ($validator->fails()) {
            return redirect()->back()
                ->withErrors($validator)
                ->withInput();
        }

        DB::beginTransaction();
        
        try {
            $data = $request->all();
            
            // ✅ Model-এর boot() মেথড loan_number নিজে জেনারেট করবে
            // ✅ তাই এখানে loan_number সেট করার দরকার নেই
            
            $data['created_by'] = auth()->id();
            
            // Create loan - loan number auto-generated in model
            $loan = Loan::create($data);
            
            // Installments auto-created in model's created event
            
            // Audit log
            AuditLog::create([
                'user_id' => auth()->id(),
                'user_type' => auth()->user()->user_type,
                'action' => 'নতুন লোন আবেদন (৩ মাসের জন্য)',
                'table_name' => 'loans',
                'record_id' => $loan->id,
                'old_values' => null,
                'new_values' => json_encode($data),
                'ip_address' => $request->ip(),
            ]);
            
            DB::commit();
            
            return redirect()->route('loans.show', $loan)
                ->with('success', 'লোন আবেদন সফলভাবে জমা হয়েছে। লোন নম্বর: ' . $loan->loan_number);
                
        } catch (\Illuminate\Database\QueryException $e) {
            DB::rollback();
            
            \Log::error('Loan Store Query Error: ' . $e->getMessage());
            \Log::error('Error Info: ' . json_encode($e->errorInfo));
            
            $errorMessage = 'ডাটাবেস ত্রুটি: ';
            if (isset($e->errorInfo[1])) {
                switch ($e->errorInfo[1]) {
                    case 1062:
                        $errorMessage .= 'লোন নম্বর ডুপ্লিকেট';
                        break;
                    case 1364:
                        $errorMessage .= 'কিছু প্রয়োজনীয় তথ্য মিসিং আছে';
                        break;
                    default:
                        $errorMessage .= $e->getMessage();
                }
            } else {
                $errorMessage .= $e->getMessage();
            }
            
            return redirect()->back()
                ->withInput()
                ->with('error', $errorMessage);
                
        } catch (\Exception $e) {
            DB::rollback();
            
            \Log::error('Loan Store Error: ' . $e->getMessage());
            \Log::error($e->getTraceAsString());
            
            return redirect()->back()
                ->withInput()
                ->with('error', 'ত্রুটি: ' . $e->getMessage());
        }
    }

    /**
     * Create installments according to business rules
     */
    private function createInstallments(Loan $loan)
    {
        $principal = (float) $loan->loan_amount;
        $monthlyInterest = ($principal * $loan->interest_rate) / 100;
        $startDate = \Carbon\Carbon::parse($loan->start_date);
        
        // First month: Only interest
        LoanInstallment::create([
            'loan_id' => $loan->id,
            'installment_number' => 1,
            'due_date' => $startDate->copy()->addMonth(),
            'amount' => $monthlyInterest,
            'principal_amount' => 0,
            'interest_amount' => $monthlyInterest,
            'status' => 'pending',
            'description' => 'প্রথম মাস - শুধু মুনাফা',
        ]);
        
        // Second month: Only interest
        LoanInstallment::create([
            'loan_id' => $loan->id,
            'installment_number' => 2,
            'due_date' => $startDate->copy()->addMonths(2),
            'amount' => $monthlyInterest,
            'principal_amount' => 0,
            'interest_amount' => $monthlyInterest,
            'status' => 'pending',
            'description' => 'দ্বিতীয় মাস - শুধু মুনাফা',
        ]);
        
        // Third month: Interest + Principal
        LoanInstallment::create([
            'loan_id' => $loan->id,
            'installment_number' => 3,
            'due_date' => $startDate->copy()->addMonths(3),
            'amount' => $monthlyInterest + $principal,
            'principal_amount' => $principal,
            'interest_amount' => $monthlyInterest,
            'status' => 'pending',
            'description' => 'তৃতীয় মাস - মুনাফা + আসল',
        ]);
        
        \Log::info('Installments created for loan:', [
            'loan_id' => $loan->id,
            'installment_1' => $monthlyInterest,
            'installment_2' => $monthlyInterest,
            'installment_3' => $monthlyInterest + $principal,
        ]);
    }

    /**
     * Display the specified resource.
     */
    public function show(Loan $loan)
    {
        $installments = $loan->installments()->orderBy('installment_number')->get();
        
        return view('loans.show', compact('loan', 'installments'));
    }

    /**
     * Show the form for editing the specified resource.
     */
    public function edit(Loan $loan)
    {
        if (!in_array($loan->status, ['pending', 'rejected'])) {
            return redirect()->back()
                ->with('error', 'শুধুমাত্র পেন্ডিং বা রিজেক্টেড লোন এডিট করা যাবে।');
        }
        
        $members = Member::active()->approved()->get();
        $guarantors = Member::active()->approved()->get();
        
        return view('loans.edit', compact('loan', 'members', 'guarantors'));
    }

    /**
     * Update the specified resource in storage.
     */
    public function update(Request $request, Loan $loan)
    {
        if (!in_array($loan->status, ['pending', 'rejected'])) {
            return redirect()->back()
                ->with('error', 'শুধুমাত্র পেন্ডিং বা রিজেক্টেড লোন আপডেট করা যাবে।');
        }
        
        // আপনার ব্যবসায়িক নিয়ম অনুযায়ী validation rules
        $validator = Validator::make($request->all(), [
            'loan_type' => 'required|in:regular,emergency,business,agriculture,education,medical,housing,vehicle',
            'loan_amount' => 'required|numeric|min:1000|max:500000',
            'duration_months' => 'required|integer|min:3|max:3',
            'purpose' => 'required|string|max:500',
            'loan_date' => 'required|date',
            'guarantor1_id' => 'required|exists:members,member_id',
            'guarantor2_id' => 'required|exists:members,member_id|different:guarantor1_id',
            'notes' => 'nullable|string|max:1000',
        ], [
            'guarantor2_id.different' => 'একই ব্যক্তিকে দুটি গ্যারান্টর হিসেবে নির্বাচন করা যাবে না।',
            'duration_months.min' => 'সকল ঋণ ৩ মাসের জন্য',
            'duration_months.max' => 'সকল ঋণ ৩ মাসের জন্য',
        ]);

        if ($validator->fails()) {
            return redirect()->back()
                ->withErrors($validator)
                ->withInput();
        }

        DB::beginTransaction();
        
        try {
            $oldData = $loan->toArray();
            $data = $request->all();
            
            // Fixed values
            $data['interest_rate'] = 6;
            $data['duration_months'] = 3;
            $data['installment_count'] = 3;
            $data['start_date'] = $data['loan_date'];
            
            // Calculate dates
            if (!empty($data['start_date'])) {
                $startDate = \Carbon\Carbon::parse($data['start_date']);
                $data['end_date'] = $startDate->addMonths(3)->format('Y-m-d');
            }
            
            // Recalculate loan details
            $principal = (float) $data['loan_amount'];
            $monthlyInterest = ($principal * 6) / 100;
            $data['total_interest'] = $monthlyInterest * 3;
            $data['total_payable'] = $principal + $data['total_interest'];
            $data['monthly_installment'] = $data['total_payable'] / 3;
            
            // Update loan
            $loan->update($data);
            
            // Delete existing installments and create new ones
            $loan->installments()->delete();
            $this->createInstallments($loan);
            
            // Audit log
            AuditLog::create([
                'user_id' => auth()->id(),
                'user_type' => auth()->user()->user_type,
                'action' => 'লোন তথ্য আপডেট',
                'table_name' => 'loans',
                'record_id' => $loan->id,
                'old_values' => json_encode($oldData),
                'new_values' => json_encode($data),
                'ip_address' => $request->ip(),
            ]);
            
            DB::commit();
            
            return redirect()->route('loans.show', $loan)
                ->with('success', 'লোন তথ্য সফলভাবে আপডেট করা হয়েছে।');
                
        } catch (\Exception $e) {
            DB::rollback();
            
            \Log::error('Loan Update Error: ' . $e->getMessage());
            
            return redirect()->back()
                ->withInput()
                ->with('error', 'আপডেট করতে সমস্যা: ' . $e->getMessage());
        }
    }

    /**
     * Remove the specified resource from storage.
     */
    public function destroy(Loan $loan)
    {
        if (!in_array($loan->status, ['pending', 'rejected'])) {
            return redirect()->back()
                ->with('error', 'শুধুমাত্র পেন্ডিং বা রিজেক্টেড লোন ডিলিট করা যাবে।');
        }
        
        DB::beginTransaction();
        
        try {
            // Delete installments first
            $loan->installments()->delete();
            
            $loanNumber = $loan->loan_number;
            $memberName = $loan->member->name ?? 'Unknown';
            
            // Delete loan
            $loan->delete();
            
            // Audit log
            AuditLog::create([
                'user_id' => auth()->id(),
                'user_type' => auth()->user()->user_type,
                'action' => 'লোন ডিলিট',
                'table_name' => 'loans',
                'record_id' => $loan->id,
                'old_values' => json_encode([
                    'loan_number' => $loanNumber,
                    'member_name' => $memberName,
                    'loan_amount' => $loan->loan_amount
                ]),
                'new_values' => null,
                'ip_address' => request()->ip(),
            ]);
            
            DB::commit();
            
            return redirect()->route('loans.index')
                ->with('success', 'লোন সফলভাবে ডিলিট করা হয়েছে।');
                
        } catch (\Exception $e) {
            DB::rollback();
            
            \Log::error('Loan Delete Error: ' . $e->getMessage());
            
            return redirect()->back()
                ->with('error', 'ডিলিট করতে সমস্যা: ' . $e->getMessage());
        }
    }

    
    /**
 * ঋণ অনুমোদন - FINAL OPTIMIZED VERSION
 */
public function approve(Request $request, Loan $loan)
{
    // Direct validation without calling canBeApproved()
    if ($loan->status != 'pending') {
        return redirect()->back()
            ->with('error', 'শুধুমাত্র পেন্ডিং লোন অনুমোদন করা যাবে।');
    }
    
    if (!$loan->guarantor1_id || !$loan->guarantor2_id) {
        return redirect()->back()
            ->with('error', 'লোনের জন্য দুজন গ্যারান্টর প্রয়োজন।');
    }
    
    // Check member status using simple DB query
    try {
        $memberStatus = DB::table('members')
            ->where('member_id', $loan->member_id)
            ->value('status');
            
        if ($memberStatus !== 'active') {
            return redirect()->back()
                ->with('error', 'সদস্য সক্রিয় নেই।');
        }
        
    } catch (\Exception $e) {
        return redirect()->back()
            ->with('error', 'সদস্য তথ্য যাচাই করতে সমস্যা: ' . substr($e->getMessage(), 0, 100));
    }
    
    DB::beginTransaction();
    
    try {
        // Disable model events temporarily
        \App\Models\Loan::withoutEvents(function () use ($loan, $request) {
            $loan->status = 'approved';
            $loan->approved_by = auth()->id();
            $loan->approved_date = now();
            
            if ($request->remarks) {
                $loan->notes = ($loan->notes ?? '') . "\nঅনুমোদন মন্তব্য: " . 
                    substr($request->remarks, 0, 500);
            }
            
            $loan->save();
        });
        
        // Simple audit log using DB::table (not model)
        DB::table('audit_logs')->insert([
            'user_id' => auth()->id(),
            'user_type' => auth()->user()->user_type,
            'action' => 'লোন অনুমোদন',
            'table_name' => 'loans',
            'record_id' => $loan->id,
            'old_values' => json_encode(['status' => 'pending']),
            'new_values' => json_encode([
                'status' => 'approved',
                'approved_by' => auth()->id(),
                'approved_date' => now()->format('Y-m-d H:i:s')
            ]),
            'ip_address' => $request->ip(),
            'created_at' => now(),
            'updated_at' => now()
        ]);
        
        DB::commit();
        
        return redirect()->back()
            ->with('success', 'লোন সফলভাবে অনুমোদিত হয়েছে। লোন নম্বর: ' . $loan->loan_number);
            
    } catch (\Exception $e) {
        DB::rollback();
        
        error_log('Loan Approve Error: ' . substr($e->getMessage(), 0, 200));
        
        return redirect()->back()
            ->with('error', 'অনুমোদন করতে সমস্যা: ' . substr($e->getMessage(), 0, 100));
    }
}

    
/**
 * ঋণ বিতরণ - ENUM COMPATIBLE VERSION
 */
public function disburse(Request $request, $id)
{
    try {
        DB::beginTransaction();
        
        // 1. Get loan
        $loan = DB::table('loans')->where('id', $id)->first();
        
        if (!$loan) {
            return redirect()->back()->with('error', 'লোন পাওয়া যায়নি।');
        }
        
        if ($loan->status != 'approved') {
            return redirect()->back()->with('error', 'শুধুমাত্র অনুমোদিত লোন বিতরণ করা যাবে।');
        }
        
        // 2. Simple validation
        $request->validate([
            'disbursement_method' => 'required|in:cash,bank_transfer,cheque',
            'transaction_id' => 'nullable|string|max:100',
            'remarks' => 'nullable|string|max:500',
        ]);
        
        // 3. Get member name
        $member = DB::table('members')
            ->where('member_id', $loan->member_id)
            ->select('name')
            ->first();
        $memberName = $member->name ?? 'অজানা';
        
        // 4. Update loan
        DB::table('loans')
            ->where('id', $id)
            ->update([
                'status' => 'disbursed',
                'disbursed_by' => auth()->id(),
                'disbursed_date' => now(),
                'updated_at' => now(),
            ]);
        
        // 5. Insert income record - Use existing ENUM value
        // ENUM values based on your table: 'membership_fee', 'monthly_saving', 'loan_interest', etc.
        // Since loan disbursement is an expense, use the closest available category
        $incomeData = [
            'income_number' => 'INC' . date('YmdHis') . rand(100, 999),
            'income_date' => now(),
            'category' => 'loan_interest', // ✅ Use existing ENUM value
            'source' => 'ঋণ বিতরণ - ' . substr($memberName, 0, 100),
            'amount' => -$loan->loan_amount, // Negative as expense
            'payment_method' => $request->disbursement_method,
            'transaction_id' => $request->transaction_id ?? null,
            'received_by' => auth()->id(),
            'description' => 'ঋণ নং: ' . $loan->loan_number . ' - বিতরণ',
            'created_at' => now(),
            'created_by' => auth()->id(),
            'remarks' => substr($request->remarks ?? '', 0, 200),
        ];
        
        DB::table('incomes')->insert($incomeData);
        
        DB::commit();
        
        return redirect()->back()
            ->with('success', 'লোন সফলভাবে বিতরণ করা হয়েছে। লোন নম্বর: ' . $loan->loan_number);
            
    } catch (\Exception $e) {
        DB::rollback();
        
        error_log('Loan Disburse Error: ' . $e->getMessage());
        
        return redirect()->back()
            ->with('error', 'বিতরণ করতে সমস্যা: ' . substr($e->getMessage(), 0, 80));
    }
}

    /**
     * ঋণ প্রত্যাখ্যান
     */
    public function reject(Request $request, Loan $loan)
    {
        if ($loan->status != 'pending') {
            return redirect()->back()
                ->with('error', 'শুধুমাত্র পেন্ডিং লোন প্রত্যাখ্যান করা যাবে।');
        }
        
        $validator = Validator::make($request->all(), [
            'reason' => 'required|string|max:500',
        ]);

        if ($validator->fails()) {
            return redirect()->back()
                ->withErrors($validator)
                ->withInput();
        }

        DB::beginTransaction();
        
        try {
            $oldStatus = $loan->status;
            
            $loan->update([
                'status' => 'rejected',
                'notes' => $loan->notes . "\nপ্রত্যাখ্যান কারণ: " . $request->reason,
            ]);
            
            // Audit log
            AuditLog::create([
                'user_id' => auth()->id(),
                'user_type' => auth()->user()->user_type,
                'action' => 'লোন প্রত্যাখ্যান',
                'table_name' => 'loans',
                'record_id' => $loan->id,
                'old_values' => json_encode(['status' => $oldStatus]),
                'new_values' => json_encode([
                    'status' => 'rejected',
                    'rejection_reason' => $request->reason
                ]),
                'ip_address' => $request->ip(),
            ]);
            
            DB::commit();
            
            return redirect()->back()
                ->with('success', 'লোন সফলভাবে প্রত্যাখ্যান করা হয়েছে।');
                
        } catch (\Exception $e) {
            DB::rollback();
            
            \Log::error('Loan Reject Error: ' . $e->getMessage());
            
            return redirect()->back()
                ->with('error', 'প্রত্যাখ্যান করতে সমস্যা: ' . $e->getMessage());
        }
    }

    /**
     * কিস্তি পরিশোধ
     */
    public function payInstallment(Request $request, Loan $loan)
    {
        $validator = Validator::make($request->all(), [
            'installment_id' => 'required|exists:loan_installments,id',
            'amount' => 'required|numeric|min:1',
            'payment_method' => 'required|in:cash,bank_transfer,cheque,mobile_banking',
            'transaction_id' => 'nullable|string|max:100',
            'payment_date' => 'required|date',
            'remarks' => 'nullable|string|max:500',
        ]);

        if ($validator->fails()) {
            return redirect()->back()
                ->withErrors($validator)
                ->withInput();
        }

        DB::beginTransaction();
        
        try {
            $installment = LoanInstallment::findOrFail($request->installment_id);
            
            if ($installment->loan_id != $loan->id) {
                throw new \Exception('কিস্তি এই লোনের সাথে মেলে না।');
            }
            
            // Record payment
            $loan->recordPayment($request->amount, $request->payment_date);
            
            // Record income
            Income::create([
                'income_number' => Income::generateIncomeNumber(),
                'income_date' => $request->payment_date,
                'category' => 'loan_installment',
                'source' => 'ঋণ কিস্তি - ' . $loan->member->name . ' (কিস্তি #' . $installment->installment_number . ')',
                'amount' => $request->amount,
                'payment_method' => $request->payment_method,
                'transaction_id' => $request->transaction_id,
                'received_by' => auth()->id(),
                'description' => 'ঋণ নং: ' . $loan->loan_number . ' - ' . $request->remarks,
            ]);
            
            // Audit log
            AuditLog::create([
                'user_id' => auth()->id(),
                'user_type' => auth()->user()->user_type,
                'action' => 'লোন কিস্তি পরিশোধ',
                'table_name' => 'loan_installments',
                'record_id' => $installment->id,
                'old_values' => json_encode([
                    'status' => $installment->status,
                    'paid_amount' => $installment->paid_amount
                ]),
                'new_values' => json_encode([
                    'status' => 'paid',
                    'paid_amount' => $request->amount,
                    'payment_method' => $request->payment_method,
                    'transaction_id' => $request->transaction_id,
                    'payment_date' => $request->payment_date
                ]),
                'ip_address' => $request->ip(),
            ]);
            
            DB::commit();
            
            return redirect()->back()
                ->with('success', 'কিস্তি সফলভাবে পরিশোধিত হয়েছে।');
                
        } catch (\Exception $e) {
            DB::rollback();
            
            \Log::error('Installment Payment Error: ' . $e->getMessage());
            
            return redirect()->back()
                ->withInput()
                ->with('error', 'পেমেন্ট করতে সমস্যা: ' . $e->getMessage());
        }
    }

    /**
     * ঋণ আবেদন (সদস্যের জন্য)
     */
    public function apply(Request $request)
    {
        $user = auth()->user();
        
        if (!$user || $user->user_type != 'member') {
            return redirect()->route('dashboard')
                ->with('error', 'শুধুমাত্র সদস্যরা ঋণ আবেদন করতে পারেন।');
        }
        
        $member = $user->member;
        
        if (!$member) {
            return redirect()->route('profile')
                ->with('error', 'আপনার সদস্য প্রোফাইল পাওয়া যায়নি।');
        }
        
        // সদস্যের জন্য validation rules
        $validator = Validator::make($request->all(), [
            'loan_type' => 'required|in:regular,emergency,business,agriculture,education,medical,housing,vehicle',
            'loan_amount' => 'required|numeric|min:1000|max:200000',
            'duration_months' => 'required|integer|min:3|max:3', // Fixed 3 months
            'purpose' => 'required|string|max:500',
            'guarantor1_id' => 'required|exists:members,member_id',
            'guarantor2_id' => 'required|exists:members,member_id|different:guarantor1_id',
            'loan_date' => 'required|date',
        ], [
            'guarantor2_id.different' => 'একই ব্যক্তিকে দুটি গ্যারান্টর হিসেবে নির্বাচন করা যাবে না।',
            'duration_months.min' => 'সকল ঋণ ৩ মাসের জন্য',
            'duration_months.max' => 'সকল ঋণ ৩ মাসের জন্য',
        ]);

        if ($validator->fails()) {
            return redirect()->back()
                ->withErrors($validator)
                ->withInput();
        }

        DB::beginTransaction();
        
        try {
            $data = $request->all();
            $data['member_id'] = $member->member_id;
            $data['interest_rate'] = 6; // Fixed 6%
            $data['status'] = 'pending';
            $data['created_by'] = auth()->id();
            
            // ✅ Model-এর boot() মেথড loan_number নিজে জেনারেট করবে
            // ✅ তাই এখানে loan_number সেট করার দরকার নেই
            
            // Fixed values
            $data['installment_count'] = 3;
            $data['start_date'] = $data['loan_date'];
            
            // Calculate dates
            if (!empty($data['start_date'])) {
                $startDate = \Carbon\Carbon::parse($data['start_date']);
                $data['end_date'] = $startDate->addMonths(3)->format('Y-m-d');
            }
            
            // Calculate loan details
            $principal = (float) $data['loan_amount'];
            $monthlyInterest = ($principal * 6) / 100;
            $data['total_interest'] = $monthlyInterest * 3;
            $data['total_payable'] = $principal + $data['total_interest'];
            $data['monthly_installment'] = $data['total_payable'] / 3;
            
            // Create loan - loan number auto-generated in model
            $loan = Loan::create($data);
            
            // Installments auto-created in model's created event
            
            // Audit log
            AuditLog::create([
                'user_id' => $user->id,
                'user_type' => $user->user_type,
                'action' => 'সদস্য কর্তৃক লোন আবেদন',
                'table_name' => 'loans',
                'record_id' => $loan->id,
                'old_values' => null,
                'new_values' => json_encode($data),
                'ip_address' => $request->ip(),
            ]);
            
            DB::commit();
            
            return redirect()->route('loans.show', $loan)
                ->with('success', 'আপনার ঋণের আবেদন সফলভাবে জমা দেওয়া হয়েছে। লোন নম্বর: ' . $loan->loan_number);
                
        } catch (\Exception $e) {
            DB::rollback();
            
            \Log::error('Member Loan Application Error: ' . $e->getMessage());
            
            return redirect()->back()
                ->withInput()
                ->with('error', 'আবেদন করতে সমস্যা: ' . $e->getMessage());
        }
    }

    /**
     * ঋণ রিপোর্ট
     */
    public function report(Request $request)
    {
        $query = Loan::with(['member', 'approver', 'disburser']);
        
        // ফিল্টার
        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }
        
        if ($request->filled('loan_type')) {
            $query->where('loan_type', $request->loan_type);
        }
        
        if ($request->filled('from_date')) {
            $query->whereDate('start_date', '>=', $request->from_date);
        }
        
        if ($request->filled('to_date')) {
            $query->whereDate('start_date', '<=', $request->to_date);
        }
        
        if ($request->filled('member_id')) {
            $query->where('member_id', $request->member_id);
        }
        
        $loans = $query->orderBy('start_date', 'desc')->get();
        
        // স্ট্যাটিস্টিক্স
        $stats = [
            'total_loans' => $loans->count(),
            'total_amount' => $loans->sum('loan_amount'),
            'total_payable' => $loans->sum('total_payable'),
            'total_paid' => $loans->sum('paid_amount'),
            'total_due' => $loans->sum('remaining_balance'),
            
            'pending_count' => $loans->where('status', 'pending')->count(),
            'approved_count' => $loans->where('status', 'approved')->count(),
            'disbursed_count' => $loans->where('status', 'disbursed')->count(),
            'active_count' => $loans->where('status', 'active')->count(),
            'completed_count' => $loans->where('status', 'completed')->count(),
            'defaulted_count' => $loans->where('status', 'defaulted')->count(),
            'rejected_count' => $loans->where('status', 'rejected')->count(),
        ];
        
        return view('loans.report', compact('loans', 'stats'));
    }
    
    // LoanController-এ এই helper function যোগ করুন
private function getIncomeCategoryForLoanDisbursement()
{
    // Try different possible ENUM values
    $possibleCategories = [
        'loan_interest',    // Most likely
        'loan',             // Possible
        'other',            // If exists
        'miscellaneous',    // If exists
        'expense',          // If exists
    ];
    
    // You can also check ENUM values from database
    try {
        // Get ENUM values from information_schema
        $enumValues = DB::select("
            SELECT COLUMN_TYPE 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'incomes' 
            AND COLUMN_NAME = 'category'
        ");
        
        if (!empty($enumValues)) {
            $columnType = $enumValues[0]->COLUMN_TYPE;
            // Extract ENUM values from string like: enum('value1','value2')
            preg_match_all("/'([^']+)'/", $columnType, $matches);
            
            if (!empty($matches[1])) {
                $allowedValues = $matches[1];
                
                // Check for preferred categories in order
                foreach (['loan_interest', 'loan', 'other'] as $preferred) {
                    if (in_array($preferred, $allowedValues)) {
                        return $preferred;
                    }
                }
                
                // Return first available value
                return $allowedValues[0];
            }
        }
    } catch (\Exception $e) {
        // If query fails, use default
    }
    
    // Default fallback
    return 'loan_interest';
}

    /**
     * মেয়াদোত্তীর্ণ ঋণ
     */
    public function overdueLoans(Request $request)
    {
        $overdueLoans = Loan::overdue()
            ->with(['member', 'installments' => function($query) {
                $query->where('status', 'pending')
                    ->where('due_date', '<', now())
                    ->orderBy('due_date');
            }])
            ->paginate(20);
        
        $totalOverdueAmount = $overdueLoans->sum('due_amount');
        
        return view('loans.overdue', compact('overdueLoans', 'totalOverdueAmount'));
    }

    /**
     * ঋণ বিবরণী
     */
    public function statement(Loan $loan)
    {
        $installments = $loan->installments()->orderBy('installment_number')->get();
        $paymentHistory = $installments->where('status', 'paid');
        
        return view('loans.statement', compact('loan', 'installments', 'paymentHistory'));
    }

    /**
     * ঋণ কার্ড প্রিন্ট
     */
    public function printCard(Loan $loan)
    {
        return view('loans.print-card', compact('loan'));
    }

    /**
     * ঋণ সারাংশ
     */
    public function summary()
    {
        $totalActiveLoans = Loan::active()->count();
        $totalActiveAmount = Loan::active()->sum('loan_amount');
        $totalOverdueLoans = Loan::overdue()->count();
        $totalOverdueAmount = Loan::overdue()->sum('due_amount');
        $totalCompletedLoans = Loan::completed()->count();
        
        $recentLoans = Loan::with('member')->orderBy('created_at', 'desc')->limit(10)->get();
        $recentPayments = LoanInstallment::with(['loan.member'])
            ->where('status', 'paid')
            ->orderBy('paid_date', 'desc')
            ->limit(10)
            ->get();
        
        return view('loans.summary', compact(
            'totalActiveLoans',
            'totalActiveAmount',
            'totalOverdueLoans',
            'totalOverdueAmount',
            'totalCompletedLoans',
            'recentLoans',
            'recentPayments'
        ));
    }
}