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,
    page: offset / pageSize + 1,
    pageCount: Math.round(count / pageSize),
    count: count,
    prevOffset: prevOffset >= 0 ? prevOffset : undefined,
    nextOffset: nextOffset < count ? nextOffset : undefined
  };
}

function connect(res, cb) {
  pg.connect(process.env.DATABASE_URL, function(err, client) {
    if(err) {
      res.send('Unable to connect to PostgreSQL.', {'Content-Type': 'text/plain'}, 500);
    }
    else {
      cb(client);
    }
  });
}

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)
  }
}

function method(handlers) {
  return function(req, res) {
    // I'm just too lazy to include two rows
    handlers.HEAD = handlers.GET;
    var handler = handlers[req.method];
    if(handler) {
      return handler(req, res)
    }
    res.header('Allow', _.keys(handlers));
    return res.send(405);
  }
}

function send_text(res, status, text) {
  res.writeHead(status, {
    'Content-Type': 'text/plain',
    'Content-Length': text.length
  });
  res.write(text);
  res.write('\n');
  res.end();
}

function send_html(req, res, render) {
  if(req.method == 'HEAD') {
    // Can't be bothered to calculate Content-Length even if I
    // should..
    res.writeHead(200, {
      'Content-Type': 'text/html',
    });
    res.end();
  }
  else {
    render();
  }
}

function send_cj(status, req, res, c) {
  var text = JSON.stringify(collection_json.fromObject(c));
  var headers = {
    'Content-Type': 'application/vnd.collection+json',
    'Content-Length': text.length
  };
  res.writeHead(status, headers);
  if(req.method == 'GET') {
    res.write(text);
  }
  res.end();
}

function linksFromPager(req, p, urlgenerator) {
  var links = [];
  if(_.isNumber(p.prevOffset)) {
    links.push({
      href: urlgenerator(_.extend({}, req.query, {offset: p.prevOffset})),
      rel: 'prev',
      prompt: 'Previous page (' + (p.page - 1) + ' of ' + p.pageCount + ')'
    });
  }
  if(_.isNumber(p.nextOffset)) {
    links.push({
      href: urlgenerator(_.extend({}, req.query, {offset: p.nextOffset})),
      rel: 'next',
      prompt: 'Next page (' + (p.page + 1) + ' of ' + p.pageCount + ')'
    });
  }
  return links;
}

function getIndex(req, res) {
  switch(req.accept.types.getBestMatch(['text/html', 'application/vnd.collection+json'])) {
  case 'text/html':
    send_html(req, res, _.bind(res.render, res, 'index', {
      title: 'Employee DB',
      urlgenerator: res.urlgenerator
    }));
    break;
  case 'application/vnd.collection+json':
  case '*/*':
    var c = {collection: {
      links: [ {
        rel: 'departments',
        prompt: 'Department List',
        href: res.urlgenerator.departments()
      }, {
        rel: 'employees',
        prompt: 'Employee List',
        href: res.urlgenerator.employees()
      } ]
    }};
    send_cj(200, req, res, c);
    break;
  default:
    res.send(406);
  }
};

exports.index = method({
  GET: getIndex
});

function getDepartments(req, res) {
  connect(res, function(client) {
    var sql = 'SELECT COUNT(dept_no) AS count FROM 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].count));
      client.query(sql2, [ p.offset, p.limit ], after(res, function(rs2) {
        switch(req.accept.types.getBestMatch(['text/html', 'application/vnd.collection+json'])) {
        case 'text/html':
            send_html(req, res, _.bind(res.render, res, 'departments', {
            title: 'Department List',
            urlgenerator: res.urlgenerator, pager: p, query: req.query,
            departments: rs2.rows
          }));
          break;
        case 'application/vnd.collection+json':
        case '*/*':
          var c = {collection: {
            href: res.urlgenerator.departments(),
            items: mapDepartments(res, rs2.rows),
            links: linksFromPager(req, p, res.urlgenerator.departments),
          }};
          send_cj(200, req, res, c);
          break;
        default:
          res.send(406);
        }
      }));
    }));
  });
};

exports.departments = method({
  GET: getDepartments
});

