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

namespace Item_Creator
{
    class SQL
    {
        public static int Slot = 0;

        public static string CharacterData(string CharacterName, string Information)
        {
            using (SqlConnection SQLConnection = new SqlConnection(Main.SQLInformation))
            {
                SQLConnection.Open();

                SqlCommand ExecuteCommand = new SqlCommand("SELECT * FROM tCharacter WHERE sID = @CharacterName", SQLConnection);
                ExecuteCommand.Parameters.AddWithValue("@CharacterName", CharacterName);

                SqlDataReader ExecuteReader = ExecuteCommand.ExecuteReader();
                ExecuteReader.Read();

                return ExecuteReader[Information].ToString();
            }
        }

        public static bool CheckCharacterExist(string CharacterName)
        {
            using (SqlConnection SQLConnection = new SqlConnection(Main.SQLInformation))
            {
                SQLConnection.Open();

                SqlCommand ExecuteCommand = new SqlCommand("SELECT * FROM tCharacter WHERE sID = @CharacterName", SQLConnection);
                ExecuteCommand.Parameters.AddWithValue("@CharacterName", CharacterName);

                SqlDataReader ExecuteReader = ExecuteCommand.ExecuteReader();

                if (ExecuteReader.HasRows)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public static int AvailableSlot(int CharacterNumber, int StorageType)
        {
            using (SqlConnection SQLConnection = new SqlConnection(Main.SQLInformation))
            {
                SQLConnection.Open();

                SqlCommand ExecuteCommand = new SqlCommand("SELECT * FROM tItem WHERE nOwner = @CharacterNumber AND nStorageType = @StorageType AND nStorage = @Slot", SQLConnection);
                ExecuteCommand.Parameters.AddWithValue("@CharacterNumber", CharacterNumber);
                ExecuteCommand.Parameters.AddWithValue("@StorageType", StorageType);
                ExecuteCommand.Parameters.AddWithValue("@Slot", Slot);

                SqlDataReader ExecuteReader = ExecuteCommand.ExecuteReader();

                if (ExecuteReader.HasRows)
                {
                    Slot = Slot + 1;

                    AvailableSlot(CharacterNumber, StorageType);
                }
            }
            return Slot;
        }

        public static void InsertDataValue(string ItemKey, int ItemType, int ItemData)
        {
            using (SqlConnection SQLConnection = new SqlConnection(Main.SQLInformation))
            {
                SQLConnection.Open();

                SqlCommand ExecuteCommand = new SqlCommand("INSERT INTO tItemOptions (nItemKey, nOptionType, nOptionData) VALUES (@ItemKey, @OptionType, @OptionData)", SQLConnection);
                ExecuteCommand.Parameters.AddWithValue("@ItemKey", ItemKey);
                ExecuteCommand.Parameters.AddWithValue("@OptionType", ItemType);
                ExecuteCommand.Parameters.AddWithValue("@OptionData", ItemData);

                ExecuteCommand.ExecuteNonQuery();
            }
        }

        public static string CreateKey()
        {
            string Numbers = "123456789";
            string ItemKey;

            Random RandomGenerator = new Random();

            char[] Buffer = new char[16];

            for (int Counter = 0; Counter < 16; Counter++)
            {
                Buffer[Counter] = Numbers[RandomGenerator.Next(Numbers.Length)];
            }

            ItemKey = new string(Buffer);

            using (SqlConnection SQLConnection = new SqlConnection(Main.SQLInformation))
            {
                SQLConnection.Open();

                SqlCommand ExecuteCommand = new SqlCommand("SELECT * FROM tItem WHERE nItemKey = @ItemKey", SQLConnection);
                ExecuteCommand.Parameters.AddWithValue("@ItemKey", ItemKey);

                SqlDataReader ExecuteReader = ExecuteCommand.ExecuteReader();

                if (ExecuteReader.HasRows)
                {
                    CreateKey();
                }
                else
                {
                    return ItemKey;
                }
            }
            return ItemKey;
        }

    }
}