ANTOOZA-BACKEND/Application/Features/Admin/Store/Coupon/ImportCodes/Handler.cs

using System.Globalization;
using System.Security.Cryptography;
using System.Text;
using Application.Abstractions.Data;
using Application.Abstractions.Messaging;
using Microsoft.EntityFrameworkCore;
using SharedKernel.Helpers;
using SharedKernel.Results;
using CouponCodeEntity = Domain.Entities.Store.CouponCode;

namespace Application.Features.Admin.Store.Coupon.ImportCodes;

/// <summary>
/// CSV 양식 (DB 컬럼명 기준, 9 컬럼):
///   ID, Code, Status, IssuedToMemberID, IssuedToMemberName, IssuedToMemberEmail, IssuedAt, UsedAt, CreatedAt
///
/// Upload 규칙:
///   - 헤더 행 필수 (대소문자 무관, 영어 컬럼명)
///   - Status = 0 (Available 신규 등록) 또는 1 (Used; 회원/일시 필수)
///   - IssuedToMemberID 가 Member 테이블에 존재해야 함 (Status=1 일 때)
///   - IssuedToMemberName / IssuedToMemberEmail / CreatedAt / ID 는 import 시 무시 (참고용)
///   - 일시는 KST 입력 → UTC 변환 후 저장
///   - 기존 코드 hash 중복은 skip
/// </summary>
public sealed class Handler(IAppDbContext db) : ICommandHandler<Command, Result<Response>>
{
    private const int BatchSize = 500;
    private const int LookupBatchSize = 1000;

    private static readonly string[] ExpectedHeader =
    [
        "ID",
        "Code",
        "Status",
        "IssuedToMemberID",
        "IssuedToMemberName",
        "IssuedToMemberEmail",
        "IssuedAt",
        "UsedAt",
        "CreatedAt"
    ];

