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

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

// Log function for debugging
function logDebug($message) {
    $logFile = __DIR__ . '/sale_debug.log';
    $timestamp = date('Y-m-d H:i:s');
    file_put_contents($logFile, "[$timestamp] $message\n", FILE_APPEND);
}

// Function to generate unique invoice number
function generateUniqueInvoiceNumber($pdo, $shop_id, $proposed_invoice = null) {
    $prefix = 'SHOP' . $shop_id . '-' . date('Ymd') . '-';
    
    // If client provided invoice, check if it's unique
    if ($proposed_invoice) {
        $check_sql = "SELECT COUNT(*) as cnt FROM sales WHERE invoice_no = ?";
        $check_stmt = $pdo->prepare($check_sql);
        $check_stmt->execute([$proposed_invoice]);
        $result = $check_stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($result['cnt'] == 0) {
            return $proposed_invoice; // Use client's invoice if unique
        }
        // If not unique, we'll generate a new one with suffix
    }
    
    // Generate new unique invoice number
    $counter = 1;
    $max_attempts = 100;
    
    while ($counter <= $max_attempts) {
        $invoice_no = $prefix . str_pad($counter, 3, '0', STR_PAD_LEFT);
        
        // Check if this invoice exists
        $check_sql = "SELECT COUNT(*) as cnt FROM sales WHERE invoice_no = ?";
        $check_stmt = $pdo->prepare($check_sql);
        $check_stmt->execute([$invoice_no]);
        $result = $check_stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($result['cnt'] == 0) {
            return $invoice_no;
        }
        
        $counter++;
    }
    
    // If all attempts fail, use timestamp-based invoice
    return $prefix . time();
}

// Function to get user-friendly error messages
function getUserFriendlyError($error_code, $error_message) {
    $errors = [
        '23000' => 'Duplicate entry detected. A record with the same details already exists.',
        '22001' => 'Data too long for one of the fields.',
        '22003' => 'Numeric value out of range.',
        '22007' => 'Invalid date/time format.',
        '42000' => 'Syntax error in SQL statement.',
        'HY000' => 'General database error.',
        'negative_stock' => 'Insufficient stock available. Please check quantities.',
        'invalid_data' => 'Invalid data provided. Please check all fields.'
    ];
    
    // Extract error code if it's in the message
    if (preg_match('/SQLSTATE\[(\w+)\]/', $error_message, $matches)) {
        $sql_error_code = $matches[1];
        if (isset($errors[$sql_error_code])) {
            return $errors[$sql_error_code];
        }
    }
    
    // Check for specific MySQL error numbers
    if (preg_match('/1062/', $error_message)) {
        return 'This invoice number already exists. System generated a new unique invoice.';
    }
    
    if (preg_match('/1452/', $error_message)) {
        return 'Invalid reference (e.g., customer or product does not exist).';
    }
    
    if (preg_match('/1366/', $error_message)) {
        return 'Invalid data format. Please check all input fields.';
    }
    
    // Return original message for unknown errors with cleanup
    $clean_message = preg_replace('/SQLSTATE\[\w+\]/', '', $error_message);
    $clean_message = trim($clean_message, ' []');
    
    return $clean_message ?: 'An unexpected error occurred. Please try again.';
}

logDebug("=== SALE SAVE REQUEST STARTED ===");

