<?php
use Magento\Framework\App\Bootstrap;
require __DIR__ . '/app/bootstrap.php';

$bootstrap = Bootstrap::create(BP, $_SERVER);
$objectManager = $bootstrap->getObjectManager();

$state = $objectManager->get(\Magento\Framework\App\State::class);
$state->setAreaCode('adminhtml');

$connection = $objectManager->get(\Magento\Framework\App\ResourceConnection::class)->getConnection();

$quoteId = 394141;

// Check quote details
$sql = "SELECT 
    q.entity_id,
    q.is_active,
    q.reserved_order_id,
    q.created_at,
    q.updated_at,
    q.customer_email,
    q.customer_is_guest,
    q.items_count,
    q.store_id
FROM quote q
WHERE q.entity_id = :quote_id";

$quote = $connection->fetchRow($sql, ['quote_id' => $quoteId]);

if ($quote) {
    echo "Quote Details:" . PHP_EOL;
    echo "=============" . PHP_EOL;
    echo "Quote ID: " . $quote['entity_id'] . PHP_EOL;
    echo "Is Active: " . ($quote['is_active'] ? 'Yes' : 'No') . PHP_EOL;
    echo "Is Guest: " . ($quote['customer_is_guest'] ? 'Yes' : 'No') . PHP_EOL;
    echo "Items Count: " . $quote['items_count'] . PHP_EOL;
    echo "Store ID: " . $quote['store_id'] . PHP_EOL;
    echo "Reserved Order ID: " . ($quote['reserved_order_id'] ?: 'None') . PHP_EOL;
    echo "Created: " . $quote['created_at'] . PHP_EOL;
    echo "Updated: " . $quote['updated_at'] . PHP_EOL;
    
    // Check masked ID
    $maskSql = "SELECT masked_id FROM quote_id_mask WHERE quote_id = :quote_id";
    $maskedId = $connection->fetchOne($maskSql, ['quote_id' => $quoteId]);
    echo "Masked ID: " . ($maskedId ?: 'Not found') . PHP_EOL;
    
    // Check for orders
    echo PHP_EOL . "Orders from this quote:" . PHP_EOL;
    echo "======================" . PHP_EOL;
    
    $orderSql = "SELECT 
        o.entity_id,
        o.increment_id,
        o.state,
        o.status,
        o.created_at,
        o.customer_email,
        o.grand_total,
        o.total_paid,
        p.method as payment_method
    FROM sales_order o
    LEFT JOIN sales_order_payment p ON p.parent_id = o.entity_id
    WHERE o.quote_id = :quote_id
    ORDER BY o.created_at DESC";
    
    $orders = $connection->fetchAll($orderSql, ['quote_id' => $quoteId]);
    
    if ($orders) {
        foreach ($orders as $order) {
            echo PHP_EOL . "Order #" . $order['increment_id'] . PHP_EOL;
            echo "- Order ID: " . $order['entity_id'] . PHP_EOL;
            echo "- State: " . $order['state'] . PHP_EOL;
            echo "- Status: " . $order['status'] . PHP_EOL;
            echo "- Payment Method: " . $order['payment_method'] . PHP_EOL;
            echo "- Total Paid: " . ($order['total_paid'] ?: '0') . PHP_EOL;
            echo "- Grand Total: " . $order['grand_total'] . PHP_EOL;
            echo "- Created: " . $order['created_at'] . PHP_EOL;
        }
        
        echo PHP_EOL . "Total orders placed: " . count($orders) . PHP_EOL;
        
        if ($quote['is_active'] && $orders) {
            echo PHP_EOL . "WARNING: Quote is still active but has " . count($orders) . " order(s)!" . PHP_EOL;
        }
    } else {
        echo "No orders found for this quote." . PHP_EOL;
    }
    
    // Check quote items
    echo PHP_EOL . "Quote items:" . PHP_EOL;
    echo "============" . PHP_EOL;
    
    $itemsSql = "SELECT 
        item_id,
        product_id,
        sku,
        name,
        qty
    FROM quote_item
    WHERE quote_id = :quote_id
    AND parent_item_id IS NULL";
    
    $items = $connection->fetchAll($itemsSql, ['quote_id' => $quoteId]);
    
    foreach ($items as $item) {
        echo "- SKU: " . $item['sku'] . PHP_EOL;
        echo "  Name: " . $item['name'] . PHP_EOL;
        echo "  Qty: " . $item['qty'] . PHP_EOL;
        
        // Check product status
        $productSql = "SELECT 
            cpei_status.value as status,
            si.is_in_stock,
            si.qty as stock_qty
        FROM catalog_product_entity p
        LEFT JOIN catalog_product_entity_int cpei_status ON cpei_status.entity_id = p.entity_id 
            AND cpei_status.attribute_id = (SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'status' AND entity_type_id = 4)
        LEFT JOIN cataloginventory_stock_item si ON si.product_id = p.entity_id
        WHERE p.entity_id = :product_id";
        
        $product = $connection->fetchRow($productSql, ['product_id' => $item['product_id']]);
        if ($product) {
            echo "  Product Status: " . ($product['status'] == 1 ? 'Enabled' : 'Disabled') . PHP_EOL;
            echo "  In Stock: " . ($product['is_in_stock'] ? 'Yes' : 'No') . PHP_EOL;
            echo "  Stock Qty: " . $product['stock_qty'] . PHP_EOL;
        }
        echo PHP_EOL;
    }
    
} else {
    echo "Quote with ID $quoteId not found!" . PHP_EOL;
}