    public async Task<Result<Response>> Handle(Command request, CancellationToken ct)
    {
        var coupon = await db.Coupon.FirstOrDefaultAsync(c => c.ID == request.CouponID, ct);

        if (coupon is null)
        {
            return Result.Failure<Response>(Error.NotFound("Coupon.NotFound", "쿠폰을 찾을 수 없습니다."));
        }

        if (request.CsvFile is null || request.CsvFile.Length == 0)
        {
            return Result.Failure<Response>(Error.Problem("Coupon.EmptyFile", "업로드할 CSV 파일이 비어있습니다."));
        }

        if (request.CsvFile.Length > 10 * 1024 * 1024)
        {
            return Result.Failure<Response>(Error.Problem("Coupon.FileTooLarge", "CSV 파일은 10MB 이하만 허용합니다."));
        }

        List<string[]> rows;
        using (var stream = request.CsvFile.OpenReadStream())
        using (var reader = new StreamReader(stream, Encoding.UTF8))
        {
            rows = [.. CsvHelper.Read(reader)];
        }

        if (rows.Count == 0)
        {
            return Result.Failure<Response>(Error.Problem("Coupon.EmptyCsv", "CSV 에 데이터가 없습니다."));
        }

        var header = rows[0];

        if (header.Length < ExpectedHeader.Length)
        {
            return Result.Failure<Response>(Error.Problem("Coupon.InvalidHeader",
                $"CSV 헤더 컬럼 수 부족. {ExpectedHeader.Length}개 필요, {header.Length}개 발견."));
        }

        for (var i = 0; i < ExpectedHeader.Length; i++)
        {
            if (!string.Equals(header[i].Trim(), ExpectedHeader[i], StringComparison.OrdinalIgnoreCase))
            {
                return Result.Failure<Response>(Error.Problem("Coupon.InvalidHeader",
                    $"CSV 헤더 {i + 1}번째 컬럼이 다릅니다. 기대: {ExpectedHeader[i]}, 실제: {header[i]}"));
            }
        }

        var dataRows = rows.Skip(1).ToList();
        var totalLines = dataRows.Count;

        // 회원 ID 사전 lookup (Status=1 행 후보)
        var memberIDsInCsv = new HashSet<int>();

        foreach (var row in dataRows)
        {
            if (row.Length < 4)
            {
                continue;
            }

            if (int.TryParse(row[3].Trim(), out var mid) && mid > 0)
            {
                memberIDsInCsv.Add(mid);
            }
        }

        var existingMemberIDs = new HashSet<int>();

        foreach (var batch in memberIDsInCsv.Chunk(LookupBatchSize))
        {
            var found = await db.Member.AsNoTracking()
                .Where(m => batch.Contains(m.ID))
                .Select(m => m.ID)
                .ToListAsync(ct);

            foreach (var id in found)
            {
                existingMemberIDs.Add(id);
            }
        }

        // 기존 코드 hash 사전 조회
        var hashesInCsv = new HashSet<string>();
        var rowHashes = new string?[dataRows.Count];

        for (var i = 0; i < dataRows.Count; i++)
        {
            var row = dataRows[i];

            if (row.Length < 2)
            {
                continue;
            }

            var code = row[1].Trim();

            if (string.IsNullOrWhiteSpace(code) || code.Length > 100)
            {
                continue;
            }

            var hash = ComputeHash(code);
            rowHashes[i] = hash;
            hashesInCsv.Add(hash);
        }

        var existingHashes = new HashSet<string>();

        foreach (var batch in hashesInCsv.Chunk(LookupBatchSize))
        {
            var found = await db.CouponCode.AsNoTracking()
                .Where(c => batch.Contains(c.CodeHash))
                .Select(c => c.CodeHash)
                .ToListAsync(ct);

            foreach (var h in found)
            {
                existingHashes.Add(h);
            }
        }

        var added = 0;
        var addedAvailable = 0;
        var addedUsed = 0;
        var skippedDuplicate = 0;
        var skippedInvalid = 0;
        var skippedMissingMember = 0;
        var batchAddedSinceSave = 0;
        var seenInCsv = new HashSet<string>();

        for (var i = 0; i < dataRows.Count; i++)
        {
            var row = dataRows[i];

            if (row.Length < ExpectedHeader.Length)
            {
                skippedInvalid++;
                continue;
            }

            var code = row[1].Trim();

            if (string.IsNullOrWhiteSpace(code) || code.Length > 100)
            {
                skippedInvalid++;
                continue;
            }

            var hash = rowHashes[i];

            if (hash is null)
            {
                skippedInvalid++;
                continue;
            }

            if (existingHashes.Contains(hash))
            {
                skippedDuplicate++;
                continue;
            }

            if (!seenInCsv.Add(hash))
            {
                skippedDuplicate++;
                continue;
            }

            var statusStr = row[2].Trim();

            if (statusStr != "0" && statusStr != "1")
            {
                skippedInvalid++;
                continue;
            }

            CouponCodeEntity entity;

            if (statusStr == "0")
            {
                entity = CouponCodeEntity.Create(request.CouponID, code);
                addedAvailable++;
            }
            else
            {
                if (!int.TryParse(row[3].Trim(), out var memberID) || memberID <= 0)
                {
                    skippedInvalid++;
                    continue;
                }

                if (!existingMemberIDs.Contains(memberID))
                {
                    skippedMissingMember++;
                    continue;
                }

                if (!TryParseLocalDateTime(row[6].Trim(), out var issuedAtLocal) ||
                    !TryParseLocalDateTime(row[7].Trim(), out var usedAtLocal))
                {
                    skippedInvalid++;
                    continue;
                }

                var issuedAtUtc = DateTime.SpecifyKind(issuedAtLocal, DateTimeKind.Local).ToUniversalTime();
                var usedAtUtc = DateTime.SpecifyKind(usedAtLocal, DateTimeKind.Local).ToUniversalTime();

                if (issuedAtUtc > usedAtUtc)
                {
                    skippedInvalid++;
                    continue;
                }

                try
                {
                    entity = CouponCodeEntity.CreateUsedForImport(request.CouponID, code, memberID, issuedAtUtc, usedAtUtc);
                }
                catch
                {
                    skippedInvalid++;
                    continue;
                }

                addedUsed++;
            }

            db.CouponCode.Add(entity);
            added++;
            batchAddedSinceSave++;

            if (batchAddedSinceSave >= BatchSize)
            {
                await db.SaveChangesAsync(ct);
                batchAddedSinceSave = 0;
            }
        }

        if (batchAddedSinceSave > 0)
        {
            await db.SaveChangesAsync(ct);
        }

        if (added > 0)
        {
            coupon.IncreaseGeneratedCount(added);

            if (addedUsed > 0)
            {
                coupon.IncreaseUsedCount(addedUsed);
            }

            await db.SaveChangesAsync(ct);
        }

        return Result.Success(new Response(
            TotalLines: totalLines,
            Added: added,
            AddedAvailable: addedAvailable,
            AddedUsed: addedUsed,
            SkippedDuplicate: skippedDuplicate,
            SkippedInvalid: skippedInvalid,
            SkippedMissingMember: skippedMissingMember
        ));
    }

    private static bool TryParseLocalDateTime(string input, out DateTime result)
    {
        var formats = new[]
        {
            "yyyy-MM-dd HH:mm:ss",
            "yyyy-MM-dd HH:mm",
            "yyyy-MM-ddTHH:mm:ss",
            "yyyy-MM-ddTHH:mm",
            "yyyy/MM/dd HH:mm:ss",
            "yyyy/MM/dd HH:mm"
        };

        if (DateTime.TryParseExact(input, formats, CultureInfo.InvariantCulture, DateTimeStyles.None, out result))
        {
            return true;
        }

        return DateTime.TryParse(input, CultureInfo.InvariantCulture, DateTimeStyles.None, out result);
    }

    private static string ComputeHash(string code)
    {
        var bytes = Encoding.UTF8.GetBytes(code);
        var hash = SHA256.HashData(bytes);
        return Convert.ToHexString(hash);
    }
}