<?php

namespace App\Http\Controllers\Modules\Purchase;

use App\Http\Controllers\Controller;
use App\Http\Requests\Purchase\StorePurchaseCreditNoteRequest;
use App\Http\Requests\Purchase\StorePurchaseDebitNoteRequest;
use App\Http\Requests\Purchase\StorePurchaseInvoiceRequest;
use App\Http\Traits\HasRoleViews;
use App\Models\CreditNote;
use App\Models\CustomerVendor;
use App\Models\Grn;
use App\Models\Purchase;
use App\Models\PurchaseDebitNote;
use App\Models\PurchaseFile;
use App\Models\PurchaseItem;
use App\Models\PurchaseOrder;
use App\Models\StockItem;
use App\Services\ExportService;
use Carbon\Carbon;
use Illuminate\Http\JsonResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

/**
 * Unified Purchase Management Controller
 *
 * Handles purchase invoices, credit notes, debit notes, aging reports.
 * Reference: PurchaseManagementController (SuperAdmin)
 */
class PurchaseManagementController extends Controller
{
    use HasRoleViews;

    protected ExportService $exportService;

    public function __construct(ExportService $exportService)
    {
        $this->exportService = $exportService;
    }

    /**
     * Display the purchase management page with DataTable
     */
    public function index(Request $request)
    {
        if ($request->ajax()) {
            $query = Purchase::with(['party', 'items']);

            if ($request->date_range) {
                [$start, $end] = explode(' to ', $request->date_range);
                $query->whereBetween('invoice_date', [$start, $end]);
            }

            if ($request->party_id) {
                $query->where('party_id', $request->party_id);
            }

            if ($request->invoice_no) {
                $query->where('invoice_no', 'like', '%' . $request->invoice_no . '%');
            }

            if ($request->status) {
                $query->where('status', $request->status);
            }

            return datatables()->of($query->latest())
                ->addColumn('party', fn ($row) => $row->party->company ?? '')
                ->addColumn('items', fn ($row) => $row->items()->count())
                ->addColumn('actions', function ($row) {
                    return '
                        <button class="btn btn-sm btn-outline-primary view-purchase" data-id="' . $row->id . '">
                            <i class="fas fa-eye"></i>
                        </button>
                        <button class="btn btn-sm btn-outline-success edit-purchase" data-id="' . $row->id . '" data-row="' . base64_encode(json_encode($row)) . '">
                            <i class="fas fa-edit"></i>
                        </button>
                        <button class="btn btn-sm btn-outline-warning credit-note-purchase"
                            data-id="' . $row->id . '"
                            data-invoice="' . $row->invoice_no . '"
                            data-party="' . htmlspecialchars($row->party->company ?? '') . '"
                            data-party_id="' . $row->party_id . '">
                            <i class="fas fa-file-invoice-dollar"></i>
                        </button>
                        <button class="btn btn-sm btn-outline-danger delete-purchase" data-id="' . $row->id . '">
                            <i class="fas fa-trash"></i>
                        </button>
                    ';
                })
                ->rawColumns(['actions'])
                ->make(true);
        }

        $parties = CustomerVendor::all();
        $items = StockItem::all();
        $purchaseOrders = PurchaseOrder::with(['quotation', 'quotation.items'])->where('status', 'approved')->get();
        $nextInvoiceNo = nextNumber('UEPL/P/INV', 'purchases', 'invoice_no');
        $currentDate = date('Y-m-d');

        return $this->roleView('purchasemanagement.index', compact('parties', 'items', 'purchaseOrders', 'nextInvoiceNo', 'currentDate'));
    }

    /**
     * Accounts view
     */
    public function accounts()
    {
        $parties = CustomerVendor::all();
        $items = StockItem::all();
        $purchaseOrders = PurchaseOrder::with(['quotation', 'quotation.items'])->where('status', 'approved')->get();
        $nextInvoiceNo = nextNumber('UEPL/P/INV', 'purchases', 'invoice_no');
        $currentDate = date('Y-m-d');

        return $this->roleView('purchasemanagement.accounts', compact('parties', 'items', 'purchaseOrders', 'nextInvoiceNo', 'currentDate'));
    }

