<?php
/**
 * debug_reviewer_sync.php — DELETE AFTER USE
 * Access: communeglobal.com/admin/debug_reviewer_sync.php?sid=XX
 */
if (session_status()===PHP_SESSION_NONE) session_start();
include __DIR__ . '/config/db.php';
include __DIR__ . '/config/auth.php';
require_role('admin');

$sid = (int)($_GET['sid'] ?? 0);

// Manual sync trigger
$syncResult = null;
if (isset($_POST['do_sync']) && $sid) {
    $syncFile = __DIR__ . '/../config/reviewer_sync.php';
    if (file_exists($syncFile)) {
        require_once $syncFile;
        $syncResult = syncSubmissionReviewers($pdo, $sid);
    } else {
        $syncResult = ['error' => 'reviewer_sync.php NOT FOUND at: ' . $syncFile];
    }
}

// Fetch debug info
$info = [];

// 1. reviewer_sync.php path check
$syncPath = __DIR__ . '/../config/reviewer_sync.php';
$info['reviewer_sync_file'] = file_exists($syncPath) ? '✅ EXISTS: '.$syncPath : '❌ NOT FOUND: '.$syncPath;

// 2. Check submissions table columns
try {
    $cols = $pdo->query("SHOW COLUMNS FROM submissions")->fetchAll(PDO::FETCH_COLUMN);
    $needed = ['submission_status','tracking_code','submitted_at'];
    foreach ($needed as $c) {
        $info["submissions.$c"] = in_array($c,$cols) ? '✅ exists' : '❌ MISSING';
    }
} catch(PDOException $e){ $info['submissions columns'] = 'ERROR: '.$e->getMessage(); }

// 3. Check users table columns
try {
    $cols2 = $pdo->query("SHOW COLUMNS FROM users")->fetchAll(PDO::FETCH_COLUMN);
    foreach (['role','researcher_id'] as $c) {
        $info["users.$c"] = in_array($c,$cols2) ? '✅ exists' : '❌ MISSING — run SQL migration';
    }
} catch(PDOException $e){ $info['users columns'] = 'ERROR: '.$e->getMessage(); }

// 4. Check suggested_reviewers columns
try {
    $cols3 = $pdo->query("SHOW COLUMNS FROM suggested_reviewers")->fetchAll(PDO::FETCH_COLUMN);
    foreach (['email','institutional_email','research_areas','linked_user_id'] as $c) {
        $info["suggested_reviewers.$c"] = in_array($c,$cols3) ? '✅ exists' : '❌ MISSING';
    }
} catch(PDOException $e){ $info['suggested_reviewers cols'] = 'ERROR: '.$e->getMessage(); }

// 5. If sid given — show reviewers for that submission
$reviewers = [];
if ($sid) {
    try {
        $q = $pdo->prepare("SELECT reviewer_id, reviewer_type, first_name, last_name, email, institutional_email, linked_user_id FROM suggested_reviewers WHERE submission_id=?");
        $q->execute([$sid]);
        $reviewers = $q->fetchAll(PDO::FETCH_ASSOC);
    } catch(PDOException $e){ $info['reviewers fetch'] = 'ERROR: '.$e->getMessage(); }

    try {
        $sq = $pdo->prepare("SELECT submission_id, submission_status, tracking_code FROM submissions WHERE submission_id=?");
        $sq->execute([$sid]);
        $subRow = $sq->fetch();
        $info['submission_status'] = $subRow ? json_encode($subRow) : 'NOT FOUND';
    } catch(PDOException $e){}
}

