<?php
session_start();
header('Content-Type: application/json');
include_once('../../includes/config.php');

// Check authentication
if (!isset($_SESSION['uid'])) {
    echo json_encode(['success' => false, 'message' => 'Not authenticated']);
    exit();
}

$response = ['success' => false, 'message' => ''];

// Get JSON data
$jsonData = file_get_contents('php://input');
$data = json_decode($jsonData, true);

if ($data) {
    // Start transaction
    mysqli_begin_transaction($con);
    
    try {
        $invoiceNumber = mysqli_real_escape_string($con, $data['invoiceNumber']);
    $salesType = isset($data['salesType']) ? mysqli_real_escape_string($con, $data['salesType']) : 'GST';
    $isQuotation = (strcasecmp($salesType, 'QUOTATION') === 0);
        $customerId = intval($data['customerId']);
        $saleDate = mysqli_real_escape_string($con, $data['saleDate']);
        $subTotal = floatval($data['subTotal']);
        $cooliAmount = floatval($data['cooliAmount']);
        
        // Determine if this is a return transaction
        $isReturn = (strpos($salesType, '-RETURN') !== false);
        $transportName = isset($data['transportName']) ? mysqli_real_escape_string($con, $data['transportName']) : '';
        $vehicleNumber = isset($data['vehicleNumber']) ? mysqli_real_escape_string($con, $data['vehicleNumber']) : '';
        $transportGSTPercent = isset($data['transportGSTPercent']) ? floatval($data['transportGSTPercent']) : 0;
        $transportGSTType = isset($data['transportGSTType']) ? mysqli_real_escape_string($con, $data['transportGSTType']) : 'inclusive';
        $transportGSTAmount = isset($data['transportGSTAmount']) ? floatval($data['transportGSTAmount']) : 0;
        $roundOffAmount = isset($data['roundOffAmount']) ? floatval($data['roundOffAmount']) : 0;
        $totalAmount = floatval($data['totalAmount']);
        $finalAmount = isset($data['finalAmount']) ? floatval($data['finalAmount']) : $totalAmount;
        $paymentMode = mysqli_real_escape_string($con, $data['paymentMode']);
        $cashAmount = isset($data['cashAmount']) ? floatval($data['cashAmount']) : 0;
        $upiAmount = isset($data['upiAmount']) ? floatval($data['upiAmount']) : 0;
        $paymentAmount = round($cashAmount + $upiAmount, 2);
        $creditAmount = round($totalAmount - $paymentAmount, 2);
        if ($creditAmount < 0) {
            $creditAmount = 0;
        }
        // Derive payment status based on outstanding amount
        if ($creditAmount > 0) {
            $paymentStatus = $paymentAmount > 0 ? 'Partial' : 'Unpaid';
        } else {
            $paymentStatus = 'Paid';
            $creditAmount = 0;
        }

        if ($isQuotation) {
            // Quotation should not record payments or outstanding balances
            $paymentMode = mysqli_real_escape_string($con, 'Quotation');
            $cashAmount = 0;
            $upiAmount = 0;
            $paymentAmount = 0;
            $creditAmount = 0;
            $paymentStatus = 'Quotation';
        }
        $notes = mysqli_real_escape_string($con, $data['notes']);
        $userId = intval($data['userId']);
        $editingSaleId = isset($data['editingSaleId']) ? intval($data['editingSaleId']) : 0;
        
        // Points redemption data
        $redeemedPoints = isset($data['redeemedPoints']) ? floatval($data['redeemedPoints']) : 0;
        $billDiscountType = isset($data['billDiscountType']) ? mysqli_real_escape_string($con, $data['billDiscountType']) : '';
        
        if ($editingSaleId > 0) {
            // EDIT MODE - Update existing sale
            
            // Get old sale data to reverse customer account and stock
            $oldSaleQuery = "SELECT CustomerID, SalesType, TotalAmount, InvoiceNumber, PaymentAmount FROM tblsales WHERE ID = $editingSaleId";
            $oldSaleResult = mysqli_query($con, $oldSaleQuery);
            if (!$oldSaleResult || mysqli_num_rows($oldSaleResult) == 0) {
                throw new Exception('Sale record not found for editing');
            }
            $oldSale = mysqli_fetch_assoc($oldSaleResult);
            $oldCustomerId = $oldSale['CustomerID'];
            $oldSalesType = isset($oldSale['SalesType']) ? $oldSale['SalesType'] : 'GST';
            $oldTotalAmount = floatval($oldSale['TotalAmount']);
            $oldPaymentAmountStored = floatval($oldSale['PaymentAmount']);
            $oldInvoiceNumber = $oldSale['InvoiceNumber'];
            
            // Determine if old transaction was a return
            $oldIsReturn = (strpos($oldSalesType, '-RETURN') !== false);
            $oldIsQuotation = (strcasecmp($oldSalesType, 'QUOTATION') === 0);
            
            // Get old sale details to reverse stock
            $oldDetailsQuery = "SELECT ProductID, Quantity FROM tblsales_details WHERE SaleID = $editingSaleId";
            $oldDetailsResult = mysqli_query($con, $oldDetailsQuery);
            $oldItems = [];
            while ($row = mysqli_fetch_assoc($oldDetailsResult)) {
                $oldItems[$row['ProductID']] = floatval($row['Quantity']);
            }
            
            // Reverse stock for old items based on transaction type
            // For OLD SALES: INCREASE stock (we're removing the sale, goods come back)
            // For OLD RETURNS: DECREASE stock (we're removing the return, goods go out again)
            if (!$oldIsQuotation) {
                foreach ($oldItems as $productId => $quantity) {
                    $reverseOperation = $oldIsReturn ? '-' : '+';
                    $reverseStockSql = "UPDATE tblitems SET CurrentStock = CurrentStock $reverseOperation $quantity WHERE ID = $productId";
                    if (!mysqli_query($con, $reverseStockSql)) {
                        throw new Exception('Error reversing stock for product ID ' . $productId . ': ' . mysqli_error($con));
                    }
                }
            }
            
            // Delete old sale details
            $deleteSql = "DELETE FROM tblsales_details WHERE SaleID = $editingSaleId";
            if (!mysqli_query($con, $deleteSql)) {
                throw new Exception('Error deleting old sale details: ' . mysqli_error($con));
            }
            
            // Delete old GST details if exists
            $deleteGSTSql = "DELETE FROM tblsales_gst_details WHERE SaleID = $editingSaleId";
            mysqli_query($con, $deleteGSTSql);
            
            // Reverse old loyalty points earned from this sale
            $oldPointsQuery = "SELECT Points FROM tblcustomer_loyalty_points 
                              WHERE SaleID = $editingSaleId AND CustomerID = $oldCustomerId AND TransactionType = 'EARNED'";
            $oldPointsResult = mysqli_query($con, $oldPointsQuery);
            if ($oldPointsResult && mysqli_num_rows($oldPointsResult) > 0) {
                $oldPointsRow = mysqli_fetch_assoc($oldPointsResult);
                $oldPointsEarned = floatval($oldPointsRow['Points']);
                
                if ($oldPointsEarned > 0) {
                    // Reverse the points from customer (deduct old earned points)
                    $reversePointsSql = "UPDATE tblcustomers 
                                        SET LoyaltyPoints = LoyaltyPoints - $oldPointsEarned,
                                            TotalPointsEarned = TotalPointsEarned - $oldPointsEarned
                                        WHERE ID = $oldCustomerId";
                    mysqli_query($con, $reversePointsSql);
                    
                    // Delete the old loyalty points transaction
                    $deleteOldPointsSql = "DELETE FROM tblcustomer_loyalty_points 
                                          WHERE SaleID = $editingSaleId AND CustomerID = $oldCustomerId AND TransactionType = 'EARNED'";
                    mysqli_query($con, $deleteOldPointsSql);
                }
            }
            
            // Reverse old points redemption if any
            $oldRedemptionQuery = "SELECT Points FROM tblcustomer_loyalty_points 
                                  WHERE SaleID = $editingSaleId AND CustomerID = $oldCustomerId AND TransactionType = 'REDEEMED'";
            $oldRedemptionResult = mysqli_query($con, $oldRedemptionQuery);
            if ($oldRedemptionResult && mysqli_num_rows($oldRedemptionResult) > 0) {
                $oldRedemptionRow = mysqli_fetch_assoc($oldRedemptionResult);
                $oldPointsRedeemed = floatval($oldRedemptionRow['Points']);
                
                if ($oldPointsRedeemed > 0) {
                    // Add back the redeemed points to customer
                    $reverseRedemptionSql = "UPDATE tblcustomers 
                                            SET LoyaltyPoints = LoyaltyPoints + $oldPointsRedeemed,
                                                TotalPointsRedeemed = TotalPointsRedeemed - $oldPointsRedeemed
                                            WHERE ID = $oldCustomerId";
                    mysqli_query($con, $reverseRedemptionSql);
                    
                    // Delete the old redemption transaction
                    $deleteOldRedemptionSql = "DELETE FROM tblcustomer_loyalty_points 
                                              WHERE SaleID = $editingSaleId AND CustomerID = $oldCustomerId AND TransactionType = 'REDEEMED'";
                    mysqli_query($con, $deleteOldRedemptionSql);
                }
            }
            
            // Delete old payment entries first (to avoid duplicates)
            $oldPaymentRef = 'PAY-' . $oldInvoiceNumber;
            $oldPaymentQuery = "SELECT Amount FROM tblcustomer_accounts 
                               WHERE InvoiceNumber = '$oldPaymentRef' AND CustomerID = $oldCustomerId AND TransactionType = 'Credit'";
            $oldPaymentResult = mysqli_query($con, $oldPaymentQuery);
            $oldPaymentAmount = 0;
            if ($oldPaymentResult && mysqli_num_rows($oldPaymentResult) > 0) {
                $oldPaymentRow = mysqli_fetch_assoc($oldPaymentResult);
                $oldPaymentAmount = floatval($oldPaymentRow['Amount']);
                
                // Delete old payment entry
                $deleteOldPaymentSql = "DELETE FROM tblcustomer_accounts 
                                       WHERE InvoiceNumber = '$oldPaymentRef' AND CustomerID = $oldCustomerId AND TransactionType = 'Credit'";
                if (!mysqli_query($con, $deleteOldPaymentSql)) {
                    throw new Exception('Error deleting old payment: ' . mysqli_error($con));
                }
                
                // Reverse old payment from customer balance (add it back since it was deducted)
                $reversePaymentSql = "UPDATE tblcustomers 
                                     SET CurrentBalance = CurrentBalance + $oldPaymentAmount 
                                     WHERE ID = $oldCustomerId";
                if (!mysqli_query($con, $reversePaymentSql)) {
                    throw new Exception('Error reversing old payment balance: ' . mysqli_error($con));
                }
            }
            
            // Update sale master
            $saleSql = "UPDATE tblsales SET 
                        SalesType = '$salesType',
                        CustomerID = $customerId,
                        SaleDate = '$saleDate',
                        SubTotal = $subTotal,
                        CooliAmount = $cooliAmount,
                        TransportName = '$transportName',
                        VehicleNumber = '$vehicleNumber',
                        TransportGSTPercent = $transportGSTPercent,
                        TransportGSTType = '$transportGSTType',
                        TransportGSTAmount = $transportGSTAmount,
                        RoundOffAmount = $roundOffAmount,
                        TotalAmount = $totalAmount,
                        FinalAmount = $finalAmount,
                        PaymentMode = '$paymentMode',
                        PaymentAmount = $paymentAmount,
                        PaymentStatus = '$paymentStatus',
                        CashAmount = $cashAmount,
                        UpiAmount = $upiAmount,
                        CreditAmount = $creditAmount,
                        Notes = '$notes'
                        WHERE ID = $editingSaleId";
            
            if (!mysqli_query($con, $saleSql)) {
                throw new Exception('Error updating sale: ' . mysqli_error($con));
            }
            
            $saleId = $editingSaleId;
            
            // Customer account ledger handling for edit mode
            $oldAccountSql = "SELECT ID, TransactionType, Amount FROM tblcustomer_accounts 
                              WHERE InvoiceNumber = '$oldInvoiceNumber' AND CustomerID = $oldCustomerId LIMIT 1";
            $oldAccountResult = mysqli_query($con, $oldAccountSql);
            $oldAccountEntry = null;
            if ($oldAccountResult && mysqli_num_rows($oldAccountResult) > 0) {
                $oldAccountEntry = mysqli_fetch_assoc($oldAccountResult);
            }

            if ($oldAccountEntry) {
                $oldAccountId = intval($oldAccountEntry['ID']);
                $oldAccountType = $oldAccountEntry['TransactionType'];
                $oldLedgerAmount = floatval($oldAccountEntry['Amount']);

                $deleteOldAccountSql = "DELETE FROM tblcustomer_accounts WHERE ID = $oldAccountId";
                if (!mysqli_query($con, $deleteOldAccountSql)) {
                    throw new Exception('Error deleting old customer ledger entry: ' . mysqli_error($con));
                }

                if ($oldLedgerAmount > 0) {
                    if ($oldAccountType === 'Debit') {
                        $reverseOldBalanceSql = "UPDATE tblcustomers SET CurrentBalance = CurrentBalance - $oldLedgerAmount WHERE ID = $oldCustomerId";
                    } else {
                        $reverseOldBalanceSql = "UPDATE tblcustomers SET CurrentBalance = CurrentBalance + $oldLedgerAmount WHERE ID = $oldCustomerId";
                    }

                    if (!mysqli_query($con, $reverseOldBalanceSql)) {
                        throw new Exception('Error reversing previous customer balance: ' . mysqli_error($con));
                    }
                }
            }

            $needsLedger = !$isQuotation && $creditAmount > 0;
            if ($needsLedger) {
                $newTransactionType = $isReturn ? 'Credit' : 'Debit';
                $newDescription = $isReturn ? 'Sales Return' : 'Sales Bill';
                $ledgerAmount = $isReturn ? $creditAmount : $totalAmount;

                $accountSql = "INSERT INTO tblcustomer_accounts 
                              (CustomerID, InvoiceNumber, TransactionType, TransactionDate, Amount, BalanceAmount, Description, PaymentMode, AddedBy) 
                              VALUES 
                              ($customerId, '$invoiceNumber', '$newTransactionType', '$saleDate', $ledgerAmount, $ledgerAmount, '$newDescription', '$paymentMode', $userId)";

                if (!mysqli_query($con, $accountSql)) {
                    throw new Exception('Error inserting updated customer account entry: ' . mysqli_error($con));
                }

                if ($newTransactionType === 'Debit') {
                    $applyNewBalanceSql = "UPDATE tblcustomers SET CurrentBalance = CurrentBalance + $ledgerAmount WHERE ID = $customerId";
                } else {
                    $applyNewBalanceSql = "UPDATE tblcustomers SET CurrentBalance = CurrentBalance - $ledgerAmount WHERE ID = $customerId";
                }

                if (!mysqli_query($con, $applyNewBalanceSql)) {
                    throw new Exception('Error applying updated customer balance: ' . mysqli_error($con));
                }
            }
            
        } else {
            // NEW SALE MODE - Insert new sale
            
            // Insert sale master
            $saleSql = "INSERT INTO tblsales 
                        (InvoiceNumber, SalesType, CustomerID, SaleDate, SubTotal, CooliAmount, TransportName, VehicleNumber, TransportGSTPercent, TransportGSTType, TransportGSTAmount, RoundOffAmount, TotalAmount, FinalAmount, PaymentMode, PaymentAmount, PaymentStatus, CashAmount, UpiAmount, CreditAmount, Notes, AddedBy) 
                        VALUES 
                        ('$invoiceNumber', '$salesType', $customerId, '$saleDate', $subTotal, $cooliAmount, '$transportName', '$vehicleNumber', $transportGSTPercent, '$transportGSTType', $transportGSTAmount, $roundOffAmount, $totalAmount, $finalAmount, '$paymentMode', $paymentAmount, '$paymentStatus', $cashAmount, $upiAmount, $creditAmount, '$notes', $userId)";
            
            if (!mysqli_query($con, $saleSql)) {
                throw new Exception('Error inserting sale: ' . mysqli_error($con));
            }
            
            $saleId = mysqli_insert_id($con);
            
            if ($creditAmount > 0 && !$isQuotation) {
                // For RETURN transactions: Credit customer (they return goods, we owe them)
                // For SALES transactions: Debit customer (they owe us)
                $transactionType = $isReturn ? 'Credit' : 'Debit';
                $description = $isReturn ? 'Sales Return' : 'Sales Bill';
                $ledgerAmount = $isReturn ? $creditAmount : $totalAmount;

                // Add to customer account
                $accountSql = "INSERT INTO tblcustomer_accounts 
                              (CustomerID, InvoiceNumber, TransactionType, TransactionDate, Amount, BalanceAmount, Description, PaymentMode, AddedBy) 
                              VALUES 
                              ($customerId, '$invoiceNumber', '$transactionType', '$saleDate', $ledgerAmount, $ledgerAmount, '$description', '$paymentMode', $userId)";

                if (!mysqli_query($con, $accountSql)) {
                    throw new Exception('Error inserting customer account: ' . mysqli_error($con));
                }

                // Update customer balance
                // For RETURN: Decrease balance (we owe them, so their receivable from us increases - negative balance)
                // For SALES: Increase balance (they owe us)
                $balanceModifier = $isReturn ? '-' : '+';
                $updateBalanceSql = "UPDATE tblcustomers 
                                    SET CurrentBalance = CurrentBalance $balanceModifier $ledgerAmount 
                                    WHERE ID = $customerId";

                if (!mysqli_query($con, $updateBalanceSql)) {
                    throw new Exception('Error updating customer balance: ' . mysqli_error($con));
                }
            }
        }
        
        // Insert sale details (common for both new and edit)
        foreach ($data['items'] as $item) {
            $productId = intval($item['productId']);
            $productCode = mysqli_real_escape_string($con, $item['productCode']);
            $productName = mysqli_real_escape_string($con, $item['productName']);
            $rate = floatval($item['rate']);
            $purchaseRate = isset($item['purchaseRate']) ? floatval($item['purchaseRate']) : 0;
            $quantity = floatval($item['quantity']);
            $amount = floatval($item['amount']);
            
            // Discount fields
            $discount = isset($item['discount']) ? floatval($item['discount']) : 0;
            $discountType = isset($item['discountType']) ? mysqli_real_escape_string($con, $item['discountType']) : 'PERCENTAGE';
            
            // Calculate discount amount based on type
            $discountAmount = 0;
            if ($discount > 0) {
                if ($discountType === 'PERCENTAGE') {
                    $discountAmount = ($rate * $quantity * $discount) / 100;
                } else {
                    $discountAmount = $discount;
                }
            }
            
            // Calculate final rate and final total
            $itemSubtotal = $rate * $quantity;
            $finalTotal = $itemSubtotal - $discountAmount;
            $finalRate = $quantity > 0 ? ($finalTotal / $quantity) : $rate;
            
            // If ProductID is 0 or missing, fetch it from items table
            if ($productId == 0) {
                $productQuery = "SELECT ID FROM tblitems WHERE ItemCode = '$productCode' LIMIT 1";
                $productResult = mysqli_query($con, $productQuery);
                if ($productResult && mysqli_num_rows($productResult) > 0) {
                    $productRow = mysqli_fetch_assoc($productResult);
                    $productId = intval($productRow['ID']);
                } else {
                    // If product not found, create a default entry or skip
                    $productId = 0;
                }
            }
            
            $detailSql = "INSERT INTO tblsales_details 
                         (SaleID, ProductID, ProductCode, ProductName, Rate, PurchaseRate, Quantity, 
                          Discount, DiscountType, DiscountAmount, FinalRate, FinalTotal, Amount) 
                         VALUES 
                         ($saleId, $productId, '$productCode', '$productName', $rate, $purchaseRate, $quantity, 
                          $discount, '$discountType', $discountAmount, $finalRate, $finalTotal, $amount)";
            
            if (!mysqli_query($con, $detailSql)) {
                throw new Exception('Error inserting sale detail: ' . mysqli_error($con));
            }
            
            // Stock Management based on transaction type
            // For SALES: DECREASE stock (goods going out)
            // For RETURNS: INCREASE stock (goods coming back)
            if ($productId > 0 && $quantity > 0 && !$isQuotation) {
                $stockOperation = $isReturn ? '+' : '-';
                $updateStockSql = "UPDATE tblitems 
                                  SET CurrentStock = CurrentStock $stockOperation $quantity 
                                  WHERE ID = $productId";
                if (!mysqli_query($con, $updateStockSql)) {
                    throw new Exception('Error updating stock for product ID ' . $productId . ': ' . mysqli_error($con));
                }
            }
            
            // Insert GST details if available
            if (isset($item['gstDetails'])) {
                $gstDetails = $item['gstDetails'];
                $hsnCode = isset($gstDetails['hsnCode']) ? mysqli_real_escape_string($con, $gstDetails['hsnCode']) : '';
                $taxableAmount = isset($gstDetails['taxableAmount']) ? floatval($gstDetails['taxableAmount']) : $finalTotal;
                $taxPercentage = isset($gstDetails['taxPercentage']) ? floatval($gstDetails['taxPercentage']) : 0;
                $taxType = isset($gstDetails['taxType']) ? mysqli_real_escape_string($con, $gstDetails['taxType']) : 'INTRA';
                $cgstPercent = isset($gstDetails['cgstPercent']) ? floatval($gstDetails['cgstPercent']) : 0;
                $cgstAmount = isset($gstDetails['cgstAmount']) ? floatval($gstDetails['cgstAmount']) : 0;
                $sgstPercent = isset($gstDetails['sgstPercent']) ? floatval($gstDetails['sgstPercent']) : 0;
                $sgstAmount = isset($gstDetails['sgstAmount']) ? floatval($gstDetails['sgstAmount']) : 0;
                $igstPercent = isset($gstDetails['igstPercent']) ? floatval($gstDetails['igstPercent']) : 0;
                $igstAmount = isset($gstDetails['igstAmount']) ? floatval($gstDetails['igstAmount']) : 0;
                $totalTaxAmount = $cgstAmount + $sgstAmount + $igstAmount;
                $gstFinalAmount = $taxableAmount + $totalTaxAmount;
                
                $gstSql = "INSERT INTO tblsales_gst_details
                          (SaleID, InvoiceNumber, ProductID, ProductCode, ProductName, HSNCode, Rate, Quantity,
                           DiscountAmount, TaxableAmount, TaxPercentage, TaxType,
                           CGSTPercent, CGSTAmount, SGSTPercent, SGSTAmount, IGSTPercent, IGSTAmount,
                           TotalTaxAmount, FinalAmount)
                          VALUES
                          ($saleId, '$invoiceNumber', $productId, '$productCode', '$productName', '$hsnCode', $rate, $quantity,
                           $discountAmount, $taxableAmount, $taxPercentage, '$taxType',
                           $cgstPercent, $cgstAmount, $sgstPercent, $sgstAmount, $igstPercent, $igstAmount,
                           $totalTaxAmount, $gstFinalAmount)";
                
                mysqli_query($con, $gstSql); // Don't throw error if GST insert fails
            }
        }
        
        // Handle payment if amount is provided
    if ($paymentAmount > 0 && $creditAmount > 0 && !$isReturn && !$isQuotation) {
            // Generate payment reference number
            $paymentRef = 'PAY-' . $invoiceNumber;
            
            // Insert payment into customer account as credit (Credit)
            $paymentAccountSql = "INSERT INTO tblcustomer_accounts 
                                  (CustomerID, InvoiceNumber, TransactionType, TransactionDate, Amount, BalanceAmount, Description, PaymentMode, ReferenceNumber, AddedBy) 
                                  VALUES 
                                  ($customerId, '$paymentRef', 'Credit', '$saleDate', $paymentAmount, $paymentAmount, 'Payment for Bill $invoiceNumber', '$paymentMode', '$invoiceNumber', $userId)";
            
            if (!mysqli_query($con, $paymentAccountSql)) {
                throw new Exception('Error inserting payment account: ' . mysqli_error($con));
            }
            
            // Update customer balance - reduce by payment amount
            $updatePaymentBalanceSql = "UPDATE tblcustomers 
                                        SET CurrentBalance = CurrentBalance - $paymentAmount 
                                        WHERE ID = $customerId";
            
            if (!mysqli_query($con, $updatePaymentBalanceSql)) {
                throw new Exception('Error updating customer balance for payment: ' . mysqli_error($con));
            }
        }
        
        // Commit transaction
        mysqli_commit($con);
        
        // Re-enable autocommit
        mysqli_autocommit($con, true);
        
        // Verify the sale was saved by reading it back
        $verifySql = "SELECT ID, InvoiceNumber FROM tblsales WHERE ID = $saleId AND InvoiceNumber = '$invoiceNumber'";
        $verifyResult = mysqli_query($con, $verifySql);
        if (!$verifyResult || mysqli_num_rows($verifyResult) == 0) {
            throw new Exception('Sale verification failed - record not found after commit');
        }
        
        // ========== LOYALTY POINTS CALCULATION ==========
        // Calculate and award loyalty points for this sale
        // Skip loyalty points for Walk-in Customer
        $skipLoyalty = false;
        $customerQuery = "SELECT CustomerName FROM tblcustomers WHERE ID = $customerId";
        $customerResult = mysqli_query($con, $customerQuery);
        if ($customerResult && mysqli_num_rows($customerResult) > 0) {
            $customerRow = mysqli_fetch_assoc($customerResult);
            $customerName = strtolower($customerRow['CustomerName']);
            // Skip if customer name contains "walk" and "in"
            if (strpos($customerName, 'walk') !== false && strpos($customerName, 'in') !== false) {
                $skipLoyalty = true;
            }
        }
        
    if (!$skipLoyalty && !$isQuotation && $customerId > 0 && $totalAmount > 0) {
            // Get loyalty settings
            $settingsQuery = "SELECT SettingKey, SettingValue FROM tblsettings_loyalty WHERE SettingKey = 'points_per_100'";
            $settingsResult = mysqli_query($con, $settingsQuery);
            $pointsPer100 = 1; // Default
            
            if ($settingsResult && mysqli_num_rows($settingsResult) > 0) {
                $settingRow = mysqli_fetch_assoc($settingsResult);
                $pointsPer100 = floatval($settingRow['SettingValue']);
            }
            
            // Calculate points earned (Amount ÷ 100 × Points per 100)
            $pointsEarned = floor(($totalAmount / 100) * $pointsPer100);
            
            if ($pointsEarned > 0) {
                // Get current balance
                $balanceQuery = "SELECT LoyaltyPoints FROM tblcustomers WHERE ID = $customerId";
                $balanceResult = mysqli_query($con, $balanceQuery);
                $currentBalance = 0;
                if ($balanceResult && mysqli_num_rows($balanceResult) > 0) {
                    $balanceRow = mysqli_fetch_assoc($balanceResult);
                    $currentBalance = floatval($balanceRow['LoyaltyPoints']);
                }
                
                $newBalance = $currentBalance + $pointsEarned;
                
                // Update customer loyalty points and total earned
                $updatePointsSql = "UPDATE tblcustomers 
                                   SET LoyaltyPoints = $newBalance,
                                       TotalPointsEarned = TotalPointsEarned + $pointsEarned 
                                   WHERE ID = $customerId";
                mysqli_query($con, $updatePointsSql);
                
                // Log the transaction in tblcustomer_loyalty_points
                $logPointsSql = "INSERT INTO tblcustomer_loyalty_points 
                                (CustomerID, TransactionType, Points, SaleID, Reference, Notes, CurrentBalance, CreatedBy) 
                                VALUES 
                                ($customerId, 'EARNED', $pointsEarned, $saleId, '$invoiceNumber', 'Points earned from sale', $newBalance, $userId)";
                mysqli_query($con, $logPointsSql);
                
                $response['loyaltyPoints'] = [
                    'earned' => $pointsEarned,
                    'newBalance' => $newBalance
                ];
            }
        }
        // ========== END LOYALTY POINTS ==========
        
        // ========== POINTS REDEMPTION (DISCOUNT) ==========
        // If customer used points for discount, deduct them
        // Skip for Walk-in Customer
    if (!$skipLoyalty && !$isQuotation && $redeemedPoints > 0 && $billDiscountType === 'POINTS') {
            // Get current loyalty points
            $pointsQuery = "SELECT LoyaltyPoints FROM tblcustomers WHERE ID = $customerId";
            $pointsResult = mysqli_query($con, $pointsQuery);
            if ($pointsResult && mysqli_num_rows($pointsResult) > 0) {
                $pointsRow = mysqli_fetch_assoc($pointsResult);
                $currentPoints = floatval($pointsRow['LoyaltyPoints']);
                
                if ($currentPoints >= $redeemedPoints) {
                    $newPointsBalance = $currentPoints - $redeemedPoints;
                    
                    // Update customer points - deduct redeemed points
                    $updatePointsSql = "UPDATE tblcustomers 
                                       SET LoyaltyPoints = $newPointsBalance,
                                           TotalPointsRedeemed = TotalPointsRedeemed + $redeemedPoints 
                                       WHERE ID = $customerId";
                    mysqli_query($con, $updatePointsSql);
                    
                    // Log redemption transaction
                    $logRedemptionSql = "INSERT INTO tblcustomer_loyalty_points 
                                        (CustomerID, TransactionType, Points, SaleID, Reference, Notes, CurrentBalance, CreatedBy) 
                                        VALUES 
                                        ($customerId, 'REDEEMED', $redeemedPoints, $saleId, '$invoiceNumber', 'Points redeemed for discount', $newPointsBalance, $userId)";
                    mysqli_query($con, $logRedemptionSql);
                    
                    $response['pointsRedeemed'] = [
                        'points' => $redeemedPoints,
                        'newBalance' => $newPointsBalance
                    ];
                } else {
                    throw new Exception('Insufficient loyalty points for redemption');
                }
            }
        }
        // ========== END POINTS REDEMPTION ==========
        
        $response['success'] = true;
        $response['message'] = ($editingSaleId > 0 ? 'Sale updated' : 'Sale saved') . ' successfully';
        $response['saleId'] = $saleId;
        $response['invoiceNumber'] = $invoiceNumber;
        
    } catch (Exception $e) {
        // Rollback transaction
        mysqli_rollback($con);
        $response['message'] = 'Database Error: ' . $e->getMessage();
        $response['debug'] = [
            'invoice' => isset($invoiceNumber) ? $invoiceNumber : 'N/A',
            'customerId' => isset($customerId) ? $customerId : 'N/A',
            'editMode' => isset($editingSaleId) ? $editingSaleId : 0
        ];
    }
} else {
    $response['message'] = 'Invalid data received or JSON decode failed';
    $response['debug'] = $jsonData;
}

echo json_encode($response);
?>
