using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using DTS.Common.Utilities.Logging;

// ReSharper disable once CheckNamespace
namespace DTS.Common.Storage
{
    /// <summary>
    /// this class is used for migration from 52 to 53 in the database
    /// it handles moving the users from the old structures to the new ones
    /// http://fogbugz/fogbugz/default.asp?9749
    /// </summary>
    public class UserMigrationHelper
    {
        public string UserName { get; }
        public string DisplayName { get; }
        public string Password { get; }
        public short Role { get; }
        public DateTime LastModified { get; }
        public string LastModifiedBy { get; }
        public bool LocalOnly { get; }
        public Dictionary<string, short> Permissions { get; }
        public Dictionary<string, bool> Visibility { get; }
        public int[] TagIds { get; }

        public UserMigrationHelper(DataRow row)
        {
            UserName = Convert.ToString(row["UserName"]);
            DisplayName = Convert.ToString(row["DisplayName"]);
            Password = Convert.ToString(row["Password"]);
            Permissions = GetPermissionsFromString(Convert.ToString(row["IUIItemPermissions"]));
            Visibility = GetVisibilityFromString(Convert.ToString(row["IUIItemVisibility"]));
            Role = Convert.ToInt16(row["Role"]);
            LastModified = Convert.ToDateTime(row["LastModified"]);
            LastModifiedBy = Convert.ToString(row["LastModifiedBy"]);
            LocalOnly = Convert.ToBoolean(row["LocalOnly"]);
            var tagObject = row["UserTags"];
            if (!DBNull.Value.Equals(tagObject))
            {
                var tags = new List<int>(GetTags((byte[])row["UserTags"]).Distinct());
                tags.Remove(0);
                TagIds = tags.ToArray();
            }
            else
            {
                TagIds = new int[0];
            }
        }

        private static Dictionary<string, short> GetPermissionsFromString(string sPermissions)
        {
            var lookup = new Dictionary<string, short>();
            var tokens = sPermissions.Split(',');
            foreach (var token in tokens)
            {
                var subtokens = token.Split('=');
                if (2 != subtokens.Length) { continue; }
                lookup[subtokens[0]] = Convert.ToInt16(subtokens[1]);
            }
            return lookup;
        }

        private static Dictionary<string, bool> GetVisibilityFromString(string sVisibilities)
        {
            var lookup = new Dictionary<string, bool>();
            var tokens = sVisibilities.Split(',');
            foreach (var token in tokens)
            {
                var subtokens = token.Split('=');
                if (2 != subtokens.Length) { continue; }
                lookup[subtokens[0]] = Convert.ToBoolean(Convert.ToInt32(subtokens[1]));
            }
            return lookup;
        }

        private static int[] GetTags(byte[] bytes)
        {
            if (!bytes.Any()) return new int[0];
            var tags = new int[bytes.Length / sizeof(int)];
            try
            {
                Buffer.BlockCopy(bytes, 0, tags, 0, bytes.Length);
                return tags;
            }
            catch (Exception ex)
            {
                APILogger.Log(ex);
            }
            return new int[0];
        }

