using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data;
using MySql.Data.MySqlClient;


namespace Camping_App
{
    /*
     * Data object responsible for storing lists of database entities
     * Access information stored in database through this class
     * Insert information into database through this class
     *
     * In orde to access fhict server, VPN connection is required
     * Use Cisco AnyConnect Client - vdi.fhict.nl
     */

    // documentation non-existent

    class DataObj
    {
        private List<users> users_list;
        private List<orders> orders_list;
        private List<order_items> order_items;
<<<<<<< HEAD
        private List<user_with_camping_reservation> reservation_list;
=======
      private List<user_with_camping_reservation> reservation_list;
>>>>>>> 2090e70fcd7ca0c55c8bb955364c0aa999309ba8

        public DataObj()
        {
            users_list = new List<users>();
            orders_list = new List<orders>();
            order_items = new List<order_items>();
            reservation_list = new List<user_with_camping_reservation>();
        }

        public void createAndInsertUser(string name_f, string name_l, string email)
        {
            users user = new users(name_f, name_l, email);
            user.insertUser();
        }

        public bool userNotInList(users user)
        {
            foreach (users u in users_list)
            {
                if (user.userID() == u.userID())
                    return false;
            }
            return true;
        }

        public bool only_uniqueId(orders order)
        {
            foreach (orders u in orders_list)
            {
                if (order.orderID() == u.orderID())
                    return false;
            }
            return true;
        }

        public bool item_to_a_basket(order_items oi)
        {
            foreach (order_items o in order_items)
            {
                if (oi.order_itemID() == o.order_itemID())
                    return false;
            }
            return true;
        }

        public void runQuerry_updateUserList_thatHaveBooked()
<<<<<<< HEAD
        {
            //string input = $"select id, rfid from users";//first_name, last_name, email, rfid, password from users";//created_at, updated_at from users;";

            string input = "select user_id from orders where order_type = 'camping' AND order_status = 1;";


            MySqlConnection databaseConnection = new MySqlConnection("Server=studmysql01.fhict.local;" +
                                                                        "Uid=dbi428954;" +
                                                                        "Database=dbi428954;" +
                                                                        "Pwd=planetfun69planetfun;");
            MySqlCommand comandDatabase = new MySqlCommand(input, databaseConnection);
            comandDatabase.CommandTimeout = 60;
            try
            {
                databaseConnection.Open();
                MySqlDataReader sqlReader = comandDatabase.ExecuteReader();
                if (sqlReader.HasRows)
                {
                    while (sqlReader.Read())
                    {
                        user_with_camping_reservation reserv_usr = new user_with_camping_reservation(sqlReader.GetInt64(0)
                                                      //sqlReader.GetString(1)//,
                                                      /*sqlReader.GetString(2),
                                                      sqlReader.GetString(3),
                                                      sqlReader.GetString(4),
                                                      sqlReader.GetString(5)//,*/
                                                      //sqlReader.GetDateTime(6),
                                                      //sqlReader.GetDateTime(7)
                                                      );
                        if (onlyUniqueReservations(reserv_usr))
                            this.reservation_list.Add(reserv_usr);
                    }
                }
                databaseConnection.Close();
            }
            catch (Exception exception) { }
        }

