using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using InABox.Clients;

namespace InABox.Core
{
    public delegate void DataModelUpdateEvent(string section, DataModel model);

    [AttributeUsage(AttributeTargets.Property, Inherited = true, AllowMultiple = false)]
    public sealed class DataModelTableNameAttribute : Attribute
    {
        public string TableName { get; set; }

        public DataModelTableNameAttribute(string tableName)
        {
            TableName = tableName;
        }
    }

    public interface IDataModelSource
    {
        string SectionName { get; }
        DataModel DataModel(Selection selection);

        event DataModelUpdateEvent? OnUpdateDataModel;
    }

    public interface IDataModelRelationship
    {
        string ChildColumn { get; }
        string ChildTable { get; }
        string ParentColumn { get; }
        string ParentTable { get; }

        bool IsLookup { get;  }

        DataRelation AsDataRelation();

        string ParentColumnAsPropertyName();
        string ChildColumnAsPropertyName();
    }

    public interface IDataModelQueryDef : IQueryDef
    {
        string TableName { get; }
    }

    public class DataModelQueryDef<T> : IDataModelQueryDef
    {
        public DataModelQueryDef(Filter<T> filter, Columns<T> columns, SortOrder<T> sortorder, CoreRange? range = null, string? alias = null)
        {
            Type = typeof(T);
            Filter = filter;
            Columns = columns;
            SortOrder = sortorder;
            TableName = DataModel.TableName(Type, alias);
            Range = range;
        }

        public Type Type { get; }

        public IFilter Filter { get; }

        public IColumns Columns { get; }

        public ISortOrder SortOrder { get; }
        
        public CoreRange? Range { get; set; }

        public string TableName { get; }
    }

    public delegate void OnBeforeLoad(CancelEventArgs args);

    public delegate void OnAfterLoad(CancelEventArgs args);

    public interface IDataModel
    {
        IEnumerable<DataTable> DefaultTables { get; }

        IEnumerable<KeyValuePair<string, DataModel.DataModelTable>> ModelTables { get; }

        void AddTable(string alias, CoreTable table, bool isdefault = false);

        void AddTable(Type type, CoreTable table, bool isdefault = false, string? alias = null);

        /// <summary>
        /// Adds a table to the datamodel.
        /// </summary>
        /// <typeparam name="TType">The type of the object represented by the table.</typeparam>
        /// <param name="filter">A filter for the table. If set to <see langword="null"/>, loads all objects of <typeparamref name="TType"/>.</param>
        /// <param name="columns">The columns to load for this table. If set to <see langword="null"/>, loads all the columns of <typeparamref name="TType"/>.</param>
        /// <param name="isdefault">
        /// Is this table default loaded? If set to <see langword="true"/>, this table is added to <see cref="DefaultTables"/>.</param>
        /// <param name="alias">The name of this table. Defaults to <typeparamref name="TType"/> if not set or <see langword="null"/>.</param>
        /// <param name="shouldLoad">
        /// <see langword="true"/> if this table should be loaded - in some cases a table's data should loaded manually.<br/>
        /// Set to <see langword="false"/> if this is the case.
        /// </param>
        void AddTable<TType>(Filter<TType>? filter, Columns<TType>? columns, bool isdefault = false, string? alias = null, bool shouldLoad = true);
        void LinkTable(Type parenttype, string parentcolumn, Type childtype, string childcolumn, string? parentalias = null, string? childalias = null, bool isLookup = false);
        void LinkTable(Type parenttype, string parentcolumn, string childalias, string childcolumn, string? parentalias = null, bool isLookup = false);

        void LinkTable<TParent, TChild>(Expression<Func<TParent, object>> parent, Expression<Func<TChild, object>> child, string? parentalias = null,
            string? childalias = null, bool isLookup = false);

        void AddChildTable<TParent, TChild>(Expression<Func<TParent, object>> parentcolumn, Expression<Func<TChild, object>> childcolumn,
            Filter<TChild>? filter = null, Columns<TChild>? columns = null, bool isdefault = false, string? parentalias = null,
            string? childalias = null);

