using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;

namespace AgencySystem
{
    class RestaurantsLayout : DatabaseLayout
    {
        public class Restaurants
        {
            public int Id { get; set; }
            public string CountryName { get; set; }
            public int DestinationId { get; set; }
            public string DestinationName { get; set; }
            public string Name { get; set; }
            public string Description { get; set; }
            public string Street { get; set; }
            public string StreetNumber { get; set; }
            public int Postcode { get; set; }
            public double Latitude { get; set; }
            public double Longtitude { get; set; }
            public string coverImageURL { get; set; }
            public bool Recommended { get; set; }
        }

        /// <summary>
        /// Function to get all restaurants from database
        /// </summary>
        /// <returns></returns>
        public static Restaurants[] GetRestaurantsFromDB()
        {
            Restaurants[] allRestaurants = null;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand GetRestaurants = myConnection.CreateCommand();
                GetRestaurants.Connection = myConnection;
                GetRestaurants.CommandText = "SELECT restaurants.*, destinations.Name as DescName, destinations.Id as DescId, countries.Name as CountName FROM restaurants, destinations, countries WHERE restaurants.destinationId=destinations.Id AND destinations.countryId=countries.Id ORDER BY restaurants.Name ASC";

                using (MySqlDataReader reader = GetRestaurants.ExecuteReader())
                {
                    var list = new List<Restaurants>();
                    while (reader.Read())
                    {
                        list.Add(new Restaurants
                        {
                            Id = Convert.ToInt16(reader["Id"]),
                            CountryName = reader["CountName"].ToString(),
                            DestinationId = Convert.ToInt16(reader["DescId"]),
                            DestinationName = reader["DescName"].ToString(),
                            Name = reader["Name"].ToString(),
                            Description = reader["Description"].ToString(),
                            Street = reader["Street"].ToString(),
                            StreetNumber = reader["StreetNumber"].ToString(),
                            Postcode = Convert.ToInt32(reader["Postcode"]),
                            Latitude = Convert.ToDouble(reader["Latitude"]),
                            Longtitude = Convert.ToDouble(reader["Longtitude"]),
                            coverImageURL = Convert.ToString(reader["imageURL"]),
                            Recommended = Convert.ToBoolean(reader["Recommended"])
                        });
                    }
                    allRestaurants = list.ToArray();
                }
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return allRestaurants;
        }


        /// <summary>
        /// Function to get full details of restaurant from database by restaurantId
        /// </summary>
        /// <param name="currentId"></param>
        /// <returns></returns>
        public static Restaurants[] GetRestaurantFromDbById(int currentId)
        {
            Restaurants[] currentRestaurant = null;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand GetRestaurants = myConnection.CreateCommand();
                GetRestaurants.Connection = myConnection;
                GetRestaurants.CommandText = "SELECT restaurants.*, destinations.Id as DescId, destinations.Name as DescName FROM restaurants, destinations WHERE restaurants.destinationId=destinations.Id AND restaurants.Id=@currentId";
                GetRestaurants.Parameters.AddWithValue("@currentId", currentId);

                using (MySqlDataReader reader = GetRestaurants.ExecuteReader())
                {
                    var list = new List<Restaurants>();
                    while (reader.Read())
                    {
                        list.Add(new Restaurants
                        {
                            Id = Convert.ToInt16(reader["Id"]),
                            CountryName = "",
                            DestinationId = Convert.ToInt16(reader["DescId"]),
                            DestinationName = reader["DescName"].ToString(),
                            Name = reader["Name"].ToString(),
                            Description = reader["Description"].ToString(),
                            Street = reader["Street"].ToString(),
                            StreetNumber = reader["StreetNumber"].ToString(),
                            Postcode = Convert.ToInt32(reader["Postcode"]),
                            Latitude = Convert.ToDouble(reader["Latitude"]),
                            Longtitude = Convert.ToDouble(reader["Longtitude"]),
                            coverImageURL = Convert.ToString(reader["imageURL"]),
                            Recommended = Convert.ToBoolean(reader["Recommended"])
                        });
                    }
                    currentRestaurant = list.ToArray();
                }
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return currentRestaurant;
        }

        /// <summary>
        /// Function to delete restaurant from database
        /// </summary>
        /// <param name="restaurantId"></param>
        /// <returns></returns>
        public static int DeleteRestaurant(int restaurantId)
        {
            int status = 0;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand DeleteRestaurant = myConnection.CreateCommand();
                DeleteRestaurant.Connection = myConnection;
                DeleteRestaurant.CommandText = "DELETE FROM restaurants WHERE Id=@restaurantId";
                DeleteRestaurant.Parameters.AddWithValue("@restaurantId", restaurantId);
                status = DeleteRestaurant.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return status;
        }

