using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Text.RegularExpressions;


namespace PC_Build_Games_SQL
{
    public class SQL
    {
        public string User;//  пользователя, который вошел в аккаунт

        public SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString);


        public bool ConnectivityCheck()
        {
            sqlConnection.Open();
            if (sqlConnection.State == System.Data.ConnectionState.Open)
            {
                Console.WriteLine("Подключение прошло успешно");
                return true;
            }
            sqlConnection.Close();
            return false;

        }

        public bool Login_verification(string nickname, string password)
        {

            sqlConnection.Open();
            string authenticationrequest = "SELECT * from User_Authorization";
            SqlCommand command = new SqlCommand(authenticationrequest, sqlConnection);
            SqlDataReader reader = command.ExecuteReader();
            bool flag = false;
            int i = 1;
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        if (nickname == Convert.ToString(reader.GetValue(1)))
                        {
                            if (password == Convert.ToString(reader.GetValue(2)))
                            {
                                Console.WriteLine("Нашел");
                                flag = true;
                                User = Convert.ToString(reader.GetValue(1));

                            }
                        }
                        i++;

                    }
                }
            }



            reader.Close();
            sqlConnection.Close();
            return flag;
        }
        public int Registration(string name, string surname, string nickname, string email, string password)// Регистрация пользователя
        {
            if (name == "" || surname == "" || nickname == "" || email == "" || password == "")
            {
                return 1; // Поля должны быть заполнены 
            }

            Regex regex = new Regex(@"^(?! )(?!.* $)(?!(?:.* ){2}).+$");
            Regex regexEmail = new Regex(@"^[_a-z0-9-\+-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,})$");
            Regex regexPassword = new Regex(@"([0-9,a-z,A-Z,~,!,@,#,$,%,^,&,*,(,),_]){6,}$");
            MatchCollection match = regex.Matches(name);



            if (regexEmail.IsMatch(email) == false)
            {
                return 5;// Такой почты не существует
            }
            if (regexPassword.IsMatch(password) == false)
            {
                return 6;// Пароль не соответствует требованиям
            }
            if (regex.IsMatch(name) == true && regex.IsMatch(surname) && regex.IsMatch(nickname))
            {
                sqlConnection.Open();
                string User_Authorization = "SELECT * from User_Authorization";
                string User = "select * from [User]";
                string Query_User_Authorization = String.Format($"INSERT INTO User_Authorization (Nickname, PASSWORD) VALUES ('{nickname}','{password}')");
                string Query_User = String.Format($"INSERT INTO [User] ([Name],Surname,EMAIL) VALUES ('{name}','{surname}','{email}')");

                SqlCommand User_Authorization_Table = new SqlCommand(User_Authorization, sqlConnection);
                SqlDataReader Reader_User_Authorization_Table = User_Authorization_Table.ExecuteReader();


                using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
                {
                    while (Reader_User_Authorization_Table.Read())
                    {
                        if (nickname == Convert.ToString(Reader_User_Authorization_Table.GetValue(1)))
                        {


                            return 2; // пользователь с таким ником уже существует

                        }
                    }
                }
                Reader_User_Authorization_Table.Close();
                SqlCommand User_Table = new SqlCommand(User, sqlConnection);
                SqlDataReader Reader_User_Table = User_Table.ExecuteReader();

                using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
                {
                    while (Reader_User_Table.Read())
                    {
                        if (email == Convert.ToString(Reader_User_Table.GetValue(3)))
                        {
                            return 3; // Пользователь с таким адрессом электронной почты уже существует
                        }
                    }
                }

                Reader_User_Table.Close();

                using (SqlCommand command = new SqlCommand(Query_User_Authorization, sqlConnection))
                {

                    command.Parameters.AddWithValue("@Nickname", nickname);
                    command.Parameters.AddWithValue("@PASSWORD", password);
                    Console.WriteLine(command.ExecuteNonQuery());

                }
                using (SqlCommand command = new SqlCommand(Query_User, sqlConnection))
                {

                    command.Parameters.AddWithValue("@Name", name);
                    command.Parameters.AddWithValue("@Surname", surname);
                    command.Parameters.AddWithValue("@EMAIL", email);
                    Console.WriteLine(command.ExecuteNonQuery());

                }
                sqlConnection.Close();

                return 0; // Вы успешно зарегестрировались

            }
            else
            {
                return 4; // Имя не должно начинаться с пробелал, заканчиваться на пробел, иметь 2 пробела в тексте
            }
        }
        public void New_configuration(string Nickname, string Graphics_card_name, string CPUName, int RAM, string nameConfig)// Создание новой конфигурации
        {
            sqlConnection.Open();
            int IDUser = 0;
            string videoСardQuery = "Select * from VideoCard";
            int IDvideoCard = 0;
            int IDCPU = 0;
            string CPUQuery = "Select * from CPU";
            string UserQuery = "Select * from User_Authorization";
            string fillingoutTheConfiguration;

            SqlCommand User_Authorization = new SqlCommand(UserQuery, sqlConnection);
            SqlDataReader ReaderUserAuthorizationTable = User_Authorization.ExecuteReader();

            using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (ReaderUserAuthorizationTable.Read())
                {
                    if (Nickname == Convert.ToString(ReaderUserAuthorizationTable.GetValue(1)))
                    {
                        IDUser = Convert.ToInt32(ReaderUserAuthorizationTable.GetValue(0));

                        break;

                    }
                }


            }
            ReaderUserAuthorizationTable.Close();
            SqlCommand videoСardIDTable = new SqlCommand(videoСardQuery, sqlConnection);
            SqlDataReader Reader_User_Authorization_Table = videoСardIDTable.ExecuteReader();

            using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (Reader_User_Authorization_Table.Read())
                {
                    if (Graphics_card_name == Convert.ToString(Reader_User_Authorization_Table.GetValue(2)))
                    {
                        IDvideoCard = Convert.ToInt32(Reader_User_Authorization_Table.GetValue(0));
                        Console.WriteLine(IDvideoCard);

                    }
                }


            }
            Reader_User_Authorization_Table.Close();

            SqlCommand CPUTable = new SqlCommand(CPUQuery, sqlConnection);
            SqlDataReader readerCPUTable = CPUTable.ExecuteReader();

            using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (readerCPUTable.Read())
                {
                    if (CPUName == Convert.ToString(readerCPUTable.GetValue(2)))
                    {

                        IDCPU = Convert.ToInt32(readerCPUTable.GetValue(0));
                        Console.WriteLine(IDCPU);

                    }
                }


            }
            readerCPUTable.Close();

            fillingoutTheConfiguration = $"insert into Configuration ([name],ID_User,ID_VideoCard,ID_CPU, RAM) VALUES ('{nameConfig}','{IDUser}','{IDvideoCard}',{IDCPU},{RAM})";

            using (SqlCommand command = new SqlCommand(fillingoutTheConfiguration, sqlConnection))
            {

                Console.WriteLine(command.ExecuteNonQuery());

            }

            sqlConnection.Close();
        }


        public List<string> UserInformation(string Nickname)//Информация о пользователе
        {
            sqlConnection.Open();

            List<string> userInfo = new List<string>();

            string UserAuthorizationQuery = "Select * from User_Authorization";
            string UserQuery = "Select * from [User]";
            int UserID = 0;
            SqlCommand UserAuthorizationTable = new SqlCommand(UserAuthorizationQuery, sqlConnection);
            SqlDataReader readerUserAuthorizationTable = UserAuthorizationTable.ExecuteReader();

            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (readerUserAuthorizationTable.Read())
                {
                    if (Nickname == Convert.ToString(readerUserAuthorizationTable.GetValue(1)))
                    {
                        UserID = Convert.ToInt32(readerUserAuthorizationTable.GetValue(0));

                        break;
                    }
                }
            }
            readerUserAuthorizationTable.Close();

            SqlCommand UserTable = new SqlCommand(UserQuery, sqlConnection);
            SqlDataReader readerUserTable = UserTable.ExecuteReader();

            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (readerUserTable.Read())
                {
                    if (UserID == Convert.ToInt32(readerUserTable.GetValue(0)))
                    {
                        userInfo.Add(Convert.ToString(readerUserTable.GetValue(1)));
                        Console.WriteLine(userInfo[0]);
                        userInfo.Add(Convert.ToString(readerUserTable.GetValue(2)));
                        Console.WriteLine(userInfo[1]);
                        userInfo.Add(Convert.ToString(readerUserTable.GetValue(3)));
                        Console.WriteLine(userInfo[2]);

                    }
                }
                sqlConnection.Close();
                return userInfo;
            }

        }
        public List<string> CPUGPUList(string InfoReturn)//список видеокард и процессоров
        {
            sqlConnection.Open();
            List<string> userInfo = new List<string>();

            string GPUQuery = "select [Name] from VideoCard";
            string CPUQuery = "select [Name] from CPU";

            if (InfoReturn == "GPU")
            {
                SqlCommand GPUTable = new SqlCommand(GPUQuery, sqlConnection);
                SqlDataReader sqlDataReader = GPUTable.ExecuteReader();

                using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
                {
                    for (int i = 0; sqlDataReader.Read(); i++)
                    {
                        userInfo.Add(Convert.ToString(sqlDataReader.GetValue(0)));

                    }

                }
                sqlDataReader.Close();
            }
            if (InfoReturn == "CPU")
            {
                SqlCommand CPUTable = new SqlCommand(CPUQuery, sqlConnection);
                SqlDataReader sqlDataReader = CPUTable.ExecuteReader();

                using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
                {
                    for (int i = 0; sqlDataReader.Read(); i++)
                    {
                        userInfo.Add(Convert.ToString(sqlDataReader.GetValue(0)));
                        Console.WriteLine(userInfo);
                    }

                }
                sqlDataReader.Close();
            }
            sqlConnection.Close();
            return userInfo;
        }

        public List<string> ListOfConfigurations(string Nickname)// список конфигурация пользователя
        {
            sqlConnection.Open();

            List<string> list = new List<string>();
            int IDUser = 0;

            string UserAuthorizationQuery = "Select * from User_Authorization";

            SqlCommand UserAuthorizationTable = new SqlCommand(UserAuthorizationQuery, sqlConnection);
            SqlDataReader ReaderUserAuthorizationTable = UserAuthorizationTable.ExecuteReader();

            using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (ReaderUserAuthorizationTable.Read())
                {
                    if (Nickname == Convert.ToString(ReaderUserAuthorizationTable.GetValue(1)))
                    {
                        IDUser = Convert.ToInt32(ReaderUserAuthorizationTable.GetValue(0));

                        break;

                    }
                }


            }

            ReaderUserAuthorizationTable.Close();
            string ConfigurationQuery = "Select [name],ID_User from Configuration ";

            SqlCommand ConfigurationTable = new SqlCommand(ConfigurationQuery, sqlConnection);
            SqlDataReader sqlDataReader = ConfigurationTable.ExecuteReader();

            using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (sqlDataReader.Read())
                {
                    if (Convert.ToInt32(sqlDataReader.GetValue(1)) == IDUser)
                    {
                        list.Add(Convert.ToString(sqlDataReader.GetValue(0)));
                    }
                }
            }
            sqlDataReader.Close();
            sqlConnection.Close();
            return list;

        }
        public List<string> PartsList(string NameConfiguration)// информация о конфигурации
        {
            sqlConnection.Open();
            int IDGPU = 0;
            int IDCPU = 0;
            int RAM = 0;

            List<string> PartsList = new List<string>();

            string ConfigurationQuery = "Select [name],ID_VideoCard,ID_CPU,RAM from Configuration ";
            string GPUQuery = "Select ID_VideoCard, [Name] from VideoCard";
            string CPUQuery = "Select ID_CPU, [Name] from CPU";

            SqlCommand ConfigurationTable = new SqlCommand(ConfigurationQuery, sqlConnection);
            SqlDataReader sqlDataReader = ConfigurationTable.ExecuteReader();

            using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                for (int i = 0; sqlDataReader.Read(); i++)
                {
                    if (Convert.ToString(sqlDataReader.GetValue(0)) == NameConfiguration)
                    {
                        IDGPU = Convert.ToInt32(sqlDataReader.GetValue(1));
                        IDCPU = Convert.ToInt32(sqlDataReader.GetValue(2));
                        RAM = Convert.ToInt32(sqlDataReader.GetValue(3));


                    }
                }

            }
            sqlDataReader.Close();

            SqlCommand GPUTable = new SqlCommand(GPUQuery, sqlConnection);
            SqlDataReader GPUReader = GPUTable.ExecuteReader();

            using (SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (GPUReader.Read())
                {
                    if (IDGPU == Convert.ToInt32(GPUReader.GetValue(0)))
                    {
                        PartsList.Add(Convert.ToString(GPUReader.GetValue(1)));
                    }
                }
            }
            GPUReader.Close();

            SqlCommand CPUTable = new SqlCommand(CPUQuery, sqlConnection);
            SqlDataReader CPUReader = CPUTable.ExecuteReader();

            using (SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (CPUReader.Read())
                {
                    if (IDCPU == Convert.ToInt32(CPUReader.GetValue(0)))
                    {
                        PartsList.Add(Convert.ToString(CPUReader.GetValue(1)));
                        PartsList.Add(Convert.ToString(RAM));
                    }
                }
            }
            CPUReader.Close();
            sqlConnection.Close();
            return PartsList;



        }

        public string CompatibilityCheck(string NameConfigurathoin, string NameProgramm)// проверка совместимости(демо)
        {
            sqlConnection.Open();
            int[] minimumRequirements = new int[3];
            int[] RecRequirements = new int[3];
            int[] сomponentСonfigurations = new int[3];

            List<string> PartsList = new List<string>();

            string minimumRequirementsQuery = $"SELECT VideoCard.ID_VideoCard,CPU.ID_CPU,Minimum_Requirements.RAM FROM [Application] JOIN Minimum_Requirements ON Minimum_Requirements.ID_MIN=[Application].ID_REC JOIN VideoCard ON Minimum_Requirements.ID_VideoCard=VideoCard.ID_VideoCard JOIN CPU ON Minimum_Requirements.ID_CPU=CPU.ID_CPU WHERE [Application].Title='{NameProgramm}';";
            string RecomendedRequiremetsQuery = $"SELECT VideoCard.ID_VideoCard,CPU.ID_CPU,Recommended_Requirements.RAM FROM [Application] JOIN Recommended_Requirements ON Recommended_Requirements.ID_REC=[Application].ID_REC JOIN VideoCard ON Recommended_Requirements.ID_VideoCard=VideoCard.ID_VideoCard JOIN CPU ON Recommended_Requirements.ID_CPU=CPU.ID_CPU WHERE [Application].Title='{NameProgramm}';";
            string сomponentСonfigurationsQuery = $"select ID_VideoCard,ID_CPU,RAM from [Configuration] where [name] = '{NameConfigurathoin}'";

            SqlCommand MinimumRequirementsTable = new SqlCommand(minimumRequirementsQuery, sqlConnection);
            SqlDataReader MinimumRequirementsReader = MinimumRequirementsTable.ExecuteReader();

            using (SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (MinimumRequirementsReader.Read())
                {
                    minimumRequirements[0] = Convert.ToInt32(MinimumRequirementsReader.GetValue(0));
                    minimumRequirements[1] = Convert.ToInt32(MinimumRequirementsReader.GetValue(1));
                    minimumRequirements[2] = Convert.ToInt32(MinimumRequirementsReader.GetValue(2));
                }
            }
            MinimumRequirementsReader.Close();

            SqlCommand RecRequirementsTable = new SqlCommand(RecomendedRequiremetsQuery, sqlConnection);
            SqlDataReader RecRequirementsTableReader = RecRequirementsTable.ExecuteReader();

            using (SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (RecRequirementsTableReader.Read())
                {
                    RecRequirements[0] = Convert.ToInt32(RecRequirementsTableReader.GetValue(0));
                    RecRequirements[1] = Convert.ToInt32(RecRequirementsTableReader.GetValue(1));
                    RecRequirements[2] = Convert.ToInt32(RecRequirementsTableReader.GetValue(2));
                }
            }
            RecRequirementsTableReader.Close();

            SqlCommand сomponentСonfigurationsTable = new SqlCommand(сomponentСonfigurationsQuery, sqlConnection);
            SqlDataReader сomponentСonfigurationsTableReader = сomponentСonfigurationsTable.ExecuteReader();

            using (SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (сomponentСonfigurationsTableReader.Read())
                {
                    сomponentСonfigurations[0] = Convert.ToInt32(сomponentСonfigurationsTableReader.GetValue(0));
                    сomponentСonfigurations[1] = Convert.ToInt32(сomponentСonfigurationsTableReader.GetValue(1));
                    сomponentСonfigurations[2] = Convert.ToInt32(сomponentСonfigurationsTableReader.GetValue(2));
                }
            }
            сomponentСonfigurationsTableReader.Close();

            if ((сomponentСonfigurations[0] <= RecRequirements[0]) && (сomponentСonfigurations[1] <= RecRequirements[1]) && (сomponentСonfigurations[2] >= RecRequirements[2]))
            {
                sqlConnection.Close();
                
                return "Ваша конфигурация подходит для рекомендованных требований";
            }
            else if ((сomponentСonfigurations[0] <= minimumRequirements[0]) && (сomponentСonfigurations[1] <= minimumRequirements[1]) && (сomponentСonfigurations[2] >= minimumRequirements[2]))
            {
                sqlConnection.Close();
                
                return "Ваша конфигурация подходит для минимальных требований";
            }
            else
            { 
                sqlConnection.Close();

                return gaga(NameConfigurathoin, NameProgramm);
            }


        }
        public string gaga(string NameConfigurathoin, string NameProgramm)// пока демо
        {
            sqlConnection.Open();

            int[] minimumRequirements = new int[3];
            int[] RecRequirements = new int[3];
            int[] сomponentСonfigurations = new int[3];
            string[] minimumRequirementsString = new string[3];
            string[] RecRequirementsString = new string[3];
            string returninfo;
            List<string> info = new List<string>();


            string minimumRequirementsQuery = $"SELECT VideoCard.ID_VideoCard,CPU.ID_CPU,Minimum_Requirements.RAM FROM [Application] JOIN Minimum_Requirements ON Minimum_Requirements.ID_MIN=[Application].ID_REC JOIN VideoCard ON Minimum_Requirements.ID_VideoCard=VideoCard.ID_VideoCard JOIN CPU ON Minimum_Requirements.ID_CPU=CPU.ID_CPU WHERE [Application].Title='{NameProgramm}';";
            string RecomendedRequiremetsQuery = $"SELECT VideoCard.ID_VideoCard,CPU.ID_CPU,Recommended_Requirements.RAM FROM [Application] JOIN Recommended_Requirements ON Recommended_Requirements.ID_REC=[Application].ID_REC JOIN VideoCard ON Recommended_Requirements.ID_VideoCard=VideoCard.ID_VideoCard JOIN CPU ON Recommended_Requirements.ID_CPU=CPU.ID_CPU WHERE [Application].Title='{NameProgramm}';";
            string сomponentСonfigurationsQuery = $"select ID_VideoCard,ID_CPU,RAM from [Configuration] where [name] = '{NameConfigurathoin}'";
            string minimumRequirementsQuerystring = $"SELECT VideoCard.[Name],CPU.[Name],Minimum_Requirements.RAM FROM [Application] JOIN Minimum_Requirements ON Minimum_Requirements.ID_MIN=[Application].ID_MIN JOIN VideoCard ON Minimum_Requirements.ID_VideoCard=VideoCard.ID_VideoCard JOIN CPU ON Minimum_Requirements.ID_CPU=CPU.ID_CPU WHERE [Application].Title='{NameProgramm}';";
            string RecomendedRequiremetsQuerystring = $"SELECT VideoCard.[Name],CPU.[Name],Recommended_Requirements.RAM FROM [Application] JOIN Recommended_Requirements ON Recommended_Requirements.ID_REC=[Application].ID_REC JOIN VideoCard ON Recommended_Requirements.ID_VideoCard=VideoCard.ID_VideoCard JOIN CPU ON Recommended_Requirements.ID_CPU=CPU.ID_CPU WHERE [Application].Title='{NameProgramm}'";

            SqlCommand MinimumRequirementsTable = new SqlCommand(minimumRequirementsQuery, sqlConnection);
            SqlDataReader MinimumRequirementsReader = MinimumRequirementsTable.ExecuteReader();

            using (SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (MinimumRequirementsReader.Read())
                {
                    minimumRequirements[0] = Convert.ToInt32(MinimumRequirementsReader.GetValue(0));
                    minimumRequirements[1] = Convert.ToInt32(MinimumRequirementsReader.GetValue(1));
                    minimumRequirements[2] = Convert.ToInt32(MinimumRequirementsReader.GetValue(2));
                }
            }
            MinimumRequirementsReader.Close();

            SqlCommand RecRequirementsTable = new SqlCommand(RecomendedRequiremetsQuery, sqlConnection);
            SqlDataReader RecRequirementsTableReader = RecRequirementsTable.ExecuteReader();

            using (SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (RecRequirementsTableReader.Read())
                {
                    RecRequirements[0] = Convert.ToInt32(RecRequirementsTableReader.GetValue(0));
                    RecRequirements[1] = Convert.ToInt32(RecRequirementsTableReader.GetValue(1));
                    RecRequirements[2] = Convert.ToInt32(RecRequirementsTableReader.GetValue(2));
                }
            }
            RecRequirementsTableReader.Close();

            SqlCommand сomponentСonfigurationsTable = new SqlCommand(сomponentСonfigurationsQuery, sqlConnection);
            SqlDataReader сomponentСonfigurationsTableReader = сomponentСonfigurationsTable.ExecuteReader();

            using (SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (сomponentСonfigurationsTableReader.Read())
                {
                    сomponentСonfigurations[0] = Convert.ToInt32(сomponentСonfigurationsTableReader.GetValue(0));
                    сomponentСonfigurations[1] = Convert.ToInt32(сomponentСonfigurationsTableReader.GetValue(1));
                    сomponentСonfigurations[2] = Convert.ToInt32(сomponentСonfigurationsTableReader.GetValue(2));
                }
            }
            сomponentСonfigurationsTableReader.Close();

            SqlCommand minimumRequirementsStringTable = new SqlCommand(minimumRequirementsQuerystring, sqlConnection);
            SqlDataReader minimumRequirementsStringTableReader = minimumRequirementsStringTable.ExecuteReader();

            using (SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (minimumRequirementsStringTableReader.Read())
                {
                    minimumRequirementsString[0] = Convert.ToString(minimumRequirementsStringTableReader.GetValue(0));
                    minimumRequirementsString[1] = Convert.ToString(minimumRequirementsStringTableReader.GetValue(1));
                    minimumRequirementsString[2] = Convert.ToString(minimumRequirementsStringTableReader.GetValue(2));
                }
            }
            minimumRequirementsStringTableReader.Close();

            SqlCommand RecRequirementsStringTable = new SqlCommand(RecomendedRequiremetsQuerystring, sqlConnection);
            SqlDataReader RecRequirementsStringTableReader = RecRequirementsStringTable.ExecuteReader();

            using (SqlConnection sqlConnection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (RecRequirementsStringTableReader.Read())
                {
                    RecRequirementsString[0] = Convert.ToString(RecRequirementsStringTableReader.GetValue(0));
                    RecRequirementsString[1] = Convert.ToString(RecRequirementsStringTableReader.GetValue(1));
                    RecRequirementsString[2] = Convert.ToString(RecRequirementsStringTableReader.GetValue(2));
                }
            }
            RecRequirementsStringTableReader.Close();

            if (сomponentСonfigurations[2] > minimumRequirements[2])
            {
                returninfo = $"Для игры на минимальных настройках вам понадобиться Видеокарта - {minimumRequirementsString[0]}, процессор - {minimumRequirementsString[1]}\n";
            }
            else
            {
                returninfo = $"Для игры на минимальных настройках вам понадобиться Видеокарта - {minimumRequirementsString[0]}, процессор - {minimumRequirementsString[1]}, и добавить  {minimumRequirements[2] - сomponentСonfigurations[2]}Гб оперативной памяти\n";
            }
            if (сomponentСonfigurations[2] > RecRequirements[2])
            {
                returninfo +=$"Для игры на рекомендованных настройках вам понадобиться Видеокарта - {RecRequirementsString[0]}, процессор - {RecRequirementsString[1]}\n";
            }
            else
            {
                returninfo += $"Для игры на рекомендованных настройках вам понадобиться Видеокарта - {RecRequirementsString[0]}, процессор - {RecRequirementsString[1]}, и добавить  {RecRequirements[2] - сomponentСonfigurations[2]}Гб оперативной памяти\n";
            }
            return returninfo;




        }
        public List<string> Listofprograms() // список приложений
        {
            sqlConnection.Open();

            string ListofprogramsQuery = "select Title from Application";

            List<string> Listofprograms = new List<string>();

            SqlCommand ListofprogramsTable = new SqlCommand(ListofprogramsQuery, sqlConnection);
            SqlDataReader ListofprogramsReader = ListofprogramsTable.ExecuteReader();

            using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (ListofprogramsReader.Read())
                {
                    Listofprograms.Add(Convert.ToString(ListofprogramsReader.GetValue(0)));
                }
            }
            ListofprogramsReader.Close();
            sqlConnection.Close();
            return Listofprograms;
        }

        public string UserEditing(string Name, string Surname, string Email, string FormerNickname, string Nickname)// смена контактных данных
        {
            sqlConnection.Open();

            string userAuthorizationQuery = $" update User_Authorization set [Nickname] = '{FormerNickname}' where User_Authorization.ID_User = (select ID_User from User_Authorization where Nickname = '{Nickname}')";
            string userQuery = $" update[User] set[Name] = '{Name}',Surname = '{Surname}',EMAIL = '{Email}' where[User].ID_User = (select ID_User from User_Authorization where Nickname = '{Nickname}')";
            Regex regexEmail = new Regex(@"^[_a-zA-Z0-9-\+-]+(\.[_a-zA_Z0-9-]+)*@[a-zA-Z0-9-]+(\.[a-zA-Z0-9-]+)*(\.[a-zA-Z]{2,})$");
            string authenticationrequest = "SELECT * from User_Authorization";

            SqlCommand command = new SqlCommand(authenticationrequest, sqlConnection);
            SqlDataReader reader = command.ExecuteReader();
            
            
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        if (FormerNickname == Convert.ToString(reader.GetValue(1)))
                        {
                            return "Такой никнейм уже существует";
                        }
                        

                    }
                }
            }
            reader.Close();
            if (Nickname == FormerNickname)
            {
                return "Ваш новый никнейм совпадает с предыдущим";
            }
            if (regexEmail.IsMatch(Email) == false)
            {
                return "Почта имеет неправильный формат";
            }

            SqlCommand UserСhanges = new SqlCommand(userQuery, sqlConnection);
            SqlDataReader dataReader = UserСhanges.ExecuteReader();
            using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                dataReader.Dispose();
                UserСhanges.ExecuteNonQuery();
            }
            dataReader.Close();

            SqlCommand userAuthorizationChanges = new SqlCommand(userAuthorizationQuery, sqlConnection);
            SqlDataReader sqlData = userAuthorizationChanges.ExecuteReader();

            using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                sqlData.Dispose();
                userAuthorizationChanges.ExecuteNonQuery();
            }
            sqlData.Close();
            sqlConnection.Close();
            return "Профиль успешно обновлен";

        }

        public string UserEditing(string Nickname, string previousPassword, string NewPassword, string newPasswordRepeat)
        {
            sqlConnection.Open();
            string userPasswordUpdate = $"update User_Authorization set [PASSWORD] = '{NewPassword}' where User_Authorization.ID_User = (select ID_User from User_Authorization where Nickname = '{Nickname}')";
            string userPasswordOldQuery = $"select PASSWORD from User_Authorization where Nickname ='{Nickname}'";
            Regex regexPassword = new Regex(@"([0-9,a-z,A-Z,~,!,@,#,$,%,^,&,*,(,),_]){6,}$");
            string oldPassword = "s";

            if (regexPassword.IsMatch(NewPassword) == false)
            {
                return "Пароль не соответствует требованиям";
            }
            SqlCommand userPasswordOldTable = new SqlCommand(userPasswordOldQuery, sqlConnection);
            SqlDataReader userPasswordOldTableReader = userPasswordOldTable.ExecuteReader();

            using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                while (userPasswordOldTableReader.Read())
                {

                    oldPassword = Convert.ToString(userPasswordOldTableReader.GetValue(0));
                }

            }
            userPasswordOldTableReader.Close();

            if (previousPassword != oldPassword)
            {
                return "Неверный пароль";
            }
            if (NewPassword != newPasswordRepeat)
            {
                return "Введенные пароли не совпадают";
            }
            if(NewPassword == previousPassword)
            {
                return "Новый пароль совпадает с предыдущим";
            }
            SqlCommand userPasswordUpdateTable = new SqlCommand(userPasswordUpdate, sqlConnection);
            SqlDataReader userPasswordUpdateTableReader = userPasswordUpdateTable.ExecuteReader();

            using (SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["SQL"].ConnectionString))
            {
                userPasswordUpdateTableReader.Dispose();
                userPasswordUpdateTable.ExecuteNonQuery();
            }
            userPasswordUpdateTableReader.Close();
            sqlConnection.Close();
            return "Пароль успешно изменен";

        }




    }
}