<?php

namespace App\Http\Controllers;

use App\Models\Invoice;
use App\Models\InvoiceItem;
use App\Models\CreditMemo;
use App\Models\CreditMemoItem;
use App\Models\Customer;
use App\Models\Account;
use App\Models\JournalEntry;
use App\Models\Product;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;

class InvoiceController extends Controller
{
    /**
     * Display a listing of invoices with aging and financial status.
     */
    public function index()
    {
        $companyId = Auth::user()->current_company_id;

        $invoices = Invoice::with('customer')
            ->where('company_id', $companyId)
            ->orderBy('invoice_date', 'desc')
            ->get()
            ->map(function ($invoice) {
                if ($invoice->status !== 'paid' && $invoice->due_date->isPast()) {
                    $invoice->days_overdue = now()->diffInDays($invoice->due_date);
                } else {
                    $invoice->days_overdue = 0;
                }
                return $invoice;
            });

        return view('invoices.index', compact('invoices'));
    }

    /**
     * Show the form for creating a new invoice.
     */
    public function create()
    {
        $companyId = Auth::user()->current_company_id;
        
        $customers = Customer::where('company_id', $companyId)->active()->get();
        $products = Product::where('company_id', $companyId)->get();
        $accounts = Account::where('company_id', $companyId)->byCategory('Revenue')->get();

        $lastInvoice = Invoice::where('company_id', $companyId)->latest()->first();
        $nextId = $lastInvoice ? ($lastInvoice->id + 1) : 1;
        $invoiceNo = 'INV-' . str_pad($nextId, 5, '0', STR_PAD_LEFT);

        return view('invoices.create', compact('customers', 'products', 'accounts', 'invoiceNo'));
    }

    /**
     * Store a newly created invoice (Either as Draft or Posted).
     */
    public function store(Request $request)
    {
        $request->validate([
            'customer_id'   => 'required|exists:customers,id',
            'invoice_no'    => 'required|string',
            'invoice_date'  => 'required|date',
            'due_date'      => 'required|date|after_or_equal:invoice_date',
            'items'         => 'required|array|min:1',
            'items.*.product_id' => 'required|exists:products,id',
            'items.*.qty'        => 'required|numeric|min:0.01',
            'items.*.price'      => 'required|numeric|min:0',
            'items.*.tax_rate'   => 'required|numeric|min:0',
        ]);

        try {
            $msg = DB::transaction(function () use ($request) {
                $companyId = Auth::user()->current_company_id;
                $isDraft = $request->input('action') === 'draft';
                
                $invoice = Invoice::create([
                    'company_id'          => $companyId,
                    'customer_id'         => $request->customer_id,
                    'invoice_no'          => $request->invoice_no,
                    'invoice_date'        => $request->invoice_date,
                    'due_date'            => $request->due_date,
                    'status'              => $isDraft ? 'draft' : 'unpaid',
                    'memo'                => $request->memo,
                    'created_by'          => Auth::id(),
                    'subtotal'            => 0,
                    'tax_total'           => 0,
                    'total_amount'        => 0,
                    'total_paid'          => 0,
                    'outstanding_balance' => 0,
                ]);

                $runningSubtotal = 0;
                $runningTaxTotal = 0;

                foreach ($request->items as $itemData) {
                    $product = Product::findOrFail($itemData['product_id']);

                    // Verify accounting readiness
                    if (!$product->income_account_id) {
                        throw new \Exception("Product '{$product->name}' is missing an Income Account mapping.");
                    }

                    $lineSubtotal = $itemData['qty'] * $itemData['price'];
                    $lineTax = $lineSubtotal * ($itemData['tax_rate'] / 100);

                    $invoice->items()->create([
                        'product_id'         => $product->id,
                        'income_account_id'  => $product->income_account_id,  // Snapshot
                        'expense_account_id' => $product->expense_account_id, // Snapshot
                        'asset_account_id'   => $product->asset_account_id,   // Snapshot
                        'description'        => $itemData['description'] ?? null,
                        'qty'                => $itemData['qty'],
                        'unit_price'         => $itemData['price'],
                        'subtotal'           => $lineSubtotal,
                        'tax_rate'           => $itemData['tax_rate'],
                        'tax_amount'         => $lineTax,
                        'total_amount'       => $lineSubtotal + $lineTax,
                    ]);

                    $runningSubtotal += $lineSubtotal;
                    $runningTaxTotal += $lineTax;
                }

                $grandTotal = $runningSubtotal + $runningTaxTotal;
                $invoice->update([
                    'subtotal'            => $runningSubtotal,
                    'tax_total'           => $runningTaxTotal,
                    'total_amount'        => $grandTotal,
                    'outstanding_balance' => $grandTotal,
                ]);

                if (!$isDraft) {
                    $this->postInvoiceToLedger($invoice);
                    return "Invoice #{$invoice->invoice_no} posted successfully.";
                }

                return "Invoice #{$invoice->invoice_no} saved as draft.";
            });

            return redirect()->route('invoices.index')->with('success', $msg);

        } catch (\Exception $e) {
            \Log::error("Invoice Store Error: " . $e->getMessage());
            return redirect()->back()->withInput()->with('error', 'Critical Error: ' . $e->getMessage());
        }
    }

