using System.Data;
using System.Linq;
using Dapper;
using DataModel.Models;
using DataModel.Process;
using Newtonsoft.Json;
using WorkerService.Models.Reporting;
using WorkerService.Templates;

namespace WorkerService.Helpers
{
    /// <summary>
    /// SearchModel class helper functions
    /// </summary>
    public static class SearchModelHelpers
    {
        /// <summary>
        /// Converts the given search to a search reporting model
        /// </summary>
        /// <param name="search">Search to convert</param>
        /// <returns>Search reporting model</returns>
        public static SearchModel ToSearchModel(this Search search)
        {
            //Load search details
            SearchModel model = new SearchModel
            {
                ID = search.ID,
                UserName = search.UserName,
                Name = search.Name,
                SubmitDT = search.SubmitDT,
                StartDT = search.StartDT,
                EndDT = search.EndDT
            };

            //Load search critera
            SearchCriteria criteria = JsonConvert.DeserializeObject<SearchCriteria>(search.CriteriaJSON);
            model.MinimumDT = criteria.MinimumDT;
            model.MaximumDT = criteria.MaximumDT;
            model.WorkOrderFilter.AddRange(LotFinderDB.LookupWorkorders(criteria.WorkOrderNumbers).Select(wo => new WorkOrderFilterEntry() { WorkOrderNumber = wo.WorkOrderNumber, ItemNumber = wo.ItemNumber }));
            model.ItemNumberFilter.AddRange(LotFinderDB.LookupItems(criteria.ItemNumbers).Select(i => new ItemNumberFilterEntry() { ItemNumber = i.ItemNumber, ItemDescription = i.Description }));
            model.ProfitCenterFilter.AddRange(LotFinderDB.LookupProfitCenters(criteria.ProfitCenters).Select(pc => new ProfitCenterFilterEntry() { Code = pc.Code, Description = pc.Description }));
            model.WorkCenterFilter.AddRange(LotFinderDB.LookupWorkCenters(criteria.WorkCenters).Select(wc => new WorkCenterFilterEntry() { Code = wc.Code, Description = wc.Description }));
            model.ComponentLotFilter.AddRange(LotFinderDB.LookupLots(criteria.ComponentLotNumbers).Select(cl => new ComponentLotFilterEntry() { LotNumber = cl.LotNumber, ItemNumber = cl.ItemNumber }));
            model.OperatorFilter.AddRange(LotFinderDB.LookupUsers(criteria.OperatorIDs).Select(o => new OperatorFilterEntry() { AddressNumber = o.AddressNumber, UserID = o.UserID, FullName = o.FullName }));
            model.ItemOperationMisFilter.AddRange(criteria.PartOperations.Select(po => new ItemOperationMisFilterEntry() { ItemNumber = po.ItemNumber, OperationNumber = po.OperationNumber, MisNumber = po.MisNumber, MisRevision = po.MisRevision }));
            model.ExtractMisData = criteria.ExtractMisData;

            return model;
        }

        /// <summary>
        /// Generates the search query for the given search model
        /// </summary>
        /// <param name="model">Search model to generate search query for</param>
        /// <returns>Search query for the given search model</returns>
        public static string GetQuery(this SearchModel model)
        {
            QueryTemplate queryTemplate = new QueryTemplate(model);
            return queryTemplate.TransformText();
        }

        /// <summary>
        /// Checks if work order step data should be searched for the given search model
        /// </summary>
        /// <param name="model">Search model to evaluate</param>
        /// <returns>Whether or not work order step data should be searched for the given search model</returns>
        public static bool ShouldSearchSteps(this SearchModel model)
        {
            return model.MinimumDT.HasValue || model.MaximumDT.HasValue || model.ProfitCenterFilterEnabled || model.WorkCenterFilterEnabled || model.OperatorFilterEnabled;
        }

        /// <summary>
        /// Creates table-valued-parameter for search's work order filter
        /// </summary>
        /// <param name="model">Search to create work order filter parameter for</param>
        /// <returns>Table-valued-parameter for search's work order filter</returns>
        public static SqlMapper.ICustomQueryParameter CreateWorkOrderFilterParameter(this SearchModel model)
        {
            //Generate data table with filter data
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("WorkOrderNumber", typeof(long));

            foreach (WorkOrderFilterEntry workOrderFilterEntry in model.WorkOrderFilter)
            {
                dataTable.Rows.Add(workOrderFilterEntry.WorkOrderNumber);
            }

            return dataTable.AsTableValuedParameter("WorkOrderFilterParameter");
        }

