using Admin.NET.Plugin.AiDOP.Infrastructure;
using Admin.NET.Plugin.AiDOP.MaterialWarehouse.Dto;
using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

/// <summary>
/// S5 IQC 原材料检规 ↔ 物料 派生映射服务（B-1）。
///
/// 职责边界（**本服务只建立 Spec ↔ Material 关系，不做任何"选哪条检规"的裁决**）：
///   qms_jygf.wlbm ──tokenize/normalize──> 与 ItemMaster.ItemNum 精确比对 ──> ado_s5_iqc_spec_material_map
///
/// 硬边界：
///   ① 绝不修改源 qms_jygf / qms_jygfzb 任何字段；
///   ② 禁止 LIKE 子串匹配，token 与 ItemMaster.ItemNum **等值**比对；
///   ③ 自动重算只删自己产生的行（is_manual=0），**人工绑定行（is_manual=1）永不被自动同步删除**；
///   ④ 全部按 AidopTenantScope 登录租户隔离，绝不跨租户匹配、绝不透传源行租户；
///   ⑤ **本服务不得出现候选排序/择一逻辑**（如 OrderByDescending(sxrq).First()）——
///      那是 B-2 Resolver 的职责。本服务的查询接口一律返回全部候选，不做选择。
///
/// 分词/标准化/分级规则集中在 <see cref="S5IqcMaterialTokenizer"/>，运行期与 UpdateScripts 迁移共用同一口径。
/// </summary>
[ApiDescriptionSettings(Order = 341, Description = "IQC检规物料映射")]
[Route("api/S5IqcSpecMap")]
[NonUnify]
public class S5IqcSpecMaterialMapService : IDynamicApiController, ITransient
{
    private const string MapTable = "ado_s5_iqc_spec_material_map";

    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

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

    private long ResolveTenantOrThrow() => AidopTenantScope.ResolveOrThrow(_userManager);

    // ==================================================================
    // 查询（只读，返回全部候选，**不做择一**）
    // ==================================================================

