/* Copyright 2012-2013, Sebastian Reichel <sre@ring0.de>
 *
 * Permission to use, copy, modify, and/or distribute this software for any
 * purpose with or without fee is hereby granted, provided that the above
 * copyright notice and this permission notice appear in all copies.
 *
 * THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
 * WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
 * MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR
 * ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
 * WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN
 * ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF
 * OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.
 */

public const int day_in_seconds = 24*60*60;

[DBus (name = "io.mainframe.shopsystem.Database")]
public class DataBase : Object {
	private class Statement {
		private Sqlite.Statement stmt;

		public Statement(Sqlite.Database db, string query) {
			int rc = db.prepare_v2(query, -1, out stmt);

			if(rc != Sqlite.OK) {
				error("could not prepare statement: %s", query);
			}
		}

		public void reset() {
			stmt.reset();
		}

		public int step() {
			return stmt.step();
		}

		public int bind_null(int index) {
			return stmt.bind_null(index);
		}

		public int bind_int(int index, int value) {
			return stmt.bind_int(index, value);
		}

		public int bind_text(int index, string value) {
			return stmt.bind_text(index, value);
		}

		public int bind_int64(int index, int64 value) {
			return stmt.bind_int64(index, value);
		}

		public int column_int(int index) {
			return stmt.column_int(index);
		}

		public string column_text(int index) {
			var result = stmt.column_text(index);
			return (result != null) ? result : "";
		}

		public int64 column_int64(int index) {
			return stmt.column_int64(index);
		}
	}

	private Sqlite.Database db;
	private static Gee.HashMap<string,string> queries = new Gee.HashMap<string,string>();
	private static Gee.HashMap<string,Statement> statements = new Gee.HashMap<string,Statement>();
	//private static HashTable<string,string> queries = new HashTable<string,string>(null, null);
	//private static HashTable<string,Statement> statements = new HashTable<string,Statement>(null, null);

