using System.Text.RegularExpressions;
using FrameworkBLL.Connection;

namespace FrameworkBLL.Engine
{
    /// <summary>
    /// Generates SELECT SQL dynamically from MDATASETDETAIL configuration and watermarks.
    /// Column names are not known at compile time — this is the documented exception to
    /// the "all SQL in QB constants" rule.
    /// </summary>
    internal static class SyncQueryBuilder
    {
        // ── Identifier quoting ────────────────────────────────────────────────

        // Returns a properly quoted identifier for the target RDBMS.
        internal static string Quote(string identifier, DatabaseType dbType) => dbType switch
        {
            DatabaseType.SqlServer  => $"[{identifier}]",
            DatabaseType.PostgreSQL => $"\"{identifier}\"",
            DatabaseType.MySQL      => $"`{identifier}`",
            DatabaseType.Oracle     => $"\"{identifier.ToUpper()}\"",
            _                       => $"[{identifier}]"
        };

        // Returns a fully-qualified [schema].[table] string appropriate for the RDBMS.
        // MySQL has no schema prefix at the SQL level — returns just the quoted table name.
        internal static string QuoteTable(string schema, string table, DatabaseType dbType) =>
            string.IsNullOrEmpty(schema)
                ? Quote(table, dbType)
                : $"{Quote(schema, dbType)}.{Quote(table, dbType)}";

        // ── Paged SELECT ──────────────────────────────────────────────────────

        // Builds a paged SELECT with watermark filters applied.
        // schemaName: default schema for the source DB (dbo / public / empty for MySQL)
        // tableName:  the table from DBOBJECT
        // pkColumn:   MDATASETDETAIL.PRIMARYKEYCOLUMN — used for ORDER BY and conflict detection
        // createdDateCol:  MDATASETDETAIL.CREATEDDATECOLUMN (null → skip INSERT watermark)
        // modifiedDateCol: MDATASETDETAIL.MODIFIEDDATECOLUMN (null → skip UPDATE watermark)
        // lastInsertedTill, lastUpdatedTill: watermarks from TDSYNCJOB (null = full load)
        // extraWhere: TDSYNCJOB.QUERYCONDITION (validated before injection — no DML/DDL/SELECT)
        internal static string BuildSelectSql(
            string schemaName,
            string tableName,
            string pkColumn,
            string? createdDateCol,
            string? modifiedDateCol,
            DateTime? lastInsertedTill,
            DateTime? lastUpdatedTill,
            string? extraWhere,
            DatabaseType dbType)
        {
            var where = new List<string>();

            // Insert-watermark and update-watermark conditions are alternatives, not a
            // conjunction — a row qualifies for sync if it was EITHER created OR modified
            // since the last run. Combining them with AND (as this previously did) meant a
            // row whose CreatedDateColumn predates the insert watermark could never match
            // even when it was genuinely updated afterward, since an old CREATEDON AND a new
            // MODIFIEDON can never both be true at once — silently excluding every real
            // update to a pre-existing row from every run after the first. Only a brand-new
            // row (whose CreatedOn and ModifiedOn are both recent) ever happened to satisfy
            // the old AND'd condition, which made initial full loads look correct while
            // masking that updates never actually synced.
            var watermarkConditions = new List<string>();

            // INSERT watermark: rows created since last run
            if (!string.IsNullOrWhiteSpace(createdDateCol) && lastInsertedTill.HasValue)
                watermarkConditions.Add($"{Quote(createdDateCol, dbType)} >= @LastInsertedTill");

            // UPDATE watermark: rows modified since last run
            if (!string.IsNullOrWhiteSpace(modifiedDateCol) && lastUpdatedTill.HasValue)
                watermarkConditions.Add($"{Quote(modifiedDateCol, dbType)} >= @LastUpdatedTill");

            if (watermarkConditions.Count > 0)
                where.Add(watermarkConditions.Count > 1
                    ? $"({string.Join(" OR ", watermarkConditions)})"
                    : watermarkConditions[0]);

            // Custom condition from TDSYNCJOB.QUERYCONDITION (user-supplied extra filter)
            if (!string.IsNullOrWhiteSpace(extraWhere))
            {
                ValidateExtraWhere(extraWhere);
                where.Add($"({extraWhere})");
            }

            var whereClause = where.Count > 0
                ? $"WHERE {string.Join(" AND ", where)}"
                : string.Empty;

            var tableFqn  = QuoteTable(schemaName, tableName, dbType);
            var pkQuoted  = Quote(pkColumn, dbType);

            // MySQL uses LIMIT n OFFSET x; SQL Server / PostgreSQL use OFFSET x FETCH NEXT n;
            // Oracle uses same OFFSET/FETCH syntax but with colon-prefixed bind variables.
            return dbType switch
            {
                DatabaseType.SqlServer or DatabaseType.PostgreSQL =>
                    $@"SELECT * FROM {tableFqn}
                    {whereClause}
                    ORDER BY {pkQuoted}
                    OFFSET @ChunkOffset ROWS FETCH NEXT @ChunkSize ROWS ONLY",

                DatabaseType.MySQL =>
                    $@"SELECT * FROM {tableFqn}
                    {whereClause}
                    ORDER BY {pkQuoted}
                    LIMIT @ChunkSize OFFSET @ChunkOffset",

                DatabaseType.Oracle =>
                    $@"SELECT * FROM {tableFqn}
                    {whereClause}
                    ORDER BY {pkQuoted}
                    OFFSET :ChunkOffset ROWS FETCH NEXT :ChunkSize ROWS ONLY",

                _ => throw new InvalidOperationException($"Unsupported DatabaseType {dbType}")
            };
        }

        // ── CDC delete detection (SQL Server only) ────────────────────────────

        // Builds the CDC delete-detection query.
        // pkColumn: MDATASETDETAIL.PRIMARYKEYCOLUMN — avoids the hardcoded TABLEID assumption.
        // SinceUtc is always passed as the @SinceUtc parameter, never interpolated.
        internal static string BuildCdcSelectSql(string tableName, string pkColumn, DateTime sinceUtc)
        {
            // CDC table: cdc.dbo_<Table>_CT  (__$operation=1 means delete)
            return $@"
                SELECT [{pkColumn}] AS PkValue
                FROM   cdc.[dbo_{tableName}_CT]
                WHERE  __$operation = 1
                  AND  __$start_lsn >= sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @SinceUtc)";
        }

        // ── QUERYCONDITION safety validation ──────────────────────────────────

        // Blocks DML, DDL, and the most common SELECT-injection vectors.
        // SELECT/FROM/UNION/INTO are blocked to prevent subquery injection and UNION-based exfiltration.
        private static readonly Regex _dmlPattern =
            new(@"\b(INSERT|UPDATE|DELETE|DROP|ALTER|CREATE|TRUNCATE|EXEC|EXECUTE|GRANT|REVOKE|DENY|UNION|SELECT|INTO|FROM)\b",
                RegexOptions.IgnoreCase | RegexOptions.Compiled);

        private static void ValidateExtraWhere(string extraWhere)
        {
            if (_dmlPattern.IsMatch(extraWhere))
                throw new InvalidOperationException(
                    "QUERYCONDITION contains disallowed keywords (DML/DDL/SELECT/UNION).");
        }
    }
}