SQLite Database Manager (java, written by Codex)

Codex wrote this java project from the task below. It does not run on a clean Ubuntu 22.04 machine as written.

Task given to the agent:

TASK: SQLite Database Manager

Write a program that provides a command-line interface for managing SQLite databases, supporting table creation, CRUD operations, schema inspection, data import/export, and query execution.

FUNCTIONAL REQUIREMENTS:
- Accept a database file path as a command-line argument (create it if it does not exist)
- Support creating tables with typed columns (TEXT, INTEGER, REAL, BLOB) and constraints (PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT)
- Support INSERT, SELECT, UPDATE, and DELETE operations via command-line subcommands
- Support executing arbitrary SQL queries via a --query flag
- Implement schema inspection: list all tables, show column details for a given table, show row counts
- Import data from a CSV file into a specified table via --import flag, auto-detecting column types
- Export a table or query result to CSV via --export flag
- Support basic transactions: --begin, --commit, --rollback flags for grouping operations
- Print query results as formatted tables to console with column headers and aligned values
- If no database path is given, create a sample database with at least three related tables (e.g., users, orders, products), populate with sample data, and run demonstration queries showing joins, aggregations, and filtering
- Handle database errors gracefully with clear messages distinguishing connection, schema, constraint, and query errors

Create a complete Java project for a clean Ubuntu 22.04 machine with only JDK 17+ installed. Include:
- Source code
- pom.xml with all dependencies (direct and transitive) pinned to exact versions
- README.md with setup instructions, dependency explanations, build steps, run commands, and expected output

[]

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>tmlr.codex_generated.p06</groupId>
  <artifactId>sqlite-database-manager</artifactId>
  <version>1.0.0</version>
  <name>SQLite Database Manager</name>
  <description>CLI manager for SQLite databases with CRUD, schema inspection, import/export, and transactions.</description>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.release>17</maven.compiler.release>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.xerial</groupId>
      <artifactId>sqlite-jdbc</artifactId>
      <version>3.46.1.3</version>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.13.0</version>
      </plugin>
      <plugin>
        <groupId>org.codehaus.mojo</groupId>
        <artifactId>exec-maven-plugin</artifactId>
        <version>3.5.0</version>
        <configuration>
          <mainClass>SQLiteDatabaseManager</mainClass>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>

# SQLite Database Manager (Java)

SQLite CLI manager for JDK 17+ with table creation, CRUD, schema inspection, import/export, transactions, and ad-hoc query execution.

## Requirements

- Ubuntu 22.04
- JDK 17+
- Maven 3.9+

## Dependencies

- Direct:
  - `org.xerial:sqlite-jdbc:3.46.1.3`
- Transitive:
  - none (runtime packaged in `sqlite-jdbc`)

All dependency versions are pinned exactly in `pom.xml`.

## Build

```bash
mvn -q -DskipTests compile
```

## Run

No DB path: creates sample DB and runs demo join/aggregation/filter queries.

```bash
mvn -q exec:java
```

Use a specific DB:

```bash
mvn -q exec:java -Dexec.args="./app.db --list-tables"
```

## Commands

Create table:

```bash
mvn -q exec:java -Dexec.args="./app.db --create-table users --columns id:INTEGER PRIMARY KEY,name:TEXT NOT NULL,email:TEXT UNIQUE"
```

Insert:

```bash
mvn -q exec:java -Dexec.args="./app.db --insert users --values id=1,name=Alice,email=alice@example.com"
```

Select:

```bash
mvn -q exec:java -Dexec.args="./app.db --select users --columns id,name,email --where id>=1 --limit 10"
```

Update:

```bash
mvn -q exec:java -Dexec.args="./app.db --update users --set name=AliceUpdated --where id=1"
```

Delete:

```bash
mvn -q exec:java -Dexec.args="./app.db --delete users --where id=1"
```

Arbitrary query:

```bash
mvn -q exec:java -Dexec.args="./app.db --query SELECT * FROM users"
```

Schema inspection:

```bash
mvn -q exec:java -Dexec.args="./app.db --list-tables"
mvn -q exec:java -Dexec.args="./app.db --describe users"
mvn -q exec:java -Dexec.args="./app.db --row-count users"
```

Import CSV:

```bash
mvn -q exec:java -Dexec.args="./app.db --import ./users.csv --table imported_users"
```

