inabox/inabox.database.sqlite/SQLiteProvider.cs

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

namespace InABox.Database.SQLite;

internal abstract class SQLiteAccessor : IDisposable
{
    public SQLiteConnection Connection { get; private set; }

    protected static int nConnections = 0;

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

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

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

        Connection = new SQLiteConnection(conn);
        Connection.BusyTimeout = Convert.ToInt32(TimeSpan.FromMinutes(2).TotalMilliseconds);

        Connection.Open();

        Connection.SetLimitOption(SQLiteLimitOpsEnum.SQLITE_LIMIT_VARIABLE_NUMBER, 10000);
        ++nConnections;
    }
}

#region Custom Decimal Functions

[SQLiteFunction(Name = "DECIMAL_SUM", Arguments = 1, FuncType = FunctionType.Aggregate)]
public class SQLiteDecimalSum : SQLiteFunction
{
    public override void Step(object[] args, int stepNumber, ref object contextData)
    {
        if (args.Length < 1 || args[0] == DBNull.Value)
            return;
        decimal d = Convert.ToDecimal(args[0]);
        if (contextData != null) d += (decimal)contextData;
        contextData = d;
    }

    public override object Final(object contextData)
    {
        return contextData;
    }
}

[SQLiteFunction(Name = "DECIMAL_ADD", Arguments = -1, FuncType = FunctionType.Scalar)]
public class SQLiteDecimalAdd : SQLiteFunction
{
    public override object? Invoke(object[] args)
    {
        var result = 0.0M;
        for(int i = 0; i < args.Length; ++i)
        {
            var arg = args[i];
            if(arg == DBNull.Value)
            {
                return null;
            }
            else
            {
                result += Convert.ToDecimal(arg);
            }
        }
        return result;
    }
}
[SQLiteFunction(Name = "DECIMAL_SUB", Arguments = -1, FuncType = FunctionType.Scalar)]
public class SQLiteDecimalSub : SQLiteFunction
{
    public override object? Invoke(object[] args)
    {
        if(args.Length == 0)
        {
            return 0.0M;
        }
        else if(args.Length == 1)
        {
            if (args[0] == DBNull.Value)
            {
                return null;
            }
            else
            {
                return -Convert.ToDecimal(args[0]);
            }
        }
        else
        {
            if (args[0] == DBNull.Value)
            {
                return null;
            }
            var result = Convert.ToDecimal(args[0]);
            foreach(var arg in args.Skip(1))
            {
                if(arg == DBNull.Value)
                {
                    return null;
                }
                result -= Convert.ToDecimal(arg);
            }
            return result;
        }
    }
}
[SQLiteFunction(Name = "DECIMAL_MUL", Arguments = -1, FuncType = FunctionType.Scalar)]
public class SQLiteDecimalMult : SQLiteFunction
{
    public override object? Invoke(object[] args)
    {
        var result = 1.0M;
        foreach(var arg in args)
        {
            if(arg == DBNull.Value)
            {
                return null;
            }
            result *= Convert.ToDecimal(arg);
        }
        return result;
    }
}
[SQLiteFunction(Name = "DECIMAL_DIV", Arguments = -1, FuncType = FunctionType.Scalar)]
public class SQLiteDecimalDiv : SQLiteFunction
{
    public override object? Invoke(object[] args)
    {
        if(args.Length == 0)
        {
            return 1.0M;
        }
        else if(args.Length == 1)
        {
            if (args[0] == DBNull.Value)
            {
                return null;
            }
            else
            {
                var denom = Convert.ToDecimal(args[0]);
                if(denom == 0M)
                {
                    return new Exception("Attempt to divide by zero.");
                }
                return 1.0M / denom;
            }
        }
        else
        {
            if (args[0] == DBNull.Value)
            {
                return null;
            }
            var result = Convert.ToDecimal(args[0]);
            foreach(var arg in args.Skip(1))
            {
                if(arg == DBNull.Value)
                {
                    return null;
                }
                var denom = Convert.ToDecimal(arg);
                if(denom == 0M)
                {
                    return new Exception("Attempt to divide by zero.");
                }
                result /= denom;
            }
            return result;
        }
    }
}

