<?php

namespace App\Http\Controllers\SuperAdmin;

use App\Http\Controllers\Controller;
use App\Models\CreditNote;
use App\Models\CustomerVendor;
use App\Models\Purchase;
use App\Models\PurchaseItem;
use App\Models\PurchaseFile;
use App\Models\PurchaseOrder;
use App\Models\StockItem;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Illuminate\Support\Facades\Response;
use Illuminate\Support\Facades\Storage;
use App\Services\ExportService;

class PurchaseManagementController extends Controller
{
    protected $exportService;

    public function __construct(ExportService $exportService)
    {
        $this->exportService = $exportService;
    }
    // AJAX listing with filters
    public function index(Request $request)
    {
        Log::info('PurchaseManagementController@index called');
        if ($request->ajax()) {
            $query = Purchase::with(['party','items']);

            // Filtering - check both old and new column names for compatibility
            if ($request->date_range) {
                [$start, $end] = explode(' to ', $request->date_range);
                $query->where(function($q) use ($start, $end) {
                    $q->whereBetween('invoice_date', [$start, $end])
                      ->orWhereBetween('purchase_date', [$start, $end]);
                });
            }

            if ($request->party_id) {
                $query->where(function($q) use ($request) {
                    $q->where('party_id', $request->party_id)
                      ->orWhere('vendor_id', $request->party_id);
                });
            }
            if ($request->invoice_no) {
                $query->where(function($q) use ($request) {
                    $q->where('invoice_no', 'like', '%' . $request->invoice_no . '%')
                      ->orWhere('purchase_no', 'like', '%' . $request->invoice_no . '%');
                });
            }
            if ($request->status) {
                $query->where(function($q) use ($request) {
                    $q->where('status', $request->status)
                      ->orWhere('payment_status', $request->status);
                });
            }

            $data = datatables()->of($query->latest())
                ->addColumn('party', fn($row) => $row->party->company ?? '-')
                ->addColumn('items', fn($row) => $row->items()->count())
                ->addColumn('actions', function ($row) {
                    $partyCompany = htmlspecialchars($row->party->company ?? '');
                    $partyId = $row->vendor_id ?? $row->party_id;
                    $invoiceNo = $row->invoice_no ?? $row->purchase_no;
                    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="' . $invoiceNo . '"
                            data-party="' . $partyCompany . '"
                            data-party_id="' . $partyId . '">
                            <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);

            Log::info('PurchaseManagementController@index returning AJAX data');
            return $data;
        }

        $parties = CustomerVendor::all();
        $items = StockItem::all();
        Log::info('PurchaseManagementController@index returning view', [
            'parties_count' => $parties->count(),
            'items_count' => $items->count()
        ]);
        $purchaseOrders = PurchaseOrder::with(['quotation','quotation.items'])->where('status', 'approved')->get();
        // dd($purchaseOrders);
        $nextInvoiceNo = nextNumber('UEPL/P/INV', 'purchases', 'invoice_no');
        $currentDate = date('Y-m-d');
        return view('superadmin.purchasemanagement.index', compact('parties', 'items', 'purchaseOrders', 'nextInvoiceNo', 'currentDate'));
    }

    public function accounts(Request $request)
    {
        $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 view('superadmin.purchasemanagement.accounts', compact('parties', 'items', 'purchaseOrders', 'nextInvoiceNo', 'currentDate'));
    }

    public function show($id)
    {
        Log::info('PurchaseManagementController@show called', ['id' => $id]);
        $purchase = Purchase::with(['party', 'items.item', 'files'])->findOrFail($id);
        Log::info('PurchaseManagementController@show loaded purchase', ['purchase_id' => $purchase->id]);
        return response()->json($purchase);
    }

    public function edit($id)
    {
        Log::info('PurchaseManagementController@edit called', ['id' => $id]);
        $purchase = Purchase::with(['items', 'files'])->findOrFail($id);
        Log::info('PurchaseManagementController@edit loaded purchase', ['purchase_id' => $purchase->id]);
        return response()->json($purchase);
    }

    // Update purchase
    public function update(Request $request, $id)
    {
        // Step 1: Find the existing purchase
        $purchase = Purchase::findOrFail($id);

        // Step 2: Validate the incoming request
        $validatedData = $request->validate([
            'purchase_order_id' => 'nullable|integer',
            'purchase_order_date' => 'nullable|date',
            'purchase_no' => 'required|string', // Vendor Invoice No
            'vendor_email' => 'nullable|email',
            'invoice_no' => 'required|string', // Purchase ID (auto-generated)
            'invoice_date' => 'required|date',
            'purchase_id' => 'required|integer|exists:customer_vendors,id', // Party/Vendor ID
            'vendor_reference_no' => 'nullable|string',
            'payment_terms' => 'nullable|integer',
            'company_gstn' => 'nullable|string',
            'company_address' => 'nullable|string',
            'company_phone' => 'nullable|string',
            'description' => 'nullable|string',
            'delivery_terms' => 'nullable|string',
            'items' => 'required|array|min:1',
            'items.*.sl_no' => 'nullable|integer',
            'items.*.item_id' => 'required|integer',
            'items.*.description' => 'nullable|string',
            'items.*.quantity' => 'required|numeric|min:0.01',
            'items.*.uom' => 'nullable|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',
        ]);

        // Step 3: Update the purchase record
        // Note: Form field names must be mapped to model's $fillable field names
        $paymentStatus = ($validatedData['paid_amount'] ?? 0) >= $validatedData['total_amount'] ? 'paid' : 'unpaid';
        $purchaseOrderNo = $request->purchase_order_id ? \App\Models\PurchaseOrder::find($request->purchase_order_id)?->purchase_order_no : $purchase->purchase_order_no;

        $purchase->update([
            'purchase_date' => $validatedData['invoice_date'],
            'purchase_order_no' => $purchaseOrderNo,
            'purchase_order_date' => $validatedData['purchase_order_date'],
            'vendor_id' => $validatedData['purchase_id'], // Party dropdown maps to vendor_id
            'vendor_ref_no' => $validatedData['vendor_reference_no'] ?? null,
            'vendor_invoice_no' => $validatedData['purchase_no'], // Vendor Invoice No from form
            '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' => $paymentStatus,
            '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,
        ]);

        // Also update original migration columns for backward compatibility with DataTable listing
        DB::table('purchases')->where('id', $purchase->id)->update([
            'invoice_date' => $validatedData['invoice_date'],
            'party_id' => $validatedData['purchase_id'],
            'sub_total' => $validatedData['sub_total'],
            'total_amount' => $validatedData['total_amount'],
            'status' => $paymentStatus,
            'notes' => $validatedData['note'] ?? null,
        ]);

        // Step 4: Handle items - Sync logic (update existing, add new, remove deleted)
        $itemIdsFromRequest = collect($validatedData['items'])->map(function ($item) use ($purchase) {
            return isset($item['id']) ? $item['id'] : null; // if item already exists in DB, it should have an 'id'
        })->filter(); // only non-null IDs

        // Delete items not present in the request
         $purchase->items()
            ->whereNotIn('id', $itemIdsFromRequest)
            ->delete();

        // Update or create items
        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,
            ];

            // Original migration columns for backward compatibility
            $legacyData = [
                'item_id' => $item['item_id'],
                'rate' => $item['rate'],
            ];

            if (isset($item['id'])) {
                // Existing item: update
                $purchase->items()->where('id', $item['id'])->update(array_merge($itemData, $legacyData));
            } else {
                // New item: create
                $newItem = $purchase->items()->create($itemData);
                DB::table('purchase_items')->where('id', $newItem->id)->update($legacyData);
            }
        }

