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

namespace ChatProject.Layers.Business
{
    public class Connection
    {

        private static string sql_query = null;
        private static string server_address = "ise172.ise.bgu.ac.il,1433\\DB_LAB";
        //private static string server_address = "LAPTOP-C1281MRK\\SQLEXPRESS";
        private static string database_name = "MS3";
        private static string user_name = "publicUser";
        private static string password = "isANerd";
        private static string connection_string = $"Data Source={server_address};Initial Catalog={database_name};User ID = {user_name};Password={password};";
        // private static string connection_string = $"Data Source={server_address};Initial Catalog={database_name};Persist Security Info=False;";
        SqlConnection connection = new SqlConnection(connection_string);
        SqlCommand command;
        SqlDataReader reader;

        public Connection() { }

        //insert a new user to the database
        public void writeUserToDatabase(int groupid, string username, string password)
        {//"SET IDENTITY_INSERT dbo.Users ON" +
            sql_query = $" INSERT INTO [dbo].[Users] (Group_Id,Nickname,Password) VALUES ('{groupid}','{username}','{password}')";
            command = new SqlCommand(sql_query, connection);
            try
            {
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception e)
            {
                connection.Close();
                throw new Exception(e.Message);
            }
        }

        //edit the message content by user id.
        public void editMsgById(int userid, string msg)
        {
            sql_query = $"UPDATE dbo.Messages SET Body = '{msg}' WHERE User_Id = {userid}";
            command = new SqlCommand(sql_query, connection);
            try
            {
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception e)
            {
                connection.Close();
                throw new Exception(e.Message);
            }
        }

        //check if the user exist by his nick, group id, password.
        public bool userExist(string nickname, int groupid, string password)
        {
            bool isExist = false;
            sql_query = $"SELECT IIF(Nickname='{nickname}' AND Group_Id='{groupid}' AND Password='{password}',1,0) as excist FROM dbo.Users;";
            command = new SqlCommand(sql_query, connection);
            try
            {
                connection.Open();
                reader = command.ExecuteReader();
            }
            catch (Exception e)
            {
                reader.Close();
                connection.Close();
                throw new Exception(e.Message);
            }
            while (reader.Read())
            {
                if (reader.GetInt32(0) == 1)
                    isExist = true;
            }
            reader.Close();
            connection.Close();
            return isExist;
        }

        //return the user by his nick, group id and password.
        public User getUser(string nickname, int groupid, string password)
        {
            User user = null;
            try
            {
                if (userExist(nickname, groupid, password))
                {
                    connection.Open();
                    sql_query = $"SELECT * FROM Users WHERE Nickname='{nickname}' AND Group_Id='{groupid}' AND Password='{password}'";
                    command = new SqlCommand(sql_query, connection);
                    reader = command.ExecuteReader();
                    if (reader.Read())
                        user = new User(reader.GetString(2), reader.GetInt32(1).ToString(), reader.GetString(3));
                    reader.Close();
                    connection.Close();
                }
            }
            catch (Exception e)
            {
                reader.Close();
                connection.Close();
                throw new Exception(e.Message);
            }
            return user;
        }

        //return the user from the database by his id.
        public User userFromId(int id)
        {
            User user = null;
            sql_query = $"SELECT * FROM Users WHERE Id = '{id}'";
            command = new SqlCommand(sql_query, connection);
            try
            {
                connection.Open();
                reader = command.ExecuteReader();
                if (reader.Read())
                    user = new User(reader.GetString(2), reader.GetString(1), reader.GetString(3));
                reader.Close();
                connection.Close();
            }
            catch (Exception e)
            {
                reader.Close();
                connection.Close();
                throw new Exception(e.Message);
            }
            return user;
        }

        //retrieve last 200 msg.
        public List<Message> retrieveLastMsg() {
            int i = 0;
            List<Message> messages = new List<Message>();
            sql_query = "SELECT * FROM Messages INNER JOIN Users ON Messages.User_Id = Users.Id ORDER BY Messages.SendTime";
            command = new SqlCommand(sql_query, connection);
            try
            {
                connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read() | i < 200)
                {
                    Guid guid = Guid.Parse(reader.GetValue(0).ToString());
                    string username = reader.GetString(6);
                    DateTime date = reader.GetDateTime(2).ToLocalTime();
                    string content = reader.GetString(3).Trim();
                    string groupid = reader.GetInt32(5).ToString();

                    //the method should change by changes in the const requirements.
                    Message msg = new Message(guid, username, date, content, groupid);
                    messages.Add(msg);
                    i++;
                }
                reader.Close();
                connection.Close();
            }
            catch (Exception e)
            {
                reader.Close();
                connection.Close();
                throw new Exception(e.Message);
            }
            return messages;
        }
    }
}