using System.Text;
using System.Text.RegularExpressions;
using SwDAL.DTO.MetadataSync;

namespace SwBLL.MetadataSync;

/// <summary>
/// Builds the DML script attached to a Draft ChangeRequest from a metadata version's tracked
/// changes. Extracted from MetadataSyncBLL for testability and to fix a real injection risk:
/// TableName/ColumnName arrive as free text via SaveMetadataChange (client-editable), and were
/// previously interpolated into DDL/DML text with no validation at all — only NewValue/OldValue
/// were escaped (naively, via Replace("'", "''")).
///
/// This is dynamic multi-statement SQL script text destined for ITargetDbExecutor (executed
/// against an external target database, not GB5's own DB via IQueryExecutor) — Dapper-style
/// parameterization does not apply to identifiers (table/column names can never be SQL
/// parameters in any dialect). The defense here is structural: TableName/ColumnName must match
/// a strict identifier pattern before they are ever interpolated, so no injection payload
/// (quotes, semicolons, comment markers, keywords) can survive validation regardless of its
/// content.
/// </summary>
public static class MetadataSyncSqlBuilder
{
    // Schema-qualified SQL identifier: letters/digits/underscore, optionally one "schema.table"
    // dot-separated segment. Matches SQL Server/Postgres/MySQL identifier rules across the
    // multi-DBMS targets this module is heading toward — deliberately conservative (no quoted
    // identifiers, no brackets) since nothing legitimate in this codebase's own schema needs them.
    private static readonly Regex ValidIdentifier =
        new(@"^[A-Za-z_][A-Za-z0-9_]*(\.[A-Za-z_][A-Za-z0-9_]*)?$", RegexOptions.Compiled);

    public static string BuildDmlFromChanges(string versionLabel, IList<MetadataChangeDTO> changes)
    {
        var sb = new StringBuilder();
        sb.AppendLine($"-- MetadataSync: {versionLabel}");
        sb.AppendLine($"-- Generated on: {DateTime.UtcNow:yyyy-MM-dd HH:mm:ss} UTC");
        sb.AppendLine();

        foreach (var c in changes)
        {
            var tableName  = RequireValidIdentifier(c.TableName, nameof(c.TableName), c);
            var columnName = RequireValidIdentifier(c.ColumnName, nameof(c.ColumnName), c);

            var type    = (c.ChangeType ?? string.Empty).Trim().ToUpperInvariant();
            var safeNew = QuoteLiteral(c.NewValue);
            var safeOld = QuoteLiteral(c.OldValue);

            switch (type)
            {
                case "INSERT":
                    sb.AppendLine($"INSERT INTO {tableName} ({columnName}) VALUES ({safeNew});");
                    break;
                case "UPDATE":
                    sb.AppendLine($"UPDATE {tableName} SET {columnName} = {safeNew} WHERE {columnName} = {safeOld};");
                    break;
                case "DELETE":
                    sb.AppendLine($"DELETE FROM {tableName} WHERE {columnName} = {safeOld};");
                    break;
                default:
                    sb.AppendLine($"-- {tableName}.{columnName}: {c.ChangeType} (OldValue={safeOld}, NewValue={safeNew})");
                    break;
            }
        }

        return sb.ToString();
    }

    /// <summary>
    /// Rejects anything that isn't a plain (optionally schema-qualified) SQL identifier —
    /// the only structural guarantee available without a live schema-catalog check.
    /// </summary>
    private static string RequireValidIdentifier(string? value, string fieldName, MetadataChangeDTO change)
    {
        if (string.IsNullOrWhiteSpace(value) || !ValidIdentifier.IsMatch(value))
            throw new InvalidOperationException(
                $"MetadataChange {change.MetaVersionId}: {fieldName} '{value}' is not a valid SQL identifier — refusing to generate DML.");

        return value;
    }

    /// <summary>
    /// Standard SQL string-literal escaping (single-quote doubling) applied to a value already
    /// wrapped in quotes — NULL maps to the SQL literal NULL, not the string "NULL".
    /// </summary>
    private static string QuoteLiteral(string? value)
        => value is null ? "NULL" : $"'{value.Replace("'", "''")}'";
}