	public DataBase(string file) {
		int rc;

		rc = Sqlite.Database.open(file, out db);
		if(rc != Sqlite.OK) {
			error("could not open database!");
		}

		/* setup queries */
		queries["product_name"]      = "SELECT name FROM products WHERE id = ?";
		queries["product_amount"]    = "SELECT amount FROM products WHERE id = ?";
		queries["products"]          = "SELECT id, name, amount FROM products ORDER BY name";
		queries["purchase"]          = "INSERT INTO sales ('user', 'product', 'timestamp') VALUES (?, ?, ?)";
		queries["last_purchase"]     = "SELECT product FROM sales WHERE user = ? ORDER BY timestamp DESC LIMIT 1";
		queries["undo"]              = "DELETE FROM sales WHERE user = ? ORDER BY timestamp DESC LIMIT 1";
		queries["product_create"]    = "INSERT INTO products ('id', 'name', 'amount') VALUES (?, ?, ?)";
		queries["price_create"]      = "INSERT INTO prices ('product', 'valid_from', 'memberprice', 'guestprice') VALUES (?, ?, ?, ?)";
		queries["stock"]             = "INSERT INTO restock ('user', 'product', 'amount', 'price', 'timestamp', 'supplier', 'best_before_date') VALUES (?, ?, ?, ?, ?, ?, ?)";
		queries["price"]             = "SELECT memberprice, guestprice FROM prices WHERE product = ? AND valid_from <= ? ORDER BY valid_from DESC LIMIT 1";
		queries["prices"]            = "SELECT valid_from, memberprice, guestprice FROM prices WHERE product = ? ORDER BY valid_from ASC;";
		queries["restocks"]          = "SELECT timestamp, amount, price, supplier, best_before_date FROM restock WHERE product = ? ORDER BY timestamp ASC;";
		queries["profit_complex"]    = "SELECT SUM(memberprice - (SELECT price FROM purchaseprices WHERE product = purch.product)) FROM sales purch, prices WHERE purch.product = prices.product AND purch.user > 0 AND purch.timestamp > ? AND purch.timestamp < ? AND prices.valid_from = (SELECT valid_from FROM prices WHERE product = purch.product AND valid_from < purch.timestamp ORDER BY valid_from DESC LIMIT 1);";
		queries["sales_complex"]     = "SELECT SUM(memberprice) FROM sales purch, prices WHERE purch.product = prices.product AND purch.user > 0 AND purch.timestamp > ? AND purch.timestamp < ? AND prices.valid_from = (SELECT valid_from FROM prices WHERE product = purch.product AND valid_from < purch.timestamp ORDER BY valid_from DESC LIMIT 1);";
		queries["stock_status"]      = "SELECT id, name, amount, memberprice, guestprice FROM products, prices WHERE products.id = prices.product AND prices.valid_from = (SELECT valid_from FROM prices WHERE product = products.id ORDER BY valid_from DESC LIMIT 1) ORDER BY name";
		queries["stock_amount"]      = "SELECT timestamp, amount FROM restock WHERE product = ? UNION ALL SELECT timestamp, -1 AS amount FROM sales WHERE product = ? ORDER BY timestamp DESC";
		queries["session_set"]       = "UPDATE authentication SET session=? WHERE user = ?";
		queries["session_get"]       = "SELECT user FROM authentication WHERE session = ?";
		queries["username"]          = "SELECT firstname, lastname FROM users WHERE id = ?";
		queries["password_get"]      = "SELECT password FROM authentication WHERE user = ?";
		queries["password_set"]      = "UPDATE authentication SET password=? WHERE user = ?";
		queries["userinfo"]          = "SELECT firstname, lastname, email, gender, street, plz, city, pgp FROM users WHERE id = ?";
		queries["userauth"]          = "SELECT disabled, superuser FROM authentication WHERE user = ?";
		queries["profit_by_product"] = "SELECT name, SUM(memberprice - (SELECT price FROM purchaseprices WHERE product = purch.product)) AS price FROM sales purch, prices, products WHERE purch.product = products.id AND purch.product = prices.product AND purch.user > 0 AND purch.timestamp > ? AND purch.timestamp < ? AND prices.valid_from = (SELECT valid_from FROM prices WHERE product = purch.product AND valid_from < purch.timestamp ORDER BY valid_from DESC LIMIT 1) GROUP BY name ORDER BY price;";
		queries["invoice"]           = "SELECT timestamp, productid, productname, price FROM invoice WHERE user = ? AND timestamp >= ? AND timestamp < ?;";
		queries["purchase_first"]    = "SELECT timestamp FROM sales WHERE user = ? ORDER BY timestamp ASC  LIMIT 1";
		queries["purchase_last"]     = "SELECT timestamp FROM sales WHERE user = ? ORDER BY timestamp DESC LIMIT 1";
		queries["count_articles"]    = "SELECT COUNT(*) FROM products";
		queries["count_users"]       = "SELECT COUNT(*) FROM users";
		queries["stock_value"]       = "SELECT SUM(amount * price) FROM products INNER JOIN purchaseprices ON products.id = purchaseprices.product";
		queries["total_sales"]       = "SELECT SUM(price) FROM invoice WHERE user >= 0 AND timestamp >= ?";
		queries["total_profit"]      = "SELECT SUM(price - (SELECT price FROM purchaseprices WHERE product = productid)) FROM invoice WHERE user >= 0 AND timestamp >= ?";
		queries["user_get_ids"]      = "SELECT id FROM users WHERE id > 0";
		queries["user_replace"]      = "INSERT OR REPLACE INTO users ('id', 'email', 'firstname', 'lastname', 'gender', 'street', 'plz', 'city', 'pgp') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
		queries["user_auth_create"]  = "INSERT OR IGNORE INTO authentication (user) VALUES (?)";
		queries["user_disable"]      = "UPDATE authentication SET disabled = ? WHERE user = ?";
		queries["last_timestamp"]    = "SELECT timestamp FROM sales ORDER BY timestamp DESC LIMIT 1";
		queries["supplier_list"]     = "SELECT id, name, postal_code, city, street, phone, website FROM supplier";
		queries["supplier_get"]      = "SELECT id, name, postal_code, city, street, phone, website FROM supplier WHERE id = ?";
		queries["supplier_add"]      = "INSERT INTO supplier('name', 'postal_code', 'city', 'street', 'phone', 'website') VALUES (?, ?, ?, ?, ?, ?)";

		/* compile queries into statements */
		foreach(var entry in queries.entries) {
			statements[entry.key] = new Statement(db, entry.value);
		}
#if 0
		foreach(var key in queries.get_keys()) {
			statements[key] = new Statement(db, queries[key]);
		}
#endif
	}

