<?php
// ============================================================
//  db/check.php — show which schema migrations are applied
// ============================================================
//
//  Read-only. Safe to leave on the server (only lists table names
//  and row counts, no data).
//
//  Visit in the browser. Shows what's there, what's missing, and
//  what to do about it.
//
// ============================================================

require_once __DIR__ . '/../includes/db.php';

header('Content-Type: text/plain; charset=utf-8');

echo "Buy Local Lowveld — schema check\n";
echo str_repeat('=', 50) . "\n\n";

// Tables we expect, by phase
$phases = [
    'Phase 2a (schema.sql)' => [
        'members', 'categories', 'listings', 'blog_posts',
        'branding_items', 'orders', 'order_items', 'invoices',
        'transactions', 'password_resets', 'business_claims',
        'mailchimp_sync_log',
    ],
    'Phase 2b (schema-2b.sql)' => [
        'payments', 'payment_tokens', 'cron_runs', 'rate_limits',
    ],
];

$missing = [];
$total_found = 0;
$total_expected = 0;

foreach ($phases as $phase => $tables) {
    echo "$phase\n";
    echo str_repeat('-', strlen($phase)) . "\n";
    foreach ($tables as $t) {
        $total_expected++;
        try {
            $count = (int)db_value("SELECT COUNT(*) FROM `$t`");
            printf("  %-22s  %6d rows\n", $t, $count);
            $total_found++;
        } catch (Throwable $e) {
            printf("  %-22s  MISSING\n", $t);
            $missing[$phase][] = $t;
        }
    }
    echo "\n";
}

echo str_repeat('=', 50) . "\n";
echo "Found: $total_found / $total_expected tables\n\n";

if (!empty($missing)) {
    echo "ACTION NEEDED:\n";
    foreach ($missing as $phase => $tables) {
        $file = strpos($phase, '2b') !== false ? 'db/schema-2b.sql' : 'db/schema.sql';
        echo "  Run $file to create: " . implode(', ', $tables) . "\n";
    }
    echo "\n";
    echo "From cPanel: phpMyAdmin -> your database -> Import tab -> upload the .sql file\n";
    echo "From CLI:    mysql -u USER -p DBNAME < $file\n";
} else {
    echo "✓ All tables present. You're good to go.\n";
}