prs/prs.shared/Database Update Scripts/Update_8_58.cs

using Comal.Classes;
using InABox.Configuration;
using InABox.Core;
using InABox.Database;
using InABox.DynamicGrid;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using InABox.Database.SQLite;

namespace PRS.Shared.Database_Update_Scripts;

internal class Update_8_58 : DatabaseUpdateScript
{
    public override VersionNumber Version => new(8, 58);

    private static void UpdateTimeSheets(IProvider provider)
    {
        Logger.Send(LogType.Information, "", $"Migrating TimeSheet.Processed -> TimeSheet.Posted");

        var timeSheets = provider.Query(
            Filter<TimeSheet>.Where(x => x.Processed).IsNotEqualTo(DateTime.MinValue)
                .And(x => x.Posted).IsEqualTo(DateTime.MinValue),
            Columns.None<TimeSheet>()
                .Add(x => x.ID)
                .Add(x => x.Processed)
                .Add(x => x.Posted))
            .ToArray<TimeSheet>();
        if (timeSheets.Length == 0)
        {
            Logger.Send(LogType.Information, "", $"Migrating TimeSheet.Processed -> TimeSheet.Posted: Done");
            return;
        }
        Logger.Send(LogType.Information, "", $"Migrating TimeSheet.Processed -> TimeSheet.Posted: {timeSheets.Length} items");
        Utils.Utils.ProcessInChunks(
            timeSheets,
            chunk =>
            {
                foreach (var timeSheet in chunk)
                {
                    timeSheet.Posted = timeSheet.Processed;
                }
                provider.Save(chunk);
            },
            200,
            percentage => Logger.Send(LogType.Information, "", $"Migrating TimeSheet.Processed: {percentage:F2}%"));
    }

    private static void ConvertLink<T>(IProvider provider, string fromLink, string toLink)
        where T : Entity, new()
    {
        
        var fromColumn = new Column<T>(fromLink).SubColumn(new Column<IEntityLink>(x => x.ID));
        var toColumn = new Column<T>(toLink).SubColumn(new Column<IEntityLink>(x => x.ID));

        if (provider is SQLiteProvider p)
        {
            Logger.Send(LogType.Information, "", $"Migrating {typeof(T).Name}.{fromColumn.Property} -> {typeof(T).Name}.{toColumn.Property}");
            var sql =
                $"update {typeof(T).Name} set [{toColumn.Property}]=[{fromColumn.Property}] where [{fromColumn.Property}] is not null and [{toColumn.Property}] is null";
            p.Update(sql);
            return;
        }
        
        var items = provider.Query(
            Filter<T>.Where<Guid>(fromColumn).IsNotEqualTo(Guid.Empty)
                .And<Guid>(toColumn).IsEqualTo(Guid.Empty),
            Columns.None<T>()
                .Add(x => x.ID)
                .Add(toColumn) // The order here matters a lot; since I've made a lot of obsolete links just point directly to the new one, then we have to set the new before the old.
                .Add(fromColumn))
            .ToArray<T>();
        if (items.Length == 0)
        {
            return;
        }
        Logger.Send(LogType.Information, "", $"Migrating {typeof(T).Name}.{fromColumn.Property} -> {typeof(T).Name}.{toColumn.Property}: {items.Length} items");

        Utils.Utils.ProcessInChunks(
            items,
            chunk =>
            {
                foreach (var item in chunk)
                {
                    item.SetObserving(false);
                    var originalValue = toColumn.PropertyDefinition.Getter()(item);
                    toColumn.PropertyDefinition.Setter()(
                        item,
                        fromColumn.PropertyDefinition.Getter()(item));
                    item.SetOriginalValue(toColumn.Property, originalValue);
                    item.SetObserving(true);
                }
                provider.Save(chunk);
            },
            1000,
            percentage => Logger.Send(LogType.Information, "", $"Migrating {typeof(T).Name}.{fromColumn.Property}: {percentage:F2}%"));
    }
    private static void ConvertLink<T, TFromLink, TToLink>(IProvider provider, Expression<Func<T, TFromLink>> fromLink, Expression<Func<T, TToLink>> toLink)
        where T : Entity, new()
        where TFromLink : IEntityLink
        where TToLink : IEntityLink
    {
        ConvertLink<T>(provider, CoreUtils.GetFullPropertyName(fromLink, "."), CoreUtils.GetFullPropertyName(toLink, "."));
    }
    private static void ConvertLink<T, TLink>(IProvider provider, Expression<Func<T, TLink>> fromLink, Expression<Func<T, TLink>> toLink)
        where T : Entity, new()
        where TLink : IEntityLink
    {
        ConvertLink<T, TLink, TLink>(provider, fromLink, toLink);
    }

