sozsoft-platform/api/modules/Sozsoft.Mcp/Sozsoft.Mcp.Domain/Services/McpSqlObjectWriter.cs

using System;
using System.IO;
using System.Text.RegularExpressions;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Localization;
using Sozsoft.Platform.Data.Seeds;
using Sozsoft.Platform.Localization;
using Volo.Abp.Domain.Services;

namespace Sozsoft.Mcp.Services;

/// <summary>
/// Ekranin veri kaynagi olan view, procedure ve fonksiyon scriptlerini SQL Query Manager'in yazdigi
/// yere (<c>sql/object/{Ad}.sql</c>) ve PostgreSQL esine (<c>postgres/object/{Ad}.sql</c>) yazar.
/// Govde diyalektler arasinda guvenle cevrilemedigi icin (<see cref="PostgreSqlTableScript"/>) iki
/// script de istemciden gelir; biri olmadan yazilmaz.
/// <para>
/// Veritabaninda calistirilmaz: nesneyi, tablo gibi DB Migrate kurar (<c>SqlDataSeeder</c>). Nesnenin
/// adi SQL Server scriptinin basligindan okunur; basliklar tekrar calismaya dayanikli bicime cevrilir
/// (<c>CREATE OR ALTER</c> / <c>CREATE OR REPLACE</c>), ayni adla tekrar yazmak scripti gunceller.
/// </para>
/// </summary>
public interface IMcpSqlObjectWriter
{
    McpOutcome<McpSavedSqlObject> Save(string sqlServerScript, string postgreSqlScript);
}

public partial class McpSqlObjectWriter(
    IConfiguration configuration,
    IStringLocalizer<PlatformResource> localizer) : DomainService, IMcpSqlObjectWriter
{
    private const string SqlServerProvider = "SQL Server";
    private const string PostgreSqlProvider = "PostgreSQL";

    public McpOutcome<McpSavedSqlObject> Save(string sqlServerScript, string postgreSqlScript)
    {
        var sqlServer = Canonicalize(sqlServerScript, SqlServerHeaderRegex(), "CREATE OR ALTER");
        if (sqlServer is null)
            return McpProblem.From(localizer, McpErrorCodes.SqlObjectInvalid, SqlServerProvider);

        var postgreSql = Canonicalize(postgreSqlScript, PostgreSqlHeaderRegex(), "CREATE OR REPLACE");
        if (postgreSql is null || !string.Equals(sqlServer.Name, postgreSql.Name, StringComparison.OrdinalIgnoreCase))
            return McpProblem.From(localizer, McpErrorCodes.SqlObjectInvalid, PostgreSqlProvider);

        // Tablo scriptinin yerine view yazilirsa migrate tabloyu bir daha kuramazdi.
        if (PostgreSqlTableScript.ReadSeedColumns(configuration, sqlServer.Name).Count > 0)
            return McpProblem.From(localizer, McpErrorCodes.TableAlreadyExists, sqlServer.Name);

        var fileName = $"{sqlServer.Name}.sql";
        var sqlServerPath = Write(SeedPathResolver.SqlFolder, fileName, sqlServer.Script);
        var postgreSqlPath = Write(SeedPathResolver.PostgresFolder, fileName, postgreSql.Script);

        return new McpSavedSqlObject(sqlServer.Name, sqlServer.Kind, sqlServerPath, postgreSqlPath);
    }

    /// <summary>Scripti saglayicinin <c>object</c> klasorune yazar; seed kokune goreli yolu dondurur.</summary>
    private string Write(string providerFolder, string fileName, string script)
    {
        var folder = SeedPathResolver.GetPath(configuration, providerFolder, SeedPathResolver.ObjectFolder);
        Directory.CreateDirectory(folder);
        File.WriteAllText(Path.Combine(folder, fileName), script);

        return $"{providerFolder}/{SeedPathResolver.ObjectFolder}/{fileName}";
    }

    /// <summary>
    /// Script tek bir nesne tanimlamalidir; basligi tekrar calismaya dayanikli bicime cevrilir.
    /// Yorumlar yalnizca aramada yok sayilir, dosyada kalir. Tek nesne tanimlamayan script icin null.
    /// </summary>
    private static CanonicalScript? Canonicalize(string script, Regex headerRegex, string createClause)
    {
        var headers = string.IsNullOrWhiteSpace(script) ? null : headerRegex.Matches(CommentRegex().Replace(script, " "));
        if (headers is not { Count: 1 })
            return null;

        var name = headers[0].Groups["name"].Value;
        var kind = headers[0].Groups["kind"].Value.ToUpperInvariant() switch
        {
            "PROC" => "PROCEDURE",
            var value => value
        };

        // Yorumdaki ornek baslik degismesin diye yalnizca nesnenin adini tasiyan ilk baslik cevrilir.
        var replaced = false;
        var canonical = headerRegex.Replace(script.Trim(), m =>
        {
            if (replaced || !string.Equals(m.Groups["name"].Value, name, StringComparison.OrdinalIgnoreCase))
                return m.Value;

            replaced = true;
            return $"{createClause} {kind} {m.Groups["qualified"].Value}";
        });

        return new CanonicalScript(canonical + Environment.NewLine, name, kind);
    }

    private sealed record CanonicalScript(string Script, string Name, string Kind);

    [GeneratedRegex(@"/\*.*?\*/|--[^\r\n]*", RegexOptions.Singleline)]
    private static partial Regex CommentRegex();

    [GeneratedRegex(@"\b(?:CREATE\s+OR\s+ALTER|CREATE|ALTER)\s+(?<kind>VIEW|PROCEDURE|PROC|FUNCTION)\s+(?<qualified>(?:\[?dbo\]?\s*\.\s*)?\[?(?<name>[A-Za-z_][A-Za-z0-9_]*)\]?)", RegexOptions.IgnoreCase | RegexOptions.CultureInvariant)]
    private static partial Regex SqlServerHeaderRegex();

    [GeneratedRegex(@"\bCREATE\s+(?:OR\s+REPLACE\s+)?(?<kind>VIEW|PROCEDURE|FUNCTION)\s+(?<qualified>(?:""?public""?\s*\.\s*)?""?(?<name>[A-Za-z_][A-Za-z0-9_]*)""?)", RegexOptions.IgnoreCase | RegexOptions.CultureInvariant)]
    private static partial Regex PostgreSqlHeaderRegex();
}

/// <param name="Kind"><c>VIEW</c>, <c>PROCEDURE</c> ya da <c>FUNCTION</c>.</param>
/// <param name="SqlServerPath">Seed kokune gore goreli yol.</param>
/// <param name="PostgreSqlPath">Seed kokune gore goreli yol.</param>
public sealed record McpSavedSqlObject(string Name, string Kind, string SqlServerPath, string PostgreSqlPath);