Database Operations

/**
 * Creates a new database connection
 * @param filename - Path to database file or ':memory:' for in-memory database
 * @param mode - Optional database mode flags (OPEN_READONLY, OPEN_READWRITE, OPEN_CREATE)
 * @param callback - Optional callback called when database is opened
 */
constructor(filename: string, mode?: number, callback?: (err: Error | null) => void);

const sqlite3 = require('sqlite3').verbose();

// In-memory database
const memDb = new sqlite3.Database(':memory:');

// File database with callback
const db = new sqlite3.Database('example.db', (err) => {
    if (err) {
        console.error('Error opening database:', err.message);
    } else {
        console.log('Connected to SQLite database');
    }
});

// Read-only mode
const readOnlyDb = new sqlite3.Database('data.db', sqlite3.OPEN_READONLY);

/**
 * Executes a SQL statement
 * @param sql - SQL statement to execute
 * @param params - Optional parameters for parameter binding
 * @param callback - Optional callback with RunResult context
 * @returns Database instance for chaining
 */
run(sql: string, params?: any, callback?: (this: RunResult, err: Error | null) => void): this;

// Simple statement
db.run("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");

// With parameters
db.run("INSERT INTO users (name) VALUES (?)", ["Alice"], function(err) {
    if (err) {
        console.error(err.message);
    } else {
        console.log(`Row inserted with ID: ${this.lastID}`);
        console.log(`Rows changed: ${this.changes}`);
    }
});

// Multiple parameters
db.run("INSERT INTO users (id, name) VALUES (?, ?)", [1, "Bob"]);

/**
 * Gets a single row from a SELECT query
 * @param sql - SELECT SQL statement
 * @param params - Optional parameters for parameter binding
 * @param callback - Callback receiving the result row
 * @returns Database instance for chaining
 */
get<T>(sql: string, params?: any, callback?: (this: Statement, err: Error | null, row: T) => void): this;

// Simple query
db.get("SELECT name FROM users WHERE id = 1", (err, row) => {
    if (err) {
        console.error(err.message);
    } else {
        console.log(row ? row.name : 'No user found');
    }
});

// With parameters
db.get("SELECT * FROM users WHERE name = ?", ["Alice"], (err, row) => {
    if (row) {
        console.log(`User: ${row.name}, ID: ${row.id}`);
    }
});

/**
 * Gets all rows from a SELECT query
 * @param sql - SELECT SQL statement
 * @param params - Optional parameters for parameter binding
 * @param callback - Callback receiving array of result rows
 * @returns Database instance for chaining
 */
all<T>(sql: string, params?: any, callback?: (this: Statement, err: Error | null, rows: T[]) => void): this;

// Get all users
db.all("SELECT * FROM users", (err, rows) => {
    if (err) {
        console.error(err.message);
    } else {
        rows.forEach(row => {
            console.log(`${row.id}: ${row.name}`);
        });
    }
});

// With parameters
db.all("SELECT * FROM users WHERE name LIKE ?", ["%A%"], (err, rows) => {
    console.log(`Found ${rows.length} users`);
});

/**
 * Iterates through rows from a SELECT query
 * @param sql - SELECT SQL statement
 * @param params - Optional parameters for parameter binding
 * @param callback - Callback called for each row
 * @param complete - Optional callback called when iteration completes
 * @returns Database instance for chaining
 */
each<T>(
    sql: string, 
    params?: any, 
    callback?: (this: Statement, err: Error | null, row: T) => void, 
    complete?: (err: Error | null, count: number) => void
): this;

// Process each row
db.each("SELECT * FROM users", (err, row) => {
    if (err) {
        console.error(err.message);
    } else {
        console.log(`Processing user: ${row.name}`);
    }
}, (err, count) => {
    console.log(`Processed ${count} rows`);
});

// With parameters
db.each("SELECT * FROM users WHERE id > ?", [10], (err, row) => {
    // Process each row
}, (err, count) => {
    console.log(`Found ${count} users with ID > 10`);
});

