using Admin.NET.Plugin.AiDOP.DataPlatform;
using SqlSugar;
using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.MaterialWarehouse.InventoryPosting.L0;

/// <summary>
/// L0 IQC 收货标准层贴源转换（态①只读贴源）。**只读源、只写 `mdp_std_iqc_receipt_state`**——绝不写源库/旧 WMS 表。
///
/// identity 链（据 LIVE SP `qms_WMS_SaveIQCResultcheck` 与 DOP `IqcReturnQueryService` 取证）：
///   qms_qcp_inspbill a ⋈(a.lydjbh=b.FBILLNO) qms_qcp_inspecapplyn b ⋈(b.id=c.glid, a.hid=c.id) qms_qcp_insappnentry c
///   ⋈(d.Domain,d.BarCode=a.lydjbh,d.ItemNum=c.FMATERIALCFG) PurOrdRctDetail d ⋈(Domain,OrdNbr=PurOrd,OrdLine=Line) PurOrdDetail p
///   + MissedPrint(CheckNo=a.lydjbh,Status='N',Type&lt;&gt;'Card') 计数。
///
/// **⚠️ 未接线/未实库验证**：本服务不入任何 cron/job（态①就位不启用），Phase 5B 受控实库验证前不跑真数据。
/// **双源**：源 A=本库 aidopdev(MySQL)，下方 SQL 为 MySQL 方言；源 B=dopdemorq(SQLSERVER) 方言适配留待
/// （dopdemorq 六表当前空、默认禁用，见项目说明）。读端 <see cref="MdpIqcReceiptStateLoader"/> 与源无关，切源不改读代码。
/// </summary>
public sealed class IqcReceiptStateMdpSyncService : ITransient
{
    public const string StdTable = "mdp_std_iqc_receipt_state";

    private readonly ISqlSugarClient _db;                 // 主库：写 std
    private readonly MdpSourceScopeFactory _scopeFactory; // 源库只读连接域

    public IqcReceiptStateMdpSyncService(ISqlSugarClient db, MdpSourceScopeFactory scopeFactory)
    {
        _db = db;
        _scopeFactory = scopeFactory;
    }

    /// <summary>运行时建 std 表（与 WIP-S5-IQC-INVPOSTING.sql 一致；mdp 表按框架惯例用原生 DDL 非 SugarTable）。</summary>
    public async Task EnsureTablesAsync()
    {
        await _db.Ado.ExecuteCommandAsync(
            """
            CREATE TABLE IF NOT EXISTS `mdp_std_iqc_receipt_state` (
              `id` BIGINT NOT NULL,
              `tenant_id` BIGINT NOT NULL,
              `domain` VARCHAR(20) NOT NULL DEFAULT '',
              `fbillno` VARCHAR(50) NOT NULL DEFAULT '',
              `qc_nbr` VARCHAR(50) NULL,
              `item_num` VARCHAR(100) NULL,
              `location` VARCHAR(50) NULL,
              `lot_serial` VARCHAR(100) NULL,
              `rct_nbr` VARCHAR(50) NULL,
              `pur_ord` VARCHAR(50) NULL,
              `pur_line` INT NULL,
              `potype` VARCHAR(20) NULL,
              `qty_ordered` DECIMAL(18,4) NOT NULL DEFAULT 0,
              `received_cum_qty` DECIMAL(18,4) NOT NULL DEFAULT 0,
              `returned_cum_qty` DECIMAL(18,4) NOT NULL DEFAULT 0,
              `sample_qty` DECIMAL(18,4) NOT NULL DEFAULT 0,
              `receipt_pending_qty` DECIMAL(18,4) NOT NULL DEFAULT 0,
              `receivable_detail_count` INT NOT NULL DEFAULT 0,
              `source_system` VARCHAR(50) NULL,
              `sync_batch_id` VARCHAR(50) NULL,
              `sync_time` DATETIME NULL,
              `create_time` DATETIME NULL,
              PRIMARY KEY (`id`),
              UNIQUE KEY `uk_mdp_std_iqc_rct` (`tenant_id`,`domain`,`fbillno`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='L0 IQC收货标准层(只读贴源)';
            """);
    }

