import sqlite3 from 'sqlite3'
import { open } from 'sqlite'

// this is a top-level await 
(async () => {
    // open the database
    const db = await open({
      filename: '/tmp/database.db',
      driver: sqlite3.Database
    })
})()

import sqlite3 from 'sqlite3'
import { open } from 'sqlite'

open({
  filename: '/tmp/database.db',
  driver: sqlite3.Database
}).then((db) => {
  // do your thing
})

import sqlite3 from 'sqlite3'
import { open } from 'sqlite'

// you would have to import / invoke this in another file
export async function openDb () {
  return open({
    filename: '/tmp/database.db',
    driver: sqlite3.Database
  })
}

import sqlite3 from 'sqlite3'
import { open } from 'sqlite'

(async () => {
    const db = await open({
      filename: '/tmp/database.db',
      driver: sqlite3.cached.Database
    })
})()

import sqlite3 from 'sqlite3'

sqlite3.verbose()

db.on('trace', (data) => {
  
})

import sqlite3Offline from 'sqlite3-offline-next'
import { open } from 'sqlite'

(async () => {
    const db = await open({
      filename: '/tmp/database.db',
      driver: sqlite3Offline.Database
    })
})()

import sqlite3 from 'sqlite3'
import { open } from 'sqlite'

(async () => {
  const [db1, db2] = await Promise.all([
    open({
      filename: '/tmp/database.db',
      driver: sqlite3.Database
    }),
    open({
      filename: '/tmp/database2.db',
      driver: sqlite3.Database
    }),
  ])

  await db1.migrate({
    migrationsPath: '...'
  })

  await db2.migrate({
    migrationsPath: '...'
  })
})()

// db is an instance of `sqlite#Database`
// which is a wrapper around `sqlite3#Database`
const db = await open({
  /**
   * Valid values are filenames, ":memory:" for an anonymous in-memory
   * database and an empty string for an anonymous disk-based database.
   * Anonymous databases are not persisted and when closing the database
   * handle, their contents are lost.
   */
  filename: string

  /**
   * One or more of sqlite3.OPEN_READONLY, sqlite3.OPEN_READWRITE and
   * sqlite3.OPEN_CREATE. The default value is OPEN_READWRITE | OPEN_CREATE.
   */
  mode?: number

  /**
   * The database driver. Most will install `sqlite3` and use the `Database` class from it.
   * As long as the library you are using conforms to the `sqlite3` API, you can use it as
   * the driver.
   *
   * @example
   *
   * ```
   * import sqlite from 'sqlite3'
   *
   * const driver = sqlite.Database
   * ```
   */
  driver: any
})

await db.exec('CREATE TABLE tbl (col TEXT)')
await db.exec('INSERT INTO tbl VALUES ("test")')

const result = await db.get('SELECT col FROM tbl WHERE col = ?', 'test')

// { col: 'test' }

const result = await db.get('SELECT col FROM tbl WHERE col = ?', ['test'])

// { col: 'test' }

const result = await db.get('SELECT col FROM tbl WHERE col = :test', {
  ':test': 'test'
})

// { col: 'test' }

const result = await db.all('SELECT col FROM tbl')

// [{ col: 'test' }]

const result = await db.run(
  'INSERT INTO tbl (col) VALUES (?)',
  'foo'
)

/*
{
  // row ID of the inserted row
  lastID: 1,
  // instance of `sqlite#Statement`
  // which is a wrapper around `sqlite3#Statement`
  stmt: <Statement>
}
*/

const result = await db.run('INSERT INTO tbl(col) VALUES (:col)', {
  ':col': 'something'
})

const result = await db.run(
  'UPDATE tbl SET col = ? WHERE col = ?',
  'foo',
  'test'
)

/*
{
  // number of rows changed
  changes: 1,
  // instance of `sqlite#Statement`
  // which is a wrapper around `sqlite3#Statement`
  stmt: <Statement>
}
*/

// stmt is an instance of `sqlite#Statement`
// which is a wrapper around `sqlite3#Statement`
const stmt = await db.prepare('SELECT col FROM tbl WHERE 1 = ? AND 5 = ?5')
await stmt.bind({ 1: 1, 5: 5 })
let result = await stmt.get()
// { col: 'some text' }

const stmt = await db.prepare(
  'SELECT col FROM tbl WHERE 13 = @thirteen ORDER BY col DESC'
)

const result = await stmt.all({ '@thirteen': 13 })

try {
  // You need to wrap this in a try / catch for SQL parse / connection errors
  const rowsCount = await db.each(
    'SELECT col FROM tbl WHERE ROWID = ?',
    [2],
    (err, row) => {
      if (err) {
        // This would be if there is an error specific to the row result
        throw err
      }

      // row = { col: 'other thing' }
    }
  )
} catch (e) {
  throw e
}

// rowsCount = 1

const rawDb = db.getDatabaseInstance()
const rawStatement = stmt.getStatementInstance()

await db.close()

await db.migrate({    
    /**
    * If true, will force the migration API to rollback and re-apply the latest migration over
    * again each time when Node.js app launches.
    */
    force?: boolean
    /**
    * Migrations table name. Default is 'migrations'
    */
    table?: string
    /**
    * Path to the migrations folder. Default is `path.join(process.cwd(), 'migrations')`
    */
    migrationsPath?: string
})

// Assuming you have @types/sqlite3 installed
import sqlite3 from 'sqlite3'

// sqlite3.Database, sqlite3.Statement is the default if no explicit generic is specified
await open<sqlite3.Database, sqlite3.Statement>({
  filename: ':memory'
})

interface Row {
  col: string
}

// result will be of type Row, allowing Typescript supported IDEs to autocomplete on the properties!
const result = await db.get<Row>('SELECT col FROM tbl WHERE col = ?', 'test')

interface Row {
  col: string
}

// Result is an array of rows, you can now have array-autocompletion data
const result = await db.all<Row[]>('SELECT col FROM tbl')

result.each((row) => {
  // row should have type information now!
})