using System.ComponentModel;
using System.Data.SqlClient;
using Microsoft.Extensions.Configuration;
using ModelContextProtocol.Server;

[McpServerToolType]
public class DatabaseTools
{
    private readonly IConfiguration _configuration;
    private readonly ILogger<DatabaseTools> _logger;

    public DatabaseTools(IConfiguration configuration, ILogger<DatabaseTools> logger)
    {
        _configuration = configuration;
        _logger = logger;
    }

    [McpServerTool, Description("데이터베이스에서 사용자 정보를 조회합니다.")]
    public async Task<string> GetUserInfo(
        [Description("조회할 사용자 ID")] int userId)
    {
        try
        {
            var connectionString = _configuration.GetConnectionString("DefaultConnection");
            using var connection = new SqlConnection(connectionString);
            await connection.OpenAsync();

            var query = "SELECT Id, Name, Email, CreatedDate FROM Users WHERE Id = @UserId";
            using var command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@UserId", userId);

            using var reader = await command.ExecuteReaderAsync();
            if (await reader.ReadAsync())
            {
                var user = new
                {
                    Id = reader.GetInt32("Id"),
                    Name = reader.GetString("Name"),
                    Email = reader.GetString("Email"),
                    CreatedDate = reader.GetDateTime("CreatedDate")
                };
                return JsonSerializer.Serialize(user);
            }
            return "사용자를 찾을 수 없습니다.";
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "사용자 정보 조회 중 오류 발생");
            return $"오류 발생: {ex.Message}";
        }
    }

    [McpServerTool, Description("데이터베이스에서 주문 통계를 생성합니다.")]
    public async Task<string> GetOrderStatistics(
        [Description("시작 날짜 (yyyy-MM-dd)")] string startDate,
        [Description("종료 날짜 (yyyy-MM-dd)")] string endDate)
    {
        try
        {
            var connectionString = _configuration.GetConnectionString("DefaultConnection");
            using var connection = new SqlConnection(connectionString);
            await connection.OpenAsync();

            var query = @"
                SELECT 
                    COUNT(*) as TotalOrders,
                    SUM(Amount) as TotalAmount,
                    AVG(Amount) as AverageAmount,
                    MAX(Amount) as MaxAmount,
                    MIN(Amount) as MinAmount
                FROM Orders 
                WHERE OrderDate BETWEEN @StartDate AND @EndDate";

            using var command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@StartDate", DateTime.Parse(startDate));
            command.Parameters.AddWithValue("@EndDate", DateTime.Parse(endDate));

            using var reader = await command.ExecuteReaderAsync();
            if (await reader.ReadAsync())
            {
                var stats = new
                {
                    Period = $"{startDate} ~ {endDate}",
                    TotalOrders = reader.GetInt32("TotalOrders"),
                    TotalAmount = reader.GetDecimal("TotalAmount"),
                    AverageAmount = reader.GetDecimal("AverageAmount"),
                    MaxAmount = reader.GetDecimal("MaxAmount"),
                    MinAmount = reader.GetDecimal("MinAmount")
                };
                return JsonSerializer.Serialize(stats, new JsonSerializerOptions { WriteIndented = true });
            }
            return "통계 데이터를 찾을 수 없습니다.";
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "주문 통계 조회 중 오류 발생");
            return $"오류 발생: {ex.Message}";
        }
    }
}