<?php
/**
 * Database Diagnostic Tool
 * Shows what databases are available and which config file is being used
 *
 * Place this in your carstock or signing folder and access via browser:
 * http://yoursite.com/carstock/database-diagnostic.php
 * http://yoursite.com/docusign/database-diagnostic.php
 */

// Try to load bootstrap if available
$bootstrap_paths = [
    __DIR__ . '/includes/bootstrap.php',
    __DIR__ . '/../includes/bootstrap.php',
    __DIR__ . '/../../includes/bootstrap.php',
];

$config_loaded = false;
foreach ($bootstrap_paths as $path) {
    if (file_exists($path)) {
        require_once $path;
        $config_loaded = true;
        break;
    }
}

// If bootstrap doesn't work, try loading config directly
if (!$config_loaded) {
    $config_paths = [
        __DIR__ . '/config.php',
        __DIR__ . '/../config.php',
        __DIR__ . '/../../config.php',
    ];

    foreach ($config_paths as $path) {
        if (file_exists($path)) {
            require_once $path;
            $config_loaded = true;
            break;
        }
    }
}

?>
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Database Diagnostic</title>
    <style>
        body {
            font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;
            max-width: 1200px;
            margin: 0 auto;
            padding: 20px;
            background: #f8fafc;
            color: #0f172a;
        }
        h1 { color: #0f172a; border-bottom: 3px solid #3b82f6; padding-bottom: 10px; }
        h2 { color: #1e293b; margin-top: 30px; }
        .section {
            background: white;
            border: 1px solid #e2e8f0;
            border-radius: 8px;
            padding: 20px;
            margin-bottom: 20px;
        }
        .info-row {
            display: grid;
            grid-template-columns: 200px 1fr;
            gap: 20px;
            padding: 10px 0;
            border-bottom: 1px solid #f1f5f9;
        }
        .info-row:last-child { border-bottom: none; }
        .label { font-weight: 600; color: #475569; }
        .value {
            font-family: 'Courier New', monospace;
            background: #f8fafc;
            padding: 8px;
            border-radius: 4px;
            word-break: break-all;
        }
        .success { color: #047857; }
        .error { color: #dc2626; }
        .warning { color: #d97706; }
        table {
            width: 100%;
            border-collapse: collapse;
            margin: 15px 0;
        }
        th {
            background: #f1f5f9;
            padding: 12px;
            text-align: left;
            border-bottom: 2px solid #e2e8f0;
            font-weight: 600;
        }
        td {
            padding: 12px;
            border-bottom: 1px solid #e2e8f0;
        }
        .db-row { background: #f8fafc; }
        .db-row:hover { background: #f1f5f9; }
        .code-block {
            background: #1e293b;
            color: #e2e8f0;
            padding: 15px;
            border-radius: 6px;
            font-family: 'Courier New', monospace;
            font-size: 13px;
            overflow-x: auto;
        }
        .btn {
            display: inline-block;
            padding: 10px 20px;
            background: #3b82f6;
            color: white;
            text-decoration: none;
            border-radius: 6px;
            border: none;
            cursor: pointer;
            font-size: 14px;
        }
        .btn:hover { background: #2563eb; }
    </style>
</head>
<body>
    <h1>🔍 Database Diagnostic Tool</h1>
    <p>This page shows what databases are available and which one this site is currently using.</p>

    <div class="section">
        <h2>Current Site Configuration</h2>

        <?php if ($config_loaded): ?>
        <div class="info-row">
            <span class="label">Config Status</span>
            <span class="value success">✓ Configuration loaded</span>
        </div>
        <?php else: ?>
        <div class="info-row">
            <span class="label">Config Status</span>
            <span class="value error">✗ Could not load configuration</span>
        </div>
        <?php endif; ?>

        <div class="info-row">
            <span class="label">Current Directory</span>
            <span class="value"><?php echo htmlspecialchars(__DIR__); ?></span>
        </div>

        <div class="info-row">
            <span class="label">Script Location</span>
            <span class="value"><?php echo htmlspecialchars(__FILE__); ?></span>
        </div>

        <?php if (defined('DB_HOST')): ?>
        <div class="info-row">
            <span class="label">Database Host</span>
            <span class="value"><?php echo htmlspecialchars(DB_HOST); ?></span>
        </div>
        <div class="info-row">
            <span class="label">Database User</span>
            <span class="value"><?php echo htmlspecialchars(DB_USER); ?></span>
        </div>
        <div class="info-row">
            <span class="label">Database Name</span>
            <span class="value success"><?php echo htmlspecialchars(DB_NAME); ?></span>
        </div>
        <?php elseif (function_exists('db')): ?>
        <div class="info-row">
            <span class="label">Database Connection</span>
            <span class="value success">✓ PDO connection available via db()</span>
        </div>
        <?php endif; ?>
    </div>

    <div class="section">
        <h2>Available Databases on Server</h2>

        <?php
        $all_databases = [];
        $error_msg = '';

        try {
            if (function_exists('db')) {
                $pdo = db();
            } elseif (defined('DB_HOST') && defined('DB_USER') && defined('DB_PASS')) {
                $pdo = new PDO(
                    'mysql:host=' . DB_HOST,
                    DB_USER,
                    DB_PASS,
                    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
                );
            } else {
                throw new Exception('Cannot connect to database - no credentials available');
            }

            $result = $pdo->query("SHOW DATABASES");
            $all_databases = $result->fetchAll(PDO::FETCH_COLUMN);

        } catch (Exception $e) {
            $error_msg = "Error: " . $e->getMessage();
        }

        if (!empty($error_msg)):
        ?>
            <div style="background: #fee2e2; color: #991b1b; padding: 15px; border-radius: 6px; margin: 15px 0;">
                <strong>⚠️ Connection Error:</strong> <?php echo htmlspecialchars($error_msg); ?>
            </div>
        <?php
        elseif (!empty($all_databases)):
            $system_dbs = ['information_schema', 'mysql', 'performance_schema', 'sys', 'test'];
            $user_dbs = array_filter($all_databases, function($db) use ($system_dbs) {
                return !in_array($db, $system_dbs);
            });
            $current_db = defined('DB_NAME') ? DB_NAME : '';
        ?>
            <p><strong>Total Databases:</strong> <?php echo count($all_databases); ?>
               | <strong>User Databases:</strong> <?php echo count($user_dbs); ?></p>

            <table>
                <thead>
                    <tr>
                        <th>Database Name</th>
                        <th>Type</th>
                        <th>Current?</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($all_databases as $db_name): ?>
                    <tr class="<?php echo in_array($db_name, $system_dbs) ? '' : 'db-row'; ?>">
                        <td><strong><?php echo htmlspecialchars($db_name); ?></strong></td>
                        <td>
                            <?php if (in_array($db_name, $system_dbs)): ?>
                                <span class="warning">System</span>
                            <?php else: ?>
                                <span class="success">User Database</span>
                            <?php endif; ?>
                        </td>
                        <td>
                            <?php if ($db_name === $current_db): ?>
                                <strong class="success">✓ CURRENT</strong>
                            <?php else: ?>
                                -
                            <?php endif; ?>
                        </td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
        <?php
        else:
        ?>
            <div style="background: #fef3c7; color: #92400e; padding: 15px; border-radius: 6px;">
                No databases found or unable to retrieve list.
            </div>
        <?php endif; ?>
    </div>

    <div class="section">
        <h2>What This Means</h2>
        <p><strong>For your setup:</strong></p>
        <ul>
            <li><strong>carapp database</strong> = Car stock management site</li>
            <li><strong>forms database</strong> = Form templates storage</li>
            <li><strong>document_signing database</strong> = Document signing system</li>
        </ul>

        <p style="background: #dbeafe; color: #0c4a6e; padding: 15px; border-radius: 6px; margin: 15px 0;">
            <strong>✓ Both databases should be visible above.</strong><br>
            If you don't see both "forms" and "document_signing", they may not exist yet or there's a permission issue.
        </p>

        <h3>Next Steps:</h3>
        <ol>
            <li><strong>If both databases appear:</strong> Your setup is correct. The database-manager will now show both options.</li>
            <li><strong>If only carapp appears:</strong> The document_signing or forms database may need to be created, or your database user doesn't have access to them.</li>
            <li><strong>For the signing site:</strong> It should have its own config.php that points to document_signing database.</li>
        </ol>
    </div>

    <div class="section">
        <h2>Troubleshooting</h2>

        <h3>If databases are missing:</h3>
        <p>Check your database user permissions. Run this in phpMyAdmin:</p>
        <div class="code-block">
SHOW GRANTS FOR '<?php echo defined('DB_USER') ? htmlspecialchars(DB_USER) : 'your_user'; ?>'@'<?php echo defined('DB_HOST') ? htmlspecialchars(DB_HOST) : 'localhost'; ?>';
        </div>

        <h3>Create missing databases:</h3>
        <p>If databases don't exist, you can create them in phpMyAdmin:</p>
        <div class="code-block">
CREATE DATABASE IF NOT EXISTS document_signing CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE DATABASE IF NOT EXISTS forms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
        </div>
    </div>

    <div style="text-align: center; color: #64748b; font-size: 13px; margin-top: 30px;">
        <p>Run this diagnostic on both your carapp site and your signing site to compare.</p>
        <p>This file is safe to leave on your server but should be protected/hidden from public access.</p>
    </div>
</body>
</html>
