using System.Globalization;
using System.Text;
using System.Text.RegularExpressions;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.DTO.Framework.SchemaIntrospection;

namespace SwBLL.Provisioning;

/// <summary>
/// Generic, registry-driven replacement for hand-authoring a reference/standard-data DdlScript
/// per table (tracker §36.2). Given a source connection + table name + optional row filter, this
/// introspects the table's real columns live (via the already-existing
/// ITargetDbExecutor.GetServerColumnsAsync, the same schema-browsing path the Analytics Catalog
/// Wizard already uses), pulls the matching rows, and emits the same idempotent
/// INSERT...SELECT...FROM(VALUES...)...WHERE NOT EXISTS shape proven by hand for
/// MCURRENCY/MUOM/MPAYMENTTERM (20260828_StandardRefData_V1_MCurrency_MUom_MPaymentTerm_SqlServer.sql)
/// — but without hardcoding a single column name or table shape, so a new table means a new
/// caller/registry row, not new code.
///
/// TENANTID handling is generic, not a per-table branch: if the introspected column list contains
/// a TENANTID column, its value is always emitted as DdlScriptTemplating.TenantIdToken
/// ("{{TENANTID}}") rather than copied from the source row — reference tables are commonly
/// tenant-scoped (confirmed live for MCURRENCY: every row on a real target carries one fixed
/// TENANTID equal to that database's own owning client, not a shared value), and a script
/// authored once against one source database must not leak that source's own TenantId into every
/// other client database it gets applied to.
///
/// A small set of well-known audit/provenance columns are always overridden with fixed values
/// rather than copied from the source row, matching the convention already established for every
/// hand-authored platform seed script in this repo (see e.g.
/// 20260716_Entitlement_Phase2_5_PasswordResetMailTemplate_Seed_SqlServer.sql's own header:
/// "SOURCETYPE = 1 (Framework) -- this row is migration-seeded platform configuration") — a
/// generated script is itself a fresh platform delivery, not a historical record of who created
/// the source's own copy of the row.
/// </summary>
public interface IReferenceDataScriptGenerator
{
    Task<string> GenerateSeedScriptAsync(
        string sourceConnectionString, byte sourceDbType,
        string tableName, string? rowFilterSql,
        CancellationToken ct);
}

public class ReferenceDataScriptGenerator : IReferenceDataScriptGenerator
{
    private readonly ITargetDbExecutor _targetDbExecutor;

    public ReferenceDataScriptGenerator(ITargetDbExecutor targetDbExecutor)
        => _targetDbExecutor = targetDbExecutor;

