#!/bin/bash
# Diagnostic script for inventory_txn issues
# Usage: ./diagnose_inventory.sh [database_name]

DB_NAME="${1:-TAF}"
SQL_FILE="/var/www/html/TAF/sql/debug_inventory_txn.sql"

echo "╔═══════════════════════════════════════════════════════╗"
echo "║   Inventory Transaction Diagnostics                  ║"
echo "╚═══════════════════════════════════════════════════════╝"
echo ""
echo "Database: $DB_NAME"
echo "Timestamp: $(date '+%Y-%m-%d %H:%M:%S')"
echo ""
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo ""

if [ -f "$SQL_FILE" ]; then
    sudo -u postgres psql "$DB_NAME" -f "$SQL_FILE"
else
    echo "Running inline queries..."
    
    echo "1. Checking unprocessed transactions..."
    sudo -u postgres psql "$DB_NAME" -c "
        SELECT COUNT(*) as unprocessed_count
        FROM inventory_txn
        WHERE processed_at IS NULL AND isDeleted = false;
    "
    
    echo ""
    echo "2. Viewing unprocessed transaction details..."
    sudo -u postgres psql "$DB_NAME" -c "
        SELECT 
            inventory_txn_id,
            product_id,
            change,
            branch_id,
            reason,
            updatedAt
        FROM inventory_txn
        WHERE processed_at IS NULL AND isDeleted = false
        ORDER BY updatedAt DESC;
    "
    
    echo ""
    echo "3. Checking for missing batch numbers..."
    sudo -u postgres psql "$DB_NAME" -c "
        SELECT 
            it.product_id,
            p.name as product_name,
            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;
    "
fi

echo ""
echo "━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━"
echo ""
echo "To process these transactions, run:"
echo "  curl -X POST http://localhost:8080/api/inventory/processTransactions \\"
echo "    -H 'Content-Type: application/json' -d '{}'"
echo ""