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

namespace KillerAppSven
{
    class Database
    {
        // Hier wordt een connectie met de Database gemaakt.
        private const string connstr = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\USERS\SVEN\DOCUMENTS\VISUAL STUDIO 2015\PROJECTS\KILLERAPPSVEN\KILLERAPPSVEN\GEBRUIKERS.MDF;Integrated Security = True; Connect Timeout = 30";
        private SqlConnection sqlconn = new SqlConnection(connstr);

        public string exe;

        //Dit is de methode die gebruikt wordt om in te loggen.
        public bool Login(string LoginName, string Password)
        {
            bool LoginCorrect;
            string UserName;
            string Pass;

            // Hier gaat hij de tabel openen en query defineren
            string query = "SELECT * FROM [ExistingUsers] WHERE LoginName='" + LoginName + "'";
            sqlconn.Open();
            SqlCommand cmd = new SqlCommand(query, sqlconn);
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                //Hier leest hij de Username en Password uit die bij de ingevoerde loginname horen.
                reader.Read();
                {
                    try
                    {
                        UserName = reader.GetString(1);
                    }
                    catch
                    {
                        UserName = "Not found";
                    }

                    try
                    {
                        Pass = reader.GetString(2);
                    }
                    catch
                    {
                        Pass = "Not Found";
                    }

                }
            }
            sqlconn.Close();

            //Hier vergelijkt hij de Username en Password met de bekende gegevens.
            if (LoginName == UserName && Pass == Password)
            {
                LoginCorrect = true;
            }
            else
            {
                LoginCorrect = false;
            }
            return LoginCorrect;
        }


        //Dit is de methode die gebruikt wordt om in te loggen.
        public string GetAccountID(string LoginName)
        {
            string AccountID;

            // Hier gaat hij de tabel openen en query defineren
            string query = "SELECT * FROM [ExistingUsers] WHERE LoginName='" + LoginName + "'";
            sqlconn.Open();
            SqlCommand cmd = new SqlCommand(query, sqlconn);
            using (SqlDataReader reader = cmd.ExecuteReader())

            {
                //Hier leest hij de Username en Password uit die bij de ingevoerde loginname horen.
                reader.Read();
                {
                    AccountID = reader.GetString(0);
                }
            }
            sqlconn.Close();

            return AccountID;
        }

        //Deze methode wordt gebruikt om een account aan te maken.
        public bool CreateAccount(string accountid, string username, string password)
        {
            string date = DateTime.Now.ToString();
            bool accountcreated;
            sqlconn.Open();
            try
            {
                string query = "INSERT INTO [ExistingUsers] VALUES ('" + accountid + "', '" + username + "', '" + password + "');";
                SqlCommand cmd = new SqlCommand(query, sqlconn);
                int result = cmd.ExecuteNonQuery();
                accountcreated = true;
            }

            catch (Exception ex)
            {
                accountcreated = false;
                exe = ex.Message;
            }
            sqlconn.Close();

            return accountcreated;
        }

        //Deze methode wordt gebruikt om een account aan te maken.
        public bool CreateUser(string accountid, string name, string surname, string email, string phonenumber)
        {
            bool usercreated = false;
            sqlconn.Open();
            try
            {
                string query = "INSERT INTO [CreatedUsers] (AccountID, Firstname, Surname, Email, Phonenumber) VALUES ('" + accountid + "', '" + name + "', '" + surname + "', '" + email + "', '" + phonenumber + "');";
                SqlCommand cmd = new SqlCommand(query, sqlconn);
                int result = cmd.ExecuteNonQuery();
                usercreated = true;
            }

            catch (Exception ex)
            {
                usercreated = false;
                exe = ex.Message;
            }
            sqlconn.Close();

            return usercreated;
        }

        //Dit is de methode die gebruikt wordt om een Naam bij een Account ID te vinden.
        public string GetUserName(string AccountId)
        {
            string name;

            // Hier gaat hij de tabel openen en query defineren
            string query = "SELECT * FROM [CreatedUsers] WHERE AccountID='" + AccountId + "'";
            sqlconn.Open();
            SqlCommand cmd = new SqlCommand(query, sqlconn);
            using (SqlDataReader reader = cmd.ExecuteReader())

            {
                reader.Read();
                {
                    name = reader.GetString(1);
                }
            }
            sqlconn.Close();

            return name;
        }
                public string IDGenerator()
        {
            string RandomOutcomeOutput = "";
            string ID = "";
            Random random = new Random();
            for (int i = 0; i < 10; i++)
            {
                int RandomOutcomeInput = random.Next(0, 51);
                if (RandomOutcomeInput > 26 || RandomOutcomeInput < 1)
                {
                    RandomOutcomeOutput = Convert.ToString(RandomOutcomeInput);
                }
                else
                {
                    switch (RandomOutcomeInput)
                    {
                        case 1: RandomOutcomeOutput = "a"; break;
                        case 2: RandomOutcomeOutput = "b"; break;
                        case 3: RandomOutcomeOutput = "c"; break;
                        case 4: RandomOutcomeOutput = "d"; break;
                        case 5: RandomOutcomeOutput = "e"; break;
                        case 6: RandomOutcomeOutput = "f"; break;
                        case 7: RandomOutcomeOutput = "g"; break;
                        case 8: RandomOutcomeOutput = "h"; break;
                        case 9: RandomOutcomeOutput = "i"; break;
                        case 10: RandomOutcomeOutput = "j"; break;
                        case 11: RandomOutcomeOutput = "k"; break;
                        case 12: RandomOutcomeOutput = "l"; break;
                        case 13: RandomOutcomeOutput = "m"; break;
                        case 14: RandomOutcomeOutput = "n"; break;
                        case 15: RandomOutcomeOutput = "o"; break;
                        case 16: RandomOutcomeOutput = "p"; break;
                        case 17: RandomOutcomeOutput = "q"; break;
                        case 18: RandomOutcomeOutput = "r"; break;
                        case 19: RandomOutcomeOutput = "s"; break;
                        case 20: RandomOutcomeOutput = "t"; break;
                        case 21: RandomOutcomeOutput = "u"; break;
                        case 22: RandomOutcomeOutput = "v"; break;
                        case 23: RandomOutcomeOutput = "w"; break;
                        case 24: RandomOutcomeOutput = "x"; break;
                        case 25: RandomOutcomeOutput = "y"; break;
                        case 26: RandomOutcomeOutput = "z"; break;
                    }
                }
                ID = ID + RandomOutcomeOutput;
            }
            return ID;
        }
    }
}