<?php
/**
 * Quick Bill Status Checker - Sales Module
 * Check the current state of any sales bill and its ledger entries
 */

session_start();
include_once('../includes/config.php');

// Check if user is logged in
if (!isset($_SESSION['uid'])) {
    die('Please login first.');
}

// Get bill number from URL or use default
$billNumber = isset($_GET['bill']) ? mysqli_real_escape_string($con, $_GET['bill']) : 'SAL-20251027-0001';
$paymentRef = 'PAY-' . $billNumber;

?>
<!DOCTYPE html>
<html>
<head>
    <title>Sales Bill Status Checker</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; background: #f4f6fb; }
        h2 { color: #4e73df; border-bottom: 3px solid #4e73df; padding-bottom: 10px; }
        h3 { color: #1cc88a; margin-top: 30px; }
        table { border-collapse: collapse; width: 100%; margin: 20px 0; background: white; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
        th, td { border: 1px solid #ddd; padding: 12px; text-align: left; }
        th { background-color: #4e73df; color: white; font-weight: bold; }
        .warning { background: #fff3cd; color: #856404; padding: 15px; border-left: 4px solid #ffc107; margin: 20px 0; }
        .success { background: #d4edda; color: #155724; padding: 15px; border-left: 4px solid #28a745; margin: 20px 0; }
        .error { background: #f8d7da; color: #721c24; padding: 15px; border-left: 4px solid #dc3545; margin: 20px 0; }
        .info { background: #d1ecf1; color: #0c5460; padding: 15px; border-left: 4px solid #17a2b8; margin: 20px 0; }
        .duplicate { background: #ffe6e6; }
        .form-group { margin: 20px 0; padding: 15px; background: white; border-radius: 5px; }
        input[type="text"] { padding: 8px; width: 300px; font-size: 14px; }
        button { padding: 10px 20px; background: #4e73df; color: white; border: none; cursor: pointer; font-size: 14px; }
        button:hover { background: #2e59d9; }
        .badge { padding: 5px 10px; border-radius: 3px; font-size: 12px; font-weight: bold; }
        .badge-patru { background: #f8d7da; color: #721c24; }
        .badge-varavu { background: #d4edda; color: #155724; }
    </style>
</head>
<body>
    <h2>📋 Sales Bill Status Checker</h2>
    
    <div class="form-group">
        <form method="GET">
            <label><strong>Bill Number:</strong></label><br>
            <input type="text" name="bill" value="<?php echo htmlspecialchars($billNumber); ?>" placeholder="SAL-YYYYMMDD-0001">
            <button type="submit">Check Status</button>
        </form>
    </div>

    <?php
    // Check if bill exists
    $saleQuery = "SELECT s.*, c.CustomerName, c.CustomerCode, c.CurrentBalance 
                  FROM tblsales s
                  LEFT JOIN tblcustomers c ON s.CustomerID = c.ID
                  WHERE s.InvoiceNumber = '$billNumber'";
    $saleResult = mysqli_query($con, $saleQuery);
    
    if (!$saleResult || mysqli_num_rows($saleResult) == 0) {
        echo "<div class='error'><strong>❌ Bill Not Found:</strong> {$billNumber}</div>";
        echo "<p><a href='index.php'>← Back to Sales Module</a></p>";
        exit;
    }
    
    $sale = mysqli_fetch_assoc($saleResult);
    ?>

    <h3>📄 Sales Bill Details</h3>
    <table>
        <tr>
            <th width="30%">Field</th>
            <th width="70%">Value</th>
        </tr>
        <tr>
            <td><strong>Invoice Number</strong></td>
            <td><?php echo $sale['InvoiceNumber']; ?></td>
        </tr>
        <tr>
            <td><strong>Customer</strong></td>
            <td><?php echo $sale['CustomerCode'] . ' - ' . $sale['CustomerName']; ?> (ID: <?php echo $sale['CustomerID']; ?>)</td>
        </tr>
        <tr>
            <td><strong>Sale Date</strong></td>
            <td><?php echo date('d-M-Y', strtotime($sale['SaleDate'])); ?></td>
        </tr>
        <tr>
            <td><strong>Sub Total</strong></td>
            <td>₹<?php echo number_format($sale['SubTotal'], 2); ?></td>
        </tr>
        <tr>
            <td><strong>Transport Amount</strong></td>
            <td>₹<?php echo number_format($sale['CooliAmount'], 2); ?></td>
        </tr>
        <tr>
            <td><strong>Total Amount</strong></td>
            <td><strong>₹<?php echo number_format($sale['TotalAmount'], 2); ?></strong></td>
        </tr>
        <tr>
            <td><strong>Payment Amount</strong></td>
            <td>₹<?php echo number_format($sale['PaymentAmount'], 2); ?></td>
        </tr>
        <tr>
            <td><strong>Payment Mode</strong></td>
            <td><?php echo $sale['PaymentMode']; ?></td>
        </tr>
        <tr>
            <td><strong>Current Customer Balance</strong></td>
            <td>₹<?php echo number_format($sale['CurrentBalance'], 2); ?></td>
        </tr>
    </table>

    <h3>🛒 Sale Items</h3>
    <?php
    $itemsQuery = "SELECT * FROM tblsales_details WHERE SaleID = " . $sale['ID'] . " ORDER BY ID";
    $itemsResult = mysqli_query($con, $itemsQuery);
    
    if (mysqli_num_rows($itemsResult) > 0) {
        echo "<table>
                <tr>
                    <th>S.No</th>
                    <th>Product Code</th>
                    <th>Product Name</th>
                    <th>Rate</th>
                    <th>Quantity</th>
                    <th>Amount</th>
                </tr>";
        
        $sno = 1;
        while ($item = mysqli_fetch_assoc($itemsResult)) {
            echo "<tr>
                    <td>{$sno}</td>
                    <td>{$item['ProductCode']}</td>
                    <td>{$item['ProductName']}</td>
                    <td>₹" . number_format($item['Rate'], 2) . "</td>
                    <td>" . number_format($item['Quantity'], 2) . "</td>
                    <td>₹" . number_format($item['Amount'], 2) . "</td>
                  </tr>";
            $sno++;
        }
        echo "</table>";
    } else {
        echo "<div class='warning'>No items found for this sale.</div>";
    }
    ?>

    <h3>📖 Customer Ledger Entries</h3>
    <?php
    $ledgerQuery = "SELECT * FROM tblcustomer_accounts 
                    WHERE (InvoiceNumber = '$billNumber' OR InvoiceNumber = '$paymentRef')
                    AND CustomerID = " . $sale['CustomerID'] . "
                    ORDER BY CreatedDate ASC, ID ASC";
    $ledgerResult = mysqli_query($con, $ledgerQuery);
    
    if (mysqli_num_rows($ledgerResult) > 0) {
        // Track payment entries
        $paymentEntries = [];
        $saleEntries = [];
        
        while ($entry = mysqli_fetch_assoc($ledgerResult)) {
            if ($entry['InvoiceNumber'] == $paymentRef && $entry['TransactionType'] == 'வரவு') {
                $paymentEntries[] = $entry;
            } else if ($entry['InvoiceNumber'] == $billNumber && $entry['TransactionType'] == 'பற்று') {
                $saleEntries[] = $entry;
            }
        }
        
        // Check for duplicates
        $hasDuplicates = count($paymentEntries) > 1 || count($saleEntries) > 1;
        
        if ($hasDuplicates) {
            echo "<div class='error'>
                    <strong>⚠️ DUPLICATES DETECTED!</strong><br>
                    - Sale Entries (பற்று): " . count($saleEntries) . " (Expected: 1)<br>
                    - Payment Entries (வரவு): " . count($paymentEntries) . " (Expected: " . ($sale['PaymentAmount'] > 0 ? '1' : '0') . ")<br>
                    <strong>Action Required:</strong> Run the cleanup script to fix this issue.
                  </div>";
        } else {
            echo "<div class='success'>
                    <strong>✓ No Duplicates Found</strong><br>
                    Ledger entries are correct.
                  </div>";
        }
        
        // Reset result pointer
        mysqli_data_seek($ledgerResult, 0);
        
        echo "<table>
                <tr>
                    <th>ID</th>
                    <th>Date</th>
                    <th>Invoice</th>
                    <th>Type</th>
                    <th>Description</th>
                    <th>Amount</th>
                    <th>Created Date</th>
                </tr>";
        
        $seenPayments = [];
        $seenSales = [];
        
        while ($entry = mysqli_fetch_assoc($ledgerResult)) {
            // Mark duplicates
            $rowClass = '';
            if ($entry['InvoiceNumber'] == $paymentRef && $entry['TransactionType'] == 'வரவு') {
                if (in_array($entry['Amount'], $seenPayments)) {
                    $rowClass = 'duplicate';
                }
                $seenPayments[] = $entry['Amount'];
            }
            if ($entry['InvoiceNumber'] == $billNumber && $entry['TransactionType'] == 'பற்று') {
                if (in_array($entry['Amount'], $seenSales)) {
                    $rowClass = 'duplicate';
                }
                $seenSales[] = $entry['Amount'];
            }
            
            $badgeClass = $entry['TransactionType'] == 'பற்று' ? 'badge-patru' : 'badge-varavu';
            
            echo "<tr class='{$rowClass}'>
                    <td>{$entry['ID']}</td>
                    <td>" . date('d-M-Y', strtotime($entry['TransactionDate'])) . "</td>
                    <td>{$entry['InvoiceNumber']}</td>
                    <td><span class='badge {$badgeClass}'>{$entry['TransactionType']}</span></td>
                    <td>{$entry['Description']}</td>
                    <td>₹" . number_format($entry['Amount'], 2) . "</td>
                    <td>" . date('d-M-Y H:i:s', strtotime($entry['CreatedDate'])) . "</td>
                  </tr>";
        }
        echo "</table>";
        
        if ($hasDuplicates) {
            echo "<div class='info'>
                    <strong>Legend:</strong> Rows with <span style='background: #ffe6e6; padding: 2px 5px;'>pink background</span> are duplicates.
                  </div>";
        }
    } else {
        echo "<div class='warning'>No ledger entries found for this bill.</div>";
    }
    ?>

    <h3>🔧 Actions</h3>
    <div style="margin: 20px 0;">
        <?php if ($hasDuplicates): ?>
            <a href="fix-duplicate-payments.php" style="display: inline-block; padding: 10px 20px; background: #dc3545; color: white; text-decoration: none; border-radius: 5px; font-weight: bold;">
                🛠️ Run Cleanup Script
            </a>
        <?php endif; ?>
        
        <a href="../customer/ledger.php?customer_id=<?php echo $sale['CustomerID']; ?>" style="display: inline-block; padding: 10px 20px; background: #4e73df; color: white; text-decoration: none; border-radius: 5px; margin-left: 10px;">
            📖 View Full Customer Ledger
        </a>
        
        <a href="index.php" style="display: inline-block; padding: 10px 20px; background: #6c757d; color: white; text-decoration: none; border-radius: 5px; margin-left: 10px;">
            ← Back to Sales Module
        </a>
    </div>

</body>
</html>