function getDepartment(req, res) {
  var dept_no = req.params.dept_no;
  switch(req.accept.types.getBestMatch(['text/html', 'application/vnd.collection+json'])) {
  case 'text/html':
    send_html(req, res, _.bind(res.render, res, 'department', {
      title: 'Department ' + dept_no,
      urlgenerator: res.urlgenerator,
      dept_no: dept_no
    }));
    break;
  case 'application/vnd.collection+json':
  case '*/*':
    var c = {collection: {
      href: res.urlgenerator.department(dept_no),
      links: [ {
        rel: 'employees',
        prompt: 'Employees in department ' + dept_no,
        href: res.urlgenerator.employeesInDepartment(dept_no)
      },{
        rel: 'departments',
        prompt: 'All departments',
        href: res.urlgenerator.departments()
      } ]
    }};
    send_cj(200, req, res, c);
    break;
  default:
    res.send(406);
  }
}

exports.department = method({
  GET: getDepartment
});

function getEmployeesInDepartment(req, res) {
  connect(res, function(client) {
    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) {
        switch(req.accept.types.getBestMatch(['text/html', 'application/vnd.collection+json'])) {
        case 'text/html':
          send_html(req, res, _.bind(res.render, res, 'employeesInDepartment', {
            title: 'Department: #' + dept_no,
            urlgenerator: res.urlgenerator, pager: p, query: req.query,
            dept_no: dept_no,
            employees: rs2.rows
          }));
          break;
        case 'application/vnd.collection+json':
        case '*/*':
          var c = {collection: {
            href: res.urlgenerator.employeesInDepartment(dept_no),
            links: [ {
              rel: 'department',
              prompt: 'Department: #' + dept_no,
              href: res.urlgenerator.department(dept_no)
            } ],
            items: mapEmployees(res, rs2.rows)
          }};
          send_cj(200, req, res, c);
          break;
        default:
          res.send(406);
        }
      }));
    }));
  });
};

exports.employeesInDepartment = method({
  GET: getEmployeesInDepartment
});

function getEmployees(req, res) {
  connect(res, function(client) {
    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) {
        switch(req.accept.types.getBestMatch(['text/html', 'application/vnd.collection+json'])) {
        case 'text/html':
          send_html(req, res, _.bind(res.render, res, 'employees', {
            title: 'Employee List',
            urlgenerator: res.urlgenerator, pager: p, query: req.query,
            employees: rs2.rows,
            query: req.query
          }));
          break;
        case 'application/vnd.collection+json':
        case '*/*':
          var c = {collection: {
            href: res.urlgenerator.employees(req.query),
            links: linksFromPager(req, p, res.urlgenerator.employees),
            queries: [{
              href: res.urlgenerator.employees(),
              rel: 'search',
              name: 'employee-search',
              prompt: 'Employee search',
              data: [
                { name: 'name' }
              ]
            }],
            items: mapEmployees(res, rs2.rows)
          }};
          send_cj(200, req, res, c);
          break;
        default:
          res.send(406);
        }
      }));
    }));
  });
};

exports.employees = method({
  GET: getEmployees
});

function getEmployee(req, res) {
  connect(res, function(client) {
    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) {
      switch(req.accept.types.getBestMatch(['text/html', 'application/vnd.collection+json', '*/*'])) {
      case 'text/html':
        if(rs.rowCount > 0) {
          send_html(req, res, _.bind(res.render, res, 'employee', {
            title: 'Employee: #' + emp_no,
            urlgenerator: res.urlgenerator,
            employee: rs.rows[0]
          }));
        }
        else {
          send_text(res, 404, 'No employee with emp_no=' + emp_no + '.');
        }
        break;
      case 'application/vnd.collection+json':
      case '*/*':
        var c;
        if(rs.rowCount > 0) {
          c = {collection: {
            href: res.urlgenerator.employee(emp_no),
            items: mapEmployees(res, rs.rows)
          }};
        }
        else {
          c = {collection: {
            error: {
              title: 'No employee with emp_no=' + emp_no + '.'
            }
          }};
        }
        send_cj(rs.rowCount > 0 ? 200 : 404, req, res, c);
        break;
      default:
        res.send(406);
      }
    }))
  });
}

function deleteEmployee(req, res) {
  connect(res, function(client) {
    var emp_no = parseInt(req.params.emp_no) || 0;
    if(emp_no < 10000 || emp_no > 999999) {
      return send_text(res, 404, 'Illegal emp_no.');
    }
    client.query('DELETE FROM employees WHERE emp_no=$1', [ emp_no ], after(res, function(rs) {
      if(rs.rowCount == 0) {
        return send_text(res, 404, 'No employee with emp_no=' + emp_no + '.');
      }
      res.send(204);
    }));
  });
}

exports.employee = method({
  GET: getEmployee,
  DELETE: deleteEmployee
});