<?php

namespace App\Http\Controllers;

use App\Models\Expense;
use App\Models\ExpenseCategory;
use App\Models\User;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\Validator;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Str;
use Carbon\Carbon;

class ExpenseController extends Controller
{
    /**
     * কনস্ট্রাক্টর - মিডলওয়্যার সেটআপ
     */
    public function __construct()
    {
        $this->middleware('auth');
        $this->middleware('permission:view_expenses')->only(['index', 'show', 'report', 'monthlyReport']);
        $this->middleware('permission:create_expenses')->only(['create', 'store']);
        $this->middleware('permission:edit_expenses')->only(['edit', 'update']);
        $this->middleware('permission:delete_expenses')->only(['destroy', 'bulkDestroy']);
        $this->middleware('permission:approve_expenses')->only(['approve', 'reject']);
    }

    /**
     * Display a listing of the resource.
     */
    public function index(Request $request)
    {
        $query = Expense::with(['category', 'user', 'approver']);
        
        // ফিল্টার
        if ($request->filled('category_id')) {
            $query->where('category_id', $request->category_id);
        }
        
        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }
        
        if ($request->filled('payment_method')) {
            $query->where('payment_method', $request->payment_method);
        }
        
        if ($request->filled('from_date')) {
            $query->whereDate('expense_date', '>=', $request->from_date);
        }
        
        if ($request->filled('to_date')) {
            $query->whereDate('expense_date', '<=', $request->to_date);
        }
        
        if ($request->filled('search')) {
            $search = $request->search;
            $query->where(function($q) use ($search) {
                $q->where('title', 'like', "%{$search}%")
                  ->orWhere('description', 'like', "%{$search}%")
                  ->orWhere('reference_no', 'like', "%{$search}%")
                  ->orWhere('notes', 'like', "%{$search}%");
            });
        }
        
        $expenses = $query->orderBy('expense_date', 'desc')
                         ->orderBy('id', 'desc')
                         ->paginate(20)
                         ->withQueryString();
        
        // স্ট্যাটিসটিক্স
        $totalExpense = Expense::sum('amount');
        $monthlyExpense = Expense::whereMonth('expense_date', now()->month)
            ->whereYear('expense_date', now()->year)
            ->sum('amount');
        $todayExpense = Expense::whereDate('expense_date', today())->sum('amount');
        $pendingExpense = Expense::where('status', 'pending')->sum('amount');
        
        // ক্যাটাগরি লিস্ট
        $categories = ExpenseCategory::where('is_active', true)
            ->orderBy('name')
            ->get();
        
        // স্ট্যাটাস লিস্ট
        $statuses = [
            'pending' => 'অপেক্ষমাণ',
            'approved' => 'অনুমোদিত',
            'rejected' => 'বাতিল'
        ];
        
        // পেমেন্ট মেথড লিস্ট
        $paymentMethods = [
            'cash' => 'নগদ',
            'bank' => 'ব্যাংক',
            'check' => 'চেক',
            'mobile_banking' => 'মোবাইল ব্যাংকিং'
        ];
        
