using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using System.Configuration;
using APICode.Models;
using System.Data;

namespace APICode.DAL
{
    public class ReservationDAL
    {
        static string connString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;

        #region Public Method

        public ResultModel Login(string emailid, string password)
        {
            var resultModel = new ResultModel();
            MySqlConnection conn = new MySqlConnection(connString);
            try
            {

                string sql = "select * from users where email='" + emailid + "' and password='" + password + "'";
                conn.Open();

                MySqlDataAdapter mydata = new MySqlDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                mydata.Fill(ds);

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    var loginResultModel = new LoginResultModel();
                    loginResultModel.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["id"]);
                    loginResultModel.Name = Convert.ToString(ds.Tables[0].Rows[0]["name"]);
                    loginResultModel.Email = Convert.ToString(ds.Tables[0].Rows[0]["email"]);
                    loginResultModel.Role = Convert.ToInt32(ds.Tables[0].Rows[0]["role"]);

                    resultModel.IsSucessful = true;
                    resultModel.Result = loginResultModel;
                }
                else
                {
                    resultModel.ErrorMessage = "Username / password is incorrect";
                }
            }
            catch (Exception ex)
            {
                resultModel.IsSucessful = false;
                resultModel.ErrorMessage = ex.ToString();
            }
            finally
            {
                conn.Close();
            }
            return resultModel;
        }

        public ResultModel GetRoomTypes(int userId)
        {
            var resultModel = new ResultModel();
            MySqlConnection conn = new MySqlConnection(connString);
            try
            {
                string sql = "select * from room_types";
                conn.Open();

                MySqlDataAdapter mydata = new MySqlDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                mydata.Fill(ds);

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    var keyvalpairs = new List<KeyValuePair<int, string>>();

                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        keyvalpairs.Add(new KeyValuePair<int, string>(
                                Convert.ToInt32(ds.Tables[0].Rows[i]["id"]),
                                Convert.ToString(ds.Tables[0].Rows[i]["name"])
                            ));
                    }

                    resultModel.IsSucessful = true;
                    resultModel.Result = keyvalpairs;
                }
                else
                {
                    resultModel.ErrorMessage = "room type not found";
                }
            }
            catch (Exception ex)
            {
                resultModel.IsSucessful = false;
                resultModel.ErrorMessage = ex.ToString();
            }
            finally
            {
                conn.Close();
            }
            return resultModel;
        }

        public ResultModel GetRooms(int typeId)
        {
            var resultModel = new ResultModel();
            MySqlConnection conn = new MySqlConnection(connString);
            try
            {
                string sql = "select * from rooms where type_id=" + typeId;
                conn.Open();

                MySqlDataAdapter mydata = new MySqlDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                mydata.Fill(ds);

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    var roomdata = new List<RoomModel<TimeSpanModel>>();

                    for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    {
                        var room = new RoomModel<TimeSpanModel>();
                        room.Id = Convert.ToInt32(ds.Tables[0].Rows[i]["id"]);
                        room.Type_Id = Convert.ToInt32(ds.Tables[0].Rows[i]["type_id"]);
                        room.Name = Convert.ToString(ds.Tables[0].Rows[i]["name"]);
                        room.StartTime = Convert.ToString(ds.Tables[0].Rows[i]["start_time"]);
                        room.EndTime = Convert.ToString(ds.Tables[0].Rows[i]["end_time"]);
                        room.BookingInterval = Convert.ToInt32(ds.Tables[0].Rows[i]["booking_interval"]);
                        room.BookingCapacity = Convert.ToInt32(ds.Tables[0].Rows[i]["booking_capacity"]);
                        roomdata.Add(room);
                    }

                    resultModel.IsSucessful = true;
                    resultModel.Result = roomdata;
                }
                else
                {
                    resultModel.ErrorMessage = "room type not found";
                }
            }
            catch (Exception ex)
            {
                resultModel.IsSucessful = false;
                resultModel.ErrorMessage = ex.ToString();
            }
            finally
            {
                conn.Close();
            }
            return resultModel;
        }

        public ResultModel UserBookings(int userId)
        {
            var resultModel = new ResultModel();
            MySqlConnection conn = new MySqlConnection(connString);
            try
            {
                string sql = null;
                if (userId == 0)
                {
                    sql = "SELECT bookings.*, rooms.name, rooms.room_type_id, users.name AS `user_name`, users.email FROM `bookings` JOIN rooms ON bookings.room_id = rooms.id JOIN users ON bookings.user_id = users.id";
                }
                else
                {
                    sql = "SELECT bookings.*, rooms.name, rooms.room_type_id, users.name AS `user_name`, users.email FROM `bookings` JOIN rooms ON bookings.room_id = rooms.id JOIN users ON bookings.user_id = users.id  WHERE `user_id` = " + userId;
                }


                conn.Open();

                MySqlDataAdapter mydata = new MySqlDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                mydata.Fill(ds);

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    var roomdata = ds.Tables[0];
                    resultModel.IsSucessful = true;
                    resultModel.Result = roomdata;
                }
                else
                {
                    resultModel.ErrorMessage = "room type not found";
                }
            }
            catch (Exception ex)
            {
                resultModel.IsSucessful = false;
                resultModel.ErrorMessage = ex.ToString();
            }
            finally
            {
                conn.Close();
            }
            return resultModel;
        }



        public ResultModel checkAvailability(int roomType, String date, String startTime, String endTime)
        {

            return checkAvailabilityFunction(roomType, date, startTime, endTime);

        }

        private ResultModel checkAvailabilityFunction(int roomType, String date, String startTime, String endTime){
            var resultModel = new ResultModel();
            MySqlConnection conn = new MySqlConnection(connString);

            try
            {
                string sql = "SELECT * FROM bookings WHERE (room_type = " + roomType + " AND bookings.status = 1 AND date = '" + date + "') AND ((bookings.start_time <= '" + startTime + "' AND bookings.end_time >= '" + endTime + "') OR (bookings.start_time >= '" + startTime + "' AND bookings.start_time <= '" + endTime + "') OR (bookings.end_time >= '" + startTime + "' AND bookings.end_time <= '" + endTime + "')) GROUP BY room_id";
                conn.Open();

                MySqlDataAdapter mydata = new MySqlDataAdapter(sql, conn);
                DataSet ds = new DataSet();
                mydata.Fill(ds);

                //resultModel.IsSucessful = true;
                //resultModel.Result = ds.Tables[0];
                //return resultModel;

                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    string sql3 = "SELECT bookings.room_id FROM bookings WHERE (room_type = '" + roomType + "' AND bookings.status = 1 AND date = '" + date + "') AND ((bookings.start_time <= '" + startTime + "' AND bookings.end_time >= '" + endTime + "') OR (bookings.start_time >= '" + startTime + "' AND bookings.start_time <= '" + endTime + "') OR (bookings.end_time >= '" + startTime + "' AND bookings.end_time <= '" + endTime + "')) GROUP BY room_id";
                    string sql2 = "SELECT rooms.* FROM rooms WHERE rooms.room_type_id = '" + roomType + "' AND rooms.id <> ANY (" + sql3 + ")";

                    MySqlDataAdapter mydata2 = new MySqlDataAdapter(sql2, conn);
                    DataSet ds2 = new DataSet();
                    mydata2.Fill(ds2);

                    if (ds2.Tables.Count > 0 && ds2.Tables[0].Rows.Count > 0)
                    {
                        string sql4 = "SELECT * FROM rooms WHERE room_type_id = " + roomType;
                        MySqlDataAdapter rooms = new MySqlDataAdapter(sql4, conn);
                        DataSet ds3 = new DataSet();
                        rooms.Fill(ds3);

                        if (ds.Tables[0].Rows.Count == ds3.Tables[0].Rows.Count)
                        {
                            var roomdata = ds.Tables[0];
                            resultModel.IsSucessful = false;
                            resultModel.Result = roomdata;
                        }
                        else
                        {
                            var roomdata = ds2.Tables[0];
                            resultModel.IsSucessful = true;
                            resultModel.Result = roomdata;
                        }

                    }
                }
                else
                {
                    string sql2 = "SELECT * FROM rooms WHERE room_type_id = " + roomType;
                    MySqlDataAdapter mydata2 = new MySqlDataAdapter(sql2, conn);
                    DataSet ds2 = new DataSet();
                    mydata2.Fill(ds2);

                    if (ds2.Tables.Count > 0 && ds2.Tables[0].Rows.Count > 0)
                    {
                        var roomdata = ds2.Tables[0];
                        resultModel.IsSucessful = true;
                        resultModel.Result = roomdata;
                    }

                }

            }
            catch (Exception ex)
            {
                resultModel.IsSucessful = false;
                resultModel.ErrorMessage = ex.ToString();
            }
            finally
            {
                conn.Close();
            }

            return resultModel;
        }

        public ResultModel BookRoom(int userId, int roomType, int roomId, String date, String startTime, String endTime)
        {
            var resultModel = new ResultModel();
            MySqlConnection conn = new MySqlConnection(connString);
            try
            {
                resultModel = this.checkAvailabilityFunction(roomType, date, startTime, endTime);

                if(resultModel.IsSucessful==true){
                    MySqlCommand cmd = new MySqlCommand("INSERT INTO bookings (user_id, room_type, room_id, date, start_time, end_time, status) VALUES " +
                                                    "('" + userId + "','" + roomType + "','" + roomId + "','" + date + "','" + startTime + "','" + endTime + "',1)", conn);


                    conn.Open();
                    cmd.ExecuteNonQuery();
                    resultModel.IsSucessful = true;
                } else{
                    resultModel.IsSucessful = false;
                }


            }
            catch (Exception ex)
            {
                resultModel.IsSucessful = false;
                resultModel.ErrorMessage = ex.ToString();
            }
            finally
            {
                conn.Close();
            }
            return resultModel;
        }

        public ResultModel DeleteBooking(int bookingId)
        {
            var resultModel = new ResultModel();
            MySqlConnection conn = new MySqlConnection(connString);
            try
            {
                MySqlCommand cmd = new MySqlCommand("update bookings set status=0 where id=" + bookingId, conn);
                conn.Open();
                cmd.ExecuteNonQuery();
                resultModel.IsSucessful = true;

            }
            catch (Exception ex)
            {
                resultModel.IsSucessful = false;
                resultModel.ErrorMessage = ex.ToString();
            }
            finally
            {
                conn.Close();
            }
            return resultModel;
        }

        #endregion
    }

}