using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
using System.Linq;
using Newtonsoft.Json;
using MisIngredientesVue.Core.Models;
using System.Reflection;
using System.Linq.Expressions;
using Npgsql;
using System.Data;
using Dapper;
using MisIngredientesVue.Core.Attributes;
using MisIngredientesVue.Core.Extensions;
using Microsoft.AspNetCore.JsonPatch;
using System.IO;
using OfficeOpenXml;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel;
using Microsoft.Extensions.Options;
using System.Text.RegularExpressions;
using System.ComponentModel.DataAnnotations;
using System.Collections;
using OfficeOpenXml.Style;
using System.Globalization;

namespace MisIngredientesVue.Core.Services
{

    public class RepositoryEntity<TId>
    {
        //[BsonId]
        //[BsonRepresentation(BsonType.ObjectId)]
        public TId Id { get; set; }
    }


    public class RepositoryEntityWithTenantId<TId> : RepositoryEntity<TId>
    {
        [DoNotUpdate]
        [IgnoreRelationship]
        public string TenantId { get; set; }

        [DoNotUpdate]
        public int? CreatedById { get; set; }

        [DisplayName("Creado por")]
        [DoNotUpdate]
        public UserData CreatedBy { get; set; }

        [DisplayName("Creado")]
        [IncludeTime]
        [DoNotUpdate]
        public DateTime Created { get; set; }

        [DoNotUpdate]
        public int? ModifiedById { get; set; }

        [DisplayName("Modificado por")]
        [DoNotUpdate]
        public UserData ModifiedBy { get; set; }

        [DisplayName("Modificado")]
        [DoNotUpdate]
        [IncludeTime]
        public DateTime Modified { get; set; }

        [DoNotUpdate]
        public bool IsActive { get; set; }
    }

    public class FindResults<T>
    {
        public object Filters { get; set; }
        public object CustomData { get; set; }
        public long Total { get; set; }
        public IEnumerable<T> Results { get; set; }
    }


    public interface IRepository
    {
        Task<T> Query<T>(Func<IDbConnection, Task<T>> func);
        Task CreateTable<T>();
        Task<int> GetAutoIncrementNumber(string tenantId, string type);
        Task<IEnumerable<T>> FindAll<T, TId>(string tenantId) where T : RepositoryEntity<TId>;
        Task<IEnumerable<T>> FindByIds<T, TId>(string tenantId, TId[] ids) where T : RepositoryEntity<TId>;
        Task<IEnumerable<TId>> FindIds<T, TId>(string tenantId, Expression<Func<T, bool>> expression) where T : RepositoryEntity<TId>;
        Task<IEnumerable<T>> FindAll<T, TId>(string tenantId, System.Linq.Expressions.Expression<Func<T, bool>> expression) where T : RepositoryEntity<TId>;
        Task<FindResults<T>> Find<T, TId>(string tenantId, Expression<Func<T, bool>> expression, int? skip = null, int? take = null, 
            string sortBy = "", string sortOrder = "asc", Func<IDbConnection, string, DynamicParameters, Task<object>> GetCustomData = null, 
            string joinsQuery = "", Func<IDbConnection, string, DynamicParameters, Task<IEnumerable<T>>> MappingFunc = null, bool onlyActive = true) where T : RepositoryEntity<TId>;
        Task<FindResults<T>> CustomFind<T, TId>(string tenantId, string resultsQuery, string totalQuery,
             DynamicParameters dynamicParameters, int? skip = null, int? take = null, string sortBy = "", string sortOrder = "asc",
             Func<IDbConnection, string, DynamicParameters, Task<IEnumerable<T>>> ResultsMappingFunc = null,
             Func<IDbConnection, string, DynamicParameters, Task<dynamic>> TotalMappingFunc = null);
        Task<IEnumerable<DistinctResult>> GetDistinct<T, TId>(string tenantId, Expression<Func<T, object>> valueExpression, Expression<Func<T, object>> textExpression, string term = null) where T : RepositoryEntity<TId>;
        Task<IEnumerable<DistinctResult>> GetDistinct<T, TId>(string tenantId, Expression<Func<T, object>> expression, string term = null) where T : RepositoryEntity<TId>;
        Task<IEnumerable<DistinctResult>> GetDistinct<T, TId>(string tenantId, string propertyName, string term = null) where T : RepositoryEntity<TId>;
        Task<T> Insert<T, TId>(string tenantId, int? createdById, T item, bool nested = true) where T : RepositoryEntity<TId>;
        Task BulkUpload<T, TId>(string tenantId, int createdOrModifiedBy, Expression<Func<T, object>> updateKey, IEnumerable<T> items) where T : RepositoryEntity<TId>;
        Task<T> Patch<T, TId>(string tenantId, int modifiedById, TId id, JsonPatchDocument<T> changesDocument, bool returnUpdatedObject = true) where T : RepositoryEntity<TId>;
        Task<MemoryStream> GetExcel<T, TId>(string tenantId, int offset, Expression<Func<T, bool>> expression, string sortBy = "", string sortOrder = "asc", Func<IDbConnection, string, DynamicParameters, Task<object>> GetCustomData = null, string joinsQuery = "", Func<IDbConnection, string, DynamicParameters, Task<IEnumerable<T>>> MappingFunc = null, Func<PropertyInfo, T, object, string> valueFormatter = null) where T : RepositoryEntity<TId>;
        Task<MemoryStream> GetExcel<T, TId>(FindResults<T> results, int offset, Func<PropertyInfo, T, object, string> valueFormatter = null) where T : RepositoryEntity<TId>;
        Task<MemoryStream> GetExcelFromData<T, TId>(IEnumerable<T> items, int offset) where T : RepositoryEntity<TId>;
        Task Replace<T, TId>(string tenantId, T item) where T : RepositoryEntity<TId>;
        string GetDeleteQuery<T, TId>(string tenantId, T item, ref DynamicParameters parameters) where T : RepositoryEntity<TId>;
        string GetDeleteQuery<T, TId>(string tenantId, Expression<Func<T, bool>> expression, ref DynamicParameters dynamicParameters) where T : RepositoryEntity<TId>;
        Task DeleteMany<T, TId>(string tenantId, int deleteById, T[] items) where T : RepositoryEntity<TId>;
        Task DeleteManyByIds<T, TId>(string tenantId, int deleteById, TId[] ids) where T : RepositoryEntity<TId>;
        Task DeleteMany<T, TId>(string tenantId, int deleteById, System.Linq.Expressions.Expression<Func<T, bool>> expression) where T : RepositoryEntity<TId>;
        Task<T> TryFindOneById<T, TId>(string tenantId, TId id) where T : RepositoryEntity<TId>;
        Task<T> FindOneById<T, TId>(string tenantId, TId id) where T : RepositoryEntity<TId>;
        Task<T> TryFindOne<T, TId>(string tenantId, Expression<Func<T, bool>> expression) where T : RepositoryEntity<TId>;
        Task<T> FindOne<T, TId>(string tenantId, Expression<Func<T, bool>> expression) where T : RepositoryEntity<TId>;
        string GetUpdateQuery<T, TId>(string tenantId, int modifiedById, Expression<Func<T, bool>> findExpression, ref DynamicParameters dynamicParams, params UpdateInfo<T>[] updates) where T : RepositoryEntity<TId>;
        string GetUpdateQuery<T, TId>(string tenantId, int modifiedById, Expression<Func<T, bool>> findExpression, T item, ref DynamicParameters dynamicParams) where T : RepositoryEntity<TId>;
        //string GetUpdateQueryNoTenantId<T, TId>(int modifiedById, Expression<Func<T, bool>> findExpression, T item, ref DynamicParameters dynamicParams) where T : RepositoryEntity<TId>;
        string GetInsertQuery<T, TId>(string tenantId, int createdById, T item, ref DynamicParameters parameters, string returningIdInto = null) where T : RepositoryEntity<TId>;
        string GetUpsertQuery<T, TId>(string tenantId, int createdOrModifiedBy, T item, Expression<Func<T, object>> conflictProperty, ref DynamicParameters dynamicParams) where T : RepositoryEntity<TId>;
        Task Update<T, TId>(string tenantId, int modifiedBy, Expression<Func<T, bool>> findExpression, params UpdateInfo<T>[] updates) where T : RepositoryEntity<TId>;
        Task Update<T, TId>(string tenantId, int modifiedBy, T item) where T : RepositoryEntity<TId>;
        Task<T> TryFindOneByTenantId<T, TId>(string tenantId) where T : RepositoryEntity<TId>;
        string GetTableName<T>();
        Task<T> QueryFirstOrDefault<T>(string query, object parameters);
        string PrintQuery(string query, DynamicParameters dynamicParameters);
        Task<T> QuerySingle<T>(string query, object parameters);
        Task Reactivate<T, TId>(string tenantId, int modifiedBy, TId id) where T : RepositoryEntity<TId>;
        DynamicParameters GetWhereQueryFromExpression<T, TId>(string tenantId, System.Linq.Expressions.Expression<Func<T, bool>> expression, out string query, bool onlyActive = true);
        
    }

    public class UpdateInfo<T>
    {
        public object Value { get; set; }
        public Expression<Func<T, object>> Expression { get; set; }
    }

    public class JoinInfo
    {
        public bool IsList { get; set; }
        public string Join { get; set; }
        public Type ParentType { get; set; }
        public Type Type { get; set; }

        public PropertyInfo Property { get; set; }
    }

    public class JsonBAttribute : Attribute
    {

    }

    public class DistinctResult
    {
        public string Text { get; set; }
        public object Value { get; set; }
    }

    //Used to ignore relationships on properties that end with Id
    public class IgnoreRelationshipAttribute : Attribute
    {
        
        
    }

    public class TextAttribute : Attribute
    {

    }

    public class IncludeTimeAttribute : Attribute
    {

    }

    public class NoJoinAttribute : Attribute
    {

    }

    public class IncludeIdAttribute : Attribute
    {
        public string DisplayName { get; set; }

        public IncludeIdAttribute(string displayName) {
            DisplayName = displayName;
        }
    }

    public class UniqueAttribute : Attribute
    {

    }

    public class BaseRepository : IRepository
    {
        private readonly IOptions<DatabaseOptions> _options;

        private IDbConnection Connection
        {
            get
            {
                return new NpgsqlConnection(_options.Value.ConnectionString);
            }
        }

        public async Task<T> Query<T>(Func<IDbConnection, Task<T>> func)
        {
            using (IDbConnection dbConnection = Connection)
            {
                return await func(dbConnection);
            }
        }

        public BaseRepository(IOptions<DatabaseOptions> options)
        {
            _options = options;
        }


        public async Task<T> QuerySingle<T>(string query, object parameters)
        {
            using (IDbConnection dbConnection = Connection)
            {
                return await dbConnection.QuerySingleAsync<T>(query, parameters);
            }
        }

        public async Task<T> QueryFirstOrDefault<T>(string query, object parameters)
        {
            using (IDbConnection dbConnection = Connection)
            {
                return await dbConnection.QueryFirstOrDefaultAsync<T>(query, parameters);
            }
        }

