module main

import db.sqlite
import os
import readline
import time

const version = '0.1.0'

const help_text = 'vsqlite ${version} - SQLite CLI written in V

Usage:
  v sqlite <database>               Open database in interactive mode
  v sqlite <database> <stmt>        Execute a single SQL statement
  v sqlite <database> -f <file>     Execute SQL from a file
  v sqlite --help                   Show this help
  v sqlite --version                Show version

Interactive mode commands:
  .tables                          List all tables
  .schema [table]                  Show schema for table(s)
  .mode [table|csv|line|box|       Change output mode (default: table)
        markdown|json|html|
        insert [tbl]|quote]
  .headers [on|off]                Toggle column headers
  .nullvalue [str]                 Set NULL display string (default: NULL)
  .separator [sep]                 Set column separator (default: ,)
  .width [N1 N2 ...]               Set per-column widths (0 = reset to auto)
  .output [file|-]                 Redirect output to file (no arg = stdout)
  .once <file>                     Redirect next query result to file
  .timer [on|off]                  Toggle query execution timing (default: off)
  .explain <stmt>                  Show EXPLAIN QUERY PLAN tree for a statement
  .import <file> <table>           Import CSV/TSV into table (auto-detects .tsv/.tab)
  .export <file>                   Export last query to CSV
  .dump [file]                     Dump full SQL schema+data to stdout or file
  .load <file> [entry]             Load a SQLite extension (.so/.dylib)
  .bail [on|off]                   Stop on first error (default: off)
  .echo [on|off]                   Echo each statement before executing (default: off)
  .log [file|off]                  Log all statements to a file; off to disable
  .changes [on|off]                Show row-change count after each statement (default: off)
  .open <file>                     Open (or switch to) a different database file
  .databases                       List attached databases
  .indexes [table]                 List indexes
  .size                            Show database file size
  .read <file>                     Execute SQL file from within the REPL
  .show                            Print snapshot of all current settings
  .print [string...]               Print literal text (blank line if no args)
  .prompt MAIN [CONTINUE]          Set REPL prompt strings
  .eqp [on|off]                    Auto-EXPLAIN QUERY PLAN before each SELECT
  .trace [file|stderr|off]         Trace all SQL statements as they execute
  .timeout <ms>                    Set busy-wait timeout (milliseconds)
  .shell <cmd> / .system <cmd>     Run an OS command
  .backup <file>                   Backup database using VACUUM INTO
  .fullschema                      Full schema including sqlite_stat tables
  .dbinfo                          Database file info (page count, encoding, etc.)
  .stats                           Page and table statistics
  .lint                            Report missing indexes on FK columns
  .cd [directory]                  Change or show working directory
  .quit / .exit / Ctrl+D           Exit'

const history_file = os.join_path(os.home_dir(), '.vsqlite_history')

struct App {
mut:
    db           sqlite.DB
    rl           readline.Readline
    mode         OutputMode = .table
    headers      bool       = true
    last_rows    []sqlite.Row
    nullvalue    string = 'NULL'
    separator    string = ','
    col_widths   map[int]int
    output_path  string
    output_once  bool
    insert_table string = 'tbl'
    timer        bool
    bail         bool
    echo         bool
    log_path     string
    changes      bool
    db_path      string
    eqp          bool
    trace_path   string
    prompt       string = 'vsqlite> '
    prompt2      string = '     ...> '
}

fn main() {
    // When launched via `v sqlite`, os.args[1] is the subcommand name 'sqlite'.
    // Skip it so the remaining args start with the database path.
    raw := os.args[1..]
    args := if raw.len > 0 && raw[0] == 'sqlite' { raw[1..] } else { raw }

    if args.len == 0 || args[0] in ['--help', '-h'] {
        println(help_text)
        return
    }

    if args[0] in ['--version', '-v'] {
        println('vsqlite ${version}')
        return
    }

    mut db := sqlite.connect(args[0]) or {
        eprintln('Error: cannot open "${args[0]}": ${err}')
        exit(1)
    }
    db.exec_none('PRAGMA journal_mode=WAL')
    db.exec_none('PRAGMA foreign_keys=ON')

    mut app := App{
        db:      db
        db_path: args[0]
    }

    if args.len == 1 {
        app.interactive_mode()
    } else if args[1] == '-f' {
        if args.len < 3 {
            eprintln('Error: -f requires a file path')
            exit(1)
        }
        app.exec_file(args[2])
    } else {
        cmd := args[1..].join(' ')
        if cmd.starts_with('.') {
            app.dot_cmd(cmd)
        } else {
            app.run(cmd)
        }
    }
}

