SQLite Database Manager (cpp, written by Codex)

Codex wrote this cpp project from the task below. It installed and ran 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 C++ project for a clean Ubuntu 22.04 machine with only G++ 12+ and CMake 3.22+ installed. Include:
- Source code
- CMakeLists.txt with all dependencies (direct and transitive) pinned to exact versions
- README.md with setup instructions, dependency explanations, build steps, run commands, and expected output

[]

cmake_minimum_required(VERSION 3.22)
project(sqlite_database_manager_cpp VERSION 1.0.0 LANGUAGES C CXX)

set(CMAKE_CXX_STANDARD 20)
set(CMAKE_CXX_STANDARD_REQUIRED ON)
set(CMAKE_CXX_EXTENSIONS OFF)

include(FetchContent)

# Pinned SQLite amalgamation release (3.46.0)
FetchContent_Declare(
  sqlite_amalgamation
  URL https://www.sqlite.org/2024/sqlite-amalgamation-3460000.zip
)
FetchContent_MakeAvailable(sqlite_amalgamation)

add_library(sqlite3 STATIC
  ${sqlite_amalgamation_SOURCE_DIR}/sqlite3.c
)
target_include_directories(sqlite3 PUBLIC ${sqlite_amalgamation_SOURCE_DIR})

add_executable(sqlite_manager src/main.cpp)
target_link_libraries(sqlite_manager PRIVATE sqlite3)

# SQLite Database Manager (C++)

SQLite CLI manager implemented with the SQLite C API.

## Requirements

- Ubuntu 22.04
- G++ 12+
- CMake 3.22+
- Network access during CMake configure (for SQLite source fetch)

## Dependencies

- Direct:
  - SQLite amalgamation `3.46.0` from `https://www.sqlite.org/2024/sqlite-amalgamation-3460000.zip`
- Transitive:
  - none

The dependency is pinned by exact URL in `CMakeLists.txt`.

## Build

```bash
cmake -S . -B build
cmake --build build
```

## Run

No DB path: creates sample DB and runs demo queries.

```bash
./build/sqlite_manager
```

Use a specific DB:

```bash
./build/sqlite_manager ./app.db --list-tables
```

## Commands

Create table:

```bash
./build/sqlite_manager ./app.db --create-table users --columns "id:INTEGER PRIMARY KEY,name:TEXT NOT NULL,email:TEXT UNIQUE"
```

Insert:

```bash
./build/sqlite_manager ./app.db --insert users --values "id=1,name=Alice,email=alice@example.com"
```

Select:

```bash
./build/sqlite_manager ./app.db --select users --columns "id,name,email" --where "id >= 1" --limit 10
```

Update:

```bash
./build/sqlite_manager ./app.db --update users --set "name=Alice Updated" --where "id = 1"
```

Delete:

```bash
./build/sqlite_manager ./app.db --delete users --where "id = 1"
```

Run arbitrary SQL:

```bash
./build/sqlite_manager ./app.db --query "SELECT name FROM sqlite_master WHERE type='table'"
```

Schema inspection:

```bash
./build/sqlite_manager ./app.db --list-tables
./build/sqlite_manager ./app.db --describe users
./build/sqlite_manager ./app.db --row-count users
```

Import CSV:

```bash
./build/sqlite_manager ./app.db --import ./users.csv --table imported_users
```

Export CSV:

```bash
./build/sqlite_manager ./app.db --export ./users_out.csv --table users
./build/sqlite_manager ./app.db --export ./query_out.csv --query "SELECT * FROM users WHERE id > 5"
```

Transactions:

```bash
./build/sqlite_manager ./app.db --begin --insert users --values "id=3,name=Carol,email=carol@example.com" --commit
./build/sqlite_manager ./app.db --begin --delete users --where "id=3" --rollback
```

## Expected Output

- Aligned query result tables with headers
- Categorized DB errors (`Connection Error`, `Schema Error`, `Constraint Error`, `Query Error`)
- Automatic DB creation if missing

#include <algorithm>
#include <cctype>
#include <filesystem>
#include <fstream>
#include <iomanip>
#include <iostream>
#include <map>
#include <regex>
#include <set>
#include <sstream>
#include <stdexcept>
#include <string>
#include <vector>

#include <sqlite3.h>

