<?php
namespace App\Http\Controllers\SuperAdmin;

use App\Http\Controllers\Controller;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\CustomerVendor;
use App\Models\TaxInvoice;
use App\Models\SalesCreditNote;
use App\Models\SalesDebitNote;
use App\Models\SalesPayment;
use Carbon\Carbon;
use Illuminate\Http\JsonResponse;
use Exception;
use App\Services\ExportService;

class SuperAdminSalesManagementController extends Controller
{
    protected $exportService;

    public function __construct(ExportService $exportService)
    {
        $this->exportService = $exportService;
    }
    public function index(Request $request)
    {
        // Fetch customers with both Title Case and lowercase roles to be safe
        $customers = CustomerVendor::whereIn('company_role', ['Customer', 'Both', 'customer', 'both'])
            ->orderBy('company')
            ->get();
        return view('superadmin.salesmanagement.index', compact('customers'));
    }

    // ==================== CUSTOMER PAYMENTS ====================

    /**
     * Get paginated payments data for DataTable
     */
    public function getPayments(Request $request): JsonResponse
    {
        try {
            $query = TaxInvoice::with('company');

            // Apply filters
            if ($request->filled('party_id')) {
                $query->where('company_id', $request->party_id);
            }

            if ($request->filled('date_range')) {
                $dates = explode(' - ', $request->date_range);
                if (count($dates) == 2) {
                    $query->whereBetween('invoice_date', [$dates[0], $dates[1]]);
                }
            }

            if ($request->filled('min_amount')) {
                $query->where('total_amount', '>=', $request->min_amount);
            }

            if ($request->filled('max_amount')) {
                $query->where('total_amount', '<=', $request->max_amount);
            }

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

            // Preload payment and credit data to avoid N+1
            $invoiceIds = $invoices->pluck('id')->toArray();
            $invoiceNos = $invoices->pluck('invoice_no')->filter()->toArray();

            $paidAmounts = SalesPayment::whereIn('invoice_id', $invoiceIds)
                ->selectRaw('invoice_id, SUM(payment_amount) as total_paid')
                ->groupBy('invoice_id')
                ->pluck('total_paid', 'invoice_id');

            $creditAmounts = SalesCreditNote::whereIn('invoice_no', $invoiceNos)
                ->selectRaw('invoice_no, SUM(amount) as total_credit')
                ->groupBy('invoice_no')
                ->pluck('total_credit', 'invoice_no');

            $lastPayments = SalesPayment::whereIn('invoice_id', $invoiceIds)
                ->orderBy('payment_date', 'desc')
                ->get()
                ->unique('invoice_id')
                ->keyBy('invoice_id');

            $data = $invoices->map(function ($invoice) use ($paidAmounts, $creditAmounts, $lastPayments) {
                $paidAmount = floatval($paidAmounts[$invoice->id] ?? 0);
                $creditAmount = floatval($creditAmounts[$invoice->invoice_no] ?? 0);
                $totalAmount = floatval($invoice->total_amount ?? 0);
                $balance = $totalAmount - $paidAmount - $creditAmount;

                // Determine payment status
                $status = 'Unpaid';
                $statusClass = 'danger';
                if ($paidAmount >= $totalAmount && $totalAmount > 0) {
                    $status = 'Paid';
                    $statusClass = 'success';
                } elseif ($paidAmount > 0) {
                    $status = 'Partial';
                    $statusClass = 'warning';
                }

                $lastPayment = $lastPayments[$invoice->id] ?? null;

                return [
                    'id' => $invoice->id,
                    'invoice_no' => $invoice->invoice_no ?? '-',
                    'invoice_date' => $invoice->invoice_date ? Carbon::parse($invoice->invoice_date)->format('d-m-Y') : '-',
                    'customer' => $invoice->company->company ?? 'N/A',
                    'total_amount' => $totalAmount,
                    'paid_amount' => $paidAmount,
                    'balance' => $balance,
                    'status' => $status,
                    'status_class' => $statusClass,
                    'payment_method' => $lastPayment ? ucfirst(str_replace('_', ' ', $lastPayment->payment_method)) : '-',
                    'reference_no' => $lastPayment ? ($lastPayment->reference_no ?? '-') : '-'
                ];
            });

            return response()->json(['data' => $data]);
        } catch (Exception $e) {
            return response()->json(['data' => [], 'error' => $e->getMessage()]);
        }
    }

