" autoload/dbtables.vim
" Functions are loaded on demand

if exists('*dbtables#*')
  finish
endif



"=============================================
" 				Utilities	
"=============================================

let s:query_history = []
let s:snippets = []



" - - - - - - - - - - - - - - - - - - - - - -
" 			DB/shell interaction
" - - - - - - - - - - - - - - - - - - - - - -

" 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, directly to the db
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

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

" Function to execute SQL from a visual selection with parameter prompt
function! dbtables#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

" 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



" - - - - - - - - - - - - - - - - - - - - - -
" 				Visuals	
" - - - - - - - - - - - - - - - - - - - - - -

" Display data in a new read only 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



"=============================================
" 				DBTables	
"=============================================
" Open the list of tables in the database as a
" buffer on the right side. Selecting a table
" shows either the tables schema or the data
" in the table.

function! dbtables#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

" 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



"=============================================
" 				Query History	
"=============================================
" Shows the list of recently ran queries.
" Selecting a query will re-run it.

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



"=============================================
" 				Query Snippets	
"=============================================
" Shows the list of pre-set query snippets.
" Selecting a snippet will open it in a new 
" tab to be executed or modified.

" View query snippets 
function! dbtables#OpenQuerySnippets()
    " Open the query snippets in a new split window
    execute '34 vsplit'
    enew

	" Populate the buffer with the list of snippet files
	let s:snippets = globpath(g:snippets_directory, '*', 0, 1)
	let l:display = mapnew(s:snippets, 'fnamemodify(v:val, ":t:r")')
	call setline(1, l:display)
	
	nnoremap <buffer> <CR> :call s:OpenInNewTab(line('.'),
				\ fnameescape(s:snippets[a:lnum-1]))<CR>

    setlocal buftype=nofile bufhidden=wipe nobuflisted noswapfile wrap
endfunction



"=============================================
" 				DB Console	
"=============================================
" Opens a console to interact with the DB. 

" Open MariaDB console
function! dbtables#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