using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.JsonPatch;
using EnumsNET;
using AyaNova.Util;
using AyaNova.Api.ControllerHelpers;
using AyaNova.Biz;
using AyaNova.Models;
using System.Collections.Generic;
using System.Linq;


namespace AyaNova.Biz
{
    internal static class PickListFetcher
    {

        internal static PickListResult GetPickList(AyContext ct, long userId, PagingOptions pagingOptions, string tableName)
        {
            
            List<NameIdItem> listItems = new List<NameIdItem>();

            using (var cm = ct.Database.GetDbConnection().CreateCommand())
            {

                ct.Database.OpenConnection();

                //BUILD THE QUERY
                //base query
                var q = $"SELECT id, name FROM {tableName} ";

                //GET THE FILTER / SORT
                if (pagingOptions.DataFilterId > 0)
                {
                    var TheFilter = ct.DataFilter.FirstOrDefault(x => x.Id == pagingOptions.DataFilterId);

                    //BUILD WHERE AND APPEND IT
                    q = q + FilterSqlCriteriaBuilder.DataFilterToSQLCriteria(TheFilter, WidgetBiz.FilterOptions(), userId);

                    //BUILD ORDER BY AND APPEND IT
                    q = q + FilterSqlOrderByBuilder.DataFilterToSQLOrderBy(TheFilter);
                }
                else
                {
                    //GET DEFAULT ORDER BY
                    q = q + FilterSqlOrderByBuilder.DefaultOrderBy();
                }

                cm.CommandText = q;
                using (var dr = cm.ExecuteReader())
                {                    
                    while (dr.Read())
                    {
                       listItems.Add(new NameIdItem() { Id = dr.GetInt64(0), Name = dr.GetString(1) });                       
                    }                    
                }
            }

            PickListResult ret = new PickListResult();
            ret.Items = listItems.ToArray();
            ret.TotalRecordCount = 9999;

            return ret;

        }


        // //Returns existance status of object type and id specified in database
        // internal static string Name(AyaType aytype, long id, System.Data.Common.DbCommand cmd)
        // {
        //     string TABLE = string.Empty;
        //     string COLUMN = "name";
        //     switch (aytype)
        //     {
        //         case AyaType.User:
        //             TABLE = "auser";
        //             break;
        //         case AyaType.Widget:
        //             TABLE = "awidget";
        //             break;

        //         case AyaType.FileAttachment:
        //             TABLE = "afileattachment";
        //             COLUMN = "displayfilename";
        //             break;
        //         case AyaType.DataFilter:
        //             TABLE = "adatafilter";
        //             break;
        //         default:
        //             throw new System.NotSupportedException($"AyaNova.BLL.BizObjectNameFetcher::Name type {aytype.ToString()} is not supported");
        //     }
        //     cmd.CommandText = $"SELECT m.{COLUMN} FROM {TABLE} AS m WHERE m.id = {id} LIMIT 1";
        //     using (var dr = cmd.ExecuteReader())
        //         return dr.Read() ? dr.GetString(0) : "UNKNOWN";
        // }





        /////////////////////////////////////////////////////////////////////

    }//eoc


}//eons

