using Admin.NET.Plugin.AiDOP.Entity;
using Admin.NET.Plugin.AiDOP.Manufacturing.Dto;

namespace Admin.NET.Plugin.AiDOP.Manufacturing;

/// <summary>
/// S6 模工具台账管理 服务（FUNC-S6-004）。
///
/// 数据源：aidopdev.MoldToolList（模工具台账），忠实复刻证据 sql.txt（select * from MoldToolList），
/// 列映射见字段配置图：类型=lx / 名称=MoldName / 供应商名=gysmc / 数量=MoldCount / 功能应用名称=gnyymc /
/// 模具类型编码=MoldTypeCode / 对应产品物料编码=product_material_code / 图纸号=product_drawing_number /
/// 产品名称=product_name / 工序=process。lx 含 模具 / 工装 / 检具（同表同页同源，类型下拉可筛选）。
///
/// list / type-options 为既有只读查询，本批不改其签名与返回结构。
/// detail / create / update / delete 为 CRUD V1 新增能力，写字段严格限于 9 个白名单业务字段；
/// gys / gysmc / MoldUsingDepartment / zrr / zt / sm / bypl / fj / xh / bh / gnyyid /
/// MoldMaintenanceReleaseTime / ActiveMaintenanceMoldCount 语义未确认，本批不写。
///
/// 租户：所有读写一律经 AidopTenantScope.ResolveOrThrow 取 JWT 可信租户，并在每条语句显式带
/// tenant 条件；不信任前端传入的 tenantId，不依赖全局 QueryFilter（其对超管不生效）。
/// </summary>
[ApiDescriptionSettings(Order = 320, Description = "模工具台账管理")]
[Route("api/S6ToolingLedger")]
[NonUnify]
public class ToolingLedgerService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;
    private readonly MoldToolReferenceChecker _refChecker;

    public ToolingLedgerService(ISqlSugarClient db, UserManager userManager, MoldToolReferenceChecker refChecker)
    {
        _db = db;
        _userManager = userManager;
        _refChecker = refChecker;
    }

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

        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        var where = new List<string> { "m.tenant_id = @TenantId" };
        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };

        if (!string.IsNullOrWhiteSpace(input.Name))
        {
            where.Add("m.MoldName LIKE @Name");
            pars.Add(new SugarParameter("@Name", $"%{input.Name.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.WorkGroup))
        {
            where.Add("m.gnyymc LIKE @WorkGroup");
            pars.Add(new SugarParameter("@WorkGroup", $"%{input.WorkGroup.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.UsageDept))
        {
            where.Add("m.MoldUsingDepartment LIKE @UsageDept");
            pars.Add(new SugarParameter("@UsageDept", $"%{input.UsageDept.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Type))
        {
            where.Add("m.lx = @Type");
            pars.Add(new SugarParameter("@Type", input.Type.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.MoldTypeCode))
        {
            where.Add("m.MoldTypeCode LIKE @MoldTypeCode");
            pars.Add(new SugarParameter("@MoldTypeCode", $"%{input.MoldTypeCode.Trim()}%"));
        }

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

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

        var list = await _db.Ado.SqlQueryAsync<ToolingLedgerListRow>(
            $"""
            SELECT
                m.id                    AS Id,
                m.lx                    AS Type,
                m.MoldName              AS Name,
                m.gysmc                 AS SupplierName,
                m.MoldCount             AS Qty,
                m.gnyymc                AS FunctionAppName,
                m.MoldTypeCode          AS MoldTypeCode,
                m.product_material_code AS ProductMaterialCode,
                m.product_drawing_number AS ProductDrawingNo,
                m.product_name          AS ProductName,
                m.process               AS Process
            FROM MoldToolList m
            WHERE {whereSql}
            ORDER BY m.id DESC
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    /// <summary>
    /// 类型下拉选项（MoldToolList.lx distinct：模具 / 工装 / 检具，只读）。
    /// </summary>
    [DisplayName("模工具台账类型选项")]
    [HttpGet("type-options")]
    public async Task<object> GetTypeOptions()
    {
        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        var list = await _db.Ado.SqlQueryAsync<ToolingLedgerTypeOption>(
            """
            SELECT DISTINCT lx AS Val
            FROM MoldToolList
            WHERE tenant_id = @TenantId
              AND IFNULL(lx,'') <> ''
            ORDER BY lx
            """,
            new SugarParameter("@TenantId", tenantId));
        return list;
    }

    // ─────────────────────────── CRUD V1（FUNC-S6-004） ───────────────────────────

    /// <summary>
    /// 模工具台账详情（租户内）。不存在或不属当前租户一律 404，不泄露记录存在性。
    /// </summary>
    [DisplayName("模工具台账详情")]
    [HttpGet("detail/{id:int}")]
    public async Task<IActionResult> GetDetail(int id)
    {
        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        var entity = await LoadScopedAsync(id, tenantId);
        return entity == null ? NotFound() : new OkObjectResult(ToDetail(entity));
    }

    /// <summary>
    /// 新增模工具台账。tenant_id 只由服务端从 JWT 盖章，不接受任何客户端租户入参。
    /// </summary>
    [DisplayName("新增模工具台账")]
    [HttpPost("create")]
    public async Task<IActionResult> Create([FromBody] ToolingLedgerCreateInput input)
    {
        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);

        var type = NormalizeType(input.Type);
        var name = RequireText(input.Name, "名称");
        var qty = RequireQty(input.Qty);
        var code = RequireText(input.MoldTypeCode, "模具类型编码");

        // 友好预检；真正的并发唯一性由 DB 唯一索引 uk_MoldToolList_tenant_type_code 保证（见下方 catch）。
        var duplicated = await _db.Queryable<AdoS6MoldToolList>()
            .Where(x => x.TenantId == tenantId && x.MoldTypeCode == code)
            .AnyAsync();
        if (duplicated) return DuplicateCode(code);

        var entity = new AdoS6MoldToolList
        {
            TenantId = tenantId, // 服务端盖章，不取 DTO / query
            Lx = type,
            MoldName = name,
            MoldCount = qty,
            MoldTypeCode = code,
            Gnyymc = Trim(input.WorkGroup),
            ProductMaterialCode = Trim(input.ProductMaterialCode),
            ProductDrawingNumber = Trim(input.ProductDrawingNo),
            ProductName = Trim(input.ProductName),
            Process = Trim(input.Process)
        };

        try
        {
            entity.Id = await _db.Insertable(entity).ExecuteReturnIdentityAsync();
        }
        catch (Exception ex) when (IsDuplicateKeyViolation(ex))
        {
            // 并发下预检与插入之间存在 TOCTOU 窗口，由唯一索引兜底，转成同一业务错误，不外泄 SQL 异常。
            return DuplicateCode(code);
        }

        return new OkObjectResult(ToDetail(entity));
    }

    /// <summary>
    /// 编辑模工具台账（租户内）。MoldTypeCode 与 tenant_id 均不可修改（入参类型上即不接受）。
    /// </summary>
    [DisplayName("编辑模工具台账")]
    [HttpPut("update/{id:int}")]
    public async Task<IActionResult> Update(int id, [FromBody] ToolingLedgerUpdateInput input)
    {
        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        var entity = await LoadScopedAsync(id, tenantId);
        if (entity == null) return NotFound();

        var type = NormalizeType(input.Type);
        var name = RequireText(input.Name, "名称");
        var qty = RequireQty(input.Qty);
        var workGroup = Trim(input.WorkGroup);
        var materialCode = Trim(input.ProductMaterialCode);
        var drawingNo = Trim(input.ProductDrawingNo);
        var productName = Trim(input.ProductName);
        var process = Trim(input.Process);

        // 显式 SET + 显式 WHERE（id + tenant_id）：
        // ① 杜绝裸 UPDATE ... WHERE id=@id；② 只写 8 个白名单列，
        // MoldTypeCode / tenant_id / 全部 DEFERRED 列不出现在 SET 子句中。
        var affected = await _db.Updateable<AdoS6MoldToolList>()
            .SetColumns(x => new AdoS6MoldToolList
            {
                Lx = type,
                MoldName = name,
                MoldCount = qty,
                Gnyymc = workGroup,
                ProductMaterialCode = materialCode,
                ProductDrawingNumber = drawingNo,
                ProductName = productName,
                Process = process
            })
            .Where(x => x.Id == id && x.TenantId == tenantId)
            .ExecuteCommandAsync();
        if (affected <= 0) return NotFound();

        entity.Lx = type;
        entity.MoldName = name;
        entity.MoldCount = qty;
        entity.Gnyymc = workGroup;
        entity.ProductMaterialCode = materialCode;
        entity.ProductDrawingNumber = drawingNo;
        entity.ProductName = productName;
        entity.Process = process;
        return new OkObjectResult(ToDetail(entity));
    }

    /// <summary>
    /// 删除模工具台账（租户内物理删除；表无软删列）。被下游引用时拒绝删除，不级联、不清空引用。
    /// </summary>
    [DisplayName("删除模工具台账")]
    [HttpDelete("delete/{id:int}")]
    public async Task<IActionResult> Delete(int id)
    {
        var tenantId = Infrastructure.AidopTenantScope.ResolveOrThrow(_userManager);
        var entity = await LoadScopedAsync(id, tenantId);
        if (entity == null) return NotFound();

        var reference = await _refChecker.ReferencesAsync(tenantId, entity.MoldTypeCode);
        if (reference is { } r)
            return Problem(409, $"该模工具已被 {r.Count} 条{r.Table}引用，无法删除");

        var affected = await _db.Deleteable<AdoS6MoldToolList>()
            .Where(x => x.Id == id && x.TenantId == tenantId)
            .ExecuteCommandAsync();
        if (affected <= 0) return NotFound();

        return new OkObjectResult(new { message = "删除成功" });
    }

    // ─────────────────────────── 内部辅助 ───────────────────────────

    /// <summary>租户内按主键取单条；不存在或属于其他租户一律返回 null（调用方统一转 404，不泄露存在性）。</summary>
    private Task<AdoS6MoldToolList> LoadScopedAsync(int id, long tenantId)
        => _db.Queryable<AdoS6MoldToolList>()
            .Where(x => x.Id == id && x.TenantId == tenantId)
            .FirstAsync();

    /// <summary>
    /// 业务错误结果。刻意用 ObjectResult 显式给状态码，而不是 Oops.Oh(...).StatusCode(n)：
    /// 本服务是 [NonUnify] 的 IDynamicApiController，实测该路径下 Furion 友好异常一律落 400，
    /// StatusCode(409/404) 不生效。校验类错误仍走 Oops.Oh（400 语义正确）。
    /// body 为纯中文字符串，与既有 S6 错误响应形态一致，前端 request 拦截器可直接提取展示。
    /// </summary>
    private static IActionResult Problem(int statusCode, string message)
        => new ObjectResult(message) { StatusCode = statusCode };

    private static IActionResult NotFound() => Problem(404, "模工具台账记录不存在");

    private static IActionResult DuplicateCode(string code) =>
        Problem(409, $"模具类型编码「{code}」已存在，请更换后重试");

    /// <summary>类型固定枚举校验（不依赖存量 DISTINCT，空租户同样可创建首条）。</summary>
    private static string NormalizeType(string? value)
    {
        var type = value?.Trim();
        if (string.IsNullOrEmpty(type)) throw Oops.Oh("类型不能为空");
        if (!ToolingLedgerConst.Types.Contains(type))
            throw Oops.Oh($"类型只能是 {string.Join(" / ", ToolingLedgerConst.Types)}");
        return type;
    }

    private static string RequireText(string? value, string label)
    {
        var text = value?.Trim();
        if (string.IsNullOrEmpty(text)) throw Oops.Oh($"{label}不能为空");
        return text;
    }

    /// <summary>数量必填且非负；注意 0 是合法值，不得当成空值。</summary>
    private static int RequireQty(int? value)
    {
        if (value == null) throw Oops.Oh("数量不能为空");
        if (value < 0) throw Oops.Oh("数量不能为负数");
        return value.Value;
    }

    /// <summary>空白串归一为 null，避免把历史 NULL 污染成空串。</summary>
    private static string? Trim(string? value)
    {
        var text = value?.Trim();
        return string.IsNullOrEmpty(text) ? null : text;
    }

    /// <summary>
    /// 识别唯一键冲突。按驱动异常文本判定（MySQL 1062 / "Duplicate entry"），
    /// 不额外引入 MySqlConnector 包引用，且对被 SqlSugar 包装的异常链同样有效。
    /// </summary>
    private static bool IsDuplicateKeyViolation(Exception ex)
    {
        for (var e = ex; e != null; e = e.InnerException)
        {
            var message = e.Message;
            if (string.IsNullOrEmpty(message)) continue;
            if (message.Contains("Duplicate entry", StringComparison.OrdinalIgnoreCase)) return true;
            if (message.Contains("uk_MoldToolList_tenant_type_code", StringComparison.OrdinalIgnoreCase)) return true;
        }
        return false;
    }

    private static ToolingLedgerDetailDto ToDetail(AdoS6MoldToolList e) => new()
    {
        Id = e.Id,
        Type = e.Lx,
        Name = e.MoldName,
        Qty = e.MoldCount,
        MoldTypeCode = e.MoldTypeCode,
        WorkGroup = e.Gnyymc,
        ProductMaterialCode = e.ProductMaterialCode,
        ProductDrawingNo = e.ProductDrawingNumber,
        ProductName = e.ProductName,
        Process = e.Process
    };
}