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

namespace AgencySystem
{
    class CountriesLayout : DatabaseLayout
    {
        public class Countries
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public string Description { get; set; }
        }
        
        
        /// <summary>
        /// Function to get all countries from DB
        /// </summary>
        /// <returns></returns>
        public static Countries[] GetCountriesFromDB()
        {
            Countries[] allCountries = null;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand GeCountries = myConnection.CreateCommand();
                GeCountries.Connection = myConnection;
                GeCountries.CommandText = "SELECT * FROM countries ORDER BY Name ASC";

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

        /// <summary>
        /// Function to retreive discription from  database
        /// </summary>
        /// <param name="name">countryId</param>
        /// <returns>descriptionFromDB</returns>
        public static string GetCountryDescription(int countryId)
        {
            string descriptionFromDB = "";
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand GetDescription = myConnection.CreateCommand();
                GetDescription.Connection = myConnection;
                GetDescription.CommandText = "SELECT Description FROM countries WHERE id=@countryId";
                GetDescription.Parameters.AddWithValue("@countryId", countryId);

                MySqlDataReader reader = GetDescription.ExecuteReader();
                while (reader.Read())
                {
                    descriptionFromDB = (string)reader["Description"];
                }
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return descriptionFromDB;
        }

        /// <summary>
        /// Function to add new country to the database
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static int AddCountry(string name, string description)
        {
            int status = 0;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand addNewCountry = myConnection.CreateCommand();
                addNewCountry.Connection = myConnection;
                addNewCountry.CommandText = "INSERT INTO countries (Name, Description) VALUES (@Name, @Description)";
                addNewCountry.Parameters.AddWithValue("@Name", name);
                addNewCountry.Parameters.AddWithValue("@Description", description);
                status = addNewCountry.ExecuteNonQuery();
                DefaultScreen.StatusLabel.Text = DateTime.Now + " - " + Properties.Resources.CountriesUpdatedSuccessfully;
            }
            catch (Exception e)
            {
                DefaultScreen.StatusLabel.Text = DateTime.Now + " - " + e.ToString();
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return status;
        }

        /// <summary>
        /// Function to edit country name in database
        /// </summary>
        /// <param name="countryId"></param>
        /// <param name="countryNewName"></param>
        /// /// <param name="countryNewDescription"></param>
        /// <returns></returns>
        public static int EditCountry(int countryId, string countryNewName, string countryNewDescription)
        {
            int status = 0;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand EditCountry = myConnection.CreateCommand();
                EditCountry.Connection = myConnection;
                EditCountry.CommandText = "UPDATE countries SET name=@name, description=@description WHERE id=@id";
                EditCountry.Parameters.AddWithValue("@name", countryNewName);
                EditCountry.Parameters.AddWithValue("@description", countryNewDescription);
                EditCountry.Parameters.AddWithValue("@id", countryId);
                status = EditCountry.ExecuteNonQuery();
                DefaultScreen.StatusLabel.Text = DateTime.Now + " - " + Properties.Resources.CountryEditedSuccessfully;
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return status;
        }

        /// <summary>
        /// Function to delete country from database
        /// </summary>
        /// <param name="countryId"></param>
        /// <returns></returns>
        public static int DeleteCountry(int countryId)
        {
            int status = 0;
            try
            {
                ConnectionToDatabaseOpen();
                MySqlCommand DeleteCountry = myConnection.CreateCommand();
                DeleteCountry.Connection = myConnection;
                DeleteCountry.CommandText = "DELETE FROM countries WHERE Id=@Id";
                DeleteCountry.Parameters.AddWithValue("@Id", countryId);
                status = DeleteCountry.ExecuteNonQuery();
                // DefaultScreen.StatusLabel.Text = DateTime.Now + " - " + Properties.Resources.CountryDeletedSuccessfully;
            }
            catch (Exception e)
            {
                Default.ErrorReporting(e);
            }
            finally
            {
                ConnectionToDatabaseClose();
            }
            return status;
        }
    }
}
