using System;
using System.Text.RegularExpressions;

namespace SyncEngine.Access
{
    /// <summary>Translates a subset of Access ValidationRule expressions to T-SQL CHECK predicates.</summary>
    public static class AccessValidationTranslator
    {
        public static bool TryTranslate(string accessRule, string columnName, out string sqlPredicate)
        {
            sqlPredicate = null;
            if (string.IsNullOrWhiteSpace(accessRule))
            {
                return false;
            }

            var expr = accessRule.Trim();
            expr = Regex.Replace(expr, @"\bIs Not Null\b", " IS NOT NULL ", RegexOptions.IgnoreCase);
            expr = Regex.Replace(expr, @"\bIs Null\b", " IS NULL ", RegexOptions.IgnoreCase);
            expr = Regex.Replace(expr, @"\bAnd\b", " AND ", RegexOptions.IgnoreCase);
            expr = Regex.Replace(expr, @"\bOr\b", " OR ", RegexOptions.IgnoreCase);
            expr = Regex.Replace(expr, @"\bNot\b", " NOT ", RegexOptions.IgnoreCase);
            expr = Regex.Replace(expr, @"\bLike\b", " LIKE ", RegexOptions.IgnoreCase);
            expr = Regex.Replace(expr, @"\bBetween\b", " BETWEEN ", RegexOptions.IgnoreCase);
            expr = Regex.Replace(expr, @"\bTrue\b", "1", RegexOptions.IgnoreCase);
            expr = Regex.Replace(expr, @"\bFalse\b", "0", RegexOptions.IgnoreCase);

            if (!string.IsNullOrWhiteSpace(columnName) && !expr.Contains("["))
            {
                expr = PrefixColumnName(expr, columnName);
            }

            if (expr.IndexOfAny(new[] { ';', '\r', '\n' }) >= 0)
            {
                return false;
            }

            if (Regex.IsMatch(expr, @"\b(SELECT|INSERT|UPDATE|DELETE|DROP|EXEC)\b", RegexOptions.IgnoreCase))
            {
                return false;
            }

            sqlPredicate = Regex.Replace(expr.Trim(), @"\s+", " ");
            return sqlPredicate.Length > 0;
        }

        private static string PrefixColumnName(string expr, string columnName)
        {
            var trimmed = expr.TrimStart();
            if (trimmed.StartsWith(">=", StringComparison.Ordinal) ||
                trimmed.StartsWith("<=", StringComparison.Ordinal) ||
                trimmed.StartsWith("<>", StringComparison.Ordinal) ||
                trimmed.StartsWith("!=", StringComparison.Ordinal) ||
                trimmed.StartsWith(">", StringComparison.Ordinal) ||
                trimmed.StartsWith("<", StringComparison.Ordinal) ||
                trimmed.StartsWith("=", StringComparison.Ordinal))
            {
                return "[" + columnName + "]" + trimmed;
            }

            if (Regex.IsMatch(trimmed, @"^(IS NULL|IS NOT NULL|LIKE|BETWEEN|\d)", RegexOptions.IgnoreCase))
            {
                return "[" + columnName + "] " + trimmed;
            }

            return expr;
        }

        public static string BuildCheckConstraintName(string tableName, string columnName)
        {
            var suffix = string.IsNullOrWhiteSpace(columnName) ? "table" : columnName;
            var name = "CK_" +
                SqlServer.SqlIdentifierHelper.SanitizeConstraintName(tableName) + "_" +
                SqlServer.SqlIdentifierHelper.SanitizeConstraintName(suffix);
            return name.Length <= 128 ? name : name.Substring(0, 128);
        }
    }
}