<?php
// debug_sync.php — DELETE THIS FILE after debugging
// Access: yourdomain.com/admin/debug_sync.php
if (session_status() === PHP_SESSION_NONE) session_start();
include __DIR__ . '/config/db.php';
include __DIR__ . '/config/auth.php';
require_role('admin');

$info = [];

// 1. Check which DB $pdo is connected to
try {
    $dbName = $pdo->query("SELECT DATABASE()")->fetchColumn();
    $info['pdo_database'] = $dbName;
} catch(PDOException $e){ $info['pdo_database'] = 'ERROR: '.$e->getMessage(); }

// 2. Check if users table exists in current DB
try {
    $tables = $pdo->query("SHOW TABLES LIKE 'users'")->fetchColumn();
    $info['users_table_in_current_db'] = $tables ? 'YES ✅' : 'NO ❌';
} catch(PDOException $e){ $info['users_table_in_current_db'] = 'ERROR: '.$e->getMessage(); }

// 3. Check if review_application table exists
try {
    $tables2 = $pdo->query("SHOW TABLES LIKE 'review_application'")->fetchColumn();
    $info['review_application_table'] = $tables2 ? 'YES ✅' : 'NO ❌';
} catch(PDOException $e){ $info['review_application_table'] = 'ERROR: '.$e->getMessage(); }

// 4. Check role column in users
try {
    $cols = $pdo->query("SHOW COLUMNS FROM users LIKE 'role'")->fetch();
    $info['role_column_exists'] = $cols ? 'YES ✅ — Type: '.$cols['Type'] : 'NO ❌';
} catch(PDOException $e){ $info['role_column_exists'] = 'ERROR: '.$e->getMessage(); }

// 5. Check all available databases
try {
    $dbs = $pdo->query("SHOW DATABASES")->fetchAll(PDO::FETCH_COLUMN);
    $info['all_databases'] = implode(', ', $dbs);
} catch(PDOException $e){ $info['all_databases'] = 'ERROR: '.$e->getMessage(); }

// 6. Try fetching one approved reviewer application
try {
    $app = $pdo->query("SELECT id, full_name, institutional_email, personal_email, status FROM review_application WHERE status=1 LIMIT 1")->fetch();
    $info['sample_approved_app'] = $app ? json_encode($app) : 'No approved applications yet';
} catch(PDOException $e){ $info['sample_approved_app'] = 'ERROR: '.$e->getMessage(); }

// 7. Count users table
try {
    $cnt = $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();
    $info['users_count'] = $cnt;
} catch(PDOException $e){ $info['users_count'] = 'ERROR: '.$e->getMessage(); }
?>
<!DOCTYPE html>
<html>
<head>
<title>Debug Sync</title>
<style>
body{font-family:monospace;padding:30px;background:#f5f5f5}
.box{background:#fff;border-radius:8px;padding:20px;max-width:800px;margin:0 auto;box-shadow:0 2px 10px rgba(0,0,0,.1)}
h2{color:#dc2626;margin-bottom:20px}
.row{display:flex;gap:16px;padding:10px 0;border-bottom:1px solid #f0f0f0}
.label{font-weight:700;color:#666;min-width:280px;font-size:13px}
.value{color:#1a1a1a;font-size:13px;word-break:break-all}
.ok{color:#16a34a}.err{color:#dc2626}.warn{color:#d97706}
.btn{padding:10px 20px;background:#1d4ed8;color:#fff;border:none;border-radius:6px;cursor:pointer;font-size:14px;margin-top:20px}
</style>
</head>
<body>
<div class="box">
<h2>🔍 Debug: Reviewer Sync to Users Table</h2>
<?php foreach($info as $k=>$v): ?>
<div class="row">
  <div class="label"><?= htmlspecialchars($k) ?></div>
  <div class="value <?= strpos($v,'ERROR')!==false?'err':(strpos($v,'YES')!==false?'ok':'') ?>">
    <?= htmlspecialchars($v) ?>
  </div>
</div>
<?php endforeach; ?>

<?php
// 8. Try manual sync test
if(isset($_POST['test_sync'])){
    $email = $_POST['test_email']??'';
    $result = [];
    try {
        $q = $pdo->prepare("SELECT user_id, role FROM users WHERE email=? LIMIT 1");
        $q->execute([$email]);
        $u = $q->fetch();
        if($u){ $result[] = "User found: ID={$u['user_id']}, role={$u['role']}"; }
        else   { $result[] = "User NOT found in users table with email: $email"; }

        // Try insert
        $tempHash = password_hash('TestPass123', PASSWORD_BCRYPT);
        $pdo->prepare("INSERT IGNORE INTO users (email,password_hash,first_name,last_name,role,is_active,is_email_verified,created_at,updated_at) VALUES (?,?,'Test','Reviewer','reviewer',1,1,NOW(),NOW())")
           ->execute([$email.'_test_'.time(), $tempHash]);
        $result[] = "Test INSERT succeeded — lastInsertId: ".$pdo->lastInsertId();
        // Clean up
        $pdo->prepare("DELETE FROM users WHERE first_name='Test' AND last_name='Reviewer'")->execute();
        $result[] = "Test row cleaned up";
    } catch(PDOException $e){
        $result[] = "ERROR: ".$e->getMessage();
    }
    echo '<div style="background:#f0fdf4;padding:16px;border-radius:6px;margin-top:16px">';
    foreach($result as $r) echo '<div>'.htmlspecialchars($r).'</div>';
    echo '</div>';
}
?>

<form method="POST" style="margin-top:20px;display:flex;gap:10px;align-items:center">
  <input type="email" name="test_email" placeholder="test@email.com"
         style="padding:8px 12px;border:1px solid #ddd;border-radius:6px;font-size:14px">
  <button type="submit" name="test_sync" class="btn">Test INSERT to users table</button>
</form>

<div style="margin-top:20px;padding:14px;background:#FEF3C7;border-radius:6px;font-size:13px;color:#92400E">
  ⚠️ <strong>DELETE this file after debugging!</strong><br>
  Path: <code><?= __FILE__ ?></code>
</div>
</div>
</body>
</html>