        private DynamicParameters GetWhereQueryFromId<T, TId>(string tenantId, TId id, out string query)
        {
            var p = new DynamicParameters();
            p.Add("@id", id);

            if (typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1")
            {
                p.Add("@tenantId", tenantId);
                query = "WHERE TenantId = @tenantId AND id = @id";
            }
            else
                query = "WHERE id = @id";

            return p;
        }

        //public DynamicParameters GetWhereQueryFromExpressionNoTenantId<T, TId>(System.Linq.Expressions.Expression<Func<T, bool>> expression, out string query, bool onlyActive = true)
        //{
        //    var tableName = GetTableName<T>();
        //    var parameters = new DynamicParameters();

        //    query = ((LambdaExpression)expression).Body.ToString();

        //    if (string.IsNullOrWhiteSpace(query) || query == "True")
        //    {
        //        if (typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1" || typeof(T).GetProperties().Any(x => x.Name == "TenantId"))
        //        {
        //            if (typeof(T).GetProperties().Any(x => x.Name == "IsActive"))
        //                query = $"WHERE {(onlyActive ? $"AND {GetTableName<T>()}.IsActive = True" : "")}";
        //            else
        //                query = $"WHERE ";
        //        }

        //        return parameters;
        //    }

        //    var paramName = expression.Parameters[0].Name;
        //    //var paramTypeName = expression.Parameters[0].Type.Name;

        //    query = query.Replace(paramName + ".", tableName + ".")
        //                     .Replace("== null", "IS NULL")
        //                     .Replace("!= null", "IS NOT NULL")
        //                     .Replace("==", "=")
        //                     .Replace("OrElse", "OR")
        //                     .Replace("AndAlso", "AND");

        //    query = Regex.Replace(query, @"value\(.+?\)\.[a-zA-Z]*\.Id", new MatchEvaluator((match) =>
        //    {
        //        var rawValue = FindValueRecursive(expression.Body, match.Value);
        //        var pName = "@v" + Guid.NewGuid().ToString("N");

        //        if (rawValue == null || (rawValue is string && rawValue as string == string.Empty))
        //        {
        //            pName = "[NULL]";
        //        }
        //        else
        //        {
        //            parameters.Add(pName, rawValue);
        //        }

        //        return pName;

        //    }));

        //    query = Regex.Replace(query, @"value\(.+?\)\.[a-zA-Z]*", new MatchEvaluator((match) =>
        //    {
        //        var rawValue = FindValueRecursive(expression.Body, match.Value);
        //        var pName = "@v" + Guid.NewGuid().ToString("N");

        //        if (rawValue == null || (rawValue is string && rawValue as string == string.Empty))
        //        {
        //            pName = "[NULL]";
        //        }
        //        else
        //        {
        //            if (rawValue.GetType().IsArray)
        //            {
        //                if ((rawValue as Array).Length == 0)
        //                    return "[NULL]";

        //                var intValues = new List<int>();
        //                foreach (var item in (rawValue as Array))
        //                {
        //                    intValues.Add((int)item);
        //                }

        //                parameters.Add(pName, intValues.ToArray());
        //            }
        //            else
        //            {
        //                parameters.Add(pName, rawValue);
        //            }
        //        }

        //        return pName;

        //    }));

        //    query = Regex.Replace(query, @"\""(.+?)\""", new MatchEvaluator((match) =>
        //    {
        //        var rawValue = match.Value;
        //        var pName = "@v" + Guid.NewGuid().ToString("N");

        //        if (rawValue == null || (rawValue is string && (rawValue as string == string.Empty || rawValue == "\"\"")))
        //        {
        //            pName = "[NULL]";
        //        }
        //        else
        //        {
        //            parameters.Add(pName, rawValue.Substring(1, rawValue.Length - 2));
        //        }

        //        return pName;

        //    }));

        //    query = Regex.Replace(query, @"Convert\((.+?),(.+?)\)", new MatchEvaluator((match) =>
        //    {
        //        return $"{match.Groups[1]}";

        //    }));

        //    query = Regex.Replace(query, @"[a-zA-Z.]* = \[NULL\]", "1 = 1");
        //    query = Regex.Replace(query, @"[a-zA-Z.]* > \[NULL\]", "1 = 1");
        //    query = Regex.Replace(query, @"[a-zA-Z.]* >= \[NULL\]", "1 = 1");
        //    query = Regex.Replace(query, @"[a-zA-Z.]* < \[NULL\]", "1 = 1");
        //    query = Regex.Replace(query, @"[a-zA-Z.]* <= \[NULL\]", "1 = 1");
        //    query = Regex.Replace(query, @"[a-zA-Z.]*\.Contains\(\[NULL\]\)", "1 = 1");
        //    query = Regex.Replace(query, @"[a-zA-Z.]*\.Equals\(\[NULL\]\)", "1 = 1");

        //    query = Regex.Replace(query, @"([@a-zA-Z0-9.]*)\.Contains\((.+?)\)", new MatchEvaluator((match) =>
        //    {
        //        if (string.IsNullOrEmpty(match.Groups[1].Value))
        //            return "1 = 1";

        //        if (match.Groups[2].Value.StartsWith(tableName + "."))
        //            return $"{match.Groups[2]} = ANY({match.Groups[1]})";


        //        var token = match.Groups[2].ToString();
        //        var preparedValue = parameters.Get<string>(token).PrepareQuery();
        //        parameters.Add(token, preparedValue);

        //        return $"{match.Groups[1]} ~* {token}";

        //    }));

        //    query = query.Replace("[NULL]", "");

        //    query = Regex.Replace(query, @"([a-zA-Z.]*)\.Equals\((.+?)\)", new MatchEvaluator((match) =>
        //    {
        //        return $"{match.Groups[1]} = {match.Groups[2]}";
        //    }));

        //    if (query.Contains("Json."))
        //    {
        //        query = Regex.Replace(query, $@"{tableName}\.[a-zA-Z.]+", new MatchEvaluator((match) =>
        //        {
        //            if (!match.Value.Contains("Json.")) return match.Value;

        //            var matchValues = match.Value.Split('.');

        //            var builder = new StringBuilder($"{matchValues[0]}.data");

        //            for (var i = 2; i < matchValues.Length - 1; i++)
        //            {
        //                builder.Append($"->'{matchValues[i].ToLower()}'");
        //            }

        //            builder.Append($"->>'{matchValues.Last().ToLower()}'");

        //            return builder.ToString();

        //        }));
        //    }

        //    if (query == "True" || query == "1 = 1") query = "";

        //    query = query.Trim();

        //    if (query.StartsWith("(") && query.EndsWith(")"))
        //        query = query.Substring(1, query.Length - 2);

        //    if (typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1" || typeof(T).GetProperties().Any(x => x.Name == "TenantId"))
        //    {
        //        if (typeof(T).GetProperties().Any(x => x.Name == "IsActive"))
        //        {
        //            if (string.IsNullOrWhiteSpace(query))
        //                query = $" {(onlyActive ? $"AND {GetTableName<T>()}.IsActive = True" : "")}";
        //            else
        //                query = $"({query}) {(onlyActive ? $"AND {GetTableName<T>()}.IsActive = True" : "")}";
        //        }
        //        else
        //        {
        //            if (string.IsNullOrWhiteSpace(query))
        //                query = $"";
        //            else
        //                query = $"({query}) ";
        //        }
        //    }

        //    if (!string.IsNullOrWhiteSpace(query))
        //        query = " WHERE " + query + " ";
        //    else
        //        query = string.Empty;

        //    return parameters;
        //}

        public DynamicParameters GetWhereQueryFromExpression<T, TId>(string tenantId, System.Linq.Expressions.Expression<Func<T, bool>> expression, out string query, bool onlyActive = true)
        {
            if (string.IsNullOrEmpty(tenantId))
                throw new Exception("Invalid Tenant Id");

            var tableName = GetTableName<T>();
            var parameters = new DynamicParameters();

            parameters.Add("@tenantId", tenantId);

            query = ((LambdaExpression)expression).Body.ToString();

            if (string.IsNullOrWhiteSpace(query) || query == "True")
            {
                if (typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1" || typeof(T).GetProperties().Any(x => x.Name == "TenantId"))
                {
                    if(typeof(T).GetProperties().Any(x => x.Name == "IsActive"))
                        query = $"WHERE {GetTableName<T>()}.TenantId = @tenantId {(onlyActive ? $"AND {GetTableName<T>()}.IsActive = True" : "")}";
                    else
                        query = $"WHERE {GetTableName<T>()}.TenantId = @tenantId";
                }

                return parameters;
            }

            var paramName = expression.Parameters[0].Name;
            //var paramTypeName = expression.Parameters[0].Type.Name;

            query = query.Replace(paramName + ".", tableName + ".")
                             .Replace("== null", "IS NULL")
                             .Replace("!= null", "IS NOT NULL")
                             .Replace("==", "=")
                             .Replace("OrElse", "OR")
                             .Replace("AndAlso", "AND");

            query = Regex.Replace(query, @"value\(.+?\)\.[a-zA-Z]*\.Id", new MatchEvaluator((match) =>
            {
                var rawValue = FindValueRecursive(expression.Body, match.Value);
                var pName = "@v" + Guid.NewGuid().ToString("N");

                if (rawValue == null || (rawValue is string && rawValue as string == string.Empty))
                {
                    pName = "[NULL]";
                }
                else
                {
                    parameters.Add(pName, rawValue);
                }

                return pName;

            }));

            query = Regex.Replace(query, @"value\(.+?\)\.[a-zA-Z]*", new MatchEvaluator((match) =>
            {
                var rawValue = FindValueRecursive(expression.Body, match.Value);
                var pName = "@v" + Guid.NewGuid().ToString("N");

                if (rawValue == null || (rawValue is string && rawValue as string == string.Empty))
                {
                    pName = "[NULL]";
                }
                else
                {
                    if (rawValue.GetType().IsArray)
                    {
                        if ((rawValue as Array).Length == 0)
                            return "[NULL]";

                        var intValues = new List<int>();
                        foreach (var item in (rawValue as Array))
                        {
                            intValues.Add((int)item);
                        }

                        parameters.Add(pName, intValues.ToArray());
                    }
                    else
                    {
                        parameters.Add(pName, rawValue);
                    }
                }

                return pName;

            }));

            query = Regex.Replace(query, @"\""(.+?)\""", new MatchEvaluator((match) =>
            {
                var rawValue = match.Value;
                var pName = "@v" + Guid.NewGuid().ToString("N");

                if (rawValue == null || (rawValue is string && (rawValue as string == string.Empty || rawValue == "\"\"")))
                {
                    pName = "[NULL]";
                }
                else
                {
                    parameters.Add(pName, rawValue.Substring(1, rawValue.Length - 2));
                }

                return pName;

            }));

            query = Regex.Replace(query, @"Convert\((.+?),(.+?)\)", new MatchEvaluator((match) =>
            {
                return $"{match.Groups[1]}";

            }));

            query = Regex.Replace(query, @"[a-zA-Z.]* = \[NULL\]", "1 = 1");
            query = Regex.Replace(query, @"[a-zA-Z.]* > \[NULL\]", "1 = 1");
            query = Regex.Replace(query, @"[a-zA-Z.]* >= \[NULL\]", "1 = 1");
            query = Regex.Replace(query, @"[a-zA-Z.]* < \[NULL\]", "1 = 1");
            query = Regex.Replace(query, @"[a-zA-Z.]* <= \[NULL\]", "1 = 1");
            query = Regex.Replace(query, @"[a-zA-Z.]*\.Contains\(\[NULL\]\)", "1 = 1");
            query = Regex.Replace(query, @"[a-zA-Z.]*\.Equals\(\[NULL\]\)", "1 = 1");

            query = Regex.Replace(query, @"([@a-zA-Z0-9.]*)\.Contains\((.+?)\)", new MatchEvaluator((match) =>
            {
                if (string.IsNullOrEmpty(match.Groups[1].Value))
                    return "1 = 1";

                if (match.Groups[2].Value.StartsWith(tableName + "."))
                    return $"{match.Groups[2]} = ANY({match.Groups[1]})";


                var token = match.Groups[2].ToString();
                var preparedValue = parameters.Get<string>(token).PrepareQuery();
                parameters.Add(token, preparedValue);
               
                return $"{match.Groups[1]} ~* {token}";

            }));

            query = query.Replace("[NULL]", "");

            query = Regex.Replace(query, @"([a-zA-Z.]*)\.Equals\((.+?)\)", new MatchEvaluator((match) =>
            {
                return $"{match.Groups[1]} = {match.Groups[2]}";
            }));

            if (query.Contains("Json."))
            {
                query = Regex.Replace(query, $@"{tableName}\.[a-zA-Z.]+", new MatchEvaluator((match) =>
                {
                    if (!match.Value.Contains("Json.")) return match.Value;

                    var matchValues = match.Value.Split('.');

                    var builder = new StringBuilder($"{matchValues[0]}.data");

                    for (var i = 2; i < matchValues.Length - 1; i++)
                    {
                        builder.Append($"->'{matchValues[i].ToLower()}'");
                    }

                    builder.Append($"->>'{matchValues.Last().ToLower()}'");

                    return builder.ToString();

                }));
            }

            if (query == "True" || query == "1 = 1") query = "";

            query = query.Trim();

            if (query.StartsWith("(") && query.EndsWith(")"))
                query = query.Substring(1, query.Length - 2);

            if (typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1" || typeof(T).GetProperties().Any(x => x.Name == "TenantId"))
            {
                if (typeof(T).GetProperties().Any(x => x.Name == "IsActive")) {
                    if (string.IsNullOrWhiteSpace(query))
                        query = $"{GetTableName<T>()}.TenantId = @tenantId {(onlyActive ? $"AND {GetTableName<T>()}.IsActive = True" : "")}";
                    else
                        query = $"({query}) AND {GetTableName<T>()}.TenantId = @tenantId {(onlyActive ? $"AND {GetTableName<T>()}.IsActive = True" : "")}";
                }
                else
                {
                    if (string.IsNullOrWhiteSpace(query))
                        query = $"{GetTableName<T>()}.TenantId = @tenantId";
                    else
                        query = $"({query}) AND {GetTableName<T>()}.TenantId = @tenantId";
                }
            }

            if (!string.IsNullOrWhiteSpace(query))
                query = " WHERE " + query + " ";
            else
                query = string.Empty;

            return parameters;
        }

        private IDbConnection GetDatabaseConnection()
        {
            return Connection;
        }

        private int GetMaxLength(PropertyInfo propInfo)
        {
            var attr = Attribute.GetCustomAttributes(propInfo).Where(y => y is MaxLengthAttribute).FirstOrDefault();
            if (attr == null) return 50;

            return (attr as MaxLengthAttribute).Length;
        }

        private string GetDisplayName(PropertyInfo propInfo)
        {
            var attr = Attribute.GetCustomAttributes(propInfo).Where(y => y is DisplayNameAttribute).FirstOrDefault();
            if (attr == null) return propInfo.Name;

            return (attr as DisplayNameAttribute).DisplayName;
        }

        public async Task CreateTable<T>()
        {

            var tableName = GetTableName<T>();

            var properties = typeof(T).GetProperties();

            var props = GetNormalProps(typeof(T));

            var propertiesBuilder = new List<string>();
            var uniqueBuilder = new List<string>();

            var idProp = properties.Where(x => x.Name == "Id").FirstOrDefault();

            if (idProp != null)
            {
                if (idProp.PropertyType == typeof(int))
                    propertiesBuilder.Add($"Id SERIAL PRIMARY KEY");
                else
                    propertiesBuilder.Add($"Id varchar(255) PRIMARY KEY UNIQUE");
            }

            foreach (var prop in props)
            {
                if (Attribute.GetCustomAttributes(prop).Any(y => y is UniqueAttribute))
                {
                    if(uniqueBuilder.Count == 0)
                    {
                        uniqueBuilder.Add($"UNIQUE (tenantId");
                    }

                    uniqueBuilder.Add($"{prop.Name}");
                }
                
                var underlyingNullableType = Nullable.GetUnderlyingType(prop.PropertyType);
                var propName = prop.Name;
                
                if (Attribute.GetCustomAttributes(prop).Any(y => y is JsonBAttribute))
                {
                    propertiesBuilder.Add($"{propName} jsonb NULL");
                }
                else if (Attribute.GetCustomAttributes(prop).Any(y => y is TextAttribute))
                {
                    propertiesBuilder.Add($"{propName} TEXT NULL");
                }
                else if (propName != "Id" && propName.EndsWith("Id") && !Attribute.GetCustomAttributes(prop).Any(y => y is IgnoreRelationshipAttribute))
                {
                    var isNullable = (prop.PropertyType.Name == "Nullable`1");
                    var objProp = properties.FirstOrDefault(x => x.Name == propName.Substring(0, propName.Length - 2));
                    var otherTableName = objProp != null ? GetTableName(objProp.PropertyType) : GetTableName(propName.Substring(0, propName.Length - 2));
                    propertiesBuilder.Add($"{propName} {((prop.PropertyType.Name == "Int32" || prop.PropertyType.Name == "Nullable`1") ? "int" : "varchar(255)")} REFERENCES {otherTableName}(id) {(isNullable ? "NULL" : "NOT NULL")}");
                }
                else if (propName != "Id")
                {
                    if (underlyingNullableType?.IsEnum ?? false)
                        propertiesBuilder.Add($"{propName} int NULL");
                    else if(propName == "Lat")
                        propertiesBuilder.Add($"{propName} decimal(10, 8) NOT NULL");
                    else if (propName == "Lng")
                        propertiesBuilder.Add($"{propName} decimal(11, 8) NOT NULL");
                    else if (prop.PropertyType.IsEnum)
                        propertiesBuilder.Add($"{propName} int NOT NULL");
                    else if (prop.PropertyType == typeof(string))
                        propertiesBuilder.Add($"{propName} varchar({GetMaxLength(prop)}) {((!Attribute.GetCustomAttributes(prop).Any(y => y is RequiredAttribute)) ? "NULL" : "NOT NULL")}");
                    else if (prop.PropertyType == typeof(decimal))
                        propertiesBuilder.Add($"{propName} decimal(12, 2) NOT NULL");
                    else if (prop.PropertyType == typeof(decimal?))
                        propertiesBuilder.Add($"{propName} decimal(12, 2) NULL");
                    else if (prop.PropertyType == typeof(DateTime?))
                    {
                        if (prop.GetCustomAttribute<IncludeTimeAttribute>() != null)
                            propertiesBuilder.Add($"{propName} TIMESTAMP NULL");
                        else
                            propertiesBuilder.Add($"{propName} DATE NULL");
                    }
                    else if (prop.PropertyType == typeof(DateTime))
                    {
                        if (prop.GetCustomAttribute<IncludeTimeAttribute>() != null)
                            propertiesBuilder.Add($"{propName} TIMESTAMP NOT NULL");
                        else
                        {
                            if (propName == "Created" || propName == "Modified")
                                propertiesBuilder.Add($"{propName} TIMESTAMP NOT NULL");
                            else
                                propertiesBuilder.Add($"{propName} DATE NOT NULL");
                        }
                    }
                    else if (prop.PropertyType == typeof(int))
                        propertiesBuilder.Add($"{propName} int NOT NULL");
                    else if (prop.PropertyType == typeof(int?))
                        propertiesBuilder.Add($"{propName} int NULL");
                    else if (prop.PropertyType == typeof(bool))
                    {
                        if (propName == "IsActive")
                            propertiesBuilder.Add($"{propName} boolean NOT NULL DEFAULT True");
                        else
                            propertiesBuilder.Add($"{propName} boolean NOT NULL");
                    }
                }
            }

            using (IDbConnection dbConnection = Connection)
            {
                dbConnection.Open();

                var unique = (string.Join(", ", uniqueBuilder));

                if (!string.IsNullOrEmpty(unique))
                    unique = unique + ")";

                if (!string.IsNullOrEmpty(unique))
                    unique = " ," + unique;

                var query = $"CREATE TABLE {tableName} ({(string.Join(", ", propertiesBuilder))} {unique})";
                Console.WriteLine(query);
                //await dbConnection.QueryAsync<T>(query);
            }

        }

        public string PrintQuery(string query, DynamicParameters dynamicParameters)
        {
            //return "";
            foreach (var p in dynamicParameters.ParameterNames)
            {
                var val = dynamicParameters.Get<object>(p);

                if (val == null)
                    query = query.Replace("@" + p, "NULL");
                else if (val is string)
                    query = query.Replace("@" + p, $"'{val}'");
                else if (val is Int32[])
                    query = query.Replace("@" + p, $"ARRAY[{string.Join(",", (Int32[])val)}]");
                else
                    query = query.Replace("@" + p, val.ToString());
            }

            //Console.WriteLine(query);
            return query;
        }

        private int GetIndex(ref List<JoinInfo> joinInfo, Type type)
        {
            var index = 0;
            foreach (var info in joinInfo)
            {
                if (info.Type == type)
                    return index + 1;
            }

            return 0;
        }

        private void GetJoinInfo(string tableName, Type type, ref List<JoinInfo> joinInfo, bool includeMany = true)
        {
            var idProps = GetIdProps(type);
            var navProps = GetNavProps(type);
            var listProps = GetListProps(type);

            foreach (var navProp in navProps)
            {
                var otherTable = GetTableName(navProp.PropertyType);
                var idProp = idProps.FirstOrDefault(x => x.Name == navProp.Name + "Id");

                if (idProp != null) //One to many
                {
                    //if (joinInfo.Any(x => x.Join.StartsWith($"LEFT JOIN {otherTable} ON ")))
                    //joinInfo.Remove(joinInfo.First(x => x.Join.StartsWith($"LEFT JOIN {otherTable} ON ")));

                    var aliasTableName = "t" + Guid.NewGuid().ToString("N");


                    if(tableName != "userroles")
                        joinInfo.Add(new JoinInfo()
                            {
                                IsList = false,
                                ParentType = type,
                                Join = $"LEFT JOIN {otherTable} AS {aliasTableName} ON {aliasTableName}.Id = {tableName}.{idProp.Name}",
                                Property = navProp,
                                Type = navProp.PropertyType
                            });
                }

                else
                {
                    //if (joinInfo.Any(x => x.Join.StartsWith($"LEFT JOIN {otherTable} ON ")))
                    //joinInfo.Remove(joinInfo.First(x => x.Join.StartsWith($"LEFT JOIN {otherTable} ON ")));

                    var aliasTableName = "t" + Guid.NewGuid().ToString("N");

                    joinInfo.Add(new JoinInfo()  //one to one
                        {
                            IsList = false,
                            ParentType = type,
                            Join = $"LEFT JOIN {otherTable} AS {aliasTableName} ON {tableName}.Id = {aliasTableName}.{type.Name}Id",
                            Property = navProp,
                            Type = navProp.PropertyType
                        });
                }

                
                GetJoinInfo(otherTable, navProp.PropertyType, ref joinInfo, includeMany);

            }

            if (includeMany)
            {
                foreach (var listProp in listProps)
                {
                    var otherTable = GetTableName(listProp.PropertyType.GenericTypeArguments[0]);
                    var listItemType = listProp.PropertyType.GenericTypeArguments[0];

                    //if (joinInfo.Any(x => x.Join.StartsWith($"LEFT JOIN {otherTable} ON ")))
                    //joinInfo.Remove(joinInfo.First(x => x.Join.StartsWith($"LEFT JOIN {otherTable} ON ")));

                    var aliasTableName = "t" + Guid.NewGuid().ToString("N");

                    joinInfo.Add(new JoinInfo()
                        {
                            IsList = true,
                            ParentType = type,
                            Join = $"LEFT JOIN {otherTable} AS {aliasTableName} ON {tableName}.Id = {aliasTableName}.{type.Name}Id",
                            Property = listProp,
                            Type = listItemType
                        });

                    
                        GetJoinInfo(otherTable, listProp.PropertyType.GenericTypeArguments[0], ref joinInfo, includeMany);
                }
            }
        }


        private object FindValueRecursive(Expression expression, string bodyValue)
        {
            if (expression is System.Linq.Expressions.BinaryExpression)
            {
                var resultOnLeft = FindValueRecursive((expression as System.Linq.Expressions.BinaryExpression).Left, bodyValue);
                var resultOnRight = FindValueRecursive((expression as System.Linq.Expressions.BinaryExpression).Right, bodyValue);

                if (resultOnLeft != null) return resultOnLeft;
                if (resultOnRight != null) return resultOnRight;
            }
            else if (expression is System.Linq.Expressions.MemberExpression)
            {
                var exp = expression as System.Linq.Expressions.MemberExpression;

                if (bodyValue.Contains("Convert("))
                {
                    bodyValue = bodyValue.Substring(8);
                    bodyValue = bodyValue.Substring(0, bodyValue.Length - 1);
                }

                if (exp.ToString() == (bodyValue))
                {
                    var result = (exp.Member as FieldInfo) != null ?
                        (exp.Member as FieldInfo).GetValue((exp.Expression as ConstantExpression).Value) :
                        (exp.Member as PropertyInfo).GetValue(((exp.Expression as MemberExpression).Expression as ConstantExpression).Value);

                    if (result != null)
                        return result;
                }
                return null;

                //var result = FindValueRecursive(exp.Expression, bodyValue);
                //if (result != null)
                //    return result;

            }
            else if (expression is System.Linq.Expressions.MethodCallExpression)
            {
                var exp = expression as System.Linq.Expressions.MethodCallExpression;
                var result = FindValueRecursive(exp.Arguments[0], bodyValue);
                if (result != null)
                    return result;
            }

            else if (expression is System.Linq.Expressions.UnaryExpression)
            {
                var exp = expression as System.Linq.Expressions.UnaryExpression;
                var result = FindValueRecursive(exp.Operand, bodyValue);
                if (result != null)
                    return result;
            }
            //else if(expression is System.Linq.Expressions.ConstantExpression)
            //{
            //    if (expression.ToString().StartsWith(bodyValue))
            //    {

            //        return (expression as System.Linq.Expressions.ConstantExpression).Value;
            //    }

            //}


            return null;

        }

        public async Task<MemoryStream> GetExcelFromData<T, TId>(IEnumerable<T> items, int offset) where T : RepositoryEntity<TId>
        {
            var memoryStream = new MemoryStream();

            using (var excelPackage = new ExcelPackage())
            {
                var ws = excelPackage.Workbook.Worksheets.Add("Datos");
                var props = GetExcelProps(typeof(T), out string idDisplayName);

                var col = 1;
                foreach (var prop in props)
                {
                    if (prop.Name == "TenantId") continue;

                    if (prop.Name == "Id" && !string.IsNullOrEmpty(idDisplayName))
                    {
                        ws.Cells[1, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        ws.SetValue(1, col, idDisplayName);
                    }
                    else
                    {
                        ws.Cells[1, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        ws.SetValue(1, col, GetDisplayName(prop));
                    }

                    col++;
                }

                var row = 2;

                foreach (var item in items)
                {
                    col = 1;

                    foreach (var prop in props)
                    {
                        if (prop.Name == "TenantId") continue;

                        object value = null;

                        if (prop.PropertyType.IsClass
                          && prop.PropertyType != typeof(string)
                          && prop.PropertyType != typeof(DateTime)
                          && prop.PropertyType != typeof(DateTime?))
                        {
                            var displayColumn = "Id";
                            var displayColumnAttribute = prop.PropertyType.GetCustomAttribute<DisplayColumnAttribute>();

                            if (displayColumnAttribute != null)
                                displayColumn = displayColumnAttribute.DisplayColumn;

                            var subProp = prop.PropertyType.GetProperties().FirstOrDefault(x => x.Name == displayColumn);

                            if (subProp != null)
                            {
                                try
                                {
                                    var subValue = prop.GetValue(item);

                                    if (subValue != null)
                                        value = subProp.GetValue(subValue);
                                }
                                catch (Exception ex)
                                {
                                    value = "Error";
                                }
                            }
                            else
                                value = "-- Invalid column --";

                        }
                        else
                            value = prop.GetValue(item);

                        string formattedValue = null;

                        if (formattedValue == null)
                        {
                            var propertyType = prop.PropertyType;

                            Type u = Nullable.GetUnderlyingType(propertyType);
                            if (u != null)
                                propertyType = u;

                            if (value == null)
                                ws.SetValue(row, col, "");
                            else if (value is bool)
                                ws.SetValue(row, col, (bool)value ? "Sí" : "No");
                            else if (value is DateTime)
                            {
                                if (prop.Name == "Created" ||
                                    prop.Name == "Modified" ||
                                    prop.Name == "CostModified" || //TODO
                                    propertyType.GetCustomAttribute<IncludeTimeAttribute>() != null)
                                    ws.SetValue(row, col, (((DateTime)value).AddMinutes(-offset)).ToString("dd/MM/yyyy HH:mm:ss"));
                                else
                                    ws.SetValue(row, col, ((DateTime)value).ToString("dd/MM/yyyy"));
                            }
                            else
                            {
                                if (propertyType.IsEnum)
                                    ws.SetValue(row, col, GetEnumDisplayName((Enum)Enum.Parse(propertyType, value.ToString())));
                                else
                                    ws.SetValue(row, col, value);
                            }
                        }
                        else
                            ws.SetValue(row, col, formattedValue);

                        col++;

                    }

                    row++;
                }

                for (var i = 1; i < col; i++)
                {
                    ws.Column(i).AutoFit();
                }

                excelPackage.SaveAs(memoryStream);
            }

            memoryStream.Position = 0;
            return memoryStream;
        }

        public async Task<MemoryStream> GetExcel<T, TId>(FindResults<T> results, int offset, Func<PropertyInfo, T, object, string> valueFormatter = null) where T : RepositoryEntity<TId>
        {
            var memoryStream = new MemoryStream();

            using (var excelPackage = new ExcelPackage())
            {
                var ws = excelPackage.Workbook.Worksheets.Add("Datos");
                var props = GetExcelProps(typeof(T), out string idDisplayName);

                var col = 1;
                foreach (var prop in props)
                {
                    if (prop.Name == "TenantId") continue;

                    if (prop.Name == "Id" && !string.IsNullOrEmpty(idDisplayName))
                    {
                        ws.Cells[1, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        ws.SetValue(1, col, idDisplayName);
                    }
                    else
                    {
                        ws.Cells[1, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        ws.SetValue(1, col, GetDisplayName(prop));
                    }

                    col++;
                }

                var row = 2;

                foreach (var item in results.Results)
                {
                    col = 1;

                    foreach (var prop in props)
                    {
                        if (prop.Name == "TenantId") continue;

                        object value = null;

                        if (prop.PropertyType.IsClass
                          && prop.PropertyType != typeof(string)
                          && prop.PropertyType != typeof(DateTime)
                          && prop.PropertyType != typeof(DateTime?))
                        {
                            var displayColumn = "Id";
                            var displayColumnAttribute = prop.PropertyType.GetCustomAttribute<DisplayColumnAttribute>();

                            if (displayColumnAttribute != null)
                                displayColumn = displayColumnAttribute.DisplayColumn;

                            var subProp = prop.PropertyType.GetProperties().FirstOrDefault(x => x.Name == displayColumn);

                            if (subProp != null)
                            {
                                try
                                {
                                    var subValue = prop.GetValue(item);

                                    if (subValue != null)
                                        value = subProp.GetValue(subValue);
                                }
                                catch (Exception ex)
                                {
                                    value = "Error";
                                }
                            }
                            else
                                value = "-- Invalid column --";

                        }
                        else
                            value = prop.GetValue(item);

                        string formattedValue = null;

                        if (valueFormatter != null)
                            formattedValue = valueFormatter(prop, item, value);

                        if (formattedValue == null)
                        {
                            var propertyType = prop.PropertyType;

                            Type u = Nullable.GetUnderlyingType(propertyType);
                            if (u != null)
                                propertyType = u;

                            if (value == null)
                                ws.SetValue(row, col, "");
                            else if (value is bool)
                                ws.SetValue(row, col, (bool)value ? "Sí" : "No");
                            else if (value is DateTime)
                            {
                                if (prop.Name == "Created" ||
                                    prop.Name == "Modified" ||
                                    prop.Name == "CostModified" || //TODO
                                    propertyType.GetCustomAttribute<IncludeTimeAttribute>() != null)
                                    ws.SetValue(row, col, (((DateTime)value).AddMinutes(-offset)).ToString("dd/MM/yyyy HH:mm:ss"));
                                else
                                    ws.SetValue(row, col, ((DateTime)value).ToString("dd/MM/yyyy"));
                            }
                            else
                            {
                                if (propertyType.IsEnum)
                                    ws.SetValue(row, col, GetEnumDisplayName((Enum)Enum.Parse(propertyType, value.ToString())));
                                else
                                    ws.SetValue(row, col, value);
                            }
                        }
                        else
                            ws.SetValue(row, col, formattedValue);

                        col++;

                    }

                    row++;
                }

                for (var i = 1; i < col; i++)
                {
                    ws.Column(i).AutoFit();
                }

                excelPackage.SaveAs(memoryStream);
            }

            memoryStream.Position = 0;
            return memoryStream;
        }

        public async Task<MemoryStream> GetExcel<T, TId>(string tenantId, int offset, Expression<Func<T, bool>> expression, 
        string sortBy = "", string sortOrder = "asc", 
        Func<IDbConnection, string, DynamicParameters, Task<object>> GetCustomData = null, 
        string joinsQuery = "", Func<IDbConnection, string, DynamicParameters, Task<IEnumerable<T>>> MappingFunc = null,
        Func<PropertyInfo, T, object, string> valueFormatter = null) where T : RepositoryEntity<TId>
        {
            var results = await Find<T, TId>(tenantId, expression, 0, 5000, sortBy, sortOrder, GetCustomData, joinsQuery, MappingFunc);   

            var memoryStream = new MemoryStream();

            using (var excelPackage = new ExcelPackage())
            {
                var ws = excelPackage.Workbook.Worksheets.Add("Datos");
                var props = GetExcelProps(typeof(T), out string idDisplayName);

                var col = 1;
                foreach (var prop in props)
                {
                    if (prop.Name == "TenantId") continue;

                    if(prop.Name == "Id" && !string.IsNullOrEmpty(idDisplayName))
                    {
                        ws.Cells[1, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        ws.SetValue(1, col, idDisplayName);
                    }
                    else
                    {
                        ws.Cells[1, col].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        ws.SetValue(1, col, GetDisplayName(prop));
                    }
                    
                    col++;
                }

                var row = 2;

                foreach (var item in results.Results)
                {
                    col = 1;

                    foreach (var prop in props)
                    {
                        if (prop.Name == "TenantId") continue;

                        object value = null;

                        if (prop.PropertyType.IsClass
                          && prop.PropertyType != typeof(string)
                          && prop.PropertyType != typeof(DateTime)
                          && prop.PropertyType != typeof(DateTime?))
                        {
                            var displayColumn = "Id";
                            var displayColumnAttribute = prop.PropertyType.GetCustomAttribute<DisplayColumnAttribute>();

                            if (displayColumnAttribute != null)
                                displayColumn = displayColumnAttribute.DisplayColumn;

                            var subProp = prop.PropertyType.GetProperties().FirstOrDefault(x => x.Name == displayColumn);

                            if (subProp != null)
                            {
                                try
                                {
                                    var subValue = prop.GetValue(item);

                                    if(subValue != null)
                                        value = subProp.GetValue(subValue);
                                }
                                catch(Exception ex)
                                {
                                    value = "Error";
                                }
                            }
                            else
                                value = "-- Invalid column --";
                            
                        }
                        else
                            value = prop.GetValue(item);

                        string formattedValue = null;

                        if (valueFormatter != null)
                            formattedValue = valueFormatter(prop, item, value);
                        
                        if(formattedValue == null)
                        {
                            var propertyType = prop.PropertyType;

                            Type u = Nullable.GetUnderlyingType(propertyType);
                            if (u != null)
                                propertyType = u;

                            if (value == null)
                                ws.SetValue(row, col, "");
                            else if (value is bool)
                                ws.SetValue(row, col, (bool)value ? "Sí" : "No");
                            else if (value is DateTime)
                            {
                                if (prop.Name == "Created" || 
                                    prop.Name == "Modified" || 
                                    prop.Name == "CostModified" || //TODO
                                    propertyType.GetCustomAttribute<IncludeTimeAttribute>() != null)
                                    ws.SetValue(row, col, (((DateTime)value).AddMinutes(-offset)).ToString("dd/MM/yyyy HH:mm:ss"));
                                else
                                    ws.SetValue(row, col, ((DateTime)value).ToString("dd/MM/yyyy"));
                            }
                            else
                            {
                                if (propertyType.IsEnum)
                                    ws.SetValue(row, col, GetEnumDisplayName((Enum)Enum.Parse(propertyType, value.ToString())));
                                else
                                    ws.SetValue(row, col, value);
                            }
                        }
                        else
                            ws.SetValue(row, col, formattedValue);

                        col++;

                    }

                    row++;
                }

                for(var i = 1; i < col; i++)
                {
                    ws.Column(i).AutoFit();
                }

                excelPackage.SaveAs(memoryStream);
            }

            memoryStream.Position = 0;
            return memoryStream;
            
        }

        public static bool IsNullableEnum(Type t)
        {
            Type u = Nullable.GetUnderlyingType(t);
            return (u != null) && u.IsEnum;
        }

        public async Task<FindResults<T>> CustomFind<T, TId>(string tenantId, string resultsQuery, string totalQuery,
             DynamicParameters dynamicParameters, int? skip = null, int? take = null, string sortBy = "", string sortOrder = "asc",
             Func<IDbConnection, string, DynamicParameters, Task<IEnumerable<T>>> ResultsMappingFunc = null,
             Func<IDbConnection, string, DynamicParameters, Task<dynamic>> TotalMappingFunc = null)
        {

            var tableName = GetTableName<T>();

            if (!typeof(T).GetProperties().Any(x => x.Name?.ToLower() == sortBy?.ToLower()))
                sortBy = "";

            if (!string.IsNullOrEmpty(sortBy) && !sortBy.Contains("."))
                sortBy = $"{tableName}.{sortBy}";

            resultsQuery = $"{resultsQuery} " +
                    (!string.IsNullOrEmpty(sortBy) ? $"ORDER BY {sortBy} {sortOrder} " : "") +
                    (take != null ? $"LIMIT {take} " : "") +
                    (skip != null ? $"OFFSET {skip} " : "");

            PrintQuery(resultsQuery, dynamicParameters);
            PrintQuery(totalQuery, dynamicParameters);

            using (IDbConnection dbConnection = Connection)
            {
                dbConnection.Open();

                var customData = await TotalMappingFunc(dbConnection, totalQuery, dynamicParameters);

                //Console.WriteLine(JsonConvert.SerializeObject(customData));

                return new FindResults<T>()
                {
                    CustomData = customData,
                    Results = await ResultsMappingFunc(dbConnection, resultsQuery, dynamicParameters),
                    Total = customData.total
                };

            }
        }

        public async Task<FindResults<T>> Find<T, TId>(string tenantId, Expression<Func<T, bool>> expression, int? skip = null, int? take = null,
            string sortBy = "", string sortOrder = "asc", Func<IDbConnection, string, DynamicParameters, Task<object>> GetCustomData = null,
            string joinsQuery = "", Func<IDbConnection, string, DynamicParameters, Task<IEnumerable<T>>> MappingFunc = null, bool onlyActive = true) where T : RepositoryEntity<TId>
        {
            if (!string.IsNullOrEmpty(joinsQuery) && MappingFunc == null)
                throw new Exception("If JoinsQuery is not null, you have to also specify the MappingFunc parameter");

            if (string.IsNullOrEmpty(tenantId))
                throw new Exception("Invalid tenantId");

            var tableName = GetTableName<T>();

            var whereQuery = "";
            var dynamicParameters = GetWhereQueryFromExpression<T, TId>(tenantId, expression, out whereQuery, onlyActive);

            //Console.WriteLine(whereQuery);

            if (!string.IsNullOrEmpty(sortBy))
            {
                if (sortBy.Contains("."))
                {
                    var spl = sortBy.Split('.');
                    sortBy = string.Join("->", spl.Select(x => x.EndsWith("Json") ? "data" : $"'{x}'").ToArray());
                }
                else
                {
                    if (!typeof(T)
                         .GetProperties()
                         .Any(x => x.Name.ToLower() == sortBy.ToLower()))
                        sortBy = "";
                    else sortBy = $"{GetTableName<T>()}.{sortBy}";
                }
            }
            else sortBy = $"{tableName}.id";

            sortOrder = sortOrder ?? "ASC";
            sortOrder = sortOrder.ToLower() == "asc" ? "ASC" : "DESC";

            var joinInfoList = new List<JoinInfo>();
            var joinInfo = joinsQuery;

            if (string.IsNullOrEmpty(joinsQuery))
            {
                GetJoinInfo(tableName, typeof(T), ref joinInfoList, (skip == null && take == null));
                joinInfo = (string.Join(" ", joinInfoList.Select(x => x.Join).ToArray()));
            }

            var listProps = GetListProps(typeof(T));

            using (IDbConnection dbConnection = Connection)
            {
                dbConnection.Open();

                //TODO: Quitar esto
                var replace = false;
                if (whereQuery.Contains("orders.Branch.ClientBrandId.Value"))
                {
                    replace = true;
                    whereQuery = whereQuery.Replace("orders.Branch.ClientBrandId.Value", "clientbrands.id");
                }

                var totalQuery = $"SELECT COUNT(*) FROM {tableName} {whereQuery}";
               
                if(replace)
                {
                    totalQuery = $"SELECT COUNT(*) FROM {tableName} LEFT JOIN clientbranches ON clientbranches.id = orders.branchid LEFT JOIN clientbrands ON clientbrands.id = clientbranches.clientbrandid {whereQuery}"; 
                }


                var resultsQuery = $"SELECT * FROM {tableName} {joinInfo} {whereQuery} " +
                (!string.IsNullOrEmpty(sortBy) ? $"ORDER BY {sortBy} {sortOrder} " : "") +
                (take != null ? $"LIMIT {take} " : "") +
                (skip != null ? $"OFFSET {skip} " : "");

                Console.WriteLine(resultsQuery);

                var orderDictionary = new Dictionary<TId, T>();
                var types = joinInfoList.Select(x => x.Type).Prepend(typeof(T)).ToArray();

                PrintQuery(resultsQuery, dynamicParameters);

                object customData = null;

                if (GetCustomData != null) 
                    customData = await GetCustomData(dbConnection, whereQuery, dynamicParameters);

                return new FindResults<T>()
                {
                    CustomData = customData,
                    Results = MappingFunc != null ? (await MappingFunc(dbConnection, resultsQuery, dynamicParameters)) :
                    (await dbConnection.QueryAsync<T>(resultsQuery, types, (x) =>
                    {
                        T itemToReturn = (T)x.First();

                        var itemAlreadyInDictionary = true;

                        if (!orderDictionary.TryGetValue(itemToReturn.Id, out itemToReturn))
                        {
                            itemAlreadyInDictionary = false;
                            itemToReturn = (T)x.First();

                            foreach (var listProp in listProps)
                            {
                                var IListRef = typeof(List<>);
                                Type[] IListParam = { listProp.PropertyType.GenericTypeArguments[0] };
                                var list = Activator.CreateInstance(IListRef.MakeGenericType(IListParam));
                                listProp.SetValue(itemToReturn, list);
                            }

                            orderDictionary.Add(itemToReturn.Id, itemToReturn);
                        }

                        //itemToReturn.OrderDetails.Add(orderDetail);

                        var index = 0;

                        foreach (var info in joinInfoList)
                        {
                            var itemIndex = Array.IndexOf(types, info.ParentType);
                            var item = x[itemIndex];

                            if (itemAlreadyInDictionary && itemIndex == 0)
                                item = itemToReturn;

                            if (info.Property.PropertyType.Name.StartsWith("List"))
                            {
                                var list = info.Property.GetValue(item);

                                if(list != null)
                                    info.Property.PropertyType.GetMethod("Add").Invoke(list, new[] { x[index + 1] });
                            }
                            //else if(!itemAlreadyInDictionary)
                            else
                            {
                                info.Property.SetValue(item, x[index + 1]);
                            }

                            index++;
                        }

                        return itemToReturn;

                    }, dynamicParameters))
                        .Distinct()
                        .ToList(),
                    Total = (skip != null && take != null) ? await dbConnection.ExecuteScalarAsync<long>(totalQuery, dynamicParameters) : 0,
                };

            }

        }

        public async Task<T> TryFindOneById<T, TId>(string tenantId, TId id) where T : RepositoryEntity<TId>
        {
            var results = await Find<T, TId>(tenantId, x => x.Id.Equals(id));
            return results.Results.FirstOrDefault();
        }

        public async Task<T> FindOneById<T, TId>(string tenantId, TId id) where T : RepositoryEntity<TId>
        {
            var results = await Find<T, TId>(tenantId, x => x.Id.Equals(id));
            return results.Results.First();
        }

        private void GetQueries<TId>(string tenantId, Type type, string parentId, object item, ref DynamicParameters dynamicParams, ref List<string> operations)
        {
            if (type.BaseType.Name == "RepositoryEntityWithTenantId`1")
                (item as RepositoryEntityWithTenantId<TId>).TenantId = tenantId;

            var navProps = GetNavProps(type);

            var listProps = GetListProps(type);

            if (navProps.Length > 0 || listProps.Length > 0)
            {
                foreach (var navProp in navProps)
                {
                    var navItem = navProp.GetValue(item);

                    if (navItem == null) continue;

                    if (navItem.GetType().BaseType.Name == "RepositoryEntityWithTenantId`1")
                        (navItem as RepositoryEntityWithTenantId<TId>).TenantId = tenantId;

                    var returnIntoId = navProp.Name + "Id";

                    var otherTable = GetTableName(navProp.PropertyType);

                    var otherTableProps = GetNormalProps(navProp.PropertyType);

                    var listItemValues = new List<string>();

                    foreach (var otherTableProp in otherTableProps)
                    {
                        if (otherTableProp.Name == type.Name + "Id")
                        {
                            listItemValues.Add($"(SELECT * FROM {parentId})");
                        }
                        else
                        {
                            var listItemValue = $"@{parentId}_{navProp.Name}_{otherTableProp.Name}";
                            listItemValues.Add(listItemValue);
                            dynamicParams.Add(listItemValue, navItem != null ? otherTableProp.GetValue(navItem) : null);
                        }

                    }

                    operations.Add($"{parentId}_{returnIntoId} AS (INSERT INTO {otherTable} ({string.Join(",", otherTableProps.Select(x => x.Name).ToArray())}) VALUES({string.Join(", ", listItemValues.ToArray())}) RETURNING Id)");
                    GetQueries<TId>(tenantId, navProp.PropertyType, $"{parentId}_{returnIntoId}", item, ref dynamicParams, ref operations);
                }

                foreach (var listProp in listProps)
                {

                    var list = (IEnumerable)listProp.GetValue(item);

                    if (list == null) continue;

                    var returnIntoId = listProp.Name + "Id";

                    var otherTable = GetTableName(listProp.PropertyType.GenericTypeArguments[0]);
                    var otherTableProps = GetNormalProps(listProp.PropertyType.GenericTypeArguments[0]);

                    var itemIndex = 1;
                    foreach (var listItem in list)
                    {

                        if (listItem == null) continue;

                        if (listItem.GetType().BaseType.Name == "RepositoryEntityWithTenantId`1")
                            (listItem as RepositoryEntityWithTenantId<TId>).TenantId = tenantId;

                        bool hasNavProperty = false;
                        var listItemValues = new List<string>();

                        foreach (var otherTableProp in otherTableProps)
                        {
                            if (otherTableProp.Name == type.Name + "Id")
                            {
                                hasNavProperty = true;
                                listItemValues.Add($"(SELECT * FROM {parentId})");
                            }
                            else
                            {
                                var listItemValue = $"@{parentId}_{listProp.Name}_{otherTableProp.Name}_{itemIndex}";
                                listItemValues.Add(listItemValue);
                                dynamicParams.Add(listItemValue, otherTableProp.GetValue(listItem));
                            }


                        }



                        operations.Add($"{parentId}_{returnIntoId}_{itemIndex} AS (INSERT INTO {otherTable} ({string.Join(",", otherTableProps.Select(x => x.Name).ToArray())}) VALUES({string.Join(", ", listItemValues.ToArray())}) RETURNING Id)");

                        if (!hasNavProperty)
                        {

                            operations.Add($"I{Guid.NewGuid().ToString("N")} AS (INSERT INTO {GetTableName(type)}_{otherTable} ({type.Name}Id, {listProp.PropertyType.GenericTypeArguments[0].Name}Id) VALUES((SELECT * FROM {returnIntoId}), (SELECT * FROM {parentId}_{itemIndex}_{returnIntoId})) RETURNING Id)");
                        }

                        GetQueries<TId>(tenantId, listProp.PropertyType.GenericTypeArguments[0], $"{parentId}_{returnIntoId}_{itemIndex}", listItem, ref dynamicParams, ref operations);

                        itemIndex++;
                    }
                }

            }
        }

        public async Task<T> Insert<T, TId>(string tenantId, int? createdById, T item, bool nested = true) where T : RepositoryEntity<TId>
        {
            if (string.IsNullOrEmpty(tenantId))
                throw new Exception("Invalid tenantId");

            if (typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1")
            {
                var now = DateTime.UtcNow;

                (item as RepositoryEntityWithTenantId<TId>).Created = now;
                (item as RepositoryEntityWithTenantId<TId>).CreatedById = createdById;
                (item as RepositoryEntityWithTenantId<TId>).Modified = now;
                (item as RepositoryEntityWithTenantId<TId>).ModifiedById = createdById;
                (item as RepositoryEntityWithTenantId<TId>).TenantId = tenantId;
                (item as RepositoryEntityWithTenantId<TId>).IsActive = true;
            }

            using (IDbConnection dbConnection = Connection)
            {
                var props = GetNormalProps(typeof(T));

                var propertyNames = props.Select(x => x.Name).ToList();

                dbConnection.Open();

                var navProps = GetNavProps(typeof(T))
                    .Where(x => x.Name != "CreatedBy")
                    .Where(x => x.Name != "ModifiedBy")
                    .Where(x => x.Name != "CostModifiedBy") //TODO: 
                    .ToArray();

                var listProps = GetListProps(typeof(T));

                TId newId;

                if ((navProps.Length > 0 || listProps.Length > 0) && nested)
                {
                    var dynamicParams = new DynamicParameters();

                    var operations = new List<string>();

                    var parentId = typeof(T).Name + "Id";
                    var returnIdVarName = parentId;

                    operations.Add($"WITH {returnIdVarName} AS (INSERT INTO {GetTableName<T>()} ({string.Join(",", propertyNames)}) VALUES({string.Join(",", propertyNames.Select(x => "@" + x).ToArray())}) RETURNING Id)");

                    foreach (var prop in props)
                    {
                        dynamicParams.Add("@" + prop.Name, prop.GetValue(item));
                    }

                    foreach (var navProp in navProps)
                    {
                        var navItem = navProp.GetValue(item);

                        if (navItem == null) continue;

                        if (navItem.GetType().BaseType.Name == "RepositoryEntityWithTenantId`1")
                            (navItem as RepositoryEntityWithTenantId<TId>).TenantId = tenantId;

                        var returnIntoId = navProp.Name + "Id";

                        var otherTable = GetTableName(navProp.PropertyType);

                        var otherTableProps = GetNormalProps(navProp.PropertyType);

                        var listItemValues = new List<string>();

                        foreach (var otherTableProp in otherTableProps)
                        {
                            if (otherTableProp.Name == parentId)
                            {
                                listItemValues.Add($"(SELECT * FROM {parentId})");
                            }
                            else
                            {
                                var listItemValue = $"@{navProp.Name}_{otherTableProp.Name}";
                                listItemValues.Add(listItemValue);
                                dynamicParams.Add(listItemValue, navItem != null ? otherTableProp.GetValue(navItem) : null);
                            }

                        }

                        operations.Add($"{parentId}_{returnIntoId} AS (INSERT INTO {otherTable} ({string.Join(",", otherTableProps.Select(x => x.Name).ToArray())}) VALUES({string.Join(", ", listItemValues.ToArray())}) RETURNING Id)");
                        GetQueries<TId>(tenantId, navProp.PropertyType, $"{parentId}_{returnIntoId}", navItem, ref dynamicParams, ref operations);
                    }

                    foreach (var listProp in listProps)
                    {

                        var list = (IEnumerable)listProp.GetValue(item);

                        if (list == null) continue;

                        var returnIntoId = listProp.Name + "Id";

                        var otherTable = GetTableName(listProp.PropertyType.GenericTypeArguments[0]);
                        var otherTableProps = GetNormalProps(listProp.PropertyType.GenericTypeArguments[0]);

                        var itemIndex = 1;
                        foreach (var listItem in list)
                        {
                            if (listItem == null) continue;

                            if (listItem.GetType().BaseType.Name == "RepositoryEntityWithTenantId`1")
                                (listItem as RepositoryEntityWithTenantId<TId>).TenantId = tenantId;

                            var listItemValues = new List<string>();
                            bool hasNavProperty = false;

                            foreach (var otherTableProp in otherTableProps)
                            {
                                if (otherTableProp.Name == parentId)
                                {
                                    hasNavProperty = true;
                                    listItemValues.Add($"(SELECT * FROM {parentId})");
                                }
                                else
                                {
                                    var listItemValue = $"@{listProp.Name}_{otherTableProp.Name}_{itemIndex}";
                                    listItemValues.Add(listItemValue);
                                    dynamicParams.Add(listItemValue, otherTableProp.GetValue(listItem));
                                }
                            }

                            operations.Add($"{parentId}_{itemIndex}_{returnIntoId} AS (INSERT INTO {otherTable} ({string.Join(",", otherTableProps.Select(x => x.Name).ToArray())}) VALUES({string.Join(", ", listItemValues.ToArray())}) RETURNING Id)");

                            if (!hasNavProperty)
                            {
                                operations.Add($"I{Guid.NewGuid().ToString("N")} AS (INSERT INTO {GetTableName<T>()}_{otherTable} ({typeof(T).Name}Id, {listProp.PropertyType.GenericTypeArguments[0].Name}Id) VALUES((SELECT * FROM {returnIdVarName}), (SELECT * FROM {parentId}_{itemIndex}_{returnIntoId})) RETURNING Id)");
                            }

                            GetQueries<TId>(tenantId, listProp.PropertyType.GenericTypeArguments[0], $"{parentId}_{itemIndex}_{returnIntoId}", listItem, ref dynamicParams, ref operations);

                            itemIndex++;
                        }
                    }

                    var sqlQuery = $@"
        {string.Join(", ", operations)}
        	SELECT * FROM {parentId}
        ";

                    PrintQuery(sqlQuery, dynamicParams);

                    newId = await dbConnection.ExecuteScalarAsync<TId>(sqlQuery, dynamicParams);
                }
                else
                {
                    var query = $"INSERT INTO {GetTableName<T>()} ({string.Join(",", propertyNames)}) VALUES({string.Join(",", props.Select(x => Attribute.GetCustomAttributes(x).Any(y => y is JsonBAttribute) ? $"'{x.GetValue(item)}'" : $"@{x.Name}").ToArray())}) RETURNING Id;";
                    var parameters = new DynamicParameters();

                    foreach (var p in props)
                    {
                        parameters.Add(p.Name, p.GetValue(item));
                    }

                    PrintQuery(query, parameters);

                    newId = await dbConnection.ExecuteScalarAsync<TId>(query, parameters);
                }

                item.Id = newId;

                return item;
            }

        }

        public string GetInsertQuery<T, TId>(string tenantId, int createdById, T item, ref DynamicParameters parameters, string returningIdInto = null) where T : RepositoryEntity<TId>
        {
            var props = GetNormalProps(typeof(T));
            var dict = new Dictionary<string, string>();

            foreach(var prop in props)
            {
                dict.Add(prop.Name, "@v" + Guid.NewGuid().ToString("N"));
            }

            var propertyNames = props.Select(x => x.Name).ToList();

            if (typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1")
            {
                var now = DateTime.UtcNow;

                (item as RepositoryEntityWithTenantId<TId>).TenantId = tenantId;
                (item as RepositoryEntityWithTenantId<TId>).CreatedById = createdById;
                (item as RepositoryEntityWithTenantId<TId>).IsActive = true;
                (item as RepositoryEntityWithTenantId<TId>).Created = now;
                (item as RepositoryEntityWithTenantId<TId>).Modified = now;
                (item as RepositoryEntityWithTenantId<TId>).ModifiedById = createdById;
            }

            var query = $"INSERT INTO {GetTableName<T>()} ({string.Join(",", propertyNames)}) VALUES({string.Join(",", props.Select(x => Attribute.GetCustomAttributes(x).Any(y => y is JsonBAttribute) ? $"'{x.GetValue(item)}'" : $"{dict[x.Name]}").ToArray())}) {(!string.IsNullOrEmpty(returningIdInto) ? $"RETURNING Id;" : ";")}";

            foreach (var prop in props)
            {
                parameters.Add(dict[prop.Name], prop.GetValue(item));
            }

            return query;
        }

        public async Task BulkUpload<T, TId>(string tenantId, int createdOrModifiedBy, Expression<Func<T, object>> conflictProperty, IEnumerable<T> items) where T : RepositoryEntity<TId>
        {
            if (string.IsNullOrEmpty(tenantId))
                throw new Exception("Invalid tenantId");

            var dynamicParams = new DynamicParameters();
            var strBuilder = new StringBuilder();

            using (IDbConnection dbConnection = Connection)
            {
                dbConnection.Open();
                foreach (var item in items)
                {
                    strBuilder.AppendLine(GetUpsertQuery<T, TId>(tenantId, createdOrModifiedBy, item, conflictProperty, ref dynamicParams));
                }

                PrintQuery(strBuilder.ToString(), dynamicParams);

                await dbConnection.ExecuteAsync(strBuilder.ToString(), dynamicParams);
            }
        }

        public string GetUpsertQuery<T, TId>(string tenantId, int createdOrModifiedBy, T item, Expression<Func<T, object>> conflictProperty, ref DynamicParameters dynamicParams) where T : RepositoryEntity<TId>
        {
            var insertQuery = GetInsertQuery<T, TId>(tenantId, createdOrModifiedBy, item, ref dynamicParams);

            if (insertQuery.EndsWith(";"))
                insertQuery = insertQuery.Substring(0, insertQuery.Length - 1);

            return $@"
{insertQuery}
ON CONFLICT (tenantId,{GetMemberName(conflictProperty)}) DO
{GetUpdateQuery<T, TId>(tenantId, createdOrModifiedBy, null, item, ref dynamicParams)}
";
        }

        public async Task Replace<T, TId>(string tenantId, T item) where T : RepositoryEntity<TId>
        {
            var whereQuery = "";

            var dynamicParams = GetWhereQueryFromId<T, TId>(tenantId, item.Id, out whereQuery);

            var updatesList = new List<string>();

            var normalProps = GetNormalProps(typeof(T));

            var index = 1;
            foreach (var prop in normalProps)
            {
                if (prop.Name == "Id")
                    continue;

                var varName = $"@value{index}";
                updatesList.Add($"{prop.Name} = {varName}");

                dynamicParams.Add(varName, prop.GetValue(item));

                index++;
            }

            var query = $"UPDATE {GetTableName<T>()} SET {string.Join(", ", updatesList.ToArray())} {whereQuery}";

            using (IDbConnection dbConnection = Connection)
            {
                dbConnection.Open();
                await dbConnection.QueryAsync<T>(query, dynamicParams);
            }
        }

        public async Task DeleteMany<T, TId>(string tenantId, int deletedById, System.Linq.Expressions.Expression<Func<T, bool>> expression) where T : RepositoryEntity<TId>
        {
            var whereQuery = "";
            var dynamicParams = GetWhereQueryFromExpression<T, TId>(tenantId, expression, out whereQuery);

            using (IDbConnection dbConnection = Connection)
            {
                var query = $"DELETE FROM {GetTableName<T>()} {whereQuery}";

                dbConnection.Open();

                await dbConnection.QueryAsync<T>(query, dynamicParams);
            }
        }

        public async Task DeleteMany<T, TId>(string tenantId, int deletedById, T[] items) where T : RepositoryEntity<TId>
        {
            await DeleteManyByIds<T, TId>(tenantId, deletedById, items.Select(x => x.Id).ToArray());
        }

        public async Task DeleteManyByIds<T, TId>(string tenantId, int deletedById, TId[] ids) where T : RepositoryEntity<TId>
        {
            await Query<string>(async (db) =>
            {
                foreach(var id in ids)
                {
                    try
                    {
                        //await db.QueryAsync($"DELETE FROM {GetTableName<T>()} WHERE ID = @id AND tenantid = @tenantId", 
                        //    new { id = id, tenantId = tenantId });
                        await db.QueryAsync($"UPDATE {GetTableName<T>()} SET isactive = false, modifiedbyid = @deletedById WHERE ID = @id AND tenantid = @tenantId", 
                            new { id, tenantId, deletedById });

                    }
                    catch (PostgresException ex)
                    {
                        //if (ex.SqlState == "23503") //foreign key violation
                        //{
                        //    await db.QueryAsync($"UPDATE {GetTableName<T>()} SET isactive = false WHERE ID = @id AND tenantid = @tenantId",
                        //        new { id = id, tenantId = tenantId });
                        //}
                        //else
                            throw ex;
                    }
                    
                }
                

                return null;
            });

            //using (IDbConnection dbConnection = Connection)
            //{
            //    dbConnection.Open();

            //    var tempParams = new DynamicParameters();
            //    tempParams.Add("@tenantId", tenantId);

            //    var queryBuilder = new List<string>();
            //    var idIndex = 1;
            //    foreach (var id in ids)
            //    {
            //        tempParams.Add("@id" + idIndex, id);
            //        queryBuilder.Add($"Id = @id{idIndex}");
            //        idIndex++;
            //    }

            //    var query = $"DELETE FROM {GetTableName<T>()} WHERE {(typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1" ? "TenantId = @tenantId AND" : "")} ({(string.Join(" OR ", queryBuilder))})";

            //    Console.WriteLine(query);

            //    await dbConnection.QueryAsync<T>(query, tempParams);
            //}

        }

        public string GetTableName<T>()
        {
            return GetTableName(typeof(T));
        }

        public string GetTableName(Type type)
        {
            var attr = type.GetCustomAttributes<TableAttribute>().FirstOrDefault();

            if (attr != null) return attr.Name;

            return GetTableName(type.Name);
        }

        public string GetTableName(string typeName)
        {
            var name = typeName.ToLower();
            if (name.EndsWith("s")) return name;
            else if (name.EndsWith("ch") || name.EndsWith("x")) return name + "es";
            else if (name.EndsWith("y")) return name.Substring(0, name.Length - 1) + "ies";
            else return name + "s";
        }

        public async Task<T> Patch<T, TId>(string tenantId, int modifiedBy, TId id, JsonPatchDocument<T> changesDocument, bool returnUpdatedObject = true) where T : RepositoryEntity<TId>
        {
            var dynamicParams = new DynamicParameters();
            
            var query = GetUpdateQueryFromPatchDocument(tenantId, modifiedBy, id, changesDocument, ref dynamicParams);

            Console.WriteLine(query);

            using (IDbConnection dbConnection = Connection)
            {
                dbConnection.Open();
                await dbConnection.QueryAsync<T>(query, dynamicParams);

                if(returnUpdatedObject)
                    return await FindOneById<T, TId>(tenantId, id);

                return null;
            }
        }

        public async Task<IEnumerable<T>> FindAll<T, TId>(string tenantId) where T : RepositoryEntity<TId>
        {
            var results = await Find<T, TId>(tenantId, x => true);
            return results.Results;
        }

        public async Task<IEnumerable<T>> FindAll<T, TId>(string tenantId, Expression<Func<T, bool>> expression) where T : RepositoryEntity<TId>
        {
            throw new NotImplementedException();
            //Está lanzando un Stack Overflow Exception
            var results = await Find<T, TId>(tenantId, expression);
            return results.Results;
        }

        public string GetUpdateQuery<T, TId>(string tenantId, int modifiedBy, Expression<Func<T, bool>> findExpression, ref DynamicParameters dynamicParams, params UpdateInfo<T>[] updates) where T : RepositoryEntity<TId>
        {
            var whereQuery = "";

            var whereDynamicParams = GetWhereQueryFromExpression<T, TId>(tenantId, findExpression, out whereQuery);

            foreach (var p in whereDynamicParams.ParameterNames)
                dynamicParams.Add(p, whereDynamicParams.Get<dynamic>(p));

            var updatesList = new List<string>();

            foreach (var update in updates)
            {
                var exp = ((LambdaExpression)update.Expression).Body.ToString();

                var paramName = update.Expression.Parameters[0].Name;

                exp = exp.Replace(paramName + ".", "");

                exp = Regex.Replace(exp, @"Convert\((.+?),(.+?)\)", new MatchEvaluator((match) =>
                {
                    return $"{match.Groups[1]}";
                }));

                var varName = $"@v" + Guid.NewGuid().ToString("N");
                updatesList.Add($"{exp} = {varName}");

                dynamicParams.Add(varName, update.Value);
            }

            if (typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1")
            {
                updatesList.Add($"{nameof(RepositoryEntityWithTenantId<TId>.ModifiedById)} = @modifiedBy");
                updatesList.Add($"{nameof(RepositoryEntityWithTenantId<TId>.Modified)} = @modified");

                dynamicParams.Add("@modifiedBy", modifiedBy);
                dynamicParams.Add("@modified", DateTime.UtcNow);
            }

            return $"UPDATE {GetTableName<T>()} SET {string.Join(", ", updatesList.ToArray())} {whereQuery}; ";
        }

        public string GetUpdateQuery<T, TId>(string tenantId, int modifiedBy, Expression<Func<T, bool>> findExpression, T item, ref DynamicParameters dynamicParams) where T : RepositoryEntity<TId>
        {
            var whereQuery = "";

            if(findExpression != null)
            {
                var whereDynamicParams = GetWhereQueryFromExpression<T, TId>(tenantId, findExpression, out whereQuery);
                foreach (var p in whereDynamicParams.ParameterNames)
                    dynamicParams.Add(p, whereDynamicParams.Get<dynamic>(p));
            }
            
            var updatesList = new List<string>();

            var props = GetUpdateableProps(typeof(T));
            foreach (var prop in props)
            {
                var varName = $"@v" + Guid.NewGuid().ToString("N");

                updatesList.Add($"{prop.Name} = {varName}");

                dynamicParams.Add(varName, prop.GetValue(item));
            }

            if (typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1")
            {
                updatesList.Add($"{nameof(RepositoryEntityWithTenantId<TId>.ModifiedById)} = @modifiedBy");
                updatesList.Add($"{nameof(RepositoryEntityWithTenantId<TId>.Modified)} = @modified");

                dynamicParams.Add("@modifiedBy", modifiedBy);
                dynamicParams.Add("@modified", DateTime.UtcNow);
            }

            if(string.IsNullOrEmpty(whereQuery))
                return $"UPDATE SET {string.Join(", ", updatesList.ToArray())}; ";

            return $"UPDATE {GetTableName<T>()} SET {string.Join(", ", updatesList.ToArray())} {whereQuery}; ";
        }

        //public string GetUpdateQueryNoTenantId<T, TId>(int modifiedBy, Expression<Func<T, bool>> findExpression, T item, ref DynamicParameters dynamicParams) where T : RepositoryEntity<TId>
        //{
        //    var whereQuery = "";

        //    if (findExpression != null)
        //    {
        //        var whereDynamicParams = GetWhereQueryFromExpressionNoTenantId<T, TId>(findExpression, out whereQuery);
        //        foreach (var p in whereDynamicParams.ParameterNames)
        //            dynamicParams.Add(p, whereDynamicParams.Get<dynamic>(p));
        //    }

        //    var updatesList = new List<string>();

        //    var props = GetUpdateableProps(typeof(T));
        //    foreach (var prop in props)
        //    {
        //        var varName = $"@v" + Guid.NewGuid().ToString("N");

        //        updatesList.Add($"{prop.Name} = {varName}");

        //        dynamicParams.Add(varName, prop.GetValue(item));
        //    }

        //    if (typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1")
        //    {
        //        updatesList.Add($"{nameof(RepositoryEntityWithTenantId<TId>.ModifiedById)} = @modifiedBy");
        //        updatesList.Add($"{nameof(RepositoryEntityWithTenantId<TId>.Modified)} = @modified");

        //        dynamicParams.Add("@modifiedBy", modifiedBy);
        //        dynamicParams.Add("@modified", DateTime.UtcNow);
        //    }

        //    if (string.IsNullOrEmpty(whereQuery))
        //        return $"UPDATE SET {string.Join(", ", updatesList.ToArray())}; ";

        //    return $"UPDATE {GetTableName<T>()} SET {string.Join(", ", updatesList.ToArray())} {whereQuery}; ";
        //}

        public string GetUpdateQueryFromPatchDocument<T, TId>(string tenantId, int modifiedBy, TId id, JsonPatchDocument<T> patchDoc, ref DynamicParameters dynamicParams) where T : RepositoryEntity<TId>
        {
            var whereQuery = "";

            var whereDynamicParams = GetWhereQueryFromExpression<T, TId>(tenantId, x => x.Id.Equals(id), out whereQuery);

            foreach (var p in whereDynamicParams.ParameterNames)
                dynamicParams.Add(p, whereDynamicParams.Get<dynamic>(p));

            var updatesList = new List<string>();

            var props = GetUpdateableProps(typeof(T));

            foreach (var prop in props)
            {
                var firstOrDefault = patchDoc.Operations.FirstOrDefault(x => x.path.ToLower() == $"/{prop.Name.ToLower()}");

                if (firstOrDefault == null) continue;

                var varName = $"@v" + Guid.NewGuid().ToString("N");

                updatesList.Add($"{prop.Name} = {varName}");

                dynamicParams.Add(varName, GetValue(prop.PropertyType, firstOrDefault.value));
            }

            if (typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1")
            {
                updatesList.Add($"{nameof(RepositoryEntityWithTenantId<TId>.ModifiedById)} = @modifiedBy");
                updatesList.Add($"{nameof(RepositoryEntityWithTenantId<TId>.Modified)} = @modified");

                dynamicParams.Add("@modifiedBy", modifiedBy);
                dynamicParams.Add("@modified", DateTime.UtcNow);
            }

            if(typeof(T) == typeof(User))
            {
                updatesList.Add($"{nameof(RepositoryEntityWithTenantId<TId>.ModifiedById)} = @modifiedBy");

                dynamicParams.Add("@modifiedBy", modifiedBy);
            }


            return $"UPDATE {GetTableName<T>()} SET {string.Join(", ", updatesList.ToArray())} {whereQuery};";
        }

        private object GetValue(Type propType, object value)
        {
            if (propType.FullName.Contains("System.DateTime")) return DateTime.Parse(value?.ToString());
            if (propType.FullName.Contains("Decimal")) return Convert.ToDecimal(value?.ToString(), CultureInfo.InvariantCulture);
            if (propType.FullName.Contains("Int32")) return Convert.ToInt32(value?.ToString());
            if (propType.FullName.Contains("Int64")) return Convert.ToInt64(value?.ToString());

            return value;
        }
        

        public async Task Update<T, TId>(string tenantId, int modifiedBy, Expression<Func<T, bool>> findExpression, params UpdateInfo<T>[] updates) where T : RepositoryEntity<TId>
        {
            var dynamicParams = new DynamicParameters();
            var query = GetUpdateQuery<T, TId>(tenantId, modifiedBy, findExpression, ref dynamicParams, updates);
            PrintQuery(query, dynamicParams);
            using (IDbConnection dbConnection = Connection)
            {
                dbConnection.Open();
                await dbConnection.QueryAsync<T>(query, dynamicParams);
            }
        }

        public async Task Reactivate<T, TId>(string tenantId, int modifiedBy, TId id) where T : RepositoryEntity<TId>
        {
            using (IDbConnection dbConnection = Connection)
            {
                await dbConnection.QueryAsync<TId>($"UPDATE {GetTableName<T>()} SET isactive = true WHERE id = @id AND tenantid = @tenantId", new { tenantId, id });
            }
        }

        public async Task Update<T, TId>(string tenantId, int modifiedBy, T item) where T : RepositoryEntity<TId>
        {
            var dynamicParams = new DynamicParameters();

            var id = item.Id;
            var query = GetUpdateQuery<T, TId>(tenantId, modifiedBy, x => x.Id.Equals(id), item, ref dynamicParams);
            using (IDbConnection dbConnection = Connection)
            {
                PrintQuery(query, dynamicParams);
                dbConnection.Open();
                await dbConnection.QueryAsync<T>(query, dynamicParams);
            }
        }

        public async Task<T> TryFindOneByTenantId<T, TId>(string tenantId) where T : RepositoryEntity<TId>
        {
            var res = await FindAll<T, TId>(tenantId);
            return res.FirstOrDefault();
        }

        public async Task<IEnumerable<T>> FindByIds<T, TId>(string tenantId, TId[] ids) where T : RepositoryEntity<TId>
        {
            var results = await Find<T, TId>(tenantId, x => ids.Contains(x.Id), null, 1000);
            return results.Results;
        }

        public async Task<IEnumerable<TId>> FindIds<T, TId>(string tenantId, Expression<Func<T, bool>> expression) where T : RepositoryEntity<TId>
        {
            using (IDbConnection dbConnection = Connection)
            {
                dbConnection.Open();

                var whereQuery = "";
                var dynamicParameters = GetWhereQueryFromExpression<T, TId>(tenantId, expression, out whereQuery);

                var query = @$"SELECT id FROM {GetTableName<T>()} {whereQuery}";
                //Console.WriteLine(query);

                return await dbConnection.QueryAsync<TId>(query, dynamicParameters);
            }
        }

        public async Task<T> TryFindOne<T, TId>(string tenantId, Expression<Func<T, bool>> expression) where T : RepositoryEntity<TId>
        {
            var results = await Find<T, TId>(tenantId, expression);
            return results.Results.FirstOrDefault();
        }

        public async Task<T> FindOne<T, TId>(string tenantId, Expression<Func<T, bool>> expression) where T : RepositoryEntity<TId>
        {
            var results = await Find<T, TId>(tenantId, expression);
            return results.Results.First();
        }

        public string GetDeleteQuery<T, TId>(string tenantId, T item, ref DynamicParameters dynamicParameters) where T : RepositoryEntity<TId>
        {
            var idName = "@" + Guid.NewGuid().ToString("N");
            dynamicParameters.Add("@tenantId", tenantId);
            dynamicParameters.Add(idName, item.Id);

            return $"DELETE FROM {GetTableName<T>()} WHERE {(typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1" ? "TenantId = @tenantId AND" : "")} id = {idName}; ";
        }

        public string GetDeleteQuery<T, TId>(string tenantId, Expression<Func<T, bool>> expression, ref DynamicParameters dynamicParameters) where T : RepositoryEntity<TId>
        {
            var query = "";

            var whereDynamicParams = GetWhereQueryFromExpression<T, TId>(tenantId, expression, out query);

            foreach (var p in whereDynamicParams.ParameterNames)
                dynamicParameters.Add(p, whereDynamicParams.Get<dynamic>(p));

            return $"DELETE FROM {GetTableName<T>()} {query}; ";
        }

        public async Task<IEnumerable<DistinctResult>> GetDistinct<T, TId>(string tenantId, Expression<Func<T, object>> valueExpression, Expression<Func<T, object>> textExpression, string term = null) where T : RepositoryEntity<TId>
        {
            using (IDbConnection dbConnection = Connection)
            {
                dbConnection.Open();

                var tempParams = new DynamicParameters();
                tempParams.Add("@tenantId", tenantId);
                tempParams.Add("@term", term.PrepareQuery());

                var query = $"SELECT DISTINCT {GetMemberName(valueExpression)} as value, {GetMemberName(textExpression)} as text FROM {GetTableName<T>()} WHERE {GetMemberName(textExpression)} ~* @term {(typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1" ? "AND TenantId = @tenantId" : "")} ORDER BY text";

                PrintQuery(query, tempParams);

                return await dbConnection.QueryAsync<DistinctResult>(query, tempParams);
            }
        }

        public async Task<IEnumerable<DistinctResult>> GetDistinct<T, TId>(string tenantId, Expression<Func<T, object>> expression, string term = null) where T : RepositoryEntity<TId>
        {
            return await GetDistinct<T, TId>(tenantId, GetMemberName(expression), term);
        }

        public async Task<IEnumerable<DistinctResult>> GetDistinct<T, TId>(string tenantId, string propertyName, string term = null) where T : RepositoryEntity<TId>
        {
            var prop = typeof(T).GetProperties().FirstOrDefault(x => x.Name.ToLower() == propertyName.ToLower());

            if (prop == null) return new List<DistinctResult>();

            var propIsClass = prop.PropertyType.IsClass 
                    && prop.PropertyType != typeof(string)
                    && prop.PropertyType != typeof(DateTime)
                    && prop.PropertyType != typeof(DateTime?);

            using (IDbConnection dbConnection = Connection)
            {
                dbConnection.Open();

                term = term?.PrepareQuery();

                var tempParams = new DynamicParameters();
                tempParams.Add("@tenantId", tenantId);
                tempParams.Add("@term", term);

                var query = $"SELECT DISTINCT {prop.Name} AS value FROM {GetTableName<T>()} WHERE {prop.Name} ~* @term AND {(typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1" ? "TenantId = @tenantId" : "")} LIMIT 20";

                if(propIsClass)
                    query = $@"SELECT DISTINCT {GetTableName(prop.PropertyType.Name)}.id AS value, public.{GetTableName(prop.PropertyType.Name)}.name AS text	
        FROM public.{GetTableName<T>()}
        JOIN public.{GetTableName(prop.PropertyType.Name)} ON public.{GetTableName<T>()}.{prop.Name}id = public.{GetTableName(prop.PropertyType.Name)}.id 
        WHERE public.{GetTableName(prop.PropertyType.Name)}.name ~* @term AND {(typeof(T).BaseType.Name == "RepositoryEntityWithTenantId`1" ? $"{GetTableName<T>()}.TenantId = @tenantId" : "")}
        LIMIT 20";

                PrintQuery(query, tempParams);

                var list = await dbConnection.QueryAsync<DistinctResult>(query, tempParams);

                if (prop.PropertyType.IsEnum)
                    return list.Select(x => new DistinctResult() { 
                        Text = GetEnumDisplayName((Enum)Enum.Parse(prop.PropertyType, x.Value.ToString())), 
                        Value = x.Value?.ToString() });

                if (propIsClass)
                    return list.Select(x => new DistinctResult() { Text = x.Text?.ToString(), Value = x.Value?.ToString() });

                return list.Select(x => new DistinctResult() { Text = x.Value?.ToString(), Value = x.Value?.ToString() });
            }
        }

        private PropertyInfo[] GetUpdateableProps(Type type)
        {
            var props = type.GetProperties();

                return type.GetProperties()
                        .Where(x => x.Name != "Id"
                        && x.GetSetMethod() != null
                        && !Attribute.GetCustomAttributes(x).Any(y => y is NotMappedAttribute)
                        && !Attribute.GetCustomAttributes(x).Any(y => y is DoNotUpdateAttribute)
                        && (!x.PropertyType.IsClass
                        || x.PropertyType == typeof(string)
                        || x.PropertyType == typeof(DateTime)
                        || x.PropertyType == typeof(DateTime?))
                        ).ToArray();
        }

        private PropertyInfo[] GetNormalProps(Type type)
        {
            var props = type.GetProperties();

            var idProp = props.Where(x => x.Name == "Id").FirstOrDefault();

            if (idProp != null && idProp.PropertyType.Name == "Int32")
            {
                return props
                        .Where(x => x.Name != "Id"
                        && x.GetSetMethod() != null
                        && !Attribute.GetCustomAttributes(x).Any(y => y is NotMappedAttribute)
                        && (!x.PropertyType.IsClass
                        || x.PropertyType == typeof(string)
                        || x.PropertyType == typeof(DateTime)
                        || x.PropertyType == typeof(DateTime?))
                        ).ToArray();
            }
            else
            {
                return props
                        .Where(x => x.GetSetMethod() != null
                        && !Attribute.GetCustomAttributes(x).Any(y => y is NotMappedAttribute)
                        && (!x.PropertyType.IsClass
                        || x.PropertyType == typeof(string)
                        || x.PropertyType == typeof(DateTime)
                        || x.PropertyType == typeof(DateTime?))
                        ).ToArray();
            }
        }

        public PropertyInfo[] GetExcelProps(Type type, out string idDisplayName)
        {
            idDisplayName = null;

            var props = type.GetProperties();

            var attr = type.GetCustomAttribute<IncludeIdAttribute>();

            if (attr != null)
            {
                idDisplayName = attr.DisplayName;
                return props
                        .Where(x => Attribute.GetCustomAttributes(x).Any(y => y is DisplayNameAttribute) && x.Name != "Id")
                        .Prepend(props.Single(x => x.Name == "Id"))
                        .ToArray();
            }
            else
            {
                return props
                        .Where(x => Attribute.GetCustomAttributes(x).Any(y => y is DisplayNameAttribute) && x.Name != "Id")
                        .ToArray();
            }
        }

        private PropertyInfo[] GetIdProps(Type type)
        {
            return type.GetProperties()
                .Where(x => x.Name != "Id"
                    && x.Name.EndsWith("Id")
                    && !Attribute.GetCustomAttributes(x).Any(y => y is NotMappedAttribute)
                    && !Attribute.GetCustomAttributes(x).Any(y => y is NoJoinAttribute)
                    && !Attribute.GetCustomAttributes(x).Any(y => y is IgnoreRelationshipAttribute)
                    ).ToArray();
        }

        private PropertyInfo[] GetListProps(Type type)
        {
            return type.GetProperties()
                .Where(x => x.PropertyType.Name.StartsWith("List")
                && !Attribute.GetCustomAttributes(x).Any(y => y is NotMappedAttribute)
                && !Attribute.GetCustomAttributes(x).Any(y => y is NoJoinAttribute))
                         .ToArray();
        }


        private PropertyInfo[] GetNavProps(Type type)
        {
            return type.GetProperties()
                .Where(x => x.Name != "Id"
                    && x.GetSetMethod() != null
                    && !Attribute.GetCustomAttributes(x).Any(y => y is NotMappedAttribute)
                    && !Attribute.GetCustomAttributes(x).Any(y => y is NoJoinAttribute)
                    && !x.PropertyType.Name.StartsWith("List")
                    && x.PropertyType.IsClass
                    && x.PropertyType != typeof(string)
                    && x.PropertyType != typeof(DateTime)
                    && x.PropertyType != typeof(DateTime?)
                    ).ToArray();
        }

        private string GetMemberName<T>(Expression<T> expression)
        {
            switch (expression.Body)
            {
                case MemberExpression m:
                    return m.Member.Name;
                case UnaryExpression u when u.Operand is MemberExpression m:
                    return m.Member.Name;
                default:
                    throw new NotImplementedException(expression.GetType().ToString());
            }
        }

        
        private string GetEnumDisplayName(Enum enumValue)
        {
                return enumValue.GetType()
                                .GetMember(enumValue.ToString())
                                .First()
                                .GetCustomAttribute<DisplayAttribute>()
                                .GetName();
        }

        public async Task<int> GetAutoIncrementNumber(string tenantId, string type)
        {
            if (string.IsNullOrEmpty(tenantId))
                throw new Exception("Empty tenantId");

            return await Query<int>(async db =>
            {
                try
                {
                    return await db.QuerySingleAsync<int>($"INSERT INTO counters_{type} (tenantId) VALUES(@tenantId) RETURNING id", new { tenantId });
                }
                catch
                {
                    return await db.QuerySingleAsync<int>($"CREATE TABLE counters_{type} ( id SERIAL PRIMARY KEY, tenantid VARCHAR(50) ); INSERT INTO counters_{type} (tenantid) VALUES(@tenantId) RETURNING id", new { tenantId });
                }
            });
        }

      
    }



    //public class RepositoryEntity
    //{
    //    public int Id { get; set; }

    //    //[BsonId]
    //    //[BsonRepresentation(BsonType.ObjectId)]
    //    //public string Id { get; set; }
    //}

    //public class FindResults<T> where T : RepositoryEntity
    //{
    //    public long Total { get; set; }
    //    public IEnumerable<T> Results { get; set; }
    //}

    //public class DistinctResult
    //{
    //    public string Text { get; set; }
    //    public int Value { get; set; }
    //}

    //public interface IRepository
    //{
    //    Task<IEnumerable<DistinctResult>> GetDistinct<T>(Expression<Func<T, object>> propertyToSet) where T : RepositoryEntity;
    //    Task<IEnumerable<DistinctResult>> GetDistinct<T>(Expression<Func<T, object>> propertyToSet, string term, int limit = 20) where T : RepositoryEntity;
    //    Task<T> FindById<T>(int id) where T : RepositoryEntity;
    //    Task<IEnumerable<T>> FindAll<T>() where T : RepositoryEntity;
    //    Task<FindResults<T>> Find<T>(int skip, int take, string sortBy, string sortOrder = "asc", string q = "", string rawQuery = "") where T : RepositoryEntity;
    //    Task<MemoryStream> GetExcel<T>(string sortBy, string sortOrder = "asc", string q = "") where T : RepositoryEntity;
    //    Task<T> Insert<T>(T item) where T : RepositoryEntity;
    //    Task InsertMany<T>(T[] item) where T : RepositoryEntity;
    //    Task Update<T>(T item) where T : RepositoryEntity;
    //    Task Update<T>(int id, JsonPatchDocument<T> changesDocument) where T : RepositoryEntity;
    //    Task Update<T>(int[] ids, Expression<Func<T, object>> propertyToSet, object value);
    //    Task Update<T>(int id, params PropertyToSetAndValue<T>[] propertiesToSet);
    //    Task DeleteMany<T>(RepositoryEntity[] items) where T : RepositoryEntity;
    //    Task<T> Query<T>(Func<IDbConnection, Task<T>> func);
    //    string GetTableName<T>();

    //}

    //public class Repository : IRepository
    //{
    //    internal IDbConnection Connection
    //    {
    //        get
    //        {
    //            return new NpgsqlConnection("Host=raja.db.elephantsql.com;Username=szwfhujz;Database=szwfhujz;Password=sjFxJmpqlNL-qNrzSCZv1PwOGUSCBA4J");
    //            //return new NpgsqlConnection("Host=localhost;Username=postgres;Database=misingredientes;Password=adminadmin");
    //        }
    //    }

    //    private IEnumerable<IFindStrategy> _findStrategies;
    //    private IEnumerable<IInsertStrategy> _insertStrategies;
    //    private IEnumerable<IUpdateStrategy> _updateStrategies;

    //    public Repository(IEnumerable<IInsertStrategy> insertStrategies,
    //        IEnumerable<IUpdateStrategy> updateStrategies,
    //        IEnumerable<IFindStrategy> findStrategies)
    //    {
    //        _findStrategies = findStrategies;
    //        _insertStrategies = insertStrategies;
    //        _updateStrategies = updateStrategies;
    //    }

    //    public async Task<T> Query<T>(Func<IDbConnection, Task<T>> func)
    //    {
    //        using (IDbConnection dbConnection = Connection)
    //        {
    //            return await func(dbConnection);
    //        }
    //    }

    //    public async Task<IEnumerable<DistinctResult>> GetDistinct<T>(Expression<Func<T, object>> propertyToSet) where T : RepositoryEntity
    //    {
    //        using (IDbConnection dbConnection = Connection)
    //        {
    //            var query = $"SELECT DISTINCT id as value, {GetMemberName(propertyToSet)} as text FROM {GetTableName<T>()}";
    //            dbConnection.Open();

    //            return await dbConnection.QueryAsync<DistinctResult>(query);
    //        }
    //    }

    //    public async Task<IEnumerable<DistinctResult>> GetDistinct<T>(Expression<Func<T, object>> propertyToSet, string term, int limit = 10) where T : RepositoryEntity
    //    {
    //        using (IDbConnection dbConnection = Connection)
    //        {
    //            var query = $"SELECT DISTINCT id as value, {GetMemberName(propertyToSet)} as text FROM {GetTableName<T>()} WHERE {SqlQuery.GetSqlSearchQuery(term, propertyToSet)} LIMIT {limit}";
    //            dbConnection.Open();

    //            return await dbConnection.QueryAsync<DistinctResult>(query);
    //        }
    //    }

    //    public async Task<T> FindById<T>(int id) where T : RepositoryEntity
    //    {
    //        using (IDbConnection dbConnection = Connection)
    //        {
    //            var strategy = _findStrategies.FirstOrDefault(x => x.CanProcess(typeof(T)));

    //            dbConnection.Open();

    //            if(strategy != null)
    //            {
    //                return await strategy.FindById<T>(GetTableName<T>(), dbConnection, id);
    //            }
    //            else
    //            {
    //                var query = $"SELECT * FROM {GetTableName<T>()} WHERE id = {id} LIMIT 1";
    //                return await dbConnection.QueryFirstAsync<T>(query);
    //            }

    //        }
    //    }

    //    public async Task<IEnumerable<T>> FindAll<T>() where T : RepositoryEntity
    //    {
    //        using (IDbConnection dbConnection = Connection)
    //        {
    //            dbConnection.Open();
    //            var query = $"SELECT * FROM {GetTableName<T>()}";
    //            return await dbConnection.QueryAsync<T>(query);
    //        }
    //    }

    //    public async Task DeleteMany<T>(RepositoryEntity[] items) where T : RepositoryEntity
    //    {
    //        using (IDbConnection dbConnection = Connection)
    //        {
    //            var table = GetTableName<T>();

    //            var queries = items.Select(x => $"Id = {x.Id}").ToArray();

    //            dbConnection.Open();
    //            await dbConnection.QueryAsync<dynamic>($"DELETE FROM {table} WHERE {string.Join(" OR ", queries)}");
    //        }
    //    }

    //    public async Task<FindResults<T>> Find<T>(int skip, int take, string sortBy, string sortOrder = "asc", string q = "", string rawQuery = "") where T : RepositoryEntity
    //    {
    //        if (string.IsNullOrEmpty(sortBy))
    //            sortBy = "id";

    //        sortBy = $"{GetTableName<T>()}.{sortBy}";

    //        if (string.IsNullOrEmpty(sortOrder))
    //            sortOrder = "ASC";

    //        using (IDbConnection dbConnection = Connection)
    //        {
    //            var strategy = _findStrategies.FirstOrDefault(x => x.CanProcess(typeof(T)));

    //            dbConnection.Open();

    //            if (strategy != null)
    //            {
    //                return await strategy.Find<T>(GetTableName<T>(), dbConnection, skip, take, sortBy, sortOrder, q, rawQuery);
    //            }
    //            else
    //            {

    //                if(!string.IsNullOrEmpty(q))
    //                {
    //                    var properties = typeof(T)
    //                        .GetProperties()
    //                        .Where(prop => prop.IsDefined(typeof(IncludeInSearchAttribute), false));

    //                    q = "WHERE " + string.Join(" OR ",
    //                        properties.Select(x => SqlQuery.GetSqlSearchQuery<T>(q, x.Name))
    //                        );

    //                }

    //                var totalQuery = $"SELECT COUNT(*) FROM {GetTableName<T>()} {q}";
    //                var resultsQuery = $"SELECT * FROM {GetTableName<T>()} {q} ORDER BY {sortBy} {sortOrder} LIMIT {take} OFFSET {skip}";

    //                return new FindResults<T>()
    //                {
    //                    Results = await dbConnection.QueryAsync<T>(resultsQuery),
    //                    Total = await dbConnection.ExecuteScalarAsync<long>(totalQuery),
    //                };
    //            }

    //        }

    //        //long count = 0;

    //        //using (var conn = new NpgsqlConnection("Host=localhost;Username=postgres;Database=misingredientes;Password=adminadmin"))
    //        //{
    //        //    conn.Open();

    //        //    var refTypes = typeof(T).GetProperties();

    //        //    var query = "SELECT * FROM " + GetTableName<T>();
    //        //    using (var cmd = new NpgsqlCommand(query, conn))
    //        //    {
    //        //        using (var reader = await cmd.ExecuteReaderAsync())
    //        //            while (reader.Read())
    //        //            {
    //        //                reader.Get
    //        //                document = new Document();
    //        //                document.Id = reader.GetInt32(0);
    //        //                document.Name = reader.GetString(1);
    //        //                document.Json = reader?.GetValue(2).ToString();

    //        //            }

    //        //    }
    //        //}

    //        //return null;
    //    }

    //    public async Task InsertMany<T>(T[] item) where T : RepositoryEntity
    //    {

    //    }

    //    public async Task<T> Insert<T>(T item) where T : RepositoryEntity
    //    {
    //        var strategy = _insertStrategies.FirstOrDefault(x => x.CanProcess(typeof(T)));

    //        using (IDbConnection dbConnection = Connection)
    //        {
    //            string query = strategy != null
    //                ? strategy.GetInsertQuery(GetTableName<T>(), item)
    //                : SqlQuery.Insert(item, "id");

    //            dbConnection.Open();
    //            var newId = await dbConnection.ExecuteScalarAsync<int>(query);

    //            item.Id = newId;

    //            if (item.Id == 0)
    //                throw new Exception("No id was returned");

    //            return item;

    //        }
    //    }

    //    public async Task Update<T>(int id, params PropertyToSetAndValue<T>[] propertiesToSet)
    //    {
    //        using (IDbConnection dbConnection = Connection)
    //        {
    //            var set = string.Join(',', propertiesToSet.Select(x => $"{GetMemberName(x.PropertyToSet)} = '{x.Value}'").ToArray());
    //            var query = $"UPDATE {GetTableName<T>()} SET {set} WHERE id = '{id}'";
    //            dbConnection.Open();

    //            await dbConnection.ExecuteScalarAsync(query);
    //        }
    //    }

    //    public async Task Update<T>(int[] ids, Expression<Func<T, object>> propertyToSet, object value)
    //    {
    //        using (IDbConnection dbConnection = Connection)
    //        {
    //            var query = $"UPDATE {GetTableName<T>()} SET {GetMemberName(propertyToSet)} = '{value}' WHERE {(string.Join(" OR ", ids.Select(x => $"id = {x}").ToArray()))}";
    //            dbConnection.Open();

    //            await dbConnection.ExecuteScalarAsync(query);
    //        }
    //    }

    //    public async Task Update<T>(int id, JsonPatchDocument<T> changesDocument) where T : RepositoryEntity
    //    {
    //        var item = await FindById<T>(id);
    //        changesDocument.ApplyTo(item);
    //        await Update(item);
    //    }

    //    public async Task Update<T>(T item) where T : RepositoryEntity
    //    {
    //        var strategy = _updateStrategies.FirstOrDefault(x => x.CanProcess(typeof(T)));
    //        var findStrategy = _findStrategies.FirstOrDefault(x => x.CanProcess(typeof(T)));

    //        if (strategy == null)
    //            throw new NotImplementedException();

    //        using (IDbConnection dbConnection = Connection)
    //        {
    //            dbConnection.Open();

    //            T existingItem = null;

    //            if (strategy != null)
    //                existingItem = await findStrategy.FindById<T>(GetTableName<T>(), dbConnection, item.Id);
    //            else
    //                existingItem = await dbConnection.QueryFirstAsync<T>($"SELECT * FROM {GetTableName<T>()} WHERE id = {item.Id} LIMIT 1");

    //            if (existingItem != null)
    //            {
    //                var query = strategy.GetUpdateQuery(GetTableName<T>(), item, existingItem);
    //                await dbConnection.ExecuteScalarAsync(query);
    //            }
    //        }
    //    }

    //    private string GetMemberName<T>(Expression<T> expression)
    //    {
    //        switch (expression.Body)
    //        {
    //            case MemberExpression m:
    //                return m.Member.Name;
    //            case UnaryExpression u when u.Operand is MemberExpression m:
    //                return m.Member.Name;
    //            default:
    //                throw new NotImplementedException(expression.GetType().ToString());
    //        }
    //    }


    //    public string GetTableName<T>()
    //    {
    //        var name = typeof(T).Name.ToLower();
    //        if (name.EndsWith("s")) return name;
    //        else if (name.EndsWith("ch") || name.EndsWith("x")) return name + "es";
    //        else if (name.EndsWith("y")) return new string(name.Substring(0, name.Length - 1)) + "ies";
    //        else return name + "s";
    //    }

    //    private async Task<IEnumerable<T>> FindDataForExcel<T>(string sortBy, string sortOrder = "asc", string q = "")
    //    {
    //        if (string.IsNullOrEmpty(sortBy))
    //            sortBy = "id";

    //        sortBy = $"{GetTableName<T>()}.{sortBy}";

    //        if (string.IsNullOrEmpty(sortOrder))
    //            sortOrder = "ASC";

    //        using (IDbConnection dbConnection = Connection)
    //        {
    //            dbConnection.Open();

    //            if (!string.IsNullOrEmpty(q))
    //            {
    //                var properties = typeof(T)
    //                    .GetProperties()
    //                    .Where(prop => prop.IsDefined(typeof(IncludeInSearchAttribute), false));

    //                q = "WHERE " + string.Join(" OR ",
    //                    properties.Select(x => SqlQuery.GetSqlSearchQuery<T>(q, x.Name))
    //                    );

    //            }

    //            var resultsQuery = $"SELECT * FROM {GetTableName<T>()} {q} ORDER BY {sortBy} {sortOrder}";

    //            return await dbConnection.QueryAsync<T>(resultsQuery);
    //        }
    //    }

    //    public async Task<MemoryStream> GetExcel<T>(string sortBy, string sortOrder = "asc", string q = "") where T : RepositoryEntity
    //    {

    //        var memoryStream = new MemoryStream();
    //        var letters = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };

    //        using (var excelPackage = new ExcelPackage())
    //        {
    //            var ws = excelPackage.Workbook.Worksheets.Add("Datos");

    //            var properties = typeof(T)
    //                       .GetProperties()
    //                       .Where(prop => !prop.IsDefined(typeof(DoNotUpdateAttribute), false))
    //                       .Where(prop => !prop.IsDefined(typeof(NotMappedAttribute), false))
    //                       .Where(x => x.Name != "Id"
    //                           && x.GetSetMethod() != null
    //                           && (!x.PropertyType.IsClass
    //                           || x.PropertyType == typeof(string)
    //                           || x.PropertyType == typeof(DateTime)
    //                           || x.PropertyType == typeof(DateTime?))
    //                       );

    //            var letterIndex = 0;

    //            foreach(var prop in properties)
    //            {
    //                var displayNameAttribute = prop.GetCustomAttributes<DisplayNameAttribute>().FirstOrDefault();

    //                ws.Column(letterIndex + 1).Width = 40;

    //                ws.SetValue(letters[letterIndex] + "1", displayNameAttribute != null 
    //                    ? displayNameAttribute.DisplayName : prop.Name);

    //                letterIndex++;
    //            }

    //            var row = 2;

    //            var data = await FindDataForExcel<T>(sortBy, sortOrder, q);

    //            foreach (var item in data)
    //            {
    //                letterIndex = 0;

    //                foreach(var prop in properties)
    //                {
    //                    ws.SetValue(letters[letterIndex] + "" + row, prop.GetValue(item));
    //                    letterIndex++;
    //                }

    //                row++;
    //            }



    //            excelPackage.SaveAs(memoryStream);
    //        }

    //        memoryStream.Position = 0;

    //        return memoryStream;
    //    }
    //}

    //public class PropertyToSetAndValue<T>
    //{
    //    public Expression<Func<T, object>> PropertyToSet { get; set; } 
    //    public object Value { get; set; }
    //}


}