namespace {

struct ParsedArgs {
    std::map<std::string, std::string> options;
    std::set<std::string> flags;
    std::vector<std::string> positional;
};

struct QueryResult {
    std::vector<std::string> columns;
    std::vector<std::vector<std::string>> rows;
};

enum class ValueType { Null, Integer, Real, Text };

struct SqlValue {
    ValueType type = ValueType::Text;
    long long integer = 0;
    double real = 0.0;
    std::string text;
};

ParsedArgs parseArgs(int argc, char** argv) {
    ParsedArgs parsed;
    for (int i = 1; i < argc; i++) {
        std::string token = argv[i];
        if (token.rfind("--", 0) == 0) {
            std::string key = token.substr(2);
            if (i + 1 < argc && std::string(argv[i + 1]).rfind("--", 0) != 0) {
                parsed.options[key] = argv[i + 1];
                i++;
            } else {
                parsed.flags.insert(key);
            }
        } else {
            parsed.positional.push_back(token);
        }
    }
    return parsed;
}

std::string escapeIdent(const std::string& name) {
    std::string out = "\"";
    for (char c : name) {
        if (c == '"') out += "\"\"";
        else out += c;
    }
    out += "\"";
    return out;
}

std::string classifyError(const std::string& message) {
    std::string lower = message;
    std::transform(lower.begin(), lower.end(), lower.begin(), [](unsigned char c) { return static_cast<char>(std::tolower(c)); });
    if (lower.find("no such table") != std::string::npos || lower.find("no such column") != std::string::npos ||
        lower.find("already exists") != std::string::npos) {
        return "Schema Error";
    }
    if (lower.find("constraint failed") != std::string::npos || lower.find("not null") != std::string::npos ||
        lower.find("unique") != std::string::npos) {
        return "Constraint Error";
    }
    if (lower.find("syntax error") != std::string::npos || lower.find("near") != std::string::npos) {
        return "Query Error";
    }
    return "Connection Error";
}

std::vector<std::string> parseCsvLine(const std::string& line) {
    std::vector<std::string> fields;
    std::string current;
    bool inQuotes = false;
    for (std::size_t i = 0; i < line.size(); i++) {
        char ch = line[i];
        if (ch == '"') {
            if (inQuotes && i + 1 < line.size() && line[i + 1] == '"') {
                current += '"';
                i++;
            } else {
                inQuotes = !inQuotes;
            }
        } else if (ch == ',' && !inQuotes) {
            fields.push_back(current);
            current.clear();
        } else {
            current += ch;
        }
    }
    fields.push_back(current);
    return fields;
}

std::string toCsvLine(const std::vector<std::string>& fields) {
    std::ostringstream out;
    for (std::size_t i = 0; i < fields.size(); i++) {
        if (i) out << ",";
        const std::string& value = fields[i];
        bool quote = value.find(',') != std::string::npos || value.find('"') != std::string::npos || value.find('\n') != std::string::npos;
        if (quote) {
            out << '"';
            for (char c : value) {
                if (c == '"') out << "\"\"";
                else out << c;
            }
            out << '"';
        } else {
            out << value;
        }
    }
    return out.str();
}

std::string trim(const std::string& input) {
    std::size_t start = 0;
    while (start < input.size() && std::isspace(static_cast<unsigned char>(input[start]))) start++;
    std::size_t end = input.size();
    while (end > start && std::isspace(static_cast<unsigned char>(input[end - 1]))) end--;
    return input.substr(start, end - start);
}

SqlValue parseValue(const std::string& raw) {
    std::string text = trim(raw);
    if (text.empty()) return SqlValue{ValueType::Text, 0, 0.0, ""};
    std::string lower = text;
    std::transform(lower.begin(), lower.end(), lower.begin(), [](unsigned char c) { return static_cast<char>(std::tolower(c)); });
    if (lower == "null") return SqlValue{ValueType::Null, 0, 0.0, ""};
    if (lower == "true") return SqlValue{ValueType::Integer, 1, 0.0, ""};
    if (lower == "false") return SqlValue{ValueType::Integer, 0, 0.0, ""};
    if (std::regex_match(text, std::regex(R"(^-?\d+$)"))) {
        return SqlValue{ValueType::Integer, std::stoll(text), 0.0, ""};
    }
    if (std::regex_match(text, std::regex(R"(^-?\d+\.\d+$)"))) {
        return SqlValue{ValueType::Real, 0, std::stod(text), ""};
    }
    if ((text.front() == '"' && text.back() == '"') || (text.front() == '\'' && text.back() == '\'')) {
        return SqlValue{ValueType::Text, 0, 0.0, text.substr(1, text.size() - 2)};
    }
    return SqlValue{ValueType::Text, 0, 0.0, text};
}

void checkRc(int rc, sqlite3* db, const std::string& context) {
    if (rc != SQLITE_OK && rc != SQLITE_DONE && rc != SQLITE_ROW) {
        throw std::runtime_error(context + ": " + sqlite3_errmsg(db));
    }
}

void executeNonQuery(sqlite3* db, const std::string& sql) {
    char* err = nullptr;
    int rc = sqlite3_exec(db, sql.c_str(), nullptr, nullptr, &err);
    if (rc != SQLITE_OK) {
        std::string msg = err ? err : "Unknown SQLite error";
        sqlite3_free(err);
        throw std::runtime_error(msg);
    }
}

QueryResult executeQuery(sqlite3* db, const std::string& sql) {
    QueryResult result;
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr);
    checkRc(rc, db, "prepare");

