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

namespace AgencySystem
{
    class DestinationsLayout : DatabaseLayout
    {
        public class Destinations
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public string CountryName { get; set; }
            public int CountryId { get; set; }
            public string Description { get; set; }
            public bool Recommended { get; set; }
        }

        /// <summary>
        /// Function gets all destinations from DB
        /// </summary>
        /// <returns></returns>
        public static Destinations[] GetDestinationsFromDB()
        {
            Destinations[] allDestinations = null;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand GetDestinations = myConnection.CreateCommand();
                GetDestinations.Connection = myConnection;
                GetDestinations.CommandText = "SELECT destinations.id as DestID, destinations.name as DestName, destinations.countryId, countries.name as CounName, countries.id, countries.id as CounID FROM destinations, countries WHERE destinations.countryId=countries.id ORDER BY DestName ASC";

                using (MySqlDataReader reader = GetDestinations.ExecuteReader())
                {
                    var list = new List<Destinations>();
                    while (reader.Read())
                    {
                        list.Add(new Destinations
                        {
                            Id = Convert.ToInt16(reader["DestID"]),
                            Name = reader["DestName"].ToString(),
                            CountryName = reader["CounName"].ToString(),
                            CountryId = Convert.ToInt16(reader["CounID"]),
                        });
                    }
                    allDestinations = list.ToArray();
                }
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return allDestinations;
        }

        /// <summary>
        /// Function gets number of properties (properties, restaurants, entertainment) assigned to destinationID from database
        /// </summary>
        /// <param name="destinationID"></param>
        /// <returns></returns>
        public static int GetAllByDestinationID(int destinationID)
        {
            int count = 0;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand NumberOfAll = myConnection.CreateCommand();
                NumberOfAll.Connection = myConnection;
                NumberOfAll.CommandText = "SELECT (select count(*) from entertainment where destinationId=@destinationId) +(select count(*) from properties where destinationId=@destinationId)+(select count(*) from restaurants where destinationId=@destinationId)";
                NumberOfAll.Parameters.AddWithValue("@destinationId", destinationID);
                count = int.Parse(NumberOfAll.ExecuteScalar().ToString());
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return count;
        }



        public static Destinations[] GetDestinationDataFromDbById(int destinationId)
        {
            Destinations[] destination = null;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand GetDestination = myConnection.CreateCommand();
                GetDestination.Connection = myConnection;
                GetDestination.CommandText = "SELECT description,recommended FROM destinations WHERE Id=@destinationId";
                GetDestination.Parameters.AddWithValue("@destinationId", destinationId);

                using (MySqlDataReader reader = GetDestination.ExecuteReader())
                {
                    var list = new List<Destinations>();
                    while (reader.Read())
                    {
                        list.Add(new Destinations
                        {
                            Description = reader["Description"].ToString(),
                            Recommended = Convert.ToBoolean(reader["Recommended"])
                        });
                    }
                    destination = list.ToArray();
                }
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return destination;
        }

        /// <summary>
        /// Function to edit destination in database
        /// </summary>
        /// <param name="destinationId"></param>
        /// <param name="destinationNewName"></param>
        /// /// <param name="destinationDescription"></param>
        /// /// <param name="destinationRecommended"></param>
        /// <param name="countryId"></param>
        /// <returns></returns>
        public static int EditDestination(int destinationId, string destinationNewName, string destinationDescription, bool destinationRecommended, int countryId)
        {
            int status = 0;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand EditDestination = myConnection.CreateCommand();
                EditDestination.Connection = myConnection;
                EditDestination.CommandText = "UPDATE destinations SET name=@name, countryID=@countryId, description=@description, recommended=@recommended WHERE id=@id";
                EditDestination.Parameters.AddWithValue("@name", destinationNewName);
                EditDestination.Parameters.AddWithValue("@description", destinationDescription);
                EditDestination.Parameters.AddWithValue("@recommended", destinationRecommended);
                EditDestination.Parameters.AddWithValue("@countryId", countryId);
                EditDestination.Parameters.AddWithValue("@id", destinationId);
                status = EditDestination.ExecuteNonQuery();
                DefaultScreen.StatusLabel.Text = DateTime.Now + " - " + Properties.Resources.DestinationEditedSuccessfully;
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return status;
        }

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

        /// <summary>
        /// Function to add new destination to database
        /// </summary>
        /// <param name="name"></param>
        /// <param name="CountryId"></param>
        /// <returns></returns>
        public static int AddDestination(string name, string description, bool recommended, int CountryId)
        {
            int status = 0;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand addNewDestination = myConnection.CreateCommand();
                addNewDestination.Connection = myConnection;
                addNewDestination.CommandText = "INSERT INTO destinations (countryId, Name, Description, Recommended) VALUES (@countryId, @Name, @Description, @Recommended)";
                addNewDestination.Parameters.AddWithValue("@countryId", CountryId);
                addNewDestination.Parameters.AddWithValue("@Name", name);
                addNewDestination.Parameters.AddWithValue("@Description", description);
                addNewDestination.Parameters.AddWithValue("@Recommended", recommended);
                status = addNewDestination.ExecuteNonQuery();
                DefaultScreen.StatusLabel.Text = DateTime.Now + " - " + Properties.Resources.DestinationAddedSuccessfully;
            }
            catch (Exception e)
            {
                DefaultScreen.StatusLabel.Text = DateTime.Now + " - " + e.ToString();
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return status;
        }

        /// <summary>
        /// Function for getting number of all desinations assigned under countryID
        /// </summary>
        /// <param name="countryID"></param>
        /// <returns></returns>
        public static int GetDestinationsByCountryID(int countryID)
        {
            int count = 1;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand NumberOfDestinations = myConnection.CreateCommand();
                NumberOfDestinations.Connection = myConnection;
                NumberOfDestinations.CommandText = "SELECT count(*) FROM Destinations WHERE countryID=@countryID";
                NumberOfDestinations.Parameters.AddWithValue("@countryID", countryID);
                count = int.Parse(NumberOfDestinations.ExecuteScalar().ToString());
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return count;
        }

        /// <summary>
        /// Function to get countryId from database by destinationId record
        /// </summary>
        /// <param name="destinationId"></param>
        /// <returns></returns>
        public static int GetCountryIdByDestinationId(int destinationId)
        {
            int countryId = 0;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand GetCountryId = myConnection.CreateCommand();
                GetCountryId.Connection = myConnection;
                GetCountryId.CommandText = "SELECT countryId FROM destinations WHERE destinations.Id=@destinationId LIMIT 1";
                GetCountryId.Parameters.AddWithValue("@destinationId", destinationId);

                using (MySqlDataReader reader = GetCountryId.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        countryId = Convert.ToInt16(reader["countryId"]);
                    }
                }
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return countryId;
        }
    }
}
