using Dapper;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using MySql.Data.MySqlClient;
using Performance.DtoModels;
using Performance.DtoModels.AppSettings;
using Performance.EntityModels;
using Performance.Infrastructure;
using Performance.Repository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;

namespace Performance.Services
{
    public class StatisticsService : IAutoInjection
    {
        private readonly ILogger<StatisticsService> _logger;
        private readonly IOptions<AppConnection> _options;
        private readonly PerforReportStatisticsRepository _reportStatisticsRepository;
        private readonly PerforReportStatisticsSelectionRepository _reportStatisticsSelectionRepository;
        private readonly PerforReportStatisticsSelectionRecordRepository _reportStatisticsSelectionRecordRepository;
        private readonly PerforReportStatisticsStoreRepository _reportStatisticsStoreRepository;
        private readonly PerforReportStatisticsTreeRepository _reportStatisticsTreeRepository;
        public StatisticsService(
            ILogger<StatisticsService> logger,
            IOptions<AppConnection> options,
            PerforReportStatisticsRepository ReportStatisticsRepository,
            PerforReportStatisticsSelectionRepository ReportStatisticsSelectionRepository,
            PerforReportStatisticsSelectionRecordRepository ReportStatisticsSelectionRecordRepository,
            PerforReportStatisticsStoreRepository ReportStatisticsStoreRepository,
            PerforReportStatisticsTreeRepository ReportStatisticsTreeRepository
            )
        {
            _logger = logger;
            _options = options;
            _reportStatisticsRepository = ReportStatisticsRepository;
            _reportStatisticsSelectionRepository = ReportStatisticsSelectionRepository;
            _reportStatisticsSelectionRecordRepository = ReportStatisticsSelectionRecordRepository;
            _reportStatisticsStoreRepository = ReportStatisticsStoreRepository;
            _reportStatisticsTreeRepository = ReportStatisticsTreeRepository;
        }

        private (string sql, DynamicParameters param) GetSqlQuery(string sql, Dictionary<string, object> pairs)
        {
            List<string> plist = new List<string>();
            foreach (var item in pairs)
            {
                var json = JsonHelper.Serialize(item.Value);
                if (!Regex.IsMatch(sql, $"@{item.Key}", RegexOptions.IgnoreCase | RegexOptions.Multiline) && !string.IsNullOrEmpty(json) && json != "[]")
                {
                    var key = item.Key.StartsWith("@") ? item.Key.Substring(1, item.Key.Length - 1) : item.Key;
                    if (item.Value.GetType().GetInterfaces().Any(iface => iface.IsGenericType && iface.GetGenericTypeDefinition() == typeof(ICollection<>)))
                    {
                        plist.Add($"{key} IN @{key}");
                    }
                    else
                        plist.Add($"{key} = @{key}");
                }
            }
            string @wh = string.Join(" AND ", plist.ToArray());
            // 多种占位方式
            string[] ws = new string[] { "@wh", "@w", "{w}", "{wh}" };
            foreach (var item in ws)
            {
                if (Regex.IsMatch(sql, item, RegexOptions.IgnoreCase | RegexOptions.Multiline))
                    sql = Regex.Replace(sql, item, @wh, RegexOptions.IgnoreCase | RegexOptions.Multiline);
            }
            DynamicParameters param = new DynamicParameters();
            foreach (var item in pairs)
            {
                var key = item.Key.StartsWith("@") ? item.Key : $"@{item.Key}";
                param.Add(key, item.Value);
            }

            return (sql, param);
        }