    private static readonly IReadOnlyDictionary<string, string> OverrideColumns =
        new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["CREATEDBYID"]  = "-1",
            ["CREATEDON"]    = "GETDATE()",
            ["MODIFIEDBYID"] = "-1",
            ["MODIFIEDON"]   = "GETDATE()",
            ["SOURCETYPE"]   = "1",   // Framework — this batch is itself a fresh platform delivery
            ["VERSION"]      = "0",  // optimistic-concurrency counter for a newly-delivered row —
                                      // copying the source's own arbitrary historical edit-count
                                      // (confirmed live: GB5DEMO's real MCURRENCY rows carry
                                      // VERSION values like 2/3/4) would be meaningless here.
        };

    private const string TenantIdColumn = "TENANTID";

    private static readonly HashSet<string> NumericTypes = new(StringComparer.OrdinalIgnoreCase)
        { "int", "tinyint", "smallint", "bigint", "decimal", "numeric", "float", "real", "bit", "money", "smallmoney" };

    private static readonly HashSet<string> DateTypes = new(StringComparer.OrdinalIgnoreCase)
        { "datetime", "datetime2", "smalldatetime", "date" };

    // Same discipline as SyncQueryBuilder's QUERYCONDITION guard — a caller-supplied row filter
    // is registry-authored configuration, not end-user input, but gets the identical defense-in-
    // depth treatment since its output ultimately becomes governed script content.
    private static readonly Regex DisallowedKeywords =
        new(@"\b(INSERT|UPDATE|DELETE|DROP|ALTER|CREATE|TRUNCATE|EXEC|EXECUTE|GRANT|REVOKE|DENY|UNION|SELECT|INTO|FROM)\b",
            RegexOptions.IgnoreCase | RegexOptions.Compiled);

    public async Task<string> GenerateSeedScriptAsync(
        string sourceConnectionString, byte sourceDbType,
        string tableName, string? rowFilterSql,
        CancellationToken ct)
    {
        if (string.IsNullOrWhiteSpace(tableName))
            throw new ArgumentException("TableName is required.", nameof(tableName));

        if (!string.IsNullOrWhiteSpace(rowFilterSql) && DisallowedKeywords.IsMatch(rowFilterSql))
            throw new InvalidOperationException("Row filter contains disallowed keywords (DML/DDL/SELECT/UNION).");

        var allColumns = await _targetDbExecutor.GetServerColumnsAsync(sourceConnectionString, sourceDbType, ct)
            .ConfigureAwait(false);

        var columns = allColumns
            .Where(c => c.TableName.Equals(tableName, StringComparison.OrdinalIgnoreCase))
            .OrderBy(c => c.OrdinalPosition)
            .ToList();

        if (columns.Count == 0)
            throw new InvalidOperationException($"Table '{tableName}' has no columns (not found, or introspection returned nothing).");

        var pkColumn = columns.FirstOrDefault(c => c.IsPrimaryKey)?.ColumnName
            ?? throw new InvalidOperationException($"Table '{tableName}' has no single-column primary key — not supported by this generator.");

        bool hasTenantId = columns.Any(c => c.ColumnName.Equals(TenantIdColumn, StringComparison.OrdinalIgnoreCase));

        string whereClause = string.IsNullOrWhiteSpace(rowFilterSql) ? string.Empty : $"WHERE {rowFilterSql}";
        string selectSql = $"SELECT * FROM {tableName} {whereClause}".Trim();

        var rows = await _targetDbExecutor.ExecuteQueryAsync(sourceConnectionString, selectSql, [], ct)
            .ConfigureAwait(false);
        var rowList = rows.ToList();

        if (rowList.Count == 0)
            throw new InvalidOperationException($"No rows matched for table '{tableName}' with the given filter — nothing to generate.");

        var columnNames = columns.Select(c => c.ColumnName).ToList();

        var valuesRows = rowList.Select(row => "    (" + string.Join(", ", columns.Select(c =>
            FormatColumnValue(c, row.TryGetValue(c.ColumnName, out var v) ? v : null, hasTenantId))) + ")");

        var columnListSql = string.Join(", ", columnNames);
        var selectListSql = string.Join(", ", columnNames.Select(c => $"v.{c}"));

        var sb = new StringBuilder();
        sb.AppendLine($"INSERT INTO {tableName}");
        sb.AppendLine($"    ({columnListSql})");
        sb.AppendLine($"SELECT {selectListSql}");
        sb.AppendLine($"FROM (VALUES");
        sb.AppendLine(string.Join(",\n", valuesRows));
        sb.AppendLine($") AS v({columnListSql})");
        sb.AppendLine($"WHERE NOT EXISTS (SELECT 1 FROM {tableName} x WHERE x.{pkColumn} = v.{pkColumn});");

        return sb.ToString();
    }

    private static string FormatColumnValue(SchemaTableColumnDTO column, object? value, bool hasTenantId)
    {
        if (column.ColumnName.Equals(TenantIdColumn, StringComparison.OrdinalIgnoreCase))
            return DdlScriptTemplating.TenantIdToken;

        if (OverrideColumns.TryGetValue(column.ColumnName, out var overrideLiteral))
            return overrideLiteral;

        if (value is null || value is DBNull)
            return "NULL";

        if (NumericTypes.Contains(column.DataType))
            return Convert.ToString(value, CultureInfo.InvariantCulture) ?? "NULL";

        if (DateTypes.Contains(column.DataType))
            return $"'{Convert.ToDateTime(value, CultureInfo.InvariantCulture):yyyy-MM-dd HH:mm:ss}'";

        return $"N'{value.ToString()!.Replace("'", "''")}'";
    }
}