inabox/inabox.database.sqlite/SQLiteProvider.cs

using System.Collections;
using System.Data;
using System.Data.SQLite;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.Serialization.Formatters.Binary;
using System.Text;
using InABox.Core;


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

        public void Dispose()
        {
            if (Connection != null)
                Connection.Close();
        }

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

    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);
                }
                finally
                {
                    Monitor.Exit(writelock);
                }

            if (Connection == null)
                throw new Exception("Timeout while aqcuiring write lock!");
        }

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

    public class SQLiteProvider : IProvider
    {
        private static object writelock = new();

        private bool bForceRebuild;

        private bool RebuildTriggers = false;

        private SQLiteProvider()
        {
        }

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

        public string URL { get; set; }

        public event LogEvent OnLog;

        public Type[] Types { get; set; }

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


            using (var access = GetWriteAccess())
            {
                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())
            //                OnLog?.Invoke(LogType.Information,String.Format("{0}",reader.GetValue(0)));
            //        }
            //    }
            //}


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

            // 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.ContainsKey(table))
            {
                OnLog?.Invoke(LogType.Information, "Creating Table: " + typeof(CustomProperty).EntityName().Split('.').Last());
                using (var access = GetWriteAccess())
                {
                    CreateTable(access, typeof(CustomProperty), true, new CustomProperty[] { });
                }
            }
            else
            {
                using (var access = GetWriteAccess())
                {
                    CheckFields(access, typeof(CustomProperty), metadata[table].Item1, new CustomProperty[] { });
                }
            }

            var customproperties = 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))
                    {
                        OnLog?.Invoke(LogType.Information, "Creating Table: " + type.EntityName().Split('.').Last());
                        using (var access = GetWriteAccess())
                        {
                            CreateTable(access, type, true, customproperties);
                        }
                    }
                }
            }

            metadata = LoadMetaData();

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

            metadata = LoadMetaData();

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

                {
                    LoadDeletions(type);
                    table = type.EntityName().Split('.').Last();
                    using (var access = GetWriteAccess())
                    {
                        CheckTriggers(access, type, metadata[table].Item2);
                    }
                }
            }

            metadata = LoadMetaData();

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

                    table = type.EntityName().Split('.').Last();
                    using (var access = GetWriteAccess())
                    {
                        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))
                    {
                        OnLog?.Invoke(LogType.Information, "Creating Table: " + type.EntityName().Split('.').Last());
                        using (var access = GetWriteAccess())
                        {
                            CreateTable(access, type, true, customproperties);
                        }
                    }
                    else
                    {
                        using (var access = GetWriteAccess())
                        {
                            CheckFields(access, type, metadata[table].Item1, customproperties);
                        }
                    }
                }
            }

            if (bForceRebuild)
            {
                using (var access = GetWriteAccess())
                {
                    ExecuteSQL(access, "VACUUM;");
                }

                File.Delete(chkfile);
            }
        }

        public bool IsRelational()
        {
            return true;
        }

        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;
            OnLog?.Invoke(type, e.Statement + ";");
        }


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

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

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

            return result;
        }

        #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 = 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.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");
                    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.CanWrite);
            foreach (var property in properties)
                if (property.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)))
                {
                    var subprop = property.PropertyType.GetProperty("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;
        }

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

            // Get the EntityLink that is associated with this class
            var linkclass = CoreUtils.TypeList(
                new[] { type.Assembly },
                x => typeof(IEntityLink).GetTypeInfo().IsAssignableFrom(x) && x.GetInheritedGenericTypeArguments().FirstOrDefault() == type
            ).FirstOrDefault();

            // if The entitylink does not exist, we don't need to do anything
            if (linkclass == null)
                return result;

            var actions = new List<string>();
            var childtypes = Types.Where(x => /* (x != type) && */ x.IsSubclassOf(typeof(Entity)));
            foreach (var childtype in childtypes)
            {
                // Get all registererd types for this entitylink
                var fields = new List<string>();
                var bDelete = false;

                var tablename = childtype.EntityName().Split('.').Last();
                // Find any IEntityLink<> properties that refer back to this class
                var childprops = CoreUtils.PropertyList(childtype, x => x.PropertyType == linkclass);

                foreach (var childprop in childprops)
                {
                    var fieldname = string.Format("[{0}.ID]", childprop.Name);
                    var attr = childprop.GetCustomAttributes(typeof(EntityRelationshipAttribute), true).FirstOrDefault();
                    if (attr != null && ((EntityRelationshipAttribute)attr).Action.Equals(DeleteAction.Cascade))
                    {
                        fields.Clear();
                        bDelete = true;
                        fields.Add(fieldname);
                        break;
                    }

                    fields.Add(fieldname);

                    //actions[childtype] = String.Format("UPDATE {1} SET [{0}.ID] = NULL WHERE [id] = old.[ID];", tablename)
                }

                if (fields.Any())
                {
                    if (bDelete)
                        actions.Add(string.Format("DELETE FROM {0} WHERE {1} = old.ID;", tablename, fields.First()));
                    else
                        foreach (var field in fields)
                            actions.Add(string.Format("UPDATE {0} SET {1} = NULL WHERE {1} = old.ID;", tablename, field));
                }
            }

            if (actions.Any())
                result.Add(string.Format("CREATE TRIGGER {0}_BEFOREDELETE BEFORE DELETE ON {0} FOR EACH ROW BEGIN {1} END",
                    type.EntityName().Split('.').Last(), string.Join(" ", actions)));


            return result;
        }

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

            var customproperties = 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))
                    {
                        OnLog?.Invoke(LogType.Information, "Creating Table: " + type.EntityName().Split('.').Last());
                        using (var access = 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 = 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("Deleted"))
                result["Deleted"] = 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 ddl = new List<string>();
            AutoEntity view = type.GetCustomAttribute<AutoEntity>();
            if (view != null)
            {
                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='table' and name='" + type.EntityName().Split('.').Last() +
                                          "' and sql is not null;";
                    using (var reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                            while (reader.Read())
                                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 entity in union.Entities)
                    {
                        
                        var columns = Activator.CreateInstance(typeof(Columns<>).MakeGenericType(entity)) as IColumns;
                        var viewfields = new Dictionary<string, string>();
                        LoadFields(union.Definition, viewfields, null, new CustomProperty[] { });
                        foreach (var field in viewfields.Keys)
                            columns.Add(field);

                        var selectFnc = typeof(SQLiteProvider).GetMethod("PrepareSelect").MakeGenericMethod(entity);
                        var query = selectFnc.Invoke(this, new object[]
                        {
                            new SQLiteCommand(),
                            'A',
                            union.Filter(entity),
                            columns,
                            null,
                            null,
                            CheckDefaultColumns(union),
                            int.MaxValue,
                            union.Distinct,
                            false
                        }) as string;
                        
                        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("[Deleted]"))
                    //     fields.Add(String.Format("NULL as [Deleted]"));
                    // 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} WHERE [Deleted] is NULL",
                    //         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($"{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}.[Deleted] is NULL and {4}.[Deleted] is NULL and {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 selectFnc = typeof(SQLiteProvider).GetMethod("PrepareSelect").MakeGenericMethod(table.Type);
                        var subQueryText = selectFnc.Invoke(this, new object[]
                        {
                            new SQLiteCommand(),
                            'A',
                            table.Filter,
                            table.Columns,
                            null,
                            null,
                            null,
                            int.MaxValue,
                            cartesian.Distinct,
                            false
                        }) as string;
                        
                        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($"{EscapeValue(constant.Constant)} as [{constant.Mapping.Property}]");
                    
                    foreach (var constant in CheckDefaultColumns(cartesian))
                        fields.Add($"{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
            {
                ExecuteSQL(access, statement);
            }
            catch (Exception e)
            {
                OnLog?.Invoke(LogType.Error, "Unable to Create Table: " + e.Message);
                throw e;
            }
        }

        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)
            {
                OnLog?.Invoke(LogType.Information, "Recreating View: " + table);
                try
                {
                    String drop = "";
                    using (var command = access.CreateCommand())
                    {
                        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))
                        ExecuteSQL(access, string.Format("DROP {0} {1};", drop, table));

                    CreateTable(access, type, true, customproperties);

                }
                catch (Exception e)
                {
                    OnLog?.Invoke(LogType.Error, string.Format("RebuildTable({0}) [VIEW] failed: {1}\n{2}", table, e.Message, e.StackTrace));
                    throw e;
                }
            }
            else
            {
                
                OnLog?.Invoke(LogType.Information, "Rebuilding Table: " + table);
                try
                {
                    ExecuteSQL(access, "PRAGMA foreign_keys = off;");

                    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)
                            ExecuteSQL(access, drop);

                        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 + "]");

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

                        ExecuteSQL(access, string.Format("DROP TABLE _{0}_old;", table));

                        transaction.Commit();
                    }

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

        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 = null;
                    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);
                        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]))
                    ExecuteSQL(access, string.Format("DROP INDEX {0}", index));

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

        private void CheckTriggers(SQLiteWriteAccessor access, Type type, Dictionary<string, string> db_triggers)
        {
#if PURGE
            foreach (var trigger in db_triggers.Keys)
                ExecuteSQL(access, string.Format("DROP TRIGGER {0}", trigger));
#else
            var type_triggers = LoadTriggers(type);

            foreach (var trigger in db_triggers.Keys)
                if (!type_triggers.Contains(db_triggers[trigger]))
                    ExecuteSQL(access, string.Format("DROP TRIGGER {0}", trigger));
            foreach (var trigger in type_triggers)
                if (!db_triggers.ContainsValue(trigger))
                    ExecuteSQL(access, 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

        #region CRUD Operations

        private bool IsNull(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))
                {
                    var array = o as byte[];
                    if (o is byte[])
                        using (var ms = new MemoryStream((byte[])o))
                        {
                            var deser = new BinaryFormatter().Deserialize(ms);
                            return deser as string[];
                        }

                    return new string[] { };
                }

                return new string[] { };
            }

            if (type.GetInterfaces().Contains(typeof(IPackable)))
            {
                var packable = Activator.CreateInstance(type) as IPackable;
                if (!IsNull(o))
                {
                    var array = o as byte[];
                    if (o is byte[])
                        packable.Unpack(new FastBinaryReader((byte[])o));
                }

                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;
                if (double.TryParse(o.ToString(), out var hrs))
                    return TimeSpan.FromHours(hrs);
                if (o.ToString().Contains(":"))
                    return TimeSpan.Parse(o.ToString());
            }

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

            if (type == typeof(bool))
                return IsNull(o) ? false : o.ToString().Equals("1") || o.ToString().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 object Encode(object o, Type type)
        {
            if (IsNull(o))
                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(":"))
                {
                    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())
                {
                    new BinaryFormatter().Serialize(ms, o);
                    return ms.GetBuffer();
                }

            if (o is IPackable)
                using (var writer = new FastBinaryWriter())
                {
                    ((IPackable)o).Pack(writer);
                    return writer.GetBuffer();
                }

            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.AddDays(1))
                    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})" }
        };

        private String EscapeValue(object value)
        {
            if (IsNull(value))
                return "NULL";
            return (value is String) || (value is Enum)
                ? String.Format("\'"+"{0}"+"\'", value.ToString().Replace("\'", "\'\'"))
                : value is String[] 
                    ? string.Format("hex({0})", BitConverter.ToString(Encoding.ASCII.GetBytes(value.ToString())).Replace("-", string.Empty))
                    : value.ToString();

        }


        private String GetFilterConstant(FilterConstant constant)
        {
            switch (constant)
            {
                case FilterConstant.Now :
                    return "datetime()";
                case FilterConstant.Today :
                    return "date()";
            }
            throw new Exception($"FilterConstant.{constant} is not implemented!");
        }
        
        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
        {
            if (filter == null || filter.Expression == null)
                return "";

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

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

                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[])
                    {
                        var list = filter.Value as Guid[];
                        result = string.Format("(" + operators[filter.Operator] + ")", prop, string.Format("\"{0}\"", string.Join("\",\"", list)));
                    }
                    else if (filter.Value is IEnumerable)
                    {
                        var list = filter.Value as IEnumerable;
                        var paramlist = new List<object>();
                        foreach (var item in list)
                        {
                            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)
                {
                    var subQuery = filter.Value;

                    var subEntityType = subQuery.GetType().GenericTypeArguments[0];
                    var selectFnc = typeof(SQLiteProvider).GetMethod("PrepareSelect").MakeGenericMethod(subEntityType);

                    var columnsType = typeof(Columns<>).MakeGenericType(subEntityType);

                    var subColumns = Activator.CreateInstance(columnsType);
                    var subColumn = subQuery.GetType().GetProperty("Column").GetValue(subQuery);
                    var arr = Array.CreateInstance(subColumn.GetType(), 1);
                    arr.SetValue(subColumn, 0);

                    columnsType.GetProperty("Items").SetValue(subColumns, arr);

                    var subQueryText = selectFnc.Invoke(this, new[]
                    {
                        command,
                        'A',
                        subQuery.GetType().GetProperty("Filter").GetValue(subQuery),
                        subColumns,
                        null,
                        null,
                        null,
                        int.MaxValue,
                        false,
                        useparams
                    }) as string;

                    result = string.Format("(" + operators[filter.Operator] + ")", prop, subQueryText);
                }
                else
                {
                    if (filter.Value is FilterConstant)
                        result = string.Format("(" + operators[filter.Operator] + ")", prop, GetFilterConstant((FilterConstant)filter.Value));
                    else
                    {
                        var value = Encode(filter.Value, mexp.Type);
                        if (IsNull(value))
                        {
                            result = string.Format("({0} {1} NULL)", prop,
                                filter.Operator == Operator.IsEqualTo || filter.Operator == Operator.IsLessThan ||
                                filter.Operator == Operator.IsLessThanOrEqualTo
                                    ? "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("-", string.Empty);
                                    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));
                        }

                    }
                }
            }

            var bChanged = false;
            if (filter.Ands != null && filter.Ands.Count > 0)
            {
                foreach (var and in filter.Ands)
                {
                    var AndResult = GetFilterClause(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.Count > 0)
            {
                foreach (var or in filter.Ors)
                {
                    var OrResult = GetFilterClause(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 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) where T : Entity
        {
            if (sort == null)
                return "";

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

                var prop = CoreUtils.GetProperty(typeof(T), result);
                if (prop.GetCustomAttribute<DoNotSerialize>() == null && prop.GetCustomAttribute<DoNotPersist>() == null && prop.CanWrite)
                {
                    LoadFieldsandTables(command, typeof(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 + ", " + GetSortClause(command, then, prefix, tables, fieldmap, columns, useparams);

            return result;
        }

        private string GetCalculation(AggregateAttribute attribute, string columnname)
        {
            switch (attribute.Calculation)
            {
                case AggregateCalculation.Sum:
                    return "TOTAL";
                case AggregateCalculation.Count:
                    return "COUNT";
                case AggregateCalculation.Maximum:
                    return "MAX";
                case AggregateCalculation.Minimum:
                    return "MIN";
                case AggregateCalculation.Average:
                    return "AVERAGE";
            }

            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 (!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 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 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));
            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())
        //        OnLog?.Invoke(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();
                        var attr = prop.GetCustomAttributes().FirstOrDefault(x => x.GetType().Equals(typeof(AggregateAttribute)));
                        // LogStop("GetCustomAttributes(Aggregate)");

                        if (attr != null)
                        {
                            var agg = attr as AggregateAttribute;
                            
                            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 != null)
                                    {
                                        // LogStart();
                                        var sagg = sattr as AggregateAttribute;
                                        scols[sagg.Aggregate] = GetCalculation(sagg, sibling);
                                        // LogStop("GetCalculation(Sibling)");
                                    }
                                }

                                // LogStart();
                                var subcoltype = typeof(Columns<>).MakeGenericType(linkedtype);
                                // LogStop("Columns<>.MakeGnericType");

                                // LogStart();
                                var subcols = Activator.CreateInstance(subcoltype) as IColumns;
                                // LogStop("Columns<>.CreateInstance");


                                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 preparemethod = GetType().GetMethod("PrepareSelect").MakeGenericMethod(linkedtype);

                                var filter = Activator.CreateInstance(typeof(Filter<>).MakeGenericType(linkedtype), "Deleted") as IFilter;
                                filter!.Operator = Operator.IsEqualTo;
                                filter.Value = Guid.Empty;

                                var aggFilter = agg.Filter;
                                if(aggFilter is not null)
                                {
                                    var filterfields = this.GetType().GetMethod("FilterFields").MakeGenericMethod(linkedtype);
                                    List<String> ffs = new List<string>();
                                    filterfields.Invoke(this, new object[] { aggFilter, ffs });
                                    //foreach (var ff in ffs)
                                    //    subcols.Add(ff);
                                    filter.And(aggFilter);
                                }
                                
                                var linkedtable = string.Format("({0})",
                                    preparemethod.Invoke(this, new object[] { command, newprefix, filter, subcols, null, scols, null, int.MaxValue, 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
                        {
                            // LogStart();
                            attr = prop.GetCustomAttributes().FirstOrDefault(x => x.GetType().Equals(typeof(FormulaAttribute)));
                            // LogStop("GetAttribute(Formula)");

                            if (attr != null)
                            {
                                var fnc = attr as FormulaAttribute;
                                var functionmap = new Dictionary<string, string>();

                                // LogStart();
                                CheckColumn(columns, fnc.Value);
                                // LogStop("Formula.CheckColumn");

                                //// LogStart();
                                LoadFieldsandTables(command, type, prefix, functionmap, tables, columns, fnc.Value, useparams);
                                //// LogStop("Formula.LoadFieldsAndTables");

                                foreach (var column in fnc.Modifiers)
                                {
                                    // LogStart();
                                    CheckColumn(columns, column);
                                    // LogStop("Formula.Modifiers.CheckColumn");

                                    //// LogStart();
                                    LoadFieldsandTables(command, type, prefix, functionmap, tables, columns, column, useparams);
                                    //// LogStop("Formula.Modifiers.LoadFieldsAndTables");
                                }

                                fieldmap[columnname] = GetFunction(fnc, functionmap, columnname);
                            }
                            else
                            {
                                // LogStart();
                                attr = prop.GetCustomAttributes().FirstOrDefault(x => x.GetType().Equals(typeof(ConditionAttribute)));
                                // LogStop("GetAttribute(Condition)");

                                if (attr != null)
                                {
                                    var cnd = attr as ConditionAttribute;

                                    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.GetInterfaces().Contains(typeof(IEnclosedEntity)))
                        {
                            combinecount++;
                            prop = CoreUtils.GetProperty(type, string.Join(".", bits.Take(combinecount)));
                        }

                        if (prop.PropertyType.GetInterfaces().Contains(typeof(IEntityLink)))
                        {
                            var linkedtype = prop.PropertyType.GetInheritedGenericTypeArguments().First();
                            var remote = linkedtype.GetProperty(bits.Last());

                            // Are there any other properties for this link?  These will form the columns for our subquery
                            var siblings = columns.Where(x => x.Split('.').First().Equals(bits.First()))
                                .Select(x => string.Join(".", x.Split('.').Skip(combinecount))).ToList();
                            if (remote != null && !siblings.Contains(remote.Name))
                                siblings.Add(remote.Name);
                            
                            if (siblings.Count.Equals(1) && siblings.First().Equals("ID"))
                                fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
                            
                            // Hmmm.. there must have been a reason why we do this, but...
                            // Deleted is not part of parent tables (only ID is), so the same 
                            // shortcut that limits joining when we are only selecting ID's does
                            // not work if we are selecting Deleted's.
                            
                            //else if (siblings.Count.Equals(2) && siblings.Skip(1).First().Equals("Deleted"))
                            //    fieldmap[columnname] = string.Format("{0}1.[{1}]", prefix, columnname);
                            
                            else
                            {
                                if (!siblings.Contains("ID"))
                                    siblings.Insert(0, "ID");                                
                                if (!siblings.Contains("Deleted"))
                                    siblings.Insert(1, "Deleted");

                                // LogStart();
                                var subcoltype = typeof(Columns<>).MakeGenericType(linkedtype);
                                // LogStop("Columns<>.MakeGenericType");

                                // LogStart();
                                var subcols = Activator.CreateInstance(subcoltype) as IColumns;
                                // LogStop("Columns<>.CreateInstance");

                                foreach (var sibling in siblings)
                                    subcols.Add(sibling);


                                var preparemethod = GetType().GetMethod("PrepareSelect")!.MakeGenericMethod(linkedtype);

                                var filter = Activator.CreateInstance(typeof(Filter<>).MakeGenericType(linkedtype), "Deleted") as IFilter;
                                filter!.Operator = Operator.IsEqualTo;
                                filter.Value = Guid.Empty;


                                var linkedtable = string.Format("({0})",
                                    preparemethod.Invoke(this, new object?[] { command, newprefix, filter, subcols, null, null, null, int.MaxValue, 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<T>(Filter<T>? 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<T>(SortOrder<T>? 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 PrepareSelect<T>(SQLiteCommand command, char prefix, Filter<T>? filter, Columns<T>? columns, SortOrder<T>? sort,
            Dictionary<string, string>? aggregates, Dictionary<string, object>? constants, int top, bool distinct, bool useparams) where T : Entity
        {

            var fieldmap = new Dictionary<string, string>();
            
            var cols = CoreUtils.GetColumns(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 = "";
            
            tables.Add(new Tuple<string, string, string, string>(
                typeof(T).EntityName().Split('.').Last(),
                string.Format("{0}1", prefix),
                string.Format("{0} {1}1", typeof(T).EntityName().Split('.').Last(), prefix),
                "")
            );

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

            var parameters = new Dictionary<string, object>();
            
            condition = GetFilterClause(command, prefix, filter, tables, fieldmap, fields, useparams);
            sortorder = GetSortClause(command, sort, prefix, tables, fieldmap, fields, useparams);

            fields.Clear();
            foreach (var column in cols.Items)
                if (fieldmap.ContainsKey(column.Property))
                {
                    if (aggregates != null && aggregates.ContainsKey(column.Property))
                        fields.Add(string.Format("{0}({1}) as [{2}]", aggregates[column.Property], fieldmap[column.Property], column.Property));
                    else
                        fields.Add(string.Format("{0} as [{1}]", fieldmap[column.Property], column.Property));
                }

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

            var result = new List<string>();
            result.Add("SELECT");
            if (distinct)
                result.Add("DISTINCT");
            result.Add(string.Join(", ", fields));
            result.Add("FROM");
            result.AddRange(tables.Select(x => x.Item3));
            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.Items.Any(c => c.Property.Equals(x.Key)) && !aggregates.ContainsKey(x.Key)).Select(f => f.Value));

                if (!string.IsNullOrWhiteSpace(str))
                {
                    result.Add("GROUP BY");
                    result.Add(str);
                }
            }
            if (top != int.MaxValue)
                result.Add(String.Format("LIMIT {0}", top));

            //Breadcrumb.Remove(Breadcrumb.Last());

            return string.Join(" ", result);
        }

        private void PrepareUpsert<T>(SQLiteCommand command, T item, bool addDelete = false) where T : Entity
        {
            command.CommandText = "";
            command.Parameters.Clear();

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

            Dictionary<string, object> insert = item.GetValues(true);
            Dictionary<string, object> update = item.GetValues(false);
            if (addDelete)
            {
                insert.Add("Deleted", item.Deleted);
                if (item.HasOriginalValue("Deleted"))
                    update.Add("Deleted", item.Deleted);
            }

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

            var iParam = 0;
            foreach (var key in insert.Keys)
            {
                if (insert[key] is UserProperties)
                    continue;

                var sParam = string.Format("@p{0}", iParam++);
                object value = null;
                try
                {
                    value = Encode(insert[key], insert[key]?.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>();
            particles.Add("INSERT INTO");
            particles.Add(typeof(T).EntityName().Split('.').Last());
            particles.Add("(");
            particles.Add(string.Join(",", insertfields));
            particles.Add(")");
            particles.Add("VALUES");
            particles.Add("(");
            particles.Add(string.Join(",", insertvalues));
            particles.Add(")");
            if (updatecommands.Any())
                particles.Add("ON CONFLICT([ID]) DO UPDATE SET " + string.Join(", ", updatecommands));
            else
                particles.Add("ON CONFLICT DO NOTHING");
            command.CommandText = string.Join(" ", particles);
        }

        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 Schema Handling

        public Dictionary<string, Type> GetSchema()
        {
            var result = new Dictionary<string, Type>();
            return result;
        }


        public void CreateSchema(params Type[] types)
        {
        }

        public void SaveSchema(Dictionary<string, Type> schema)
        {
        }

        public void UpgradeSchema(params Type[] types)
        {
        }

        #endregion

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

        public IEnumerable<object[]> List<T>(Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null) where T : Entity, new()
        {
            var newFilter = new Filter<T>(x => x.Deleted).IsEqualTo(Guid.Empty);
            if (filter != null)
                newFilter.And(filter);
            filter = newFilter;

            //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 == null ? LookupFactory.DefineSort<T>() : sort;

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

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

            return result;
        }

        private CoreTable DoQuery<T>(Filter<T>? filter, Columns<T>? columns, SortOrder<T>? sort, int top, bool log, bool distinct)
            where T : Entity, new()
        {
            var start = DateTime.Now;
            //LogReset();

            //LogStart();
            var cols = CoreUtils.GetColumns(columns);
            //LogStop("GetColumns");

            var result = new CoreTable();
            result.TableName = typeof(T).EntityName();
            foreach (var col in cols.Items)
                result.Columns.Add(new CoreColumn { ColumnName = col.Property, DataType = col.ExpressionType() });
            //LogStop("MakeTable");

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

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

                    //LogStart();
                    String sql = PrepareSelect(command, 'A', filter, cols, sortorder, null, null, top, distinct, true) + ";";
   
                    command.CommandText = sql;

                    try
                    {
                        using (var reader = command.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                var rows = new List<CoreRow>();
                                while (reader.Read())
                                {
                                    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);
                                }
                            }

                            reader.Close();
                        }
                    }
                    catch (Exception e)
                    {
                        OnLog?.Invoke(LogType.Error, e.Message);
                    }
                    //LogStop("ReadData");

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

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

        public CoreTable Query<T>(Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null, int top = int.MaxValue, bool log = true, bool distinct = false) 
            where T : Entity, new()
        {
            var newFilter = new Filter<T>(x => x.Deleted).IsEqualTo(Guid.Empty);
            if (filter != null)
                newFilter.And(filter);

            return DoQuery(newFilter, columns, sort, top, log, distinct);
        }
        
        public CoreTable QueryDeleted<T>(Deletion deletion, Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null, int top = int.MaxValue)
            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, top, true, 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) where T : Entity, new()
        {
            var newFilter = new Filter<T>(x => x.Deleted).IsEqualTo(Guid.Empty);
            if (filter != null)
                newFilter.And(filter);
            filter = newFilter;

            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, int.MaxValue, 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 = null;
                                        if (cols.Items[i].Expression.Type == typeof(long) && !reader.IsDBNull(i))
                                            value = reader.GetInt64(i);
                                        else
                                            value = reader.GetValue(i);
                                        try
                                        {
                                            var decoded = Decode(value, cols.Items[i].Expression.Type);
                                            CoreUtils.SetPropertyValue(entity, cols.Items[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();
        }

        private void OnSave<T>(IEnumerable<T> entities, bool addDelete = false) where T : Entity
        {
            if (!entities.Any())
                return;

            using (var access = GetWriteAccess())
            {
                Exception? error = null;
                using (var transaction = access.BeginTransaction())
                {
                    try
                    {
                        using (var command = access.CreateCommand())
                        {
                            foreach (var entity in entities)
                            {
                                PrepareUpsert(command, entity, addDelete);
                                command.ExecuteNonQuery();
                            }

                            transaction.Commit();
                        }
                    }
                    catch (Exception e)
                    {
                        error = e;
                        transaction.Rollback();
                    }
                }

                if (error != null)
                    throw error;
            }
        }
        
        public static bool CanSave<T>()
        {
            if (DbFactory.IsReadOnly)
            {
                if (typeof(T).IsAssignableTo(typeof(License)))
                {
                    return true;
                }
                DbFactory.LogReadOnly();
                return false;
            }
            return true;
        }

        public void Save<T>(IEnumerable<T> entities) where T : Entity
        {
            if (!CanSave<T>())
            {
                return;
            }
            OnSave(entities, false);
        }

        private void OnSave<T>(T entity, bool addDelete = false) where T : Entity
        {
            Exception? error = null;
            using (var access = GetWriteAccess())
            {
                using (var command = access.CreateCommand())
                {
                    try
                    {
                        PrepareUpsert(command, entity, addDelete);
                        command.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        error = e;
                    }
                }
            }

            if (error != null)
                throw error;
        }

        public void Save<T>(T entity) where T : Entity
        {
            if (!CanSave<T>())
            {
                return;
            }
            OnSave(entity, false);
        }
        
        public void Purge<T>(T entity) where T : Entity
        {
            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 (!entities.Any())
                return;

            Exception? error = null;
            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 e)
                    {
                        transaction.Rollback();
                        error = e;
                    }
                }
            }

            if (error != null)
                throw error;
        }

        private Dictionary<Type, List<Tuple<Type, string>>> _cascades = new();
        private Dictionary<Type, List<Tuple<Type, List<string>>>> _setNulls = new();
        private void LoadDeletions(Type type)
        {
            // Get the EntityLink that is associated with this class
            var linkclass = CoreUtils.TypeList(
                new[] { type.Assembly },
                x => typeof(IEntityLink).GetTypeInfo().IsAssignableFrom(x) && x.GetInheritedGenericTypeArguments().FirstOrDefault() == type
            ).FirstOrDefault();

            // if The entitylink does not exist, we don't need to do anything
            if (linkclass == null)
                return;

            var cascades = new List<Tuple<Type, string>>();
            var setNulls = new List<Tuple<Type, List<string>>>();

            var childtypes = Types.Where(x => x.IsSubclassOf(typeof(Entity)));
            foreach (var childtype in childtypes)
            {
                // Get all registered types for this entitylink
                var fields = new List<string>();
                var bDelete = false;

                // Find any IEntityLink<> properties that refer back to this class
                var childprops = CoreUtils.PropertyList(childtype, x => x.PropertyType == linkclass);

                foreach (var childprop in childprops)
                {
                    var fieldname = string.Format("{0}.ID", childprop.Name);
                    var attr = childprop.GetCustomAttributes(typeof(EntityRelationshipAttribute), true).FirstOrDefault();
                    if (attr != null && ((EntityRelationshipAttribute)attr).Action.Equals(DeleteAction.Cascade))
                    {
                        cascades.Add(new(childtype, fieldname));
                        bDelete = true;
                        break;
                    }

                    fields.Add(fieldname);
                }
                if(!bDelete && fields.Any())
                {
                    setNulls.Add(new(childtype, fields));
                }
            }

            if(cascades.Count > 0)
            {
                _cascades[type] = cascades;
            }
            if(setNulls.Count > 0)
            {
                _setNulls[type] = setNulls;
            }
        }

        private MethodInfo _deleteEntitiesMethod = typeof(SQLiteProvider).GetMethods(BindingFlags.NonPublic | BindingFlags.Instance)
            .Single(x => x.Name == nameof(DeleteEntity) && x.IsGenericMethod);
        private void DeleteEntity<T>(Guid parentID, string parentField, Deletion deletion) where T : Entity, new()
        {
            var entities = Query(new Filter<T>(parentField).IsEqualTo(parentID), new Columns<T>(x => x.ID))
                .Rows.Select(x => x.ToObject<T>()).ToList();
            foreach(var entity in entities)
            {
                entity.Deleted = deletion.ID;
            }
            OnSave(entities, true);
            foreach (var entity in entities)
            {
                CascadeDelete(typeof(T), entity.ID, deletion);
            }
        }
       
        private void DeleteEntity(Type T, Guid parentID, string parentField, Deletion deletion)
        {
            _deleteEntitiesMethod.MakeGenericMethod(T).Invoke(this, new object?[] { parentID, parentField, deletion });
        }

        private void CascadeDelete(Type type, Guid parentID, Deletion deletion)
        {
            if (!_cascades.TryGetValue(type, out var cascades))
                return;
            foreach(var cascade in cascades)
            {
                DeleteEntity(cascade.Item1, parentID, cascade.Item2, deletion);
            }
        }

        public void Delete<T>(T entity, string userID) where T : Entity, new()
        {
#if PURGE
            if (!CanSave<T>())
            {
                return;
            }
            entity = DoQuery(
                new Filter<T>(x => x.ID).IsEqualTo(entity.ID),
                null,
                null,
                int.MaxValue,
                true,
                false
            ).Rows.First().ToObject<T>();

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

            entity.Deleted = deletion.ID;
            OnSave(entity, true);
            CascadeDelete(typeof(T), entity.ID, deletion);
#else
            Purge(entity);
#endif
        }

        public void Delete<T>(IEnumerable<T> entities, string userID) where T : Entity, new()
        {
#if PURGE
            if (!CanSave<T>())
            {
                return;
            }
            if (!entities.Any())
                return;
            var ids = entities.Select(x => x.ID).ToArray();
            var entityList = Query(new Filter<T>(x => x.ID).InList(ids)).Rows.Select(x => x.ToObject<T>()).ToList();
            if (!entityList.Any())
                return;

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

            foreach(var entity in entityList)
            {
                entity.Deleted = deletion.ID;
                OnSave(entity, true);
                CascadeDelete(typeof(T), entity.ID, deletion);
            }
#else
            Purge(entities);
#endif
        }

        private void AddDeletionType(Type type, List<Type> deletions)
        {
            deletions.Add(type);
            if (!_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;
        }

        private void DoSetNull<TChild>(string field, Guid[] parentIDs) where TChild : Entity, new()
        {
            var columns = new Columns<TChild>(x => x.ID);
            columns.Add(field);
            var children = DoQuery(
                new Filter<TChild>(field).InList(parentIDs),
                columns,
                null,
                int.MaxValue,
                true,
                false
            ).Rows.Select(x => x.ToObject<TChild>()).ToList();
            foreach(var child in children)
            {
                CoreUtils.SetPropertyValue(child, field, Guid.Empty);
            }
            OnSave(children);
        }

        private void PurgeEntityType<T>(Deletion deletion) where T : Entity, new()
        {
            var entities = QueryDeleted(deletion, null, new Columns<T>(x => x.ID)).Rows.Select(x => x.ToObject<T>()).ToList();
            if (_setNulls.TryGetValue(typeof(T), out var setNulls))
            {
                var ids = entities.Select(x => x.ID).ToArray();
                var setNullMethod = typeof(SQLiteProvider).GetMethod(nameof(DoSetNull), BindingFlags.NonPublic | BindingFlags.Instance)!;
                foreach (var (childType, childFields) in setNulls)
                {
                    var method = setNullMethod.MakeGenericMethod(childType);
                    foreach(var childField in childFields)
                    {
                        method.Invoke(this, new object[] { childField, ids });
                    }
                }
            }
            Purge(entities);
        }

        private void RecoverEntityType<T>(Deletion deletion) where T : Entity, new()
        {
            var entities = QueryDeleted(deletion, null, new Columns<T>(x => x.ID, x => x.Deleted)).Rows.Select(x => x.ToObject<T>()).ToList();
            foreach (var entity in entities)
            {
                entity.Deleted = Guid.Empty;
            }
            OnSave(entities, true);
        }
        
        public void Purge(Deletion deletion)
        {
            if(deletion.ID == Guid.Empty)
            {
                Logger.Send(LogType.Error, "", "Empty Deletion ID; Purge cancelled");
                return;
            }

            var entityType = CoreUtils.Entities.FirstOrDefault(x => x.Name == deletion.HeadTable);
            var deletionTypes = GetDeletionTypes(entityType);

            var purgeMethod = typeof(SQLiteProvider).GetMethod(nameof(PurgeEntityType), BindingFlags.NonPublic | BindingFlags.Instance)!;
            foreach(var type in deletionTypes)
            {
                purgeMethod.MakeGenericMethod(type).Invoke(this, new object[] { deletion });
            }

            Purge<Deletion>(deletion);
        }

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

            var entityType = CoreUtils.Entities.FirstOrDefault(x => x.Name == deletion.HeadTable);
            var deletionTypes = GetDeletionTypes(entityType);

            var recoveryMethod = typeof(SQLiteProvider).GetMethod(nameof(RecoverEntityType), BindingFlags.NonPublic | BindingFlags.Instance)!;
            foreach (var type in deletionTypes)
            {
                recoveryMethod.MakeGenericMethod(type).Invoke(this, new object[] { deletion });
            }

            Purge<Deletion>(deletion);
        }

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

        #endregion
        
    }
}