using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using SyncEngine.Access;

namespace SyncEngine.SqlServer
{
    public static class SqlViewTranslator
    {
        private static readonly string[] DateFormats =
        {
            "yyyy/MM/dd", "yyyy/M/d", "yyyy-MM-dd", "M/d/yyyy", "MM/dd/yyyy", "d/M/yyyy", "dd/MM/yyyy"
        };

        public static string Translate(string accessSql, ViewColumnCatalog catalog = null)
        {
            if (string.IsNullOrWhiteSpace(accessSql))
            {
                return null;
            }

            var sql = accessSql.Trim();
            sql = StripParametersBlock(sql);
            sql = TranslateAccessLogicalOperators(sql);
            sql = TranslateIIfCalls(sql);
            sql = NormalizeAccessDoubleQuotedStrings(sql);
            sql = Regex.Replace(sql, @"#([^#]+)#", QuoteAccessDateLiteral);
            sql = QuoteBareDateLiterals(sql);
            sql = Regex.Replace(sql, @"'(\d{4}/\d{1,2}/\d{1,2})'", m => QuoteDateString(m.Groups[1].Value));
            sql = Regex.Replace(sql, @"\bNz\s*\(\s*([^',()]+)\s*\)", "ISNULL($1, '')", RegexOptions.IgnoreCase);
            sql = Regex.Replace(sql, @"\bNz\s*\(", "ISNULL(", RegexOptions.IgnoreCase);
            sql = FixSingleArgumentIsNull(sql);
            sql = FixIsNullUsedAsBooleanCondition(sql);
            sql = FixMalformedQuotedComparisons(sql);
            sql = Regex.Replace(sql, @"\bFirst\s*\(", "MIN(", RegexOptions.IgnoreCase);
            sql = StripTrailingOrderBy(sql);
            sql = ExpandTableStarInSelectList(sql, catalog);
            sql = Regex.Replace(sql, @"\bIs Null\b", "IS NULL", RegexOptions.IgnoreCase);
            sql = Regex.Replace(sql, @"\bIs Not Null\b", "IS NOT NULL", RegexOptions.IgnoreCase);
            sql = Regex.Replace(sql, @"\s&\s", " + ", RegexOptions.None);
            sql = Regex.Replace(sql, @"\bTrue\b", "1", RegexOptions.IgnoreCase);
            sql = Regex.Replace(sql, @"\bFalse\b", "0", RegexOptions.IgnoreCase);
            sql = Regex.Replace(sql, @"\[([^\]]+)\]", "[$1]");

            return sql;
        }

        public static bool IsLikelyTranslatable(string sql)
        {
            if (string.IsNullOrWhiteSpace(sql))
            {
                return false;
            }

            return sql.IndexOf("TRANSFORM", StringComparison.OrdinalIgnoreCase) < 0 &&
                   sql.IndexOf("CROSSTAB", StringComparison.OrdinalIgnoreCase) < 0;
        }

        private static string StripParametersBlock(string sql)
        {
            if (!sql.StartsWith("PARAMETERS", StringComparison.OrdinalIgnoreCase))
            {
                return sql;
            }

            var semi = sql.IndexOf(';');
            return semi > 0 ? sql.Substring(semi + 1).Trim() : sql;
        }

        private static string TranslateAccessLogicalOperators(string sql)
        {
            sql = Regex.Replace(sql, @"\bAnd\b", "AND", RegexOptions.IgnoreCase);
            sql = Regex.Replace(sql, @"\bOr\b", "OR", RegexOptions.IgnoreCase);
            sql = Regex.Replace(sql, @"\bNot\b", "NOT", RegexOptions.IgnoreCase);
            return sql;
        }

        private static string TranslateIIfCalls(string sql)
        {
            while (true)
            {
                var iifIndex = FindInnermostIIfIndex(sql);
                if (iifIndex < 0)
                {
                    break;
                }

                var openParen = sql.IndexOf('(', iifIndex);
                string inner;
                int closeParen;
                if (!TryExtractParenthesesContent(sql, openParen, out inner, out closeParen))
                {
                    break;
                }

                var parts = SplitTopLevelArguments(inner);
                if (parts.Count != 3)
                {
                    break;
                }

                var condition = FormatIIfCondition(parts[0].Trim());
                var replacement = string.Format(
                    "CASE WHEN {0} THEN {1} ELSE {2} END",
                    condition,
                    parts[1].Trim(),
                    parts[2].Trim());

                sql = sql.Substring(0, iifIndex) + replacement + sql.Substring(closeParen + 1);
            }

            return sql;
        }