        // ReSharper disable once InconsistentNaming
        public void Commit(Dictionary<string, long> IUIItemNameToID)
        {
            int id;
            using (var cmd = DbOperations.GetCommand())
            {
                try
                {
                    cmd.CommandText = "INSERT INTO [DataPROUsers] ([UserName],[DisplayName],[Password],[Role],[LastModified],[LastModifiedBy],[LocalOnly]) VALUES (@1,@2,@3,@4,@5,@6,@7)";
                    DbOperations.CreateParam(cmd, "@1", SqlDbType.NVarChar, UserName);
                    DbOperations.CreateParam(cmd, "@2", SqlDbType.NVarChar, DisplayName);
                    DbOperations.CreateParam(cmd, "@3", SqlDbType.NVarChar, Password);
                    DbOperations.CreateParam(cmd, "@4", SqlDbType.SmallInt, Role);
                    DbOperations.CreateParam(cmd, "@5", SqlDbType.DateTime, LastModified);
                    DbOperations.CreateParam(cmd, "@6", SqlDbType.NVarChar, LastModifiedBy);
                    DbOperations.CreateParam(cmd, "@7", SqlDbType.Bit, LocalOnly);

                    //DbOperations.Connection.ExecuteCommand(cmd);
                    if (DbOperations._usingMSSQL)
                    {
                        DbOperations.Connection.ExecuteCommand(cmd);
                    }
                    else
                    {
                        DbOperations.Connection.ExecuteSQLiteCommand(cmd);
                    }

                    using (var nestedCmd = DbOperations.GetCommand())
                    {
                        try
                        {
                            nestedCmd.CommandText = "SELECT [ID] FROM [DataPROUsers] WHERE [UserName]=@1";
                            DbOperations.CreateParam(nestedCmd, "@1", SqlDbType.NVarChar, UserName);
                            using (var ds = DbOperations.Connection.QueryDataSet(nestedCmd))
                            {
                                var dr = ds.Tables[0].Rows[0];
                                id = Convert.ToInt32(dr["ID"]);
                            }
                        }
                        finally
                        {
                            nestedCmd.Connection.Dispose();
                        }
                    }
                }
                finally
                {
                    cmd.Connection.Dispose();
                }
            }
            if (Permissions.Any())
            {
                IDbCommand cmd = null;
                StringBuilder sb = null;
                var i = 0;
                using (var e = Permissions.GetEnumerator())
                {
                    while (e.MoveNext())
                    {
                        if (null == cmd)
                        {
                            cmd = DbOperations.GetCommand();
                            sb = new StringBuilder();
                            sb.Append(DbOperations.BEGIN_STATEMENT);
                        }

                        var permission = e.Current.Value;
                        var visible = !Visibility.ContainsKey(e.Current.Key) || Visibility[e.Current.Key];

                        sb.AppendFormat(
                            "INSERT INTO UserUIItemSettings (UserID, UIItemID, Permission, Visible) VALUES (@{0}_1, @{0}_2, @{0}_3, @{0}_4);",
                            i);

                        DbOperations.CreateParam(cmd, string.Format("@{0}_1", i), SqlDbType.Int, id);
                        DbOperations.CreateParam(cmd, string.Format("@{0}_2", i), SqlDbType.BigInt,
                            IUIItemNameToID[e.Current.Key]);
                        DbOperations.CreateParam(cmd, string.Format("@{0}_3", i), SqlDbType.SmallInt, permission);
                        DbOperations.CreateParam(cmd, string.Format("@{0}_4", i), SqlDbType.BigInt, visible);

                        if (0 == i % 25 && 0 != i)
                        {
                            sb.Append(DbOperations.COMMIT_STATEMENT);
                            cmd.CommandText = sb.ToString();
                            //DbOperations.Connection.ExecuteCommand(cmd);
                            if (DbOperations._usingMSSQL)
                            {
                                DbOperations.Connection.ExecuteCommand(cmd);
                            }
                            else
                            {
                                DbOperations.Connection.ExecuteSQLiteCommand(cmd);
                            }

                            cmd.Connection.Dispose();
                            cmd.Dispose();
                            sb = null;
                            cmd = null;
                        }

                        i++;
                    }
                }

                if (null != cmd)
                {
                    sb.Append(DbOperations.COMMIT_STATEMENT);
                    cmd.CommandText = sb.ToString();
                    //DbOperations.Connection.ExecuteCommand(cmd);
                    if (DbOperations._usingMSSQL)
                    {
                        DbOperations.Connection.ExecuteCommand(cmd);
                    }
                    else
                    {
                        DbOperations.Connection.ExecuteSQLiteCommand(cmd);
                    }
                    cmd.Connection.Dispose();
                    cmd.Dispose();
                }
            }

            // ReSharper disable once InvertIf
            if (TagIds.Any())
            {
                IDbCommand cmd = null;
                StringBuilder sb = null;
                var i = 0;
                foreach (var tag in TagIds)
                {
                    if (null == cmd)
                    {
                        cmd = DbOperations.GetSQLCommand();
                        sb = new StringBuilder();
                        sb.Append(DbOperations.BEGIN_STATEMENT);
                    }

                    sb.Append(string.Format("INSERT INTO [TagAssignments] ([ObjectID],[ObjectType],[TagID]) VALUES (@{0}_1,@{0}_2,@{0}_3);", i));

                    DbOperations.CreateParam(cmd, string.Format("@{0}_1", i), SqlDbType.Int, id);
                    DbOperations.CreateParam(cmd, string.Format("@{0}_2", i), SqlDbType.SmallInt, 0);
                    DbOperations.CreateParam(cmd, string.Format("@{0}_3", i), SqlDbType.Int, tag);
                    i++;
                }

                if (null == sb)
                {
                    return;
                }
                sb.Append(DbOperations.COMMIT_STATEMENT);
                cmd.CommandText = sb.ToString();
                //DbOperations.Connection.ExecuteCommand(cmd);
                if (DbOperations._usingMSSQL)
                {
                    DbOperations.Connection.ExecuteCommand(cmd);
                }
                else
                {
                    DbOperations.Connection.ExecuteSQLiteCommand(cmd);
                }
                cmd.Connection.Dispose();
            }
        }
    }
}