    /**
     * Show purchase details
     */
    public function show(int $id): JsonResponse
    {
        $purchase = Purchase::with(['party', 'items.item', 'files'])->findOrFail($id);

        return response()->json($purchase);
    }

    /**
     * Get purchase for editing
     */
    public function edit(int $id): JsonResponse
    {
        $purchase = Purchase::with(['items', 'files'])->findOrFail($id);

        return response()->json($purchase);
    }

    /**
     * Store a new purchase
     */
    public function store(Request $request)
    {
        $validatedData = $request->validate([
            'purchase_order_id' => 'nullable|integer',
            'purchase_order_date' => 'nullable|date',
            'purchase_no' => 'required|string',
            'vendor_email' => 'nullable|email',
            'invoice_no' => 'required|string',
            'invoice_date' => 'required|date',
            'purchase_id' => 'required|integer|exists:customer_vendors,id',
            'vendor_reference_no' => 'nullable|string',
            'payment_terms' => 'nullable|integer',
            'company_gstn' => 'required|string',
            'company_address' => 'required|string',
            'company_phone' => 'required|string',
            'description' => 'nullable|string',
            'delivery_terms' => 'nullable|string',
            'items' => 'required|array',
            'items.*.sl_no' => 'required|integer',
            'items.*.item_id' => 'required|integer',
            'items.*.description' => 'nullable|string',
            'items.*.quantity' => 'required|numeric',
            'items.*.uom' => 'required|string',
            'items.*.rate' => 'required|numeric',
            'items.*.tds' => 'nullable|numeric',
            'items.*.discount' => 'nullable|numeric',
            'items.*.value' => 'nullable|numeric',
            'items.*.sgst' => 'nullable|numeric',
            'items.*.cgst' => 'nullable|numeric',
            'items.*.igst' => 'nullable|numeric',
            'items.*.amount' => 'nullable|numeric',
            'additional_charges' => 'nullable|numeric',
            'total_tds' => 'nullable|numeric',
            'sub_total' => 'required|numeric',
            'total_amount' => 'required|numeric',
            'paid_amount' => 'nullable|numeric',
            'balance_amount' => 'nullable|numeric',
            'payment_mode' => 'nullable|string',
            'payment_reference' => 'nullable|string',
            'payment_reason' => 'nullable|string',
            'note' => 'nullable|string',
        ]);

        $purchase = Purchase::create([
            'purchase_no' => $validatedData['invoice_no'],
            'purchase_date' => $validatedData['invoice_date'],
            'purchase_order_no' => $request->purchase_order_id ? PurchaseOrder::find($request->purchase_order_id)?->purchase_order_no : null,
            'purchase_order_date' => $validatedData['purchase_order_date'] ?? null,
            'vendor_id' => $validatedData['purchase_id'],
            'vendor_ref_no' => $validatedData['vendor_reference_no'] ?? null,
            'vendor_invoice_no' => $validatedData['purchase_no'],
            'company_phone' => $validatedData['company_phone'],
            'company_email' => $validatedData['vendor_email'],
            'company_gstn' => $validatedData['company_gstn'],
            'company_address' => $validatedData['company_address'],
            'payment_terms' => $validatedData['payment_terms'] ?? null,
            'subtotal' => $validatedData['sub_total'],
            'additional_charges' => $validatedData['additional_charges'] ?? 0,
            'tds_amount' => $validatedData['total_tds'] ?? 0,
            'grand_total' => $validatedData['total_amount'],
            'paid_amount' => $validatedData['paid_amount'] ?? 0,
            'balance_amount' => $validatedData['balance_amount'] ?? 0,
            'payment_status' => 'unpaid',
            'payment_method' => $validatedData['payment_mode'] ?? null,
            'payment_reference_no' => $validatedData['payment_reference'] ?? null,
            'payment_delay_reason' => $validatedData['payment_reason'] ?? null,
            'description' => $validatedData['description'] ?? null,
            'delivery_terms' => $validatedData['delivery_terms'] ?? null,
            'note' => $validatedData['note'] ?? null,
        ]);

        foreach ($validatedData['items'] as $item) {
            $stockItem = StockItem::find($item['item_id']);
            PurchaseItem::create([
                'purchase_id' => $purchase->id,
                'stock_item_id' => $item['item_id'],
                'stock_item_name' => $stockItem ? $stockItem->item_name : '',
                'description' => $item['description'] ?? '',
                'uom' => $item['uom'],
                'quantity' => $item['quantity'],
                'unit_rate' => $item['rate'],
                'tds' => $item['tds'] ?? 0,
                'discount' => $item['discount'] ?? 0,
                'value' => $item['value'] ?? 0,
                'sgst' => $item['sgst'] ?? 0,
                'cgst' => $item['cgst'] ?? 0,
                'igst' => $item['igst'] ?? 0,
                'amount' => $item['amount'] ?? 0,
            ]);
        }

        return $this->redirectToRoute('purchase.index')->with('success', 'Purchase created successfully');
    }

