EmailBill/Repository/TransactionRecordRepository.cs

namespace Repository;

public interface ITransactionRecordRepository : IBaseRepository<TransactionRecord>
{
    Task<TransactionRecord?> ExistsByEmailMessageIdAsync(long emailMessageId, DateTime occurredAt);

    Task<TransactionRecord?> ExistsByImportNoAsync(string importNo, string importFrom);

    Task<List<TransactionRecord>> QueryAsync(
        int? year = null,
        int? month = null,
        DateTime? startDate = null,
        DateTime? endDate = null,
        TransactionType? type = null,
        string[]? classifies = null,
        string? searchKeyword = null,
        string? reason = null,
        int pageIndex = 1,
        int pageSize = int.MaxValue,
        bool sortByAmount = false);

    Task<long> CountAsync(
        int? year = null,
        int? month = null,
        DateTime? startDate = null,
        DateTime? endDate = null,
        TransactionType? type = null,
        string[]? classifies = null,
        string? searchKeyword = null,
        string? reason = null);

    Task<List<string>> GetDistinctClassifyAsync();

    Task<List<TransactionRecord>> GetByEmailIdAsync(long emailMessageId);

    Task<int> GetCountByEmailIdAsync(long emailMessageId);

    Task<List<TransactionRecord>> GetUnclassifiedAsync(int pageSize = 10);

    Task<List<TransactionRecord>> GetClassifiedByKeywordsAsync(List<string> keywords, int limit = 10);

    Task<List<TransactionRecord>> GetUnconfirmedRecordsAsync();

    Task<int> BatchUpdateByReasonAsync(string reason, TransactionType type, string classify);

    Task<int> UpdateCategoryNameAsync(string oldName, string newName, TransactionType type);

    Task<int> ConfirmAllUnconfirmedAsync(long[] ids);
}

public class TransactionRecordRepository(IFreeSql freeSql) : BaseRepository<TransactionRecord>(freeSql), ITransactionRecordRepository
{
    private ISelect<TransactionRecord> BuildQuery(
        int? year = null,
        int? month = null,
        DateTime? startDate = null,
        DateTime? endDate = null,
        TransactionType? type = null,
        string[]? classifies = null,
        string? searchKeyword = null,
        string? reason = null)
    {
        var query = FreeSql.Select<TransactionRecord>();

        query = query.WhereIf(!string.IsNullOrWhiteSpace(searchKeyword),
                                t => t.Reason.Contains(searchKeyword!) ||
                                    t.Classify.Contains(searchKeyword!) ||
                                    t.Card.Contains(searchKeyword!) ||
                                    t.ImportFrom.Contains(searchKeyword!))
                    .WhereIf(!string.IsNullOrWhiteSpace(reason),
                                t => t.Reason == reason);

        if (classifies is { Length: > 0 })
        {
            var filterClassifies = classifies.Select(c => c == "未分类" ? string.Empty : c).ToList();
            query = query.Where(t => filterClassifies.Contains(t.Classify));
        }

        query = query.WhereIf(type.HasValue, t => t.Type == type!.Value);

        if (year.HasValue)
        {
            if (month.HasValue && month.Value > 0)
            {
                // 查询指定年月
                var dateStart = new DateTime(year.Value, month.Value, 1);
                var dateEnd = dateStart.AddMonths(1);
                query = query.Where(t => t.OccurredAt >= dateStart && t.OccurredAt < dateEnd);
            }
            else
            {
                // 查询整年数据（1月1日到下年1月1日）
                var dateStart = new DateTime(year.Value, 1, 1);
                var dateEnd = new DateTime(year.Value + 1, 1, 1);
                query = query.Where(t => t.OccurredAt >= dateStart && t.OccurredAt < dateEnd);
            }
        }

        query = query.WhereIf(startDate.HasValue, t => t.OccurredAt >= startDate!.Value)
                     .WhereIf(endDate.HasValue, t => t.OccurredAt <= endDate!.Value);

        return query;
    }

    public async Task<TransactionRecord?> ExistsByEmailMessageIdAsync(long emailMessageId, DateTime occurredAt)
    {
        return await FreeSql.Select<TransactionRecord>()
            .Where(t => t.EmailMessageId == emailMessageId && t.OccurredAt == occurredAt)
            .FirstAsync();
    }

    public async Task<TransactionRecord?> ExistsByImportNoAsync(string importNo, string importFrom)
    {
        return await FreeSql.Select<TransactionRecord>()
            .Where(t => t.ImportNo == importNo && t.ImportFrom == importFrom)
            .FirstAsync();
    }

