using Xunit;

namespace Admin.NET.Plugin.AiDOP.Tests.DataPlatform;

public class MdpStandardUpsertContractTests
{
    [Fact]
    public void S1ShipStandardUpsert_RefreshesFactoryScope()
    {
        var source = File.ReadAllText(FindFile(
            "server", "Plugins", "Admin.NET.Plugin.AiDOP", "Order", "S1MdpSyncTransformService.cs"));

        Assert.True(Count(source, "factory_id=VALUES(factory_id)") >= 3);
        Assert.Contains("shipper_no=VALUES(shipper_no)", source);
        Assert.DoesNotContain("SELECT tenant_id AS TenantId, 1 AS FactoryId,\n                   'S1_L1_004'", source);
        Assert.DoesNotContain("SELECT tenant_id AS TenantId, 1 AS FactoryId,\n                   'S1_L2_013'", source);
        Assert.DoesNotContain("SELECT tenant_id AS TenantId, 1 AS FactoryId,\n                   'S1_L2_014'", source);
        Assert.DoesNotContain("SELECT tenant_id AS TenantId, 1 AS FactoryId,\n                   'S1_L2_015'", source);
    }

    /// <summary>
    /// mdp_std_so 的 closed 必须与 order_status 一起刷新。
    ///
    /// <para>两列派生自同一个源字段 <c>h.raw_data.$.closed</c>（分别在 :1057 / :1063）。
    /// 若只把 order_status 放进 ODKU，订单在源端关闭后同一行会分叉成
    /// <c>order_status='CLOSED'</c> 而 <c>closed=0</c> —— 而 <c>mdp_std_so.closed</c> 的
    /// 全仓唯一读取方 <c>S8OrderDeliveryDataProvider</c>（<c>IFNULL(s.closed,0)=0</c>）正是靠它
    /// 把已关闭订单排除出 Rule02 数据集。分叉的后果是已关单订单继续命中（假建单），
    /// 且已建异常无法因关单退出数据集（漏恢复）。</para>
    ///
    /// <para>断言限定在 <c>mdp_std_so</c> 这一条 INSERT 的 ODKU 区间内 —— 对整份源文件做
    /// Contains 会被别的表的 ODKU 蒙混过关。</para>
    /// </summary>
    [Fact]
    public void S1SoStandardUpsert_RefreshesClosedAlongsideOrderStatus()
    {
        var source = File.ReadAllText(FindFile(
            "server", "Plugins", "Admin.NET.Plugin.AiDOP", "Order", "S1MdpSyncTransformService.cs"));

        var odku = ExtractOnDuplicateKeyUpdate(source, "INSERT INTO mdp_std_so");

        Assert.Contains("closed=VALUES(closed)", odku);
        Assert.Contains("order_status=VALUES(order_status)", odku);

        // 补 closed 不得挤掉既有刷新列（deleted_flag 与 closed 成对被 Provider 消费）。
        Assert.Contains("deleted_flag=VALUES(deleted_flag)", odku);
    }

    [Fact]
    public void S3DeliveryScheduleUpsert_RefreshesLifecycleDates()
    {
        var source = File.ReadAllText(FindFile(
            "server", "Plugins", "Admin.NET.Plugin.AiDOP", "Supply", "S3MdpSyncTransformService.cs"));

        Assert.Contains("request_date=VALUES(request_date)", source);
        Assert.Contains("submit_date=VALUES(submit_date)", source);
        Assert.Contains("last_sent_date=VALUES(last_sent_date)", source);
        Assert.Contains("send_date=VALUES(send_date)", source);
        Assert.Contains("arrive_date=VALUES(arrive_date)", source);
        Assert.Contains("issued_qty=VALUES(issued_qty)", source);
        Assert.Contains("shortage_qty=VALUES(shortage_qty)", source);
        Assert.Contains("ready_status=VALUES(ready_status)", source);
        Assert.Contains("factory_id=VALUES(factory_id)", source);
    }

    [Fact]
    public void S3S4DashboardDerivedQueries_StayFactoryScoped()
    {
        var source = File.ReadAllText(FindFile(
            "server", "Plugins", "Admin.NET.Plugin.AiDOP", "Controllers", "AidopKanbanController.cs"));

        Assert.Contains("GetS3ReadinessSummaryAsync(long tenantId, long factoryId)", source);
        Assert.Contains("GetS3DeliverySummaryAsync(long tenantId, long factoryId)", source);
        Assert.True(Count(source, "COALESCE(NULLIF(factory_id,0),1)=@factoryId") >= 10);
    }

