import BunLiteDB from 'bunlite-typed';

// Create schema configuration
const schemaConfig = {
  users: {
    id: { type: "INTEGER PRIMARY KEY AUTOINCREMENT" },
    name: { type: "TEXT NOT NULL" },
    email: { type: "TEXT UNIQUE NOT NULL" }
  },
  posts: {
    id: { type: "INTEGER PRIMARY KEY AUTOINCREMENT" },
    userId: { type: "INTEGER", foreignKey: "REFERENCES users(id)" },
    title: { type: "TEXT NOT NULL" },
    content: { type: "TEXT NOT NULL" }
  }
} as const; // the type property gets converted from its SQL binding to TS types. https://bun.sh/docs/api/sqlite#datatypes
// If youre providing the schema directly into the constructor, you dont need the 'as const'.

// Initialize database with schema configuration - types are inferred automatically
const db = new BunLiteDB("mydb.sqlite", schemaConfig);

// Create all tables from schema
db.createTablesFromSchema();

// Type-safe insertions
db.insertRecord("users", {
  name: "John Doe", // 'TEXT' from the schema type prop gets inferred to string
  email: "[email protected]"
  // id is auto-generated
});

// Query with full type inference
const users = db.fetchAllRecords("users");
// users is typed as { id: number, name: string, email: string }[]

// Upsert with type checking
db.upsertRecord(
  "users",
  { name: "John Updated", email: "[email protected]" },
  "email"  // Conflict column is type-checked
);

// Typed query results
const user = db.fetchRecordsWithCondition(
  "users",
  "email = ?",
  ["[email protected]"]
);

// Get schema information
const schema = db.getSchema("users");

// Get record count
const totalUsers = db.getRecordCount("users");

// Get count with conditions
const activeUsers = db.getRecordCount(
  "users",
  "status = ?",
  ["active"]
);

// Complex counting
const recentAdmins = db.getRecordCount(
  "users",
  "role = ? AND created_at > ?",
  ["admin", Date.now() - 86400000] // Admins created in last 24 hours
);

// Basic pagination
const page1 = db.fetchRecordsWithPagination("users", 1, 10);

// Pagination with conditions
const activePage = db.fetchRecordsWithPagination(
  "users", 
  1, 
  10,
  "status = ?",
  ["active"]
);

// Complex conditions
const filtered = db.fetchRecordsWithPagination(
  "users",
  1,
  10,
  "created_at > ? AND role = ?",
  [Date.now() - 86400000, "admin"] // Last 24 hours admins
);

// Basic iteration
async function processUsers() {
  for await (const user of db.recordsIterator("users")) {
    console.log(user.name);
  }
}

// Iteration with condition
async function processActiveUsers() {
  for await (const user of db.recordsIterator("users", 500, "status = ?", ["active"])) {
    console.log(user.name);
  }
}

// Complex filtering
async function processRecentAdmins() {
  const condition = "role = ? AND last_login > ?";
  const values = ["admin", Date.now() - 86400000];
  
  for await (const user of db.recordsIterator("users", 100, condition, values)) {
    console.log(user.name);
  }
}

new BunLiteDB<Schema>(
  dbName: ":memory:" | string,
  schemaConfig?: SchemaConfig<Schema>,
  opts?: DbOptions
)