using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.Sql;
using System.Data;

namespace KillerApp
{
    class Database
    {
        private static string connectionString = "Data Source=127.0.0.1;Initial Catalog=KillerApp;User ID=PT12admin;Password=jestin123";
        private SqlConnection connection = new SqlConnection(connectionString);

        /// <summary>
        /// Method for checking correct login credentials (logging in)
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool Login(string userName, string password)
        {
            //Query to check if the filled-in username corresponds with the filled-in password
            bool correctLogin = false;
            string query = "SELECT COUNT(*) as counter, User_ID FROM USERS WHERE UserName='" + userName + "' AND Password='" + password + "' GROUP BY User_ID";
            SqlDataAdapter sda = new SqlDataAdapter(query, connection);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            if (dt.Rows[0][0].ToString() == "1")
            {
                int _userID = Convert.ToInt32(dt.Rows[0][1]);
                User newUser = new User(_userID, userName);
                Globaal.NewuserLoggedin(newUser);
                correctLogin = true;
            }
            else
            {
                correctLogin = false;
            }

            return correctLogin;

        }

        /// <summary>
        /// Method for registering a new user
        /// </summary>
        /// <param name="FirstName"></param>
        /// <param name="Infix"></param>
        /// <param name="LastName"></param>
        /// <param name="Street"></param>
        /// <param name="HouseNumber"></param>
        /// <param name="City"></param>
        /// <param name="ZipCode"></param>
        /// <param name="Province"></param>
        /// <param name="UserName"></param>
        /// <param name="Password"></param>
        /// <param name="Email"></param>
        /// <param name="CountryCode"></param>
        public void AddUser(string FirstName, string Infix, string LastName, string Street, int HouseNumber, string City, string ZipCode, string Province, string UserName, string Password, string Email, string CountryCode)
        {

            using (connection)
            {
                //Query to insert all information needed to create a user
                string query = "insert into USERS (City, Email, FirstName, HouseNumber, Infix, LastName, Password, Province, Street, UserName, ZipCode, CountryCode) values(@City, @Email, @FirstName, @HouseNumber, @Infix, @LastName, @Password, @Province, @Street, @UserName, @ZipCode, @CountryCode)";
                connection.Open();
                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    cmd.Parameters.AddWithValue("@City", City);
                    cmd.Parameters.AddWithValue("@Email", Email);
                    cmd.Parameters.AddWithValue("@FirstName", FirstName);
                    cmd.Parameters.AddWithValue("@HouseNumber", HouseNumber);
                    cmd.Parameters.AddWithValue("@Infix", Infix);
                    cmd.Parameters.AddWithValue("@LastName", LastName);
                    cmd.Parameters.AddWithValue("@Password", Password);
                    cmd.Parameters.AddWithValue("@Province", Province);
                    cmd.Parameters.AddWithValue("@Street", Street);
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@ZipCode", ZipCode);
                    cmd.Parameters.AddWithValue("@CountryCode", CountryCode);


                    cmd.ExecuteNonQuery();

                    connection.Close();
                }
            }

        }

        /// <summary>
        /// Writes the created orderlist to the database
        /// </summary>
        /// <param name="OrderListProduct_ID"></param>
        /// <param name="OrderListUser_ID"></param>
        public void WriteOrderlistToDatabase(int OrderListProduct_ID, int OrderListUser_ID)
        {
             string connectionString = "Data Source=127.0.0.1;Initial Catalog=KillerApp;User ID=PT12admin;Password=jestin123";
             SqlConnection connection = new SqlConnection(connectionString);
            using (connection)
            {
                //Query to insert Product_ID and User_ID into junction table ORDERLISTS
                string query = "insert into ORDERLISTS (OrderListProduct_ID, OrderListUser_ID) values (@OrderListProduct_ID, @OrderListUser_ID)";
                connection.Open();
                using (SqlCommand cmd = new SqlCommand(query, connection))
                {
                    cmd.Parameters.AddWithValue("@OrderListProduct_ID", OrderListProduct_ID);
                    cmd.Parameters.AddWithValue("@OrderListUser_ID", OrderListUser_ID);

                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// Method for getting the correct country code / language
        /// </summary>
        /// <returns></returns>
        public List<Country> GetCountry()
        {
            //Query to get country information from the given fields
            List<Country> countries = new List<Country>();
            string query = "SELECT CountryCode, Dutch, German, English FROM COUNTRIES";

            connection.Open();

            SqlCommand cmd = new SqlCommand(query, connection);
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    countries.Add(new Country(reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3)));
                }
            }
            connection.Close();

            return countries;
        }

        /// <summary>
        /// Method to get the right product with it's correct information
        /// </summary>
        /// <param name="BarCode"></param>
        /// <returns></returns>
        public Product GetProduct(string BarCode)
        {
            //Query to grab the corresponding product with the filled-in BarCode
            string query = "SELECT Product_ID, ProductName, BarCode, Price, Category FROM PRODUCTS WHERE BarCode = " + BarCode;

            connection.Open();

            try
            {
                SqlCommand cmd = new SqlCommand(query, connection);
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        return new Product(reader.GetInt32(0), reader.GetString(1), reader.GetString(2), reader.GetDecimal(3), reader.GetString(4));

                    }
                }
            }
            finally
            {
                connection.Close();
            }

            return null;
        }

    }
}