HyperCube/Models/Survey.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Console = HyperCube.Utils.AdvConsole;

namespace HyperCube.Models
{
    public class Survey
    {        
        public int ID { get; set; }
        public int EvenID { get; set; }
        public string Name { get; set; }
        public string Description { get; set; }
        public DateTime DateCreated { get; set; }
        public DateTime DateUpdated { get; set; }
        public string CreatorID { get; set; }
        public Dictionary<int, SurveyItem> SurveyItems { get; set; } = new();
        public bool IsNew { get; set; } = true;
        public bool IsDeleted { get; set; } = false;

        public async Task<int> Save(string userID)
        {
            long newID;
            string stringSQL;

            if (IsNew)
            {
                stringSQL = $"INSERT INTO surveys (eventid, name, description, creatorid) " +
                    $"VALUES ({EvenID}, '{Name}', '{Description}', '{userID}')";
            }
            else
            {
                stringSQL = $"UPDATE surveys " +
                    $"SET eventid={EvenID}, name='{Name}', description='{Description}', deleted={Convert.ToInt32(IsDeleted)},  date_updated='{DateTime.Now:yyyy-MM-dd HH:mm:ss}' " +
                    $"WHERE id={ID}";
            }

            newID = await MySQLConnector.Instance().SQLInsert(stringSQL);
            IsNew = false;

            /// saving survey items
            int newItemID;
            Dictionary<int, SurveyItem> tmpSurveyItems = new();
            foreach (var item in SurveyItems)
            {
                if (newID != 0)
                    item.Value.SurveyID = (int)newID;                

                newItemID = await item.Value.Save(userID);
                if (newItemID != 0 && item.Key != newItemID)
                {
                    Console.WriteLine($"Adding and updating item id:{item.Key} with new id:{newItemID}");
                    tmpSurveyItems.Add(newItemID, item.Value);
                }
                else
                {
                    Console.WriteLine($"Adding existed item id:{item.Key}");
                    tmpSurveyItems.Add(item.Key, item.Value);
                }

                /// saving survey item options
                int newOptionID;
                Dictionary<int, SurveyItemOption> tmpSurveyItemOption = new();
                foreach (var option in item.Value.SurveyItemOptions)
                {
                    if (newItemID != 0)
                        option.Value.ItemID = newItemID;

                    newOptionID = await option.Value.Save(userID);
                    if (newOptionID != 0 && option.Key != newOptionID)
                    {
                        Console.WriteLine($"Adding and updating option id:{option.Key} with new id:{newOptionID}");
                        tmpSurveyItemOption.Add(newOptionID, option.Value);
                    }
                    else
                    {
                        Console.WriteLine($"Adding existed option id:{option.Key}");
                        tmpSurveyItemOption.Add(option.Key, option.Value);
                    }
                }

                /// updating options collection with actual IDs from DB
                if (tmpSurveyItemOption.Count > 0)
                {
                    item.Value.SurveyItemOptions.Clear();
                    item.Value.SurveyItemOptions = tmpSurveyItemOption.ToDictionary(entry => entry.Key, entry => (SurveyItemOption)entry.Value.Clone());
                }
            }

            /// updating items collection with actual IDs from DB
            if (tmpSurveyItems.Count > 0)
            {
                SurveyItems.Clear();
                SurveyItems = tmpSurveyItems.ToDictionary(entry => entry.Key, entry => (SurveyItem)entry.Value.Clone());
            }

            return ID = (int)newID;
        }

        public void AddNewItem(string userID)
        {
            int surveyID = ID;
            Console.WriteLine($"Survey AddNewItem. survey id={surveyID}.");
            int newTempKey = (SurveyItems.Count > 0) ? SurveyItems.Keys.Max() + 1 : 1;
            int position = (SurveyItems.Count > 0) ? SurveyItems.Select(o => o.Value.Position).Max() + 1 : 1;
            Console.WriteLine($"Survey AddNewItem. item newTempKey={newTempKey}.");
            SurveyItem item = new()
            {
                ID = newTempKey,
                SurveyID = surveyID,
                Position = position,
                CreatorID = userID,
                DateCreated = DateTime.Now
            };
            SurveyItems.Add(newTempKey, item);
        }

