using Microsoft.Extensions.Caching.Db;

namespace nancy.test
{
    public class NpqsqlQueries : IDbQueries
    {
        /*
         services.AddDistributedDbServerCache(x =>
            {
                x.Factory = NpgsqlFactory.Instance;
                x.SchemaName = "public";
                x.TableName = "aspnet_session";
                x.DbQueries = new NpqsqlQueries(x.SchemaName, x.TableName);
                x.SystemClock = new SystemClock();

                var cs = new NpgsqlConnectionStringBuilder
                {
                   //....
                };

                x.ConnectionString = cs.ConnectionString;
            });
             */
        private const string TableInfoFormat =
            "SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE " +
            "FROM INFORMATION_SCHEMA.TABLES " +
            "WHERE TABLE_SCHEMA = '{0}' " +
            "AND TABLE_NAME = '{1}'";

        private const string UpdateCacheItemFormat =
            "UPDATE {0} " +
            "SET ExpiresAtTime = " +
            @"(CASE WHEN  AbsoluteExpiration - @UtcNow
 <= CAST((CAST(SlidingExpirationInSeconds as varchar) || ' seconds') as INTERVAL)
 THEN
 AbsoluteExpiration
 ELSE
 @UtcNow +  CAST( (CAST(SlidingExpirationInSeconds as varchar) || ' seconds' ) as INTERVAL )
 END) " +
            "WHERE Id = @Id " +
            "AND @UtcNow <= ExpiresAtTime " +
            "AND SlidingExpirationInSeconds IS NOT NULL " +
            "AND (AbsoluteExpiration IS NULL OR AbsoluteExpiration <> ExpiresAtTime) ;";

        private const string GetCacheItemFormat =
            "SELECT Id, ExpiresAtTime, SlidingExpirationInSeconds, AbsoluteExpiration, \"Value\" " +
            "FROM {0} WHERE Id = @Id AND @UtcNow <= ExpiresAtTime;";

        private const string SetCacheItemFormat =
            @"with upsert as ( UPDATE {0} SET ""Value"" = @Value, 
ExpiresAtTime = (CASE WHEN(@SlidingExpirationInSeconds IS NUll)
THEN @AbsoluteExpiration
ELSE @UtcNow + CAST( (CAST(@SlidingExpirationInSeconds as varchar) || ' seconds') 
as INTERVAL )
END),
SlidingExpirationInSeconds = @SlidingExpirationInSeconds,
AbsoluteExpiration = @AbsoluteExpiration
WHERE Id = @Id RETURNING *)
INSERT INTO {0}
(Id, ""Value"", ExpiresAtTime, SlidingExpirationInSeconds, AbsoluteExpiration)
 SELECT @Id, @Value, (CASE WHEN (@SlidingExpirationInSeconds IS NUll)
THEN @AbsoluteExpiration
ELSE @UtcNow + CAST( (CAST(@SlidingExpirationInSeconds as varchar) || ' seconds') 
as INTERVAL )
END), @SlidingExpirationInSeconds, @AbsoluteExpiration
WHERE NOT EXISTS (SELECT * FROM upsert)
  ";

        private const string DeleteCacheItemFormat = "DELETE FROM {0} WHERE Id = @Id";

        public const string DeleteExpiredCacheItemsFormat = "DELETE FROM {0} WHERE @UtcNow > ExpiresAtTime";

        public NpqsqlQueries(string schemaName, string tableName)
        {
            var tableNameWithSchema = string.Format(
                "{0}.{1}", DelimitIdentifier(schemaName), DelimitIdentifier(tableName));

            // when retrieving an item, we do an UPDATE first and then a SELECT
            GetCacheItem = string.Format(UpdateCacheItemFormat + GetCacheItemFormat, tableNameWithSchema);
            GetCacheItemWithoutValue = string.Format(UpdateCacheItemFormat, tableNameWithSchema);
            DeleteCacheItem = string.Format(DeleteCacheItemFormat, tableNameWithSchema);
            DeleteExpiredCacheItems = string.Format(DeleteExpiredCacheItemsFormat, tableNameWithSchema);
            SetCacheItem = string.Format(SetCacheItemFormat, tableNameWithSchema);
            TableInfo = string.Format(TableInfoFormat, EscapeLiteral(schemaName), EscapeLiteral(tableName));
        }

        public string TableInfo { get; }

        public string GetCacheItem { get; }

        public string GetCacheItemWithoutValue { get; }

        public string SetCacheItem { get; }

        public string DeleteCacheItem { get; }

        public string DeleteExpiredCacheItems { get; }

        // From EF's SqlServerQuerySqlGenerator
        private string DelimitIdentifier(string identifier)
        {
            // return "[" + identifier.Replace("]", "]]") + "]";
            return '"' + identifier + '"';
        }

        private string EscapeLiteral(string literal)
        {
            return literal.Replace("'", "''");
        }
    }
}