using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Logging;
using OfficeOpenXml.FormulaParsing.Excel.Functions.DateTime;
using SqlSugar;
using System;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WIDESEA_Core.BaseRepository;
using WIDESEA_Core.BaseServices;
using WIDESEA_Core.Seed;
using WIDESEA_IBasicService;
using WIDESEA_Model.Models;
using WIDESEA_Model.Models.Basic;

namespace WIDESEA_BasicService
{
    public class DailySequenceService : ServiceBase<DT_DailySequence, IRepository<DT_DailySequence>>, IDailySequenceService
    {
        private readonly ILogger<DailySequenceService> _logger;
        private readonly IMemoryCache _cache;
 
        private readonly SemaphoreSlim _lock = new(1, 1);
        private readonly IUnitOfWorkManage _unitOfWorkManage;
        public DailySequenceService(IRepository<DT_DailySequence> BaseDal, ILogger<DailySequenceService> logger, IMemoryCache cache, IUnitOfWorkManage unitOfWorkManage) : base(BaseDal)
        {
            _logger = logger;
            _cache = cache;
            _unitOfWorkManage = unitOfWorkManage;
        }

        public IRepository<DT_DailySequence> Repository => BaseDal;


        public async Task<int> GetNextSequenceAsync()
        {
            return await GetNextSequenceAsync("MESBarcode");
        }                
        public async Task<int> GetNextSequence2Async(string sequenceKey)
        {
            var today = DateTime.Today;
            var cacheKey = $"DailySequence_{sequenceKey}_{today:yyyyMMdd}";

          
            await _lock.WaitAsync();
            try
            {
                // 先检查缓存
                if (_cache.TryGetValue(cacheKey, out int currentValue))
                {
                    currentValue++;
                    _cache.Set(cacheKey, currentValue, TimeSpan.FromHours(24));
                    return currentValue;
                }

                // 缓存中没有，从数据库获取或创建
                var result= await Repository.Db.Ado.UseTranAsync(async () =>
                {
                    // 清理昨天的记录
                    var yesterday = today.AddDays(-1);
                    await Repository.Db.Deleteable<DT_DailySequence>()
                        .Where(x => x.SequenceDate == yesterday && x.SequenceKey == sequenceKey)
                        .ExecuteCommandAsync();

                    // 查找或创建今天的记录
                    var sequence = await Repository.Db.Queryable<DT_DailySequence>()
                        .Where(x => x.SequenceDate == today && x.SequenceKey == sequenceKey)
                        .With(SqlWith.UpdLock)
                        .FirstAsync();

                    int nextValue;
                    if (sequence == null)
                    {
                        sequence = new DT_DailySequence
                        {
                            SequenceDate = today,
                            SequenceKey = sequenceKey,
                            CurrentValue = 1,
                         
                        };
                        await Repository.Db.Insertable(sequence).ExecuteReturnEntityAsync();
                        nextValue = 1;
                    }
                    else
                    {
                        nextValue = sequence.CurrentValue + 1;
                        sequence.CurrentValue = nextValue;
                      
                        await Repository.Db.Updateable(sequence).ExecuteCommandAsync();
                    }

                    // 设置缓存
                    _cache.Set(cacheKey, nextValue, TimeSpan.FromHours(24));
                    return nextValue;
                });
                return result.Data;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "获取序列号失败，SequenceKey: {SequenceKey}", sequenceKey);
                throw;
            }
            finally
            {
                _lock.Release();
            }
        }