    /**
     * Get unpaid invoices for payment dropdown
     */
    public function getUnpaidInvoices(Request $request): JsonResponse
    {
        try {
            $invoices = TaxInvoice::with('company')
                ->orderBy('invoice_date', 'desc')
                ->get();

            // Preload aggregates to avoid N+1
            $invoiceIds = $invoices->pluck('id')->toArray();
            $invoiceNos = $invoices->pluck('invoice_no')->filter()->toArray();

            $paidAmounts = SalesPayment::whereIn('invoice_id', $invoiceIds)
                ->selectRaw('invoice_id, SUM(payment_amount) as total_paid')
                ->groupBy('invoice_id')
                ->pluck('total_paid', 'invoice_id');

            $creditAmounts = SalesCreditNote::whereIn('invoice_no', $invoiceNos)
                ->selectRaw('invoice_no, SUM(amount) as total_credit')
                ->groupBy('invoice_no')
                ->pluck('total_credit', 'invoice_no');

            $data = [];
            foreach ($invoices as $invoice) {
                $paidAmount = floatval($paidAmounts[$invoice->id] ?? 0);
                $creditAmount = floatval($creditAmounts[$invoice->invoice_no] ?? 0);
                $totalAmount = floatval($invoice->total_amount ?? 0);
                $balance = $totalAmount - $paidAmount - $creditAmount;

                if ($balance > 0) {
                    $data[] = [
                        'id' => $invoice->id,
                        'invoice_no' => $invoice->invoice_no,
                        'customer' => $invoice->company->company ?? 'N/A',
                        'balance' => $balance,
                        'display' => $invoice->invoice_no . ' - ' . ($invoice->company->company ?? 'N/A') . ' (Balance: ₹' . number_format($balance, 2) . ')'
                    ];
                }
            }

            return response()->json(['success' => true, 'data' => $data]);
        } catch (Exception $e) {
            return response()->json(['success' => false, 'data' => [], 'error' => $e->getMessage()]);
        }
    }

