using System;
using System.Collections.Generic;
using System.Linq;

namespace HumaneSociety
{
    public static class Query
    {
        private static HumaneSocietyDataContext db = new HumaneSocietyDataContext();

        /// <summary>
        /// A method to read the employee from the employee table
        /// </summary>
        /// <param name="employee"></param>
        internal static void ReadEmployee(Employee employee)
        {
            //Console.WriteLine("Retrieving Employee");
            employee = db.Employees.Where(e => e.EmployeeNumber == employee.EmployeeNumber).Single();
            UserInterface.DisplayUserOptions("Name: " + employee.FirstName + " " + employee.LastName);
            UserInterface.DisplayUserOptions("Username: " + employee.UserName);
            UserInterface.DisplayUserOptions("Password: " + employee.Password);
            UserInterface.DisplayUserOptions("Email: " + employee.Email);
            UserInterface.DisplayUserOptions("Employee Number: " + employee.EmployeeNumber);
            UserInterface.DisplayUserOptions("Assigned Animals: ");
            foreach (Animal a in employee.Animals)
            {
                UserInterface.DisplayUserOptions(a.Name);
            }
        }

        /// <summary>
        /// A method to delete an employee from the employee table
        /// </summary>
        /// <param name="employee"></param>
        internal static void DeleteEmployee(Employee employee)
        {
            Employee target = db.Employees.Where(e => e.EmployeeNumber == employee.EmployeeNumber && e.LastName == employee.LastName).Single();
            Console.WriteLine("You are about to delete {0} {1}, Employee Number: {2}. Are you sure? (y/n)", target.FirstName, target.LastName, target.EmployeeNumber);
            if (Console.ReadKey().KeyChar == 'y')
            {
                db.Employees.DeleteOnSubmit(target);
                db.SubmitChanges();
                //Console.WriteLine("Employee deleted.");
            }
            else
            {
                Console.WriteLine("Delete aborted.");
            }
            Console.WriteLine("Press any key to return to the previous menu.");
            Console.ReadKey(true);
        }

        internal static void UpdateEmployee(int employeeNumber)
        {
            Employee target = db.Employees.Where(e => e.EmployeeNumber == employeeNumber).Single();
            Console.WriteLine("Current values:");
            ReadEmployee(target);
            Console.WriteLine("Which field would you like to change?");
            Console.ReadLine();
        }

        internal static bool EmployeeNumberIsAlreadyInUse(int? employeeNumber)
        {
            try
            {
                Employee test = db.Employees.Where(e => e.EmployeeNumber == employeeNumber).Single();
                Console.WriteLine("Employee Number {0} is already in use", employeeNumber);
                Console.WriteLine("Press any key to return to previous menu.");
                Console.ReadKey(true);
                return true;
            }
            catch (Exception)
            {
                Console.WriteLine("Valid number");
                return false;
            }
        }

        /// <summary>
        /// A method to insert an employee into employee table
        /// </summary>
        /// <param name="employee"></param>
        internal static void CreateEmployee(Employee employee)
        {
            db.Employees.InsertOnSubmit(employee);
            db.SubmitChanges();
        }

        //A method to retrieve a client from the clients table
        internal static Client GetClient(string userName, string password)
        {
            Client outputClient = db.Clients.Where(u => u.UserName == userName && u.Password == password).Single();
            return outputClient;
        }

        /// <summary>
        /// A method to return an enumerable list of animals available for adoption
        /// </summary>
        /// <param name="client"></param>
        /// <returns></returns>
        internal static IQueryable<Adoption> GetUserAdoptionStatus(Client client)
        {
            //SELECT * FROM ADOPTIONS WHERE CLIENTID = ""
            return db.Adoptions.Where(a => a.ClientId == client.ClientId);
        }

        /// <summary>
        /// A method to get an animal based on ID
        /// </summary>
        /// <param name="iD"></param>
        /// <returns></returns>
        internal static Animal GetAnimalByID(int iD)
        {
            //SELECT FROM ANIMALS WHERE ANIMALID = ""
            return db.Animals.Where(x => x.AnimalId == iD).Single();
        }

        internal static void Adopt(Animal animal, Client client)
        {
            animal.AdoptionStatus = "Pending";
            Adoption adoption = new Adoption();
            adoption.Client = client;
            adoption.Animal = animal;
            adoption.ApprovalStatus = "Pending";
            adoption.AdoptionFee = 75;
            adoption.PaymentCollected = true;

            db.Adoptions.InsertOnSubmit(adoption);
            db.SubmitChanges();

        }

        internal static IQueryable<Animal> SearchForAnimalByMultipleTraits()
        {
            //SELECT FROM ANIMALS WHERE ANIMALS
            return db.Animals.Where(x => true);
        }

        internal static IQueryable<Client> RetrieveClients()
        {
            return db.Clients;
        }