    /**
     * Update purchase
     */
    public function update(Request $request, int $id)
    {
        $purchase = Purchase::findOrFail($id);

        $validatedData = $request->validate([
            'purchase_order_id' => 'nullable|integer',
            'purchase_order_date' => 'required|date',
            'purchase_no' => 'required|string',
            'vendor_email' => 'required|email',
            'invoice_no' => 'required|string',
            'invoice_date' => 'required|date',
            'purchase_id' => 'required|integer|exists:customer_vendors,id',
            'vendor_reference_no' => 'nullable|string',
            'payment_terms' => 'nullable|integer',
            'company_gstn' => 'required|string',
            'company_address' => 'required|string',
            'company_phone' => 'required|string',
            'description' => 'nullable|string',
            'delivery_terms' => 'nullable|string',
            'items' => 'required|array|min:1',
            'items.*.sl_no' => 'required|integer',
            'items.*.item_id' => 'required|integer',
            'items.*.description' => 'nullable|string',
            'items.*.quantity' => 'required|numeric|min:0.01',
            'items.*.uom' => 'required|string',
            'items.*.rate' => 'required|numeric|min:0',
            'items.*.tds' => 'nullable|numeric|min:0',
            'items.*.discount' => 'nullable|numeric|min:0',
            'items.*.value' => 'nullable|numeric',
            'items.*.sgst' => 'nullable|numeric',
            'items.*.cgst' => 'nullable|numeric',
            'items.*.igst' => 'nullable|numeric',
            'items.*.amount' => 'nullable|numeric',
            'additional_charges' => 'nullable|numeric|min:0',
            'total_tds' => 'nullable|numeric|min:0',
            'sub_total' => 'required|numeric|min:0',
            'total_amount' => 'required|numeric|min:0',
            'paid_amount' => 'nullable|numeric|min:0',
            'balance_amount' => 'nullable|numeric|min:0',
            'payment_mode' => 'nullable|string',
            'payment_reference' => 'nullable|string',
            'payment_reason' => 'nullable|string',
            'note' => 'nullable|string',
        ]);

        $purchase->update([
            'purchase_date' => $validatedData['invoice_date'],
            'purchase_order_no' => $request->purchase_order_id ? PurchaseOrder::find($request->purchase_order_id)?->purchase_order_no : $purchase->purchase_order_no,
            'purchase_order_date' => $validatedData['purchase_order_date'],
            'vendor_id' => $validatedData['purchase_id'],
            'vendor_ref_no' => $validatedData['vendor_reference_no'] ?? null,
            'vendor_invoice_no' => $validatedData['purchase_no'],
            'company_phone' => $validatedData['company_phone'],
            'company_email' => $validatedData['vendor_email'],
            'company_gstn' => $validatedData['company_gstn'],
            'company_address' => $validatedData['company_address'],
            'payment_terms' => $validatedData['payment_terms'] ?? null,
            'subtotal' => $validatedData['sub_total'],
            'additional_charges' => $validatedData['additional_charges'] ?? 0,
            'tds_amount' => $validatedData['total_tds'] ?? 0,
            'grand_total' => $validatedData['total_amount'],
            'paid_amount' => $validatedData['paid_amount'] ?? 0,
            'balance_amount' => $validatedData['balance_amount'] ?? 0,
            'payment_status' => ($validatedData['paid_amount'] ?? 0) >= $validatedData['total_amount'] ? 'paid' : 'unpaid',
            'payment_method' => $validatedData['payment_mode'] ?? null,
            'payment_reference_no' => $validatedData['payment_reference'] ?? null,
            'payment_delay_reason' => $validatedData['payment_reason'] ?? null,
            'description' => $validatedData['description'] ?? null,
            'delivery_terms' => $validatedData['delivery_terms'] ?? null,
            'note' => $validatedData['note'] ?? null,
        ]);

        $itemIdsFromRequest = collect($validatedData['items'])->map(fn ($item) => $item['id'] ?? null)->filter();
        $purchase->items()->whereNotIn('id', $itemIdsFromRequest)->delete();

        foreach ($validatedData['items'] as $item) {
            $stockItem = StockItem::find($item['item_id']);
            $itemData = [
                'stock_item_id' => $item['item_id'],
                'stock_item_name' => $stockItem ? $stockItem->item_name : '',
                'description' => $item['description'] ?? '',
                'uom' => $item['uom'],
                'quantity' => $item['quantity'],
                'unit_rate' => $item['rate'],
                'tds' => $item['tds'] ?? 0,
                'discount' => $item['discount'] ?? 0,
                'value' => $item['value'] ?? 0,
                'sgst' => $item['sgst'] ?? 0,
                'cgst' => $item['cgst'] ?? 0,
                'igst' => $item['igst'] ?? 0,
                'amount' => $item['amount'] ?? 0,
            ];

            if (isset($item['id'])) {
                $purchase->items()->where('id', $item['id'])->update($itemData);
            } else {
                $purchase->items()->create($itemData);
            }
        }

        return $this->redirectToRoute('purchase.index')->with('success', 'Purchase updated successfully');
    }

