using System.Collections.Generic;
using System;
using System.Globalization;
using System.Text;
using Newtonsoft.Json.Linq;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace AyaNova.Biz
{
    public static class FilterSqlCriteriaBuilder
    {       
        public static string DataFilterToSQLCriteria(AyaNova.Models.DataFilter dataFilter, FilterOptions filterOptions, long userId)
        {

            if (string.IsNullOrWhiteSpace(dataFilter.Filter))
            {
                return "";
            }

            StringBuilder sb = new StringBuilder();

            //iterate the datafilter and concatenate a sql query from it
            var FilterArray = JArray.Parse(dataFilter.Filter);
            for (int i = 0; i < FilterArray.Count; i++)
            {

                var filterItem = FilterArray[i];
                var fld = filterItem["fld"].Value<string>();
                var opType = filterItem["op"].Value<string>();
                List<string> tagList = new List<string>();
                string val = string.Empty;
                if (filterItem["value"].Type != JTokenType.Array)
                    val = filterItem["value"].Value<string>();
                else
                {
                    tagList = filterItem["value"].ToObject<List<String>>();
                }

                var dataType = filterOptions.Flds.Find(x => x.Fld == fld).Type;
                sb.Append("(");
                sb.Append(DataFilterToColumnCriteria(fld, dataType, opType, val, tagList, userId));
                if (i < FilterArray.Count - 1)
                {
                    sb.Append(") AND ");
                }
            }

            return " where " + sb.ToString() + ")";
        }

        /// <summary>
        /// Translate DataFilter to Postgres friendly SQL criteria
        /// </summary>
        private static string DataFilterToColumnCriteria(string sColumn, string sDataType, string sOperator, string sValue, List<string> sTags, long userId)//, bool IsCompound)
        {
            bool TagFilter = sTags.Count < 0;
            StringBuilder sb = new StringBuilder();
            //Column name
            //sb.Append(" ");
            sb.Append(sColumn);
            sb.Append(" ");

            //handle null values separately           
            if (!TagFilter && sValue == FilterSpecialToken.Null)
            {
                switch (sDataType)
                {
                    case AyDataType.Text:
                        {
                            if (sOperator == FilterComparisonOperator.Equality)
                            {

                                sb.Append("Is Null");
                                sb.Append(" OR ");
                                sb.Append(sColumn);
                                sb.Append(" = ''");
                            }
                            else
                                sb.Append(" <> ''");
                        }
                        break;
                    default:
                        {
                            if (sOperator == FilterComparisonOperator.Equality)
                                sb.Append("Is Null");
                            else
                                sb.Append("Is Not Null");
                        }
                        break;
                }
            }
            else
            {//non null value 

                //Changed 21-March-2006:
                //But wait!  Both mssql and firebird won't include a null result
                //on the filter field so in some cases we need to include them
                //i.e. if a value is supposed to be not equal to a specific non-null value
                //then null is a valid value to return but won't be normally
                //so....
                //Special addition to handle nulls

                if (!TagFilter)
                {
                    switch (sOperator)
                    {
                        case FilterComparisonOperator.Equality:
                            //no change on equals for nulls
                            break;
                        case FilterComparisonOperator.GreaterThan:
                            //no change on greater than for nulls
                            //(nulls are going to be assumed to be always at the
                            //less than end of the scale)
                            break;
                        case FilterComparisonOperator.GreaterThanOrEqualTo:
                            //no change on greater than for nulls
                            //(nulls are going to be assumed to be always at the
                            //less than end of the scale)
                            break;
                        case FilterComparisonOperator.LessThan:
                            sb.Append("Is Null OR ");
                            sb.Append(sColumn);
                            sb.Append(" ");
                            break;
                        case FilterComparisonOperator.LessThanOrEqualTo:
                            sb.Append("Is Null OR ");
                            sb.Append(sColumn);
                            sb.Append(" ");
                            break;
                        // case "Like":
                        //     //No change on like
                        //     break;
                        case FilterComparisonOperator.NotEqual:
                            //This is the big one:
                            sb.Append("Is Null OR ");
                            sb.Append(sColumn);
                            sb.Append(" ");
                            break;
                    }
                }

                #region Build for specific type
                switch (sDataType)
                {
                    case AyDataType.Text:
                        //escape any pre-existing apostrophes
                        //i.e. "O'Flaherty's pub"
                        sValue = sValue.Replace("'", "''");

                        //case 1951 - unescape ampersands
                        sValue = sValue.Replace("&amp;", "&");

                        //RAVEN NOTE: Decided not to implement TEXT token criteria for now (TTM, probably not useful)
                        //meaning the A-H etc group ranges by alpha bet chunk

                        #region Build TEXT OPS criteria
                        switch (sOperator)
                        {
                            case FilterComparisonOperator.Equality:
                                sb.Append("='");
                                sb.Append(sValue);
                                sb.Append("'");
                                break;

                            case FilterComparisonOperator.GreaterThan:
                                sb.Append(">'");
                                sb.Append(sValue);
                                sb.Append("'");
                                break;

                            case FilterComparisonOperator.GreaterThanOrEqualTo:
                                sb.Append(">='");
                                sb.Append(sValue);
                                sb.Append("'");
                                break;

                            case FilterComparisonOperator.LessThan:
                                sb.Append("<'");
                                sb.Append(sValue);
                                sb.Append("'");
                                break;

                            case FilterComparisonOperator.LessThanOrEqualTo:
                                sb.Append("<='");
                                sb.Append(sValue);
                                sb.Append("'");
                                break;

                            case FilterComparisonOperator.NotEqual:
                                sb.Append("<>'");
                                sb.Append(sValue);
                                sb.Append("'");
                                break;

                            //Following 7 operators added 14-June-2006
                            case FilterComparisonOperator.NotContains:
                                sb.Append("Not Like '%");
                                sb.Append(sValue);
                                sb.Append("%'");
                                break;

                            case FilterComparisonOperator.Contains:
                                sb.Append("Like '%");
                                sb.Append(sValue);
                                sb.Append("%'");
                                break;

                            case FilterComparisonOperator.StartsWith:
                                sb.Append("Like '");
                                sb.Append(sValue);
                                sb.Append("%'");
                                break;

                            case FilterComparisonOperator.EndsWith:
                                sb.Append("Like '%");
                                sb.Append(sValue);
                                sb.Append("'");
                                break;

                            // case "DoesNotStartWith":
                            //     sb.Append("Not Like '");
                            //     sb.Append(sValue);
                            //     sb.Append("%'");
                            //     break;

                            // case "DoesNotEndWith":
                            //     sb.Append("Not Like '%");
                            //     sb.Append(sValue);
                            //     sb.Append("'");
                            //     break;

                            // case "NotLike":
                            //     sb.Append("Not Like '");
                            //     sb.Append(sValue);
                            //     sb.Append("%'");
                            //     break;

                            default:
                                throw new System.ArgumentOutOfRangeException("OPERATOR_TYPE", sOperator, "GridToSqlCriteria unhandled operator type [" + sOperator + "] IN STRING");

                        }
                        #endregion build text ops criteria
                        break;
                    case AyDataType.Bool:
                        {
                            switch (sOperator)
                            {
                                case FilterComparisonOperator.Equality:
                                    sb.Append("= ");
                                    if (sValue.ToLowerInvariant() == "true")
                                        sb.Append("true");
                                    else
                                        sb.Append("false");
                                    break;

                                case FilterComparisonOperator.NotEqual:
                                    sb.Append("<> ");
                                    if (sValue.ToLowerInvariant() == "true")
                                        sb.Append("true");
                                    else
                                        sb.Append("false");

                                    break;
                                default:
                                    throw new System.ArgumentOutOfRangeException("OPERATOR_TYPE", sOperator, "GridToSqlCriteria unhandled operator type [" + sOperator + "] in BOOL");

                            }
                        }
                        break;

                    case AyDataType.Date:
                        {
                            //Note: it is assumed all dates come into here from the CLIENT in UTC iso8601 format 
                            //suitable for the database to handle as all database dates are in UTC
                            //Local display and parsing will be considered a CLIENT issue at all times

                            //HOWEVER, if it's a relative date filter TOKEN like "nextMonth" then the users time zone offset will be taken into consideration

                            //So this is the core date time to work off of
                            DateTime RelativeToday = DateTime.Today;
                            DateTime RelativeNow = DateTime.Now;
                            // ILogger log = AyaNova.Util.ApplicationLogging.CreateLogger("FilterSqlCriteriaBuilder::DataFilterToColumnCriteria");
                            // log.LogInformation("RelativeToday (before adjustment):");
                            // log.LogInformation(RelativeToday.ToString());
                            // log.LogInformation("RelativeNow (before adjustment):");
                            // log.LogInformation(RelativeNow.ToString());


                            if (sValue.StartsWith("{[") && sValue.EndsWith("]}"))
                            {
                                //Need to adjust RelativeToday to users time frame
                                //Fetch useroptions object and relative time offset
                                AyaNova.Models.AyContext ct = AyaNova.Util.ServiceProviderProvider.DBContext;
                                var u = ct.User.AsNoTracking().Where(a => a.Id == userId).Select(m => new { tz = m.UserOptions.TimeZoneOffset }).First();

                                //Add this value to any time's hours to convert to user local time
                                Double TimeZoneAdjustment = ((double)u.tz) * -1;

                                //Stock times used for many of the tokens:
                                RelativeToday = RelativeToday.AddHours(TimeZoneAdjustment);//flip the sign to adjust towards UTC
                                RelativeNow = RelativeNow.AddHours(TimeZoneAdjustment);//flip the sign to adjust towards UTC

                                //TESTING:
                                //LOG THE CRIT AND QUERY
                                //  ILogger log = AyaNova.Util.ApplicationLogging.CreateLogger("FilterSqlCriteriaBuilder::DataFilterToColumnCriteria");
                                // log.LogInformation("RelativeToday (adjusted):");
                                // log.LogInformation(RelativeToday.ToString());
                                // log.LogInformation("RelativeNow (adjusted):");
                                // log.LogInformation(RelativeNow.ToString());
                                // log.LogInformation("Offset used:");
                                // log.LogInformation(u.tz.ToString());

                                #region Build criteria for date RANGE TOKEN specified
                                //Used as the basis point
                                System.DateTime dtAfter;
                                System.DateTime dtBefore;
                                switch (sValue)
                                {
                                    //Case 402
                                    case FilterSpecialToken.Yesterday:
                                        //Between Day before yesterday at midnight and yesterday at midnight
                                        dtAfter = RelativeToday.AddDays(-1);
                                        dtAfter = dtAfter.AddSeconds(-1);
                                        dtBefore = RelativeToday;//.AddDays(-1);
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;

                                    case FilterSpecialToken.Today:
                                        //Between yesterday at midnight and tommorow at midnight
                                        dtAfter = RelativeToday.AddSeconds(-1);
                                        dtBefore = RelativeToday.AddDays(1);
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;
                                    case FilterSpecialToken.Tomorrow:
                                        //Between Tonight at midnight and day after tommorow at midnight
                                        dtAfter = RelativeToday.AddDays(1);
                                        dtAfter = dtAfter.AddSeconds(-1);
                                        dtBefore = RelativeToday.AddDays(2);
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;

                                    //Case 402
                                    case FilterSpecialToken.LastWeek:
                                        //Between two Sundays ago at midnight and last sunday at midnight
                                        dtAfter = RelativeToday;

                                        //go back a week
                                        dtAfter = dtAfter.AddDays(-7);

                                        //go backwards to Sunday
                                        while (dtAfter.DayOfWeek != DayOfWeek.Sunday)
                                            dtAfter = dtAfter.AddDays(-1);

                                        //go to very start of eighth dayahead
                                        dtBefore = dtAfter.AddDays(8);

                                        dtAfter = dtAfter.AddSeconds(-1);

                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;


                                    case FilterSpecialToken.ThisWeek:
                                        //Between Sunday at midnight and Next sunday at midnight
                                        dtAfter = RelativeToday;
                                        //go backwards to monday
                                        while (dtAfter.DayOfWeek != DayOfWeek.Monday)
                                            dtAfter = dtAfter.AddDays(-1);

                                        //Now go back to sunday last second
                                        dtAfter = dtAfter.AddSeconds(-1);



                                        dtBefore = RelativeToday;
                                        //go forwards to monday
                                        if (RelativeToday.DayOfWeek == DayOfWeek.Monday)
                                        {
                                            //Monday today? then go to next monday
                                            dtBefore = dtBefore.AddDays(7);
                                        }
                                        else
                                        {
                                            while (dtBefore.DayOfWeek != DayOfWeek.Monday)
                                                dtBefore = dtBefore.AddDays(1);
                                        }

                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;
                                    case FilterSpecialToken.NextWeek:
                                        //Between Next Sunday at midnight and Next Next sunday at midnight
                                        dtAfter = RelativeToday;

                                        //If today is monday skip over it first
                                        if (dtAfter.DayOfWeek == DayOfWeek.Monday)
                                            dtAfter = dtAfter.AddDays(1);

                                        //go forwards to next monday
                                        while (dtAfter.DayOfWeek != DayOfWeek.Monday)
                                            dtAfter = dtAfter.AddDays(1);

                                        //Now go back to sunday last second
                                        dtAfter = dtAfter.AddDays(-1);

                                        //go seven days ahead
                                        dtBefore = dtAfter.AddDays(7);

                                        //case 1155
                                        dtAfter = dtAfter.AddSeconds(-1);

                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;
                                    case FilterSpecialToken.LastMonth:
                                        //start with the first day of this month
                                        dtAfter = new DateTime(RelativeToday.Year, RelativeToday.Month, 1, RelativeToday.Hour, RelativeToday.Minute, 00);
                                        //subtract a Month
                                        dtAfter = dtAfter.AddMonths(-1);

                                        //Add one month to dtAfter to get end date
                                        dtBefore = dtAfter.AddMonths(1);

                                        //case 1155
                                        dtAfter = dtAfter.AddSeconds(-1);

                                        // 'yyyy-mm-ddTHH:MM:SS' 

                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;
                                    case FilterSpecialToken.ThisMonth:
                                        //start with the first day of this month
                                        dtAfter = new DateTime(RelativeToday.Year, RelativeToday.Month, 1, RelativeToday.Hour, RelativeToday.Minute, 00);

                                        //Add one month to dtAfter to get end date
                                        dtBefore = dtAfter.AddMonths(1);

                                        //case 1155
                                        dtAfter = dtAfter.AddSeconds(-1);

                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;

                                    case FilterSpecialToken.NextMonth:
                                        //start with the first day of this month
                                        dtAfter = new DateTime(RelativeToday.Year, RelativeToday.Month, 1, RelativeToday.Hour, RelativeToday.Minute, 00);
                                        //Add a Month
                                        dtAfter = dtAfter.AddMonths(1);

                                        //Add one month to dtAfter to get end date
                                        dtBefore = dtAfter.AddMonths(1);

                                        //case 1155
                                        dtAfter = dtAfter.AddSeconds(-1);
                                        //TODO: REMOVE THIS TESTING
                                        // log.LogInformation($"NEXT MONTH TOKEN BUILDER IMMEDIATELY BEFORE BUILDING FRAGMENT: BEFORE={dtBefore.ToString()} AND AFTER={dtAfter.ToString()}");
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;
                                    case FilterSpecialToken.FourteenDayWindow:
                                        //start with today zero hour
                                        dtAfter = new DateTime(RelativeToday.Year, RelativeToday.Month, RelativeToday.Day, RelativeToday.Hour, RelativeToday.Minute, 00);
                                        dtAfter = dtAfter.AddDays(-7);

                                        //Add 15 days to get end date (zero hour so not really 15 full days)
                                        dtBefore = dtAfter.AddDays(15);

                                        //case 1155
                                        dtAfter = dtAfter.AddSeconds(-1);

                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;


                                    //case 2067 ADDITIONAL DATE RANGES ************

                                    case FilterSpecialToken.Past:
                                        //Forever up to Now 
                                        dtAfter = new DateTime(1753, 1, 2, 00, 00, 00);
                                        dtBefore = DateTime.UtcNow;
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;

                                    case FilterSpecialToken.Future:
                                        //From Now to forever (999 years from now)                                        
                                        dtAfter = DateTime.UtcNow;
                                        dtBefore = DateTime.UtcNow.AddYears(999);
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;

                                    case FilterSpecialToken.LastYear:
                                        //From zero hour january 1 a year ago
                                        dtAfter = new DateTime(RelativeNow.AddYears(-1).Year, 1, 1, 0, 0, 00).AddSeconds(-1).AddHours(TimeZoneAdjustment);
                                        //To zero hour January 1 this year
                                        dtBefore = new DateTime(RelativeNow.Year, 1, 1, 0, 0, 00).AddHours(TimeZoneAdjustment);
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;

                                    case FilterSpecialToken.ThisYear:
                                        //From zero hour january 1 this year
                                        dtAfter = new DateTime(RelativeNow.Year, 1, 1, 00, 00, 00).AddSeconds(-1).AddHours(TimeZoneAdjustment);
                                        //To zero hour Jan 1 next year
                                        dtBefore = new DateTime(RelativeNow.AddYears(1).Year, 1, 1, 00, 00, 00).AddHours(TimeZoneAdjustment);
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;

                                    case FilterSpecialToken.InTheLast3Months:
                                        //From Now minus 3 months
                                        dtAfter = DateTime.UtcNow.AddMonths(-3);
                                        //To Now
                                        dtBefore = DateTime.UtcNow;
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;

                                    case FilterSpecialToken.InTheLast6Months:
                                        //From Now minus 6 months
                                        dtAfter = DateTime.UtcNow.AddMonths(-6);
                                        //To Now
                                        dtBefore = DateTime.UtcNow;
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;

                                    case FilterSpecialToken.InTheLastYear:
                                        //From Now minus 365 days
                                        dtAfter = DateTime.UtcNow.AddDays(-365);
                                        //To Now
                                        dtBefore = DateTime.UtcNow;
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;

                                    //=======================
                                    //NEW ONES FOR RAVEN


                                    case FilterSpecialToken.YearToDate:
                                        //From zero hour january 1 this year
                                        dtAfter = new DateTime(RelativeNow.Year, 1, 1, 00, 00, 00).AddSeconds(-1).AddHours(TimeZoneAdjustment); ;
                                        //To now
                                        dtBefore = RelativeNow;
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;

                                    case FilterSpecialToken.Past90Days:
                                        //From Now minus 90 days
                                        dtAfter = DateTime.UtcNow.AddDays(-90);
                                        //To Now
                                        dtBefore = DateTime.UtcNow;
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;
                                    case FilterSpecialToken.Past30Days:
                                        //From Now minus 30 days
                                        dtAfter = DateTime.UtcNow.AddDays(-30);
                                        //To Now
                                        dtBefore = DateTime.UtcNow;
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;
                                    case FilterSpecialToken.Past24Hours:
                                        //From Now minus 24 hours
                                        dtAfter = DateTime.UtcNow.AddHours(-24);
                                        //To Now
                                        dtBefore = DateTime.UtcNow;
                                        BuildBetweenTwoDatesFragment(sColumn, sb, dtAfter, dtBefore);
                                        break;

                                    default:
                                        throw new System.ArgumentOutOfRangeException("TOKEN", sOperator, "GridToSqlCriteria invalid filter TOKEN type [" + sValue + "] IN DATE_TIME");

                                        //-----
                                }

                                #endregion
                            }
                            else
                            {
                                #region Build criteria for date specified
                                //RAVEN NOTE: it is my intention that dates come in iso8601 UTC format from the client
                                //so a simple parse should be sufficient
                                //https://docs.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings#Roundtrip
                                System.DateTime dtData = DateTime.Parse(sValue);

                                string sHighEnd = PostgresDateFormat(MaxMilliseconds(dtData));
                                string sLowEnd = PostgresDateFormat(ZeroMilliseconds(dtData));

                                switch (sOperator)
                                {
                                    case FilterComparisonOperator.Equality:
                                        sb.Append(">='");
                                        sb.Append(sLowEnd);
                                        sb.Append("' AND ");
                                        sb.Append(sColumn);
                                        sb.Append(" ");
                                        sb.Append("<='");
                                        sb.Append(sHighEnd);
                                        sb.Append("'");
                                        break;

                                    case FilterComparisonOperator.GreaterThan:
                                        sb.Append(">'");
                                        sb.Append(sLowEnd);
                                        sb.Append("'");
                                        break;

                                    case FilterComparisonOperator.GreaterThanOrEqualTo:
                                        sb.Append(">='");
                                        sb.Append(sLowEnd);
                                        sb.Append("'");
                                        break;
                                    case FilterComparisonOperator.LessThan:
                                        sb.Append("<'");
                                        sb.Append(sHighEnd);
                                        sb.Append("'");
                                        break;
                                    case FilterComparisonOperator.LessThanOrEqualTo:
                                        sb.Append("<='");
                                        sb.Append(sHighEnd);
                                        sb.Append("'");
                                        break;

                                    case FilterComparisonOperator.NotEqual:
                                        sb.Append("<'");
                                        sb.Append(sLowEnd);
                                        sb.Append("' OR ");
                                        sb.Append(sColumn);
                                        sb.Append(" ");
                                        sb.Append(">'");
                                        sb.Append(sHighEnd);
                                        sb.Append("'");

                                        break;
                                    default:
                                        throw new System.ArgumentOutOfRangeException("OPERATOR_TYPE", sOperator, "GridToSqlCriteria unhandled operator type [" + sOperator + "] IN DATE_TIME");


                                }
                                #endregion
                            }
                        }
                        break;
                    case AyDataType.Enum://enums are just ints to the db, but it's a special type so the client can recognize it
                    case AyDataType.Decimal:
                    case AyDataType.Integer:    //whole numbers, not only integer                
                        {
                            //case 1795 - it's numeric, convert to locale independant format
                            //RAVEN NOTE: this code looks suspect to me, but I'll leave it in for now
                            NumberFormatInfo nfi = System.Globalization.CultureInfo.CurrentCulture.NumberFormat;
                            switch (sDataType)
                            {
                                case AyDataType.Decimal:
                                    {
                                        if (nfi.CurrencyDecimalSeparator != ".")
                                        {
                                            sValue = sValue.Replace(nfi.CurrencyGroupSeparator, "");
                                            sValue = sValue.Replace(nfi.CurrencyDecimalSeparator, ".");
                                        }
                                    }
                                    break;
                                case AyDataType.Integer:
                                    {
                                        if (nfi.NumberDecimalSeparator != ".")
                                        {
                                            sValue = sValue.Replace(nfi.NumberGroupSeparator, "");
                                            sValue = sValue.Replace(nfi.NumberDecimalSeparator, ".");
                                        }
                                    }
                                    break;
                            }



                            switch (sOperator)
                            {
                                case FilterComparisonOperator.Equality:
                                    sb.Append("=");
                                    sb.Append(sValue);
                                    break;
                                case FilterComparisonOperator.GreaterThan:
                                    sb.Append(">");
                                    sb.Append(sValue);
                                    break;
                                case FilterComparisonOperator.GreaterThanOrEqualTo:
                                    sb.Append(">=");
                                    sb.Append(sValue);
                                    break;
                                case FilterComparisonOperator.LessThan:
                                    sb.Append("<");
                                    sb.Append(sValue);
                                    break;
                                case FilterComparisonOperator.LessThanOrEqualTo:
                                    sb.Append("<=");
                                    sb.Append(sValue);
                                    break;
                                case FilterComparisonOperator.NotEqual:
                                    sb.Append("<>");
                                    sb.Append(sValue);
                                    break;
                                default:
                                    throw new System.ArgumentOutOfRangeException("OPERATOR_TYPE", sOperator, "GridToSqlCriteria unhandled operator type [" + sOperator + "] IN NUMBER");


                            }
                            break;
                        }
                    case AyDataType.Tags:
                        {
                            //Build tags filter fragment
                            //for initial release a tag filter is inclusive of all tags only
                            //in other words all tags presented must be in record to match (simple AND)
                            //select * from awidget where awidget.tags @> array['blah','blah3'::varchar(255)]
                            StringBuilder sbTags = new StringBuilder();
                            sbTags.Append("@> array[");
                            List<string> normalizedTags = TagUtil.NormalizeTags(sTags);
                            foreach (string s in normalizedTags)
                            {

                                sbTags.Append($"'{s}',");
                            }
                            sb.Append(sbTags.ToString().TrimEnd(','));
                            sb.Append("::varchar(255)]");
                        }
                        break;
                    default:
                        throw new System.ArgumentOutOfRangeException("DATA_TYPE", sDataType, "GridToSqlCriteria unhandled data type[" + sDataType + "]");
                }
                #endregion
            }//end of nonnull path

            return sb.ToString();


        }

        //This is only used by the token date query code above and that code does convert to UTC time to match the DB so this function will not need to handle that
        //The other non tokenized date criteria builders are all working with dates that come from the client in UTC already and they don't use this method 
        //so nothing required there
        private static void BuildBetweenTwoDatesFragment(string sColumn, StringBuilder sb, DateTime dtAfter, DateTime dtBefore)
        {
            sb.Append(">'");
            sb.Append(PostgresDateFormat(dtAfter));
            sb.Append("' AND ");
            sb.Append(sColumn);
            sb.Append(" ");
            sb.Append("<'");
            sb.Append(PostgresDateFormat(dtBefore));
            sb.Append("'");
        }

        // public static string SqlDateFormatHighLow(bool bhigh)
        // {
        //     //new test version
        //     if (bhigh)
        //         return SqlDateFormat() + ".999";
        //     else
        //         return SqlDateFormat() + ".000";

        // }


        private static DateTime ZeroMilliseconds(DateTime d)
        {
            if (d.Millisecond == 0) return d;
            return new DateTime(d.Year, d.Month, d.Day, d.Hour, d.Minute, d.Second, DateTimeKind.Utc);
        }
        private static DateTime MaxMilliseconds(DateTime d)
        {
            if (d.Millisecond == 999) return d;
            return new DateTime(d.Year, d.Month, d.Day, d.Hour, d.Minute, d.Second, 999, DateTimeKind.Utc);
        }

        /// <summary>
        /// Postgres compatible date format 
        /// https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-DATE-TABLE
        /// </summary>
        /// <returns></returns>
        private static string PostgresDateFormat(DateTime theDate)
        {
            //TODO: Double check that this returns the date as an actual converted to UTC date and not just the local time in UTC format

            //If this was used it should be like this for a UTC date to iso8601
            // ISO8601 with 7 decimal places
            return theDate.ToString("o", CultureInfo.InvariantCulture);

            // if (DBUtil.DB.DBType == DataBaseType.MSSQL)
            //     return "yyyy-MM-ddTHH:mm:ss";
            // else
            //     return "yyyy-MM-dd HH:mm:ss";
        }

    }//eoc
}//ens
