#!/bin/bash
set -e

BASE="db-dashboard"
SRC="$BASE/src"
DOC="$BASE/docs"

echo "Generating DB Dashboard project..."

mkdir -p "$SRC" "$DOC"

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

###############################################
# conn-db.php
###############################################
cat << 'EOF' > "$SRC/conn-db.php"
<?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 << 'EOF' > "$SRC/diagnostics-select-builder.php"
<?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 << 'EOF' > "$SRC/diagnostics-debug.php"
<?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 (FULL WORKING VERSION)
###############################################
cat << 'EOF' > "$SRC/db-dashboard.php"
<?php
declare(strict_types=1);

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

/* Load .env */
$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' => '',
];

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

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

/* Connect */
$pdo = connDb($overrides);

/* Module */
$module = $_GET['module'] ?? 'home';

/* Safe query */
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 queries */
$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.</p>

<?php elseif ($module === 'connection'): ?>
            <h2>Connection Test</h2>
            <?= $pdo ? "<p class='text-success'>Connected.</p>" : "<p class='text-danger'>Failed.</p>" ?>

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

<?php elseif ($module === 'profiler'): ?>
            <h2>Table Profiler</h2>
            <pre><?= htmlspecialchars(json_encode(safeQuery($pdo, "SHOW 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
/* CUSTOM QUERY */
if ($_POST && isset($_POST['sql'])) {
    $raw = $_POST['sql'];
    $lower = strtolower(trim($raw));

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

    if ($lower) {
        $result = safeQuery($pdo, $raw);
        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>";
        }
    }
}

/* PREDEFINED */
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 diagnosticsDebug($pdo, 'information_schema.tables'); ?>
            <pre><?= diagnosticsSelectBuilder($pdo, 'information_schema.tables') ?></pre>

<?php elseif ($module === 'failure'): ?>
            <h2>Failure Demonstration</h2>

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

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

<?php endif; ?>

        </div>
    </div>
</div>
</body>
</html>
EOF

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

Generated by generate-dashboard.sh

- Uses .env for DB defaults
- Independent overrides
- SELECT-only query tester
- Diagnostics suite
- Schema inspector
- Table profiler
- Failure demo
EOF

echo "Dashboard code generated successfully in $BASE/"