//#define AYSHOWQUERYINFO

using System.Collections.Generic;
using System.Linq;
using Sockeye.Biz;
using Newtonsoft.Json.Linq;
using Microsoft.Extensions.Logging;
using Sockeye.Models;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace Sockeye.DataList
{
    internal static class DataListFetcher
    {

#if (AYSHOWQUERYINFO)
#if (DEBUG)
#warning FYI AYSHOWQUERYINFO is defined
#else 
#error ### HOLDUP: AYSHOWQUERYINFO is defined in a RELEASE BUILD!!!!
#endif
#endif
        ////////////////////////////////////////////////
        // Get the data list data requested
        //
        //
        internal static async Task<DataListReturnData> GetResponseAsync(AyContext ct, DataListTableProcessingOptions dataListTableProcessingOptions, IDataListProcessing DataList, AuthorizationRoles userRoles, ILogger log, long userId)
        {
            //#BUILD THE QUERY     

            //SELECT CLAUSE            
            var qSelect = DataListSqlSelectBuilder.BuildForDataTableListResponse(DataList.FieldDefinitions, dataListTableProcessingOptions.AllUniqueColumnKeysReferenced);

            //FROM CLAUSE             
            var qFrom = DataList.SQLFrom;

            var qWhere = string.Empty;
            var qOrderBy = string.Empty;

            //WHERE CLAUSE - FILTER            
            qWhere = DataListSqlFilterCriteriaBuilder.DataFilterToSQLCriteria(DataList.FieldDefinitions, dataListTableProcessingOptions);

            //ORDER BY CLAUSE - SORT 
            //BUILD ORDER BY 
            qOrderBy = DataListSqlFilterOrderByBuilder.DataFilterToSQLOrderBy(DataList.FieldDefinitions, dataListTableProcessingOptions);

            //LIMIT AND OFFSET CLAUSE - PAGING
            dataListTableProcessingOptions.Offset = dataListTableProcessingOptions.Offset ?? DataListTableProcessingOptions.DefaultOffset;
            dataListTableProcessingOptions.Limit = dataListTableProcessingOptions.Limit ?? DataListTableProcessingOptions.DefaultLimit;
            var qLimitOffset = $"LIMIT {dataListTableProcessingOptions.Limit} OFFSET {dataListTableProcessingOptions.Offset}";

            //PUT IT ALL TOGETHER
            string qDataQuery = string.Empty;
            string qTotalRecordsQuery = string.Empty;

            qDataQuery = $"{qSelect.Select} {qFrom} {qWhere} {qOrderBy} {qLimitOffset}".Replace("  ", " ");
            qTotalRecordsQuery = $"SELECT COUNT(*) {qFrom} {qWhere}".Replace("  ", " ");

            //RETURN OBJECTS                  
            int returnRowColumnCount = dataListTableProcessingOptions.Columns.Count();
            List<List<DataListField>> rows = new List<List<DataListField>>();
            long totalRecordCount = 0;
#if (DEBUG && AYSHOWQUERYINFO)
            System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();

#endif

            //QUERY THE DB
            using (var command = ct.Database.GetDbConnection().CreateCommand())
            {
                await ct.Database.OpenConnectionAsync();

                //GET DATA RETURN ROWS
                command.CommandText = qDataQuery;
                try
                {
#if (DEBUG && AYSHOWQUERYINFO)
                    stopWatch.Start();
#endif
                    using (var dr = await command.ExecuteReaderAsync())
                    {
#if (DEBUG && AYSHOWQUERYINFO)
                        stopWatch.Stop();
                        log.LogInformation($"(debug) DataListFetcher:GetResponse DATA query took {stopWatch.ElapsedMilliseconds}ms to execute: {qDataQuery}");
                        stopWatch.Reset();
#endif
                        while (dr.Read())
                        {
                            List<DataListField> row = new List<DataListField>(returnRowColumnCount);
                            //INSERT REMAINING FIELDS FROM TEMPLATE INTO THE RETURN ROWS LIST
                            foreach (string TemplateField in dataListTableProcessingOptions.Columns)
                            {
                                //get the AyaObjectFieldDefinition
                                DataListFieldDefinition f = DataList.FieldDefinitions.FirstOrDefault(z => z.FieldKey == TemplateField);
                                if (f == null)
                                {

                                    log.LogError($"DataListFetcher:GetResponseAsync Template field '{TemplateField}' was NOT found in the field definitions for data list {DataList.ToString()}");
                                    continue;
                                }
                                if (f.IsCustomField)
                                {
                                    DataListField AyaField = new DataListField();
                                    //could be null
                                    var rawValue = dr.GetValue(qSelect.map[f.GetSqlValueColumnName()]);
                                    if (rawValue != null)
                                    {
                                        string cust = rawValue.ToString();
                                        if (!string.IsNullOrWhiteSpace(cust))
                                        {
                                            JObject j = JObject.Parse(cust);
                                            //convert field name to cust name then get value
                                            var InternalCustomFieldName = FormFieldOptionalCustomizableReference.TranslateLTCustomFieldToInternalCustomFieldName(TemplateField);
                                            //Sometimes a custom field is specified but doesn't exist in the collection so don't assume it's there
                                            JToken o = j[InternalCustomFieldName];
                                            if (o != null)
                                                AyaField.v = o.Value<object>();
                                            else
                                                AyaField.v = null;
                                        }
                                        else
                                        {
                                            AyaField.v = null;
                                        }
                                    }
                                    row.Add(AyaField);
                                }
                                else
                                {
                                    DataListField AyaField = new DataListField();
                                    AyaField.v = dr.GetValue(qSelect.map[f.GetSqlValueColumnName()]);

                                    if (f.IsRowId)
                                    {
                                        AyaField.rid = true;
                                    }
                                    else
                                    {
                                        AyaField.rid = null;
                                    }

                                    if (f.SqlIdColumnName != null)
                                    {
                                        var ordinal = qSelect.map[f.SqlIdColumnName];
                                        if (!await dr.IsDBNullAsync(ordinal))
                                            AyaField.i = dr.GetInt64(ordinal);
                                    }

                                    if (f.SqlATypeColumnName != null)
                                    {
                                        var ordinal = qSelect.map[f.SqlATypeColumnName];
                                        if (!await dr.IsDBNullAsync(ordinal))
                                            AyaField.ot = dr.GetInt32(ordinal);
                                    }

                                    if (f.SqlColorColumnName != null)
                                    {
                                        var ordinal = qSelect.map[f.SqlColorColumnName];
                                        if (!await dr.IsDBNullAsync(ordinal))
                                            AyaField.clr = dr.GetString(ordinal);
                                    }

                                    row.Add(AyaField);
                                }
                            }
                            rows.Add(row);
                        }
                    }

                    //GET TOTAL RECORD COUNT
                    command.CommandText = qTotalRecordsQuery;
#if (DEBUG && AYSHOWQUERYINFO)
                    stopWatch.Start();
#endif
                    using (var dr = await command.ExecuteReaderAsync())
                    {
#if (DEBUG && AYSHOWQUERYINFO)
                        stopWatch.Stop();
                        log.LogInformation($"(debug) DataListFetcher:GetResponse COUNT query took {stopWatch.ElapsedMilliseconds}ms to execute: {qTotalRecordsQuery}");
#endif
                        if (dr.Read())
                        {
                            totalRecordCount = dr.GetInt64(0);
                        }
                    }
                }
                catch (Npgsql.PostgresException e)
                {
                    //log out the exception and the query
                    log.LogError("DataListFetcher:GetResponseAsync query failed.  Data Query was:");
                    log.LogError(qDataQuery);
                    log.LogError("Count Query was:");
                    log.LogError(qTotalRecordsQuery);
                    log.LogError(e, "DB Exception");
                    throw new System.Exception("DataListFetcher:GetResponseAsync - Query failed see log");

                }
                catch (System.Exception e)
                {
                    //ensure any other type of exception gets surfaced properly
                    //log out the exception and the query
                    log.LogError("DataListFetcher:GetResponseAsync unexpected failure.  Data Query was:");
                    log.LogError(qDataQuery);
                    log.LogError("Count Query was:");
                    log.LogError(qTotalRecordsQuery);
                    log.LogError(e, "Exception");
                    throw new System.Exception("DataListFetcher:GetResponseAsync - unexpected failure see log");
                }
            }

            //BUILD THE COLUMNS RETURN PROPERTY JSON FRAGMENT          
            Newtonsoft.Json.Linq.JArray ColumnsJSON = null;
            ColumnsJSON = DataList.GenerateReturnListColumns(dataListTableProcessingOptions.Columns);
            return new DataListReturnData(rows,
                                          totalRecordCount,
                                          ColumnsJSON,
                                          dataListTableProcessingOptions.SortBy,
                                          dataListTableProcessingOptions.Filter.Where(z => z.Column.StartsWith("meta") == false).ToList(),
                                          dataListTableProcessingOptions.HiddenAffectiveColumns);
        }



        /////////////////////////////////////////////////////////////////
        // Get a list of id's of the datalist results for reporting
        // (and other uses like job ops, exporting etc)
        // called from RehydrateIdList only
        //
        internal static async Task<long[]> GetIdListResponseAsync(
            AyContext ct,
            DataListSelectedProcessingOptions dataListSelectionOptions,
            IDataListProcessing DataList,
            AuthorizationRoles userRoles,
            ILogger log,
            long userId,
            bool limitForReportDesigner)
        {
            //#BUILD THE QUERY   

            //SELECT FRAGMENT COLUMNS FROM TEMPLATE               
            var qSelect = DataListSqlSelectBuilder.BuildForIdListResponse(DataList.FieldDefinitions, dataListSelectionOptions);

            //FROM CLAUSE             
            var qFrom = DataList.SQLFrom;

            var qWhere = string.Empty;
            var qOrderBy = string.Empty;

            //WHERE CLAUSE - FILTER            
            qWhere = DataListSqlFilterCriteriaBuilder.DataFilterToSQLCriteria(DataList.FieldDefinitions, dataListSelectionOptions);

            //ORDER BY CLAUSE - SORT           
            qOrderBy = DataListSqlFilterOrderByBuilder.DataFilterToSQLOrderBy(DataList.FieldDefinitions, dataListSelectionOptions);

            //LIMIT (if report designer)
            var qLimit = string.Empty;
            if (limitForReportDesigner)
                qLimit = "LIMIT 5";

            //PUT IT ALL TOGETHER
            string qDataQuery = string.Empty;

            qDataQuery = $"{qSelect} {qFrom} {qWhere} {qOrderBy} {qLimit} ".Replace("  ", " ");

            //RETURN OBJECTS                  
            var retList = new List<long>();

            using (var command = ct.Database.GetDbConnection().CreateCommand())
            {
                await ct.Database.OpenConnectionAsync();
                command.CommandText = qDataQuery;
                try
                {
                    using (var dr = await command.ExecuteReaderAsync())
                    {
                        while (dr.Read())
                        {
                            //only one column and it's the zeroth id column
                            if (!dr.IsDBNull(0))
                                retList.Add(dr.GetInt64(0));
                        }
                    }
                }
                catch (Npgsql.PostgresException e)
                {
                    //log out the exception and the query
                    log.LogError("DataListFetcher:GetIdListResponseAsync query failed unexpectedly.  IDList Query was:");
                    log.LogError(qDataQuery);

                    log.LogError(e, "DB Exception");
                    throw new System.Exception("DataListFetcher:GetIdListResponseAsync - Query failed see log");
                }
                catch (System.Exception e)
                {
                    //ensure any other type of exception gets surfaced properly
                    //log out the exception and the query
                    log.LogError("DataListFetcher:GetIdListResponseAsync unexpected failure.  IDList Query was:");
                    log.LogError(qDataQuery);

                    log.LogError(e, "Exception");
                    throw new System.Exception("DataListFetcher:GetIdListResponseAsync - unexpected failure see log");
                }
            }
            return retList.ToArray();
        }


    }//eoc
}//eons