    [Fact]
    public void S4ProcurementCycle_UsesActualReceiptDate()
    {
        var source = File.ReadAllText(FindFile(
            "server", "Plugins", "Admin.NET.Plugin.AiDOP", "ProcurementExecution", "S4MdpSyncTransformService.cs"));

        Assert.Contains("FROM mdp_std_delivery_result", source);
        Assert.Contains("MAX(receipt_date) AS receipt_date", source);
        Assert.Contains("TIMESTAMPDIFF(DAY, ds.request_date, r.receipt_date)", source);
        Assert.DoesNotContain(
            "TIMESTAMPDIFF(DAY, ds.request_date, COALESCE(ds.submit_date, ds.last_sent_date))",
            source);
    }

    /// <summary>
    /// 注册表反查的比较对象必须限定到外层驱动表，不能写裸列名。
    ///
    /// <para><c>mdp_source_table_registry</c> 自己就有 <c>source_table</c> / <c>source_system</c> 两列，
    /// 而 MySQL 解析不带前缀的列名时内层作用域优先。于是
    /// <c>WHERE r.source_table = source_table</c> 绑成了 <c>r.source_table = r.source_table</c> —— 恒真，
    /// <c>LIMIT 1</c> 返回注册表的第一行。实测（aidopdev，只读）裸写法对每一行都返回
    /// <c>DOPDEMORQ_SQLSERVER</c>（id=1 的 InvTransHist），而正确关联的 <c>d.source_table</c>
    /// 对未登记的 srm_pr_main 返回 NULL，继而回落到该行自带的 AIDOP_NATIVE。</para>
    ///
    /// <para>后果不是字段填错就完事：<c>MdpNeutralSourceGate</c> 会按 SOURCE_MISMATCH 拒写，
    /// 数据被静默丢掉。多表语句还会因为外层 <c>NULLIF(source_system,'')</c> 两边都能匹配而直接报
    /// 「Column 'source_system' in field list is ambiguous」，整轮跑批 FAILED。</para>
    /// </summary>
    [Theory]
    [InlineData("Order", "S1MdpSyncTransformService.cs")]
    [InlineData("Production", "S2MdpSyncTransformService.cs")]
    [InlineData("Supply", "S3MdpSyncTransformService.cs")]
    [InlineData("ProcurementExecution", "S4MdpSyncTransformService.cs")]
    public void SourceRegistryLookup_CorrelatesToDrivingTable(string folder, string file)
    {
        var source = File.ReadAllText(FindFile(
            "server", "Plugins", "Admin.NET.Plugin.AiDOP", folder, file));

        var lookups = System.Text.RegularExpressions.Regex.Matches(
            source, @"mdp_source_table_registry r WHERE r\.source_table = (?<operand>\S+)");
        Assert.NotEmpty(lookups);

        foreach (var operand in lookups.Select(m => m.Groups["operand"].Value))
            Assert.True(operand.Contains('.') || operand.StartsWith('@'),
                $"{file}：注册表反查写成了裸列 `{operand}`，会恒真并取到注册表第一行");
    }

    /// <summary>
    /// 取出 <paramref name="insertMarker"/> 之后那一条 INSERT 的 ODKU 区间（到 raw string 结束符为止）。
    /// 用它把断言限定在目标表上——同一份源文件里有多条 INSERT，全文 Contains 判不出列属于哪张表。
    /// </summary>
    private static string ExtractOnDuplicateKeyUpdate(string source, string insertMarker)
    {
        var insertAt = source.IndexOf(insertMarker, StringComparison.Ordinal);
        Assert.True(insertAt >= 0, $"未找到 {insertMarker}");

        var odkuAt = source.IndexOf("ON DUPLICATE KEY UPDATE", insertAt, StringComparison.Ordinal);
        Assert.True(odkuAt > insertAt, $"{insertMarker} 之后未找到 ON DUPLICATE KEY UPDATE");

        var endAt = source.IndexOf("\"\"\"", odkuAt, StringComparison.Ordinal);
        Assert.True(endAt > odkuAt, $"{insertMarker} 的 ODKU 未正常收尾");

        return source[odkuAt..endAt];
    }

    private static int Count(string source, string token)
    {
        var count = 0;
        for (var index = 0; (index = source.IndexOf(token, index, StringComparison.Ordinal)) >= 0; index += token.Length)
            count++;
        return count;
    }

    private static string FindFile(params string[] parts)
    {
        var dir = new DirectoryInfo(AppContext.BaseDirectory);
        while (dir != null)
        {
            var candidate = Path.Combine(new[] { dir.FullName }.Concat(parts).ToArray());
            if (File.Exists(candidate)) return candidate;
            dir = dir.Parent;
        }
        throw new FileNotFoundException(string.Join("/", parts));
    }
}