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

namespace AgencySystem
{
    class POILayout : DatabaseLayout
    {
        public class POI
        {
            public int Id { get; set; }
            public string CountryName { get; set; }
            public int DestinationId { get; set; }
            public string DestinationName { get; set; }
            public int POITypeId { get; set; }
            public string POITypeName { 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 class POITypes
        {
            public int Id { get; set; }
            public string Name { get; set; }
        }

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

                using (MySqlDataReader reader = GetPOI.ExecuteReader())
                {
                    var list = new List<POI>();
                    while (reader.Read())
                    {
                        list.Add(new POI
                        {
                            Id = Convert.ToInt16(reader["Id"]),
                            CountryName = reader["CountName"].ToString(),
                            DestinationId = Convert.ToInt16(reader["DescId"]),
                            DestinationName = reader["DescName"].ToString(),
                            POITypeId = Convert.ToInt16(reader["typeId"]),
                            POITypeName = reader["TypeName"].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"])
                        });
                    }
                    allPOI = list.ToArray();
                }
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return allPOI;
        }

        /// <summary>
        /// Function to get all POI types from database
        /// </summary>
        /// <returns></returns>
        public static POITypes[] GetPOITypeFromDB()
        {
            POITypes[] allPOI = null;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand GetPOI = myConnection.CreateCommand();
                GetPOI.Connection = myConnection;
                GetPOI.CommandText = "SELECT * FROM poitypes ORDER BY Name ASC";

                using (MySqlDataReader reader = GetPOI.ExecuteReader())
                {
                    var list = new List<POITypes>();
                    while (reader.Read())
                    {
                        list.Add(new POITypes
                        {
                            Id = Convert.ToInt16(reader["Id"]),
                            Name = reader["Name"].ToString()
                        });
                    }
                    allPOI = list.ToArray();
                }
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return allPOI;
        }


        /// <summary>
        /// Function to get full details of POI from database by POIId
        /// </summary>
        /// <param name="currentId"></param>
        /// <returns></returns>
        public static POI[] GetPOIFromDbById(int currentId)
        {
            POI[] currentPOI = null;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand GetPOI = myConnection.CreateCommand();
                GetPOI.Connection = myConnection;
                GetPOI.CommandText = "SELECT POI.*, poitypes.Name as TypeName, destinations.Id as DescId, destinations.Name as DescName FROM POI, destinations, poitypes WHERE POI.destinationId=destinations.Id AND poi.typeId=poitypes.Id AND POI.Id=@currentId";
                GetPOI.Parameters.AddWithValue("@currentId", currentId);

                using (MySqlDataReader reader = GetPOI.ExecuteReader())
                {
                    var list = new List<POI>();
                    while (reader.Read())
                    {
                        list.Add(new POI
                        {
                            Id = Convert.ToInt16(reader["Id"]),
                            CountryName = "",
                            DestinationId = Convert.ToInt16(reader["DescId"]),
                            DestinationName = reader["DescName"].ToString(),
                            POITypeId = Convert.ToInt16(reader["typeId"]),
                            POITypeName = reader["TypeName"].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"])
                        });
                    }
                    currentPOI = list.ToArray();
                }
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return currentPOI;
        }

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

        /// <summary>
        /// Function to add new POI to database
        /// </summary>
        /// <param name="POIDestinationId"></param>
        /// <param name="POITypeId"></param>
        /// <param name="POIName"></param>
        /// <param name="POItreetName"></param>
        /// <param name="POItreetNumber"></param>
        /// <param name="POIPostcode"></param>
        /// <param name="POILatitude"></param>
        /// <param name="POILongtitude"></param>
        /// <param name="POIDescription"></param>
        /// <returns></returns>
        public static int AddPOI(int POIDestinationId, int POITypeId, string POIName, string POItreetName, string POItreetNumber, string POIPostcode, string POILatitude, string POILongtitude, string POIDescription)
        {
            int status = 0;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand addNewPOI = myConnection.CreateCommand();
                addNewPOI.Connection = myConnection;
                addNewPOI.CommandText = "INSERT INTO POI (destinationId, typeId, Name, Description, Street, StreetNumber, Postcode, Latitude, Longtitude) VALUES (@destinationId,@typeId, @Name, @Description, @Street, @StreetNumber, @Postcode, @Latitude, @Longtitude)";
                addNewPOI.Parameters.AddWithValue("@destinationId", POIDestinationId);
                addNewPOI.Parameters.AddWithValue("@typeId", POITypeId);
                addNewPOI.Parameters.AddWithValue("@Name", POIName);
                addNewPOI.Parameters.AddWithValue("@Description", POIDescription);
                addNewPOI.Parameters.AddWithValue("@Street", POItreetName);
                addNewPOI.Parameters.AddWithValue("@StreetNumber", POItreetNumber);
                addNewPOI.Parameters.AddWithValue("@Postcode", POIPostcode);
                addNewPOI.Parameters.AddWithValue("@Latitude", Convert.ToDouble(POILatitude));
                addNewPOI.Parameters.AddWithValue("@Longtitude", Convert.ToDouble(POILongtitude));

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

        /// <summary>
        /// Function to edit POI in database
        /// </summary>
        /// <param name="POIId"></param>
        /// <param name="POIDestinationId"></param>
        /// <param name="POITypeId"></param>
        /// <param name="POIName"></param>
        /// <param name="POItreetName"></param>
        /// <param name="POItreetNumber"></param>
        /// <param name="POIPostcode"></param>
        /// <param name="POILatitude"></param>
        /// <param name="POILongtitude"></param>
        /// <param name="POIDescription"></param>
        /// <returns></returns>
        public static int EditPOI(int POIId, int POIDestinationId, int POITypeId, string POIName, string POItreetName, string POItreetNumber, string POIPostcode, string POILatitude, string POILongtitude, string POIDescription)
        {
            int status = 0;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand EditPOI = myConnection.CreateCommand();
                EditPOI.Connection = myConnection;
                EditPOI.CommandText = "UPDATE POI SET destinationId=@destinationId,typeId=@typeId, Name=@Name, Description=@Description, Street=@Street, StreetNumber=@StreetNumber, Postcode=@Postcode,Latitude=@Latitude, Longtitude=@Longtitude WHERE Id=@POIId";
                EditPOI.Parameters.AddWithValue("@POIId", POIId);
                EditPOI.Parameters.AddWithValue("@destinationId", POIDestinationId);
                EditPOI.Parameters.AddWithValue("@typeId", POITypeId);
                EditPOI.Parameters.AddWithValue("@Name", POIName);
                EditPOI.Parameters.AddWithValue("@Description", POIDescription);
                EditPOI.Parameters.AddWithValue("@Street", POItreetName);
                EditPOI.Parameters.AddWithValue("@StreetNumber", POItreetNumber);
                EditPOI.Parameters.AddWithValue("@Postcode", POIPostcode);
                EditPOI.Parameters.AddWithValue("@Latitude", Convert.ToDouble(POILatitude));
                EditPOI.Parameters.AddWithValue("@Longtitude", Convert.ToDouble(POILongtitude));
                
                status = EditPOI.ExecuteNonQuery();
                DefaultScreen.StatusLabel.Text = DateTime.Now + " - " + Properties.Resources.POIEditedSuccessfully;
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return status;
        }
    }
}
