var collection_json = require('collection_json')
  , pg = require('pg').native
  , util = require('util')
  , _ = require('underscore');

function mapRow(row) {
  var data = _.map(row, function(value, key) {
      return {name: key, value: value};
  });
  return { data: data };
}

function mapDepartments(res, departments) {
  return _.map(departments, function(row) {
    var item = mapRow(row);
    item.href = res.urlgenerator.department(row.dept_no);
    return item;
  });
}

function mapEmployees(res, employees) {
  return _.map(employees, function(row) {
    var item = mapRow(row);
    item.href = res.urlgenerator.employee(row.emp_no);
    return item;
  });
}

function pager(req, count) {
  var pageSize = 5;
  var offset = parseInt(req.query.offset) || 0;
  var limit = parseInt(req.query.limit) || pageSize;
  var prevOffset = offset - pageSize;
  var nextOffset = offset + pageSize;
  return {
    pageSize: pageSize,
    offset: offset,
    limit: limit,
    count: count,
    prevOffset: prevOffset >= 0 ? prevOffset : undefined,
    nextOffset: nextOffset < count ? nextOffset : undefined
  };
}

function after(res, callback) {
  return function(err, queryResult) {
    if(err) {
      res.writeHead(500, {"Content-Type" : "text/plain"});
      return res.end("Error! " + util.inspect(err))
    }
    callback(queryResult)
  }
}

exports.index = function(req, res) {
  if(req.accepts('html')) {
    res.render('index', {
      title: 'Employee DB',
      urlgenerator: res.urlgenerator
    });
  }
  else {
    var c = {collection: {
      links: [ {
        rel: 'departments',
        prompt: 'Department List',
        href: res.urlgenerator.departments()
      }, {
        rel: 'employees',
        prompt: 'Employee List',
        href: res.urlgenerator.employees()
      } ]
    }};
    res.contentType('application/vnd.collection+json');
    res.send(JSON.stringify(collection_json.fromObject(c)), 200);
  }
};

exports.departments = function(req, res) {
  pg.connect(process.env.DATABASE_URL, function(err, client) {
    if(err) throw err;
    var sql = 'SELECT n_live_tup FROM pg_stat_user_tables WHERE relname=\'departments\'';
    var sql2 = 'SELECT dept_no, dept_name FROM departments OFFSET $1 LIMIT $2';
    client.query(sql, after(res, function(rs) {
      var p = pager(req, parseInt(rs.rows[0].n_live_tup));
      client.query(sql2, [ p.offset, p.limit ], after(res, function(rs2) {
        if(req.accepts('html')) {
          res.render('departments', {
            title: 'Department List',
            urlgenerator: res.urlgenerator, pager: p, query: req.query,
            departments: rs2.rows
          });
        }
        else {
          var c = {collection: {
            href: res.urlgenerator.departments(),
            items: mapDepartments(res, departments.rows)
          }};
          res.contentType('application/vnd.collection+json');
          res.send(JSON.stringify(collection_json.fromObject(c)), 200);
        }
      }));
    }));
  });
};

exports.department = function(req, res) {
  var dept_no = req.params.dept_no;
  if(req.accepts('html')) {
    res.render('department', {
      title: 'Department ' + dept_no,
      urlgenerator: res.urlgenerator,
      dept_no: dept_no
    });
  }
  else {
    var c = {collection: {
      href: res.urlgenerator.department(dept_no),
      links: [ {
        rel: 'employees',
        prompt: 'Employees in department ' + dept_no,
        href: res.urlgenerator.employees_in_department(dept_no)
      },{
        rel: 'departments',
        prompt: 'All departments',
        href: res.urlgenerator.departments()
      } ]
    }};
    res.contentType('application/vnd.collection+json');
    res.send(collection_json.fromObject(c));
  }
}

