<?php
// Test script to check payment-transaction linking
require_once __DIR__ . '/includes/db.php';

// Check if transaction_id column exists
$check = $mysqli->query("SHOW COLUMNS FROM payments LIKE 'transaction_id'");
if ($check->num_rows == 0) {
    echo "ERROR: transaction_id column does NOT exist in payments table!<br>";
    echo "Please run: ALTER TABLE payments ADD COLUMN transaction_id INT NULL AFTER reversed_payment_id;<br>";
    exit;
}

echo "transaction_id column exists ✓<br><br>";

// Check recent payments and their linked transactions
$result = $mysqli->query("SELECT p.id, p.invoice_id, p.reference_no, p.transaction_id, t.document_reference, t.narration 
    FROM payments p 
    LEFT JOIN transactions t ON t.id = p.transaction_id 
    ORDER BY p.id DESC LIMIT 10");

echo "Recent payments:<br>";
echo "<table border='1'><tr><th>Payment ID</th><th>Invoice ID</th><th>Reference</th><th>Transaction ID</th><th>Transaction Doc Ref</th></tr>";
while ($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . $row['id'] . "</td>";
    echo "<td>" . $row['invoice_id'] . "</td>";
    echo "<td>" . ($row['reference_no'] ?: 'NULL') . "</td>";
    echo "<td>" . ($row['transaction_id'] ?: 'NULL') . "</td>";
    echo "<td>" . ($row['document_reference'] ?: 'NULL') . "</td>";
    echo "</tr>";
}
echo "</table><br>";

// Check for orphan receipts (transactions not linked to payments)
$orphan = $mysqli->query("SELECT t.id, t.document_reference, t.narration, t.amount 
    FROM transactions t 
    LEFT JOIN payments p ON p.transaction_id = t.id 
    WHERE t.transaction_type = 'Receipt' AND p.id IS NULL 
    ORDER BY t.id DESC LIMIT 10");

echo "Orphan receipts (no payment link):<br>";
echo "<table border='1'><tr><th>Trans ID</th><th>Doc Ref</th><th>Narration</th><th>Amount</th></tr>";
while ($row = $orphan->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . $row['id'] . "</td>";
    echo "<td>" . ($row['document_reference'] ?: 'NULL') . "</td>";
    echo "<td>" . ($row['narration'] ?: 'NULL') . "</td>";
    echo "<td>" . $row['amount'] . "</td>";
    echo "</tr>";
}
echo "</table>";
