using Admin.NET.Plugin.AiDOP.ProcurementExecution.Dto;
using Admin.NET.Plugin.AiDOP.ProcurementExecution.Entity;

namespace Admin.NET.Plugin.AiDOP.ProcurementExecution;

/// <summary>
/// S4 采购退货单（PurOrdRctMaster / PurOrdRctDetail，RctType=pt）
/// 路由：/api/ProcurementExecution/purchase-return/...
/// </summary>
[ApiDescriptionSettings(Order = 321, Description = "S4采购退货单")]
[Route("api/ProcurementExecution")]
[AllowAnonymous]
[NonUnify]
public class PurchaseReturnService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly SqlSugarRepository<PurOrdRctMaster> _masterRep;
    private readonly SqlSugarRepository<PurOrdRctDetail> _detailRep;
    private readonly UserManager _userManager;

    public PurchaseReturnService(
        ISqlSugarClient db,
        SqlSugarRepository<PurOrdRctMaster> masterRep,
        SqlSugarRepository<PurOrdRctDetail> detailRep,
        UserManager userManager)
    {
        _db = db;
        _masterRep = masterRep;
        _detailRep = detailRep;
        _userManager = userManager;
    }

    private static string NormalizeOrderBy(string field, string order)
    {
        var col = (field ?? "").Trim();
        var ord = string.Equals(order?.Trim(), "desc", StringComparison.OrdinalIgnoreCase) ? "DESC" : "ASC";
        var map = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            // 注意：列表 SQL 外层包了 SELECT * FROM (...) t，因此 ORDER BY 只能引用外层可见列名
            ["receiver"] = "Receiver",
            ["rctdate"] = "RctDate",
            ["ordnbr"] = "OrdNbr",
            ["itemnum"] = "ItemNum",
            ["rctqty"] = "RctQty",
            ["suppname"] = "SuppName",
        };
        if (!map.TryGetValue(col, out var sqlCol))
            sqlCol = "Id";
        return $"ORDER BY {sqlCol} {ord}, DetailRecId ASC";
    }

    [DisplayName("采购退货单分页列表")]
    [HttpGet("purchase-return/list")]
    public async Task<object> GetList([FromQuery] PurchaseReturnListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var cond = new List<string> { "p.RctType='pt'", "p.IsActive=1" };
        var pars = new List<SugarParameter>();
        if (!string.IsNullOrWhiteSpace(input.Receiver))
        {
            cond.Add("p.Receiver LIKE @Receiver");
            pars.Add(new SugarParameter("@Receiver", $"%{input.Receiver.Trim()}%"));
        }

        if (!string.IsNullOrWhiteSpace(input.Supp))
        {
            cond.Add("p.Supp=@Supp");
            pars.Add(new SugarParameter("@Supp", input.Supp.Trim()));
        }

        if (!string.IsNullOrWhiteSpace(input.OrdNbr))
        {
            cond.Add("p.OrdNbr LIKE @OrdNbr");
            pars.Add(new SugarParameter("@OrdNbr", $"%{input.OrdNbr.Trim()}%"));
        }

        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            cond.Add("d1.ItemNum LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }

        var where = string.Join(" AND ", cond);
        var baseSql = $"""
            SELECT
              p.RecID AS Id,
              IFNULL(d1.RecID, 0) AS DetailRecId,
              p.RctType AS RctType,
              p.Receiver AS Receiver,
              p.RctDate AS RctDate,
              p.OrdNbr AS OrdNbr,
              p.TaxClass AS TaxClass,
              IFNULL(p.TaxIn, 0) AS TaxIn,
              p.Curr AS Curr,
              p.Terms AS Terms,
              IFNULL(p.IsPlan, 0) AS IsPlan,
              TRIM(CONCAT(TRIM(IFNULL(p.Department,'')),' ',TRIM(IFNULL(d.Descr,'')))) AS DepartmentDescr,
              TRIM(CONCAT(TRIM(IFNULL(p.Supp,'')),' ',TRIM(IFNULL(s.SortName,'')))) AS SuppName,
              d1.ItemNum AS ItemNum,
              IFNULL(i.Descr,'') AS Descr,
              IFNULL(i.Descr1,'') AS Descr1,
              IFNULL(d1.RctQty, 0) AS RctQty
            FROM PurOrdRctMaster p
            LEFT JOIN PurOrdRctDetail d1 ON p.RecID = d1.PurOrdRctRecID AND d1.RctType='pt' AND d1.IsActive=1
            LEFT JOIN SuppMaster s ON p.Domain = s.Domain AND p.Supp = s.Supp
            LEFT JOIN DepartmentMaster d ON p.Domain = d.Domain AND p.Department = d.Department
            LEFT JOIN ItemMaster i ON d1.Domain = i.Domain AND d1.ItemNum = i.ItemNum
            WHERE {where}
            """;

        var orderBy = NormalizeOrderBy(input.SortField, input.SortOrder);

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM ({baseSql}) t", pars);
        var list = await _db.Ado.SqlQueryAsync<PurchaseReturnListRow>(
            $"SELECT * FROM ({baseSql}) t {orderBy} LIMIT {pageSize} OFFSET {offset}", pars);

        return new { total, page, pageSize, list };
    }

    [DisplayName("采购退货供应商下拉")]
    [HttpGet("purchase-return/options/suppliers")]
    public async Task<object> GetSuppliers()
    {
        var rows = await _db.Ado.SqlQueryAsync<PurchaseReturnKvItem>(
            """
            SELECT Supp AS `Value`,
                   TRIM(CONCAT(TRIM(IFNULL(Supp,'')),' ',TRIM(IFNULL(SortName,'')))) AS `Label`
            FROM SuppMaster
            ORDER BY Supp
            """);
        return rows;
    }

    [DisplayName("收货单号下拉(rc)")]
    [HttpGet("purchase-return/options/rc-receivers")]
    public async Task<object> GetRcReceivers([FromQuery] string supp)
    {
        if (string.IsNullOrWhiteSpace(supp))
            return Array.Empty<object>();
        var rows = await _db.Ado.SqlQueryAsync<PurchaseReturnKvItem>(
            """
            SELECT p.Receiver AS `Value`,
                   TRIM(CONCAT(TRIM(IFNULL(p.Receiver,'')),' ',
                              TRIM(IFNULL(d.LotSerial,'')),' ',
                              TRIM(IFNULL(d.ItemNum,'')),' ',
                              TRIM(IFNULL(i.Descr,'')))) AS `Label`
            FROM PurOrdRctMaster p
            LEFT JOIN PurOrdRctDetail d
              ON p.Domain = d.Domain AND p.RctType = d.RctType AND p.Receiver = d.Receiver
            LEFT JOIN ItemMaster i ON d.Domain = i.Domain AND d.ItemNum = i.ItemNum
            WHERE p.RctType = 'rc' AND p.Supp = @Supp AND d.RecID > 0 AND IFNULL(p.TermsofTrade,'') <> ''
            GROUP BY p.Receiver, d.LotSerial, d.ItemNum, i.Descr, p.Domain
            ORDER BY p.Receiver DESC
            """,
            new SugarParameter("@Supp", supp.Trim()));
        return rows;
    }

    [DisplayName("按收货单加载明细模板")]
    [HttpGet("purchase-return/rc-detail-lines")]
    public async Task<object> GetRcDetailLines([FromQuery] string receiver)
    {
        if (string.IsNullOrWhiteSpace(receiver))
            throw Oops.Oh("请先选择收货单号");
        var rows = await _db.Ado.SqlQueryAsync<PurchaseReturnRcLineRow>(
            """
            SELECT
              p.Domain AS Domain,
              p.Department AS Department,
              p.Terms AS Terms,
              p.TermsofTrade AS TermsofTrade,
              d.ItemNum AS ItemNum,
              d.UM AS UM,
              d.OrdNbr AS OrdNbr,
              CAST(d.OrdLine AS SIGNED) AS OrdLine,
              d.QtyOrded AS QtyOrded,
              d.Supp AS Supp,
              d.LotSerial AS LotSerial,
              CASE WHEN UPPER(IFNULL(d.Potype,'')) = 'PW' THEN d.Supp ELSE d.Location END AS Location,
              d.QtyReceived AS QtyReceived,
              d.Potype AS Potype,
              d.Typed AS Typed,
              p.TaxClass AS TaxClass,
              IFNULL(p.TaxIn, 0) AS TaxIn,
              p.Curr AS Curr,
              DATE_FORMAT(CURDATE(), '%Y-%m-%d') AS RctDate,
              d.UMConversion AS UMConversion,
              d.POCost AS POCost,
              d.QtyReceived AS QtyReturn,
              d.Receiver AS RctNbr,
              CAST(d.Line AS SIGNED) AS RctLine,
              d.CustPO AS CustPO,
              d.BlanketLine AS BlanketLine,
              IFNULL(d.RctQty, 0) AS RctQty,
              CAST(d.Line AS SIGNED) AS Line
            FROM PurOrdRctMaster p
            LEFT JOIN PurOrdRctDetail d
              ON p.Domain = d.Domain AND p.RctType = d.RctType AND p.Receiver = d.Receiver
            WHERE p.RctType = 'rc' AND p.Receiver = @Receiver AND d.RecID > 0
            ORDER BY d.Line
            """,
            new SugarParameter("@Receiver", receiver.Trim()));

        PurchaseReturnRcLineRow head = null;
        if (rows.Count > 0)
            head = rows[0];
        return new { head, lines = rows };
    }

    [DisplayName("部门下拉")]
    [HttpGet("purchase-return/options/departments")]
    public async Task<object> GetDepartments()
    {
        return await _db.Ado.SqlQueryAsync<PurchaseReturnKvItem>(
            """
            SELECT Department AS `Value`,
                   TRIM(CONCAT(TRIM(IFNULL(Department,'')),' ',TRIM(IFNULL(Descr,'')))) AS `Label`
            FROM DepartmentMaster
            ORDER BY Department
            """);
    }

    [DisplayName("库位下拉")]
    [HttpGet("purchase-return/options/locations")]
    public async Task<object> GetLocations()
    {
        return await _db.Ado.SqlQueryAsync<PurchaseReturnKvItem>(
            """
            SELECT Location AS `Value`,
                   TRIM(CONCAT(TRIM(IFNULL(Location,'')),' ',TRIM(IFNULL(Descr,'')))) AS `Label`
            FROM LocationMaster
            WHERE IFNULL(Typed,'') <> 'Supp'
            ORDER BY Location
            """);
    }

    [DisplayName("退货原因下拉")]
    [HttpGet("purchase-return/options/qc-reasons")]
    public async Task<object> GetQcReasons()
    {
        return await _db.Ado.SqlQueryAsync<PurchaseReturnKvItem>(
            """
            SELECT Val AS `Value`,
                   CONCAT(IFNULL(Val,''),'_',IFNULL(Comments,'')) AS `Label`
            FROM GeneralizedCodeMaster
            WHERE FldName = 'SAPTransReason'
              AND IFNULL(Ufld1,'') <> ''
              AND LOCATE('122', IFNULL(Ufld1,'')) > 0
            ORDER BY Val
            """);
    }

    [DisplayName("物料选择分页")]
    [HttpGet("purchase-return/item-options")]
    public async Task<object> GetItems([FromQuery] KeywordPageInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 20 : input.PageSize;
        var offset = (page - 1) * pageSize;
        var pars = new List<SugarParameter>();
        var where = "1=1";
        if (!string.IsNullOrWhiteSpace(input.Keyword))
        {
            where += " AND (ItemNum LIKE @Kw OR Descr LIKE @Kw OR Descr1 LIKE @Kw)";
            pars.Add(new SugarParameter("@Kw", $"%{input.Keyword.Trim()}%"));
        }

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM ItemMaster WHERE {where}", pars);
        var list = await _db.Ado.SqlQueryAsync<PurchaseReturnKvItem>(
            $"""
             SELECT ItemNum AS `Value`,
                    TRIM(CONCAT(IFNULL(Descr,''),' ',IFNULL(Descr1,''))) AS `Label`
             FROM ItemMaster
             WHERE {where}
             ORDER BY ItemNum
             LIMIT {pageSize} OFFSET {offset}
             """,
            pars);
        return new { total, page, pageSize, list };
    }

    [DisplayName("采购退货单详情")]
    [HttpGet("purchase-return/{id:int}")]
    public async Task<object> GetDetail(int id)
    {
        var master = await _masterRep.GetFirstAsync(m => m.RecID == id && m.RctType == "pt")
                     ?? throw Oops.Oh("单据不存在");

        var m = await _db.Ado.SqlQueryAsync<PurchaseReturnMasterDto>(
            """
            SELECT
              m.RecID AS RecID,
              m.Domain AS Domain,
              m.Receiver AS Receiver,
              DATE_FORMAT(m.RctDate,'%Y-%m-%d') AS RctDate,
              m.Supp AS Supp,
              m.OrdNbr AS OrdNbr,
              m.Department AS Department,
              IFNULL(m.Terms,'') AS Terms,
              IFNULL(m.Curr,'') AS Curr,
              IFNULL(m.TaxClass,'') AS TaxClass,
              IFNULL(m.TaxIn, 0) AS TaxIn,
              IFNULL(m.TermsofTrade,'') AS TermsofTrade,
              IFNULL(m.Remark,'') AS Remark,
              IFNULL(m.IsPlan, 0) AS IsPlan
            FROM PurOrdRctMaster m
            WHERE m.RecID = @Id
            LIMIT 1
            """,
            new SugarParameter("@Id", id));
        var head = m.FirstOrDefault() ?? throw Oops.Oh("单据不存在");

        var detailRows = await _db.Queryable<PurOrdRctDetail>()
            .Where(d => d.PurOrdRctRecID == id && d.RctType == "pt" && d.IsActive)
            .OrderBy(d => d.Line)
            .ToListAsync();

        var details = detailRows.Select(ToDetailDto).ToList();

        return new { master = head, details };
    }

    private static PurchaseReturnDetailDto ToDetailDto(PurOrdRctDetail d)
    {
        return new PurchaseReturnDetailDto
        {
            RecID = d.RecID,
            Line = d.Line,
            ItemNum = d.ItemNum,
            OrdNbr = d.OrdNbr,
            OrdLine = d.OrdLine,
            QtyOrded = d.QtyOrded,
            QtyReceived = d.QtyReceived,
            RctQty = d.RctQty,
            LotSerial = d.LotSerial,
            UM = d.UM,
            Location = d.Location,
            QCDescr = d.QCDescr,
            Remark = d.Remark,
            RctDate = d.RctDate?.ToString("yyyy-MM-dd"),
            Typed = d.Typed,
            TaxIn = d.TaxIn,
            TaxClass = d.TaxClass,
            Curr = d.Curr,
            Potype = d.Potype,
            IsActive = d.IsActive,
            IsConfirm = d.IsConfirm,
            UMConversion = d.UMConversion,
            POCost = d.POCost,
            RctNbr = d.RctNbr,
            RctLine = d.RctLine,
            CustPO = d.CustPO,
            BlanketLine = d.BlanketLine,
            QtyReturn = d.QtyReturn,
            Supp = d.Supp,
        };
    }

    [DisplayName("保存采购退货单")]
    [HttpPost("purchase-return/save")]
    public async Task<object> Save([FromBody] PurchaseReturnSaveInput input)
    {
        if (input == null) throw Oops.Oh("参数无效");
        if (string.IsNullOrWhiteSpace(input.Supp)) throw Oops.Oh("请选择供应商");
        if (string.IsNullOrWhiteSpace(input.OrdNbr)) throw Oops.Oh("请选择收货单号");
        if (string.IsNullOrWhiteSpace(input.Department)) throw Oops.Oh("请选择部门");
        if (input.Details == null || input.Details.Count == 0) throw Oops.Oh("请维护退货明细");

        var now = DateTime.Now;
        var account = _userManager.Account ?? "system";

        var domain = string.IsNullOrWhiteSpace(input.Domain)
            ? await ResolveRcDomainAsync(input.OrdNbr.Trim())
            : input.Domain.Trim();
        if (string.IsNullOrWhiteSpace(domain))
            domain = "100";

        if (input.Id is null or 0)
        {
            var receiver = await AllocReceiverAsync();
            var rctDate = ParseDate(input.RctDate) ?? now.Date;

            var master = new PurOrdRctMaster
            {
                Domain = domain,
                RctType = "pt",
                Receiver = receiver,
                RctDate = rctDate,
                OrdNbr = input.OrdNbr.Trim(),
                Supp = input.Supp.Trim(),
                Department = input.Department.Trim(),
                Remark = input.Remark?.Trim(),
                Terms = input.Terms?.Trim(),
                Curr = input.Curr?.Trim(),
                TaxClass = input.TaxClass?.Trim(),
                TaxIn = input.TaxIn,
                TermsofTrade = input.TermsofTrade?.Trim(),
                Typed = "C",
                IsActive = true,
                IsConfirm = false,
                IsPlan = true,
                CreateUser = account,
                CreateTime = now,
                UpdateUser = account,
                UpdateTime = now,
            };

            var newId = await _db.Insertable(master).ExecuteReturnIdentityAsync();
            master.RecID = Convert.ToInt32(newId);

            await SaveDetailsAsync(master.RecID, domain, receiver, "pt", input.Details, account, now);

            return new { id = master.RecID, receiver, message = "新增成功" };
        }

        var existing = await _masterRep.GetFirstAsync(m => m.RecID == input.Id!.Value && m.RctType == "pt")
                       ?? throw Oops.Oh("单据不存在");
        if (!existing.IsPlan)
            throw Oops.Oh("该单据不允许修改");

        existing.RctDate = ParseDate(input.RctDate) ?? existing.RctDate;
        existing.OrdNbr = input.OrdNbr.Trim();
        existing.Supp = input.Supp.Trim();
        existing.Department = input.Department.Trim();
        existing.Remark = input.Remark?.Trim();
        existing.Terms = input.Terms?.Trim();
        existing.Curr = input.Curr?.Trim();
        existing.TaxClass = input.TaxClass?.Trim();
        existing.TaxIn = input.TaxIn;
        existing.TermsofTrade = input.TermsofTrade?.Trim();
        existing.Domain = domain;
        existing.UpdateUser = account;
        existing.UpdateTime = now;

        await _masterRep.UpdateAsync(existing);

        await SaveDetailsAsync(existing.RecID, existing.Domain, existing.Receiver, "pt", input.Details, account, now);

        return new { id = existing.RecID, receiver = existing.Receiver, message = "保存成功" };
    }

    private async Task SaveDetailsAsync(
        int masterRecId,
        string domain,
        string receiver,
        string rctType,
        List<PurchaseReturnDetailInput> entries,
        string account,
        DateTime now)
    {
        var dbRows = await _detailRep.GetListAsync(d =>
            d.PurOrdRctRecID == masterRecId && d.RctType == rctType);
        var dbById = dbRows.ToDictionary(d => d.RecID);
        var inputIds = new HashSet<int>(entries.Where(e => e.RecID is > 0).Select(e => e.RecID!.Value));

        for (var i = 0; i < entries.Count; i++)
        {
            var e = entries[i];
            var lineNo = e.Line > 0 ? e.Line : (short)(i + 1);

            if (string.IsNullOrWhiteSpace(e.ItemNum))
                throw Oops.Oh($"第{i + 1}行：请填写物料");

            var loc = string.IsNullOrWhiteSpace(e.Location) ? "00" : e.Location.Trim();

            if (e.RecID is > 0 && dbById.TryGetValue(e.RecID.Value, out var row))
            {
                ApplyDetailInput(row, e, domain, receiver, masterRecId, account, now, lineNo, loc);
                row.UpdateUser = account;
                row.UpdateTime = now;
                await _detailRep.UpdateAsync(row);
            }
            else
            {
                var detailNew = new PurOrdRctDetail();
                ApplyDetailInput(detailNew, e, domain, receiver, masterRecId, account, now, lineNo, loc);
                detailNew.CreateUser = account;
                detailNew.CreateTime = now;
                detailNew.UpdateUser = account;
                detailNew.UpdateTime = now;
                await _detailRep.InsertAsync(detailNew);
            }
        }

        foreach (var del in dbRows.Where(d => !inputIds.Contains(d.RecID)))
            await _detailRep.DeleteAsync(d => d.RecID == del.RecID);
    }

    private static void ApplyDetailInput(
        PurOrdRctDetail row,
        PurchaseReturnDetailInput e,
        string domain,
        string receiver,
        int masterRecId,
        string account,
        DateTime now,
        short lineNo,
        string location)
    {
        row.Domain = domain;
        row.RctType = "pt";
        row.Receiver = receiver;
        row.PurOrdRctRecID = masterRecId;
        row.Line = lineNo;
        row.Dimension1 = "";
        row.Dimension2 = "";
        row.ItemNum = e.ItemNum?.Trim();
        row.OrdNbr = string.IsNullOrWhiteSpace(e.OrdNbr) ? "" : e.OrdNbr.Trim();
        row.OrdLine = (short)e.OrdLine;
        row.RctDate = ParseDate(e.RctDate);
        row.UM = e.UM;
        row.QtyOrded = e.QtyOrded;
        row.QtyReceived = e.QtyReceived;
        row.RctQty = e.RctQty;
        row.LotSerial = e.LotSerial;
        row.Location = location;
        row.QCDescr = e.QCDescr;
        row.Remark = e.Remark;
        row.Typed = string.IsNullOrWhiteSpace(e.Typed) ? "C" : e.Typed.Trim();
        row.TaxIn = e.TaxIn;
        row.TaxClass = e.TaxClass;
        row.Curr = e.Curr;
        row.Potype = string.IsNullOrWhiteSpace(e.Potype) ? "P" : e.Potype.Trim();
        row.IsActive = true;
        row.IsConfirm = e.IsConfirm;
        row.UMConversion = e.UMConversion;
        row.POCost = e.POCost;
        row.RctNbr = e.RctNbr;
        row.RctLine = (short)e.RctLine;
        row.CustPO = e.CustPO;
        row.BlanketLine = e.BlanketLine;
        row.QtyReturn = e.QtyReturn;
        row.Supp = e.Supp;
    }

    [DisplayName("删除采购退货单(软删除)")]
    [HttpPost("purchase-return/delete/{id:int}")]
    public async Task<object> SoftDelete(int id)
    {
        var m = await _masterRep.GetFirstAsync(x => x.RecID == id && x.RctType == "pt")
                ?? throw Oops.Oh("单据不存在");
        if (!m.IsPlan)
            throw Oops.Oh("该单据不允许删除");

        m.IsActive = false;
        m.UpdateUser = _userManager.Account ?? "system";
        m.UpdateTime = DateTime.Now;
        await _masterRep.UpdateAsync(m);

        await _db.Ado.ExecuteCommandAsync(
            "UPDATE PurOrdRctDetail SET IsActive=0, UpdateUser=@U, UpdateTime=@T WHERE PurOrdRctRecID=@Id AND RctType='pt'",
            new SugarParameter("@U", m.UpdateUser),
            new SugarParameter("@T", m.UpdateTime),
            new SugarParameter("@Id", id));

        return new { message = "已删除" };
    }

    private async Task<string> ResolveRcDomainAsync(string rcReceiver)
    {
        var d = await _db.Ado.GetStringAsync(
            "SELECT Domain FROM PurOrdRctMaster WHERE RctType='rc' AND Receiver=@R LIMIT 1",
            new SugarParameter("@R", rcReceiver));
        return d;
    }

    private async Task<string> AllocReceiverAsync()
    {
        for (var i = 0; i < 20; i++)
        {
            var suffix = (DateTime.UtcNow.Ticks ^ Random.Shared.Next()).ToString("x");
            if (suffix.Length > 12)
                suffix = suffix[..12];
            var cand = $"PT{DateTime.Now:yyMMdd}{suffix}";
            if (cand.Length > 24)
                cand = cand[..24];
            var n = await _db.Ado.GetIntAsync(
                "SELECT COUNT(1) FROM PurOrdRctMaster WHERE Receiver=@R",
                new SugarParameter("@R", cand));
            if (n == 0)
                return cand;
        }

        return $"PT{Guid.NewGuid():N}"[..24];
    }

    private static DateTime? ParseDate(string raw)
    {
        if (string.IsNullOrWhiteSpace(raw)) return null;
        return DateTime.TryParse(raw.Trim(), out var d) ? d.Date : null;
    }
}