🚀 PeopleWorks GPT MCP Server

using Infrastructure.Persistence.Contexts;
using Microsoft.EntityFrameworkCore;
using ModelContextProtocol.Server;
using PeopleworksGPT.MCP.Server.Services;
using System.ComponentModel;
using System.Text.Json;

namespace PeopleworksGPT.MCP.Server.Tools
{
    [McpServerToolType]
    public sealed class AuthenticationTool
    {
        private readonly ApplicationDbContext _context;
        private readonly SessionManager _sessionManager;

        public AuthenticationTool(ApplicationDbContext context, SessionManager sessionManager)
        {
            _context = context;
            _sessionManager = sessionManager;
        }

        [McpServerTool(Name = "AuthenticateAsync")]
        [Description("Authenticate user and receive session token for subsequent requests")]
        public async Task<string> AuthenticateAsync(
            [Description("Username")] string username,
            [Description("API key for authentication")] string apiKey)
        {
            try
            {
                // Find user by username and API key
                var user = await _context.ApplicationUsers
                    .FirstOrDefaultAsync(u => u.UserName == username 
                        && u.ApiKey == apiKey 
                        && u.IsActive 
                        && !u.Deleted);

                if (user == null)
                {
                    return JsonSerializer.Serialize(new
                    {
                        success = false,
                        error = "Invalid credentials"
                    });
                }

                // Update last login timestamp
                user.UpdatedDate = DateTime.Now;
                await _context.SaveChangesAsync();

                // Generate JWT token with 60-minute expiration
                var sessionToken = _sessionManager.GenerateJwtToken(user.Id, user.UserName!);

                return JsonSerializer.Serialize(new
                {
                    success = true,
                    session_token = sessionToken,
                    user_id = user.Id,
                    username = user.UserName,
                    expires_at = DateTime.UtcNow.AddMinutes(60).ToString("O")
                });
            }
            catch (Exception ex)
            {
                return JsonSerializer.Serialize(new
                {
                    success = false,
                    error = $"Authentication failed: {ex.Message}"
                });
            }
        }
    }
}

[McpServerTool(Name = "ExecuteQueryAsync")]
[Description("Execute a natural language query against a specific database connection")]
public async Task<string> ExecuteQueryAsync(
    [Description("Session token from authenticate()")] string sessionToken,
    [Description("Database connection ID from list_connections()")] long connectionId,
    [Description("Natural language query")] string query,
    [Description("Include database hints (default: true)")] bool includeHints = true,
    [Description("Additional context for filtering")] string? additionalContext = null,
    [Description("Security filter for multi-tenant isolation (JSON)")] string? securityFilter = null)
{
    var stopwatch = Stopwatch.StartNew();
    
    try
    {
        // 1. Validate JWT session token
        var (isValid, userId, username) = _sessionManager.ValidateToken(sessionToken);
        if (!isValid)
        {
            return JsonSerializer.Serialize(new { 
                success = false, 
                error = "Invalid or expired session token" 
            });
        }

        // 2. Verify user has access to this connection
        var connection = await _context.DatabaseConnectionSettings
            .FirstOrDefaultAsync(c => 
                c.Id == connectionId && 
                c.AvailableForUsers.Any(u => u.Id == userId) && 
                !c.Deleted && 
                !c.Disabled);

        if (connection == null)
        {
            return JsonSerializer.Serialize(new { 
                success = false, 
                error = "Connection not found or access denied" 
            });
        }

        // 3. Execute query through DatabaseGPT engine
        var (sql, results) = await _queryExecutionService.ExecuteNaturalLanguageQueryAsync(
            connection,
            query,
            includeHints,
            additionalContext,
            securityFilter);

        stopwatch.Stop();

        // 4. Log to audit trail
        await _auditLogger.LogQueryAsync(
            userId,
            connectionId,
            query,
            sql,
            (int)stopwatch.ElapsedMilliseconds,
            results.Count,
            success: true);

        // 5. Return structured results
        return JsonSerializer.Serialize(new
        {
            success = true,
            query_id = Guid.NewGuid().ToString(),
            original_query = query,
            generated_sql = sql,
            results,
            row_count = results.Count,
            execution_time_ms = stopwatch.ElapsedMilliseconds,
            connection_name = connection.DbName
        });
    }
    catch (Exception ex)
    {
        stopwatch.Stop();
        
        // Log failure to audit
        await _auditLogger.LogQueryAsync(
            userId, connectionId, query, null,
            (int)stopwatch.ElapsedMilliseconds, 0, 
            success: false, errorMessage: ex.Message);

        return JsonSerializer.Serialize(new
        {
            success = false,
            error = $"Query execution failed: {ex.Message}"
        });
    }
}

public class SessionManager
{
    private readonly IConfiguration _configuration;

    public string GenerateJwtToken(long userId, string username)
    {
        var secretKey = _configuration["JWT:SecretKey"] 
            ?? throw new InvalidOperationException("JWT SecretKey not configured");
        
        var securityKey = new SymmetricSecurityKey(
            Encoding.UTF8.GetBytes(secretKey));
        var credentials = new SigningCredentials(
            securityKey, SecurityAlgorithms.HmacSha256);

        var claims = new[]
        {
            new Claim(ClaimTypes.NameIdentifier, userId.ToString()),
            new Claim(ClaimTypes.Name, username),
            new Claim(JwtRegisteredClaimNames.Jti, Guid.NewGuid().ToString())
        };

        var token = new JwtSecurityToken(
            issuer: _configuration["JWT:Issuer"],
            audience: _configuration["JWT:Audience"],
            claims: claims,
            expires: DateTime.UtcNow.AddMinutes(60),
            signingCredentials: credentials
        );

        return new JwtSecurityTokenHandler().WriteToken(token);
    }

