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 List<KeyValuePair<int, SurveyItem>> SurveyItemsSorted { get; set; } = new(); /// wrap for sorting
        public bool IsNew { get; set; } = true;
        public bool IsDeleted { get; set; } = false;

        public async Task LoadByID(int id)
        {
            string sql = $"SELECT * FROM surveys WHERE id={id}";
            await Load(sql);
        }

        public async Task LoadByEventID(int eventID)
        {
            string sql = $"SELECT * FROM surveys WHERE eventid={eventID}";
            await Load(sql);
        }

        private async Task Load(string sql)
        {            
            var surveys = await MySQLConnector.Instance().SQLSelectComplex(sql);
            if (surveys.Count > 0)
            {
                EvenID = Convert.ToInt32(surveys[0]["eventid"]);
                ID = Convert.ToInt32(surveys[0]["id"]);
                Name = Convert.ToString(surveys[0]["name"]);
                Description = Convert.ToString(surveys[0]["description"]);
                DateCreated = Convert.ToDateTime(surveys[0]["date_created"]);
                DateUpdated = Convert.ToDateTime(surveys[0]["date_updated"]);
                CreatorID = Convert.ToString(surveys[0]["creatorid"]);
                IsNew = false;
                
                Console.WriteLine($"Loading Survey, ID: {ID}, Name: {Name}");
            }

            SurveyItem item;
            SurveyItemOption option;
            int itemPosition = 1;
            int optionPosition = 1;

            var surveyItems = await MySQLConnector.Instance().SQLSelectComplex($"SELECT * FROM surveyitems WHERE surveyid={ID} AND deleted<>1 ORDER BY position");
            if (surveyItems.Count > 0)
            {
                foreach (var i in surveyItems)
                {
                    item = new()
                    {
                        ID = Convert.ToInt32(i["id"]),
                        SurveyID = Convert.ToInt32(i["surveyid"]),
                        DateCreated = Convert.ToDateTime(i["date_created"]),
                        DateUpdated = Convert.ToDateTime(i["date_updated"]),
                        CreatorID = Convert.ToString(i["creatorid"]),
                        Text = Convert.ToString(i["itemtext"]),                        
                        Position = itemPosition++,
                        Required = Convert.ToBoolean(i["required"]),
                        IsNew = false
                    };
                    Console.WriteLine($"Loading SurveyItem, survey: {item.SurveyID}, item id: {item.ID}, position: {item.Position}, name: {item.Text}");

                    var surveyItemOption = await MySQLConnector.Instance().SQLSelectComplex($"SELECT * FROM surveyitemoptions WHERE itemid={item.ID} AND deleted<>1 ORDER BY position");
                    if (surveyItemOption.Count > 0)
                    {
                        foreach (var o in surveyItemOption)
                        {
                            option = new()
                            {
                                ID = Convert.ToInt32(o["id"]),
                                ItemID = Convert.ToInt32(o["itemid"]),
                                DateCreated = Convert.ToDateTime(o["date_created"]),
                                DateUpdated = Convert.ToDateTime(o["date_updated"]),
                                CreatorID = Convert.ToString(o["creatorid"]),
                                Text = Convert.ToString(o["optiontext"]),                                
                                Position = optionPosition++,
                                Rate1 = Convert.ToInt32(o["rate1"]),
                                Rate2 = Convert.ToInt32(o["rate2"]),
                                Rate3 = Convert.ToInt32(o["rate3"]),
                                Rate4 = Convert.ToInt32(o["rate4"]),
                                IsNew = false
                            };
                            Console.WriteLine($"Loading SurveyItemOption, survey: {item.SurveyID}, item {item.ID}, option id: {option.ID}, position: {option.Position}, name: {option.Text}.");

                            item.SurveyItemOptions.Add(option.ID, option);
                        }
                    }
                    SurveyItems.Add(item.ID, item);
                    item.SortOptions();
                }
                SortItems();
            }
        }

        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 void SortItems()
        {
            int itemPosition = 1;
            SurveyItemsSorted = SurveyItems.OrderBy(i => i.Value.Position).ToList();
            foreach (var item in SurveyItemsSorted) { item.Value.Position = itemPosition++; }
        }
    }

    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 void SortOptions()
        {
            int optionPosition = 1;
            SurveyItemOptionsSorted = SurveyItemOptions.OrderBy(i => i.Value.Position).ToList();
            foreach (var item in SurveyItemOptionsSorted) { item.Value.Position = optionPosition++; }
        }

        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();
        }
    }
}