	public GLib.HashTable<string,string> get_products() {
		var result = new GLib.HashTable<string,string>(null, null);
		statements["products"].reset();

		while(statements["products"].step() == Sqlite.ROW)
			result[statements["products"].column_text(0)] = statements["products"].column_text(1);

		return result;
	}

#if 0
	public stock get_stats_stock() {
		var result = new stock();
		var now = time_t();

		/* init products */
		statements["products"].reset();
		while(statements["products"].step() == Sqlite.ROW) {
			var id = uint64.parse(statements["products"].column_text(0));
			var name = statements["products"].column_text(1);
			int amount = int.parse(statements["products"].column_text(2));
			var product = new stock.product(id, name);
			result.add(product);
			product.add(now, amount);

			statements["stock_amount"].reset();
			statements["stock_amount"].bind_text(1, "%llu".printf(id));
			statements["stock_amount"].bind_text(2, "%llu".printf(id));

			while(statements["stock_amount"].step() == Sqlite.ROW) {
				var timestamp = uint64.parse(statements["stock_amount"].column_text(0));
				var diff = statements["stock_amount"].column_int(1);
				product.add(timestamp+1, amount);
				amount -= diff;
				product.add(timestamp, amount);
			}
		}

		return result;
	}
#endif

#if 0
	public profit_per_product get_stats_profit_per_products() {
		var result = new profit_per_product();

		statements["profit_by_product"].reset();
		statements["profit_by_product"].bind_int(1, 0);
		statements["profit_by_product"].bind_text(2, "99999999999999");

		while(statements["profit_by_product"].step() == Sqlite.ROW) {
			var name = statements["profit_by_product"].column_text(0);
			var profit = statements["profit_by_product"].column_int(1);
			result.add(name, profit);
		}

		return result;
	}
#endif

#if 0
	public profit_per_weekday get_stats_profit_per_weekday() {
		var result = new profit_per_weekday();

		var now = new DateTime.now_utc();
		var today = new DateTime.utc(now.get_year(), now.get_month(), now.get_day_of_month(), 8, 0, 0);
		var tomorrow = today.add_days(1);
		var weekday = tomorrow.get_day_of_week()-1;

		var to   = tomorrow.to_unix();
		var from = to - day_in_seconds;

		var weeks = 8;

		for(int i=0; i<weeks*7; i++) {
			statements["profit_complex"].reset();
			statements["profit_complex"].bind_text(1, "%llu".printf(from));
			statements["profit_complex"].bind_text(2, "%llu".printf(to));

			if(statements["profit_complex"].step() == Sqlite.ROW)
				result.day[weekday] += statements["profit_complex"].column_int(0);

			from-=day_in_seconds;
			to-=day_in_seconds;
			weekday = (weekday + 1) % 7;
		}

		for(int i=0; i<7; i++)
			result.day[i] /= weeks;

		return result;
	}
#endif

#if 0
	public profit_per_day get_stats_profit_per_day() {
		var result = new profit_per_day();
		var to   = time_t();
		var from = to - day_in_seconds;

		/* 8 weeks */
		for(int i=0; i<8*7; i++) {
			statements["profit_complex"].reset();
			statements["profit_complex"].bind_text(1, "%llu".printf(from));
			statements["profit_complex"].bind_text(2, "%llu".printf(to));
			statements["sales_complex"].reset();
			statements["sales_complex"].bind_text(1, "%llu".printf(from));
			statements["sales_complex"].bind_text(2, "%llu".printf(to));


			if(statements["profit_complex"].step() == Sqlite.ROW)
				result.add_profit(from, statements["profit_complex"].column_int(0));
			if(statements["sales_complex"].step() == Sqlite.ROW)
				result.add_sales(from, statements["sales_complex"].column_int(0));

			from-=day_in_seconds;
			to-=day_in_seconds;
		}

		return result;
	}
#endif

