using System.Collections.Concurrent;
using System.Reflection;
using System.Text.RegularExpressions;
using InABox.Configuration;
using InABox.Core;
using NPOI.OpenXmlFormats.Dml;
using NPOI.POIFS.FileSystem;

namespace InABox.Database
{
    public static class UserTrackingCache
    {
        public static ConcurrentBag<UserTracking> Cache = new();
        public static DateTime Date = DateTime.MinValue;
    }

    // public interface ICacheStore<T>
    // {
    //     void LoadCache();
    //     T GetCacheItem();
    //     void UpdateCache();
    // }

    public class Store<T> : IStore<T> where T : Entity, new()
    {

        public Type Type => typeof(T);
        
        public bool IsSubStore { get; set; }

        public Guid UserGuid { get; set; }
        public string UserID { get; set; }

        public Platform Platform { get; set; }
        public string Version { get; set; }
        public IProvider Provider { get; set; }

        public Logger Logger { get; set; }

        public virtual void Init()
        {
            
        }
        
        private Type GetTrackingType(Type type)
        {
            var attr = type.GetCustomAttribute<UserTrackingAttribute>();

            if (attr == null)
                return type;

            if (!attr.Enabled)
                return null;

            if (attr.Parent != null)
                return GetTrackingType(attr.Parent);

            return type;
        }
        
        
        private void UpdateUserTracking(UserTrackingAction action)
        {
            if (IsSubStore)
                return;

            if (!DbFactory.IsSupported<UserTracking>())
                return;

            if (string.IsNullOrWhiteSpace(UserID))
                return;

            var type = GetTrackingType(typeof(T));
            if (type == null)
                return;

            UpdateUserTracking(type, action);
        }

        protected void UpdateUserTracking(Type type, UserTrackingAction action)
        {
            if (UserTrackingCache.Date != DateTime.Today)
            {
                var tdata = Provider.Query(
                    new Filter<UserTracking>(x => x.Date).IsEqualTo(DateTime.Today),
                    new Columns<UserTracking>(ColumnTypeFlags.DefaultVisible | ColumnTypeFlags.IncludeForeignKeys)
                );
                UserTrackingCache.Cache = new ConcurrentBag<UserTracking>(tdata.Rows.Select(x => x.ToObject<UserTracking>()));
                UserTrackingCache.Date = DateTime.Today;
            }

            var tracking = UserTrackingCache.Cache.FirstOrDefault(x =>
                Equals(x.User.ID, UserGuid) && DateTime.Equals(x.Date, DateTime.Today) && string.Equals(x.Type, type.Name));
            if (tracking == null)
            {
                tracking = new UserTracking();
                tracking.Date = DateTime.Today;
                tracking.Type = type.Name;
                tracking.User.ID = UserGuid;
                UserTrackingCache.Cache.Add(tracking);
            }

            tracking.Increment(DateTime.Now, action);
            Provider.Save(tracking);
        }

        public IStore FindSubStore(Type t)
        {
            var defType = typeof(Store<>).MakeGenericType(t);

            var subType = DbFactory.Stores.Where(myType => myType.IsSubclassOf(defType)).FirstOrDefault();

            var result = (IStore)Activator.CreateInstance(subType == null ? defType : subType);
            result.UserGuid = UserGuid;
            result.UserID = UserID;
            result.Platform = Platform;
            result.Version = Version;
            result.IsSubStore = true;
            result.Provider = Provider;
            result.Logger = Logger;
            return result;
        }

        public IStore<TEntity> FindSubStore<TEntity>() where TEntity : Entity, new()
        {
            return (FindSubStore(typeof(TEntity)) as Store<TEntity>)!;
        }

        private Filter<T>? RunScript(ScriptType type, Filter<T>? filter)
        {
            var scriptname = type.ToString();
            var key = string.Format("{0} {1}", typeof(T).EntityName(), scriptname);

            if (DbFactory.LoadedScripts.ContainsKey(key))
            {
                var script = DbFactory.LoadedScripts[key];

                Logger.Send(LogType.Information, UserID, string.Format("{0}.{1} Executing..", typeof(T).EntityName(), scriptname));
                try
                {
                    script.SetValue("Store", this);
                    script.SetValue("Filter", filter);
                    var result = script.Execute();
                    Logger.Send(LogType.Information, UserID, string.Format("{0}.{1} returns {2}", typeof(T).EntityName(), scriptname, result));
                    return result ? script.GetValue("Filter") as Filter<T> : filter;
                }
                catch (Exception eExec)
                {
                    Logger.Send(LogType.Information, UserID,
                        string.Format("{0}.{1} Invoke Exception: {2}", typeof(T).EntityName(), scriptname, eExec.Message));
                }
            }

            return filter;
        }

