inabox/inabox.database.sqlite/SQLiteProvider.cs

using System.Collections;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics.CodeAnalysis;
using System.Linq.Expressions;
using System.Reflection;
using System.Resources;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using InABox.Core;
using Microsoft.CodeAnalysis;
using NPOI.POIFS.FileSystem;

namespace InABox.Database.SQLite
{
    internal abstract class SQLiteAccessor : IDisposable
    {
        public SQLiteConnection Connection { get; private set; }

        protected static int nConnections = 0;

        public void Dispose()
        {
            Connection?.Close();
            --nConnections;
        }

        public SQLiteCommand CreateCommand()
        {
            return new SQLiteCommand(Connection);
        }

        protected void Setup(string url)
        {
            var sb = new SQLiteConnectionStringBuilder();
            sb.DataSource = url;
            sb.Version = 3;
            sb.DateTimeFormat = SQLiteDateFormats.Ticks;
            sb.JournalMode = SQLiteJournalModeEnum.Wal;
            
            var conn = sb.ToString();

            Connection = new SQLiteConnection(conn);
            Connection.BusyTimeout = Convert.ToInt32(TimeSpan.FromMinutes(2).TotalMilliseconds);
            Connection.Open();
            Connection.SetLimitOption(SQLiteLimitOpsEnum.SQLITE_LIMIT_VARIABLE_NUMBER, 10000);
            ++nConnections;
        }
    }

    internal class SQLiteReadAccessor : SQLiteAccessor
    {
        public SQLiteReadAccessor(string url)
        {
            Setup(url);
        }
    }

    internal class SQLiteWriteAccessor : SQLiteAccessor
    {
        private static readonly object writelock = new();

        public SQLiteWriteAccessor(string url)
        {
            if (Monitor.TryEnter(writelock, new TimeSpan(0, 1, 0)))
                try
                {
                    Setup(url);
                }
                catch(Exception e)
                {
                    throw new Exception($"Exception while acquiring write lock! {nConnections} connections open.", e);
                }
                finally
                {
                    Monitor.Exit(writelock);
                }

            if (Connection == null)
                throw new Exception($"Timeout while aqcuiring write lock! {nConnections} connections open.");
        }

        public SQLiteTransaction BeginTransaction()
        {
            return Connection.BeginTransaction();
        }
    }

    public class SQLiteProviderFactory : IProviderFactory
    {
        private static object writelock = new();

        private bool bForceRebuild;

        private bool RebuildTriggers = false;

        public SQLiteProviderFactory(string filename)
        {
            var path = Path.GetDirectoryName(filename);
            if (!path.IsNullOrWhiteSpace())
                Directory.CreateDirectory(path);
            URL = filename;
        }

        public string URL { get; set; }

        /// <summary>
        /// An array containing every <see cref="Entity"/> type in the database.
        /// </summary>
        public Type[] Types { get; set; } = [];

        private SQLiteProvider MainProvider;

        public SQLiteProvider NewProvider(Logger logger) => new SQLiteProvider(this)
        {
            Logger = logger
        };

        IProvider IProviderFactory.NewProvider(Logger logger) => NewProvider(logger);

        public void Start()
        {
            var chkfile = Path.ChangeExtension(URL, "chk");
            bForceRebuild = File.Exists(chkfile);

            if (!File.Exists(URL))
            {
                SQLiteConnection.CreateFile(URL);
            }
            else if (bForceRebuild)
            {
                var i = 0;
                while (File.Exists(Path.ChangeExtension(URL, string.Format("{0:D3}", i))))
                    i++;
                File.Copy(URL, Path.ChangeExtension(URL, string.Format("{0:D3}", i)));
            }

            MainProvider = NewProvider(Logger.Main);

            using var access = MainProvider.GetWriteAccess();

            MainProvider.ExecuteSQL(access, "PRAGMA journal_mode=WAL;");

            //using (var access = GetReadAccess())
            //{
            //    using (var command = access.CreateCommand())
            //    {
            //        command.CommandText = "PRAGMA compile_options";
            //        using (var reader = command.ExecuteReader())
            //        {
            //            while (reader.Read())
            //                Log(LogType.Information,String.Format("{0}",reader.GetValue(0)));
            //        }
            //    }
            //}


            //ExecuteSQL("PRAGMA foreign_keys = on;");

            foreach(var type in Types)
            {
                DatabaseSchema.CheckProperties(type);
            }

            // Need to arrange the typelist to ensure that foreign keys
            // refer to tables that already exist
            var ordered = new List<Type>();
            foreach (var type in Types)
                LoadType(type, ordered);

            //Load up the metadata
            var metadata = LoadMetaData();
            
            var table = typeof(CustomProperty).EntityName().Split('.').Last();
            if (!metadata.TryGetValue(table, out var value))
            {
                Log(LogType.Information, $"Creating Table: {nameof(CustomProperty)}");

                CreateTable(access, typeof(CustomProperty), true, []);
            }
            else
            {
                CheckFields(access, typeof(CustomProperty), value.Item1, []);
            }

            var customproperties = MainProvider.Load<CustomProperty>(); // new Filter<CustomProperty>(x => x.Class).IsEqualTo(type.EntityName()))

            metadata = LoadMetaData();

            foreach (var type in ordered)
            {
                if (type.GetCustomAttribute<AutoEntity>() == null)
                {
                    table = type.EntityName().Split('.').Last();
                    if (!metadata.ContainsKey(table))
                    {
                        Log(LogType.Information, "Creating Table: " + type.Name);
                        CreateTable(access, type, true, customproperties);
                    }
                }
            }

            metadata = LoadMetaData();
            
            foreach (var type in ordered)
            {
                if (type.GetCustomAttribute<AutoEntity>() == null)
                {
                    table = type.EntityName().Split('.').Last();
                    CheckFields(access, type, metadata[table].Item1, customproperties);
                }
            }

            metadata = LoadMetaData();
            
            foreach (var type in ordered)
            {
                if (type.GetCustomAttribute<AutoEntity>() == null)
                {
                    table = type.Name;
                    CheckTriggers(access, type, metadata[table].Item2);
                }
            }

            metadata = LoadMetaData();

            foreach (var type in ordered)
            {
                if (type.GetCustomAttribute<AutoEntity>() == null)
                {

                    table = type.EntityName().Split('.').Last();
                    CheckIndexes(access, type, metadata[table].Item3);
                }
            }

            metadata = LoadMetaData();
            
            foreach (var type in ordered)
            {
                if (type.GetCustomAttribute<AutoEntity>() != null)
                {
                    table = type.EntityName().Split('.').Last();
                    if (!metadata.ContainsKey(table))
                    {
                        Log(LogType.Information, "Creating Table: " + type.EntityName().Split('.').Last());
                        CreateTable(access, type, true, customproperties);
                    }
                    else
                    {
                        CheckFields(access, type, metadata[table].Item1, customproperties);
                    }
                }
            }

            if (bForceRebuild)
            {
                MainProvider.ExecuteSQL(access, "VACUUM;");

                File.Delete(chkfile);
            }
        }

        public bool IsRelational()
        {
            return true;
        }

        #region Database Structure Management

        private class MetadataEntry
        {
            public String Name { get; set; }
            public String SQL { get; set; }
        }

        private class Metadata
        {
            public List<MetadataEntry> Tables { get; init; }
            public List<MetadataEntry> Views { get; init; }
            public List<MetadataEntry> Indexes { get; init; }
            public List<MetadataEntry> Triggers { get; init; }

            public Metadata() : base()
            {
                Tables = new List<MetadataEntry>();
                Views = new List<MetadataEntry>();
                Indexes = new List<MetadataEntry>();
                Triggers = new List<MetadataEntry>();
            }
        }
        