        public StatisticsInfoDto GetInfo(int hospitalID, int statisticsId)
        {
            var reportStatistics = _reportStatisticsRepository.GetEntity(t => t.ID == statisticsId);
            if (reportStatistics == null)
                return new StatisticsInfoDto();

            var reportStatisticsSelection = _reportStatisticsSelectionRepository.GetEntities(t => t.StatisticsID == reportStatistics.ID);
            if (reportStatisticsSelection.Count == 0)
                return new StatisticsInfoDto();

            var result = new StatisticsInfoDto()
            {
                StatisticsID = statisticsId,
                HospitalId = (int)reportStatistics.HospitalId,
                Name = reportStatistics.Name,
                IsComparison = (int)reportStatistics.IsComparison,
                Selections = new List<StatisticsSelectionDto> { }
            };
            Dictionary<string, object> pairs = new Dictionary<string, object>
            {
                { "@hospitalId", hospitalID },
            };
            foreach (var item in reportStatisticsSelection)
            {
                var (sql, param) = GetSqlQuery(item.Content, pairs);
                var reportStatisticsStore = _reportStatisticsStoreRepository.DapperQuery<TitleValue>(sql, param).ToList();

                reportStatisticsStore.ForEach(t => t.State = (int)item.State);

                StatisticsSelectionDto statisticsSelectionDto = new StatisticsSelectionDto()
                {
                    SelectionID = item.ID,
                    StatisticsID = item.StatisticsID,
                    InputName = item.InputName,
                    InputType = item.InputType,
                    Required = item.Required,
                    Sort = item.Sort,
                    Title = item.Title,
                    Options = reportStatisticsStore
                };
                result.Selections.Add(statisticsSelectionDto);
            }
            result.Selections = result.Selections.OrderBy(t => t.Sort).ToList();
            return result;
        }

        public List<report_statistics_selection_record> GetSelectionRecord(int hospitalID, int statisticsId)
        {
            var result = _reportStatisticsSelectionRecordRepository.GetEntities(t => t.HospitalId == hospitalID && t.StatisticsID == statisticsId).OrderByDescending(t => t.UpdateDate).OrderByDescending(t => t.CreateDate).ToList();
            return result;
        }

        public ApiResponse SaveSelectionRecord(report_statistics_selection_record record)
        {
            record.Content = JsonHelper.Serialize(record.Content);

            var result = _reportStatisticsSelectionRecordRepository.GetEntity(t => t.ID == record.ID);
            bool ok;
            if (result != null)
            {
                result.UpdateDate = DateTime.Now;
                ok = _reportStatisticsSelectionRecordRepository.Update(record);
            }
            else
            {
                record.CreateDate = DateTime.Now;
                ok = _reportStatisticsSelectionRecordRepository.Add(record);
            }
            if (ok) return new ApiResponse(ResponseType.OK);
            else return new ApiResponse(ResponseType.Fail);
        }

        public bool DeleteSelectionRecord(int id)
        {
            var result = _reportStatisticsSelectionRecordRepository.GetEntity(t => t.ID == id);
            return _reportStatisticsSelectionRecordRepository.Remove(result);

        }

        public StatisticsQueryResultDto Search(StatisticsQuery query)
        {
            var reportStatistics = _reportStatisticsRepository.GetEntity(t => t.ID == query.StatisticsID);
            if (reportStatistics == null)
                return new StatisticsQueryResultDto();

            Dictionary<string, object> pairs = new Dictionary<string, object> { };

            var reportStatisticsTree = _reportStatisticsTreeRepository.GetEntities(t => t.StatisticsID == query.StatisticsID);
            var isTree = (reportStatisticsTree != null && reportStatisticsTree.Count > 0);

            /* 
             * 报表分三种
             * 1.绩效情况六--各科室医护绩效对比  这是特殊的，直接查询视图，对列头特殊处理
             * 2.没有层级  通过1条SQL查询全部数据展示
             * 3.配置层级  通过层级查询固定SQL
             */
            if (query.StatisticsID == 7)
                return StatisticsQuery7(query, reportStatistics);
            else if (!isTree)
                return StatisticsQuery(query, reportStatistics);
            else
                return StatisticsTreeQuery(query, reportStatisticsTree);
        }