    int cols = sqlite3_column_count(stmt);
    for (int i = 0; i < cols; i++) {
        const char* col = sqlite3_column_name(stmt, i);
        result.columns.push_back(col ? col : "");
    }

    while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
        std::vector<std::string> row;
        for (int i = 0; i < cols; i++) {
            if (sqlite3_column_type(stmt, i) == SQLITE_NULL) {
                row.push_back("NULL");
            } else {
                const unsigned char* txt = sqlite3_column_text(stmt, i);
                row.push_back(txt ? reinterpret_cast<const char*>(txt) : "");
            }
        }
        result.rows.push_back(row);
    }
    if (rc != SQLITE_DONE) {
        std::string msg = sqlite3_errmsg(db);
        sqlite3_finalize(stmt);
        throw std::runtime_error(msg);
    }
    sqlite3_finalize(stmt);
    return result;
}

void printTable(const QueryResult& result) {
    if (result.columns.empty()) {
        std::cout << "(no columns)\n";
        return;
    }
    std::vector<std::size_t> widths;
    widths.reserve(result.columns.size());
    for (const auto& c : result.columns) widths.push_back(c.size());
    for (const auto& row : result.rows) {
        for (std::size_t i = 0; i < row.size(); i++) {
            widths[i] = std::max(widths[i], row[i].size());
        }
    }

    std::ostringstream sep;
    sep << "+-";
    for (std::size_t i = 0; i < widths.size(); i++) {
        sep << std::string(widths[i], '-');
        sep << (i + 1 < widths.size() ? "-+-" : "-+");
    }

    auto printRow = [&](const std::vector<std::string>& values) {
        std::cout << "| ";
        for (std::size_t i = 0; i < widths.size(); i++) {
            std::string cell = i < values.size() ? values[i] : "";
            std::cout << cell << std::string(widths[i] > cell.size() ? widths[i] - cell.size() : 0, ' ');
            if (i + 1 < widths.size()) std::cout << " | ";
        }
        std::cout << " |\n";
    };

    std::cout << sep.str() << "\n";
    printRow(result.columns);
    std::cout << sep.str() << "\n";
    for (const auto& row : result.rows) printRow(row);
    std::cout << sep.str() << "\n";
    std::cout << result.rows.size() << " row(s)\n";
}

bool tableExists(sqlite3* db, const std::string& tableName) {
    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(db, "SELECT name FROM sqlite_master WHERE type='table' AND name = ?;", -1, &stmt, nullptr);
    checkRc(rc, db, "prepare tableExists");
    sqlite3_bind_text(stmt, 1, tableName.c_str(), -1, SQLITE_TRANSIENT);
    rc = sqlite3_step(stmt);
    bool exists = (rc == SQLITE_ROW);
    sqlite3_finalize(stmt);
    return exists;
}

std::vector<std::pair<std::string, SqlValue>> parseKeyValueSpec(const std::string& spec) {
    std::vector<std::pair<std::string, SqlValue>> items;
    for (const auto& part : parseCsvLine(spec)) {
        std::size_t eq = part.find('=');
        if (eq == std::string::npos || eq == 0) throw std::runtime_error("Invalid key=value pair: " + part);
        std::string key = trim(part.substr(0, eq));
        std::string value = trim(part.substr(eq + 1));
        items.push_back({key, parseValue(value)});
    }
    return items;
}

