@extends('layouts.app')

@section('content')
<div class="max-w-7xl mx-auto px-6 py-8">
    <div class="flex justify-between items-center mb-6">
        <div>
            <h1 class="text-3xl font-extrabold text-red-900 dark:text-red-400">Database Query Executor</h1>
            <p class="mt-2 text-red-600 dark:text-red-300 font-semibold">
                <i class="fas fa-exclamation-triangle mr-1"></i> Warning: Arbitrary SQL execution. Modifying records here disables application-level audits and can cause catastrophic data loss. Use with extreme caution.
            </p>
        </div>
        <div>
            <a href="{{ route('db_query.backup') }}" class="bg-blue-600 hover:bg-blue-700 text-white font-bold py-2 px-4 rounded shadow-md flex items-center transition">
                <i class="fas fa-download mr-2"></i> Download DB Backup
            </a>
        </div>
    </div>

    {{-- Error message --}}
    @if(isset($error) && $error)
        <div class="bg-red-100 border-l-4 border-red-500 text-red-700 p-4 mb-6 relative shadow-md" role="alert">
            <p class="font-bold">SQL Execution Error</p>
            <p class="mt-1 font-mono text-sm whitespace-pre-wrap">{{ $error }}</p>
        </div>
    @endif
    
    @if(session('error'))
        <div class="bg-red-100 border-l-4 border-red-500 text-red-700 p-4 mb-6 relative shadow-md" role="alert">
            <p class="font-bold">Action Failed</p>
            <p class="mt-1 text-sm">{{ session('error') }}</p>
        </div>
    @endif

    @error('backup_downloaded')
        <div class="bg-red-100 border-l-4 border-red-500 text-red-700 p-4 mb-6 relative shadow-md" role="alert">
            <p class="mt-1 text-sm font-bold">{{ $message }}</p>
        </div>
    @enderror

    <div class="grid grid-cols-1 lg:grid-cols-4 gap-6">
        {{-- Left Sidebar: Database Tables Browser --}}
        <div class="col-span-1">
            <div class="bg-white dark:bg-gray-800 rounded shadow-md border border-gray-200 dark:border-gray-700 h-[600px] flex flex-col sticky top-6">
                <div class="p-4 border-b border-gray-200 dark:border-gray-700 bg-gray-50 dark:bg-gray-900 rounded-t">
                    <h2 class="font-bold text-gray-700 dark:text-gray-300">Database Tables ({{ count($tables) }})</h2>
                    <input type="text" id="tableSearch" placeholder="Filter tables..." class="mt-3 w-full px-3 py-1.5 text-sm border border-gray-300 rounded focus:ring-red-500 focus:border-red-500 dark:bg-gray-700 dark:border-gray-600 dark:text-gray-200">
                </div>
                <div class="overflow-y-auto flex-1 p-2">
                    <ul id="tableList" class="space-y-1">
                        @foreach($tables as $table)
                            <li>
                                <button type="button" onclick="setQuery('{{ $table }}')" class="w-full text-left px-3 py-2 text-sm text-gray-700 dark:text-gray-300 hover:bg-red-50 dark:hover:bg-red-900/30 hover:text-red-700 dark:hover:text-red-400 rounded transition flex items-center">
                                    <i class="fas fa-table text-gray-400 dark:text-gray-500 mr-2"></i> {{ $table }}
                                </button>
                            </li>
                        @endforeach
                    </ul>
                </div>
            </div>
        </div>

        {{-- Right Content: Query Editor and Results --}}
        <div class="col-span-1 lg:col-span-3">
            {{-- Query Form --}}
            <div class="bg-white dark:bg-gray-800 rounded shadow-md p-6 mb-8 border border-gray-200 dark:border-gray-700">
                <form action="{{ route('db_query.execute') }}" method="POST" x-data="{ backupConfirmed: false }">
            @csrf
            <div class="mb-4">
                <label for="query" class="block text-sm font-bold text-gray-700 dark:text-gray-300 mb-2">SQL Query</label>
                <textarea 
                    name="query" 
                    id="query" 
                    rows="8" 
                    class="w-full font-mono text-sm p-4 border border-gray-300 dark:border-gray-600 rounded bg-gray-50 dark:bg-gray-900 text-gray-900 dark:text-gray-100 focus:ring-red-500 focus:border-red-500" 
                    placeholder="SELECT * FROM users LIMIT 10;"
                    required>{{ isset($sql) ? $sql : old('query') }}</textarea>
                @error('query')
                    <p class="text-red-500 text-xs italic mt-2">{{ $message }}</p>
                @enderror
            </div>
            
            <div class="mb-6 bg-yellow-50 dark:bg-yellow-900 border border-yellow-200 dark:border-yellow-700 rounded p-4">
                <label class="flex items-center cursor-pointer">
                    <input type="checkbox" name="backup_downloaded" id="backup_downloaded" value="1" required x-model="backupConfirmed" class="w-5 h-5 text-red-600 border-gray-300 rounded focus:ring-red-500 dark:focus:ring-red-600 dark:ring-offset-gray-800 focus:ring-2 dark:bg-gray-700 dark:border-gray-600">
                    <span class="ml-3 text-sm font-medium text-gray-800 dark:text-gray-200">
                        I confirm that I have downloaded a recent database backup before executing this query. (This is required to proceed)
                    </span>
                </label>
            </div>

            <div class="flex items-center justify-between">
                <button type="submit" :disabled="!backupConfirmed" :class="!backupConfirmed ? 'opacity-50 cursor-not-allowed' : ''" class="bg-red-600 hover:bg-red-700 text-white font-bold py-2 px-6 rounded focus:outline-none focus:shadow-outline transition duration-150 shadow-md flex items-center">
                    <i class="fas fa-play mr-2"></i> Execute SQL
                </button>
                @if(session('executionTime'))
                    <span class="text-sm text-gray-500 dark:text-gray-400">Execution time: {{ session('executionTime') }} ms</span>
                @endif
            </div>
        </form>
    </div>

    {{-- Results Section for UPDATE/DELETE/INSERT --}}
    @if(isset($affectedRows) && $affectedRows !== null)
        <div class="bg-green-100 border-l-4 border-green-500 text-green-700 p-4 mb-6 shadow-md rounded" role="alert">
            <p class="font-bold mb-1"><i class="fas fa-check-circle mr-2"></i> Query Executed</p>
            <p>{{ is_numeric($affectedRows) ? $affectedRows . ' row(s) affected.' : $affectedRows }}</p>
        </div>
    @endif

    {{-- Results Section for SELECT --}}
    @if(isset($results) && $isSelect)
        <div class="bg-white dark:bg-gray-800 rounded shadow-md border border-gray-200 dark:border-gray-700 p-6">
            <h2 class="text-xl font-bold text-gray-800 dark:text-gray-200 mb-4">
                Query Results ({{ count($results) }} rows)
            </h2>
            
            @if(count($results) > 0)
                <!-- Top Scrollbar -->
                <div id="query-top-scrollbar" class="overflow-x-auto mb-2 hidden">
                    <div id="query-top-scrollbar-content" class="h-1"></div>
                </div>

                <div class="overflow-x-auto" id="query-table-container">
                    <table id="queryResultsTable" class="min-w-full divide-y divide-gray-200 dark:divide-gray-700 border dark:border-gray-600">
                        <thead class="bg-gray-50 dark:bg-gray-700">
                            <tr>
                                {{-- Dynamically generate headers from the first row's keys --}}
                                @foreach(get_object_vars($results[0]) as $column => $value)
                                    <th scope="col" class="px-6 py-3 text-left text-xs font-bold text-gray-500 dark:text-gray-300 uppercase tracking-wider border-b dark:border-gray-600">
                                        {{ $column }}
                                    </th>
                                @endforeach
                            </tr>
                        </thead>
                        <tbody class="bg-white dark:bg-gray-800 divide-y divide-gray-200 dark:divide-gray-700">
                            @foreach($results as $row)
                                <tr class="hover:bg-gray-50 dark:hover:bg-gray-700 transition">
                                    @foreach(get_object_vars($row) as $column => $value)
                                        <td class="px-6 py-4 whitespace-nowrap text-sm text-gray-900 dark:text-gray-300 border-b dark:border-gray-700">
                                            @if(is_null($value))
                                                <span class="text-gray-400 italic">NULL</span>
                                            @else
                                                <span class="truncate max-w-xs inline-block" title="{{ $value }}">{{ Str::limit((string)$value, 150) }}</span>
                                            @endif
                                        </td>
                                    @endforeach
                                </tr>
                            @endforeach
                        </tbody>
                    </table>
                </div>
            @else
                <div class="text-center py-8 text-gray-500 dark:text-gray-400 bg-gray-50 dark:bg-gray-900 rounded border border-dashed dark:border-gray-700">
                    <p class="font-medium"><i class="fas fa-info-circle mr-2"></i> Query executed successfully but returned 0 rows.</p>
                </div>
            @endif
        </div>
    @endif
        </div>
    </div>
</div>
@endsection

@section('scripts')
<script>
    $(document).ready(function() {
        if ($('#queryResultsTable').length > 0) {
            var table = $('#queryResultsTable').DataTable({
                pageLength: 50,
                scrollX: true,
                dom: 'Bfrtip',
                buttons: [
                    {
                        extend: 'excelHtml5',
                        text: '<i class="fas fa-file-excel mr-2"></i> Export to Excel',
                        className: 'btn btn-success'
                    },
                    {
                        extend: 'csvHtml5',
                        text: '<i class="fas fa-file-csv mr-2"></i> Export to CSV',
                        className: 'btn btn-info'
                    }
                ]
            });

            // Top Scrollbar Synchronization
            $('#query-top-scrollbar').removeClass('hidden');
            
            const topScrollbar = document.getElementById('query-top-scrollbar');
            const topScrollbarContent = document.getElementById('query-top-scrollbar-content');
            
            function setupScrollSync() {
                const dataTableScrollBody = document.querySelector('.dataTables_scrollBody');
                
                if (!dataTableScrollBody) return;

                function syncScrollWidth() {
                    const innerTable = dataTableScrollBody.querySelector('table');
                    if (innerTable && topScrollbarContent) {
                        topScrollbarContent.style.width = innerTable.offsetWidth + 'px';
                    }
                }

                syncScrollWidth();
                window.addEventListener('resize', syncScrollWidth);
                table.on('draw column-sizing.dt', syncScrollWidth);

                // Sync top scrollbar -> bottom scrollbar
                topScrollbar.addEventListener('scroll', () => {
                    dataTableScrollBody.scrollLeft = topScrollbar.scrollLeft;
                });

                // Sync bottom scrollbar -> top scrollbar
                dataTableScrollBody.addEventListener('scroll', () => {
                    topScrollbar.scrollLeft = dataTableScrollBody.scrollLeft;
                });
            }

            // Let DataTables finish rendering its DOM structure before attaching
            setTimeout(setupScrollSync, 300);
        }
    });

    // Make tab key work in textarea
    document.getElementById('query').addEventListener('keydown', function(e) {
        if (e.key == 'Tab') {
            e.preventDefault();
            var start = this.selectionStart;
            var end = this.selectionEnd;

            // set textarea value to: text before caret + tab + text after caret
            this.value = this.value.substring(0, start) +
                "\t" + this.value.substring(end);

            // put caret at right position again
            this.selectionStart =
                this.selectionEnd = start + 1;
        }
    });

    // Sidebar table filter
    document.getElementById('tableSearch').addEventListener('input', function(e) {
        const filter = e.target.value.toLowerCase();
        const items = document.querySelectorAll('#tableList li');
        items.forEach(item => {
            const text = item.textContent.toLowerCase();
            if (text.includes(filter)) {
                item.style.display = '';
            } else {
                item.style.display = 'none';
            }
        });
    });

    // Populate query box with default SELECT query for the chosen table
    function setQuery(tableName) {
        const queryBox = document.getElementById('query');
        queryBox.value = 'SELECT * FROM `' + tableName + '` LIMIT 100;';
        queryBox.focus();
        // Optional: Automatically trigger Alpine reactivity or highlight syntax
    }
</script>
<!-- DataTables Buttons CSS & JS for exporting results -->
<link rel="stylesheet" href="https://cdn.datatables.net/buttons/2.4.2/css/buttons.dataTables.min.css">
<script src="https://cdn.datatables.net/buttons/2.4.2/js/dataTables.buttons.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.2/js/buttons.html5.min.js"></script>

<style>
    .dt-buttons .dt-button {
        background-color: #ef4444 !important; /* text-red-500 */
        color: white !important;
        border: none !important;
        border-radius: 4px !important;
        padding: 5px 15px !important;
        margin-bottom: 10px;
    }
    .dt-buttons .dt-button:hover {
        background-color: #dc2626 !important; /* text-red-600 */
    }
</style>
@endsection