Prepared Statements

/**
 * Binds parameters to the prepared statement
 * @param params - Parameters to bind (array, object, or individual values)
 * @param callback - Optional callback called when binding completes
 * @returns Statement instance for chaining
 */
bind(params?: any, callback?: (err: Error | null) => void): this;

/**
 * Binds multiple parameters as separate arguments
 */
bind(...params: any[]): this;

const stmt = db.prepare("INSERT INTO users (name, email, age) VALUES (?, ?, ?)");

// Bind with array
stmt.bind(["Alice", "alice@example.com", 25], (err) => {
    if (err) console.error('Bind error:', err.message);
});

// Bind with individual parameters
stmt.bind("Bob", "bob@example.com", 30);

// Named parameters (using object)
const namedStmt = db.prepare("INSERT INTO users (name, email) VALUES ($name, $email)");
namedStmt.bind({
    $name: "Charlie",
    $email: "charlie@example.com"
});

/**
 * Resets the statement to its initial state
 * @param callback - Optional callback called when reset completes
 * @returns Statement instance for chaining
 */
reset(callback?: (err: null) => void): this;

const stmt = db.prepare("SELECT * FROM users WHERE age > ?");

// Use statement
stmt.bind([18]).get((err, row) => {
    console.log('First query result:', row);
    
    // Reset and reuse
    stmt.reset().bind([25]).get((err, row) => {
        console.log('Second query result:', row);
    });
});

/**
 * Finalizes the statement and releases resources
 * @param callback - Optional callback called when finalization completes
 * @returns Database instance that created this statement
 */
finalize(callback?: (err: Error) => void): Database;

const stmt = db.prepare("INSERT INTO logs (message) VALUES (?)");

// Use statement multiple times
stmt.run("Log entry 1");
stmt.run("Log entry 2");
stmt.run("Log entry 3");

// Always finalize when done
stmt.finalize((err) => {
    if (err) {
        console.error('Finalization error:', err.message);
    } else {
        console.log('Statement finalized successfully');
    }
});

/**
 * Executes the prepared statement
 * @param params - Optional parameters to bind before execution
 * @param callback - Optional callback with RunResult context
 * @returns Statement instance for chaining
 */
run(params?: any, callback?: (this: RunResult, err: Error | null) => void): this;

/**
 * Executes with multiple parameters as separate arguments
 */
run(...params: any[]): this;

const insertStmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");

// Execute with parameters
insertStmt.run(["Alice", "alice@example.com"], function(err) {
    if (err) {
        console.error('Insert error:', err.message);
    } else {
        console.log(`Inserted user with ID: ${this.lastID}`);
        console.log(`Changes made: ${this.changes}`);
    }
});

// Execute with individual parameters
insertStmt.run("Bob", "bob@example.com");

// Chaining operations
insertStmt
    .run("Charlie", "charlie@example.com")
    .run("David", "david@example.com")
    .finalize();

/**
 * Gets a single row using the prepared statement
 * @param params - Optional parameters to bind before execution
 * @param callback - Callback receiving the result row
 * @returns Statement instance for chaining
 */
get<T>(params?: any, callback?: (this: RunResult, err: Error | null, row?: T) => void): this;

/**
 * Gets with multiple parameters as separate arguments
 */
get(...params: any[]): this;

const selectStmt = db.prepare("SELECT * FROM users WHERE age > ? AND city = ?");

// Get with array parameters
selectStmt.get([25, "New York"], (err, row) => {
    if (err) {
        console.error('Query error:', err.message);
    } else if (row) {
        console.log(`Found user: ${row.name}, age ${row.age}`);
    } else {
        console.log('No user found matching criteria');
    }
});

// Get with individual parameters
selectStmt.get(30, "London", (err, row) => {
    if (row) {
        console.log(`User: ${row.name}`);
    }
});

// With named parameters
const namedSelectStmt = db.prepare("SELECT * FROM users WHERE name = $name");
namedSelectStmt.get({ $name: "Alice" }, (err, row) => {
    console.log('Found user:', row);
});

/**
 * Gets all rows using the prepared statement
 * @param params - Optional parameters to bind before execution
 * @param callback - Callback receiving array of result rows
 * @returns Statement instance for chaining
 */
all<T>(params?: any, callback?: (this: RunResult, err: Error | null, rows: T[]) => void): this;

/**
 * Gets all with multiple parameters as separate arguments
 */
all(...params: any[]): this;

const selectAllStmt = db.prepare("SELECT * FROM users WHERE age BETWEEN ? AND ?");

// Get all with parameters
selectAllStmt.all([18, 65], (err, rows) => {
    if (err) {
        console.error('Query error:', err.message);
    } else {
        console.log(`Found ${rows.length} users`);
        rows.forEach(row => {
            console.log(`- ${row.name}, age ${row.age}`);
        });
    }
});