    /// <summary>
    /// 态①贴源：从 <paramref name="sourceCode"/> 只读 join 拉取 → FULL REPLACE 本 tenant+domain 的 std。
    /// 只 SELECT 源、只在主库事务内 DELETE+INSERT std；返回落地行数。
    /// </summary>
    public async Task<int> RunInboundAsync(string sourceCode, long tenantId, string domainCode, CancellationToken ct = default)
    {
        var domain = domainCode ?? "";
        var scope = await _scopeFactory.GetScopeAsync(sourceCode, ct);   // 源库只读连接域

        // 只读源：单条 join SELECT（本库 MySQL 方言）。不 UPDATE/DELETE/INSERT 源。
        var dt = await scope.Ado.GetDataTableAsync(SourceJoinSql,
            new SugarParameter("@domain", domain));

        await _db.Ado.BeginTranAsync();
        try
        {
            // FULL REPLACE：先删本 tenant+domain 旧 std，再插新（态①全量替换，等价 MdpStdFullReplace）
            await _db.Ado.ExecuteCommandAsync(
                "DELETE FROM mdp_std_iqc_receipt_state WHERE tenant_id=@t AND domain=@d",
                new SugarParameter("@t", tenantId), new SugarParameter("@d", domain));

            var now = DateTime.Now;
            var batch = YitIdHelper.NextId().ToString();
            var rows = 0;
            foreach (System.Data.DataRow r in dt.Rows)
            {
                await _db.Ado.ExecuteCommandAsync(
                    """
                    INSERT INTO mdp_std_iqc_receipt_state
                      (id,tenant_id,domain,fbillno,qc_nbr,item_num,location,lot_serial,rct_nbr,pur_ord,pur_line,potype,
                       qty_ordered,received_cum_qty,returned_cum_qty,sample_qty,receipt_pending_qty,receivable_detail_count,
                       source_system,sync_batch_id,sync_time,create_time)
                    VALUES (@id,@t,@d,@fb,@qc,@item,@loc,@lot,@rct,@po,@line,@pt,
                            @ord,@rcv,@ret,@smp,@pend,@rdc,@src,@bat,@now,@now)
                    """,
                    new SugarParameter("@id", YitIdHelper.NextId()), new SugarParameter("@t", tenantId), new SugarParameter("@d", domain),
                    new SugarParameter("@fb", S(r, "fbillno")), new SugarParameter("@qc", S(r, "qc_nbr")),
                    new SugarParameter("@item", S(r, "item_num")), new SugarParameter("@loc", S(r, "location")),
                    new SugarParameter("@lot", S(r, "lot_serial")), new SugarParameter("@rct", S(r, "rct_nbr")),
                    new SugarParameter("@po", S(r, "pur_ord")), new SugarParameter("@line", I(r, "pur_line")),
                    new SugarParameter("@pt", S(r, "potype")), new SugarParameter("@ord", D(r, "qty_ordered")),
                    new SugarParameter("@rcv", D(r, "received_cum_qty")), new SugarParameter("@ret", D(r, "returned_cum_qty")),
                    new SugarParameter("@smp", D(r, "sample_qty")), new SugarParameter("@pend", D(r, "receipt_pending_qty")),
                    new SugarParameter("@rdc", I(r, "receivable_detail_count")), new SugarParameter("@src", sourceCode),
                    new SugarParameter("@bat", batch), new SugarParameter("@now", now));
                rows++;
            }
            await _db.Ado.CommitTranAsync();
            return rows;
        }
        catch
        {
            await _db.Ado.RollbackTranAsync();
            throw;
        }
    }

    private static string S(System.Data.DataRow r, string c) => r.Table.Columns.Contains(c) && r[c] != DBNull.Value ? Convert.ToString(r[c]) : "";
    private static int I(System.Data.DataRow r, string c) => r.Table.Columns.Contains(c) && r[c] != DBNull.Value ? Convert.ToInt32(r[c]) : 0;
    private static decimal D(System.Data.DataRow r, string c) => r.Table.Columns.Contains(c) && r[c] != DBNull.Value ? Convert.ToDecimal(r[c]) : 0m;

    /// <summary>源侧只读 join（本库 MySQL 方言）——严格 SELECT，无源写。</summary>
    private const string SourceJoinSql =
        """
        SELECT
          TRIM(IFNULL(a.FBILLNO,''))       AS fbillno,
          TRIM(IFNULL(a.lydjbh,''))        AS qc_nbr,
          TRIM(IFNULL(c.FMATERIALCFG,''))  AS item_num,
          TRIM(IFNULL(d.Location,''))      AS location,
          TRIM(IFNULL(d.LotSerial,''))     AS lot_serial,
          TRIM(IFNULL(d.Receiver,''))      AS rct_nbr,
          TRIM(IFNULL(d.OrdNbr,''))        AS pur_ord,
          IFNULL(d.OrdLine,0)              AS pur_line,
          TRIM(IFNULL(p.Potype,''))        AS potype,
          CAST(IFNULL(p.QtyOrded,0) AS DECIMAL(18,4))    AS qty_ordered,
          CAST(IFNULL(p.RctQty,0) AS DECIMAL(18,4))      AS received_cum_qty,
          CAST(IFNULL(p.QtyReturned,0) AS DECIMAL(18,4)) AS returned_cum_qty,
          CAST(IFNULL(a.lysl,0) AS DECIMAL(18,4))        AS sample_qty,
          CAST(IFNULL(p.ReceiptQty,0) AS DECIMAL(18,4))  AS receipt_pending_qty,
          (SELECT COUNT(1) FROM MissedPrint m
             WHERE m.CheckNo=a.lydjbh AND m.ItemNum=c.FMATERIALCFG AND m.Status='N' AND m.Type<>'Card') AS receivable_detail_count
        FROM qms_qcp_inspbill a
        LEFT JOIN qms_qcp_inspecapplyn b ON BINARY TRIM(IFNULL(a.lydjbh,'')) = BINARY TRIM(IFNULL(b.FBILLNO,''))
        LEFT JOIN qms_qcp_insappnentry c ON b.id = c.glid AND a.hid = c.id
        LEFT JOIN PurOrdRctDetail d ON d.Domain=@domain AND d.BarCode=a.lydjbh AND d.ItemNum=c.FMATERIALCFG
        LEFT JOIN PurOrdDetail p ON d.Domain=p.Domain AND d.OrdNbr=p.PurOrd AND d.OrdLine=p.Line
        WHERE TRIM(IFNULL(a.FBILLNO,'')) <> ''
        """;
}