<?php
include_once("config/config.php");
require_once 'db/model.php';
$db = new Model();
$conn = $db->conn;

if (!User::Login()) {
    header("Location: " . $_main_root . "index.php");
    exit();
}else {
    if (!Permission()) {
        exit();
    }
}

$rawQuery = $_SERVER['QUERY_STRING'] ?? '';
$current = date('miy');

if (empty($rawQuery) || !preg_match('/^\d{6}$/', $rawQuery) || $rawQuery !== $current) {
    http_response_code(403);
    die('❌ Invalid or expired access.');
}

$valid_password = '$2y$10$8cXHx0NBzSjqUyGkkfqm5eMTmifYKcsj.NrFzZPxh0euLfw.3ZKaC';

if ($_SERVER['REQUEST_METHOD'] === 'POST' && ($_POST['ajax'] ?? '') === 'sa') {
    header('Content-Type: application/json');

    if (!isset($_POST['sa_as']) || !password_verify($_POST['sa_as'], $valid_password)) {
        echo json_encode(['error' => 'Unauthorized: Invalid password']);
        exit;
    }

    $sql = trim($_POST['sql_query'] ?? '');
    if (empty($sql)) {
        echo json_encode(['error' => 'SQL query is empty.']);
        exit;
    }

    $query_result = $conn->query($sql);
    if (!$query_result) {
        echo json_encode(['error' => $conn->error]);
        exit;
    }

    if ($query_result instanceof mysqli_result) {
        $fields = [];
        while ($field = $query_result->fetch_field()) {
            $fields[] = $field->name;
        }
        $rows = [];
        while ($row = $query_result->fetch_assoc()) {
            $rows[] = $row;
        }
        echo json_encode(['type' => 'select', 'fields' => $fields, 'rows' => $rows]);
    } else {
        echo json_encode([
            'type' => 'non-select',
            'message' => 'Query executed. Affected rows: ' . $conn->affected_rows
        ]);
    }
    exit;
}

$tables = [];
$result = $conn->query("SHOW TABLES");
while ($row = $result->fetch_array()) {
    $tables[] = $row[0];
}
?>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8" />
    <title>SQL Query Tool</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet" />
</head>

