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

namespace GeoTema
{
    class Sql
    {
        private string ConnectionString = "Data Source=WIN-EOBHB6BVE3D; User Id=" + MainMenu.User + "; Password=" + MainMenu.Pass + "; Trusted_Connection=True; Integrated Security=False;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False";

        public string ConString() 
        {
            string C = ConnectionString;
            return C;
        }
        public static void SQLConnect(string SQL, List<string> Params) //This method opens a connection to the SQL database and executes an SQLcommand.
        {
            Sql CS = new Sql();
            using (SqlConnection con = new SqlConnection(CS.ConString()))
            {
                con.Open();               
                SqlCommand cmd = new SqlCommand(SQL, con);
                if (Params.Count == 1)
                {
                    cmd.Parameters.Add(new SqlParameter("@User", Params[0]));
                }
                else if (Params.Count == 2)
                {
                    cmd.Parameters.Add(new SqlParameter("@User", Params[0]));
                    cmd.Parameters.Add(new SqlParameter("@Pass", Params[1]));
                }
                else if (Params.Count == 3)
                {
                    cmd.Parameters.Add(new SqlParameter("@Country", Params[0]));
                    cmd.Parameters.Add(new SqlParameter("@Rank", Params[1]));
                    cmd.Parameters.Add(new SqlParameter("@Birth", Params[2]));
                }
                cmd.ExecuteNonQuery();                
            }
        }
        public static string SQLCheck(string SQL) //This method first checks whether a user exists in the database and if so, returns that user's usertype
        {
            Sql CS = new Sql();
            string type = "";
            DataTable table = new DataTable();
            try
            {
                using (SqlConnection con = new SqlConnection(CS.ConString()))
                {
                    con.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(SQL, con);
                    adapter.Fill(table);
                }
                if (table.Rows.Count > 0)
                {
                    type = table.Rows[0]["Usertype"].ToString();
                }
                else
                    MessageBox.Show("User doesn't exist! Please contact a database administrator.");
            }
            catch
            {                
                MessageBox.Show("Incorrect username or password!");
            }

            return type;
        }
        public static bool ExistCheck(string SQL, string var) //This method checks whether the inputted data exists in the database and returns either true or false
        {
            Sql CS = new Sql();
            bool Exists = false;
            DataTable table = new DataTable();
            using (SqlConnection con = new SqlConnection(CS.ConString()))
            {
                con.Open();
                SqlDataAdapter adapter = new SqlDataAdapter(SQL, con);
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@Exist", var));
                adapter.Fill(table);
            }
            if (table.Rows.Count > 0)
                Exists = true;
            else
                Exists = false;

            return Exists;
        }
    }
}