using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace Server
{
    public sealed class Sql
    {
        SqlConnection sqlConnection;

        public Sql()
        {
        }

        public void Connect()
        {
            try
            {
                sqlConnection = new SqlConnection(@"server = .\sqlexpress;
                                                    integrated security = true;
                                                    database = xBot");
                sqlConnection.Open();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Cannot connect to database.", "Error",
                    System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
                Environment.Exit(0);
            }
        }

        public int GetID(string userName, string userPassword)
        {
            try
            {
                if (sqlConnection.State != ConnectionState.Broken && sqlConnection.State != ConnectionState.Closed)
                {
                    if (userName != null && userPassword != null)
                    {
                        SqlCommand getID = sqlConnection.CreateCommand();
                        getID.CommandText = "select UserID from Users where UserName = @userName and UserPassword = @userPassword";
                        getID.Parameters.Add("@userName", SqlDbType.NChar).Value = userName;
                        getID.Parameters.Add("@userPassword", SqlDbType.NChar).Value = userPassword;

                        SqlDataReader reader = getID.ExecuteReader();
                        int id = 0;
                        while (reader.Read())
                        {
                            id = int.Parse(reader["UserID"].ToString());
                        }
                        return id;
                    }
                }
                return 0;
            }
            catch
            {
                return 0;
            }
        }

        public bool ChangeUserPassword(string userName, string oldPassword, string newPassword)
        {
            try
            {
                if (sqlConnection.State != ConnectionState.Broken && sqlConnection.State != ConnectionState.Closed)
                {
                    if (userName != null && oldPassword != null && newPassword != null)
                    {
                        SqlCommand changePassword = sqlConnection.CreateCommand();
                        changePassword.CommandText = @"update Users
                                                       set UserPassword = @newPassword
                                                       where UserName = @userName and UserPassword = @oldPassword";
                        changePassword.Parameters.Add("@userName", SqlDbType.NChar).Value = userName;
                        changePassword.Parameters.Add("@newPassword", SqlDbType.NChar).Value = newPassword;
                        changePassword.Parameters.Add("@oldPassword", SqlDbType.NChar).Value = oldPassword;

                        if (changePassword.ExecuteNonQuery() != 0) return true;
                        else return false;

                    }
                }
                return false;
            }
            catch
            {
                return false;
            }
        }

        public bool CreateAccount(string userName, string password, string eMail)
        {
            try
            {
                if (sqlConnection.State != ConnectionState.Broken && sqlConnection.State != ConnectionState.Closed)
                {
                    if (userName != null && password != null && eMail != null)
                    {
                        SqlCommand checkForUser = sqlConnection.CreateCommand();
                        checkForUser.CommandText = "select * from Users where UserName = @userName";
                        checkForUser.Parameters.Add("@userName", SqlDbType.NChar).Value = userName;
                        SqlDataReader reader = checkForUser.ExecuteReader();

                        if (!reader.HasRows)
                        {
                            reader.Close();
                            SqlCommand create = sqlConnection.CreateCommand();
                            create.CommandText = @"insert into Users (UserName,UserPassword,UserEmail,RegistrationDate)
                                                   values (@userName,@userPassword,@userEmail,@registrationDate)";
                            create.Parameters.Add("@userName", SqlDbType.NChar).Value = userName;
                            create.Parameters.Add("@userPassword", SqlDbType.NChar).Value = password;
                            create.Parameters.Add("@userEmail", SqlDbType.NChar).Value = eMail;
                            create.Parameters.Add("@registrationDate", SqlDbType.DateTime).Value = DateTime.Now.ToShortDateString();

                            if (create.ExecuteNonQuery() != 0)
                                return true;
                            else return false;
                        }
                        else
                        {
                            reader.Close();
                            return false;
                        }
                    }

                }
                return false;
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message);
                return false;
            }
        }

        public bool DeleteUser(string userName, string userPassword)
        {
            try
            {
                if (sqlConnection.State != ConnectionState.Broken && sqlConnection.State != ConnectionState.Closed)
                {
                    if (userName != null && userPassword != null)
                    {
                        SqlCommand deleteUser = sqlConnection.CreateCommand();
                        deleteUser.CommandText = "delete from Users where UserName = @userName and UserPassword = @userPassword";
                        deleteUser.Parameters.Add("@userName", SqlDbType.NChar).Value = userName;
                        deleteUser.Parameters.Add("@userPassword", SqlDbType.NChar).Value = userPassword;
                        if (deleteUser.ExecuteNonQuery() != 0)
                            return true;
                        else
                            return false;
                    }
                }
                return false;
            }
            catch
            {
                return false;
            }
        }

        public DataTable RetrieveUsers()
        {
            try
            {
                SqlCommand getUsers = sqlConnection.CreateCommand();
                getUsers.CommandText = "select * from Users";
                SqlDataReader reader = getUsers.ExecuteReader();
                DataTable Users = new DataTable("Users");
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    Users.Columns.Add(new DataColumn(reader.GetName(i)));
                }
                while (reader.Read())
                {
                    object[] values = new object[reader.FieldCount];
                    for (int i = 0; i < values.Length; i++)
                    {
                        values[i] = reader.GetValue(i);
                    }
                    Users.Rows.Add(values);
                }
                reader.Close();
                return Users;
            }
            catch
            {
                return null;
            }
        }
    }
}