#!/bin/bash
# fix-diagnostics.sh
# Auto-create diagnostics files for test-sample-app

BASE_DIR="/Users/rbutare/Sites/apps/games/2player"

cd "$BASE_DIR" || { echo "Base dir not found"; exit 1; }

echo "🔧 Fixing diagnostics stack in $BASE_DIR"

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

function connDb(): ?PDO
{
    $host = '127.0.0.1';
    $db   = 'your_db';      // change to your DB
    $user = 'your_user';    // change to your user
    $pass = 'your_pass';    // change to your password
    $dsn  = "mysql:host=$host;dbname=$db;charset=utf8mb4";

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

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

function buildSelectAutoFix(?PDO $pdo, string $table): string
{
    if (!$pdo) {
        return "SELECT * FROM `$table` LIMIT 50";
    }

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

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

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

    return "SELECT $colList FROM `$table` LIMIT 50";
}
EOF

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

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

    if (!$pdo) {
        echo "DB connection: FALSE\n";
        echo "Generated SQL:\n";
        echo "SELECT * 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 "Generated SQL:\n";
        echo "SELECT * FROM `$table` LIMIT 50\n";
        echo "</pre>";
        return;
    }

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

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

    echo "Generated SQL:\n";
    echo "SELECT $colList FROM `$table` LIMIT 50\n";
    echo "</pre>";
}
EOF

############################################
# test-sample-app.php
############################################
cat << 'EOF' > "$BASE_DIR/test-sample-app.php"
<?php
declare(strict_types=1);

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

$pdo = connDb();

$table = 'your_table'; // change to an existing table

debugSelectBuilder($pdo, $table);

$sql = buildSelectAutoFix($pdo, $table);

echo "<pre>Running SQL:\n$sql\n</pre>";

if ($pdo) {
    try {
        $stmt = $pdo->query($sql);
        $rows = $stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : [];
        echo "<pre>" . json_encode($rows, JSON_PRETTY_PRINT) . "</pre>";
    } catch (Throwable $e) {
        echo "<pre>Query failed: " . $e->getMessage() . "</pre>";
    }
} else {
    echo "<pre>No DB connection; using fallback SQL only.</pre>";
}
EOF

echo "✅ Diagnostics files created. Now open: http://localhost/test-sample-app.php"