        return view('expenses.index', compact(
            'expenses', 
            'totalExpense', 
            'monthlyExpense', 
            'todayExpense',
            'pendingExpense',
            'categories', 
            'statuses',
            'paymentMethods'
        ));
    }

    /**
     * Show the form for creating a new resource.
     */
    public function create()
    {
        $categories = ExpenseCategory::where('is_active', true)
            ->orderBy('name')
            ->get();
        
        $paymentMethods = [
            'cash' => 'নগদ',
            'bank' => 'ব্যাংক',
            'check' => 'চেক',
            'mobile_banking' => 'মোবাইল ব্যাংকিং'
        ];
        
        // ভাউচার নম্বর জেনারেট
        $voucherNumber = $this->generateVoucherNumber();
        
        return view('expenses.create', compact('categories', 'paymentMethods', 'voucherNumber'));
    }

    /**
     * Store a newly created resource in storage.
     */
    public function store(Request $request)
    {
        $validator = Validator::make($request->all(), [
            'title' => 'required|string|max:255',
            'description' => 'nullable|string',
            'amount' => 'required|numeric|min:0',
            'category_id' => 'required|exists:expense_categories,id',
            'expense_date' => 'required|date',
            'payment_method' => 'required|in:cash,bank,check,mobile_banking',
            'reference_no' => 'nullable|string|max:100',
            'receipt_attachment' => 'nullable|file|mimes:jpg,jpeg,png,pdf|max:5120', // 5MB
            'notes' => 'nullable|string',
            'paid_to' => 'nullable|string|max:255',
        ]);

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

        DB::beginTransaction();
        
        try {
            $data = $request->except(['_token', '_method']);
            
            // ইউজার আইডি সেট করুন
            $data['user_id'] = Auth::id();
            
            // ডিফল্ট স্ট্যাটাস পেন্ডিং (অ্যাডমিন হলে অ্যাপ্রুভড)
            if (Auth::user()->user_type == 'admin' || Auth::user()->can('approve_expenses')) {
                $data['status'] = 'approved';
                $data['approved_by'] = Auth::id();
                $data['approved_at'] = now();
            } else {
                $data['status'] = 'pending';
            }
            
            // ভাউচার নম্বর জেনারেট
            if (empty($data['voucher_number'])) {
                $data['voucher_number'] = $this->generateVoucherNumber();
            }
            
            // ফাইল আপলোড
            if ($request->hasFile('receipt_attachment')) {
                $file = $request->file('receipt_attachment');
                $originalName = pathinfo($file->getClientOriginalName(), PATHINFO_FILENAME);
                $extension = $file->getClientOriginalExtension();
                $fileName = time() . '_' . Str::slug($originalName) . '.' . $extension;
                $path = $file->storeAs('expense_receipts', $fileName, 'public');
                $data['receipt_attachment'] = $path;
            }
            
            $expense = Expense::create($data);
            
            DB::commit();
            
            return redirect()->route('expenses.show', $expense->id)
                ->with('success', 'ব্যয় সফলভাবে যোগ করা হয়েছে।')
                ->with('print', true)
                ->with('expense_id', $expense->id);
                
        } catch (\Exception $e) {
            DB::rollback();
            
            \Log::error('Expense Store Error: ' . $e->getMessage());
            \Log::error($e->getTraceAsString());
            
            return redirect()->back()
                ->withInput()
                ->with('error', 'ব্যয় যোগ করতে সমস্যা: ' . $e->getMessage());
        }
    }

    /**
     * Display the specified resource.
     */
    public function show(Expense $expense)
    {
        $expense->load(['category', 'user', 'approver']);
        
        $paymentMethods = [
            'cash' => 'নগদ',
            'bank' => 'ব্যাংক',
            'check' => 'চেক',
            'mobile_banking' => 'মোবাইল ব্যাংকিং'
        ];
        
        $statuses = [
            'pending' => 'অপেক্ষমাণ',
            'approved' => 'অনুমোদিত',
            'rejected' => 'বাতিল'
        ];
        
        return view('expenses.show', compact('expense', 'paymentMethods', 'statuses'));
    }

    /**
     * Show the form for editing the specified resource.
     */
    public function edit(Expense $expense)
    {
        $this->authorize('update', $expense);
        
        $categories = ExpenseCategory::where('is_active', true)
            ->orderBy('name')
            ->get();
        
        $paymentMethods = [
            'cash' => 'নগদ',
            'bank' => 'ব্যাংক',
            'check' => 'চেক',
            'mobile_banking' => 'মোবাইল ব্যাংকিং'
        ];
        
        $statuses = [
            'pending' => 'অপেক্ষমাণ',
            'approved' => 'অনুমোদিত',
            'rejected' => 'বাতিল'
        ];
        
        return view('expenses.edit', compact('expense', 'categories', 'paymentMethods', 'statuses'));
    }

    /**
     * Update the specified resource in storage.
     */
    public function update(Request $request, Expense $expense)
    {
        $this->authorize('update', $expense);
        
        $validator = Validator::make($request->all(), [
            'title' => 'required|string|max:255',
            'description' => 'nullable|string',
            'amount' => 'required|numeric|min:0',
            'category_id' => 'required|exists:expense_categories,id',
            'expense_date' => 'required|date',
            'payment_method' => 'required|in:cash,bank,check,mobile_banking',
            'reference_no' => 'nullable|string|max:100',
            'receipt_attachment' => 'nullable|file|mimes:jpg,jpeg,png,pdf|max:5120',
            'notes' => 'nullable|string',
            'paid_to' => 'nullable|string|max:255',
            'status' => 'sometimes|in:pending,approved,rejected',
        ]);

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

        DB::beginTransaction();
        
        try {
            $data = $request->except(['_token', '_method', '_token']);
            
            // ফাইল আপলোড (যদি নতুন ফাইল দেওয়া হয়)
            if ($request->hasFile('receipt_attachment')) {
                // পুরানো ফাইল ডিলিট করুন (যদি থাকে)
                if ($expense->receipt_attachment && Storage::disk('public')->exists($expense->receipt_attachment)) {
                    Storage::disk('public')->delete($expense->receipt_attachment);
                }
                
                $file = $request->file('receipt_attachment');
                $originalName = pathinfo($file->getClientOriginalName(), PATHINFO_FILENAME);
                $extension = $file->getClientOriginalExtension();
                $fileName = time() . '_' . Str::slug($originalName) . '.' . $extension;
                $path = $file->storeAs('expense_receipts', $fileName, 'public');
                $data['receipt_attachment'] = $path;
            } else {
                // যদি ফাইল আপলোড না করা হয়, তাহলে পুরানো অ্যাটাচমেন্ট রাখুন
                if (!isset($data['receipt_attachment']) || empty($data['receipt_attachment'])) {
                    $data['receipt_attachment'] = $expense->receipt_attachment;
                }
            }
            
            // স্ট্যাটাস পরিবর্তন হলে অ্যাপ্রুভার তথ্য আপডেট
            if (isset($data['status']) && $data['status'] != $expense->status) {
                if ($data['status'] == 'approved') {
                    $data['approved_by'] = Auth::id();
                    $data['approved_at'] = now();
                } elseif ($data['status'] == 'rejected') {
                    $data['approved_by'] = Auth::id();
                    $data['approved_at'] = now();
                }
            }
            
            $expense->update($data);
            
            DB::commit();
            
            return redirect()->route('expenses.show', $expense->id)
                ->with('success', 'ব্যয় সফলভাবে আপডেট করা হয়েছে।');
                
        } catch (\Exception $e) {
            DB::rollback();
            
            \Log::error('Expense Update Error: ' . $e->getMessage());
            \Log::error($e->getTraceAsString());
            
            return redirect()->back()
                ->withInput()
                ->with('error', 'ব্যয় আপডেট করতে সমস্যা: ' . $e->getMessage());
        }
    }

    /**
     * Remove the specified resource from storage.
     */
    public function destroy(Expense $expense)
    {
        $this->authorize('delete', $expense);
        
        DB::beginTransaction();
        
        try {
            // ফাইল ডিলিট করুন (যদি থাকে)
            if ($expense->receipt_attachment && Storage::disk('public')->exists($expense->receipt_attachment)) {
                Storage::disk('public')->delete($expense->receipt_attachment);
            }

            $expenseNumber = $expense->voucher_number ?? $expense->id;
            $expense->delete();
            
            DB::commit();

            if (request()->ajax()) {
                return response()->json(['success' => "ব্যয় রেকর্ড #{$expenseNumber} সফলভাবে ডিলিট করা হয়েছে।"]);
            }

            return redirect()->route('expenses.index')
                ->with('success', "ব্যয় রেকর্ড #{$expenseNumber} সফলভাবে ডিলিট করা হয়েছে။");
                
        } catch (\Exception $e) {
            DB::rollback();
            
            \Log::error('Expense Delete Error: ' . $e->getMessage());
            
            if (request()->ajax()) {
                return response()->json(['error' => 'ব্যয় রেকর্ড ডিলিট করতে সমস্যা: ' . $e->getMessage()], 500);
            }
            
            return redirect()->back()
                ->with('error', 'ব্যয় রেকর্ড ডিলিট করতে সমস্যা: ' . $e->getMessage());
        }
    }

    /**
     * ব্যয় অনুমোদন
     */
    public function approve(Expense $expense)
    {
        $this->authorize('approve', $expense);
        
        DB::beginTransaction();
        
        try {
            $expense->update([
                'status' => 'approved',
                'approved_by' => Auth::id(),
                'approved_at' => now()
            ]);
            
            DB::commit();
            
            return redirect()->back()
                ->with('success', 'ব্যয় সফলভাবে অনুমোদন করা হয়েছে।');
                
        } catch (\Exception $e) {
            DB::rollback();
            
            return redirect()->back()
                ->with('error', 'ব্যয় অনুমোদন করতে সমস্যা: ' . $e->getMessage());
        }
    }

    /**
     * ব্যয় বাতিল
     */
    public function reject(Request $request, Expense $expense)
    {
        $this->authorize('approve', $expense);
        
        $validator = Validator::make($request->all(), [
            'rejection_reason' => 'required|string|max:500',
        ]);

        if ($validator->fails()) {
            return redirect()->back()
                ->withErrors($validator)
                ->withInput();
        }
        
        DB::beginTransaction();
        
        try {
            $expense->update([
                'status' => 'rejected',
                'approved_by' => Auth::id(),
                'approved_at' => now(),
                'rejection_reason' => $request->rejection_reason
            ]);
            
            DB::commit();
            
            return redirect()->back()
                ->with('success', 'ব্যয় বাতিল করা হয়েছে।');
                
        } catch (\Exception $e) {
            DB::rollback();
            
            return redirect()->back()
                ->with('error', 'ব্যয় বাতিল করতে সমস্যা: ' . $e->getMessage());
        }
    }

    /**
     * রিপোর্ট
     */
    public function report(Request $request)
    {
        $query = Expense::with(['category', 'user', 'approver']);

        // তারিখ ফিল্টার
        if ($request->filled('from_date')) {
            $query->whereDate('expense_date', '>=', $request->from_date);
        }

        if ($request->filled('to_date')) {
            $query->whereDate('expense_date', '<=', $request->to_date);
        }

        // ক্যাটাগরি ফিল্টার
        if ($request->filled('category_id')) {
            $query->where('category_id', $request->category_id);
        }

        // স্ট্যাটাস ফিল্টার
        if ($request->filled('status')) {
            $query->where('status', $request->status);
        }

        // পেমেন্ট মেথড ফিল্টার
        if ($request->filled('payment_method')) {
            $query->where('payment_method', $request->payment_method);
        }

        $expenses = $query->orderBy('expense_date', 'desc')->orderBy('id', 'desc')->get();
        $categories = ExpenseCategory::all();
        
        $paymentMethods = [
            'cash' => 'নগদ',
            'bank' => 'ব্যাংক',
            'check' => 'চেক',
            'mobile_banking' => 'মোবাইল ব্যাংকিং'
        ];
        
        $statuses = [
            'pending' => 'অপেক্ষমাণ',
            'approved' => 'অনুমোদিত',
            'rejected' => 'বাতিল'
        ];

        $totalAmount = $expenses->sum('amount');
        
        // ক্যাটাগরি অনুযায়ী সারাংশ
        $categorySummary = $expenses->groupBy('category_id')->map(function($items, $categoryId) use ($categories, $expenses) {
            $category = $categories->find($categoryId);
            $totalAmount = $expenses->sum('amount');
            $categoryAmount = $items->sum('amount');
            
            return [
                'name' => $category ? $category->name : 'Unknown',
                'name_bn' => $category ? $category->name_bn : 'অজানা',
                'amount' => $categoryAmount,
                'count' => $items->count(),
                'percentage' => $totalAmount > 0 ? round(($categoryAmount / $totalAmount) * 100, 2) : 0
            ];
        })->sortByDesc('amount');

        return view('expenses.report', compact('expenses', 'categories', 'totalAmount', 'categorySummary', 'paymentMethods', 'statuses'));
    }

    /**
     * মাসিক ব্যয় রিপোর্ট
     */
    public function monthlyReport(Request $request)
    {
        $year = $request->filled('year') ? $request->year : now()->year;
        
        $monthlyExpenses = Expense::selectRaw("YEAR(expense_date) as year, MONTH(expense_date) as month, SUM(amount) as total")
            ->whereYear('expense_date', $year)
            ->groupBy('year', 'month')
            ->orderBy('month', 'asc')
            ->get()
            ->map(function($item) {
                $item->month_name = $this->getBengaliMonthName($item->month);
                $item->month_name_en = Carbon::createFromDate($item->year, $item->month, 1)->format('F');
                return $item;
            });
        
        $categoryWise = Expense::selectRaw('category_id, SUM(amount) as total')
            ->whereYear('expense_date', $year)
            ->groupBy('category_id')
            ->with('category')
            ->get()
            ->map(function($item) {
                $item->name = $item->category->name ?? 'Unknown';
                $item->name_bn = $item->category->name_bn ?? 'অজানা';
                return $item;
            });
        
        $totalYearly = Expense::whereYear('expense_date', $year)->sum('amount');
        
        // মাসের নাম
        $monthNames = [
            '1' => 'জানুয়ারি', '2' => 'ফেব্রুয়ারি', '3' => 'মার্চ',
            '4' => 'এপ্রিল', '5' => 'মে', '6' => 'জুন',
            '7' => 'জুলাই', '8' => 'আগস্ট', '9' => 'সেপ্টেম্বর',
            '10' => 'অক্টোবর', '11' => 'নভেম্বর', '12' => 'ডিসেম্বর'
        ];
        
        return view('expenses.monthly-report', compact('monthlyExpenses', 'categoryWise', 'totalYearly', 'year', 'monthNames'));
    }

    /**
     * ভাউচার প্রিন্ট
     */
    public function printVoucher(Expense $expense)
    {
        $expense->load(['category', 'user', 'approver']);
        
        $paymentMethods = [
            'cash' => 'নগদ',
            'bank' => 'ব্যাংক',
            'check' => 'চেক',
            'mobile_banking' => 'মোবাইল ব্যাংকিং'
        ];
        
        $statuses = [
            'pending' => 'অপেক্ষমাণ',
            'approved' => 'অনুমোদিত',
            'rejected' => 'বাতিল'
        ];
        
        return view('expenses.print-voucher', compact('expense', 'paymentMethods', 'statuses'));
    }

    /**
     * অ্যাটাচমেন্ট ডাউনলোড
     */
    public function downloadAttachment(Expense $expense)
    {
        if (!$expense->receipt_attachment || !Storage::disk('public')->exists($expense->receipt_attachment)) {
            return redirect()->back()->with('error', 'অ্যাটাচমেন্ট পাওয়া যায়নি।');
        }
        
        return Storage::disk('public')->download($expense->receipt_attachment);
    }

    /**
     * একাধিক ব্যয় ডিলিট
     */
    public function bulkDestroy(Request $request)
    {
        $request->validate([
            'ids' => 'required|array',
            'ids.*' => 'exists:expenses,id'
        ]);
        
        $this->authorize('bulkDelete', Expense::class);
        
        DB::beginTransaction();
        
        try {
            $expenses = Expense::whereIn('id', $request->ids)->get();
            $count = 0;
            
            foreach ($expenses as $expense) {
                // ফাইল ডিলিট করুন
                if ($expense->receipt_attachment && Storage::disk('public')->exists($expense->receipt_attachment)) {
                    Storage::disk('public')->delete($expense->receipt_attachment);
                }
                
                $expense->delete();
                $count++;
            }
            
            DB::commit();
            
            return response()->json([
                'success' => true,
                'message' => "{$count} টি ব্যয় রেকর্ড সফলভাবে ডিলিট করা হয়েছে।"
            ]);
            
        } catch (\Exception $e) {
            DB::rollback();
            
            \Log::error('Bulk Expense Delete Error: ' . $e->getMessage());
            
            return response()->json([
                'error' => 'ব্যয় রেকর্ড ডিলিট করতে সমস্যা: ' . $e->getMessage()
            ], 500);
        }
    }

    /**
     * ভাউচার নম্বর জেনারেট
     */
    private function generateVoucherNumber()
    {
        $year = date('Y');
        $month = date('m');
        $prefix = 'EXP';
        
        $lastExpense = Expense::whereYear('created_at', $year)
            ->whereMonth('created_at', $month)
            ->orderBy('id', 'desc')
            ->first();
        
        if ($lastExpense && preg_match('/EXP-\d{6}-(\d{3})/', $lastExpense->voucher_number ?? '', $matches)) {
            $sequence = intval($matches[1]) + 1;
        } else {
            $sequence = 1;
        }
        
        return $prefix . '-' . $year . $month . '-' . str_pad($sequence, 3, '0', STR_PAD_LEFT);
    }

    /**
     * বাংলা মাসের নাম
     */
    private function getBengaliMonthName($month)
    {
        $months = [
            '1' => 'জানুয়ারি',
            '2' => 'ফেব্রুয়ারি',
            '3' => 'মার্চ',
            '4' => 'এপ্রিল',
            '5' => 'মে',
            '6' => 'জুন',
            '7' => 'জুলাই',
            '8' => 'আগস্ট',
            '9' => 'সেপ্টেম্বর',
            '10' => 'অক্টোবর',
            '11' => 'নভেম্বর',
            '12' => 'ডিসেম্বর'
        ];
        
        return $months[$month] ?? $month;
    }

    /**
     * চার্ট ডাটা
     */
    public function chartData(Request $request)
    {
        $year = $request->filled('year') ? $request->year : now()->year;
        
        $monthlyData = Expense::selectRaw('MONTH(expense_date) as month, SUM(amount) as total')
            ->whereYear('expense_date', $year)
            ->groupBy('month')
            ->orderBy('month')
            ->pluck('total', 'month')
            ->toArray();
        
        $categoryData = Expense::selectRaw('category_id, SUM(amount) as total')
            ->whereYear('expense_date', $year)
            ->groupBy('category_id')
            ->with('category')
            ->get()
            ->map(function($item) {
                return [
                    'category' => $item->category->name ?? 'Unknown',
                    'category_bn' => $item->category->name_bn ?? 'অজানা',
                    'total' => $item->total
                ];
            });
        
        return response()->json([
            'success' => true,
            'data' => [
                'monthly' => $monthlyData,
                'category' => $categoryData
            ]
        ]);
    }
    
    /**
     * ড্যাশবোর্ড স্ট্যাটস
     */
    public function dashboardStats()
    {
        $todayExpense = Expense::whereDate('expense_date', today())->sum('amount');
        $currentMonthExpense = Expense::whereMonth('expense_date', now()->month)
            ->whereYear('expense_date', now()->year)
            ->sum('amount');
        $lastMonthExpense = Expense::whereMonth('expense_date', now()->subMonth()->month)
            ->whereYear('expense_date', now()->subMonth()->year)
            ->sum('amount');
        
        $recentExpenses = Expense::with('category')
            ->orderBy('expense_date', 'desc')
            ->orderBy('id', 'desc')
            ->limit(10)
            ->get()
            ->map(function($expense) {
                return [
                    'id' => $expense->id,
                    'voucher_number' => $expense->voucher_number,
                    'title' => $expense->title,
                    'amount' => $expense->amount,
                    'date' => $expense->expense_date->format('Y-m-d'),
                    'category' => $expense->category->name ?? 'Unknown',
                    'status' => $expense->status
                ];
            });
        
        $categoryStats = Expense::whereMonth('expense_date', now()->month)
            ->whereYear('expense_date', now()->year)
            ->groupBy('category_id')
            ->selectRaw('category_id, sum(amount) as total_amount, count(*) as count')
            ->with('category')
            ->get()
            ->map(function($item) {
                return [
                    'category_id' => $item->category_id,
                    'category' => $item->category->name ?? 'Unknown',
                    'category_bn' => $item->category->name_bn ?? 'অজানা',
                    'total_amount' => $item->total_amount,
                    'count' => $item->count
                ];
            });
        
        return response()->json([
            'success' => true,
            'data' => [
                'today_expense' => $todayExpense,
                'current_month_expense' => $currentMonthExpense,
                'last_month_expense' => $lastMonthExpense,
                'recent_expenses' => $recentExpenses,
                'category_stats' => $categoryStats
            ]
        ]);
    }
}