namespace Admin.NET.Plugin.AiDOP.WorkOrder;

/// <summary>从资源检查结果同步工单物料明细（WorkOrdDetail）。</summary>
public class WorkOrderMaterialDetailSyncService : ITransient
{
    private readonly ISqlSugarClient _db;

    public WorkOrderMaterialDetailSyncService(ISqlSugarClient db)
    {
        _db = db;
    }

    public async Task<int> EnsureFromResourceCheckAsync(long tenantId, string workOrd, string account)
    {
        var wo = await LoadWorkOrderAsync(tenantId, workOrd);
        if (wo is null)
            return 0;

        var components = await LoadResourceCheckComponentsAsync(tenantId, workOrd);
        if (components.Count == 0)
            return 0;

        var now = DateTime.Now;

        // 加载已有明细，按 (ItemNum) 匹配进行 upsert
        var existingDetails = await _db.Ado.SqlQueryAsync<ExistingDetailRow>(
            """
            SELECT RecID, ItemNum, QtyRequired
            FROM WorkOrdDetail
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd AND IFNULL(IsActive, 0) = 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
        var existingMap = existingDetails.ToDictionary(x => x.ItemNum, x => x);

        var inserted = 0;
        var line = existingDetails.Count + 1;

        foreach (var c in components)
        {
            if (existingMap.TryGetValue(c.ItemNumber, out var ex))
            {
                // 已有明细：更新 QtyRequired
                if (ex.QtyRequired != c.QtyRequired)
                {
                    await _db.Ado.ExecuteCommandAsync(
                        """
                        UPDATE WorkOrdDetail
                        SET QtyRequired = @QtyRequired, UpdateUser = @User, UpdateTime = @Now
                        WHERE RecID = @RecID
                        """,
                        new SugarParameter("@QtyRequired", c.QtyRequired),
                        new SugarParameter("@User", account),
                        new SugarParameter("@Now", now),
                        new SugarParameter("@RecID", ex.RecID));
                }
                existingMap.Remove(c.ItemNumber);
            }
            else
            {
                // 新增明细
                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO WorkOrdDetail (
                        WorkOrdMasterRecID, `Domain`, WorkOrd, LineNum, ItemNum, Op,
                        Location, QtyRequired, QtyPosted, UM, IsActive, IsConfirm,
                        CreateUser, CreateTime, UpdateUser, UpdateTime, tenant_id
                    ) VALUES (
                        @MasterRecId, @Domain, @WorkOrd, @LineNum, @ItemNum, @Op,
                        @Location, @QtyRequired, 0, @UM, 1, 0,
                        @User, @Now, @User, @Now, @TenantId
                    )
                    """,
                    new SugarParameter("@MasterRecId", wo.RecId),
                    new SugarParameter("@Domain", wo.Domain ?? tenantId.ToString()),
                    new SugarParameter("@WorkOrd", workOrd),
                    new SugarParameter("@LineNum", line),
                    new SugarParameter("@ItemNum", c.ItemNumber),
                    new SugarParameter("@Op", c.Op),
                    new SugarParameter("@Location", c.Location ?? (object)DBNull.Value),
                    new SugarParameter("@QtyRequired", c.QtyRequired),
                    new SugarParameter("@UM", c.Unit ?? (object)DBNull.Value),
                    new SugarParameter("@User", account),
                    new SugarParameter("@Now", now),
                    new SugarParameter("@TenantId", tenantId));
                inserted++;
                line++;
            }
        }

        // 删除不再有缺料的旧明细
        foreach (var stale in existingMap.Values)
        {
            await _db.Ado.ExecuteCommandAsync(
                """
                UPDATE WorkOrdDetail
                SET IsActive = 0, UpdateUser = @User, UpdateTime = @Now
                WHERE RecID = @RecID
                """,
                new SugarParameter("@User", account),
                new SugarParameter("@Now", now),
                new SugarParameter("@RecID", stale.RecID));
        }

        return components.Count;
    }

    private async Task<WorkOrderRow?> LoadWorkOrderAsync(long tenantId, string workOrd)
    {
        var rows = await _db.Ado.SqlQueryAsync<WorkOrderRow>(
            """
            SELECT RecID AS RecId, `Domain`, ItemNum
            FROM WorkOrdMaster
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
        return rows.FirstOrDefault();
    }

    private async Task<List<ComponentRow>> LoadResourceCheckComponentsAsync(long tenantId, string workOrd)
    {
        return await _db.Ado.SqlQueryAsync<ComponentRow>(
            """
            SELECT
                bce.item_number AS ItemNumber,
                SUM(IFNULL(bce.needCount, 0)) AS QtyRequired,
                IFNULL(MAX(im.Um), '') AS Unit,
                IFNULL(MAX(im.Location), '') AS Location,
                IFNULL(MAX(bce.Op), 0) AS Op
            FROM b_examine_result ber
            INNER JOIN b_bom_child_examine bce ON ber.Id = bce.examine_id AND bce.is_use = 1
            LEFT JOIN ItemMaster im ON bce.item_number = im.ItemNum
            WHERE ber.tenant_id = @TenantId
              AND ber.IsDeleted = 0
              AND ber.morder_no = @WorkOrd
              AND ber.Id = (
                  SELECT br.Id FROM b_examine_result br
                  WHERE br.tenant_id = @TenantId AND br.morder_no = @WorkOrd AND br.IsDeleted = 0
                  ORDER BY br.create_time DESC LIMIT 1
              )
              AND IFNULL(bce.num, '') <> '1'
              AND IFNULL(bce.backflush, 0) = 0
              AND IFNULL(bce.erp_cls, 3) <> 4
            GROUP BY bce.item_number
            HAVING SUM(IFNULL(bce.needCount, 0)) > 0
            ORDER BY MIN(bce.id)
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
    }

    private sealed class WorkOrderRow
    {
        public long RecId { get; set; }
        public string? Domain { get; set; }
        public string? ItemNum { get; set; }
    }

    private sealed class ComponentRow
    {
        public string ItemNumber { get; set; } = string.Empty;
        public decimal QtyRequired { get; set; }
        public string? Unit { get; set; }
        public string? Location { get; set; }
        public int Op { get; set; }
    }

    private sealed class ExistingDetailRow
    {
        public long RecID { get; set; }
        public string ItemNum { get; set; } = string.Empty;
        public decimal QtyRequired { get; set; }
    }
}