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

namespace DAL.Repositories
{
    public class ShippingHandoverFormRepository : IShippingHandoverFormRepository
    {
        private readonly ISqlSugarProvider _provider;

        public ShippingHandoverFormRepository(ISqlSugarProvider provider)
        {
            _provider = provider;
            // 初始化表结构
            var db = _provider.GetClient();
            db.CodeFirst.InitTables(typeof(ShippingHandoverFormEntity));
        }

        public async Task<int> InsertAsync(ShippingHandoverFormEntity form)
        {
            var db = _provider.GetClient();
            return await db.Insertable(form).ExecuteReturnIdentityAsync();
        }

        public async Task<ShippingHandoverFormEntity> GetByIdAsync(int id)
        {
            var db = _provider.GetClient();
            return await db.Queryable<ShippingHandoverFormEntity>().Where(f => f.Id == id).FirstAsync();
        }

        public async Task<ShippingHandoverFormEntity> GetByHandoverNumberAsync(string handoverNumber)
        {
            var db = _provider.GetClient();
            return await db.Queryable<ShippingHandoverFormEntity>().Where(f => f.HandoverNumber == handoverNumber).FirstAsync();
        }

        public async Task<List<ShippingHandoverFormEntity>> GetAllAsync()
        {
            var db = _provider.GetClient();
            return await db.Queryable<ShippingHandoverFormEntity>().ToListAsync();
        }

        public async Task<int> UpdateAsync(ShippingHandoverFormEntity form)
        {
            var db = _provider.GetClient();
            return await db.Updateable(form).ExecuteCommandAsync();
        }

        public async Task<int> DeleteAsync(int id)
        {
            var db = _provider.GetClient();
            return await db.Deleteable<ShippingHandoverFormEntity>().Where(f => f.Id == id).ExecuteCommandAsync();
        }

        public async Task<bool> ExistsByHandoverNumberAsync(string handoverNumber)
        {
            var db = _provider.GetClient();
            return await db.Queryable<ShippingHandoverFormEntity>().Where(f => f.HandoverNumber == handoverNumber).AnyAsync();
        }

        public async Task<(List<ShippingHandoverFormEntity> Forms, int TotalCount)> GetShippingHandoverFormsBatchAsync(
            int page, 
            int pageSize, 
            string sortBy, 
            string sortOrder,
            string handoverNumber,
            string channel,
            string creator,
            System.DateTime? startDeliveryTime = null,
            System.DateTime? endDeliveryTime = null)
        {
            var db = _provider.GetClient();
            var query = db.Queryable<ShippingHandoverFormEntity>();

            // 应用过滤条件
            if (!string.IsNullOrEmpty(handoverNumber))
            {
                query = query.Where(f => f.HandoverNumber.Contains(handoverNumber));
            }
            if (!string.IsNullOrEmpty(channel))
            {
                query = query.Where(f => f.Channel.Contains(channel));
            }
            if (!string.IsNullOrEmpty(creator))
            {
                query = query.Where(f => f.Creator.Contains(creator));
            }
            if (startDeliveryTime.HasValue)
            {
                query = query.Where(f => f.DeliveryTime >= startDeliveryTime);
            }
            if (endDeliveryTime.HasValue)
            {
                query = query.Where(f => f.DeliveryTime <= endDeliveryTime);
            }

            // 获取总记录数
            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 forms = await query.Skip((page - 1) * pageSize).Take(pageSize).ToListAsync();

            return (forms, totalCount);
        }
    }
}