exports.employees_in_department = function(req, res) {
  pg.connect(process.env.DATABASE_URL, function(err, client) {
    if(err) throw err;
    var dept_no = req.params.dept_no;
    // TODO: Add dept_name to view
    // TODO: Add manager as a link
    var where = 'dept_emp.dept_no=$1 AND dept_emp.to_date > now() AND now() > dept_emp.from_date'
    var sql1 = 'SELECT count(emp_no) as count FROM dept_emp WHERE ' + where;
    var sql2 = 'SELECT employees.* FROM employees, dept_emp WHERE employees.emp_no=dept_emp.emp_no AND ' + where + ' OFFSET $2 LIMIT $3';
    client.query(sql1, [ dept_no ], after(res, function(rs1) {
      var p = pager(req, parseInt(rs1.rows[0].count));
      client.query(sql2, [ dept_no, p.offset, p.limit ], after(res, function(rs2) {
        if(req.accepts('html')) {
          res.render('employees_in_department', {
            title: 'Department: #' + dept_no,
            urlgenerator: res.urlgenerator, pager: p, query: req.query,
            dept_no: dept_no,
            employees: rs2.rows
          });
        }
        else {
          var c = {collection: {
            href: res.urlgenerator.employees_in_department(dept_no),
            links: [ {
              rel: 'department',
              prompt: 'Department: #' + dept_no,
              href: res.urlgenerator.department(dept_no)
            } ],
            items: mapEmployees(res, rs2.rows)
          }};
          res.contentType('application/vnd.collection+json');
          res.send(collection_json.fromObject(c));
        }
      }));
    }));
  });
};

exports.employees = function(req, res) {
  pg.connect(process.env.DATABASE_URL, function(err, client) {
    if(err) throw err;
    var emp_no = req.params.emp_no;
    var sql1, sql2;
    var sql1Params = [];
    var sql2Params = [];
    var qname = req.query.name;
    if(_.isString(qname)) {
      if(qname.charAt(0) == '*') {
        qname = '%' + qname.slice(1);
      }
      if(qname.charAt(qname.length - 1) == '*') {
        qname = qname.slice(0, -1) + '%';
      }
      var where = 'WHERE UPPER(first_name) LIKE UPPER($1) OR UPPER(last_name) LIKE UPPER($1)';
      sql1 = 'SELECT count(emp_no) AS count FROM employees ' + where;
      sql1Params.push(qname);
      sql2 = 'SELECT * FROM employees ' + where + ' OFFSET $2 LIMIT $3';
      sql2Params.push(qname);
    }
    else {
      sql1 = 'SELECT n_live_tup AS count FROM pg_stat_user_tables WHERE relname=\'employees\'';
      sql2 = 'SELECT * FROM employees ORDER BY emp_no OFFSET $1 LIMIT $2';
    }
    client.query(sql1, sql1Params, after(res, function(rs1) {
      var p = pager(req, parseInt(rs1.rows[0].count));
      sql2Params.push(p.offset);
      sql2Params.push(p.limit);
      client.query(sql2, sql2Params, after(res, function(rs2) {
        if(req.accepts('html')) {
          res.render('employees', {
            title: 'Employee List',
            urlgenerator: res.urlgenerator, pager: p, query: req.query,
            employees: rs2.rows,
            query: req.query
          });
        }
        else {
          var links = [];
          if(_.isNumber(p.prevOffset)) {
            links.push({
              href: res.urlgenerator.employees(_.extend({}, req.query, {offset: p.prevOffset})),
              rel: 'prev',
              prompt: 'Previous page'
            });
          }
          if(_.isNumber(p.nextOffset)) {
            links.push({
              href: res.urlgenerator.employees(_.extend({}, req.query, {offset: p.nextOffset})),
              rel: 'next',
              prompt: 'Next page'
            });
          }
          var c = {collection: {
            href: res.urlgenerator.employees(req.query),
            items: mapEmployees(res, rs2.rows),
            links: links
          }};
          res.contentType('application/vnd.collection+json');
          res.send(JSON.stringify(collection_json.fromObject(c)), 200);
        }
      }));
    }));
  });
};

exports.employee = function(req, res) {
  pg.connect(process.env.DATABASE_URL, function(err, client) {
    if(err) throw err;
    var emp_no = req.params.emp_no;
    var sql = 'SELECT * FROM employees WHERE employees.emp_no=$1';
    client.query(sql, [ emp_no ], after(res, function(rs) {
      if(req.accepts('html')) {
        res.render('employee', {
          title: 'Employee: #' + emp_no,
          urlgenerator: res.urlgenerator,
          employee: rs.rows[0]
        });
      }
      else {
        var c = {collection: {
          href: res.urlgenerator.employee(emp_no),
          items: [ mapRow(rs.rows[0]) ],
        }};
        res.contentType('application/vnd.collection+json');
        res.send(JSON.stringify(collection_json.fromObject(c)), 200);
      }
    }))
  });
}