AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Controllers/MdpApiOutController.cs

using System.Text.Json;

namespace Admin.NET.Plugin.AiDOP.Controllers;

/// <summary>
/// S9 API_OUT：受控只读 KPI 出站（字段白名单 + 访问日志）。禁止任意 SQL。
/// </summary>
[ApiDescriptionSettings(Order = 330, Description = "MDP API_OUT 受控只读")]
[Route("api/aidop/out")]
[AllowAnonymous]
[NonUnify]
public class MdpApiOutController : IDynamicApiController, ITransient
{
    private static readonly Dictionary<string, string> ResourceSql = new(StringComparer.OrdinalIgnoreCase)
    {
        ["kpi_l1_day"] = "SELECT metric_code, metric_value, biz_date, tenant_id, factory_id FROM ado_s9_kpi_value_l1_day WHERE tenant_id=@tid ORDER BY biz_date DESC LIMIT @limit",
        ["kpi_l2_day"] = "SELECT metric_code, metric_value, biz_date, tenant_id, factory_id FROM ado_s9_kpi_value_l2_day WHERE tenant_id=@tid ORDER BY biz_date DESC LIMIT @limit",
        ["kpi_l3_day"] = "SELECT metric_code, metric_value, biz_date, tenant_id, factory_id FROM ado_s9_kpi_value_l3_day WHERE tenant_id=@tid ORDER BY biz_date DESC LIMIT @limit",
        ["kpi_l4_day"] = "SELECT metric_code, metric_value, biz_date, tenant_id, factory_id FROM ado_s9_kpi_value_l4_day WHERE tenant_id=@tid ORDER BY biz_date DESC LIMIT @limit",
    };

    private readonly ISqlSugarClient _db;

    public MdpApiOutController(ISqlSugarClient db) => _db = db;

    [DisplayName("API_OUT 资源列表")]
    [HttpGet("resources")]
    public object ListResources() => new { resources = ResourceSql.Keys.OrderBy(x => x).ToArray() };

    [DisplayName("API_OUT 查询")]
    [HttpGet("{resourceCode}")]
    public async Task<object> Query(
        string resourceCode,
        [FromQuery] long tenantId = 0,
        [FromQuery] int limit = 100,
        [FromQuery] string? caller = null)
    {
        await EnsureLogTableAsync();
        if (!ResourceSql.TryGetValue(resourceCode, out var sql))
        {
            await WriteLogAsync(tenantId, caller, resourceCode, null, 0, false, "resource not in whitelist");
            return new { ok = false, message = "resource not allowed" };
        }

        limit = Math.Clamp(limit, 1, 500);
        try
        {
            var rows = await _db.Ado.SqlQueryAsync<dynamic>(sql,
                new SugarParameter("@tid", tenantId),
                new SugarParameter("@limit", limit));
            var list = rows?.ToList() ?? [];
            await WriteLogAsync(tenantId, caller, resourceCode,
                JsonSerializer.Serialize(new { tenantId, limit }), list.Count, true, null);
            return new { ok = true, resource = resourceCode, count = list.Count, data = list };
        }
        catch (Exception ex)
        {
            await WriteLogAsync(tenantId, caller, resourceCode,
                JsonSerializer.Serialize(new { tenantId, limit }), 0, false, ex.Message);
            return new { ok = false, message = ex.Message };
        }
    }

    private async Task EnsureLogTableAsync()
    {
        await _db.Ado.ExecuteCommandAsync("""
            CREATE TABLE IF NOT EXISTS mdp_api_out_access_log (
              id bigint NOT NULL AUTO_INCREMENT,
              tenant_id bigint NOT NULL DEFAULT 0,
              caller varchar(100) DEFAULT NULL,
              resource_code varchar(100) NOT NULL,
              query_json text,
              row_count int DEFAULT 0,
              success tinyint NOT NULL DEFAULT 1,
              message varchar(500) DEFAULT NULL,
              create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
              PRIMARY KEY (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
            """);
    }

    private Task WriteLogAsync(long tenantId, string? caller, string resource, string? queryJson, int rowCount, bool success, string? message) =>
        _db.Ado.ExecuteCommandAsync(
            """
            INSERT INTO mdp_api_out_access_log
              (tenant_id, caller, resource_code, query_json, row_count, success, message, create_time)
            VALUES (@tid, @caller, @res, @q, @cnt, @ok, @msg, NOW())
            """,
            new SugarParameter("@tid", tenantId),
            new SugarParameter("@caller", caller ?? ""),
            new SugarParameter("@res", resource),
            new SugarParameter("@q", queryJson),
            new SugarParameter("@cnt", rowCount),
            new SugarParameter("@ok", success ? 1 : 0),
            new SugarParameter("@msg", message != null && message.Length > 480 ? message[..480] : message));
}