#!/bin/bash
set -e

BASE="db-dashboard"

echo "Creating DB Dashboard structure in ./$BASE ..."

mkdir -p "$BASE/src" "$BASE/docs"

# .env
cat > "$BASE/.env" << 'EOF'
DB_HOST=209.182.212.74
DB_NAME=intel145_apps
DB_USER=root
DB_PASS=
EOF

# conn-db.php
cat > "$BASE/src/conn-db.php" << 'EOF'
<?php
declare(strict_types=1);

function connDb(array $override = []): ?PDO
{
    $envPath = __DIR__ . '/../.env';
    $env = [];

    if (file_exists($envPath)) {
        foreach (file($envPath, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) as $line) {
            if (strpos($line, '=') !== false) {
                [$key, $val] = explode('=', $line, 2);
                $env[$key] = $val;
            }
        }
    }

    $defaults = [
        'DB_HOST' => '209.182.212.74',
        'DB_NAME' => 'intel145_apps',
        'DB_USER' => 'root',
        'DB_PASS' => '',
    ];

    $cfg = array_merge($defaults, $env, $override);

    $dsn = "mysql:host={$cfg['DB_HOST']};dbname={$cfg['DB_NAME']};charset=utf8mb4";

    try {
        return new PDO($dsn, $cfg['DB_USER'], $cfg['DB_PASS'], [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        ]);
    } catch (Throwable $e) {
        echo "<pre>DB connection failed: {$e->getMessage()}</pre>";
        return null;
    }
}
EOF

# diagnostics-select-builder.php
cat > "$BASE/src/diagnostics-select-builder.php" << 'EOF'
<?php
declare(strict_types=1);

function diagnosticsSelectBuilder(?PDO $pdo, string $table): string
{
    $isQualified = str_contains($table, '.');
    $tableRef = $isQualified ? $table : "`$table`";

    if (!$pdo) {
        return "SELECT * FROM $tableRef LIMIT 50";
    }

    try {
        $stmt = $pdo->query("SHOW COLUMNS FROM $table");
        $cols = $stmt ? $stmt->fetchAll(PDO::FETCH_COLUMN) : [];
    } catch (Throwable $e) {
        return "SELECT * FROM $tableRef LIMIT 50";
    }

    if (!$cols || count($cols) === 0) {
        return "SELECT * FROM $tableRef LIMIT 50";
    }

    $safeCols = array_map(fn($c) => "`$c`", $cols);
    $colList  = implode(", ", $safeCols);

    return "SELECT $colList FROM $tableRef LIMIT 50";
}
EOF

# diagnostics-debug.php
cat > "$BASE/src/diagnostics-debug.php" << 'EOF'
<?php
declare(strict_types=1);

function diagnosticsDebug(?PDO $pdo, string $table): void
{
    echo "<pre>";
    echo "=== DIAGNOSTICS DEBUG ===\n";
    echo "Table: $table\n\n";

    if (!$pdo) {
        echo "DB connection: FAILED\n";
        echo "Fallback SQL:\nSELECT * FROM $table LIMIT 50\n";
        echo "</pre>";
        return;
    }

    try {
        $stmt = $pdo->query("SHOW COLUMNS FROM $table");
        $cols = $stmt ? $stmt->fetchAll(PDO::FETCH_COLUMN) : [];
        echo "Columns detected: " . json_encode($cols) . "\n\n";
    } catch (Throwable $e) {
        echo "SHOW COLUMNS failed: {$e->getMessage()}\n\n";
        echo "Fallback SQL:\nSELECT * FROM $table LIMIT 50\n";
        echo "</pre>";
        return;
    }

    if (!$cols || count($cols) === 0) {
        echo "No columns detected — fallback SELECT\n\n";
        echo "Fallback SQL:\nSELECT * FROM $table LIMIT 50\n";
        echo "</pre>";
        return;
    }

    echo "Columns OK\n";
    echo "</pre>";
}
EOF

# db-dashboard.php
cat > "$BASE/src/db-dashboard.php" << 'EOF'
<?php
declare(strict_types=1);

require __DIR__ . '/conn-db.php';
require __DIR__ . '/diagnostics-select-builder.php';
require __DIR__ . '/diagnostics-debug.php';

/* .env load */
$envPath = __DIR__ . '/../.env';
$env = [];
if (file_exists($envPath)) {
    foreach (file($envPath, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) as $line) {
        if (strpos($line, '=') !== false) {
            [$key, $val] = explode('=', $line, 2);
            $env[$key] = $val;
        }
    }
}

/* defaults */
$defaults = [
    'DB_HOST' => '209.182.212.74',
    'DB_NAME' => 'intel145_apps',
    'DB_USER' => 'root',
    'DB_PASS' => '',
];

/* overrides */
$overrides = [];
foreach (['DB_HOST','DB_NAME','DB_USER','DB_PASS'] as $key) {
    if (isset($_GET[$key]) && $_GET[$key] !== '') {
        $overrides[$key] = $_GET[$key];
    }
}