        /// <summary>Access allows numeric/string expressions as IIf conditions; SQL Server needs boolean.</summary>
        private static string FormatIIfCondition(string condition)
        {
            if (ContainsComparisonOperator(condition))
            {
                return condition;
            }

            return string.Format("({0} IS NOT NULL AND {0} <> 0 AND {0} <> '')", condition);
        }

        private static bool ContainsComparisonOperator(string condition)
        {
            return condition.IndexOf('=') >= 0 ||
                   condition.IndexOf('<') >= 0 ||
                   condition.IndexOf('>') >= 0 ||
                   condition.IndexOf(" LIKE ", StringComparison.OrdinalIgnoreCase) >= 0 ||
                   condition.IndexOf(" IS ", StringComparison.OrdinalIgnoreCase) >= 0 ||
                   condition.IndexOf(" IN ", StringComparison.OrdinalIgnoreCase) >= 0 ||
                   condition.IndexOf(" BETWEEN ", StringComparison.OrdinalIgnoreCase) >= 0 ||
                   condition.IndexOf(" AND ", StringComparison.OrdinalIgnoreCase) >= 0 ||
                   condition.IndexOf(" OR ", StringComparison.OrdinalIgnoreCase) >= 0;
        }

        private static int FindInnermostIIfIndex(string sql)
        {
            var searchFrom = 0;
            while (searchFrom < sql.Length)
            {
                var iifIndex = IndexOfIgnoreCase(sql, "IIf", searchFrom);
                if (iifIndex < 0)
                {
                    return -1;
                }

                var openParen = sql.IndexOf('(', iifIndex);
                string inner;
                int closeParen;
                if (!TryExtractParenthesesContent(sql, openParen, out inner, out closeParen))
                {
                    searchFrom = iifIndex + 3;
                    continue;
                }

                if (IndexOfIgnoreCase(inner, "IIf", 0) < 0)
                {
                    return iifIndex;
                }

                searchFrom = iifIndex + 3;
            }

            return -1;
        }

        private static string NormalizeAccessDoubleQuotedStrings(string sql)
        {
            return Regex.Replace(sql, @"""([^""]*)""", m => QuoteDateString(m.Groups[1].Value.Trim()));
        }

        /// <summary>Access Nz(field) in OR/AND is truthy; ISNULL(field,'') alone is not boolean in SQL Server.</summary>
        private static string FixIsNullUsedAsBooleanCondition(string sql)
        {
            return Regex.Replace(
                sql,
                @"\(\s*(ISNULL\([^)]+\))\s*\)(?=\s*(?:AND|OR|\)|;|$))",
                "($1 <> '')",
                RegexOptions.IgnoreCase);
        }

        /// <summary>Fix (field)&lt;"'2025-01-01'" from mixed Access quoting.</summary>
        private static string FixMalformedQuotedComparisons(string sql)
        {
            return Regex.Replace(
                sql,
                @"([<>=!]+)\s*""'([^']*)'""",
                "$1 '$2'");
        }