    /// <summary>映射列表（分页）。物料编码为等值过滤，禁子串匹配。</summary>
    [DisplayName("IQC检规物料映射列表")]
    [HttpGet("list")]
    public async Task<object> GetList([FromQuery] S5IqcSpecMapListInput input)
    {
        var tenantId = ResolveTenantOrThrow();
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 20 : (input.PageSize > 200 ? 200 : input.PageSize);

        var where = new List<string> { "m.tenant_id=@t" };
        var pars = new List<SugarParameter> { new("@t", tenantId) };

        if (!string.IsNullOrWhiteSpace(input.MaterialCode))
        {
            where.Add("m.material_code=@mc"); // 等值，禁 LIKE
            pars.Add(new SugarParameter("@mc", S5IqcMaterialTokenizer.Normalize(input.MaterialCode)));
        }
        if (input.SpecId is > 0)
        {
            where.Add("m.spec_id=@sid");
            pars.Add(new SugarParameter("@sid", input.SpecId!.Value));
        }
        if (!string.IsNullOrWhiteSpace(input.SpecNo))
        {
            where.Add("m.spec_no=@sno");
            pars.Add(new SugarParameter("@sno", input.SpecNo.Trim()));
        }
        if (!string.IsNullOrWhiteSpace(input.MatchStatus))
        {
            where.Add("m.match_status=@ms");
            pars.Add(new SugarParameter("@ms", input.MatchStatus.Trim()));
        }

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

        // 排序仅为分页稳定性（spec_id/seq/id 全序），**不是候选优先级**，不含任何业务择一语义。
        var list = await _db.Ado.SqlQueryAsync<S5IqcSpecMapRow>(
            $@"SELECT m.id AS Id, m.spec_id AS SpecId, m.spec_no AS SpecNo, m.spec_version AS SpecVersion,
                      m.seq AS Seq, m.raw_token AS RawToken, m.material_code AS MaterialCode,
                      m.match_status AS MatchStatus, m.source_raw AS SourceRaw, m.is_manual AS IsManual,
                      m.note AS Note, m.create_time AS CreateTime, m.update_time AS UpdateTime
               FROM {MapTable} m
               WHERE {whereSql}
               ORDER BY m.spec_id, m.seq, m.id
               LIMIT {pageSize} OFFSET {(page - 1) * pageSize}", pars);

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

    // ==================================================================
    // 同步
    // ==================================================================

    /// <summary>手动重新同步单条检规的映射（人工 resync 入口，供钩子失败后补偿）。</summary>
    [DisplayName("重新同步单条检规映射")]
    [HttpPost("sync/{specId:long}")]
    public async Task<S5IqcSpecSyncSummary> Sync(long specId)
    {
        var tenantId = ResolveTenantOrThrow();
        return await SyncSpecAsync(tenantId, specId);
    }

    /// <summary>
    /// 单检规同步（S0 CRUD / 导入后调用）：只重建**这一条检规**的自动派生映射。
    ///
    /// 语义（对应 B-1 验收问题）：
    ///   · AUTO 重算：先 DELETE 本 (tenant, spec, is_manual=0) 全部旧行，再按当前 wlbm 重建 → 旧 EXACT 会被删除；
    ///     wlbm 被改动后旧物料的映射因此自然失效，无需额外过期逻辑。
    ///   · MANUAL：DELETE 条件恒带 is_manual=0，人工行**永不**被自动同步删除；仅刷新其 spec_no/spec_version 元数据。
    ///   · 检规不存在（已删除）：走 <see cref="PurgeSpecAsync"/> 语义由调用方决定；本方法在 spec 缺失时只清 AUTO 行。
    ///   · 幂等：同一 (tenant, spec) 重复调用 → 先删后建，结果集恒等，无重复行。
    /// </summary>
    public async Task<S5IqcSpecSyncSummary> SyncSpecAsync(long tenantId, long specId)
    {
        var summary = new S5IqcSpecSyncSummary { SpecId = specId };
        if (tenantId <= 0 || specId <= 0)
        {
            summary.Message = "invalid-args";
            return summary;
        }

        var spec = await _db.Ado.SqlQuerySingleAsync<SpecRow>(
            "SELECT id AS Id, wjbh AS Wjbh, bb AS Bb, wlbm AS Wlbm FROM qms_jygf WHERE id=@id AND tenant_id=@t LIMIT 1",
            new SugarParameter("@id", specId), new SugarParameter("@t", tenantId));

        summary.SpecMissing = spec == null;

        var rows = new List<Dictionary<string, object>>();
        if (spec != null)
        {
            var itemSet = await LoadItemCodeSetAsync(tenantId);
            var now = DateTime.Now;
            var userId = _userManager.UserId;
            var seen = new HashSet<string>(StringComparer.Ordinal);
            var seq = 0;

            foreach (var raw in S5IqcMaterialTokenizer.Split(spec.Wlbm))
            {
                var normalized = S5IqcMaterialTokenizer.Normalize(raw);
                if (normalized.Length == 0) continue;
                if (!seen.Add(normalized)) { summary.DuplicateDropped++; continue; }

                var status = S5IqcMaterialTokenizer.Classify(normalized, itemSet);
                switch (status)
                {
                    case S5IqcMapStatus.Exact: summary.Exact++; break;
                    case S5IqcMapStatus.GlueUnresolved: summary.GlueUnresolved++; break;
                    default: summary.NotFound++; break;
                }

                seq++;
                summary.TokenCount++;
                rows.Add(new Dictionary<string, object>
                {
                    ["id"] = YitIdHelper.NextId(),
                    ["tenant_id"] = tenantId,
                    ["spec_id"] = spec.Id,
                    ["spec_no"] = (object?)spec.Wjbh ?? DBNull.Value,
                    ["spec_version"] = (object?)spec.Bb ?? DBNull.Value,
                    ["seq"] = seq,
                    ["raw_token"] = raw,
                    // 只有 EXACT 才落 material_code；NOT_FOUND / GLUE_UNRESOLVED 一律 NULL，绝不猜物料
                    ["material_code"] = status == S5IqcMapStatus.Exact ? normalized : DBNull.Value,
                    ["match_status"] = status,
                    ["source_raw"] = (object?)spec.Wlbm ?? DBNull.Value,
                    ["is_manual"] = 0,
                    ["note"] = DBNull.Value,
                    ["create_time"] = now,
                    ["update_time"] = now,
                    ["create_user_id"] = (object?)userId ?? DBNull.Value,
                });
            }
        }

        var tran = await _db.Ado.UseTranAsync(async () =>
        {
            summary.AutoRowsReplaced = await _db.Ado.ExecuteCommandAsync(
                $"DELETE FROM {MapTable} WHERE tenant_id=@t AND spec_id=@s AND is_manual=0",
                new SugarParameter("@t", tenantId), new SugarParameter("@s", specId));

            if (rows.Count > 0) await BulkInsertAsync(rows);

            if (spec != null)
            {
                // 人工行随主表 wjbh/bb 变更刷新元数据（不改归属、不改 is_manual、不删除）
                await _db.Ado.ExecuteCommandAsync(
                    $@"UPDATE {MapTable} SET spec_no=@w, spec_version=@b, update_time=@u
                       WHERE tenant_id=@t AND spec_id=@s AND is_manual=1",
                    new SugarParameter("@w", (object?)spec.Wjbh ?? DBNull.Value),
                    new SugarParameter("@b", (object?)spec.Bb ?? DBNull.Value),
                    new SugarParameter("@u", DateTime.Now),
                    new SugarParameter("@t", tenantId), new SugarParameter("@s", specId));
            }
        });
        if (!tran.IsSuccess) throw tran.ErrorException ?? Oops.Oh("同步 IQC 检规物料映射失败");

        summary.ManualRowsPreserved = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(1) FROM {MapTable} WHERE tenant_id=@t AND spec_id=@s AND is_manual=1",
            new SugarParameter("@t", tenantId), new SugarParameter("@s", specId));

        summary.Ok = true;
        return summary;
    }

    /// <summary>
    /// 检规删除后的映射清理：删除该 (tenant, spec) 的**全部**行，含人工绑定行。
    ///
    /// 与 <see cref="SyncSpecAsync"/> 的差别是刻意的：检规已不存在，指向它的人工绑定是悬挂引用，
    /// 保留只会让 B-2 Resolver JOIN 到空行。故 Delete 场景全清（不同于 S7 保留人工行待处置）。
    /// </summary>
    public async Task<S5IqcSpecSyncSummary> PurgeSpecAsync(long tenantId, long specId)
    {
        var summary = new S5IqcSpecSyncSummary { SpecId = specId, SpecMissing = true };
        if (tenantId <= 0 || specId <= 0)
        {
            summary.Message = "invalid-args";
            return summary;
        }

        summary.AutoRowsReplaced = await _db.Ado.ExecuteCommandAsync(
            $"DELETE FROM {MapTable} WHERE tenant_id=@t AND spec_id=@s",
            new SugarParameter("@t", tenantId), new SugarParameter("@s", specId));
        summary.Ok = true;
        summary.Message = "purged";
        return summary;
    }

    /// <summary>整租户重建自动派生映射（人工行原样保留）。用于首次建库或批量订正后的兜底。</summary>
    [DisplayName("重建IQC检规物料映射")]
    [HttpPost("rebuild")]
    public async Task<S5IqcSpecRebuildSummary> Rebuild()
    {
        var tenantId = ResolveTenantOrThrow();
        var result = new S5IqcSpecRebuildSummary();

        var specs = await _db.Ado.SqlQueryAsync<SpecRow>(
            "SELECT id AS Id, wjbh AS Wjbh, bb AS Bb, wlbm AS Wlbm FROM qms_jygf WHERE tenant_id=@t ORDER BY id",
            new SugarParameter("@t", tenantId));
        result.SpecCount = specs.Count;

        foreach (var s in specs)
        {
            var one = await SyncSpecAsync(tenantId, s.Id);
            result.TokenCount += one.TokenCount;
            result.Exact += one.Exact;
            result.NotFound += one.NotFound;
            result.GlueUnresolved += one.GlueUnresolved;
            result.DuplicateDropped += one.DuplicateDropped;
            if (one.Exact > 0) result.SpecsWithExact++;
        }

        result.ManualRowsPreserved = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(1) FROM {MapTable} WHERE tenant_id=@t AND is_manual=1",
            new SugarParameter("@t", tenantId));
        result.Ok = true;
        return result;
    }

