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 existing = await _db.Ado.GetIntAsync(
            """
            SELECT COUNT(*) FROM WorkOrdDetail
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd AND IFNULL(IsActive, 0) = 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        if (existing > 0)
            return 0;

        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;
        var line = 1;
        foreach (var c in components)
        {
            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));
            line++;
        }

        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,
                CASE
                    WHEN IFNULL(bce.use_qty, 0) > 0 THEN bce.use_qty
                    ELSE IFNULL(bce.needCount, 0)
                END AS QtyRequired,
                IFNULL(bce.unit, im.Um) AS Unit,
                IFNULL(im.Location, '') AS Location,
                IFNULL(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
              AND (
                  IFNULL(bce.use_qty, 0) > 0
                  OR IFNULL(bce.needCount, 0) > 0
              )
            ORDER BY 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; }
    }
}