async function run6(connection) {
    const dataFilePaths = [
        '/Users/nitinbansal/Downloads/mix data/generated.csv',
        '/Users/nitinbansal/Downloads/hackernews_10k_dec 2024.csv'
    ];
    const tableNames = ['table_1', 'table_2'];

    let counts = 0;

    for(let i = 0; i < 3; i++) {
        console.log(`iteration: ${i+1}`);

        for(let j = 0; j < dataFilePaths.length; j++) {
            await useConnection(DB_FILE_PATH, async (connection) => {
                await dropTables(connection, true);

                console.log(`Tables dropped. Loading data and getting count...`);

                let result = await loadFile(connection, dataFilePaths[j], tableNames[j], 3);
                let totalRecords = await getRecordCount(connection, tableNames[j]);

                counts = totalRecords;
            });
        }
    }
}

async function initConnection(dbFilePath) {
    const instance = await DuckDBInstance.create(dbFilePath);
    return await instance.connect();
}


async function useConnection(dbFilePath, callback) {
    let connection = null;
    try {
        connection = await initConnection(dbFilePath);
        return await callback(connection); // Return the result of the callback
    } catch (error) {
        console.error("Error while using connection:", error);
        throw error;
    } finally {
        connection = null; // todo: prod: use close() if available
    }
}


async function dropTables(connection, dropAllTables, tablesToDrop) {
    if(dropAllTables) {
        const query = `SELECT table_name FROM information_schema.tables WHERE table_schema = 'main'`;
        const result = await runAndGetResults(connection, query, -1, true);
        tablesToDrop = result.rows.flat();
    }

    if (tablesToDrop.length === 0) {
        console.log('No tables to drop.');
        return;
    }

    console.log(`Going to drop following tables`, tablesToDrop);

    for(const tableName of tablesToDrop) {
        console.log(`dropping table: ${tableName}`);

        try {
            await runAndGetResults(connection, `DROP TABLE IF EXISTS ${tableName}`, -1, false);
            console.log(`dropped table: ${tableName}`);
        } catch (err) {
            console.error(`error dropping table ${tableName}:`, err);
        }
    }
}


async function runAndGetResults(connection, query, maxRowsToFetch = 2048, jsonParseEachRow = false, returnIterator = false) {
    const result = await connection.run(query);
    ...
    ...
    return {
        header: columnNames,
        schema: schema,
        rows: collectedRows
    };
}


async function loadFile(connection, filepath, tableName, sampleRowsToGet = 3) {
    // Runs `CREATE TABLE ${tableName} AS SELECT * FROM read_csv_auto('${filepath}')`
    ...
    ...
    return {
        "table": tableName,
        "filepath": filepath,
        "schema": queryResult.schema,
        "sampleRows": queryResult.rows
    }
}


async function getRecordCount(connection, tableName) {
    const query = `select count(1) as total_count from ${tableName}`;
    const result = await runAndGetResults(connection, query, -1, true, false);

    if(result.rows.length > 0) {
        return result.rows[0][0];
    } else {
        return 0;
    }
}

async function run6(connection) {
    const dataFilePaths = [
        '/Users/nitinbansal/Downloads/mix data/generated.csv',
        '/Users/nitinbansal/Downloads/hackernews_10k_dec 2024.csv'
    ];
    const tableNames = ['table_1', 'table_2'];

    let counts = 0;

    await useConnection(DB_FILE_PATH, async (connection) => {
        await dropTables(connection, true);

        console.log(`Tables dropped. Loading data and getting count...`);

        let result = await loadFile(connection, dataFilePaths[0], tableNames[0], 3);
        result = await loadFile(connection, dataFilePaths[1], tableNames[1], 3);

        for(let k = 0; k < 200; k++) {
            console.log(`iteration: ${k+1}`);

            result = await runAndGetResults(connection, `select * from table_1`, 2048, false, false);
            console.log(`selected row count ${result?.rows?.length}`)

            result = await runAndGetResults(connection, `select * from table_2`, 2048, false, false);
            console.log(`selected row count ${result?.rows?.length}`)
        }
    });
}