import { nSQL } from "nano-sql";

var nSQL = require("nano-sql").nSQL;
var crypto =  require("crypto") // needed for strong UUIDs, otherwise Math.random is used.

// Import the two below if you plan to use the level db.
var fs = require("fs"); 
var levelup = require("levelup");

nSQL('users') //  "users" is our table name.
.model([ // Declare data model
    {key:'id',type:'int',props:['pk','ai']}, // pk == primary key, ai == auto incriment
    {key:'name',type:'string'}
])
.connect() // Init the data store for usage.
.then(function(result, db) {
    // "db" holds the current NanoSQL var with the previous table still selected.
    return db.query('upsert',{ // Add a record
        name:"Billy",
    }).exec();
})
.then(function(result, db) {
    return db.query('select').exec(); // select all rows from the current active table
})
.then(function(result, db) {
    console.log(result) // <= [{id:1,name:"Billy"}]
})

nSQL('users')// Table/Store Name, required to declare model and attach it to this store.
.model([ // Data Model, required
    {key:'id',type:'uuid',props:['pk']}, // This has the primary key value
    {key:'name',type:'string', default:"None"}, // This will cause inserts to always use "None" if no value is provided.
    {key:'age',type:'int'},
    {key:'balance',type:'float', default: 0},
    {key:'postIDs',type:'array'},
    {key:'meta',type:'map'}
])
.config({persistent:true}) // With this enabled, the best storage engine will be auttomatically selected and all changes saved to it.  Works in browser AND nodeJS automatically.
.actions([ // Optional
    {
        name:'add_new_user',
        args:['user:map'],
        call:function(args, db) {
            return db.query('upsert',args.user).exec();
        }
    }
])
.views([ // Optional
    {
        name: 'get_user_by_name',
        args: ['name:string'],
        call: function(args, db) {
            return db.query('select').where(['name','=',args.name]).exec();
        }
    },
    {
        name: 'list_all_users',
        args: ['page:int'],
        call: function(args, db) {
            return db.query('select',['id','name']).exec();
        }
    }                       
])

// Initializes the db.
nSQL().connect().then(function(result, db) {
    // DB ready to use.
    db.doAction('add_new_user',{user:{
        id:null,
        name:'jim',
        age:30,
        balance:25.02,
        postIDs:[0,20,5],
        meta:{
            favorteColor:'blue'
        }
    }}).then(function(result, db) {
        console.log(result) //  <- "1 Row(s) upserted"
        return db.getView('list_all_users');
    }).then(function(result, db) {
        console.log(result) //  <- single object array containing the row we inserted.
    });
});

// Roll the entire database back one delete/drop/upsert query
nSQL().extend("<") 
// Yep, that's it.

// Listen for changes on the users table
nSQL("users").on("change", function(dbEvent) { ... });

// Listen for changes on any table
nSQL("*").on("change", function(dbEvent) { ... });

// Get all rows, only provide the "name" column.
nSQL("users").query("select",["name"]).exec().then(function(rows, db) {...});

// Select all users with the name "John".
nSQL("users").query("select").where(["name","=","John"]).exec().then(function(rows, db) {...});

// Compound where statements, supports AND & OR
nSQL("users").query("select").where([["name","=","John"],"AND",["age",">",25]]).exec().then(function(rows, db) {...});

// Order results by name ascending, then age descending.
nSQL("users").query("select").orderBy({name:"asc",age:"desc"}).exec().then(function(rows, db) {...});

// Limit and offset are easy to use as well
nSQL("users").query("select").limit(10).offset(100).exec().then(function(rows, db) {...});

// AS and aggregate functions also work.
nSQL("users").query("select",["COUNT(*) AS totalUsers"]).exec().then(function(rows, db) {...});

// Mix and match as you like
nSQL("users")
.query("select",["id", "name AS username", "age"])
.where([["name","=","John"],"AND",["age",">",25]]) // Where statements can't use AS aliases
.orderBy({username:"desc",age:"asc"}) // But order by does!
.exec().then(function(rows, db) {})

// Relatively simple join
nSQL("users")
.query("select",["orders.id", "users.name","orders.total"])
.where(["users.balance",">",500])
.join({
    type:"left", // Supported join types are left, inner, right, cross and outer.
    table: "orders",
    where: ["orders.userID","=","users.id"] // Compound WHERE statements with AND/OR don't work, just single ones do.
}).exec().then(function(rows, db) {...})

// Group By also works
nSQL("users")
.query("select",["favoriteColor", "eyeColor", "COUNT(*) AS users"])
.groupBy({favoriteColor:"asc", eyeColor:"desc"}) // Multiple group bys aren't a problem!
.having(["users" ,">", 2]) // Having uses the same syntax as WHERE, but runs after the GROUP BY command.
.orderBy({users:"desc"})
.exec().then(function(rows, db) {...})

// Look mah, I used every feature!
nSQL("users")
.query("select",["orders.userID AS ID", "users.name AS Customer", "COUNT(*) AS Orders", "SUM(orders.total) AS Total"])
.join({
    type:"left", 
    table: "orders",
    where: ["orders.userID","=","users.id"] 
})
.where([["users.balance", ">", 100], "OR",["users.age", ">", 45]])
.groupBy({"orders.userID":"asc"})
.having(["Total", ">", 100])
.orderBy({Total:"desc"})
.limit(20)
.exec().then(function(rows, db) {...})