        void AddLookupTable<TSource, TLookup>(Expression<Func<TSource, object>> sourcecolumn, Expression<Func<TLookup, object>> lookupcolumn,
            Filter<TLookup>? filter = null, Columns<TLookup>? columns = null, bool isdefault = false, string? sourcealias = null,
            string? lookupalias = null);

        /// <summary>
        /// Remove a table from the data model.
        /// </summary>
        /// <typeparam name="TType">The type of the table to be removed.</typeparam>
        /// <param name="alias">The table name, defaulting to the name of <typeparamref name="TType"/>.</param>
        /// <returns><see langword="true"/> if the table was removed, <see langword="false"/> if it was not removed because it didn't exist.</returns>
        bool RemoveTable<TType>(string? alias = null);

        /// <summary>
        /// Gets the filter for a given table, which is used during <see cref="LoadModel(IEnumerable{string}, IDataModelQueryDef[]).
        /// </summary>
        /// <typeparam name="TType">The type of the table to get the filter of.</typeparam>
        /// <param name="alias">The name of the table, defaulting to <typeparamref name="TType"/></param>
        /// <returns>The filter.</returns>
        Filter<TType>? GetFilter<TType>(string? alias = null);
        /// <summary>
        /// Gets the columns for a given table, which are used during <see cref="LoadModel(IEnumerable{string}, IDataModelQueryDef[]).
        /// </summary>
        /// <typeparam name="TType">The type of the table to get the columns of.</typeparam>
        /// <param name="alias">The name of the table, defaulting to <typeparamref name="TType"/></param>
        /// <returns>The columns.</returns>
        Columns<TType>? GetColumns<TType>(string? alias = null);

        /// <summary>
        /// Sets the filter for a given table, which is used during <see cref="LoadModel(IEnumerable{string}, IDataModelQueryDef[]).
        /// </summary>
        /// <typeparam name="TType">The type of the table to set the filter of.</typeparam>
        /// <param name="filter">The new filter.</param>
        /// <param name="alias">The name of the table, defaulting to <typeparamref name="TType"/></param>
        void SetFilter<TType>(Filter<TType>? filter, string? alias = null);
        /// <summary>
        /// Sets the columns for a given table, which are used during <see cref="LoadModel(IEnumerable{string}, IDataModelQueryDef[]).
        /// </summary>
        /// <typeparam name="TType">The type of the table.</typeparam>
        /// <param name="columns">The new columns.</param>
        /// <param name="alias">The name of the table, defaulting to <typeparamref name="TType"/>.</param>
        void SetColumns<TType>(Columns<TType>? columns, string? alias = null);
        void SetIsDefault<TType>(bool isDefault, string? alias = null);
        void SetShouldLoad<TType>(bool shouldLoad, string? alias = null);

        CoreTable GetTable<TType>(string? alias = null);
        void SetTableData(Type type, CoreTable tableData, string? alias = null);
        bool HasTable(Type type, string? alias = null);
        bool HasTable<TType>(string? alias = null);

        void LoadModel(IEnumerable<string>? requiredTables, Dictionary<string, IQueryDef>? requiredQueries = null);
        void LoadModel(IEnumerable<string>? requiredTables, params IDataModelQueryDef[] requiredQueries);

        /// <summary>
        /// Load the model, loading all tables that are set to be default. (See <see cref="SetIsDefault{TType}(bool, string?)"/>).
        /// </summary>
        void LoadModel();

        TType[] ExtractValues<TSource, TType>(Expression<Func<TSource, TType>> column, bool distinct = true, string? alias = null);
    }

    public interface IDataModel<T> : IDataModel
        where T : Entity, IRemotable, IPersistent, new()
    {
    }

    public abstract class DataModel : IDataModel
    {
        private readonly List<IDataModelRelationship> _relationships = new List<IDataModelRelationship>();