    /**
     * Delete purchase
     */
    public function destroy(Purchase $purchase): JsonResponse
    {
        // Authorization: Only SuperAdmin or Manager can delete
        if (!$this->isSuperAdmin() && !$this->isManager()) {
            return response()->json([
                'success' => false,
                'message' => 'Unauthorized. Only SuperAdmin or Manager can delete purchases.',
            ], 403);
        }

        // Check if there are related credit notes
        $hasCreditNotes = CreditNote::where('invoice_no', $purchase->purchase_no)->exists();
        if ($hasCreditNotes && !$this->isSuperAdmin()) {
            return response()->json([
                'success' => false,
                'message' => 'Cannot delete purchase with existing credit notes. Only SuperAdmin can delete.',
            ], 403);
        }

        // Check if there are related debit notes
        $hasDebitNotes = PurchaseDebitNote::where('invoice_no', $purchase->purchase_no)->exists();
        if ($hasDebitNotes && !$this->isSuperAdmin()) {
            return response()->json([
                'success' => false,
                'message' => 'Cannot delete purchase with existing debit notes. Only SuperAdmin can delete.',
            ], 403);
        }

        $purchase->items()->delete();

        foreach ($purchase->files as $file) {
            if ($file->file_path && Storage::disk('public')->exists($file->file_path)) {
                Storage::disk('public')->delete($file->file_path);
            }
            $file->delete();
        }

        $purchase->delete();

        return response()->json(['success' => true, 'message' => 'Purchase and related data deleted.']);
    }

    /**
     * Get next invoice number
     */
    public function getNextInvoiceNumber(): JsonResponse
    {
        $latest = Purchase::orderBy('id', 'desc')->first();
        if (!$latest || !$latest->invoice_no) {
            $nextNumber = 1;
        } else {
            $lastNumber = (int) substr($latest->invoice_no, -4);
            $nextNumber = $lastNumber + 1;
        }
        $nextInvoiceNo = 'UEPL/P/INV/' . str_pad($nextNumber, 4, '0', STR_PAD_LEFT);

        return response()->json(['invoice_no' => $nextInvoiceNo]);
    }

    // ==================== CREDIT NOTES ====================