        private IEnumerable<T> RunScript(ScriptType type, IEnumerable<T> entities)
        {
            var scriptname = type.ToString();
            var key = string.Format("{0} {1}", typeof(T).EntityName(), scriptname);
            if (DbFactory.LoadedScripts.ContainsKey(key))
            {
                var variable = typeof(T).EntityName().Split('.').Last() + "s";
                var script = DbFactory.LoadedScripts[key];
                script.SetValue("Store", this);
                script.SetValue(variable, entities);
                Logger.Send(LogType.Information, UserID, string.Format("{0}.{1} Executing..", typeof(T).EntityName(), scriptname));
                foreach (var entity in entities)
                    try
                    {
                        var result = script.Execute();
                        Logger.Send(LogType.Information, UserID,
                            string.Format("{0}.{1} returns {2}: {3}", typeof(T).EntityName(), scriptname, result, entity));
                        return result ? script.GetValue(variable) as IEnumerable<T> : entities;
                    }
                    catch (Exception eExec)
                    {
                        var stack = new List<string>();
                        var eStack = eExec;
                        while (eStack != null)
                        {
                            stack.Add(eStack.Message);
                            eStack = eStack.InnerException;
                        }

                        stack.Reverse();
                        var message = string.Join("\n", stack);
                        Logger.Send(LogType.Information, UserID,
                            string.Format("{0}.{1} Invoke Exception: {2}", typeof(T).EntityName(), scriptname, message));
                    }
            }

            return entities;
        }

        private CoreTable RunScript(ScriptType type, CoreTable table)
        {
            var scriptname = type.ToString();
            var variable = typeof(T).EntityName().Split('.').Last() + "s";
            var key = string.Format("{0} {1}", typeof(T).EntityName(), scriptname);

            if (DbFactory.LoadedScripts.ContainsKey(key))
            {
                var script = DbFactory.LoadedScripts[key];
                Logger.Send(LogType.Information, UserID, string.Format("{0}.{1} Executing..", typeof(T).EntityName(), scriptname));
                try
                {
                    script.SetValue("Store", this);
                    script.SetValue(variable, table);
                    var result = script.Execute();
                    Logger.Send(LogType.Information, UserID,
                        string.Format("{0}.{1} returns {2}: {3}", typeof(T).EntityName(), scriptname, result, table));
                    return result ? script.GetValue(variable) as CoreTable : table;
                }
                catch (Exception eExec)
                {
                    Logger.Send(LogType.Information, UserID,
                        string.Format("{0}.{1} Invoke Exception: {2}", typeof(T).EntityName(), scriptname, eExec.Message));
                }
            }

            return table;
        }

        #region Query Functions

        protected virtual CoreTable OnQuery(Filter<T>? filter, Columns<T>? columns, SortOrder<T>? sort, CoreRange? range)
        {
            return Provider.Query(filter, columns, sort, range);
        }
        
        private CoreTable DoQuery(Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null, CoreRange? range = null)
        {
            UpdateUserTracking(UserTrackingAction.Read);

            try
            {
                var flt = PrepareFilter(filter);

                flt = RunScript(ScriptType.BeforeQuery, flt);

                var result = OnQuery(filter, columns, sort, range);

                AfterQuery(result);

                result = RunScript(ScriptType.AfterQuery, result);

                return result;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message + "\n\n" + e.StackTrace + "\n");
            }
        }

        public CoreTable Query(Filter<T>? filter = null, Columns<T>? columns = null, SortOrder<T>? sort = null, CoreRange? range = null)
        {
            return DoQuery(filter, columns, sort, range);
        }

        public CoreTable Query(IFilter? filter = null, IColumns? columns = null, ISortOrder? sort = null, CoreRange? range = null)
        {
            return DoQuery(filter as Filter<T>, columns as Columns<T>, sort as SortOrder<T>, range);
        }

        protected virtual void AfterQuery(CoreTable data)
        {
        }

        #endregion

        #region Load Functions

        protected virtual Filter<T>? PrepareFilter(Filter<T>? filter)
        {
            return filter;
        }

        #endregion