        private readonly Dictionary<string, DataModelTable> _tables = new Dictionary<string, DataModelTable>();

        public DataModel()
        {
            AddTable<CompanyInformation>(null, null, true);
            AddChildTable<CompanyInformation, Document>(x => x.Logo.ID, x => x.ID, null, null, true, null, "CompanyLogo");

            AddTable(new Filter<User>(x => x.ID).IsEqualTo(ClientFactory.UserGuid), null, true);
        }

        public IEnumerable<KeyValuePair<string, DataModelTable>> ModelTables => _tables;

        public abstract string Name { get; }

        public IEnumerable<DataRelation> Relations => _relationships.Select(x => x.AsDataRelation()).ToArray();
        public IEnumerable<DataTable> Tables => _tables.Select(x => x.Value.Table.ToDataTable(x.Key));
        public IEnumerable<DataTable> DefaultTables => _tables.Where(x => x.Value.IsDefault).Select(x => x.Value.Table.ToDataTable(x.Key));
        public IEnumerable<string> DefaultTableNames => _tables.Where(x => x.Value.IsDefault).Select(x => x.Key);
        public IEnumerable<string> TableNames => _tables.Select(x => x.Key);

        public TType[] ExtractValues<TSource, TType>(Expression<Func<TSource, TType>> column, bool distinct = true, string? alias = null)
        {
            return GetTable<TSource>(alias).ExtractValues(column, distinct).ToArray();
        }

        public DataSet AsDataSet()
        {
            var result = new DataSet();

            foreach(var (key, table) in _tables)
            {
                var current = table.Table.Columns.ToDictionary(x => x.ColumnName, x => x);

                IColumns? additional = null;
                if(table.Type != null)
                {
                    additional = Columns.None(table.Type);
                    foreach (var column in CoreUtils.GetColumnNames(table.Type, x => true))
                    {
                        if (!current.ContainsKey(column))
                        {
                            additional.Add(column);
                        }
                    }
                }

                var dataTable = table.Table.ToDataTable(key, additional);
                result.Tables.Add(dataTable);
            }

            //result.Tables.AddRange(_tables.Select(x => x.Value.Table.ToDataTable(x.Key)).ToArray());
            foreach (var relation in _relationships)
            {
                var childTable = result.Tables[relation.ChildTable];
                var parentTable = result.Tables[relation.ParentTable];

                if (childTable is null)
                {
                    continue;
                }
                if (parentTable is null)
                {
                    result.Tables.Remove(childTable);
                    continue;
                }

                var parentColumn = parentTable.Columns[relation.ParentColumn.Replace(".", "_")];
                var childColumn = childTable.Columns[relation.ChildColumn.Replace(".", "_")];
                if (parentColumn is null || childColumn is null)
                {
                    result.Tables.Remove(childTable);
                    continue;
                }

                if (relation.IsLookup)
                {
                    result.Relations.Add(
                        string.Format("{0}_{1}", relation.ChildTable, relation.ParentTable),
                        childColumn,
                        parentColumn,
                        false
                    );
                }
                else
                {
                    result.Relations.Add(
                        string.Format("{0}_{1}", relation.ParentTable, relation.ChildTable),
                        parentColumn,
                        childColumn,
                        false
                    );
                }
            }
            return result;
        }

        public event OnBeforeLoad? OnBeforeLoad;
        public event OnAfterLoad? OnAfterLoad;

        protected virtual void BeforeLoad(IEnumerable<string> requiredtables)
        {
        }

        protected virtual void CheckRequiredTables(List<string> requiredtables)
        {
        }

        //protected abstract void Load(IEnumerable<string> requiredtables);

        protected virtual void AfterLoad(IEnumerable<string> requiredTables)
        {
        }

        protected void Load(Type type, CoreTable data, IEnumerable<string> requiredtables, string? alias = null)
        {
            CheckTable(type, alias);
            var name = TableName(type, alias);
            var table = _tables[name].Table;
            if(!ReferenceEquals(table, data))
            {
                table.Rows.Clear();
                if (IsRequired(type, requiredtables, alias))
                    data.CopyTo(table);
            }
        }