    /**
     * Get credit notes data
     */
    public function creditNotesData(Request $request)
    {
        $query = CreditNote::with('party');

        if ($request->date_range) {
            [$start, $end] = explode(' to ', $request->date_range);
            $query->whereBetween('date', [$start, $end]);
        }

        if ($request->party_id) {
            $query->where('party_id', $request->party_id);
        }

        if ($request->credit_note_no) {
            $query->where('credit_note_no', 'like', "%{$request->credit_note_no}%");
        }

        return datatables()->eloquent($query)
            ->addColumn('party', fn ($row) => $row->party ? $row->party->company : '')
            ->addColumn('actions', function ($row) {
                return '
                    <button class="btn btn-sm btn-outline-primary view-credit me-1" data-id="' . $row->id . '" title="View">
                        <i class="fas fa-eye"></i>
                    </button>
                    <button class="btn btn-sm btn-outline-info edit-credit me-1" data-id="' . $row->id . '" title="Edit">
                        <i class="fas fa-edit"></i>
                    </button>
                    <button class="btn btn-sm btn-outline-danger delete-credit" data-id="' . $row->id . '" title="Delete">
                        <i class="fas fa-trash"></i>
                    </button>
                ';
            })
            ->rawColumns(['actions'])
            ->make(true);
    }

    /**
     * Get next credit note number
     */
    public function getNextCreditNoteNo(): JsonResponse
    {
        $year = date('Y');
        $latest = CreditNote::where('credit_note_no', 'like', "CN-{$year}-%")
            ->orderByDesc('id')->first();

        $nextNumber = $latest ? (intval(substr($latest->credit_note_no, -4)) + 1) : 1;
        $nextCreditNo = 'CN-' . $year . '-' . str_pad($nextNumber, 4, '0', STR_PAD_LEFT);

        return response()->json(['credit_note_no' => $nextCreditNo]);
    }

    /**
     * Store credit note
     */
    public function creditNoteStore(StorePurchaseCreditNoteRequest $request): JsonResponse
    {
        $creditNote = CreditNote::create($request->validated());

        return response()->json(['success' => true, 'credit_note_id' => $creditNote->id]);
    }

    /**
     * Get credit note for editing
     */
    public function getCreditNote(int $id): JsonResponse
    {
        $creditNote = CreditNote::with('party')->findOrFail($id);
        $data = $creditNote->toArray();
        $data['date'] = $creditNote->date ? Carbon::parse($creditNote->date)->format('Y-m-d') : null;

        return response()->json(['success' => true, 'data' => $data]);
    }

    /**
     * Update credit note
     */
    public function creditNoteUpdate(Request $request, int $id): JsonResponse
    {
        $creditNote = CreditNote::findOrFail($id);

        $data = $request->validate([
            'date' => 'required|date',
            'invoice_no' => 'required|string',
            'party_id' => 'required|exists:customer_vendors,id',
            'amount' => 'required|numeric|min:0',
            'reason' => 'required|string',
            'description' => 'nullable|string',
        ]);

        $creditNote->update($data);

        return response()->json(['success' => true, 'message' => 'Credit Note updated successfully']);
    }

    /**
     * Delete credit note
     */
    public function creditNoteDelete(int $id): JsonResponse
    {
        // Authorization: Only SuperAdmin or Manager can delete
        if (!$this->isSuperAdmin() && !$this->isManager()) {
            return response()->json([
                'success' => false,
                'message' => 'Unauthorized. Only SuperAdmin or Manager can delete credit notes.',
            ], 403);
        }

        CreditNote::findOrFail($id)->delete();

        return response()->json(['success' => true, 'message' => 'Credit Note deleted successfully']);
    }

    // ==================== DEBIT NOTES ====================

    /**
     * Get debit notes
     */
    public function getDebitNotes(Request $request): JsonResponse
    {
        $query = PurchaseDebitNote::with('party');

        if ($request->has('party_id') && !empty($request->party_id)) {
            $query->where('party_id', $request->party_id);
        }

        if ($request->has('from_date') && !empty($request->from_date)) {
            $query->whereDate('date', '>=', $request->from_date);
        }

        if ($request->has('to_date') && !empty($request->to_date)) {
            $query->whereDate('date', '<=', $request->to_date);
        }

        $debitNotes = $query->latest()->get();

        return response()->json(['data' => $debitNotes]);
    }