    public async Task<List<TransactionRecord>> QueryAsync(
        int? year = null,
        int? month = null,
        DateTime? startDate = null,
        DateTime? endDate = null,
        TransactionType? type = null,
        string[]? classifies = null,
        string? searchKeyword = null,
        string? reason = null,
        int pageIndex = 1,
        int pageSize = int.MaxValue,
        bool sortByAmount = false)
    {
        var query = BuildQuery(year, month, startDate, endDate, type, classifies, searchKeyword, reason);

        if (sortByAmount)
        {
            return await query
                .OrderByDescending(t => t.Amount)
                .OrderByDescending(t => t.Id)
                .Page(pageIndex, pageSize)
                .ToListAsync();
        }

        return await query
            .OrderByDescending(t => t.OccurredAt)
            .OrderByDescending(t => t.Id)
            .Page(pageIndex, pageSize)
            .ToListAsync();
    }

    public async Task<long> CountAsync(
        int? year = null,
        int? month = null,
        DateTime? startDate = null,
        DateTime? endDate = null,
        TransactionType? type = null,
        string[]? classifies = null,
        string? searchKeyword = null,
        string? reason = null)
    {
        var query = BuildQuery(year, month, startDate, endDate, type, classifies, searchKeyword, reason);
        return await query.CountAsync();
    }

    public async Task<List<string>> GetDistinctClassifyAsync()
    {
        return await FreeSql.Select<TransactionRecord>()
            .Where(t => !string.IsNullOrEmpty(t.Classify))
            .Distinct()
            .ToListAsync(t => t.Classify);
    }

    public async Task<List<TransactionRecord>> GetByEmailIdAsync(long emailMessageId)
    {
        return await FreeSql.Select<TransactionRecord>()
            .Where(t => t.EmailMessageId == emailMessageId)
            .OrderBy(t => t.OccurredAt)
            .ToListAsync();
    }

    public async Task<int> GetCountByEmailIdAsync(long emailMessageId)
    {
        return (int)await FreeSql.Select<TransactionRecord>()
            .Where(t => t.EmailMessageId == emailMessageId)
            .CountAsync();
    }

    public async Task<List<TransactionRecord>> GetUnclassifiedAsync(int pageSize = 10)
    {
        return await FreeSql.Select<TransactionRecord>()
            .Where(t => string.IsNullOrEmpty(t.Classify))
            .OrderByDescending(t => t.OccurredAt)
            .Page(1, pageSize)
            .ToListAsync();
    }

    public async Task<List<TransactionRecord>> GetClassifiedByKeywordsAsync(List<string> keywords, int limit = 10)
    {
        if (keywords.Count == 0)
        {
            return [];
        }

        var query = FreeSql.Select<TransactionRecord>()
            .Where(t => t.Classify != "");

        if (keywords.Count > 0)
        {
            query = query.Where(t => keywords.Any(keyword => t.Reason.Contains(keyword)));
        }

        return await query
            .OrderByDescending(t => t.OccurredAt)
            .Limit(limit)
            .ToListAsync();
    }

    public async Task<List<TransactionRecord>> GetUnconfirmedRecordsAsync()
    {
        return await FreeSql.Select<TransactionRecord>()
            .Where(t => t.UnconfirmedClassify != null && t.UnconfirmedClassify != "")
            .OrderByDescending(t => t.OccurredAt)
            .ToListAsync();
    }

    public async Task<int> BatchUpdateByReasonAsync(string reason, TransactionType type, string classify)
    {
        return await FreeSql.Update<TransactionRecord>()
            .Set(t => t.Type, type)
            .Set(t => t.Classify, classify)
            .Where(t => t.Reason == reason)
            .ExecuteAffrowsAsync();
    }

    public async Task<int> UpdateCategoryNameAsync(string oldName, string newName, TransactionType type)
    {
        return await FreeSql.Update<TransactionRecord>()
            .Set(a => a.Classify, newName)
            .Where(a => a.Classify == oldName && a.Type == type)
            .ExecuteAffrowsAsync();
    }

    public async Task<int> ConfirmAllUnconfirmedAsync(long[] ids)
    {
        return await FreeSql.Update<TransactionRecord>()
            .Set(t => t.Classify == t.UnconfirmedClassify)
            .Set(t => t.Type == (t.UnconfirmedType ?? t.Type))
            .Set(t => t.UnconfirmedClassify, null)
            .Set(t => t.UnconfirmedType, null)
            .Where(t => t.UnconfirmedClassify != null && t.UnconfirmedClassify != "")
            .Where(t => ids.Contains(t.Id))
            .ExecuteAffrowsAsync();
    }
}