<?php
session_start();
require_once '../../assets/db_connect.php';

header('Content-Type: application/json');

if (!isset($_SESSION['user']['id'])) {
    echo json_encode(['status' => 'error', 'message' => 'Unauthorized']);
    exit();
}

try {
    $pdo->beginTransaction();
    
    // Get form data from POS form
    $product_id = $_POST['product_id'] ?? 0;
    $customer_name = trim($_POST['customer_name'] ?? '');
    $invoice_no = $_POST['invoice_no'] ?? '';
    $sale_date = $_POST['sale_date'] ?? date('Y-m-d H:i:s');
    $qty = $_POST['qty'] ?? 0;
    $unit_price = $_POST['unit_price'] ?? 0;
    $discount = $_POST['discount'] ?? 0;
    $batch_id = $_POST['batch_id'] ?? null;
    $payment_method = $_POST['payment_method'] ?? 'CASH';
    $amount_paid = $_POST['amount_paid'] ?? 0;
    $shop_id = $_POST['shop_id'] ?? $_SESSION['user']['shop_id'];
    $created_by = $_POST['created_by'] ?? $_SESSION['user']['id'];
    $vat_percent = $_POST['vat_percent'] ?? 0;
    
    // Convert to proper types
    $product_id = intval($product_id);
    $qty = intval($qty);
    $unit_price = floatval($unit_price);
    $discount = floatval($discount);
    $amount_paid = floatval($amount_paid);
    $vat_percent = floatval($vat_percent);
    
    // Validation
    if ($product_id <= 0) {
        throw new Exception('Invalid product selected');
    }
    
    if ($qty <= 0) {
        throw new Exception('Quantity must be greater than 0');
    }
    
    if ($unit_price <= 0) {
        throw new Exception('Unit price must be greater than 0');
    }
    
    if ($amount_paid <= 0) {
        throw new Exception('Amount paid must be greater than 0');
    }
    
    // Check total product stock first
    $total_stock_sql = "SELECT 
                           COALESCE(SUM(
                               CASE 
                                   WHEN im.movement_type IN ('PURCHASE', 'RETURN') THEN im.change_qty
                                   WHEN im.movement_type IN ('SALE', 'ADJUSTMENT') THEN -im.change_qty
                                   ELSE 0
                               END
                           ), 0) as total_stock
                        FROM inventory_movements im
                        JOIN product_batches pb ON im.batch_id = pb.id
                        WHERE pb.product_id = ? 
                        AND pb.shop_id = ?
                        AND pb.is_active = 1";
    
    $total_stmt = $pdo->prepare($total_stock_sql);
    $total_stmt->execute([$product_id, $shop_id]);
    $total_stock = $total_stmt->fetchColumn();
    $total_stock = intval($total_stock);
    
    if ($total_stock < $qty) {
        throw new Exception('Insufficient total stock. Available: ' . $total_stock . '. Requested: ' . $qty);
    }
    
    // Calculate totals
    $subtotal = $qty * $unit_price;
    $discount_amount = $subtotal * ($discount / 100);
    $total_before_vat = $subtotal - $discount_amount;
    
    $vat_amount = $total_before_vat * ($vat_percent / 100);
    $total = $total_before_vat + $vat_amount;
    
    // Check if auto-selecting batch (FIFO)
    if (!$batch_id || $batch_id == '' || $batch_id == 'null') {
        // Get oldest batch with sufficient positive stock
        $batch_sql = "SELECT pb.id 
                      FROM product_batches pb
                      WHERE pb.product_id = ? 
                      AND pb.shop_id = ?
                      AND pb.is_active = 1
                      AND (
                          SELECT COALESCE(SUM(
                              CASE 
                                  WHEN im.movement_type IN ('PURCHASE', 'RETURN') THEN im.change_qty
                                  WHEN im.movement_type IN ('SALE', 'ADJUSTMENT') THEN -im.change_qty
                                  ELSE 0 
                              END
                          ), 0) 
                          FROM inventory_movements im 
                          WHERE im.batch_id = pb.id
                      ) >= ?
                      ORDER BY pb.expiry_date ASC 
                      LIMIT 1";
        
        $batch_stmt = $pdo->prepare($batch_sql);
        $batch_stmt->execute([$product_id, $shop_id, $qty]);
        $batch_id = $batch_stmt->fetchColumn();
        
        if (!$batch_id) {
            // Check if stock is available across multiple batches
            $available_batches_sql = "SELECT 
                                         pb.id,
                                         pb.batch_no,
                                         COALESCE(SUM(
                                             CASE 
                                                 WHEN im.movement_type IN ('PURCHASE', 'RETURN') THEN im.change_qty
                                                 WHEN im.movement_type IN ('SALE', 'ADJUSTMENT') THEN -im.change_qty
                                                 ELSE 0
                                             END
                                         ), 0) as batch_stock
                                      FROM product_batches pb
                                      LEFT JOIN inventory_movements im ON pb.id = im.batch_id
                                      WHERE pb.product_id = ? 
                                      AND pb.shop_id = ?
                                      AND pb.is_active = 1
                                      GROUP BY pb.id, pb.batch_no
                                      HAVING batch_stock > 0
                                      ORDER BY pb.expiry_date ASC";
            
            $available_stmt = $pdo->prepare($available_batches_sql);
            $available_stmt->execute([$product_id, $shop_id]);
            $available_batches = $available_stmt->fetchAll(PDO::FETCH_ASSOC);
            
            $batch_list = array_map(function($b) {
                return $b['batch_no'] . ' (' . $b['batch_stock'] . ' left)';
            }, $available_batches);
            
            $batch_info = implode(', ', $batch_list);
            
            throw new Exception('No single batch has sufficient stock. Available batches: ' . $batch_info . '. Please select specific batch.');
        }
    }
    
    // Verify selected batch has enough stock
    $batch_stock_sql = "SELECT 
                           COALESCE(SUM(
                               CASE 
                                   WHEN im.movement_type IN ('PURCHASE', 'RETURN') THEN im.change_qty
                                   WHEN im.movement_type IN ('SALE', 'ADJUSTMENT') THEN -im.change_qty
                                   ELSE 0
                               END
                           ), 0) as batch_stock
                        FROM inventory_movements im 
                        WHERE im.batch_id = ?";
    
    $batch_stock_stmt = $pdo->prepare($batch_stock_sql);
    $batch_stock_stmt->execute([$batch_id]);
    $batch_stock = $batch_stock_stmt->fetchColumn();
    $batch_stock = intval($batch_stock);
    
    if ($batch_stock < $qty) {
        // Get batch details for error message
        $batch_details_sql = "SELECT batch_no FROM product_batches WHERE id = ?";
        $batch_details_stmt = $pdo->prepare($batch_details_sql);
        $batch_details_stmt->execute([$batch_id]);
        $batch_info = $batch_details_stmt->fetch(PDO::FETCH_ASSOC);
        
        $batch_name = $batch_info ? $batch_info['batch_no'] : 'Batch #' . $batch_id;
        throw new Exception('Selected batch "' . $batch_name . '" has insufficient stock. Available: ' . $batch_stock . '. Requested: ' . $qty);
    }
    
    // Also verify batch belongs to this product and shop
    $batch_check_sql = "SELECT id FROM product_batches WHERE id = ? AND product_id = ? AND shop_id = ?";
    $batch_check_stmt = $pdo->prepare($batch_check_sql);
    $batch_check_stmt->execute([$batch_id, $product_id, $shop_id]);
    
    if (!$batch_check_stmt->fetch()) {
        throw new Exception('Invalid batch selected for this product');
    }
    
    // Handle customer - Save to customers table and get ID
    $customer_id = null;
    if (!empty($customer_name)) {
        // Check if customer already exists with this name (case-insensitive)
        $customer_check_sql = "SELECT id FROM customers WHERE LOWER(name) = LOWER(?) LIMIT 1";
        $customer_check_stmt = $pdo->prepare($customer_check_sql);
        $customer_check_stmt->execute([$customer_name]);
        $existing_customer = $customer_check_stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($existing_customer && isset($existing_customer['id'])) {
            // Customer exists, use existing ID
            $customer_id = $existing_customer['id'];
        } else {
            // Create new customer
            $customer_sql = "INSERT INTO customers (name, shop_id) VALUES (?, ?)";
            $customer_stmt = $pdo->prepare($customer_sql);
            $success = $customer_stmt->execute([$customer_name, $shop_id]);
            
            if ($success) {
                $customer_id = $pdo->lastInsertId();
            } else {
                // If customer creation fails, log error but continue with null customer_id
                error_log("Failed to create customer: " . $customer_name);
            }
        }
    }
    
    // Create sale record with customer_id (NULL for walk-in)
    $sale_sql = "INSERT INTO sales (invoice_no, sale_date, customer_id, total, paid, 
                 status, created_by, shop_id) 
                 VALUES (?, ?, ?, ?, ?, 'paid', ?, ?)";
    
    $sale_stmt = $pdo->prepare($sale_sql);
    $sale_stmt->execute([
        $invoice_no, 
        $sale_date, 
        $customer_id,
        $total, 
        $amount_paid, 
        $created_by, 
        $shop_id
    ]);
    
    $sale_id = $pdo->lastInsertId();
    
    // Create sale line
    $line_sql = "INSERT INTO sale_lines (sale_id, product_id, batch_id, qty, unit_price, 
                 discount, line_total, shop_id) 
                 VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
    
    $line_stmt = $pdo->prepare($line_sql);
    $line_stmt->execute([
        $sale_id, 
        $product_id, 
        $batch_id, 
        $qty, 
        $unit_price, 
        $discount, 
        $total_before_vat, 
        $shop_id
    ]);
    
    // Create inventory movement (reduce stock)
    $movement_sql = "INSERT INTO inventory_movements (product_id, batch_id, change_qty, 
                     movement_type, reference_id, created_by, shop_id) 
                     VALUES (?, ?, ?, 'SALE', ?, ?, ?)";
    
    $movement_stmt = $pdo->prepare($movement_sql);
    $movement_stmt->execute([
        $product_id, 
        $batch_id, 
        $qty, 
        $sale_id, 
        $created_by, 
        $shop_id
    ]);
    
    // Create payment record
    $payment_sql = "INSERT INTO payments (sale_id, amount, method, created_by, shop_id) 
                    VALUES (?, ?, ?, ?, ?)";
    
    $payment_stmt = $pdo->prepare($payment_sql);
    $payment_stmt->execute([
        $sale_id, 
        $amount_paid, 
        $payment_method, 
        $created_by, 
        $shop_id
    ]);
    
    $pdo->commit();
    
    // Prepare response
    $response = [
        'status' => 'success',
        'message' => 'Sale completed successfully!',
        'sale_id' => $sale_id,
        'invoice_no' => $invoice_no,
        'total' => number_format($total, 2),
        'change' => number_format($amount_paid - $total, 2),
        'customer_name' => !empty($customer_name) ? $customer_name : 'Walk-in Customer',
        'customer_id' => $customer_id
    ];
    
    echo json_encode($response);
    
} catch (Exception $e) {
    $pdo->rollBack();
    http_response_code(400);
    echo json_encode([
        'status' => 'error',
        'message' => $e->getMessage()
    ]);
}

// Helper function to get available stock
function getAvailableStock($product_id, $shop_id) {
    global $pdo;
    
    $sql = "SELECT 
                COALESCE(SUM(
                    CASE 
                        WHEN movement_type IN ('PURCHASE', 'RETURN') THEN change_qty
                        WHEN movement_type IN ('SALE', 'ADJUSTMENT') THEN -change_qty
                        ELSE 0
                    END
                ), 0) as total_stock
            FROM inventory_movements im
            JOIN product_batches pb ON im.batch_id = pb.id
            WHERE pb.product_id = ? 
            AND pb.shop_id = ?
            AND pb.is_active = 1";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$product_id, $shop_id]);
    $stock = $stmt->fetchColumn() ?? 0;
    return intval($stock);
}
?>