        public void DeleteItem(int itemID)
        {
            if (SurveyItems.Count > 0 && SurveyItems.ContainsKey(itemID))
            {
                Console.WriteLine($"Survey [{ID}] DeleteItem id:{SurveyItems[itemID].ID} - isnew:{SurveyItems[itemID].IsNew}");

                if (SurveyItems[itemID].IsNew)
                    SurveyItems.Remove(itemID);
                else
                    SurveyItems[itemID].IsDeleted = true;
            }
        }

        public void MoveItem(int itemID, int step)
        {
            if (SurveyItems.Count > 0 && SurveyItems.ContainsKey(itemID))
            {
                Console.WriteLine($"Survey:[{ID}] MoveItem id: [{SurveyItems[itemID].ID}]");

                int min = SurveyItems.Select(o => o.Value.Position).Min();
                int max = SurveyItems.Select(o => o.Value.Position).Max();

                Console.WriteLine($"min:{min}, max:{max}.");

                int oldPosition, newPosition;
                oldPosition = SurveyItems[itemID].Position;

                int targetPosition = (oldPosition + step < min) ? min : oldPosition + step;
                targetPosition = (oldPosition + step > max) ? max : oldPosition + step;

                Console.WriteLine($"oldPosition:{oldPosition}, step:{step}, target:{targetPosition}.");

                int targetKey = SurveyItems.FirstOrDefault(x => x.Value.Position == targetPosition).Key;                
                if (targetKey != 0)
                {
                    newPosition = SurveyItems[targetKey].Position;
                    Console.WriteLine($"newPosition:{newPosition}.");

                    SurveyItems[itemID].Position = newPosition;
                    SurveyItems[targetKey].Position = oldPosition;
                }
            }
        }
    }

    public class SurveyItem : ICloneable
    {
        public int ID { get; set; }
        public int SurveyID { get; set; }
        public string Text { get; set; }
        public int Position { get; set; }
        public string CreatorID { get; set; }
        public DateTime DateCreated { get; set; }
        public DateTime DateUpdated { get; set; }
        public bool Required { get; set; } = true;
        public Dictionary<int, SurveyItemOption> SurveyItemOptions { get; set; } = new();
        public List<KeyValuePair<int, SurveyItemOption>> SurveyItemOptionsSorted { get; set; } = new(); /// wrap for sorting
        public int AnswerID { get; set; } /// SurveyItemOption selected ID
        public bool IsNew { get; set; } = true;
        public bool IsDeleted { get; set; } = false;

        public async Task<int> Save(string userID)
        {
            long newID;
            string stringSQL;

            if (IsNew)
            {
                stringSQL = $"INSERT INTO surveyitems (surveyid, itemtext, position, required, creatorid) " +
                    $"VALUES ({SurveyID}, '{Text}', {Position}, {Convert.ToInt32(Required)}, '{userID}')";
            }
            else
            {
                stringSQL = $"UPDATE surveyitems " +
                    $"SET surveyid={SurveyID}, itemtext='{Text}', position={Position}, required={Convert.ToInt32(Required)}, deleted={Convert.ToInt32(IsDeleted)} " +
                    $"WHERE id={ID}";
            }

            newID = await MySQLConnector.Instance().SQLInsert(stringSQL);
            IsNew = false;
            return ID = (int)newID;
        }