/**
 * Maps query results to key-value pairs
 * @param sql - SELECT SQL statement  
 * @param params - Optional parameters for parameter binding
 * @param callback - Callback receiving mapped results
 * @returns Database instance for chaining
 */
map<T>(sql: string, params?: any, callback?: (this: Statement, err: Error | null, result: {[key: string]: T}) => void): this;

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

/**
 * Executes raw SQL statements (multiple statements allowed)
 * @param sql - SQL statements separated by semicolons
 * @param callback - Optional callback called when execution completes
 * @returns Database instance for chaining
 */
exec(sql: string, callback?: (this: Statement, err: Error | null) => void): this;

const schema = `
    CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
    CREATE TABLE posts (id INTEGER PRIMARY KEY, user_id INTEGER, title TEXT);
    INSERT INTO users (name) VALUES ('Alice'), ('Bob');
`;

db.exec(schema, (err) => {
    if (err) {
        console.error('Schema creation failed:', err.message);
    } else {
        console.log('Schema created successfully');
    }
});

/**
 * Prepares a SQL statement for repeated execution
 * @param sql - SQL statement with parameter placeholders
 * @param params - Optional initial parameters
 * @param callback - Optional callback called when statement is prepared
 * @returns Statement instance
 */
prepare(sql: string, params?: any, callback?: (this: Statement, err: Error | null) => void): Statement;

// Basic prepared statement
const stmt = db.prepare("INSERT INTO users (name) VALUES (?)");
stmt.run("Alice");
stmt.run("Bob");
stmt.finalize();

// With callback
const selectStmt = db.prepare("SELECT * FROM users WHERE name = ?", (err) => {
    if (err) {
        console.error('Prepare failed:', err.message);
    }
});

/**
 * Serializes database operations to run sequentially
 * @param callback - Optional callback function to execute in serial mode
 */
serialize(callback?: () => void): void;

db.serialize(() => {
    db.run("CREATE TABLE test (id INTEGER, value TEXT)");
    db.run("INSERT INTO test VALUES (1, 'first')");
    db.run("INSERT INTO test VALUES (2, 'second')");
    
    db.get("SELECT COUNT(*) as count FROM test", (err, row) => {
        console.log(`Total rows: ${row.count}`);
    });
});

/**
 * Parallelizes database operations to run concurrently
 * @param callback - Optional callback function to execute in parallel mode
 */
parallelize(callback?: () => void): void;

db.parallelize(() => {
    // These operations will run in parallel
    db.run("INSERT INTO logs VALUES (?, ?)", [1, "Log entry 1"]);
    db.run("INSERT INTO logs VALUES (?, ?)", [2, "Log entry 2"]);
    db.run("INSERT INTO logs VALUES (?, ?)", [3, "Log entry 3"]);
});

/**
 * Configures database options
 * @param option - Configuration option name
 * @param value - Configuration value (for busyTimeout)
 */
configure(option: "busyTimeout", value: number): void;

/**
 * Configures database limits
 * @param option - Must be "limit"
 * @param id - Limit ID constant
 * @param value - Limit value
 */
configure(option: "limit", id: number, value: number): void;

// Set busy timeout to 30 seconds
db.configure("busyTimeout", 30000);

// Set SQL length limit
db.configure("limit", sqlite3.LIMIT_SQL_LENGTH, 1000000);

/**
 * Loads a SQLite extension
 * @param filename - Path to extension file
 * @param callback - Optional callback called when extension is loaded
 * @returns Database instance for chaining
 */
loadExtension(filename: string, callback?: (err: Error | null) => void): this;

// Load an extension
db.loadExtension("./my-extension.so", (err) => {
    if (err) {
        console.error('Extension loading failed:', err.message);
    } else {
        console.log('Extension loaded successfully');
    }
});

/**
 * Waits for all pending database operations to complete
 * @param callback - Optional callback called when all operations complete
 * @returns Database instance for chaining
 */
wait(callback?: (param: null) => void): this;

