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

namespace DAL.Repositories
{
    public class BagTagRepository : IBagTagRepository
    {
        private readonly ISqlSugarProvider _provider;

        public BagTagRepository(ISqlSugarProvider provider)
        {
            _provider = provider;
            // 初始化表结构，只执行一次
            var db = _provider.GetClient();
            if (db != null)
            {
                db.CodeFirst.InitTables(typeof(BagTagEntity));
                db.CodeFirst.InitTables(typeof(BagTagWaybillEntity));
            }
        }

        /// <summary>
        /// 获取数据库连接，每次都获取新的连接
        /// </summary>
        /// <returns>数据库连接</returns>
        private ISqlSugarClient GetDb()
        {
            return _provider.GetClient();
        }

        public async Task<int> InsertAsync(BagTagEntity tag)
        {
            return await GetDb().Insertable(tag).ExecuteCommandAsync();
        }

        public async Task<BagTagEntity> GetByIdAsync(int id)
        {
            return await GetDb().Queryable<BagTagEntity>().Where(t => t.Id == id).FirstAsync();
        }

        public async Task<BagTagEntity> GetByTagNumberAsync(string tagNumber)
        {
            return await GetDb().Queryable<BagTagEntity>().Where(t => t.TagNumber == tagNumber).FirstAsync();
        }

        public async Task<List<BagTagEntity>> GetAllAsync()
        {
            return await GetDb().Queryable<BagTagEntity>().ToListAsync();
        }

        public async Task<int> UpdateAsync(BagTagEntity tag)
        {
            return await GetDb().Updateable(tag).ExecuteCommandAsync();
        }

        public async Task<int> InsertWaybillAsync(BagTagWaybillEntity waybill)
        {
            return await GetDb().Insertable(waybill).ExecuteCommandAsync();
        }

        public async Task<List<MDL.DTOs.BagTagWaybillWithDetailsDto>> GetWaybillsByTagNumberAsync(string tagNumber)
        {
            var db = GetDb();
            // Optimized SQL query
            var sql = $@"
                SELECT 
                    w.Id, 
                    w.TagNumber, 
                    w.FinalMileTrackingNumber, 
                    w.CreatedAt, 
                    w.Creator, 
                    w.Remark, 
                    l.NeutralWaybillNumber, 
                    l.BillOfLadingNumber, 
                    c.CustomerName as CustomerAbbreviation,
                    NULL as ReplaceCompletedTime
                FROM bag_tag_waybills w
                LEFT JOIN (
                    SELECT 
                        FinalMileTrackingNumber, 
                        NeutralWaybillNumber, 
                        BillOfLadingNumber, 
                        CustomerId
                    FROM (
                        SELECT 
                            FinalMileTrackingNumber, 
                            NeutralWaybillNumber, 
                            BillOfLadingNumber, 
                            CustomerId,
                            ROW_NUMBER() OVER (PARTITION BY FinalMileTrackingNumber ORDER BY CreatedAt DESC) as rn
                        FROM label_replace_requests
                    ) sub
                    WHERE sub.rn = 1
                ) l ON w.FinalMileTrackingNumber = l.FinalMileTrackingNumber
                LEFT JOIN customers c ON l.CustomerId = c.Id
                WHERE w.TagNumber = @tagNumber
                ORDER BY w.Id
            ";
            return await db.Ado.SqlQueryAsync<MDL.DTOs.BagTagWaybillWithDetailsDto>(sql, new { tagNumber });
        }

        public async Task<bool> ExistsByTagNumberAsync(string tagNumber)
        {
            return await GetDb().Queryable<BagTagEntity>().Where(t => t.TagNumber == tagNumber).AnyAsync();
        }

        public async Task<bool> IsWaybillAssociatedAsync(string tagNumber, string finalMileTrackingNumber)
        {
            return await GetDb().Queryable<BagTagWaybillEntity>()
                .Where(w => w.TagNumber == tagNumber && w.FinalMileTrackingNumber == finalMileTrackingNumber)
                .AnyAsync();
        }

        public async Task<(List<BagTagEntity> BagTags, int TotalCount)> GetBagTagsBatchAsync(
            int page, 
            int pageSize, 
            string sortBy, 
            string sortOrder, 
            string tagNumber, 
            string channel, 
            string status, 
            string creator)
        {
            var query = GetDb().Queryable<BagTagEntity>();

            // 应用过滤条件
            if (!string.IsNullOrEmpty(tagNumber))
            {
                query = query.Where(t => t.TagNumber.Contains(tagNumber));
            }
            if (!string.IsNullOrEmpty(channel))
            {
                query = query.Where(t => t.ChannelName == channel);
            }
            if (!string.IsNullOrEmpty(status))
            {
                query = query.Where(t => t.Status == status);
            }
            if (!string.IsNullOrEmpty(creator))
            {
                query = query.Where(t => t.Creator.Contains(creator));
            }

            // 获取总记录数
            var totalCount = await query.CountAsync();

            // 应用排序
            if (!string.IsNullOrEmpty(sortBy))
            {
                query = sortOrder.ToLower() == "asc" 
                    ? query.OrderBy(sortBy)
                    : query.OrderBy($"{sortBy} desc");
            }
            else
            {
                // 默认按创建时间降序排序
                query = query.OrderBy("CreatedAt desc");
            }

            // 应用分页
            var bagTags = await query.Skip((page - 1) * pageSize).Take(pageSize).ToListAsync();

            return (bagTags, totalCount);
        }