    /**
     * Get next debit note number
     */
    public function getNextDebitNoteNumber(): JsonResponse
    {
        $latest = PurchaseDebitNote::latest()->first();
        if (!$latest) {
            return response()->json(['next_number' => 'DN-0001']);
        }

        $parts = explode('-', $latest->debit_note_no);
        $lastNumber = count($parts) > 1 ? intval($parts[1]) : 0;
        $nextNumber = 'DN-' . str_pad($lastNumber + 1, 4, '0', STR_PAD_LEFT);

        return response()->json(['next_number' => $nextNumber]);
    }

    /**
     * Store debit note
     */
    public function storeDebitNote(StorePurchaseDebitNoteRequest $request): JsonResponse
    {
        $validated = $request->validated();

        // Auto-generate debit note number if not provided
        if (empty($validated['debit_note_no'])) {
            $next = $this->getNextDebitNoteNumber()->getData();
            $validated['debit_note_no'] = $next->next_number;
        }

        PurchaseDebitNote::create($validated);

        return response()->json(['success' => true, 'message' => 'Debit Note created successfully']);
    }

    /**
     * Get single debit note
     */
    public function getDebitNote(int $id): JsonResponse
    {
        $debitNote = PurchaseDebitNote::with('party')->findOrFail($id);

        return response()->json($debitNote);
    }

    /**
     * Update debit note
     */
    public function updateDebitNote(Request $request, int $id): JsonResponse
    {
        $debitNote = PurchaseDebitNote::findOrFail($id);

        $request->validate([
            'date' => 'required|date',
            'amount' => 'required|numeric|min:0',
            'invoice_no' => 'required|string',
            'reason' => 'required|string',
        ]);

        $debitNote->update([
            'date' => $request->date,
            'invoice_no' => $request->invoice_no,
            'amount' => $request->amount,
            'reason' => $request->reason,
            'description' => $request->description,
        ]);

        return response()->json(['success' => true, 'message' => 'Debit Note updated successfully']);
    }

    /**
     * Delete debit note
     */
    public function deleteDebitNote(int $id): JsonResponse
    {
        // Authorization: Only SuperAdmin or Manager can delete
        if (!$this->isSuperAdmin() && !$this->isManager()) {
            return response()->json([
                'success' => false,
                'message' => 'Unauthorized. Only SuperAdmin or Manager can delete debit notes.',
            ], 403);
        }

        PurchaseDebitNote::findOrFail($id)->delete();

        return response()->json(['success' => true, 'message' => 'Debit Note deleted successfully']);
    }

    // ==================== AGING REPORT ====================

    /**
     * Calculate actual balance for a purchase invoice
     * Formula: Invoice Total + Debit Notes - Credit Notes - Paid Amount
     */
    protected function calculatePurchaseBalance(Purchase $purchase): float
    {
        // Get credit notes for this purchase
        $creditNotesTotal = CreditNote::where('invoice_no', $purchase->purchase_no)->sum('amount') ?? 0;

        // Get debit notes for this purchase
        $debitNotesTotal = PurchaseDebitNote::where('invoice_no', $purchase->purchase_no)->sum('amount') ?? 0;

        // Calculate balance: Invoice + Debit Notes - Credit Notes - Paid
        $balance = ($purchase->grand_total ?? 0)
                 + $debitNotesTotal
                 - $creditNotesTotal
                 - ($purchase->paid_amount ?? 0);

        return max(0, $balance);
    }

    /**
     * Get aging report
     */
    public function agingReport(Request $request): JsonResponse
    {
        $asOfDate = $request->filled('as_of_date') ? $request->as_of_date : date('Y-m-d');
        $partyId = $request->filled('party_id') ? $request->party_id : null;

        $query = Purchase::with('party')
            ->where('purchase_date', '<=', $asOfDate);

        if ($partyId) {
            $query->where('vendor_id', $partyId);
        }

        $purchases = $query->get();

        $report = [];
        foreach ($purchases as $purchase) {
            // Calculate actual balance including credit/debit notes
            $actualBalance = $this->calculatePurchaseBalance($purchase);

            // Skip if no balance due
            if ($actualBalance <= 0) {
                continue;
            }

            $days = Carbon::parse($purchase->purchase_date)->diffInDays($asOfDate, false);
            $party = $purchase->party ? $purchase->party->company : 'Unknown';

            if (!isset($report[$party])) {
                $report[$party] = [
                    'party' => $party,
                    'current' => 0,
                    '1_30' => 0,
                    '31_60' => 0,
                    '61_90' => 0,
                    'over_90' => 0,
                    'total_due' => 0,
                ];
            }

            if ($days <= 0) {
                $report[$party]['current'] += $actualBalance;
            } elseif ($days <= 30) {
                $report[$party]['1_30'] += $actualBalance;
            } elseif ($days <= 60) {
                $report[$party]['31_60'] += $actualBalance;
            } elseif ($days <= 90) {
                $report[$party]['61_90'] += $actualBalance;
            } else {
                $report[$party]['over_90'] += $actualBalance;
            }

            $report[$party]['total_due'] += $actualBalance;
        }

        return response()->json(['data' => array_values($report)]);
    }

