let g:db_user = ""
let g:db_password = ""
let g:db_name = ""
let g:db_host = ""

" Execute shell commands
function! s:ExecuteShellCommand(command)
    let result = system(a:command . ' 2>&1')
    return [v:shell_error == 0, result]
endfunction

" Execute a SQL query
function! s:ExecuteDBQuery(query)
    let command = printf('mariadb --user=%s --password=%s --database=%s --host=%s -e %s',
                \ shellescape(g:db_user), shellescape(g:db_password),
                \ shellescape(g:db_name), shellescape(g:db_host),
                \ shellescape(a:query))
    
    let [success, result] = s:ExecuteShellCommand(command)
    if success
        return split(result, "\n")
    else
        call s:HandleDBError(result, command)
        return []
    endif
endfunction

" Handle database command errors
function! s:HandleDBError(result, command)
    echoerr 'Shell command failed: ' . a:command
    if a:result =~# 'Access denied'
        echoerr 'Authentication error: Check your username and password.'
    elseif a:result =~# 'Unknown database'
        echoerr 'Database error: The specified database does not exist.'
    elseif a:result =~# 'Could not connect'
        echoerr 'Connection error: Verify host and network connectivity.'
    elseif a:result =~# 'You have an error in your SQL syntax'
        echoerr 'Syntax error in SQL query.'
    else
        echoerr 'Unexpected error: ' . a:result
    endif
endfunction

" Display schema or data in a new buffer
function! s:OpenInNewTab(title, data)
    tabnew
    enew
    execute 'file ' . a:title
    if !empty(a:data)
        call append(0, a:data)
    else
        call append(0, 'No data found or an error occurred.')
    endif
    setlocal filetype=sql buftype=nofile bufhidden=wipe nobuflisted noswapfile nomodifiable
endfunction

" Open the database schema
function! s:OpenTableSchema(table)
    call s:OpenInNewTab(a:table . '_schema', s:ExecuteDBQuery('DESCRIBE ' . a:table . ';'))
endfunction

" Open the table data
function! s:OpenTableData(table)
    call s:OpenInNewTab(a:table, s:ExecuteDBQuery('SELECT * FROM ' . a:table . ';'))
endfunction

let s:popup_table = ""
" Popup selection for table actions
function! s:ShowTablePopup()
    " Use Vim popup_menu if available
    if exists('*popup_menu')
        let s:popup_table = getline(line('.'))
        let items = ['View Data', 'View Schema', 'Cancel']
        call popup_menu(items, #{ 
		\ callback: '<SID>HandlePopupSelection',
		\ })
        return
    endif
    " Popup_menu not available
    echoerr 'Floating popup requires Vim8.2+.'
    call s:ShowTableMenuModal()
endfunction

function! s:HandlePopupSelection(id, result)
    let table = s:popup_table
    call s:ClosePopup()
    if a:result == 1
        call s:OpenTableData(table)
    elseif a:result == 2
        call s:OpenTableSchema(table)
    endif
endfunction

function! s:ClosePopup()
    unlet! s:popup_table
endfunction

" Open the database tables list
function! s:OpenDBTablesWindow()
    execute '34 vsplit'
    enew
    file DBTables
    let tables = s:ExecuteDBQuery("SHOW TABLES;")
    if empty(tables)
        echo "No tables found or an error occurred."
    else
        call append(0, ['Available Tables:'] + tables[1:])
        setlocal buftype=nofile bufhidden=wipe nobuflisted noswapfile nomodifiable nonumber norelativenumber winfixwidth
        nnoremap <buffer> <CR> :call <SID>ShowTablePopup()<CR>
    endif
endfunction

let s:query_history = []

" Function to execute SQL from the current buffer with parameter prompt
function! s:ExecuteSQLQuery()
    let query = join(getline(1, '$'), "\n")
    call s:PromptAndExecuteQuery(query)
endfunction

" Function to execute SQL from a visual selection with parameter prompt
function! s:ExecuteVisualSQLQuery() range
    " Get the content of the selected lines as a single SQL query
    let lines = getline(a:firstline, a:lastline)
    let query = join(lines, "\n")

    " Execute query with prompt for parameters
    call s:PromptAndExecuteQuery(query)
endfunction

" Handle parameter prompts and execute query
function! s:PromptAndExecuteQuery(query)
    " Identify parameters in the form of :parameter_name
    let pattern = ':\(\k\+\)'
    let params = []
    
    " Find all matches in the query
    let start = 0
    while match(a:query, pattern, start) != -1
        let match = matchstr(a:query, pattern, start)
        if index(params, match) == -1
            call add(params, match)
        endif
        let start = matchend(a:query, pattern, start)
    endwhile

    " Initialize a dictionary for parameters and their values
    let param_values = {}
    
    " Prompt user for each parameter
    for param in params
        let value = input('Enter value for ' . param . ': ')
        let param_values[param] = value
    endfor
    
    " Replace parameters in the query with user-provided values
    let updated_query = a:query
    for [param, value] in items(param_values)
        let updated_query = substitute(updated_query, param, value, 'g')
    endfor

    call add(s:query_history, updated_query)
    call s:OpenInNewTab('SQLQueryResult', s:ExecuteDBQuery(updated_query))
endfunction

" View query history
function! s:OpenQueryHistory()
    " Open the query history in a new split window
    execute 'vsplit'
    enew
    call setline(1, s:query_history)
    nnoremap <buffer> <CR> :call <SID>ExecuteHistoryQuery(line('.'))<CR>
    setlocal buftype=nofile bufhidden=wipe nobuflisted noswapfile
endfunction

function! s:ExecuteHistoryQuery(lnum)
    let query = getline(a:lnum)
    execute 'normal! i' . query
    " Optional execution or further processing
endfunction

" Open MariaDB console
function! DBConsole()
    " Save the current cursor position
    let save_cursor = getpos(".")
    let command = printf('mariadb --user=%s --password=%s --database=%s --host=%s',
                  \ g:db_user, g:db_password,
                  \ g:db_name, g:db_host)
    
    let command =  substitute(command, '\n', '', 'g')
    execute ':term ' . command
    call setpos(".", save_cursor)
endfunction

" Keybinding to open query history
nnoremap <Leader>qh :call <SID>OpenQueryHistory()<CR>
nnoremap <Leader>db :call DBConsole()<CR>
command! DBTables call s:OpenDBTablesWindow()
command! ExecuteSQL call s:ExecuteSQLQuery()

" Function to refresh the list of tables
nnoremap <Leader>rt :DBTables<CR>

" Function to execute the contents of the current buffer as an SQL query
nnoremap <Leader>eq :ExecuteSQL<CR>

" Function to open the database tables window
nnoremap <Leader>dt :DBTables<CR>

" Optional: Shortcut to open MariaDB console
nnoremap <Leader>mc :call DBConsole()<CR>
" Map visual selection to execute selected SQL
xnoremap <silent> <Leader>ev :call <SID>ExecuteVisualSQLQuery()<CR>