ANTOOZA-BACKEND/Infrastructure/StockData/SeibroForeignImport.cs

using Application.Abstractions.Data;
using Domain.Entities.Stocks;
using Microsoft.EntityFrameworkCore;

namespace Infrastructure.StockData;

/// <summary>
/// SeibroForeignSyncService 의 DB 반영 로직 분리 (테스트 가능) — HTTP 없이 파서 출력(fixture)으로 검증한다 (SeibroDerivImport 패턴 미러링).
/// 네 엔티티(국가별 보관·국가별 결제·종목별 보관·종목별 결제) upsert 담당. 모든 응답에 날짜/국가/ISIN 이 없어(요청 파라미터로만 받음)
/// 배치가 STD_DT|SETL_DT|PROC_DT·NATION_CD·ISIN 요청값을 스탬핑한다. 자연키:
///   • ForeignCustodyNation   UQ (StdDt, NationCd, SecnTpcd)  — 국가×종목구분 다수 행/콜
///   • ForeignSettlementNation UQ (SetlDt, NationCd, IntlBizCacd, SecnTpcd) — 국가×매매×종목구분 다수 행/콜
///   • ForeignCustodySecurity  UQ (StdDt, Isin, NationCd, SecnTpcd) — 종목당 국가별 복수 행 전부 반영 (리뷰 결함 #3)
///   • ForeignSettlementSecurity UQ (ProcDt, Isin, IntlBizCacd) — 종목×매매(1110매수/1120매도) 다수 행/콜. 종목당 단일 국가라 NationCd 불요(샘플 확인)
/// </summary>
public static class SeibroForeignImport
{
    /// <summary>getNationFrsecCusInfo 결과를 ForeignCustodyNation 으로 upsert — UQ (StdDt, NationCd, SecnTpcd). STD_DT·NATION_CD 요청값 스탬핑. 종목구분 다수 행/콜.</summary>
    public static async Task<(int Inserted, int Updated)> UpsertCustodyNationAsync(IAppDbContext db, DateOnly stdDt, string nationCd, IReadOnlyList<SeibroNationFrsecCusParser.Row> rows, CancellationToken ct)
    {
        if (rows.Count == 0)
        {
            return (0, 0);
        }

        var existing = await db.ForeignCustodyNation.Where(c => c.StdDt == stdDt && c.NationCd == nationCd).ToListAsync(ct);
        var byKey = existing.ToDictionary(c => c.SecnTpcd);
        var seen = new HashSet<string>();
        var inserted = 0;
        var updated = 0;

        foreach (var row in rows)
        {
            if (!seen.Add(row.SecnTpcd))
            {
                continue;
            }

            if (byKey.TryGetValue(row.SecnTpcd, out var entity))
            {
                var was = entity.UpdatedAt;
                entity.Update(row.FrsecCusAmt);
                if (entity.UpdatedAt != was)
                {
                    updated++;
                }
            }
            else
            {
                var created = ForeignCustodyNation.Create(stdDt, nationCd, row.SecnTpcd, row.FrsecCusAmt);
                await db.ForeignCustodyNation.AddAsync(created, ct);
                byKey[row.SecnTpcd] = created;
                inserted++;
            }
        }

        await db.SaveChangesAsync(ct);
        return (inserted, updated);
    }

    /// <summary>getNationFrsecSetlInfo 결과를 ForeignSettlementNation 으로 upsert — UQ (SetlDt, NationCd, IntlBizCacd, SecnTpcd). SETL_DT·NATION_CD 요청값 스탬핑.</summary>
    public static async Task<(int Inserted, int Updated)> UpsertSettlementNationAsync(IAppDbContext db, DateOnly setlDt, string nationCd, IReadOnlyList<SeibroNationFrsecSetlParser.Row> rows, CancellationToken ct)
    {
        if (rows.Count == 0)
        {
            return (0, 0);
        }

        var existing = await db.ForeignSettlementNation.Where(c => c.SetlDt == setlDt && c.NationCd == nationCd).ToListAsync(ct);
        var byKey = existing.ToDictionary(c => (c.IntlBizCacd, c.SecnTpcd));
        var seen = new HashSet<(string, string)>();
        var inserted = 0;
        var updated = 0;

        foreach (var row in rows)
        {
            var key = (row.IntlBizCacd, row.SecnTpcd);
            if (!seen.Add(key))
            {
                continue;
            }

            if (byKey.TryGetValue(key, out var entity))
            {
                var was = entity.UpdatedAt;
                entity.Update(row.SetlCount, row.FrsecSetlAmt);
                if (entity.UpdatedAt != was)
                {
                    updated++;
                }
            }
            else
            {
                var created = ForeignSettlementNation.Create(setlDt, nationCd, row.IntlBizCacd, row.SecnTpcd, row.SetlCount, row.FrsecSetlAmt);
                await db.ForeignSettlementNation.AddAsync(created, ct);
                byKey[key] = created;
                inserted++;
            }
        }

        await db.SaveChangesAsync(ct);
        return (inserted, updated);
    }