        /// <summary>Expand table.* to explicit columns using Access schema (dedupes columns already in SELECT).</summary>
        private static string ExpandTableStarInSelectList(string sql, ViewColumnCatalog catalog)
        {
            if (catalog == null)
            {
                return sql;
            }

            int selectListStart;
            int fromIndex;
            if (!TryExtractSelectListBounds(sql, out selectListStart, out fromIndex))
            {
                return sql;
            }

            var selectList = sql.Substring(selectListStart, fromIndex - selectListStart);
            var starPattern = new Regex(@",?\s*((?:\[[^\]]+\]|\w+))\.\*", RegexOptions.IgnoreCase);
            var matches = starPattern.Matches(selectList);
            if (matches.Count == 0)
            {
                return sql;
            }

            var result = selectList;
            for (var i = matches.Count - 1; i >= 0; i--)
            {
                var match = matches[i];
                var tableRef = match.Groups[1].Value;
                IReadOnlyList<string> columns;
                if (!catalog.TryGetColumns(tableRef, out columns))
                {
                    continue;
                }

                var listed = GetAlreadyListedColumns(selectList, tableRef, match.Index);
                var expanded = columns
                    .Where(col => !listed.Contains(col))
                    .Select(col => QualifyColumn(tableRef, col))
                    .ToList();

                var hasLeadingComma = match.Value.TrimStart().StartsWith(",", StringComparison.Ordinal);
                var replacement = expanded.Count == 0
                    ? string.Empty
                    : (hasLeadingComma ? ", " : string.Empty) + string.Join(", ", expanded);

                result = result.Remove(match.Index, match.Length).Insert(match.Index, replacement);
            }

            result = Regex.Replace(result, @",\s*,", ",");
            result = Regex.Replace(result, @",\s*$", string.Empty);
            result = Regex.Replace(result, @"^\s*,\s*", string.Empty);

            if (string.Equals(result, selectList, StringComparison.Ordinal))
            {
                return sql;
            }

            return sql.Substring(0, selectListStart) + result + sql.Substring(fromIndex);
        }

        private static HashSet<string> GetAlreadyListedColumns(string selectList, string tableRef, int beforeIndex)
        {
            var listed = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
            var portion = selectList.Substring(0, beforeIndex);
            var prefix = Regex.Escape(tableRef) + @"\.(?:\[([^\]]+)\]|(\w+))";
            foreach (Match match in Regex.Matches(portion, prefix, RegexOptions.IgnoreCase))
            {
                var col = match.Groups[1].Success ? match.Groups[1].Value : match.Groups[2].Value;
                listed.Add(col);
            }

            return listed;
        }

        private static string QualifyColumn(string tableRef, string columnName)
        {
            var colNeedsBrackets = columnName.IndexOf(' ') >= 0;
            if (tableRef.StartsWith("[", StringComparison.Ordinal))
            {
                return colNeedsBrackets
                    ? tableRef + ".[" + columnName + "]"
                    : tableRef + "." + columnName;
            }

            return colNeedsBrackets
                ? tableRef + ".[" + columnName + "]"
                : tableRef + "." + columnName;
        }

        private static bool TryExtractSelectListBounds(string sql, out int selectListStart, out int fromIndex)
        {
            selectListStart = -1;
            fromIndex = -1;
            var selectMatch = Regex.Match(sql, @"^\s*SELECT\s+", RegexOptions.IgnoreCase);
            if (!selectMatch.Success)
            {
                return false;
            }

            selectListStart = selectMatch.Index + selectMatch.Length;
            fromIndex = FindTopLevelFromIndex(sql, selectListStart);
            return fromIndex > selectListStart;
        }

        private static int FindTopLevelFromIndex(string sql, int start)
        {
            var searchFrom = start;
            while (searchFrom < sql.Length)
            {
                var idx = IndexOfIgnoreCase(sql, "FROM", searchFrom);
                if (idx < 0)
                {
                    return -1;
                }

                if (IsTopLevelPosition(sql, idx))
                {
                    return idx;
                }

                searchFrom = idx + 4;
            }

            return -1;
        }

        /// <summary>SQL Server views cannot end with ORDER BY unless TOP/OFFSET is present.</summary>
        private static string StripTrailingOrderBy(string sql)
        {
            var trimmed = sql.Trim();
            var hadSemicolon = trimmed.EndsWith(";", StringComparison.Ordinal);
            if (hadSemicolon)
            {
                trimmed = trimmed.Substring(0, trimmed.Length - 1).TrimEnd();
            }

            var orderByIndex = FindTopLevelOrderByIndex(trimmed);
            if (orderByIndex < 0)
            {
                return sql;
            }

            var result = trimmed.Substring(0, orderByIndex).TrimEnd();
            return hadSemicolon ? result + ";" : result;
        }