std::vector<std::pair<std::string, std::string>> parseColumnSpec(const std::string& spec) {
    std::vector<std::pair<std::string, std::string>> cols;
    for (const auto& part : parseCsvLine(spec)) {
        std::string t = trim(part);
        std::size_t colon = t.find(':');
        if (colon == std::string::npos || colon == 0 || colon + 1 >= t.size()) {
            throw std::runtime_error("Invalid column definition: " + t);
        }
        cols.push_back({trim(t.substr(0, colon)), trim(t.substr(colon + 1))});
    }
    return cols;
}

void bindValue(sqlite3_stmt* stmt, int index, const SqlValue& v) {
    switch (v.type) {
        case ValueType::Null:
            sqlite3_bind_null(stmt, index);
            break;
        case ValueType::Integer:
            sqlite3_bind_int64(stmt, index, v.integer);
            break;
        case ValueType::Real:
            sqlite3_bind_double(stmt, index, v.real);
            break;
        case ValueType::Text:
            sqlite3_bind_text(stmt, index, v.text.c_str(), -1, SQLITE_TRANSIENT);
            break;
    }
}

std::string detectType(const std::vector<std::string>& values) {
    std::vector<std::string> nonEmpty;
    for (const auto& v : values) {
        std::string t = trim(v);
        if (!t.empty()) nonEmpty.push_back(t);
    }
    if (nonEmpty.empty()) return "TEXT";
    auto allInt = std::all_of(nonEmpty.begin(), nonEmpty.end(), [](const std::string& v) {
        return std::regex_match(v, std::regex(R"(^-?\d+$)"));
    });
    if (allInt) return "INTEGER";
    auto allReal = std::all_of(nonEmpty.begin(), nonEmpty.end(), [](const std::string& v) {
        return std::regex_match(v, std::regex(R"(^-?\d+(\.\d+)?$)"));
    });
    if (allReal) return "REAL";
    auto allBlob = std::all_of(nonEmpty.begin(), nonEmpty.end(), [](const std::string& v) {
        return std::regex_match(v, std::regex(R"(^0x[0-9a-fA-F]+$)"));
    });
    if (allBlob) return "BLOB";
    return "TEXT";
}

bool doCreateTable(sqlite3* db, const ParsedArgs& args) {
    auto it = args.options.find("create-table");
    if (it == args.options.end()) return false;
    auto colIt = args.options.find("columns");
    if (colIt == args.options.end()) throw std::runtime_error("--create-table requires --columns");

    auto cols = parseColumnSpec(colIt->second);
    std::ostringstream sql;
    sql << "CREATE TABLE IF NOT EXISTS " << escapeIdent(it->second) << " (";
    for (std::size_t i = 0; i < cols.size(); i++) {
        if (i) sql << ", ";
        sql << escapeIdent(cols[i].first) << " " << cols[i].second;
    }
    sql << ");";
    executeNonQuery(db, sql.str());
    std::cout << "Created/verified table: " << it->second << "\n";
    return true;
}

bool doInsert(sqlite3* db, const ParsedArgs& args) {
    auto it = args.options.find("insert");
    if (it == args.options.end()) return false;
    auto valIt = args.options.find("values");
    if (valIt == args.options.end()) throw std::runtime_error("--insert requires --values");
    auto entries = parseKeyValueSpec(valIt->second);

    std::ostringstream sql;
    sql << "INSERT INTO " << escapeIdent(it->second) << " (";
    for (std::size_t i = 0; i < entries.size(); i++) {
        if (i) sql << ", ";
        sql << escapeIdent(entries[i].first);
    }
    sql << ") VALUES (";
    for (std::size_t i = 0; i < entries.size(); i++) {
        if (i) sql << ", ";
        sql << "?";
    }
    sql << ");";

    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(db, sql.str().c_str(), -1, &stmt, nullptr);
    checkRc(rc, db, "prepare insert");
    for (std::size_t i = 0; i < entries.size(); i++) {
        bindValue(stmt, static_cast<int>(i + 1), entries[i].second);
    }
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        std::string msg = sqlite3_errmsg(db);
        sqlite3_finalize(stmt);
        throw std::runtime_error(msg);
    }
    sqlite3_finalize(stmt);
    std::cout << "Inserted 1 row into " << it->second << "\n";
    return true;
}

