var _ = require('underscore');

function EmployeeDao(con) {
  this.con = con;
  this.getDepartments = getDepartments;
  this.getEmployeesInDepartment = getEmployeesInDepartment;
  this.getEmployeesByName = getEmployeesByName;
  this.getEmployee = getEmployee;
  this.deleteEmployee = deleteEmployee;
  this.insertEmployee = insertEmployee;
  this.updateEmployee = updateEmployee;
}

function getDepartments(pager, cb) {
  var con = this.con;
  var departments_count = {
    text: 'SELECT COUNT(dept_no) AS count FROM departments',
    values: []
  } 

  var departments = {
    text: 'SELECT dept_no, dept_name FROM departments OFFSET $1 LIMIT $2',
    values: [ pager.offset, pager.limit ]
  } 

  con.query(departments_count, function(err, rs1) {
    if(err) return cb(err);
    con.query(departments, function(err, rs2) {
      if(err) return cb(err);
      cb(undefined, rs2.rows, rs1.rows[0].count);
    });
  });
}

function getEmployeesInDepartment(dept_no, pager, cb) {
  var con = this.con;
  var where = 'dept_emp.dept_no=$1 AND dept_emp.to_date > now() AND now() > dept_emp.from_date'
  var employees_in_department_count = {
    text: 'SELECT COUNT(emp_no) AS count FROM dept_emp WHERE ' + where,
    values: [ dept_no ]
  };

  var employees_in_department = {
    text: 'SELECT employees.* FROM employees, dept_emp WHERE employees.emp_no=dept_emp.emp_no AND ' + where + ' OFFSET $2 LIMIT $3',
    values: [ dept_no, pager.offset, pager.limit ]
  };

  con.query(employees_in_department_count, function(err, rs1) {
    if(err) return cb(err);
    con.query(employees_in_department, function(err, rs2) {
      if(err) return cb(err);
      cb(undefined, rs2.rows, rs1.rows[0].count);
    });
  });
}

function getEmployeesByName(query, pager, cb) {
  var con = this.con;
  var where = 'WHERE UPPER(first_name) LIKE UPPER($1) OR UPPER(last_name) LIKE UPPER($1)';
  var employees_by_name_count = {
    text: 'SELECT count(emp_no) AS count FROM employees ' + where,
    values: []
  };
  var employees_by_name = {
    text: 'SELECT * FROM employees ' + where + ' ORDER BY emp_no OFFSET $2 LIMIT $3',
    values: []
  };

  var employee_count = {
    text: 'SELECT COUNT(emp_no) AS count FROM employees',
    values: []
  };
  var employees = {
    text: 'SELECT * FROM employees ORDER BY emp_no OFFSET $1 LIMIT $2',
    values: []
  };

  var q1, q2;
  if(_.isString(query)) {
    if(query.charAt(0) == '*') {
      query = '%' + query.slice(1);
    }
    if(query.charAt(query.length - 1) == '*') {
      query = query.slice(0, -1) + '%';
    }
    employees_by_name_count.values.push(query);
    employees_by_name.values.push(query);
    q1 = employees_by_name_count;
    q2 = employees_by_name;
  } else {
    q1 = employee_count;
    q2 = employees;
  }
  con.query(q1, function(err, rs1) {
    if(err) return cb(err);
    q2.values.push(pager.offset);
    q2.values.push(pager.limit);
    con.query(q2, function(err, rs2) {
      if(err) return cb(err);
      cb(undefined, rs2.rows, rs1.rows[0].count);
    });
  });
}

function getEmployee(emp_no, cb) {
  var employee_by_emp_no = {
    text: 'SELECT * FROM employees WHERE employees.emp_no=$1',
    values: []
  }
  employee_by_emp_no.values.push(emp_no);
  this.con.query(employee_by_emp_no, function(err, rs) {
    if(err) return cb(err);
    cb(undefined, rs.rows[0]);
  });
}

function deleteEmployee(emp_no, cb) {
  var delete_employee_by_emp_no = {
    text: 'DELETE FROM employees WHERE emp_no=$1',
    values: []
  }
  delete_employee_by_emp_no.values.push(emp_no);
  this.con.query(delete_employee_by_emp_no, function(err, rs) {
    if(err) return cb(err);
    cb(undefined, rs.rowCount);
  });
}

function updateEmployee(emp_no, object, cb) {
  var query = [];
  var values = [];
  var i = 1;
  _.each(object, function(value, key) {
    query.push(key + '=$' + i++);
    values.push(value);
  });
  query = 'UPDATE employees SET ' + query.join(', ') + ' WHERE emp_no=$' + i;
  values.push(emp_no);

  /*
  console.log('object', object);
  console.log('query', query);
  console.log('values', values);
  */
  this.con.query(query, values, function(err, rs) {
    if(err) return cb(err);
    cb(undefined, rs.rowCount);
  });
}

function insertEmployee(birth_date, first_name, last_name, gender, hire_date, cb) {
  var con = this.con;
  var insert_employee = {
    text: 'INSERT INTO employees(birth_date, first_name, last_name, gender, hire_date) VALUES($1, $2, $3, $4, $5)',
    values: []
  };
  var employee_currval = {
    text: 'SELECT currval(\'employees_seq\') as emp_no',
    values: []
  };
  insert_employee.values.push(birth_date);
  insert_employee.values.push(first_name);
  insert_employee.values.push(last_name);
  insert_employee.values.push(gender);
  insert_employee.values.push(hire_date);
  con.query('begin', function(err, rs) { if(err) return cb(err);
    con.query(insert_employee, function(err, rs) { if(err) return cb(err);
      con.query(employee_currval, function(err, rs2) { if(err) return cb(err);
        con.query('commit', function(err) { if(err) return cb(err);
          cb(undefined, rs2.rows[0].emp_no);
        });
      });
    });
  });
}

module.exports = EmployeeDao;