    // ==================================================================
    // 人工订正
    // ==================================================================

    /// <summary>人工绑定：把一个标准物料码绑定到指定检规（is_manual=1）。不改源 wlbm。</summary>
    [DisplayName("人工绑定IQC检规物料")]
    [HttpPost("bind")]
    public async Task<object> Bind([FromBody] S5IqcSpecMapBindInput input)
    {
        var tenantId = ResolveTenantOrThrow();
        if (input == null || input.SpecId <= 0 || string.IsNullOrWhiteSpace(input.MaterialCode))
            throw Oops.Oh("检规 id 与物料编码必填");
        var materialCode = S5IqcMaterialTokenizer.Normalize(input.MaterialCode);

        var spec = await _db.Ado.SqlQuerySingleAsync<SpecRow>(
            "SELECT id AS Id, wjbh AS Wjbh, bb AS Bb, wlbm AS Wlbm FROM qms_jygf WHERE id=@id AND tenant_id=@t LIMIT 1",
            new SugarParameter("@id", input.SpecId), new SugarParameter("@t", tenantId));
        if (spec == null) throw Oops.Oh("检规不存在或不属于当前租户");

        var itemExists = await _db.Ado.GetIntAsync(
            "SELECT COUNT(1) FROM ItemMaster WHERE tenant_id=@t AND ItemNum=@mc",
            new SugarParameter("@t", tenantId), new SugarParameter("@mc", materialCode));
        if (itemExists == 0) throw Oops.Oh($"物料 {materialCode} 不在当前租户 ItemMaster，无法绑定");

        var dup = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(1) FROM {MapTable} WHERE tenant_id=@t AND spec_id=@s AND raw_token=@rt AND is_manual=1",
            new SugarParameter("@t", tenantId), new SugarParameter("@s", input.SpecId),
            new SugarParameter("@rt", materialCode));
        if (dup > 0) return new { ok = true, message = "已存在该人工绑定" };

