<?php
/* TEMPORARY DIAGNOSTIC — delete after use */
require_once __DIR__ . '/config.php';
require_once __DIR__ . '/db.php';
header('Content-Type: text/plain; charset=utf-8');
$conn = $db->getConnection();

function q($conn, $label, $sql) {
    echo "== $label\n";
    try {
        $r = $conn->query($sql);
        if (!$r) { echo "   ERROR: " . $conn->error . "\n\n"; return; }
        $n = 0;
        while ($row = $r->fetch_assoc()) { echo "   " . json_encode($row, JSON_UNESCAPED_UNICODE) . "\n"; $n++; if ($n > 30) break; }
        if ($n === 0) echo "   (no rows)\n";
        echo "\n";
    } catch (\Throwable $e) { echo "   THROWN: " . $e->getMessage() . "\n\n"; }
}

echo "DB: " . DB_NAME . "\n\n";

foreach (['subscription_plans','subscription_plan_items','subscription_plan_history','user_subscriptions','tasks','task_categories','staff_form_fields'] as $t) {
    $r = $conn->query("SHOW TABLES LIKE '$t'");
    echo str_pad($t, 30) . (($r && $r->num_rows) ? "EXISTS" : "*** MISSING ***") . "\n";
}
echo "\n";

foreach (['is_subscribed','subscription_plan_id','subscription_started_at','subscription_expires_at','staff_level','perm_extra','perm_denied'] as $c) {
    $r = $conn->query("SHOW COLUMNS FROM users LIKE '$c'");
    echo "users." . str_pad($c, 26) . (($r && $r->num_rows) ? "EXISTS" : "*** MISSING ***") . "\n";
}
echo "\n";

q($conn, 'raw rows in subscription_plans', "SELECT id, name, plan_code, price, mrp, status, is_default, sort_order FROM subscription_plans ORDER BY id");
q($conn, 'count', "SELECT COUNT(*) AS c FROM subscription_plans");

echo "== the exact query the page runs\n";
$sql = "SELECT p.*,
            (SELECT COUNT(*) FROM subscription_plan_items i WHERE i.plan_id = p.id) AS item_count,
            (SELECT COUNT(*) FROM users u WHERE u.subscription_plan_id = p.id) AS member_count
     FROM subscription_plans p ORDER BY p.sort_order ASC, p.id ASC";
$r = $conn->query($sql);
if (!$r) { echo "   *** THIS IS THE PROBLEM *** " . $conn->error . "\n"; }
else { echo "   OK — " . $r->num_rows . " row(s)\n"; }
echo "\n";

q($conn, 'columns of subscription_plans', "SHOW COLUMNS FROM subscription_plans");