    /**
     * Store a new payment
     */
    public function storePayment(Request $request): JsonResponse
    {
        try {
            $request->validate([
                'invoice_id' => 'required|exists:tax_invoices,id',
                'payment_date' => 'required|date',
                'payment_amount' => 'required|numeric|min:0.01',
                'payment_method' => 'required|string'
            ]);

            $invoice = TaxInvoice::find($request->invoice_id);

            // Calculate existing payments
            $existingPayments = SalesPayment::where('invoice_id', $request->invoice_id)->sum('payment_amount');
            $balance = floatval($invoice->total_amount) - floatval($existingPayments);

            if ($request->payment_amount > $balance) {
                return response()->json([
                    'success' => false,
                    'message' => 'Payment amount cannot exceed balance of ₹' . number_format($balance, 2)
                ], 422);
            }

            // Generate payment number
            $lastPayment = SalesPayment::orderBy('id', 'desc')->first();
            $nextNum = $lastPayment ? (intval(substr($lastPayment->payment_no, -4)) + 1) : 1;
            $paymentNo = 'PAY/' . date('Y') . '/' . str_pad($nextNum, 4, '0', STR_PAD_LEFT);

            $payment = SalesPayment::create([
                'payment_no' => $paymentNo,
                'invoice_id' => $request->invoice_id,
                'invoice_no' => $invoice->invoice_no,
                'party_id' => $invoice->company_id, // Ensure party_id matches invoice
                'payment_date' => $request->payment_date,
                'payment_amount' => $request->payment_amount,
                'payment_method' => $request->payment_method,
                'reference_no' => $request->reference_no,
                'notes' => $request->notes
            ]);

            return response()->json([
                'success' => true,
                'message' => 'Payment recorded successfully',
                'data' => $payment
            ]);
        } catch (Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    /**
     * Get a single payment
     */
    public function getPayment($id): JsonResponse
    {
        $payment = SalesPayment::with(['invoice', 'party'])->find($id);

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

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

    /**
     * Update a payment
     */
    public function updatePayment(Request $request, $id): JsonResponse
    {
        $payment = SalesPayment::find($id);

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

        $request->validate([
            'payment_date' => 'required|date',
            'payment_amount' => 'required|numeric|min:0.01',
            'payment_method' => 'required|string'
        ]);

        $payment->update([
            'payment_date' => $request->payment_date,
            'payment_amount' => $request->payment_amount,
            'payment_method' => $request->payment_method,
            'reference_no' => $request->reference_no,
            'notes' => $request->notes
        ]);

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

    /**
     * Delete a payment
     */
    public function deletePayment($id): JsonResponse
    {
        $payment = SalesPayment::find($id);

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

        $payment->delete();

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

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

    /**
     * Get credit notes for DataTable
     */
    public function getCreditNotes(Request $request): JsonResponse
    {
        try {
            $query = SalesCreditNote::with('party');

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

            if ($request->filled('date_range')) {
                $dates = explode(' - ', $request->date_range);
                if (count($dates) == 2) {
                    $query->whereBetween('date', [$dates[0], $dates[1]]);
                }
            }

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

            $creditNotes = $query->orderBy('date', 'desc')->get();

            $data = $creditNotes->map(function ($cn) {
                return [
                    'id' => $cn->id,
                    'credit_note_no' => $cn->credit_note_no,
                    'date' => $cn->date ? Carbon::parse($cn->date)->format('Y-m-d') : null,
                    'party' => $cn->party->company ?? 'N/A',
                    'invoice_no' => $cn->invoice_no ?? '-',
                    'amount' => $cn->amount,
                    'reason' => $cn->reason ?? '-'
                ];
            });

            return response()->json(['data' => $data]);
        } catch (Exception $e) {
            return response()->json(['data' => [], 'error' => $e->getMessage()]);
        }
    }

    /**
     * Get next credit note number
     */
    public function getNextCreditNoteNumber(): JsonResponse
    {
        $lastCreditNote = SalesCreditNote::orderBy('id', 'desc')->first();
        $nextNum = $lastCreditNote ? (intval(substr($lastCreditNote->credit_note_no, -4)) + 1) : 1;
        $creditNoteNo = 'SCN/' . date('Y') . '/' . str_pad($nextNum, 4, '0', STR_PAD_LEFT);

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

    /**
     * Store a new credit note
     */
    public function storeCreditNote(Request $request): JsonResponse
    {
        try {
            $request->validate([
                'credit_note_no' => 'required|string|unique:sales_credit_notes,credit_note_no',
                'date' => 'required|date',
                'party_id' => 'required|exists:customer_vendors,id',
                'amount' => 'required|numeric|min:0.01',
                'reason' => 'required|string'
            ]);

            $creditNote = SalesCreditNote::create([
                'credit_note_no' => $request->credit_note_no,
                'date' => $request->date,
                'party_id' => $request->party_id,
                'invoice_no' => $request->invoice_no,
                'amount' => $request->amount,
                'reason' => $request->reason,
                'description' => $request->description
            ]);

            return response()->json([
                'success' => true,
                'message' => 'Credit note created successfully',
                'data' => $creditNote
            ]);
        } catch (Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()], 500);
        }
    }

    /**
     * Get a single credit note
     */
    public function getCreditNote($id): JsonResponse
    {
        $creditNote = SalesCreditNote::with('party')->find($id);

        if (!$creditNote) {
            return response()->json(['success' => false, 'message' => 'Credit note not found'], 404);
        }

        // Format date for HTML date input (expects YYYY-MM-DD)
        $dateFormatted = null;
        if ($creditNote->date) {
            $dateFormatted = $creditNote->date instanceof \Carbon\Carbon
                ? $creditNote->date->format('Y-m-d')
                : Carbon::parse($creditNote->date)->format('Y-m-d');
        }

        return response()->json([
            'success' => true,
            'data' => [
                'id' => $creditNote->id,
                'credit_note_no' => $creditNote->credit_note_no,
                'date' => $dateFormatted,
                'party_id' => $creditNote->party_id,
                'invoice_no' => $creditNote->invoice_no,
                'amount' => $creditNote->amount,
                'reason' => $creditNote->reason,
                'description' => $creditNote->description,
            ]
        ]);
    }

    /**
     * Update a credit note
     */
    public function updateCreditNote(Request $request, $id): JsonResponse
    {
        $creditNote = SalesCreditNote::find($id);

        if (!$creditNote) {
            return response()->json(['success' => false, 'message' => 'Credit note not found'], 404);
        }

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

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

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

    /**
     * Delete a credit note
     */
    public function deleteCreditNote($id): JsonResponse
    {
        $creditNote = SalesCreditNote::find($id);

        if (!$creditNote) {
            return response()->json(['success' => false, 'message' => 'Credit note not found'], 404);
        }

        $creditNote->delete();

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

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

    /**
     * Get Debit Notes list
     */
    public function getDebitNotes(Request $request): JsonResponse
    {
        try {
            $query = SalesDebitNote::with('party');

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

            if ($request->filled('date_range')) {
                $dates = explode(' - ', $request->date_range);
                if (count($dates) == 2) {
                    $query->whereBetween('date', [$dates[0], $dates[1]]);
                }
            }

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

            $debitNotes = $query->orderBy('date', 'desc')->get();

            $data = $debitNotes->map(function ($note) {
                return [
                    'id' => $note->id,
                    'debit_note_no' => $note->debit_note_no,
                    'date' => $note->date->format('Y-m-d'),
                    'party' => $note->party->company ?? 'N/A',
                    'party_id' => $note->party_id,
                    'invoice_no' => $note->invoice_no,
                    'amount' => $note->amount,
                    'reason' => $note->reason,
                    'description' => $note->description
                ];
            });

            return response()->json(['success' => true, 'data' => $data]);
        } catch (Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    /**
     * Get next debit note number
     */
    public function getNextDebitNoteNumber(): JsonResponse
    {
        $lastNote = SalesDebitNote::orderBy('id', 'desc')->first();
        $nextNumber = $lastNote ? ((int)substr($lastNote->debit_note_no, -4) + 1) : 1;
        $debitNoteNo = 'UEPL/DN/' . str_pad($nextNumber, 4, '0', STR_PAD_LEFT);

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

    /**
     * Store a new debit note
     */
    public function storeDebitNote(Request $request): JsonResponse
    {
        $request->validate([
            'debit_note_no' => 'required|string|unique:sales_debit_notes,debit_note_no',
            'date' => 'required|date',
            'party_id' => 'required|exists:customer_vendors,id',
            'invoice_no' => 'required|string',
            'amount' => 'required|numeric|min:0.01',
            'reason' => 'required|string'
        ]);

        SalesDebitNote::create([
            'debit_note_no' => $request->debit_note_no,
            'date' => $request->date,
            'party_id' => $request->party_id,
            'invoice_no' => $request->invoice_no,
            'amount' => $request->amount,
            'reason' => $request->reason,
            'description' => $request->description
        ]);

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

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

        if (!$debitNote) {
            return response()->json(['success' => false, 'message' => 'Debit note not found'], 404);
        }

        // Format date for HTML date input (expects YYYY-MM-DD)
        $dateFormatted = null;
        if ($debitNote->date) {
            $dateFormatted = $debitNote->date instanceof \Carbon\Carbon
                ? $debitNote->date->format('Y-m-d')
                : Carbon::parse($debitNote->date)->format('Y-m-d');
        }

        return response()->json([
            'success' => true,
            'data' => [
                'id' => $debitNote->id,
                'debit_note_no' => $debitNote->debit_note_no,
                'date' => $dateFormatted,
                'party_id' => $debitNote->party_id,
                'invoice_no' => $debitNote->invoice_no,
                'amount' => $debitNote->amount,
                'reason' => $debitNote->reason,
                'description' => $debitNote->description,
            ]
        ]);
    }

    /**
     * Update a debit note
     */
    public function updateDebitNote(Request $request, $id): JsonResponse
    {
        $debitNote = SalesDebitNote::find($id);

        if (!$debitNote) {
            return response()->json(['success' => false, 'message' => 'Debit note not found'], 404);
        }

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

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

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

    /**
     * Delete a debit note
     */
    public function deleteDebitNote($id): JsonResponse
    {
        $debitNote = SalesDebitNote::find($id);

        if (!$debitNote) {
            return response()->json(['success' => false, 'message' => 'Debit note not found'], 404);
        }

        $debitNote->delete();

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

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

    /**
     * Get aging report data
     */
    public function getAgingReport(Request $request): JsonResponse
    {
        try {
            $asOfDate = $request->input('as_of_date', date('Y-m-d'));
            $partyId = $request->input('party_id');
            $reportType = $request->input('report_type', 'summary');

            // Get all invoices
            $query = TaxInvoice::with('company')
                ->whereDate('invoice_date', '<=', $asOfDate);

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

            $invoices = $query->get();

            // Preload aggregates to avoid N+1
            $invoiceIds = $invoices->pluck('id')->toArray();
            $invoiceNos = $invoices->pluck('invoice_no')->filter()->toArray();

            $paidAmounts = SalesPayment::whereIn('invoice_id', $invoiceIds)
                ->whereDate('payment_date', '<=', $asOfDate)
                ->selectRaw('invoice_id, SUM(payment_amount) as total_paid')
                ->groupBy('invoice_id')
                ->pluck('total_paid', 'invoice_id');

            $creditAmounts = SalesCreditNote::whereIn('invoice_no', $invoiceNos)
                ->whereDate('date', '<=', $asOfDate)
                ->selectRaw('invoice_no, SUM(amount) as total_credit')
                ->groupBy('invoice_no')
                ->pluck('total_credit', 'invoice_no');

            // Group by customer and calculate aging buckets
            $customerData = [];
            $totals = [
                'current' => 0,
                '1_15' => 0,
                '16_30' => 0,
                '31_45' => 0,
                'over_45' => 0,
                'total_due' => 0
            ];

            foreach ($invoices as $invoice) {
                $paidAmount = floatval($paidAmounts[$invoice->id] ?? 0);
                $creditAmount = floatval($creditAmounts[$invoice->invoice_no] ?? 0);

                $balance = floatval($invoice->total_amount ?? 0) - $paidAmount - $creditAmount;

                if ($balance <= 0) continue;

                $customerId = $invoice->company_id;
                $customerName = $invoice->company->company ?? 'Unknown Customer';

                if (!isset($customerData[$customerId])) {
                    $customerData[$customerId] = [
                        'customer_id' => $customerId,
                        'customer_name' => $customerName,
                        'current' => 0,
                        '1_15' => 0,
                        '16_30' => 0,
                        '31_45' => 0,
                        'over_45' => 0,
                        'total_due' => 0,
                        'invoices' => []
                    ];
                }

                // Calculate days overdue
                $invoiceDate = Carbon::parse($invoice->invoice_date);
                $asOf = Carbon::parse($asOfDate);
                $daysOverdue = $invoiceDate->diffInDays($asOf);

                // Determine bucket
                $bucket = 'current';
                if ($daysOverdue > 45) {
                    $bucket = 'over_45';
                } elseif ($daysOverdue > 30) {
                    $bucket = '31_45';
                } elseif ($daysOverdue > 15) {
                    $bucket = '16_30';
                } elseif ($daysOverdue > 0) {
                    $bucket = '1_15';
                }

                $customerData[$customerId][$bucket] += $balance;
                $customerData[$customerId]['total_due'] += $balance;
                $totals[$bucket] += $balance;
                $totals['total_due'] += $balance;

                if ($reportType === 'detailed') {
                    $customerData[$customerId]['invoices'][] = [
                        'invoice_no' => $invoice->invoice_no,
                        'invoice_date' => $invoice->invoice_date,
                        'days_overdue' => $daysOverdue,
                        'grand_total' => $invoice->total_amount,
                        'paid_amount' => $paidAmount,
                        'balance_amount' => $balance,
                        'bucket' => $bucket
                    ];
                }
            }

            // Calculate received in last 30 days
            $thirtyDaysAgo = Carbon::parse($asOfDate)->subDays(30)->format('Y-m-d');
            $receivedLast30Days = SalesPayment::whereBetween('payment_date', [$thirtyDaysAgo, $asOfDate])->sum('payment_amount');

            return response()->json([
                'success' => true,
                'data' => array_values($customerData),
                'totals' => $totals,
                'received_last_30_days' => floatval($receivedLast30Days),
                'as_of_date' => $asOfDate
            ]);
        } catch (Exception $e) {
            return response()->json([
                'success' => false,
                'data' => [],
                'totals' => ['current' => 0, '1_15' => 0, '16_30' => 0, '31_45' => 0, 'over_45' => 0, 'total_due' => 0],
                'received_last_30_days' => 0,
                'error' => $e->getMessage()
            ]);
        }
    }

    /**
     * Export aging report as styled Excel
     */
    public function exportAgingReport(Request $request)
    {
        $asOfDate = $request->input('as_of_date', date('Y-m-d'));

        $agingResponse = $this->getAgingReport($request);
        $agingData = json_decode($agingResponse->getContent(), true);

        // Generate Styled Excel
        $spreadsheet = $this->exportService->createStyledSpreadsheet('Sales Aging Report - As of ' . $asOfDate);
        $sheet = $spreadsheet->getActiveSheet();
        $headerRow = 4;
        
        $headers = ['Customer', 'Current', '1-15 Days', '16-30 Days', '31-45 Days', '>45 Days', 'Total Due'];
        $colCount = count($headers);
        $sheet->fromArray($headers, NULL, 'A' . $headerRow);
        $this->exportService->styleHeaderRow($sheet, $headerRow, $colCount);

        $rowNum = $headerRow + 1;
        foreach ($agingData['data'] ?? [] as $row) {
            $sheet->fromArray([
                $row['customer_name'] ?? 'Unknown',
                $row['current'] ?? 0,
                $row['1_15'] ?? 0,
                $row['16_30'] ?? 0,
                $row['31_45'] ?? 0,
                $row['over_45'] ?? 0,
                $row['total_due'] ?? 0
            ], NULL, 'A' . $rowNum);
            $rowNum++;
        }
        
        $this->exportService->styleDataRows($sheet, $headerRow + 1, $rowNum - 1, $colCount);
        
        // Add totals row
        $sheet->fromArray([
            'TOTAL',
            $agingData['totals']['current'] ?? 0,
            $agingData['totals']['1_15'] ?? 0,
            $agingData['totals']['16_30'] ?? 0,
            $agingData['totals']['31_45'] ?? 0,
            $agingData['totals']['over_45'] ?? 0,
            $agingData['totals']['total_due'] ?? 0
        ], NULL, 'A' . $rowNum);
        $this->exportService->styleTotalsRow($sheet, $rowNum, $colCount);
        
        $this->exportService->autoSizeColumns($sheet, $colCount);
        
        $filename = "Sales-Aging-Report-" . date('Ymd_His') . ".xlsx";
        $this->exportService->download($spreadsheet, $filename);
    }

    // ==================== STATEMENT OF ACCOUNTS ====================

    public function getStatementOfAccounts(Request $request): JsonResponse
    {
        try {
            $partyId = $request->party_id;
            $fromDate = $request->from_date;
            $toDate = $request->to_date;

            if (!$partyId) {
                return response()->json(['success' => false, 'message' => 'Please select a customer']);
            }

            // Fetch Data
            $invoices = TaxInvoice::where('company_id', $partyId)
                ->whereBetween('invoice_date', [$fromDate, $toDate])
                ->get()
                ->map(function($inv) {
                     return [
                         'date' => $inv->invoice_date,
                         'type' => 'Invoice',
                         'ref_no' => $inv->invoice_no,
                         'debit' => floatval($inv->total_amount),
                         'credit' => 0,
                         'description' => 'Invoice generation'
                     ];
                });

            $payments = SalesPayment::where('party_id', $partyId)
                ->whereBetween('payment_date', [$fromDate, $toDate])
                ->get()
                ->map(function($pay) {
                     return [
                         'date' => $pay->payment_date->format('Y-m-d'),
                         'type' => 'Payment',
                         'ref_no' => $pay->payment_no,
                         'debit' => 0,
                         'credit' => floatval($pay->payment_amount),
                         'description' => $pay->payment_method . ' - ' . $pay->reference_no
                     ];
                });
            
            // Credit Notes
            $creditNotes = SalesCreditNote::where('party_id', $partyId)
                ->whereBetween('date', [$fromDate, $toDate])
                ->get()
                ->map(function($cn) {
                     return [
                         'date' => $cn->date->format('Y-m-d'),
                         'type' => 'Credit Note',
                         'ref_no' => $cn->credit_note_no,
                         'debit' => 0,
                         'credit' => floatval($cn->amount),
                         'description' => $cn->reason
                     ];
                });

            // Debit Notes
            $debitNotes = SalesDebitNote::where('party_id', $partyId)
                 ->whereBetween('date', [$fromDate, $toDate])
                 ->get()
                 ->map(function($dn) {
                      return [
                          'date' => $dn->date->format('Y-m-d'),
                          'type' => 'Debit Note',
                          'ref_no' => $dn->debit_note_no,
                          'debit' => floatval($dn->amount),
                          'credit' => 0,
                          'description' => $dn->reason
                      ];
                 });
            
            // Merge and Sort
            $transactions = $invoices->concat($payments)->concat($creditNotes)->concat($debitNotes)
                 ->sortBy('date') ->values();
            
            // Opening Balance Calculation
            $openingDebit = TaxInvoice::where('company_id', $partyId)->where('invoice_date', '<', $fromDate)->sum('total_amount')
                + SalesDebitNote::where('party_id', $partyId)->where('date', '<', $fromDate)->sum('amount');
            
            $openingCredit = SalesPayment::where('party_id', $partyId)->where('payment_date', '<', $fromDate)->sum('payment_amount')
                + SalesCreditNote::where('party_id', $partyId)->where('date', '<', $fromDate)->sum('amount');

            $openingBalance = $openingDebit - $openingCredit;

            $runningBalance = $openingBalance;
            $data = $transactions->map(function($txn) use (&$runningBalance) {
                $runningBalance += $txn['debit'] - $txn['credit'];
                $txn['balance'] = $runningBalance;
                return $txn;
            });

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

        } catch (Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    // ==================== RECEIPTS ====================
    public function getReceipts(Request $request): JsonResponse
    {
        try {
            $query = SalesPayment::with(['party', 'invoice']);

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

            if ($request->filled('date_range')) {
                $dates = explode(' - ', $request->date_range);
                if (count($dates) == 2) {
                    $query->whereBetween('payment_date', [$dates[0], $dates[1]]);
                }
            }

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

            $payments = $query->orderBy('payment_date', 'desc')->get()->map(function($pay) {
                return [
                    'id' => $pay->id,
                    'payment_no' => $pay->payment_no,
                    'date' => $pay->payment_date->format('Y-m-d'),
                    'party' => $pay->party->company ?? 'N/A',
                    'invoice_no' => $pay->invoice_no ?? '-',
                    'amount' => floatval($pay->payment_amount),
                    'method' => ucfirst($pay->payment_method),
                    'reference' => $pay->reference_no ?? '-'
                ];
            });

            return response()->json(['success' => true, 'data' => $payments]);
        } catch (Exception $e) {
            return response()->json(['success' => false, 'message' => $e->getMessage()]);
        }
    }

    public function downloadReceipt($id)
    {
        $payment = SalesPayment::with('invoice', 'party')->find($id);
        if (!$payment) return back()->with('error', 'Payment not found');
        return view('superadmin.salesmanagement.pdf.receipt', compact('payment'));
    }
}