        var now = DateTime.Now;
        await BulkInsertAsync(new List<Dictionary<string, object>>
        {
            new()
            {
                ["id"] = YitIdHelper.NextId(),
                ["tenant_id"] = tenantId,
                ["spec_id"] = input.SpecId,
                ["spec_no"] = (object?)spec.Wjbh ?? DBNull.Value,
                ["spec_version"] = (object?)spec.Bb ?? DBNull.Value,
                ["seq"] = 0,
                ["raw_token"] = materialCode,
                ["material_code"] = materialCode,
                ["match_status"] = S5IqcMapStatus.Manual,
                ["source_raw"] = (object?)spec.Wlbm ?? DBNull.Value,
                ["is_manual"] = 1,
                ["note"] = (object?)input.Note ?? DBNull.Value,
                ["create_time"] = now,
                ["update_time"] = now,
                ["create_user_id"] = (object?)_userManager.UserId ?? DBNull.Value,
            }
        });
        return new { ok = true };
    }

    /// <summary>解绑人工绑定（只允许删除 is_manual=1 的行）。</summary>
    [DisplayName("解绑IQC检规物料")]
    [HttpPost("unbind")]
    public async Task<object> Unbind([FromBody] S5IqcSpecMapUnbindInput input)
    {
        var tenantId = ResolveTenantOrThrow();
        if (input == null || input.Id <= 0) throw Oops.Oh("id 必填");
        var n = await _db.Ado.ExecuteCommandAsync(
            $"DELETE FROM {MapTable} WHERE id=@id AND tenant_id=@t AND is_manual=1",
            new SugarParameter("@id", input.Id), new SugarParameter("@t", tenantId));
        return new { ok = n > 0 };
    }

    // ==================================================================
    // 内部
    // ==================================================================

    /// <summary>取当前租户 ItemMaster 全部物料码（标准化后）。绝不跨租户。</summary>
    private async Task<HashSet<string>> LoadItemCodeSetAsync(long tenantId)
    {
        var codes = await _db.Ado.SqlQueryAsync<string>(
            "SELECT DISTINCT ItemNum FROM ItemMaster WHERE tenant_id=@t AND ItemNum IS NOT NULL",
            new SugarParameter("@t", tenantId));
        return new HashSet<string>(
            codes.Where(x => !string.IsNullOrWhiteSpace(x)).Select(S5IqcMaterialTokenizer.Normalize),
            StringComparer.Ordinal);
    }

    private static readonly string[] MapColumns =
    {
        "id", "tenant_id", "spec_id", "spec_no", "spec_version", "seq", "raw_token", "material_code",
        "match_status", "source_raw", "is_manual", "note", "create_time", "update_time", "create_user_id"
    };

    /// <summary>
    /// 参数化分块批插。不用 db.Insertable(List&lt;Dictionary&gt;)：SqlSugar 字典批插对 nullable 列的
    /// DBNull 渲染在 S7 侧曾触发 MySQL "Incorrect datetime value: ''"（见 FqcSpecMaterialMapService 同名方法注释）。
    /// </summary>
    private async Task BulkInsertAsync(List<Dictionary<string, object>> rows)
    {
        if (rows == null || rows.Count == 0) return;
        const int chunkSize = 200;
        var colList = string.Join(", ", MapColumns);
        for (var start = 0; start < rows.Count; start += chunkSize)
        {
            var slice = rows.GetRange(start, Math.Min(chunkSize, rows.Count - start));
            var valueClauses = new List<string>(slice.Count);
            var pars = new List<SugarParameter>(slice.Count * MapColumns.Length);
            for (var r = 0; r < slice.Count; r++)
            {
                var placeholders = new string[MapColumns.Length];
                for (var c = 0; c < MapColumns.Length; c++)
                {
                    var name = $"@{MapColumns[c]}_{r}";
                    placeholders[c] = name;
                    var val = slice[r].TryGetValue(MapColumns[c], out var v) ? (v ?? DBNull.Value) : DBNull.Value;
                    pars.Add(new SugarParameter(name, val));
                }
                valueClauses.Add($"({string.Join(", ", placeholders)})");
            }
            await _db.Ado.ExecuteCommandAsync(
                $"INSERT INTO {MapTable} ({colList}) VALUES {string.Join(", ", valueClauses)}", pars);
        }
    }

    private sealed class SpecRow
    {
        public long Id { get; set; }
        public string? Wjbh { get; set; }
        public string? Bb { get; set; }
        public string? Wlbm { get; set; }
    }
}