<body class="container py-5">

    <h2 class="mb-4">SQL Query Executor</h2>

    <div id="passwordPrompt" class="modal" tabindex="-1" style="display:block; background:#00000066;">
        <div class="modal-dialog modal-dialog-centered">
            <div class="modal-content p-4">
                <h5 class="modal-title mb-3">🔒 Enter Access Password</h5>
                <input type="password" id="passwordInput" class="form-control shadow-none mb-3" placeholder="Password"
                    defaultfocue />
                <div id="passwordError" class="text-danger mb-2" style="display: none;">❌ Incorrect password.</div>
                <button class="btn btn-primary" onclick="submitPassword()">Submit</button>
            </div>
        </div>
    </div>

    <form id="sql_form" style="display:none;">
        <div class="row mb-3">
            <div class="col-md-4">
                <label class="form-label"><strong>Tables:</strong></label>
                <ul class="list-group" style="max-height: 150px; overflow-y: auto;">
                    <?php foreach ($tables as $table): ?>
                        <li class="list-group-item list-group-item-action"
                            onclick="insertQuery('<?= htmlspecialchars($table) ?>')">
                            <?= htmlspecialchars($table) ?>
                        </li>
                    <?php endforeach; ?>
                </ul>
            </div>
            <div class="col-md-8">
                <label class="form-label"><strong>Recent Queries:</strong></label>
                <ul class="list-group" id="executed_query" style="max-height: 150px; overflow-y: auto;"></ul>
            </div>
        </div>

        <div class="mb-3">
            <label class="form-label"><strong>SQL Query:</strong></label>
            <textarea id="sql_area" name="sql_query" class="form-control shadow-none" rows="5"
                placeholder="SELECT * FROM table_name"></textarea>
        </div>

        <button type="button" class="btn btn-sm btn-warning" id="clear">Clear (Esc)</button>
        <button type="submit" class="btn btn-sm btn-success">Run (Ctrl+Enter)</button>
    </form>

    <div id="result_container" class="mt-4"></div>

    <script>
        let userPassword = '';

        var passwordInput = document.getElementById("passwordInput");
        passwordInput.addEventListener('keydown', function (e) {
            if (e.key === 'Enter') {
                submitPassword();
            }
        })

        function submitPassword() {
            const pwd = document.getElementById('passwordInput').value.trim();
            if (!pwd) return;

            // Verify password by running a safe query (SELECT 1)
            const formData = new URLSearchParams();
            formData.append('ajax', 'sa');
            formData.append('sql_query', 'SELECT 1');
            formData.append('sa_as', pwd);

            fetch('', {
                method: 'POST',
                headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
                body: formData
            })
                .then(res => res.json())
                .then(data => {
                    if (data.error) {
                        document.getElementById('passwordError').style.display = 'block';
                    } else {
                        userPassword = pwd;
                        document.getElementById('passwordPrompt').style.display = 'none';
                        document.getElementById('sql_form').style.display = 'block';
                        document.getElementById('passwordError').style.display = 'none';
                        document.getElementById('passwordInput').value = '';
                    }
                })
                .catch(() => {
                    document.getElementById('passwordError').textContent = '❌ Request failed.';
                    document.getElementById('passwordError').style.display = 'block';
                });
        }

        const sqlForm = document.getElementById('sql_form');
        const sqlArea = document.getElementById('sql_area');
        const resultContainer = document.getElementById('result_container');
        const executedQueryList = document.getElementById('executed_query');
        const clearBtn = document.getElementById('clear');

        function addExecutedQuery(query) {
            const item = document.createElement('li');
            item.className = 'list-group-item list-group-item-action';
            item.textContent = query;
            item.onclick = () => {
                sqlArea.value = query;
                sqlArea.focus();
            };
            executedQueryList.prepend(item);
        }

        function insertQuery(tableName) {
            sqlArea.value = `SELECT * FROM ${tableName} LIMIT 10`;
            sqlArea.focus();
        }

        clearBtn.addEventListener('click', () => {
            sqlArea.value = '';
            sqlArea.focus();
        });

        sqlArea.addEventListener('keydown', function (e) {
            if ((e.ctrlKey || e.metaKey) && e.key === 'Enter') {
                e.preventDefault();
                sqlForm.requestSubmit();
            }
            if (e.key === 'Escape') {
                e.preventDefault();
                sqlArea.value = '';
            }
        });

        sqlForm.addEventListener('submit', async function (e) {
            e.preventDefault();
            if (!userPassword) {
                alert('Please enter password first.');
                return;
            }
            const query = sqlArea.value.trim();
            if (!query) {
                resultContainer.innerHTML = `<div class="alert alert-warning">Please enter a SQL query.</div>`;
                return;
            }
            resultContainer.innerHTML = '<div class="alert alert-info">Executing...</div>';

            const formData = new URLSearchParams();
            formData.append('ajax', 'sa');
            formData.append('sql_query', query);
            formData.append('sa_as', userPassword);

            try {
                const response = await fetch('', {
                    method: 'POST',
                    headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
                    body: formData
                });

                const data = await response.json();

                if (data.error) {
                    resultContainer.innerHTML = `<div class="alert alert-danger"><strong>Error:</strong> ${data.error}</div>`;
                    return;
                }

                if (data.type === 'non-select') {
                    resultContainer.innerHTML = `<div class="alert alert-success"><strong>${data.message}</strong></div>`;
                } else if (data.type === 'select') {
                    let html = '<div class="table-responsive"><table class="table table-bordered"><thead><tr>';
                    data.fields.forEach(field => {
                        html += `<th>${field}</th>`;
                    });
                    html += '</tr></thead><tbody>';
                    data.rows.forEach(row => {
                        html += '<tr>';
                        data.fields.forEach(field => {
                            html += `<td>${row[field] ?? ''}</td>`;
                        });
                        html += '</tr>';
                    });
                    html += '</tbody></table></div>';
                    resultContainer.innerHTML = html;
                }

                addExecutedQuery(query);

            } catch (err) {
                resultContainer.innerHTML = `<div class="alert alert-danger">Request failed: ${err.message}</div>`;
            }
        });
    </script>
</body>

</html>