    /**
     * Export aging report
     */
    public function exportAgingReport(Request $request)
    {
        $asOfDate = $request->filled('as_of_date') ? $request->as_of_date : date('Y-m-d');
        $partyId = $request->filled('party_id') ? $request->party_id : null;

        $query = Purchase::with('party')
            ->where('purchase_date', '<=', $asOfDate);

        if ($partyId) {
            $query->where('vendor_id', $partyId);
        }

        $purchases = $query->get();

        $report = [];
        foreach ($purchases as $purchase) {
            // Calculate actual balance including credit/debit notes
            $actualBalance = $this->calculatePurchaseBalance($purchase);

            // Skip if no balance due
            if ($actualBalance <= 0) {
                continue;
            }

            $days = Carbon::parse($purchase->purchase_date)->diffInDays($asOfDate, false);
            $party = $purchase->party ? $purchase->party->company : 'Unknown';

            if (!isset($report[$party])) {
                $report[$party] = [
                    'party' => $party,
                    'current' => 0,
                    '1_30' => 0,
                    '31_60' => 0,
                    '61_90' => 0,
                    'over_90' => 0,
                    'total_due' => 0,
                ];
            }

            if ($days <= 0) {
                $report[$party]['current'] += $actualBalance;
            } elseif ($days <= 30) {
                $report[$party]['1_30'] += $actualBalance;
            } elseif ($days <= 60) {
                $report[$party]['31_60'] += $actualBalance;
            } elseif ($days <= 90) {
                $report[$party]['61_90'] += $actualBalance;
            } else {
                $report[$party]['over_90'] += $actualBalance;
            }

            $report[$party]['total_due'] += $actualBalance;
        }

        $report = array_values($report);

        $spreadsheet = $this->exportService->createStyledSpreadsheet('Purchase Aging Report - As of ' . $asOfDate);
        $sheet = $spreadsheet->getActiveSheet();
        $headerRow = 4;

        $headers = ['Vendor', 'Current', '1-30 Days', '31-60 Days', '61-90 Days', '>90 Days', 'Total Due'];
        $colCount = count($headers);
        $sheet->fromArray($headers, null, 'A' . $headerRow);
        $this->exportService->styleHeaderRow($sheet, $headerRow, $colCount);

        $rowNum = $headerRow + 1;
        $totals = ['current' => 0, '1_30' => 0, '31_60' => 0, '61_90' => 0, 'over_90' => 0, 'total_due' => 0];

        foreach ($report as $row) {
            $sheet->fromArray([
                $row['party'],
                $row['current'],
                $row['1_30'],
                $row['31_60'],
                $row['61_90'],
                $row['over_90'],
                $row['total_due'],
            ], null, 'A' . $rowNum);

            $totals['current'] += $row['current'];
            $totals['1_30'] += $row['1_30'];
            $totals['31_60'] += $row['31_60'];
            $totals['61_90'] += $row['61_90'];
            $totals['over_90'] += $row['over_90'];
            $totals['total_due'] += $row['total_due'];

            $rowNum++;
        }

        $this->exportService->styleDataRows($sheet, $headerRow + 1, $rowNum - 1, $colCount);

        $sheet->fromArray([
            'TOTAL',
            $totals['current'],
            $totals['1_30'],
            $totals['31_60'],
            $totals['61_90'],
            $totals['over_90'],
            $totals['total_due'],
        ], null, 'A' . $rowNum);
        $this->exportService->styleTotalsRow($sheet, $rowNum, $colCount);

        $this->exportService->autoSizeColumns($sheet, $colCount);

        $filename = 'Purchase-Aging-Report-' . date('Ymd_His') . '.xlsx';
        $this->exportService->download($spreadsheet, $filename);
    }