        /// <summary>
        /// 绩效情况六--各科室医护绩效对比  这是特殊的，直接查询视图，对列头特殊处理
        /// </summary>
        /// <param name="query"></param>
        /// <param name="statistics"></param>
        /// <returns></returns>
        private StatisticsQueryResultDto StatisticsQuery7(StatisticsQuery query, report_statistics statistics)
        {
            Dictionary<string, object> pairs = new Dictionary<string, object>
            {
                { "HospitalID", query.HospitalID },
            };
            foreach (var item in query.Values)
            {
                pairs.Add(item.Title, item.Values);
            }
            var table = new List<Dictionary<string, object>>();
            using (IDbConnection connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
            {
                if (!string.IsNullOrEmpty(statistics.TotalScript))
                {
                    var (sql, param) = GetSqlQuery(statistics.TotalScript, pairs);
                    var data = connection.Query(sql, param);
                    table = JsonHelper.Deserialize<List<Dictionary<string, object>>>(JsonHelper.Serialize(data));
                }
            }
            var columns = new List<StatisticsColumn>();

            if (table != null && table.Count > 0)
            {
                foreach (var item in table.First().Keys)
                {
                    /*
                     * 对列头特殊处理
                     * 按"_"拆分，并合并父子列头
                     */
                    if (!item.Contains("_"))
                    {
                        columns.Add(new StatisticsColumn { ColumnName = item, OrgColumnName = item, Fixed = "left" });
                    }
                    else
                    {
                        var cols = item.SplitRemoveEmpty("_");

                        var col = columns.FirstOrDefault(w => w.ColumnName == cols.First());
                        if (col == null)
                        {
                            col = new StatisticsColumn { ColumnName = cols.First(), OrgColumnName = item, };
                            columns.Add(col);
                        }
                        if (!col.ChildColumns.Any(w => w.ColumnName == cols.Last()))
                            col.ChildColumns.Add(new StatisticsColumn { ColumnName = cols.Last(), OrgColumnName = item, });
                    }

                }
            }
            return new StatisticsQueryResultDto
            {
                IsComparison = query.IsComparison,
                Columns = columns,
                Datas = table,
            };
        }

        /// <summary>
        /// 没有层级  通过1条SQL查询全部数据展示
        /// </summary>
        /// <param name="query"></param>
        /// <param name="statistics"></param>
        /// <returns></returns>
        private StatisticsQueryResultDto StatisticsQuery(StatisticsQuery query, report_statistics statistics)
        {
            Dictionary<string, object> pairs = new Dictionary<string, object>
            {
                { "HospitalID", query.HospitalID },
            };
            foreach (var item in query.Values)
            {
                pairs.Add(item.Title, item.Values);
            }
            var table = new List<Dictionary<string, object>>();
            using (IDbConnection connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
            {
                if (!string.IsNullOrEmpty(statistics.TotalScript))
                {
                    var (sql, param) = GetSqlQuery(statistics.TotalScript, pairs);
                    var data = connection.Query(sql, param);
                    table = JsonHelper.Deserialize<List<Dictionary<string, object>>>(JsonHelper.Serialize(data));
                }
            }
            var columns = new List<StatisticsColumn>();
            string[] cols = new string[] { "数值", "金额", "占比", "权重" };

            if (table != null && table.Count > 0)
            {
                foreach (var item in table.First().Keys)
                {
                    columns.Add(new StatisticsColumn { ColumnName = item, OrgColumnName = item, Fixed = cols.Contains(item) ? "" : "left" });
                }
            }
            return new StatisticsQueryResultDto
            {
                IsComparison = query.IsComparison,
                Columns = columns,
                Datas = table,
            };
        }
        /// <summary>
        /// 配置层级  通过层级查询固定SQL
        /// </summary>
        /// <param name="query"></param>
        /// <param name="reportStatisticsTree"></param>
        /// <returns></returns>
        private StatisticsQueryResultDto StatisticsTreeQuery(StatisticsQuery query, List<report_statistics_tree> reportStatisticsTree)
        {
            var columns = new List<StatisticsColumn>();
            columns.Add(new StatisticsColumn { ColumnName = "项目", OrgColumnName = "项目", Fixed = "left", });
            if (reportStatisticsTree.Any(w => !string.IsNullOrEmpty(w.TotalScript) || !string.IsNullOrEmpty(w.TotalFormula)))
            {
                columns.Add(new StatisticsColumn { ColumnName = "数值", OrgColumnName = "数值", });
                columns.Add(new StatisticsColumn { ColumnName = "数值公式", OrgColumnName = "数值公式", });
            }

            if (reportStatisticsTree.Any(w => !string.IsNullOrEmpty(w.ProportionFormula)))
            {
                columns.Add(new StatisticsColumn { ColumnName = "占比", OrgColumnName = "占比", });
                columns.Add(new StatisticsColumn { ColumnName = "占比公式", OrgColumnName = "占比公式", });
            }
            if (reportStatisticsTree.Any(w => !string.IsNullOrEmpty(w.WeightFormula)))
            {
                columns.Add(new StatisticsColumn { ColumnName = "权重", OrgColumnName = "权重", });
                columns.Add(new StatisticsColumn { ColumnName = "权重公式", OrgColumnName = "权重公式", });
            }
            Dictionary<string, object> pairs = new Dictionary<string, object>
            {
                { "HospitalID", query.HospitalID },
            };
            foreach (var item in query.Values)
            {
                pairs.Add(item.Title, item.Values);
            }

            var table = new List<Dictionary<string, object>>();
            // 层级有几层，则数据就有几层
            foreach (var item in reportStatisticsTree)
            {
                var row = new Dictionary<string, object>();
                row.Add("ID", item.ID);
                row.Add("ParentID", item.ParentID);

                row.Add("项目", item.Name);
                if (reportStatisticsTree.Any(w => !string.IsNullOrEmpty(w.TotalScript) || !string.IsNullOrEmpty(w.TotalFormula)))
                {
                    row.Add("数值", "");
                    row.Add("数值公式", item.TotalFormula ?? "");
                }
                if (reportStatisticsTree.Any(w => !string.IsNullOrEmpty(w.ProportionFormula)))
                {
                    row.Add("占比", "");
                    row.Add("占比公式", item.ProportionFormula ?? "");
                }
                if (reportStatisticsTree.Any(w => !string.IsNullOrEmpty(w.WeightFormula)))
                {
                    row.Add("权重", "");
                    row.Add("权重公式", item.WeightFormula ?? "");
                }

                using (IDbConnection connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (!string.IsNullOrEmpty(item.TotalScript))
                    {
                        // 通过层级查询固定SQL                         
                        var (sql, param) = GetSqlQuery(item.TotalScript, pairs);
                        var data = connection.Query(sql, param);

                        if (data == null || !data.Any()) continue;
                        var val = "";
                        if (data.Count() < 2)
                        {
                            val = data.FirstOrDefault().数值?.ToString() ?? "";

                        }
                        else
                        {
                            val = data.Sum(t => (decimal?)t.数值 ?? 0).ToString() ?? "";
                            DateTime dateStart = new DateTime(1970, 1, 1, 8, 0, 0);
                            var children = data.Select(t => new Dictionary<string, object>
                            {
                                { "ID", Convert.ToInt32((DateTime.Now - dateStart).TotalSeconds) },
                                { "ParentID", item.ID },
                                { "项目", t.项目 },
                                { "数值", t.数值 },
                            });
                            table.AddRange(children);
                        }

                        if (row.ContainsKey("数值"))
                            row["数值"] = val;
                        else
                            row.Add("数值", val);
                    }
                }
                table.Add(row);
            }

            // 平级转层级
            var statisticsTree = StatisticsTree(table, 0);
            return new StatisticsQueryResultDto
            {
                IsComparison = query.IsComparison,
                Columns = columns,
                Datas = statisticsTree,
            };
        }

        private List<Dictionary<string, object>> StatisticsTree(List<Dictionary<string, object>> oldTree, int parentID)
        {
            if (oldTree.Any(w => (int)w["ParentID"] == parentID))
            {
                List<Dictionary<string, object>> newTree = new List<Dictionary<string, object>>();

                foreach (var item in oldTree.Where(t => (int)t["ParentID"] == parentID))
                {
                    item.Add("Children", StatisticsTree(oldTree, (int)item["ID"]));
                    newTree.Add(item);
                }
                return newTree;
            }
            else return null;
        }
    }
}
