var _ = require('lodash');

function DillerDao(tx) {

  var deviceColumns = 'id, key, created_timestamp';
  var propertyColumns = 'id, device, key, created_timestamp';
  var valueColumns = 'property, timestamp, value_text, value_numeric';

  // -------------------------------------------------------------------------------------------------------------------
  // Device
  // -------------------------------------------------------------------------------------------------------------------

  function devices() {
    return tx.manyOrNone("SELECT " + deviceColumns + " FROM device");
  }

  function deviceById(id) {
    return tx.one("SELECT " + deviceColumns + " FROM device WHERE id=$1", id);
  }

  function deviceByKey(key) {
    return tx.oneOrNone("SELECT " + deviceColumns + " FROM device WHERE key=$1", key);
  }

  function insertDevice(key) {
    return tx.one("INSERT INTO device(id, key, created_timestamp) VALUES(DEFAULT, $1, CURRENT_TIMESTAMP) RETURNING " + deviceColumns, key);
  }

  function updateDevice(id, attributes) {

    var values = [id];
    var i = 2;
    var fields = _.map(attributes, function (value, name) {
      console.log('name', name, 'value', value, 'i', i);
      if (name == 'name') {
        values.push(value);
        return 'name = $' + i++;
      }
    });

    if (fields.length == 0) {
      return; // TODO: return an empty promise;
    }

    fields = _.collect(fields);

    var x = 'UPDATE device SET ' + fields.join(', ') + ' WHERE id = $1';
    console.log('x', x);
    console.log('values', values);
    return tx.none(x, values);
  }

  // -------------------------------------------------------------------------------------------------------------------
  // Device Property
  // -------------------------------------------------------------------------------------------------------------------

  function devicePropertyById(id) {
    return tx.one('SELECT ' + propertyColumns + ' FROM device_property WHERE id=$1', [id]);
  }

  function devicePropertyByDeviceIdAndKey(deviceId, key) {
    return tx.oneOrNone('SELECT ' + propertyColumns + ' FROM device_property WHERE device=$1 AND key=$2', [deviceId, key]);
  }

  function devicePropertiesByDeviceId(deviceId) {
    return tx.manyOrNone('SELECT ' + propertyColumns + ' FROM device_property WHERE device=$1', [deviceId]);
  }

  function insertDeviceProperty(deviceId, key) {
    return tx.one('INSERT INTO device_property(id, device, key, created_timestamp) VALUES(DEFAULT, $1, $2, CURRENT_TIMESTAMP) RETURNING ' + propertyColumns, [deviceId, key]);
  }

  function updatePropertyName(id, name) {
    return tx.none('UPDATE device_property SET name=$1 WHERE id=$2', [name, id]);
  }

  function updatePropertyDescription(id, description) {
    return tx.none('UPDATE device_property SET description=$1 WHERE id=$2', description, id);
  }

  // -------------------------------------------------------------------------------------------------------------------
  // Value
  // -------------------------------------------------------------------------------------------------------------------

  function valuesByPropertyId(propertyId, limit) {
    limit = limit || 10;
    return tx.manyOrNone('SELECT timestamp, coalesce(value_numeric::text, value_text) AS value FROM value WHERE property=$1 ORDER BY timestamp DESC LIMIT $2', [propertyId, limit]);
  }

  function insertValue(propertyId, value) {
    var value_numeric = parseFloat(value) || undefined,
      value_text = value_numeric ? null : value;

    return tx.one('INSERT INTO value(property, timestamp, value_text, value_numeric) VALUES($1, CURRENT_TIMESTAMP, $2, $3::NUMERIC) RETURNING timestamp', [propertyId, value_text, value_numeric]);
  }

  function updateHourAggregatesForProperty(propertyId, timestamp) {
    return tx.none('DELETE FROM value_by_hour WHERE property=$1 AND timestamp=DATE_TRUNC(\'hour\', $2::TIMESTAMPTZ)', [propertyId, timestamp])
      .then(function () {
        return tx.oneOrNone('INSERT INTO value_by_hour(property, timestamp, count, max, min, avg) ' +
          'SELECT property, DATE_TRUNC(\'hour\', timestamp) AS timestamp, COUNT(value_numeric) AS count, MAX(value_numeric) AS max, MIN(value_numeric) AS min, AVG(value_numeric) AS avg ' +
          'FROM value WHERE property=$1 AND DATE_TRUNC(\'hour\', timestamp)=DATE_TRUNC(\'hour\', $2::TIMESTAMPTZ) AND value_numeric IS NOT NULL ' +
          'GROUP BY property, DATE_TRUNC(\'hour\', timestamp) ' +
          'RETURNING *;', [propertyId, timestamp]);
      });
  }

  function updateMinuteAggregatesForProperty(propertyId, timestamp) {
    return tx.none('DELETE FROM value_by_minute WHERE property=$1 AND timestamp=DATE_TRUNC(\'minute\', $2::TIMESTAMPTZ)', [propertyId, timestamp])
      .then(function () {
        return tx.oneOrNone('INSERT INTO value_by_minute(property, timestamp, count, max, min, avg) ' +
          'SELECT property, DATE_TRUNC(\'minute\', timestamp) AS timestamp, COUNT(value_numeric) AS count, MAX(value_numeric) AS max, MIN(value_numeric) AS min, AVG(value_numeric) AS avg ' +
          'FROM value ' +
          'WHERE property=$1 AND DATE_TRUNC(\'minute\', timestamp)=DATE_TRUNC(\'minute\', $2::TIMESTAMPTZ) AND value_numeric IS NOT NULL ' +
          'GROUP BY property, DATE_TRUNC(\'minute\', timestamp) ' +
          'RETURNING *;', [propertyId, timestamp]);
      });
  }

  return {
    devices: devices,
    deviceById: deviceById,
    deviceByKey: deviceByKey,
    insertDevice: insertDevice,
    updateDevice: updateDevice,

    devicePropertyById: devicePropertyById,
    devicePropertyByDeviceIdAndKey: devicePropertyByDeviceIdAndKey,
    devicePropertiesByDeviceId: devicePropertiesByDeviceId,
    insertDeviceProperty: insertDeviceProperty,
    updatePropertyName: updatePropertyName,
    updatePropertyDescription: updatePropertyDescription,

    valuesByPropertyId: valuesByPropertyId,
    insertValue: insertValue,
    updateHourAggregatesForProperty: updateHourAggregatesForProperty,
    updateMinuteAggregatesForProperty: updateMinuteAggregatesForProperty
  }
}

module.exports = DillerDao;