        private static int FindTopLevelOrderByIndex(string sql)
        {
            var lastIndex = -1;
            var searchFrom = 0;
            while (searchFrom < sql.Length)
            {
                var idx = IndexOfIgnoreCase(sql, "ORDER BY", searchFrom);
                if (idx < 0)
                {
                    break;
                }

                if (IsTopLevelPosition(sql, idx))
                {
                    lastIndex = idx;
                }

                searchFrom = idx + 8;
            }

            return lastIndex;
        }

        private static bool IsTopLevelPosition(string sql, int keywordStart)
        {
            var depth = 0;
            var inString = false;
            for (var i = 0; i < keywordStart; i++)
            {
                var ch = sql[i];
                if (ch == '\'')
                {
                    inString = !inString;
                }
                else if (!inString)
                {
                    if (ch == '(')
                    {
                        depth++;
                    }
                    else if (ch == ')')
                    {
                        depth--;
                    }
                }
            }

            return depth == 0 && !inString;
        }

        private static string QuoteAccessDateLiteral(Match match)
        {
            return QuoteDateString(match.Groups[1].Value.Trim());
        }

        private static string QuoteBareDateLiterals(string sql)
        {
            // Skip dates already inside single-quoted literals ('2025/01/01').
            return Regex.Replace(
                sql,
                @"(?<![#'\w\]])(\d{4}/\d{1,2}/\d{1,2})(?![#/\w'])",
                m => QuoteDateString(m.Groups[1].Value));
        }

        private static string QuoteDateString(string inner)
        {
            if (inner.StartsWith("'", StringComparison.Ordinal) && inner.EndsWith("'", StringComparison.Ordinal))
            {
                inner = inner.Substring(1, inner.Length - 2);
            }

            DateTime parsed;
            if (DateTime.TryParseExact(inner, DateFormats, CultureInfo.InvariantCulture, DateTimeStyles.None, out parsed) ||
                DateTime.TryParse(inner, CultureInfo.InvariantCulture, DateTimeStyles.None, out parsed) ||
                DateTime.TryParse(inner, out parsed))
            {
                return string.Format("'{0:yyyy-MM-dd}'", parsed);
            }

            return string.Format("'{0}'", inner.Replace("'", "''"));
        }

        private static string FixSingleArgumentIsNull(string sql)
        {
            return Regex.Replace(
                sql,
                @"\bISNULL\s*\(\s*([^',()]+)\s*\)",
                "ISNULL($1, '')",
                RegexOptions.IgnoreCase);
        }

        private static int IndexOfIgnoreCase(string text, string value, int start)
        {
            return text.IndexOf(value, start, StringComparison.OrdinalIgnoreCase);
        }

        private static bool TryExtractParenthesesContent(string sql, int openParen, out string inner, out int closeParen)
        {
            inner = null;
            closeParen = -1;
            if (openParen < 0 || openParen >= sql.Length || sql[openParen] != '(')
            {
                return false;
            }

            var depth = 0;
            for (var i = openParen; i < sql.Length; i++)
            {
                var ch = sql[i];
                if (ch == '(')
                {
                    depth++;
                }
                else if (ch == ')')
                {
                    depth--;
                    if (depth == 0)
                    {
                        closeParen = i;
                        inner = sql.Substring(openParen + 1, i - openParen - 1);
                        return true;
                    }
                }
            }

            return false;
        }

        private static IList<string> SplitTopLevelArguments(string args)
        {
            var parts = new List<string>();
            var current = new StringBuilder();
            var depth = 0;
            var inString = false;

            for (var i = 0; i < args.Length; i++)
            {
                var ch = args[i];
                if (ch == '\'')
                {
                    inString = !inString;
                    current.Append(ch);
                }
                else if (!inString && ch == '(')
                {
                    depth++;
                    current.Append(ch);
                }
                else if (!inString && ch == ')')
                {
                    depth--;
                    current.Append(ch);
                }
                else if (!inString && ch == ',' && depth == 0)
                {
                    parts.Add(current.ToString());
                    current.Clear();
                }
                else
                {
                    current.Append(ch);
                }
            }

            if (current.Length > 0)
            {
                parts.Add(current.ToString());
            }

            return parts;
        }
    }
}