using Admin.NET.Plugin.AiDOP.MaterialWarehouse.Dto;

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

/// <summary>
/// S5 库存查询 只读 list 服务。
///
/// 数据源：aidopdev.LocationMaster ⋈ LocationDetail（现存量），忠实复刻旧系统存储过程
/// pr_WMS_GetMissedLocationQty 主口径：Domain 过滤 + (AvailStatusQty+Assay+FreezeQty)&gt;0，
/// 类型 LocType 由 LocationMaster.Typed 派生(VMI / Supp→O库)，join ItemMaster(名称/单位)、
/// 富集 MissedPrint(货架/版本/供应商，Status not in U/P/C、Location 非空、Qty&gt;0，按库位·物料·批次聚合)。
/// Domain 由 GeneralizedCodeMaster(SystemConfig/CompanyCode) 解析，默认 8010。
///
/// 忠实度说明：存储过程「多货架行拆分 + 按 MissedPrint 状态重算量」(原过程第 99-121 行) 仅在同一
/// 库位·物料·批次存在多货架时生效；当前 MissedPrint 为 0 行，其效果为零且无数据可验，本服务不复刻该拆分
/// （LocCount≤1 场景即 base + 富集，与现数据一致）。MissedPrint 回补后如需多货架拆分，另起批次复验。
///
/// 本服务仅 SELECT：无新增/编辑/删除/库存事务/状态流转。
/// 安全：LocationMaster / GeneralizedCodeMaster 含 tenant_id，按当前租户强制过滤；MissedPrint 无 tenant 列，
/// 由已按租户约束的父键（tenant-safe 的 LocationMaster/LocationDetail 基础层）经关联键约束，不单独写 MissedPrint.tenant_id。
/// 租户只来自认证后可信 JWT（严格解析），忽略前端 tenantId，杜绝匿名/跨租户可见。
/// </summary>
[ApiDescriptionSettings(Order = 300, Description = "库存查询")]
[Route("api/S5StockQuery")]
[NonUnify]
public class StockQueryService : IDynamicApiController, ITransient
{
    /// <summary>主/系统租户哨兵：超管未选择目标租户时其 JWT TenantId 即此值，拒绝作为业务租户。</summary>
    private const long MainTenantId = 1300000000001L;

    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

    public StockQueryService(ISqlSugarClient db, UserManager userManager)
    {
        _db = db;
        _userManager = userManager;
    }

    /// <summary>严格可信租户解析：只来自认证后 JWT；无 Token(&lt;=0) 或超管未选主租户 → 拒绝；不读前端 tenantId、无默认回退。</summary>
    private long ResolveTenantOrThrow()
    {
        var tid = _userManager.TenantId;
        if (tid <= 0)
            throw Oops.Oh("无法确定当前租户，请重新登录或选择目标租户");
        if (_userManager.SuperAdmin && tid == MainTenantId)
            throw Oops.Oh("超级管理员操作前必须选择目标租户");
        return tid;
    }

    private sealed class GrandTotals
    {
        public decimal QtyOnHand { get; set; }
        public decimal Unrestricted { get; set; }
        public decimal Qc { get; set; }
        public decimal Frozen { get; set; }
    }

    /// <summary>解析公司域（GeneralizedCodeMaster SystemConfig/CompanyCode，默认 8010）。</summary>
    private async Task<string> ResolveDomainAsync()
    {
        var list = await _db.Ado.SqlQueryAsync<string>(
            "SELECT Domain FROM GeneralizedCodeMaster WHERE FldName='SystemConfig' AND Val='CompanyCode' ORDER BY RecID LIMIT 1");
        var domain = list.FirstOrDefault();
        return string.IsNullOrWhiteSpace(domain) ? "8010" : domain;
    }

    // 派生 LocType 表达式（与存储过程一致）
    private const string LocTypeExpr = "CASE WHEN t.typed='VMI' THEN 'VMI' WHEN t.typed='Supp' THEN 'O库' ELSE '' END";