        protected void Load<TType>(CoreTable data, IEnumerable<string> requiredtables, string? alias = null)
        {
            Load(typeof(TType), data, requiredtables, alias);
        }

        protected void Load<TType>(IEnumerable<TType> items, IEnumerable<string> requiredtables, string? alias = null)
            where TType : notnull
        {
            CheckTable<TType>(alias);
            var name = TableName<TType>(alias);
            var table = _tables[name].Table;
            table.Rows.Clear();
            if (IsRequired<TType>(requiredtables))
                foreach (var item in items)
                {
                    table.LoadRow(item);
                }
        }

        private void CheckTable<TType>(string? alias = null)
        {
            CheckTable(typeof(TType), alias);
        }

        protected string TableName<TType>(string? alias = null)
        {
            return TableName(typeof(TType), alias);
        }

        public class DataModelTable
        {
            private bool shouldLoad;

            public DataModelTable(Type? type, CoreTable table, bool isDefault, IFilter? filter, IColumns? columns, bool shouldLoad = true)
            {
                Type = type;
                Table = table;
                IsDefault = isDefault;
                Filter = filter;
                Columns = columns;
                ShouldLoad = shouldLoad;
            }

            public Type? Type { get; }

            public CoreTable Table { get; set; }

            public bool IsDefault { get; set; }

            public IFilter? Filter { get; set; }

            public IColumns? Columns { get; set; }

            public bool ShouldLoad
            {
                get => shouldLoad && Type != null;
                set
                {
                    shouldLoad = value;
                }
            }
        }

        #region New Load Methods

        private Filter<TChild> GetSubquery<TParent, TChild>(IDataModelRelationship relation, Dictionary<string, IQueryDef> requiredQueries)
            where TParent : Entity, IRemotable, IPersistent, new()
            where TChild : Entity, IRemotable, IPersistent, new()
        {
            var parentFilter = GetTableFilter<TParent>(relation.ParentTable, requiredQueries);
            var subQuery = new SubQuery<TParent>(parentFilter, new Column<TParent>(relation.ParentColumnAsPropertyName()));

            var filter = new Filter<TChild>();
            filter.Expression = CoreUtils.CreateMemberExpression(typeof(TChild), relation.ChildColumnAsPropertyName());
            filter.Operator = Operator.InQuery;
            filter.Value = subQuery;

            return filter;
        }

        private Filter<TType>? GetTableFilter<TType>(string tableName, Dictionary<string, IQueryDef> requiredQueries)
            where TType : Entity, IRemotable, IPersistent, new()
        {
            var newFilter = _tables[tableName].Filter as Filter<TType>;

            IQueryDef? query = null;
            requiredQueries?.TryGetValue(tableName, out query);
            if (query?.Filter is Filter<TType> filter)
            {
                if (newFilter != null)
                    newFilter.And(filter);
                else
                    newFilter = filter;
            }

            var relation = _relationships.Where(x => x.ChildTable == tableName).FirstOrDefault();
            if (relation != null)
            {
                var table = _tables[relation.ParentTable];

                if(table.Type != null)
                {
                    var subFilter = (typeof(DataModel).GetMethod(nameof(GetSubquery), BindingFlags.NonPublic | BindingFlags.Instance)
                    .MakeGenericMethod(_tables[relation.ParentTable].Type, typeof(TType))
                    .Invoke(this, new object?[] { relation, requiredQueries }) as Filter<TType>)!;

                    if (newFilter != null)
                        newFilter.And(subFilter);
                    else
                        newFilter = subFilter;
                }
            }

            return newFilter;
        }