        internal static IQueryable<USState> GetStates()
        {
            return db.USStates;
        }

        internal static void AddNewClient(string firstName, string lastName, string username, string password, string email, string streetAddress, int zipCode, int state)
        {
            Address address = new Address();
            address.AddressLine1 = streetAddress;
            address.Zipcode = zipCode;
            address.USStateId = state;
            db.Addresses.InsertOnSubmit(address);
            db.SubmitChanges();

            Client client = new Client();
            client.FirstName = firstName;
            client.LastName = lastName;
            client.UserName = username;
            client.Password = password;
            client.Email = email;
            client.AddressId = address.AddressId;
            db.Clients.InsertOnSubmit(client);
            db.SubmitChanges();
        }

        internal static void UpdateClient(Client client)
        {
            db.SubmitChanges();
        }

        internal static IQueryable<Adoption> GetPendingAdoptions()
        {
            throw new NotImplementedException();
        }

        internal static void RemoveAnimal(Animal animal)
        {
            db.Animals.DeleteOnSubmit(animal);
            db.SubmitChanges();
        }

        /// <summary>
        /// A method to get species as a list
        /// </summary>
        /// <returns></returns>
        internal static Species GetSpecies()
        {
            List<Species> listingOfSpecies = new List<Species>();
            foreach (Species s in db.Species)
            {
                listingOfSpecies.Add(s);
            }
            Species newSpecies;
            DisplaySpeciesOptions();
            string input = UserInterface.GetStringData("menu number", "the species");

            try
            {
                if (Int32.Parse(input) - 1 >= listingOfSpecies.Count)
                {
                    return CreateSpecies();
                }
                else
                {
                    newSpecies = listingOfSpecies[Int32.Parse(input) - 1];
                    return newSpecies;
                }
            }
            catch (Exception)
            {
                Console.Clear();
                Console.WriteLine("Incorrect input please try again! \n");
                GetSpecies();
            }

            return null;
        }

        /// <summary>
        /// A method to display the species options
        /// </summary>
        internal static void DisplaySpeciesOptions()
        {
            int counter = 1;
            Console.WriteLine("Please select the animal's species: \n");

            foreach (Species s in db.Species)
            {
                Console.WriteLine(counter + "- " + s.Name + "\n");
                counter++;
            }
            Console.WriteLine((counter) + "- Species Not Listed \n");
        }

        /// <summary>
        /// A method to create the species
        /// </summary>
        /// <returns></returns>
        internal static Species CreateSpecies()
        {
            Species newSpecies = new Species();
            newSpecies.Name = UserInterface.GetStringData("the species", "the name of");
            db.Species.InsertOnSubmit(newSpecies);
            db.SubmitChanges();
            return newSpecies;
        }

        /// <summary>
        /// A method to get the diet plans
        /// </summary>
        /// <returns></returns>
        internal static DietPlan GetDietPlan()
        {
            DisplayDietPlans();
            string dietName = UserInterface.GetStringData("name", "the desired diet plan");
            return db.DietPlans.Where(d => d.Name == dietName).Single();
        }


        /// <summary>
        /// A method to get display the diet plans
        /// </summary>
        internal static void DisplayDietPlans()
        {
            UserInterface.DisplayUserOptions("Available Diet Plans: ");
            foreach (DietPlan d in db.DietPlans.Where(p => true))
            {
                UserInterface.DisplayUserOptions("- " + d.Name);
            }
        }

        /// <summary>
        /// A method to add an animal
        /// </summary>
        /// <param name="animal"></param>
        internal static void AddAnimal(Animal animal)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// A method to login to the database
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        internal static Employee EmployeeLogin(string userName, string password)
        {
            //SELECT FROM EMPLOYEES WHERE USERNAME = "" AND PASSWORD = ""
            var Employee = db.Employees.Where(x => x.UserName == userName && x.Password == password);
            return Employee.Single();
        }

        /// <summary>
        /// A method to get the employee login information
        /// </summary>
        /// <param name="email"></param>
        /// <param name="employeeNumber"></param>
        /// <returns></returns>
        internal static Employee RetrieveEmployeeUser(string email, int? employeeNumber)
        {
            //SELECT FROM EMPLOYEES WHERE EMAIL = "" AND EMPLOYEENUMBER = ""
            var Employee = db.Employees.Where(e => e.Email == email && e.EmployeeNumber == employeeNumber);
            return Employee.Single();
        }

        /// <summary>
        /// A method to update the adoption status
        /// </summary>
        /// <param name="v"></param>
        /// <param name="adoption"></param>
        internal static void UpdateAdoption(bool v, Adoption adoption)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// A method to get a room for an animal
        /// </summary>
        /// <param name="animalId"></param>
        /// <returns></returns>
        internal static Room GetRoom(int animalId)
        {
            //SELECT FROM ROOMS WHERE ANIMALID = ""
            return db.Rooms.Where(r => r.AnimalId == animalId).Single();
        }