        public async Task<int> RemoveWaybillAssociationAsync(string tagNumber, string finalMileTrackingNumber)
        {
            return await GetDb().Deleteable<BagTagWaybillEntity>()
                .Where(w => w.TagNumber == tagNumber && w.FinalMileTrackingNumber == finalMileTrackingNumber)
                .ExecuteCommandAsync();
        }

        public async Task<BagTagWaybillEntity> GetWaybillAssociationAsync(string finalMileTrackingNumber)
        {
            return await GetDb().Queryable<BagTagWaybillEntity>()
                .Where(w => w.FinalMileTrackingNumber == finalMileTrackingNumber)
                .FirstAsync();
        }

        public async Task<bool> IsWaybillAssociatedAnywhereAsync(string finalMileTrackingNumber)
        {
            return await GetDb().Queryable<BagTagWaybillEntity>()
                .Where(w => w.FinalMileTrackingNumber == finalMileTrackingNumber)
                .AnyAsync();
        }

        public async Task<List<BagTagEntity>> GetAvailableBagTagsByChannelAsync(string channel)
        {
            var db = GetDb();
            
            // 先查询所有状态为Closed且渠道匹配的袋牌
            var closedBagTags = await db.Queryable<BagTagEntity>()
                .Where(t => t.Status == "Closed" && t.ChannelName == channel)
                .ToListAsync();
            
            if (closedBagTags.Count == 0)
            {
                return closedBagTags;
            }
            
            // 提取袋牌ID列表
            var bagTagIds = closedBagTags.Select(t => t.Id).ToList();
            
            // 查询已绑定到出库交接单的袋牌ID
            var boundBagTagIds = await db.Queryable<ShippingHandoverFormBagTagEntity>()
                .Where(st => bagTagIds.Contains(st.BagTagId))
                .Select(st => st.BagTagId)
                .ToListAsync();
            
            // 过滤出未绑定的袋牌并排序
            var availableBagTags = closedBagTags
                .Where(t => !boundBagTagIds.Contains(t.Id))
                .OrderBy(t => t.Id)
                .ToList();
            
            return availableBagTags;
        }

        public async Task<List<string>> GetEligibleUspsWaybillsAsync(DateTime cutoffTime)
        {
            var db = GetDb();
            var sql = @"
                SELECT DISTINCT 
                    l.FinalMileTrackingNumber
                FROM label_replace_requests l
                LEFT JOIN bag_tag_waybills b ON l.FinalMileTrackingNumber = b.FinalMileTrackingNumber
                JOIN label_scan_history s ON l.NeutralWaybillNumber = s.NeutralWaybillNumber
                WHERE 
                    l.ReplaceStatus = 'Y'
                    AND s.Result = 0
                    AND s.CreatedAt >= @CutoffTime
                    AND l.FinalMileTrackingNumber IS NOT NULL
                    AND l.FinalMileTrackingNumber != ''
                    AND b.Id IS NULL
                    AND (
                        l.FinalMileTrackingNumber REGEXP '^(92|93|94|95)'
                        OR l.FinalMileTrackingNumber REGEXP '^420[0-9]{5}(92|93|94|95)'
                        OR l.FinalMileTrackingNumber REGEXP '^420[0-9]{9}(92|93|94|95)'
                    )
                ORDER BY s.CreatedAt ASC
            ";
            return await db.Ado.SqlQueryAsync<string>(sql, new { CutoffTime = cutoffTime });
        }

        public async Task<int> GetEligibleUspsWaybillCountAsync(DateTime cutoffTime)
        {
            var db = GetDb();
            var sql = @"
                SELECT COUNT(DISTINCT l.FinalMileTrackingNumber)
                FROM label_replace_requests l
                LEFT JOIN bag_tag_waybills b ON l.FinalMileTrackingNumber = b.FinalMileTrackingNumber
                JOIN label_scan_history s ON l.NeutralWaybillNumber = s.NeutralWaybillNumber
                WHERE 
                    l.ReplaceStatus = 'Y'
                    AND s.Result = 0
                    AND s.CreatedAt >= @CutoffTime
                    AND l.FinalMileTrackingNumber IS NOT NULL
                    AND l.FinalMileTrackingNumber != ''
                    AND b.Id IS NULL
                    AND (
                        l.FinalMileTrackingNumber REGEXP '^(92|93|94|95)'
                        OR l.FinalMileTrackingNumber REGEXP '^420[0-9]{5}(92|93|94|95)'
                        OR l.FinalMileTrackingNumber REGEXP '^420[0-9]{9}(92|93|94|95)'
                    )
            ";
            return await db.Ado.GetIntAsync(sql, new { CutoffTime = cutoffTime });
        }
    }
}