        public void AddNewOption(string userID)
        {
            int itemID = ID;
            Console.WriteLine($"SurveyItem AddNewOption. item id={itemID}.");
            int newTempKey = (SurveyItemOptions.Count > 0) ? SurveyItemOptions.Keys.Max() + 1 : 1;
            int position = (SurveyItemOptions.Count > 0) ? SurveyItemOptions.Select(o => o.Value.Position).Max() + 1 : 1;
            Console.WriteLine($"SurveyItem AddNewOption. option newTempKey={newTempKey}.");
            SurveyItemOption option = new()
            {
                ID = newTempKey,
                ItemID = itemID,
                Position = position,
                CreatorID = userID,
                DateCreated = DateTime.Now
            };
            SurveyItemOptions.Add(newTempKey, option);
        }

        public void DeleteOption(int optionID)
        {
            if (SurveyItemOptions.Count > 0 && SurveyItemOptions.ContainsKey(optionID))
            {
                Console.WriteLine($"Survey [{SurveyID}] Item [{ID}] DeleteItem id:{SurveyItemOptions[optionID].ID} - isnew:{SurveyItemOptions[optionID].IsNew}");

                if (SurveyItemOptions[optionID].IsNew)
                    SurveyItemOptions.Remove(optionID);
                else
                    SurveyItemOptions[optionID].IsDeleted = true;
            }
        }

        public void MoveItem(int optionID, int step)
        {
            if (SurveyItemOptions.Count > 0 && SurveyItemOptions.ContainsKey(optionID))
            {
                Console.WriteLine($"Survey [{SurveyID}] Item [{ID}] MoveOption id:{SurveyItemOptions[optionID].ID}");

                int min = SurveyItemOptions.Select(o => o.Value.Position).Min();
                int max = SurveyItemOptions.Select(o => o.Value.Position).Max();

                Console.WriteLine($"min:{min}, max:{max}.");

                int oldPosition, newPosition;
                oldPosition = SurveyItemOptions[optionID].Position;

                int targetPosition = (oldPosition + step < min) ? min : oldPosition + step;
                targetPosition = (oldPosition + step > max) ? max : oldPosition + step;

                Console.WriteLine($"oldPosition:{oldPosition}, step:{step}, target:{targetPosition}.");

                int targetKey = SurveyItemOptions.FirstOrDefault(x => x.Value.Position == targetPosition).Key;
                if (targetKey != 0)
                {
                    newPosition = SurveyItemOptions[targetKey].Position;
                    Console.WriteLine($"newPosition:{newPosition}.");

                    SurveyItemOptions[optionID].Position = newPosition;
                    SurveyItemOptions[targetKey].Position = oldPosition;
                }
            }
        }

        public object Clone()
        {
            return MemberwiseClone();
        }
    }

    public class SurveyItemOption : ICloneable
    {
        public int ID { get; set; }
        public int ItemID { get; set; }
        public string Text { get; set; }
        public int Rate1 { get; set; }
        public int Rate2 { get; set; }
        public int Rate3 { get; set; }
        public int Rate4 { get; set; }
        public int Position { get; set; }
        public string CreatorID { get; set; }
        public DateTime DateCreated { get; set; }
        public DateTime DateUpdated { get; set; }
        public bool IsNew { get; set; } = true;
        public bool IsDeleted { get; set; } = false;

        public async Task<int> Save(string userID)
        {
            long newID;
            string stringSQL;

            if (IsNew)
            {
                stringSQL = $"INSERT INTO surveyitemoptions (itemid, optiontext, position, rate1, rate2, rate3, rate4, creatorid) " +
                    $"VALUES ({ItemID}, '{Text}', {Position}, {Rate1}, {Rate2}, {Rate3}, {Rate4}, '{userID}')";
            }
            else
            {
                stringSQL = $"UPDATE surveyitemoptions " +
                    $"SET itemid={ItemID}, optiontext='{Text}', position={Position}, rate1={Rate1}, rate2={Rate2}, rate3={Rate3}, rate4={Rate4}, deleted={Convert.ToInt32(IsDeleted)} " +
                    $"WHERE id={ID}";
            }

            newID = await MySQLConnector.Instance().SQLInsert(stringSQL);
            IsNew = false;
            return ID = (int)newID;
        }

        public object Clone()
        {
            return MemberwiseClone();
        }
    }
}