using Dapper;
using MassTransit;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using MySql.Data.MySqlClient;
using Performance.DtoModels;
using Performance.DtoModels.AppSettings;
using Performance.EntityModels;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;

namespace Performance.Services
{
    public class DapperService : IAutoInjection
    {
        private readonly IOptions<AppConnection> _options;
        private readonly ILogger<DapperService> _logger;

        public DapperService(IOptions<AppConnection> options, ILogger<DapperService> logger)
        {
            _options = options;
            _logger = logger;
        }

        #region 数据静态存储
        /// <summary>
        /// 数据静态存储
        /// </summary>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public void FreezeAllotSync(int allotId)
        {
            Task.Factory.StartNew(() =>
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();
                    string sql = $@"call proc_freeze_allot({allotId})";
                    connection.Execute(sql, commandTimeout: 60 * 60);
                }
            });
        }

        /// <summary>
        /// 数据静态存储
        /// </summary>
        /// <param name="allotId"></param>
        /// <returns></returns>
        public void FreezeAllot(int allotId)
        {
            try
            {
                int i = 0;
                do
                {
                    HanderFreeze(allotId);
                    i++;
                } while (QueryDiff(allotId) < -50 && i <= 3);
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }

            void HanderFreeze(int allotId)
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();
                    string sql = $@"call proc_freeze_allot({allotId})";
                    connection.Execute(sql, commandTimeout: 60 * 60);
                }
            }

            decimal QueryDiff(int allotId)
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();
                    string sql = $@"SELECT Diff FROM view_allot_result_diff WHERE AllotID = @allotId LIMIT 1";
                    return connection.QuerySingle<decimal>(sql, new { allotId }, commandTimeout: 60 * 60);
                }
            }
        }
        #endregion

        #region 归档绩效记录
        /// <summary>
        /// 归档绩效记录
        /// </summary>
        /// <param name="allot"></param>
        public void Pigeonhole(int allotId)
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();

                    string sql = $@"call proc_allot_pigeonhole({allotId})";
                    connection.Execute(sql, commandTimeout: 60 * 60);

                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }
        #endregion

        #region 二次分配模板修复
        /// <summary>
        /// 二次分配模板修复
        /// </summary>
        /// <param name="allotId"></param>
        public void SecondUseTempRestore()
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();

                    string sql = $@"call proc_second_restore()";
                    connection.Execute(sql, commandTimeout: 60 * 60);

                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }
        #endregion

        #region 清理无效绩效数据
        /// <summary>
        /// 清理无效绩效数据
        /// </summary>
        /// <param name="allotId"></param>
        public void ClearAllot()
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();
                    string sql = $@"call proc_clear_allot()";
                    connection.Execute(sql, commandTimeout: 60 * 60);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }
        #endregion

        public void SyncDataToResult(int allotId)
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();

                    string sql = $@"call proc_sync_datatoresult({allotId})";
                    connection.Execute(sql, commandTimeout: 60 * 60);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }
        public void PerEmployeeBackup(int allotId)
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();

                    string sql = $@"call proc_per_employee_backup({allotId})";
                    connection.Execute(sql, commandTimeout: 60 * 60);

                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }

        #region 数据提取

        public IEnumerable<per_dept_dic> GetAccountBasicAccountingUnit(int hospitalId)
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();

                    string sql = @"select * from