    private static string QuoteString(string str)
    {
        return $"\"{str.Replace("\\", "\\\\").Replace("\"", "\\\"")}\"";
    }

    private static void ConvertQAQuestions(IProvider provider)
    {
        var qaQuestions = provider.Query(
            Filter<QAQuestion>.Where(x => x.Converted).IsEqualTo(false),
            Columns.None<QAQuestion>()
                .Add(x => x.ID)
                .Add(x => x.Converted)
                .Add(x => x.Code)
                .Add(x => x.Question)
                .Add(x => x.Section)
                .Add(x => x.Description)
                .Add(x => x.Answer)
                .Add(x => x.Parameters)
                .Add(x => x.Sequence)
                .Add(x => x.Form.ID))
            .ToObjects<QAQuestion>()
            .GroupBy(x => x.Form.ID)
            .Select(x => new
            {
                FormID = x.Key,
                Questions = x.OrderBy(x => x.Sequence).ToList()
            })
            .ToList();
        Logger.Send(LogType.Information, "", $"Converting {qaQuestions.Count} QA forms into DigitalForms");
        var formSequences = provider.Query(
            Filter<DigitalFormVariable>.Where(x => x.Form.ID)
                .InList(qaQuestions.ToArray(x => x.FormID)),
            Columns.None<DigitalFormVariable>()
                .Add(x => x.Form.ID)
                .Add(x => x.Sequence))
            .ToObjects<DigitalFormVariable>()
            .GroupBy(x => x.Form.ID)
            .ToDictionary(x => x.Key, x => x.Max(x => x.Sequence));

        var variableMappings = new Dictionary<Guid, Dictionary<Guid, string>>();

        var variables = new List<DigitalFormVariable>();
        var layouts = new List<DigitalFormLayout>();
        foreach (var item in qaQuestions)
        {
            if (formSequences.TryGetValue(item.FormID, out var sequence))
            {
                sequence++;
            }
            else
            {
                sequence = 0;
            }

            var layout = new DFLayout();
            layout.ColumnWidths.Add("Auto");
            layout.ColumnWidths.Add("*");

            var codes = new HashSet<string>();
            string GenerateCode(string code)
            {
                var originalCode = code;
                var i = 1;
                while (codes.Contains(code))
                {
                    code = $"{originalCode}{i}";
                    ++i;
                }
                return code;
            }

            var mappings = variableMappings.GetValueOrAdd(item.FormID);

            var nButtons = 0;

            var i = 1;
            foreach (var question in item.Questions)
            {
                layout.RowHeights.Add("Auto");
                var row = layout.RowHeights.Count;

                if (question.Answer == QAAnswer.Comment)
                {
                    var label = new DFLayoutLabel { Caption = question.Question, Row = row, Column = 1, ColumnSpan = 3 };
                    label.Style.HorizontalTextAlignment = DFLayoutAlignment.Middle;
                    layout.Elements.Add(label);
                }
                else
                {
                    var rowNum = new DFLayoutLabel { Caption = i.ToString(), Row = row, Column = 1 };
                    var label = new DFLayoutLabel { Caption = question.Question, Row = row, Column = 2 };
                    layout.Elements.Add(rowNum);
                    layout.Elements.Add(label);

                    var variable = new DigitalFormVariable();
                    variable.Form.CopyFrom(question.Form);
                    variable.Sequence = sequence++;

                    DFLayoutFieldProperties properties;
                    Type fieldType;

                    var code = GenerateCode(question.Code.NotWhiteSpaceOr(question.Answer.ToString()));

                    var parameters = question.ParseParameters();
                    switch (question.Answer)
                    {
                        case QAAnswer.Choice:
                            {
                                // ColourExpression

                                var buttons = parameters["Options"].Split(',').ToArray(x => x.Trim());
                                var colors = parameters["Colors"].Split(',').ToArray(x => x.Trim());
                                var defValue = parameters["Default"].Trim();

                                fieldType = typeof(DFLayoutOptionField);
                                var optionProperties = new DFLayoutOptionFieldProperties();
                                properties = optionProperties;

                                optionProperties.Default = defValue;
                                optionProperties.OptionType = DFLayoutOptionType.Buttons;
                                optionProperties.Options = DFLayoutOptionFieldProperties.WriteOptions(buttons);

                                var colourExpression = "null";
                                foreach (var (option, colour) in buttons.Zip(colors))
                                {
                                    colourExpression = $"If([{code}] == {QuoteString(option)}, {QuoteString(colour)}, {colourExpression})";
                                }
                                optionProperties.ColourExpression = colourExpression;

                                nButtons = Math.Max(nButtons, buttons.Length);
                            }
                            break;
                        case QAAnswer.Number:
                            {
                                var defValue = parameters["Default"];

                                fieldType = typeof(DFLayoutDoubleField);
                                var doubleProperties = new DFLayoutDoubleFieldProperties();
                                properties = doubleProperties;

                                doubleProperties.Default = double.TryParse(defValue, out var d) ? d : default;
                            }
                            break;
                        case QAAnswer.Text:
                            {
                                var defValue = parameters["Default"];

                                fieldType = typeof(DFLayoutStringField);
                                var stringProperties = new DFLayoutStringFieldProperties();
                                properties = stringProperties;

                                stringProperties.Default = defValue;
                            }
                            break;
                        case QAAnswer.Combo:
                            {
                                var buttons = parameters["Options"].Split(',');
                                var defValue = parameters["Default"];

                                fieldType = typeof(DFLayoutOptionField);
                                var optionProperties = new DFLayoutOptionFieldProperties();
                                properties = optionProperties;

                                optionProperties.Default = defValue;
                                optionProperties.OptionType = DFLayoutOptionType.Combo;
                                optionProperties.Options = DFLayoutOptionFieldProperties.WriteOptions(buttons);
                            }
                            break;
                        default:
                            throw new Exception("Impossible");
                    }

                    properties.Code = code;
                    properties.Description = question.Description.NotWhiteSpaceOr(question.Question);
                    properties.Required = parameters.GetValueOrDefault("Default").IsNullOrWhiteSpace();

                    variable.SaveProperties(fieldType, properties);

                    mappings.Add(question.ID, variable.Code);
                    codes.Add(variable.Code);

                    var field = (Activator.CreateInstance(variable.FieldType()) as DFLayoutField)!;
                    field.Name = variable.Code;
                    field.Row = row;
                    field.Column = 3;
                    layout.Elements.Add(field);

                    variables.Add(variable);
                    ++i;
                }

                question.Converted = true;
            }

            layout.ColumnWidths.Add(Math.Max(150, nButtons * 80).ToString());

            var dfLayout = new DigitalFormLayout();
            dfLayout.Form.ID = item.FormID;
            dfLayout.Layout = layout.SaveLayout();
            dfLayout.Description = "Generated from QA form";
            dfLayout.Type = DFLayoutType.Desktop;
            dfLayout.Active = true;
            layouts.Add(dfLayout);
        }

        provider.Save(variables);
        provider.Save(layouts);
        provider.Save(qaQuestions.SelectMany(x => x.Questions));

        FormUpdater.UpdateAllForms(
            (form, variables) => false,
            (formType, instance, form, variables) =>
            {
                if (!variableMappings.TryGetValue(form.ID, out var mappings)) return false;

                var values = DigitalForm.DeserializeFormSaveData(instance) ?? new();
                var items = values.ToLoadStorage().Items().ToArray();
                foreach (var (key, value) in items)
                {
                    if (!Guid.TryParse(key, out var id)) continue;
                    if (!mappings.TryGetValue(id, out var code)) continue;

                    values.AddValue(code, value?.ToString()?.Trim());
                }

                DigitalForm.SerializeFormData(instance, values);
                return true;
            },
            filter: Filter<DigitalForm>.Where(x => x.ID).InList(qaQuestions.ToArray(x => x.FormID)));
    }

    private interface IExtraMap
    {
        public Type TFrom { get; }
        public Type TTo { get; }

        IProperty From { get; }

        IProperty To { get; }

        Func<object?, object?>? Map { get; }
    }

    private class ExtraMap<TFrom, TTo> : IExtraMap
    {
        Type IExtraMap.TFrom => typeof(TFrom);
        Type IExtraMap.TTo => typeof(TTo);

        public IProperty From { get; }

        public IProperty To { get; }

        public Func<object?, object?>? Map { get; init; } = null;

        public ExtraMap(Expression<Func<TFrom, object?>> from, Expression<Func<TTo, object?>> to)
        {
            From = DatabaseSchema.PropertyStrict(from);
            To = DatabaseSchema.PropertyStrict(to);
        }

        public ExtraMap(IProperty from, IProperty to)
        {
            From = from;
            To = to;
        }

        public static ExtraMap<TFrom, TTo> New<TFromValue, TToValue>(
            Expression<Func<TFrom, TFromValue>> from,
            Expression<Func<TTo, TToValue>> to,
            Func<TFromValue, TToValue> map)
        {
            return new(
                DatabaseSchema.PropertyStrict(from),
                DatabaseSchema.PropertyStrict(to))
            {
                Map = x => x is TFromValue tFrom ? map(tFrom) : default
            };
        }
    }

    private static void RenameTable<TFrom, TTo>(IProvider provider,
        List<ExtraMap<TFrom, TTo>>? extraMaps = null)
        where TFrom : Entity, new()
        where TTo : Entity, new()
    {
        var currentMaps = new HashSet<string>();

        var maps = new List<(IProperty from, IProperty to, Func<object?, object?>? map)>();

        foreach (var map in extraMaps ?? [])
        {
            currentMaps.Add(map.From.Name);

            maps.Add((map.From, map.To, map.Map));
        }

        foreach (var fromProperty in DatabaseSchema.LocalProperties(typeof(TFrom)))
        {
            if (currentMaps.Contains(fromProperty.Name)) continue;

            if (DatabaseSchema.Property(typeof(TTo), fromProperty.Name) is IProperty toProperty)
            {
                if (fromProperty.PropertyType != toProperty.PropertyType)
                {
                    throw new Exception($"Cannot migrate {typeof(TFrom).Name}.{fromProperty.Name} -> {typeof(TTo).Name}.{toProperty.Name}: type mismatch");
                }
                maps.Add((fromProperty, toProperty, null));
            }
            else
            {

            }
        }

        var items = provider.Query<TFrom>(
            Filter<TFrom>.Where(x => x.ID).NotInQuery(Filter.All<TTo>(), x => x.ID),
            Columns.None<TFrom>()
                .Add(x => x.ID)
                .Add(maps.Select(x => new Column<TFrom>(x.from))))
            .ToArray<TFrom>();
        if (items.Length == 0) return;

        Logger.Send(LogType.Information, "", $"Migrating {typeof(TFrom).Name} -> {typeof(TTo).Name}: {items.Length} items");

        Utils.Utils.ProcessInChunks(
            items,
            chunk =>
            {
                var newItems = new List<TTo>();
                foreach (var item in chunk)
                {
                    var newItem = new TTo();
                    newItem.SetObserving(false);
                    newItem.ID = item.ID;
                    foreach (var (from, to, map) in maps)
                    {
                        if (map is not null)
                        {
                            to.Setter()(newItem, map(from.Getter()(item)));
                        }
                        else
                        {
                            to.Setter()(newItem, from.Getter()(item));
                        }
                    }
                    newItem.SetObserving(true);
                    newItems.Add(newItem);
                }
                provider.Save(newItems);
            },
            1000,
            percentage => Logger.Send(LogType.Information, "", $"Migrating {typeof(TFrom).Name}: {percentage:F2}%"));

        ConvertColumnsTag(provider, typeof(TFrom).Name, typeof(TTo).Name);
        UpdateAutoSecurityTokens<TFrom, TTo>(provider);
    }

    private static void ConvertLinks(IProvider provider)
    {
        ConvertLink<Assignment, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<EquipmentAssignment, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<Kanban, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<Requisition, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<RequisitionItem, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<Setout, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<JobBillOfMaterialsActivity, JobLink>(provider, x => x.JobLink, x => x.Job);

        ConvertLink<ManufacturingSection, QAFormLink, DigitalFormLink>(provider, x => x.QAForm, x => x.DigitalForm);
        ConvertLink<ManufacturingTemplateStage, QAFormLink, DigitalFormLink>(provider, x => x.QAForm, x => x.DigitalForm);

        var method = typeof(Update_8_58).GetMethods(BindingFlags.Static | BindingFlags.NonPublic)
            .Where(x => x.Name == nameof(ConvertLink)
                && x.IsGenericMethod
                && x.GetGenericArguments().Length == 1)
            .First();
        foreach (var entity in DbFactory.ProviderFactory.Types.Where(x => x.HasInterface(typeof(IEntityDocument))))
        {
            var entityMethod = method.MakeGenericMethod(entity);

            var entityFromProp = DatabaseSchema.PropertyStrict<IEntityDocument>(entity, x => x.EntityLink);
            var entityToProp = DatabaseSchema.PropertyStrict<IEntityDocument>(entity, x => x.Entity);
            entityMethod.Invoke(null, [provider, entityFromProp.Name, entityToProp.Name]);

            var documentFromProp = DatabaseSchema.PropertyStrict<IEntityDocument>(entity, x => x.DocumentLink);
            var documentToProp = DatabaseSchema.PropertyStrict<IEntityDocument>(entity, x => x.Document);
            entityMethod.Invoke(null, [provider, documentFromProp.Name, documentToProp.Name]);
        }

        foreach (var entity in DbFactory.ProviderFactory.Types)
        {
            MethodInfo? entityMethod = null;
            foreach (var property in DatabaseSchema.LocalProperties(entity))
            {
                if (property.Parent is null
                    || property.Parent.PropertyType != typeof(LocalityLink)
                    || property.Parent.Parent is null
                    || property.Parent.Parent.PropertyType != typeof(Address)) continue;

                var addressProp = property.Parent.Parent;

                entityMethod ??= method.MakeGenericMethod(entity);

                var fromProp = DatabaseSchema.PropertyStrict(entity, addressProp.Name + "." + nameof(Address.LocalityLink));
                var toProp = DatabaseSchema.PropertyStrict(entity, addressProp.Name + "." + nameof(Address.Locality));
                entityMethod.Invoke(null, [provider, fromProp.Name, toProp.Name]);
            }
        }

        ConvertLink<Schedule, EmployeeLink>(provider, x => x.EmployeeLink, x => x.Employee);
        ConvertLink<Schedule, EmployeeLink>(provider, x => x.ManagerLink, x => x.Manager);
        ConvertLink<Assignment, EmployeeLink>(provider, x => x.EmployeeLink, x => x.Employee);
        ConvertLink<Assignment, AssignmentActivityLink>(provider, x => x.ActivityLink, x => x.Activity);
        ConvertLink<Assignment, LeaveRequestLink>(provider, x => x.LeaveRequestLink, x => x.LeaveRequest);
        ConvertLink<Bill, SupplierLink>(provider, x => x.SupplierLink, x => x.Supplier);
        ConvertLink<BillLine, BillLink>(provider, x => x.BillLink, x => x.Bill);
        ConvertLink<DeliveryItem, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<DeliveryItem, ManufacturingPacketLink>(provider, x => x.ManufacturingPacketLink, x => x.ManufacturingPacket);
        ConvertLink<DeliveryItem, RequisitionLink, PickingListLink>(provider, x => x.RequisitionLink, x => x.PickingList);
        ConvertLink<DeliveryItem, SetoutLink>(provider, x => x.SetoutLink, x => x.Setout);
        ConvertLink<DeliveryItem, ShipmentLink>(provider, x => x.ShipmentLink, x => x.Shipment);
        ConvertLink<Employee, UserLink>(provider, x => x.UserLink, x => x.User);
        ConvertLink<Employee, OvertimeRuleLink>(provider, x => x.OvertimeRuleLink, x => x.OvertimeRule);
        ConvertLink<EmployeeRole, EmployeeLink>(provider, x => x.EmployeeLink, x => x.Employee);
        ConvertLink<EmployeeRole, RoleLink>(provider, x => x.RoleLink, x => x.Role);
        ConvertLink<Equipment, EquipmentGroupLink>(provider, x => x.GroupLink, x => x.Group);
        ConvertLink<Equipment, GPSTrackerLink>(provider, x => x.TrackerLink, x => x.Tracker);
        ConvertLink<Invoice, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<Invoice, CustomerLink>(provider, x => x.CustomerLink, x => x.Customer);
        ConvertLink<InvoiceLine, InvoiceLink>(provider, x => x.InvoiceLink, x => x.Invoice);
        ConvertLink<Job, ScheduleLink>(provider, x => x.ScheduleLink, x => x.Schedule);
        ConvertLink<JobActivity, AssignmentActivityLink>(provider, x => x.ActivityLink, x => x.Activity);
        ConvertLink<JobBillOfMaterialsActivity, AssignmentActivityLink>(provider, x => x.ActivityLink, x => x.Activity);
        ConvertLink<JobEmployee, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<JobEmployee, EmployeeLink>(provider, x => x.EmployeeLink, x => x.Employee);
        ConvertLink<JobEquipment, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<JobEquipment, EquipmentLink>(provider, x => x.EquipmentLink, x => x.Equipment);
        ConvertLink<JobTracker, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<JobTracker, GPSTrackerLink>(provider, x => x.TrackerLink, x => x.Tracker);
        ConvertLink<JobITP, EmployeeLink>(provider, x => x.EmployeeLink, x => x.Employee);
        ConvertLink<Kanban, EmployeeLink>(provider, x => x.EmployeeLink, x => x.Employee);
        ConvertLink<Kanban, EmployeeLink>(provider, x => x.ManagerLink, x => x.Manager);
        ConvertLink<Kanban, ScheduleLink>(provider, x => x.ScheduleLink, x => x.Schedule);
        ConvertLink<LeaveRequest, EmployeeLink>(provider, x => x.EmployeeLink, x => x.Employee);
        ConvertLink<ManufacturingPacket, SetoutLink>(provider, x => x.SetoutLink, x => x.Setout);
        ConvertLink<ManufacturingPacket, ManufacturingTemplateLink>(provider, x => x.ManufacturingTemplateLink, x => x.ManufacturingTemplate);
        ConvertLink<ManufacturingPacket, ManufacturingPacketStageLink>(provider, x => x.StageLink, x => x.Stage);
        ConvertLink<ManufacturingPacketStage, ManufacturingSectionLink>(provider, x => x.ManufacturingSectionLink, x => x.ManufacturingSection);
        ConvertLink<ManufacturingPacketComponent, RequisitionLink, PickingListLink>(provider, x => x.Requisition, x => x.PickingList);
        ConvertLink<BillPayment, BillLink>(provider, x => x.BillLink, x => x.Bill);
        ConvertLink<BillPayment, PaymentLink>(provider, x => x.PaymentLink, x => x.Payment);
        ConvertLink<Payment, SupplierLink>(provider, x => x.SupplierLink, x => x.Supplier);
        ConvertLink<Payment, PaymentTypeLink>(provider, x => x.PaymentTypeLink, x => x.PaymentType);
        ConvertLink<PurchaseOrder, SupplierLink>(provider, x => x.SupplierLink, x => x.Supplier);
        ConvertLink<PurchaseOrderItem, PurchaseOrderLink>(provider, x => x.PurchaseOrderLink, x => x.PurchaseOrder);
        ConvertLink<InvoiceReceipt, InvoiceLink>(provider, x => x.InvoiceLink, x => x.Invoice);
        ConvertLink<InvoiceReceipt, ReceiptLink>(provider, x => x.ReceiptLink, x => x.Receipt);
        ConvertLink<Receipt, CustomerLink>(provider, x => x.CustomerLink, x => x.Customer);
        ConvertLink<Receipt, ReceiptTypeLink>(provider, x => x.ReceiptTypeLink, x => x.ReceiptType);
        ConvertLink<RequisitionItem, RequisitionLink>(provider, x => x.RequisitionLink, x => x.Requisition);
        ConvertLink<Shipment, GPSTrackerLink>(provider, x => x.TrackerLink, x => x.Tracker);
        ConvertLink<SupplierProduct, SupplierLink>(provider, x => x.SupplierLink, x => x.Supplier);
        ConvertLink<StockMovementBatch, RequisitionLink, PickingListLink>(provider, x => x.Requisition, x => x.PickingList);
        ConvertLink<EmployeeTeam, EmployeeLink>(provider, x => x.EmployeeLink, x => x.Employee);
        ConvertLink<EmployeeTeam, TeamLink>(provider, x => x.TeamLink, x => x.Team);
        ConvertLink<TimeSheet, EmployeeLink>(provider, x => x.EmployeeLink, x => x.Employee);
        ConvertLink<TimeSheet, JobLink>(provider, x => x.JobLink, x => x.Job);
        ConvertLink<TimeSheet, TimeSheetActivityLink>(provider, x => x.ActivityLink, x => x.Activity);
        ConvertLink<TimeSheet, LeaveRequestLink>(provider, x => x.LeaveRequestLink, x => x.LeaveRequest);
        ConvertLink<TimeSheet, StandardLeaveLink>(provider, x => x.StandardLeaveLink, x => x.StandardLeave);

        RenameTable<Requisition, PickingList>(provider);
        RenameTable<RequisitionItem, PickingListItem>(provider,
            [
                new(x => x.Requisition.ID, x => x.PickingList.ID)
            ]);
        RenameTable<RequisitionDestination, PickingListDestination>(provider);
        RenameTable<RequisitionDocument, PickingListDocument>(provider);
        RenameTable<RequisitionKanban, PickingListKanban>(provider);
    }

    private static Dictionary<Type, Dictionary<string, IExtraMap>> _filterMaps = [];

    private static IExtraMap[] GetFilterMaps()
    {
        return new IExtraMap[]
        {
            new ExtraMap<Kanban, Kanban>(DatabaseSchema.PropertyStrict(typeof(Kanban), "Category"), DatabaseSchema.PropertyStrict<Kanban>(x => x.Status))
            {
                Map = x =>
                {
                    if(x is string category)
                    {
                        if (String.IsNullOrWhiteSpace(category) || category.Equals("Open"))
                            return KanbanStatus.Open;
                        if(category.Equals("In Progress"))
                            return KanbanStatus.InProgress;
                        if (category.Equals("Waiting"))
                            return KanbanStatus.Waiting;
                        if (category.Equals("Complete"))
                            return KanbanStatus.Complete;
                        return KanbanStatus.Open;
                    }
                    else
                    {
                        return KanbanStatus.Open;
                    }
                }
            }
        };
    }

    private static bool CheckFilter(IFilter filter, Dictionary<string, IExtraMap>? maps)
    {
        var changed = false;
        if(maps is not null && maps.TryGetValue(filter.Property, out var map))
        {
            filter.Property = map.To.Name;
            var value = map.Map?.Invoke(filter.Value);
            if(value is null)
            {
                filter.Value = null;
                changed = true;
            }
            else if (filter.Type.IsAssignableFrom(value.GetType()))
            {
                filter.Value = value;
                changed = true;
            }
        }
        if (!changed)
        {
            var columnName = ConvertColumnName(filter.Type, filter.Property);
            if(columnName is not null)
            {
                filter.Property = columnName;
                changed = true;
            }
        }
        foreach(var and in filter.Ands)
        {
            changed = CheckFilter(and, maps) || changed;
        }
        foreach(var or in filter.Ors)
        {
            changed = CheckFilter(or, maps) || changed;
        }
        return changed;
    }

    private static void UpdateFilters<T>(IProvider provider)
        where T : Entity, IDatabaseStoredSettings, new()
    {
        var settings = provider.Query(
            Filter<T>.Where(x => x.Section).IsEqualTo(nameof(CoreFilterDefinitions)),
            Columns.None<T>()
                .Add(x => x.ID)
                .Add(x => x.Key)
                .Add(x => x.Contents))
            .ToArray<T>();

        var changedSettings = new List<T>();
        foreach(var setting in settings)
        {
            if (setting.Key.IsNullOrWhiteSpace()) continue;

            var entityName = setting.Key.Split('.')[^1];
            var entity = CoreUtils.Entities.Where(x => x.Name == entityName
                && x.IsSubclassOf(typeof(Entity)))
                .FirstOrDefault();

            var filters = Serialization.Deserialize<CoreFilterDefinitions>(setting.Contents);
            if(filters is not null)
            {
                var changed = false;
                foreach(var filter in filters)
                {
                    var deserialiseEntity = entity is not null ? typeof(Filter<>).MakeGenericType(entity) : typeof(IFilter);
                    IFilter? deserialised = null;
                    try
                    {
                        deserialised = (Serialization.Deserialize(deserialiseEntity, filter.Filter, strict: true) as IFilter)!;
                        if(deserialised is null)
                        {
                            continue;
                        }
                    }
                    catch (Exception e)
                    {
                        var dict = Serialization.Deserialize<Dictionary<string, object?>>(filter.Filter) ?? [];
                        if (dict.TryGetValue("Expression", out var expression) && expression is string str)
                        {
                            try
                            {
                                var exp = CoreUtils.StringToExpression(str);
                                if(exp is MemberExpression memExp)
                                {
                                    deserialiseEntity = memExp.Expression?.Type;
                                    if(deserialiseEntity is not null)
                                    {
                                        deserialiseEntity = typeof(Filter<>).MakeGenericType(deserialiseEntity);
                                        deserialised = (Serialization.Deserialize(deserialiseEntity, filter.Filter, strict: true) as IFilter)!;
                                    }
                                }
                            }
                            catch(Exception)
                            {
                            }
                        }
                        if(deserialised is null)
                        {
                            Logger.Send(LogType.Error, "", $"Error in deserialising filter '{filter.Name}' of {setting.Key}: {CoreUtils.FormatException(e)}");
                            continue;
                        }
                    }

                    var type = deserialised.GetType().GetSuperclassDefinition(typeof(Filter<>))!.GenericTypeArguments[0];
                    if (CheckFilter(deserialised, _filterMaps.GetValueOrDefault(type)))
                    {
                        filter.Filter = Serialization.Serialize(deserialised);
                        changed = true;
                    }
                }
                if (changed)
                {
                    setting.Contents = Serialization.Serialize(filters);
                    changedSettings.Add(setting);
                }
            }
        }

        provider.Save(changedSettings);
    }
    private static void UpdateFilters(IProvider provider)
    {
        _filterMaps = GetFilterMaps()
            .GroupBy(x => x.TFrom)
            .ToDictionary(x => x.Key, x => x.ToDictionary(x => x.From.Name));

        UpdateFilters<GlobalSettings>(provider);
        UpdateFilters<UserSettings>(provider);
    }

    private static string? ConvertColumnName(Type entity, string originalColumnName)
    {
        var i = 0;
        var changed = false;
        while(i < originalColumnName.Length)
        {
            var index = originalColumnName.IndexOf("Link.", i);
            if (index == -1) break;

            var columnName = originalColumnName[0..index];
            if(DatabaseSchema.Property(entity, columnName) is IProperty property)
            {
                originalColumnName = $"{property.Name}.{originalColumnName[(index + 5)..]}";
                changed = true;
            }
            else
            {
                i = index + 5;
            }
        }
        return changed ? originalColumnName : null;
    }

    private static void UpdateColumns<T>(IProvider provider)
        where T : Entity, IDatabaseStoredSettings, new()
    {
        var settings = provider.Query(
            Filter<T>.Where(x => x.Section).IsEqualTo(nameof(DynamicGridColumns)),
            Columns.None<T>()
                .Add(x => x.ID)
                .Add(x => x.Key)
                .Add(x => x.Contents))
            .ToArray<T>();

        var changedSettings = new List<T>();
        foreach(var setting in settings)
        {
            if (setting.Key.IsNullOrWhiteSpace()) continue;

            var entityName = setting.Key.Split('.')[^1];
            var entity = CoreUtils.Entities.Where(x => x.Name == entityName
                && x.IsSubclassOf(typeof(Entity)))
                .FirstOrDefault();
            if (entity is null) continue;

            var columns = Serialization.Deserialize<DynamicGridColumns>(setting.Contents);
            if(columns is not null)
            {
                var changed = false;
                foreach(var column in columns)
                {
                    var columnName = ConvertColumnName(entity, column.ColumnName);
                    if(columnName is not null)
                    {
                        column.ColumnName = columnName;
                        changed = true;
                    }
                }
                if (changed)
                {
                    setting.Contents = Serialization.Serialize(columns);
                    changedSettings.Add(setting);
                }
            }
        }

        provider.Save(changedSettings);
    }
    private static void UpdateColumns(IProvider provider)
    {
        UpdateColumns<GlobalSettings>(provider);
        UpdateColumns<UserSettings>(provider);
    }

    private static void UpdateSecurityToken<T>(IProvider provider, string oldDescriptor, string newDescriptor)
        where T : Entity, ISecurityToken, new()
    {
        var tokens = provider.Query(
            Filter<T>.Where(x => x.Descriptor).IsEqualTo(oldDescriptor),
            Columns.None<T>()
                .Add(x => x.ID)
                .Add(x => x.Descriptor))
            .ToArray<T>();
        foreach(var token in tokens)
        {
            token.Descriptor = newDescriptor;
        }
        provider.Save(tokens);
    }
    private static void UpdateSecurityToken(IProvider provider, string oldDescriptor, string newDescriptor)
    {
        UpdateSecurityToken<GlobalSecurityToken>(provider, oldDescriptor, newDescriptor);
        UpdateSecurityToken<UserSecurityToken>(provider, oldDescriptor, newDescriptor);
        UpdateSecurityToken<SecurityToken>(provider, oldDescriptor, newDescriptor);
    }

    private static void UpdateAutoSecurityToken<TFrom, TTo>(IProvider provider, Type token)
    {
        var toDescriptor = (Activator.CreateInstance(token.MakeGenericType(typeof(TTo))) as IAutoSecurityDescriptor)!;
        var overriden = Security.SecurityDescriptorOverride(toDescriptor);
        if (overriden != toDescriptor) return; // No point in updating these if the token has been overriden.

        var fromDescriptor = (Activator.CreateInstance(token.MakeGenericType(typeof(TFrom))) as IAutoSecurityDescriptor)!;

        UpdateSecurityToken(provider, Security.SecurityDescriptorOverride(fromDescriptor).Code, toDescriptor.Code);
    }
    private static void UpdateAutoSecurityTokens<TFrom, TTo>(IProvider provider)
    {
        var list = CoreUtils.Entities.Where(
            x => x.HasInterface(typeof(IAutoSecurityDescriptor)))
            .ToArray();
        foreach(var T in CoreUtils.Entities.Where(
            x => x.HasInterface(typeof(IAutoSecurityDescriptor))
                && x.IsGenericType && x.GetGenericArguments().Length == 1))
        {
            UpdateAutoSecurityToken<TFrom, TTo>(provider, T);
        }
    }

    private static void UpdateSecurityTokens(IProvider provider)
    {
        UpdateSecurityToken(provider, "CanViewRequisitionsDock", nameof(CanViewPickingListDock));
        UpdateSecurityToken(provider, "CanSkipRequisitionPhotos", nameof(CanSkipPickingListPhotos));
        UpdateSecurityToken(provider, "CanUpdateRequisitionStockMovements", nameof(CanUpdatePickingListStockMovements));
        UpdateSecurityToken(provider, "CanArchiveRequisitions", nameof(CanArchivePickingLists));
    }

    private static void ConvertSettings<T>(IProvider provider, string fromSection, string toSection)
        where T : Entity, IDatabaseStoredSettings, new()
    {
        var settings = provider.Query(
            Filter<T>.Where(x => x.Section).IsEqualTo(fromSection),
            Columns.None<T>()
                .Add(x => x.ID)
                .Add(x => x.Section))
            .ToArray<T>();

        foreach(var setting in settings)
        {
            setting.Section = toSection;
        }

        provider.Save(settings);
    }
    private static void ConvertSettingsKey<TSettings, T>(IProvider provider, string fromKey, string toKey)
        where TSettings : Entity, IDatabaseStoredSettings, new()
    {
        var settings = provider.Query(
            Filter<TSettings>.Where(x => x.Section).IsEqualTo(typeof(T).Name)
                .And(x => x.Key).IsEqualTo(fromKey),
            Columns.None<TSettings>()
                .Add(x => x.ID)
                .Add(x => x.Key))
            .ToArray<TSettings>();

        foreach(var setting in settings)
        {
            setting.Key = toKey;
        }

        provider.Save(settings);
    }
    private static void ConvertSettingsKey<T>(IProvider provider, string fromKey, string toKey)
    {
        ConvertSettingsKey<GlobalSettings, T>(provider, fromKey, toKey);
        ConvertSettingsKey<UserSettings, T>(provider, fromKey, toKey);
    }

    private static void ConvertColumnsTag(IProvider provider, string fromTag, string toTag)
    {
        ConvertSettingsKey<DynamicGridColumns>(provider, fromTag, toTag);
        ConvertSettingsKey<DynamicGridColumns>(provider, $"{fromTag}:DirectEdit", $"{toTag}:DirectEdit");
    }

    public override bool Update()
    {
        var provider = DbFactory.NewProvider(Logger.Main);

        UpdateTimeSheets(provider);

        ConvertLinks(provider);
        UpdateFilters(provider);
        UpdateColumns(provider);

        UpdateSecurityTokens(provider);

        ConvertSettings<UserSettings>(provider, "RequisitionSettings", "PickingListSettings");

        ConvertColumnsTag(provider, "PickingList.DeliveryRequi", "PickingList.DeliveryPickingList");
        ConvertColumnsTag(provider, "RequisitionItem.RequisitionItem_Logistics", "PickingListItem.PickingListItem_Logistics");

        ConvertQAQuestions(provider);

        return true;
    }
}