using System;
using System.Collections.Generic;
using System.Threading.Tasks;
using MDL.Models;
using DAL.Interfaces;
using DB.Database;
using SqlSugar;

namespace DAL.Repositories
{
    /// <summary>
    /// 面单PDF缓存的数据访问实现类
    /// </summary>
    public class LabelPdfCacheRepository : ILabelPdfCacheRepository
    {
        private readonly ISqlSugarProvider _provider;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="provider">SqlSugar数据库提供程序</param>
        public LabelPdfCacheRepository(ISqlSugarProvider provider)
        {
            _provider = provider;
        }

        /// <summary>
        /// 根据中性面单单号获取缓存记录
        /// </summary>
        /// <param name="waybillNumber">中性面单单号</param>
        /// <returns>缓存记录实体</returns>
        public async Task<LabelPdfCache?> GetByWaybillNumberAsync(string waybillNumber)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelPdfCache>()
                .Where(c => c.NeutralWaybillNumber == waybillNumber)
                .FirstAsync();
        }

        /// <summary>
        /// 根据中性面单单号获取缓存元数据，显式排除 pdf_bytes 大字段。
        /// 用于在返回 PDF 前做状态校验，避免把整个 BLOB 加载到内存后再丢弃。
        /// </summary>
        /// <param name="waybillNumber">中性面单单号</param>
        /// <returns>不含 PdfBytes 的缓存实体，不存在时返回 null</returns>
        public async Task<LabelPdfCache?> GetMetaByWaybillNumberAsync(string waybillNumber)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelPdfCache>()
                .Where(c => c.NeutralWaybillNumber == waybillNumber)
                .Select(c => new LabelPdfCache
                {
                    Id = c.Id,
                    NeutralWaybillNumber = c.NeutralWaybillNumber,
                    Status = c.Status,
                    UpdatedTime = c.UpdatedTime,
                    CreatedTime = c.CreatedTime,
                    PageCount = c.PageCount,
                    FileSize = c.FileSize,
                    RetryCount = c.RetryCount,
                    CustomerId = c.CustomerId,
                    FinalMileTrackingNumber = c.FinalMileTrackingNumber,
                    OriginalUrl = c.OriginalUrl
                })
                .FirstAsync();
        }

        /// <summary>
        /// 单独查询指定面单的 PDF 字节流。
        /// 仅在 GetMetaByWaybillNumberAsync 校验通过（Status=1）后调用，
        /// 确保只在缓存真正有效时才执行 BLOB 传输。
        /// </summary>
        /// <param name="waybillNumber">中性面单单号</param>
        /// <returns>PDF 字节数组，记录不存在或 pdf_bytes 为空时返回 null</returns>
        public async Task<byte[]?> GetPdfBytesAsync(string waybillNumber)
        {
            var db = _provider.GetClient();
            var result = await db.Queryable<LabelPdfCache>()
                .Where(c => c.NeutralWaybillNumber == waybillNumber)
                .Select(c => new LabelPdfCache { PdfBytes = c.PdfBytes })
                .FirstAsync();
            return result?.PdfBytes;
        }

        /// <summary>
        /// 创建缓存记录
        /// </summary>
        /// <param name="entity">缓存实体</param>
        /// <returns>创建的记录ID</returns>
        public async Task<long> CreateAsync(LabelPdfCache entity)
        {
            var db = _provider.GetClient();
            
            // 确保表存在
            if (!db.DbMaintenance.IsAnyTable("label_pdf_cache"))
            {
                db.CodeFirst.InitTables(typeof(LabelPdfCache));
            }

            // 设置时间戳
            entity.CreatedTime = DateTime.UtcNow;
            entity.UpdatedTime = DateTime.UtcNow;

            // 插入数据并返回自增ID
            return await db.Insertable(entity).ExecuteReturnIdentityAsync();
        }

        /// <summary>
        /// 更新缓存记录
        /// </summary>
        /// <param name="entity">缓存实体</param>
        /// <returns>更新是否成功</returns>
        public async Task<bool> UpdateAsync(LabelPdfCache entity)
        {
            var db = _provider.GetClient();
            entity.UpdatedTime = DateTime.UtcNow;
            return await db.Updateable(entity).ExecuteCommandAsync() > 0;
        }

        /// <summary>
        /// 标记缓存为失效状态
        /// </summary>
        /// <param name="waybillNumber">中性面单单号</param>
        /// <returns>操作是否成功</returns>
        public async Task<bool> InvalidateCacheAsync(string waybillNumber)
        {
            var db = _provider.GetClient();
            var existing = await db.Queryable<LabelPdfCache>()
                .Where(c => c.NeutralWaybillNumber == waybillNumber)
                .FirstAsync();
                
            if (existing == null)
            {
                return false;
            }
            
            existing.Status = 3; // 3=已失效
            existing.UpdatedTime = DateTime.UtcNow;
            return await db.Updateable(existing).ExecuteCommandAsync() > 0;
        }

        /// <summary>
        /// 获取待处理的缓存任务列表
        /// </summary>
        /// <param name="maxRetryCount">最大重试次数</param>
        /// <param name="limit">最大返回数量</param>
        /// <returns>待处理的缓存记录列表</returns>
        public async Task<List<LabelPdfCache>> GetPendingTasksAsync(int maxRetryCount, int limit)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelPdfCache>()
                .Where(c => c.Status == 0 || (c.Status == 2 && c.RetryCount < maxRetryCount))
                .Where(c => SqlFunc.Subqueryable<LabelReplaceEntity>()
                    .Where(l => l.NeutralWaybillNumber == c.NeutralWaybillNumber && !string.IsNullOrEmpty(l.Label))
                    .Any())
                .OrderBy(c => c.CreatedTime, OrderByType.Asc)
                .Take(limit)
                .ToListAsync();
        }

        /// <summary>
        /// 获取订单表中新的有标签订单（缓存表中不存在的）
        /// </summary>
        /// <param name="limit">最大返回数量</param>
        /// <returns>新订单的面单号列表</returns>
        public async Task<List<string>> GetNewOrdersWithLabelsAsync(int limit)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelReplaceEntity>()
                .Where(o => !string.IsNullOrEmpty(o.Label))
                .Where(o => !SqlFunc.Subqueryable<LabelPdfCache>()
                    .Where(c => c.NeutralWaybillNumber == o.NeutralWaybillNumber)
                    .Any())
                .Select(o => o.NeutralWaybillNumber)
                .Take(limit)
                .ToListAsync();
        }

        /// <summary>
        /// 获取需要重新处理的失效缓存列表
        /// </summary>
        /// <param name="limit">最大返回数量</param>
        /// <returns>失效的缓存记录列表</returns>
        public async Task<List<LabelPdfCache>> GetInvalidCachesAsync(int limit)
        {
            var db = _provider.GetClient();
            return await db.Queryable<LabelPdfCache>()
                .Where(c => c.Status == 3)
                .Where(c => SqlFunc.Subqueryable<LabelReplaceEntity>()
                    .Where(l => l.NeutralWaybillNumber == c.NeutralWaybillNumber && !string.IsNullOrEmpty(l.Label))
                    .Any())
                .OrderBy(c => c.UpdatedTime, OrderByType.Asc)
                .Take(limit)
                .ToListAsync();
        }
        
        /// <summary>
        /// 异步更新条码信息
        /// </summary>
        /// <param name="waybillNumber">中性面单单号</param>
        /// <param name="barcodeNumber">条码号</param>
        /// <param name="barcodeType">条码类型</param>
        /// <param name="confidence">识别置信度</param>
        /// <returns>更新是否成功</returns>
        public async Task<bool> UpdateBarcodeInfoAsync(string waybillNumber, string barcodeNumber, byte barcodeType, int confidence)
        {
            var db = _provider.GetClient();
            var existing = await db.Queryable<LabelPdfCache>()
                .Where(c => c.NeutralWaybillNumber == waybillNumber)
                .FirstAsync();
                
            if (existing == null)
            {
                return false;
            }
            
            existing.BarcodeNumber = barcodeNumber;
            existing.BarcodeType = barcodeType;
            existing.BarcodeConfidence = confidence;
            existing.BarcodeExtractTime = DateTime.UtcNow;
            existing.UpdatedTime = DateTime.UtcNow;
            return await db.Updateable(existing).ExecuteCommandAsync() > 0;
        }

        /// <summary>
        /// 获取数据库客户端实例
        /// </summary>
        /// <returns>SqlSugar数据库客户端</returns>
        public ISqlSugarClient GetClient()
        {
            return _provider.GetClient();
        }

        /// <summary>
        /// 获取定时任务新订单（仅2026-05-10之后的订单）
        /// </summary>
        /// <param name="limit">最大返回数量</param>
        /// <returns>新订单的中性面单号列表（仅>=2026-05-10）</returns>
        public async Task<List<string>> GetNewOrdersWithLabelsForBackgroundTaskAsync(int limit)
        {
            var db = _provider.GetClient();
            var cutoffDate = new DateTime(2026, 5, 10, 0, 0, 0);
            
            return await db.Queryable<LabelReplaceEntity>()
                .Where(o => !string.IsNullOrEmpty(o.Label))
                .Where(o => o.CreatedAt >= cutoffDate)
                .Where(o => !SqlFunc.Subqueryable<LabelPdfCache>()
                    .Where(c => c.NeutralWaybillNumber == o.NeutralWaybillNumber)
                    .Any())
                .Select(o => o.NeutralWaybillNumber)
                .Take(limit)
                .ToListAsync();
        }
    }
}