        private Dictionary<string, Tuple<Dictionary<string, string>, Dictionary<string, string>, Dictionary<string, string>>> LoadMetaData()
        {
            // Key is table name
            // Item1 = Field Name / Field Type map
            // Item2 = Trigger Name / Trigger SQL
            // Item3 = Index Name / Index SQL
            // Item4 = View Name /View SQL
            var metadata = new Dictionary<string, Tuple<Dictionary<string, string>, Dictionary<string, string>, Dictionary<string, string>>>();

            using (var access = MainProvider.GetReadAccess())
            {
                using (var command = access.CreateCommand())
                {
                    command.CommandText = "SELECT tbl_name, sql, type FROM sqlite_master WHERE type='table' or type='view' ORDER BY type;";
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                            while (reader.Read())
                            {
                                var tblinfo = new Tuple<Dictionary<string, string>, Dictionary<string, string>, Dictionary<string, string>>(
                                    new Dictionary<string, string>(), new Dictionary<string, string>(), new Dictionary<string, string>());

                                var table = reader.GetString(0);
                                var sql = reader.GetString(1);
                                bool istable = String.Equals(reader.GetString(2),"table");

                                if (istable)
                                {
                                    sql = sql.Replace("\"", "")
                                        .Replace(string.Format("CREATE TABLE {0} (", table), "");
                                    sql = sql.Remove(sql.Length - 1).Trim();
                                    var fields = sql.Replace("\n\t", "").Replace("\t", " ").Replace("\"", "").Trim().Split(',');
                                    var primarykey = "";
                                    foreach (var fld in fields)
                                    {
                                        var field = fld.Trim().Replace("\t", " ").Replace("\"", "").Replace("[", "").Replace("]", "");
                                        //if (field.ToUpper().StartsWith("CONSTRAINT"))
                                        //    tblinfo.Item2.Add(field);
                                        if (field.ToUpper().StartsWith("PRIMARY KEY"))
                                        {
                                            primarykey = field.Replace("PRIMARY KEY(", "").Replace(")", "");
                                        }
                                        else
                                        {
                                            var comps = field.Split(' ');

                                            tblinfo.Item1[comps[0]] = string.Format("{0}{1}", comps[1],
                                                field.Contains("PRIMARY KEY") ? " PRIMARY KEY" : "");
                                        }
                                    }

                                    if (!string.IsNullOrEmpty(primarykey))
                                    {
                                        var pkfld = tblinfo.Item1[primarykey];
                                        if (!pkfld.ToUpper().Contains("PRIMARY KEY"))
                                            tblinfo.Item1[primarykey] = string.Format("{0} PRIMARY KEY", pkfld.Trim());
                                    }
                                }
                                else
                                {
                                    sql = sql.Replace("\"", "")
                                        .Replace("DISTINCT ", "");
                                    sql = sql.Split(new String[] { " AS SELECT " }, StringSplitOptions.TrimEntries).Last();
                                    sql = sql.Split(new String[] { " FROM " }, StringSplitOptions.TrimEntries).First();

                                    var fields = sql.Replace("\n\t", "").Replace("\t", " ").Replace("\"", "").Trim().Split(',');
                                    foreach (var fld in fields)
                                    {
                                        var field = fld.Trim()
                                            .Replace("\t", " ")
                                            .Replace("\"", "")
                                            .Replace("[", "").Replace("]", "");

                                        var parts = field.Split(" as ");
                                        if(parts.Length == 1)
                                        {
                                            tblinfo.Item1[field] = "";
                                        }
                                        else if(parts.Length == 2)
                                        {
                                            field = parts[1];
                                            if (parts[0] != "NULL")
                                            {
                                                tblinfo.Item1[field] = "";
                                            }
                                        }
                                    }
                                }

                                metadata[table] = tblinfo;
                            }

                        reader.Close();
                    }

                    // Now Load Up all the indexes
                    command.CommandText = "select name, tbl_name, sql from sqlite_master where type='index' and sql is not null;";
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                            while (reader.Read())
                            {
                                var name = reader.GetString(0);
                                var table = reader.GetString(1);
                                if (metadata.ContainsKey(table))
                                {
                                    var tblInfo = metadata[table];
                                    var sql = reader.GetString(2);
                                    tblInfo.Item3[name] = sql;
                                }
                            }
                    }

                    // Now Load Up all the triggers
                    command.CommandText = "select name, tbl_name, sql from sqlite_master where type='trigger';";
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                            while (reader.Read())
                            {
                                var name = reader.GetString(0);
                                var table = reader.GetString(1);
                                if (metadata.ContainsKey(table))
                                {
                                    var tblInfo = metadata[table];
                                    var sql = reader.GetString(2);
                                    tblInfo.Item2[name] = sql;
                                }
                            }
                    }
                }
            }

            return metadata;
        }

        private static void LoadType(Type type, List<Type> into)
        {
            if (into.Contains(type))
                return;
            var props = type.GetProperties().Where(x => x.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)));
            if (!props.Any())
            {
                into.Insert(0, type);
            }
            else
            {
                into.Add(type);
                foreach (var prop in props)
                {
                    var subtype = prop.PropertyType.BaseType;
                    while (subtype != null && !subtype.GetGenericArguments().Any())
                        subtype = subtype.BaseType;
                    subtype = subtype?.GetGenericArguments().FirstOrDefault();
                    if (subtype != null && subtype != type)
                        LoadType(subtype, into);
                }
            }
        }

        private string ColumnName(params PropertyInfo[] properties)
        {
            var bits = new List<string>();
            foreach (var property in properties)
                bits.Add(property.Name);
            var result = string.Join(".", bits);
            return result;
        }

        private string ColumnType(Type type)
        {
            if (type.IsOrdinal())
                return "INT";

            if (type.IsEnum)
                return "TEXT";

            if (type == typeof(DateTime))
                return "TEXT";

            if (type == typeof(TimeSpan))
                return "NUM";

            if (type == typeof(string[]))
                return "BLOB";

            if (type == typeof(byte[]))
                return "BLOB";

            if (type.IsFloatingPoint())
                return "NUM";

            if (type.GetInterfaces().Contains(typeof(IPackable)))
                return "BLOB";

            return "TEXT";
        }

        private void LoadFields(Type type, Dictionary<string, string> fields, List<PropertyInfo>? prefixes, CustomProperty[] customproperties)
        {
            if (prefixes == null)
                prefixes = new List<PropertyInfo>();
            
            AutoEntity? view = type.GetCustomAttribute<AutoEntity>();
            Type definition = view?.Generator != null
                ? view.Generator.Definition
                : type;
            
            var properties = CoreUtils.GetInheritedProperties(definition).Where(x =>
                x.GetCustomAttribute<DoNotPersist>() == null
                // We think this is wrong; but who knows? && x.GetCustomAttribute<DoNotSerialize>() == null
                && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(AggregateAttribute))) == null
                && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(FormulaAttribute))) == null
                && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(ConditionAttribute))) == null
                && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(ChildEntityAttribute))) == null
                && x.CanWrite
                && x.PropertyType != typeof(UserProperties)
            );

            foreach (var property in properties)
                if (property.PropertyType.GetInterfaces().Contains(typeof(IEnclosedEntity)))
                {
                    LoadFields(property.PropertyType, fields, prefixes.Concat(new[] { property }).ToList(), customproperties);
                }
                else if (property.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)))
                {
                    var subprop = property.PropertyType.GetProperty("ID")!; // Not-null because IEntityLink has ID
                    var subname = ColumnName(prefixes.Concat(new[] { property, subprop }).ToArray());
                    var subtype = ColumnType(subprop.PropertyType);
                    fields[subname] = subtype;
                }
                else
                {
                    var colname = ColumnName(prefixes.Concat(new[] { property }).ToArray());
                    var coltype = ColumnType(property.PropertyType);
                    fields[colname] = string.Format("{0}{1}", coltype, colname.Equals("ID") ? " PRIMARY KEY" : "");
                }

            // Now add in the Custom Properties (if any exist)
            //CustomProperty[] customprops = Load<CustomProperty>(new Filter<CustomProperty>(x=>x.Class).IsEqualTo(type.EntityName()));
            foreach (var prop in customproperties.Where(x => x.Class.Equals(type.EntityName())))
                fields[prop.Name] = ColumnType(prop.PropertyType);
        }

        private List<string> LoadIndexes(Type type)
        {
            var result = new List<string>();

            var properties = type.GetProperties().Where(x =>
                x.GetCustomAttribute<DoNotSerialize>() == null && x.GetCustomAttribute<DoNotPersist>() == null &&
                x.GetCustomAttributes().FirstOrDefault(a => a.GetType().IsSubclassOf(typeof(AggregateAttribute))) == null
                && x.GetCustomAttributes().FirstOrDefault(a => a.GetType().Equals(typeof(ChildEntityAttribute))) == null
                && x.CanWrite);
            foreach (var property in properties)
                if (property.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)))
                {
                    var subprop = property.PropertyType.GetProperty("ID")!; // Not-null because IEntityLink has ID
                    var subname = ColumnName(property, subprop);
                    var tablename = type.EntityName().Split('.').Last();
                    result.Add(string.Format("CREATE INDEX idx{0}{1} ON {0} ([{2}])", tablename, subname.Replace(".", ""), subname));
                }
                else
                {
                    var index = property.GetCustomAttributes<SecondaryIndexAttribute>().FirstOrDefault();
                    if (index != null)
                    {
                        var colname = ColumnName(property);
                        var tablename = type.EntityName().Split('.').Last();
                        result.Add(string.Format("CREATE INDEX idx{0}{1} ON {0} ([{2}])", tablename, colname.Replace(".", ""), colname));
                    }
                }

            return result;
        }

        internal Dictionary<Type, List<Tuple<Type, List<string>>>> _cascades = new();
        internal Dictionary<Type, List<Tuple<Type, List<string>>>> _setNulls = new();

        private void LoadDeletions(Type type)
        {
            var cascades = new List<Tuple<Type, List<string>>>();
            var setNulls = new List<Tuple<Type, List<string>>>();

            foreach(var otherType in Types.Where(x => x.GetCustomAttribute<AutoEntity>() is null))
            {
                var setNullFields = new List<string>();
                var cascadeFields = new List<string>();

                var props = DatabaseSchema.RootProperties(otherType)
                    .Where(x => x.IsEntityLink
                                && x.PropertyType.GetInterfaceDefinition(typeof(IEntityLink<>))?.GenericTypeArguments[0] == type
                                && !x.IsCalculated);
                foreach(var prop in props)
                {
                    var fieldname = $"{prop.Name}.ID";
                    if(prop.GetAttribute<EntityRelationshipAttribute>() is EntityRelationshipAttribute attr
                        && attr.Action == DeleteAction.Cascade)
                    {
                        cascadeFields.Add(fieldname);
                    }
                    else
                    {
                        setNullFields.Add(fieldname);
                    }
                }
                cascadeFields.Sort();
                setNullFields.Sort();

                if(cascadeFields.Count > 0)
                {
                    cascades.Add(new(otherType, cascadeFields));
                }
                if(setNulls.Count > 0)
                {
                    setNulls.Add(new(otherType, setNullFields));
                }
            }

            if(cascades.Count > 0)
            {
                _cascades[type] = cascades;
            }
            if(setNulls.Count > 0)
            {
                _setNulls[type] = setNulls;
            }
        }
        private string? LoadTrigger(Type type)
        {
            var actions = new List<string>();

            if(_setNulls.TryGetValue(type, out var setNulls))
            {
                foreach(var (otherType, fields) in setNulls)
                {
                    foreach(var field in fields)
                    {
                        actions.Add($"UPDATE {otherType.Name} SET [{field}] = NULL WHERE [{field}] = old.ID;");
                    }
                }
            }
            if(_cascades.TryGetValue(type, out var cascades))
            {
                foreach(var (otherType, fields) in cascades)
                {
                    foreach(var field in fields)
                    {
                        actions.Add($"DELETE FROM {otherType.Name} WHERE [{field}] = old.ID;");
                    }
                }
            }

            if (actions.Count != 0)
            {
                return $"CREATE TRIGGER {type.Name}_BEFOREDELETE BEFORE DELETE ON {type.Name} FOR EACH ROW BEGIN {string.Join(' ', actions)} END";
            }
            else
            {
                return null;
            }
        }

        public void ForceRecreateViews()
        {
            var ordered = new List<Type>();
            foreach (var type in Types)
                LoadType(type, ordered);

            var customproperties = MainProvider.Load<CustomProperty>();
            var metadata = LoadMetaData();

            foreach (var type in ordered)
            {
                var view = type.GetCustomAttribute<AutoEntity>();
                if (view?.Generator != null)
                {
                    var table = type.EntityName().Split('.').Last();
                    if (!metadata.ContainsKey(table))
                    {
                        Log(LogType.Information, "Creating Table: " + type.EntityName().Split('.').Last());
                        using (var access = MainProvider.GetWriteAccess())
                        {
                            CreateTable(access, type, true, customproperties);
                        }
                    }
                    else
                    {
                        var type_fields = new Dictionary<string, string>();
                        LoadFields(view.Generator.Definition, type_fields, null, customproperties);

                        using (var access = MainProvider.GetWriteAccess())
                        {
                            RebuildTable(access, type, metadata[table].Item1, type_fields, customproperties);
                        }
                    }
                }
            }
        }

        private Dictionary<string, object?> CheckDefaultColumns(IAutoEntityGenerator generator)
        {
            var viewfields = new Dictionary<string, string>();
            LoadFields(generator.Definition, viewfields, null, new CustomProperty[] { });
            Dictionary<String, object?> result = new Dictionary<string, object?>();
            if (!viewfields.ContainsKey("ID"))
                result["ID"] = null;
            if (!viewfields.ContainsKey("Created"))
                result["Created"] = null;
            if (!viewfields.ContainsKey("CreatedBy"))
                result["CreatedBy"] = null;
            if (!viewfields.ContainsKey("LastUpdate"))
                result["LastUpdate"] = null;
            if (!viewfields.ContainsKey("LastUpdateBy"))
                result["LastUpdateBy"] = null;
            return result;
        }
        
        private void CreateTable(SQLiteWriteAccessor access, Type type, bool includeconstraints, CustomProperty[] customproperties)
        {
            var tablename = type.EntityName().Split('.').Last();
            var ddl = new List<string>();
            var view = type.GetCustomAttribute<AutoEntity>();
            if (view != null)
            {
                using (var command = access.CreateCommand())
                {
                    command.CommandText = $"select name from sqlite_master where type='view' and name='{tablename}';";
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                            while (reader.Read())
                                MainProvider.ExecuteSQL(access,string.Format("DROP VIEW {0}", reader.GetString(0)));
                    }
                }

                ddl.Add("CREATE VIEW");
                ddl.Add(type.EntityName().Split('.').Last());
                ddl.Add("AS");
                
                if (view.Generator is IAutoEntityUnionGenerator union)
                {
                    List<String> queries = new List<String>();
                    foreach (var table in union.Tables)
                    {

                        var columns = Columns.None(table.Entity);
                        var constants = CheckDefaultColumns(union);
                        
                        var interfacefields = new Dictionary<string, string>();
                        LoadFields(union.Definition, interfacefields, null, new CustomProperty[] { });
                        
                        var entityfields = new Dictionary<string, string>();
                        LoadFields(table.Entity, entityfields, null, new CustomProperty[] { });

                        foreach (var field in interfacefields.Keys)
                        {
                            if (entityfields.ContainsKey(field))
                                columns.Add(field);
                            else
                            {
                                var constant = table.Constants.FirstOrDefault(x => String.Equals(x.Mapping.Property, field));
                                if (constant != null)
                                    constants[field] = constant.Value;
                                else
                                    constants[field] = null;
                            }
                        }

                        var query = MainProvider.PrepareSelectNonGeneric(table.Entity, new SQLiteCommand(), 'A',
                            table.Filter, columns, null,
                            null, constants, null, union.Distinct, false);
                        
                        queries.Add(query);
                    }
                    // var viewfields = new Dictionary<string, string>();
                    //LoadFields(union.Definition, viewfields, null, new CustomProperty[] { });
                    // var fields = viewfields.Keys.Select(x => String.Format("[{0}]", x)).ToList();
                    // if (!fields.Contains("[ID]"))
                    //     fields.Add(String.Format("NULL as [ID]"));
                    // if (!fields.Contains("[Created]"))
                    //     fields.Add(String.Format("NULL as [Created]"));
                    // if (!fields.Contains("[CreatedBy]"))
                    //     fields.Add(String.Format("NULL as [CreatedBy]"));
                    // if (!fields.Contains("[LastUpdate]"))
                    //     fields.Add(String.Format("NULL as [LastUpdate]"));
                    // if (!fields.Contains("[LastUpdateBy]"))
                    //     fields.Add(String.Format("NULL as [LastUpdateBy]"));
                    //
                    // List<String> queries = new List<String>();
                    // foreach (var entity in union.Entities)
                    //     queries.Add(String.Format("SELECT {0} {1} FROM {2}",
                    //         union.Distinct ? "DISTINCT" : "",
                    //         String.Join(", ", fields),
                    //         entity.EntityName().Split('.').Last())
                    //     );
                    ddl.Add(String.Join(" UNION ", queries));
                }
                else if ( view.Generator is IAutoEntityCrossGenerator cross)
                {
                    List<String> constants = new List<string>();
                    foreach (var constant in CheckDefaultColumns(cross))
                        constants.Add($"{SQLiteProvider.EscapeValue(constant.Value)} as [{constant.Key}]");
                    
                    String query = String.Format(
                        "SELECT {0} {1}.[{2}] as [{3}], {4}.[{5}] as [{6}], {7} FROM {1}, {4} WHERE {1}.[{8}] = {4}.[{9}]",
                        cross.Distinct ? "DISTINCT" : "",
                        cross.LeftEntity(),
                        cross.LeftProperty(),
                        cross.LeftMapping(),
                        cross.RightEntity(),
                        cross.RightProperty(),
                        cross.RightMapping(),
                        String.Join(", ", constants),
                        cross.LeftLink(),
                        cross.RightLink()
                    );
                    
                    ddl.Add(query);
                }
                else if ( view.Generator is IAutoEntityCartesianGenerator cartesian)
                {

                    List<String> fields = new List<string>();
                    List<String> tables = new List<String>();
                    List<String> filters = new List<String>();
                    int iTable = 0;
                    foreach (var table in cartesian.Tables)
                    {
                        var subQueryText = MainProvider.PrepareSelectNonGeneric(table.Type, new SQLiteCommand(), 'A',
                            table.Filter, table.Columns, null,
                            null, null, null, cartesian.Distinct, false);
                        
                        tables.Add($"({subQueryText}) T{iTable}");
                        
                        foreach (var mapping in table.Mappings)
                            fields.Add($"T{iTable}.[{mapping.Column.Property}] as [{mapping.Mapping.Property}]");
                        
                        iTable++;
                    }

                    foreach (var constant in cartesian.Constants)
                        fields.Add($"{SQLiteProvider.EscapeValue(constant.Constant)} as [{constant.Mapping.Property}]");
                    
                    foreach (var constant in CheckDefaultColumns(cartesian))
                        fields.Add($"{SQLiteProvider.EscapeValue(constant.Value)} as [{constant.Key}]");

                    StringBuilder sb = new StringBuilder();
                    sb.Append("SELECT ");
                    sb.Append(String.Join(", ", fields));
                    sb.Append(" FROM ");
                    sb.Append(String.Join(", ", tables));
                    if (filters.Any())
                        sb.Append($" WHERE {String.Join(" AND ", filters)}");
                    ddl.Add(sb.ToString());
                }


                ddl.Add(";");
                var viewstatement = string.Join(" ", ddl);
            }
            else
            {
                ddl.Add("CREATE TABLE");
                ddl.Add(type.EntityName().Split('.').Last());
                ddl.Add("(");
                var fields = new Dictionary<string, string>();
                var constraints = new List<string>();
                var indexes = new List<string>();
                LoadFields(type, fields, null, customproperties);
                var defs = new List<string>();
                foreach (var key in fields.Keys)
                    defs.Add(string.Format("[{0}] {1}", key, fields[key]));
                if (includeconstraints)
                    defs.AddRange(constraints);
                ddl.Add(string.Join(", ", defs));
                ddl.Add(");");
            }

            var statement = string.Join(" ", ddl);
            try
            {
                MainProvider.ExecuteSQL(access, statement);
            }
            catch (Exception e)
            {
                Log(LogType.Error, "Unable to Create Table: " + e.Message);
                throw;
            }
        }

        private void RebuildTable(SQLiteWriteAccessor access, Type type, Dictionary<string, string> table_fields,
            Dictionary<string, string> type_fields,
            CustomProperty[] customproperties)
        {

            var table = type.EntityName().Split('.').Last();

            if (type.GetCustomAttribute<AutoEntity>() != null)
            {
                Log(LogType.Information, "Recreating View: " + table);
                try
                {
                    String drop = "";
                    using (var command = access.CreateCommand())
                    {
                        
                        command.CommandText =
                            $"select name from sqlite_master where type='trigger' and tbl_name='{table}' and sql is not null;";
                        using (var reader = command.ExecuteReader())
                        {
                            if (reader.HasRows)
                                while (reader.Read())
                                    MainProvider.ExecuteSQL(access,string.Format("DROP TRIGGER {0}", reader.GetString(0)));
                        }
                        
                        command.CommandText = "select type from sqlite_master where name='" + table + "' and sql is not null;";
                        using (var reader = command.ExecuteReader())
                        {
                            if (reader.HasRows)
                                while (reader.Read())
                                    drop = reader.GetString(0).ToUpper();
                        }
                    }

                    if (!String.IsNullOrWhiteSpace(drop))
                        MainProvider.ExecuteSQL(access, string.Format("DROP {0} {1};", drop, table));

                    CreateTable(access, type, true, customproperties);

                }
                catch (Exception e)
                {
                    Log(LogType.Error, string.Format("RebuildTable({0}) [VIEW] failed: {1}\n{2}", table, e.Message, e.StackTrace));
                    throw;
                }
            }
            else
            {
                
                Log(LogType.Information, "Rebuilding Table: " + table);
                try
                {
                    
                    MainProvider.ExecuteSQL(access, "PRAGMA foreign_keys = off;");
                    
                    // using (var command = access.CreateCommand())
                    // {
                    //
                    //     command.CommandText =
                    //         $"select name from sqlite_master where type='trigger' and tbl_name='{table}' and sql is not null;";
                    //     using (var reader = command.ExecuteReader())
                    //     {
                    //         if (reader.HasRows)
                    //             while (reader.Read())
                    //                 ExecuteSQL(access,string.Format("DROP TRIGGER {0}", reader.GetString(0)));
                    //     }
                    //     command.CommandText = $"select name from sqlite_master where type='view' and name='{table}';";
                    //     using (var reader = command.ExecuteReader())
                    //     {
                    //         if (reader.HasRows)
                    //             while (reader.Read())
                    //                 ExecuteSQL(access,string.Format("DROP VIEW {0}", reader.GetString(0)));
                    //     }
                    // }
                    
                    using (var transaction = access.Connection.BeginTransaction())
                    {
                        var drops = new List<string>();

                        using (var command = access.CreateCommand())
                        {
                            
                            //command.CommandText = String.Format("select name from sqlite_master where type='trigger' and tbl_name='{0}' and sql is not null;", table);
                            command.CommandText = "select name from sqlite_master where type='view' and sql is not null;";
                            using (var reader = command.ExecuteReader())
                            {
                                if (reader.HasRows)
                                    while (reader.Read())
                                        drops.Add(string.Format("DROP VIEW {0}", reader.GetString(0)));
                            }
                            
                            //command.CommandText = String.Format("select name from sqlite_master where type='trigger' and tbl_name='{0}' and sql is not null;", table);
                            command.CommandText = "select name from sqlite_master where type='trigger' and sql is not null;";
                            using (var reader = command.ExecuteReader())
                            {
                                if (reader.HasRows)
                                    while (reader.Read())
                                        drops.Add(string.Format("DROP TRIGGER {0}", reader.GetString(0)));
                            }

                            command.CommandText = string.Format(
                                "select name from sqlite_master where type='index' and tbl_name='{0}' and sql is not null;",
                                table);
                            using (var reader = command.ExecuteReader())
                            {
                                if (reader.HasRows)
                                    while (reader.Read())
                                        drops.Add(string.Format("DROP INDEX {0}", reader.GetString(0)));
                            }
                        }

                        foreach (var drop in drops)
                            MainProvider.ExecuteSQL(access, drop);

                        bool existingtable = false;
                        using (var command = access.CreateCommand())
                        {
                            command.CommandText =
                                $"select name from sqlite_master where type='table' and tbl_name='{table}' and sql is not null;";
                            using (var reader = command.ExecuteReader())
                            {
                                if (reader.HasRows)
                                    existingtable = true;
                            }
                        }
                        
                        if (existingtable)
                            MainProvider.ExecuteSQL(access, string.Format("ALTER TABLE {0} RENAME TO _{0}_old;", table));

                        CreateTable(access, type, true, customproperties);

                        var fields = new List<string>();
                        foreach (var field in type_fields.Keys)
                            if (table_fields.ContainsKey(field))
                                fields.Add("[" + field + "]");

                        if (existingtable)
                        {
                            MainProvider.ExecuteSQL(access,
                                string.Format("INSERT INTO {0} ({1}) SELECT {1} FROM _{0}_old;", table,
                                    string.Join(", ", fields)));
                            MainProvider.ExecuteSQL(access, string.Format("DROP TABLE _{0}_old;", table));
                        }

                        transaction.Commit();
                    }

                    MainProvider.ExecuteSQL(access, "PRAGMA foreign_keys = on;");
                }
                catch (Exception e)
                {
                    Log(LogType.Error, string.Format("RebuildTable({0}) [TABLE] failed: {1}\n{2}", table, e.Message, e.StackTrace));
                    throw;
                }
            }
            
        }

        private void CheckFields(SQLiteWriteAccessor access, Type type, Dictionary<string, string> current_fields, CustomProperty[] customproperties)
        {
            var type_fields = new Dictionary<string, string>();
            var view = type.GetCustomAttribute<AutoEntity>();
            if ((view != null) && (view.Generator != null))
                LoadFields(view.Generator.Definition, type_fields, null, customproperties);
            else
                LoadFields(type, type_fields, null, customproperties);


            var bRebuild = false;
            foreach (var field in type_fields.Keys)
            {
                try
                {
                    Type tType;
                    var cprop = customproperties.FirstOrDefault(x => string.Equals(x.Name, field));
                    if (cprop != null)
                        tType = cprop.PropertyType;
                    else
                        tType = CoreUtils.GetProperty(type, field).PropertyType;

                    if ((view == null) && (tType == typeof(TimeSpan)) && current_fields.ContainsKey(field) && !current_fields[field].Equals(type_fields[field]))
                    {
                        var sql = string.Format(
                            "update {0} set [{1}] = cast(substr([{1}],1,2) as double) + cast(substr([{1}],4,2) as double)/60 + cast(substr([{1}],7,2) as double)/3600 where [{1}] like \"%:%:%\"",
                            type.Name, field);
                        MainProvider.ExecuteSQL(access, sql);
                    }
                }
                catch (Exception e)
                {
                    Logger.Send(LogType.Error, "", string.Format("*** Unknown Error: {0}\n{1}", e.Message, e.StackTrace));
                }

                if (!current_fields.ContainsKey(field) || (!String.IsNullOrWhiteSpace(current_fields[field])) && (current_fields[field] != type_fields[field]))
                    bRebuild = true;
            }

            foreach (var field in current_fields.Keys)
                if (!type_fields.ContainsKey(field))
                    bRebuild = true;

            if (bForceRebuild || bRebuild)
                RebuildTable(access, type, current_fields, type_fields, customproperties);
        }

        private void CheckIndexes(SQLiteWriteAccessor access, Type type, Dictionary<string, string> db_indexes)
        {
            var type_indexes = LoadIndexes(type);

            foreach (var index in db_indexes.Keys)
                if (!type_indexes.Contains(db_indexes[index]))
                    MainProvider.ExecuteSQL(access, string.Format("DROP INDEX {0}", index));

            foreach (var index in type_indexes)
                if (!db_indexes.ContainsValue(index))
                    MainProvider.ExecuteSQL(access, index);
        }

        private void CheckTriggers(SQLiteWriteAccessor access, Type type, Dictionary<string, string> db_triggers)
        {
            LoadDeletions(type);

            /*
#if PURGE
            foreach (var trigger in db_triggers.Keys)
                ExecuteSQL(access, string.Format("DROP TRIGGER {0}", trigger));
#else*/
            var type_trigger = LoadTrigger(type);

            foreach (var (key, trigger) in db_triggers)
                if (!Equals(type_trigger, trigger))
                    MainProvider.ExecuteSQL(access, $"DROP TRIGGER {key}");

            if(type_trigger is not null)
            {
                if (!db_triggers.ContainsValue(type_trigger))
                    MainProvider.ExecuteSQL(access, type_trigger);
            }
//#endif
        }

        // private void CheckViews(SQLiteWriteAccessor access, Type type, Dictionary<String, String> db_views)
        // {
        //     var type_view = LoadView(type);
        //     
        //     
        //         if (!type_triggers.Contains(db_triggers[viewname]))
        //             ExecuteSQL(access, string.Format("DROP TRIGGER {0}", trigger));
        //     
        //         if (!db_views.ContainsValue(type_view))
        //             ExecuteSQL(access, type_view);            
        // }

        #endregion

        private void Log(LogType type, string message)
        {
            Logger.Send(type, "", message);
        }
    }

    public class SQLiteProvider : IProvider
    {
        public Logger Logger { get; set; }

        private SQLiteProviderFactory Factory;

        internal SQLiteProvider(SQLiteProviderFactory factory)
        {
            Factory = factory;
        }
     
        private void Connection_Trace(object sender, TraceEventArgs e)
        {
            var type = LogType.Query;
            string[] updates = { "INSERT ", "UPDATE ", "DELETE ", "ALTER ", "DROP ", "CREATE ", "VACUUM" };
            foreach (var update in updates)
                type = e.Statement.ToUpper().StartsWith(update) ? LogType.Update : type;
            Logger.Send(type, "", e.Statement + ";");
        }


        internal SQLiteReadAccessor GetReadAccess()
        {
            var result = new SQLiteReadAccessor(Factory.URL);
            result.Connection.Trace += Connection_Trace;
            return result;
        }

        internal SQLiteWriteAccessor GetWriteAccess()
        {
            var result = new SQLiteWriteAccessor(Factory.URL);
            result.Connection.Trace += Connection_Trace;
            return result;
        }

        internal int ExecuteSQL(SQLiteWriteAccessor writer, string statement)
        {
            var result = 0;
            try
            {
                using (var command = writer.CreateCommand())
                {
                    command.CommandText = statement;
                    result = command.ExecuteNonQuery();
                }
            }
            catch (Exception)
            {
                throw;
            }

            return result;
        }

        #region CRUD Operations

        private static bool IsNull([NotNullWhen(false)] object? o)
        {
            return o == null || o is DBNull;
        }

        public object? Decode(object o, Type type)
        {
            if (IsNull(o))
                return type == typeof(string) ? "" : type.GetDefault();

            if (type == typeof(string[]))
            {
                if (!IsNull(o))
                {
                    if (o is byte[] array)
                        using (var ms = new MemoryStream(array))
                        {
#pragma warning disable SYSLIB0011
                            var deser = new BinaryFormatter().Deserialize(ms);
#pragma warning restore SYSLIB0011
                            return deser as string[];
                        }

                    return Array.Empty<string>();
                }

                return Array.Empty<string>();
            }

            if (type.GetInterfaces().Contains(typeof(IPackable)))
            {
                var packable = (Activator.CreateInstance(type) as IPackable)!; // Not-null because of above check
                if (!IsNull(o))
                {
                    if (o is byte[] array)
                    {
                        using (var ms = new MemoryStream(array))
                            packable.Unpack(new BinaryReader(ms));
                    }
                }

                return packable;
            }

            if (type == typeof(DateTime))
                return IsNull(o) || string.IsNullOrEmpty(o.ToString()) ? DateTime.MinValue : DateTime.Parse(o.ToString() ?? "");

            if (type == typeof(TimeSpan))
            {
                if (IsNull(o))
                    return TimeSpan.MinValue;
                var oStr = o.ToString() ?? "";
                if (double.TryParse(oStr, out var hrs))
                    return TimeSpan.FromHours(hrs);
                if (oStr.Contains(':'))
                    return TimeSpan.Parse(oStr);
            }

            if (type == typeof(Guid))
                return IsNull(o) ? Guid.Empty : Guid.Parse(o.ToString() ?? "");

            if (type == typeof(bool))
            {
                var oStr = o.ToString() ?? "";
                return !IsNull(o) && (oStr.Equals("1") || oStr.ToUpper().Equals("TRUE"));
            }

            if (type.IsEnum)
                return Enum.Parse(type, o.ToString() ?? "");

            if (IsNull(o))
                return null;

            if (type == typeof(long))
                return o;

            var result = CoreUtils.ChangeType(o, type);
            return result;
        }

        public static object Encode(object? o, Type? type)
        {
            if (IsNull(o) || type is null)
                return DBNull.Value;

            if (type == typeof(DateTime) && o.GetType() == typeof(string))
                o = DateTime.Parse(o.ToString() ?? "");

            if (type == typeof(double) && o.GetType() == typeof(string))
                o = double.Parse(o.ToString() ?? "");

            if (type == typeof(float) && o.GetType() == typeof(string))
                o = float.Parse(o.ToString() ?? "");

            if (type == typeof(long) && o.GetType() == typeof(string))
                o = long.Parse(o.ToString() ?? "");

            if (type.IsEnum && o.GetType() == typeof(string))
                o = Enum.Parse(type, o.ToString() ?? "");

            if (type == typeof(TimeSpan) && o.GetType() == typeof(string))
            {
                if (o.ToString()?.Contains(':') == true)
                {
                    if (TimeSpan.TryParse(o.ToString(), out var time))
                        o = time;
                }
                else if (double.TryParse(o.ToString(), out var hrs))
                {
                    o = TimeSpan.FromHours(hrs);
                }
            }

            if (type == typeof(bool))
            {
                if (o.GetType() == typeof(string))
                    o = bool.Parse(o.ToString() ?? "");
                if (o.Equals(false))
                    return DBNull.Value;
                return o;
            }

            if (type == typeof(string) && string.IsNullOrEmpty(o.ToString()))
                return DBNull.Value;


            if (type == typeof(Guid) && o.GetType() == typeof(string))
                o = Guid.Parse(o.ToString() ?? "");

            if (o is string[])
                using (var ms = new MemoryStream())
                {
#pragma warning disable SYSLIB0011
                    new BinaryFormatter().Serialize(ms, o);
#pragma warning restore SYSLIB0011
                    return ms.GetBuffer();
                }

            if (o is IPackable pack)
            {
                using var ms = new MemoryStream();
                using var writer = new BinaryWriter(ms);
                pack.Pack(writer);
                return ms.ToArray();
            }

            if (o.GetType() == typeof(double) && o.Equals(default(double)))
                return DBNull.Value;

            if (o.GetType() == typeof(float) && o.Equals(default(float)))
                return DBNull.Value;

            if (o.GetType() == typeof(int) && o.Equals(default(int)))
                return DBNull.Value;

            if (o.GetType() == typeof(long) && o.Equals(default(long)))
                return DBNull.Value;

            if (o.GetType() == typeof(DateTime))
            {
                if ((DateTime)o == DateTime.MinValue)
                    return DBNull.Value;
                return string.Format("{0:yyyy-MM-dd HH:mm:ss.FFFFFFF}", o);
            }

            if (o.GetType() == typeof(TimeSpan))
            {
                if (((TimeSpan)o).Ticks == 0L)
                    return DBNull.Value;
                return ((TimeSpan)o).TotalHours;
            }

            if (o.GetType() == typeof(Guid))
            {
                if (o.Equals(Guid.Empty))
                    return DBNull.Value;
                return o.ToString() ?? "";
            }

            if (type == typeof(double) && o.GetType() == typeof(string))
                if (double.TryParse((string)o, out var value))
                    o = value;

            if (o.GetType().IsEnum)
                return o.ToString() ?? "";

            return o;
        }

        private static readonly Dictionary<Operator, string> operators = new()
        {
            { Operator.IsEqualTo, "{0} = {1}" },
            { Operator.IsNotEqualTo, "{0} != {1}" },
            { Operator.IsGreaterThan, "{0} > {1}" },
            { Operator.IsGreaterThanOrEqualTo, "{0} >= {1}" },
            { Operator.IsLessThan, "{0} < {1}" },
            { Operator.IsLessThanOrEqualTo, "{0} <= {1}" },
            { Operator.BeginsWith, "{0} LIKE {1} || '%'" },
            { Operator.Contains, "{0} LIKE '%' || {1} || '%'" },
            { Operator.EndsWith, "{0} LIKE '%' || {1}" },
            { Operator.InList, "{0} IN ({1})" },
            { Operator.NotInList, "{0} NOT IN ({1})" },
            { Operator.InQuery, "{0} IN ({1})" },
            { Operator.NotInQuery, "{0} NOT IN ({1})" }
        };

        internal static string EscapeValue(object? value)
        {
            if (IsNull(value))
                return "NULL";
            if ((value is string) || (value is Enum) || (value is Guid))
                return string.Format("\'" + "{0}" + "\'", value.ToString()?.Replace("\'", "\'\'"));
            if (value is string[])
                return string.Format("hex({0})", BitConverter.ToString(Encoding.ASCII.GetBytes(value.ToString() ?? "")).Replace("-", ""));
            if (value is IColumn col)
                return $"[{col.Property}]";
            return value.ToString() ?? "";

        }
        private static string GetFilterConstant(FilterConstant constant)
        {
            return constant switch
            {
                FilterConstant.Null => "NULL",
                
                FilterConstant.Now => "datetime()",
                FilterConstant.Today => "datetime(date())",
                FilterConstant.Zero => "0",
                
                // These figures are based around today
                FilterConstant.OneWeekAgo => "datetime(date(),'-7 days')",
                FilterConstant.OneWeekAhead => "datetime(date(),'+7 days')",
                FilterConstant.OneMonthAgo => "datetime(date(),'-1 month')",
                FilterConstant.OneMonthAhead => "datetime(date(),'+1 month')",
                FilterConstant.ThreeMonthsAgo => "datetime(date(),'-3 months')",
                FilterConstant.ThreeMonthsAhead => "datetime(date(),'+3 months')",
                FilterConstant.SixMonthsAgo => "datetime(date(),'-6 months')",
                FilterConstant.SixMonthsAhead => "datetime(date(),'+6 months')",
                FilterConstant.OneYearAgo => "datetime(date(),'-1 year')",
                FilterConstant.OneYearAhead => "datetime(date(),'+1 year')",
                    
                // Relative Week values run from Monday thru Sunday
                FilterConstant.StartOfLastWeek => "datetime(date(), 'weekday 0', '-13 days')",
                FilterConstant.EndOfLastWeek => "datetime(date(), 'weekday 0', '-6 days', '-000.0001 seconds')",
                FilterConstant.StartOfThisWeek => "datetime(date(), 'weekday 0', '-6 days')",
                FilterConstant.EndOfThisWeek => "datetime(date(), 'weekday 0', '+1 day', '-000.0001 seconds')",
                FilterConstant.StartOfNextWeek => "datetime(date(), 'weekday 0', '+1 days')",
                FilterConstant.EndOfNextWeek => "datetime(date(), 'weekday 0', '+8 days', '-000.0001 seconds')",
                
                FilterConstant.StartOfLastMonth => "datetime(date(), 'start of month', '-1 month')",
                FilterConstant.EndOfLastMonth => "datetime(date(), 'start of month', '-000.0001 seconds')",
                FilterConstant.StartOfThisMonth => "datetime(date(), 'start of month')",
                FilterConstant.EndOfThisMonth => "datetime(date(), 'start of month', '+1 month', '-000.0001 seconds')",
                FilterConstant.StartOfNextMonth => "datetime(date(), 'start of month', '+1 month')",
                FilterConstant.EndOfNextMonth => "datetime(date(), 'start of month', '+2 months', '-000.0001 seconds')",

                FilterConstant.StartOfLastCalendarYear => "datetime(date(), 'start of year', '-1 year')",
                FilterConstant.EndOfLastCalendarYear => "datetime(date(), 'start of year', '-000.0001 seconds')",
                FilterConstant.StartOfThisCalendarYear => "datetime(date(), 'start of year')",
                FilterConstant.EndOfThisCalendarYear => "datetime(date(), 'start of year', '+1 year', '-000.0001 seconds')",
                FilterConstant.StartOfNextCalendarYear => "datetime(date(), 'start of year', '+1 year')",
                FilterConstant.EndOfNextCalendarYear => "datetime(date(), 'start of year', '+2 years', '-000.0001 seconds')",
                
                FilterConstant.StartOfLastFinancialYear => "datetime(date(), '-18 months', 'start of year', '+6 months')",
                FilterConstant.EndOfLastFinancialYear => "datetime(date(), '-18 months', 'start of year', '-18 months', '-000.0001 seconds')",
                FilterConstant.StartOfThisFinancialYear => "datetime(date(), '-6 months', 'start of year', '+6 months')",
                FilterConstant.EndOfThisFinancialYear => "datetime(date(), '-6 months', 'start of year', '+18 months', '-000.0001 seconds')",
                FilterConstant.StartOfNextFinancialYear => "datetime(date(), '+6 months', 'start of year', '+6 months')",
                FilterConstant.EndOfNextFinancialYear => "datetime(date(), '+6 months', 'start of year', '+18 months', '-000.0001 seconds')",

                    
                _ => throw new Exception($"FilterConstant.{constant} is not implemented!"),
            };
        }

        private string GetFilterClauseNonGeneric(Type T, SQLiteCommand command, char prefix, IFilter? filter, List<Tuple<string, string, string, string>> tables,
            Dictionary<string, string> fieldmap, List<string> columns, bool useparams)
        {
            if (filter == null || filter.Expression == null)
                return "";

            var result = "";
            if (filter.Operator == Operator.All)
            {
                result = filter.IsNot ? "1 = 0" : "1 = 1";
            }
            else if (filter.Operator == Operator.None)
            {
                result = filter.IsNot ? "1 = 1" : "1 = 0";
            }
            else
            {
                string prop;
                if (CoreUtils.TryFindMemberExpression(filter.Expression, out var mexp))
                {
                    prop = CoreUtils.GetFullPropertyName(mexp, ".");
                }
                else
                {
                    prop = filter.Expression.ToString();
                    if (prop.Contains("=>"))
                        prop = string.Join(".", prop.Split('.').Skip(1));
                    mexp = CoreUtils.GetMemberExpression(T, prop);
                }

                LoadFieldsandTables(command, T, prefix, fieldmap, tables, columns, prop, useparams);
                if (fieldmap.ContainsKey(prop))
                    prop = fieldmap[prop];

                if(filter.Value is CustomFilterValue)
                {
                    throw new Exception("Custom Filter Value not able to be processed server-side!");
                }

                if (filter.Operator == Operator.InList || filter.Operator == Operator.NotInList)
                {
                    // if, and only if the list contains Guids, we can safely bypass the 
                    // 1000-parameter limit by using building the string ourselves
                    if (filter.Value is Guid[] list)
                    {
                        result = string.Format("(" + operators[filter.Operator] + ")", prop, string.Format("\"{0}\"", string.Join("\",\"", list)));
                    }
                    else if (filter.Value is IEnumerable enumerable)
                    {
                        var paramlist = new List<object>();
                        foreach (var item in enumerable)
                        {
                            var sParam = string.Format("@p{0}", command.Parameters.Count);
                            command.Parameters.AddWithValue(sParam, Encode(item, mexp.Type));
                            paramlist.Add(sParam);
                        }

                        result = string.Format("(" + operators[filter.Operator] + ")", prop, string.Join(",", paramlist));
                    }
                }
                else if (filter.Operator == Operator.InQuery || filter.Operator == Operator.NotInQuery)
                {
                    if(filter.Value is ISubQuery subQuery)
                    {
                        var subEntityType = subQuery.GetQueryType();

                        var subColumns = Columns.None(subEntityType);
                        var subColumn = subQuery.GetColumn();

                        subColumns.Add(subColumn);

                        var subQueryText = PrepareSelectNonGeneric(subEntityType, command, 'A',
                            subQuery.GetFilter(), subColumns, null,
                            null, null, null, false, useparams);

                        result = string.Format("(" + operators[filter.Operator] + ")", prop, subQueryText);
                    }
                }
                else
                {
                    if (filter.Value is FilterConstant constant)
                    {
                        if (constant == FilterConstant.Null)
                        {
                            result = string.Format("({0} {1} NULL)", prop,
                                filter.Operator == Operator.IsEqualTo 
                                    ? "IS"
                                    : "IS NOT");
                        }
                        else
                            result = string.Format("(" + operators[filter.Operator] + ")", prop,
                                GetFilterConstant(constant));
                    }
                    else
                    {
                        var value = Encode(filter.Value, mexp.Type);
                        if (IsNull(value) && ((filter.Operator == Operator.IsEqualTo) || (filter.Operator == Operator.IsNotEqualTo)))
                        {
                            result = string.Format("({0} {1} NULL)", prop,
                                filter.Operator == Operator.IsEqualTo 
                                    ? "IS"
                                    : "IS NOT");
                        }
                        else
                        {
                            if (useparams)
                            {
                                var sParam = string.Format("@p{0}", command.Parameters.Count);

                                if (filter.Expression.Type == typeof(string[]))
                                {
                                    var bytes = Encoding.ASCII.GetBytes(value.ToString() ?? "");
                                    value = BitConverter.ToString(bytes).Replace("-", "");
                                    result = string.Format("(" + operators[filter.Operator] + ")", string.Format("hex({0})", prop), sParam);
                                }
                                else
                                {
                                    result = string.Format("(" + operators[filter.Operator] + ")", prop, sParam);
                                }
                                command.Parameters.AddWithValue(sParam, value);
                            }
                            else
                                result = string.Format("(" + operators[filter.Operator] + ")", prop, EscapeValue(filter.Value));
                        }

                    }
                }

                if (filter.IsNot)
                {
                    result = $"(NOT {result})";
                }
            }

            var bChanged = false;
            if (filter.Ands != null && filter.Ands.Any())
            {
                foreach (var and in filter.Ands)
                {
                    var AndResult = GetFilterClauseNonGeneric(T, command, prefix, and, tables, fieldmap, columns, useparams);
                    if (!string.IsNullOrEmpty(AndResult))
                    {
                        result = string.Format("{0} and {1}", result, AndResult);
                        bChanged = true;
                    }
                }

                if (bChanged)
                    result = string.Format("({0})", result);
            }

            bChanged = false;
            if (filter.Ors != null && filter.Ors.Any())
            {
                foreach (var or in filter.Ors)
                {
                    var OrResult = GetFilterClauseNonGeneric(T, command, prefix, or, tables, fieldmap, columns, useparams);
                    if (!string.IsNullOrEmpty(OrResult))
                    {
                        result = string.Format("{0} or {1}", result, OrResult);
                        bChanged = true;
                    }
                }

                if (bChanged)
                    result = string.Format("({0})", result);
            }

            return result;
        }

        private string GetFilterClause<T>(SQLiteCommand command, char prefix, Filter<T>? filter, List<Tuple<string, string, string, string>> tables,
            Dictionary<string, string> fieldmap, List<string> columns, bool useparams) where T : Entity
            => GetFilterClauseNonGeneric(typeof(T), command, prefix, filter, tables, fieldmap, columns, useparams);

        private string GetSortClauseNonGeneric(Type T, 
            SQLiteCommand command, ISortOrder? sort, char prefix, List<Tuple<string, string, string, string>> tables,
            Dictionary<string, string> fieldmap, List<string> columns, bool useparams)
        {
            if (sort == null)
                return "";

            var result = "";
            if (sort.Expression != null)
            {
                if (CoreUtils.TryFindMemberExpression(sort.Expression, out var mexp))
                    result = CoreUtils.GetFullPropertyName(mexp, ".");
                else
                    result = sort.Expression.ToString();

                var prop = CoreUtils.GetProperty(T, result);
                if (prop.GetCustomAttribute<DoNotSerialize>() == null && prop.GetCustomAttribute<DoNotPersist>() == null && prop.CanWrite)
                {
                    LoadFieldsandTables(command, T, prefix, fieldmap, tables, columns, result, useparams);
                    if (fieldmap.ContainsKey(result))
                        result = fieldmap[result];

                    if (sort.Expression.Type.Equals(typeof(string)))
                        result = string.Format("{0} COLLATE NOCASE", result);

                    if (sort.Direction == SortDirection.Ascending)
                        result = string.Format("{0} ASC", result);
                    else
                        result = string.Format("{0} DESC", result);
                }
                else
                {
                    result = "";
                }
            }

            foreach (var then in sort.Thens)
                result = result + ", " + GetSortClauseNonGeneric(T, command, then, prefix, tables, fieldmap, columns, useparams);

            return result;
        }

        private string GetSortClause<T>(SQLiteCommand command, SortOrder<T>? sort, char prefix, List<Tuple<string, string, string, string>> tables,
            Dictionary<string, string> fieldmap, List<string> columns, bool useparams)
            => GetSortClauseNonGeneric(typeof(T), command, sort, prefix, tables, fieldmap, columns, useparams);

        private static string GetCalculation(AggregateAttribute attribute, string columnname)
        {
            return attribute.Calculation switch
            {
                AggregateCalculation.Sum => "SUM",
                AggregateCalculation.Count => "COUNT",
                AggregateCalculation.Maximum => "MAX",
                AggregateCalculation.Minimum => "MIN",
                AggregateCalculation.Average => "AVERAGE",
                AggregateCalculation.Concat => "GROUP_CONCAT",
                _ => throw new Exception(string.Format("{0}.{1} is not a valid aggregate", columnname, attribute.Calculator.GetType().Name)),
            };
        }

        private string GetFunction(FormulaAttribute attribute, Dictionary<string, string> fieldmap, string columnname)
        {
            if (attribute.Operator == FormulaOperator.None)
                throw new Exception(string.Format("{0}.{1} is not a valid formula", columnname, attribute.Calculator.GetType().Name));
            
            if (attribute.Operator == FormulaOperator.Constant)
                return EscapeValue(attribute.Value);

            if (!fieldmap.ContainsKey(attribute.Value))
                throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.Value));
            
            foreach (var modifier in attribute.Modifiers)
                if (!fieldmap.ContainsKey(modifier))
                    throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, modifier));


            if (attribute.Operator == FormulaOperator.Add)
                return string.Format("(IFNULL({0},0.00) + {1})", fieldmap[attribute.Value],
                    string.Join(" + ", attribute.Modifiers.Select(x => string.Format("IFNULL({0},0.00)", fieldmap[x]))));

            if (attribute.Operator == FormulaOperator.Subtract)
                return string.Format("(IFNULL({0},0.00) - ({1}))", fieldmap[attribute.Value],
                    string.Join(" + ", attribute.Modifiers.Select(x => string.Format("IFNULL({0},0.00)", fieldmap[x]))));

            if (attribute.Operator == FormulaOperator.Multiply)
                return string.Format("(IFNULL({0},0.00) * {1})", fieldmap[attribute.Value],
                    string.Join(" * ", attribute.Modifiers.Select(x => string.Format("IFNULL({0},0.00)", fieldmap[x]))));

            if (attribute.Operator == FormulaOperator.Divide)
            {
                var result = string.Format("IFNULL({0},0.00)", fieldmap[attribute.Value]);
                foreach (var modifier in attribute.Modifiers)
                    result = string.Format("({0} / {1})", result, string.Format("IFNULL({0},1.00)", fieldmap[modifier]));
                return result;
            }

            if (attribute.Operator == FormulaOperator.Maximum)
            {
                var parameters = attribute.Modifiers.Select(m => $"IFNULL({fieldmap[m]},0.00)");
                var result = $"MAX({fieldmap[attribute.Value]}, {String.Join(", ", parameters)})";
                // var result = string.Format(
                //     "CASE IFNULL({0},0.00) WHEN IFNULL({0},0.00) < IFNULL({1},0.00) THEN IFNULL({1},0.00) ELSE IFNULL({0},0.00) END",
                //     fieldmap[attribute.Value], fieldmap[attribute.Modifiers.First()]);
                return result;
            }

            if (attribute.Operator == FormulaOperator.Minumum)
            {
                var parameters = attribute.Modifiers.Select(m => $"IFNULL({fieldmap[m]},0.00)");
                var result = $"MIN({fieldmap[attribute.Value]}, {String.Join(", ", parameters)})";
                //var result = string.Format(
                //    "CASE IFNULL({0},0.00) WHEN IFNULL({0},0.00) > IFNULL({1},0.00) THEN IFNULL({1},0.00) ELSE IFNULL({0},0.00) END",
                //    fieldmap[attribute.Value], fieldmap[attribute.Modifiers.First()]);
                return result;
            }

            throw new Exception(string.Format("Calculation Type [{0}.{1}] not found", columnname, attribute.GetType().Name));
        }

        private string GetCondition(ConditionAttribute attribute, Dictionary<string, string> fieldmap, string columnname)
        {
            var intf = attribute.Calculator.GetType().GetInterfaces()
                .FirstOrDefault(x => x.Name.StartsWith("ICondition") && x.GenericTypeArguments.Length.Equals(3));
            if (intf is null)
                throw new Exception($"Attribute calculate {attribute.Calculator} is not an ICondition");

            var valuetype = intf.GenericTypeArguments[1];
            var defvalue = valuetype.GetDefault();

            if (!fieldmap.ContainsKey(attribute.Left))
                throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.Left));

            var condition = "";
            if (attribute.Condition == Condition.Equals)
                condition = "=";
            else if (attribute.Condition == Condition.NotEqual)
                condition = "<>";
            else if (attribute.Condition == Condition.GreaterThan)
                condition = ">";
            else if (attribute.Condition == Condition.GreaterThanOrEqualTo)
                condition = ">=";
            else if (attribute.Condition == Condition.LessThan)
                condition = "<";
            else if (attribute.Condition == Condition.LessThanOrEqualTo)
                condition = "<=";
            else
                throw new Exception(string.Format("{0}.{1} is not a valid condition", columnname, attribute.Calculator.GetType().Name));

            if (!fieldmap.ContainsKey(attribute.Right))
                throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.Right));

            if (!fieldmap.ContainsKey(attribute.True))
                throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.True));

            if (!fieldmap.ContainsKey(attribute.False))
                throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.False));

            return string.Format("CASE WHEN COALESCE({0},{1}) {2} {3} THEN {4} ELSE {5} END", fieldmap[attribute.Left], defvalue, condition,
                fieldmap[attribute.Right], fieldmap[attribute.True], fieldmap[attribute.False]);
        }
        
        //private static List<string> Breadcrumb = new List<string>();

        //Dictionary<String, Tuple<int, TimeSpan>> stopwatch = new Dictionary<string, Tuple<int, TimeSpan>>();
        //Stopwatch sw = new Stopwatch();

        //private void LogReset()
        //{
        //    stopwatch = new Dictionary<string, Tuple<int, TimeSpan>>();
        //    sw = new Stopwatch();
        //}

        //void LogStart()
        //{
        //    if (sw.IsRunning)
        //        sw.Restart();
        //    else
        //        sw.Start();
        //}

        //void LogStop(String msg)
        //{
        //    TimeSpan elapsed = sw.Elapsed;
        //    if (Breadcrumb.Any())
        //        msg = String.Join(".", Breadcrumb) + "." + msg;
        //    if (!stopwatch.ContainsKey(msg))
        //        stopwatch[msg] = new Tuple<int, TimeSpan>(1,elapsed);
        //    else
        //        stopwatch[msg] = new Tuple<int, TimeSpan>(stopwatch[msg].Item1 + 1,stopwatch[msg].Item2 + elapsed);
        //    sw.Restart();
        //}

        //private void LogPrint(String header)
        //{
        //    foreach (var keyvalue in stopwatch.ToArray())
        //        Log(LogType.Information, String.Format("- {0}: {1} = ({2}) {3,10:0.0000}", header, keyvalue.Key, keyvalue.Value.Item1, keyvalue.Value.Item2.TotalMilliseconds));
        //}

        private void LoadFieldsandTables(SQLiteCommand command, Type type, char prefix, Dictionary<string, string> fieldmap,
            List<Tuple<string, string, string, string>> tables, List<string> columns, string columnname, bool useparams)
        {
            if (fieldmap.ContainsKey(columnname))
                return;

            var newprefix = (char)(prefix + 1);

            // LogStart();
            var property = DatabaseSchema.Property(type, columnname);
            // LogStop("DatabaseSchema.Property");

            if (property != null && property is CustomProperty)
            {
                fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
            }
            else
            {
                var bits = columnname.Split('.');
                if (bits.Length == 1)
                {
                    var prop = type.GetProperty(columnname);
                    if (prop != null)
                    {
                        // LogStart();
                        // LogStop("GetCustomAttributes(Aggregate)");

                        if (prop.GetCustomAttribute<AggregateAttribute>() is AggregateAttribute agg)
                        {
                            bool internalaggregate = agg.Calculator.GetType().GetInterfaces()
                                .Any(x => x.IsGenericType && x.GetGenericTypeDefinition() == typeof(ICoreAggregate<,>));

                            if (internalaggregate)
                            {
                                
                            }
                            else
                            {

                                var scols = new Dictionary<string, string> { { agg.Aggregate, GetCalculation(agg, columnname) } };

                                var siblings = columns.Where(x => !x.Equals(columnname) && x.Split('.').First().Equals(bits.First()))
                                    .Select(x => string.Join(".", x.Split('.').Skip(1))).ToList();
                                var linkedtype = agg.Source;
                                foreach (var sibling in siblings)
                                {
                                    // LogStart();
                                    var sprop = CoreUtils.GetProperty(type, sibling);
                                    // LogStop("GetProperty(sibling)");

                                    // LogStart();
                                    var sattr = prop.GetCustomAttributes().FirstOrDefault(x => x.GetType().Equals(typeof(AggregateAttribute)));
                                    // LogStop("GetCustomAttributes(Sibling.Aggregate)");
                                    if (sattr is AggregateAttribute sagg)
                                    {
                                        // LogStart();
                                        scols[sagg.Aggregate] = GetCalculation(sagg, sibling);
                                        // LogStop("GetCalculation(Sibling)");
                                    }
                                }

                                var subcols = Columns.None(linkedtype);

                                foreach (var key in agg.Links.Keys)
                                    subcols.Add(key);
                                //subcols.Add(agg.Link);

                                foreach (var scol in scols.Keys)
                                    subcols.Add(scol);

                                // LogStart();

                                var aggFilter = agg.Filter;
                                if(aggFilter is not null)
                                {
                                    var ffs = new List<string>();
                                    FilterFields(aggFilter, ffs);
                                    //foreach (var ff in ffs)
                                    //    subcols.Add(ff);
                                }

                                var linkedtable = string.Format("({0})",
                                    PrepareSelectNonGeneric(linkedtype, command, newprefix, aggFilter, subcols, null, scols, null, null, false, useparams));
                                
                                var alias = tables.Count + 1;

                                var link = string.Join(" , ", agg.Links.Keys.Select(x => string.Format("{0}{1}.{2}", prefix, alias, x)));

                                var tuple = tables.FirstOrDefault(x =>
                                    x.Item1.Equals(linkedtable) && x.Item4.Equals(link) && !x.Item2.Equals(string.Format("{0}1", prefix)));
                                if (tuple == null)
                                {
                                    var joins = new List<string>();

                                    foreach (var key in agg.Links.Keys)
                                        joins.Add(
                                            string.Format("IFNULL({0}{1}.[{2}],'') = IFNULL({0}1.[{3}],'')", prefix, alias, key, agg.Links[key]));
                                    var join = string.Format("LEFT OUTER JOIN {0} {1}{2} ON {3}", linkedtable, prefix, alias,
                                        string.Join(" AND ", joins));
                                    tuple = new Tuple<string, string, string, string>(linkedtable, prefix + alias.ToString(), join, link);

                                    //tuple = new Tuple<String, String, String, String>(linkedtable, prefix + alias.ToString(), String.Format("LEFT OUTER JOIN {0} {1}{2} ON {1}{2}.[{3}] = {1}1.[ID]", linkedtable, prefix, alias, agg.Link), link);

                                    tables.Add(tuple);
                                }

                                //if (bits.Last().Equals("ID"))
                                //    fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
                                //else
                                fieldmap[columnname] = string.Format("{0}.[{1}]", tuple.Item2, agg.Aggregate);

                                //LoadFieldsandTables(command, agg.Type, newprefix, fieldmap, tables, columns, columnname);
                                //var linkedtable = String.Format("(SELECT [{3}],{0}([{1}]) as [{1}] from {2} group by [{3}])", GetCalculation(agg, columnname), agg.AggregateProperty, agg.Type.EntityName().Split('.').Last(), agg.LinkedProperty);
                                //int alias = tables.Count + 1;
                                //var tuple = new Tuple<String, String, String, String>(linkedtable, prefix + alias.ToString(), String.Format("LEFT OUTER JOIN {0} {1}{2} ON {1}{2}.[{3}] = {1}1.[ID]", linkedtable, prefix, alias, agg.LinkedProperty), "");
                                //tables.Add(tuple);
                                //fieldmap[columnname] = String.Format("{0}.[{1}]", tuple.Item2, agg.AggregateProperty);
                            }
                        }
                        else if (prop.GetCustomAttribute<FormulaAttribute>() is FormulaAttribute fnc)
                        {

                            // var functionmap = new Dictionary<string, string>();
                            // CheckColumn(columns, fnc.Value);
                            // LoadFieldsandTables(command, type, prefix, functionmap, tables, columns, fnc.Value, useparams);
                            // foreach (var column in fnc.Modifiers)
                            // {
                            //     CheckColumn(columns, column);
                            //     LoadFieldsandTables(command, type, prefix, functionmap, tables, columns, column, useparams);
                            // }
                            // fieldmap[columnname] = GetFunction(fnc, functionmap, columnname);

                            foreach (var field in fnc.Modifiers.Prepend(fnc.Value))
                            {
                                CheckColumn(columns, field);
                                LoadFieldsandTables(command, type, prefix, fieldmap, tables, columns, field, useparams);
                            }
                            fieldmap[columnname] = GetFunction(fnc, fieldmap, columnname);

                        }
                        else if(prop.GetCustomAttribute<ConditionAttribute>() is ConditionAttribute cnd)
                        {
                            var cndmap = new Dictionary<string, string>();
                            // LogStart();
                            CheckColumn(columns, cnd.Left);
                            // LogStop("CheckColumn(Left)");

                            // LogStart();
                            CheckColumn(columns, cnd.Right);
                            // LogStop("CheckColumn(Right)");

                            // LogStart();
                            CheckColumn(columns, cnd.True);
                            // LogStop("CheckColumn(True)");

                            // LogStart();
                            CheckColumn(columns, cnd.False);
                            // LogStop("CheckColumn(False)");

                            //// LogStart();
                            LoadFieldsandTables(command, type, prefix, cndmap, tables, columns, cnd.Left, useparams);
                            //// LogStop("LoadFieldsAndTables(Left)");

                            //// LogStart();
                            LoadFieldsandTables(command, type, prefix, cndmap, tables, columns, cnd.Right, useparams);
                            //// LogStop("LoadFieldsAndTables(Right)");

                            //// LogStart();
                            LoadFieldsandTables(command, type, prefix, cndmap, tables, columns, cnd.True, useparams);
                            //// LogStop("LoadFieldsAndTables(True)");

                            //// LogStart();
                            LoadFieldsandTables(command, type, prefix, cndmap, tables, columns, cnd.False, useparams);
                            //// LogStop("LoadFieldsAndTables(False)");

                            // LogStart();
                            fieldmap[columnname] = GetCondition(cnd, cndmap, columnname);
                            // LogStop("GetCondition");
                        }
                        else
                        {
                            fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
                        }
                    }
                    else
                    {
                        //IProperty customprop = DataModel.Property(type, columnname);
                        //if (customprop != null)
                        //    fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
                        //else
                        fieldmap[columnname] = columnname;
                    }
                }
                else
                {
                    var prop = type.GetProperty(bits.First());
                    if (prop != null)
                    {
                        var combinecount = 1;
                        while (prop.PropertyType.HasInterface<IEnclosedEntity>())
                        {
                            combinecount++;
                            prop = CoreUtils.GetProperty(type, string.Join(".", bits.Take(combinecount)));
                        }

                        var entityLinkInterface = prop.PropertyType.GetInterfaceDefinition(typeof(IEntityLink<>));
                        if (entityLinkInterface is not null)
                        {
                            var linkedType = entityLinkInterface.GenericTypeArguments[0];
                            var enclosingProperty = string.Join('.', bits.Take(combinecount)) + ".";
                            var remoteProperty = string.Join('.', bits.Skip(combinecount));

                            var remote = linkedType.GetProperty(remoteProperty);

                            // Are there any other properties for this link?  These will form the columns for our subquery
                            var siblings = columns.Where(x => x.StartsWith(enclosingProperty))
                                .Select(x => x[enclosingProperty.Length..]).ToList();
                            if (remote != null && !siblings.Contains(remote.Name))
                                siblings.Add(remote.Name);

                            if(prop.GetCustomAttribute<ChildEntityAttribute>() is ChildEntityAttribute child)
                            {
                                var parent = child.Calculator.ParentColumn;

                                if (!siblings.Contains(nameof(Entity.ID)))
                                    siblings.Insert(0, nameof(Entity.ID));
                                if (!siblings.Contains(parent))
                                    siblings.Add(parent);

                                var subcols = Columns.None(linkedType).Add(siblings);

                                var subPrefix = (char)(newprefix + 1);

                                var innerSQL = string.Format("({0})",
                                    PrepareSelectNonGeneric(
                                        linkedType,
                                        command,
                                        subPrefix,
                                        child.Calculator.Filter,
                                        subcols,
                                        child.Calculator.Sort,
                                        null,
                                        null,
                                        null,
                                        false,
                                        useparams));

                                var linkedTable = $"(SELECT {string.Join(", ", siblings.Select(x => $"{newprefix}1.[{x}] as [{x}]"))}"
                                    + $" FROM {innerSQL} {newprefix}1"
                                    + $" GROUP BY [{parent}])";

                                var link = string.Format("{0}.ID", prop.Name);
                                var tuple = tables.FirstOrDefault(x =>
                                    x.Item1.Equals(linkedTable) && x.Item4.Equals(link) && !x.Item2.Equals(string.Format("{0}1", prefix)));
                                if (tuple is null)
                                {
                                    var alias = tables.Count + 1;
                                    tuple = new Tuple<string, string, string, string>(
                                        linkedTable,
                                        prefix + alias.ToString(),
                                        string.Format(
                                            "LEFT OUTER JOIN {0} {1}{2} ON {1}{2}.[{3}] = {1}1.[ID]",
                                            linkedTable,
                                            prefix,
                                            alias,
                                            parent
                                        ),
                                        link
                                    );
                                    tables.Add(tuple);
                                }

                                //if (bits.Last().Equals("ID"))
                                //    fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
                                //else
                                fieldmap[columnname] = string.Format("{0}.[{1}]", tuple.Item2, string.Join(".", bits.Skip(combinecount)));
                                foreach (var sibling in siblings)
                                {
                                    var subcol = string.Format("{0}.{1}", string.Join(".", bits.Take(combinecount)), sibling);
                                    if (!subcol.Equals(columnname))
                                        fieldmap[subcol] = string.Format("{0}.[{1}]", tuple.Item2, sibling);
                                }
                            }
                            else
                            {
                                if (siblings.Count.Equals(1) && siblings.First().Equals("ID"))
                                {
                                    fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
                                }
                                else
                                {
                                    if (!siblings.Contains("ID"))
                                        siblings.Insert(0, "ID");

                                    var subcols = Columns.None(linkedType).Add(siblings);

                                    var linkedtable = string.Format("({0})",
                                        PrepareSelectNonGeneric(linkedType, command, newprefix, null, subcols, null, null, null, null, false, useparams));

                                    var link = string.Format("{0}.ID", prop.Name);
                                    var tuple = tables.FirstOrDefault(x =>
                                        x.Item1.Equals(linkedtable) && x.Item4.Equals(link) && !x.Item2.Equals(string.Format("{0}1", prefix)));
                                    if (tuple == null)
                                    {
                                        var alias = tables.Count + 1;
                                        tuple = new Tuple<string, string, string, string>(
                                            linkedtable,
                                            prefix + alias.ToString(),
                                            string.Format(
                                                "LEFT OUTER JOIN {0} {1}{2} ON {1}{2}.[ID] = {1}1.[{3}.ID]",
                                                linkedtable,
                                                prefix,
                                                alias,
                                                string.Join(".", bits.Take(combinecount)) //prop.Name
                                            ),
                                            link
                                        );
                                        tables.Add(tuple);
                                    }

                                    //if (bits.Last().Equals("ID"))
                                    //    fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
                                    //else
                                    fieldmap[columnname] = string.Format("{0}.[{1}]", tuple.Item2, string.Join(".", bits.Skip(combinecount)));
                                    foreach (var sibling in siblings)
                                    {
                                        var subcol = string.Format("{0}.{1}", string.Join(".", bits.Take(combinecount)), sibling);
                                        if (!subcol.Equals(columnname))
                                            fieldmap[subcol] = string.Format("{0}.[{1}]", tuple.Item2, sibling);
                                    }
                                }
                            }
                        }
                        else if (prop.PropertyType.GetInterfaces().Contains(typeof(IEnclosedEntity)))
                        {
                            fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
                        }
                        else
                        {
                            fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
                        }
                    }
                    else
                    {
                        fieldmap[columnname] = columnname;
                    }
                }
            }
        }

        private static void CheckColumn(List<string> columns, string column)
        {
            if (!columns.Contains(column))
                columns.Add(column);
        }

        public void FilterFields(IFilter? filter, List<string> fields)
        {
            if (filter == null)
                return;
            if (filter.Operator != Operator.None && filter.Operator != Operator.All)
            {
                var exp = CoreUtils.GetFullPropertyName(CoreUtils.ExtractMemberExpression(filter.Expression), ".");
                if (!fields.Contains(exp))
                    fields.Add(exp);
            }
            foreach (var and in filter.Ands)
                FilterFields(and, fields);
            foreach (var or in filter.Ors)
                FilterFields(or, fields);
        }

        public void SortFields(ISortOrder? sort, List<string> fields)
        {
            if (sort == null)
                return;
            var exp = CoreUtils.GetFullPropertyName(CoreUtils.ExtractMemberExpression(sort.Expression), ".");
            if (!fields.Contains(exp))
                fields.Add(exp);
            foreach (var then in sort.Thens)
                SortFields(then, fields);
        }

        public enum AggregateType
        {
            None,
            Sum,
            Count
        }

        public string PrepareSelectNonGeneric(Type T, SQLiteCommand command, char prefix,
            IFilter? filter, IColumns? columns, ISortOrder? sort,
            Dictionary<string, string>? aggregates, Dictionary<string, object?>? constants, CoreRange? range, bool distinct, bool useparams)
        {

            var fieldmap = new Dictionary<string, string>();

            var cols = CoreUtils.GetColumns(T, columns);

            var fields = new List<string>();

            fields.AddRange(cols.ColumnNames());

            FilterFields(filter, fields);
            SortFields(sort, fields);

            var tables = new List<Tuple<string, string, string, string>>();

            var condition = "";
            var sortorder = "";

            var entityName = T.EntityName().Split('.').Last();
            tables.Add(new Tuple<string, string, string, string>(
                entityName,
                $"{prefix}1",
                $"{entityName} {prefix}1",
                "")
            );

            foreach (var column in cols.ColumnNames())
                LoadFieldsandTables(command, T, prefix, fieldmap, tables, fields, column, useparams);

            var parameters = new Dictionary<string, object>();

            condition = GetFilterClauseNonGeneric(T, command, prefix, filter, tables, fieldmap, fields, useparams);
            sortorder = GetSortClauseNonGeneric(T, command, sort, prefix, tables, fieldmap, fields, useparams);

            var combined = new SortedDictionary<string, string>();

            fields.Clear();
            foreach (var column in cols.ColumnNames())
                if (fieldmap.TryGetValue(column, out string? value))
                {
                    if (aggregates != null && aggregates.ContainsKey(column))
                        combined[constants != null ? column : String.Format("{0:D8}", combined.Keys.Count)] = string.Format("{0}({1}) as [{2}]", aggregates[column], value, column);
                    else
                        combined[constants != null ? column : String.Format("{0:D8}", combined.Keys.Count)] = string.Format("{0} as [{1}]", value, column);
                }

            if (constants != null)
            {
                foreach(var (column, value) in constants)
                {
                    combined[column] = string.Format("{0} as [{1}]", EscapeValue(value), column);
                }
            }

            var result = new List<string>();
            result.Add("SELECT");
            if (distinct)
                result.Add("DISTINCT");
            result.Add(string.Join(", ", combined.Values));
            result.Add("FROM");
            result.AddRange(tables.Select(x => x.Item3));

            if (range?.Type == CoreRangeType.Database && range.Offset != 0)
            {
                var subColumns = Columns.None(T).Add("ID");

                var subQuery = PrepareSelectNonGeneric(T, command, 'A', filter, subColumns, sort, null, null, CoreRange.Database(range.Offset), false, useparams);

                var idColumn = $"{prefix}1.[ID]";
                var offsetCondition = $"({idColumn} NOT IN ({subQuery}))";
                if (!condition.IsNullOrWhiteSpace())
                {
                    condition = $"({offsetCondition} and {condition})";
                }
                else
                {
                    condition = offsetCondition;
                }
            }
            if (!string.IsNullOrWhiteSpace(condition))
            {
                result.Add("WHERE");
                result.Add(condition);
            }

            if (!string.IsNullOrWhiteSpace(sortorder))
            {
                result.Add("ORDER BY");
                result.Add(sortorder);
            }

            if (aggregates != null)
            {
                var str = string.Join(", ",
                    fieldmap.Where(x => cols.Contains(x.Key) && !aggregates.ContainsKey(x.Key)).Select(f => f.Value));

                if (!string.IsNullOrWhiteSpace(str))
                {
                    result.Add("GROUP BY");
                    result.Add(str);
                }
            }

            if (range?.Type == CoreRangeType.Database)
            {
                if (range.Limit != int.MaxValue)
                    result.Add($"LIMIT {range.Limit}");

                //if (range.Offset != 0)
                //    result.Add($"OFFSET {range.Offset}");
            }
            
            return string.Join(" ", result);
        }

        public string PrepareSelect<T>(SQLiteCommand command, char prefix, Filter<T>? filter, Columns<T>? columns, SortOrder<T>? sort,
            Dictionary<string, string>? aggregates, Dictionary<string, object?>? constants, CoreRange? range, bool distinct, bool useparams) where T : Entity
            => PrepareSelectNonGeneric(typeof(T), command, prefix, filter, columns, sort, aggregates, constants, range, distinct, useparams);

        private void PrepareUpsertNonGeneric(Type T, SQLiteCommand command, Entity item)
        {
            command.CommandText = "";
            command.Parameters.Clear();

            if (item.ID == Guid.Empty)
                item.ID = Guid.NewGuid();

            var insert = item.GetValues(true);
            var update = item.GetValues(false);

            var insertfields = new List<string>();
            var insertvalues = new List<string>();
            var updatecommands = new List<string>();

            var iParam = 0;
            foreach (var (key, v) in insert)
            {
                if (v is UserProperties)
                    continue;

                var sParam = string.Format("@p{0}", iParam++);
                object? value = null;
                try
                {
                    value = Encode(v, v?.GetType());
                }
                catch (Exception e)
                {
                    Logger.Send(LogType.Error, "", string.Format("*** Unknown Error: {0}\n{1}", e.Message, e.StackTrace));
                }

                command.Parameters.AddWithValue(sParam, value);

                insertfields.Add(string.Format("[{0}]", key));

                insertvalues.Add(sParam);

                if (update.ContainsKey(key))
                    if (!key.Equals("ID"))
                        updatecommands.Add(string.Format("[{0}]={1}", key, sParam));
            }

            var particles = new List<string>
            {
                "INSERT INTO",
                T.EntityName().Split('.').Last(),
                "(",
                string.Join(",", insertfields),
                ")",
                "VALUES",
                "(",
                string.Join(",", insertvalues),
                ")"
            };
            if (updatecommands.Count != 0)
                particles.Add("ON CONFLICT([ID]) DO UPDATE SET " + string.Join(", ", updatecommands));
            else
                particles.Add("ON CONFLICT DO NOTHING");
            command.CommandText = string.Join(" ", particles);
        }

        private void PrepareUpsert<T>(SQLiteCommand command, T item) where T : Entity
            => PrepareUpsertNonGeneric(typeof(T), command, item);

        public void PrepareDelete<T>(SQLiteCommand command, T item) where T : Entity
        {
            command.CommandText = string.Format("DELETE FROM {0} WHERE [ID] = @p0", typeof(T).EntityName().Split('.').Last());
            command.Parameters.Clear();
            command.Parameters.AddWithValue("@p0", Encode(item.ID, typeof(Guid)));
        }

        #endregion

        #region CRUD Operations
        
        public static object[] GetValues(IDataReader reader, int count)
        {
            var result = new object[count];
            reader.GetValues(result);
            return result;
        }

        #region Query

        public IEnumerable<object[]> List<T>(Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null, CoreRange? range = null) where T : Entity, new()
        {
            //Dictionary<String, TimeSpan> stopwatch = new Dictionary<string, TimeSpan>();
            //Stopwatch sw = new Stopwatch();
            //sw.Start();

            var cols = CoreUtils.GetColumns(columns);
            //stopwatch["GetColumns"] = new TimeSpan(sw.ElapsedTicks);
            //sw.Restart();

            var result = new List<object[]>();
            //stopwatch["MakeResult"] = new TimeSpan(sw.ElapsedTicks);
            //sw.Restart();

            using (var access = GetReadAccess())
            {
                using (var command = access.CreateCommand())
                {
                    var sortorder = sort ?? LookupFactory.DefineSort<T>();

                    command.CommandText = "";
                    command.Parameters.Clear();
                    command.CommandText = PrepareSelect(command, 'A', filter, cols, sortorder, null, null, range, false, true) + ";";

                    using (var reader = command.ExecuteReader())
                    {
                        foreach (var row in reader)
                        {
                            var values = GetValues(reader, cols.Count);
                            result.Add(values);
                        }
                        reader.Close();
                    }
                }
            }

            return result;
        }

        private CoreTable DoQueryNonGeneric(Type T, IFilter? filter = null, IColumns? columns = null, ISortOrder? sort = null, CoreRange? range = null, bool log = false, bool distinct = false)
        {
            var start = DateTime.Now;
            //LogReset();

            //LogStart();
            //LogStop("GetColumns");

            var cols = CoreUtils.GetColumns(T, columns);

            var blobColumns = Columns.None(T);
            foreach(var column in cols)
            {
                var prop = DatabaseSchema.Property(T, column.Property);
                if (prop is not null && prop.HasAttribute<ExternalStorageAttribute>())
                {
                    blobColumns.Add(column);
                }
            }
            if(blobColumns.Count > 0)
            {
                cols.Add("ID");
            }

            var result = new CoreTable(T.EntityName());
            
            if (range?.Type == CoreRangeType.Paged)
                result.Offset = range.Offset;
            
            foreach (var col in cols)
                result.Columns.Add(new CoreColumn { ColumnName = col.Property, DataType = col.Type });
            //LogStop("MakeTable");

            using (var access = GetReadAccess())
            {
                using (var command = access.CreateCommand())
                {
                    var sortorder = sort ?? LookupFactory.DefineSort(T);

                    command.CommandText = "";
                    command.Parameters.Clear();

                    //LogStart();
                    var sql = PrepareSelectNonGeneric(T, command, 'A', filter, cols, sortorder, null, null, range, distinct, true) + ";";

                    command.CommandText = sql;

                    try
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                var rows = new List<CoreRow>();
                                int iRow = 0;
                                while (reader.Read())
                                {

                                    // casting to long here to prevent integer overflows
                                    bool exclude = range?.Type == CoreRangeType.Paged &&
                                        ((iRow < range.Offset) || (iRow >= ((long)range.Offset + (long)range.Limit)));

                                    if (!exclude)
                                    {
                                        
                                        var row = result.NewRow();

                                        for (var i = 0; i < reader.FieldCount; i++)
                                            try
                                            {
                                                var type = result.Columns[i].DataType;
                                                if (reader.IsDBNull(i))
                                                {
                                                    row.Values.Add(result.Columns[i].DataType.GetDefault());
                                                }
                                                else if (type.IsEnum)
                                                {
                                                    ReadAndDecodeValue(result, reader, row, i);
                                                }
                                                else if (type == typeof(byte))
                                                {
                                                    row.Values.Add(reader.GetByte(i));
                                                }
                                                else if (type == typeof(byte[]))
                                                {
                                                    ReadAndDecodeValue(result, reader, row, i);
                                                }
                                                else if (type == typeof(bool))
                                                {
                                                    ReadAndDecodeValue(result, reader, row, i);
                                                }
                                                else if (type == typeof(short))
                                                {
                                                    row.Values.Add(reader.GetInt16(i));
                                                }
                                                else if (type == typeof(int))
                                                {
                                                    row.Values.Add(reader.GetInt32(i));
                                                }
                                                else if (type == typeof(long))
                                                {
                                                    row.Values.Add(reader.GetInt64(i));
                                                }
                                                else if (type == typeof(float))
                                                {
                                                    row.Values.Add(reader.GetFloat(i));
                                                }
                                                else if (type == typeof(double))
                                                {
                                                    row.Values.Add(reader.GetDouble(i));
                                                }
                                                else if (type == typeof(decimal))
                                                {
                                                    row.Values.Add(reader.GetDecimal(i));
                                                }
                                                else if (type == typeof(string))
                                                {
                                                    var value = reader.GetString(i);
                                                    row.Values.Add(value != null ? value : "");
                                                }
                                                else if (type == typeof(DateTime))
                                                {
                                                    ReadAndDecodeValue(result, reader, row, i);
                                                }
                                                else if (type == typeof(TimeSpan))
                                                {
                                                    ReadAndDecodeValue(result, reader, row, i);
                                                }
                                                else if (type == typeof(Guid))
                                                {
                                                    row.Values.Add(reader.GetGuid(i));
                                                }
                                                else
                                                {
                                                    ReadAndDecodeValue(result, reader, row, i);
                                                }
                                            }
                                            catch (Exception e)
                                            {
                                                row.Values.Add(result.Columns[i].DataType.GetDefault());
                                            }

                                        result.Rows.Add(row);
                                    }

                                    iRow++;
                                }

                                result.TotalRecords = iRow;
                            }

                            reader.Close();
                        }
                    }
                    catch (Exception e)
                    {
                        Logger.Error($"{e.Message}: {sql}");
                    }
                    //LogStop("ReadData");

                    //LogPrint(String.Format("Query{0}", typeof(T).Name));
                }
            }

            foreach(var column in blobColumns.ColumnNames())
            {
                foreach(var row in result.Rows)
                {
                    var id = row.Get<Guid>("ID");
                    var data = GetExternalData(T, column, id);
                    if(data is not null)
                    {
                        row.Set<byte[]>(column, data);
                    }
                }
            }

            if (log)
            {
                var duration = DateTime.Now - start;
                Logger.Send(LogType.Information, "",
                    string.Format("SQLite::Query<{0}>({1} cols) returns {2} rows in {3}ms", T.Name, cols.Count, result.Rows.Count,
                        duration.TotalMilliseconds));
            }
            return result;
        }

        private CoreTable DoQuery<T>(Filter<T>? filter, Columns<T>? columns, SortOrder<T>? sort, CoreRange? range, bool log, bool distinct)
            where T : Entity, new() => DoQueryNonGeneric(typeof(T), filter, columns, sort, range, log, distinct);

        public CoreTable Query(Type type, IFilter? filter, IColumns? columns, ISortOrder? sort, CoreRange? range, bool log, bool distinct)
            => DoQueryNonGeneric(type, filter, columns, sort, range, log, distinct);
        
        public CoreTable Query<T>(Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null, CoreRange? range = null, bool log = false, bool distinct = false) 
            where T : Entity, new()
        {
            return DoQuery(filter, columns, sort, range, log, distinct);
        }

        [Obsolete]
        public CoreTable QueryDeleted<T>(Deletion deletion, Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null, CoreRange? range = null, bool log = false)
            where T : Entity, new()
        {
            if (filter != null)
                filter.And(x => x.Deleted).IsEqualTo(deletion.ID);
            else
                filter = new Filter<T>(x => x.Deleted).IsEqualTo(deletion.ID);

            return DoQuery(filter, columns, sort, range, false, false);
        }

        private void ReadAndDecodeValue(CoreTable result, SQLiteDataReader reader, CoreRow row, int i)
        {
            var value = reader.GetValue(i);
            var decoded = Decode(value, result.Columns[i].DataType);
            row.Values.Add(decoded);
        }

        public T[] Load<T>(Filter<T>? filter = null, SortOrder<T>? sort = null, CoreRange? range = null) where T : Entity, new()
        {
            var result = new List<T>();
            var cols = CoreUtils.GetColumns<T>(null);

            using (var access = GetReadAccess())
            {
                using (var command = access.CreateCommand())
                {
                    try
                    {
                        command.CommandText = "";
                        command.Parameters.Clear();
                        command.CommandText = PrepareSelect(command, 'A', filter, cols, sort, null, null, range, false, true) + ";";
                        using (var reader = command.ExecuteReader())
                        {
                            if (reader.HasRows)
                                while (reader.Read())
                                {
                                    var entity = new T();
                                    entity.SetObserving(false);
                                    for (var i = 0; i < reader.FieldCount; i++)
                                    {
                                        object value;
                                        if (cols[i].Type == typeof(long) && !reader.IsDBNull(i))
                                            value = reader.GetInt64(i);
                                        else
                                            value = reader.GetValue(i);
                                        try
                                        {
                                            var decoded = Decode(value, cols[i].Type);
                                            CoreUtils.SetPropertyValue(entity, cols[i].Property, decoded);
                                        }
                                        catch (Exception e)
                                        {
                                            Logger.Send(LogType.Error, "", string.Format("*** Unknown Error: {0}\n{1}", e.Message, e.StackTrace));
                                        }
                                    }

                                    entity.SetObserving(true);
                                    result.Add(entity);
                                }

                            reader.Close();
                        }
                    }
                    catch (Exception e)
                    {
                        Logger.Send(LogType.Error, "", string.Format("*** Unknown Error: {0}\n{1}", e.Message, e.StackTrace));
                    }
                }
            }

            return result.ToArray();
        }

        #endregion

        #region Save

        private static readonly Dictionary<Type, IProperty[]> _externalProperties = new Dictionary<Type, IProperty[]>();
        private static IProperty[] GetExternalProperties(Type T)
        {
            if(!_externalProperties.TryGetValue(T, out var properties))
            {
                properties = DatabaseSchema.Properties(T).Where(x => x.HasAttribute<ExternalStorageAttribute>()).ToArray();
                _externalProperties.Add(T, properties);
            }
            return properties;
        }

        private void OnSaveNonGeneric(Type T, Entity entity)
        {
            var props = GetExternalProperties(T);
            List<(IProperty, byte[])>? data = null;
            if (props.Any())
            {
                data = new List<(IProperty, byte[])>();
                foreach(var prop in props)
                {
                    var value = prop.Getter()(entity) as byte[];
                    if(value is not null && value.Length > 0)
                    {
                        data.Add(new(prop, value));
                        prop.Setter()(entity, Array.Empty<byte>());
                    }
                }
            }

            using var access = GetWriteAccess();
            using var command = access.CreateCommand();

            PrepareUpsertNonGeneric(T, command, entity);
            command.ExecuteNonQuery();

            if(data is not null)
            {
                foreach(var (prop, value) in data)
                {
                    SaveExternalData(T, prop.Name, entity.ID, value);
                    prop.Setter()(entity, value);
                }
            }
        }

        private void OnSaveNonGeneric(Type T, IEnumerable<Entity> entities)
        {
            // Casting to IList so that we can use it multiple times.
            entities = entities.AsIList();

            if (!entities.Any())
                return;

            var props = GetExternalProperties(T);
            List<(IProperty, List<(Entity, byte[])>)>? data = null;
            if (props.Any())
            {
                data = new List<(IProperty, List<(Entity, byte[])>)>();
                foreach(var prop in props)
                {
                    var lst = new List<(Entity, byte[])>();
                    foreach(var entity in entities)
                    {
                        var value = prop.Getter()(entity) as byte[];
                        if(value is not null && value.Length > 0)
                        {
                            lst.Add((entity, value));
                            prop.Setter()(entity, Array.Empty<byte>());
                        }
                    }
                    data.Add(new(prop, lst));
                }
            }

            using var access = GetWriteAccess();
            using var transaction = access.BeginTransaction();

            try
            {
                using var command = access.CreateCommand();
                foreach (var entity in entities)
                {
                    PrepareUpsertNonGeneric(T, command, entity);
                    command.ExecuteNonQuery();
                }

                transaction.Commit();

                if(data is not null)
                {
                    foreach(var (property, list) in data)
                    {
                        foreach(var (entity, value) in list)
                        {
                            SaveExternalData(T, property.Name, entity.ID, value);
                            property.Setter()(entity, value);
                        }
                    }
                }
            }
            catch (Exception)
            {
                transaction.Rollback();
                throw;
            }
        }

        private void OnSave<T>(T entity) where T : Entity
            => OnSaveNonGeneric(typeof(T), entity);

        public static bool CanSave(Type T)
        {
            if (DbFactory.IsReadOnly)
            {
                if (T.IsAssignableTo(typeof(License)) || T.IsAssignableTo(typeof(UserTracking)))
                    return true;
                DbFactory.LogReadOnly();
                throw new DbLockedException();
            }
            return true;
        }
        public static bool CanSave<T>() => CanSave(typeof(T));

        public void Save(Type type, Entity entity)
        {
            if (!CanSave(type))
            {
                return;
            }
            OnSaveNonGeneric(type, entity);
        }
        public void Save(Type type, IEnumerable<Entity> entities)
        {
            if (!CanSave(type))
            {
                return;
            }
            OnSaveNonGeneric(type, entities);
        }

        public void Save<T>(IEnumerable<T> entities) where T : Entity => Save(typeof(T), entities);

        public void Save<T>(T entity) where T : Entity => Save(typeof(T), entity);

        #endregion

        #region Delete

        public void Purge<T>(T entity) where T : Entity
        {
            if (!CanSave<T>()) return;

            using var access = GetWriteAccess();
            using var command = access.CreateCommand();
            PrepareDelete(command, entity);
            var rows = command.ExecuteNonQuery();
        }

        public void Purge<T>(IEnumerable<T> entities) where T : Entity
        {
            if (!CanSave<T>()) return;

            // Casting to IList so that we can use it multiple times.
            entities = entities.AsIList();

            if (!entities.Any())
                return;

            using var access = GetWriteAccess();
            using var transaction = access.BeginTransaction();
            try
            {
                using (var command = access.CreateCommand())
                {
                    foreach (var entity in entities)
                    {
                        PrepareDelete(command, entity);
                        var rows = command.ExecuteNonQuery();
                    }
                }

                transaction.Commit();
            }
            catch (Exception)
            {
                transaction.Rollback();
                throw;
            }
        }

        private const int deleteBatchSize = 100;

        private readonly MethodInfo _deleteEntitiesMethod = typeof(SQLiteProvider).GetMethods(BindingFlags.NonPublic | BindingFlags.Instance)
            .Single(x => x.Name == nameof(DeleteEntity) && x.IsGenericMethod);
        private void DeleteEntity<T>(Guid[] parentIDs, List<string> parentFields, DeletionData deletionData) where T : Entity, new()
        {
            var columns = DeletionData.DeletionColumns<T>();

            var entityIDs = new List<Guid>();

            for (int i = 0; i < parentIDs.Length; i += deleteBatchSize)
            {
                var items = new ArraySegment<Guid>(parentIDs, i, Math.Min(deleteBatchSize, parentIDs.Length - i));
                var ids = items.ToArray();

                var filter = new Filters<T>();
                foreach(var field in parentFields)
                {
                    filter.Add(new Filter<T>(field).InList(ids));
                }

                var entities = Query(filter.CombineOr(), columns);
                foreach (var row in entities.Rows)
                {
                    deletionData.DeleteEntity<T>(row);
                    entityIDs.Add(row.Get<T, Guid>(x => x.ID));
                }
            }
            CascadeDelete(typeof(T), entityIDs.ToArray(), deletionData);
        }
       
        private void DeleteEntity(Type T, Guid[] parentIDs, List<string> parentFields, DeletionData deletionData)
        {
            _deleteEntitiesMethod.MakeGenericMethod(T).Invoke(this, new object?[] { parentIDs, parentFields, deletionData });
        }

        private readonly MethodInfo _setNullEntityMethod = typeof(SQLiteProvider).GetMethods(BindingFlags.NonPublic | BindingFlags.Instance)
            .Single(x => x.Name == nameof(SetNullEntity) && x.IsGenericMethod);
        private void SetNullEntity<T>(List<string> properties, Guid[] parentIDs, DeletionData deletionData) where T : Entity, new()
        {
            foreach(var property in properties)
            {
                var columns = Columns.None<T>().Add(x => x.ID).Add(property);

                for(int i = 0; i < parentIDs.Length; i += deleteBatchSize)
                {
                    var items = new ArraySegment<Guid>(parentIDs, i, Math.Min(deleteBatchSize, parentIDs.Length - i));
                    var entities = Query(new Filter<T>(property).InList(items.ToArray()), columns);
                    foreach (var row in entities.Rows)
                    {
                        deletionData.SetNullEntity<T>(row.Get<T, Guid>(x => x.ID), property, row.Get<Guid>(property));
                    }
                }
            }
        }

        private void SetNullEntity(Type T, List<string> properties, Guid[] parentIDs, DeletionData deletionData)
        {
            _setNullEntityMethod.MakeGenericMethod(T).Invoke(this, new object?[] { properties, parentIDs, deletionData });
        }

        private void CascadeDelete(Type type, Guid[] parentIDs, DeletionData deletionData)
        {
            if(parentIDs.Length == 0)
            {
                return;
            }
            if (Factory._cascades.TryGetValue(type, out var cascades))
            {
                foreach (var cascade in cascades)
                {
                    DeleteEntity(cascade.Item1, parentIDs, cascade.Item2, deletionData);
                }
            }
            if(Factory._setNulls.TryGetValue(type, out var setNulls))
            {
                foreach(var setNull in setNulls)
                {
                    SetNullEntity(setNull.Item1, setNull.Item2, parentIDs, deletionData);
                }
            }
        }

        public void Delete<T>(T entity, string userID) where T : Entity, new()
        {
            if (!CanSave<T>())
            {
                return;
            }
            if (typeof(T).HasAttribute<UnrecoverableAttribute>())
            {
                Purge(entity);

                var props = GetExternalProperties(typeof(T));
                foreach(var prop in props)
                {
                    DeleteExternalData(typeof(T), prop.Name, entity.ID);
                }
            }
            else
            {
                entity = DoQuery(
                    new Filter<T>(x => x.ID).IsEqualTo(entity.ID),
                    DeletionData.DeletionColumns<T>(),
                    null,
                    null,
                    false,
                    false
                ).Rows.First().ToObject<T>();

                var deletionData = new DeletionData();
                deletionData.DeleteEntity(entity);
                CascadeDelete(typeof(T), new Guid[] { entity.ID }, deletionData);

                var tableName = typeof(T).Name;
                var deletion = new Deletion()
                {
                    DeletionDate = DateTime.Now,
                    HeadTable = tableName,
                    Description = entity.ToString() ?? "",
                    DeletedBy = userID,
                    Data = Serialization.Serialize(deletionData)
                };
                OnSave(deletion);
                Purge(entity);
            }
        }

        public void Delete<T>(IEnumerable<T> entities, string userID) where T : Entity, new()
        {
            if (!CanSave<T>())
            {
                return;
            }
            entities = entities.AsIList();
            if (!entities.Any())
                return;

            if (typeof(T).HasAttribute<UnrecoverableAttribute>())
            {
                Purge(entities);

                var props = GetExternalProperties(typeof(T));
                foreach(var prop in props)
                {
                    foreach(var entity in entities)
                    {
                        DeleteExternalData(typeof(T), prop.Name, entity.ID);
                    }
                }
            }
            else
            {
                var ids = entities.Select(x => x.ID).ToArray();
                var entityList = Query(
                    new Filter<T>(x => x.ID).InList(ids),
                    DeletionData.DeletionColumns<T>()).Rows.Select(x => x.ToObject<T>()).ToList();
                if (!entityList.Any())
                    return;

                var deletionData = new DeletionData();
                foreach (var entity in entityList)
                {
                    deletionData.DeleteEntity(entity);
                }
                CascadeDelete(typeof(T), ids, deletionData);

                var tableName = typeof(T).Name;
                var deletion = new Deletion()
                {
                    DeletionDate = DateTime.Now,
                    HeadTable = tableName,
                    Description = $"Deleted {entityList.Count} entries",
                    DeletedBy = userID,
                    Data = Serialization.Serialize(deletionData)
                };
                OnSave(deletion);
                Purge(entities);
            }
        }

        private void AddDeletionType(Type type, List<Type> deletions)
        {
            deletions.Add(type);
            if (!Factory._cascades.TryGetValue(type, out var cascades))
                return;
            foreach (var cascade in cascades)
            {
                AddDeletionType(cascade.Item1, deletions);
            }
        }

        private Dictionary<Type, List<Type>> _allCascades = new();
        private List<Type> GetDeletionTypes(Type type)
        {
            if(_allCascades.TryGetValue(type, out var cascades))
            {
                return cascades;
            }
            var deletionTypes = new List<Type>();
            AddDeletionType(type, deletionTypes);
            _allCascades[type] = deletionTypes;
            return deletionTypes;
        }
        
        public void Purge(Deletion deletion)
        {
            if (!CanSave<Deletion>())
            {
                return;
            }

            var data = Serialization.Deserialize<DeletionData>(deletion.Data);
            if(data is not null)
            {
                foreach(var (entityName, cascade) in data.Cascades)
                {
                    if (!CoreUtils.TryGetEntity(entityName, out var entityType)) continue;

                    var props = GetExternalProperties(entityType);
                    foreach(var prop in props)
                    {
                        foreach(var entity in cascade.ToObjects(entityType).Cast<Entity>())
                        {
                            DeleteExternalData(entityType, prop.Name, entity.ID);
                        }
                    }
                }
            }

            Purge<Deletion>(deletion);
        }

        public void Recover(Deletion deletion)
        {
            if (!CanSave<Deletion>())
            {
                return;
            }

            if (deletion.ID == Guid.Empty)
            {
                Logger.Send(LogType.Error, "", "Empty Deletion ID; Recovery cancelled");
                return;
            }

            var data = Serialization.Deserialize<DeletionData>(deletion.Data);
            if (data is null)
            {
                Logger.Send(LogType.Error, "", "Deletion Data deserialisation failed; Recovery cancelled");
                return;
            }
            foreach (var (entityName, setNulls) in data.SetNulls)
            {
                if (!CoreUtils.TryGetEntity(entityName, out var entityType)) continue;

                var saves = new List<Entity>();

                foreach(var setNull in setNulls)
                {
                    var row = DoQueryNonGeneric(
                        entityType,
                        Filter.Create<Entity>(entityType, x => x.ID).IsEqualTo(setNull.EntityID),
                        Columns.None(entityType)
                            .Add<Entity>(x => x.ID)
                            .Add(setNull.Property),
                        null,
                        CoreRange.Database(1),
                        false,
                        false
                        ).Rows.FirstOrDefault();
                    if (row is null) continue;

                    var entity = (row.ToObject(entityType) as Entity)!;
                    CoreUtils.SetPropertyValue(entity, setNull.Property, setNull.ParentID);
                    saves.Add(entity);
                }

                OnSaveNonGeneric(entityType, saves);
            }
            foreach(var (entityName, cascade) in data.Cascades)
            {
                if (!CoreUtils.TryGetEntity(entityName, out var entityType)) continue;

                OnSaveNonGeneric(entityType, cascade.ToObjects(entityType).Cast<Entity>());
            }

            Purge<Deletion>(deletion);
        }

        public List<Type> GetDeletionTypes(Deletion deletion)
        {
            var entityType = CoreUtils.Entities.First(x => x.Name == deletion.HeadTable);
            return GetDeletionTypes(entityType);
        }

        #endregion

        #endregion

        #region External Data Storage

        private string ExternalDataFolder(Type T, string columnName, string idString)
        {
            return Path.Combine(
                Path.GetDirectoryName(Factory.URL) ?? "",
                $"{Path.GetFileName(Factory.URL)}.data",
                T.Name,
                columnName,
                idString.Substring(0, 2));
        }

        private byte[]? GetExternalData(Type T, string columnName, Guid id)
        {
            var idString = id.ToString();
            var filename = Path.Combine(ExternalDataFolder(T, columnName, idString), idString);
            try
            {
                return File.ReadAllBytes(filename);
            }
            catch(Exception e)
            {
                //Logger.Send(LogType.Error, "", $"Could not load external {T.Name}.{columnName}: {e.Message}");
                return null;
            }
        }
        private void SaveExternalData(Type T, string columnName, Guid id, byte[] data)
        {
            var idString = id.ToString();
            var directory = ExternalDataFolder(T, columnName, idString);
            Directory.CreateDirectory(directory);
            var filename = Path.Combine(directory, idString);
            File.WriteAllBytes(filename, data);
        }
        private void DeleteExternalData(Type T, string columnName, Guid id)
        {
            var idString = id.ToString();
            var directory = ExternalDataFolder(T, columnName, idString);
            Directory.CreateDirectory(directory);
            var filename = Path.Combine(directory, idString);
            if (File.Exists(filename))
            {
                File.Delete(filename);
            }
        }

        #endregion
    }
}