    public (bool IsValid, long UserId, string Username) ValidateToken(string token)
    {
        try
        {
            var tokenHandler = new JwtSecurityTokenHandler();
            var validationParameters = new TokenValidationParameters
            {
                ValidateIssuer = true,
                ValidateAudience = true,
                ValidateLifetime = true,
                ValidateIssuerSigningKey = true,
                ValidIssuer = _configuration["JWT:Issuer"],
                ValidAudience = _configuration["JWT:Audience"],
                IssuerSigningKey = new SymmetricSecurityKey(
                    Encoding.UTF8.GetBytes(_configuration["JWT:SecretKey"]!))
            };

            var principal = tokenHandler.ValidateToken(token, validationParameters, out _);
            var userId = long.Parse(principal.FindFirst(ClaimTypes.NameIdentifier)!.Value);
            var username = principal.FindFirst(ClaimTypes.Name)!.Value;

            return (true, userId, username);
        }
        catch
        {
            return (false, 0, string.Empty);
        }
    }
}

{
  "mcpServers": {
    "peopleworksgpt": {
      "command": "dotnet",
      "args": [
        "run",
        "--project",
        "C:\\Proyecto\\AI\\PeopleworksGPT\\PeopleworksGPT.MCP.Server\\PeopleworksGPT.MCP.Server.csproj"
      ],
      "env": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      }
    }
  }
}

{
  "mcpServers": {
    "peopleworksgpt": {
      "url": "https://mcp.peopleworks.com/mcp",
      "transport": "http",
      "headers": {
        "MCP-Protocol-Version": "2025-06-18"
      }
    }
  }
}

# Publish the application
dotnet publish -c Release -o C:\inetpub\PeopleworksGPT-MCP

# Create IIS Application Pool
New-WebAppPool -Name "PeopleworksGPT-MCP" -Force
Set-ItemProperty IIS:\AppPools\PeopleworksGPT-MCP `
    -Name managedRuntimeVersion -Value ""

# Create IIS Website
New-WebSite -Name "PeopleworksGPT-MCP" `
    -Port 5000 `
    -PhysicalPath "C:\inetpub\PeopleworksGPT-MCP" `
    -ApplicationPool "PeopleworksGPT-MCP" `
    -Force

# Configure web.config for InProcess hosting
$webConfig = @"
<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <system.webServer>
    <handlers>
      <add name="aspNetCore" path="*" verb="*" 
           modules="AspNetCoreModuleV2" 
           resourceType="Unspecified" />
    </handlers>
    <aspNetCore processPath="dotnet" 
                arguments=".\PeopleworksGPT.MCP.Server.dll" 
                stdoutLogEnabled="true" 
                stdoutLogFile=".\logs\stdout" 
                hostingModel="InProcess" />
  </system.webServer>
</configuration>
"@

Set-Content -Path "C:\inetpub\PeopleworksGPT-MCP\web.config" -Value $webConfig

Write-Host "Deployment complete! Server available at http://localhost:5000" -ForegroundColor Green

var builder = WebApplication.CreateBuilder(args);

// Configure Serilog for structured logging
builder.Host.UseSerilog();

// Add database context with SQL Server
builder.Services.AddDbContext<ApplicationDbContext>(options =>
{
    var connectionString = builder.Configuration.GetConnectionString("PeopleworksGPT");
    options.UseSqlServer(connectionString);
});

// Add Identity for user management
builder.Services.AddIdentity<ApplicationUser, IdentityRole<long>>()
    .AddEntityFrameworkStores<ApplicationDbContext>();

// Add CORS for HTTP transport
builder.Services.AddCors(options =>
{
    options.AddPolicy("MCPClients", policy =>
    {
        policy.AllowAnyOrigin()
              .AllowAnyHeader()
              .AllowAnyMethod();
    });
});

// Add PeopleWorks services (reuse existing infrastructure!)
builder.Services.AddScoped<ISettingService, SettingService>();
builder.Services.AddScoped<IPromptProvider, PromptProvider>();
builder.Services.AddScoped<IOpenAiCompatiblePwDatabaseGptClient, OpenAiCompatiblePwDatabaseGptClient>();

// Add MCP-specific services
builder.Services.AddScoped<SessionManager>();
builder.Services.AddScoped<AuditLogger>();
builder.Services.AddScoped<QueryExecutionService>();

// Add MCP Server with automatic tool discovery
builder.Services.AddMcpServer(options =>
{
    options.ServerInfo = new Implementation
    {
        Name = "PeopleWorksGPT",
        Version = "1.0.0"
    };
})
.WithHttpTransport(httpOptions =>
{
    httpOptions.Stateless = true; // No session state required
})
.WithToolsFromAssembly(); // Auto-discover all [McpServerTool] methods

var app = builder.Build();

// Enable CORS
app.UseCors("MCPClients");

// Map MCP endpoints
app.MapMcp();

await app.RunAsync();