notex.nvim/lua/notex/database/init.lua

-- Database connection and initialization module
local M = {}

local sqlite3 = require('lsqlite3')
local utils = require('notex.utils')

-- Database connection state
local db = nil
local db_path = nil

-- Initialize database connection
function M.init(path)
  if db then
    return true, "Database already initialized"
  end

  db_path = path or vim.fn.stdpath('data') .. '/notex/notex.db'

  -- Ensure directory exists
  local dir = vim.fn.fnamemodify(db_path, ':h')
  vim.fn.mkdir(dir, 'p')

  local ok, err = pcall(function()
    db = sqlite3.open(db_path)
  end)

  if not ok then
    return false, "Failed to open database: " .. tostring(err)
  end

  -- Enable foreign key constraints
  db:exec("PRAGMA foreign_keys = ON")

  -- Set WAL mode for better performance
  db:exec("PRAGMA journal_mode = WAL")

  -- Set synchronous mode for performance vs safety balance
  db:exec("PRAGMA synchronous = NORMAL")

  return true, "Database initialized successfully"
end

-- Get database connection
function M.get_connection()
  if not db then
    error("Database not initialized. Call init() first.")
  end
  return db
end

-- Close database connection
function M.close()
  if db then
    db:close()
    db = nil
    return true, "Database closed successfully"
  end
  return false, "Database not initialized"
end

-- Execute query with error handling
function M.execute(query, params)
  local conn = M.get_connection()
  local stmt = conn:prepare(query)

  if not stmt then
    return false, "Failed to prepare query: " .. query
  end

  local result = {}
  local ok, err = pcall(function()
    if params then
      stmt:bind_names(params)
    end

    for row in stmt:nrows() do
      table.insert(result, row)
    end

    stmt:finalize()
  end)

  if not ok then
    stmt:finalize()
    return false, "Query execution failed: " .. tostring(err)
  end

  return true, result
end

-- Execute transaction
function M.transaction(queries)
  local conn = M.get_connection()
  local ok, err = pcall(function()
    conn:exec("BEGIN TRANSACTION")

    for _, query_data in ipairs(queries) do
      local stmt = conn:prepare(query_data.query)
      if query_data.params then
        stmt:bind_names(query_data.params)
      end
      stmt:step()
      stmt:finalize()
    end

    conn:exec("COMMIT")
  end)

  if not ok then
    conn:exec("ROLLBACK")
    return false, "Transaction failed: " .. tostring(err)
  end

  return true, "Transaction completed successfully"
end

-- Get database status
function M.status()
  if not db then
    return {
      initialized = false,
      path = nil,
      size_bytes = 0,
      wal_mode = false
    }
  end

  local size = 0
  local file = io.open(db_path, "r")
  if file then
    size = file:seek("end")
    file:close()
  end

  local wal_mode = false
  local wal_file = db_path .. "-wal"
  local wal = io.open(wal_file, "r")
  if wal then
    wal_mode = true
    wal:close()
  end

  return {
    initialized = true,
    path = db_path,
    size_bytes = size,
    wal_mode = wal_mode
  }
end

return M