        private IQueryDef LoadModelTable<TType>(string tableName, Dictionary<string, IQueryDef> requiredQueries)
            where TType : Entity, IRemotable, IPersistent, new()
        {
            var newFilter = GetTableFilter<TType>(tableName, requiredQueries);

            var newColumns = _tables[tableName].Columns as Columns<TType>;

            var newSort = LookupFactory.DefineSort<TType>();

            IQueryDef? query = null;
            requiredQueries?.TryGetValue(tableName, out query);

            if (query == null) return new QueryDef<TType>(newFilter, newColumns, newSort);

            if (query.Columns != null) newColumns = query.Columns as Columns<TType>;
            return new QueryDef<TType>(
                newFilter,
                newColumns,
                query.SortOrder as SortOrder<TType>
            );
        }

        public virtual void LoadModel(IEnumerable<string>? requiredTables, Dictionary<string, IQueryDef>? requiredQueries = null)
        {
            var requiredTablesList = requiredTables != null ? requiredTables.ToList() : new List<string>();
            CheckRequiredTables(requiredTablesList);

            var args = new CancelEventArgs();
            OnBeforeLoad?.Invoke(args);
            if (!args.Cancel) BeforeLoad(requiredTablesList);

            var queries = new Dictionary<string, IQueryDef>();
            var genericMethod = typeof(DataModel).GetMethods(BindingFlags.NonPublic | BindingFlags.Instance)
                .Where(x => x.Name == nameof(LoadModelTable) && x.IsGenericMethod)
                .FirstOrDefault()!;
            foreach (var table in _tables)
                if (table.Value.ShouldLoad)
                    if (requiredTables == null || requiredTablesList.Contains(table.Key))
                        queries[table.Key] = (genericMethod.MakeGenericMethod(table.Value.Type).Invoke(this, new object?[]
                        {
                            table.Key,
                            requiredQueries
                        }) as IQueryDef)!;

            var results = Client.QueryMultiple(queries);
            foreach (var result in results)
                if (_tables.TryGetValue(result.Key, out var table))
                    table.Table = result.Value;
                else
                    Logger.Send(LogType.Error, "",
                        string.Format("QueryMultiple returned table with key {0}, which is not in the data model!", result.Key));

            args = new CancelEventArgs();
            OnAfterLoad?.Invoke(args);
            if (!args.Cancel) AfterLoad(requiredTablesList);
        }

        public void LoadModel(IEnumerable<string>? requiredTables, params IDataModelQueryDef[] requiredQueries)
        {
            LoadModel(requiredTables, requiredQueries.ToDictionary(x => x.TableName, x => x as IQueryDef));
        }

        public void LoadModel()
        {
            LoadModel(DefaultTableNames);
        }

        #endregion

        #region Non-Generic Stuff

        public bool IsChildTable(string tableName)
        {
            return _relationships.Any(x => x.ChildTable == tableName);
        }

        public static string TableName(Type? type, string? alias = null)
        {
            return string.IsNullOrWhiteSpace(alias) ? (type ?? throw new Exception("No type or alias given!")).EntityName().Split('.').Last() : alias;
        }

        private void CheckTable(Type? type, string? alias = null)
        {
            var name = TableName(type, alias);
            if (!_tables.ContainsKey(name))
                throw new Exception(string.Format("No Table for {0}", name));
        }

        public void AddTable(Type? type, CoreTable table, bool isdefault = false, string? alias = null)
        {
            var name = TableName(type, alias);
            if (!_tables.ContainsKey(name))
                _tables[name] = new DataModelTable(type, table, isdefault, null, null, false);
            else
                throw new Exception(string.Format("[{0}] already exists in this data model!", name));
        }

        public void SetTableData(Type type, CoreTable tableData, string? alias = null)
        {
            var name = TableName(type, alias);
            if (_tables.TryGetValue(name, out var table))
            {
                table.Table = tableData;
                table.ShouldLoad = false;
            }
            else
            {
                throw new Exception(string.Format("[{0}] does not exist in this data model!", name));
            }
        }
        public void LinkTable(Type parenttype, string parentcolumn, string childalias, string childcolumn, string? parentalias = null, bool isLookup = false) =>
            LinkTable(parenttype, parentcolumn, null, childcolumn, parentalias, childalias, isLookup);

