<?php

namespace App\Http\Controllers\Manager;

use App\Http\Controllers\Controller;
use App\Models\CreditNote;
use App\Models\CustomerVendor;
use App\Models\Purchase;
use App\Models\PurchaseItem;
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;

class ManagerPurchaseManagementController extends Controller
{
    // AJAX listing with filters
    public function index(Request $request)
    {
        Log::info('PurchaseManagementController@index called');
        if ($request->ajax()) {
            $query = Purchase::with('party');

            // Filtering
            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->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 . '">
                            <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>
                    ';
                })
                ->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()
        ]);
        return view('manager.purchasemanagement.index', compact('parties', 'items'));
    }

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

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

        $data = $request->validate([
            'invoice_no' => 'required',
            'date' => 'required|date',
            'party_id' => 'required|exists:customer_vendors,id',
            'items' => 'required|array|min:1',
            'items.*.item_id' => 'required|exists:stock_items,id',
            'items.*.quantity' => 'required|integer|min:1',
            'items.*.uom' => 'required',
            'items.*.rate' => 'required|numeric|min:0',
            'items.*.discount' => 'nullable|numeric|min:0|max:100',
            'additional_charges' => 'nullable|numeric|min:0',
            'tds' => 'nullable|numeric|min:0|max:100',
            'paid_amount' => 'nullable|numeric|min:0',
            'payment_mode' => 'nullable|string',
            'payment_reference' => 'nullable|string',
            'payment_reason' => 'nullable|string',
            'notes' => 'nullable|string',
        ]);

        DB::transaction(function () use ($data, $id) {
            $purchase = Purchase::findOrFail($id);

            $subTotal = 0;
            foreach ($data['items'] as $item) {
                $amount = $item['quantity'] * $item['rate'];
                $discount = ($amount * ($item['discount'] ?? 0)) / 100;
                $subTotal += ($amount - $discount);
            }
            $additionalCharges = $data['additional_charges'] ?? 0;
            $tds = $data['tds'] ?? 0;
            $totalBeforeTDS = $subTotal + $additionalCharges;
            $tdsAmount = $totalBeforeTDS * ($tds / 100);
            $totalAmount = $totalBeforeTDS - $tdsAmount;
            $paidAmount = $data['paid_amount'] ?? 0;
            $balanceAmount = $totalAmount - $paidAmount;
            $status = $paidAmount >= $totalAmount ? 'paid' : ($paidAmount > 0 ? 'partial' : 'unpaid');

            // Update main purchase fields
            $purchase->update([
                'invoice_no'         => $data['invoice_no'],
                'date'               => $data['date'],
                'party_id'           => $data['party_id'],
                'sub_total'          => $subTotal,
                'additional_charges' => $additionalCharges,
                'tds'                => $tds,
                'total_amount'       => $totalAmount,
                'paid_amount'        => $paidAmount,
                'balance_amount'     => $balanceAmount,
                'status'             => $status,
                'notes'              => $data['notes'] ?? '',
                'remarks'            => $data['payment_reason'] ?? '',
                'reference'          => $data['payment_reference'] ?? '',
                'payment_mode'       => $data['payment_mode'] ?? '',
            ]);

            // Delete old items, re-insert (optional: use smarter sync)
            $purchase->items()->delete();
            foreach ($data['items'] as $item) {
                $purchase->items()->create([
                    'item_id'  => $item['item_id'],
                    'uom'      => $item['uom'],
                    'quantity' => $item['quantity'],
                    'rate'     => $item['rate'],
                    'discount' => $item['discount'] ?? 0,
                ]);
            }
        });

        Log::info('PurchaseManagementController@update finished', ['id' => $id]);
        return response()->json(['success' => true]);
    }


    public function store(Request $request)
    {
        Log::info('PurchaseManagementController@store called');
        $data = $request->validate([
            'invoice_no' => 'required|unique:purchases,invoice_no',
            'date' => 'required|date',
            'party_id' => 'required|exists:customer_vendors,id',
            'items' => 'required|array|min:1',
            'items.*.item_id' => 'required|exists:stock_items,id',
            'items.*.quantity' => 'required|integer|min:1',
            'items.*.uom' => 'required',
            'items.*.rate' => 'required|numeric|min:0',
            'items.*.discount' => 'nullable|numeric|min:0|max:100',
            'additional_charges' => 'nullable|numeric|min:0',
            'tds' => 'nullable|numeric|min:0|max:100',
            'paid_amount' => 'nullable|numeric|min:0',
            'payment_mode' => 'nullable|string',
            'payment_reference' => 'nullable|string',
            'payment_reason' => 'nullable|string',
            'notes' => 'nullable|string',
        ]);

        DB::transaction(function () use ($data) {
            $subTotal = 0;
            foreach ($data['items'] as $item) {
                $amount = $item['quantity'] * $item['rate'];
                $discount = ($amount * ($item['discount'] ?? 0)) / 100;
                $subTotal += ($amount - $discount);
            }
            $additionalCharges = $data['additional_charges'] ?? 0;
            $tds = $data['tds'] ?? 0;
            $totalBeforeTDS = $subTotal + $additionalCharges;
            $tdsAmount = $totalBeforeTDS * ($tds / 100);
            $totalAmount = $totalBeforeTDS - $tdsAmount;
            $paidAmount = $data['paid_amount'] ?? 0;
            $balanceAmount = $totalAmount - $paidAmount;

            $status = $paidAmount >= $totalAmount ? 'paid' : ($paidAmount > 0 ? 'partial' : 'unpaid');

            $purchase = Purchase::create([
                'invoice_no'         => $data['invoice_no'],
                'date'               => $data['date'],
                'party_id'           => $data['party_id'],
                'sub_total'          => $subTotal,
                'additional_charges' => $additionalCharges,
                'tds'                => $tds,
                'total_amount'       => $totalAmount,
                'paid_amount'        => $paidAmount,
                'balance_amount'     => $balanceAmount,
                'status'             => $status,
                'notes'              => $data['notes'] ?? '',
                'remarks'            => $data['payment_reason'] ?? '', // From validated data!
                'reference'          => $data['payment_reference'] ?? '', // From validated data!
                'payment_mode'       => $data['payment_mode'] ?? '', // From validated data!
            ]);

            Log::info('Insert data', [
                'remarks' => $data['payment_reason'] ?? null,
                'reference' => $data['payment_reference'] ?? null,
                'payment_mode' => $data['payment_mode'] ?? null,
            ]);

            foreach ($data['items'] as $item) {
                $purchase->items()->create([
                    'item_id' => $item['item_id'],
                    'uom' => $item['uom'],
                    'quantity' => $item['quantity'],
                    'rate' => $item['rate'],
                    'discount' => $item['discount'] ?? 0
                ]);
            }
        });

        Log::info('PurchaseManagementController@store finished');
        return response()->json(['success' => true]);
    }

    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/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->whereBetween('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('date', 'desc')->get();

        // Create Spreadsheet
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        // Set headers
        $sheet->fromArray([
            ['Invoice No', '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->invoice_no : '',
                    $first ? $purchase->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->invoice_no,
                    $purchase->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');
        $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('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('invoice_no', fn($row) => $row->invoice_no)
            ->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]);
        }
        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('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->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
        ]);
    }
}
