#include <iostream>
#include <soci/soci.h>
#include <soci/sqlite3/soci-sqlite3.h>

using namespace soci;

int main() {
    try {
        // 建立数据库会话
        session sql(sqlite3, "test.db");

        // 创建表
        sql << "CREATE TABLE IF NOT EXISTS users ("
                "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                "name TEXT, "
                "age INTEGER)";

        // 插入数据
        std::string name = "Alice";
        int age = 25;
        sql << "INSERT INTO users(name, age) VALUES(:name, :age)",
             use(name), use(age);

        // 查询数据
        std::string query_name;
        int query_age;
        rowset<row> rs = (sql.prepare 
            << "SELECT name, age FROM users WHERE age > :min_age",
            use(20));

        for (auto const& row : rs) {
            query_name = row.get<std::string>(0);
            query_age = row.get<int>(1);
            std::cout << "Name: " << query_name 
                      << ", Age: " << query_age << std::endl;
        }

    } catch (const std::exception& e) {
        std::cerr << "Error: " << e.what() << std::endl;
    }

    return 0;
}

struct User {
    int id;
    std::string name;
    int age;

    // 映射到数据库插入
    void s*e(session& sql) {
        if (id == 0) {
            sql << "INSERT INTO users(name, age) VALUES(:name, :age)",
                 use(name), use(age);
            sql << "SELECT last_insert_rowid()", into(id);
        } else {
            sql << "UPDATE users SET name=:name, age=:age WHERE id=:id",
                 use(name), use(age), use(id);
        }
    }

    static std::vector<User> find_by_age(session& sql, int min_age) {
        std::vector<User> result;
        rowset<row> rs = (sql.prepare 
            << "SELECT id, name, age FROM users WHERE age >= :age",
            use(min_age));

        for (auto const& r : rs) {
            User u;
            u.id = r.get<int>(0);
            u.name = r.get<std::string>(1);
            u.age = r.get<int>(2);
            result.push_back(u);
        }
        return result;
    }
};

try {
    transaction tr(sql);
    sql << "DELETE FROM users WHERE age < 18";
    // 如果没有调用 tr.commit()，析构时自动回滚
    tr.commit();
} catch (...) {
    // 自动回滚
}