<!DOCTYPE html>
<html lang="en">
<head>
    <!-- Specifies the character encoding of the HTML document -->
    <meta charset="UTF-8"> 

    <!-- Makes the page responsive by setting the viewport width to the device's width -->
    <meta name="viewport" content="width=device-width, initial-scale=1.0"> 

    <!-- Title that appears in the browser tab -->
    <title>Lightweight DBMS</title> 

    <!-- Links the external CSS stylesheet located in the static folder -->
    <link rel="stylesheet" href="{{ url_for('static', filename='style.css') }}"> 

    <!-- Links the jQuery library for simplifying JavaScript tasks -->
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script> 
</head>
<body>
    <!-- Header section at the top of the page -->
    <header>
        <!-- Main title of the application -->
        <img src="{{ url_for('static', filename='DBMS_logo.png') }}" alt="Logo" class="logo">
        <h1>Light Weight DBMS</h1>
    </header>

    <!-- Main content section of the page -->
    <main>
        <!-- Left box for user input -->
        <div id="query-box">
            <h2>Submit SQL Query</h2> <!-- Subheading for the query section -->
            
            <!-- Textarea where users input SQL queries -->
            <textarea id="query-input" placeholder="Enter your SQL query here:"></textarea>

            <!-- Button to execute the SQL query -->
            <button id="submit-query">Execute Query</button>

            <!-- Button to export and close the database -->
            <button id="close-db">Export & Close Database</button>
        </div>

        <!-- Right box for displaying query results -->
        <div id="data-box">
            <h2>Database Output</h2> <!-- Subheading for the output section -->
            
            <!-- Container to display the query output -->
            <div id="output"></div>
        </div>
    </main>

    <!-- Footer section at the bottom of the page -->
    <footer>
        <!-- Attribution message in the footer -->
        <p>Powered By - Github.com/TabithaSW</p>
    </footer>
    <!-- javaScript, calls my app.py functions execute and close, formatted so data appears tabular. -->
    <script>
        // Handles the submission of a query when the "Execute Query" button is clicked.
        // The html submit-query is the button, so The .click(function() { ... }) part attaches a click event handler to the button.
        $('#submit-query').click(function () {
            const query = $('#query-input').val();
    
            $.post('/execute', { query: query }, function (data) {
                // Retrieves the value (text) from the <textarea> with ID query-input.
                // This is the SQL query entered by the user.
                if (data.success) {
                    const results = data.result;
    
                    if (Array.isArray(results) && results.length > 0 && Array.isArray(results[0])) {
                        let tableHtml = '<table border="1" style="width: 100%; border-collapse: collapse; text-align: left;">';
                        tableHtml += '<thead><tr>';
    
                        const keys = results[0][0] instanceof Object 
                            ? Object.keys(results[0][0]) 
                            : Array.from({ length: results[0][0].length }, (_, i) => `Column ${i + 1}`);
                        
                        for (const key of keys) {
                            tableHtml += `<th>${key}</th>`;
                        }
                        tableHtml += '</tr></thead><tbody>';
    
                        for (const row of results[0]) {
                            tableHtml += '<tr>';
                            if (row instanceof Object) {
                                for (const cell of Object.values(row)) {
                                    tableHtml += `<td>${cell}</td>`;
                                }
                            } else {
                                for (const cell of row) {
                                    tableHtml += `<td>${cell}</td>`;
                                }
                            }
                            tableHtml += '</tr>';
                        }
    
                        tableHtml += '</tbody></table>';
                        $('#output').html(tableHtml);
                    } else {
                        $('#output').html(`<pre>${JSON.stringify(results, null, 2)}</pre>`);
                    }
                } else {
                    $('#output').html(`<p style="color: red;">Error: ${data.error}</p>`);
                }
            });
        });
    
        $('#close-db').click(function () {
            $.post('/close', {}, function (data) {
                if (data.success) {
                    alert(data.message);
                } else {
                    alert(`Error: ${data.error}`);
                }
            });
        });
    </script>
</body>
</html>