        public void LinkTable(Type parenttype, string parentcolumn, Type? childtype, string childcolumn, string? parentalias = null,
            string? childalias = null, bool isLookup = false)
        {
            CheckTable(parenttype, parentalias);

            CheckTable(childtype, childalias);

            var relationship = new DataModelRelationship(
                TableName(parenttype, parentalias),
                parentcolumn,
                TableName(childtype, childalias),
                childcolumn,
                isLookup
            );

            if (!_relationships.Any(x => x.ParentTable.Equals(relationship.ParentTable) && x.ChildTable.Equals(relationship.ChildTable)))
                _relationships.Add(relationship);
        }

        public bool HasTable(Type type, string? alias = null)
        {
            var name = TableName(type, alias);
            return _tables.ContainsKey(name);
        }
        public bool HasTable<T>(string? alias = null) => HasTable(typeof(T), alias);

        #endregion

        #region Adding & Linking Tables

        public void AddTable(string alias, CoreTable table, bool isdefault = false) => AddTable(null, table, isdefault, alias);

        public void AddTable<TType>(Filter<TType>? filter, Columns<TType>? columns, bool isdefault = false, string? alias = null, bool shouldLoad = true)
        {
            var name = TableName<TType>(alias);
            if (!_tables.ContainsKey(name))
            {
                var table = new CoreTable();
                if(columns != null)
                {
                    table.LoadColumns(columns);
                }
                else
                {
                    table.LoadColumns(typeof(TType));
                }
                _tables[name] = new DataModelTable(typeof(TType), table, isdefault, filter, columns, shouldLoad);
            }
        }

        public void AddChildTable<TParent, TChild>(Expression<Func<TParent, object>> parentcolumn, Expression<Func<TChild, object>> childcolumn,
            Filter<TChild>? filter = null, Columns<TChild>? columns = null, bool isdefault = false, string? parentalias = null, string? childalias = null)
        {
            CheckTable<TParent>(parentalias);
            AddTable(filter, columns, isdefault, childalias);
            LinkTable(parentcolumn, childcolumn, parentalias, childalias, false);
        }

        public void AddLookupTable<TSource, TLookup>(Expression<Func<TSource, object>> sourcecolumn, Expression<Func<TLookup, object>> lookupcolumn,
            Filter<TLookup>? filter = null, Columns<TLookup>? columns = null, bool isdefault = false,
            string? sourcealias = null, string? lookupalias = null)
        {
            CheckTable<TSource>(sourcealias);
            AddTable(filter, columns, isdefault, lookupalias);
            LinkTable(sourcecolumn, lookupcolumn, sourcealias, lookupalias, true);
        }

        public CoreTable GetTable<TType>(string? alias = null)
        {
            CheckTable<TType>(alias);
            var name = TableName<TType>(alias);
            return _tables[name].Table;
        }
        public DataModelTable GetDataModelTable(string name)
        {
            return _tables[name];
        }
        public DataModelTable GetDataModelTable<TType>(string? alias = null)
        {
            CheckTable<TType>(alias);
            var name = TableName<TType>(alias);
            return _tables[name];
        }

        protected bool IsRequired<TType>(IEnumerable<string> requiredtables, string? alias = null)
        {
            var name = TableName<TType>(alias);
            return requiredtables == null || requiredtables.Contains(name);
        }

        protected bool IsRequired(Type type, IEnumerable<string> requiredtables, string? alias = null)
        {
            var name = TableName(type, alias);
            return requiredtables == null || requiredtables.Contains(name);
        }

        public void LinkTable<TParent, TChild>(Expression<Func<TParent, object>> parent, Expression<Func<TChild, object>> child,
            string? parentalias = null, string? childalias = null, bool isLookup = false)
        {
            CheckTable<TParent>(parentalias);
            CheckTable<TChild>(childalias);

            var relationship = new DataModelRelationship<TParent, TChild>(parentalias, parent, childalias, child, isLookup);
            if (!_relationships.Any(x => x.ParentTable.Equals(relationship.ParentTable) && x.ChildTable.Equals(relationship.ChildTable)))
                _relationships.Add(relationship);

            //SetupIDs<TParent>(relationship.ParentColumn);
        }

