using SqlSugar;

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse;

/// <summary>
/// S5 来料检验（IQC）标准事实层投影服务。
///
/// <para>链路：<c>qms_qcp_inspbill</c>（+ 报检申请/分录 + 审批流实例）→ <c>mdp_std_iqc_inspection</c>。
/// 目的是让 S8 Stage-3 不再直读业务表与框架表 —— 那四张表此后只由本 Writer 读。</para>
///
/// <para><b>Grain = 一张检验单</b>（<c>tenant_id + iqc_bill_id</c>）。先证后建：全库 23 张单实测
/// entries ≤ 1、distinct_po ≤ 1、flow_instances ≤ 1，结构保证来自 <c>inspbill.hid</c> 这把
/// 指向报检分录的单值外键。</para>
///
/// <para><b>两条正交轴分开存</b>：质量轴取 <c>pd</c>（0 合格 / 1 不合格 / NULL 未判定），
/// 流程轴取审批流 <c>Status</c> 与 <c>EndTime</c>。实测存在「检验完成且 pd=1」的单，
/// 所以绝不可把流程完成当成质量合格，也绝不用 <c>FBILLSTATUS</c> 当质量 Authority。</para>
///
/// <para><b>租户方向是单向的</b>：<c>ApprovalFlowInstance</c> 没有 TenantId 列，
/// 因此租户只能来自 <c>qms_qcp_inspbill.tenant_id</c>，审批流仅按 (BizType, BizId) 反查，
/// 绝不反向驱动租户。</para>
///
/// <para><b>刻意不做</b>：不投 PO 行号 / 收货行号（上游 <c>FSRCBILLID</c>、
/// <c>FSRCBILLENTRYSEQ</c> 实测全空，属 SOURCE CONTRACT GAP）；不做订单级完成聚合；
/// 不写任何业务源表。</para>
/// </summary>
public class IqcInspectionMdpSyncService : ITransient
{
    private const string JobCode = "S5_IQC_INSPECTION_MDP_SYNC";
    private const string JobName = "S5来料检验标准事实层投影";

    /// <summary>审批流业务类型，与 <c>IqcInspBillFlowConst.BizType</c> 一致。</summary>
    private const string FlowBizType = "S5_IQC_INSPBILL";

    private readonly ISqlSugarClient _db;

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

    /// <summary>
    /// 全量投影（UPSERT）。可按租户收窄；<paramref name="tenantId"/> 为 0 表示全租户。
    /// 幂等：同一检验单重复运行只更新不新增，唯一键 (tenant_id, source_row_id)。
    /// </summary>
    public async Task<IqcInspectionMdpSyncResult> RunFullAsync(
        long tenantId = 0,
        string triggerType = "AUTO",
        CancellationToken cancellationToken = default)
    {
        cancellationToken.ThrowIfCancellationRequested();

        var now = DateTime.Now;
        var batchId = $"S5_IQC_STD_{(tenantId > 0 ? tenantId + "_" : "")}{now:yyyyMMddHHmmss}";
        var runLogId = await InsertRunLogAsync(batchId, now, triggerType);
        var result = new IqcInspectionMdpSyncResult { BatchId = batchId, RunLogId = runLogId };

        try
        {
            result.StdRows = await TransformStandardAsync(batchId, now, tenantId);
            await CompleteRunLogAsync(runLogId, result.StdRows, now);
            return result;
        }
        catch (Exception ex)
        {
            await FailRunLogAsync(runLogId, ex.Message);
            throw;
        }
    }

