<?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;

class PurchaseManagementController extends Controller
{
    // AJAX listing with filters
    public function index(Request $request)
    {
        Log::info('PurchaseManagementController@index called', ['request' => $request->all()]);
        if ($request->ajax()) {
            $query = Purchase::with(['party','items']);

            // Filtering
            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);
            }

            $data = 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);

            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 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' => 'required|integer',
            'purchase_order_date' => 'required|date',
            'purchase_no' => 'required|string|exists:purchases,purchase_id',
            'vendor_email' => 'required|email',
            'invoice_no' => 'required|string|exists:purchases,invoice_no',
            'invoice_date' => 'required|date',
            'purchase_id' => 'required|integer|exists:customer_vendors,id', // likely party/vendor 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',
        ]);

        // Step 3: Update the purchase record
        $purchase->update([
            'purchase_id' => $validatedData['purchase_no'], // assuming purchase_no is stored as purchase_id
            'purchase_date' => $validatedData['purchase_order_date'],
            'invoice_no' => $validatedData['invoice_no'],
            'invoice_date' => $validatedData['invoice_date'],
            'party_id' => $validatedData['purchase_id'], // vendor/party ID
            'sub_total' => $validatedData['sub_total'],
            'additional_charges' => $validatedData['additional_charges'],
            'tds' => $validatedData['total_tds'],
            'total_amount' => $validatedData['total_amount'],
            'paid_amount' => $validatedData['paid_amount'],
            'balance_amount' => $validatedData['balance_amount'],
            'status' => $validatedData['paid_amount'] >= $validatedData['total_amount'] ? 'paid' : 'unpaid',
            'payment_mode' => $validatedData['payment_mode'],
            'reference' => $validatedData['payment_reference'],
            'notes' => $validatedData['note'],
        ]);

        // 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) {
            $itemData = [
                'item_id' => $item['item_id'],
                'uom' => $item['uom'],
                'quantity' => $item['quantity'],
                'rate' => $item['rate'],
                'discount' => $item['discount'] ?? 0,
            ];

            if (isset($item['id'])) {
                // Existing item: update
                $purchase->items()->where('id', $item['id'])->update($itemData);
            } else {
                // New item: create
                 $purchase->items()->create($itemData);
            }
        }

        // 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
        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' => 'required|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',
            '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' => 'required|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',
        ]);

        // Step 2: Create the purchase record
        $purchase = Purchase::create([
            'purchase_id' => $validatedData['purchase_no'], // Use purchase_no as purchase_id
            'purchase_date' => $validatedData['purchase_order_date'],
            'invoice_no' => $validatedData['invoice_no'],
            'invoice_date' => $validatedData['invoice_date'],
            'party_id' => $validatedData['purchase_id'], // Assuming purchase_id refers to party_id
            'sub_total' => $validatedData['sub_total'],
            'additional_charges' => $validatedData['additional_charges'],
            'tds' => $validatedData['total_tds'],
            'total_amount' => $validatedData['total_amount'],
            'paid_amount' => $validatedData['paid_amount'],
            'balance_amount' => $validatedData['balance_amount'],
            'status' => 'unpaid', // Default status
            'payment_mode' => $validatedData['payment_mode'],
            'reference' => $validatedData['payment_reference'],
            'notes' => $validatedData['note'],
        ]);

        // Step 3: Create purchase items records
        foreach ($validatedData['items'] as $item) {
            PurchaseItem::create([
                'purchase_id' => $purchase->id,
                'item_id' => $item['item_id'],
                'uom' => $item['uom'],
                'quantity' => $item['quantity'],
                'rate' => $item['rate'],
                'discount' => $item['discount'],
            ]);
        }

        // 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
        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', ['request' => $request->all()]);
        // 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->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();

        // 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;
            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 no items, still show the purchase
            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++;
            }
        }

        // 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', ['request' => $request->all()]);
        $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
        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->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)
            ->addColumn('purchase_date', fn($row) => $row->purchase_date)
            ->addColumn('invoice_no', fn($row) => $row->invoice_no)
            ->addColumn('invoice_date', fn($row) => $row->invoice_date)
            ->addColumn('paid_amount', fn($row) => $row->paid_amount)
            ->addColumn('payment_mode', fn($row) => $row->payment_mode)
            ->addColumn('reference', fn($row) => $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" data-id="' . $row->id . '"><i class="fas fa-eye"></i></button>';
        })->rawColumns(['actions'])
            ->make(true);
    }
    
    public function showCreditNote($id)
    {
        $creditNote = \App\Models\CreditNote::with('party')->findOrFail($id);
        return response()->json($creditNote);
    }
    
    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
        $query = \App\Models\Purchase::with('party')
            ->where('invoice_date', '<=', $asOfDate)
            ->where('balance_amount', '>', 0);

        if ($partyId) {
            $query->where('party_id', $partyId);
        }

        $purchases = $query->get();

        // Group by party
        $report = [];
        foreach ($purchases as $purchase) {
            $days = \Carbon\Carbon::parse($purchase->invoice_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 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']);
    }
}