const sqlite3 = require('sqlite3').verbose()
var db
 
exports.db = db
 
exports.open=function(path) {
 return new Promise(function(resolve) {
 this.db = new sqlite3.Database(path,
  function(err) {
   if(err) reject("Open error: "+ err.message)
   else resolve(path + " opened")
  }
 ) 
 })
}
 
// any query: insert/delete/update
exports.run=function(query) {
 return new Promise(function(resolve, reject) {
  this.db.run(query,
   function(err) {
    if(err) reject(err.message)
    else resolve(true)
  })
 }) 
}
 
// first row read
exports.get=function(query, params) {
 return new Promise(function(resolve, reject) {
  this.db.get(query, params, function(err, row) {
   if(err) reject("Read error: " + err.message)
   else {
    resolve(row)
   }
  })
 })
}
 
// set of rows read
exports.all=function(query, params) {
 return new Promise(function(resolve, reject) {
  if(params == undefined) params=[]
 
  this.db.all(query, params, function(err, rows) {
   if(err) reject("Read error: " + err.message)
   else {
    resolve(rows)
   }
  })
 })
}
 
// each row returned one by one
exports.each=function(query, params, action) {
 return new Promise(function(resolve, reject) {
  var db = this.db
  db.serialize(function() {
   db.each(query, params, function(err, row) {
    if(err) reject("Read error: " + err.message)
    else {
     if(row) {
      action(row)
     } 
    }
   })
   db.get("", function(err, row) {
    resolve(true)
   })   
  })
 })
}
 
exports.close=function() {
 return new Promise(function(resolve, reject) {
  this.db.close()
  resolve(true)
 })
}

const fs = require("fs")
const sqlite = require("aa-sqlite")
 
async function mainApp() {
  
 console.log(await sqlite.open('./users.db'))
  
 // Adds a table
  
 var r = await sqlite.run('CREATE TABLE users(ID integer NOT NULL PRIMARY KEY, name text, city text)')
 if(r) console.log("Table created")
 
 // Fills the table
  
 let users = {
  "Naomi": "chicago",
  "Julia": "Frisco",
  "Amy": "New York",
  "Scarlett": "Austin",
  "Amy": "Seattle"
 }
  
 var id = 1
 for(var x in users) {
  var entry = `'${id}','${x}','${users[x]}'`
  var sql = "INSERT INTO users(ID, name, city) VALUES (" + entry + ")"
  r = await sqlite.run(sql)
  if(r) console.log("Inserted.")
  id++  
 }
 
 // Starting a new cycle to access the data
 
 await sqlite.close();
 await sqlite.open('./users.db')
 
 console.log("Select one user:")
  
 var sql = "SELECT ID, name, city FROM users WHERE name='Naomi'"
 r = await sqlite.get(sql)
 console.log("Read:", r.ID, r.name, r.city)
  
 console.log("Get all users:")
  
 sql = "SELECT * FROM users"
 r = await sqlite.all(sql, [])
 r.forEach(function(row) {
  console.log("Read:", row.ID, row.name, row.city) 
 })
  
 console.log("Get some users:")
  
 sql = "SELECT * FROM users WHERE name="
 r = await sqlite.all(sql, ['Amy'])
 r.forEach(function(row) {
  console.log("Read:", row.ID, row.name, row.city) 
 })
 
 console.log("One by one:")
  
 sql = "SELECT * FROM users"
 r = await sqlite.each(sql, [], function(row) {
  console.log("Read:", row.ID, row.name, row.city) 
 })
 
 if(r) console.log("Done.")
 
 sqlite.close();
}
 
try {
 fs.unlinkSync("./users.db")
}
catch(e) {
}
 
mainApp()