<?php

namespace App\Http\Controllers;

use App\Models\Expense;
use App\Models\Supplier;
use App\Models\Account;
use App\Models\JournalEntry; 
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use App\Imports\ExpensesImport;
use Maatwebsite\Excel\Facades\Excel;

class ExpenseController extends Controller
{
    /**
     * Display a listing of expenses.
     */
    public function index()
{
    $companyId = Auth::user()->current_company_id;

    $expenses = Expense::where('company_id', $companyId)
        ->with('supplier')
        ->orderBy('payment_date', 'desc')
        ->paginate(20);

    // Calculate Card Stats
    $stats = [
        'total'    => Expense::where('company_id', $companyId)->sum('total_amount'),
        'posted'   => Expense::where('company_id', $companyId)->where('status', 'posted')->sum('total_amount'),
        'draft'    => Expense::where('company_id', $companyId)->where('status', 'draft')->sum('total_amount'),
        'reversed' => Expense::where('company_id', $companyId)->where('status', 'reversed')->sum('total_amount'),
    ];

    return view('suppliers.expenses', compact('expenses', 'stats'));
}
    /**
     * Show the form for creating a new expense.
     */
   public function create()
{
    $companyId = Auth::user()->current_company_id;
    
    $suppliers = Supplier::where('company_id', $companyId)
        ->orderBy('name', 'asc')
        ->get();

    // 1. Get accounts belonging to the "Expenses" Primary Category
    $expenseAccounts = Account::where('company_id', $companyId)
        ->whereHas('type.group.category', function($q) {
            $q->where('name', 'Expenses'); 
        })
        ->orderBy('name', 'asc')
        ->get();

    // 2. Get accounts for the Payment dropdown (Assets like Cash/Bank)
    $paymentAccounts = Account::where('company_id', $companyId)
        ->whereHas('type.group.category', function($q) {
            $q->where('name', 'Assets');
        })
        ->whereHas('type', function($q) {
            $q->whereIn('name', ['Cash', 'Bank', 'Mobile Money', 'Cash and cash equivalents']);
        })
        ->orderBy('name', 'asc')
        ->get();

    return view('suppliers.expenses-create', compact('suppliers', 'expenseAccounts', 'paymentAccounts'));
}
    /**
     * Store a newly created expense and post to journals.
     */
    public function store(Request $request)
    {
        $request->validate([
            'bank_account_id' => 'required|exists:accounts,id',
            'payment_date'    => 'required|date',
            'payment_method'  => 'required|string',
            'total_amount'    => 'required|numeric|min:0.01',
            'items'           => 'required|array|min:1',
            'items.*.account_id' => 'required|exists:accounts,id',
            'items.*.amount'     => 'required|numeric|min:0',
        ]);

        try {
            DB::transaction(function () use ($request) {
                // 1. Create Expense Header
                $expense = Expense::create([
                    'company_id'      => Auth::user()->current_company_id,
                    'supplier_id'     => $request->supplier_id,
                    'bank_account_id' => $request->bank_account_id,
                    'payment_date'    => $request->payment_date,
                    'payment_method'  => $request->payment_method,
                    'reference_no'    => $request->reference_no,
                    'total_amount'    => $request->total_amount,
                    'memo'            => $request->memo,
                    'status'          => $request->status ?? 'posted',
                    'created_by'      => Auth::id(),
                ]);

                // 2. Create Line Items
                foreach ($request->items as $item) {
                    $expense->items()->create([
                        'account_id'  => $item['account_id'],
                        'description' => $item['description'],
                        'amount'      => $item['amount'],
                    ]);
                }

                // 3. Post to Journal Entries if 'posted'
                if ($expense->status === 'posted') {
                    $this->postToJournal($expense);
                }
            });

            return redirect()->route('suppliers.expenses.index')
                ->with('success', 'Expense saved successfully as ' . strtoupper($request->status));

        } catch (\Exception $e) {
            return back()->with('error', 'Error: ' . $e->getMessage())->withInput();
        }
    }