        #region Saving Functions

        private void CheckAutoIncrement(params T[] entities)
        {
            if (ProcessNumericAutoInc(entities))
                return;
            ProcessStringAutoIncrement(entities);
        }

        private bool ProcessStringAutoIncrement(params T[] entities)
        {
            if (!entities.Any())
                return false;

            if (entities.First() is IStringAutoIncrement<T> autoinc)
            {

                var prefix = autoinc.AutoIncrementPrefix() ?? "";
                
                var prop = CoreUtils.GetPropertyFromExpression<T, string>(autoinc.AutoIncrementField());

                var requiredEntities = entities.Where(x =>
                {
                    return (prop.GetValue(x) as string).IsNullOrWhiteSpace() && (x.ID == Guid.Empty || x.HasOriginalValue(prop.Name));
                }).ToList();

                if (requiredEntities.Count > 0)
                {
                    var sql = string.IsNullOrWhiteSpace(prefix)
                        ? $"select max(cast({prop.Name} as integer)) from {typeof(T).Name.Split('.').Last()}"
                        : $"select max(cast(substr({prop.Name},{prefix.Length+1}) as integer)) from {typeof(T).Name.Split('.').Last()} where {prop.Name} like '{prefix}%'";
                    var result = Provider.List(sql);
                    int.TryParse(result.FirstOrDefault()?.FirstOrDefault()?.ToString() ?? "0", out int newvalue);
                    
                    
                    // var filter =  new Filter<T>(prop.Name).IsGreaterThanOrEqualTo(String.Format("{0}0",prefix))
                    //     .And(prop.Name).IsLessThanOrEqualTo(String.Format("{0}9999999999", prefix));
                    //
                    // var filter2 = autoinc.AutoIncrementFilter();
                    // if (filter2 != null)
                    //     filter = filter.And(filter2);
                    //
                    // var newvalue = 0;
                    // var row = Provider.Query(
                    //     filter,
                    //     Columns.None<T>().Add(prop.Name),
                    //     new SortOrder<T>(prop.Name, SortDirection.Descending),
                    //     CoreRange.Database(1)
                    // ).Rows.FirstOrDefault();
                    // if (row != null)
                    // {
                    //     var id = row.Get<string>(prop.Name);
                    //     if (!string.IsNullOrWhiteSpace(prefix))
                    //         id = id.Substring(prefix.Length);
                    //     id = new string(id.Where(c => char.IsDigit(c)).ToArray());
                    //     int.TryParse(id, out newvalue);
                    // }
                    //
                    
                    foreach (var entity in requiredEntities)
                    {
                        newvalue++;
                        prop.SetValue(entity, prefix + string.Format(autoinc.AutoIncrementFormat(), newvalue));
                    }

                    return true;
                }
            }

            return false;
        }
        
        private bool ProcessNumericAutoInc(params T[] entities)
        {
            if (!entities.Any())
                return false;
            if (entities.First() is INumericAutoIncrement<T> autoinc)
            {
                var prop = CoreUtils.GetPropertyFromExpression(autoinc.AutoIncrementField());

                var requiredEntities = entities.Where(x =>
                {
                    return object.Equals(prop.GetValue(x), 0) && (x.ID == Guid.Empty || x.HasOriginalValue(prop.Name));
                }).ToList();

                if (requiredEntities.Count > 0)
                {
                    
                    var row = Provider.Query(
                        autoinc.AutoIncrementFilter(), 
                        Columns.None<T>().Add(prop.Name), 
                        new SortOrder<T>(prop.Name,SortDirection.Descending),
                        CoreRange.Database(1)
                    ).Rows.FirstOrDefault();
                    int newvalue = row != null ? row.Get<int>(prop.Name) : 0;

                    foreach (var entity in requiredEntities)
                    {
                        newvalue++;
                        prop.SetValue(entity, newvalue);
                    }
                    return true;
                }
            }

            return false;
        }