fn (mut app App) interactive_mode() {
    println('vsqlite ${version} - Type .help for commands, .quit to exit')
    println('')
    app.rl.skip_empty = true
    app.load_history()
    app.refresh_completions()
    mut buf := []string{}
    for {
        prompt := if buf.len == 0 { app.prompt } else { app.prompt2 }
        line := app.rl.read_line(prompt) or {
            if buf.len > 0 {
                eprintln('Incomplete statement discarded.')
            }
            break
        }
        trimmed := line.trim_space()
        if trimmed == '' {
            continue
        }
        if buf.len == 0 {
            if trimmed in ['.quit', '.exit', 'quit', 'exit'] {
                break
            }
            if trimmed.starts_with('.') {
                app.dot_cmd(trimmed)
                continue
            }
        }
        buf << line
        if stmt_complete(buf) {
            full := buf.join('\n').trim_space()
            for s in split_statements(full) {
                if s != '' {
                    app.run(s)
                }
            }
            buf = []string{}
        }
    }
    app.save_history()
    println('\nBye!')
}

fn stmt_complete(lines []string) bool {
    return lines.join('\n').trim_space().ends_with(';')
}

fn (mut app App) load_history() {
    history_load(mut app.rl, history_file)
}

fn (app App) save_history() {
    history_save(app.rl, history_file)
}

fn history_load(mut rl readline.Readline, path string) {
    content := os.read_file(path) or { return }
    for line in content.split('\n') {
        if line.len > 0 {
            rl.previous_lines << line.runes()
        }
    }
}

fn history_save(rl readline.Readline, path string) {
    lines := rl.previous_lines.map(it.string()).filter(it.len > 0)
    os.write_file(path, lines.join('\n') + '\n') or {}
}

fn (app App) make_format_opts() FormatOptions {
    return FormatOptions{
        mode:       app.mode
        headers:    app.headers
        nullvalue:  app.nullvalue
        separator:  app.separator
        col_widths: app.col_widths
        table_name: app.insert_table
    }
}

fn (mut app App) write_out(s string) {
    if app.output_path != '' {
        os.write_file(app.output_path, os.read_file(app.output_path) or { '' } + s + '\n') or {
            eprintln('Error: cannot write to "${app.output_path}": ${err}')
        }
    } else {
        println(s)
    }
}

fn (mut app App) finish_output() {
    if app.output_once {
        app.output_path = ''
        app.output_once = false
    }
}

fn (mut app App) log_stmt(stmt string) {
    if app.log_path == '' {
        return
    }
    existing := os.read_file(app.log_path) or { '' }
    os.write_file(app.log_path, existing + stmt + ';\n') or {}
}

fn (mut app App) trace_stmt(stmt string) {
    if app.trace_path == '' {
        return
    }
    if app.trace_path == 'stderr' {
        eprintln(stmt)
        return
    }
    existing := os.read_file(app.trace_path) or { '' }
    os.write_file(app.trace_path, existing + stmt + ';\n') or {}
}

fn (mut app App) run(stmt string) bool {
    if app.echo {
        println(stmt)
    }
    app.log_stmt(stmt)
    app.trace_stmt(stmt)
    upper := stmt.trim_space().to_upper()
    is_query := upper.starts_with('SELECT') || upper.starts_with('PRAGMA')
        || upper.starts_with('EXPLAIN') || upper.starts_with('WITH') || upper.starts_with('VALUES')

    if app.eqp && is_query && !upper.starts_with('EXPLAIN') {
        app.run_explain(stmt)
    }

    t0 := time.now()

    if is_query {
        rows := app.db.exec(stmt) or {
            eprintln('Error: ${err}')
            if app.bail {
                exit(1)
            }
            return false
        }
        elapsed := time.since(t0)
        if rows.len == 0 {
            app.finish_output()
            if app.timer {
                println('Run time: ${format_duration(elapsed)}')
            }
            return true
        }
        app.last_rows = rows
        app.write_out(format_opts(rows, app.make_format_opts()))
        app.write_out('(${rows.len} row${if rows.len == 1 { '' } else { 's' }})')
        app.finish_output()
        if app.timer {
            println('Run time: ${format_duration(elapsed)}')
        }
    } else {
        code := app.db.exec_none(stmt)
        if code != 101 && code != 100 {
            // sqlite_done=101, sqlite_row=100; anything else is an error
            eprintln('Error: exec failed (code ${code})')
            if app.bail {
                exit(1)
            }
            return false
        }
        elapsed := time.since(t0)
        affected := app.db.get_affected_rows_count()
        last_id := app.db.last_insert_rowid()
        if upper.starts_with('INSERT') {
            println('Inserted 1 row (rowid: ${last_id})')
        } else if upper.starts_with('UPDATE') || upper.starts_with('DELETE') {
            println('${affected} row${if affected == 1 { '' } else { 's' }} affected')
        } else {
            println('OK')
        }
        if app.changes {
            println('Changes: ${affected}')
        }
        if app.timer {
            println('Run time: ${format_duration(elapsed)}')
        }
        if upper.starts_with('CREATE') || upper.starts_with('DROP') || upper.starts_with('ALTER') {
            app.refresh_completions()
        }
    }
    return true
}