        #endregion

        #region Getting/Setting Table Data

        public Filter<TType>? GetFilter<TType>(string? alias = null)
        {
            var table = GetDataModelTable<TType>(alias);
            return table.Filter as Filter<TType>;
        }
        public Columns<TType>? GetColumns<TType>(string? alias = null)
        {
            var table = GetDataModelTable<TType>(alias);
            return table.Columns as Columns<TType>;
        }
        public IColumns? GetColumns(string alias)
        {
            var table = GetDataModelTable(alias);
            return table.Columns;
        }

        [Obsolete("Use SetColumns instead")]
        public void SetTableColumns<TType>(Columns<TType> columns, string? alias = null) => SetColumns(columns, alias);

        public void SetFilter<TType>(Filter<TType>? filter, string? alias = null)
        {
            var table = GetDataModelTable<TType>(alias);
            table.Filter = filter;
        }

        public void SetColumns<TType>(Columns<TType>? columns, string? alias = null)
        {
            var table = GetDataModelTable<TType>(alias);
            table.Columns = columns;
        }

        public void SetIsDefault<TType>(bool isDefault, string? alias = null)
        {
            var table = GetDataModelTable<TType>(alias);
            table.IsDefault = isDefault;
        }

        public void SetShouldLoad<TType>(bool shouldLoad, string? alias = null)
        {
            var table = GetDataModelTable<TType>(alias);
            table.ShouldLoad = shouldLoad;
        }

        #endregion

        #region Removing Tables

        private bool RemoveTable(Type type, string? alias = null)
        {
            var name = TableName(type, alias);
            return _tables.Remove(name);
        }
        public bool RemoveTable<TType>(string? alias = null) => RemoveTable(typeof(TType), alias);

        #endregion

        #region Cache of Link Values based on relationships

        // Type = Parent, String = ParentColumn, List=Values
        //private Dictionary<Type, Dictionary<String, List<object>>> _ids = new Dictionary<Type, Dictionary<String, List<object>>>();

        // private void SetupIds<TType>(String columnname)
        //{
        //    if (!_ids.ContainsKey(typeof(TParent)))
        //        _ids[typeof(TParent)] = new Dictionary<string, List<object>>();
        //    _ids[typeof(TParent)][relationship.ParentColumn] = new List<object>();
        //}

        //private void ClearIDs<TType>()
        //{
        //    if (_ids.ContainsKey(typeof(TType)))
        //    {
        //        var cols = _ids[typeof(TType)];
        //        foreach (var col in cols.Keys)
        //            cols[col].Clear();
        //    }
        //}

        //private void UpdateIDs<TType>()
        //{
        //    if (_ids.ContainsKey(typeof(TType)))
        //    {
        //        var cols = _ids[typeof(TType)];
        //        foreach (var col in cols.Keys)
        //            cols[col].AddRange(GetTable<TType>().ExtractValues<object>(col, true));
        //    }
        //}

        //protected object[] GetIDs<TType>(String column)
        //{
        //    if (_ids.ContainsKey(typeof(TType)))
        //    {
        //        var cols = _ids[typeof(TType)];
        //        if (cols.ContainsKey(column))
        //            return cols[column].ToArray();
        //    }
        //    return new object[] { };
        //}

        #endregion
    }

    public abstract class DataModel<T> : DataModel, IDataModel<T>
        where T : Entity, IRemotable, IPersistent, new()
    {
        public DataModel(Filter<T>? filter, Columns<T>? columns = null, SortOrder<T>? sort = null)
        {
            Filter = filter;
            Columns = columns;
            Sort = sort;

            AddTable(filter, columns, true);
            AddChildTable<T, AuditTrail>(x => x.ID, x => x.EntityID);
        }

        public Filter<T>? Filter { get; set; }

        public Columns<T>? Columns { get; set; }

        public SortOrder<T>? Sort { get; set; }
    }

