const path = require('path');
const fs = require('fs');
const csv_parse = require('csv-parse');
const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:');

function loadDataFile(dataFile, tableName, fileOptions={}) {
  const startTime = new Date();

  // read CSV file
  const csvPath = path.join(__dirname, 'data', dataFile);
  fs.readFile(csvPath, 'utf8', (err, file_data) => {
    if(err)
      console.error(err);

    // parse file data into arrays
    csv_parse(file_data, fileOptions, (err, table) => {
      if(err)
        console.error(err);

      const colNames = table[0];

      db.serialize(function () {

        // create table in database
        db.run(`CREATE TABLE ${tableName} ( ${colNames.map(col => col+' TEXT').join(',') })`);

        // prepare insert statement and run for each row
        const placeholders = colNames.map(() => '?').join(',');
        const stmt = db.prepare(`INSERT INTO ${tableName} VALUES (${placeholders})`);
        for (let row of table.slice(1)) {
          stmt.run(row)
        }

        // print row count and elapsed time
        const elapsed = (new Date() - startTime)/1000;
        db.get(`SELECT COUNT(*) FROM ${tableName}`, (err, res) => {
          stmt.finalize(() => console.log(`Loaded ${res['COUNT(*)']} rows into ${tableName} in ${elapsed} seconds`))
        });

      });

      db.close();
    })

  });

}

loadDataFile('stock_trades.csv', 'STOCK_TRADES');