fn (mut app App) exec_file(path string) {
    content := os.read_file(path) or {
        eprintln('Error reading "${path}": ${err}')
        exit(1)
    }
    mut count := 0
    for stmt in split_statements(content) {
        trimmed := stmt.trim_space()
        // Strip comment-only lines to decide if the chunk has real SQL,
        // but pass the original (with comments) to SQLite so it can parse them.
        sql_content :=
            trimmed.split('\n').filter(!it.trim_space().starts_with('--')).join('\n').trim_space()
        if sql_content == '' {
            continue
        }
        app.run(trimmed)
        count++
    }
    println('Executed ${count} statement(s) from ${path}')
}

fn (mut app App) read_file_repl(path string) {
    content := os.read_file(path) or {
        eprintln('Error: cannot read "${path}": ${err}')
        return
    }
    mut count := 0
    for stmt in split_statements(content) {
        trimmed := stmt.trim_space()
        sql_content :=
            trimmed.split('\n').filter(!it.trim_space().starts_with('--')).join('\n').trim_space()
        if sql_content == '' {
            continue
        }
        app.run(trimmed)
        count++
    }
    println('Executed ${count} statement(s) from ${path}')
}

fn (mut app App) dot_cmd(cmd string) {
    parts := cmd.split(' ').filter(it != '')
    match parts[0] {
        '.help' {
            println(help_text)
        }
        '.tables' {
            tables := app.db.tables() or {
                eprintln('Error: ${err}')
                return
            }
            if tables.len == 0 {
                println('(no tables)')
            } else {
                println(tables.join('\n'))
            }
        }
        '.schema' {
            table := if parts.len > 1 { parts[1] } else { '' }
            result := app.db.schema(table) or {
                eprintln('Error: ${err}')
                return
            }
            if result == '' {
                println('(no schema found)')
            } else {
                println(result)
            }
        }
        '.mode' {
            if parts.len < 2 {
                println('Current mode: ${app.mode}')
                return
            }
            match parts[1] {
                'table' {
                    app.mode = .table
                }
                'csv' {
                    app.mode = .csv
                }
                'line' {
                    app.mode = .line
                }
                'box' {
                    app.mode = .box
                }
                'markdown' {
                    app.mode = .markdown
                }
                'json' {
                    app.mode = .json
                }
                'html' {
                    app.mode = .html
                }
                'insert' {
                    app.mode = .insert
                    if parts.len > 2 {
                        app.insert_table = parts[2]
                    }
                }
                'quote' {
                    app.mode = .quote
                }
                else {
                    eprintln('Unknown mode: ${parts[1]}. Use: table, csv, line, box, markdown, json, html, insert [tbl], quote')
                    return
                }
            }

            println('Mode set to: ${app.mode}')
        }
        '.headers' {
            if parts.len < 2 {
                println('Headers: ${if app.headers { 'on' } else { 'off' }}')
                return
            }
            match parts[1] {
                'on' { app.headers = true }
                'off' { app.headers = false }
                else { eprintln('Use: .headers on|off') }
            }
        }
        '.nullvalue' {
            if parts.len < 2 {
                println('NULL display: "${app.nullvalue}"')
                return
            }
            app.nullvalue = parts[1]
            println('NULL value set to "${app.nullvalue}"')
        }
        '.separator' {
            if parts.len < 2 {
                println('Separator: "${app.separator}"')
                return
            }
            mut sep := parts[1]
            sep = sep.replace('\\t', '\t').replace('\\n', '\n')
            app.separator = sep
            println('Separator set to "${app.separator}"')
        }
        '.width' {
            if parts.len < 2 {
                if app.col_widths.len == 0 {
                    println('Column widths: auto')
                } else {
                    mut pairs := []string{}
                    for k, v in app.col_widths {
                        pairs << 'col${k}=${v}'
                    }
                    println('Column widths: ${pairs.join(', ')}')
                }
                return
            }
            if parts.len == 2 && parts[1] == '0' {
                app.col_widths = map[int]int{}
                println('Column widths reset to auto')
                return
            }
            app.col_widths = map[int]int{}
            for i, part in parts[1..] {
                w := part.int()
                if w > 0 {
                    app.col_widths[i] = w
                }
            }
            println('Column widths set')
        }
        '.output' {
            if parts.len < 2 || parts[1] in ['stdout', '-'] {
                app.output_path = ''
                app.output_once = false
                println('Output reset to stdout')
            } else {
                os.write_file(parts[1], '') or {
                    eprintln('Error: cannot open "${parts[1]}": ${err}')
                    return
                }
                app.output_path = parts[1]
                app.output_once = false
                println('Output redirected to ${parts[1]}')
            }
        }
        '.once' {
            if parts.len < 2 {
                eprintln('Usage: .once <file>')
                return
            }
            os.write_file(parts[1], '') or {
                eprintln('Error: cannot open "${parts[1]}": ${err}')
                return
            }
            app.output_path = parts[1]
            app.output_once = true
        }
        '.timer' {
            if parts.len < 2 {
                println('Timer: ${if app.timer { 'on' } else { 'off' }}')
                return
            }
            match parts[1] {
                'on' {
                    app.timer = true
                    println('Timer: on')
                }
                'off' {
                    app.timer = false
                    println('Timer: off')
                }
                else {
                    eprintln('Use: .timer on|off')
                }
            }
        }
        '.explain' {
            if parts.len < 2 {
                eprintln('Usage: .explain <sql statement>')
                return
            }
            stmt := parts[1..].join(' ')
            app.run_explain(stmt)
        }
        '.import' {
            if parts.len < 3 {
                eprintln('Usage: .import <file> <table>')
                return
            }
            app.import_csv(parts[1], parts[2])
        }
        '.export' {
            if parts.len < 2 {
                eprintln('Usage: .export <file>')
                return
            }
            if app.last_rows.len == 0 {
                eprintln('No previous query results to export')
                return
            }
            write_csv(parts[1], app.last_rows, app.headers) or {
                eprintln('Error: ${err}')
                return
            }
            println('Exported ${app.last_rows.len} rows to ${parts[1]}')
        }
        '.dump' {
            text := dump_database(mut app.db)
            if parts.len > 1 {
                os.write_file(parts[1], text) or {
                    eprintln('Error: cannot write "${parts[1]}": ${err}')
                    return
                }
                println('Dumped to ${parts[1]}')
            } else {
                println(text)
            }
        }
        '.load' {
            if parts.len < 2 {
                eprintln('Usage: .load <file> [entry-point]')
                return
            }
            entry := if parts.len > 2 { parts[2] } else { '' }
            ep := if entry != '' { ", '${entry.replace("'", "''")}'" } else { '' }
            escaped_path := parts[1].replace("'", "''")
            _ = app.db.exec("SELECT load_extension('${escaped_path}' ${ep})") or {
                eprintln('Error: ${err}')
                return
            }
            println('Extension loaded: ${parts[1]}')
        }
        '.bail' {
            if parts.len < 2 {
                println('Bail: ${if app.bail { 'on' } else { 'off' }}')
                return
            }
            match parts[1] {
                'on' {
                    app.bail = true
                    println('Bail: on')
                }
                'off' {
                    app.bail = false
                    println('Bail: off')
                }
                else {
                    eprintln('Use: .bail on|off')
                }
            }
        }
        '.echo' {
            if parts.len < 2 {
                println('Echo: ${if app.echo { 'on' } else { 'off' }}')
                return
            }
            match parts[1] {
                'on' {
                    app.echo = true
                    println('Echo: on')
                }
                'off' {
                    app.echo = false
                    println('Echo: off')
                }
                else {
                    eprintln('Use: .echo on|off')
                }
            }
        }
        '.log' {
            if parts.len < 2 {
                if app.log_path == '' {
                    println('Log: off')
                } else {
                    println('Log: ${app.log_path}')
                }
                return
            }
            if parts[1] == 'off' {
                app.log_path = ''
                println('Logging off')
            } else {
                app.log_path = parts[1]
                println('Logging to ${app.log_path}')
            }
        }
        '.changes' {
            if parts.len < 2 {
                println('Changes: ${if app.changes { 'on' } else { 'off' }}')
                return
            }
            match parts[1] {
                'on' {
                    app.changes = true
                    println('Changes: on')
                }
                'off' {
                    app.changes = false
                    println('Changes: off')
                }
                else {
                    eprintln('Use: .changes on|off')
                }
            }
        }
        '.open' {
            if parts.len < 2 {
                eprintln('Usage: .open <database-file>')
                return
            }
            mut new_db := sqlite.connect(parts[1]) or {
                eprintln('Error: cannot open "${parts[1]}": ${err}')
                return
            }
            new_db.exec_none('PRAGMA journal_mode=WAL')
            new_db.exec_none('PRAGMA foreign_keys=ON')
            app.db = new_db
            app.db_path = parts[1]
            app.refresh_completions()
            println('Opened ${parts[1]}')
        }
        '.databases' {
            rows := app.db.exec('PRAGMA database_list') or { return }
            for row in rows {
                println('${row.vals[1]}: ${row.vals[2]}')
            }
        }
        '.indexes' {
            filter := if parts.len > 1 {
                escaped := parts[1].replace("'", "''")
                "AND tbl_name='${escaped}'"
            } else {
                ''
            }
            rows := app.db.exec("SELECT name, tbl_name FROM sqlite_master WHERE type='index' ${filter} ORDER BY tbl_name, name") or {
                return
            }
            if rows.len == 0 {
                println('(no indexes found)')
            } else {
                for row in rows {
                    println('${row.vals[1]}.${row.vals[0]}')
                }
            }
        }
        '.size' {
            sz := app.db.db_size() or {
                eprintln('Error: ${err}')
                return
            }
            println('Database size: ${format_bytes(sz)}')
        }
        '.read' {
            if parts.len < 2 {
                eprintln('Usage: .read <file>')
                return
            }
            app.read_file_repl(parts[1])
        }
        '.show' {
            log_disp := if app.log_path == '' { 'off' } else { app.log_path }
            trace_disp := if app.trace_path == '' { 'off' } else { app.trace_path }
            output_disp := if app.output_path == '' { 'stdout' } else { app.output_path }
            println('       bail: ${if app.bail { 'on' } else { 'off' }}')
            println('    changes: ${if app.changes { 'on' } else { 'off' }}')
            println('       echo: ${if app.echo { 'on' } else { 'off' }}')
            println('        eqp: ${if app.eqp { 'on' } else { 'off' }}')
            println('    headers: ${if app.headers { 'on' } else { 'off' }}')
            println('   log_path: ${log_disp}')
            println('       mode: ${app.mode}')
            println('  nullvalue: "${app.nullvalue}"')
            println('     output: ${output_disp}')
            println('     prompt: "${app.prompt}"')
            println('  separator: "${app.separator}"')
            println('      timer: ${if app.timer { 'on' } else { 'off' }}')
            println('      trace: ${trace_disp}')
        }
        '.print' {
            if parts.len < 2 {
                println('')
            } else {
                println(parts[1..].join(' '))
            }
        }
        '.prompt' {
            if parts.len < 2 {
                eprintln('Usage: .prompt MAIN [CONTINUE]')
                return
            }
            app.prompt = parts[1]
            if parts.len > 2 {
                app.prompt2 = parts[2]
            }
        }
        '.eqp' {
            if parts.len < 2 {
                println('EQP: ${if app.eqp { 'on' } else { 'off' }}')
                return
            }
            match parts[1] {
                'on' {
                    app.eqp = true
                    println('EQP: on')
                }
                'off' {
                    app.eqp = false
                    println('EQP: off')
                }
                'full' {
                    app.eqp = true
                    println('EQP: on (full)')
                }
                else {
                    eprintln('Use: .eqp on|off')
                }
            }
        }
        '.trace' {
            if parts.len < 2 || parts[1] == 'off' {
                app.trace_path = ''
                println('Tracing off')
            } else if parts[1] == 'stderr' {
                app.trace_path = 'stderr'
                println('Tracing to stderr')
            } else {
                app.trace_path = parts[1]
                println('Tracing to ${app.trace_path}')
            }
        }
        '.timeout' {
            if parts.len < 2 {
                eprintln('Usage: .timeout <ms>')
                return
            }
            ms := parts[1].int()
            app.db.busy_timeout(ms)
            println('Timeout: ${ms}ms')
        }
        '.shell', '.system' {
            if parts.len < 2 {
                eprintln('Usage: ${parts[0]} <command>')
                return
            }
            shell_cmd := parts[1..].join(' ')
            result := os.execute(shell_cmd)
            if result.output.len > 0 {
                print(result.output)
                if !result.output.ends_with('\n') {
                    println('')
                }
            }
            if result.exit_code != 0 {
                eprintln('Exit code: ${result.exit_code}')
            }
        }
        '.backup' {
            if parts.len < 2 {
                eprintln('Usage: .backup <file>')
                return
            }
            escaped := parts[1].replace("'", "''")
            app.db.exec("VACUUM INTO '${escaped}'") or {
                eprintln('Error: ${err}')
                return
            }
            println('Backed up to ${parts[1]}')
        }
        '.fullschema' {
            full_schema := app.db.schema('') or {
                eprintln('Error: ${err}')
                return
            }
            if full_schema == '' {
                println('(no schema found)')
            } else {
                println(full_schema)
            }
            for stat_tbl in ['sqlite_stat1', 'sqlite_stat2', 'sqlite_stat3', 'sqlite_stat4'] {
                rows := app.db.exec('SELECT * FROM ${stat_tbl}') or { continue }
                if rows.len == 0 {
                    continue
                }
                println('')
                println('/* contents of ${stat_tbl} */')
                for row in rows {
                    println(row.vals.join('|'))
                }
            }
        }
        '.dbinfo' {
            db_list_rows := app.db.exec('PRAGMA database_list') or { []sqlite.Row{} }
            mut filename := app.db_path
            for row in db_list_rows {
                if row.vals.len > 1 && row.vals[1] == 'main' {
                    filename = if row.vals.len > 2 { row.vals[2] } else { app.db_path }
                    break
                }
            }
            ver_rows := app.db.exec('SELECT sqlite_version()') or { []sqlite.Row{} }
            sqlite_ver := if ver_rows.len > 0 && ver_rows[0].vals.len > 0 {
                ver_rows[0].vals[0]
            } else {
                'unknown'
            }
            pc_rows := app.db.exec('PRAGMA page_count') or { []sqlite.Row{} }
            ps_rows := app.db.exec('PRAGMA page_size') or { []sqlite.Row{} }
            enc_rows := app.db.exec('PRAGMA encoding') or { []sqlite.Row{} }
            jm_rows := app.db.exec('PRAGMA journal_mode') or { []sqlite.Row{} }
            fl_rows := app.db.exec('PRAGMA freelist_count') or { []sqlite.Row{} }
            ai_rows := app.db.exec('PRAGMA application_id') or { []sqlite.Row{} }
            uv_rows := app.db.exec('PRAGMA user_version') or { []sqlite.Row{} }
            page_count := if pc_rows.len > 0 { pc_rows[0].vals[0] } else { '0' }
            page_size := if ps_rows.len > 0 { ps_rows[0].vals[0] } else { '0' }
            encoding := if enc_rows.len > 0 { enc_rows[0].vals[0] } else { 'unknown' }
            journal_mode := if jm_rows.len > 0 { jm_rows[0].vals[0] } else { 'unknown' }
            freelist_count := if fl_rows.len > 0 { fl_rows[0].vals[0] } else { '0' }
            application_id := if ai_rows.len > 0 { ai_rows[0].vals[0] } else { '0' }
            user_version := if uv_rows.len > 0 { uv_rows[0].vals[0] } else { '0' }
            file_size := page_count.i64() * page_size.i64()
            println('       filename: ${filename}')
            println(' sqlite_version: ${sqlite_ver}')
            println('     page_count: ${page_count}')
            println('      page_size: ${page_size}')
            println('      file_size: ${format_bytes(file_size)}')
            println('       encoding: ${encoding}')
            println('   journal_mode: ${journal_mode}')
            println(' freelist_count: ${freelist_count}')
            println(' application_id: ${application_id}')
            println('   user_version: ${user_version}')
        }
        '.stats' {
            pc_rows := app.db.exec('PRAGMA page_count') or { []sqlite.Row{} }
            ps_rows := app.db.exec('PRAGMA page_size') or { []sqlite.Row{} }
            fl_rows := app.db.exec('PRAGMA freelist_count') or { []sqlite.Row{} }
            cs_rows := app.db.exec('PRAGMA cache_size') or { []sqlite.Row{} }
            page_count := if pc_rows.len > 0 { pc_rows[0].vals[0] } else { '0' }
            page_size := if ps_rows.len > 0 { ps_rows[0].vals[0] } else { '0' }
            freelist_count := if fl_rows.len > 0 { fl_rows[0].vals[0] } else { '0' }
            cache_size := if cs_rows.len > 0 { cs_rows[0].vals[0] } else { '0' }
            pc := page_count.i64()
            ps := page_size.i64()
            fl := freelist_count.i64()
            total_size := pc * ps
            used_size := (pc - fl) * ps
            free_size := fl * ps
            println('   page_count: ${page_count}')
            println('    page_size: ${page_size}')
            println('   total_size: ${format_bytes(total_size)}')
            println('    used_size: ${format_bytes(used_size)}')
            println('    free_size: ${format_bytes(free_size)}')
            println('   cache_size: ${cache_size}')
            tables := app.db.tables() or { []string{} }
            if tables.len > 0 {
                println('')
                println('Table row counts:')
                for tbl in tables {
                    escaped_tbl := tbl.replace('"', '""')
                    cnt_rows := app.db.exec('SELECT COUNT(*) FROM "${escaped_tbl}"') or { continue }
                    cnt := if cnt_rows.len > 0 { cnt_rows[0].vals[0] } else { '0' }
                    println('  ${tbl}: ${cnt}')
                }
            }
        }
        '.lint' {
            tables := app.db.tables() or { []string{} }
            mut issue_count := 0
            for tbl in tables {
                escaped_tbl := tbl.replace('"', '""')
                fk_rows := app.db.exec('PRAGMA foreign_key_list("${escaped_tbl}")') or { continue }
                if fk_rows.len == 0 {
                    continue
                }
                mut indexed_cols := map[string]bool{}
                ti_rows := app.db.exec('PRAGMA table_info("${escaped_tbl}")') or { []sqlite.Row{} }
                for ti_row in ti_rows {
                    if ti_row.vals.len > 5 && ti_row.vals[5] != '0' {
                        indexed_cols[ti_row.vals[1]] = true
                    }
                }
                il_rows := app.db.exec('PRAGMA index_list("${escaped_tbl}")') or { []sqlite.Row{} }
                for il_row in il_rows {
                    if il_row.vals.len < 2 {
                        continue
                    }
                    idx_name := il_row.vals[1].replace('"', '""')
                    ii_rows := app.db.exec('PRAGMA index_info("${idx_name}")') or { continue }
                    for ii_row in ii_rows {
                        if ii_row.vals.len > 2 {
                            indexed_cols[ii_row.vals[2]] = true
                        }
                    }
                }
                for fk_row in fk_rows {
                    if fk_row.vals.len < 4 {
                        continue
                    }
                    fk_col := fk_row.vals[3]
                    ref_table := fk_row.vals[2]
                    ref_col := fk_row.vals[4]
                    if fk_col !in indexed_cols {
                        println('${tbl}: FK column "${fk_col}" -> ${ref_table}(${ref_col}) has no covering index')
                        issue_count++
                    }
                }
            }
            if issue_count == 0 {
                println('No issues found.')
            } else {
                println('${issue_count} issue${if issue_count == 1 { '' } else { 's' }} found.')
            }
        }
        '.cd' {
            if parts.len < 2 {
                cwd := os.getwd()
                println(cwd)
                return
            }
            os.chdir(parts[1]) or {
                eprintln('Error: cannot change to "${parts[1]}": ${err}')
                return
            }
            println('Changed to ${parts[1]}')
        }
        else {
            eprintln('Unknown command: ${parts[0]}. Type .help for help.')
        }
    }
}

fn (mut app App) import_csv(file string, table string) {
    sep := if file.ends_with('.tsv') || file.ends_with('.tab') { u8(`\t`) } else { u8(`,`) }
    headers, rows := read_csv_sep(file, sep) or {
        eprintln('Error: ${err}')
        return
    }
    col_list := headers.join(',')
    app.db.exec_none('BEGIN TRANSACTION')
    mut imported := 0
    for vals in rows {
        quoted := vals.map("'${it.replace("'", "''")}'")
        code := app.db.exec_none('INSERT INTO ${table} (${col_list}) VALUES (${quoted.join(',')})')
        if code != 101 && code != 100 {
            app.db.exec_none('ROLLBACK')
            eprintln('Error: import failed on row ${imported + 1} (code ${code})')
            return
        }
        imported++
    }
    app.db.exec_none('COMMIT')
    println('Imported ${imported} rows into ${table}')
}

fn (mut app App) refresh_completions() {
    dot_cmds := ['.tables', '.schema', '.mode', '.headers', '.nullvalue', '.separator', '.width',
        '.output', '.once', '.timer', '.explain', '.import', '.export', '.dump', '.load', '.bail',
        '.echo', '.log', '.changes', '.open', '.databases', '.indexes', '.size', '.help', '.quit',
        '.exit', '.read', '.show', '.print', '.prompt', '.eqp', '.trace', '.timeout', '.shell',
        '.system', '.backup', '.fullschema', '.dbinfo', '.stats', '.lint', '.cd']
    kws := ['SELECT', 'FROM', 'WHERE', 'INSERT', 'INTO', 'UPDATE', 'SET', 'DELETE', 'CREATE', 'TABLE',
        'DROP', 'ALTER', 'JOIN', 'LEFT', 'RIGHT', 'INNER', 'OUTER', 'ON', 'ORDER', 'BY', 'GROUP',
        'HAVING', 'LIMIT', 'OFFSET', 'AND', 'OR', 'NOT', 'NULL', 'VALUES', 'PRIMARY', 'KEY',
        'INTEGER', 'TEXT', 'REAL', 'BLOB', 'UNIQUE', 'DEFAULT', 'AUTOINCREMENT', 'PRAGMA', 'INDEX',
        'DISTINCT', 'COUNT', 'SUM', 'MIN', 'MAX', 'AVG', 'AS', 'LIKE', 'IN', 'IS', 'BETWEEN', 'CASE',
        'WHEN', 'THEN', 'ELSE', 'END', 'BEGIN', 'TRANSACTION', 'COMMIT', 'ROLLBACK']
    mut words := []string{}
    words << dot_cmds
    words << kws
    tables := app.db.tables() or { []string{} }
    words << tables
    for t in tables {
        words << (app.db.columns(t) or { []string{} })
    }
    app.rl.completion_callback = make_completer(words)
}

fn make_completer(words []string) fn (string) []string {
    return fn [words] (line string) []string {
        if line.len == 0 {
            return []string{}
        }
        if line.starts_with('.') && !line.contains(' ') {
            return words.filter(it.starts_with(line))
        }
        tok_start := last_token_start(line)
        pre := line[..tok_start]
        tok := line[tok_start..]
        if tok.len == 0 {
            return []string{}
        }
        tok_upper := tok.to_upper()
        mut results := []string{}
        for w in words {
            if w.to_upper().starts_with(tok_upper) {
                results << pre + w
            }
        }
        return results
    }
}

fn last_token_start(line string) int {
    for i := line.len - 1; i >= 0; i-- {
        b := line[i]
        if b == 32 || b == 9 || b == 40 || b == 44 {
            return i + 1
        }
    }
    return 0
}

// dump_database generates a SQL script that recreates the entire database.
fn dump_database(mut db sqlite.DB) string {
    mut lines := []string{}
    lines << 'BEGIN TRANSACTION;'
    lines << ''
    schema_rows := db.exec("SELECT type, name, sql FROM sqlite_master WHERE sql IS NOT NULL AND name NOT LIKE 'sqlite_%' ORDER BY CASE type WHEN 'table' THEN 0 ELSE 1 END, name") or {
        lines << 'COMMIT;'
        return lines.join('\n')
    }
    for row in schema_rows {
        if row.vals[0] != 'table' {
            continue
        }
        obj_name := row.vals[1]
        escaped := obj_name.replace('"', '""')
        lines << row.vals[2] + ';'
        col_rows := db.exec('PRAGMA table_info("${escaped}")') or { continue }
        if col_rows.len == 0 {
            lines << ''
            continue
        }
        col_names := col_rows.map(it.vals[1])
        col_list := col_names.map('"${it.replace('"', '""')}"').join(', ')
        // Use quote() to correctly distinguish NULL from empty string.
        quoted_cols := col_names.map('quote("${it.replace('"', '""')}")').join(', ')
        data_rows := db.exec('SELECT ${quoted_cols} FROM "${escaped}"') or { continue }
        for drow in data_rows {
            lines << 'INSERT INTO "${escaped}"(${col_list}) VALUES(${drow.vals.join(', ')});'
        }
        lines << ''
    }
    for row in schema_rows {
        if row.vals[0] == 'table' {
            continue
        }
        lines << row.vals[2] + ';'
        lines << ''
    }
    lines << 'COMMIT;'
    return lines.join('\n')
}

fn format_bytes(n i64) string {
    if n < 1024 {
        return '${n} B'
    } else if n < 1024 * 1024 {
        return '${n / 1024} KB'
    } else if n < 1024 * 1024 * 1024 {
        return '${n / (1024 * 1024)} MB'
    } else {
        return '${n / (1024 * 1024 * 1024)} GB'
    }
}

fn format_duration(d time.Duration) string {
    us := d.microseconds()
    if us < 1000 {
        return '${us} µs'
    }
    ms := us / 1000
    frac := us % 1000
    frac_str := if frac < 10 {
        '00${frac}'
    } else if frac < 100 {
        '0${frac}'
    } else {
        '${frac}'
    }
    return '${ms}.${frac_str} ms'
}

// split_statements splits SQL on semicolons, respecting quoted strings and
// BEGIN...END blocks (e.g. CREATE TRIGGER bodies).
fn split_statements(src string) []string {
    mut stmts := []string{}
    mut start := 0
    mut in_single := false
    mut in_double := false
    mut begin_depth := 0
    for i := 0; i < src.len; i++ {
        c := src[i]
        if c == `'` && !in_double {
            in_single = !in_single
        } else if c == `"` && !in_single {
            in_double = !in_double
        } else if !in_single && !in_double {
            // Track BEGIN/END nesting for trigger bodies.
            if c == `;` && begin_depth == 0 {
                s := src[start..i].trim_space()
                if s != '' {
                    stmts << s
                }
                start = i + 1
            } else if is_keyword_at(src, i, 'BEGIN') {
                // Only count as a trigger-body BEGIN if there is already content
                // in this statement. A standalone 'BEGIN'/'BEGIN TRANSACTION'
                // starts a new statement and must not suppress semicolon splitting.
                if src[start..i].trim_space() != '' {
                    begin_depth++
                }
            } else if is_keyword_at(src, i, 'END') && begin_depth > 0 {
                begin_depth--
            }
        }
    }
    s := src[start..].trim_space()
    if s != '' {
        stmts << s
    }
    return stmts
}

// is_keyword_at checks if keyword appears at position i as a whole word
// (not part of a larger identifier).
fn is_keyword_at(src string, i int, keyword string) bool {
    if i + keyword.len > src.len {
        return false
    }
    // Must not be preceded by a word character.
    if i > 0 && is_word_char(src[i - 1]) {
        return false
    }
    // Must not be followed by a word character.
    end := i + keyword.len
    if end < src.len && is_word_char(src[end]) {
        return false
    }
    return src[i..end].to_upper() == keyword
}

fn is_word_char(c u8) bool {
    return (c >= `a` && c <= `z`) || (c >= `A` && c <= `Z`) || (c >= `0` && c <= `9`) || c == `_`
}

fn (mut app App) run_explain(stmt string) {
    rows := app.db.exec('EXPLAIN QUERY PLAN ${stmt}') or {
        eprintln('Error: ${err}')
        return
    }
    if rows.len == 0 {
        println('(no query plan)')
        return
    }
    println('QUERY PLAN')
    app.print_eqp_tree(rows, 0, 0)
}

fn (mut app App) print_eqp_tree(rows []sqlite.Row, parent_id int, depth int) {
    indent := '  '.repeat(depth)
    for row in rows {
        if row.vals.len < 4 {
            continue
        }
        if row.vals[1].int() == parent_id {
            println('${indent}|--${row.vals[3]}')
            app.print_eqp_tree(rows, row.vals[0].int(), depth + 1)
        }
    }
}