// 6. Recent submissions (last 5)
$recentSubs = [];
try {
    $recentSubs = $pdo->query("SELECT submission_id, submission_status, tracking_code, updated_at FROM submissions ORDER BY submission_id DESC LIMIT 5")->fetchAll(PDO::FETCH_ASSOC);
} catch(PDOException $e){}
?>
<!DOCTYPE html>
<html>
<head>
<title>Debug Reviewer Sync</title>
<style>
body{font-family:monospace;padding:24px;background:#f5f5f5;font-size:13px}
.box{background:#fff;border-radius:8px;padding:20px;max-width:900px;margin:0 auto 20px;box-shadow:0 2px 8px rgba(0,0,0,.1)}
h2{color:#dc2626;margin:0 0 16px}h3{color:#1d4ed8;margin:16px 0 8px}
.row{display:flex;gap:12px;padding:8px 0;border-bottom:1px solid #f0f0f0;align-items:flex-start}
.lbl{font-weight:700;color:#555;min-width:260px;flex-shrink:0}
.val{color:#111;word-break:break-all}
.ok{color:#16a34a}.err{color:#dc2626}.warn{color:#d97706}
table{width:100%;border-collapse:collapse;font-size:12px}
th{background:#1d4ed8;color:#fff;padding:6px 10px;text-align:left}
td{padding:6px 10px;border-bottom:1px solid #eee}
tr:hover td{background:#f0f7ff}
.btn{padding:10px 20px;background:#059669;color:#fff;border:none;border-radius:6px;cursor:pointer;font-size:13px;font-weight:700}
.btn-blue{background:#1d4ed8}
.warn-box{background:#FFFBEB;border:1px solid #FDE68A;border-radius:6px;padding:12px;color:#92400E;margin-top:16px}
</style>
</head>
<body>
<div class="box">
<h2>🔍 Debug: Reviewer Sync to Users Table</h2>

<!-- Submission picker -->
<form method="GET" style="margin-bottom:16px;display:flex;gap:10px;align-items:center">
    <input type="number" name="sid" value="<?= $sid ?>"
           placeholder="Submission ID"
           style="padding:8px 12px;border:1px solid #ddd;border-radius:6px;font-size:13px;width:200px">
    <button type="submit" class="btn btn-blue">Load</button>
</form>

<!-- System checks -->
<h3>System Checks</h3>
<?php foreach($info as $k=>$v): ?>
<div class="row">
    <div class="lbl"><?= htmlspecialchars($k) ?></div>
    <div class="val <?= strpos($v,'✅')!==false?'ok':(strpos($v,'❌')!==false?'err':'warn') ?>">
        <?= htmlspecialchars($v) ?>
    </div>
</div>
<?php endforeach; ?>

<!-- Recent submissions -->
<?php if($recentSubs): ?>
<h3>Recent Submissions (last 5)</h3>
<table>
    <tr><th>ID</th><th>Status</th><th>Tracking Code</th><th>Updated</th><th>Action</th></tr>
    <?php foreach($recentSubs as $s): ?>
    <tr>
        <td><?= $s['submission_id'] ?></td>
        <td><?= $s['submission_status'] ?></td>
        <td><?= $s['tracking_code'] ?: '—' ?></td>
        <td><?= $s['updated_at'] ?></td>
        <td><a href="?sid=<?= $s['submission_id'] ?>" style="color:#1d4ed8">View →</a></td>
    </tr>
    <?php endforeach; ?>
</table>
<?php endif; ?>

<!-- Reviewers for submission -->
<?php if ($sid && $reviewers): ?>
<h3>Reviewers for Submission #<?= $sid ?></h3>
<table>
    <tr><th>ID</th><th>Type</th><th>Name</th><th>Email</th><th>Inst Email</th><th>linked_user_id</th><th>Valid Email?</th></tr>
    <?php foreach($reviewers as $r):
        $email1 = trim($r['institutional_email']??'');
        $email2 = trim($r['email']??'');
        $validEmail = filter_var($email1,FILTER_VALIDATE_EMAIL) ? $email1 : (filter_var($email2,FILTER_VALIDATE_EMAIL)?$email2:'');
    ?>
    <tr>
        <td><?= $r['reviewer_id'] ?></td>
        <td><?= $r['reviewer_type'] ?></td>
        <td><?= htmlspecialchars(($r['first_name']??'').' '.($r['last_name']??'')) ?></td>
        <td style="color:<?= filter_var($email2,FILTER_VALIDATE_EMAIL)?'#16a34a':'#dc2626' ?>"><?= htmlspecialchars($email2 ?: '— NULL —') ?></td>
        <td style="color:<?= filter_var($email1,FILTER_VALIDATE_EMAIL)?'#16a34a':'#dc2626' ?>"><?= htmlspecialchars($email1 ?: '— NULL —') ?></td>
        <td><?= $r['linked_user_id'] ? '✅ '.$r['linked_user_id'] : '❌ not synced' ?></td>
        <td><?= $validEmail ? '<span class="ok">✅ '.$validEmail.'</span>' : '<span class="err">❌ NO VALID EMAIL</span>' ?></td>
    </tr>
    <?php endforeach; ?>
</table>
<?php elseif($sid): ?>
<div class="warn-box">⚠️ No reviewers found for submission #<?= $sid ?></div>
<?php endif; ?>

<!-- Manual sync -->
<?php if ($sid): ?>
<h3>Manual Sync Trigger</h3>
<form method="POST">
    <input type="hidden" name="sid" value="<?= $sid ?>">
    <button type="submit" name="do_sync" class="btn">
        🔄 Force Sync Reviewers → Users (Submission #<?= $sid ?>)
    </button>
</form>
<?php if ($syncResult): ?>
<div style="margin-top:12px;padding:14px;background:#f0fdf4;border:1px solid #86efac;border-radius:6px">
    <strong>Sync Result:</strong><br>
    <pre><?= htmlspecialchars(json_encode($syncResult, JSON_PRETTY_PRINT)) ?></pre>
</div>
<?php endif; ?>
<?php endif; ?>

<div class="warn-box" style="margin-top:20px">
    ⚠️ <strong>DELETE this file after debugging!</strong><br>
    Path: <code><?= __FILE__ ?></code>
</div>
</div>
</body>
</html>