bool doSelect(sqlite3* db, const ParsedArgs& args) {
    auto it = args.options.find("select");
    if (it == args.options.end()) return false;
    std::string columns = "*";
    if (auto c = args.options.find("columns"); c != args.options.end()) columns = c->second;
    std::ostringstream sql;
    sql << "SELECT " << columns << " FROM " << escapeIdent(it->second);
    if (auto w = args.options.find("where"); w != args.options.end()) sql << " WHERE " << w->second;
    if (auto l = args.options.find("limit"); l != args.options.end()) sql << " LIMIT " << std::stoi(l->second);
    sql << ";";
    printTable(executeQuery(db, sql.str()));
    return true;
}

bool doUpdate(sqlite3* db, const ParsedArgs& args) {
    auto it = args.options.find("update");
    if (it == args.options.end()) return false;
    auto setIt = args.options.find("set");
    if (setIt == args.options.end()) throw std::runtime_error("--update requires --set");
    auto entries = parseKeyValueSpec(setIt->second);

    std::ostringstream sql;
    sql << "UPDATE " << escapeIdent(it->second) << " SET ";
    for (std::size_t i = 0; i < entries.size(); i++) {
        if (i) sql << ", ";
        sql << escapeIdent(entries[i].first) << " = ?";
    }
    if (auto w = args.options.find("where"); w != args.options.end()) sql << " WHERE " << w->second;
    sql << ";";

    sqlite3_stmt* stmt = nullptr;
    int rc = sqlite3_prepare_v2(db, sql.str().c_str(), -1, &stmt, nullptr);
    checkRc(rc, db, "prepare update");
    for (std::size_t i = 0; i < entries.size(); i++) bindValue(stmt, static_cast<int>(i + 1), entries[i].second);
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_DONE) {
        std::string msg = sqlite3_errmsg(db);
        sqlite3_finalize(stmt);
        throw std::runtime_error(msg);
    }
    sqlite3_finalize(stmt);
    long long changed = sqlite3_changes64(db);
    std::cout << "Updated " << changed << " row(s) in " << it->second << "\n";
    return true;
}

bool doDelete(sqlite3* db, const ParsedArgs& args) {
    auto it = args.options.find("delete");
    if (it == args.options.end()) return false;
    std::ostringstream sql;
    sql << "DELETE FROM " << escapeIdent(it->second);
    if (auto w = args.options.find("where"); w != args.options.end()) sql << " WHERE " << w->second;
    sql << ";";
    executeNonQuery(db, sql.str());
    long long changed = sqlite3_changes64(db);
    std::cout << "Deleted " << changed << " row(s) from " << it->second << "\n";
    return true;
}

bool doImport(sqlite3* db, const ParsedArgs& args) {
    auto it = args.options.find("import");
    if (it == args.options.end()) return false;
    auto tableIt = args.options.find("table");
    if (tableIt == args.options.end()) throw std::runtime_error("--import requires --table");
    std::filesystem::path csvPath = std::filesystem::absolute(it->second);
    if (!std::filesystem::exists(csvPath)) throw std::runtime_error("CSV file not found: " + csvPath.string());

    std::ifstream in(csvPath);
    if (!in.is_open()) throw std::runtime_error("Failed to open CSV: " + csvPath.string());
    std::vector<std::vector<std::string>> rows;
    std::string line;
    while (std::getline(in, line)) {
        if (line.empty()) continue;
        rows.push_back(parseCsvLine(line));
    }
    if (rows.empty()) throw std::runtime_error("CSV is empty.");
    std::vector<std::string> headers = rows[0];
    std::vector<std::vector<std::string>> data(rows.begin() + 1, rows.end());
    const std::string& table = tableIt->second;

    if (!tableExists(db, table)) {
        std::ostringstream createSql;
        createSql << "CREATE TABLE " << escapeIdent(table) << " (";
        for (std::size_t i = 0; i < headers.size(); i++) {
            if (i) createSql << ", ";
            std::vector<std::string> colVals;
            for (const auto& row : data) colVals.push_back(i < row.size() ? row[i] : "");
            createSql << escapeIdent(headers[i]) << " " << detectType(colVals);
        }
        createSql << ");";
        executeNonQuery(db, createSql.str());
    }

    if (!data.empty()) {
        std::ostringstream insertSql;
        insertSql << "INSERT INTO " << escapeIdent(table) << " (";
        for (std::size_t i = 0; i < headers.size(); i++) {
            if (i) insertSql << ", ";
            insertSql << escapeIdent(headers[i]);
        }
        insertSql << ") VALUES (";
        for (std::size_t i = 0; i < headers.size(); i++) {
            if (i) insertSql << ", ";
            insertSql << "?";
        }
        insertSql << ");";

        sqlite3_stmt* stmt = nullptr;
        int rc = sqlite3_prepare_v2(db, insertSql.str().c_str(), -1, &stmt, nullptr);
        checkRc(rc, db, "prepare import");
        for (const auto& row : data) {
            for (std::size_t i = 0; i < headers.size(); i++) {
                std::string value = i < row.size() ? row[i] : "";
                bindValue(stmt, static_cast<int>(i + 1), parseValue(value));
            }
            rc = sqlite3_step(stmt);
            if (rc != SQLITE_DONE) {
                std::string msg = sqlite3_errmsg(db);
                sqlite3_finalize(stmt);
                throw std::runtime_error(msg);
            }
            sqlite3_reset(stmt);
            sqlite3_clear_bindings(stmt);
        }
        sqlite3_finalize(stmt);
    }
    std::cout << "Imported " << data.size() << " row(s) into " << table << "\n";
    return true;
}