        // Step 5: Handle file uploads (if any)
        if ($request->hasFile('purchase_files')) {
            // Example: Add new files, or replace old ones
            // You may want to store them and attach to purchase
            // Storage::put('purchases/'.$purchase->id, $request->file('purchase_files'));
        }

        // Step 6: Return success response
        if ($request->ajax() || $request->wantsJson()) {
            return response()->json(['success' => true, 'message' => 'Purchase updated successfully', 'id' => $purchase->id]);
        }
        return redirect()->route('superadmin.purchase.index')->with('success', 'Purchase updated successfully');
    }


    public function store(Request $request)
    {
        // Step 1: Validate the request data
        $validatedData = $request->validate([
            'purchase_order_id' => 'nullable|integer',
            'purchase_order_date' => 'nullable|date',
            'purchase_no' => 'required|string', // Vendor Invoice No
            'vendor_email' => 'nullable|email',
            'invoice_no' => 'required|string', // Purchase ID (auto-generated)
            'invoice_date' => 'required|date',
            'purchase_id' => 'required|integer|exists:customer_vendors,id', // Party/Vendor ID
            'vendor_reference_no' => 'nullable|string',
            'payment_terms' => 'nullable|integer',
            'company_gstn' => 'nullable|string',
            'company_address' => 'nullable|string',
            'company_phone' => 'nullable|string',
            'description' => 'nullable|string',
            'delivery_terms' => 'nullable|string',
            'items' => 'required|array',
            'items.*.sl_no' => 'nullable|integer',
            'items.*.item_id' => 'required|integer',
            'items.*.description' => 'nullable|string',
            'items.*.quantity' => 'required|numeric',
            'items.*.uom' => 'nullable|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',
        ]);

        // Step 2: Create the purchase record
        // Note: Form field names must be mapped to model's $fillable field names
        // We write to BOTH the original migration columns AND the new model columns
        // to ensure DataTable listing (which reads original columns) works correctly.
        $purchaseOrderNo = $request->purchase_order_id ? \App\Models\PurchaseOrder::find($request->purchase_order_id)?->purchase_order_no : null;
        $purchase = Purchase::create([
            // New model columns
            'purchase_no' => $validatedData['invoice_no'], // Purchase ID from form (UEPL/P/INV/XXXX)
            'purchase_date' => $validatedData['invoice_date'],
            'purchase_order_no' => $purchaseOrderNo,
            'purchase_order_date' => $validatedData['purchase_order_date'] ?? null,
            'vendor_id' => $validatedData['purchase_id'], // Party dropdown maps to vendor_id
            'vendor_ref_no' => $validatedData['vendor_reference_no'] ?? null,
            'vendor_invoice_no' => $validatedData['purchase_no'], // Vendor Invoice No from form
            'company_phone' => $validatedData['company_phone'],
            'company_email' => $validatedData['vendor_email'] ?? null,
            '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', // Default status
            '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,
        ]);

        // Also populate original migration columns for backward compatibility with DataTable listing
        DB::table('purchases')->where('id', $purchase->id)->update([
            'purchase_id' => $validatedData['invoice_no'],
            'invoice_no' => $validatedData['invoice_no'],
            'invoice_date' => $validatedData['invoice_date'],
            'party_id' => $validatedData['purchase_id'],
            'sub_total' => $validatedData['sub_total'],
            'total_amount' => $validatedData['total_amount'],
            'status' => 'unpaid',
            'notes' => $validatedData['note'] ?? null,
        ]);

        // Step 3: Create purchase items records
        foreach ($validatedData['items'] as $item) {
            $stockItem = StockItem::find($item['item_id']);
            $purchaseItem = 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,
            ]);
            // Also populate original migration columns for backward compatibility
            DB::table('purchase_items')->where('id', $purchaseItem->id)->update([
                'item_id' => $item['item_id'],
                'rate' => $item['rate'],
            ]);
        }

        // Step 4: Handle files (if any)
        if ($request->hasFile('purchase_files')) {
            // Logic to handle file uploads
            // Example: Store files in storage and save paths in database
        }

        // Step 5: Return a response
        if ($request->ajax() || $request->wantsJson()) {
            return response()->json(['success' => true, 'message' => 'Purchase created successfully', 'id' => $purchase->id]);
        }
        return redirect()->route('superadmin.purchase.index')->with('success', 'Purchase created successfully');
    }

    public function getNextInvoiceNumber()
    {
        Log::info('PurchaseManagementController@getNextInvoiceNumber called');
        $latest = Purchase::orderBy('id', 'desc')->first();
        if (!$latest || !$latest->invoice_no) {
            $nextNumber = 1;
        } else {
            // Extract the last 4 digits and increment
            $lastNumber = (int)substr($latest->invoice_no, -4);
            $nextNumber = $lastNumber + 1;
        }
        $nextInvoiceNo = 'UEPL/P/INV/' . str_pad($nextNumber, 4, '0', STR_PAD_LEFT);
        Log::info('PurchaseManagementController@getNextInvoiceNumber finished', ['invoice_no' => $nextInvoiceNo]);
        return response()->json(['invoice_no' => $nextInvoiceNo]);
    }
    
    public function export(Request $request)
    {

        Log::info('PurchaseManagementController@export called');
        // Same filter logic as your index()
        $query = \App\Models\Purchase::with(['party', 'items.item']);

        if ($request->date_range) {
            [$start, $end] = explode(' to ', $request->date_range);
            $query->where(function($q) use ($start, $end) {
                $q->whereBetween('invoice_date', [$start, $end])
                  ->orWhereBetween('purchase_date', [$start, $end]);
            });
        }
        if ($request->party_id) {
            $query->where(function($q) use ($request) {
                $q->where('party_id', $request->party_id)
                  ->orWhere('vendor_id', $request->party_id);
            });
        }
        if ($request->invoice_no) {
            $query->where(function($q) use ($request) {
                $q->where('invoice_no', 'like', '%' . $request->invoice_no . '%')
                  ->orWhere('purchase_no', 'like', '%' . $request->invoice_no . '%');
            });
        }
        if ($request->status) {
            $query->where(function($q) use ($request) {
                $q->where('status', $request->status)
                  ->orWhere('payment_status', $request->status);
            });
        }

        $purchases = $query->orderByRaw('COALESCE(invoice_date, purchase_date) DESC')->get();

        // Create Spreadsheet
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        // Set headers
        $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;
            $purchaseIdVal = $purchase->purchase_id ?? $purchase->purchase_no;
            $invoiceNoVal = $purchase->invoice_no ?? $purchase->purchase_no;
            $invoiceDateVal = $purchase->invoice_date ?? $purchase->purchase_date;
            $subTotalVal = $purchase->sub_total ?: $purchase->subtotal;
            $totalAmountVal = $purchase->total_amount ?: $purchase->grand_total;
            $statusVal = $purchase->status ?? $purchase->payment_status;
            $notesVal = $purchase->notes ?? $purchase->note;

            foreach ($purchase->items as $item) {
                $sheet->fromArray([
                    $first ? $purchaseIdVal : '',
                    $first ? $purchase->purchase_date : '',
                    $first ? $invoiceNoVal : '',
                    $first ? $invoiceDateVal : '',
                    $first ? ($purchase->party ? $purchase->party->company : '') : '',
                    $first ? $subTotalVal : '',
                    $first ? $totalAmountVal : '',
                    $first ? $purchase->paid_amount : '',
                    $first ? $purchase->balance_amount : '',
                    $first ? $statusVal : '',
                    $first ? $notesVal : '',
                    $item->item ? $item->item->item_name : '',
                    $item->quantity,
                    $item->uom,
                    $item->rate ?? $item->unit_rate,
                    $item->discount
                ], NULL, 'A' . $row);
                $row++;
                $first = false;
            }
            // If no items, still show the purchase
            if ($first) {
                $sheet->fromArray([
                    $purchaseIdVal,
                    $purchase->purchase_date,
                    $invoiceNoVal,
                    $invoiceDateVal,
                    $purchase->party ? $purchase->party->company : '',
                    $subTotalVal,
                    $totalAmountVal,
                    $purchase->paid_amount,
                    $purchase->balance_amount,
                    $statusVal,
                    $notesVal,
                    '',
                    '',
                    '',
                    '',
                    ''
                ], NULL, 'A' . $row);
                $row++;
            }
        }

        // Download response
        $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);
    }
    
    public function getNextCreditNoteNo()
    {
        $year = date('Y');
        $latest = \App\Models\CreditNote::where('credit_note_no', 'like', "CN-{$year}-%")
            ->orderByDesc('id')->first();

        if (!$latest) {
            $nextNumber = 1;
        } else {
            $lastNumber = (int)substr($latest->credit_note_no, -4);
            $nextNumber = $lastNumber + 1;
        }

        $nextCreditNo = 'CN-' . $year . '-' . str_pad($nextNumber, 4, '0', STR_PAD_LEFT);

        return response()->json(['credit_note_no' => $nextCreditNo]);
    }
    
    public function creditNoteStore(Request $request)
    {
        Log::info('PurchaseManagementController@creditNoteStore called');
        $data = $request->validate([
            'credit_note_no' => 'required|unique:credit_notes,credit_note_no',
            '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 = \App\Models\CreditNote::create($data);

        return response()->json(['success' => true, 'credit_note_id' => $creditNote->id]);
    }
    
    public function payments(Request $request)
    {
        $query = Purchase::with('party');

        // Filters - check both old and new column names
        if ($request->date_range) {
            [$start, $end] = explode(' to ', $request->date_range);
            $query->where(function($q) use ($start, $end) {
                $q->whereBetween('invoice_date', [$start, $end])
                  ->orWhereBetween('purchase_date', [$start, $end]);
            });
        }
        if ($request->party_id) {
            $query->where(function($q) use ($request) {
                $q->where('party_id', $request->party_id)
                  ->orWhere('vendor_id', $request->party_id);
            });
        }
        if ($request->min_amount) {
            $query->where('paid_amount', '>=', $request->min_amount);
        }
        if ($request->max_amount) {
            $query->where('paid_amount', '<=', $request->max_amount);
        }

        return datatables()->of($query->latest())
            ->addColumn('party', fn($row) => $row->party ? $row->party->company : '')
            ->addColumn('purchase_id', fn($row) => $row->purchase_id ?? $row->purchase_no)
            ->addColumn('purchase_date', fn($row) => $row->purchase_date)
            ->addColumn('invoice_no', fn($row) => $row->invoice_no ?? $row->purchase_no)
            ->addColumn('invoice_date', fn($row) => $row->invoice_date ?? $row->purchase_date)
            ->addColumn('paid_amount', fn($row) => $row->paid_amount)
            ->addColumn('payment_mode', fn($row) => $row->payment_method ?? $row->payment_mode)
            ->addColumn('reference', fn($row) => $row->payment_reference_no ?? $row->reference)
            ->addColumn('actions', function ($row) {
                return '<button class="btn btn-sm btn-outline-primary view-payment" data-id="' . $row->id . '"><i class="fas fa-eye"></i></button>';
            })
            ->rawColumns(['actions'])
            ->make(true);
    }
    
    public function Vendorshow($id)
    {
        $purchase = Purchase::with(['party', 'items.item'])->findOrFail($id);
        return response()->json($purchase);
    }
    
    public function creditNotesData(Request $request)
    {
        $query = CreditNote::with('party');

        // Filters (optional)
        if ($request->date_range) {
            [$start, $end] = explode(' to ', $request->date_range);
            $query->whereBetween('date', [$start, $end]);
        }
        
        //dd($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}%");
        }

        // DataTables response
        return datatables()->eloquent($query)->addColumn('party', function ($row) {
            return $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);
    }
    
    public function showCreditNote($id)
    {
        $creditNote = \App\Models\CreditNote::with('party')->findOrFail($id);
        return response()->json($creditNote);
    }

    /**
     * Get credit note for editing (with formatted date)
     */
    public function getCreditNote($id)
    {
        $creditNote = \App\Models\CreditNote::with('party')->findOrFail($id);
        $data = $creditNote->toArray();
        // Format date for HTML date input (expects YYYY-MM-DD)
        $data['date'] = $creditNote->date ? \Carbon\Carbon::parse($creditNote->date)->format('Y-m-d') : null;
        return response()->json(['success' => true, 'data' => $data]);
    }

    /**
     * Update credit note
     */
    public function creditNoteUpdate(Request $request, $id)
    {
        Log::info('PurchaseManagementController@creditNoteUpdate called', ['id' => $id]);

        $creditNote = \App\Models\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($id)
    {
        $creditNote = \App\Models\CreditNote::findOrFail($id);
        $creditNote->delete();
        return response()->json(['success' => true, 'message' => 'Credit Note deleted successfully']);
    }
    
    public function agingReport(Request $request)
    {
        // Validate and get filters
        $asOfDate = $request->filled('as_of_date') ? $request->as_of_date : date('Y-m-d');
        $partyId = $request->filled('party_id') ? $request->party_id : null;

        // Query purchases with balance > 0
        // Note: Purchase model uses 'purchase_date' not 'invoice_date', and 'vendor_id' not 'party_id'
        $query = \App\Models\Purchase::with('party')
            ->where('purchase_date', '<=', $asOfDate)
            ->where('balance_amount', '>', 0);

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

        $purchases = $query->get();

        // Group by party
        $report = [];
        foreach ($purchases as $purchase) {
            $days = \Carbon\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'] += $purchase->balance_amount;
            } elseif ($days <= 30) {
                $report[$party]['1_30'] += $purchase->balance_amount;
            } elseif ($days <= 60) {
                $report[$party]['31_60'] += $purchase->balance_amount;
            } elseif ($days <= 90) {
                $report[$party]['61_90'] += $purchase->balance_amount;
            } else {
                $report[$party]['over_90'] += $purchase->balance_amount;
            }

            $report[$party]['total_due'] += $purchase->balance_amount;
        }

        // Convert to datatable-friendly array
        $report = array_values($report);

        return response()->json([
            'data' => $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;

        // Note: Purchase model uses 'purchase_date' not 'invoice_date', and 'vendor_id' not 'party_id'
        $query = \App\Models\Purchase::with('party')
            ->where('purchase_date', '<=', $asOfDate)
            ->where('balance_amount', '>', 0);

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

        $purchases = $query->get();

        $report = [];
        foreach ($purchases as $purchase) {
            $days = \Carbon\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'] += $purchase->balance_amount;
            } elseif ($days <= 30) {
                $report[$party]['1_30'] += $purchase->balance_amount;
            } elseif ($days <= 60) {
                $report[$party]['31_60'] += $purchase->balance_amount;
            } elseif ($days <= 90) {
                $report[$party]['61_90'] += $purchase->balance_amount;
            } else {
                $report[$party]['over_90'] += $purchase->balance_amount;
            }

            $report[$party]['total_due'] += $purchase->balance_amount;
        }

        $report = array_values($report);

        // Generate Styled Excel
        $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);
        
        // Add totals row
        $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);
    }
    
    public function destroy_old(Purchase $purchase)
    {
        $purchase->delete();
    
        return response()->json(['success' => true, 'message' => 'Purchase deleted.']);
    }
    
    public function destroy(Purchase $purchase)
    {
        // Delete related items
        $purchase->items()->delete();
    
        // Delete related files from storage and DB
        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();
        }
    
        // Delete the purchase record
        $purchase->delete();
    
        return response()->json(['success' => true, 'message' => 'Purchase and related data deleted.']);
    }
    
    public function deleteFile(PurchaseFile $file)
    {
        if ($file->file_path && Storage::disk('public')->exists($file->file_path)) {
            Storage::disk('public')->delete($file->file_path);
        }
    
        $file->delete();
    
        return response()->json(['success' => true, 'message' => 'File deleted successfully']);
    }

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

    /**
     * Get list of GRNs that have not been fully invoiced yet.
     */
    public function getPendingGRNs(Request $request)
    {
        // For now, we'll return all GRNs. 
        // In a real scenario, we might want to filter out those already linked to a Purchase Invoice.
        // But since multiple invoices might be possible or partial invoicing, strictly filtering might be tricky without a 'status' flag on GRN.
        // We will return all, or those without a 'is_invoiced' flag if we had one.
        
        $grns = \App\Models\Grn::orderBy('id', 'desc')->get(['id', 'grn_no', 'grn_date', 'company_name']);
        
        return response()->json($grns);
    }

    /**
     * Get details of a specific GRN including items.
     */
    public function getGRNDetails($id)
    {
        $grn = \App\Models\Grn::with('products')->find($id);
        
        if (!$grn) {
            return response()->json(['success' => false, 'message' => 'GRN not found']);
        }
        
        // Find Party ID based on company name (best effort)
        $party = CustomerVendor::where('company', $grn->company_name)->first();

        // Transform products to match PurchaseItem structure
        $items = $grn->products->map(function($product) {
            // Try to match stock item by name or code if possible
             $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, // Rate needs to be filled by user or fetched from PO
                 'amount' => 0
             ];
        });

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

    public function getDebitNotes(Request $request)
    {
        $query = \App\Models\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
        ]);
    }

    public function getNextDebitNoteNumber()
    {
        $latest = \App\Models\PurchaseDebitNote::latest()->first();
        if (!$latest) {
            return response()->json(['next_number' => 'DN-0001']);
        }

        // Assuming format DN-XXXX
        $parts = explode('-', $latest->debit_note_no);
        if(count($parts) > 1) {
            $lastNumber = intval($parts[1]);
        } else {
             $lastNumber = 0;
        }
        
        $nextNumber = 'DN-' . str_pad($lastNumber + 1, 4, '0', STR_PAD_LEFT);

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

    public function storeDebitNote(Request $request)
    {
        $validated = $request->validate([
            'party_id' => 'required|exists:customer_vendors,id',
            'date' => 'required|date',
            'amount' => 'required|numeric|min:0',
            'invoice_no' => 'required|string',
            'reason' => 'required|string',
        ]);

        // Generate next number if not provided (though frontend usually fetches it)
        // But for safety let's use what frontend sends or generate
        // Ideally should trust backend generation to avoid race conditions but for now simple
        $debitNoteNo = $request->debit_note_no;
        if(empty($debitNoteNo)){
             $next = $this->getNextDebitNoteNumber()->getData();
             $debitNoteNo = $next->next_number;
        }

        \App\Models\PurchaseDebitNote::create([
            'debit_note_no' => $debitNoteNo,
            'date' => $request->date,
            'invoice_no' => $request->invoice_no,
            'party_id' => $request->party_id,
            'amount' => $request->amount,
            'reason' => $request->reason,
            'description' => $request->description,
        ]);

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

    public function updateDebitNote(Request $request, $id)
    {
        $debitNote = \App\Models\PurchaseDebitNote::findOrFail($id);
        
        $validated = $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']);
    }

    public function deleteDebitNote($id)
    {
        $debitNote = \App\Models\PurchaseDebitNote::findOrFail($id);
        $debitNote->delete();
        return response()->json(['success' => true, 'message' => 'Debit Note deleted successfully']);
    }

    public function getDebitNote($id)
    {
        $debitNote = \App\Models\PurchaseDebitNote::with('party')->findOrFail($id);
        return response()->json($debitNote);
    }
}
