import {Db, Model, linkModels} from '@keleran/orm';

// no options = a new pg.Pool with default options will be created
const db = new Db();
// poolOptions to use instead of the default ones can be supplied 
const db = new Db({poolOptions: {max: 5}});
// an existing pool can be supplied as well
const db = new Db({pool});
// it's best to listen to db.pool's "error" event so that it doesn't crash the app
db.pool.on('error', console.log);
// call db.pool.end() during graceful shutdown
db.pool.end().then(() => process.exit());

// statement as plain string
db.exec('SELECT version()');
// with parameters
db.exec('INSERT INTO users (email) VALUES ($1)', ['test@example.com']);
// statement in the MoSQL format
// column names MUST be in snake_case
// keys of "values", "where", and "order" objects and of their children will be converted to snake_case before executing 
db.exec({type: 'select', table: 'users'});
// an additional argument can be supplied to pass context
// works with all signatures, must be added as the last argument
// "client" is reserved for internal use, others can be supplied to implement custom functionality (see below)

db.on('execFinish', data => {
  if (data.ctx.log === true) {
    // statement, values, result/err, and more 
    console.log(data);
  }
});

db.exec('SELECT version()', {log: true});

// execute a function with multiple queries under a single transaction
const ctx = {log: false};

db.transact(async ctx => {
  // ctx = the initial ctx with "client" property added
  // when "client" is set, db will use it instead of creating a new one
  // all execs within a transaction MUST be supplied with ctx for the transaction to work properly
  await db.exec('INSERT INTO users (email) VALUES ($1)', ['test@example.com'], ctx);
  await db.exec('INSERT INTO users (email) VALUES ($1)', ['test@example.com'], ctx);
  // will be rolled back and no rows will be added if email is UNIQUE  
}, ctx);

const db = new Db();

class User extends Model {
  // Db instance that stores model records MUST be defined
  static db = db;

  // The name of the table where model records are stored MUST be defined
  static table = 'users';
 
  // The function that should be used to generate instance id MUST be defined if DB doesn't assign it
  static generateId = uuid.v4;

  // custom methods MUST accept and pass ctx to DB methods for transactions to work properly  
  static customMethod(params, ctx) {
    this.db.exec('...', ctx);
  }
}

// extenders are functions that can add extra properties to instances
// extension can be performed via .extend, #extend, or by supplying "extend" parameter in query spec
// if multi-level extension is supported by the extender, the hierarchy should be expressed as "parent.child.child"
// extenders MUST NOT be defined as a static property if linkModels utility is used
User.extenders = {
  // can be an async function
  // it MUST adjust supplied instances, it does not matter what it returns 
  randomNumber: instances => instances.forEach(instance => instance.randomNumber = Math.random())
};

// count instance records, returns an integer
User.count({where: {email: {$notNull: true}}});

// find instance records, returns an array of instances
User.find({where: {email: {$notNull: true}}});

// find instance records, returns the first match
User.findOne({where: {email: {$notNull: true}}});

// update instance records
User.update({where: {email: {$notNull: true}}, values: {email: null}});

// delete instance records
User.delete({where: {email: {$notNull: true}}});

// extend instance records
User.extend(users, 'articles');
// an array can be supplied to call multiple extenders
User.extend(users, ['articles', 'comments']);

// create an instance, assigning supplied properties
// if User.generateId is defined, user will have the "id" property set
const user = new User({firstName: 'User'});

// the same as the regular constructor, but snake_key property names will be converted to camelCase
// useful when dealing with raw DB results
const user = User.fromRow({first_name: 'User'});

// a convenience method to adjust instance properties, supports chaining
user.set('firstName', 'A').set('lastName', 'B');
// is identical to
user.set({firstName: 'A', lastName: 'B'});

// rejects if there's already a record with the same id
user.insert(ctx);

// rejects if there's no record with the same id
user.update(ctx);

// tries to insert, updates on error
user.upsert(ctx);

// delete instance record from the DB
user.delete();

// extend instance
user.extend('articles');
// an array can be supplied to call multiple extenders
user.extend(['articles', 'comments']);