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

let s:popup_table = ""


" 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


" Popup selection for table actions
function! s:ShowTablePopup()
    " Use Vim popup_menu if available
    if exists('*popup_menu')
        s:popup_table = getline(line('.'))
        let items = ['View Data', 'View Schema', 'Cancel']
        popup_menu(items, {})

    	nnoremap <buffer> <CR> :call <SID>HandlePopupSelection()<CR>
        return
    endif
    " Popup_menu not available
    echoerr 'Floating popup requires Vim8.2+.'
endfunction

function! s:HandlePopupSelection()
    let choice = getline('.')
    let table = s:popup_table
    call s:ClosePopup()
    if choice ==# 'View Data'
        call s:OpenTableData(table)
    elseif choice ==# 'View Schema'
        call s:OpenTableSchema(table)
    else
	call s:ClosePopup()
    endif
endfunction

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


" Modal menu: let user pick Data or Schema from a simple list
function! s:ShowTableMenuModal()
    let table = getline(line('.'))
    let opts = [ 'Select action for "' . table . '"', 'View Data', 'View Schema', 'Cancel' ]
    let choice = inputlist(opts)
    if choice == 1
        call s:ShowTableData()
    elseif choice == 2
        call s:ShowTableSchema()
    endif
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

" Internal: insert Data/Schema options under the selected table
function! s:InsertTableOptions()
    call s:ClearTableOptions()
    let tlnum = line('.')
    " Insert indented options
    call append(tlnum, '  Data')
    call append(tlnum+1, '  Schema')
    " Move cursor to the 'Data' line
    call cursor(tlnum+1, 1)
endfunction

" Internal: find the nearest table name above the cursor (first non-indented line)
function! s:GetNearestTableName()
    let lnum = line('.')
    while lnum > 0
        let text = getline(lnum)
        if text =~# '^\S'
            if text ==# 'Available Tables:'
                return ''
            endif
            return text
        endif
        let lnum -= 1
    endwhile
    return ''
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! s:ExecuteSQLQuery()
    " Get the content of the current buffer (SQL query)
    let query = join(getline(1, '$'), " ")
    
    " Append the query to history
    call add(s:query_history, query)

    let data = s:ExecuteDBQuery(query)

    tabnew
    enew
    execute 'file SQLQueryResult'
    call append(0, data)

    " Set the buffer file type to SQL for syntax highlighting
    setlocal filetype=sql

    setlocal buftype=nofile
    setlocal bufhidden=wipe
    setlocal nobuflisted
    setlocal noswapfile
    setlocal nomodifiable
endfunction

" Execute SQL from a visual selection
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, " ")

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

    " Execute the query and capture results
    let data = s:ExecuteDBQuery(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

function! OpenMariaDBConsole()
  " The command to connect to MariaDB. Customize it with your actual connection details.
  let cmd = 'mariadb -u your_username -p your_database_name'

  " Open a new terminal window at the bottom with 10 lines height.
  " Split the window horizontally; you can adjust the height by changing `10`.
  botright 10split
  call termopen(cmd)
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>