	public StockEntry[] get_stock() {
		StockEntry[] result = {};

		statements["stock_status"].reset();
		while(statements["stock_status"].step() == Sqlite.ROW) {
			StockEntry entry = {
				statements["stock_status"].column_text(0),
				statements["stock_status"].column_text(1),
				statements["stock_status"].column_int(2),
				statements["stock_status"].column_int(3),
				statements["stock_status"].column_int(4)
			};

			result += entry;
		}

		return result;
	}

	public PriceEntry[] get_prices(uint64 product) {
		PriceEntry[] result = {};

		statements["prices"].reset();
		statements["prices"].bind_text(1, "%llu".printf(product));
		while(statements["prices"].step() == Sqlite.ROW) {
			PriceEntry entry = {
				statements["prices"].column_int64(0),
				statements["prices"].column_int(1),
				statements["prices"].column_int(2)
			};

			result += entry;
		}

		return result;
	}

	public RestockEntry[] get_restocks(uint64 product) {
		RestockEntry[] result = {};

		statements["restocks"].reset();
		statements["restocks"].bind_text(1, "%llu".printf(product));
		while(statements["restocks"].step() == Sqlite.ROW) {
			RestockEntry entry = {
				statements["restocks"].column_int64(0),
				statements["restocks"].column_int(1)
			};

			Price p = statements["restocks"].column_int(2);
			entry.price = @"$p";
			entry.supplier = statements["restocks"].column_int(3);
			entry.best_before_date = statements["restocks"].column_int64(4);

			result += entry;
		}

		return result;
	}

	public bool buy(int32 user, uint64 article) throws DatabaseError {
		int rc = 0;
		int64 timestamp = (new DateTime.now_utc()).to_unix();

		statements["purchase"].reset();
		statements["purchase"].bind_int(1, user);
		statements["purchase"].bind_text(2, "%llu".printf(article));
		statements["purchase"].bind_text(3, "%llu".printf(timestamp));

		rc = statements["purchase"].step();
		if(rc != Sqlite.DONE)
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);

