using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Globalization;

namespace JAMM.Kasse
{
    class DB
    {
        #region Fields

        private const string db = "JAMM-Kasse.sqlite";
        private SQLiteConnection dbConnection;
        private SQLiteCommand dbCommand;
        private SQLiteDataReader dbReader;

        #endregion // Fields

        #region Constructors

        public DB() : this(false)
        {
        }

        public DB(bool initialize)
        {
            OpenConnection();
            if (initialize) Initialize();
        }

        #endregion // Constructors

        #region Properties

        public SQLiteCommand DbCommand
        {
            get { return dbCommand; }
        }

        public SQLiteDataReader DbReader
        {
            get { return dbReader; }
            set { dbReader = value; }
        }

        #endregion // Properties

        #region Methods

        /// <summary>
        /// Connects to the database.
        /// </summary>
        private void OpenConnection()
        {
            dbConnection = new SQLiteConnection();
            dbConnection.ConnectionString = "Data Source=" + db;
            dbConnection.Open();
            dbCommand = new SQLiteCommand(dbConnection);
        }

        /// <summary>
        /// Closes the database connection.
        /// </summary>
        public void CloseConnection()
        {
            if (dbConnection != null)
            {
                dbReader.Dispose();
                dbCommand.Dispose();
                dbConnection.Close();
                dbConnection.Dispose();
            }
        }

        /// <summary>
        /// Checks if all expected tables are stored in the database and creates them if they are not.
        /// </summary>
        private void Initialize()
        {
            // Create table "users"
            dbCommand.CommandText =
                @"CREATE TABLE IF NOT EXISTS users (
	                id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	                name TEXT NOT NULL, password TEXT,
	                role INTEGER NOT NULL
                );";
            dbCommand.ExecuteNonQuery();

            // Create table "tables"
            dbCommand.CommandText =
                @"CREATE TABLE IF NOT EXISTS tables (
	                id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	                name TEXT NOT NULL,
	                number INTEGER NOT NULL,
	                note TEXT NOT NULL,
	                active BOOLEAN DEFAULT 1
                );";
            dbCommand.ExecuteNonQuery();