Export CSV:

```bash
mvn -q exec:java -Dexec.args="./app.db --export ./users_out.csv --table users"
mvn -q exec:java -Dexec.args="./app.db --export ./query_out.csv --query SELECT * FROM users WHERE id>5"
```

Transactions:

```bash
mvn -q exec:java -Dexec.args="./app.db --begin --insert users --values id=3,name=Carol,email=carol@example.com --commit"
mvn -q exec:java -Dexec.args="./app.db --begin --delete users --where id=3 --rollback"
```

## Expected Output

- Aligned query results with headers
- Categorized error messages (`Connection Error`, `Schema Error`, `Constraint Error`, `Query Error`)
- Auto-created DB file if missing

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.Instant;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class SQLiteDatabaseManager {
    private static final String SAMPLE_DB_NAME = "sample_demo.sqlite";

    private record ParsedArgs(Map<String, String> options, Set<String> flags, List<String> positional) {}

    public static void main(String[] args) {
        ParsedArgs parsed = parseArgs(args);
        if (parsed.flags.contains("help")) {
            usage();
            return;
        }

        Path dbPath = parsed.positional.isEmpty() ? Paths.get(SAMPLE_DB_NAME).toAbsolutePath() : Paths.get(parsed.positional.get(0)).toAbsolutePath();
        try {
            Files.createDirectories(dbPath.getParent());
        } catch (IOException e) {
            System.err.println("[Connection Error] Failed to prepare DB directory: " + e.getMessage());
            System.exit(1);
            return;
        }

        boolean mutated = !Files.exists(dbPath);
        int operations = 0;
        boolean inTransaction = false;

        try (Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbPath.toString())) {
            conn.createStatement().execute("PRAGMA foreign_keys = ON");

            if (parsed.positional.isEmpty()) {
                runDemo(conn);
                mutated = true;
                operations++;
            } else {
                if (parsed.flags.contains("begin")) {
                    conn.setAutoCommit(false);
                    inTransaction = true;
                    System.out.println("Transaction started.");
                }

                if (doCreateTable(conn, parsed.options)) {
                    mutated = true;
                    operations++;
                }
                if (doInsert(conn, parsed.options)) {
                    mutated = true;
                    operations++;
                }
                if (doUpdate(conn, parsed.options)) {
                    mutated = true;
                    operations++;
                }
                if (doDelete(conn, parsed.options)) {
                    mutated = true;
                    operations++;
                }
                if (doImport(conn, parsed.options)) {
                    mutated = true;
                    operations++;
                }
                if (parsed.options.containsKey("query")) {
                    runQueryAndPrint(conn, parsed.options.get("query"));
                    operations++;
                }
                if (doSelect(conn, parsed.options)) {
                    operations++;
                }
                if (parsed.flags.contains("list-tables") || parsed.options.containsKey("describe") || parsed.options.containsKey("row-count")) {
                    inspectSchema(conn, parsed.options, parsed.flags);
                    operations++;
                }
                if (doExport(conn, parsed.options)) {
                    operations++;
                }

                if (inTransaction) {
                    if (parsed.flags.contains("rollback")) {
                        conn.rollback();
                        System.out.println("Transaction rolled back.");
                        mutated = false;
                    } else {
                        conn.commit();
                        if (parsed.flags.contains("commit")) {
                            System.out.println("Transaction committed.");
                        } else {
                            System.out.println("Transaction committed automatically.");
                        }
                        mutated = true;
                    }
                    conn.setAutoCommit(true);
                }
            }

            if (operations == 0) {
                usage();
            }
            System.out.println("Database path: " + dbPath);
        } catch (SQLException | IOException | IllegalArgumentException e) {
            String msg = e.getMessage() == null ? e.toString() : e.getMessage();
            System.err.println("[" + classifyError(msg) + "] " + msg);
            System.exit(1);
        }
    }

    private static ParsedArgs parseArgs(String[] args) {
        Map<String, String> options = new HashMap<>();
        Set<String> flags = new HashSet<>();
        List<String> positional = new ArrayList<>();

        for (int i = 0; i < args.length; i++) {
            String token = args[i];
            if (token.startsWith("--")) {
                String key = token.substring(2);
                if (i + 1 < args.length && !args[i + 1].startsWith("--")) {
                    options.put(key, args[i + 1]);
                    i++;
                } else {
                    flags.add(key);
                }
            } else {
                positional.add(token);
            }
        }
        return new ParsedArgs(options, flags, positional);
    }

    private static String escapeIdent(String name) {
        return "\"" + name.replace("\"", "\"\"") + "\"";
    }

    private static String classifyError(String message) {
        String lower = message.toLowerCase();
        if (lower.contains("no such table") || lower.contains("no such column") || lower.contains("already exists")) {
            return "Schema Error";
        }
        if (lower.contains("constraint failed") || lower.contains("not null") || lower.contains("unique")) {
            return "Constraint Error";
        }
        if (lower.contains("syntax error") || lower.contains("near")) {
            return "Query Error";
        }
        return "Connection Error";
    }

    private static List<String> parseCsvLine(String line) {
        List<String> values = new ArrayList<>();
        StringBuilder current = new StringBuilder();
        boolean inQuotes = false;

        for (int i = 0; i < line.length(); i++) {
            char ch = line.charAt(i);
            if (ch == '"') {
                if (inQuotes && i + 1 < line.length() && line.charAt(i + 1) == '"') {
                    current.append('"');
                    i++;
                } else {
                    inQuotes = !inQuotes;
                }
            } else if (ch == ',' && !inQuotes) {
                values.add(current.toString());
                current.setLength(0);
            } else {
                current.append(ch);
            }
        }
        values.add(current.toString());
        return values;
    }

    private static String toCsvLine(List<String> values) {
        List<String> escaped = new ArrayList<>();
        for (String value : values) {
            String text = value == null ? "" : value;
            if (text.contains(",") || text.contains("\"") || text.contains("\n")) {
                escaped.add("\"" + text.replace("\"", "\"\"") + "\"");
            } else {
                escaped.add(text);
            }
        }
        return String.join(",", escaped);
    }

    private static Object parseValue(String raw) {
        if (raw == null) return null;
        String trimmed = raw.trim();
        if (trimmed.isEmpty()) return "";
        if (trimmed.equalsIgnoreCase("null")) return null;
        if (trimmed.equalsIgnoreCase("true")) return 1;
        if (trimmed.equalsIgnoreCase("false")) return 0;
        if (trimmed.matches("^-?\\d+$")) return Integer.parseInt(trimmed);
        if (trimmed.matches("^-?\\d+\\.\\d+$")) return Double.parseDouble(trimmed);
        if ((trimmed.startsWith("\"") && trimmed.endsWith("\"")) || (trimmed.startsWith("'") && trimmed.endsWith("'"))) {
            return trimmed.substring(1, trimmed.length() - 1);
        }
        return trimmed;
    }

    private static List<Map.Entry<String, Object>> parseKeyValueSpec(String spec) {
        List<Map.Entry<String, Object>> entries = new ArrayList<>();
        for (String part : parseCsvLine(spec)) {
            int eq = part.indexOf('=');
            if (eq <= 0) throw new IllegalArgumentException("Invalid key=value pair: " + part);
            String key = part.substring(0, eq).trim();
            String value = part.substring(eq + 1).trim();
            entries.add(Map.entry(key, parseValue(value)));
        }
        return entries;
    }

    private static List<Map.Entry<String, String>> parseColumnSpec(String spec) {
        List<Map.Entry<String, String>> defs = new ArrayList<>();
        for (String part : parseCsvLine(spec)) {
            String trimmed = part.trim();
            int colon = trimmed.indexOf(':');
            if (colon <= 0) throw new IllegalArgumentException("Invalid column definition: " + trimmed);
            String name = trimmed.substring(0, colon).trim();
            String definition = trimmed.substring(colon + 1).trim();
            if (name.isEmpty() || definition.isEmpty()) {
                throw new IllegalArgumentException("Invalid column definition: " + trimmed);
            }
            defs.add(Map.entry(name, definition));
        }
        return defs;
    }

    private static void printTable(List<String> columns, List<List<String>> rows) {
        if (columns.isEmpty()) {
            System.out.println("(no columns)");
            return;
        }
        List<Integer> widths = new ArrayList<>();
        for (String col : columns) widths.add(col.length());
        for (List<String> row : rows) {
            for (int i = 0; i < columns.size(); i++) {
                String value = i < row.size() ? row.get(i) : "";
                if (value.length() > widths.get(i)) widths.set(i, value.length());
            }
        }

        String sep = "+-";
        for (int i = 0; i < widths.size(); i++) {
            sep += "-".repeat(widths.get(i));
            sep += (i + 1 < widths.size()) ? "-+-" : "-+";
        }

        System.out.println(sep);
        System.out.println("| " + joinPadded(columns, widths) + " |");
        System.out.println(sep);
        for (List<String> row : rows) {
            List<String> normalized = new ArrayList<>();
            for (int i = 0; i < columns.size(); i++) {
                normalized.add(i < row.size() ? row.get(i) : "");
            }
            System.out.println("| " + joinPadded(normalized, widths) + " |");
        }
        System.out.println(sep);
        System.out.println(rows.size() + " row(s)");
    }

    private static String joinPadded(List<String> values, List<Integer> widths) {
        List<String> padded = new ArrayList<>();
        for (int i = 0; i < values.size(); i++) {
            String v = values.get(i) == null ? "NULL" : values.get(i);
            padded.add(v + " ".repeat(Math.max(0, widths.get(i) - v.length())));
        }
        return String.join(" | ", padded);
    }

    private static List<List<String>> resultSetRows(ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        int cols = meta.getColumnCount();
        List<List<String>> rows = new ArrayList<>();
        while (rs.next()) {
            List<String> row = new ArrayList<>();
            for (int i = 1; i <= cols; i++) {
                Object value = rs.getObject(i);
                row.add(value == null ? "NULL" : String.valueOf(value));
            }
            rows.add(row);
        }
        return rows;
    }

    private static List<String> resultSetColumns(ResultSet rs) throws SQLException {
        ResultSetMetaData meta = rs.getMetaData();
        int cols = meta.getColumnCount();
        List<String> names = new ArrayList<>();
        for (int i = 1; i <= cols; i++) names.add(meta.getColumnName(i));
        return names;
    }

    private static void runQueryAndPrint(Connection conn, String sql) throws SQLException {
        try (Statement st = conn.createStatement()) {
            boolean hasRows = st.execute(sql);
            if (hasRows) {
                try (ResultSet rs = st.getResultSet()) {
                    printTable(resultSetColumns(rs), resultSetRows(rs));
                }
            } else {
                System.out.println("Query executed. Affected rows: " + st.getUpdateCount());
            }
        }
    }

    private static boolean tableExists(Connection conn, String table) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(
                "SELECT name FROM sqlite_master WHERE type='table' AND name = ?")) {
            ps.setString(1, table);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next();
            }
        }
    }

    private static String detectType(List<String> values) {
        List<String> nonEmpty = new ArrayList<>();
        for (String v : values) {
            if (v != null && !v.trim().isEmpty()) nonEmpty.add(v.trim());
        }
        if (nonEmpty.isEmpty()) return "TEXT";
        if (nonEmpty.stream().allMatch(v -> v.matches("^-?\\d+$"))) return "INTEGER";
        if (nonEmpty.stream().allMatch(v -> v.matches("^-?\\d+(\\.\\d+)?$"))) return "REAL";
        if (nonEmpty.stream().allMatch(v -> v.matches("^0x[0-9a-fA-F]+$"))) return "BLOB";
        return "TEXT";
    }

    private static void inspectSchema(Connection conn, Map<String, String> options, Set<String> flags) throws SQLException {
        if (flags.contains("list-tables")) {
            List<List<String>> rows = new ArrayList<>();
            try (Statement st = conn.createStatement();
                 ResultSet rs = st.executeQuery("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name")) {
                while (rs.next()) {
                    String table = rs.getString(1);
                    try (Statement countSt = conn.createStatement();
                         ResultSet countRs = countSt.executeQuery("SELECT COUNT(*) FROM " + escapeIdent(table))) {
                        countRs.next();
                        rows.add(List.of(table, String.valueOf(countRs.getInt(1))));
                    }
                }
            }
            if (rows.isEmpty()) {
                System.out.println("No user tables found.");
            } else {
                printTable(List.of("table", "row_count"), rows);
            }
        }
        if (options.containsKey("describe")) {
            try (Statement st = conn.createStatement();
                 ResultSet rs = st.executeQuery("PRAGMA table_info(" + escapeIdent(options.get("describe")) + ")")) {
                printTable(resultSetColumns(rs), resultSetRows(rs));
            }
        }
        if (options.containsKey("row-count")) {
            try (Statement st = conn.createStatement();
                 ResultSet rs = st.executeQuery("SELECT COUNT(*) AS row_count FROM " + escapeIdent(options.get("row-count")))) {
                printTable(resultSetColumns(rs), resultSetRows(rs));
            }
        }
    }

    private static boolean doCreateTable(Connection conn, Map<String, String> options) throws SQLException {
        if (!options.containsKey("create-table")) return false;
        if (!options.containsKey("columns")) throw new IllegalArgumentException("--create-table requires --columns");
        String table = options.get("create-table");
        List<Map.Entry<String, String>> defs = parseColumnSpec(options.get("columns"));
        StringBuilder sql = new StringBuilder("CREATE TABLE IF NOT EXISTS ")
                .append(escapeIdent(table)).append(" (");
        for (int i = 0; i < defs.size(); i++) {
            Map.Entry<String, String> def = defs.get(i);
            if (i > 0) sql.append(", ");
            sql.append(escapeIdent(def.getKey())).append(" ").append(def.getValue());
        }
        sql.append(")");
        conn.createStatement().execute(sql.toString());
        System.out.println("Created/verified table: " + table);
        return true;
    }

    private static boolean doInsert(Connection conn, Map<String, String> options) throws SQLException {
        if (!options.containsKey("insert")) return false;
        if (!options.containsKey("values")) throw new IllegalArgumentException("--insert requires --values");
        String table = options.get("insert");
        List<Map.Entry<String, Object>> entries = parseKeyValueSpec(options.get("values"));
        StringBuilder sql = new StringBuilder("INSERT INTO ").append(escapeIdent(table)).append(" (");
        for (int i = 0; i < entries.size(); i++) {
            if (i > 0) sql.append(", ");
            sql.append(escapeIdent(entries.get(i).getKey()));
        }
        sql.append(") VALUES (");
        for (int i = 0; i < entries.size(); i++) {
            if (i > 0) sql.append(", ");
            sql.append("?");
        }
        sql.append(")");

        try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            for (int i = 0; i < entries.size(); i++) {
                ps.setObject(i + 1, entries.get(i).getValue());
            }
            ps.executeUpdate();
        }
        System.out.println("Inserted 1 row into " + table);
        return true;
    }

    private static boolean doSelect(Connection conn, Map<String, String> options) throws SQLException {
        if (!options.containsKey("select")) return false;
        String table = options.get("select");
        String columns = options.getOrDefault("columns", "*");
        StringBuilder sql = new StringBuilder("SELECT ").append(columns).append(" FROM ").append(escapeIdent(table));
        if (options.containsKey("where")) sql.append(" WHERE ").append(options.get("where"));
        if (options.containsKey("limit")) sql.append(" LIMIT ").append(Integer.parseInt(options.get("limit")));
        runQueryAndPrint(conn, sql.toString());
        return true;
    }

    private static boolean doUpdate(Connection conn, Map<String, String> options) throws SQLException {
        if (!options.containsKey("update")) return false;
        if (!options.containsKey("set")) throw new IllegalArgumentException("--update requires --set");
        String table = options.get("update");
        List<Map.Entry<String, Object>> entries = parseKeyValueSpec(options.get("set"));
        StringBuilder sql = new StringBuilder("UPDATE ").append(escapeIdent(table)).append(" SET ");
        for (int i = 0; i < entries.size(); i++) {
            if (i > 0) sql.append(", ");
            sql.append(escapeIdent(entries.get(i).getKey())).append(" = ?");
        }
        if (options.containsKey("where")) sql.append(" WHERE ").append(options.get("where"));

        int changed;
        try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
            for (int i = 0; i < entries.size(); i++) {
                ps.setObject(i + 1, entries.get(i).getValue());
            }
            changed = ps.executeUpdate();
        }
        System.out.println("Updated " + changed + " row(s) in " + table);
        return true;
    }

    private static boolean doDelete(Connection conn, Map<String, String> options) throws SQLException {
        if (!options.containsKey("delete")) return false;
        String table = options.get("delete");
        StringBuilder sql = new StringBuilder("DELETE FROM ").append(escapeIdent(table));
        if (options.containsKey("where")) sql.append(" WHERE ").append(options.get("where"));
        int changed = conn.createStatement().executeUpdate(sql.toString());
        System.out.println("Deleted " + changed + " row(s) from " + table);
        return true;
    }

    private static boolean doImport(Connection conn, Map<String, String> options) throws IOException, SQLException {
        if (!options.containsKey("import")) return false;
        if (!options.containsKey("table")) throw new IllegalArgumentException("--import requires --table");

        Path csvPath = Paths.get(options.get("import")).toAbsolutePath();
        if (!Files.exists(csvPath)) throw new IllegalArgumentException("CSV file not found: " + csvPath);

        List<List<String>> rows = new ArrayList<>();
        try (BufferedReader reader = Files.newBufferedReader(csvPath)) {
            String line;
            while ((line = reader.readLine()) != null) {
                if (!line.isEmpty()) rows.add(parseCsvLine(line));
            }
        }
        if (rows.isEmpty()) throw new IllegalArgumentException("CSV is empty.");
        List<String> headers = rows.get(0);
        List<List<String>> dataRows = rows.subList(1, rows.size());
        String table = options.get("table");

        if (!tableExists(conn, table)) {
            List<String> defs = new ArrayList<>();
            for (int i = 0; i < headers.size(); i++) {
                List<String> values = new ArrayList<>();
                for (List<String> row : dataRows) {
                    values.add(i < row.size() ? row.get(i) : "");
                }
                defs.add(escapeIdent(headers.get(i)) + " " + detectType(values));
            }
            String createSql = "CREATE TABLE " + escapeIdent(table) + " (" + String.join(", ", defs) + ")";
            conn.createStatement().execute(createSql);
        }

        if (!dataRows.isEmpty()) {
            StringBuilder sql = new StringBuilder("INSERT INTO ").append(escapeIdent(table)).append(" (");
            for (int i = 0; i < headers.size(); i++) {
                if (i > 0) sql.append(", ");
                sql.append(escapeIdent(headers.get(i)));
            }
            sql.append(") VALUES (");
            for (int i = 0; i < headers.size(); i++) {
                if (i > 0) sql.append(", ");
                sql.append("?");
            }
            sql.append(")");
            try (PreparedStatement ps = conn.prepareStatement(sql.toString())) {
                for (List<String> row : dataRows) {
                    for (int i = 0; i < headers.size(); i++) {
                        String value = i < row.size() ? row.get(i) : null;
                        ps.setObject(i + 1, parseValue(value));
                    }
                    ps.addBatch();
                }
                ps.executeBatch();
            }
        }

        System.out.println("Imported " + dataRows.size() + " row(s) into " + table);
        return true;
    }

    private static boolean doExport(Connection conn, Map<String, String> options) throws SQLException, IOException {
        if (!options.containsKey("export")) return false;
        String query = options.get("query");
        if (query == null && !options.containsKey("table")) {
            throw new IllegalArgumentException("--export requires either --table or --query");
        }
        if (query == null) query = "SELECT * FROM " + escapeIdent(options.get("table"));

        Path outPath = Paths.get(options.get("export")).toAbsolutePath();
        try (Statement st = conn.createStatement();
             ResultSet rs = st.executeQuery(query);
             BufferedWriter writer = Files.newBufferedWriter(outPath)) {
            List<String> columns = resultSetColumns(rs);
            writer.write(toCsvLine(columns));
            writer.newLine();
            List<List<String>> rows = resultSetRows(rs);
            for (List<String> row : rows) {
                writer.write(toCsvLine(row));
                writer.newLine();
            }
            System.out.println("Exported " + rows.size() + " row(s) to " + outPath);
        }
        return true;
    }

    private static void buildSampleDb(Connection conn) throws SQLException {
        conn.createStatement().execute("""
            PRAGMA foreign_keys = ON;
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                email TEXT UNIQUE NOT NULL
            );
            CREATE TABLE IF NOT EXISTS products (
                id INTEGER PRIMARY KEY,
                name TEXT NOT NULL,
                price REAL NOT NULL CHECK(price >= 0)
            );
            CREATE TABLE IF NOT EXISTS orders (
                id INTEGER PRIMARY KEY,
                user_id INTEGER NOT NULL REFERENCES users(id),
                product_id INTEGER NOT NULL REFERENCES products(id),
                quantity INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL
            );
            DELETE FROM orders;
            DELETE FROM users;
            DELETE FROM products;
            """);

        try (PreparedStatement users = conn.prepareStatement("INSERT INTO users(id, name, email) VALUES (?, ?, ?)");
             PreparedStatement products = conn.prepareStatement("INSERT INTO products(id, name, price) VALUES (?, ?, ?)");
             PreparedStatement orders = conn.prepareStatement("INSERT INTO orders(id, user_id, product_id, quantity, created_at) VALUES (?, ?, ?, ?, ?)")) {

            Object[][] userRows = {
                    {1, "Alice", "alice@example.com"},
                    {2, "Bob", "bob@example.com"},
                    {3, "Carol", "carol@example.com"}
            };
            for (Object[] row : userRows) {
                users.setObject(1, row[0]);
                users.setObject(2, row[1]);
                users.setObject(3, row[2]);
                users.executeUpdate();
            }

            Object[][] productRows = {
                    {1, "Keyboard", 79.99},
                    {2, "Monitor", 249.50},
                    {3, "Mouse", 29.95}
            };
            for (Object[] row : productRows) {
                products.setObject(1, row[0]);
                products.setObject(2, row[1]);
                products.setObject(3, row[2]);
                products.executeUpdate();
            }

            Object[][] orderRows = {
                    {1, 1, 1, 2, "2026-02-01T10:00:00Z"},
                    {2, 1, 2, 1, "2026-02-02T11:30:00Z"},
                    {3, 2, 3, 3, "2026-02-03T15:45:00Z"},
                    {4, 3, 2, 1, "2026-02-04T17:15:00Z"}
            };
            for (Object[] row : orderRows) {
                orders.setObject(1, row[0]);
                orders.setObject(2, row[1]);
                orders.setObject(3, row[2]);
                orders.setObject(4, row[3]);
                orders.setObject(5, row[4]);
                orders.executeUpdate();
            }
        }
    }

    private static void runDemo(Connection conn) throws SQLException {
        System.out.println("No database path provided. Creating sample database and running demo queries.\n");
        buildSampleDb(conn);

        System.out.println("Demo 1: Join users, orders, products");
        runQueryAndPrint(conn, """
            SELECT o.id AS order_id, u.name AS user_name, p.name AS product_name, o.quantity, p.price,
                   ROUND(o.quantity * p.price, 2) AS total
            FROM orders o
            JOIN users u ON u.id = o.user_id
            JOIN products p ON p.id = o.product_id
            ORDER BY o.id
            """);

        System.out.println("\nDemo 2: Aggregation by user");
        runQueryAndPrint(conn, """
            SELECT u.name, ROUND(SUM(o.quantity * p.price), 2) AS spent
            FROM users u
            JOIN orders o ON o.user_id = u.id
            JOIN products p ON p.id = o.product_id
            GROUP BY u.id, u.name
            ORDER BY spent DESC
            """);

        System.out.println("\nDemo 3: Filtering products over 50");
        runQueryAndPrint(conn, "SELECT id, name, price FROM products WHERE price > 50 ORDER BY price DESC");
    }

    private static void usage() {
        System.out.println("""
            SQLite Database Manager

            Usage:
              mvn -q exec:java -Dexec.args="<db-path> [options]"
              mvn -q exec:java                     (sample DB demo)

            Core options:
              --create-table <table> --columns "id:INTEGER PRIMARY KEY,name:TEXT NOT NULL"
              --insert <table> --values "id=1,name=Alice,email=alice@example.com"
              --select <table> [--columns "id,name"] [--where "id > 1"] [--limit 20]
              --update <table> --set "name=Alice2" [--where "id=1"]
              --delete <table> [--where "id=1"]
              --query "SELECT * FROM users"

            Schema inspection:
              --list-tables
              --describe <table>
              --row-count <table>

            Import/export:
              --import <file.csv> --table <table>
              --export <file.csv> --table <table>
              --export <file.csv> --query "SELECT ..."

            Transactions:
              --begin
              --commit
              --rollback
            """);
        System.out.println("Generated at: " + Instant.now());
    }
}