        /// <summary>
        /// Creates table-valued-parameter for search's item number filter
        /// </summary>
        /// <param name="model">Search to create item number filter parameter for</param>
        /// <returns>Table-valued-parameter for search's item number filter</returns>
        public static SqlMapper.ICustomQueryParameter CreateItemNumberFilterParameter(this SearchModel model)
        {
            //Generate data table with filter data
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("ItemNumber", typeof(string));

            foreach (ItemNumberFilterEntry itemNumberFilterEntry in model.ItemNumberFilter)
            {
                dataTable.Rows.Add(itemNumberFilterEntry.ItemNumber);
            }

            return dataTable.AsTableValuedParameter("ItemNumberFilterParameter");
        }

        /// <summary>
        /// Creates table-valued-parameter for search's profit center filter
        /// </summary>
        /// <param name="model">Search to create profit center filter parameter for</param>
        /// <returns>Table-valued-parameter for search's profit center filter</returns>
        public static SqlMapper.ICustomQueryParameter CreateProfitCenterFilterParameter(this SearchModel model)
        {
            //Generate data table with filter data
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("Code", typeof(string));

            foreach (ProfitCenterFilterEntry profitCenterFilterEntry in model.ProfitCenterFilter)
            {
                dataTable.Rows.Add(profitCenterFilterEntry.Code);
            }

            return dataTable.AsTableValuedParameter("ProfitCenterFilterParameter");
        }

        /// <summary>
        /// Creates table-valued-parameter for search's work center filter
        /// </summary>
        /// <param name="model">Search to create work center filter parameter for</param>
        /// <returns>Table-valued-parameter for search's work center filter</returns>
        public static SqlMapper.ICustomQueryParameter CreateWorkCenterFilterParameter(this SearchModel model)
        {
            //Generate data table with filter data
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("Code", typeof(string));

            foreach (WorkCenterFilterEntry workCenterFilterEntry in model.WorkCenterFilter)
            {
                dataTable.Rows.Add(workCenterFilterEntry.Code);
            }

            return dataTable.AsTableValuedParameter("WorkCenterFilterParameter");
        }

        /// <summary>
        /// Creates table-valued-parameter for search's work center filter
        /// </summary>
        /// <param name="model">Search to create work center filter parameter for</param>
        /// <returns>Table-valued-parameter for search's work center filter</returns>
        public static SqlMapper.ICustomQueryParameter CreateComponentLotFilterParameter(this SearchModel model)
        {
            //Generate data table with filter data
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("ComponentLotNumber", typeof(string));
            dataTable.Columns.Add("ItemNumber", typeof(string));

            foreach (ComponentLotFilterEntry componentLotFilterEntry in model.ComponentLotFilter)
            {
                dataTable.Rows.Add(componentLotFilterEntry.LotNumber, componentLotFilterEntry.ItemNumber);
            }

            return dataTable.AsTableValuedParameter("ComponentLotFilterParameter");
        }

        /// <summary>
        /// Creates table-valued-parameter for search's operator filter
        /// </summary>
        /// <param name="model">Search to create operator filter parameter for</param>
        /// <returns>Table-valued-parameter for search's operator filter</returns>
        public static SqlMapper.ICustomQueryParameter CreateOperatorFilterParameter(this SearchModel model)
        {
            //Generate data table with filter data
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("UserName", typeof(string));

            foreach (OperatorFilterEntry operatorFilterEntry in model.OperatorFilter)
            {
                dataTable.Rows.Add(operatorFilterEntry.UserID);
            }

            return dataTable.AsTableValuedParameter("OperatorFilterParameter");
        }

        /// <summary>
        /// Creates table-valued-parameter for search's item/operation/mis filter
        /// </summary>
        /// <param name="model">Search to create item/operation/mis filter parameter for</param>
        /// <returns>Table-valued-parameter for search's item/operation/mis filter</returns>
        public static SqlMapper.ICustomQueryParameter CreateItemOperationMisFilterParameter(this SearchModel model)
        {
            //Generate data table with filter data
            DataTable dataTable = new DataTable();
            dataTable.Columns.Add("ItemNumber", typeof(string));
            dataTable.Columns.Add("OperationNumber", typeof(string));
            dataTable.Columns.Add("MisNumber", typeof(string));
            dataTable.Columns.Add("MisRevision", typeof(string));

            foreach (ItemOperationMisFilterEntry itemOperationMisFilterEntry in model.ItemOperationMisFilter)
            {
                dataTable.Rows.Add(itemOperationMisFilterEntry.ItemNumber, itemOperationMisFilterEntry.OperationNumber, itemOperationMisFilterEntry.MisNumber, itemOperationMisFilterEntry.MisRevision);
            }

            return dataTable.AsTableValuedParameter("ItemOperationMisFilterParameter");
        }
    }
}