        public async Task<int> GetNextSequenceAsync(string sequenceKey)
        {
            var today = DateTime.Today;
            var cacheKey = $"DailySequence_{sequenceKey}_{today:yyyyMMdd}";

        
            await _lock.WaitAsync();
            try
            {
                // 检查缓存中是否有今天的序列值
                if (!_cache.TryGetValue(cacheKey, out int currentValue))
                {
                    // 缓存中没有，从数据库获取或创建
                    currentValue = await GetOrCreateSequenceFromDatabase(today, sequenceKey);
                    // 设置缓存，过期时间为今天结束
                    var _cacheOptions = new MemoryCacheEntryOptions
                    {
                        AbsoluteExpiration = today.AddDays(1)
                    };
                    _cache.Set(cacheKey, currentValue, _cacheOptions);
                    return currentValue;
                }

                // 缓存中存在，递增并更新缓存
                currentValue++;
                var cacheOptions = new MemoryCacheEntryOptions
                {
                    AbsoluteExpiration = today.AddDays(1)
                };
                _cache.Set(cacheKey, currentValue, cacheOptions);

                // 异步更新到数据库
                _ = Task.Run(async () =>
                {
                    try
                    {
                        await UpdateSequenceInDatabase(today, sequenceKey, currentValue);
                    }
                    catch (Exception ex)
                    {
                        _logger.LogWarning(ex, "异步更新序列到数据库失败，SequenceKey: {SequenceKey}", sequenceKey);
                    }
                });

                return currentValue;
            }
            finally
            {
                _lock.Release();
            }
        }
        private async Task<int> GetOrCreateSequenceFromDatabase(DateTime date, string sequenceKey)
        {
            var result= await Repository.Db.Ado.UseTranAsync(async () =>
            {
                var sequence = await Repository.Db.Queryable<DT_DailySequence>()
                    .Where(x => x.SequenceDate == date && x.SequenceKey == sequenceKey)
                    .With(SqlWith.UpdLock)
                    .FirstAsync();

                if (sequence == null)
                {
                    sequence = new DT_DailySequence
                    {
                        SequenceDate = date,
                        SequenceKey = sequenceKey,
                        CurrentValue = 1,

                    };
                    await Repository.Db.Insertable(sequence).ExecuteReturnEntityAsync();
                    return 1;
                }
                else
                {
                    var nextValue = sequence.CurrentValue + 1;
                    sequence.CurrentValue = nextValue;

                    await Repository.Db.Updateable(sequence).ExecuteCommandAsync();
                    return nextValue;
                }
            }) ;
             return result.Data;    
        }

        private async Task UpdateSequenceInDatabase(DateTime date, string sequenceKey, int value)
        {
            try
            {
                using SqlSugarClient sugarClient = new SqlSugarClient(new ConnectionConfig
                {
                    IsAutoCloseConnection = true,
                    DbType = DbType.SqlServer,
                    ConnectionString = DBContext.ConnectionString
                });
                var result = await sugarClient.Updateable<DT_DailySequence>()
                    .SetColumns(it => new DT_DailySequence()
                    {
                        CurrentValue = value,
                    })
                    .Where(it => it.SequenceDate == date && it.SequenceKey == sequenceKey)
                    .ExecuteCommandAsync();

                if (result == 0)
                {
                    // 如果没有更新到记录，可能是首次使用，插入新记录
                    var newSequence = new DT_DailySequence
                    {
                        SequenceKey = sequenceKey,
                        SequenceDate = date,
                        CurrentValue = value,
                        Creater="admin",
                        CreateDate=DateTime.Now,
                    };
                    await sugarClient.Insertable(newSequence).ExecuteCommandAsync();
                }
            }
            catch (Exception ex)
            {
                // 记录日志并重新抛出异常
                Console.WriteLine($"更新序列失败: {ex.Message}");
                throw;
            }                       

        }

        public async Task CleanOldSequencesAsync(int keepDays = 30)
        {
            try
            {
                var deleteBefore = DateTime.Today.AddDays(-keepDays);
                var deletedCount = await Repository.Db.Deleteable<DT_DailySequence>()
                    .Where(x => x.SequenceDate < deleteBefore)
                    .ExecuteCommandAsync();
               
                for (int i = 1; i < keepDays; i++) {
                    var cacheKey = $"DailySequence_MESBarcode_{DateTime.Now.AddDays(-i):yyyyMMdd}";
                    _cache.Remove(cacheKey);
                }
                

                _logger.LogInformation("清理了 {Count} 条 {Days} 天前的序列记录", deletedCount, keepDays);
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "清理旧序列记录失败");
                throw;
            }
        }
    }

}