using Admin.NET.Plugin.AiDOP.Supply;
using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.WorkOrder;

/// <summary>工单下达领料单生成（NbrMaster / NbrDetail，Type=SM）。</summary>
public class WorkOrderPickBillService : ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly NumberRuleService _numberRule;
    private readonly WorkOrderMaterialDetailSyncService _detailSync;

    public WorkOrderPickBillService(
        ISqlSugarClient db,
        NumberRuleService numberRule,
        WorkOrderMaterialDetailSyncService detailSync)
    {
        _db = db;
        _numberRule = numberRule;
        _detailSync = detailSync;
    }

    public async Task<PickBillResult> CreateForWorkOrderAsync(long tenantId, string workOrd, string account)
    {
        workOrd = workOrd.Trim();
        var existing = await FindExistingPickBillAsync(tenantId, workOrd);
        if (!string.IsNullOrWhiteSpace(existing))
            return new PickBillResult { Nbr = existing, Created = false, Message = "领料单已存在" };

        var wo = await LoadWorkOrderAsync(tenantId, workOrd)
            ?? throw Oops.Oh($"工单 {workOrd} 不存在");

        await _detailSync.EnsureFromResourceCheckAsync(tenantId, workOrd, account);

        var details = await LoadPickDetailsAsync(tenantId, workOrd);
        if (details.Count == 0)
            throw Oops.Oh($"工单 {workOrd} 无物料明细，请先完成订单评审资源检查");

        var domain = string.IsNullOrWhiteSpace(wo.Domain) ? tenantId.ToString() : wo.Domain.Trim();
        var lineTo = await ResolveLineSideLocationAsync(tenantId, wo.ItemNum);
        var now = DateTime.Now;
        var effDate = wo.OrdDate?.Date ?? now.Date;
        var pickDate = effDate < now.Date ? now.Date : effDate;

        string nbrNo;
        try
        {
            var numbers = await _numberRule.NextBatchInCurrentTransactionAsync("SM", domain, 1, account);
            nbrNo = numbers.FirstOrDefault() ?? throw Oops.Oh("领料单号生成失败");
        }
        catch
        {
            nbrNo = "SM" + now.ToString("yyyyMMddHHmmss") + YitIdHelper.NextId().ToString()[^5..];
        }

        var masterRecId = await NextRecIdAsync("NbrMaster");
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO NbrMaster (
                RecID, `Domain`, Type, Nbr, Status, Remark, Date, EffDate,
                WorkOrd, QtyOrd, QtyRec, ProdLine, Department, Name,
                TransType, IsActive, IsChanged, IsConfirm, BusinessID,
                CreateUser, CreateTime, UpdateUser, UpdateTime, tenant_id
            ) VALUES (
                @RecId, @Domain, 'SM', @Nbr, '', @Remark, @PickDate, @EffDate,
                @WorkOrd, @QtyOrd, 0, @ProdLine, '101', @Name,
                '', 1, 1, 0, 0,
                @User, @Now, @User, @Now, @TenantId
            )
            """,
            new SugarParameter("@RecId", masterRecId),
            new SugarParameter("@Domain", domain.Length > 8 ? domain[..8] : domain),
            new SugarParameter("@Nbr", nbrNo),
            new SugarParameter("@Remark", "下达自动领料"),
            new SugarParameter("@PickDate", pickDate),
            new SugarParameter("@EffDate", effDate),
            new SugarParameter("@WorkOrd", workOrd),
            new SugarParameter("@QtyOrd", wo.QtyOrded ?? 0),
            new SugarParameter("@ProdLine", wo.ProdLine ?? (object)DBNull.Value),
            new SugarParameter("@Name", account.Length > 12 ? account[..12] : account),
            new SugarParameter("@User", account.Length > 24 ? account[..24] : account),
            new SugarParameter("@Now", now),
            new SugarParameter("@TenantId", tenantId));

        short line = 1;
        foreach (var d in details)
        {
            var detailRecId = await NextRecIdAsync("NbrDetail");
            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO NbrDetail (
                    RecID, `Domain`, Type, Nbr, Line, ItemNum, Dimension1, Dimension2,
                    LocationFrom, LocationTo, QtyFrom, QtyTo, QtyOrd, QtyRec,
                    CurrQtyOpened, UM, WorkOrd, ItemName, Status,
                    IsActive, IsConfirm, IsChanged, BusinessID, NbrRecID,
                    CreateUser, CreateTime, UpdateUser, UpdateTime, tenant_id
                ) VALUES (
                    @RecId, @Domain, 'SM', @Nbr, @Line, @ItemNum, '', '',
                    @LocationFrom, @LocationTo, 0, 0, @QtyOrd, 0,
                    @CurrQtyOpened, @UM, @WorkOrd, @ItemName, '',
                    1, 0, 1, 0, @NbrRecId,
                    @User, @Now, @User, @Now, @TenantId
                )
                """,
                new SugarParameter("@RecId", detailRecId),
                new SugarParameter("@Domain", domain.Length > 8 ? domain[..8] : domain),
                new SugarParameter("@Nbr", nbrNo),
                new SugarParameter("@Line", line),
                new SugarParameter("@ItemNum", d.ItemNum),
                new SugarParameter("@LocationFrom", d.LocationFrom ?? (object)DBNull.Value),
                new SugarParameter("@LocationTo", lineTo ?? (object)DBNull.Value),
                new SugarParameter("@QtyOrd", d.QtyRequired),
                new SugarParameter("@CurrQtyOpened", d.QtyRequired),
                new SugarParameter("@UM", d.Unit ?? (object)DBNull.Value),
                new SugarParameter("@WorkOrd", workOrd),
                new SugarParameter("@ItemName", d.ItemName ?? (object)DBNull.Value),
                new SugarParameter("@NbrRecId", masterRecId),
                new SugarParameter("@User", account.Length > 24 ? account[..24] : account),
                new SugarParameter("@Now", now),
                new SugarParameter("@TenantId", tenantId));
            line++;
        }

        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE mes_morder
            SET morder_state = '下达',
                MaterialSituation = CASE
                    WHEN MaterialSituation = '缺料' THEN '未备料'
                    ELSE IFNULL(MaterialSituation, '齐套')
                END,
                update_by_name = @User,
                update_time = @Now
            WHERE tenant_id = @TenantId AND morder_no = @WorkOrd AND IsDeleted = 0
            """,
            new SugarParameter("@User", account),
            new SugarParameter("@Now", now),
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));

        return new PickBillResult
        {
            Nbr = nbrNo,
            Created = true,
            LineCount = details.Count,
            Message = "领料单已生成"
        };
    }

    private async Task<string?> FindExistingPickBillAsync(long tenantId, string workOrd)
    {
        return await _db.Ado.GetStringAsync(
            """
            SELECT Nbr FROM NbrMaster
            WHERE tenant_id = @TenantId AND WorkOrd = @WorkOrd AND Type = 'SM'
              AND IFNULL(TransType, '') = ''
            ORDER BY RecID DESC LIMIT 1
            """,
            new List<SugarParameter>
            {
                new("@TenantId", tenantId),
                new("@WorkOrd", workOrd)
            });
    }

    private async Task<WorkOrderPickRow?> LoadWorkOrderAsync(long tenantId, string workOrd)
    {
        var rows = await _db.Ado.SqlQueryAsync<WorkOrderPickRow>(
            """
            SELECT
                w.RecID AS RecId,
                w.`Domain`,
                w.ItemNum,
                w.QtyOrded,
                w.OrdDate,
                (
                    SELECT LEFT(IFNULL(r.ProdLine, ''), 8)
                    FROM WorkOrdRouting r
                    WHERE r.WorkOrd = w.WorkOrd AND r.tenant_id = w.tenant_id AND IFNULL(r.IsActive, 0) = 1
                    ORDER BY (r.OP + 0) LIMIT 1
                ) AS ProdLine
            FROM WorkOrdMaster w
            WHERE w.tenant_id = @TenantId AND w.WorkOrd = @WorkOrd
            LIMIT 1
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
        return rows.FirstOrDefault();
    }

    private async Task<List<PickDetailRow>> LoadPickDetailsAsync(long tenantId, string workOrd)
    {
        return await _db.Ado.SqlQueryAsync<PickDetailRow>(
            """
            SELECT
                d.ItemNum,
                SUM(d.QtyRequired) AS QtyRequired,
                MAX(IFNULL(d.Location, im.Location)) AS LocationFrom,
                MAX(IFNULL(d.UM, im.Um)) AS Unit,
                MAX(im.Descr) AS ItemName
            FROM WorkOrdDetail d
            LEFT JOIN ItemMaster im ON d.ItemNum = im.ItemNum
            WHERE d.tenant_id = @TenantId AND d.WorkOrd = @WorkOrd AND IFNULL(d.IsActive, 0) = 1
            GROUP BY d.ItemNum
            HAVING SUM(d.QtyRequired) > 0
            ORDER BY d.ItemNum
            """,
            new SugarParameter("@TenantId", tenantId),
            new SugarParameter("@WorkOrd", workOrd));
    }

    private async Task<string?> ResolveLineSideLocationAsync(long tenantId, string? itemNum)
    {
        if (string.IsNullOrWhiteSpace(itemNum))
            return null;

        var loc = await _db.Ado.GetStringAsync(
            """
            SELECT LEFT(IFNULL(lm.Location, lm.PickingLocation), 8)
            FROM ProdLineDetail pld
            INNER JOIN LineMaster lm ON pld.Line = lm.Line AND lm.tenant_id = pld.tenant_id
            WHERE pld.Part = @ItemNum AND pld.tenant_id = @TenantId AND IFNULL(pld.IsActive, 1) = 1
            ORDER BY IFNULL(pld.Sequence, 0)
            LIMIT 1
            """,
            new List<SugarParameter>
            {
                new("@ItemNum", itemNum.Trim()),
                new("@TenantId", tenantId)
            });

        return string.IsNullOrWhiteSpace(loc) ? null : loc;
    }

    private async Task<int> NextRecIdAsync(string table)
    {
        return await _db.Ado.GetIntAsync($"SELECT IFNULL(MAX(RecID), 0) + 1 FROM {table}");
    }

    public sealed class PickBillResult
    {
        public string Nbr { get; set; } = string.Empty;
        public bool Created { get; set; }
        public int LineCount { get; set; }
        public string Message { get; set; } = string.Empty;
    }

    private sealed class WorkOrderPickRow
    {
        public long RecId { get; set; }
        public string? Domain { get; set; }
        public string? ItemNum { get; set; }
        public decimal? QtyOrded { get; set; }
        public DateTime? OrdDate { get; set; }
        public string? ProdLine { get; set; }
    }

    private sealed class PickDetailRow
    {
        public string ItemNum { get; set; } = string.Empty;
        public decimal QtyRequired { get; set; }
        public string? LocationFrom { get; set; }
        public string? Unit { get; set; }
        public string? ItemName { get; set; }
    }
}