<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net8.0</TargetFramework>
<ImplicitUsings>enable</ImplicitUsings>
<Nullable>enable</Nullable>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Microsoft.Data.Sqlite.Core" Version="9.0.9" />
<PackageReference Include="Microsoft.Extensions.Hosting" Version="9.0.6" />
<PackageReference Include="ModelContextProtocol" Version="0.4.0-preview.1" />
<PackageReference Include="SendGrid" Version="9.29.3" />
<PackageReference Include="SQLitePCLRaw.bundle_e_sqlite3" Version="3.0.2" />
<PackageReference Include="System.Text.Json" Version="9.0.6" />
<PackageReference Include="EPPlus" Version="7.4.2" />
</ItemGroup>
</Project>
using Microsoft.Data.Sqlite;
public static class CompanyDocumentDatabaseInitializer
{
public static async Task InitializeDatabaseAsync(string dbPath)
{
var directory = Path.GetDirectoryName(dbPath);
if (!string.IsNullOrEmpty(directory))
{
Directory.CreateDirectory(directory);
}
using var connection = new SqliteConnection($"Data Source={dbPath}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
CREATE TABLE IF NOT EXISTS Documents (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
PersonId INTEGER NOT NULL,
Pesel TEXT NOT NULL,
DocumentType TEXT NOT NULL,
DocumentNumber TEXT NOT NULL,
Title TEXT NOT NULL,
Description TEXT,
FilePath TEXT,
CreatedAt TEXT NOT NULL,
ModifiedAt TEXT,
Status TEXT NOT NULL,
UNIQUE(DocumentNumber)
);
CREATE INDEX IF NOT EXISTS idx_documents_pesel ON Documents(Pesel);
CREATE INDEX IF NOT EXISTS idx_documents_personid ON Documents(PersonId);
CREATE INDEX IF NOT EXISTS idx_documents_docnumber ON Documents(DocumentNumber);
";
await command.ExecuteNonQueryAsync();
// Delete all existing data before seeding
command.CommandText = "DELETE FROM Documents";
await command.ExecuteNonQueryAsync();
// Reset autoincrement counter
command.CommandText = "DELETE FROM sqlite_sequence WHERE name='Documents'";
await command.ExecuteNonQueryAsync();
// Always seed sample data
await SeedSampleDataAsync(connection);
}
private static async Task SeedSampleDataAsync(SqliteConnection connection)
{
var command = connection.CreateCommand();
command.CommandText = @"
INSERT INTO Documents (PersonId, Pesel, DocumentType, DocumentNumber, Title, Description, FilePath, CreatedAt, Status)
VALUES
-- PersonId 1001
(1001, '90010112345', 'Invoice', 'INV-0001', 'Invoice for Services', 'Professional services rendered', '/docs/inv001.pdf', '2024-01-15', 'Active'),
(1001, '90010112345', 'Contract', 'CNT-0001', 'Employment Contract', 'Full-time employment agreement', '/docs/cnt001.pdf', '2024-01-10', 'Active'),
(1001, '90010112345', 'Receipt', 'RCT-0001', 'Office Supplies Receipt', 'Purchased stationery and printer ink', '/docs/rct001.pdf', '2024-02-05', 'Active'),
-- PersonId 1002
(1002, '85050567890', 'Invoice', 'INV-0002', 'Product Purchase Invoice', 'Hardware equipment', '/docs/inv002.pdf', '2024-02-20', 'Active'),
(1002, '85050567890', 'Report', 'RPT-0001', 'Annual Financial Report', 'Year 2023 summary', '/docs/rpt001.pdf', '2024-03-01', 'Active'),
(1002, '85050567890', 'Agreement', 'AGR-0001', 'NDA with Vendor', 'Non-disclosure agreement for project X', '/docs/agr001.pdf', '2024-03-10', 'Active'),
(1002, '85050567890', 'License', 'LIC-0001', 'Software License', 'Annual subscription for DesignPro', '/docs/lic001.pdf', '2024-04-15', 'Active'),
-- PersonId 1003
(1003, '92120398765', 'Certificate', 'CERT-0001', 'Training Certificate', 'Completed professional training', '/docs/cert001.pdf', '2024-03-15', 'Active'),
(1003, '92120398765', 'Application', 'APP-0001', 'Loan Application', 'Personal loan request', '/docs/app001.pdf', '2024-04-01', 'Pending'),
(1003, '92120398765', 'Warranty', 'WRR-0001', 'Product Warranty', '2-year warranty for laptop', '/docs/wrr001.pdf', '2024-05-20', 'Active'),
-- PersonId 1004
(1004, '88071545678', 'Invoice', 'INV-0003', 'Consulting Invoice', 'IT consulting services', '/docs/inv003.pdf', '2024-05-10', 'Active'),
(1004, '88071545678', 'Proposal', 'PRO-0001', 'Project Proposal', 'Proposal for system upgrade', '/docs/pro001.pdf', '2024-06-05', 'Draft'),
(1004, '88071545678', 'Certificate', 'CERT-0002', 'Safety Training', 'Workplace safety certification', '/docs/cert002.pdf', '2024-06-15', 'Active'),
(1004, '88071545678', 'Receipt', 'RCT-0002', 'Travel Expenses', 'Hotel and transport receipts', '/docs/rct002.pdf', '2024-07-01', 'Active'),
-- PersonId 1005
(1005, '95032012345', 'Contract', 'CNT-0002', 'Freelance Contract', '6-month freelance agreement', '/docs/cnt002.pdf', '2024-07-10', 'Active'),
(1005, '95032012345', 'Invoice', 'INV-0004', 'Freelance Invoice', 'Monthly services invoice', '/docs/inv004.pdf', '2024-08-01', 'Active'),
(1005, '95032012345', 'Report', 'RPT-0002', 'Quarterly Report', 'Q2 financial performance', '/docs/rpt002.pdf', '2024-08-15', 'Active'),
(1005, '95032012345', 'Agreement', 'AGR-0002', 'Partnership Agreement', 'Joint venture with Company Y', '/docs/agr002.pdf', '2024-09-01', 'Active'),
-- PersonId 1006
(1006, '83112578901', 'Certificate', 'CERT-0003', 'First Aid Certificate', 'First aid training completion', '/docs/cert003.pdf', '2024-09-10', 'Active'),
(1006, '83112578901', 'Invoice', 'INV-0005', 'Maintenance Invoice', 'Office maintenance services', '/docs/inv005.pdf', '2024-10-05', 'Active'),
(1006, '83112578901', 'Receipt', 'RCT-0003', 'Office Lunch', 'Team lunch receipt', '/docs/rct003.pdf', '2024-10-15', 'Active'),
(1006, '83112578901', 'License', 'LIC-0002', 'Cloud Storage License', 'Enterprise cloud storage plan', '/docs/lic002.pdf', '2024-11-01', 'Active'),
-- PersonId 1007
(1007, '91041834567', 'Application', 'APP-0002', 'Grant Application', 'Research grant proposal', '/docs/app002.pdf', '2024-11-10', 'Pending'),
(1007, '91041834567', 'Report', 'RPT-0003', 'Research Report', 'Annual research findings', '/docs/rpt003.pdf', '2024-12-01', 'Active'),
(1007, '91041834567', 'Warranty', 'WRR-0002', 'Equipment Warranty', '3-year warranty for lab equipment', '/docs/wrr002.pdf', '2024-12-15', 'Active'),
-- PersonId 1008
(1008, '86093078912', 'Proposal', 'PRO-0002', 'Marketing Proposal', 'Digital marketing campaign plan', '/docs/pro002.pdf', '2025-01-10', 'Draft'),
(1008, '86093078912', 'Invoice', 'INV-0006', 'Advertising Invoice', 'Online ad campaign invoice', '/docs/inv006.pdf', '2025-01-20', 'Active'),
(1008, '86093078912', 'Certificate', 'CERT-0004', 'Marketing Certification', 'Advanced digital marketing course', '/docs/cert004.pdf', '2025-02-05', 'Active'),
-- PersonId 1009
(1009, '93062245678', 'Contract', 'CNT-0003', 'Service Contract', 'Annual IT support contract', '/docs/cnt003.pdf', '2025-02-15', 'Active'),
(1009, '93062245678', 'Agreement', 'AGR-0003', 'Service Level Agreement', 'SLA for IT services', '/docs/agr003.pdf', '2025-03-01', 'Active'),
(1009, '93062245678', 'Receipt', 'RCT-0004', 'Software Purchase', 'New software licenses', '/docs/rct004.pdf', '2025-03-10', 'Active'),
-- PersonId 1010
(1010, '89081412345', 'Invoice', 'INV-0007', 'Consulting Invoice', 'Business consulting services', '/docs/inv007.pdf', '2025-04-01', 'Active'),
(1010, '89081412345', 'Report', 'RPT-0004', 'Audit Report', 'Annual financial audit', '/docs/rpt004.pdf', '2025-04-15', 'Active'),
(1010, '89081412345', 'License', 'LIC-0003', 'Security Software License', 'Enterprise security suite', '/docs/lic003.pdf', '2025-05-01', 'Active'),
-- PersonId 1011
(1011, '87021965432', 'Diploma', 'DIP-0001', 'University Diploma', 'Bachelor of Science in Computer Science', '/docs/dip001.pdf', '2025-05-15', 'Active'),
(1011, '87021965432', 'Resume', 'RES-0001', 'Professional Resume', 'Updated resume for job applications', '/docs/res001.pdf', '2025-05-20', 'Active'),
(1011, '87021965432', 'Reference', 'REF-0001', 'Employment Reference', 'Reference letter from previous employer', '/docs/ref001.pdf', '2025-05-25', 'Active'),
-- PersonId 1012
(1012, '94091178901', 'Insurance', 'INS-0001', 'Health Insurance Policy', 'Annual health insurance coverage', '/docs/ins001.pdf', '2025-06-01', 'Active'),
(1012, '94091178901', 'Claim', 'CLM-0001', 'Insurance Claim', 'Claim for medical expenses', '/docs/clm001.pdf', '2025-06-10', 'Pending'),
(1012, '94091178901', 'Policy', 'POL-0001', 'Privacy Policy', 'Company privacy policy document', '/docs/pol001.pdf', '2025-06-15', 'Active'),
-- PersonId 1013
(1013, '82102432165', 'Manual', 'MAN-0001', 'User Manual', 'Software user guide', '/docs/man001.pdf', '2025-07-01', 'Active'),
(1013, '82102432165', 'Guide', 'GDE-0001', 'Installation Guide', 'Step-by-step installation instructions', '/docs/gde001.pdf', '2025-07-10', 'Active'),
(1013, '82102432165', 'Form', 'FRM-0001', 'Feedback Form', 'Customer feedback collection form', '/docs/frm001.pdf', '2025-07-15', 'Active'),
-- PersonId 1014
(1014, '96011745678', 'Transcript', 'TRN-0001', 'Academic Transcript', 'Official university transcript', '/docs/trn001.pdf', '2025-08-01', 'Active'),
(1014, '96011745678', 'Letter', 'LTR-0001', 'Recommendation Letter', 'Recommendation for graduate school', '/docs/ltr001.pdf', '2025-08-10', 'Active'),
(1014, '96011745678', 'Statement', 'STM-0001', 'Bank Statement', 'Monthly bank statement', '/docs/stm001.pdf', '2025-08-15', 'Active'),
-- PersonId 1015
(1015, '84053098765', 'Permit', 'PRM-0001', 'Work Permit', 'Temporary work permit document', '/docs/prm001.pdf', '2025-09-01', 'Active'),
(1015, '84053098765', 'Ticket', 'TKT-0001', 'Support Ticket', 'IT support request and resolution', '/docs/tkt001.pdf', '2025-09-10', 'Closed'),
(1015, '84053098765', 'Survey', 'SRV-0001', 'Customer Survey', 'Annual customer satisfaction survey', '/docs/srv001.pdf', '2025-09-15', 'Active'),
-- PersonId 1016
(1016, '90120532165', 'Memo', 'MEM-0001', 'Internal Memo', 'Company-wide policy update memo', '/docs/mem001.pdf', '2025-10-01', 'Active'),
(1016, '90120532165', 'Minutes', 'MIN-0001', 'Meeting Minutes', 'Minutes from board meeting', '/docs/min001.pdf', '2025-10-10', 'Active'),
(1016, '90120532165', 'Newsletter', 'NWS-0001', 'Company Newsletter', 'Monthly internal newsletter', '/docs/nws001.pdf', '2025-10-15', 'Active'),
-- PersonId 1017
(1017, '81031865432', 'Budget', 'BGT-0001', 'Project Budget', 'Budget proposal for new project', '/docs/bgt001.pdf', '2025-11-01', 'Draft'),
(1017, '81031865432', 'Schedule', 'SCH-0001', 'Project Schedule', 'Timeline for project delivery', '/docs/sch001.pdf', '2025-11-10', 'Active'),
(1017, '81031865432', 'Checklist', 'CHK-0001', 'Onboarding Checklist', 'New employee onboarding tasks', '/docs/chk001.pdf', '2025-11-15', 'Active'),
-- PersonId 1018
(1018, '97072298765', 'Presentation', 'PRE-0001', 'Sales Presentation', 'Quarterly sales performance presentation', '/docs/pre001.pdf', '2025-12-01', 'Active'),
(1018, '97072298765', 'Brochure', 'BRH-0001', 'Product Brochure', 'Marketing brochure for new product', '/docs/brh001.pdf', '2025-12-10', 'Active'),
(1018, '97072298765', 'Catalog', 'CTG-0001', 'Product Catalog', 'Updated product catalog for 2026', '/docs/ctg001.pdf', '2025-12-15', 'Active'),
-- PersonId 1019
(1019, '05280129353', 'Report', 'RPT-0005', 'Financial Report Q1', 'Detailed financial report for Q1 2025', '/docs/rpt005.pdf', '2025-01-15', 'Active'),
(1019, '05280129353', 'Report', 'RPT-0006', 'Financial Report Q2', 'Detailed financial report for Q2 2025', '/docs/rpt006.pdf', '2025-04-15', 'Active'),
(1019, '05280129353', 'Report', 'RPT-0007', 'Financial Report Q3', 'Detailed financial report for Q3 2025', '/docs/rpt007.pdf', '2025-07-15', 'Active'),
(1019, '05280129353', 'Report', 'RPT-0008', 'Financial Report Q4', 'Detailed financial report for Q4 2025', '/docs/rpt008.pdf', '2025-10-15', 'Active'),
-- PersonId 1020
(1020, '08261437865', 'Invoice', 'INV-0008', 'Invoice January', 'Invoice for January services', '/docs/inv008.pdf', '2025-01-31', 'Active'),
(1020, '08261437865', 'Invoice', 'INV-0009', 'Invoice February', 'Invoice for February services', '/docs/inv009.pdf', '2025-02-28', 'Active'),
(1020, '08261437865', 'Invoice', 'INV-0010', 'Invoice March', 'Invoice for March services', '/docs/inv010.pdf', '2025-03-31', 'Active'),
(1020, '08261437865', 'Invoice', 'INV-0011', 'Invoice April', 'Invoice for April services', '/docs/inv011.pdf', '2025-04-30', 'Active'),
(1020, '08261437865', 'Invoice', 'INV-0012', 'Invoice May', 'Invoice for May services', '/docs/inv012.pdf', '2025-05-31', 'Active'),
(1020, '08261437865', 'Invoice', 'INV-0013', 'Invoice June', 'Invoice for June services', '/docs/inv013.pdf', '2025-06-30', 'Active'),
-- PersonId 1021
(1021, '99010758267', 'Memo', 'MEM-0002', 'HR Memo', 'HR policy update memo', '/docs/mem002.pdf', '2025-01-10', 'Active'),
(1021, '99010758267', 'Memo', 'MEM-0003', 'Office Memo', 'Office closure announcement', '/docs/mem003.pdf', '2025-05-10', 'Active'),
(1021, '99010758267', 'Memo', 'MEM-0004', 'Safety Memo', 'Workplace safety updates', '/docs/mem004.pdf', '2025-09-10', 'Active'),
-- PersonId 1022
(1022, '66112435932', 'Contract', 'CTR-0001', 'Client Contract A', 'Contract agreement with Client A', '/docs/ctr001.pdf', '2025-02-01', 'Active'),
(1022, '66112435932', 'Contract', 'CTR-0002', 'Client Contract B', 'Contract agreement with Client B', '/docs/ctr002.pdf', '2025-03-01', 'Active'),
(1022, '66112435932', 'Contract', 'CTR-0003', 'Vendor Contract', 'Contract agreement with Vendor X', '/docs/ctr003.pdf', '2025-06-01', 'Active'),
-- PersonId 1023
(1023, '84101192381', 'Policy', 'PLC-0001', 'Privacy Policy', 'Updated company privacy policy', '/docs/plc001.pdf', '2025-01-20', 'Active'),
(1023, '84101192381', 'Policy', 'PLC-0002', 'Security Policy', 'Updated IT security policy', '/docs/plc002.pdf', '2025-04-20', 'Active'),
(1023, '84101192381', 'Policy', 'PLC-0003', 'Leave Policy', 'Employee leave policy update', '/docs/plc003.pdf', '2025-08-20', 'Active'),
-- PersonId 1024
(1024, '70032189288', 'Form', 'FRM-0002', 'Leave Application Form', 'Form for applying employee leave', '/docs/frm002.pdf', '2025-01-05', 'Active'),
(1024, '70032189288', 'Form', 'FRM-0003', 'Expense Form', 'Form for submitting expenses', '/docs/frm003.pdf', '2025-03-05', 'Active'),
(1024, '70032189288', 'Form', 'FRM-0004', 'Feedback Form', 'Customer feedback submission form', '/docs/frm004.pdf', '2025-06-05', 'Active'),
-- PersonId 1025
(1025, '77092655919', 'Letter', 'LTR-0002', 'Offer Letter', 'Job offer letter for new hire', '/docs/ltr002.pdf', '2025-02-15', 'Active'),
(1025, '77092655919', 'Letter', 'LTR-0003', 'Resignation Letter', 'Formal resignation letter', '/docs/ltr003.pdf', '2025-05-15', 'Active'),
(1025, '77092655919', 'Letter', 'LTR-0004', 'Recommendation Letter', 'Letter of recommendation', '/docs/ltr004.pdf', '2025-09-15', 'Active'),
-- PersonId 1026
(1026, '76072625775', 'Manual', 'MNL-0001', 'User Manual', 'User manual for product A', '/docs/mnl001.pdf', '2025-02-28', 'Active'),
(1026, '76072625775', 'Manual', 'MNL-0002', 'Installation Guide', 'Installation manual for product B', '/docs/mnl002.pdf', '2025-06-28', 'Active'),
(1026, '76072625775', 'Manual', 'MNL-0003', 'Training Manual', 'Internal training documentation', '/docs/mnl003.pdf', '2025-10-28', 'Active');
";
await command.ExecuteNonQueryAsync();
}
}
public partial class CompanyDocument
{
public int Id { get; set; }
public int PersonId { get; set; }
public string Pesel { get; set; } = string.Empty;
public string DocumentType { get; set; } = string.Empty;
public string DocumentNumber { get; set; } = string.Empty;
public string Title { get; set; } = string.Empty;
public string Description { get; set; } = string.Empty;
public string FilePath { get; set; } = string.Empty;
public DateTime CreatedAt { get; set; }
public DateTime? ModifiedAt { get; set; }
public string Status { get; set; } = string.Empty;
}
using MCPServerDocuments.Database;
using MCPServerDocuments.DataClasses;
using Microsoft.Data.Sqlite;
namespace MyMPCServer.Database;
public class CompanyDocumentsRepository
{
private readonly DatabaseConfig dbConfig;
public CompanyDocumentsRepository(DatabaseConfig dbConfig)
{
this.dbConfig = dbConfig;
}
public async Task<List<CompanyDocument>> GetDocumentsByPeselAsync(string pesel)
{
var documents = new List<CompanyDocument>();
using var connection = new SqliteConnection($"Data Source={dbConfig.ConnectionString}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
SELECT Id, PersonId, Pesel, DocumentType, DocumentNumber, Title,
Description, FilePath, CreatedAt, ModifiedAt, Status
FROM Documents
WHERE Pesel = $pesel
ORDER BY CreatedAt DESC";
command.Parameters.AddWithValue("$pesel", pesel);
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
documents.Add(MapDocument(reader));
}
return documents;
}
public async Task<List<CompanyDocument>> GetDocumentsByPersonIdAsync(int personId)
{
var documents = new List<CompanyDocument>();
using var connection = new SqliteConnection($"Data Source={dbConfig.ConnectionString}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
SELECT Id, PersonId, Pesel, DocumentType, DocumentNumber, Title,
Description, FilePath, CreatedAt, ModifiedAt, Status
FROM Documents
WHERE PersonId = $personId
ORDER BY CreatedAt DESC";
command.Parameters.AddWithValue("$personId", personId);
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
documents.Add(MapDocument(reader));
}
return documents;
}
public async Task<int> AddDocumentAsync(CompanyDocument document)
{
using var connection = new SqliteConnection($"Data Source={dbConfig.ConnectionString}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
INSERT INTO Documents (PersonId, Pesel, DocumentType, DocumentNumber, Title,
Description, FilePath, CreatedAt, Status)
VALUES ($personId, $pesel, $docType, $docNumber, $title, $description,
$filePath, $createdAt, $status);
SELECT last_insert_rowid();";
command.Parameters.AddWithValue("$personId", document.PersonId);
command.Parameters.AddWithValue("$pesel", document.Pesel);
command.Parameters.AddWithValue("$docType", document.DocumentType);
command.Parameters.AddWithValue("$docNumber", document.DocumentNumber);
command.Parameters.AddWithValue("$title", document.Title);
command.Parameters.AddWithValue("$description", document.Description);
command.Parameters.AddWithValue("$filePath", document.FilePath);
command.Parameters.AddWithValue("$createdAt", document.CreatedAt);
command.Parameters.AddWithValue("$status", document.Status);
var result = await command.ExecuteScalarAsync();
return Convert.ToInt32(result);
}
private CompanyDocument MapDocument(SqliteDataReader reader)
{
return new CompanyDocument
{
Id = reader.GetInt32(0),
PersonId = reader.GetInt32(1),
Pesel = reader.GetString(2),
DocumentType = reader.GetString(3),
DocumentNumber = reader.GetString(4),
Title = reader.GetString(5),
Description = reader.GetString(6),
FilePath = reader.GetString(7),
CreatedAt = reader.GetDateTime(8),
ModifiedAt = reader.IsDBNull(9) ? null : reader.GetDateTime(9),
Status = reader.GetString(10)
};
}
}
public partial class DatabaseConfig
{
public string ConnectionString { get; set; } = string.Empty;
}
[McpServerToolType]
public sealed class MCPServerDataExportTools
{
private readonly CompanyDocumentsRepository _repository;
public MCPServerDataExportTools(
CompanyDocumentsRepository repository
)
{
_repository = repository;
}
[McpServerTool, Description("Search for documents by PESEL number.")]
public async Task<string> SearchDocumentsByPesel(
[Description("PESEL number to search for")] string pesel)
{
try
{
var documents = await _repository.GetDocumentsByPeselAsync(pesel);
return JsonSerializer.Serialize(new SearchDocumentsByPeselResult
{
Success = true,
Message = $"Found {documents.Count} document(s) for PESEL: {pesel}",
Pesel = pesel,
DocumentCount = documents.Count,
Documents = documents,
Timestamp = DateTime.UtcNow
}, CompanyDocumentJsonContext.Default.SearchDocumentsByPeselResult);
}
catch (Exception ex)
{
return JsonSerializer.Serialize(new
{
success = false,
error = ex.Message,
timestamp = DateTime.UtcNow
}, CompanyDocumentJsonContext.Default.SearchDocumentsError);
}
}
[McpServerTool, Description("Search for documents by Person ID.")]
public async Task<string> SearchDocumentsByPersonId(
[Description("Person ID to search for")] int personId)
{
try
{
var documents = await _repository.GetDocumentsByPersonIdAsync(personId);
return JsonSerializer.Serialize(new SearchDocumentsByPersonIdResult
{
Success = true,
Message = $"Found {documents.Count} document(s) for Person ID: {personId}",
PersonId = personId,
DocumentCount = documents.Count,
Documents = documents,
Timestamp = DateTime.UtcNow
}, CompanyDocumentJsonContext.Default.SearchDocumentsByPersonIdResult);
}
catch (Exception ex)
{
return JsonSerializer.Serialize(new
{
success = false,
error = ex.Message,
timestamp = DateTime.UtcNow
}, CompanyDocumentJsonContext.Default.SearchDocumentsError);
}
}
}
// JSON serialization context
[JsonSerializable(typeof(SearchDocumentsByPeselResult))]
[JsonSerializable(typeof(SearchDocumentsError))]
[JsonSerializable(typeof(SearchDocumentsByPersonIdResult))]
public partial class CompanyDocumentJsonContext : JsonSerializerContext
{
}
public partial class SearchDocumentsByPeselResult
{
public bool Success { get; set; }
public string Message { get; set; } = string.Empty;
public string Pesel { get; set; } = string.Empty;
public int DocumentCount { get; set; }
public List<CompanyDocument> Documents { get; set; } = new();
public DateTime Timestamp { get; set; }
}
public partial class SearchDocumentsByPersonIdResult
{
public bool Success { get; set; }
public string Message { get; set; } = string.Empty;
public int PersonId { get; set; }
public int DocumentCount { get; set; }
public List<CompanyDocument> Documents { get; set; } = new();
public DateTime Timestamp { get; set; }
}
public partial class SearchDocumentsError
{
public bool Success { get; set; }
public string Error { get; set; } = string.Empty;
public DateTime Timestamp { get; set; }
}
using MCPServerDocuments;
using MCPServerDocuments.Database;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using MyMPCServer.Database;
using System.Text.Json.Serialization;
var builder = Host.CreateEmptyApplicationBuilder(settings: null);
builder.Services
.AddMcpServer()
.WithStdioServerTransport()
.WithTools<MCPServerDataExportTools>();
builder.Services.AddSingleton<CompanyDocumentsRepository>();
var defaultDbPath = Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),
"MCPServerDocuments",
"documents.db"
);
builder.Services.AddSingleton<DatabaseConfig>(provider => new DatabaseConfig
{
ConnectionString = Environment.GetEnvironmentVariable("DB_CONNECTION_STRING") ?? defaultDbPath
});
var dbConfig = builder.Services.BuildServiceProvider().GetRequiredService<DatabaseConfig>();
await CompanyDocumentDatabaseInitializer.InitializeDatabaseAsync(dbConfig.ConnectionString);
await builder.Build().RunAsync();
# Use the official .NET SDK image for building
FROM mcr.microsoft.com/dotnet/sdk:8.0 AS build
WORKDIR /src
# Copy project files
COPY *.csproj ./
RUN dotnet restore
# Copy remaining source code
COPY . ./
# Publish as self-contained executable for linux-x64
RUN dotnet publish -c Release -r linux-x64 --self-contained true -p:PublishSingleFile=true -o /app/publish
# Use minimal runtime image since we have self-contained exe
FROM mcr.microsoft.com/dotnet/runtime-deps:8.0
WORKDIR /app
# Copy published app
COPY --from=build /app/publish .
# Create directory for database
RUN mkdir -p /data
# Set environment variable for database path
ENV DB_CONNECTION_STRING=/data/documents.db
# Run the self-contained executable
ENTRYPOINT ["./MCPServerDocuments"]
bin/
obj/
*.db
*.db-shm
*.db-wal
data/
.vs/
.vscode/
*.user
.git/
.gitignore
README.md
Dockerfile
docker-compose.yml
cd "to project directory"
docker build -t mcp-server-documents:latest .
docker run --rm -i -v ${PWD}/data:/data mcp-server-documents:latest
cd "to MCPInspector directory"
npx @modelcontextprotocol/inspector dotnet run "MCPServerDocuments. csproj"
{
"mcpServers": {
"MCP_DOCKER": {
"command": "docker",
"args": ["mcp", "gateway", "run"],
"env": {
"LOCALAPPDATA": "C:\\Users\\Cezary\\AppData\\Local",
"ProgramData": "C:\\ProgramData",
"ProgramFiles": "C:\\Program Files"
}
},
"company-documents": {
"command": "docker",
"args": [
"run",
"--rm",
"-i",
"-v",
"C:\\Users\\Cezary\\mcp-documents-data:/data",
"mcp-server-documents:latest"
]
}
}
}
{
"mcpServers": {
"obsidian": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"-e",
"OBSIDIAN_HOST",
"-e",
"OBSIDIAN_API_KEY",
"mcp/obsidian"
],
"env": {
"OBSIDIAN_HOST": "host.docker.internal",
"OBSIDIAN_API_KEY": ""
}
},
"youtube_transcript": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"mcp/youtube-transcript"
]
},
"company-documents": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"-v",
"C:\\Users\\Cezary\\mcp-documents-data:/data",
"mcp-server-documents:latest"
]
}
}
}
{
"mcpServers": {
"MCP_DOCKER": {
"command": "docker",
"args": [
"mcp",
"gateway",
"run"
],
"env": {
"LOCALAPPDATA": "C:\\Users\\Cezary\\AppData\\Local",
"ProgramData": "C:\\ProgramData",
"ProgramFiles": "C:\\Program Files"
}
},
"company-documents": {
"command": "docker",
"args": [
"run",
"--rm",
"-i",
"-v",
"C:\\Users\\Cezary\\mcp-documents-data:/data",
"mcp-server-documents:latest"
]
}
}
}
{
"servers": {
"github": {
"url": "https://api.githubcopilot.com/mcp/"
},
"company-documents": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"-v",
"C:\\Users\\Cezary\\mcp-documents-data:/data",
"mcp-server-documents:latest"
]
}
},
"inputs": []
}
name: Local Agent
version: 1.0.0
schema: v1
models:
- name: Autodetect
provider: ollama
model: AUTODETECT
mcpServers:
- name: company-documents
command: docker
args:
- run
- -i
- --rm
- -v
- C:\Users\Cezary\mcp-documents-data:/data
- mcp-server-documents:latest
env: {}
using MCPServerDocuments.Database;
using MCPServerDocuments.DataClasses;
using Microsoft.Data.Sqlite;
namespace MyMPCServer.Database;
public class UserHistoryRepository
{
private readonly DatabaseConfig dbConfig;
public UserHistoryRepository(DatabaseConfig dbConfig)
{
this.dbConfig = dbConfig;
}
#region User Methods
public async Task<User?> GetUserByIdAsync(int userId)
{
using var connection = new SqliteConnection($"Data Source={dbConfig.ConnectionString}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
SELECT Id, UserName
FROM Users
WHERE Id = $userId";
command.Parameters.AddWithValue("$userId", userId);
using var reader = await command.ExecuteReaderAsync();
if (await reader.ReadAsync())
{
return MapUser(reader);
}
return null;
}
public async Task<User?> GetUserByNameAsync(string userName)
{
using var connection = new SqliteConnection($"Data Source={dbConfig.ConnectionString}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
SELECT Id, UserName
FROM Users
WHERE UserName = $userName";
command.Parameters.AddWithValue("$userName", userName);
using var reader = await command.ExecuteReaderAsync();
if (await reader.ReadAsync())
{
return MapUser(reader);
}
return null;
}
public async Task<List<User>> GetAllUsersAsync()
{
var users = new List<User>();
using var connection = new SqliteConnection($"Data Source={dbConfig.ConnectionString}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
SELECT Id, UserName
FROM Users
ORDER BY UserName";
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
users.Add(MapUser(reader));
}
return users;
}
public async Task<int> AddUserAsync(User user)
{
using var connection = new SqliteConnection($"Data Source={dbConfig.ConnectionString}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
INSERT INTO Users (UserName)
VALUES ($userName);
SELECT last_insert_rowid();";
command.Parameters.AddWithValue("$userName", user.UserName);
var result = await command.ExecuteScalarAsync();
return Convert.ToInt32(result);
}
#endregion
#region User History Methods
public async Task<List<UserHistoryUsingCompanyDocuments>> GetHistoryByUserIdAsync(int userId)
{
var history = new List<UserHistoryUsingCompanyDocuments>();
using var connection = new SqliteConnection($"Data Source={dbConfig.ConnectionString}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
SELECT Id, UserId, UserQuery, Action, DocumentId, CreatedAt
FROM UserHistoryUsingCompanyDocuments
WHERE UserId = $userId
ORDER BY CreatedAt DESC";
command.Parameters.AddWithValue("$userId", userId);
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
history.Add(MapUserHistory(reader));
}
return history;
}
public async Task<List<UserHistoryUsingCompanyDocuments>> GetHistoryByDocumentIdAsync(int documentId)
{
var history = new List<UserHistoryUsingCompanyDocuments>();
using var connection = new SqliteConnection($"Data Source={dbConfig.ConnectionString}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
SELECT Id, UserId, UserQuery, Action, DocumentId, CreatedAt
FROM UserHistoryUsingCompanyDocuments
WHERE DocumentId = $documentId
ORDER BY CreatedAt DESC";
command.Parameters.AddWithValue("$documentId", documentId);
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
history.Add(MapUserHistory(reader));
}
return history;
}
public async Task<List<UserHistoryUsingCompanyDocuments>> GetHistoryByActionAsync(string action)
{
var history = new List<UserHistoryUsingCompanyDocuments>();
using var connection = new SqliteConnection($"Data Source={dbConfig.ConnectionString}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
SELECT Id, UserId, UserQuery, Action, DocumentId, CreatedAt
FROM UserHistoryUsingCompanyDocuments
WHERE Action = $action
ORDER BY CreatedAt DESC";
command.Parameters.AddWithValue("$action", action);
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
history.Add(MapUserHistory(reader));
}
return history;
}
public async Task<int> AddHistoryAsync(UserHistoryUsingCompanyDocuments history)
{
using var connection = new SqliteConnection($"Data Source={dbConfig.ConnectionString}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
INSERT INTO UserHistoryUsingCompanyDocuments (UserId, UserQuery, Action, DocumentId, CreatedAt)
VALUES ($userId, $userQuery, $action, $documentId, $createdAt);
SELECT last_insert_rowid();";
command.Parameters.AddWithValue("$userId", history.UserId);
command.Parameters.AddWithValue("$userQuery", history.UserQuery);
command.Parameters.AddWithValue("$action", history.Action);
command.Parameters.AddWithValue("$documentId", history.DocumentId);
command.Parameters.AddWithValue("$createdAt", history.CreatedAt);
var result = await command.ExecuteScalarAsync();
return Convert.ToInt32(result);
}
#endregion
#region Joined Query Methods
public async Task<List<UserHistoryWithDetails>> GetHistoryWithDetailsByUserIdAsync(int userId)
{
var history = new List<UserHistoryWithDetails>();
using var connection = new SqliteConnection($"Data Source={dbConfig.ConnectionString}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
SELECT
h.Id as HistoryId,
h.UserId,
u.UserName,
h.UserQuery,
h.Action,
h.DocumentId,
d.Title as DocumentTitle,
d.DocumentType,
d.DocumentNumber,
h.CreatedAt
FROM UserHistoryUsingCompanyDocuments h
INNER JOIN Users u ON h.UserId = u.Id
INNER JOIN Documents d ON h.DocumentId = d.Id
WHERE h.UserId = $userId
ORDER BY h.CreatedAt DESC";
command.Parameters.AddWithValue("$userId", userId);
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
history.Add(MapUserHistoryWithDetails(reader));
}
return history;
}
public async Task<List<UserHistoryWithDetails>> GetHistoryWithDetailsByDocumentIdAsync(int documentId)
{
var history = new List<UserHistoryWithDetails>();
using var connection = new SqliteConnection($"Data Source={dbConfig.ConnectionString}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
SELECT
h.Id as HistoryId,
h.UserId,
u.UserName,
h.UserQuery,
h.Action,
h.DocumentId,
d.Title as DocumentTitle,
d.DocumentType,
d.DocumentNumber,
h.CreatedAt
FROM UserHistoryUsingCompanyDocuments h
INNER JOIN Users u ON h.UserId = u.Id
INNER JOIN Documents d ON h.DocumentId = d.Id
WHERE h.DocumentId = $documentId
ORDER BY h.CreatedAt DESC";
command.Parameters.AddWithValue("$documentId", documentId);
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
history.Add(MapUserHistoryWithDetails(reader));
}
return history;
}
public async Task<List<UserHistoryWithDetails>> GetAllHistoryWithDetailsAsync()
{
var history = new List<UserHistoryWithDetails>();
using var connection = new SqliteConnection($"Data Source={dbConfig.ConnectionString}");
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = @"
SELECT
h.Id as HistoryId,
h.UserId,
u.UserName,
h.UserQuery,
h.Action,
h.DocumentId,
d.Title as DocumentTitle,
d.DocumentType,
d.DocumentNumber,
h.CreatedAt
FROM UserHistoryUsingCompanyDocuments h
INNER JOIN Users u ON h.UserId = u.Id
INNER JOIN Documents d ON h.DocumentId = d.Id
ORDER BY h.CreatedAt DESC";
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
history.Add(MapUserHistoryWithDetails(reader));
}
return history;
}
#endregion
#region Mapping Methods
private User MapUser(SqliteDataReader reader)
{
return new User
{
Id = reader.GetInt32(0),
UserName = reader.GetString(1)
};
}
private UserHistoryUsingCompanyDocuments MapUserHistory(SqliteDataReader reader)
{
return new UserHistoryUsingCompanyDocuments
{
Id = reader.GetInt32(0),
UserId = reader.GetInt32(1),
UserQuery = reader.GetString(2),
Action = reader.GetString(3),
DocumentId = reader.GetInt32(4),
CreatedAt = reader.GetDateTime(5)
};
}
private UserHistoryWithDetails MapUserHistoryWithDetails(SqliteDataReader reader)
{
return new UserHistoryWithDetails
{
HistoryId = reader.GetInt32(0),
UserId = reader.GetInt32(1),
UserName = reader.GetString(2),
UserQuery = reader.GetString(3),
Action = reader.GetString(4),
DocumentId = reader.GetInt32(5),
DocumentTitle = reader.GetString(6),
DocumentType = reader.GetString(7),
DocumentNumber = reader.GetString(8),
CreatedAt = reader.GetDateTime(9)
};
}
#endregion
}
using Microsoft.Data.Sqlite;
namespace MCPServerDocuments.Database;
public static class DatabaseInitializer
{
public static async Task InitializeDatabaseAsync(string dbPath)
{
var directory = Path.GetDirectoryName(dbPath);
if (!string.IsNullOrEmpty(directory))
{
Directory.CreateDirectory(directory);
}
using var connection = new SqliteConnection($"Data Source={dbPath}");
await connection.OpenAsync();
// Create all tables
await CreateTablesAsync(connection);
// Clear existing data
await ClearDataAsync(connection);
// Seed sample data
await SeedSampleDataAsync(connection);
}
private static async Task CreateTablesAsync(SqliteConnection connection)
{
var command = connection.CreateCommand();
command.CommandText = @"
CREATE TABLE IF NOT EXISTS Users (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
UserName TEXT NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS Documents (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
PersonId INTEGER NOT NULL,
Pesel TEXT NOT NULL,
DocumentType TEXT NOT NULL,
DocumentNumber TEXT NOT NULL,
Title TEXT NOT NULL,
Description TEXT,
FilePath TEXT,
CreatedAt TEXT NOT NULL,
ModifiedAt TEXT,
Status TEXT NOT NULL,
UNIQUE(DocumentNumber)
);
CREATE TABLE IF NOT EXISTS UserHistoryUsingCompanyDocuments (
Id INTEGER PRIMARY KEY AUTOINCREMENT,
UserId INTEGER NOT NULL,
UserQuery TEXT NOT NULL,
Action TEXT NOT NULL,
DocumentId INTEGER NOT NULL,
CreatedAt TEXT NOT NULL,
FOREIGN KEY (UserId) REFERENCES Users(Id),
FOREIGN KEY (DocumentId) REFERENCES Documents(Id)
);
CREATE INDEX IF NOT EXISTS idx_users_username ON Users(UserName);
CREATE INDEX IF NOT EXISTS idx_documents_pesel ON Documents(Pesel);
CREATE INDEX IF NOT EXISTS idx_documents_personid ON Documents(PersonId);
CREATE INDEX IF NOT EXISTS idx_documents_docnumber ON Documents(DocumentNumber);
CREATE INDEX IF NOT EXISTS idx_user_history_userid ON UserHistoryUsingCompanyDocuments(UserId);
CREATE INDEX IF NOT EXISTS idx_user_history_documentid ON UserHistoryUsingCompanyDocuments(DocumentId);
CREATE INDEX IF NOT EXISTS idx_user_history_action ON UserHistoryUsingCompanyDocuments(Action);
";
await command.ExecuteNonQueryAsync();
}
private static async Task ClearDataAsync(SqliteConnection connection)
{
var command = connection.CreateCommand();
// Delete data in correct order (respecting foreign keys)
command.CommandText = "DELETE FROM UserHistoryUsingCompanyDocuments";
await command.ExecuteNonQueryAsync();
command.CommandText = "DELETE FROM Users";
await command.ExecuteNonQueryAsync();
command.CommandText = "DELETE FROM Documents";
await command.ExecuteNonQueryAsync();
// Reset autoincrement counters
command.CommandText = @"
DELETE FROM sqlite_sequence WHERE name IN ('Users', 'Documents', 'UserHistoryUsingCompanyDocuments')
";
await command.ExecuteNonQueryAsync();
}
private static async Task SeedSampleDataAsync(SqliteConnection connection)
{
var command = connection.CreateCommand();
// Insert sample users
command.CommandText = @"
INSERT INTO Users (UserName)
VALUES
('john.doe'),
('jane.smith'),
('admin.user'),
('bob.johnson'),
('alice.williams');
";
await command.ExecuteNonQueryAsync();
// Insert sample documents
command.CommandText = @"
INSERT INTO Documents (PersonId, Pesel, DocumentType, DocumentNumber, Title, Description, FilePath, CreatedAt, Status)
VALUES
-- PersonId 1001
(1001, '90010112345', 'Invoice', 'INV-0001', 'Invoice for Services', 'Professional services rendered', '/docs/inv001.pdf', '2024-01-15', 'Active'),
(1001, '90010112345', 'Contract', 'CNT-0001', 'Employment Contract', 'Full-time employment agreement', '/docs/cnt001.pdf', '2024-01-10', 'Active'),
(1001, '90010112345', 'Receipt', 'RCT-0001', 'Office Supplies Receipt', 'Purchased stationery and printer ink', '/docs/rct001.pdf', '2024-02-05', 'Active'),
-- PersonId 1002
(1002, '85050567890', 'Invoice', 'INV-0002', 'Product Purchase Invoice', 'Hardware equipment', '/docs/inv002.pdf', '2024-02-20', 'Active'),
(1002, '85050567890', 'Report', 'RPT-0001', 'Annual Financial Report', 'Year 2023 summary', '/docs/rpt001.pdf', '2024-03-01', 'Active'),
(1002, '85050567890', 'Agreement', 'AGR-0001', 'NDA with Vendor', 'Non-disclosure agreement for project X', '/docs/agr001.pdf', '2024-03-10', 'Active'),
(1002, '85050567890', 'License', 'LIC-0001', 'Software License', 'Annual subscription for DesignPro', '/docs/lic001.pdf', '2024-04-15', 'Active'),
-- PersonId 1003
(1003, '92120398765', 'Certificate', 'CERT-0001', 'Training Certificate', 'Completed professional training', '/docs/cert001.pdf', '2024-03-15', 'Active'),
(1003, '92120398765', 'Application', 'APP-0001', 'Loan Application', 'Personal loan request', '/docs/app001.pdf', '2024-04-01', 'Pending'),
(1003, '92120398765', 'Warranty', 'WRR-0001', 'Product Warranty', '2-year warranty for laptop', '/docs/wrr001.pdf', '2024-05-20', 'Active'),
-- PersonId 1004
(1004, '88071545678', 'Invoice', 'INV-0003', 'Consulting Invoice', 'IT consulting services', '/docs/inv003.pdf', '2024-05-10', 'Active'),
(1004, '88071545678', 'Proposal', 'PRO-0001', 'Project Proposal', 'Proposal for system upgrade', '/docs/pro001.pdf', '2024-06-05', 'Draft'),
(1004, '88071545678', 'Certificate', 'CERT-0002', 'Safety Training', 'Workplace safety certification', '/docs/cert002.pdf', '2024-06-15', 'Active'),
(1004, '88071545678', 'Receipt', 'RCT-0002', 'Travel Expenses', 'Hotel and transport receipts', '/docs/rct002.pdf', '2024-07-01', 'Active'),
-- PersonId 1005
(1005, '95032012345', 'Contract', 'CNT-0002', 'Freelance Contract', '6-month freelance agreement', '/docs/cnt002.pdf', '2024-07-10', 'Active'),
(1005, '95032012345', 'Invoice', 'INV-0004', 'Freelance Invoice', 'Monthly services invoice', '/docs/inv004.pdf', '2024-08-01', 'Active'),
(1005, '95032012345', 'Report', 'RPT-0002', 'Quarterly Report', 'Q2 financial performance', '/docs/rpt002.pdf', '2024-08-15', 'Active'),
(1005, '95032012345', 'Agreement', 'AGR-0002', 'Partnership Agreement', 'Joint venture with Company Y', '/docs/agr002.pdf', '2024-09-01', 'Active'),
-- PersonId 1006
(1006, '83112578901', 'Certificate', 'CERT-0003', 'First Aid Certificate', 'First aid training completion', '/docs/cert003.pdf', '2024-09-10', 'Active'),
(1006, '83112578901', 'Invoice', 'INV-0005', 'Maintenance Invoice', 'Office maintenance services', '/docs/inv005.pdf', '2024-10-05', 'Active'),
(1006, '83112578901', 'Receipt', 'RCT-0003', 'Office Lunch', 'Team lunch receipt', '/docs/rct003.pdf', '2024-10-15', 'Active'),
(1006, '83112578901', 'License', 'LIC-0002', 'Cloud Storage License', 'Enterprise cloud storage plan', '/docs/lic002.pdf', '2024-11-01', 'Active'),
-- PersonId 1007
(1007, '91041834567', 'Application', 'APP-0002', 'Grant Application', 'Research grant proposal', '/docs/app002.pdf', '2024-11-10', 'Pending'),
(1007, '91041834567', 'Report', 'RPT-0003', 'Research Report', 'Annual research findings', '/docs/rpt003.pdf', '2024-12-01', 'Active'),
(1007, '91041834567', 'Warranty', 'WRR-0002', 'Equipment Warranty', '3-year warranty for lab equipment', '/docs/wrr002.pdf', '2024-12-15', 'Active'),
-- PersonId 1008
(1008, '86093078912', 'Proposal', 'PRO-0002', 'Marketing Proposal', 'Digital marketing campaign plan', '/docs/pro002.pdf', '2025-01-10', 'Draft'),
(1008, '86093078912', 'Invoice', 'INV-0006', 'Advertising Invoice', 'Online ad campaign invoice', '/docs/inv006.pdf', '2025-01-20', 'Active'),
(1008, '86093078912', 'Certificate', 'CERT-0004', 'Marketing Certification', 'Advanced digital marketing course', '/docs/cert004.pdf', '2025-02-05', 'Active'),
-- PersonId 1009
(1009, '93062245678', 'Contract', 'CNT-0003', 'Service Contract', 'Annual IT support contract', '/docs/cnt003.pdf', '2025-02-15', 'Active'),
(1009, '93062245678', 'Agreement', 'AGR-0003', 'Service Level Agreement', 'SLA for IT services', '/docs/agr003.pdf', '2025-03-01', 'Active'),
(1009, '93062245678', 'Receipt', 'RCT-0004', 'Software Purchase', 'New software licenses', '/docs/rct004.pdf', '2025-03-10', 'Active'),
-- PersonId 1010
(1010, '89081412345', 'Invoice', 'INV-0007', 'Consulting Invoice', 'Business consulting services', '/docs/inv007.pdf', '2025-04-01', 'Active'),
(1010, '89081412345', 'Report', 'RPT-0004', 'Audit Report', 'Annual financial audit', '/docs/rpt004.pdf', '2025-04-15', 'Active'),
(1010, '89081412345', 'License', 'LIC-0003', 'Security Software License', 'Enterprise security suite', '/docs/lic003.pdf', '2025-05-01', 'Active'),
-- PersonId 1011
(1011, '87021965432', 'Diploma', 'DIP-0001', 'University Diploma', 'Bachelor of Science in Computer Science', '/docs/dip001.pdf', '2025-05-15', 'Active'),
(1011, '87021965432', 'Resume', 'RES-0001', 'Professional Resume', 'Updated resume for job applications', '/docs/res001.pdf', '2025-05-20', 'Active'),
(1011, '87021965432', 'Reference', 'REF-0001', 'Employment Reference', 'Reference letter from previous employer', '/docs/ref001.pdf', '2025-05-25', 'Active'),
-- PersonId 1012
(1012, '94091178901', 'Insurance', 'INS-0001', 'Health Insurance Policy', 'Annual health insurance coverage', '/docs/ins001.pdf', '2025-06-01', 'Active'),
(1012, '94091178901', 'Claim', 'CLM-0001', 'Insurance Claim', 'Claim for medical expenses', '/docs/clm001.pdf', '2025-06-10', 'Pending'),
(1012, '94091178901', 'Policy', 'POL-0001', 'Privacy Policy', 'Company privacy policy document', '/docs/pol001.pdf', '2025-06-15', 'Active'),
-- PersonId 1013
(1013, '82102432165', 'Manual', 'MAN-0001', 'User Manual', 'Software user guide', '/docs/man001.pdf', '2025-07-01', 'Active'),
(1013, '82102432165', 'Guide', 'GDE-0001', 'Installation Guide', 'Step-by-step installation instructions', '/docs/gde001.pdf', '2025-07-10', 'Active'),
(1013, '82102432165', 'Form', 'FRM-0001', 'Feedback Form', 'Customer feedback collection form', '/docs/frm001.pdf', '2025-07-15', 'Active'),
-- PersonId 1014
(1014, '96011745678', 'Transcript', 'TRN-0001', 'Academic Transcript', 'Official university transcript', '/docs/trn001.pdf', '2025-08-01', 'Active'),
(1014, '96011745678', 'Letter', 'LTR-0001', 'Recommendation Letter', 'Recommendation for graduate school', '/docs/ltr001.pdf', '2025-08-10', 'Active'),
(1014, '96011745678', 'Statement', 'STM-0001', 'Bank Statement', 'Monthly bank statement', '/docs/stm001.pdf', '2025-08-15', 'Active'),
-- PersonId 1015
(1015, '84053098765', 'Permit', 'PRM-0001', 'Work Permit', 'Temporary work permit document', '/docs/prm001.pdf', '2025-09-01', 'Active'),
(1015, '84053098765', 'Ticket', 'TKT-0001', 'Support Ticket', 'IT support request and resolution', '/docs/tkt001.pdf', '2025-09-10', 'Closed'),
(1015, '84053098765', 'Survey', 'SRV-0001', 'Customer Survey', 'Annual customer satisfaction survey', '/docs/srv001.pdf', '2025-09-15', 'Active'),
-- PersonId 1016
(1016, '90120532165', 'Memo', 'MEM-0001', 'Internal Memo', 'Company-wide policy update memo', '/docs/mem001.pdf', '2025-10-01', 'Active'),
(1016, '90120532165', 'Minutes', 'MIN-0001', 'Meeting Minutes', 'Minutes from board meeting', '/docs/min001.pdf', '2025-10-10', 'Active'),
(1016, '90120532165', 'Newsletter', 'NWS-0001', 'Company Newsletter', 'Monthly internal newsletter', '/docs/nws001.pdf', '2025-10-15', 'Active'),
-- PersonId 1017
(1017, '81031865432', 'Budget', 'BGT-0001', 'Project Budget', 'Budget proposal for new project', '/docs/bgt001.pdf', '2025-11-01', 'Draft'),
(1017, '81031865432', 'Schedule', 'SCH-0001', 'Project Schedule', 'Timeline for project delivery', '/docs/sch001.pdf', '2025-11-10', 'Active'),
(1017, '81031865432', 'Checklist', 'CHK-0001', 'Onboarding Checklist', 'New employee onboarding tasks', '/docs/chk001.pdf', '2025-11-15', 'Active'),
-- PersonId 1018
(1018, '97072298765', 'Presentation', 'PRE-0001', 'Sales Presentation', 'Quarterly sales performance presentation', '/docs/pre001.pdf', '2025-12-01', 'Active'),
(1018, '97072298765', 'Brochure', 'BRH-0001', 'Product Brochure', 'Marketing brochure for new product', '/docs/brh001.pdf', '2025-12-10', 'Active'),
(1018, '97072298765', 'Catalog', 'CTG-0001', 'Product Catalog', 'Updated product catalog for 2026', '/docs/ctg001.pdf', '2025-12-15', 'Active'),
-- PersonId 1019
(1019, '05280129353', 'Report', 'RPT-0005', 'Financial Report Q1', 'Detailed financial report for Q1 2025', '/docs/rpt005.pdf', '2025-01-15', 'Active'),
(1019, '05280129353', 'Report', 'RPT-0006', 'Financial Report Q2', 'Detailed financial report for Q2 2025', '/docs/rpt006.pdf', '2025-04-15', 'Active'),
(1019, '05280129353', 'Report', 'RPT-0007', 'Financial Report Q3', 'Detailed financial report for Q3 2025', '/docs/rpt007.pdf', '2025-07-15', 'Active'),
(1019, '05280129353', 'Report', 'RPT-0008', 'Financial Report Q4', 'Detailed financial report for Q4 2025', '/docs/rpt008.pdf', '2025-10-15', 'Active'),
-- PersonId 1020
(1020, '08261437865', 'Invoice', 'INV-0008', 'Invoice January', 'Invoice for January services', '/docs/inv008.pdf', '2025-01-31', 'Active'),
(1020, '08261437865', 'Invoice', 'INV-0009', 'Invoice February', 'Invoice for February services', '/docs/inv009.pdf', '2025-02-28', 'Active'),
(1020, '08261437865', 'Invoice', 'INV-0010', 'Invoice March', 'Invoice for March services', '/docs/inv010.pdf', '2025-03-31', 'Active'),
(1020, '08261437865', 'Invoice', 'INV-0011', 'Invoice April', 'Invoice for April services', '/docs/inv011.pdf', '2025-04-30', 'Active'),
(1020, '08261437865', 'Invoice', 'INV-0012', 'Invoice May', 'Invoice for May services', '/docs/inv012.pdf', '2025-05-31', 'Active'),
(1020, '08261437865', 'Invoice', 'INV-0013', 'Invoice June', 'Invoice for June services', '/docs/inv013.pdf', '2025-06-30', 'Active'),
-- PersonId 1021
(1021, '99010758267', 'Memo', 'MEM-0002', 'HR Memo', 'HR policy update memo', '/docs/mem002.pdf', '2025-01-10', 'Active'),
(1021, '99010758267', 'Memo', 'MEM-0003', 'Office Memo', 'Office closure announcement', '/docs/mem003.pdf', '2025-05-10', 'Active'),
(1021, '99010758267', 'Memo', 'MEM-0004', 'Safety Memo', 'Workplace safety updates', '/docs/mem004.pdf', '2025-09-10', 'Active'),
-- PersonId 1022
(1022, '66112435932', 'Contract', 'CTR-0001', 'Client Contract A', 'Contract agreement with Client A', '/docs/ctr001.pdf', '2025-02-01', 'Active'),
(1022, '66112435932', 'Contract', 'CTR-0002', 'Client Contract B', 'Contract agreement with Client B', '/docs/ctr002.pdf', '2025-03-01', 'Active'),
(1022, '66112435932', 'Contract', 'CTR-0003', 'Vendor Contract', 'Contract agreement with Vendor X', '/docs/ctr003.pdf', '2025-06-01', 'Active'),
-- PersonId 1023
(1023, '84101192381', 'Policy', 'PLC-0001', 'Privacy Policy', 'Updated company privacy policy', '/docs/plc001.pdf', '2025-01-20', 'Active'),
(1023, '84101192381', 'Policy', 'PLC-0002', 'Security Policy', 'Updated IT security policy', '/docs/plc002.pdf', '2025-04-20', 'Active'),
(1023, '84101192381', 'Policy', 'PLC-0003', 'Leave Policy', 'Employee leave policy update', '/docs/plc003.pdf', '2025-08-20', 'Active'),
-- PersonId 1024
(1024, '70032189288', 'Form', 'FRM-0002', 'Leave Application Form', 'Form for applying employee leave', '/docs/frm002.pdf', '2025-01-05', 'Active'),
(1024, '70032189288', 'Form', 'FRM-0003', 'Expense Form', 'Form for submitting expenses', '/docs/frm003.pdf', '2025-03-05', 'Active'),
(1024, '70032189288', 'Form', 'FRM-0004', 'Feedback Form', 'Customer feedback submission form', '/docs/frm004.pdf', '2025-06-05', 'Active'),
-- PersonId 1025
(1025, '77092655919', 'Letter', 'LTR-0002', 'Offer Letter', 'Job offer letter for new hire', '/docs/ltr002.pdf', '2025-02-15', 'Active'),
(1025, '77092655919', 'Letter', 'LTR-0003', 'Resignation Letter', 'Formal resignation letter', '/docs/ltr003.pdf', '2025-05-15', 'Active'),
(1025, '77092655919', 'Letter', 'LTR-0004', 'Recommendation Letter', 'Letter of recommendation', '/docs/ltr004.pdf', '2025-09-15', 'Active'),
-- PersonId 1026
(1026, '76072625775', 'Manual', 'MNL-0001', 'User Manual', 'User manual for product A', '/docs/mnl001.pdf', '2025-02-28', 'Active'),
(1026, '76072625775', 'Manual', 'MNL-0002', 'Installation Guide', 'Installation manual for product B', '/docs/mnl002.pdf', '2025-06-28', 'Active'),
(1026, '76072625775', 'Manual', 'MNL-0003', 'Training Manual', 'Internal training documentation', '/docs/mnl003.pdf', '2025-10-28', 'Active');
";
await command.ExecuteNonQueryAsync();
// Insert sample user history
command.CommandText = @"
INSERT INTO UserHistoryUsingCompanyDocuments (UserId, UserQuery, Action, DocumentId, CreatedAt)
VALUES
-- User 1 (john.doe) activity
(1, '90010112345', 'acceptedSearch', 1, '2024-01-16 10:30:00'),
(1, '90010112345', 'Click', 1, '2024-01-16 10:31:00'),
(1, 'INV-2024-001', 'Read', 1, '2024-01-16 10:32:00'),
(1, '90010112345', 'Click', 2, '2024-01-16 10:35:00'),
(1, 'Contract', 'Download', 2, '2024-01-16 10:36:00'),
-- User 2 (jane.smith) activity
(2, '85050567890', 'acceptedSearch', 3, '2024-02-21 14:15:00'),
(2, 'INV-2024-002', 'Click', 3, '2024-02-21 14:16:00'),
(2, 'Product Purchase Invoice', 'Read', 3, '2024-02-21 14:17:00'),
(2, '85050567890', 'acceptedSearch', 4, '2024-03-02 09:00:00'),
(2, 'RPT-2024-001', 'use RAG', 4, '2024-03-02 09:05:00'),
-- User 3 (admin.user) activity
(3, '92120398765', 'acceptedSearch', 5, '2024-03-16 11:20:00'),
(3, 'CERT-2024-001', 'Click', 5, '2024-03-16 11:21:00'),
(3, 'Training Certificate', 'Download', 5, '2024-03-16 11:22:00'),
(3, '88071545678', 'acceptedSearch', 6, '2024-05-11 08:45:00'),
(3, 'INV-2024-003', 'Read', 6, '2024-05-11 08:46:00'),
-- User 4 (bob.johnson) activity
(4, '95032012345', 'acceptedSearch', 10, '2024-07-11 13:30:00'),
(4, 'CNT-2024-002', 'Click', 10, '2024-07-11 13:31:00'),
(4, 'Freelance Contract', 'use RAG', 10, '2024-07-11 13:35:00'),
(4, '95032012345', 'acceptedSearch', 11, '2024-08-02 10:00:00'),
(4, 'INV-2024-004', 'Download', 11, '2024-08-02 10:05:00'),
-- User 5 (alice.williams) activity
(5, '83112578901', 'acceptedSearch', 14, '2024-09-11 15:20:00'),
(5, 'CERT-2024-003', 'Click', 14, '2024-09-11 15:21:00'),
(5, 'First Aid Certificate', 'Read', 14, '2024-09-11 15:22:00'),
(5, '05280129353', 'acceptedSearch', 58, '2025-01-16 09:00:00'),
(5, 'RPT-2025-001', 'use RAG', 58, '2025-01-16 09:10:00'),
-- Additional mixed activity
(1, '08261437865', 'acceptedSearch', 62, '2025-02-01 11:00:00'),
(1, 'INV-2025-001', 'Click', 62, '2025-02-01 11:01:00'),
(2, '99010758267', 'acceptedSearch', 68, '2025-01-11 12:00:00'),
(2, 'MEM-2025-001', 'Read', 68, '2025-01-11 12:05:00'),
(3, '66112435932', 'acceptedSearch', 71, '2025-02-02 14:30:00'),
(3, 'CTR-2025-001', 'Download', 71, '2025-02-02 14:35:00');
";
await command.ExecuteNonQueryAsync();
}
}
// Initialize database using the properly built service provider
var dbConfig = app.Services.GetRequiredService();
await DatabaseInitializer.InitializeDatabaseAsync(dbConfig.ConnectionString);
using MCPServerDocuments.Database.Document;
using MCPServerDocuments.DataClasses;
using MCPServerDocuments.Tools;
using ModelContextProtocol.Server;
using MyMPCServer.Database;
using System.ComponentModel;
using System.Text.Json;
namespace MCPServerDocuments;
[McpServerToolType]
public sealed class MCPServerDataExportTools
{
private readonly CompanyDocumentsRepository _repository;
private readonly UserHistoryRepository _historyRepository;
public MCPServerDataExportTools(
CompanyDocumentsRepository repository,
UserHistoryRepository historyRepository)
{
_repository = repository;
_historyRepository = historyRepository;
}
…………….
[McpServerTool, Description("Get all user history with full details including user names and document information.")]
public async Task<string> GetAllUserHistoryWithDetails()
{
try
{
var historyDetails = await _historyRepository.GetAllHistoryWithDetailsAsync();
return JsonSerializer.Serialize(new GetAllUserHistoryResult
{
Success = true,
Message = $"Found {historyDetails.Count} history record(s)",
RecordCount = historyDetails.Count,
HistoryRecords = historyDetails,
Timestamp = DateTime.UtcNow
}, UserHistoryJsonContext.Default.GetAllUserHistoryResult);
}
catch (Exception ex)
{
return JsonSerializer.Serialize(new UserHistoryError
{
Success = false,
Error = ex.Message,
Timestamp = DateTime.UtcNow
}, UserHistoryJsonContext.Default.UserHistoryError);
}
}
}
public partial class GetAllUserHistoryResult
{
public bool Success { get; set; }
public string Message { get; set; } = string.Empty;
public int RecordCount { get; set; }
public List<UserHistoryWithDetails> HistoryRecords { get; set; } = new();
public DateTime Timestamp { get; set; }
}
public partial class UserHistoryError
{
public bool Success { get; set; }
public string Error { get; set; } = string.Empty;
public DateTime Timestamp { get; set; }
}
using MCPServerDocuments.DataClasses;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Drawing;
namespace MCPServerDocuments.Tools;
public class ExcelService
{
public async Task<byte[]> SaveDocumentsAsExcelAsync(List<CompanyDocument> documents, string fileNamePrefix)
{
try
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using var package = new ExcelPackage();
var worksheet = package.Workbook.Worksheets.Add("Document Export");
// Headers
worksheet.Cells[1, 1].Value = "ID";
worksheet.Cells[1, 2].Value = "Person ID";
worksheet.Cells[1, 3].Value = "PESEL";
worksheet.Cells[1, 4].Value = "Document Type";
worksheet.Cells[1, 5].Value = "Document Number";
worksheet.Cells[1, 6].Value = "Title";
worksheet.Cells[1, 7].Value = "Description";
worksheet.Cells[1, 8].Value = "File Path";
worksheet.Cells[1, 9].Value = "Created At";
worksheet.Cells[1, 10].Value = "Modified At";
worksheet.Cells[1, 11].Value = "Status";
// Data rows
for (int i = 0; i < documents.Count; i++)
{
var row = i + 2;
worksheet.Cells[row, 1].Value = documents[i].Id;
worksheet.Cells[row, 2].Value = documents[i].PersonId;
worksheet.Cells[row, 3].Value = documents[i].Pesel;
worksheet.Cells[row, 4].Value = documents[i].DocumentType;
worksheet.Cells[row, 5].Value = documents[i].DocumentNumber;
worksheet.Cells[row, 6].Value = documents[i].Title;
worksheet.Cells[row, 7].Value = documents[i].Description;
worksheet.Cells[row, 8].Value = documents[i].FilePath;
worksheet.Cells[row, 9].Value = documents[i].CreatedAt.ToString("yyyy-MM-dd HH:mm:ss");
worksheet.Cells[row, 10].Value = documents[i].ModifiedAt?.ToString("yyyy-MM-dd HH:mm:ss") ?? "";
worksheet.Cells[row, 11].Value = documents[i].Status;
}
FormatWorksheet(worksheet, 11, documents.Count);
// Save to memory
await using var stream = new MemoryStream();
await package.SaveAsAsync(stream);
return stream.ToArray();
}
catch (Exception ex)
{
throw new Exception($"Failed to create Excel file in memory: {ex.Message}", ex);
}
}
private void FormatWorksheet(ExcelWorksheet worksheet, int columnCount, int rowCount)
{
// Format header row
using (var range = worksheet.Cells[1, 1, 1, columnCount])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
range.Style.Border.BorderAround(ExcelBorderStyle.Thin);
}
// Auto-fit columns
for (int col = 1; col <= columnCount; col++)
{
worksheet.Column(col).AutoFit();
}
// Format data rows with borders
if (rowCount > 0)
{
using (var range = worksheet.Cells[2, 1, rowCount + 1, columnCount])
{
range.Style.Border.BorderAround(ExcelBorderStyle.Thin);
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
}
}
}
using Microsoft.Extensions.Logging;
using System.Net.Http.Headers;
using System.Text;
namespace MCPServerDocuments.Tools;
public class MailgunEmailService
{
private readonly MailgunConfig config;
private readonly ILogger<MailgunEmailService> logger;
private readonly HttpClient httpClient;
public MailgunEmailService(MailgunConfig config, ILogger<MailgunEmailService> logger)
{
this.config = config ?? throw new ArgumentNullException(nameof(config));
this.logger = logger ?? throw new ArgumentNullException(nameof(logger));
if (string.IsNullOrWhiteSpace(config.ApiKey))
throw new InvalidOperationException("Mailgun API key is not configured.");
if (string.IsNullOrWhiteSpace(config.Domain))
throw new InvalidOperationException("Mailgun domain is not configured.");
httpClient = new HttpClient();
var authToken = Convert.ToBase64String(Encoding.ASCII.GetBytes($"api:{config.ApiKey}"));
httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", authToken);
}
/// <summary>
/// Sends an email with an Excel file attachment via Mailgun.
/// </summary>
/// <param name="filePath">Path to the Excel file to attach</param>
/// <param name="recipient">Recipient email (optional; uses default if null)</param>
/// <param name="subject">Email subject (optional)</param>
/// <param name="messageBody">Email body text (optional)</param>
/// <returns>Mailgun Message-ID if successful</returns>
public async Task<string> SendExcelEmailAsync(
string filePath,
string? recipient = null,
string? subject = null,
string? messageBody = null)
{
if (string.IsNullOrWhiteSpace(filePath))
throw new ArgumentException("File path cannot be null or empty.", nameof(filePath));
if (!File.Exists(filePath))
throw new FileNotFoundException("Excel file not found.", filePath);
var toEmail = !string.IsNullOrWhiteSpace(recipient)
? recipient
: config.DefaultRecipient;
if (string.IsNullOrWhiteSpace(toEmail))
throw new InvalidOperationException("No recipient email address provided and no default configured.");
var emailSubject = subject ?? "Document Export Attached";
var emailContent = messageBody ?? "Please find the requested document export attached.";
using var form = new MultipartFormDataContent
{
{ new StringContent($"{config.FromName} <{config.FromEmail}>"), "from" },
{ new StringContent(toEmail), "to" },
{ new StringContent(emailSubject), "subject" },
{ new StringContent(emailContent), "text" }
};
// Add Excel attachment
var fileBytes = await File.ReadAllBytesAsync(filePath);
var fileName = Path.GetFileName(filePath);
var fileContent = new ByteArrayContent(fileBytes);
fileContent.Headers.ContentType = new MediaTypeHeaderValue(
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
form.Add(fileContent, "attachment", fileName);
var url = $"https://api.mailgun.net/v3/{config.Domain}/messages";
var response = await httpClient.PostAsync(url, form);
var responseBody = await response.Content.ReadAsStringAsync();
if (!response.IsSuccessStatusCode)
{
logger.LogError("Failed to send email via Mailgun. Status: {StatusCode}, Error: {Error}",
response.StatusCode, responseBody);
Console.WriteLine("Failed to send email via Mailgun.Status: { StatusCode}, Error: { Error}",
response.StatusCode, responseBody);
throw new InvalidOperationException($"Mailgun email failed: {responseBody}");
}
// Mailgun responds with JSON like: { "id": "<20231002123456.12345.67890@sandbox.mailgun.org>", "message": "Queued. Thank you." }
var messageId = ExtractMessageId(responseBody) ?? "unknown";
logger.LogInformation("Email sent successfully via Mailgun. Message-ID: {MessageId}, To: {To}",
messageId, toEmail);
return messageId;
}
public async Task<string> SendExcelEmailAsync(
byte[] fileBytes,
string fileName,
string? recipient = null,
string? subject = null,
string? messageBody = null)
{
var toEmail = !string.IsNullOrWhiteSpace(recipient)
? recipient
: config.DefaultRecipient;
if (string.IsNullOrWhiteSpace(toEmail))
throw new InvalidOperationException("No recipient email address provided and no default configured.");
var emailSubject = subject ?? "Document Export Attached";
var emailContent = messageBody ?? "Please find the requested document export attached.";
using var form = new MultipartFormDataContent
{
{ new StringContent($"{config.FromName} <{config.FromEmail}>"), "from" },
{ new StringContent(toEmail), "to" },
{ new StringContent(emailSubject), "subject" },
{ new StringContent(emailContent), "text" }
};
// Attach in-memory Excel file
var fileContent = new ByteArrayContent(fileBytes);
fileContent.Headers.ContentType = new MediaTypeHeaderValue(
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
form.Add(fileContent, "attachment", fileName);
var url = $"https://api.mailgun.net/v3/{config.Domain}/messages";
var response = await httpClient.PostAsync(url, form);
var responseBody = await response.Content.ReadAsStringAsync();
if (!response.IsSuccessStatusCode)
{
logger.LogError("Failed to send email via Mailgun. Status: {StatusCode}, Error: {Error}",
response.StatusCode, responseBody);
throw new InvalidOperationException($"Mailgun email failed: {responseBody}");
}
return ExtractMessageId(responseBody) ?? "unknown";
}
private static string? ExtractMessageId(string responseBody)
{
// Very simple extraction (could use JSON parser like System.Text.Json if desired)
var marker = "\"id\":";
var idx = responseBody.IndexOf(marker, StringComparison.OrdinalIgnoreCase);
if (idx >= 0)
{
var start = responseBody.IndexOf('<', idx);
var end = responseBody.IndexOf('>', start);
if (start >= 0 && end > start)
{
return responseBody.Substring(start, end - start + 1);
}
}
return null;
}
}
public class MailgunConfig
{
public string ApiKey { get; set; } = string.Empty;
public string Domain { get; set; } = string.Empty; // e.g. sandbox12345.mailgun.org
public string FromEmail { get; set; } = string.Empty;
public string FromName { get; set; } = string.Empty;
public string DefaultRecipient { get; set; } = string.Empty;
}
using MCPServerDocuments.Database.Document;
using MCPServerDocuments.DataClasses;
using MCPServerDocuments.Tools;
using ModelContextProtocol.Server;
using MyMPCServer.Database;
using System.ComponentModel;
using System.Text.Json;
namespace MCPServerDocuments;
[McpServerToolType]
public sealed class MCPServerDataExportTools
{
private readonly CompanyDocumentsRepository _repository;
private readonly UserHistoryRepository _historyRepository;
private readonly ExcelService _excelService;
private readonly MailgunEmailService _mailgunEmailService;
public MCPServerDataExportTools(
CompanyDocumentsRepository repository,
UserHistoryRepository historyRepository
,
ExcelService excelService,
MailgunEmailService mailgunEmailService)
{
_repository = repository;
_historyRepository = historyRepository;
_excelService = excelService;
_mailgunEmailService = mailgunEmailService;
}
[McpServerTool, Description("Search by Person ID, export to Excel in-memory, and send via email to recipient")]
public async Task<string> ProcessDocumentWorkflowByPersonId(
[Description("Person ID to search for")] int personId,
[Description("Email recipient (optional)")] string? recipient = null)
{
try
{
var documents = await _repository.GetDocumentsByPersonIdAsync(personId);
if (documents.Count == 0)
{
return JsonSerializer.Serialize(new
{
success = false,
error = $"No documents found for Person ID: {personId}",
timestamp = DateTime.UtcNow
}, ExcelAndEmailJsonContext.Default.ExcelError);
}
// Generate Excel in memory
var fileBytes = await _excelService.SaveDocumentsAsExcelAsync(documents, $"PersonID_{personId}");
var fileName = $"PersonID_{personId}_export.xlsx";
// Send with Mailgun
var mailgunMessageId = await _mailgunEmailService.SendExcelEmailAsync(
fileBytes,
fileName,
recipient,
$"Document Export for Person ID: {personId}",
$"Please find attached {documents.Count} document(s) for Person ID {personId}.");
return JsonSerializer.Serialize(new ExcelSuccess
{
Message = "Complete workflow executed successfully",
PersonId = personId,
DocumentCount = documents.Count,
FileName = fileName,
FilePath = $"(in-memory only)",
EmailSentTo = recipient ?? "default recipient",
EmailService = "Mailgun",
MailgunMessageId = mailgunMessageId
}, ExcelAndEmailJsonContext.Default.ExcelSuccess);
}
catch (Exception ex)
{
return JsonSerializer.Serialize(new
{
success = false,
error = ex.Message,
timestamp = DateTime.UtcNow
}, ExcelAndEmailJsonContext.Default.ExcelError);
}
}
}
public class ExcelError
{
public bool Success { get; set; } = false;
public string Error { get; set; } = string.Empty;
public DateTime Timestamp { get; set; } = DateTime.UtcNow;
}
public class ExcelSuccess
{
public bool Success { get; set; } = true;
public string Message { get; set; } = string.Empty;
public int PersonId { get; set; }
public int DocumentCount { get; set; }
public string FilePath { get; set; } = string.Empty;
public string FileName { get; set; } = string.Empty;
public string EmailSentTo { get; set; } = string.Empty;
public string EmailService { get; set; } = string.Empty;
public string? MailgunMessageId { get; set; }
public DateTime Timestamp { get; set; } = DateTime.UtcNow;
}
using MCPServerDocuments;
using MCPServerDocuments.Database;
using MCPServerDocuments.Database.Document;
using MCPServerDocuments.DataClasses;
using MCPServerDocuments.Tools;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using MyMPCServer.Database;
using System.Text.Json.Serialization;
var builder = Host.CreateEmptyApplicationBuilder(settings: null);
builder.Services
.AddMcpServer()
.WithStdioServerTransport()
.WithTools<MCPServerDataExportTools>();
builder.Logging.AddConsole();
builder.Logging.SetMinimumLevel(LogLevel.Debug);
// Register repositories
builder.Services.AddSingleton<CompanyDocumentsRepository>();
builder.Services.AddSingleton<UserHistoryRepository>();
builder.Services.AddSingleton<ExcelService>();
var defaultDbPath = Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),
"MCPServerDocuments",
"documents.db"
);
builder.Services.AddSingleton<DatabaseConfig>(provider => new DatabaseConfig
{
ConnectionString = Environment.GetEnvironmentVariable("DB_CONNECTION_STRING") ?? defaultDbPath
});
builder.Services.AddSingleton<MailgunConfig>(provider => new MailgunConfig
{
ApiKey = Environment.GetEnvironmentVariable("MAILGUN_API_KEY"),
Domain = Environment.GetEnvironmentVariable("MAILGUN_DOMAIN"),
FromEmail = Environment.GetEnvironmentVariable("MAILGUN_FROM_EMAIL"),
FromName = Environment.GetEnvironmentVariable("MAILGUN_FROM_NAME"),
DefaultRecipient = Environment.GetEnvironmentVariable("DEFAULT_RECIPIENT"),
});
// Register the email service
builder.Services.AddSingleton<MailgunEmailService>();
// Initialize database - use a separate scope or do this after Build()
var app = builder.Build();
// Initialize database using the properly built service provider
var dbConfig = app.Services.GetRequiredService<DatabaseConfig>();
await DatabaseInitializer.InitializeDatabaseAsync(dbConfig.ConnectionString);
await app.RunAsync();
// JSON serialization context
[JsonSerializable(typeof(SearchDocumentsByPeselResult))]
[JsonSerializable(typeof(SearchDocumentsError))]
[JsonSerializable(typeof(SearchDocumentsByPersonIdResult))]
public partial class CompanyDocumentJsonContext : JsonSerializerContext
{
}
[JsonSerializable(typeof(GetAllUserHistoryResult))]
[JsonSerializable(typeof(UserHistoryError))]
[JsonSerializable(typeof(List<UserHistoryWithDetails>))]
[JsonSerializable(typeof(UserHistoryWithDetails))]
public partial class UserHistoryJsonContext : JsonSerializerContext
{
}
[JsonSerializable(typeof(ExcelError))]
[JsonSerializable(typeof(ExcelSuccess))]
public partial class ExcelAndEmailJsonContext : JsonSerializerContext
{
}
$env:MAILGUN_API_KEY = ""
$env:MAILGUN_DOMAIN = ".mailgun.org"
$env:MAILGUN_FROM_EMAIL = "postmaster@.mailgun.org"
$env:MAILGUN_FROM_NAME = "Data Export Service"
$env:DEFAULT_RECIPIENT = "@gmail.com"
docker build -t mcp-server-documents:latest .
docker run --name mcp-server-documents-container --rm -i --env-file docker-env-variables.env -v ${PWD}/data:/data -v ${PWD}/exports:/exports mcp-server-documents:latest
exec mcp-server-documents-container env
name: Local Agent
version: 1.0.0
schema: v1
models:
- name: Autodetect
provider: ollama
model: AUTODETECT
mcpServers:
- name: company-documents
command: docker
args:
- exec
- -i
- mcp-server-documents-container
- ./MCPServerDocuments
env: {}
{
"mcpServers": {
"obsidian": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"-e",
"OBSIDIAN_HOST",
"-e",
"OBSIDIAN_API_KEY",
"mcp/obsidian"
],
"env": {
"OBSIDIAN_HOST": "host.docker.internal",
"OBSIDIAN_API_KEY": ""
}
},
"youtube_transcript": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"mcp/youtube-transcript"
]
},
"company-documents": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"-v", "C:\\Users\\Cezary\\mcp-documents-data:/data",
"-e", "DB_CONNECTION_STRING=/data/documents.db",
"-e", "MAILGUN_API_KEY=",
"-e", "MAILGUN_DOMAIN=s",
"-e", "MAILGUN_FROM_EMAIL=",
"-e", "MAILGUN_FROM_NAME=Data Export Service",
"-e", "DEFAULT_RECIPIENT=crezber@gmail.com",
"mcp-server-documents:latest"
],
"env": {
"DB_CONNECTION_STRING": "/data/documents.db",
"MAILGUN_API_KEY": ",
"MAILGUN_DOMAIN": ".mailgun.org",
"MAILGUN_FROM_EMAIL": "postmaster@.mailgun.org",
"MAILGUN_FROM_NAME" : "Data Export Service",
"DEFAULT_RECIPIENT": "@gmail.com"
}
}
}
}
# Use the official .NET SDK image for building
FROM mcr.microsoft.com/dotnet/sdk:8.0 AS build
WORKDIR /src
# Copy project files
COPY *.csproj ./
RUN dotnet restore
# Copy remaining source code
COPY . ./
# Publish as self-contained executable for linux-x64
RUN dotnet publish -c Release -r linux-x64 --self-contained true -p:PublishSingleFile=true -o /app/publish
# Use minimal runtime image since we have self-contained exe
FROM mcr.microsoft.com/dotnet/runtime-deps:8.0
WORKDIR /app
# Copy published app
COPY --from=build /app/publish .
# Create directory for database
RUN mkdir -p /data
# TO DZIADOSTOW ZAKOMENTOWAĆ
# Set environment variable for database path
#ENV DB_CONNECTION_STRING=/data/documents.db
# Run the self-contained executable
ENTRYPOINT ["./MCPServerDocuments"]
{
"mcpServers": {
"obsidian": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"-e",
"OBSIDIAN_HOST",
"-e",
"OBSIDIAN_API_KEY",
"mcp/obsidian"
],
"env": {
"OBSIDIAN_HOST": "host.docker.internal",
"OBSIDIAN_API_KEY": ""
}
},
"youtube_transcript": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"mcp/youtube-transcript"
]
},
"company-documents": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"-v", "C:\\Users\\Cezary\\mcp-documents-data:/data",
"-e", "DB_CONNECTION_STRING=/data/documents.db",
"-e", "MAILGUN_API_KEY=",
"-e", "MAILGUN_DOMAIN=s",
"-e", "MAILGUN_FROM_EMAIL=",
"-e", "MAILGUN_FROM_NAME=Data Export Service",
"-e", "DEFAULT_RECIPIENT=crezber@gmail.com",
"mcp-server-documents:latest"
],
"env": {
"DB_CONNECTION_STRING": "/data/documents.db",
"MAILGUN_API_KEY": ",
"MAILGUN_DOMAIN": ".mailgun.org",
"MAILGUN_FROM_EMAIL": "postmaster@.mailgun.org",
"MAILGUN_FROM_NAME" : "Data Export Service",
"DEFAULT_RECIPIENT": "@gmail.com"
}
},
,
"company-documents-server": {
"url": "http://localhost:3001/mcp/sse"
}
}
}
{
"servers": {
"github": {
"url": "https://api.githubcopilot.com/mcp/"
},
"company-documents": {
"command": "docker",
"args": [
"run",
"-i",
"--rm",
"-v",
"C:\\Users\\Cezary\\mcp-documents-data:/data",
"mcp-server-documents:latest"
]
},
"company-documents-server": {
"transport": "sse",
"url": "http://localhost:3001/mcp/sse"
}
},
"inputs": []
}
name: Local Agent
version: 1.0.0
schema: v1
models:
- name: Autodetect
provider: ollama
model: AUTODETECT
mcpServers:
- name: company-documents
command: docker
args:
- exec
- -i
- mcp-server-documents-container
- ./MCPServerDocuments
env: {}
- name: company-documents-server
type: streamable-http
url: http://localhost:3001/mcp
{
"mcpServers": {
"MCP_DOCKER": {
"command": "docker",
"args": [
"mcp",
"gateway",
"run"
],
"env": {
"LOCALAPPDATA": "C:\\Users\\Cezary\\AppData\\Local",
"ProgramData": "C:\\ProgramData",
"ProgramFiles": "C:\\Program Files"
}
},
"company-documents": {
"command": "docker",
"args": [
"run",
"--rm",
"-i",
"-v",
"C:\\Users\\Cezary\\mcp-documents-data:/data",
"mcp-server-documents:latest"
]
},
"company-documents-server": {
"url": "http://localhost:3001/mcp"
}
}
}
using MCPServerDocuments;
using MCPServerDocuments.Database;
using MCPServerDocuments.Tools;
using Microsoft.Extensions.Options;
var builder = WebApplication.CreateBuilder(args);
// MCP Server with BOTH transports
builder.Services
.AddMcpServer()
.WithHttpTransport()
.WithTools<MCPServerDataExportTools>();
// Database
var defaultDbPath = Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),
"MCPServerDocuments",
"documents.db");
builder.Services.AddSingleton(new DatabaseConfig
{
ConnectionString = Environment.GetEnvironmentVariable("DB_CONNECTION_STRING") ?? defaultDbPath
});
// Repositories & services
builder.Services.AddSingleton<CompanyDocumentsRepository>();
builder.Services.AddSingleton<UserHistoryRepository>();
builder.Services.AddSingleton<ExcelService>();
//builder.Services.AddSingleton(new MailgunConfig
//{
// ApiKey = Environment.GetEnvironmentVariable("MAILGUN_API_KEY") ?? "",
// Domain = Environment.GetEnvironmentVariable("MAILGUN_DOMAIN") ?? "",
// FromEmail = Environment.GetEnvironmentVariable("MAILGUN_FROM_EMAIL") ?? "",
// FromName = Environment.GetEnvironmentVariable("MAILGUN_FROM_NAME") ?? "",
// DefaultRecipient = Environment.GetEnvironmentVariable("DEFAULT_RECIPIENT") ?? ""
//});
builder.Services.Configure<MailgunConfig>(builder.Configuration.GetSection("Mailgun"));
builder.Services.AddSingleton(sp => sp.GetRequiredService<IOptions<MailgunConfig>>().Value);
builder.Services.AddSingleton<MailgunEmailService>();
var app = builder.Build();
// Initialize database
var dbConfig = app.Services.GetRequiredService<DatabaseConfig>();
await DatabaseInitializer.InitializeDatabaseAsync(dbConfig.ConnectionString);
// Map MCP endpoints
app.MapMcp("/mcp"); // Automatycznie mapuje /mcp/sse i mcp/message
// health check endpoint
app.MapGet("/health", () => Results.Ok(new {
status = "healthy",
timestamp = DateTime.UtcNow,
transports = new[] { "sse", "http" }
}));
app.Run();
using MCPServerDocuments.Database;
using MCPServerDocuments.DataClasses;
using MCPServerDocuments.Tools;
using ModelContextProtocol.Server;
using System.ComponentModel;
using System.Text.Json;
namespace MCPServerDocuments;
[McpServerToolType]
public sealed class MCPServerDataExportTools(
CompanyDocumentsRepository repository,
UserHistoryRepository historyRepository,
ExcelService excelService,
MailgunEmailService mailgunEmailService)
{
// Using JsonSerializerOptions with web defaults —
// camelCase, no need for source-gen context attributes.
// The SDK serializes tool return values automatically,
// but since we return string we control it ourselves.
private static readonly JsonSerializerOptions JsonOptions = new(JsonSerializerDefaults.Web)
{
WriteIndented = false
};
[McpServerTool, Description("Search for documents by PESEL number.")]
public async Task<string> SearchDocumentsByPesel(
[Description("PESEL number to search for")] string pesel)
{
try
{
var documents = await repository.GetDocumentsByPeselAsync(pesel);
return Serialize(new SearchDocumentsResult
{
Success = true,
Message = $"Found {documents.Count} document(s) for PESEL: {pesel}",
Pesel = pesel,
DocumentCount = documents.Count,
Documents = documents
});
}
catch (Exception ex)
{
return SerializeError(ex.Message);
}
}
[McpServerTool, Description("Search for documents by Person ID.")]
public async Task<string> SearchDocumentsByPersonId(
[Description("Person ID to search for")] int personId)
{
try
{
var documents = await repository.GetDocumentsByPersonIdAsync(personId);
return Serialize(new SearchDocumentsResult
{
Success = true,
Message = $"Found {documents.Count} document(s) for Person ID: {personId}",
PersonId = personId,
DocumentCount = documents.Count,
Documents = documents
});
}
catch (Exception ex)
{
return SerializeError(ex.Message);
}
}
[McpServerTool, Description("Get all user history with full details including user names and document information.")]
public async Task<string> GetAllUserHistoryWithDetails()
{
try
{
var historyDetails = await historyRepository.GetAllHistoryWithDetailsAsync();
return Serialize(new UserHistoryResult
{
Success = true,
Message = $"Found {historyDetails.Count} history record(s)",
RecordCount = historyDetails.Count,
HistoryRecords = historyDetails
});
}
catch (Exception ex)
{
return SerializeError(ex.Message);
}
}
[McpServerTool, Description("Search by Person ID, export to Excel in-memory, and send via email to recipient.")]
public async Task<string> ProcessDocumentWorkflowByPersonId(
[Description("Person ID to search for")] int personId,
[Description("Email recipient (optional)")] string? recipient = null)
{
try
{
var documents = await repository.GetDocumentsByPersonIdAsync(personId);
if (documents.Count == 0)
return SerializeError($"No documents found for Person ID: {personId}");
var fileBytes = await excelService.SaveDocumentsAsExcelAsync(documents, $"PersonID_{personId}");
var fileName = $"PersonID_{personId}_export.xlsx";
var mailgunMessageId = await mailgunEmailService.SendExcelEmailAsync(
fileBytes, fileName, recipient,
$"Document Export for Person ID: {personId}",
$"Please find attached {documents.Count} document(s) for Person ID {personId}.");
return Serialize(new WorkflowResult
{
Success = true,
Message = "Complete workflow executed successfully",
PersonId = personId,
DocumentCount = documents.Count,
FileName = fileName,
EmailSentTo = recipient ?? "default recipient",
EmailService = "Mailgun",
MailgunMessageId = mailgunMessageId
});
}
catch (Exception ex)
{
return SerializeError(ex.Message);
}
}
// Helpers
private static string Serialize<T>(T value) =>
JsonSerializer.Serialize(value, JsonOptions);
private static string SerializeError(string error) =>
Serialize(new ErrorResult { Success = false, Error = error });
}
using MCPServerDocuments.DataClasses;
namespace MCPServerDocuments.DataClasses;
// Search results
public class SearchDocumentsResult
{
public bool Success { get; set; }
public string Message { get; set; } = string.Empty;
public string? Pesel { get; set; }
public int? PersonId { get; set; }
public int DocumentCount { get; set; }
public List<CompanyDocument> Documents { get; set; } = [];
public DateTime Timestamp { get; set; } = DateTime.UtcNow;
}
// User history results
public class UserHistoryResult
{
public bool Success { get; set; }
public string Message { get; set; } = string.Empty;
public int RecordCount { get; set; }
public List<UserHistoryWithDetails> HistoryRecords { get; set; } = [];
public DateTime Timestamp { get; set; } = DateTime.UtcNow;
}
// Excel + email workflow results
public class WorkflowResult
{
public bool Success { get; set; }
public string Message { get; set; } = string.Empty;
public int? PersonId { get; set; }
public int DocumentCount { get; set; }
public string FileName { get; set; } = string.Empty;
public string EmailSentTo { get; set; } = string.Empty;
public string EmailService { get; set; } = string.Empty;
public string? MailgunMessageId { get; set; }
public DateTime Timestamp { get; set; } = DateTime.UtcNow;
}
// Generic error
public class ErrorResult
{
public bool Success { get; set; }
public string Error { get; set; } = string.Empty;
public DateTime Timestamp { get; set; } = DateTime.UtcNow;
}
using MCPServerDocuments.DataClasses;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using System.Drawing;
namespace MCPServerDocuments.Tools;
public class ExcelService
{
public Task<byte[]> SaveDocumentsAsExcelAsync(List<CompanyDocument> documents, string fileNamePrefix)
{
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using var package = new ExcelPackage();
var ws = package.Workbook.Worksheets.Add("Document Export");
// Headers
string[] headers = ["ID", "Person ID", "PESEL", "Document Type", "Document Number",
"Title", "Description", "File Path", "Created At", "Modified At", "Status"];
for (int col = 0; col < headers.Length; col++)
ws.Cells[1, col + 1].Value = headers[col];
// Data rows
for (int i = 0; i < documents.Count; i++)
{
var d = documents[i];
var row = i + 2;
ws.Cells[row, 1].Value = d.Id;
ws.Cells[row, 2].Value = d.PersonId;
ws.Cells[row, 3].Value = d.Pesel;
ws.Cells[row, 4].Value = d.DocumentType;
ws.Cells[row, 5].Value = d.DocumentNumber;
ws.Cells[row, 6].Value = d.Title;
ws.Cells[row, 7].Value = d.Description;
ws.Cells[row, 8].Value = d.FilePath;
ws.Cells[row, 9].Value = d.CreatedAt.ToString("yyyy-MM-dd HH:mm:ss");
ws.Cells[row, 10].Value = d.ModifiedAt?.ToString("yyyy-MM-dd HH:mm:ss") ?? "";
ws.Cells[row, 11].Value = d.Status;
}
FormatWorksheet(ws, headers.Length, documents.Count);
using var stream = new MemoryStream();
package.SaveAs(stream);
return Task.FromResult(stream.ToArray());
}
private static void FormatWorksheet(ExcelWorksheet ws, int columnCount, int rowCount)
{
using (var range = ws.Cells[1, 1, 1, columnCount])
{
range.Style.Font.Bold = true;
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
range.Style.Fill.BackgroundColor.SetColor(Color.LightGray);
range.Style.Border.BorderAround(ExcelBorderStyle.Thin);
}
for (int col = 1; col <= columnCount; col++)
ws.Column(col).AutoFit();
if (rowCount > 0)
{
using var range = ws.Cells[2, 1, rowCount + 1, columnCount];
range.Style.Border.BorderAround(ExcelBorderStyle.Thin);
range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
}
}
}
using Microsoft.Extensions.Logging;
using System.Net.Http.Headers;
using System.Text;
namespace MCPServerDocuments.Tools;
public class MailgunEmailService
{
private readonly MailgunConfig _config;
private readonly ILogger<MailgunEmailService> _logger;
private readonly HttpClient _httpClient;
public MailgunEmailService(MailgunConfig config, ILogger<MailgunEmailService> logger)
{
_config = config ?? throw new ArgumentNullException(nameof(config));
_logger = logger ?? throw new ArgumentNullException(nameof(logger));
if (string.IsNullOrWhiteSpace(config.ApiKey))
throw new InvalidOperationException("Mailgun API key is not configured.");
if (string.IsNullOrWhiteSpace(config.Domain))
throw new InvalidOperationException("Mailgun domain is not configured.");
_httpClient = new HttpClient();
var authToken = Convert.ToBase64String(Encoding.ASCII.GetBytes($"api:{config.ApiKey}"));
_httpClient.DefaultRequestHeaders.Authorization = new AuthenticationHeaderValue("Basic", authToken);
}
/// <summary>
/// Sends an email with an in-memory Excel file attachment via Mailgun.
/// </summary>
public async Task<string> SendExcelEmailAsync(
byte[] fileBytes,
string fileName,
string? recipient = null,
string? subject = null,
string? messageBody = null)
{
var toEmail = !string.IsNullOrWhiteSpace(recipient) ? recipient : _config.DefaultRecipient;
if (string.IsNullOrWhiteSpace(toEmail))
return "No recipient email address provided and no default configured.";
var emailSubject = subject ?? "Document Export Attached";
var emailContent = messageBody ?? "Please find the requested document export attached.";
using var form = new MultipartFormDataContent
{
{ new StringContent($"{_config.FromName} <{_config.FromEmail}>"), "from" },
{ new StringContent(toEmail), "to" },
{ new StringContent(emailSubject), "subject" },
{ new StringContent(emailContent), "text" }
};
var fileContent = new ByteArrayContent(fileBytes);
fileContent.Headers.ContentType = new MediaTypeHeaderValue(
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
form.Add(fileContent, "attachment", fileName);
var url = $"https://api.mailgun.net/v3/{_config.Domain}/messages";
var response = await _httpClient.PostAsync(url, form);
var responseBody = await response.Content.ReadAsStringAsync();
if (!response.IsSuccessStatusCode)
{
_logger.LogError("Failed to send email via Mailgun. Status: {StatusCode}, Error: {Error}",
response.StatusCode, responseBody);
return $"Mailgun email failed: {responseBody}";
}
var messageId = ExtractMessageId(responseBody) ?? "unknown";
_logger.LogInformation("Email sent via Mailgun. Message-ID: {MessageId}, To: {To}", messageId, toEmail);
return messageId;
}
private static string? ExtractMessageId(string responseBody)
{
var marker = "\"id\":";
var idx = responseBody.IndexOf(marker, StringComparison.OrdinalIgnoreCase);
if (idx >= 0)
{
var start = responseBody.IndexOf('<', idx);
var end = responseBody.IndexOf('>', start);
if (start >= 0 && end > start)
return responseBody[start..(end + 1)];
}
return null;
}
}
public class MailgunConfig
{
public string ApiKey { get; set; } = string.Empty;
public string Domain { get; set; } = string.Empty;
public string FromEmail { get; set; } = string.Empty;
public string FromName { get; set; } = string.Empty;
public string DefaultRecipient { get; set; } = string.Empty;
}
using MCPServerDocuments.DataClasses;
using Microsoft.Data.Sqlite;
namespace MCPServerDocuments.Database;
public class CompanyDocumentsRepository(DatabaseConfig dbConfig)
{
public async Task<List<CompanyDocument>> GetDocumentsByPeselAsync(string pesel)
{
using var connection = CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
SELECT Id, PersonId, Pesel, DocumentType, DocumentNumber, Title,
Description, FilePath, CreatedAt, ModifiedAt, Status
FROM Documents
WHERE Pesel = $pesel
ORDER BY CreatedAt DESC
""";
command.Parameters.AddWithValue("$pesel", pesel);
return await ReadDocumentsAsync(command);
}
public async Task<List<CompanyDocument>> GetDocumentsByPersonIdAsync(int personId)
{
using var connection = CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
SELECT Id, PersonId, Pesel, DocumentType, DocumentNumber, Title,
Description, FilePath, CreatedAt, ModifiedAt, Status
FROM Documents
WHERE PersonId = $personId
ORDER BY CreatedAt DESC
""";
command.Parameters.AddWithValue("$personId", personId);
return await ReadDocumentsAsync(command);
}
public async Task<int> AddDocumentAsync(CompanyDocument document)
{
using var connection = CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
INSERT INTO Documents (PersonId, Pesel, DocumentType, DocumentNumber, Title,
Description, FilePath, CreatedAt, Status)
VALUES ($personId, $pesel, $docType, $docNumber, $title, $description,
$filePath, $createdAt, $status);
SELECT last_insert_rowid();
""";
command.Parameters.AddWithValue("$personId", document.PersonId);
command.Parameters.AddWithValue("$pesel", document.Pesel);
command.Parameters.AddWithValue("$docType", document.DocumentType);
command.Parameters.AddWithValue("$docNumber", document.DocumentNumber);
command.Parameters.AddWithValue("$title", document.Title);
command.Parameters.AddWithValue("$description", document.Description);
command.Parameters.AddWithValue("$filePath", document.FilePath);
command.Parameters.AddWithValue("$createdAt", document.CreatedAt);
command.Parameters.AddWithValue("$status", document.Status);
var result = await command.ExecuteScalarAsync();
return Convert.ToInt32(result);
}
private SqliteConnection CreateConnection() =>
new($"Data Source={dbConfig.ConnectionString}");
private static async Task<List<CompanyDocument>> ReadDocumentsAsync(SqliteCommand command)
{
var documents = new List<CompanyDocument>();
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
documents.Add(new CompanyDocument
{
Id = reader.GetInt32(0),
PersonId = reader.GetInt32(1),
Pesel = reader.GetString(2),
DocumentType = reader.GetString(3),
DocumentNumber = reader.GetString(4),
Title = reader.GetString(5),
Description = reader.GetString(6),
FilePath = reader.GetString(7),
CreatedAt = reader.GetDateTime(8),
ModifiedAt = reader.IsDBNull(9) ? null : reader.GetDateTime(9),
Status = reader.GetString(10)
});
}
return documents;
}
}
using MCPServerDocuments.DataClasses;
using Microsoft.Data.Sqlite;
namespace MCPServerDocuments.Database;
public class UserHistoryRepository(DatabaseConfig dbConfig)
{
// User Methods
public async Task<User?> GetUserByIdAsync(int userId)
{
using var connection = CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = "SELECT Id, UserName FROM Users WHERE Id = $userId";
command.Parameters.AddWithValue("$userId", userId);
using var reader = await command.ExecuteReaderAsync();
return await reader.ReadAsync() ? MapUser(reader) : null;
}
public async Task<User?> GetUserByNameAsync(string userName)
{
using var connection = CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = "SELECT Id, UserName FROM Users WHERE UserName = $userName";
command.Parameters.AddWithValue("$userName", userName);
using var reader = await command.ExecuteReaderAsync();
return await reader.ReadAsync() ? MapUser(reader) : null;
}
public async Task<List<User>> GetAllUsersAsync()
{
using var connection = CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = "SELECT Id, UserName FROM Users ORDER BY UserName";
var users = new List<User>();
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
users.Add(MapUser(reader));
return users;
}
public async Task<int> AddUserAsync(User user)
{
using var connection = CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
INSERT INTO Users (UserName) VALUES ($userName);
SELECT last_insert_rowid();
""";
command.Parameters.AddWithValue("$userName", user.UserName);
var result = await command.ExecuteScalarAsync();
return Convert.ToInt32(result);
}
// History Methods
public async Task<List<UserHistoryUsingCompanyDocuments>> GetHistoryByUserIdAsync(int userId)
{
using var connection = CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
SELECT Id, UserId, UserQuery, Action, DocumentId, CreatedAt
FROM UserHistoryUsingCompanyDocuments
WHERE UserId = $userId
ORDER BY CreatedAt DESC
""";
command.Parameters.AddWithValue("$userId", userId);
return await ReadHistoryAsync(command);
}
public async Task<List<UserHistoryUsingCompanyDocuments>> GetHistoryByDocumentIdAsync(int documentId)
{
using var connection = CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
SELECT Id, UserId, UserQuery, Action, DocumentId, CreatedAt
FROM UserHistoryUsingCompanyDocuments
WHERE DocumentId = $documentId
ORDER BY CreatedAt DESC
""";
command.Parameters.AddWithValue("$documentId", documentId);
return await ReadHistoryAsync(command);
}
public async Task<List<UserHistoryUsingCompanyDocuments>> GetHistoryByActionAsync(string action)
{
using var connection = CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
SELECT Id, UserId, UserQuery, Action, DocumentId, CreatedAt
FROM UserHistoryUsingCompanyDocuments
WHERE Action = $action
ORDER BY CreatedAt DESC
""";
command.Parameters.AddWithValue("$action", action);
return await ReadHistoryAsync(command);
}
public async Task<int> AddHistoryAsync(UserHistoryUsingCompanyDocuments history)
{
using var connection = CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = """
INSERT INTO UserHistoryUsingCompanyDocuments (UserId, UserQuery, Action, DocumentId, CreatedAt)
VALUES ($userId, $userQuery, $action, $documentId, $createdAt);
SELECT last_insert_rowid();
""";
command.Parameters.AddWithValue("$userId", history.UserId);
command.Parameters.AddWithValue("$userQuery", history.UserQuery);
command.Parameters.AddWithValue("$action", history.Action);
command.Parameters.AddWithValue("$documentId", history.DocumentId);
command.Parameters.AddWithValue("$createdAt", history.CreatedAt);
var result = await command.ExecuteScalarAsync();
return Convert.ToInt32(result);
}
// Joined Queries
private const string HistoryWithDetailsQuery = """
SELECT
h.Id as HistoryId, h.UserId, u.UserName, h.UserQuery, h.Action,
h.DocumentId, d.Title as DocumentTitle, d.DocumentType, d.DocumentNumber, h.CreatedAt
FROM UserHistoryUsingCompanyDocuments h
INNER JOIN Users u ON h.UserId = u.Id
INNER JOIN Documents d ON h.DocumentId = d.Id
""";
public async Task<List<UserHistoryWithDetails>> GetHistoryWithDetailsByUserIdAsync(int userId)
{
using var connection = CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = $"{HistoryWithDetailsQuery} WHERE h.UserId = $userId ORDER BY h.CreatedAt DESC";
command.Parameters.AddWithValue("$userId", userId);
return await ReadHistoryWithDetailsAsync(command);
}
public async Task<List<UserHistoryWithDetails>> GetHistoryWithDetailsByDocumentIdAsync(int documentId)
{
using var connection = CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = $"{HistoryWithDetailsQuery} WHERE h.DocumentId = $documentId ORDER BY h.CreatedAt DESC";
command.Parameters.AddWithValue("$documentId", documentId);
return await ReadHistoryWithDetailsAsync(command);
}
public async Task<List<UserHistoryWithDetails>> GetAllHistoryWithDetailsAsync()
{
using var connection = CreateConnection();
await connection.OpenAsync();
var command = connection.CreateCommand();
command.CommandText = $"{HistoryWithDetailsQuery} ORDER BY h.CreatedAt DESC";
return await ReadHistoryWithDetailsAsync(command);
}
// Helpers
private SqliteConnection CreateConnection() =>
new($"Data Source={dbConfig.ConnectionString}");
private static User MapUser(SqliteDataReader reader) => new()
{
Id = reader.GetInt32(0),
UserName = reader.GetString(1)
};
private static async Task<List<UserHistoryUsingCompanyDocuments>> ReadHistoryAsync(SqliteCommand command)
{
var history = new List<UserHistoryUsingCompanyDocuments>();
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
history.Add(new UserHistoryUsingCompanyDocuments
{
Id = reader.GetInt32(0),
UserId = reader.GetInt32(1),
UserQuery = reader.GetString(2),
Action = reader.GetString(3),
DocumentId = reader.GetInt32(4),
CreatedAt = reader.GetDateTime(5)
});
}
return history;
}
private static async Task<List<UserHistoryWithDetails>> ReadHistoryWithDetailsAsync(SqliteCommand command)
{
var history = new List<UserHistoryWithDetails>();
using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
history.Add(new UserHistoryWithDetails
{
HistoryId = reader.GetInt32(0),
UserId = reader.GetInt32(1),
UserName = reader.GetString(2),
UserQuery = reader.GetString(3),
Action = reader.GetString(4),
DocumentId = reader.GetInt32(5),
DocumentTitle = reader.GetString(6),
DocumentType = reader.GetString(7),
DocumentNumber = reader.GetString(8),
CreatedAt = reader.GetDateTime(9)
});
}
return history;
}
}
namespace CompanyDocuments.Common.Services;
public enum UserRole
{
Employee,
Manager
}
/// <summary>
/// Represents the authenticated user making the MCP request.
/// Resolved from the Authorization header (Bearer token).
/// </summary>
public sealed class UserContext
{
public required string UserId { get; init; }
public required string Name { get; init; }
public required UserRole Role { get; init; }
}
namespace CompanyDocuments.Common.Services;
public sealed class ReportService
{
// Static report registry — each report has required role
private static readonly Dictionary<string, ReportDefinition> Reports =
new(StringComparer.OrdinalIgnoreCase)
{
["financial-q1"] = new(
"financial-q1",
"Raport finansowy Q1 2025",
UserRole.Manager,
"Przychody: 334 500 PLN | Koszty: 198 700 PLN | Zysk: 135 800 PLN | Marża: 40.6%"),
["financial-q2"] = new(
"financial-q2",
"Raport finansowy Q2 2025",
UserRole.Manager,
"Przychody: 357 000 PLN | Koszty: 224 500 PLN | Zysk: 132 500 PLN | Marża: 37.1%"),
["salary-report"] = new(
"salary-report",
"Zestawienie wynagrodzeń",
UserRole.Manager,
"Średnia: 14 200 PLN | Mediana: 12 800 PLN | Min: 7 500 PLN | Max: 28 000 PLN"),
["team-tasks"] = new(
"team-tasks",
"Zadania zespołu — podsumowanie",
UserRole.Employee,
"Otwarte: 12 | W trakcie: 8 | Zakończone: 34 | Sprinty: 3"),
["my-timesheet-summary"] = new(
"my-timesheet-summary",
"Podsumowanie godzin pracy",
UserRole.Employee,
"Czerwiec 2025 — Przepracowane: 168h | Nadgodziny: 12h | Urlop: 0 dni")
};
/// <summary>
/// Returns all reports with access information for the given user role.
/// Reports the user cannot access are still listed — but marked as denied.
/// </summary>
public Task<List<ReportAccessResult>> GetReportsForUserAsync(UserRole userRole)
{
var results = Reports.Values.Select(r =>
{
bool hasAccess = userRole >= r.RequiredRole;
return new ReportAccessResult(
ReportId: r.Id,
Title: r.Title,
HasAccess: hasAccess,
Content: hasAccess ? r.Content : null,
DeniedReason: hasAccess
? null
: $"Brak dostępu. Wymagana rola: {r.RequiredRole}. Twoja rola: {userRole}.");
}).ToList();
return Task.FromResult(results);
}
/// <summary>
/// Get a single report by ID with access check.
/// </summary>
public Task<ReportAccessResult> GetReportByIdAsync(string reportId, UserRole userRole)
{
if (!Reports.TryGetValue(reportId, out var report))
{
return Task.FromResult(new ReportAccessResult(
ReportId: reportId,
Title: "Nieznany raport",
HasAccess: false,
Content: null,
DeniedReason: $"Raport '{reportId}' nie istnieje."));
}
bool hasAccess = userRole >= report.RequiredRole;
return Task.FromResult(new ReportAccessResult(
ReportId: report.Id,
Title: report.Title,
HasAccess: hasAccess,
Content: hasAccess ? report.Content : null,
DeniedReason: hasAccess
? null
: $"Raport '{report.Title}' istnieje, ale nie masz do niego dostępu.
Wymagana rola: {report.RequiredRole}. Twoja rola: {userRole}."));
}
}
public sealed record ReportDefinition(
string Id,
string Title,
UserRole RequiredRole,
string Content);
public sealed record ReportAccessResult(
string ReportId,
string Title,
bool HasAccess,
string? Content,
string? DeniedReason);
// REPORT TOOLS — [RequireRole(Employee)]
// Visible to everyone authenticated.
// The tool itself shows all reports, but content is role-gated.
[McpServerToolType]
[RequireRole(UserRole.Employee)]
public sealed class ReportTools(
IHttpContextAccessor httpContextAccessor,
ReportService reportService)
{
private UserContext User =>
(httpContextAccessor.HttpContext?.Items["UserContext"] as UserContext)!;
[McpServerTool, Description(
"List all available reports. Shows which reports you have access to " +
"and which are restricted based on your role.")]
public async Task<string> GetReports()
{
var reports = await reportService.GetReportsForUserAsync(User.Role);
return McpJson.Serialize(new
{
success = true,
user = User.Name,
role = User.Role.ToString(),
totalReports = reports.Count,
accessibleReports = reports.Count(r => r.HasAccess),
deniedReports = reports.Count(r => !r.HasAccess),
reports
});
}
[McpServerTool, Description(
"Get a specific report by ID. Returns content if authorized, " +
"or an access-denied message with the required role.")]
public async Task<string> GetReportById(
[Description("Report ID (e.g. financial-q1, team-tasks, salary-report)")] string reportId)
{
var report = await reportService.GetReportByIdAsync(reportId, User.Role);
return McpJson.Serialize(new
{
success = report.HasAccess,
user = User.Name,
role = User.Role.ToString(),
report
});
}
}
public sealed class EmployeeService
{
public Task<List<Employee>> GetEmployeeListAsync() =>
Task.FromResult<List<Employee>>(
[
new(1, "Jan Nowak", "Developer", "IT"),
new(2, "Maria Wiśniewska", "QA Engineer", "IT"),
new(3, "Piotr Zieliński", "Project Manager", "PMO"),
new(4, "Katarzyna Dąbrowska", "HR Specialist", "HR")
]);
}
public sealed record Employee(
int Id,
string Name,
string Position,
string Department);
public sealed class FinancialService
{
public Task<FinancialSummary> GetFinancialSummaryAsync() =>
Task.FromResult(new FinancialSummary(
Revenue: 691_500m,
Costs: 423_200m,
Profit: 268_300m,
Currency: "PLN",
Period: "H1-2025"));
}
public sealed record FinancialSummary(
decimal Revenue,
decimal Costs,
decimal Profit,
string Currency,
string Period);
public sealed class SalesService
{
public Task<List<SaleRecord>> GetSalesReportAsync() =>
Task.FromResult<List<SaleRecord>>(
[
new("Q1-2025", "Licencje Enterprise", 245_000m, "PLN"),
new("Q1-2025", "Usługi konsultingowe", 89_500m, "PLN"),
new("Q2-2025", "Licencje Enterprise", 312_000m, "PLN"),
new("Q2-2025", "Szkolenia", 45_000m, "PLN")
]);
}
public sealed record SaleRecord(
string Period,
string Product,
decimal Amount,
string Currency);
public sealed class TaskService
{
// In real app: filter by userId from DB
public Task<List<WorkTask>> GetTasksForUserAsync(string userId) =>
Task.FromResult<List<WorkTask>>(
[
new(101, "Przygotować prezentację MCP", "InProgress", "2025-06-15"),
new(102, "Code review PR #247", "Todo", "2025-06-12"),
new(103, "Aktualizacja dokumentacji API", "Done", "2025-06-10")
]);
public Task<TimesheetConfirmation> SubmitTimesheetAsync(
string userId, string date, decimal hours, string description) =>
Task.FromResult(new TimesheetConfirmation(
Id: Guid.NewGuid().ToString("N")[..8],
UserId: userId,
Date: date,
Hours: hours,
Description: description,
Status: "Submitted"));
}
public sealed record WorkTask(
int Id,
string Title,
string Status,
string DueDate);
public sealed record TimesheetConfirmation(
string Id,
string UserId,
string Date,
decimal Hours,
string Description,
string Status);
/// <summary>
/// Marks an MCP tool class as requiring a specific role.
/// Used by RoleBasedToolProvider to filter tools per user.
/// </summary>
[AttributeUsage(AttributeTargets.Class, Inherited = false)]
public sealed class RequireRoleAttribute(UserRole minimumRole)
: Attribute
{
public UserRole MinimumRole { get; } = minimumRole;
}
// MANAGER TOOLS — [RequireRole(Manager)]
// These tools are completely invisible to Employee users.
// They won't even show up in tools/list.
[McpServerToolType]
[RequireRole(UserRole.Manager)]
public sealed class ManagerTools(
IHttpContextAccessor httpContextAccessor,
SalesService salesService,
EmployeeService employeeService,
FinancialService financialService)
{
private UserContext User => (httpContextAccessor.HttpContext?.Items["UserContext"] as UserContext)!;
[McpServerTool, Description("Get sales report with revenue data per quarter and product.")]
public async Task<string> GetSalesReport()
{
var data = await salesService.GetSalesReportAsync();
return McpJson.Serialize
(new { success = true, user = User.Name, report = "Sales Report", records = data });
}
[McpServerTool, Description("Get list of all employees with their positions and departments.")]
public async Task<string> GetEmployeeList()
{
var data = await employeeService.GetEmployeeListAsync();
return McpJson.Serialize
(new { success = true, user = User.Name, report = "Employee List", employees = data });
}
[McpServerTool, Description("Get financial summary including revenue, costs and profit.")]
public async Task<string> GetFinancialSummary()
{
var data = await financialService.GetFinancialSummaryAsync();
return McpJson.Serialize
(new { success = true, user = User.Name, report = "Financial Summary", summary = data });
}
}
// EMPLOYEE TOOLS — [RequireRole(Employee)]
// Visible to Employee AND Manager (Manager >= Employee).
[McpServerToolType]
[RequireRole(UserRole.Employee)]
public sealed class EmployeeTools(
IHttpContextAccessor httpContextAccessor,
TaskService taskService)
{
private UserContext User => (httpContextAccessor.HttpContext?.Items["UserContext"] as UserContext)!;
[McpServerTool, Description("Get my current tasks and their statuses.")]
public async Task<string> GetMyTasks()
{
var data = await taskService.GetTasksForUserAsync(User.UserId);
return McpJson.Serialize
(new { success = true, user = User.Name, tasks = data });
}
[McpServerTool, Description("Submit a timesheet entry with worked hours.")]
public async Task<string> SubmitTimesheet(
[Description("Date in YYYY-MM-DD format")] string date,
[Description("Number of hours worked")] decimal hours,
[Description("Description of work performed")] string description)
{
var result = await taskService.SubmitTimesheetAsync(User.UserId, date, hours, description);
return McpJson.Serialize
(new { success = true, user = User.Name, confirmation = result });
}
}
// Shared JSON helper
internal static class McpJson
{
public static readonly JsonSerializerOptions Options = new(JsonSerializerDefaults.Web)
{
WriteIndented = true
};
public static string Serialize<T>(T value) =>
JsonSerializer.Serialize(value, Options);
}
/// <summary>
/// Discovers all [McpServerToolType] classes from the assembly at startup,
/// then filters them per-request based on the user's role (from Bearer token).
///
/// Tools decorated with [RequireRole(UserRole.Manager)] are only visible to Managers.
/// Tools without [RequireRole] are visible to all authenticated users.
/// Unauthenticated users see zero tools.
///
/// IMPORTANT: Because of issue #707 in csharp-sdk, we cannot use .WithTools<T>()
/// together with WithListToolsHandler — registered tools always get appended.
/// Instead, we skip .WithTools<T>() entirely and handle discovery + DI ourselves.
/// </summary>
public sealed class RoleBasedToolProvider
{
private readonly List<ToolEntry> _allTools;
public RoleBasedToolProvider(IServiceProvider serviceProvider)
{
_allTools = DiscoverTools(serviceProvider);
}
/// <summary>
/// Returns only the tools the current user is authorized to see.
/// Called from WithListToolsHandler.
/// </summary>
public IReadOnlyList<McpServerTool> GetToolsForUser(UserContext? user)
{
if (user is null)
return [];
return _allTools
.Where(t => user.Role >= t.MinimumRole)
.Select(t => t.Tool)
.ToList();
}
/// <summary>
/// Finds a tool by name, but only if the user is authorized.
/// Called from WithCallToolHandler.
/// </summary>
public McpServerTool? FindTool(string toolName, UserContext? user)
{
if (user is null)
return null;
return _allTools
.Where(t => user.Role >= t.MinimumRole)
.FirstOrDefault(t => t.Tool.ProtocolTool.Name == toolName)
?.Tool;
}
// Discovery
private static List<ToolEntry> DiscoverTools(IServiceProvider serviceProvider)
{
var toolAssembly = typeof(RoleBasedToolProvider).Assembly;
var entries = new List<ToolEntry>();
var options = new McpServerToolCreateOptions
{
Services = serviceProvider
};
var toolTypes = toolAssembly.GetTypes()
.Where(t => t.GetCustomAttribute<McpServerToolTypeAttribute>() is not null);
foreach (var type in toolTypes)
{
var minimumRole = type.GetCustomAttribute<RequireRoleAttribute>()?.MinimumRole
?? UserRole.Employee;
var methods = type.GetMethods(
BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static);
foreach (var method in methods)
{
if (method.GetCustomAttribute<McpServerToolAttribute>() is null)
continue;
McpServerTool tool;
if (method.IsStatic)
{
tool = McpServerTool.Create(method, options);
}
else
{
// Use the Func<RequestContext, object> overload so SDK
// resolves the instance from DI on each invocation.
var capturedType = type;
tool = McpServerTool.Create(
method,
(RequestContext<CallToolRequestParams> ctx) =>
ctx.Services!.GetRequiredService(capturedType),
options);
}
entries.Add(new ToolEntry(tool, minimumRole));
}
}
return entries;
}
private sealed record ToolEntry(McpServerTool Tool, UserRole MinimumRole);
}
/// <summary>
/// Simple token => user resolver for demo/presentation purposes.
/// In production this would validate JWT / OAuth tokens.
/// </summary>
public sealed class TokenUserResolver
{
// Hard-coded demo tokens. Easy to show on a presentation slide
private static readonly Dictionary<string, UserContext> Users
= new(StringComparer.OrdinalIgnoreCase)
{
["manager-token"] = new UserContext
{
UserId = "user-1",
Name = "Anna Kowalska",
Role = UserRole.Manager
},
["employee-token"] = new UserContext
{
UserId = "user-2",
Name = "Jan Nowak",
Role = UserRole.Employee
}
};
public UserContext? Resolve(string? bearerToken) =>
bearerToken is not null && Users.TryGetValue(bearerToken, out var user)
? user
: null;
}
using CompanyDocuments.BaseMCP;
using CompanyDocuments.Common.Database;
using CompanyDocuments.Common.Services;
using CompanyDocuments.MCPWeb;
using Microsoft.Extensions.Options;
using ModelContextProtocol.Protocol;
var builder = WebApplication.CreateBuilder(args);
// HttpContextAccessor — needed so MCP tools can read Bearer token
builder.Services.AddHttpContextAccessor();
// Auth: token → UserContext resolver
builder.Services.AddSingleton<TokenUserResolver>();
// Pseudo services for demo
builder.Services.AddSingleton<SalesService>();
builder.Services.AddSingleton<EmployeeService>();
builder.Services.AddSingleton<FinancialService>();
builder.Services.AddSingleton<TaskService>();
builder.Services.AddSingleton<ReportService>();
// Tool class DI registrations (needed for instance-method tool creation)
builder.Services.AddScoped<ManagerTools>();
builder.Services.AddScoped<EmployeeTools>();
builder.Services.AddScoped<ReportTools>();
builder.Services.AddScoped<ElicitationTools>();
builder.Services.AddScoped<SamplingTools>();
// Database
var defaultDbPath = Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),
"MCPServerDocuments",
"documents.db");
builder.Services.AddSingleton(new DatabaseConfig
{
ConnectionString = Environment.GetEnvironmentVariable("DB_CONNECTION_STRING") ?? defaultDbPath
});
builder.Services.AddSingleton<CompanyDocumentsRepository>();
builder.Services.AddSingleton<UserHistoryRepository>();
builder.Services.AddSingleton<ExcelService>();
builder.Services.Configure<MailgunConfig>(builder.Configuration.GetSection("Mailgun"));
builder.Services.AddSingleton(sp => sp.GetRequiredService<IOptions<MailgunConfig>>().Value);
builder.Services.AddSingleton<MailgunEmailService>();
// RoleBasedToolProvider — discovers tools from assembly, filters by role
builder.Services.AddSingleton<RoleBasedToolProvider>();
// MCP Server — NO .WithTools<T>() !!
// We use custom handlers to control what tools/list and tools/call return.
// Issue csharp-sdk#707: WithTools always appends to ListToolsHandler result.
builder.Services
.AddMcpServer()
.WithHttpTransport()
.WithPromptsFromAssembly()
.WithResourcesFromAssembly()
.WithListToolsHandler((context, cancellationToken) =>
{
var accessor = context.Services!.GetRequiredService<IHttpContextAccessor>();
var user = accessor.HttpContext?.Items["UserContext"] as UserContext;
var provider = context.Services!.GetRequiredService<RoleBasedToolProvider>();
var tools = provider.GetToolsForUser(user);
var result = new ListToolsResult
{
Tools = tools.Select(t => t.ProtocolTool).ToList()
};
return ValueTask.FromResult(result);
})
.WithCallToolHandler(async (context, cancellationToken) =>
{
var accessor = context.Services!.GetRequiredService<IHttpContextAccessor>();
var user = accessor.HttpContext?.Items["UserContext"] as UserContext;
if (user is null)
{
return new CallToolResult
{
IsError = true,
Content = [new TextContentBlock
{
Text = "Brak autoryzacji. Wyślij nagłówek: Authorization: Bearer <token>"
}]
};
}
var provider = context.Services!.GetRequiredService<RoleBasedToolProvider>();
var toolName = context.Params?.Name ?? string.Empty;
var tool = provider.FindTool(toolName, user);
if (tool is null)
{
return new CallToolResult
{
IsError = true,
Content = [new TextContentBlock
{
Text = $"Narzędzie '{toolName}' nie istnieje lub nie masz do niego dostępu."
}]
};
}
return await tool.InvokeAsync(context, cancellationToken);
});
var app = builder.Build();
// Initialize database
var dbConfig = app.Services.GetRequiredService<DatabaseConfig>();
await DatabaseInitializer.InitializeDatabaseAsync(dbConfig.ConnectionString);
// Middleware: resolve user from Bearer token → HttpContext.Items
app.Use(async (context, next) =>
{
if (context.Request.Path.StartsWithSegments("/mcp"))
{
var authHeader = context.Request.Headers.Authorization.FirstOrDefault();
if (authHeader?.StartsWith("Bearer ", StringComparison.OrdinalIgnoreCase) is true)
{
var token = authHeader["Bearer ".Length..];
var resolver = context.RequestServices.GetRequiredService<TokenUserResolver>();
var user = resolver.Resolve(token);
if (user is not null)
{
context.Items["UserContext"] = user;
}
}
}
await next();
});
// Map MCP endpoints
app.MapMcp("/mcp");
// Landing page with all endpoints
app.MapGet("/", (HttpContext ctx) =>
{
var baseUrl = $"{ctx.Request.Scheme}://{ctx.Request.Host}";
return Results.Content(LandingPage.Render(baseUrl), "text/html");
});
// ── Health check — shows demo tokens and endpoints
app.MapGet("/health", () => Results.Ok(new
{
status = "healthy",
timestamp = DateTime.UtcNow,
endpoints = new
{
http = "http://localhost:3001/mcp",
https = "https://localhost:3002/mcp"
},
demo_tokens = new
{
manager = "Bearer manager-token => Anna Kowalska (Manager) => 7 tools",
employee = "Bearer employee-token => Jan Nowak (Employee) => 4 tools",
no_token = "No header => 0 tools"
}
}));
// Diagnostic: who am I?
app.MapGet("/whoami", (HttpContext ctx) =>
{
var user = ctx.Items["UserContext"] as UserContext;
return user is not null
? Results.Ok(new { user.UserId, user.Name, Role = user.Role.ToString() })
: Results.Unauthorized();
});
app.Run();
using CompanyDocuments.BaseMCP;
using CompanyDocuments.Common.Database;
using CompanyDocuments.Common.Services;
using CompanyDocuments.MCPWebAuth2.Auth;
using Microsoft.AspNetCore.Authentication.JwtBearer;
using Microsoft.Extensions.Options;
using Microsoft.IdentityModel.Tokens;
using ModelContextProtocol.Protocol;
using System.Security.Claims;
var builder = WebApplication.CreateBuilder(args);
// 1. Authentication: JWT Bearer (tokens issued by our demo OAuth server)
var serverUrl = builder.Configuration["ServerUrl"] ?? "http://localhost:3001";
// Important it didnt work without CORS
// the browser would block the response from /token endpoint and the client would never receive the access token
builder.Services.AddCors(options =>
{
options.AddDefaultPolicy(policy =>
{
policy.AllowAnyOrigin()
.AllowAnyHeader()
.AllowAnyMethod();
});
});
builder.Services
.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
.AddJwtBearer(options =>
{
options.TokenValidationParameters = new TokenValidationParameters
{
ValidateIssuer = true,
ValidIssuer = DemoOAuthServer.Issuer,
ValidateAudience = true,
ValidAudience = DemoOAuthServer.Audience,
ValidateLifetime = true,
ValidateIssuerSigningKey = true,
IssuerSigningKey = DemoOAuthServer.SigningKey,
};
})
// MCP Auth scheme: serves /.well-known/oauth-protected-resource
// and adds resource_metadata URI to WWW-Authenticate challenges
.AddMcp(options =>
{
options.ResourceMetadata = new()
{
Resource = new Uri($"{serverUrl}/mcp"),
AuthorizationServers = { new Uri(serverUrl) },
ScopesSupported = { "mcp:tools", "mcp:resources" },
};
});
builder.Services.AddAuthorization();
builder.Services.AddHttpContextAccessor();
// 2. Business services (reused from your existing projects)
builder.Services.AddSingleton<TokenUserResolver>();
builder.Services.AddSingleton<SalesService>();
builder.Services.AddSingleton<EmployeeService>();
builder.Services.AddSingleton<FinancialService>();
builder.Services.AddSingleton<TaskService>();
builder.Services.AddSingleton<ReportService>();
builder.Services.AddScoped<ManagerTools>();
builder.Services.AddScoped<EmployeeTools>();
builder.Services.AddScoped<ReportTools>();
builder.Services.AddScoped<ElicitationTools>();
builder.Services.AddScoped<SamplingTools>();
// Database
var defaultDbPath = Path.Combine(
Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),
"MCPServerDocuments", "documents.db");
builder.Services.AddSingleton(new DatabaseConfig
{
ConnectionString = Environment.GetEnvironmentVariable("DB_CONNECTION_STRING") ?? defaultDbPath
});
builder.Services.AddSingleton<CompanyDocumentsRepository>();
builder.Services.AddSingleton<UserHistoryRepository>();
builder.Services.AddSingleton<ExcelService>();
builder.Services.Configure<MailgunConfig>(builder.Configuration.GetSection("Mailgun"));
builder.Services.AddSingleton(sp => sp.GetRequiredService<IOptions<MailgunConfig>>().Value);
builder.Services.AddSingleton<MailgunEmailService>();
// Role-based tool provider
builder.Services.AddSingleton<RoleBasedToolProvider>();
// 3. MCP Server — with HTTP transport + role-based tool filtering
// Using custom handlers (same pattern as MCPWeb project)
builder.Services
.AddMcpServer()
.WithHttpTransport()
.WithPromptsFromAssembly()
.WithResourcesFromAssembly()
.WithListToolsHandler((context, cancellationToken) =>
{
var user = ResolveUserFromClaims(context.Services!);
var provider = context.Services!.GetRequiredService<RoleBasedToolProvider>();
var tools = provider.GetToolsForUser(user);
return ValueTask.FromResult(new ListToolsResult
{
Tools = tools.Select(t => t.ProtocolTool).ToList()
});
})
.WithCallToolHandler(async (context, cancellationToken) =>
{
var user = ResolveUserFromClaims(context.Services!);
if (user is null)
{
return new CallToolResult
{
IsError = true,
Content = [new TextContentBlock
{
Text = "Brak autoryzacji. Zaloguj się przez OAuth 2.0 flow."
}]
};
}
var provider = context.Services!.GetRequiredService<RoleBasedToolProvider>();
var toolName = context.Params?.Name ?? string.Empty;
var tool = provider.FindTool(toolName, user);
if (tool is null)
{
return new CallToolResult
{
IsError = true,
Content = [new TextContentBlock
{
Text = $"Narzędzie '{toolName}' nie istnieje lub nie masz do niego dostępu."
}]
};
}
return await tool.InvokeAsync(context, cancellationToken);
});
var app = builder.Build();
// 4. Initialize database
var dbConfig = app.Services.GetRequiredService<DatabaseConfig>();
await DatabaseInitializer.InitializeDatabaseAsync(dbConfig.ConnectionString);
// 5. Middleware pipeline
app.UseCors();
app.UseAuthentication();
app.UseAuthorization();
// Resolve UserContext from JWT claims (for MCP tool handlers)
app.Use(async (context, next) =>
{
if (context.Request.Path.StartsWithSegments("/mcp") && context.User.Identity?.IsAuthenticated == true)
{
var role = context.User.FindFirst(ClaimTypes.Role)?.Value ?? "Employee";
var name = context.User.FindFirst(ClaimTypes.Name)?.Value
?? context.User.FindFirst("name")?.Value
?? "Unknown";
var userId = context.User.FindFirst(ClaimTypes.NameIdentifier)?.Value
?? context.User.FindFirst("sub")?.Value
?? "0";
context.Items["UserContext"] = new UserContext
{
UserId = userId,
Name = name,
Role = Enum.TryParse<UserRole>(role, true, out var r) ? r : UserRole.Employee
};
}
await next();
});
// 6. Map endpoints
// OAuth 2.0 Authorization Server endpoints
DemoOAuthServer.MapOAuthEndpoints(app, serverUrl);
// MCP endpoint (protected by JWT auth)
app.MapMcp("/mcp");
// Landing page
app.MapGet("/", (HttpContext ctx) =>
{
var baseUrl = $"{ctx.Request.Scheme}://{ctx.Request.Host}";
return Results.Content(OAuth2LandingPage.Render(baseUrl), "text/html");
});
// Health check
app.MapGet("/health", () => Results.Ok(new
{
status = "healthy",
timestamp = DateTime.UtcNow,
auth = "OAuth 2.0 Authorization Code + PKCE",
endpoints = new
{
mcp = $"{serverUrl}/mcp",
authorize = $"{serverUrl}/authorize",
token = $"{serverUrl}/token",
metadata = $"{serverUrl}/.well-known/oauth-authorization-server",
resource_metadata = $"{serverUrl}/.well-known/oauth-protected-resource",
},
demo_users = new
{
manager = "anna / pass → Manager role → 7 tools",
employee = "jan / pass → Employee role → 4 tools",
}
}));
// Who am I (from JWT)
app.MapGet("/whoami", (HttpContext ctx) =>
{
var user = ctx.Items["UserContext"] as UserContext;
if (user is not null)
return Results.Ok(new { user.UserId, user.Name, Role = user.Role.ToString() });
if (ctx.User.Identity?.IsAuthenticated == true)
return Results.Ok(new
{
sub = ctx.User.FindFirst("sub")?.Value,
name = ctx.User.FindFirst("name")?.Value,
role = ctx.User.FindFirst(ClaimTypes.Role)?.Value,
});
return Results.Unauthorized();
}).RequireAuthorization();
app.Run();
static UserContext? ResolveUserFromClaims(IServiceProvider services)
{
var accessor = services.GetRequiredService<IHttpContextAccessor>();
var httpContext = accessor.HttpContext;
if (httpContext?.Items["UserContext"] is UserContext uc)
return uc;
var principal = httpContext?.User;
if (principal?.Identity?.IsAuthenticated != true)
return null;
var role = principal.FindFirst(ClaimTypes.Role)?.Value ?? "Employee";
var name = principal.FindFirst(ClaimTypes.Name)?.Value
?? principal.FindFirst("name")?.Value
?? "Unknown";
var userId = principal.FindFirst(ClaimTypes.NameIdentifier)?.Value
?? principal.FindFirst("sub")?.Value
?? "0";
return new UserContext
{
UserId = userId,
Name = name,
Role = Enum.TryParse<UserRole>(role, true, out var r) ? r : UserRole.Employee
};
}