    /**
     * Display the specified invoice.
     */
    public function show($id)
    {
        $companyId = Auth::user()->current_company_id;
        $invoice = Invoice::with(['customer', 'items.product', 'payments', 'user'])
            ->where('company_id', $companyId)
            ->findOrFail($id);

        return view('invoices.show', compact('invoice'));
    }

    /**
     * Double-Entry Ledger Posting
     */
private function postInvoiceToLedger($invoice)
{
    $companyId = $invoice->company_id;
    $userId = Auth::id();

    // 1. DEBIT: Trade Receivables (Customer owes money)
    $arAccount = Account::where('company_id', $companyId)
        ->where('name', 'Trade Receivables')
        ->first();

    if (!$arAccount) {
        throw new \Exception("Ledger Error: 'Trade Receivables' account not found.");
    }

    JournalEntry::create([
        'company_id'       => $companyId,
        'customer_id'      => $invoice->customer_id,
        'invoice_id'       => $invoice->id,
        'account_id'       => $arAccount->id,
        'transaction_date' => $invoice->invoice_date,
        'reference'        => $invoice->invoice_no,
        'description'      => "Sales on Account: " . $invoice->customer->name,
        'debit'            => $invoice->total_amount,
        'credit'           => 0,
        'user_id'          => $userId,
        'transource'       => 'Sales'
    ]);

    // 2. Process Items
    foreach ($invoice->items as $item) {
        $product = \App\Models\Product::findOrFail($item->product_id);

        // CREDIT: Revenue
        JournalEntry::create([
            'company_id'       => $companyId,
            'customer_id'      => $invoice->customer_id,
            'invoice_id'       => $invoice->id,
            'account_id'       => $product->income_account_id,
            'transaction_date' => $invoice->invoice_date,
            'reference'        => $invoice->invoice_no,
            'description'      => "Revenue: " . $product->name,
            'debit'            => 0,
            'credit'           => $item->subtotal,
            'user_id'          => $userId,
            'transource'       => 'Sales'
        ]);

        // 3. FIFO Inventory & COGS Calculation
        if ($product->type === 'Inventory') {
            $qtyToDrain = $item->qty;
            $totalCogsForLine = 0;

            // Fetch all available batches for this product, oldest first
            $batches = \App\Models\StockBatch::where('product_id', $product->id)
                ->where('company_id', $companyId)
                ->where('remaining_qty', '>', 0)
                ->orderBy('purchase_date', 'asc')
                ->get();

            if ($batches->sum('remaining_qty') < $qtyToDrain) {
                throw new \Exception("Insufficient stock for '{$product->name}'. Required: {$qtyToDrain}, Available: " . $batches->sum('remaining_qty'));
            }

            foreach ($batches as $batch) {
                if ($qtyToDrain <= 0) break;

                // How much can we take from THIS specific batch?
                $drainAmount = min($batch->remaining_qty, $qtyToDrain);
                $lineCost = $drainAmount * $batch->unit_cost;
                $totalCogsForLine += $lineCost;
                
                // Update the batch
                $batch->decrement('remaining_qty', $drainAmount);
                $qtyToDrain -= $drainAmount;

                // Record the specific movement for this batch
                \App\Models\StockMovement::create([
                    'company_id'    => $companyId,
                    'product_id'    => $product->id,
                    'warehouse_id'  => $batch->warehouse_id,
                    'type'          => 'sale',
                    'reference'     => $invoice->invoice_no,
                    'quantity'      => -$drainAmount,
                    'unit_cost'     => $batch->unit_cost,
                    'balance_after' => \App\Models\StockBatch::where('product_id', $product->id)->sum('remaining_qty'),
                    'description'   => "Sale: Batch layer purchased on " . $batch->purchase_date->format('d M Y'),
                    'created_by'    => $userId
                ]);
            }

            // Update legacy field for compatibility
            $product->decrement('qty_on_hand', $item->qty);

            // Post COGS to Ledger using the total calculated from all batches used
            if ($totalCogsForLine > 0) {
                $this->recordFifoCogs($invoice, $product, $totalCogsForLine);
            }
        }
    }

    // 4. CREDIT: Tax Liability
    if ($invoice->tax_total > 0) {
        $taxAccount = Account::where('company_id', $companyId)
            ->where('name', 'Value Added Tax (VAT) Out')
            ->first();

        if ($taxAccount) {
            JournalEntry::create([
                'company_id'       => $companyId,
                'invoice_id'       => $invoice->id,
                'account_id'       => $taxAccount->id,
                'transaction_date' => $invoice->invoice_date,
                'reference'        => $invoice->invoice_no,
                'description'      => "Output VAT: " . $invoice->invoice_no,
                'debit'            => 0,
                'credit'           => $invoice->tax_total,
                'user_id'          => $userId,
                'transource'       => 'Sales'
            ]);
        }
    }
}