        public void runQuerry_updateUserList()
        {
            string input = $"select id, first_name, last_name , email, rfid from users";//email, password, created_at, updated_at from users;";
            MySqlConnection databaseConnection = new MySqlConnection("Server=studmysql01.fhict.local;" +
=======
        {
         //string input = $"select id, rfid from users";//first_name, last_name, email, rfid, password from users";//created_at, updated_at from users;";

         string input = "select user_id from orders where order_type = 'camping' AND order_status = 1;";


         MySqlConnection databaseConnection = new MySqlConnection("Server=studmysql01.fhict.local;" +
>>>>>>> 2090e70fcd7ca0c55c8bb955364c0aa999309ba8
                                                                     "Uid=dbi428954;" +
                                                                     "Database=dbi428954;" +
                                                                     "Pwd=planetfun69planetfun;");
            MySqlCommand comandDatabase = new MySqlCommand(input, databaseConnection);
            comandDatabase.CommandTimeout = 60;
            try
            {
                databaseConnection.Open();
                MySqlDataReader sqlReader = comandDatabase.ExecuteReader();
                if (sqlReader.HasRows)
                {
                    while (sqlReader.Read())
                    {
<<<<<<< HEAD
                        users local_cust = new users(sqlReader.GetInt64(0),
                                                      sqlReader.GetString(1),
                                                      sqlReader.GetString(2),
                                                      sqlReader.GetString(3),
                                                      sqlReader.GetString(4)
                                                      //,
                                                      //sqlReader.GetString(4),
                                                      //sqlReader.GetDateTime(5),
                                                      //sqlReader.GetDateTime(6)*/
=======
                        user_with_camping_reservation reserv_usr = new user_with_camping_reservation(sqlReader.GetInt64(0)
                                                      //sqlReader.GetString(1)//,
                                                                            /*sqlReader.GetString(2),
                                                                            sqlReader.GetString(3),
                                                                            sqlReader.GetString(4),
                                                                            sqlReader.GetString(5)//,*/
                                                                            //sqlReader.GetDateTime(6),
                                                                            //sqlReader.GetDateTime(7)
>>>>>>> 2090e70fcd7ca0c55c8bb955364c0aa999309ba8
                                                      );
                        if (onlyUniqueReservations(reserv_usr))
                            this.reservation_list.Add(reserv_usr);
                    }
                }
                databaseConnection.Close();
            }
            catch (Exception exception) { }
        }

<<<<<<< HEAD

        public bool onlyUniqueReservations(user_with_camping_reservation particular_user)
        {
            foreach (user_with_camping_reservation u in reservation_list)
            {
                if (particular_user.reservation_user_id() == u.reservation_user_id())
                    return false;
            }
            return true;
        }


        public void runQuerry_updateOrderList()
=======
         public void runQuerry_updateUserList()
         {
            string input = $"select id, first_name, last_name , email, rfid from users";//email, password, created_at, updated_at from users;";
            MySqlConnection databaseConnection = new MySqlConnection("Server=studmysql01.fhict.local;" +
                                                                     "Uid=dbi428954;" +
                                                                     "Database=dbi428954;" +
                                                                     "Pwd=planetfun69planetfun;");
            MySqlCommand comandDatabase = new MySqlCommand(input, databaseConnection);
            comandDatabase.CommandTimeout = 60;
            try
            {
               databaseConnection.Open();
               MySqlDataReader sqlReader = comandDatabase.ExecuteReader();
               if (sqlReader.HasRows)
               {
                  while (sqlReader.Read())
                  {
                     if(!sqlReader.IsDBNull(0) && !sqlReader.IsDBNull(1) &&
                        !sqlReader.IsDBNull(2) && !sqlReader.IsDBNull(3) &&
                        !sqlReader.IsDBNull(4))
                  {

                     users local_cust = new users(sqlReader.GetInt64(0),
                                                   sqlReader.GetString(1),
                                                   sqlReader.GetString(2),
                                                   sqlReader.GetString(3),
                                                   sqlReader.GetString(4)
                                                   //,
                                                                         //sqlReader.GetString(4),
                                                                         //sqlReader.GetDateTime(5),
                                                                         //sqlReader.GetDateTime(6)*/
                                                   );
                     if (userNotInList(local_cust))
                        this.users_list.Add(local_cust);
                  }
               }
               }
               databaseConnection.Close();
            }
            catch (Exception exception) { }
         }


      public bool onlyUniqueReservations(user_with_camping_reservation particular_user)
      {
         foreach (user_with_camping_reservation u in reservation_list)
         {
            if (particular_user.reservation_user_id() == u.reservation_user_id())
               return false;
         }
         return true;
      }


      public void runQuerry_updateOrderList()
>>>>>>> 2090e70fcd7ca0c55c8bb955364c0aa999309ba8
        {
            string input = $"select * from orders;";
            MySqlConnection databaseConnection = new MySqlConnection("Server=studmysql01.fhict.local;" +
                                                                     "Uid=dbi428954;" +
                                                                     "Database=dbi428954;" +
                                                                     "Pwd=planetfun69planetfun;");
            MySqlCommand comandDatabase = new MySqlCommand(input, databaseConnection);
            comandDatabase.CommandTimeout = 60;
            try
            {
                databaseConnection.Open();
                MySqlDataReader sqlReader = comandDatabase.ExecuteReader();
                if (sqlReader.HasRows)
                {
                    while (sqlReader.Read())
                    {
                        orders local_cust = new orders(sqlReader.GetInt64(0),
                                                      sqlReader.GetInt64(1),
                                                      sqlReader.GetDateTime(2),
                                                      sqlReader.GetBoolean(3)
                                                      );
                        if (only_uniqueId(local_cust))
                            this.orders_list.Add(local_cust);
                    }
                }
                databaseConnection.Close();
            }
            catch (Exception exception) { }
        }

        public void runQuerry_updateOrderItemList()
        {
            string input = $"select id, order_id, item_id, quantity from orders;";
            MySqlConnection databaseConnection = new MySqlConnection("Server=studmysql01.fhict.local;" +
                                                                     "Uid=dbi428954;" +
                                                                     "Database=dbi428954;" +
                                                                     "Pwd=planetfun69planetfun;");
            MySqlCommand comandDatabase = new MySqlCommand(input, databaseConnection);
            comandDatabase.CommandTimeout = 60;
            try
            {
                databaseConnection.Open();
                MySqlDataReader sqlReader = comandDatabase.ExecuteReader();
                if (sqlReader.HasRows)
                {
                    while (sqlReader.Read())
                    {
                        order_items local_cust = new order_items(sqlReader.GetInt64(0),
                                                                  sqlReader.GetInt64(1),
                                                                  sqlReader.GetInt64(2),
                                                                  sqlReader.GetInt64(3)
                                                                );
                        if (item_to_a_basket(local_cust))
                            this.order_items.Add(local_cust);
                    }
                }
                databaseConnection.Close();
            }
            catch (Exception exception) { }
        }

        public int userListSize()
        {
            return this.users_list.Count();
        }

        public int orderListSize()
        {
            return this.orders_list.Count();
        }

        public bool checkUser(string first_name, string last_name, string email)
        {
            foreach (users u in users_list)
            {
                if ((u.userFirst_name() == first_name) &&
                     (u.userLast_name() == last_name) &&
                     (u.userEmail() == email))
                    return true;
            }
            return false;
        }

        public void assignRfidToUser(string first_name, string last_name, string email, string dummy_rfid)
        {
            string assigning_rfid;
            assigning_rfid = $"UPDATE `users` SET `rfid` = '{dummy_rfid}' WHERE `users`.`first_name` = '{first_name}' AND `users`.`last_name` = '{last_name}' AND `users`.`email` = '{email}';";
            runQuery(assigning_rfid);
        }

        public void runQuery(string input)
        {
            MySqlConnection databaseConnection = new MySqlConnection("Server=studmysql01.fhict.local;" +
                                                                     "Uid=dbi428954;" +
                                                                     "Database=dbi428954;" +
                                                                     "Pwd=planetfun69planetfun;");
            MySqlCommand comandDatabase = new MySqlCommand(input, databaseConnection);
            comandDatabase.CommandTimeout = 60;
            try
            {
                databaseConnection.Open();
                MySqlDataReader sqlReader = comandDatabase.ExecuteReader();
            }
            catch (Exception exception) { }
            databaseConnection.Close();
        }

        public Int64 getOrderIdForOrderItem(string input)
        {
            MySqlConnection databaseConnection = new MySqlConnection("Server=studmysql01.fhict.local;" +
                                                                     "Uid=dbi428954;" +
                                                                     "Database=dbi428954;" +
                                                                     "Pwd=planetfun69planetfun;");
            MySqlCommand comandDatabase = new MySqlCommand(input, databaseConnection);
            comandDatabase.CommandTimeout = 60;
            try
            {
                databaseConnection.Open();
                MySqlDataReader sqlReader = comandDatabase.ExecuteReader();
                if (sqlReader.HasRows)
                {
                    while (sqlReader.Read())
                    {
                        //users local_cust = new users(sqlReader.GetInt64(0),
                        Int64 last_id;
                        last_id = sqlReader.GetInt64(0);
                        return last_id;
                    }
                }
                databaseConnection.Close();
            }
            catch (Exception exception) { }
            return 0;
        }

        public Int64 checkRfid(string rfid_req)
        {
            foreach (users u in users_list)
            {
                if (u.userRfid() == rfid_req)
                {
<<<<<<< HEAD
                    foreach (user_with_camping_reservation uwcr in reservation_list)
                    {
                        if (uwcr.reservation_user_id() == u.userID())
                            return u.userID();
                    }

                }
                //return u.userID();
=======
                     foreach (user_with_camping_reservation uwcr in reservation_list)
                     {
                        if (uwcr.reservation_user_id() == u.userID())
                           return u.userID();
                     }

                }
                    //return u.userID();
>>>>>>> 2090e70fcd7ca0c55c8bb955364c0aa999309ba8
            }
            return 0;
        }

        public void addOrderItem(Int64 last_id, int item_type)
        {
            string add_order_record;
            add_order_record = $"INSERT INTO `order_items` (`order_id`, `item_id`, `quantity`) VALUES ('{last_id}', '{item_type}', '1');";
            runQuery(add_order_record);
        }

        public void addTentToItems()
        {
            string s;
            s = "INSERT INTO `items` (`id`, `name`, `list_price`) VALUES(NULL, 'Tent', '150.00');";
            runQuery(s);
        }

        public bool borrow(Int64 id, int item_id)
        {
            foreach (users u in users_list)
            {
                if (u.userID() == id)
                {
                    string borrow_tent_req;
                    borrow_tent_req = $"INSERT INTO `orders` (`user_id`) VALUES ('{id}');";
                    runQuery(borrow_tent_req);

                    // get orders into orders list
                    runQuerry_updateOrderList();

                    // insert tent item
                    string get_last_order;
                    get_last_order = $"SELECT MAX(`id`) FROM `orders`;";
                    Int64 last_id;
                    last_id = getOrderIdForOrderItem(get_last_order);
                    if (last_id != 0)
                        addOrderItem(last_id, item_id);

                    // update orders items
                    runQuerry_updateOrderItemList();

                    return true;
                }
            }
            return false;
        }

    }

    class users : DataObj
    {
        private Int64 id;
        private string first_name;
        private string last_name;
        private string email;
        private string qr;
        private string rfid;
        private string password;
        //private string remember_token;
        private DateTime created_at;
        private DateTime updated_at;

        public users(Int64 id)
        {
            this.id = id;
        }


        public users(Int64 id, string rfid)
        {
            this.id = id;
            this.rfid = rfid;
        }

        public users(string first_name, string last_name, string email)
        {
            this.first_name = first_name;
            this.last_name = last_name;
            this.email = email;
        }

<<<<<<< HEAD
        public users(Int64 id, string first_name, string last_name, string email, string rfid)
        {
            this.id = id;
            this.first_name = first_name;
            this.last_name = last_name;
            this.email = email;
            this.rfid = rfid;
        }
=======
      public users(Int64 id, string first_name, string last_name, string email, string rfid)
      {
         this.id = id;
         this.first_name = first_name;
         this.last_name = last_name;
         this.email = email;
         this.rfid = rfid;
      }
>>>>>>> 2090e70fcd7ca0c55c8bb955364c0aa999309ba8


      public users(Int64 id, string f_na, string l_na, string email, string rfid, string pass)//, DateTime creat, DateTime updat)
        {
            this.id = id;
            this.first_name = f_na;
            this.last_name = l_na;
            this.email = email;
            this.rfid = rfid;
            this.password = pass;
            //this.remember_token = tok;
            //this.created_at = creat;
            //this.updated_at = updat;
        }


        public users(Int64 id, string f_na, string l_na, string email, string rfid, string pass, DateTime creat, DateTime updat)
        {
            this.id = id;
            this.first_name = f_na;
            this.last_name = l_na;
            this.email = email;
            this.rfid = rfid;
            this.password = pass;
            //this.remember_token = tok;
            this.created_at = creat;
            this.updated_at = updat;
        }

        public void insertUser()
        {
            string input = $"INSERT INTO `users` (`first_name`, `last_name`, `email`, `password`) VALUES ('{this.first_name}', '{this.last_name}', '{this.email}', 'at_entrance');";
            runQuery(input);
        }

        public void runQuery(string input)
        {
            MySqlConnection databaseConnection = new MySqlConnection("Server=studmysql01.fhict.local;" +
                                                                     "Uid=dbi428954;" +
                                                                     "Database=dbi428954;" +
                                                                     "Pwd=planetfun69planetfun;");
            MySqlCommand comandDatabase = new MySqlCommand(input, databaseConnection);
            comandDatabase.CommandTimeout = 60;
            try
            {
                databaseConnection.Open();
                MySqlDataReader sqlReader = comandDatabase.ExecuteReader();
            }
            catch (Exception exception) { }
            databaseConnection.Close();
        }

        public Int64 userID()
        {
            return this.id;
        }

        public string userFirst_name()
        {
            return this.first_name;
        }

        public string userLast_name()
        {
            return this.last_name;
        }

        public string userEmail()
        {
            return this.email;
        }

        public string userRfid()
        {
            return this.rfid;
        }

    }

<<<<<<< HEAD
    class user_with_camping_reservation
    {
        private Int64 id;

        public user_with_camping_reservation(Int64 id)
        {
            this.id = id;
        }

        public Int64 reservation_user_id()
        {
            return this.id;
        }

    }
=======
   class user_with_camping_reservation
   {
      private Int64 id;

      public user_with_camping_reservation(Int64 id)
      {
         this.id = id;
      }

      public Int64 reservation_user_id()
      {
         return this.id;
      }

   }
>>>>>>> 2090e70fcd7ca0c55c8bb955364c0aa999309ba8

    class orders
    {
        private Int64 id;
        private Int64 user_id;
        private DateTime order_date;
        private bool order_status;

        public orders(Int64 user_id)
        {
            this.user_id = user_id;
        }

        public orders(Int64 id, Int64 user_id, DateTime order_date, bool order_status)
        {
            this.id = id;
            this.user_id = id;
            this.order_date = order_date;
            this.order_status = order_status;
        }

        public Int64 orderID()
        {
            return this.id;
        }
    }

    class order_items
    {
        private Int64 id;
        private Int64 order_id;
        private Int64 item_id;
        private Int64 quantity;

        public order_items(Int64 id, Int64 order_id, Int64 item_id, Int64 quantity)
        {
            this.id = id;
            this.order_id = order_id;
            this.item_id = item_id;
            this.quantity = quantity;
        }

        public Int64 order_itemID()
        {
            return this.id;
        }

    }
}