// Reuse with different parameters
selectAllStmt.all([25, 35], (err, rows) => {
    console.log(`Users aged 25-35: ${rows.length}`);
});

/**
 * Iterates through rows using the prepared statement
 * @param params - Optional parameters to bind before execution
 * @param callback - Callback called for each row
 * @param complete - Optional callback called when iteration completes
 * @returns Statement instance for chaining
 */
each<T>(
    params?: any, 
    callback?: (this: RunResult, err: Error | null, row: T) => void, 
    complete?: (err: Error | null, count: number) => void
): this;

/**
 * Iterates with multiple parameters as separate arguments
 */
each(...params: any[]): this;

const processStmt = db.prepare("SELECT id, name, email FROM users WHERE active = ?");

// Process each active user
processStmt.each([1], (err, row) => {
    if (err) {
        console.error('Row processing error:', err.message);
    } else {
        console.log(`Processing user ${row.id}: ${row.name}`);
        // Perform processing logic here
    }
}, (err, count) => {
    if (err) {
        console.error('Iteration error:', err.message);
    } else {
        console.log(`Processed ${count} active users`);
    }
});

// Process with different parameters
processStmt.each([0], (err, row) => {
    console.log(`Inactive user: ${row.name}`);
}, (err, count) => {
    console.log(`Found ${count} inactive users`);
});

/**
 * Maps query results to key-value pairs using the prepared statement
 * @param params - Optional parameters to bind before execution
 * @param callback - Callback receiving mapped results
 * @returns Statement instance for chaining
 */
map<T>(params?: any, callback?: (this: RunResult, err: Error | null, result: {[key: string]: T}) => void): this;

/**
 * Maps with multiple parameters as separate arguments
 */
map(...params: any[]): this;

const mapStmt = db.prepare("SELECT id, name FROM users WHERE department = ?");

// Map users by ID
mapStmt.map(["Engineering"], (err, result) => {
    if (err) {
        console.error('Map error:', err.message);
    } else {
        // result = { "1": "Alice", "2": "Bob", ... }
        console.log('Engineering users:');
        Object.entries(result).forEach(([id, name]) => {
            console.log(`ID ${id}: ${name}`);
        });
    }
});

// Map users from different department
mapStmt.map(["Marketing"], (err, result) => {
    console.log(`Marketing has ${Object.keys(result).length} users`);
});

const stmt = db.prepare("INSERT INTO users (name, email) VALUES (?, ?)");

db.serialize(() => {
    db.run("BEGIN TRANSACTION");
    
    try {
        stmt.run("User1", "user1@example.com");
        stmt.run("User2", "user2@example.com");
        stmt.run("User3", "user3@example.com");
        
        db.run("COMMIT", (err) => {
            if (err) {
                console.error('Commit failed:', err.message);
                db.run("ROLLBACK");
            } else {
                console.log('Transaction completed successfully');
            }
            stmt.finalize();
        });
    } catch (error) {
        console.error('Transaction error:', error.message);
        db.run("ROLLBACK");
        stmt.finalize();
    }
});

const batchStmt = db.prepare("INSERT INTO logs (timestamp, level, message) VALUES (?, ?, ?)");

const logEntries = [
    [Date.now(), "INFO", "Application started"],
    [Date.now(), "DEBUG", "Configuration loaded"],
    [Date.now(), "WARNING", "High memory usage detected"],
    [Date.now(), "ERROR", "Database connection failed"]
];

db.serialize(() => {
    logEntries.forEach(([timestamp, level, message]) => {
        batchStmt.run(timestamp, level, message);
    });
    
    batchStmt.finalize((err) => {
        if (err) {
            console.error('Batch processing failed:', err.message);
        } else {
            console.log(`Processed ${logEntries.length} log entries`);
        }
    });
});

// Positional parameters (?)
const positionalStmt = db.prepare("SELECT * FROM users WHERE age > ? AND city = ?");
positionalStmt.get([25, "New York"], callback);

// Named parameters ($name, :name, @name)
const namedStmt = db.prepare("SELECT * FROM users WHERE name = $name AND email = $email");
namedStmt.get({
    $name: "Alice",
    $email: "alice@example.com"
}, callback);

// Mixed parameters (not recommended, but supported)
const mixedStmt = db.prepare("SELECT * FROM users WHERE id = ? AND name = $name");
mixedStmt.bind([123]).bind({ $name: "Bob" }).get(callback);

// 1. Prepare - creates the statement
const stmt = db.prepare("INSERT INTO users (name) VALUES (?)");

// 2. Bind (optional) - can be done separately or with run/get/all/each
stmt.bind(["Alice"]);

// 3. Execute - run the statement (can be done multiple times)
stmt.run(); // Uses previously bound parameters
stmt.run(["Bob"]); // Binds new parameters and executes

// 4. Reset (optional) - clears bindings and state
stmt.reset();

// 5. Finalize - must be called to free resources
stmt.finalize((err) => {
    if (err) console.error('Finalization error:', err);
});