#endregion

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

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

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

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

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

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

    private bool bForceRebuild;

    private bool RebuildTriggers = false;

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

    public string URL { get; set; }

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

    private SQLiteProvider MainProvider;

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

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

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

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

        MainProvider = NewProvider(Logger.Main);

        using var access = MainProvider.GetWriteAccess();

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

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


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

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

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

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

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

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

        metadata = LoadMetaData();

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

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

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

        metadata = LoadMetaData();

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

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

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

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

            File.Delete(chkfile);
        }
    }

    public bool IsRelational()
    {
        return true;
    }

    #region Database Structure Management

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

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

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

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

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

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

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

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

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

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

                            metadata[table] = tblinfo;
                        }

                    reader.Close();
                }

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

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

        return metadata;
    }

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

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

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

        if (type.IsEnum)
            return "TEXT";

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

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

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

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

        if (type.IsFloatingPoint() || type == typeof(decimal))
            return "NUM";

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

        return "TEXT";
    }

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

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

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

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

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

        return result;
    }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

                CreateTable(access, type, true, customproperties);

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

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

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

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

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

                    CreateTable(access, type, true, customproperties);

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

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

                    transaction.Commit();
                }

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

    #endregion

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

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

    private SQLiteProviderFactory Factory;

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


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

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

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

        return result;
    }

    #region CRUD Operations

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

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

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

                return Array.Empty<string>();
            }

            return Array.Empty<string>();
        }

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

            return packable;
        }

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

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

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

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

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

        if (IsNull(o))
            return null;

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

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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

        return o;
    }

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

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

    }
    
    internal static string EscapeValue(string? value, Type type)
    {

        if (type == typeof(string) || type.IsEnum || (type == typeof(Guid)))
            return "\'" + $"{value?.Replace("\'", "\'\'")}" + "\'";
        if (type == typeof(string[]))
            return string.Format("hex({0})", BitConverter.ToString(Encoding.ASCII.GetBytes(value.ToString() ?? "")).Replace("-", ""));
        if (type.GetInterface(nameof(IColumn)) != null)
            return $"[{value}]";
        return value.ToString() ?? "";

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

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

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

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

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

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

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

            if (filter.Operator == Operator.InList || filter.Operator == Operator.NotInList)
            {
                if(filter.Value is Array arr && arr.Length == 0)
                {
                    result = filter.Operator == Operator.InList ? "1 = 0" : "1 = 1";
                }
                else
                {
                    // if, and only if the list contains Guids, we can safely bypass the 
                    // 1000-parameter limit by using building the string ourselves
                    if (filter.Value is Guid[] list)
                    {
                        result = string.Format("(" + operators[filter.Operator] + ")",
                            prop,
                            string.Join(',', list.Where(x => x != Guid.Empty).Select(x => $"'{x}'")));
                        if (list.Contains(Guid.Empty))
                        {
                            result = $"({result} or ({prop} IS NULL))";
                        }
                    }
                    else if (filter.Value is IEnumerable enumerable)
                    {
                        var paramlist = new List<object>();
                        foreach (var item in enumerable)
                        {
                            var sParam = string.Format("@p{0}", command.Parameters.Count);
                            command.Parameters.AddWithValue(sParam, Encode(item, mexp.Type));
                            paramlist.Add(sParam);
                        }

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

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

                    subColumns.Add(subColumn);

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

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

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

                }
            }

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

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

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

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

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

        return result;
    }

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

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

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

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

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

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

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

        return result;
    }

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

    private static string GetCalculation(AggregateCalculation calculation, string columnname, Type TResult)
    {
        return calculation switch
        {
            AggregateCalculation.Sum => TResult == typeof(decimal) ? "DECIMAL_SUM" : "SUM",
            AggregateCalculation.Count => "COUNT",
            AggregateCalculation.Maximum => "MAX",
            AggregateCalculation.Minimum => "MIN",
            AggregateCalculation.Average => "AVERAGE",
            AggregateCalculation.Concat => "GROUP_CONCAT",
            _ => throw new Exception(string.Format("{0}.{1} is not a valid aggregate", columnname, calculation)),
        };
    }

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

