using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;

var builder = Host.CreateApplicationBuilder(args);

// 关键：stdio 是 MCP 的通信管道，日志必须走 stderr，
// 打到 stdout 会污染协议消息
builder.Logging.AddConsole(o => o.LogToStandardErrorThreshold = LogLevel.Trace);

builder.Services
    .AddMcpServer()
    .WithStdioServerTransport()
    .WithToolsFromAssembly();

await builder.Build().RunAsync();

using System.ComponentModel;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.Data.Sqlite;
using ModelContextProtocol.Server;

[McpServerToolType]
public static class ExpenseQueryTools
{
    private static readonly string ConnectionString =
        Environment.GetEnvironmentVariable("EXPENSEFLOW_READONLY_CONN")
        ?? "Data Source=expenses.db;Mode=ReadOnly";

    private const int MaxRows = 200;

    [McpServerTool]
    [Description("""
        对报销库执行一条只读 SELECT 查询，返回前 200 行。
        只允许单条 SELECT 语句，禁止 INSERT/UPDATE/DELETE/DDL 和多语句。
        EmployeeId 会被脱敏，不要指望拿到完整员工编号。
        表结构见 describe_schema 工具。
        """)]
    public static string Query(
        [Description("一条 SELECT 语句，不要带分号结尾的多语句")] string sql)
    {
        var guard = Reject(sql);
        if (guard is not null) return $"❌ 拒绝执行：{guard}";

        using var conn = new SqliteConnection(ConnectionString);
        conn.Open();

        using var cmd = conn.CreateCommand();
        cmd.CommandText = $"SELECT * FROM ({sql.TrimEnd(';', ' ')}) LIMIT {MaxRows}";

        using var reader = cmd.ExecuteReader();
        var sb = new StringBuilder();

        var cols = Enumerable.Range(0, reader.FieldCount)
                             .Select(reader.GetName).ToArray();
        sb.AppendLine(string.Join(" | ", cols));
        sb.AppendLine(string.Join(" | ", cols.Select(_ => "---")));

        var rows = 0;
        while (reader.Read())
        {
            var values = Enumerable.Range(0, reader.FieldCount)
                .Select(i => Mask(cols[i], reader.IsDBNull(i) ? "" : reader.GetValue(i)?.ToString() ?? ""));
            sb.AppendLine(string.Join(" | ", values));
            rows++;
        }

        sb.AppendLine();
        sb.AppendLine(rows == MaxRows ? $"（已截断到 {MaxRows} 行）" : $"（共 {rows} 行）");
        return sb.ToString();
    }

    [McpServerTool]
    [Description("返回报销库的表结构，包含每个表的列名和类型。")]
    public static string DescribeSchema()
    {
        using var conn = new SqliteConnection(ConnectionString);
        conn.Open();
        using var cmd = conn.CreateCommand();
        cmd.CommandText =
            "SELECT name, sql FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'";

        using var reader = cmd.ExecuteReader();
        var sb = new StringBuilder();
        while (reader.Read())
        {
            sb.AppendLine($"## {reader.GetString(0)}");
            sb.AppendLine("```sql");
            sb.AppendLine(reader.GetString(1));
            sb.AppendLine("```");
            sb.AppendLine();
        }
        return sb.ToString();
    }

    private static string? Reject(string sql)
    {
        var s = sql.Trim();

        if (!s.StartsWith("SELECT", StringComparison.OrdinalIgnoreCase)
            && !s.StartsWith("WITH", StringComparison.OrdinalIgnoreCase))
            return "只允许 SELECT 或 WITH 开头的查询";

        if (s.TrimEnd(';', ' ').Contains(';'))
            return "不允许多条语句";

        var banned = new[] { "insert", "update", "delete", "drop", "alter",
                             "create", "attach", "pragma", "vacuum" };
        var lowered = s.ToLowerInvariant();
        foreach (var kw in banned)
            if (Regex.IsMatch(lowered, $@"\b{kw}\b"))
                return $"包含被禁止的关键字：{kw}";

        return null;
    }

    // 员工编号脱敏：E1024 → E1***
    private static string Mask(string column, string value) =>
        column.Equals("EmployeeId", StringComparison.OrdinalIgnoreCase) && value.Length > 2
            ? value[..2] + new string('*', value.Length - 2)
            : value;
}