  private function recordInventoryCost($invoice, $item, $product)
{
    $totalCost = $item->qty * ($product->cost ?? 0);

    // DEBIT: COGS (Pulled from Product)
    if ($product->expense_account_id) {
        JournalEntry::create([
            'company_id'       => $invoice->company_id,
            'customer_id'      => $invoice->customer_id,
            'invoice_id'       => $invoice->id,
            'account_id'       => $product->expense_account_id,
            'transaction_date' => $invoice->invoice_date,
            'description'      => "COGS: " . $product->name,
            'debit'            => $totalCost,
            'credit'           => 0,
            'user_id'          => Auth::id(),
            'transource'       => 'Sales'
        ]);
    }

    // CREDIT: Inventory Asset (Pulled from Product)
    if ($product->asset_account_id) {
        JournalEntry::create([
            'company_id'       => $invoice->company_id,
            'customer_id'      => $invoice->customer_id,
            'invoice_id'       => $invoice->id,
            'account_id'       => $product->asset_account_id,
            'transaction_date' => $invoice->invoice_date,
            'description'      => "Inventory Reduction: " . $product->name,
            'debit'            => 0,
            'credit'           => $totalCost,
            'user_id'          => Auth::id(),
            'transource'       => 'Sales'
        ]);
    }
   }

