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

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

/// <summary>
/// S5 委外发料单 只读 list/detail 服务。
///
/// 数据源：DOP 数据中台标准层 mdp_std_outsource_issue（头）/ mdp_std_outsource_issue_detail（明细）。
/// 由 OutsourceIssueMdpSyncService 从 aidopdev.NbrMaster/NbrDetail Type='CA' 同步标准化而来。
///
/// 本服务仅 SELECT：无新增/编辑/删除/写入；明细只暴露已确认 9 列（发料数量/已发数/批次号 3 候选列后置）。
/// 当前上游未产 CA 单据，标准层为空，list 返回空数组、detail 返回 null（前端空态）。
/// </summary>
[ApiDescriptionSettings(Order = 304, Description = "委外发料单")]
[Route("api/OutsourceIssue")]
[AllowAnonymous]
[NonUnify]
public class OutsourceIssueService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;

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

    /// <summary>
    /// 委外发料单列表（只读分页查询）。
    /// </summary>
    [DisplayName("委外发料单列表")]
    [HttpGet("list")]
    public async Task<object> GetList([FromQuery] OutsourceIssueListInput 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> { "1=1" };
        var pars = new List<SugarParameter>();

        if (input.TenantId is > 0)
        {
            where.Add("m.tenant_id = @TenantId");
            pars.Add(new SugarParameter("@TenantId", input.TenantId));
        }
        if (!string.IsNullOrWhiteSpace(input.BillNo))
        {
            where.Add("m.bill_no LIKE @BillNo");
            pars.Add(new SugarParameter("@BillNo", $"%{input.BillNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.OutsourceNo))
        {
            where.Add("m.outsource_no LIKE @OutsourceNo");
            pars.Add(new SugarParameter("@OutsourceNo", $"%{input.OutsourceNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.IssueDateStart))
        {
            where.Add("m.issue_date >= @IssueDateStart");
            pars.Add(new SugarParameter("@IssueDateStart", $"{input.IssueDateStart.Trim()} 00:00:00"));
        }
        if (!string.IsNullOrWhiteSpace(input.IssueDateEnd))
        {
            where.Add("m.issue_date <= @IssueDateEnd");
            pars.Add(new SugarParameter("@IssueDateEnd", $"{input.IssueDateEnd.Trim()} 23:59:59"));
        }

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

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

        var list = await _db.Ado.SqlQueryAsync<OutsourceIssueListRow>(
            $"""
            SELECT
                m.id            AS Id,
                m.bill_no       AS Nbr,
                m.issue_date    AS Date,
                m.status_desc   AS StatusDescr,
                m.outsource_no  AS Address,
                m.work_order    AS WorkOrd,
                TRIM(CONCAT(IFNULL(m.department_code,''), ' ', IFNULL(m.department_name,''))) AS DepartmentDescr,
                m.issuer        AS User1,
                m.remark        AS Remark,
                m.create_user   AS CreateUser,
                m.source_create_time AS CreateTime
            FROM mdp_std_outsource_issue m
            WHERE {whereSql}
            ORDER BY {BuildOrderBy(input.SortField, input.SortOrder)}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    /// <summary>
    /// 委外发料单详情（只读：头 + 明细）。查不到返回 null。
    /// </summary>
    [DisplayName("委外发料单详情")]
    [HttpGet("detail")]
    public async Task<OutsourceIssueDetailDto?> GetDetail([FromQuery] long id, [FromQuery] long? tenantId)
    {
        if (id <= 0) return null;

        var headPars = new List<SugarParameter> { new("@Id", id) };
        var headWhere = "m.id = @Id";
        if (tenantId is > 0)
        {
            headWhere += " AND m.tenant_id = @TenantId";
            headPars.Add(new SugarParameter("@TenantId", tenantId));
        }

        var head = await _db.Ado.SqlQuerySingleAsync<OutsourceIssueDetailDto>(
            $"""
            SELECT
                m.id            AS Id,
                m.bill_no       AS Nbr,
                m.issue_date    AS Date,
                m.outsource_no  AS Address,
                m.work_order    AS WorkOrd,
                TRIM(CONCAT(IFNULL(m.department_code,''), ' ', IFNULL(m.department_name,''))) AS DepartmentDescr,
                m.issuer        AS User1,
                m.status_desc   AS StatusDescr,
                m.remark        AS Remark,
                m.create_user   AS CreateUser,
                m.source_create_time AS CreateTime
            FROM mdp_std_outsource_issue m
            WHERE {headWhere}
            LIMIT 1
            """,
            headPars);

        if (head == null || head.Id <= 0) return null;

        head.Lines = await _db.Ado.SqlQueryAsync<OutsourceIssueDetailLineDto>(
            """
            SELECT
                d.id            AS Id,
                d.line          AS Line,
                d.item_num      AS ItemNum,
                d.item_name     AS ItemName,
                d.um            AS Um,
                d.qty_ord       AS QtyOrd,
                d.location_from AS LocationFrom,
                d.location_to   AS LocationTo,
                d.status        AS Status,
                d.remark        AS Remark
            FROM mdp_std_outsource_issue_detail d
            WHERE d.std_head_id = @HeadId
            ORDER BY d.line ASC, d.id ASC
            """,
            new List<SugarParameter> { new("@HeadId", head.Id) });

        return head;
    }

    /// <summary>
    /// 排序白名单：仅允许按已展示列排序，杜绝 SQL 注入。
    /// </summary>
    private static string BuildOrderBy(string? sortField, string? sortOrder)
    {
        var column = sortField switch
        {
            "billNo" => "m.bill_no",
            "issueDate" => "m.issue_date",
            "sourceCreateTime" => "m.source_create_time",
            _ => "m.issue_date",
        };
        var direction = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return $"{column} {direction}, m.id DESC";
    }
}