        protected virtual void BeforeSave(T entity)
        {
            if(entity is IPostable)
            {
                PosterUtils.CheckPostedStatus(new GlobalConfiguration<PostableSettings>(typeof(T).Name, new DbConfigurationProvider<GlobalSettings>(UserID)).Load(), entity);
            }

            // Check for (a) blank code fields and (b) duplicate codes
            // There may be more than one code on an entity (why would you do this?)
            // so it's a bit trickier that I would hope

            Filter<T> codes = null;
            var columns = Columns.None<T>().Add(x => x.ID);
            var props = CoreUtils.PropertyList(typeof(T), x => x.GetCustomAttributes<UniqueCodeEditor>().Any(), true);
            foreach (var key in props.Keys)
                if (entity.HasOriginalValue(key) || entity.ID == Guid.Empty)
                {
                    var code = CoreUtils.GetPropertyValue(entity, key) as string;
                    if (string.IsNullOrWhiteSpace(code))
                        throw new NullCodeException(typeof(T), key);

                    var expr = CoreUtils.GetPropertyExpression<T, object?>(key); //CoreUtils.GetMemberExpression(typeof(T),key)
                    codes = codes == null ? new Filter<T>(expr).IsEqualTo(code) : codes.Or(expr).IsEqualTo(code);
                    columns.Add(key);
                }

            if (codes != null)
            {
                var filter = new Filter<T>(x => x.ID).IsNotEqualTo(entity.ID);
                filter = filter.And(codes);
                var others = Provider.Query(filter, columns);

                var duplicates = new Dictionary<string, object>();
                foreach (var row in others.Rows)
                foreach (var key in props.Keys)
                {
                    var eval = CoreUtils.GetPropertyValue(entity, key);
                    var cval = row.Get<string>(key);
                    if (Equals(eval, cval))
                        duplicates[key] = eval;
                }

                if (duplicates.Any())
                    throw new DuplicateCodeException(typeof(T), duplicates);
            }
        }

        protected virtual void BeforeSave(IEnumerable<T> entities)
        {
            foreach(var entity in entities)
            {
                BeforeSave(entity);
            }
        }

        protected virtual void AfterSave(T entity)
        {
        }

        protected virtual void AfterSave(IEnumerable<T> entities)
        {
            foreach(var entity in entities)
            {
                AfterSave(entity);
            }
        }

        protected virtual void OnSave(T entity, ref string auditnote)
        {
            CheckAutoIncrement(entity);
            Provider.Save(entity);
        }

        private void DoSave(T entity, string auditnote)
        {
            UpdateUserTracking(UserTrackingAction.Write);

            entity = RunScript(ScriptType.BeforeSave, new[] { entity }).First();

            // Process any AutoIncrement Fields before we apply the Unique Code test
            // Thus, if we have a unique autoincrement, it will be populated prior to validation
            CheckAutoIncrement(entity);

            BeforeSave(entity);

            var changes = entity.ChangedValues();
            
            OnSave(entity, ref auditnote);

            if (DbFactory.IsSupported<AuditTrail>())
            {
                var notes = new List<string>();
                if (!string.IsNullOrEmpty(auditnote))
                    notes.Add(auditnote);
                if (!string.IsNullOrEmpty(changes))
                    notes.Add(changes);
                if (notes.Any()) AuditTrail(entity, notes);
            }

            AfterSave(entity);

            entity = RunScript(ScriptType.AfterSave, new[] { entity }).First();
            NotifyListeners([entity]);
        }

        protected void AuditTrail(IEnumerable<Entity> entities, IEnumerable<string> notes)
        {
            var updates = new List<AuditTrail>();
            foreach (var entity in entities)
            {
                var audit = new AuditTrail
                {
                    EntityID = entity.ID,
                    Timestamp = DateTime.Now,
                    User = UserID,
                    Note = string.Join(": ", notes)
                };
                updates.Add(audit);
            }

            Provider.Save(updates);
        }

        protected void AuditTrail(Entity entity, IEnumerable<string> notes)
        {
            AuditTrail(new[] { entity }, notes);
        }

        public void Save(T entity, string auditnote)
        {
            DoSave(entity, auditnote);
        }

        public void Save(Entity entity, string auditnote)
        {
            var ent = (T)entity;
            DoSave(ent, auditnote);
        }

        public void Save(IEnumerable<T> entities, string auditnote)
        {
            DoSave(entities.AsArray(), auditnote);
        }

        public void Save(IEnumerable<Entity> entities, string auditnote)
        {
            DoSave(entities.Select(x => (T)x).ToArray(), auditnote);
        }

        protected virtual void OnSave(T[] entities, ref string auditnote)
        {
            CheckAutoIncrement(entities);
            Provider.Save(entities);
        }

