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;

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

      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()
      {
         string input = $"select id, rfid from users";//first_name, last_name, email, rfid, password from users";//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())
               {
                  users local_cust = new users(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 (userNotInList(local_cust))
                     this.users_list.Add(local_cust);
               }
            }
            databaseConnection.Close();
         }
         catch (Exception exception) { }
      }

      public void runQuerry_updateOrderList()
      {
         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)
               return u.userID();
         }
         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;
      }


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

   }

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

   }
}