using DbAPI.Connections;
using DbAPI.Errors;
using DTS.Common.Interface.Database;
using System;
using System.Data;
using System.Data.SqlClient;
using DTS.Common.Interface.Groups.GroupList;
using System.Collections.Generic;
using DTS.Common.Interface.Groups;
using DTS.Common.Classes.Groups;
using DbAPI.Logging;
using System.Diagnostics;

namespace DbAPI.Groups
{
    /// <summary>
    /// Handles channel functions
    /// </summary>
    internal class Groups : IGroups
    {
        public ulong GroupsInsert(IUserDbRecord user,
            IConnectionDetails connection,
            ref IGroupDbRecord group)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_GroupsInsert");
            if (ret != ErrorCodes.ERROR_SUCCESS)
            {
                return ret;
            }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 255) { Value = group.SerialNumber });
                cmd.Parameters.Add(new SqlParameter("@DisplayName", SqlDbType.NVarChar, 255) { Value = group.DisplayName });
                cmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar, 255) { Value = group.Description });
                cmd.Parameters.Add(new SqlParameter("@Embedded", SqlDbType.Bit) { Value = group.Embedded });
                cmd.Parameters.Add(new SqlParameter("@LastModified", SqlDbType.DateTime) { Value = group.LastModified });
                cmd.Parameters.Add(new SqlParameter("@LastModifiedBy", SqlDbType.NVarChar, 255) { Value = group.LastModifiedBy });
                cmd.Parameters.Add(new SqlParameter("@StaticGroupId", SqlDbType.Int) { Value = group.StaticGroupId });
                cmd.Parameters.Add(new SqlParameter("@ExtraProperties", SqlDbType.NVarChar, -1) { Value = group.ExtraProperties ?? "" });

                var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumber);

                var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorMessage);

                var newId = new SqlParameter("@new_id", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(newId);

                cmd.ExecuteNonQuery();

                if (null != errorNumber.Value)
                {
                    if (Convert.ToInt32(errorNumber.Value) != 0)
                    {
                        LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"GroupsInsert failed: {errorNumber.Value} {errorMessage.Value}");
                        return ErrorCodes.ERROR_UNKNOWN;
                    }
                }

                group.Id = Convert.ToInt32(newId.Value);
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"GroupsInsert failed: {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
                cmd.Dispose();
            }
        }

        public ulong GroupsUpdate(IUserDbRecord user,
            IConnectionDetails connection,
            IGroupDbRecord group)
        {
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_GroupsUpdate");
            if (ret != ErrorCodes.ERROR_SUCCESS)
            {
                return ret;
            }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = group.Id });
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar, 255) { Value = group.SerialNumber });
                cmd.Parameters.Add(new SqlParameter("@DisplayName", SqlDbType.NVarChar, 255) { Value = group.DisplayName });
                cmd.Parameters.Add(new SqlParameter("@Description", SqlDbType.NVarChar, 255) { Value = group.Description });
                cmd.Parameters.Add(new SqlParameter("@Embedded", SqlDbType.Bit) { Value = group.Embedded });
                cmd.Parameters.Add(new SqlParameter("@LastModified", SqlDbType.DateTime) { Value = group.LastModified });
                cmd.Parameters.Add(new SqlParameter("@LastModifiedBy", SqlDbType.NVarChar, 255) { Value = group.LastModifiedBy });
                cmd.Parameters.Add(new SqlParameter("@StaticGroupId", SqlDbType.Int) { Value = group.StaticGroupId });
                cmd.Parameters.Add(new SqlParameter("@ExtraProperties", SqlDbType.NVarChar, -1) { Value = group.ExtraProperties });

                var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumber);

                var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorMessage);

                cmd.ExecuteNonQuery();

                if (!DBNull.Value.Equals(errorNumber.Value) && 0 != Convert.ToInt32(errorNumber.Value))
                {
                    LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"GroupsUpdate failed - {errorNumber.Value} {errorMessage.Value}");
                    return ErrorCodes.ERROR_UNKNOWN;
                }
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"GroupsUpdate failed - {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }

        }
        public ulong GroupsGet(IUserDbRecord user,
            IConnectionDetails connection,
            int? id,
            string serialNumber,
            string displayName,
            bool? embedded,
            int? staticGroupId,
            out IGroupDbRecord[] groups)
        {
            groups = null;
            var list = new List<IGroupDbRecord>();
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_GroupsGet");
            if (ret != ErrorCodes.ERROR_SUCCESS)
            {
                return ret;
            }
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = id });
                cmd.Parameters.Add(new SqlParameter("@SerialNumber", SqlDbType.NVarChar) { Value = serialNumber });
                cmd.Parameters.Add(new SqlParameter("@DisplayName", SqlDbType.NVarChar) { Value = displayName });
                cmd.Parameters.Add(new SqlParameter("@Embedded", SqlDbType.Bit) { Value = embedded });
                cmd.Parameters.Add(new SqlParameter("@StaticGroupId", SqlDbType.Int) { Value = staticGroupId });

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        list.Add(new GroupDbRecord(reader));
                    }
                    groups = list.ToArray();
                }
                return ErrorCodes.ERROR_SUCCESS;
            }
            catch (Exception ex)
            {
                LogManager.Log(TraceEventType.Error, LogManager.LogEvents.TestSetups, $"GroupsGet failed - {ex.Message}");
                return ErrorCodes.ERROR_UNKNOWN;
            }
            finally
            {
                cmd.Connection.Dispose();
            }
        }

        public ulong GroupsDelete(IUserDbRecord user,
            IConnectionDetails connection,
            long id,
            out string errorString)
        {
            errorString = string.Empty;
            if (!DbAPI.Connections.IsUserLoggedIn(user, connection))
            {
                return ErrorCodes.ERROR_ACCESS_DENIED;
            }
            var ret = ConnectionManager.GetSqlCommand(connection, out var cmd, "sp_GroupsDelete");
            if (ret != ErrorCodes.ERROR_SUCCESS)
            {
                return ret;
            }
            var errorNumberULong = ErrorCodes.ERROR_SUCCESS;
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@Id", SqlDbType.Int) { Value = id });

                var errorNumber = new SqlParameter("@errorNumber", SqlDbType.Int) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorNumber);

                var errorMessage = new SqlParameter("@errorMessage", SqlDbType.NVarChar, 255) { Direction = ParameterDirection.Output };
                cmd.Parameters.Add(errorMessage);

                cmd.ExecuteNonQuery();
                if (null != errorNumber.Value && !DBNull.Value.Equals(errorNumber.Value))
                {
                    if (0 != Convert.ToInt32(errorNumber.Value))
                    {
                        errorNumberULong = Convert.ToUInt64(errorNumber.Value);
                        errorString = (string)errorMessage.Value;
                    }
                }
            }
            catch (Exception ex)
            {
                //Concatenate any error string returned from the stored procedure call
                errorString = $"{ex.Message}; {errorString}";
                return errorNumberULong;
            }
            finally
            {
                cmd.Connection.Dispose();
            }

            return errorNumberULong;
        }
    }
}