        private void DoSave(T[] entities, string auditnote)
        {
            UpdateUserTracking(UserTrackingAction.Write);

            entities = RunScript(ScriptType.BeforeSave, entities).AsArray();

            // Process any AutoIncrement Fields before we apply the Unique Code test
            // Thus, if we have a unique autoincrement, it will be populated prior to validation
            CheckAutoIncrement(entities);

            var changes = new Dictionary<T, string>();
            foreach (var entity in entities)
            {
                changes[entity] = entity.ChangedValues();
            }
            BeforeSave(entities);

            OnSave(entities, ref auditnote);

            if (DbFactory.IsSupported<AuditTrail>())
            {
                var audittrails = new List<AuditTrail>();
                foreach (var entity in entities)
                {
                    var notes = new List<string>();

                    if (!string.IsNullOrEmpty(auditnote))
                        notes.Add(auditnote);

                    if (changes.TryGetValue(entity, out string? value) && !string.IsNullOrEmpty(value))
                        notes.Add(value);

                    if (notes.Count != 0)
                    {
                        var audit = new AuditTrail
                        {
                            EntityID = entity.ID,
                            Timestamp = DateTime.Now,
                            User = UserID,
                            Note = string.Join(": ", notes)
                        };
                        audittrails.Add(audit);
                    }
                }

                if (audittrails.Count != 0)
                    Provider.Save(audittrails);
            }

            AfterSave(entities);

            entities = RunScript(ScriptType.AfterSave, entities).AsArray();
            
            NotifyListeners(entities);
        }

        private static List<Tuple<Type, Action<Guid[]>>> _listeners = new List<Tuple<Type, Action<Guid[]>>>();

        public static void RegisterListener<TType>(Action<Guid[]> listener)
            => _listeners.Add(new Tuple<Type, Action<Guid[]>>(typeof(TType), listener));

        private void NotifyListeners(IEnumerable<T> items)
        {
            var ids = items.Select(x => x.ID).ToArray();
            foreach (var listener in _listeners.Where(x => x.Item1 == typeof(T)))
                listener.Item2(ids);
            
        }
        
        #endregion

        #region Delete Functions

        protected virtual void BeforeDelete(T entity)
        {
        }

        protected virtual void OnDelete(T entity)
        {
            Provider.Delete(entity, UserID);
        }
        protected virtual void OnDelete(IEnumerable<T> entities)
        {
            Provider.Delete(entities, UserID);
        }

        private void DoDelete(T entity, string auditnote)
        {
            UpdateUserTracking(UserTrackingAction.Write);

            entity = RunScript(ScriptType.BeforeDelete, new[] { entity }).First();

            try
            {
                BeforeDelete(entity);

                try
                {
                    OnDelete(entity);
                }
                catch (Exception e)
                {
                    Logger.Send(LogType.Error, "", $"Error in DoDelete(T entity, string auditnote):\n{CoreUtils.FormatException(e)}");
                }

                AfterDelete(entity);

                entity = RunScript(ScriptType.AfterDelete, new[] { entity }).First();
                
                NotifyListeners([entity]);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message + "\n\n" + e.StackTrace + "\n");
            }
        }

        private void DoDelete(IEnumerable<T> entities, string auditnote)
        {
            UpdateUserTracking(UserTrackingAction.Write);

            entities = RunScript(ScriptType.BeforeDelete, entities);

            try
            {
                foreach (var entity in entities)
                    BeforeDelete(entity);

                try
                {
                    OnDelete(entities);
                }
                catch (Exception e)
                {
                    Logger.Send(LogType.Error, "", $"Error in DoDelete(IEnumerable<T> entities, string auditnote):\n{CoreUtils.FormatException(e)}");
                }

                foreach (var entity in entities) AfterDelete(entity);

                entities = RunScript(ScriptType.AfterDelete, entities);
                
                NotifyListeners(entities);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message + "\n\n" + e.StackTrace + "\n");
            }
        }

        public void Delete(T entity, string auditnote)
        {
            DoDelete(entity, auditnote);
        }

        public void Delete(Entity entity, string auditnote)
        {
            DoDelete((T)entity, auditnote);
        }

        public void Delete(IEnumerable<T> entities, string auditnote)
        {
            DoDelete(entities, auditnote);
        }

        public void Delete(IEnumerable<Entity> entities, string auditnote)
        {
            var updates = new List<T>();
            foreach (var entity in entities)
                updates.Add((T)entity);
            DoDelete(updates, auditnote);
        }

        protected virtual void AfterDelete(T entity)
        {
        }

        #endregion
    }
}