        /// <summary>
        /// Function to add new restaurant to database
        /// </summary>
        /// <param name="restaurantDestinationId"></param>
        /// <param name="restaurantName"></param>
        /// <param name="restaurantStreetName"></param>
        /// <param name="restaurantStreetNumber"></param>
        /// <param name="restaurantPostcode"></param>
        /// <param name="restaurantLatitude"></param>
        /// <param name="restaurantLongtitude"></param>
        /// <param name="restaurantDescription"></param>
        /// <param name="restaurantCoverImage"></param>
        /// <returns></returns>
        public static int AddRestaurant(int restaurantDestinationId, string restaurantName, string restaurantStreetName, string restaurantStreetNumber, string restaurantPostcode, string restaurantLatitude, string restaurantLongtitude, string restaurantDescription, string restaurantCoverImage, bool restaurantRecommended)
        {
            int status = 0;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand addNewRestaurant = myConnection.CreateCommand();
                addNewRestaurant.Connection = myConnection;
                addNewRestaurant.CommandText = "INSERT INTO restaurants (destinationId, Name, Description, Street, StreetNumber, Postcode, Latitude, Longtitude, imageURL, Recommended) VALUES (@destinationId, @Name, @Description, @Street, @StreetNumber, @Postcode, @Latitude, @Longtitude, @imageURL, @Recommended)";
                addNewRestaurant.Parameters.AddWithValue("@destinationId", restaurantDestinationId);
                addNewRestaurant.Parameters.AddWithValue("@Name", restaurantName);
                addNewRestaurant.Parameters.AddWithValue("@Description", restaurantDescription);
                addNewRestaurant.Parameters.AddWithValue("@Street", restaurantStreetName);
                addNewRestaurant.Parameters.AddWithValue("@StreetNumber", restaurantStreetNumber);
                addNewRestaurant.Parameters.AddWithValue("@Postcode", restaurantPostcode);
                addNewRestaurant.Parameters.AddWithValue("@Latitude", Convert.ToDouble(restaurantLatitude));
                addNewRestaurant.Parameters.AddWithValue("@Longtitude", Convert.ToDouble(restaurantLongtitude));
                addNewRestaurant.Parameters.AddWithValue("@imageURL", Convert.ToString(restaurantCoverImage));
                addNewRestaurant.Parameters.AddWithValue("@recommended", Convert.ToBoolean(restaurantRecommended));

                status = addNewRestaurant.ExecuteNonQuery();
                DefaultScreen.StatusLabel.Text = DateTime.Now + " - " + Properties.Resources.RestaurantAddedSuccessfully;
            }
            catch (Exception e)
            {
                DefaultScreen.StatusLabel.Text = DateTime.Now + " - " + e.ToString();
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return status;
        }

        /// <summary>
        /// Function to edit restaurant in database
        /// </summary>
        /// <param name="restaurantId"></param>
        /// <param name="restaurantDestinationId"></param>
        /// <param name="restaurantName"></param>
        /// <param name="restaurantStreetName"></param>
        /// <param name="restaurantStreetNumber"></param>
        /// <param name="restaurantPostcode"></param>
        /// <param name="restaurantLatitude"></param>
        /// <param name="restaurantLongtitude"></param>
        /// <param name="restaurantDescription"></param>
        /// <returns></returns>
        public static int EditRestaurant(int restaurantId, int restaurantDestinationId, string restaurantName, string restaurantStreetName, string restaurantStreetNumber, string restaurantPostcode, string restaurantLatitude, string restaurantLongtitude, string restaurantDescription, string restaurantCoverImage, bool restaurantRecommended)
        {
            int status = 0;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand EditRestaurant = myConnection.CreateCommand();
                EditRestaurant.Connection = myConnection;
                EditRestaurant.CommandText = "UPDATE restaurants SET destinationId=@destinationId, Name=@Name, Description=@Description, Street=@Street, StreetNumber=@StreetNumber, Postcode=@Postcode,Latitude=@Latitude, Longtitude=@Longtitude, imageURL=@imageURL, Recommended=@recommended WHERE Id=@restaurantId";
                EditRestaurant.Parameters.AddWithValue("@restaurantId", restaurantId);
                EditRestaurant.Parameters.AddWithValue("@destinationId", restaurantDestinationId);
                EditRestaurant.Parameters.AddWithValue("@Name", restaurantName);
                EditRestaurant.Parameters.AddWithValue("@Description", restaurantDescription);
                EditRestaurant.Parameters.AddWithValue("@Street", restaurantStreetName);
                EditRestaurant.Parameters.AddWithValue("@StreetNumber", restaurantStreetNumber);
                EditRestaurant.Parameters.AddWithValue("@Postcode", restaurantPostcode);
                EditRestaurant.Parameters.AddWithValue("@Latitude", Convert.ToDouble(restaurantLatitude));
                EditRestaurant.Parameters.AddWithValue("@Longtitude", Convert.ToDouble(restaurantLongtitude));
                EditRestaurant.Parameters.AddWithValue("@imageURL", Convert.ToString(restaurantCoverImage));
                EditRestaurant.Parameters.AddWithValue("@recommended", Convert.ToBoolean(restaurantRecommended));
                
                status = EditRestaurant.ExecuteNonQuery();
                DefaultScreen.StatusLabel.Text = DateTime.Now + " - " + Properties.Resources.RestaurantEditedSuccessfully;
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return status;
        }
    }
}