    /**
     * Financial Logic: Generate Double Entry Journal Records
     */
  protected function postToJournal(Expense $expense)
{
    $expense->load('items');
    $reference = $expense->reference_no ?? 'EXP-' . $expense->id;

    // A. DEBITS (Expense Categories)
    foreach ($expense->items as $item) {
        JournalEntry::create([
            'company_id'       => $expense->company_id,
            'user_id'          => Auth::id(),
            'account_id'       => $item->account_id,
            'expense_id'       => $expense->id,
            'supplier_id'      => $expense->supplier_id,
            'transaction_date' => $expense->payment_date,
            'reference'        => $reference,
            'transource'       => 'Expenses',
            'description'      => "Expense: " . ($item->description ?? $expense->memo),
            'debit'            => $item->amount,
            'credit'           => 0,
        ]);
    }

    // B. CREDIT (Bank/Cash Account)
    // CRITICAL: Ensure bank_account_id is not null
    if (!$expense->bank_account_id) {
        throw new \Exception("The Payment Account is missing. Please select a Bank or Cash account.");
    }

    JournalEntry::create([
        'company_id'       => $expense->company_id,
        'user_id'          => Auth::id(),
        'account_id'       => $expense->bank_account_id, // This was NULL in your log
        'expense_id'       => $expense->id,
        'supplier_id'      => $expense->supplier_id,
        'transaction_date' => $expense->payment_date,
        'reference'        => $reference,
        'transource'       => 'Expenses',
        'description'      => "Payment for Expense: " . $reference,
        'debit'            => 0,
        'credit'           => $expense->total_amount,
    ]);
}
    
public function edit($id)
{
    // 1. Fetch the expense with items
    $expense = Expense::where('company_id', Auth::user()->current_company_id)
        ->with('items')
        ->findOrFail($id);

    // 2. Fetch Suppliers
    $suppliers = \App\Models\Supplier::where('company_id', Auth::user()->current_company_id)
        ->orderBy('name', 'asc')
        ->get();

    // 3. Fetch Expense Accounts (Named exactly as required by Blade)
    $expenseAccounts = \App\Models\Account::where('company_id', Auth::user()->current_company_id)
        ->orderBy('name', 'asc')
        ->get();

    // 4. Fetch Payment Accounts (Bank/Cash)
    $paymentAccounts = \App\Models\Account::where('company_id', Auth::user()->current_company_id)
        ->whereIn('account_type_id', [2]) // Based on your SQL, 2 = Bank/Cash
        ->orderBy('name', 'asc')
        ->get();

    // Fallback if no specific bank accounts found
    if ($paymentAccounts->isEmpty()) {
        $paymentAccounts = $expenseAccounts;
    }

    // 5. Return view with exact variable names
    return view('suppliers.expenses-edit', compact(
        'expense', 
        'suppliers', 
        'expenseAccounts', 
        'paymentAccounts'
    ));
}


public function update(Request $request, $id)
{
    // 1. Strict Validation Checks
    $request->validate([
        'supplier_id'     => 'nullable|exists:suppliers,id',
        'bank_account_id' => 'required|exists:accounts,id',
        'payment_date'    => 'required|date|before_or_equal:today',
        'payment_method'  => 'required|string',
        'reference_no'    => 'nullable|string|max:50',
        'status'          => 'required|in:draft,posted',
        'total_amount'    => 'required|numeric|gt:0',
        
        'items'               => 'required|array|min:1',
        'items.*.account_id'  => 'required|exists:accounts,id',
        'items.*.description' => 'nullable|string|max:255',
        'items.*.amount'      => 'required|numeric|min:0.01',
    ], [
        'bank_account_id.required' => 'The Payment Account (Credit side) is required.',
        'items.*.account_id.required' => 'Each line must have an Expense Category.',
        'items.*.amount.min' => 'Line amounts must be at least 0.01.',
        'total_amount.gt' => 'The total expense amount must be greater than zero.',
    ]);

    try {
        DB::transaction(function () use ($request, $id) {
            $expense = Expense::where('company_id', Auth::user()->current_company_id)
                ->findOrFail($id);

            // 2. Update Header
            $expense->update([
                'supplier_id'     => $request->supplier_id,
                'bank_account_id' => $request->bank_account_id,
                'payment_date'    => $request->payment_date,
                'payment_method'  => $request->payment_method,
                'reference_no'    => $request->reference_no,
                'total_amount'    => $request->total_amount,
                'memo'            => $request->memo,
                'status'          => $request->status,
            ]);

            // 3. Refresh Line Items
            $expense->items()->delete();
            foreach ($request->items as $item) {
                $expense->items()->create([
                    'account_id'  => $item['account_id'],
                    'description' => $item['description'],
                    'amount'      => $item['amount'],
                ]);
            }

            // 4. Ledger Synchronization
            JournalEntry::where('expense_id', $expense->id)->delete();

            // Only post if the user clicked "Update & Post"
            if ($expense->status === 'posted') {
                // CRITICAL FIX: Use ->fresh() to ensure bank_account_id is loaded
                $this->postToJournal($expense->fresh());
            }
        });

        return redirect()->route('suppliers.expenses.index')
            ->with('success', 'Expense updated and ledger synchronized successfully.');

    } catch (\Exception $e) {
        \Log::error("Expense Update Failed: " . $e->getMessage());

        // Dynamic error message to help you debug exactly what is failing
        return back()
            ->withErrors(['transaction' => 'Action failed: ' . $e->getMessage()])
            ->withInput();
    }
}
    /**
     * Reverse a transaction (Audit-safe "Undo")
     */
 
    
    public function show($id){
        
       $expense = Expense::with(['supplier', 'items.account', 'bankAccount'])
        ->where('company_id', Auth::user()->current_company_id)
        ->findOrFail($id);

      // Get the ledger impact for this specific expense
      $journals = JournalEntry::with('account')
        ->where('expense_id', $expense->id)
        ->orderBy('debit', 'desc') // Convention: Debits first
        ->get();

       return view('suppliers.expenses-show', compact('expense', 'journals'));
   }
   
   
   public function reverse(Request $request, $id)
{
    try {
        DB::transaction(function () use ($id) {
            $expense = Expense::where('company_id', Auth::user()->current_company_id)
                ->findOrFail($id);

            if ($expense->status !== 'posted') {
                throw new \Exception("Only posted expenses can be reversed.");
            }

            // 1. Mark the expense as reversed
            $expense->update([
                'status' => 'reversed',
                'reversed_at' => now(),
            ]);

            // 2. Get original journal entries
            $originalJournals = JournalEntry::where('expense_id', $expense->id)->get();

            // 3. Create Reversal Entries (Debits become Credits, Credits become Debits)
            foreach ($originalJournals as $journal) {
                JournalEntry::create([
                    'company_id'       => $journal->company_id,
                    'user_id'          => Auth::id(),
                    'account_id'       => $journal->account_id,
                    'expense_id'       => $expense->id,
                    'supplier_id'      => $journal->supplier_id,
                    'transaction_date' => now(), // Reversal happens today
                    'reference'        => 'REV-' . $journal->reference,
                    'transource'       => 'Reversal',
                    'description'      => "Reversal of: " . $journal->description,
                    'debit'            => $journal->credit, // Swap
                    'credit'           => $journal->debit,  // Swap
                    'transaction_id'   => null,
                ]);
            }
        });

        return back()->with('success', 'Expense reversed and ledger adjusted.');
    } catch (\Exception $e) {
        return back()->with('error', 'Reversal failed: ' . $e->getMessage());
    }
}

public function destroy($id)
{
    try {
        $expense = Expense::where('company_id', Auth::user()->current_company_id)
            ->findOrFail($id);

        // Security: Prevent deleting posted or reversed expenses
        if ($expense->status !== 'draft') {
            return back()->with('error', 'Only drafts can be deleted. Posted entries must be reversed.');
        }

        DB::transaction(function () use ($expense) {
            // Delete line items first
            $expense->items()->delete();
            // Delete the expense header
            $expense->delete();
        });

        return redirect()->route('suppliers.expenses.index')
            ->with('success', 'Draft expense deleted successfully.');

    } catch (\Exception $e) {
        return back()->with('error', 'Failed to delete: ' . $e->getMessage());
    }
}

public function reversalForm($id)
{
    $expense = Expense::where('company_id', Auth::user()->current_company_id)->findOrFail($id);
    return view('suppliers.expenses-reverse', compact('expense'));
}

public function processReversal(Request $request, $id)
{
    $request->validate([
        'reversal_date' => 'required|date',
        'reversal_reason' => 'required|string|min:5'
    ]);

    try {
        DB::transaction(function () use ($request, $id) {
            $expense = Expense::where('company_id', Auth::user()->current_company_id)->findOrFail($id);

            // 1. Update Expense Header
            $expense->update([
                'status' => 'reversed',
                'reversed_at' => $request->reversal_date,
                'reversal_reason' => $request->reversal_reason
            ]);

            // 2. Create Reversal Ledger Entries (The Flip)
            $originals = JournalEntry::where('expense_id', $expense->id)->get();
            foreach ($originals as $journal) {
                JournalEntry::create([
                    'company_id'       => $journal->company_id,
                    'user_id'          => Auth::id(),
                    'account_id'       => $journal->account_id,
                    'expense_id'       => $expense->id,
                    'transaction_date' => $request->reversal_date,
                    'reference'        => 'REV-' . ($journal->reference ?? $expense->id),
                    'transource'       => 'Reversal',
                    'description'      => "REV: " . $request->reversal_reason,
                    'debit'            => $journal->credit, // Credit becomes Debit
                    'credit'           => $journal->debit,  // Debit becomes Credit
                ]);
            }
        });

        return redirect()->route('suppliers.expenses.index')->with('success', 'Expense reversal completed.');
    } catch (\Exception $e) {
        return back()->with('error', 'Reversal Failed: ' . $e->getMessage());
    }
}

public function import(Request $request)
{
    $request->validate(['file' => 'required|mimes:xlsx,xls,csv']);

    try {
        \Maatwebsite\Excel\Facades\Excel::import(new \App\Imports\ExpensesImport, $request->file('file'));
        return redirect()->route('suppliers.expenses.index')->with('success', 'Import completed successfully.');
    } catch (\Exception $e) {
        // This will display the "Row X: Account does not exist" message
        return back()->with('error', $e->getMessage());
    }
}

public function downloadTemplate()
{
    $columns = [
        'reference_no', 
        'supplier', 
        'transaction_date', 
        'payment_account', // e.g., "Zambia National Commercial Bank"
        'payment_method',  // e.g., "Bank Transfer"
        'category',        // e.g., "Office Stationery"
        'description', 
        'amount_with_tax'
    ];

    $callback = function() use ($columns) {
        $file = fopen('php://output', 'w');
        fputcsv($file, $columns);
        // Sample summarized data
        fputcsv($file, ['REF100', 'Kinship Steel', '2026-03-13', 'Petty Cash', 'Cash', 'Repairs', 'Window', '4800.00']);
        fputcsv($file, ['REF100', 'Kinship Steel', '2026-03-13', 'Petty Cash', 'Cash', 'Repairs', 'Doorflame', '1200.00']);
        fclose($file);
    };

    return response()->streamDownload($callback, 'expenses_import_template.csv');
}


public function export()
{
    // Eager load supplier to optimize database performance
    $expenses = Expense::where('company_id', Auth::user()->current_company_id)
        ->with('supplier')
        ->orderBy('payment_date', 'desc')
        ->get();

    // Headers mapped to your accounting requirements
    $headers = [
        'Date', 
        'Reference', 
        'Supplier / Payee', 
        'Status', 
        'Payment Method', 
        'Total Amount',
        'Reversal Date', 
        'Reversal Reason'
    ];

    $callback = function() use ($expenses, $headers) {
        $file = fopen('php://output', 'w');
        
        // Add headers to the CSV
        fputcsv($file, $headers);

        foreach ($expenses as $expense) {
            // Determine Reversal Date display logic
            $revDate = 'N/A';
            if ($expense->status === 'reversed' && $expense->reversed_at) {
                $revDate = \Carbon\Carbon::parse($expense->reversed_at)->format('d F, Y');
            }

            // Determine Reversal Reason display logic
            $revReason = 'N/A';
            if ($expense->status === 'reversed') {
                $revReason = $expense->reversal_reason ?? 'No reason provided';
            }

            fputcsv($file, [
                // Use optional() or null check to prevent errors if payment_date is missing
                $expense->payment_date ? $expense->payment_date->format('d F, Y') : 'N/A',
                $expense->reference_no ?? 'N/A',
                $expense->supplier->name ?? 'Casual Payee',
                strtoupper($expense->status),
                $expense->payment_method ?? 'N/A',
                number_format($expense->total_amount, 2, '.', ''), // Clean numeric format for Excel
                $revDate,
                $revReason
            ]);
        }
        
        fclose($file);
    };

    // Return the stream as a CSV download
    return response()->streamDownload($callback, 'ZedBank_Expenses_Report_' . now()->format('Y-m-d_His') . '.csv', [
        'Content-Type' => 'text/csv',
        'Content-Disposition' => 'attachment; filename="expenses_report.csv"',
    ]);
}
   
}