/* merged config */
$currentCfg = array_merge($defaults, $env, $overrides);

/* connect */
$pdo    = connDb($overrides);
$module = $_GET['module'] ?? 'home';

/* safeQuery */
function safeQuery(?PDO $pdo, string $sql): array {
    if (!$pdo) return ['error' => 'No DB connection'];
    try {
        $stmt = $pdo->query($sql);
        return ['rows' => $stmt->fetchAll(PDO::FETCH_ASSOC)];
    } catch (Throwable $e) {
        return ['error' => $e->getMessage()];
    }
}

/* predefined */
$predefinedQueries = [
    'tips_id'      => 'SELECT id FROM tips LIMIT 5',
    'tips_all'     => 'SELECT * FROM tips LIMIT 10',
    'tables_info'  => 'SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.tables LIMIT 10',
    'ping'         => 'SELECT 1 AS ping',
];
?>
<!DOCTYPE html>
<html>
<head>
    <title>DB Dashboard</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css">
</head>
<body class="bg-light">
<div class="container-fluid">
    <div class="row">

        <div class="col-md-3 bg-dark text-light p-4" style="min-height:100vh;">
            <h3 class="mb-4">DB Dashboard</h3>
            <p><strong>Current Host:</strong> <?= htmlspecialchars($currentCfg['DB_HOST']) ?></p>

            <ul class="nav flex-column mb-4">
                <li class="nav-item mb-2"><a class="nav-link text-light" href="?module=home">Home</a></li>
                <li class="nav-item mb-2"><a class="nav-link text-light" href="?module=connection">Connection Test</a></li>
                <li class="nav-item mb-2"><a class="nav-link text-light" href="?module=schema">Schema Inspector</a></li>
                <li class="nav-item mb-2"><a class="nav-link text-light" href="?module=profiler">Table Profiler</a></li>
                <li class="nav-item mb-2"><a class="nav-link text-light" href="?module=query">Query Tester</a></li>
                <li class="nav-item mb-2"><a class="nav-link text-light" href="?module=diagnostics">Diagnostics Suite</a></li>
                <li class="nav-item mb-2"><a class="nav-link text-light" href="?module=failure">Failure Demo</a></li>
            </ul>

            <form method="get" class="mt-4">
                <h5 class="text-light">DB Overrides</h5>
                <input type="hidden" name="module" value="<?= htmlspecialchars($module) ?>">

                <div class="mb-2">
                    <label class="text-light">Host</label>
                    <input type="text" name="DB_HOST" class="form-control"
                           placeholder="<?= htmlspecialchars($currentCfg['DB_HOST']) ?>">
                </div>
                <div class="mb-2">
                    <label class="text-light">Database</label>
                    <input type="text" name="DB_NAME" class="form-control"
                           placeholder="<?= htmlspecialchars($currentCfg['DB_NAME']) ?>">
                </div>
                <div class="mb-2">
                    <label class="text-light">User</label>
                    <input type="text" name="DB_USER" class="form-control"
                           placeholder="<?= htmlspecialchars($currentCfg['DB_USER']) ?>">
                </div>
                <div class="mb-2">
                    <label class="text-light">Password</label>
                    <input type="password" name="DB_PASS" class="form-control"
                           placeholder="••••••">
                </div>

                <button class="btn btn-primary w-100 mt-2">Apply Overrides</button>
                <a href="?module=<?= htmlspecialchars($module) ?>" class="btn btn-secondary w-100 mt-2">
                    Reset to .env Defaults
                </a>
            </form>
        </div>

        <div class="col-md-9 p-4">
            <?php if ($module === 'home'): ?>
                <h2>Welcome</h2>
                <p>Select a test from the menu. Use DB Overrides to switch local/remote or cause failures.</p>

            <?php elseif ($module === 'connection'): ?>
                <h2>Connection Test</h2>
                <?php if ($pdo): ?>
                    <p class="text-success">Connected successfully.</p>
                <?php else: ?>
                    <p class="text-danger">Connection failed.</p>
                <?php endif; ?>

            <?php elseif ($module === 'schema'): ?>
                <h2>Schema Inspector</h2>
                <?php
                $dbs = safeQuery($pdo, "SHOW DATABASES");
                echo "<pre>" . htmlspecialchars(json_encode($dbs, JSON_PRETTY_PRINT)) . "</pre>";
                ?>

            <?php elseif ($module === 'profiler'): ?>
                <h2>Table Profiler</h2>
                <?php
                $tables = safeQuery($pdo, "SHOW TABLES");
                echo "<pre>" . htmlspecialchars(json_encode($tables, JSON_PRETTY_PRINT)) . "</pre>";
                ?>

            <?php elseif ($module === 'query'): ?>
                <h2>Query Tester</h2>

                <h5>Predefined Tests</h5>
                <ul>
                    <?php foreach ($predefinedQueries as $key => $sql): ?>
                        <li>
                            <a href="?module=query&test=<?= urlencode($key) ?>&<?= http_build_query($overrides) ?>">
                                <?= htmlspecialchars($key) ?>
                            </a>
                        </li>
                    <?php endforeach; ?>
                </ul>

                <form method="post" class="mt-3">
                    <textarea name="sql" class="form-control" rows="4"
                              placeholder="Enter SELECT query..."><?= htmlspecialchars($_POST['sql'] ?? '') ?></textarea>
                    <button class="btn btn-primary mt-3">Run Query</button>
                </form>

                <?php
                if ($_POST && isset($_POST['sql'])) {
                    $userSqlRaw = $_POST['sql'];
                    $userSql = strtolower(trim($userSqlRaw));

                    $blocked = ['delete ', 'update ', 'insert ', 'drop ', 'alter ', 'truncate ', 'create '];
                    foreach ($blocked as $word) {
                        if (str_starts_with($userSql, $word)) {
                            echo "<div class='alert alert-danger'>
                                    <strong>Blocked:</strong> Destructive queries are not allowed.<br>
                                    This dashboard only permits <strong>SELECT</strong> statements.
                                  </div>";
                            echo "<pre>{}</pre>";
                            $userSql = null;
                            break;
                        }
                    }

                    if ($userSql) {
                        $result = safeQuery($pdo, $userSqlRaw);
                        echo "<h5 class='mt-3'>Custom Query Result</h5>";
                        echo "<div id='custom-output'></div>";

                        if (isset($result['error'])) {
                            echo "<div class='alert alert-danger'>
                                    <strong>Error:</strong> " . htmlspecialchars($result['error']) . "
                                  </div>";
                            echo "<pre>{}</pre>";
                        } else {
                            echo "<pre>" . htmlspecialchars(json_encode($result, JSON_PRETTY_PRINT)) . "</pre>";
                        }
                    }
                }

                if (isset($_GET['test']) && isset($predefinedQueries[$_GET['test']])) {
                    $result = safeQuery($pdo, $predefinedQueries[$_GET['test']]);
                    echo "<h5 class='mt-3'>Predefined Test: " . htmlspecialchars($_GET['test']) . "</h5>";
                    echo "<div id='predefined-output'></div>";

                    if (isset($result['error'])) {
                        echo "<div class='alert alert-danger'>
                                <strong>Error:</strong> " . htmlspecialchars($result['error']) . "
                              </div>";
                        echo "<pre>{}</pre>";
                    } else {
                        echo "<pre>" . htmlspecialchars(json_encode($result, JSON_PRETTY_PRINT)) . "</pre>";
                    }
                }
                ?>

            <?php elseif ($module === 'diagnostics'): ?>
                <h2>Diagnostics Suite</h2>
                <?php
                $diagTable = 'information_schema.tables';
                diagnosticsDebug($pdo, $diagTable);
                $sql = diagnosticsSelectBuilder($pdo, $diagTable);
                echo "<h5>Diagnostics SQL</h5>";
                echo "<pre>" . htmlspecialchars($sql) . "</pre>";
                ?>

            <?php elseif ($module === 'failure'): ?>
                <h2>Failure Demonstration</h2>
                <p>These tests intentionally cause failures to prove they are handled.</p>

                <h5>Missing Table: tips</h5>
                <?php
                $missing = safeQuery($pdo, "SELECT id FROM tips LIMIT 5");
                echo "<div id='missing-output'></div>";
                if (isset($missing['error'])) {
                    echo "<div class='alert alert-danger'>
                            <strong>Error:</strong> " . htmlspecialchars($missing['error']) . "
                          </div>";
                    echo "<pre>{}</pre>";
                } else {
                    echo "<pre>" . htmlspecialchars(json_encode($missing, JSON_PRETTY_PRINT)) . "</pre>";
                }
                ?>

                <h5>Bad Credentials</h5>
                <?php
                $badPdo  = connDb(['DB_PASS' => 'wrong']);
                $badTest = safeQuery($badPdo, "SELECT 1");
                echo "<div id='bad-output'></div>";
                if (isset($badTest['error'])) {
                    echo "<div class='alert alert-danger'>
                            <strong>Error:</strong> " . htmlspecialchars($badTest['error']) . "
                          </div>";
                    echo "<pre>{}</pre>";
                } else {
                    echo "<pre>" . htmlspecialchars(json_encode($badTest, JSON_PRETTY_PRINT)) . "</pre>";
                }
                ?>

            <?php endif; ?>
        </div>
    </div>
</div>
</body>
</html>
EOF

# docs/README.md
cat > "$BASE/docs/README.md" << 'EOF'
# DB Dashboard

Generated by setup-db-dashboard.sh

- Uses .env for DB defaults
- Independent overrides via query string
- SELECT-only query tester (destructive SQL blocked)
- Diagnostics, schema inspector, profiler, failure demo
EOF

echo "DB Dashboard files generated in ./$BASE"