<?php
/**
 * Branch Code + Cert Info Checker
 * Upload to: /gmind/check_branch_code.php
 * Delete after use!
 */
error_reporting(E_ALL & ~E_WARNING & ~E_NOTICE);
ini_set('display_errors', '1');

require_once __DIR__ . '/config/database.php';

echo "<h1>Branch Code Check</h1>";

try {
    $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=utf8mb4";
    $pdo = new PDO($dsn, DB_USER, DB_PASS, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);

    // 1. Check branches table structure
    echo "<h2>1. Branches Table — branch_code column?</h2>";
    $stmt = $pdo->query("DESCRIBE `branches`");
    $cols = $stmt->fetchAll();
    $hasCode = false;
    foreach ($cols as $c) {
        if ($c['Field'] === 'branch_code') $hasCode = true;
    }
    echo "<p>branch_code column: " . ($hasCode ? "✓ EXISTS" : "✗ MISSING") . "</p>";

    // 2. List all branches with their codes
    echo "<h2>2. All Branches</h2>";
    $stmt = $pdo->query("SELECT id, name, branch_code FROM branches ORDER BY id");
    $branches = $stmt->fetchAll();
    echo "<table border='1' cellpadding='8' style='border-collapse:collapse;'>";
    echo "<tr><th>ID</th><th>Name</th><th>branch_code</th></tr>";
    foreach ($branches as $b) {
        echo "<tr><td>{$b['id']}</td><td>" . htmlspecialchars($b['name']) . "</td><td><strong>" . htmlspecialchars($b['branch_code'] ?? '(empty)') . "</strong></td></tr>";
    }
    echo "</table>";

    // 3. For latest 5 certificates, check if branch_code is available
    echo "<h2>3. Latest Certificates with Branch Code</h2>";
    $stmt = $pdo->query("
        SELECT c.id, c.certificate_no, c.student_name, c.branch_id, c.status,
               b.name AS branch_name, b.branch_code
        FROM certificates c
        LEFT JOIN branches b ON c.branch_id = b.id
        ORDER BY c.id DESC LIMIT 5
    ");
    $certs = $stmt->fetchAll();
    echo "<table border='1' cellpadding='8' style='border-collapse:collapse;'>";
    echo "<tr><th>Cert ID</th><th>Cert No</th><th>Student</th><th>Branch ID</th><th>Branch Name</th><th>branch_code</th><th>Status</th></tr>";
    foreach ($certs as $c) {
        $codeColor = empty($c['branch_code']) ? 'red' : 'green';
        echo "<tr>";
        echo "<td>{$c['id']}</td>";
        echo "<td>" . htmlspecialchars($c['certificate_no']) . "</td>";
        echo "<td>" . htmlspecialchars($c['student_name']) . "</td>";
        echo "<td>{$c['branch_id']}</td>";
        echo "<td>" . htmlspecialchars($c['branch_name'] ?? '') . "</td>";
        echo "<td style='color:{$codeColor};font-weight:bold;'>" . htmlspecialchars($c['branch_code'] ?? '(empty)') . "</td>";
        echo "<td>{$c['status']}</td>";
        echo "</tr>";
    }
    echo "</table>";

} catch (Exception $e) {
    echo "<p style='color:red;'>Error: " . htmlspecialchars($e->getMessage()) . "</p>";
}