		return true;
	}

	public string get_product_name(uint64 article) throws DatabaseError {
		statements["product_name"].reset();
		statements["product_name"].bind_text(1, "%llu".printf(article));

		int rc = statements["product_name"].step();

		switch(rc) {
			case Sqlite.ROW:
				return statements["product_name"].column_text(0);
			case Sqlite.DONE:
				throw new DatabaseError.PRODUCT_NOT_FOUND("unknown product: %llu", article);
			default:
				throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
		}
	}

	public int get_product_amount(uint64 article) throws DatabaseError {
		statements["product_amount"].reset();
		statements["product_amount"].bind_text(1, "%llu".printf(article));

		int rc = statements["product_amount"].step();

		switch(rc) {
			case Sqlite.ROW:
				return statements["product_amount"].column_int(0);
			case Sqlite.DONE:
				throw new DatabaseError.PRODUCT_NOT_FOUND("unknown product: %llu", article);
			default:
				throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
		}
	}

	public Price get_product_price(int user, uint64 article) throws DatabaseError {
		int64 timestamp = (new DateTime.now_utc()).to_unix();
		bool member = user != 0;

		statements["price"].reset();
		statements["price"].bind_text(1, "%llu".printf(article));
		statements["price"].bind_text(2, "%lld".printf(timestamp));

		int rc = statements["price"].step();

		switch(rc) {
			case Sqlite.ROW:
				if(member)
					return statements["price"].column_int(0);
				else
					return statements["price"].column_int(1);
			case Sqlite.DONE:
				throw new DatabaseError.PRODUCT_NOT_FOUND("unknown product: %llu", article);
			default:
				throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
		}
	}

	public string undo(int32 user) throws DatabaseError {
		uint64 pid = 0;
		int rc = 0;
		string pname;

		statements["last_purchase"].reset();
		statements["last_purchase"].bind_int(1, user);

		rc = statements["last_purchase"].step();
		switch(rc) {
			case Sqlite.ROW:
				pid = uint64.parse(statements["last_purchase"].column_text(0));
				pname = get_product_name(pid);
				write_to_log("Remove purchase of %s", pname);
				break;
			case Sqlite.DONE:
				throw new DatabaseError.PRODUCT_NOT_FOUND("undo not possible without purchases");
			default:
				throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
		}

		statements["undo"].reset();
		statements["undo"].bind_int(1, user);

		rc = statements["undo"].step();
		if(rc != Sqlite.DONE)
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);

		return pname;
	}

	public void restock(int user, uint64 product, uint amount, uint price, int supplier, int64 best_before_date) throws DatabaseError {
		int rc = 0;
		int64 timestamp = (new DateTime.now_utc()).to_unix();

		statements["stock"].reset();
		statements["stock"].bind_int(1, user);
		statements["stock"].bind_text(2, @"$product");
		statements["stock"].bind_text(3, @"$amount");
		statements["stock"].bind_text(4, @"$price");
		statements["stock"].bind_int64(5, timestamp);
		if(supplier > 0)
			statements["stock"].bind_int(6, supplier);
		else
			statements["stock"].bind_null(6);
		if(best_before_date > 0)
			statements["stock"].bind_int64(7, best_before_date);
		else
			statements["stock"].bind_null(7);

		rc = statements["stock"].step();

		if(rc != Sqlite.DONE)
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
	}

	public void new_product(uint64 id, string name, int memberprice, int guestprice) throws DatabaseError {
		statements["product_create"].reset();
		statements["product_create"].bind_text(1, @"$id");
		statements["product_create"].bind_text(2, name);
		statements["product_create"].bind_int(3, 0);
		int rc = statements["product_create"].step();

		if(rc != Sqlite.DONE) {
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
		}

		new_price(id, 0, memberprice, guestprice);
	}

	public void new_price(uint64 product, int64 timestamp, int memberprice, int guestprice) throws DatabaseError {
		statements["price_create"].reset();
		statements["price_create"].bind_text(1, @"$product");
		statements["price_create"].bind_int64(2, timestamp);
		statements["price_create"].bind_int(3, memberprice);
		statements["price_create"].bind_int(4, guestprice);
		int rc = statements["price_create"].step();

		if(rc != Sqlite.DONE) {
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
		}
	}

	public bool check_user_password(int32 user, string password) {
		statements["password_get"].reset();
		statements["password_get"].bind_int(1, user);

		if(statements["password_get"].step() == Sqlite.ROW) {
			var pwhash_db = statements["password_get"].column_text(0);
			var pwhash_user = Checksum.compute_for_string(ChecksumType.SHA256, password);

			return pwhash_db == pwhash_user;
		} else {
			return false;
		}
	}

	public void set_user_password(int32 user, string password) throws DatabaseError {
		var pwhash = Checksum.compute_for_string(ChecksumType.SHA256, password);
		int rc;

		/* create user auth line if not existing */
		statements["user_auth_create"].reset();
		statements["user_auth_create"].bind_int(1, user);
		rc = statements["user_auth_create"].step();
		if(rc != Sqlite.DONE)
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);

		/* set password */
		statements["password_set"].reset();
		statements["password_set"].bind_text(1, pwhash);
		statements["password_set"].bind_int(2, user);
		rc = statements["password_set"].step();
		if(rc != Sqlite.DONE)
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
	}

	public void set_sessionid(int user, string sessionid) throws DatabaseError {
		statements["session_set"].reset();
		statements["session_set"].bind_text(1, sessionid);
		statements["session_set"].bind_int(2, user);

		int rc = statements["session_set"].step();
		if(rc != Sqlite.DONE)
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
	}

	public int get_user_by_sessionid(string sessionid) throws DatabaseError {
		statements["session_get"].reset();
		statements["session_get"].bind_text(1, sessionid);

		if(statements["session_get"].step() == Sqlite.ROW) {
			return statements["session_get"].column_int(0);
		} else {
			throw new DatabaseError.SESSION_NOT_FOUND("No such session available in database!");
		}
	}

	public UserInfo get_user_info(int user) throws DatabaseError {
		var result = UserInfo();
		statements["userinfo"].reset();
		statements["userinfo"].bind_int(1, user);
		int rc = statements["userinfo"].step();

		if(rc == Sqlite.ROW) {
			result.id = user;
			result.firstname = statements["userinfo"].column_text(0);
			result.lastname  = statements["userinfo"].column_text(1);
			result.email     = statements["userinfo"].column_text(2);
			result.gender    = statements["userinfo"].column_text(3);
			result.street    = statements["userinfo"].column_text(4);
			result.postcode  = statements["userinfo"].column_int(5);
			result.city      = statements["userinfo"].column_text(6);
			result.pgp       = statements["userinfo"].column_text(7);
		} else if(rc == Sqlite.DONE) {
			throw new DatabaseError.USER_NOT_FOUND("user not found");
		} else {
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
		}

		return result;
	}

	public UserAuth get_user_auth(int user) throws DatabaseError {
		var result = UserAuth();
		result.id = user;
		result.disabled = false;
		result.superuser = false;

		statements["userauth"].reset();
		statements["userauth"].bind_int(1, user);
		int rc = statements["userauth"].step();

		if(rc == Sqlite.ROW) {
			result.disabled  = statements["userauth"].column_int(0) == 1;
			result.superuser = statements["userauth"].column_int(1) == 1;
		} else if(rc == Sqlite.DONE) {
			/* entry not found, we return defaults */
		} else {
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
		}

		return result;
	}

	public string get_username(int user) throws DatabaseError {
		statements["username"].reset();
		statements["username"].bind_int(1, user);

		if(statements["username"].step() == Sqlite.ROW) {
			return statements["username"].column_text(0)+" "+statements["username"].column_text(1);
		} else {
			throw new DatabaseError.USER_NOT_FOUND("No such user available in database!");
		}
	}

	public InvoiceEntry[] get_invoice(int user, int64 from=0, int64 to=-1) throws DatabaseError {
		InvoiceEntry[] result = {};

		if(to == -1) {
			to = time_t();
		}

		statements["invoice"].reset();
		statements["invoice"].bind_int(1, user);
		statements["invoice"].bind_int64(2, from);
		statements["invoice"].bind_int64(3, to);
		int rc = statements["invoice"].step();

		while(rc == Sqlite.ROW) {
			InvoiceEntry entry = {};
			entry.timestamp = statements["invoice"].column_int64(0);
			entry.product.ean = uint64.parse(statements["invoice"].column_text(1));
			entry.product.name = statements["invoice"].column_text(2);
			entry.price = statements["invoice"].column_int(3);
			result += entry;

			rc = statements["invoice"].step();
		}

		if(rc != Sqlite.DONE) {
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
		}

		return result;
	}

	public int64 get_first_purchase(int user) {
		statements["purchase_first"].reset();
		statements["purchase_first"].bind_int(1, user);

		if(statements["purchase_first"].step() == Sqlite.ROW)
			return statements["purchase_first"].column_int64(0);
		else
			return 0;
	}

	public int64 get_last_purchase(int user) {
		statements["purchase_last"].reset();
		statements["purchase_last"].bind_int(1, user);

		if(statements["purchase_last"].step() == Sqlite.ROW)
			return statements["purchase_last"].column_int64(0);
		else
			return 0;
	}

	public StatsInfo get_stats_info() {
		var result = StatsInfo();

		DateTime now = new DateTime.now_local();
		DateTime today = new DateTime.local(now.get_year(), now.get_month(), now.get_hour() < 8 ? now.get_day_of_month()-1 : now.get_day_of_month(), 8, 0, 0);
		DateTime month = new DateTime.local(now.get_year(), now.get_month(), 1, 0, 0, 0);

		DateTime last4weeks = now.add_days(-28);
		DateTime last4months = now.add_months(-4);

		statements["count_articles"].reset();
		if(statements["count_articles"].step() == Sqlite.ROW)
			result.count_articles = statements["count_articles"].column_int(0);

		statements["count_users"].reset();
		if(statements["count_users"].step() == Sqlite.ROW)
			result.count_users = statements["count_users"].column_int(0);

		statements["stock_value"].reset();
		if(statements["stock_value"].step() == Sqlite.ROW)
			result.stock_value = statements["stock_value"].column_int(0);

		statements["total_sales"].reset();
		statements["total_sales"].bind_int64(1, 0);
		if(statements["total_sales"].step() == Sqlite.ROW)
			result.sales_total = statements["total_sales"].column_int(0);

		statements["total_profit"].reset();
		statements["total_profit"].bind_int64(1, 0);
		if(statements["total_profit"].step() == Sqlite.ROW)
			result.profit_total = statements["total_profit"].column_int(0);

		statements["total_sales"].reset();
		statements["total_sales"].bind_int64(1, today.to_unix());
		if(statements["total_sales"].step() == Sqlite.ROW)
			result.sales_today = statements["total_sales"].column_int(0);

		statements["total_profit"].reset();
		statements["total_profit"].bind_int64(1, today.to_unix());
		if(statements["total_profit"].step() == Sqlite.ROW)
			result.profit_today = statements["total_profit"].column_int(0);

		statements["total_sales"].reset();
		statements["total_sales"].bind_int64(1, month.to_unix());
		if(statements["total_sales"].step() == Sqlite.ROW)
			result.sales_this_month = statements["total_sales"].column_int(0);

		statements["total_profit"].reset();
		statements["total_profit"].bind_int64(1, month.to_unix());
		if(statements["total_profit"].step() == Sqlite.ROW)
			result.profit_this_month = statements["total_profit"].column_int(0);

		statements["total_sales"].reset();
		statements["total_sales"].bind_int64(1, last4weeks.to_unix());
		if(statements["total_sales"].step() == Sqlite.ROW)
			result.sales_per_day = statements["total_sales"].column_int(0) / 28;

		statements["total_profit"].reset();
		statements["total_profit"].bind_int64(1, last4weeks.to_unix());
		if(statements["total_profit"].step() == Sqlite.ROW)
			result.profit_per_day = statements["total_profit"].column_int(0) / 28;

		statements["total_sales"].reset();
		statements["total_sales"].bind_int64(1, last4months.to_unix());
		if(statements["total_sales"].step() == Sqlite.ROW)
			result.sales_per_month = statements["total_sales"].column_int(0) / 4;

		statements["total_profit"].reset();
		statements["total_profit"].bind_int64(1, last4months.to_unix());
		if(statements["total_profit"].step() == Sqlite.ROW)
			result.profit_per_month = statements["total_profit"].column_int(0) / 4;

		return result;
	}

	public int[] get_member_ids() {
		int[] result = {};

		statements["user_get_ids"].reset();
		while(statements["user_get_ids"].step() == Sqlite.ROW)
			result += statements["user_get_ids"].column_int(0);

		return result;
	}

	public void user_disable(int user, bool value) throws DatabaseError {
		int rc;

		/* create user auth line if not existing */
		statements["user_auth_create"].reset();
		statements["user_auth_create"].bind_int(1, user);
		rc = statements["user_auth_create"].step();
		if(rc != Sqlite.DONE)
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);

		/* set disabled flag */
		statements["user_disable"].reset();
		statements["user_disable"].bind_int(1, value ? 1 : 0);
		statements["user_disable"].bind_int(2, user);
		rc = statements["user_disable"].step();
		if(rc != Sqlite.DONE)
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
	}

	public void user_replace(UserInfo u) throws DatabaseError {
		statements["user_replace"].reset();
		statements["user_replace"].bind_int(1, u.id);
		statements["user_replace"].bind_text(2, u.email);
		statements["user_replace"].bind_text(3, u.firstname);
		statements["user_replace"].bind_text(4, u.lastname);
		statements["user_replace"].bind_text(5, u.gender);
		statements["user_replace"].bind_text(6, u.street);
		statements["user_replace"].bind_int(7, u.postcode);
		statements["user_replace"].bind_text(8, u.city);
		statements["user_replace"].bind_text(9, u.pgp);

		int rc = statements["user_replace"].step();
		if(rc != Sqlite.DONE)
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
	}

	public bool user_is_disabled(int user) throws DatabaseError {
		return get_user_auth(user).disabled;
	}

	public bool user_exists(int user) throws DatabaseError {
		if(user in get_member_ids())
			return true;
		return false;
	}

	public bool user_equals(UserInfo u) throws DatabaseError {
		var dbu = get_user_info(u.id);
		return u.equals(dbu);
	}

	public int64 get_timestamp_of_last_purchase() {
		statements["last_timestamp"].reset();
		if(statements["last_timestamp"].step() != Sqlite.ROW)
			return 0;
		return statements["last_timestamp"].column_int64(0);
	}

	public Supplier[] get_supplier_list() {
		Supplier[] result = {};

		statements["supplier_list"].reset();
		while(statements["supplier_list"].step() == Sqlite.ROW) {
			Supplier entry = {
				statements["supplier_list"].column_int64(0),
				statements["supplier_list"].column_text(1),
				statements["supplier_list"].column_text(2),
				statements["supplier_list"].column_text(3),
				statements["supplier_list"].column_text(4),
				statements["supplier_list"].column_text(5),
				statements["supplier_list"].column_text(6)
			};

			result += entry;
		}

		return result;
	}

	public Supplier get_supplier(int id) {
		Supplier result = Supplier();

		statements["supplier_get"].reset();
		statements["supplier_get"].bind_int(1, id);

		if(statements["supplier_get"].step() != Sqlite.ROW) {
			result.id = 0;
			result.name = "Unknown";
			result.postal_code = "";
			result.city = "";
			result.street = "";
			result.phone = "";
			result.website = "";
		} else {
			result.id = statements["supplier_get"].column_int64(0);
			result.name = statements["supplier_get"].column_text(1);
			result.postal_code = statements["supplier_get"].column_text(2);
			result.city = statements["supplier_get"].column_text(3);
			result.street = statements["supplier_get"].column_text(4);
			result.phone = statements["supplier_get"].column_text(5);
			result.website = statements["supplier_get"].column_text(6);
		}

		return result;
	}

	public void add_supplier(string name, string postal_code, string city, string street, string phone, string website) throws DatabaseError {
		statements["supplier_add"].reset();
		statements["supplier_add"].bind_text(1, name);
		statements["supplier_add"].bind_text(2, postal_code);
		statements["supplier_add"].bind_text(3, city);
		statements["supplier_add"].bind_text(4, street);
		statements["supplier_add"].bind_text(5, phone);
		statements["supplier_add"].bind_text(6, website);
		int rc = statements["supplier_add"].step();

		if(rc != Sqlite.DONE) {
			throw new DatabaseError.INTERNAL_ERROR("internal error: %d", rc);
		}
	}
}