        /// <summary>
        /// A method to add an employee user account
        /// </summary>
        /// <param name="employee"></param>
        internal static void AddUsernameAndPassword(Employee employee)
        {
            //SELECT FROM EMPLOYEE WHERE EMAIL = "" AND EMPLOYEENUMBER = ""
            RetrieveEmployeeUser(employee.Email, employee.EmployeeNumber).UserName = employee.UserName;
            RetrieveEmployeeUser(employee.Email, employee.EmployeeNumber).Password = employee.Password;
            db.SubmitChanges();
        }

        /// <summary>
        /// A method to check if employee username exists in employee table
        /// </summary>
        /// <param name="username"></param>
        /// <returns></returns>
        internal static bool CheckEmployeeUserNameExist(string username)
        {
            //SELECT FROM EMPLOYEE WHERE USERNAME = ""
            var nameExists = db.Employees.Where(e => e.UserName == username);
            if (nameExists.Count() > 0)
            {
                return true;
            }
            return false;
        }

        internal static IQueryable<AnimalShot> GetShots(Animal animal)
        {
            var shots = db.AnimalShots.Where(a => a.AnimalId == animal.AnimalId);
            return shots;
        }

        internal static void UpdateShot(string v, Animal animal)
        {
            AnimalShot vaccinatedAnimal = new AnimalShot();
            if (CheckShot(v))
            {
                vaccinatedAnimal.Animal = animal;

                vaccinatedAnimal.DateReceived = DateTime.Now;
                vaccinatedAnimal.Shot = db.Shots.Where(s => s.Name == v).Single();
                db.AnimalShots.InsertOnSubmit(vaccinatedAnimal);
                db.SubmitChanges();
            }
            else
            {
                Console.WriteLine("Shot does not exist! \n");
            }
        }

        internal static bool CheckShot(string shotName)
        {
            bool shotExists = false;
            foreach (Shot s in db.Shots)
            {
                if (s.Name == shotName)
                {
                    shotExists = true;
                }
            }
            return shotExists;
        }

        /// <summary>
        /// A method to enter update an animal into a record book
        /// </summary>
        /// <param name="animal"></param>
        /// <param name="updates"></param>
        internal static void EnterUpdate(Animal animal, Dictionary<int, string> updates)
        {
            foreach (KeyValuePair<int, string> entry in updates)
            {
                switch (entry.Key)
                {
                    case 1:
                        //SELECT FROM SPECIES WHERE NAME = ""
                        animal.Species = db.Species.Where(s => s.Name == entry.Value).Single();
                        break;
                    case 2:
                        animal.Name = entry.Value;
                        break;
                    case 3:
                        animal.Age = Int32.Parse(entry.Value);
                        break;
                    case 4:
                        animal.Demeanor = entry.Value;
                        break;
                    case 5:
                        if (entry.Value.ToLower() == "true")
                        {
                            animal.KidFriendly = true;
                        }
                        else
                        {
                            animal.KidFriendly = false;
                        }
                        break;
                    case 6:
                        if (entry.Value.ToLower() == "true")
                        {
                            animal.PetFriendly = true;
                        }
                        else
                        {
                            animal.PetFriendly = false;
                        }
                        break;
                    case 7:
                        animal.Weight = Int32.Parse(entry.Value);
                        break;
                    default:
                        break;
                }
            }
            db.SubmitChanges();
        }

        /// <summary>
        /// A method to change animal room
        /// </summary>
        /// <param name="animal"></param>
        internal static void ChangeAnimalRoom(Animal animal)
        {
            //SELECT FROM ROOMS WHERE ANIMAL = "" AND ANIMALID = NULL
            db.Rooms.Where(r => r.Animal == animal).Single().AnimalId = null;
            DisplayAvailableRooms();
            int newRoomNumber = UserInterface.GetIntegerData("room number", "the new");
            db.Rooms.Where(r => r.RoomNumber == newRoomNumber).Single().AnimalId = animal.AnimalId;
            db.SubmitChanges();
        }

        /// <summary>
        /// A method to show available rooms
        /// </summary>
        internal static void DisplayAvailableRooms()
        {
            //SELECT FROM ROOMS WHERE ANIMALID = 0 AND ANIMALID = NULL
            var rooms = db.Rooms.Where(r => true);
            UserInterface.DisplayUserOptions("Rooms Available: ");
            foreach (Room r in rooms)
            {
                if (r.AnimalId == 0 || r.AnimalId == null)
                {
                    UserInterface.DisplayUserOptions(r.RoomNumber + ", ");
                }
            }
            Console.WriteLine("\n");
        }
    }
}