    /// <summary>
    /// 库存查询列表（只读分页查询）。
    /// </summary>
    [DisplayName("库存查询列表")]
    [HttpGet("list")]
    public async Task<object> GetList([FromQuery] StockQueryListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var tenantId = ResolveTenantOrThrow();
        var domain = await ResolveDomainAsync();

        // 强制租户过滤：LocationMaster(t) 含 tenant_id，基础层按可信 JWT 租户过滤；count/grand/list 共用同一基础层同 tid。
        // LocationDetail(d)/ItemMaster(i)/MissedPrint(mp) 均由已按租户约束的 t 经关联键约束（tenant-safe 父键）。
        var where = new List<string>
        {
            "t.Domain = @Domain",
            "t.tenant_id = @TenantId",
            "(IFNULL(d.AvailStatusQty,0)+IFNULL(d.Assay,0)+IFNULL(d.FreezeQty,0)) > 0",
        };
        var pars = new List<SugarParameter> { new("@Domain", domain), new("@TenantId", tenantId) };

        if (!string.IsNullOrWhiteSpace(input.Type))
        {
            where.Add($"({LocTypeExpr}) = @Type");
            pars.Add(new SugarParameter("@Type", input.Type.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.Location))
        {
            where.Add("d.Location = @Location");
            pars.Add(new SugarParameter("@Location", input.Location.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.MaterialCode))
        {
            where.Add("d.ItemNum LIKE @MaterialCode");
            pars.Add(new SugarParameter("@MaterialCode", $"%{input.MaterialCode.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.BatchNo))
        {
            where.Add("d.LotSerial LIKE @BatchNo");
            pars.Add(new SugarParameter("@BatchNo", $"%{input.BatchNo.Trim()}%"));
        }
        if (input.StockGt.HasValue)
        {
            where.Add("d.QtyOnHand > @StockGt");
            pars.Add(new SugarParameter("@StockGt", input.StockGt.Value));
        }
        if (input.UnrestrictedGt.HasValue)
        {
            where.Add("d.AvailStatusQty > @UnrestrictedGt");
            pars.Add(new SugarParameter("@UnrestrictedGt", input.UnrestrictedGt.Value));
        }
        if (input.QcStockGt.HasValue)
        {
            where.Add("d.Assay > @QcStockGt");
            pars.Add(new SugarParameter("@QcStockGt", input.QcStockGt.Value));
        }
        if (input.FrozenGt.HasValue)
        {
            where.Add("d.FreezeQty > @FrozenGt");
            pars.Add(new SugarParameter("@FrozenGt", input.FrozenGt.Value));
        }

        var whereSql = string.Join(" AND ", where);

        // 复刻存储过程 @tbLocDet 基础层（现存量，含派生 LocType 与库位名）
        var baseQuery =
            $"""
            SELECT d.Location, d.ItemNum, IFNULL(d.LotSerial,'') AS LotSerial,
                   d.QtyOnHand, d.AvailStatusQty, d.Assay, d.FreezeQty,
                   {LocTypeExpr} AS LocType, t.descr AS LocationName
            FROM LocationMaster t
            INNER JOIN LocationDetail d ON t.Domain = d.Domain AND t.location = d.Location
            WHERE {whereSql}
            """;

        // MissedPrint 富集（货架/版本/供应商），按库位·物料·批次聚合，1:1
        const string mpQuery =
            """
            SELECT Location, ItemNum, IFNULL(LotSerial,'') AS LotSerial,
                   MAX(Shelf) AS Shelf, MAX(LevelChar) AS LevelChar, MAX(Supply) AS Supply
            FROM MissedPrint
            WHERE Domain = @Domain AND IFNULL(Status,'') NOT IN ('U','P','C') AND IFNULL(Location,'') <> '' AND Qty > 0
            GROUP BY Location, ItemNum, IFNULL(LotSerial,'')
            """;

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM ({baseQuery}) n", pars);

        var grand = (await _db.Ado.SqlQueryAsync<GrandTotals>(
            $"""
            SELECT IFNULL(SUM(n.QtyOnHand),0) AS QtyOnHand, IFNULL(SUM(n.AvailStatusQty),0) AS Unrestricted,
                   IFNULL(SUM(n.Assay),0) AS Qc, IFNULL(SUM(n.FreezeQty),0) AS Frozen
            FROM ({baseQuery}) n
            """, pars)).FirstOrDefault() ?? new GrandTotals();

        var list = await _db.Ado.SqlQueryAsync<StockQueryListRow>(
            $"""
            SELECT
                n.LocType         AS Type,
                n.Location        AS Location,
                n.LocationName    AS LocationName,
                n.ItemNum         AS MaterialCode,
                i.Descr           AS MaterialName,
                n.LotSerial       AS BatchNo,
                IFNULL(mp.Shelf,'')     AS Shelf,
                IFNULL(mp.LevelChar,'') AS Version,
                n.AvailStatusQty  AS UnrestrictedStock,
                n.Assay           AS QcStock,
                n.FreezeQty       AS FrozenStock,
                n.QtyOnHand       AS QtyOnHand,
                i.UM              AS Unit,
                IFNULL(mp.Supply,'')    AS Supplier
            FROM ({baseQuery}) n
            LEFT JOIN ItemMaster i ON i.Domain = @Domain AND n.ItemNum = i.ItemNum
            LEFT JOIN ({mpQuery}) mp ON mp.Location = n.Location AND mp.ItemNum = n.ItemNum AND mp.LotSerial = n.LotSerial
            ORDER BY n.Location, n.ItemNum, n.LotSerial
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

        return new
        {
            total,
            page,
            pageSize,
            list,
            grandTotalQtyOnHand = grand.QtyOnHand,
            grandUnrestricted = grand.Unrestricted,
            grandQc = grand.Qc,
            grandFrozen = grand.Frozen,
        };
    }

    /// <summary>
    /// 类型下拉选项（派生 LocType：VMI / O库，只读）。
    /// </summary>
    [DisplayName("库存查询类型选项")]
    [HttpGet("type-options")]
    public async Task<object> GetTypeOptions()
    {
        var tenantId = ResolveTenantOrThrow();
        var domain = await ResolveDomainAsync();
        // LocationMaster 含 tenant_id（797:80 / 824:34），强制按可信租户过滤。
        var list = await _db.Ado.SqlQueryAsync<StockQueryTypeOption>(
            $"""
            SELECT DISTINCT {LocTypeExpr.Replace("t.typed", "typed")} AS Val
            FROM LocationMaster
            WHERE Domain = @Domain AND tenant_id = @TenantId AND {LocTypeExpr.Replace("t.typed", "typed")} <> ''
            ORDER BY Val
            """,
            new List<SugarParameter> { new("@Domain", domain), new("@TenantId", tenantId) });
        return list;
    }

    /// <summary>
    /// 库位下拉选项（LocationMaster.Location + Descr，只读）。
    /// </summary>
    [DisplayName("库存查询库位选项")]
    [HttpGet("location-options")]
    public async Task<object> GetLocationOptions()
    {
        var tenantId = ResolveTenantOrThrow();
        var domain = await ResolveDomainAsync();
        // LocationMaster 含 tenant_id（797:80 / 824:34），强制按可信租户过滤。
        var list = await _db.Ado.SqlQueryAsync<StockQueryLocationOption>(
            """
            SELECT location AS Val, descr AS Label
            FROM LocationMaster
            WHERE Domain = @Domain AND tenant_id = @TenantId AND IFNULL(location,'') <> ''
            ORDER BY location
            """,
            new List<SugarParameter> { new("@Domain", domain), new("@TenantId", tenantId) });
        return list;
    }
}