/**
 * Interrupts currently running database operations
 */
interrupt(): void;

/**
 * Closes the database connection
 * @param callback - Optional callback called when database is closed
 */
close(callback?: (err: Error | null) => void): void;

// Simple close
db.close();

// With callback
db.close((err) => {
    if (err) {
        console.error('Error closing database:', err.message);
    } else {
        console.log('Database closed successfully');
    }
});

/**
 * Creates a database backup (simple form)
 * @param filename - Destination filename
 * @param callback - Optional callback called when backup is initialized
 * @returns Backup instance
 */
backup(filename: string, callback?: (err: Error | null) => void): Backup;

/**
 * Creates a database backup (advanced form)
 * @param filename - Source or destination filename
 * @param destName - Destination database name
 * @param sourceName - Source database name  
 * @param filenameIsDest - Whether filename parameter is destination
 * @param callback - Optional callback called when backup is initialized
 * @returns Backup instance
 */
backup(
    filename: string, 
    destName: string, 
    sourceName: string, 
    filenameIsDest: boolean, 
    callback?: (err: Error | null) => void
): Backup;

/**
 * Emitted when a SQL statement is traced
 * @param sql - The SQL statement being executed
 */
on(event: "trace", listener: (sql: string) => void): this;

/**
 * Emitted when a SQL statement is profiled
 * @param sql - The SQL statement that was executed
 * @param time - Execution time in milliseconds
 */
on(event: "profile", listener: (sql: string, time: number) => void): this;

/**
 * Emitted when the database is changed
 * @param type - Type of change (insert, update, delete)
 * @param database - Database name
 * @param table - Table name
 * @param rowid - Row ID that was changed
 */
on(event: "change", listener: (type: string, database: string, table: string, rowid: number) => void): this;

/**
 * Emitted when an error occurs
 * @param err - The error object
 */
on(event: "error", listener: (err: Error) => void): this;

/**
 * Emitted when the database is opened
 */
on(event: "open", listener: () => void): this;

/**
 * Emitted when the database is closed
 */
on(event: "close", listener: () => void): this;

// Enable tracing
db.on('trace', (sql) => {
    console.log('Executing SQL:', sql);
});

// Enable profiling
db.on('profile', (sql, time) => {
    console.log(`SQL executed in ${time}ms:`, sql);
});

// Monitor changes
db.on('change', (type, database, table, rowid) => {
    console.log(`${type} in ${database}.${table}, row ${rowid}`);
});

// Handle errors
db.on('error', (err) => {
    console.error('Database error:', err.message);
});

/**
 * Adds an event listener (overrides EventEmitter.addListener)
 * Automatically configures SQLite for trace, profile, and change events
 * @param event - Event name
 * @param listener - Event listener function
 * @returns Database instance for chaining
 */
addListener(event: string, listener: (...args: any[]) => void): this;

/**
 * Alias for addListener
 */
on(event: string, listener: (...args: any[]) => void): this;

/**
 * Removes an event listener (overrides EventEmitter.removeListener)
 * Automatically disables SQLite configuration when no listeners remain
 * @param event - Event name
 * @param listener - Event listener function to remove
 * @returns Database instance for chaining
 */
removeListener(event: string, listener: (...args: any[]) => void): this;

/**
 * Removes all event listeners (overrides EventEmitter.removeAllListeners)
 * Automatically disables SQLite configuration for supported events
 * @param event - Optional event name (if omitted, removes all listeners)
 * @returns Database instance for chaining
 */
removeAllListeners(event?: string): this;

// Adding listeners automatically enables SQLite events
db.addListener('trace', (sql) => {
    console.log('SQL trace:', sql);
});

// The above is equivalent to:
db.on('trace', (sql) => {
    console.log('SQL trace:', sql);
});

// Removing listeners automatically disables events when no listeners remain
db.removeListener('trace', traceHandler);

// Remove all listeners for an event
db.removeAllListeners('trace');

// Remove all listeners for all events
db.removeAllListeners();