let g:db_user = ""      " Replace with your default database username
let g:db_password = ""  " Replace with your default database password
let g:db_name = ""      " Replace with your default database name
let g:db_host = ""      " Replace with your default database name


" Helper function to execute a command and handle errors
function! s:ExecuteShellCommand(command)
    let result = system(a:command . ' 2>&1')
    if v:shell_error != 0
        return [v:false, result]
    endif
    return [v:true, result]
endfunction

function! s:ExecuteDBQuery(query)
    let db_user = shellescape(g:db_user)
    let db_password = shellescape(g:db_password)
    let db_name = shellescape(g:db_name)
    let db_host = shellescape(g:db_host)

    let command = 'mariadb --user=' . db_user
                \ . ' --password=' . db_password
                \ . ' --database=' . db_name
                \ . ' --host=' . db_host
                \ . ' -e ' . shellescape(a:query)

    let [success, result] = s:ExecuteShellCommand(command)

    if !success
        call s:HandleDBError(result, command)
        return []
    endif
    return split(result, "\n")
endfunction

function! s:HandleDBError(result, command)
    echoerr 'Shell command failed: ' . a:command
    echoerr a:result

    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


" Show the structure/schema of the selected table
function! s:ShowTableSchema(table)
    " Open a new tab for schema
    tabnew
    enew
    execute 'file ' . table . '_schema'
    " Describe the table structure
    let data = s:ExecuteDBQuery('DESCRIBE ' . table . ';')
    if len(data) > 0
        call append(0, data)
    else
        call append(0, 'No schema found or an error occurred.')
    endif
    " Set buffer options and syntax
    setlocal filetype=sql buftype=nofile bufhidden=wipe nobuflisted noswapfile nomodifiable
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


" Internal: remove any existing dropdown option lines from the DBTables buffer
function! s:ClearTableOptions()
    let to_del = []
    for lnum in range(1, line('$'))
        let ln = getline(lnum)
        if ln =~# '^\s\+\(Data\|Schema\)$'
            call add(to_del, lnum)
        endif
    endfor
    if !empty(to_del)
        for lnum in reverse(to_del)
            call deletebufline('%', lnum)
        endfor
    endif
endfunction


" Open table data for a given table name
function! s:OpenTableData(table)
    tabnew
    enew
    execute 'file ' . a:table
    let data = s:ExecuteDBQuery('SELECT * FROM ' . a:table . ';')
    if !empty(data)
        call append(0, data)
    else
        call append(0, 'No data found.')
    endif
    setlocal buftype=nofile bufhidden=wipe nobuflisted noswapfile nomodifiable
endfunction

" Open table schema for a given table name
function! s:OpenTableSchema(table)
    tabnew
    enew
    execute 'file ' . a:table . '_schema'
    let data = s:ExecuteDBQuery('DESCRIBE ' . a:table . ';')
    if !empty(data)
        call append(0, data)
    else
        call append(0, 'No schema found or an error occurred.')
    endif
    setlocal filetype=sql buftype=nofile bufhidden=wipe nobuflisted noswapfile nomodifiable
endfunction

function! s:OpenDBTablesWindow()
    " Open a new vertical split window
    execute '34 vsplit'
    " Create a new buffer
    enew
    " Set buffer name to "DBTables"
    file DBTables

    let tables = s:ExecuteDBQuery("SHOW TABLES;")
    if empty(tables)
        echo "No tables found or an error occurred."
        return
    endif

    " Display the tables in the buffer
    call append(0, 'Available Tables:')
    call remove(tables, 0)
    for table in tables
        call append('$', table)
    endfor

    " Set buffer options
    setlocal buftype=nofile
    setlocal bufhidden=wipe
    setlocal nobuflisted
    setlocal noswapfile
    setlocal nomodifiable
    setlocal nonumber
    setlocal norelativenumber
    setlocal winfixwidth

    " Map <Enter> to a modal menu (data or schema)
    nnoremap <buffer> <CR> :call <SID>ShowTablePopup()<CR>
endfunction


function! s:ShowTableData()
    " Get the current line (table name)
    let lnum = line('.')
    let table_name = getline(lnum)

    " Open a new tab and create a new buffer
    tabnew
    enew
    " Set buffer name to the table name
    execute 'file ' . table_name

    let data = s:ExecuteDBQuery("SELECT * FROM " . table_name . ";")

    " Display the table data in the buffer
    if len(data) > 0
        call append(0, data)
    else
        call append(0, 'No data found.')
    endif

    " Set buffer options
    setlocal buftype=nofile
    setlocal bufhidden=wipe
    setlocal nobuflisted
    setlocal noswapfile
    setlocal nomodifiable
endfunction

let s:query_history = []

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

    " Execute query with prompt for parameters
    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


" Function to prompt user for parameter values 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, shellescape(value), 'g')
    endfor

    " Append the query to history
    call add(s:query_history, updated_query)

    " Execute the updated query
    let data = s:ExecuteDBQuery(updated_query)

    " Open results in a new tab
    tabnew
    enew
    execute 'file SQLQueryResult'
    call append(0, data)

    " Set buffer options and highlight
    setlocal filetype=sql
    setlocal buftype=nofile bufhidden=wipe nobuflisted noswapfile nomodifiable
endfunction


function! s:OpenQueryHistory()
    " Open the query history in a new split window
    execute 'vsplit'
    enew
    call setline(1, s:query_history)

    " Allow selecting a query to be put into a new buffer to execute
    nnoremap <buffer> <CR> :call <SID>ExecuteHistoryQuery(line('.'))<CR>

    " Set buffer options
    setlocal buftype=nofile
    setlocal bufhidden=wipe
    setlocal nobuflisted
    setlocal noswapfile
endfunction

function! s:ExecuteHistoryQuery(lnum)
    " Execute the selected query from history
    let query = getline(a:lnum)
    execute 'normal! i' . query
    " Optionally call the execute function directly or process
endfunction


function! DBConsole()
  " Save the current cursor position
  let save_cursor = getpos(".")

  "Format the files
  let db_user = g:db_user
  let db_password = g:db_password
  let db_name = g:db_name
  " let db_host = shellescape(g:db_host)
  let db_host = g:db_host



  let command = 'mariadb --user=' . db_user . ' --password=' . db_password . ' --database=' . db_name . ' --host=' . 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>