            // Create table "groups"
            dbCommand.CommandText =
                @"CREATE TABLE IF NOT EXISTS groups (
	                id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	                name TEXT NOT NULL,
	                number INTEGER NOT NULL,
	                active BOOLEAN DEFAULT 1
                );";
            dbCommand.ExecuteNonQuery();

            // Create table "products"
            dbCommand.CommandText =
                @"CREATE TABLE IF NOT EXISTS products (
	                id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	                name TEXT NOT NULL,
	                number INTEGER NOT NULL,
	                price REAL NOT NULL,
	                groupId INTEGER NOT NULL,
	                taxId INTEGER NOT NULL,
	                active BOOLEAN DEFAULT 1
                );";
            dbCommand.ExecuteNonQuery();

            // Create table "orders"
            dbCommand.CommandText =
                @"CREATE TABLE IF NOT EXISTS orders (
	                id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	                tableId INTEGER NOT NULL,
	                productId INTEGER NOT NULL,
                    userId INTEGER NOT NULL,
	                timestamp DATETIME NOT NULL
                );";
            dbCommand.ExecuteNonQuery();

            // Create table "ordered"
            dbCommand.CommandText =
                @"CREATE TABLE IF NOT EXISTS ordered (
	                id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	                orderId INTEGER NOT NULL,
	                timestamp DATETIME NOT NULL
                );";
            dbCommand.ExecuteNonQuery();

            // Create table "paid"
            dbCommand.CommandText =
                @"CREATE TABLE IF NOT EXISTS paid (
	                id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	                orderID INTEGER NOT NULL,
	                timestamp DATETIME NOT NULL
                );";
            dbCommand.ExecuteNonQuery();

            // Create table "taxes"
            dbCommand.CommandText =
                @"CREATE TABLE IF NOT EXISTS taxes (
	                id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
	                note TEXT NOT NULL,
	                tax INTEGER NOT NULL
                );";
            dbCommand.ExecuteNonQuery();

            //// insert 19% tax
            dbCommand.CommandText = "SELECT id FROM taxes WHERE id = 1";
            dbReader = dbCommand.ExecuteReader();
            if (!dbReader.HasRows)
            {
                dbReader.Close();
                dbCommand.CommandText = "INSERT INTO taxes (note, tax) VALUES ('19 %', 19)";
                dbCommand.ExecuteNonQuery();
            }
            dbReader.Close();

            // Create default admin user if the users table is empty
            dbCommand.CommandText = "SELECT * FROM users";
            dbReader = dbCommand.ExecuteReader();
            int counter = 0;

            while (dbReader.Read())
            {
                if (dbReader[0] != null) counter++;
            }

            dbReader.Close();

            if (counter == 0)
            {
                dbCommand.CommandText = "INSERT INTO USERS (id, name, password, role) VALUES (NULL, 'admin', 'admin', 2)";
                dbCommand.ExecuteNonQuery();
            }
        }

        #region user methods

        /// <summary>
        /// adds an user to db if name is free
        /// </summary>
        /// <param name="newUser">user object of new user</param>
        /// <returns>true wenn added, false when not</returns>
        public bool addUser(Models.User newUser)
        {
            //wenn Username schon vergeben -> return false
            dbCommand.CommandText = "SELECT name FROM users WHERE name = '" + newUser.UserName + "'";
            dbReader = dbCommand.ExecuteReader();
            if (dbReader.HasRows)
            {
                dbReader.Close();
                return false;
            }
            else
            {
                dbReader.Close();
                dbCommand.CommandText = "INSERT INTO users (name, password, role) VALUES ('" + newUser.UserName + "', '" + newUser.Password + "', '" + newUser.Role + "')";
                dbCommand.ExecuteNonQuery();
                return true;
            }
        }

        /// <summary>
        /// loads users out of db and returns list of the users
        /// </summary>
        /// <returns>list of users</returns>
        public List<Models.User> getUsers()
        {
            List<Models.User> users = new List<Models.User>();
            dbCommand.CommandText = "SELECT id, name, password, role FROM users";
            dbReader = dbCommand.ExecuteReader();
            while (dbReader.Read())
            {
                users.Add(new Models.User(dbReader.GetInt32(0), dbReader.GetInt32(3), dbReader.GetString(1), dbReader.GetString(2)));
            }
            dbReader.Close();

            return users;
        }

        /// <summary>
        /// updates user details
        /// </summary>
        /// <param name="editUser">user object</param>
        /// <returns>true for success</returns>
        public bool updateUser(Models.User editUser)
        {
            //wenn neuer Name schon vergeben -> return false
            dbCommand.CommandText = "SELECT name FROM users WHERE name = " + editUser.UserName;
            dbReader = dbCommand.ExecuteReader();
            if (!dbReader.HasRows)
            {
                dbReader.Close();
                return false;
            }
            dbReader.Close();

            //wenn User mit übergebener Id nicht vorhanden -> return false
            dbCommand.CommandText = "SELECT id FROM users WHERE id = " + editUser.UserId;
            dbReader = dbCommand.ExecuteReader();
            if (!dbReader.HasRows)
            {
                dbReader.Close();
                return false;
            }
            dbReader.Close();
            //User aktualisieren
            dbCommand.CommandText = "UPDATE users SET name='" + editUser.UserName + "', password='" + editUser.Password + "', role=" + editUser.Role + " WHERE id =" + editUser.UserId;
            dbCommand.ExecuteNonQuery();
            return true;
        }

        /// <summary>
        /// removes an user
        /// </summary>
        /// <param name="removeUser">user object</param>
        /// <returns>true for success</returns>
        public bool removeUser(Models.User removeUser)
        {
            //wenn User mit übergebener Id nicht vorhanden -> return false
            dbCommand.CommandText = "SELECT id FROM users WHERE id = " + removeUser.UserId;
            dbReader = dbCommand.ExecuteReader();
            if (!dbReader.HasRows)
            {
                dbReader.Close();
                return false;
            }
            dbReader.Close();

            //User löschen
            dbCommand.CommandText = "DELETE FROM users WHERE id=" + removeUser.UserId;
            dbCommand.ExecuteNonQuery();
            return true;
        }

        #endregion user methods

        #region table methods

        /// <summary>
        /// adds a new table
        /// </summary>
        /// <param name="newTable">table object</param>
        /// <returns>true for success</returns>
        public bool addTable(Models.Table newTable)
        {
            dbCommand.CommandText = "INSERT INTO tables (name, number, note) VALUES ('" + newTable.Name + "', " + newTable.Number + ", '" + newTable.Description + "')";
            dbCommand.ExecuteNonQuery();
            return true;
        }

        /// <summary>
        /// loads tables out of db an retunrs list of the tables
        /// </summary>
        /// <returns>list of tables</returns>
        public List<Models.Table> getTables(string search)
        {
            List<Models.Table> tables = new List<Models.Table>();

            dbCommand.CommandText = "SELECT id, number, name, note FROM tables WHERE name LIKE '%" + search + "%' OR note LIKE '%" + search + "%' OR CAST(number AS TEXT) LIKE '%" + search + "%'";
            dbReader = dbCommand.ExecuteReader();
            while (dbReader.Read())
            {
                tables.Add(new Models.Table(dbReader.GetInt32(0), dbReader.GetInt32(1), dbReader.GetString(2), dbReader.GetString(3)));
            }
            dbReader.Close();

            return tables;
        }

        public List<Models.Table> getTables()
        {
            return getTables("");
        }

        /// <summary>
        /// updates data of an existing table
        /// </summary>
        /// <param name="editTable">table object</param>
        /// <returns>true for success</returns>
        public bool updateTable(Models.Table editTable)
        {
            //wenn Tisch mit übergebener Id nicht vorhanden -> return false
            dbCommand.CommandText = "SELECT id FROM tables WHERE id = " + editTable.Id;
            dbReader = dbCommand.ExecuteReader();
            if (!dbReader.HasRows)
            {
                dbReader.Close();
                return false;
            }
            dbReader.Close();
            //Tisch aktualisieren
            dbCommand.CommandText = "UPDATE tables SET name='" + editTable.Name + "', number=" + editTable.Number + ", note='" + editTable.Description + "' WHERE id =" + editTable.Id;
            dbCommand.ExecuteNonQuery();
            return true;
        }

        /// <summary>
        /// removes a table
        /// </summary>
        /// <param name="removeTable">table object</param>
        /// <returns>true for success</returns>
        public bool removeTable(Models.Table removeTable)
        {
             //wenn Tisch mit übergebener Id nicht vorhanden -> return false
            dbCommand.CommandText = "SELECT id FROM tables WHERE id = " + removeTable.Id;
            dbReader = dbCommand.ExecuteReader();
            if (!dbReader.HasRows)
            {
                dbReader.Close();
                return false;
            }
            dbReader.Close();
            //Tisch löschen
            dbCommand.CommandText = "DELETE FROM tables WHERE id=" + removeTable.Id;
            dbCommand.ExecuteNonQuery();
            return true;
        }

        public List<Models.OpenTable> getOpenTables()
        {
            List<Models.OpenTable> openTables = new List<Models.OpenTable>();

            dbCommand.CommandText =
                @"SELECT
                    tables.*,
                    tableSumWithNames.sum
                FROM (
                    SELECT
                        tableId,
                        SUM(price) AS sum
                    FROM (
                        SELECT
                            orders.tableId,
                            orders.id AS orderId,
                            orders.productId
                        FROM orders
                    )
                    AS tableSum
                    JOIN products
                    ON tableSum.productId = products.id
                    WHERE tableSum.orderId NOT IN (SELECT orderId FROM paid)
                    GROUP BY tableId
                )
                AS tableSumWithNames
                JOIN tables
                ON tableSumWithNames.tableId = tables.id;";

            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                openTables.Add(new Models.OpenTable(new Models.Table(dbReader.GetInt32(0), dbReader.GetInt32(2), dbReader.GetString(1), dbReader.GetString(3)), dbReader.GetFloat(5)));
            }
            dbReader.Close();

            return openTables;
        }

        #endregion //table methods

        #region SalesReport

        public List<float> getSalesReportToday()
        {
            List<float> sum = new List<float>();

            dbCommand.CommandText = @"SELECT sum FROM (SELECT SUM(products.price) AS sum FROM (SELECT orderId, productId FROM paid JOIN orders ON orders.id = orderId WHERE paid.timestamp >= DATETIME('now', 'start of day')) JOIN products ON products.id = productId) WHERE sum NOT NULL;";
            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                sum.Add(dbReader.GetFloat(0));
            }

            dbReader.Close();

            return sum;
        }

        public List<float> getSalesReportYesterday()
        {
            List<float> sum = new List<float>();

            dbCommand.CommandText = @"SELECT sum FROM (SELECT SUM(products.price) AS sum FROM (SELECT orderId, productId FROM paid JOIN orders ON orders.id = orderId WHERE paid.timestamp BETWEEN DATETIME('now', '-1 days', 'start of day') AND DATETIME('now', 'start of day')) JOIN products ON products.id = productId) WHERE sum NOT NULL;";
            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                sum.Add(dbReader.GetFloat(0));
            }
            dbReader.Close();

            return sum;
        }

        public List<float> getSalesReportThisWeek()
        {
            List<float> sum = new List<float>();

            dbCommand.CommandText = @"SELECT sum FROM (SELECT SUM(products.price) AS sum FROM (SELECT orderId, productId FROM paid JOIN orders ON orders.id = orderId WHERE paid.timestamp BETWEEN DATETIME('now', 'weekday 0', '-7 days') AND DATETIME('now', 'weekday 0')) JOIN products ON products.id = productId) WHERE sum NOT NULL;";
            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                sum.Add(dbReader.GetFloat(0));
            }
            dbReader.Close();

            return sum;
        }

        public List<float> getSalesReportThisMonth()
        {
            List<float> sum = new List<float>();

            dbCommand.CommandText = @"SELECT sum FROM (SELECT SUM(products.price) AS sum FROM (SELECT orderId, productId FROM paid JOIN orders ON orders.id = orderId WHERE paid.timestamp BETWEEN DATETIME('now', 'start of month') AND DATETIME('now', 'start of month', '+1 months'))JOIN products ON products.id = productId) WHERE sum NOT NULL;";
            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                sum.Add(dbReader.GetFloat(0));
            }
            dbReader.Close();

            return sum;
        }

        public List<float> getSalesReportLastMonth()
        {
            List<float> sum = new List<float>();

            dbCommand.CommandText = @"SELECT sum FROM (SELECT SUM(products.price) AS sum FROM (SELECT orderId, productId FROM paid JOIN orders ON orders.id = orderId WHERE paid.timestamp BETWEEN DATETIME('now', '-1 months', 'start of month') AND DATETIME('now', '-1 months', 'start of month', '+1 months'))JOIN products ON products.id = productId) WHERE sum NOT NULL;";
            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                sum.Add(dbReader.GetFloat(0));
            }
            dbReader.Close();

            return sum;
        }
        
        public List<float> getSalesReportThisYear()
        {
           List<float> sum = new List<float>();

           dbCommand.CommandText = @"SELECT sum FROM (SELECT SUM(products.price) AS sum FROM (SELECT orderId, productId FROM paid JOIN orders ON orders.id = orderId WHERE paid.timestamp BETWEEN DATETIME('now', 'start of year') AND DATETIME('now', 'start of year', '+1 year')) JOIN products ON products.id = productId) WHERE sum NOT NULL;";
            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                if (dbReader != null)
                    sum.Add(dbReader.GetFloat(0));
            }
            dbReader.Close();

            return sum;
        }

        public List<float> getSalesReportLastYear()
        {
            List<float> sum = new List<float>();

            dbCommand.CommandText = @"SELECT sum FROM (SELECT SUM(products.price) AS sum FROM (SELECT orderId, productId FROM paid JOIN orders ON orders.id = orderId WHERE paid.timestamp BETWEEN DATETIME('now', 'start of year', '-1 year') AND DATETIME('now', 'start of year'))JOIN products ON products.id = productId) WHERE sum NOT NULL;";
            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                    sum.Add(dbReader.GetFloat(0));
            }
            dbReader.Close();

            return sum;
        }

        #endregion //SalesReport

        #region group methods

        /// <summary>
        /// adds a new group
        /// </summary>
        /// <param name="newGroup">group object</param>
        /// <returns>true for success</returns>
        public bool addGroup(Models.Group newGroup)
        {
            dbCommand.CommandText = "INSERT INTO groups (name, number) VALUES ('" + newGroup.Name + "', " + newGroup.Number + ")";
            dbCommand.ExecuteNonQuery();
            return true;
        }

        /// <summary>
        /// returns a group by id
        /// </summary>
        /// <param name="groupId">groupId</param>
        /// <returns>group object</returns>
        public Models.Group getGroup(int groupId)
        {
            Models.Group group = new Models.Group(0, 0, "nicht gefunden");
            dbCommand.CommandText = "SELECT id, number, name FROM groups WHERE id = " + groupId;
            dbReader = dbCommand.ExecuteReader();
            while (dbReader.Read())
            {
                group = new Models.Group(dbReader.GetInt32(0), dbReader.GetInt32(1), dbReader.GetString(2));
            }
            dbReader.Close();
            return group;
        }
        
        /// <summary>
        /// loads groups out of db an retunrs list of the groups
        /// </summary>
        /// <returns>list of groups</returns>
        public List<Models.Group> getGroups(string search)
        {
            List<Models.Group> groups = new List<Models.Group>();

            dbCommand.CommandText = "SELECT id, number, name FROM groups WHERE name LIKE '%" + search + "%' OR CAST(number AS TEXT) LIKE '%" + search + "%'";
            dbReader = dbCommand.ExecuteReader();
            while (dbReader.Read())
            {
                groups.Add(new Models.Group(dbReader.GetInt32(0), dbReader.GetInt32(1), dbReader.GetString(2)));
            }
            dbReader.Close();

            return groups;
        }

        public List<Models.Group> getGroups()
        {
            return getGroups("");
        }

        /// <summary>
        /// updates data of an existing group
        /// </summary>
        /// <param name="editGroup">group object</param>
        /// <returns>true for success</returns>
        public bool updateGroup(Models.Group editGroup)
        {
            //wenn Gruppe mit übergebener Id nicht vorhanden -> return false
            dbCommand.CommandText = "SELECT id FROM groups WHERE id = " + editGroup.Id;
            dbReader = dbCommand.ExecuteReader();
            if (!dbReader.HasRows)
            {
                dbReader.Close();
                return false;
            }
            dbReader.Close();
            //Gruppe aktualisieren
            dbCommand.CommandText = "UPDATE groups SET name='" + editGroup.Name + "', number=" + editGroup.Number + " WHERE id =" + editGroup.Id;
            dbCommand.ExecuteNonQuery();
            return true;
        }

        /// <summary>
        /// removes a group
        /// </summary>
        /// <param name="removeGroup">group object</param>
        /// <returns>true for success</returns>
        public bool removeGroup(Models.Group removeGroup)
        {
            //wenn Gruppe mit übergebener Id nicht vorhanden -> return false
            dbCommand.CommandText = "SELECT id FROM groups WHERE id=" + removeGroup.Id;
            dbReader = dbCommand.ExecuteReader();
            if (!dbReader.HasRows)
            {
                dbReader.Close();
                return false;
            }
            dbReader.Close();

            //wenn in Gruppe noch Produkte sind, Gruppe nicht löschen, da Produkte sonst verwaisen
            if (getProducts(removeGroup.Id).Count != 0) return false;

            //Gruppe löschen
            dbCommand.CommandText = "DELETE FROM groups WHERE id=" + removeGroup.Id;
            dbCommand.ExecuteNonQuery();
            return true;
        }

        #endregion //group methods

        #region product methods

        /// <summary>
        /// adds new product
        /// </summary>
        /// <param name="newProduct">product object</param>
        /// <returns></returns>
        public bool addProduct(Models.Product newProduct)
        {
            dbCommand.CommandText = "INSERT INTO products (name, number, price, groupId, taxId) VALUES ('" + newProduct.Name + "', " + newProduct.Number + ", " + newProduct.Price.ToString(CultureInfo.InvariantCulture.NumberFormat) + ", " + newProduct.GroupId + ", " + newProduct.TaxId + ")";
            dbCommand.ExecuteNonQuery();
            return true;
        }

        /// <summary>
        /// loads products of a group out of db an retunrs list of the products
        /// </summary>
        /// <returns>list of products</returns>
        public List<Models.Product> getProducts(int groupId, string search)
        {
            List<Models.Product> products = new List<Models.Product>();

            dbCommand.CommandText = "SELECT id, number, name, price, groupId, taxId FROM products WHERE groupId = " + groupId + " AND (name LIKE '%" + search + "%' OR CAST(number AS TEXT) LIKE '%" + search + "%')";
            dbReader = dbCommand.ExecuteReader();
            while (dbReader.Read())
            {
                products.Add(new Models.Product(dbReader.GetInt32(0), dbReader.GetInt32(1), dbReader.GetString(2), dbReader.GetFloat(3), dbReader.GetInt32(4), dbReader.GetInt32(5)));
            }
            dbReader.Close();

            return products;
        }

        public List<Models.Product> getProducts(int groupId)
        {
            return getProducts(groupId, "");
        }

        /// <summary>
        /// loads all products out of db and returns list of the products
        /// </summary>
        /// <returns></returns>
        public List<Models.Product> getAllProducts()
        {
            List<Models.Product> products = new List<Models.Product>();

            dbCommand.CommandText = "SELECT id, number, name, price, groupId, taxId FROM products";
            dbReader = dbCommand.ExecuteReader();
            while (dbReader.Read())
            {
                products.Add(new Models.Product(dbReader.GetInt32(0), dbReader.GetInt32(1), dbReader.GetString(2), dbReader.GetFloat(3), dbReader.GetInt32(4), dbReader.GetInt32(5)));
            }
            dbReader.Close();

            return products;
        }

        /// <summary>
        /// updates product details
        /// </summary>
        /// <param name="editProduct">user object</param>
        /// <returns>true for success</returns>
        public bool updateProduct(Models.Product editProduct)
        {
            //wenn Produkt mit übergebener Id nicht vorhanden -> return false
            dbCommand.CommandText = "SELECT id FROM products WHERE id = " + editProduct.Id;
            dbReader = dbCommand.ExecuteReader();
            if (!dbReader.HasRows)
            {
                dbReader.Close();
                return false;
            }
            dbReader.Close();
            //Produkt aktualisieren
            dbCommand.CommandText = "UPDATE products SET name='" + editProduct.Name + "', number=" + editProduct.Number + ", price=" + editProduct.Price + ", groupId=" + editProduct.GroupId + ", taxId=" + editProduct.TaxId + " WHERE id =" + editProduct.Id;
            dbCommand.ExecuteNonQuery();
            return true;
        }

        /// <summary>
        /// removes a product
        /// </summary>
        /// <param name="?">product object</param>
        /// <returns>true for success</returns>
        public bool removeProduct(Models.Product removeProduct)
        {
             //wenn Produkt mit übergebener Id nicht vorhanden -> return false
            dbCommand.CommandText = "SELECT id FROM products WHERE id = " + removeProduct.Id;
            dbReader = dbCommand.ExecuteReader();
            if (!dbReader.HasRows)
            {
                dbReader.Close();
                return false;
            }
            dbReader.Close();

            //Produkt löschen
            dbCommand.CommandText = "DELETE FROM products WHERE id=" + removeProduct.Id;
            dbCommand.ExecuteNonQuery();
            return true;
        }

        #endregion //product methods

        #region tax methods

        /// <summary>
        /// adds new tax to db
        /// </summary>
        /// <param name="newTax">tax object</param>
        /// <returns>true for success</returns>
        public bool addTax(Models.Tax newTax)
        {
            dbCommand.CommandText = "INSERT INTO taxes (note, tax) VALUES ('" + newTax.Name + "', " + newTax.Taxset +")";
            dbCommand.ExecuteNonQuery();
            return true;
        }

        public Models.Tax getTax(int taxId)
        {
            Models.Tax tax = new Models.Tax(0, "", 19);
            dbCommand.CommandText = "SELECT id, note, tax FROM taxes WHERE id = " + taxId;
            dbReader = dbCommand.ExecuteReader();
            while (dbReader.Read())
            {
                tax = new Models.Tax(dbReader.GetInt32(0), dbReader.GetString(1), dbReader.GetInt32(2));
            }
            dbReader.Close();
            return tax;
        }

        /// <summary>
        /// loads all taxes from db and returns list of taxes
        /// </summary>
        /// <returns>list of taxes</returns>
        public List<Models.Tax> getTaxes()
        {
            List<Models.Tax> taxes = new List<Models.Tax>();

            dbCommand.CommandText = "SELECT id, note, tax FROM taxes";
            dbReader = dbCommand.ExecuteReader();
            while (dbReader.Read())
            {
                taxes.Add(new Models.Tax(dbReader.GetInt32(0), dbReader.GetString(1), dbReader.GetInt32(2)));
            }
            dbReader.Close();

            return taxes;
        }

        /// <summary>
        /// updates an existing tax
        /// </summary>
        /// <param name="editTax">tax object</param>
        /// <returns>true for success</returns>
        public bool updateTax(Models.Tax editTax)
        {
            //wenn Steuer mit übergebener Id nicht vorhanden -> return false
            dbCommand.CommandText = "SELECT id FROM taxes WHERE id = " + editTax.Id;
            dbReader = dbCommand.ExecuteReader();
            if (!dbReader.HasRows)
            {
                dbReader.Close();
                return false;
            }
            dbReader.Close();
            //Steuer aktualisieren
            dbCommand.CommandText = "UPDATE taxes SET note='" + editTax.Name + "', tax=" + editTax.Taxset + " WHERE id =" + editTax.Id;
            dbCommand.ExecuteNonQuery();
            return true;
        }

        /// <summary>
        /// removes a tax
        /// </summary>
        /// <param name="removeTax">tax object</param>
        /// <returns>true for success</returns>
        public bool removeTax(Models.Tax removeTax)
        {
            //wenn Steuer mit übergebener Id nicht vorhanden -> return false
            dbCommand.CommandText = "SELECT id FROM taxes WHERE id=" + removeTax.Id;
            dbReader = dbCommand.ExecuteReader();
            if (!dbReader.HasRows)
            {
                dbReader.Close();
                return false;
            }
            dbReader.Close();
        
            //Steuer löschen
            dbCommand.CommandText = "DELETE FROM taxes WHERE id=" + removeTax.Id;
            dbCommand.ExecuteNonQuery();
            return true;
        }

        #endregion //tax methods

        #region order methods

        /// <summary>
        /// preorder a product for a table
        /// </summary>
        /// <param name="orderItem">OrderItem for selected Item</param>
        /// <param name="tableId">Id of the table</param>
        public void addOrder(int productId, int tableId, int userId)
        {
            dbCommand.CommandText = "INSERT INTO orders (tableId, productId, userId, timestamp) VALUES (" + tableId + ", " + productId + ", " + userId + ", datetime('now'))";
            dbCommand.ExecuteNonQuery();
        }

        public List<Models.OrderItem> getOrders(int tableId)
        {
            List<Models.OrderItem> orders = new List<Models.OrderItem>();

            dbCommand.CommandText =
                @"SELECT
                    *,
                    COUNT(id) AS quantity,
                    SUM(price) AS sum
                FROM (
                    SELECT
                        products.id,
                        products.name,
                        products.number,
                        products.price,
                        products.groupId,
                        products.taxId,
                        products.active
                    FROM orders
                    JOIN products
                    ON products.id = orders.productID
                    WHERE
                        orders.tableID = " + tableId + @"
                    AND orders.id NOT IN (SELECT orderID FROM ordered)
                )
                GROUP BY id;";

            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                var product = new Models.Product(dbReader.GetInt32(0), dbReader.GetInt32(2), dbReader.GetString(1), dbReader.GetFloat(3));
                orders.Add(new Models.OrderItem(product, dbReader.GetInt32(7), dbReader.GetFloat(8)));
            }

            dbReader.Close();

            return orders;
        }

        /// <summary>
        /// removes an order
        /// </summary>
        /// <returns>true for success</returns>
        public bool removeOrder(int productId, int tableId, int userId)
        {
            // Wenn Order mit übergebener Id nicht vorhanden -> return false
            dbCommand.CommandText = "SELECT id FROM (SELECT * FROM orders WHERE productId = " + productId + " AND tableId = " + tableId + " AND userId = " + userId + ") ORDER BY timestamp DESC LIMIT 1";
            dbReader = dbCommand.ExecuteReader();

            if (!dbReader.HasRows)
            {
                dbReader.Close();
                return false;
            }

            dbReader.Close();

            // Order löschen
            dbCommand.CommandText = "DELETE FROM orders WHERE id = (SELECT id FROM (SELECT * FROM orders WHERE productId = " + productId + " AND tableId = " + tableId + " AND userId = " + userId + ") ORDER BY timestamp DESC LIMIT 1)";
            dbCommand.ExecuteNonQuery();
            return true;
        }

        /// <summary>
        /// orders items for a table
        /// </summary>
        /// <param name="tableId">id of the table</param>
        public void order(int tableId)
        {
            List<int> orderIds = new List<int>();

            dbCommand.CommandText =
                @"SELECT
                    orders.id
                FROM orders
                JOIN products
                ON products.id = orders.productID
                WHERE orders.tableID = " + tableId + @"
                    AND orders.id NOT IN (SELECT orderID FROM ordered);";

            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                orderIds.Add(dbReader.GetInt32(0));
            }

            dbReader.Close();

            foreach (int orderId in orderIds)
            {
                dbCommand.CommandText = "INSERT INTO ordered (orderId, timestamp) VALUES (" + orderId + ", DATETIME('now'))";
                dbCommand.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// lists ordered items of a table
        /// </summary>
        /// <param name="tableId">id of the table</param>
        /// <returns>list of order items</returns>
        public List<Models.OrderItem> getOrdered(int tableId)
        {
            List<Models.OrderItem> orders = new List<Models.OrderItem>();

            dbCommand.CommandText =
                @"SELECT
                    products.id,
                    products.name,
                    products.number,
                    products.price,
                    products.groupId,
                    products.taxId,
                    products.active,
                    COUNT(*) as quantity,
                    SUM(products.price)
                FROM (
                    SELECT
                        orders.id,
                        orders.productId
                    FROM orders
                    JOIN ordered
                    ON ordered.orderId = orders.id
                    WHERE orders.tableID = " + tableId + @"
                ) AS tableOrdered
                JOIN products
                ON products.id = tableOrdered.productID
                WHERE tableOrdered.id NOT IN (SELECT orderID FROM paid)
                GROUP BY products.id;";

            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                var product = new Models.Product(dbReader.GetInt32(0), dbReader.GetInt32(2), dbReader.GetString(1), dbReader.GetFloat(3));
                orders.Add(new Models.OrderItem(product, dbReader.GetInt32(7), dbReader.GetFloat(8)));
            }

            dbReader.Close();

            return orders;
        }

        /// <summary>
        /// removes an ordered order
        /// </summary>
        public void removeOrdered(int tableId)
        {
            // Order löschen
            dbCommand.CommandText = "DELETE FROM ordered WHERE orderId IN (SELECT orderId FROM ordered JOIN orders ON orderId = orders.id WHERE orders.tableID = " + tableId + " AND orders.id NOT IN (SELECT orderID FROM paid));";
            dbCommand.ExecuteNonQuery();
        }

        /// <summary>
        /// checks out a table
        /// </summary>
        /// <param name="tableId">id of the table</param>
        /// <returns>list of the payed items</returns>
        public List<Models.OrderItem> pay(int tableId)
        {
            List<Models.OrderItem> ordered = new List<Models.OrderItem>();

            dbCommand.CommandText =
                @"SELECT
                    products.id,
                    products.name,
                    products.number,
                    products.price,
                    products.groupId,
                    products.taxId,
                    products.active,
                    COUNT(*) as quantity,
                    SUM(products.price)
                FROM (
                    SELECT
                        orders.id,
                        orders.productId
                    FROM orders
                    JOIN ordered
                    ON ordered.orderId = orders.id
                    WHERE orders.tableID = " + tableId + @"
                ) AS tableOrdered
                JOIN products
                ON products.id = tableOrdered.productID
                WHERE tableOrdered.id NOT IN (SELECT orderID FROM paid)
                GROUP BY products.id;";

            dbReader = dbCommand.ExecuteReader();

            while (dbReader.Read())
            {
                var product = new Models.Product(dbReader.GetInt32(0), dbReader.GetInt32(2), dbReader.GetString(1), dbReader.GetFloat(3));
                ordered.Add(new Models.OrderItem(product, dbReader.GetInt32(7), dbReader.GetFloat(8)));
            }

            dbReader.Close();

            // Query for inserting the ordered items into the paid table (for marking them as paid)
            dbCommand.CommandText =
                @"INSERT INTO paid (
                    orderId,
                    timestamp
                )
                SELECT
                    orders.id AS orderId,
                    DATETIME('now') AS timestamp
                FROM orders
                JOIN ordered
                ON ordered.orderId = orders.id
                WHERE orders.tableID = " + tableId + @"
                AND ordered.orderId NOT IN (SELECT orderID FROM paid);";

            dbCommand.ExecuteNonQuery();

            return ordered;
        }

        #endregion order methods

        #endregion // Methods
    }
}