    // ==================== GRN INTEGRATION ====================

    /**
     * Get pending GRNs
     */
    public function getPendingGRNs(): JsonResponse
    {
        $grns = Grn::orderBy('id', 'desc')->get(['id', 'grn_no', 'grn_date', 'company_name']);

        return response()->json($grns);
    }

    /**
     * Get GRN details
     */
    public function getGRNDetails(int $id): JsonResponse
    {
        $grn = Grn::with('products')->find($id);

        if (!$grn) {
            return response()->json(['success' => false, 'message' => 'GRN not found']);
        }

        $party = CustomerVendor::where('company', $grn->company_name)->first();

        $items = $grn->products->map(function ($product) {
            $stockItem = StockItem::where('item_name', $product->description)->first();

            return [
                'item_id' => $stockItem ? $stockItem->id : null,
                'description' => $product->description,
                'uom' => $product->unit,
                'quantity' => $product->quantity,
                'rate' => 0,
                'amount' => 0,
            ];
        });

        return response()->json([
            'success' => true,
            'grn' => $grn,
            'party_id' => $party ? $party->id : null,
            'items' => $items,
        ]);
    }

    /**
     * Export purchases to Excel
     */
    public function export(Request $request)
    {
        $query = Purchase::with(['party', 'items.item']);

        if ($request->date_range) {
            [$start, $end] = explode(' to ', $request->date_range);
            $query->whereBetween('invoice_date', [$start, $end]);
        }

        if ($request->party_id) {
            $query->where('party_id', $request->party_id);
        }

        if ($request->invoice_no) {
            $query->where('invoice_no', 'like', '%' . $request->invoice_no . '%');
        }

        if ($request->status) {
            $query->where('status', $request->status);
        }

        $purchases = $query->orderBy('invoice_date', 'desc')->get();

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->fromArray([
            ['Purchase Id', 'Purchase Date', 'Invoice No', 'Invoice Date', 'Party', 'Sub Total', 'Total Amount', 'Paid Amount', 'Balance', 'Status', 'Notes', 'Item Name', 'Quantity', 'UOM', 'Rate', 'Discount %'],
        ], null, 'A1');

        $row = 2;
        foreach ($purchases as $purchase) {
            $first = true;
            foreach ($purchase->items as $item) {
                $sheet->fromArray([
                    $first ? $purchase->purchase_id : '',
                    $first ? $purchase->purchase_date : '',
                    $first ? $purchase->invoice_no : '',
                    $first ? $purchase->invoice_date : '',
                    $first ? ($purchase->party ? $purchase->party->company : '') : '',
                    $first ? $purchase->sub_total : '',
                    $first ? $purchase->total_amount : '',
                    $first ? $purchase->paid_amount : '',
                    $first ? $purchase->balance_amount : '',
                    $first ? $purchase->status : '',
                    $first ? $purchase->notes : '',
                    $item->item ? $item->item->item_name : '',
                    $item->quantity,
                    $item->uom,
                    $item->rate,
                    $item->discount,
                ], null, 'A' . $row);
                $row++;
                $first = false;
            }

            if ($first) {
                $sheet->fromArray([
                    $purchase->purchase_id,
                    $purchase->purchase_date,
                    $purchase->invoice_no,
                    $purchase->invoice_date,
                    $purchase->party ? $purchase->party->company : '',
                    $purchase->sub_total,
                    $purchase->total_amount,
                    $purchase->paid_amount,
                    $purchase->balance_amount,
                    $purchase->status,
                    $purchase->notes,
                    '', '', '', '', '',
                ], null, 'A' . $row);
                $row++;
            }
        }

        $writer = new Xlsx($spreadsheet);
        $filename = 'purchase_export_' . date('Ymd_His') . '.xlsx';
        $temp_file = tempnam(sys_get_temp_dir(), $filename);
        $writer->save($temp_file);

        return response()->download($temp_file, $filename)->deleteFileAfterSend(true);
    }
}