// Crashes when value is a constant, and therefore not a field in the table        
//        if (!fieldmap.ContainsKey(attribute.Value))
//            throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.Value));
  
// Crashes when modifier is a constant, and therefore not a field in the table        
//        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.TryGetValue(attribute.Value, out var _value) ? _value : attribute.Value,
                string.Join(" + ", attribute.Modifiers.Select(x => fieldmap.TryGetValue(x, out var _value) ? $"IFNULL({_value},0.00)" : $"{x}")));

        if (attribute.Operator == FormulaOperator.Subtract)
            return string.Format("(IFNULL({0},0.00) - ({1}))", 
                fieldmap.TryGetValue(attribute.Value, out var _value) ? _value : attribute.Value,
                string.Join(" + ", attribute.Modifiers.Select(x => fieldmap.TryGetValue(x, out var _value) ? $"IFNULL({_value},0.00)" : $"{x}")));

        if (attribute.Operator == FormulaOperator.Multiply)
            return string.Format("(IFNULL({0},0.00) * {1})", 
                fieldmap.TryGetValue(attribute.Value, out var _value) ? _value : attribute.Value,
                string.Join(" * ", attribute.Modifiers.Select(x => fieldmap.TryGetValue(x, out var _value) ? $"IFNULL({_value},0.00)" : $"{x}")));

        if (attribute.Operator == FormulaOperator.Divide)
        {
            var result = string.Format("IFNULL({0},0.00)", fieldmap.TryGetValue(attribute.Value, out var _v) ? _v : attribute.Value);
            foreach (var modifier in attribute.Modifiers)
                result = string.Format("({0} / {1})", result, fieldmap.TryGetValue(modifier, out var _value) ? $"IFNULL({_value},1.00)" : $"{modifier}");
            return result;
        }

        if (attribute.Operator == FormulaOperator.Maximum)
        {
            var parameters = attribute.Modifiers.Select(m => fieldmap.TryGetValue(m, out var _value) ? $"IFNULL({_value},0.00)" : $"{m}");
            var primary = fieldmap.TryGetValue(attribute.Value, out var _v) ? _v : attribute.Value;
            var result = $"MAX({primary}, {String.Join(", ", parameters)})";
            return result;
        }

        if (attribute.Operator == FormulaOperator.Minumum)
        {
            var parameters = attribute.Modifiers.Select(m => fieldmap.TryGetValue(m, out var _value) ? $"IFNULL({_value},0.00)" : $"{m}");
            var primary = fieldmap.TryGetValue(attribute.Value, out var _v) ? _v : attribute.Value;
            var result = $"MIN({primary}, {String.Join(", ", parameters)})";
            return result;
        }

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

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

        var valuetype = intf.GenericTypeArguments[1];
        var defvalue = valuetype.GetDefault();
        
        var _left = fieldmap.TryGetValue(attribute.Left, out var _leftvalue)
            ? _leftvalue
            : EscapeValue(attribute.Left, valuetype);
        
        //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));
        var _right = fieldmap.TryGetValue(attribute.Right, out var _rightvalue)
            ? _rightvalue
            : EscapeValue(attribute.Right, valuetype);
        
        //if (!fieldmap.ContainsKey(attribute.True))
        //    throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.True));
        var _true = fieldmap.TryGetValue(attribute.True, out var _truevalue)
            ? _truevalue
            : EscapeValue(attribute.True, valuetype);

        //if (!fieldmap.ContainsKey(attribute.False))
        //    throw new Exception(string.Format("{0}.{1} -> {2} does not exist", columnname, attribute.GetType().Name, attribute.False));
        var _false = fieldmap.TryGetValue(attribute.False, out var _falsevalue)
            ? _falsevalue
            : EscapeValue(attribute.False, valuetype);

        return string.Format("CASE WHEN COALESCE({0},{1}) {2} {3} THEN {4} ELSE {5} END", _left, defvalue, condition,
            _right, _true, _false);
    }

    private string LoadComplexFormula(SQLiteCommand command, Type type, char prefix, Dictionary<string, string> fieldmap, List<Tuple<string, string, string, string>> tables, List<string> columns, IComplexFormulaNode node, bool useparams)
    {
        switch(node)
        {
            case IComplexFormulaAggregateNode agg:
                var expr = agg.GetExpression();
                var links = agg.GetLinks();

                var aggCol = "$AGGREGATE_0";

                var subCols = new List<IBaseColumn>
                {
                    expr.ToColumn(aggCol)
                };
                foreach (var key in links.Keys)
                    subCols.Add(Column.Create(agg.TAggregate, key));

                var aggregates = new Dictionary<string, string>
                {
                    { aggCol, GetCalculation(agg.GetCalculation(), aggCol, agg.TResult) }
                };

                var subquery = string.Format("({0})",
                    PrepareSelectNonGeneric(agg.TAggregate,
                        command,
                        (char)(prefix + 1),
                        agg.GetFilter(),
                        subCols,
                        sort: null,
                        aggregates,
                        null, null, false, useparams));

                var alias = tables.Count + 1;

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

                // Don't add the same table with equivalent SQL twice.
                var tuple = tables.FirstOrDefault(x =>
                    x.Item1.Equals(subquery) && x.Item4.Equals(linkList) && !x.Item2.Equals(string.Format("{0}1", prefix)));
                if (tuple == null)
                {
                    var joins = new List<string>();

                    foreach (var (key, link) in links)
                        joins.Add(string.Format("IFNULL({0}{1}.[{2}],'') = IFNULL({0}1.[{3}],'')", prefix, alias, key, link));

                    var join = string.Format("LEFT OUTER JOIN {0} {1}{2} ON {3}", subquery, prefix, alias,
                        string.Join(" AND ", joins));
                    tuple = new Tuple<string, string, string, string>(subquery, prefix + alias.ToString(), join, linkList);

                    tables.Add(tuple);
                }

                return string.Format("{0}.[{1}]", tuple.Item2, aggCol);
            case IComplexFormulaConstantNode constantNode:
                var constant = constantNode.GetConstant();
                if (constant is FilterConstant filterConstant)
                {
                    return GetFilterConstant(filterConstant);
                }
                else
                {
                    if (useparams)
                    {
                        var encoded = Encode(constant, type);
                        var sParam = string.Format("@p{0}", command.Parameters.Count);
                        command.Parameters.AddWithValue(sParam, encoded);

                        return sParam;
                    }
                    else
                    {
                        return EscapeValue(constant);
                    }
                }
            case IComplexFormulaFieldNode field:
                var col = Column.Create(type, field.GetField());
                CheckColumn(type, columns, col.Property);
                LoadFieldsAndTables(command, type, prefix, fieldmap, tables, columns, col, useparams);
                return fieldmap[col.Name];
            case IComplexFormulaFormulaNode formula:
                var operands = new List<string>();
                var op = formula.GetOperator();

                foreach (var field in formula.GetOperands())
                {
                    var operand = LoadComplexFormula(command, type, prefix, fieldmap, tables, columns, field, useparams);
                    if(op == FormulaOperator.Divide)
                    {
                        operands.Add($"IFNULL({operand}, 1.00)");
                    }
                    else
                    {
                        operands.Add($"IFNULL({operand}, 0.00)");
                    }
                }

                switch (op)
                {
                    case FormulaOperator.Add:
                        if(formula.TResult == typeof(decimal))
                        {
                            return $"DECIMAL_ADD({string.Join(',', operands)})";
                        }
                        else
                        {
                            if(operands.Count == 0)
                            {
                                return "0.00";
                            }
                            {
                                return $"({string.Join('+', operands)})";
                            }
                        }
                    case FormulaOperator.Subtract:
                        if (formula.TResult == typeof(decimal))
                        {
                            return $"DECIMAL_SUB({string.Join(',', operands)})";
                        }
                        else
                        {
                            if (operands.Count == 0)
                            {
                                return "0.00";
                            }
                            else if (operands.Count == 1)
                            {
                                return $"(-{operands[0]})";
                            }
                            else
                            {
                                return $"({string.Join('-', operands)})";
                            }
                        }
                    case FormulaOperator.Multiply:
                        if (formula.TResult == typeof(decimal))
                        {
                            return $"DECIMAL_MUL({string.Join(',', operands)})";
                        }
                        else
                        {
                            if (operands.Count == 0)
                            {
                                return "1.00";
                            }
                            else
                            {
                                return $"({string.Join('*', operands)})";
                            }
                        }
                    case FormulaOperator.Divide:
                        if (formula.TResult == typeof(decimal))
                        {
                            return $"DECIMAL_DIV({string.Join(',', operands)})";
                        }
                        else
                        {
                            if (operands.Count == 0)
                            {
                                return "1.00";
                            }
                            else if (operands.Count == 1)
                            {
                                return $"(1.00 / {operands[0]})";
                            }
                            else
                            {
                                return $"({string.Join('/', operands)})";
                            }
                        }
                    case FormulaOperator.Maximum:
                        return $"MAX({string.Join(',', operands)})";
                    case FormulaOperator.Minumum:
                        return $"MIN({string.Join(',', operands)})";
                    case FormulaOperator.None:
                    case FormulaOperator.Constant:
                    default:
                        throw new Exception($"Invalid formula of type {op}.");
                }
            case IComplexFormulaConditionNode condition:
                var left = LoadComplexFormula(command, type, prefix, fieldmap, tables, columns, condition.Left, useparams);
                var right = LoadComplexFormula(command, type, prefix, fieldmap, tables, columns, condition.Right, useparams);
                var trueVal = LoadComplexFormula(command, type, prefix, fieldmap, tables, columns, condition.True, useparams);
                var falseVal = LoadComplexFormula(command, type, prefix, fieldmap, tables, columns, condition.False, useparams);

                var conditionOp = condition.Condition switch
                {
                    Condition.Equals => "=",
                    Condition.NotEqual => "<>",
                    Condition.GreaterThan => ">",
                    Condition.GreaterThanOrEqualTo => ">=",
                    Condition.LessThan => "<",
                    Condition.LessThanOrEqualTo => "<=",
                    _ => throw new Exception($"{condition.Condition} is not a valid condition")
                };
                return $"(CASE WHEN COALESCE({left}, {condition.TCondition.GetDefault()}) {conditionOp} {right} THEN " +
                    $" {trueVal} ELSE {falseVal} END)";
            default:
                throw new Exception($"Unknown ComplexFormula type {node.GetType()}");
        }
    }
    
    private void LoadFieldsAndTables(SQLiteCommand command, Type type, char prefix, Dictionary<string, string> fieldmap,
        List<Tuple<string, string, string, string>> tables, List<string> columns, IBaseColumn baseCol, bool useparams)
    {
        if (fieldmap.ContainsKey(baseCol.Name))
            return;

        var newprefix = (char)(prefix + 1);
        switch (baseCol)
        {
            case IComplexColumn complexCol:
                fieldmap[baseCol.Name] = LoadComplexFormula(command, type, prefix, fieldmap, tables, columns, complexCol.Formula, false);
                break;
            case IColumn col:
                var property = DatabaseSchema.Property(type, col.Property);
                if(property is CustomProperty)
                {
                    fieldmap[baseCol.Name] = $"{prefix}1.[{col.Property}]";
                }
                else if(property is StandardProperty)
                {
                    if(property.Parent is null)
                    {
                        if(property.GetAttribute<ComplexFormulaAttribute>() is ComplexFormulaAttribute form)
                        {
                            var formula = form.Generator.GetFormula();
                            LoadFieldsAndTables(command, type, prefix, fieldmap, tables, columns, formula.ToColumn(baseCol.Name), useparams);
                        }
                        else if (property.GetAttribute<AggregateAttribute>() is AggregateAttribute agg)
                        {
                            bool internalaggregate = agg.Calculator.GetType().GetInterfaces()
                                .Any(x => x.IsGenericType && x.GetGenericTypeDefinition() == typeof(ICoreAggregate<,>));

                            if (!internalaggregate)
                            {
                                var scols = new Dictionary<string, string> { { agg.Aggregate, GetCalculation(agg.Calculation, baseCol.Name, baseCol.Type) } };

                                var linkedtype = agg.Source;
                                /*var siblings = columns.Where(x => !x.Equals(baseCol.Name) && x.Split('.').First().Equals(bits.First()))
                                    .Select(x => string.Join(".", x.Split('.').Skip(1))).ToList();
                                foreach (var sibling in siblings)
                                {
                                    var sprop = CoreUtils.GetProperty(type, sibling);
                                    var sattr = prop.GetCustomAttributes().FirstOrDefault(x => x.GetType().Equals(typeof(AggregateAttribute)));
                                    if (sattr is AggregateAttribute sagg)
                                    {
                                        scols[sagg.Aggregate] = GetCalculation(sagg, sibling);
                                    }
                                }*/

                                var subcols = Columns.None(linkedtype);

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

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

                                var aggFilter = agg.Filter;

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

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

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

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

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

                                    tables.Add(tuple);
                                }

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

                                //LoadFieldsandTables(command, agg.Type, newprefix, fieldmap, tables, columns, columnname);
                                //var linkedtable = String.Format("(SELECT [{3}],{0}([{1}]) as [{1}] from {2} group by [{3}])", GetCalculation(agg, columnname), agg.AggregateProperty, agg.Type.EntityName().Split('.').Last(), agg.LinkedProperty);
                                //int alias = tables.Count + 1;
                                //var tuple = new Tuple<String, String, String, String>(linkedtable, prefix + alias.ToString(), String.Format("LEFT OUTER JOIN {0} {1}{2} ON {1}{2}.[{3}] = {1}1.[ID]", linkedtable, prefix, alias, agg.LinkedProperty), "");
                                //tables.Add(tuple);
                                //fieldmap[columnname] = String.Format("{0}.[{1}]", tuple.Item2, agg.AggregateProperty);
                            }
                        }
                        else if (property.GetAttribute<FormulaAttribute>() is FormulaAttribute fnc)
                        {
                            foreach (var field in fnc.Modifiers.Prepend(fnc.Value))
                            {
                                if (CoreUtils.TryGetProperty(type,field,out var _))
                                {
                                    CheckColumn(type, columns, field);
                                    LoadFieldsAndTables(command, type, prefix, fieldmap, tables, columns,
                                        Column.Create(type, field), useparams);
                                }
                            }
                            fieldmap[baseCol.Name] = GetFunction(fnc, fieldmap, baseCol.Name);
                        }
                        else if(property.GetAttribute<ConditionAttribute>() is ConditionAttribute cnd)
                        {
                            var cndmap = new Dictionary<string, string>();
                            
                            if (CheckColumn(type, columns, cnd.Left))
                                LoadFieldsAndTables(command, type, prefix, cndmap, tables, columns, Column.Create(type, cnd.Left), useparams);
                                    
                            if (CheckColumn(type, columns, cnd.Right))
                                LoadFieldsAndTables(command, type, prefix, cndmap, tables, columns, Column.Create(type, cnd.Right), useparams);
                            
                            if (CheckColumn(type, columns, cnd.True))
                                LoadFieldsAndTables(command, type, prefix, cndmap, tables, columns, Column.Create(type, cnd.True), useparams);
                            
                            if (CheckColumn(type, columns, cnd.False))
                                LoadFieldsAndTables(command, type, prefix, cndmap, tables, columns, Column.Create(type, cnd.False), useparams);
                            
                            fieldmap[baseCol.Name] = GetCondition(cnd, cndmap, baseCol.Name);

                        }
                        else
                        {
                            fieldmap[baseCol.Name] = string.Format("{0}1.[{1}]", prefix, baseCol.Name);
                        }
                    }
                    else
                    {
                        var bits = property.Name.Split('.');
                        var prop = type.GetProperty(bits.First());
                        if (prop != null)
                        {
                            var combinecount = 1;
                            while (prop.PropertyType.HasInterface<IEnclosedEntity>())
                            {
                                combinecount++;
                                prop = CoreUtils.GetProperty(type, string.Join(".", bits.Take(combinecount)));
                            }

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

                                var remote = linkedType.GetProperty(remoteProperty);

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

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

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

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

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

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

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

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

                                    //if (bits.Last().Equals("ID"))
                                    //    fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
                                    //else
                                    fieldmap[baseCol.Name] = 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(baseCol.Name))
                                            fieldmap[subcol] = string.Format("{0}.[{1}]", tuple.Item2, sibling);
                                    }
                                }
                                else
                                {
                                    if (siblings.Count.Equals(1) && siblings.First().Equals("ID"))
                                    {
                                        fieldmap[baseCol.Name] = string.Format("{0}1.[{1}]", prefix, baseCol.Name);
                                    }
                                    else
                                    {
                                        if (!siblings.Contains("ID"))
                                            siblings.Insert(0, "ID");

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

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

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

                                        //if (bits.Last().Equals("ID"))
                                        //    fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
                                        //else
                                        fieldmap[baseCol.Name] = 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(baseCol.Name))
                                                fieldmap[subcol] = string.Format("{0}.[{1}]", tuple.Item2, sibling);
                                        }
                                    }
                                }
                            }
                            else if (prop.PropertyType.GetInterfaces().Contains(typeof(IEnclosedEntity)))
                            {
                                fieldmap[baseCol.Name] = string.Format("{0}1.[{1}]", prefix, baseCol.Name);
                            }
                            else
                            {
                                fieldmap[baseCol.Name] = string.Format("{0}1.[{1}]", prefix, baseCol.Name);
                            }
                        }
                        else
                        {
                            fieldmap[baseCol.Name] = baseCol.Name;
                        }
                    }
                }
                else
                {
                    //IProperty customprop = DataModel.Property(type, columnname);
                    //if (customprop != null)
                    //    fieldmap[columnname] = String.Format("{0}1.[{1}]", prefix, columnname);
                    //else
                    fieldmap[baseCol.Name] = baseCol.Name;
                }
                break;
        }
    }

    private static bool CheckColumn(Type type, List<string> columns, string column)
    {
        if (CoreUtils.TryGetProperty(type, column, out _) && !columns.Contains(column))
        {
            columns.Add(column);
            return true;
        }
        return false;
    }

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

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

    public enum AggregateType
    {
        None,
        Sum,
        Count
    }

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

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

        var cols = (columns ?? CoreUtils.GetColumns(T, null).Columns()).AsIList();

        // Contains every columns we need to load from the database, including for filters, columns and sortorders.
        var fields = new List<string>();

        fields.AddRange(cols.OfType<IColumn>().Select(x => x.Property));

        AddFilterFields(filter, fields);
        AddSortFields(sort, fields);

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

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

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

        foreach (var column in cols)
        {
            LoadFieldsAndTables(command, T, prefix, fieldmap, tables, fields, column, useparams);
        }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

            command.Parameters.AddWithValue(sParam, value);

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

            insertvalues.Add(sParam);

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

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

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

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

    #endregion

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

    #region Query

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

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

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

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

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

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

        return result;
    }

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

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

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

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

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

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

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

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

                command.CommandText = sql;

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

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

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

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

                                    result.Rows.Add(row);
                                }

                                iRow++;
                            }

                            result.TotalRecords = iRow;
                        }

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

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

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

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

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

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

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

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

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

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

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

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

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

        return result.ToArray();
    }

    #endregion

    #region Save

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

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

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

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

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

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

        if (!entities.Any())
            return;

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

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

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

            transaction.Commit();

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

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

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

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

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

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

    #endregion

    #region Delete

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

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

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

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

        if (!entities.Any())
            return;

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

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

    private const int deleteBatchSize = 100;

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

        var entityIDs = new List<Guid>();

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

        Purge<Deletion>(deletion);
    }

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

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

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

            var saves = new List<Entity>();

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

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

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

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

        Purge<Deletion>(deletion);
    }

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

    #endregion

    #endregion

    #region External Data Storage

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

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

    #endregion
}