-- Quick diagnostic queries for inventory_txn issues

-- 1. Check unprocessed transactions
SELECT 
    COUNT(*) as unprocessed_count,
    'Unprocessed Transactions' as description
FROM inventory_txn
WHERE processed_at IS NULL
AND isDeleted = false;

-- 2. View the unprocessed transaction details
SELECT 
    inventory_txn_id,
    product_id,
    change,
    branch_id,
    reason,
    org_id,
    updatedAt,
    processed_at,
    isDeleted
FROM inventory_txn
WHERE processed_at IS NULL
AND isDeleted = false
ORDER BY updatedAt DESC;

-- 3. Check if product has a batch number
SELECT 
    it.inventory_txn_id,
    it.product_id,
    p.name as product_name,
    pbn.product_batch_number_id,
    pbn.batch_number,
    CASE 
        WHEN pbn.product_batch_number_id IS NULL THEN '❌ NO BATCH NUMBER'
        ELSE '✅ Has Batch Number'
    END as status
FROM inventory_txn it
LEFT JOIN products p ON it.product_id = p.product_id
LEFT JOIN product_batch_number pbn ON it.product_id = pbn.product_id AND pbn.isDeleted = false
WHERE it.processed_at IS NULL
AND it.isDeleted = false;

-- 4. Check if inventory record exists for this product
SELECT 
    it.inventory_txn_id,
    it.product_id,
    it.branch_id,
    it.change,
    pbn.product_batch_number_id,
    i.inventory_id,
    i.current_qty as current_inventory_qty,
    CASE 
        WHEN i.inventory_id IS NULL THEN '⚠️ No inventory record'
        ELSE '✅ Inventory exists'
    END as inventory_status
FROM inventory_txn it
LEFT JOIN product_batch_number pbn ON it.product_id = pbn.product_id AND pbn.isDeleted = false
LEFT JOIN inventory i ON pbn.product_batch_number_id = i.product_batch_number_id 
    AND it.branch_id = i.branch_id 
    AND i.isDeleted = false
WHERE it.processed_at IS NULL
AND it.isDeleted = false;

-- 5. Show all transactions for reference
SELECT 
    COUNT(*) as total_count,
    SUM(CASE WHEN processed_at IS NULL THEN 1 ELSE 0 END) as unprocessed,
    SUM(CASE WHEN processed_at IS NOT NULL THEN 1 ELSE 0 END) as processed
FROM inventory_txn
WHERE isDeleted = false;
