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

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

/// <summary>
/// S5 盘点标签确认 只读 list 服务。
///
/// 数据源：aidopdev.MissedPrint(IsTag=1) LEFT JOIN ItemMaster（物料名称/规格/单位）。
/// 参考本仓既有 MissedPrint 直读只读投影先例：LabelQueryService / StockQueryService / PendingInspectionService。
///
/// 本服务仅 SELECT：无新增/编辑/删除/确认写回/批量确认/反确认/库存事务/状态流转；扁平列表，无 detail。
/// 派生列：diffQty=CompQty-Qty；status(Status='Q'→冻结)；stocktakeResult(CompQty&lt;&gt;Qty→有差异/正常)。
///
/// 【认证】类级已去 [AllowAnonymous]：原先该接口可被**完全未认证**调用
/// （实测匿名 GET —— 无 Authorization 头、credentials:'omit' 连 cookie 也不发 —— 返回 HTTP 200）。
/// 当时 IsTag=1 恰好 0 行故未造成实际泄漏，但端点是公开的，一旦出现盘点标签数据即形成匿名读取。
/// 现收敛为需认证访问。
///
/// 【仍未解决 · DATA-MODEL DEBT】租户边界。查询仍是 WHERE IsTag=1，无 Tenant/Domain 条件：
/// MissedPrint 的 tenant_id 列存在但实测 152 行全为 NULL，且 150 行 Location 为空，
/// 既无法按 tenant_id 过滤（会把页面整体查空），也无法拿 Location 当租户边界（会误删绝大多数行）。
/// 本批只关闭匿名访问，**不代表 tenant isolation 已 CLOSED**。
/// </summary>
[ApiDescriptionSettings(Order = 308, Description = "盘点标签确认")]
[Route("api/S5StocktakeLabelConfirm")]
[NonUnify]
public class StocktakeLabelConfirmService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;

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

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

        var where = new List<string> { "m.IsTag = 1" };
        var pars = new List<SugarParameter>();

        if (!string.IsNullOrWhiteSpace(input.Location))
        {
            where.Add("m.Location LIKE @Location");
            pars.Add(new SugarParameter("@Location", $"%{input.Location.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Shelf))
        {
            where.Add("m.Shelf LIKE @Shelf");
            pars.Add(new SugarParameter("@Shelf", $"%{input.Shelf.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("m.ItemNum LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.LotSerial))
        {
            where.Add("m.LotSerial LIKE @LotSerial");
            pars.Add(new SugarParameter("@LotSerial", $"%{input.LotSerial.Trim()}%"));
        }
        if (string.Equals(input.InvStatus, "有差异", StringComparison.Ordinal))
        {
            where.Add("IFNULL(m.CompQty,0) <> IFNULL(m.Qty,0)");
        }
        else if (string.Equals(input.InvStatus, "正常", StringComparison.Ordinal))
        {
            where.Add("IFNULL(m.CompQty,0) = IFNULL(m.Qty,0)");
        }

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

        var total = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(1) FROM MissedPrint m WHERE {whereSql}", pars);

        var list = await _db.Ado.SqlQueryAsync<StocktakeLabelConfirmListRow>(
            $"""
            SELECT
                m.RecID       AS Id,
                m.ItemNum     AS MaterialCode,
                i.Descr       AS MaterialName,
                i.Descr1      AS Spec,
                m.Location    AS Location,
                m.Shelf       AS Shelf,
                m.FirmString2 AS ActualLocation,
                m.FirmString4 AS ActualShelf,
                m.LotSerial   AS BatchNo,
                i.UM          AS Unit,
                m.Qty         AS LabelQty,
                m.CompQty     AS ActualQty,
                IFNULL(m.CompQty,0) - IFNULL(m.Qty,0) AS DiffQty,
                CASE WHEN m.Status='Q' THEN '冻结' ELSE IFNULL(m.Status,'') END AS Status,
                CASE WHEN IFNULL(m.CompQty,0) <> IFNULL(m.Qty,0) THEN '有差异' ELSE '正常' END AS StocktakeResult,
                m.UpdateUser  AS StocktakePerson,
                m.UpdateTime  AS StocktakeTime,
                m.BarCode     AS BarCode
            FROM MissedPrint m
            LEFT JOIN ItemMaster i ON i.Domain = m.Domain AND i.ItemNum = m.ItemNum
            WHERE {whereSql}
            ORDER BY {BuildOrderBy(input.OrderBy, input.OrderDir)}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    /// <summary>
    /// 库位下拉选项（MissedPrint IsTag=1 的 Location distinct，只读）。
    /// </summary>
    [DisplayName("盘点标签确认库位选项")]
    [HttpGet("locations")]
    public async Task<object> GetLocations()
    {
        var list = await _db.Ado.SqlQueryAsync<StocktakeLabelConfirmLocationOption>(
            """
            SELECT DISTINCT Location AS Val
            FROM MissedPrint
            WHERE IsTag = 1 AND Location IS NOT NULL AND Location <> ''
            ORDER BY Location
            """);
        return list;
    }

    /// <summary>
    /// 排序白名单：仅允许按已展示列排序，杜绝 SQL 注入；非法字段回落默认。
    /// </summary>
    private static string BuildOrderBy(string? orderBy, string? orderDir)
    {
        var column = orderBy switch
        {
            "itemNum" => "m.ItemNum",
            "location" => "m.Location",
            "shelf" => "m.Shelf",
            "lotSerial" => "m.LotSerial",
            "qty" => "m.Qty",
            "compQty" => "m.CompQty",
            "qtyDiff" => "(IFNULL(m.CompQty,0) - IFNULL(m.Qty,0))",
            "invStatus" => "(CASE WHEN IFNULL(m.CompQty,0) <> IFNULL(m.Qty,0) THEN 1 ELSE 0 END)",
            "updateTime" => "m.UpdateTime",
            _ => "m.UpdateTime",
        };
        var direction = string.Equals(orderDir, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return $"{column} {direction}, m.RecID DESC";
    }
}