bool doExport(sqlite3* db, const ParsedArgs& args) {
    auto it = args.options.find("export");
    if (it == args.options.end()) return false;
    std::string query;
    if (auto q = args.options.find("query"); q != args.options.end()) {
        query = q->second;
    } else if (auto t = args.options.find("table"); t != args.options.end()) {
        query = "SELECT * FROM " + escapeIdent(t->second) + ";";
    } else {
        throw std::runtime_error("--export requires either --table or --query");
    }

    QueryResult res = executeQuery(db, query);
    std::filesystem::path outPath = std::filesystem::absolute(it->second);
    std::ofstream out(outPath);
    if (!out.is_open()) throw std::runtime_error("Failed to open output CSV: " + outPath.string());
    out << toCsvLine(res.columns) << "\n";
    for (const auto& row : res.rows) out << toCsvLine(row) << "\n";
    out.close();
    std::cout << "Exported " << res.rows.size() << " row(s) to " << outPath.string() << "\n";
    return true;
}

void inspectSchema(sqlite3* db, const ParsedArgs& args) {
    if (args.flags.count("list-tables")) {
        QueryResult tables = executeQuery(db, "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name;");
        if (tables.rows.empty()) {
            std::cout << "No user tables found.\n";
        } else {
            QueryResult out;
            out.columns = {"table", "row_count"};
            for (const auto& row : tables.rows) {
                std::string table = row[0];
                QueryResult count = executeQuery(db, "SELECT COUNT(*) AS row_count FROM " + escapeIdent(table) + ";");
                out.rows.push_back({table, count.rows.empty() ? "0" : count.rows[0][0]});
            }
            printTable(out);
        }
    }
    if (auto d = args.options.find("describe"); d != args.options.end()) {
        printTable(executeQuery(db, "PRAGMA table_info(" + escapeIdent(d->second) + ");"));
    }
    if (auto r = args.options.find("row-count"); r != args.options.end()) {
        printTable(executeQuery(db, "SELECT COUNT(*) AS row_count FROM " + escapeIdent(r->second) + ";"));
    }
}

void buildSampleDb(sqlite3* db) {
    executeNonQuery(db, R"(
        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;
    )");

    executeNonQuery(db, "INSERT INTO users(id, name, email) VALUES (1, 'Alice', 'alice@example.com'), (2, 'Bob', 'bob@example.com'), (3, 'Carol', 'carol@example.com');");
    executeNonQuery(db, "INSERT INTO products(id, name, price) VALUES (1, 'Keyboard', 79.99), (2, 'Monitor', 249.50), (3, 'Mouse', 29.95);");
    executeNonQuery(db, "INSERT INTO orders(id, user_id, product_id, quantity, created_at) VALUES "
                        "(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');");
}