    /// <summary>
    /// 标准层投影。
    ///
    /// <para>报检分录用 <c>LEFT JOIN</c> 且【只走 <c>hid</c> 这把单值外键】：<c>hid</c> 为空的历史单
    /// 得到 NULL 的采购/收货/物料身份，而不是去申请单下"挑一条"分录 —— 宁可为空，不猜。</para>
    ///
    /// <para>审批流同样 <c>LEFT JOIN</c>：尚未发起流程的单落 NULL，不伪造完成时间。</para>
    /// </summary>
    private async Task<int> TransformStandardAsync(string batchId, DateTime now, long tenantId)
    {
        // 租户收窄：>0 时只投该租户，=0 时全投。两处（COUNT 与 INSERT）共用同一谓词，防止口径漂移。
        var tenantClause = tenantId > 0 ? " AND b.tenant_id=@TenantId" : "";

        var countPars = new List<SugarParameter>();
        if (tenantId > 0) countPars.Add(new SugarParameter("@TenantId", tenantId));

        var rows = await _db.Ado.GetIntAsync(
            $"""
            SELECT COUNT(1) FROM qms_qcp_inspbill b
            WHERE b.tenant_id > 0{tenantClause}
            """, countPars);

        var insertSql =
            $"""
            INSERT INTO mdp_std_iqc_inspection
            (tenant_id, factory_id, source_system,
             iqc_bill_id, iqc_bill_no, application_id, application_no, application_entry_id,
             purchase_order_no, receipt_no,
             material_code, material_name, lot_serial, supplier_code, supplier_name,
             raw_pd, quality_result,
             flow_status, flow_instance_id, completion_time,
             source_row_id, source_biz_key, sync_batch_id, sync_time)
            SELECT
                b.tenant_id, 1, 'AIDOP',
                b.id,
                NULLIF(b.FBILLNO, ''),
                a.id,
                NULLIF(b.lydjbh, ''),
                e.id,
                -- 采购/收货身份只从 hid 解析出的那一条分录取，取不到就是 NULL
                NULLIF(e.purchase_order_no, ''),
                NULLIF(e.FSRCORDERNUM, ''),
                NULLIF(e.FMATERIALCFG, ''),
                NULLIF(e.wlmc, ''),
                NULLIF(e.FLOTNUMBER, ''),
                NULLIF(e.gysbm, ''),
                NULLIF(e.gysmc, ''),
                b.pd,
                -- 质量结论只由 pd 派生。pd 为 NULL 时必须是 PENDING，绝不能滑成 PASS
                CASE WHEN b.pd = 0 THEN 'PASS'
                     WHEN b.pd = 1 THEN 'FAIL'
                     ELSE 'PENDING' END,
                i.Status,
                i.Id,
                -- 只有流程走到终态（Approved）才认完成时间；Running 状态下不得投影 EndTime
                CASE WHEN i.Status = 2 THEN i.EndTime ELSE NULL END,
                CAST(b.id AS CHAR),
                NULLIF(b.FBILLNO, ''),
                @BatchId, @Now
            FROM qms_qcp_inspbill b
            -- 申请单：租户显式对齐（Domain 不是租户边界，不可用作关联依据）
            LEFT JOIN qms_qcp_inspecapplyn a
                   ON a.tenant_id = b.tenant_id
                  AND a.FBILLNO = b.lydjbh
                  AND IFNULL(b.lydjbh, '') <> ''
            -- 报检分录：只认 hid 单值外键。不做「申请单下任取一条」的回退 —— 那会把多分录场景
            -- 静默变成随机挑一个 PO，是弱关联。
            LEFT JOIN qms_qcp_insappnentry e
                   ON e.tenant_id = b.tenant_id
                  AND e.id = b.hid
                  AND b.hid IS NOT NULL
            -- 审批流实例：租户方向单向 —— 由已确定租户的检验单去查流程，绝不反向。
            -- 实测每个 BizId 至多一个实例，故无需 MAX(EndTime) 之类的挑选规则。
            LEFT JOIN ApprovalFlowInstance i
                   ON i.BizType = @FlowBizType
                  AND i.BizId = b.id
            WHERE b.tenant_id > 0{tenantClause}
            ON DUPLICATE KEY UPDATE
                factory_id=VALUES(factory_id), source_system=VALUES(source_system),
                iqc_bill_no=VALUES(iqc_bill_no),
                application_id=VALUES(application_id), application_no=VALUES(application_no),
                application_entry_id=VALUES(application_entry_id),
                purchase_order_no=VALUES(purchase_order_no), receipt_no=VALUES(receipt_no),
                material_code=VALUES(material_code), material_name=VALUES(material_name),
                lot_serial=VALUES(lot_serial), supplier_code=VALUES(supplier_code), supplier_name=VALUES(supplier_name),
                raw_pd=VALUES(raw_pd), quality_result=VALUES(quality_result),
                flow_status=VALUES(flow_status), flow_instance_id=VALUES(flow_instance_id),
                completion_time=VALUES(completion_time),
                source_biz_key=VALUES(source_biz_key),
                sync_batch_id=VALUES(sync_batch_id), sync_time=VALUES(sync_time),
                update_time=CURRENT_TIMESTAMP
            """;

        var insPars = new List<SugarParameter>
        {
            new("@BatchId", batchId),
            new("@Now", now),
            new("@FlowBizType", FlowBizType),
        };
        if (tenantId > 0) insPars.Add(new SugarParameter("@TenantId", tenantId));

        await _db.Ado.ExecuteCommandAsync(insertSql, insPars);
        return rows;
    }