    public class DataModelRelationship : IDataModelRelationship
    {
        public DataModelRelationship(string parenttable, string parentcolumn, string childtable, string childcolumn, bool isLookup = false)
        {
            ParentTable = parenttable;
            ParentColumn = parentcolumn;
            ChildTable = childtable;
            ChildColumn = childcolumn;
            IsLookup = isLookup;
        }

        public string ChildColumn { get; }

        public string ChildTable { get; }

        public string ParentColumn { get; }

        public string ParentTable { get; }

        public bool IsLookup { get; }

        public DataRelation AsDataRelation()
        {
            string parentTable, parentColumn, childTable, childColumn;
            // Reverse the relationships if it is a lookup
            if (IsLookup)
            {
                parentTable = ChildTable;
                parentColumn = ChildColumn;
                childTable = ParentTable;
                childColumn = ParentColumn;
            }
            else
            {
                parentTable = ParentTable;
                parentColumn = ParentColumn;
                childTable = ChildTable;
                childColumn = ChildColumn;
            }
            var result = new DataRelation(
                string.Format("{0}_{1}_{2}_{3}", parentTable, parentColumn, childTable, childColumn),
                parentTable,
                childTable,
                new[] { parentColumn },
                new[] { childColumn },
                false
            );
            result.RelationName = string.Format("{0}_{1}_{2}_{3}", parentTable, parentColumn, childTable, childColumn);
            return result;
        }

        public string ParentColumnAsPropertyName()
        {
            return ParentColumn;
        }

        public string ChildColumnAsPropertyName()
        {
            return ChildColumn;
        }
    }

    public class DataModelRelationship<TParent, TChild> : IDataModelRelationship
    {
        public DataModelRelationship(string? parentalias, Expression<Func<TParent, object>> parent, string? childalias,
            Expression<Func<TChild, object>> child, bool isLookup)
        {
            ParentTable = string.IsNullOrWhiteSpace(parentalias) ? typeof(TParent).EntityName().Split('.').Last() : parentalias;
            Parent = parent;
            ChildTable = string.IsNullOrWhiteSpace(childalias) ? typeof(TChild).EntityName().Split('.').Last() : childalias;
            Child = child;
            IsLookup = isLookup;
        }

        public Expression<Func<TChild, object>> Child { get; }

        public Expression<Func<TParent, object>> Parent { get; }

        public string ChildTable { get; }

        public string ChildColumn => CoreUtils.GetFullPropertyName(Child, ".").Replace('.', '_');

        public string ParentTable { get; }

        public string ParentColumn => CoreUtils.GetFullPropertyName(Parent, ".").Replace('.', '_');

        public bool IsLookup { get; }

        public string ParentColumnAsPropertyName()
        {
            return CoreUtils.GetFullPropertyName(Parent, ".");
        }

        public string ChildColumnAsPropertyName()
        {
            return CoreUtils.GetFullPropertyName(Child, ".");
        }

        public DataRelation AsDataRelation()
        {
            string parentTable, parentColumn, childTable, childColumn;
            // Reverse the relationships if it is a lookup
            if (IsLookup)
            {
                parentTable = ChildTable;
                parentColumn = ChildColumn;
                childTable = ParentTable;
                childColumn = ParentColumn;
            }
            else
            {
                parentTable = ParentTable;
                parentColumn = ParentColumn;
                childTable = ChildTable;
                childColumn = ChildColumn;
            }
            var result = new DataRelation(
                string.Format("{0}_{1}_{2}_{3}", parentTable, parentColumn, childTable, childColumn),
                parentTable,
                childTable,
                new[] { parentColumn },
                new[] { childColumn },
                false
            );
            result.RelationName = string.Format("{0}_{1}_{2}_{3}", parentTable, parentColumn, childTable, childColumn);
            return result;
        }
    }
}