(
	select distinct
		unittype,
		ifnull
		(
			max(case when source = '门诊' then accountingunit end),
			max(case when source = '住院' then accountingunit end)
		) accountingunit
	from per_dept_dic
	where hospitalid = @hospitalId
	group by unittype,department,hisdeptname
) t
where ifnull(accountingunit, '无')<>'无'
order by unittype,accountingunit;";
                    return connection.Query<per_dept_dic>(sql, new { hospitalId }, commandTimeout: 60 * 60);

                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }

        public IEnumerable<DeptdicResponse> GetDepartments(int allotId)
        {
            using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
            {
                if (connection.State != ConnectionState.Open) connection.Open();
                try
                {
                    var depts = connection.Query<per_dept_dic>("select * from per_dept_dic where allotid = @allotid", new { allotId }, commandTimeout: 60 * 60);
                    if (depts == null || !depts.Any()) return null;

                    var allot = connection.QueryFirstOrDefault<per_allot>("select * from per_allot where id = @allotid", new { allotId }, commandTimeout: 60 * 60);
                    if (allot == null || !depts.Any()) return null;

                    Deptdic GetDeptdic(List<per_dept_dic> dics, string department, string hISDeptName, UnitType unitType, string source = "")
                    {
                        var dic = dics.FirstOrDefault(group => group.Department == department && group.HISDeptName == hISDeptName
                        && UnitTypeUtil.IsEqualsUnitType(group.UnitType, unitType.ToString())
                        && (group.Source ?? "") == source);
                        //if (dic == null)
                        //    dic = dics.FirstOrDefault(group => group.Department == department && group.HISDeptName == hISDeptName && group.UnitType == unitType.ToString());
                        if (dic == null) return new Deptdic() { IsVerify = 1 };
                        return new Deptdic { Id = dic.Id, AccountingUnit = dic.AccountingUnit, IsVerify = dic.IsVerify ?? 1, VerifyMessage = dic.VerifyMessage, };
                    }

                    var result = depts
                        .GroupBy(t => new { t.HISDeptName, t.Department })
                        .Select(t => new DeptdicResponse
                        {
                            HospitalId = allot.HospitalId,
                            AllotId = allot.ID,
                            HISDeptName = t.Key.HISDeptName,
                            Department = t.Key.Department,
                            OutDoctorAccounting = GetDeptdic(t.ToList(), t.Key.Department, t.Key.HISDeptName, UnitType.医生组, "门诊"),
                            OutNurseAccounting = GetDeptdic(t.ToList(), t.Key.Department, t.Key.HISDeptName, UnitType.护理组, "门诊"),
                            OutTechnicAccounting = GetDeptdic(t.ToList(), t.Key.Department, t.Key.HISDeptName, UnitType.医技组, "门诊"),
                            InpatDoctorAccounting = GetDeptdic(t.ToList(), t.Key.Department, t.Key.HISDeptName, UnitType.医生组, "住院"),
                            InpatNurseAccounting = GetDeptdic(t.ToList(), t.Key.Department, t.Key.HISDeptName, UnitType.护理组, "住院"),
                            InpatTechnicAccounting = GetDeptdic(t.ToList(), t.Key.Department, t.Key.HISDeptName, UnitType.医技组, "住院"),
                            LogisticsAccounting = GetDeptdic(t.ToList(), t.Key.Department, t.Key.HISDeptName, UnitType.行政工勤),
                            SpecialAccounting = GetDeptdic(t.ToList(), t.Key.Department, t.Key.HISDeptName, UnitType.特殊核算组),
                            CreateTime = t.Max(group => group.CreateTime),
                            IsVerify = t.Min(w => w.IsVerify) ?? 1,
                        });

                    return result.OrderBy(w => w.IsVerify).ThenByDescending(t => t.CreateTime).ThenBy(t => t.Department);
                }
                catch (Exception ex)
                {
                    _logger.LogError($"SQL执行异常：{ex}");
                    throw;
                }
            }
        }

        #endregion

        /// <summary>
        /// 科室改名历史数据处理
        /// </summary>
        /// <param name="allotId"></param>
        public void RestoreSecondAllot()
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();
                    string sql = $@"call proc_restore_secondallot()";
                    connection.Execute(sql, commandTimeout: 60 * 60);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }

        public IEnumerable<dynamic> QuerySecondPrintHead(int allotId, string unitType, string accountingUnit)
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();

                    string sql = $@"select * from view_second_print_header where AllotId = @allotId AND UnitType = @UnitType AND AccountingUnit = @AccountingUnit";
                    return connection.Query(sql, new { allotId, unitType = unitType.Replace("行政后勤", "行政工勤"), accountingUnit }, commandTimeout: 60 * 60);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }

        public IEnumerable<dynamic> QuerySecondPrintRow(int allotId, string unitType, string accountingUnit)
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();
                    string sql = $@"select * from view_second_print_row where AllotId = @allotId AND UnitType = @UnitType AND AccountingUnit = @AccountingUnit";
                    return connection.Query(sql, new { allotId, unitType = unitType.Replace("行政后勤", "行政工勤"), accountingUnit }, commandTimeout: 60 * 60);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }

        /// <summary>
        /// 管理绩效打印
        /// </summary>
        /// <param name="allotId"></param>
        /// <param name="unitTypes"></param>
        /// <param name="accountingUnit"></param>
        /// <returns></returns>
        public IEnumerable<dynamic> QuerySecondPrintManagement(int allotId, string[] unitTypes, string accountingUnit)
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();
                    string sql = $@"select * from view_second_print_management where AllotId = @allotId and UnitType in @unitTypes and AccountingUnit = @AccountingUnit";
                    return connection.Query(sql, new { allotId, unitTypes, accountingUnit }, commandTimeout: 60 * 60);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }

        public IEnumerable<dynamic> QueryTableStructure(string tableName)
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();

                    string sql = $@"select column_name,ordinal_position from information_schema.`columns` where table_schema= database() and table_name = @tableName";
                    return connection.Query(sql, new { tableName }, commandTimeout: 60 * 60);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }

        public IEnumerable<dynamic> QueryView(string viewName, int allotID)
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();

                    string sql = $@"select * from {viewName} where allotID = @allotID";
                    return connection.Query(sql, new { allotID }, commandTimeout: 60 * 60);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }

        public int UpdateAllotStates(int allotId, int states, string remark, int generate = 0)
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();

                    string sql = $@"update per_allot set States=@states,Remark=@remark,Generate = @generate where Id = @allotId";
                    return connection.Execute(sql, new { allotId, states, remark, generate }, commandTimeout: 60 * 60);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }

        /// <summary>
        /// 查询考勤下发绩效视图(运行视图定义)
        /// </summary>
        /// <param name="allotId"></param>
        /// <param name="unitType"></param>
        /// <param name="accountingUnit"></param>
        /// <param name="personnelNameOrNumber"></param>
        /// <returns></returns>
        public IEnumerable<dynamic> QueryAttendanceIssue(int allotId, List<string> unitType, string accountingUnit, string personnelNameOrNumber)
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();

                    string sql = $@"select * from view_attendance_issue where allotID = @allotId";
                    DynamicParameters parameters = new DynamicParameters();
                    parameters.Add("@allotId", allotId);

                    if (unitType?.Any() == true)
                    {
                        sql += " and unitType in @unitType";
                        parameters.Add("@unitType", unitType);
                    }
                    if (!string.IsNullOrEmpty(accountingUnit))
                    {
                        sql += " and accountingUnit = @accountingUnit";
                        parameters.Add("@accountingUnit", accountingUnit);
                    }
                    if (!string.IsNullOrEmpty(personnelNameOrNumber))
                    {
                        sql += " and (personnelNumber like @personnelNameOrNumber or personnelName like @personnelNameOrNumber)";
                        parameters.Add("@personnelNameOrNumber", $"%{personnelNameOrNumber}%");
                    }

                    return connection.Query(sql, parameters, commandTimeout: 60 * 60);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }
        /// <summary>
        /// 查询考勤下发绩效视图(运行视图定义)
        /// </summary>
        /// <param name="allotId"></param>
        /// <param name="unitType"></param>
        /// <param name="accountingUnit"></param>
        /// <param name="personnelNameOrNumber"></param>
        /// <returns></returns>
        public IEnumerable<dynamic> QueryAttendanceDeptReport(int allotId, string unitType, string accountingUnit, string personnelNameOrNumber)
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();

                    string sql = $@"select * from view_attendance_dept where allotID = @allotId";
                    DynamicParameters parameters = new DynamicParameters();
                    parameters.Add("@allotId", allotId);

                    if (unitType?.Any() == true)
                    {
                        sql += " and unitType = @unitType";
                        parameters.Add("@unitType", unitType);
                    }
                    if (!string.IsNullOrEmpty(accountingUnit))
                    {
                        sql += " and accountingUnit = @accountingUnit";
                        parameters.Add("@accountingUnit", accountingUnit);
                    }
                    if (!string.IsNullOrEmpty(personnelNameOrNumber))
                    {
                        sql += " and (personnelNumber like @personnelNameOrNumber or personnelName like @personnelNameOrNumber)";
                        parameters.Add("@personnelNameOrNumber", $"%{personnelNameOrNumber}%");
                    }

                    return connection.Query(sql, parameters, commandTimeout: 60 * 60);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }

        public IEnumerable<dynamic> QueryAttendanceDeptReportCompute(int allotId, string unitType, string accountingUnit, string personnelNameOrNumber)
        {
            try
            {
                using (var connection = new MySqlConnection(_options.Value.PerformanceConnectionString))
                {
                    if (connection.State != ConnectionState.Open) connection.Open();

                    string sql = $@"select * from view_attendance_dept_compute_report where allotID = @allotId";
                    DynamicParameters parameters = new DynamicParameters();
                    parameters.Add("@allotId", allotId);

                    if (unitType?.Any() == true)
                    {
                        sql += " and unitType = @unitType";
                        parameters.Add("@unitType", unitType);
                    }
                    if (!string.IsNullOrEmpty(accountingUnit))
                    {
                        sql += " and accountingUnit = @accountingUnit";
                        parameters.Add("@accountingUnit", accountingUnit);
                    }
                    if (!string.IsNullOrEmpty(personnelNameOrNumber))
                    {
                        sql += " and (personnelNumber like @personnelNameOrNumber or personnelName like @personnelNameOrNumber)";
                        parameters.Add("@personnelNameOrNumber", $"%{personnelNameOrNumber}%");
                    }

                    return connection.Query(sql, parameters, commandTimeout: 60 * 60);
                }
            }
            catch (Exception ex)
            {
                _logger.LogError($"SQL执行异常：{ex}");
                throw;
            }
        }


    }
}