    public function post($id)
    {
        return DB::transaction(function () use ($id) {
            $invoice = Invoice::findOrFail($id);
            if ($invoice->status !== 'draft') return redirect()->back()->with('error', 'Already posted.');
            $invoice->update(['status' => 'unpaid']);
            $this->postInvoiceToLedger($invoice);
            return redirect()->route('invoices.show', $invoice->id)->with('success', 'Invoice finalized.');
        });
    }
    
    
public function createCreditMemo(Request $request, $id)
{
    $companyId = Auth::user()->current_company_id;
    
    // 1. Fetch original invoice with items to populate the form
    $invoice = Invoice::with(['customer', 'items.product'])
        ->where('company_id', $companyId)
        ->findOrFail($id);

    // 2. Generate unique Credit Memo Number (Querying the separate credit_memos table)
    $lastCM = CreditMemo::where('company_id', $companyId)->latest()->first();
    $nextId = $lastCM ? ($lastCM->id + 1) : 1;
    $cmNumber = 'CM-' . str_pad($nextId, 5, '0', STR_PAD_LEFT);
    $reasons = DB::table('credit_note_reasons')->orderBy('sort_order')->get();

    return view('sales.credit-memos.create', compact('invoice', 'cmNumber', 'reasons'));
}

public function storeCreditMemo(Request $request)
{
    $request->validate([
        'original_invoice_id'    => 'required|exists:invoices,id',
        'credit_memo_no'         => 'required|unique:credit_memos,credit_memo_no',
        'credit_note_reason_id'  => 'required|exists:credit_note_reasons,id',
        'items'                  => 'required|array',
    ]);

    try {
        return DB::transaction(function () use ($request) {
            $companyId = Auth::user()->current_company_id;
            $originalInvoice = Invoice::findOrFail($request->original_invoice_id);

            // 1. Create Header
            $creditMemo = CreditMemo::create([
                'company_id'             => $companyId,
                'customer_id'            => $originalInvoice->customer_id,
                'original_invoice_id'    => $originalInvoice->id,
                'credit_note_reason_id'  => $request->credit_note_reason_id,
                'credit_memo_no'         => $request->credit_memo_no,
                'transaction_date'       => now(),
                'status'                 => 'posted',
                'created_by'             => Auth::id(),
                'subtotal'               => 0,
                'tax_total'              => 0,
                'total_amount'           => 0,
            ]);

            $runningSubtotal = 0;
            $runningTax = 0;
            $processedItems = collect();

            foreach ($request->items as $itemData) {
                if ($itemData['qty'] <= 0) continue;

                $product = \App\Models\Product::findOrFail($itemData['product_id']);
                
                $subtotal = $itemData['qty'] * $itemData['unit_price'];
                $tax = $subtotal * ($itemData['tax_rate'] / 100);

                $newItem = $creditMemo->items()->create([
                    'product_id'         => $product->id,
                    'income_account_id'  => $product->income_account_id,
                    'expense_account_id' => $product->expense_account_id,
                    'asset_account_id'   => $product->asset_account_id,
                    'qty'                => $itemData['qty'],
                    'unit_price'         => $itemData['unit_price'],
                    'subtotal'           => $subtotal,
                    'tax_rate'           => $itemData['tax_rate'],
                    'tax_amount'         => $tax,
                    'total_amount'       => $subtotal + $tax,
                ]);

                $processedItems->push($newItem);
                $runningSubtotal += $subtotal;
                $runningTax += $tax;

                // --- INVENTORY RESTOCK LOGIC ---
                if ($product->type === 'Inventory') {
                    $warehouse = \App\Models\Warehouse::where('company_id', $companyId)->where('is_primary', true)->first();
                    if (!$warehouse) throw new \Exception("Return failed: No primary warehouse defined.");

                    // Create a new batch for the returned goods
                    \App\Models\StockBatch::create([
                        'company_id'    => $companyId,
                        'product_id'    => $product->id,
                        'warehouse_id'  => $warehouse->id,
                        'purchase_qty'  => $itemData['qty'],
                        'remaining_qty' => $itemData['qty'],
                        'unit_cost'     => $product->cost, // Return at current standard cost
                        'purchase_date' => now(),
                        'reference'     => $request->credit_memo_no,
                        'created_by'    => Auth::id()
                    ]);

                    \App\Models\StockMovement::create([
                        'company_id'    => $companyId,
                        'product_id'    => $product->id,
                        'warehouse_id'  => $warehouse->id,
                        'type'          => 'adjustment_in',
                        'reference'     => $request->credit_memo_no,
                        'quantity'      => $itemData['qty'],
                        'unit_cost'     => $product->cost,
                        'balance_after' => \App\Models\StockBatch::where('product_id', $product->id)->sum('remaining_qty'),
                        'description'   => "Sales Return: " . $request->credit_memo_no,
                        'created_by'    => Auth::id()
                    ]);

                    $product->increment('qty_on_hand', $itemData['qty']);
                }
            }

            $creditMemo->update([
                'subtotal'     => $runningSubtotal,
                'tax_total'    => $runningTax,
                'total_amount' => $runningSubtotal + $runningTax
            ]);

            // Update Invoice Balance
            $originalInvoice->decrement('outstanding_balance', $creditMemo->total_amount);
            
            // Post Financials
            $this->postCreditMemoToLedger($creditMemo, $originalInvoice, $processedItems);

            return redirect()->route('invoices.index')->with('success', "Credit Memo processed and inventory restocked.");
        });
    } catch (\Exception $e) {
        return redirect()->back()->withInput()->with('error', $e->getMessage());
    }
}

private function postCreditMemoToLedger($cm, $originalInvoice, $items)
{
    $companyId = $cm->company_id;
    $userId = Auth::id();

    // 1. CREDIT Trade Receivables (Decreasing what the customer owes)
    // Account type 3 is typically 'Accounts Receivable'
    $arAccount = Account::where('company_id', $companyId)->where('account_type_id', 3)->first();
    
    if (!$arAccount) {
        throw new \Exception("A/R Account mapping missing. Please check your Chart of Accounts.");
    }

    JournalEntry::create([
        'company_id'       => $companyId,
        'customer_id'      => $cm->customer_id,
        'invoice_id'       => $originalInvoice->id,
        'credit_memo_id'   => $cm->id,
        'account_id'       => $arAccount->id,
        'transaction_date' => $cm->transaction_date,
        'reference'        => $cm->credit_memo_no,
        'description'      => "Credit Memo adjustment for Invoice #{$originalInvoice->invoice_no}",
        'debit'            => 0,
        'credit'           => $cm->total_amount,
        'transource'       => 'Credit Memo',
        'user_id'          => $userId
    ]);

    // 2. DEBIT Items (Reversing Revenue and Inventory Costs)
    foreach ($items as $item) {
        // Fetch product to get the current cost for COGS reversal
        $product = \App\Models\Product::find($item->product_id);

        // A. DEBIT Revenue (Decreasing Sales)
        JournalEntry::create([
            'company_id'       => $companyId,
            'customer_id'      => $cm->customer_id,
            'invoice_id'       => $originalInvoice->id,
            'credit_memo_id'   => $cm->id,
            'account_id'       => $item->income_account_id,
            'transaction_date' => $cm->transaction_date,
            'reference'        => $cm->credit_memo_no,
            'description'      => "Sales Return: " . ($item->description ?? $product->name),
            'debit'            => $item->subtotal,
            'credit'           => 0,
            'transource'       => 'Credit Memo',
            'user_id'          => $userId
        ]);

        // B. INVENTORY RESTOCKING (COGS Reversal)
        // We only do this if the product is 'Inventory' and we have the necessary accounts
        if ($product && $product->type === 'Inventory' && $item->asset_account_id && $item->expense_account_id) {
            
            $totalCostToReverse = $item->qty * ($product->cost ?? 0);

            if ($totalCostToReverse > 0) {
                // DEBIT: Inventory Asset (Increasing Asset because items came back)
                JournalEntry::create([
                    'company_id'       => $companyId,
                    'credit_memo_id'   => $cm->id,
                    'account_id'       => $item->asset_account_id,
                    'transaction_date' => $cm->transaction_date,
                    'reference'        => $cm->credit_memo_no,
                    'description'      => "Restocking: " . ($item->description ?? $product->name),
                    'debit'            => $totalCostToReverse,
                    'credit'           => 0,
                    'transource'       => 'Credit Memo',
                    'user_id'          => $userId
                ]);

                // CREDIT: Cost of Goods Sold (Decreasing Expense because sale was reversed)
                JournalEntry::create([
                    'company_id'       => $companyId,
                    'credit_memo_id'   => $cm->id,
                    'account_id'       => $item->expense_account_id,
                    'transaction_date' => $cm->transaction_date,
                    'reference'        => $cm->credit_memo_no,
                    'description'      => "COGS Reversal: " . ($item->description ?? $product->name),
                    'debit'            => 0,
                    'credit'           => $totalCostToReverse,
                    'transource'       => 'Credit Memo',
                    'user_id'          => $userId
                ]);
            }
        }
    }

    // 3. DEBIT VAT (Reversing Tax Liability)
    if ($cm->tax_total > 0) {
        $taxAccount = Account::where('company_id', $companyId)
            ->where('name', 'LIKE', '%VAT%')
            ->where('name', 'LIKE', '%Out%') // Targeting the Sales Tax account
            ->first();

        if ($taxAccount) {
            JournalEntry::create([
                'company_id'       => $companyId,
                'credit_memo_id'   => $cm->id,
                'account_id'       => $taxAccount->id,
                'transaction_date' => $cm->transaction_date,
                'reference'        => $cm->credit_memo_no,
                'description'      => "VAT Reversal for CM #{$cm->credit_memo_no}",
                'debit'            => $cm->tax_total,
                'credit'           => 0,
                'transource'       => 'Credit Memo',
                'user_id'          => $userId
            ]);
        }
    }
}


public function history($id)
{
    $companyId = Auth::user()->current_company_id;

    // 1. Fetch Invoice with its Customer (Eager load customer)
    $invoice = Invoice::with('customer')
        ->where('company_id', $companyId)
        ->findOrFail($id);

    // 2. Fetch Payments from cashbook
    $payments = DB::table('cashbook')
        ->where('invoice_id', $id)
        ->where('company_id', $companyId)
        ->select(
            'transaction_date as date', 
            'reference', 
            DB::raw("'Payment Received' as type"), 
            DB::raw("(debit + credit) as amount"),
            DB::raw("0 as is_debit")
        )
        ->get();

    // 3. Fetch Credit Memos
    $memos = DB::table('credit_memos')
        ->where('original_invoice_id', $id)
        ->where('company_id', $companyId)
        ->select(
            'transaction_date as date', 
            'credit_memo_no as reference', 
            DB::raw("'Credit Memo' as type"), 
            'total_amount as amount', 
            DB::raw("0 as is_debit")
        )
        ->get();

    // 4. Combine and Sort (Invoice First, then Credits)
    $history = collect([
        (object)[
            'date' => $invoice->invoice_date->format('Y-m-d'),
            'reference' => $invoice->invoice_no,
            'type' => 'Original Invoice',
            'amount' => $invoice->total_amount,
            'is_debit' => 1
        ]
    ])
    ->concat($payments->map(function($item) {
        $item->date = \Carbon\Carbon::parse($item->date)->format('Y-m-d');
        return $item;
    }))
    ->concat($memos->map(function($item) {
        $item->date = \Carbon\Carbon::parse($item->date)->format('Y-m-d');
        return $item;
    }))
    ->sort(function ($a, $b) {
        if ($a->date != $b->date) {
            return strcmp($a->date, $b->date);
        }
        return $b->is_debit - $a->is_debit;
    });

    return view('invoices.history', compact('invoice', 'history', 'payments', 'memos'));
}


public function agingAnalysis()
{
    $companyId = Auth::user()->current_company_id;

    // Fetch all unpaid or partially paid invoices
    $invoices = Invoice::with('customer')
        ->where('company_id', $companyId)
        ->where('status', '!=', 'paid')
        ->where('outstanding_balance', '>', 0)
        ->orderBy('due_date', 'asc')
        ->get();

    return view('invoices.aging', compact('invoices'));
}


/**
 * Record the COGS Double Entry in the General Ledger.
 */
private function recordFifoCogs($invoice, $product, $totalCogs)
{
    // 1. DEBIT: Cost of Goods Sold (Expense)
    \App\Models\JournalEntry::create([
        'company_id'       => $invoice->company_id,
        'customer_id'      => $invoice->customer_id,
        'invoice_id'       => $invoice->id,
        'account_id'       => $product->expense_account_id, // Usually COGS account
        'transaction_date' => $invoice->invoice_date,
        'reference'        => $invoice->invoice_no,
        'description'      => "COGS: " . $product->name,
        'debit'            => $totalCogs,
        'credit'           => 0,
        'user_id'          => Auth::id(),
        'transource'       => 'Sales-COGS',
    ]);

    // 2. CREDIT: Inventory Asset (Asset)
    \App\Models\JournalEntry::create([
        'company_id'       => $invoice->company_id,
        'customer_id'      => $invoice->customer_id,
        'invoice_id'       => $invoice->id,
        'account_id'       => $product->asset_account_id, // Inventory Asset account
        'transaction_date' => $invoice->invoice_date,
        'reference'        => $invoice->invoice_no,
        'description'      => "Inventory Reduction: " . $product->name,
        'debit'            => 0,
        'credit'           => $totalCogs,
        'user_id'          => Auth::id(),
        'transource'       => 'Sales-COGS',
    ]);
}
    
}