void runDemo(sqlite3* db) {
    std::cout << "No database path provided. Creating sample database and running demo queries.\n\n";
    buildSampleDb(db);

    std::cout << "Demo 1: Join users, orders, products\n";
    printTable(executeQuery(db, R"(
        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;
    )"));

    std::cout << "\nDemo 2: Aggregation by user\n";
    printTable(executeQuery(db, R"(
        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;
    )"));

    std::cout << "\nDemo 3: Filtering products over 50\n";
    printTable(executeQuery(db, "SELECT id, name, price FROM products WHERE price > 50 ORDER BY price DESC;"));
}

void usage() {
    std::cout << "SQLite Database Manager\n\n"
                 "Usage:\n"
                 "  ./sqlite_manager <db-path> [options]\n"
                 "  ./sqlite_manager            # sample DB demo\n\n"
                 "Core options:\n"
                 "  --create-table <table> --columns \"id:INTEGER PRIMARY KEY,name:TEXT NOT NULL\"\n"
                 "  --insert <table> --values \"id=1,name=Alice,email=alice@example.com\"\n"
                 "  --select <table> [--columns \"id,name\"] [--where \"id > 1\"] [--limit 20]\n"
                 "  --update <table> --set \"name=Alice2\" [--where \"id=1\"]\n"
                 "  --delete <table> [--where \"id=1\"]\n"
                 "  --query \"SELECT * FROM users\"\n\n"
                 "Schema inspection:\n"
                 "  --list-tables\n"
                 "  --describe <table>\n"
                 "  --row-count <table>\n\n"
                 "Import/export:\n"
                 "  --import <file.csv> --table <table>\n"
                 "  --export <file.csv> --table <table>\n"
                 "  --export <file.csv> --query \"SELECT ...\"\n\n"
                 "Transactions:\n"
                 "  --begin\n"
                 "  --commit\n"
                 "  --rollback\n";
}

} // namespace

int main(int argc, char** argv) {
    ParsedArgs args = parseArgs(argc, argv);
    if (args.flags.count("help")) {
        usage();
        return 0;
    }

    std::filesystem::path dbPath = args.positional.empty() ? std::filesystem::absolute("sample_demo.sqlite")
                                                           : std::filesystem::absolute(args.positional[0]);
    sqlite3* db = nullptr;
    int rc = sqlite3_open(dbPath.string().c_str(), &db);
    if (rc != SQLITE_OK) {
        std::string msg = sqlite3_errmsg(db);
        if (db) sqlite3_close(db);
        std::cerr << "[Connection Error] " << msg << "\n";
        return 1;
    }

    bool mutated = !std::filesystem::exists(dbPath);
    int operations = 0;
    bool inTx = false;

    try {
        executeNonQuery(db, "PRAGMA foreign_keys = ON;");

        if (args.positional.empty()) {
            runDemo(db);
            mutated = true;
            operations++;
        } else {
            if (args.flags.count("begin")) {
                executeNonQuery(db, "BEGIN TRANSACTION;");
                inTx = true;
                std::cout << "Transaction started.\n";
            }

            if (doCreateTable(db, args)) { mutated = true; operations++; }
            if (doInsert(db, args)) { mutated = true; operations++; }
            if (doUpdate(db, args)) { mutated = true; operations++; }
            if (doDelete(db, args)) { mutated = true; operations++; }
            if (doImport(db, args)) { mutated = true; operations++; }
            if (auto q = args.options.find("query"); q != args.options.end()) {
                printTable(executeQuery(db, q->second));
                operations++;
            }
            if (doSelect(db, args)) operations++;
            if (args.flags.count("list-tables") || args.options.count("describe") || args.options.count("row-count")) {
                inspectSchema(db, args);
                operations++;
            }
            if (doExport(db, args)) operations++;

            if (inTx) {
                if (args.flags.count("rollback")) {
                    executeNonQuery(db, "ROLLBACK;");
                    std::cout << "Transaction rolled back.\n";
                    mutated = false;
                } else {
                    executeNonQuery(db, "COMMIT;");
                    if (args.flags.count("commit")) std::cout << "Transaction committed.\n";
                    else std::cout << "Transaction committed automatically.\n";
                    mutated = true;
                }
                inTx = false;
            }
        }

        if (operations == 0) usage();
        std::cout << "Database path: " << dbPath.string() << "\n";
    } catch (const std::exception& e) {
        if (inTx) {
            try { executeNonQuery(db, "ROLLBACK;"); } catch (...) {}
        }
        std::string msg = e.what();
        std::cerr << "[" << classifyError(msg) << "] " << msg << "\n";
        sqlite3_close(db);
        return 1;
    }

    sqlite3_close(db);
    return 0;
}