try {
    // Get the raw POST data
    $input = file_get_contents('php://input');
    logDebug("Raw input length: " . strlen($input));
    
    if (empty($input)) {
        logDebug("ERROR: Empty input received");
        throw new Exception('No data received. Please refresh and try again.');
    }
    
    // Decode JSON
    $data = json_decode($input, true);
    
    if (json_last_error() !== JSON_ERROR_NONE) {
        logDebug("JSON decode error: " . json_last_error_msg());
        throw new Exception('Invalid data format. Please try again.');
    }
    
    if (!$data) {
        logDebug("ERROR: Data is null after decode");
        throw new Exception('No data provided.');
    }
    
    logDebug("JSON decoded successfully");
    logDebug("Invoice: " . ($data['invoice_no'] ?? 'N/A'));
    logDebug("Customer: " . ($data['customer_name'] ?? 'N/A'));
    logDebug("Total: " . ($data['total'] ?? 0));
    logDebug("Item count: " . (isset($data['items']) ? count($data['items']) : 0));
    
    // Validate required fields
    $required = ['shop_id', 'created_by', 'total', 'items'];
    foreach ($required as $field) {
        if (!isset($data[$field])) {
            logDebug("MISSING FIELD: $field");
            throw new Exception("Please fill all required fields. Missing: " . ucfirst(str_replace('_', ' ', $field)));
        }
    }
    
    logDebug("All required fields present");
    
    // Generate or validate invoice number
    $invoice_no = generateUniqueInvoiceNumber(
        $pdo, 
        (int)$data['shop_id'], 
        $data['invoice_no'] ?? null
    );
    
    if ($invoice_no !== ($data['invoice_no'] ?? null)) {
        logDebug("Generated new invoice number: $invoice_no (original was duplicate)");
    } else {
        logDebug("Using provided invoice number: $invoice_no");
    }
    
    // Start transaction
    $pdo->beginTransaction();
    logDebug("Database transaction started");
    
    // Handle customer
    $customer_id = null;
    if (!empty($data['customer_id']) && $data['customer_id'] != 'null' && $data['customer_id'] != '') {
        $customer_id = $data['customer_id'];
        logDebug("Using existing customer ID: $customer_id");
    } elseif (!empty($data['customer_name'])) {
        $customer_name = trim($data['customer_name']);
        logDebug("Processing customer name: $customer_name");
        
        // Check if customer exists
        $check_sql = "SELECT id FROM customers WHERE name = ? AND shop_id = ? LIMIT 1";
        $check_stmt = $pdo->prepare($check_sql);
        $check_stmt->execute([$customer_name, $data['shop_id']]);
        $existing = $check_stmt->fetch(PDO::FETCH_ASSOC);
        
        if ($existing && !empty($existing['id'])) {
            $customer_id = $existing['id'];
            logDebug("Found existing customer ID: $customer_id");
        } else {
            // Create new customer
            $insert_sql = "INSERT INTO customers (name, shop_id, created_at) VALUES (?, ?, NOW())";
            $insert_stmt = $pdo->prepare($insert_sql);
            if ($insert_stmt->execute([$customer_name, $data['shop_id']])) {
                $customer_id = $pdo->lastInsertId();
                logDebug("Created new customer with ID: $customer_id");
            } else {
                $error = $insert_stmt->errorInfo();
                $error_msg = getUserFriendlyError($error[0], $error[2]);
                throw new Exception("Failed to create customer: $error_msg");
            }
        }
    }
    
    // 1. Create sale record with retry mechanism for duplicates
    $sale_sql = "INSERT INTO sales (invoice_no, sale_date, customer_id, total, paid, status, created_by, shop_id) 
                 VALUES (?, NOW(), ?, ?, ?, ?, ?, ?)";
    
    $sale_stmt = $pdo->prepare($sale_sql);
    $sale_params = [
        $invoice_no,
        $customer_id,
        (float)$data['total'],
        (float)($data['paid'] ?? 0),
        $data['status'] ?? 'open',
        (int)$data['created_by'],
        (int)$data['shop_id']
    ];
    
    logDebug("Sale params: " . json_encode($sale_params));
    
    try {
        if (!$sale_stmt->execute($sale_params)) {
            $error = $sale_stmt->errorInfo();
            logDebug("Failed to create sale: " . json_encode($error));
            $error_msg = getUserFriendlyError($error[0], $error[2]);
            throw new Exception("Failed to create sale: $error_msg");
        }
    } catch (PDOException $e) {
        // If duplicate invoice error, generate new one and retry
        if (strpos($e->getMessage(), '1062') !== false || strpos($e->getMessage(), '23000') !== false) {
            logDebug("Duplicate invoice detected, generating new one...");
            
            // Generate new unique invoice
            $invoice_no = generateUniqueInvoiceNumber($pdo, (int)$data['shop_id']);
            $sale_params[0] = $invoice_no;
            
            // Retry with new invoice
            if (!$sale_stmt->execute($sale_params)) {
                $error = $sale_stmt->errorInfo();
                $error_msg = getUserFriendlyError($error[0], $error[2]);
                throw new Exception("Failed to create sale after retry: $error_msg");
            }
            logDebug("Sale created with new invoice: $invoice_no");
        } else {
            throw $e; // Re-throw other errors
        }
    }
    
    $sale_id = $pdo->lastInsertId();
    logDebug("Sale created successfully. ID: $sale_id, Invoice: $invoice_no");
    
    // 2. Process items with stock validation
    $item_count = 0;
    if (!is_array($data['items']) || empty($data['items'])) {
        throw new Exception("Cart is empty. Please add items before saving.");
    }
    
    foreach ($data['items'] as $item) {
        $item_count++;
        
        logDebug("Processing item $item_count: Product=" . ($item['product_id'] ?? 'N/A') . 
                ", Batch=" . ($item['batch_id'] ?? 'N/A') . 
                ", Qty=" . ($item['qty'] ?? 'N/A'));
        
        // Validate item
        if (empty($item['product_id']) || empty($item['batch_id']) || empty($item['qty'])) {
            throw new Exception("Invalid item data at position $item_count. Please check all item details.");
        }
        
        // Check stock availability (optional - can be handled by trigger)
        $check_stock_sql = "SELECT pb.qty, p.name 
                           FROM product_batches pb 
                           JOIN products p ON pb.product_id = p.id 
                           WHERE pb.id = ? AND pb.is_active = 1";
        $check_stmt = $pdo->prepare($check_stock_sql);
        $check_stmt->execute([$item['batch_id']]);
        $batch = $check_stmt->fetch(PDO::FETCH_ASSOC);
        
        if (!$batch) {
            throw new Exception("Product batch not found or inactive.");
        }
        
        if ($batch['qty'] < $item['qty']) {
            throw new Exception("Insufficient stock for {$batch['name']}. Available: {$batch['qty']}, Requested: {$item['qty']}");
        }
        
        // Create sale line
        $line_sql = "INSERT INTO sale_lines (sale_id, product_id, batch_id, qty, unit_price, line_total, shop_id) 
                     VALUES (?, ?, ?, ?, ?, ?, ?)";
        $line_stmt = $pdo->prepare($line_sql);
        $line_params = [
            $sale_id,
            (int)$item['product_id'],
            (int)$item['batch_id'],
            (int)$item['qty'],
            (float)$item['unit_price'],
            (float)$item['line_total'],
            (int)$data['shop_id']
        ];
        
        logDebug("Line params: " . json_encode($line_params));
        
        if (!$line_stmt->execute($line_params)) {
            $error = $line_stmt->errorInfo();
            logDebug("Failed to create sale line: " . json_encode($error));
            $error_msg = getUserFriendlyError($error[0], $error[2]);
            throw new Exception("Failed to add item: $error_msg");
        }
        logDebug("Sale line created");
        
        // Create inventory movement
        $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_params = [
            (int)$item['product_id'],
            (int)$item['batch_id'],
            (int)$item['qty'],
            $sale_id,
            (int)$data['created_by'],
            (int)$data['shop_id']
        ];
        
        logDebug("Movement params: " . json_encode($movement_params));
        
        if (!$movement_stmt->execute($movement_params)) {
            $error = $movement_stmt->errorInfo();
            logDebug("Failed to create inventory movement: " . json_encode($error));
            $error_msg = getUserFriendlyError($error[0], $error[2]);
            throw new Exception("Failed to update inventory: $error_msg");
        }
        logDebug("Inventory movement created");
    }
    
    logDebug("Processed $item_count items");
    
    // 3. Create payment if paid > 0
    $paid = (float)($data['paid'] ?? 0);
    if ($paid > 0) {
        $payment_sql = "INSERT INTO payments (sale_id, amount, method, created_by, shop_id, paid_at) 
                        VALUES (?, ?, ?, ?, ?, NOW())";
        $payment_stmt = $pdo->prepare($payment_sql);
        $payment_params = [
            $sale_id,
            $paid,
            $data['payment_method'] ?? 'CASH',
            (int)$data['created_by'],
            (int)$data['shop_id']
        ];
        
        logDebug("Payment params: " . json_encode($payment_params));
        
        if (!$payment_stmt->execute($payment_params)) {
            $error = $payment_stmt->errorInfo();
            logDebug("Warning: Failed to create payment: " . json_encode($error));
            // Don't throw - payment failure shouldn't rollback entire sale
            // Just log it
        } else {
            logDebug("Payment record created");
        }
    }
    
    // Commit transaction
    $pdo->commit();
    logDebug("Transaction committed successfully");
    
    $response = [
        'status' => 'success',
        'message' => 'Sale saved successfully!',
        'sale_id' => $sale_id,
        'invoice_no' => $invoice_no,
        'customer_id' => $customer_id,
        'invoice_number' => $invoice_no, // For compatibility
        'sale_date' => date('Y-m-d H:i:s')
    ];
    
    logDebug("Sending success response: " . json_encode($response));
    logDebug("=== SALE SAVE REQUEST COMPLETED SUCCESSFULLY ===\n");
    
    echo json_encode($response);
    
} catch (Exception $e) {
    // Rollback on error
    if (isset($pdo) && $pdo->inTransaction()) {
        try {
            $pdo->rollBack();
            logDebug("Transaction rolled back due to error");
        } catch (Exception $rollback_error) {
            logDebug("Rollback error: " . $rollback_error->getMessage());
        }
    }
    
    $error_msg = $e->getMessage();
    logDebug("ERROR: $error_msg");
    logDebug("=== SALE SAVE REQUEST FAILED ===\n");
    
    // Clean up technical error messages for user
    $user_message = $error_msg;
    
    // Remove PDO/SQL technical details
    $user_message = preg_replace('/SQLSTATE\[\w+\].*?: /', '', $user_message);
    $user_message = preg_replace('/Failed to (create|add|update).*?: /i', '', $user_message);
    
    $response = [
        'status' => 'error',
        'message' => trim($user_message),
        'technical_error' => $error_msg // Include for debugging
    ];
    
    echo json_encode($response);
}