    private async Task<long> InsertRunLogAsync(string batchId, DateTime startedAt, string triggerType)
    {
        await _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO mdp_transform_run_log
            (tenant_id, job_code, job_name, trigger_type, batch_id, status, start_time)
            VALUES (0, @JobCode, @JobName, @TriggerType, @BatchId, 'RUNNING', @StartTime)
            """,
            new SugarParameter("@JobCode", JobCode),
            new SugarParameter("@JobName", JobName),
            new SugarParameter("@TriggerType", NormalizeTriggerType(triggerType)),
            new SugarParameter("@BatchId", batchId),
            new SugarParameter("@StartTime", startedAt));
        return await _db.Ado.GetLongAsync(
            "SELECT id FROM mdp_transform_run_log WHERE batch_id=@BatchId ORDER BY id DESC LIMIT 1",
            new List<SugarParameter> { new("@BatchId", batchId) });
    }

    private async Task CompleteRunLogAsync(long runLogId, int stdRows, DateTime startedAt)
    {
        var endedAt = DateTime.Now;
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE mdp_transform_run_log
               SET status='SUCCESS', standard_rows=@StdRows, end_time=@EndTime,
                   duration_ms=@Duration, update_time=CURRENT_TIMESTAMP
             WHERE id=@Id
            """,
            new SugarParameter("@StdRows", stdRows),
            new SugarParameter("@EndTime", endedAt),
            new SugarParameter("@Duration", (int)(endedAt - startedAt).TotalMilliseconds),
            new SugarParameter("@Id", runLogId));
    }

    private async Task FailRunLogAsync(long runLogId, string message)
    {
        await _db.Ado.ExecuteCommandAsync(
            """
            UPDATE mdp_transform_run_log
               SET status='FAILED', end_time=@EndTime, error_message=@Msg, update_time=CURRENT_TIMESTAMP
             WHERE id=@Id
            """,
            new SugarParameter("@EndTime", DateTime.Now),
            new SugarParameter("@Msg", message.Length > 900 ? message[..900] : message),
            new SugarParameter("@Id", runLogId));
    }

    private static string NormalizeTriggerType(string triggerType)
        => string.IsNullOrWhiteSpace(triggerType) ? "AUTO" : triggerType.Trim().ToUpperInvariant();
}

/// <summary>S5 IQC 标准层投影结果。</summary>
public sealed class IqcInspectionMdpSyncResult
{
    public string BatchId { get; set; } = string.Empty;
    public long RunLogId { get; set; }

    /// <summary>本轮投影覆盖的检验单数（去重后的 Grain 行数，非 JOIN 扇出行数）。</summary>
    public int StdRows { get; set; }
}