    /// <summary>
    /// getSecnFrsecCusInfo 결과를 ForeignCustodySecurity 로 upsert — UQ (StdDt, Isin, NationCd, SecnTpcd). STD_DT·ISIN 요청값 스탬핑.
    /// 종목당 국가별 복수 행(실샘플 DE+US)을 전 행 iterate upsert 한다 (리뷰 결함 #3 — 기존 첫 행만 반영 → 국가별 보관 무음 병합 해소). 빈결과면 no-op.
    /// </summary>
    public static async Task<bool> UpsertCustodySecurityAsync(IAppDbContext db, DateOnly stdDt, string isin, IReadOnlyList<SeibroSecnFrsecCusParser.Row> rows, CancellationToken ct)
    {
        if (rows.Count == 0)
        {
            return false;
        }

        var existing = await db.ForeignCustodySecurity.Where(c => c.StdDt == stdDt && c.Isin == isin).ToListAsync(ct);
        var byKey = existing.ToDictionary(c => (c.NationCd, c.SecnTpcd));
        var seen = new HashSet<(string, string)>();

        foreach (var row in rows)
        {
            var nationCd = string.IsNullOrWhiteSpace(row.NationCd) ? string.Empty : row.NationCd.Trim();
            var secnTpcd = string.IsNullOrWhiteSpace(row.SecnTpcd) ? string.Empty : row.SecnTpcd.Trim();
            var key = (nationCd, secnTpcd);

            if (!seen.Add(key))
            {
                continue;
            }

            if (byKey.TryGetValue(key, out var entity))
            {
                entity.Update(row.FrsecTotHoldAmt, row.FrsecTotHoldQty);
            }
            else
            {
                var created = ForeignCustodySecurity.Create(stdDt, isin, row.NationCd, row.SecnTpcd, row.FrsecTotHoldAmt, row.FrsecTotHoldQty);
                await db.ForeignCustodySecurity.AddAsync(created, ct);
                byKey[key] = created;
            }
        }

        await db.SaveChangesAsync(ct);
        return true;
    }

    /// <summary>getSecnFrsecSetlInfo 결과를 ForeignSettlementSecurity 로 upsert — UQ (ProcDt, Isin, IntlBizCacd). PROC_DT·ISIN 요청값 스탬핑. 종목×매매 다수 행/콜.</summary>
    public static async Task<(int Inserted, int Updated)> UpsertSettlementSecurityAsync(IAppDbContext db, DateOnly procDt, string isin, IReadOnlyList<SeibroSecnFrsecSetlParser.Row> rows, CancellationToken ct)
    {
        if (rows.Count == 0)
        {
            return (0, 0);
        }

        var existing = await db.ForeignSettlementSecurity.Where(c => c.ProcDt == procDt && c.Isin == isin).ToListAsync(ct);
        var byKey = existing.ToDictionary(c => c.IntlBizCacd);
        var seen = new HashSet<string>();
        var inserted = 0;
        var updated = 0;

        foreach (var row in rows)
        {
            if (!seen.Add(row.IntlBizCacd))
            {
                continue;
            }

            if (byKey.TryGetValue(row.IntlBizCacd, out var entity))
            {
                var was = entity.UpdatedAt;
                entity.Update(row.NationCd, row.SecnTpcd, row.SetlAskQty, row.SetlAmt);
                if (entity.UpdatedAt != was)
                {
                    updated++;
                }
            }
            else
            {
                var created = ForeignSettlementSecurity.Create(procDt, isin, row.IntlBizCacd, row.NationCd, row.SecnTpcd, row.SetlAskQty, row.SetlAmt);
                await db.ForeignSettlementSecurity.AddAsync(created, ct);
                byKey[row.IntlBizCacd] = created;
                inserted++;
            }
        }

        await db.SaveChangesAsync(ct);
        return (inserted, updated);
    }
}