#nullable enable

using System.Collections.ObjectModel;
using System.Data;

namespace MMoneyCoderSharp.Data;

/// <summary>
/// Describes one provider-neutral input parameter for a read query.
/// Names are logical identifiers; the SQL supplies the provider marker
/// (<c>:name</c> for Oracle or <c>@name</c> for MariaDB).
/// </summary>
public sealed class DataQueryParameter
{
    private const int MaximumNameLength = 128;

    public DataQueryParameter(string name, object? value, DbType? dbType = null)
    {
        if (!IsValidName(name))
        {
            throw new ArgumentException(
                "A query parameter name is invalid.",
                nameof(name));
        }

        if (dbType.HasValue && !Enum.IsDefined(dbType.Value))
        {
            throw new ArgumentOutOfRangeException(
                nameof(dbType),
                "A query parameter database type is invalid.");
        }

        Name = name;
        Value = value;
        DbType = dbType;
    }

    public string Name { get; }

    public object? Value { get; }

    public DbType? DbType { get; }

    public override string ToString() => "Data query parameter";

    internal static bool IsValidName(string? name)
    {
        if (string.IsNullOrEmpty(name) || name.Length > MaximumNameLength)
        {
            return false;
        }

        if (!IsAsciiLetter(name[0]) && name[0] != '_')
        {
            return false;
        }

        for (var index = 1; index < name.Length; index++)
        {
            var character = name[index];
            if (!IsAsciiLetter(character) && !char.IsAsciiDigit(character) && character != '_')
            {
                return false;
            }
        }

        return true;
    }

    private static bool IsAsciiLetter(char character) =>
        character is >= 'A' and <= 'Z' or >= 'a' and <= 'z';
}

/// <summary>
/// An immutable, validated SELECT/WITH query and its input parameters.
/// The deliberately conservative validation fails closed for ambiguous SQL.
/// Each logical parameter must occur exactly once; use distinct logical names
/// when one value is intentionally bound at multiple SQL positions.
/// </summary>
public sealed class DataQuerySpec
{
    private const int MaximumSqlLength = 65_536;

    private static readonly HashSet<string> StateChangingTokens = new(
        [
            "INSERT",
            "UPDATE",
            "DELETE",
            "REPLACE",
            "MERGE",
            "CALL",
            "EXEC",
            "EXECUTE",
            "CREATE",
            "ALTER",
            "DROP",
            "TRUNCATE",
            "GRANT",
            "REVOKE",
            "INTO",
            "LOCK",
            "FUNCTION",
            "PROCEDURE",
            "BEGIN",
            "DECLARE",
            "PRAGMA"
        ],
        StringComparer.OrdinalIgnoreCase);

    private readonly ReadOnlyCollection<ParameterReference> _references;

    public DataQuerySpec(string sql)
        : this(sql, Array.Empty<DataQueryParameter>())
    {
    }

    public DataQuerySpec(string sql, IEnumerable<DataQueryParameter> parameters)
    {
        if (string.IsNullOrWhiteSpace(sql) || sql.Length > MaximumSqlLength || sql.IndexOf('\0') >= 0)
        {
            throw InvalidSql(nameof(sql));
        }

        ArgumentNullException.ThrowIfNull(parameters);

        var suppliedParameters = parameters.ToArray();
        if (suppliedParameters.Any(static parameter => parameter is null))
        {
            throw new ArgumentException(
                "A query parameter collection is invalid.",
                nameof(parameters));
        }

        var parametersByName = new Dictionary<string, DataQueryParameter>(
            StringComparer.OrdinalIgnoreCase);
        foreach (var parameter in suppliedParameters)
        {
            if (!parametersByName.TryAdd(parameter.Name, parameter))
            {
                throw new ArgumentException(
                    "A query parameter collection is invalid.",
                    nameof(parameters));
            }
        }

        var parsed = Parse(sql);
        if (parsed.Tokens.Count == 0 ||
            (!string.Equals(parsed.Tokens[0], "SELECT", StringComparison.OrdinalIgnoreCase) &&
             !string.Equals(parsed.Tokens[0], "WITH", StringComparison.OrdinalIgnoreCase)) ||
            parsed.Tokens.Any(StateChangingTokens.Contains))
        {
            throw InvalidSql(nameof(sql));
        }

        var referencedNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase);
        foreach (var reference in parsed.References)
        {
            if (!referencedNames.Add(reference.Name) || !parametersByName.ContainsKey(reference.Name))
            {
                throw InvalidSql(nameof(sql));
            }
        }

        if (referencedNames.Count != parametersByName.Count)
        {
            throw new ArgumentException(
                "A query parameter collection does not match the query.",
                nameof(parameters));
        }

        // Preserve SQL placeholder order. This also keeps positional Oracle binding
        // deterministic without constructing provider-specific parameter objects.
        var orderedParameters = parsed.References
            .Select(reference => parametersByName[reference.Name])
            .ToArray();

        Sql = sql;
        Parameters = Array.AsReadOnly(orderedParameters);
        _references = Array.AsReadOnly(parsed.References.ToArray());
    }

    public string Sql { get; }

    public IReadOnlyList<DataQueryParameter> Parameters { get; }

    /// <summary>
    /// Verifies that all placeholders use the selected provider's marker.
    /// </summary>
    public void ValidateFor(DataSourceKind source)
    {
        var expectedMarker = source switch
        {
            DataSourceKind.Oracle => ':',
            DataSourceKind.MariaDb => '@',
            _ => throw new ArgumentOutOfRangeException(
                nameof(source),
                "The query data source is invalid.")
        };

        if (_references.Any(reference => reference.Marker != expectedMarker))
        {
            throw InvalidSql(nameof(source));
        }
    }

    public override string ToString() => "Parameterized read query";

    private static ParsedSql Parse(string sql)
    {
        var tokens = new List<string>();
        var references = new List<ParameterReference>();

        for (var index = 0; index < sql.Length;)
        {
            var current = sql[index];

            if (IsPortableSqlWhitespace(current))
            {
                index++;
                continue;
            }

            if (current > 0x7f)
            {
                // Unquoted identifiers and whitespace differ between providers.
                // Unicode remains supported inside quoted text and comments only.
                throw InvalidSql(nameof(sql));
            }

            if (current == '-' && index + 1 < sql.Length && sql[index + 1] == '-')
            {
                if (index + 2 < sql.Length && !IsPortableLineCommentWhitespace(sql[index + 2]))
                {
                    // MariaDB only recognizes -- as a comment when whitespace follows.
                    // Reject the cross-provider ambiguity instead of skipping SQL text.
                    throw InvalidSql(nameof(sql));
                }

                index += 2;
                while (index < sql.Length && sql[index] is not '\r' and not '\n')
                {
                    index++;
                }

                continue;
            }

            if (current == '/' && index + 1 < sql.Length && sql[index + 1] == '*')
            {
                if ((index + 2 < sql.Length && sql[index + 2] == '!') ||
                    (index + 3 < sql.Length &&
                     (sql[index + 2] is 'M' or 'm') &&
                     sql[index + 3] == '!'))
                {
                    // MySQL/MariaDB execute version and /*M! comments as SQL.
                    throw InvalidSql(nameof(sql));
                }

                var commentEnd = sql.IndexOf("*/", index + 2, StringComparison.Ordinal);
                if (commentEnd < 0)
                {
                    throw InvalidSql(nameof(sql));
                }

                index = commentEnd + 2;
                continue;
            }

            if (current is '\'' or '"' or '`')
            {
                if (current == '\'' &&
                    index > 0 &&
                    sql[index - 1] is 'Q' or 'q')
                {
                    // Oracle alternative quoting has delimiter-dependent rules.
                    // Reject it instead of interpreting its body with standard quoting.
                    throw InvalidSql(nameof(sql));
                }

                index = SkipQuotedText(sql, index, current);
                continue;
            }

            if (current == ';')
            {
                throw InvalidSql(nameof(sql));
            }

            if (current is ':' or '@')
            {
                if (index > 0 && IsAmbiguousPlaceholderBoundary(sql[index - 1]))
                {
                    throw InvalidSql(nameof(sql));
                }

                var nameStart = index + 1;
                if (nameStart >= sql.Length ||
                    (!IsAsciiLetter(sql[nameStart]) && sql[nameStart] != '_'))
                {
                    throw InvalidSql(nameof(sql));
                }

                var nameEnd = nameStart + 1;
                while (nameEnd < sql.Length && IsParameterNameCharacter(sql[nameEnd]))
                {
                    nameEnd++;
                }

                if (nameEnd < sql.Length && !IsValidPlaceholderTerminator(sql[nameEnd]))
                {
                    throw InvalidSql(nameof(sql));
                }

                var name = sql[nameStart..nameEnd];
                if (!DataQueryParameter.IsValidName(name))
                {
                    throw InvalidSql(nameof(sql));
                }

                references.Add(new ParameterReference(current, name));
                index = nameEnd;
                continue;
            }

            if (IsAsciiLetter(current) || current == '_')
            {
                var tokenEnd = index + 1;
                while (tokenEnd < sql.Length && IsSqlTokenCharacter(sql[tokenEnd]))
                {
                    tokenEnd++;
                }

                tokens.Add(sql[index..tokenEnd]);
                index = tokenEnd;
                continue;
            }

            if (!char.IsAsciiDigit(current) && !IsAllowedSqlPunctuation(current))
            {
                throw InvalidSql(nameof(sql));
            }

            index++;
        }

        return new ParsedSql(tokens, references);
    }

    private static int SkipQuotedText(string sql, int quoteStart, char quote)
    {
        for (var index = quoteStart + 1; index < sql.Length; index++)
        {
            if (sql[index] == '\\')
            {
                // MariaDB backslash escaping depends on SQL_MODE. Reject the
                // ambiguity so executable text can never be mistaken for a literal.
                throw InvalidSql(nameof(sql));
            }

            if (sql[index] != quote)
            {
                continue;
            }

            if (index + 1 < sql.Length && sql[index + 1] == quote)
            {
                index++;
                continue;
            }

            return index + 1;
        }

        throw InvalidSql(nameof(sql));
    }

    private static bool IsParameterNameCharacter(char character) =>
        IsAsciiLetter(character) || char.IsAsciiDigit(character) || character == '_';

    private static bool IsSqlTokenCharacter(char character) =>
        IsParameterNameCharacter(character) || character is '$' or '#';

    private static bool IsPortableLineCommentWhitespace(char character) =>
        IsPortableSqlWhitespace(character);

    private static bool IsPortableSqlWhitespace(char character) =>
        character is ' ' or '\t' or '\r' or '\n' or '\f';

    private static bool IsAmbiguousPlaceholderBoundary(char character) =>
        char.IsLetterOrDigit(character) ||
        char.GetUnicodeCategory(character) is
            System.Globalization.UnicodeCategory.ConnectorPunctuation or
            System.Globalization.UnicodeCategory.NonSpacingMark or
            System.Globalization.UnicodeCategory.SpacingCombiningMark or
            System.Globalization.UnicodeCategory.Format ||
        character is '$' or '#' or ':' or '@';

    private static bool IsValidPlaceholderTerminator(char character) =>
        IsPortableSqlWhitespace(character) ||
        character is ',' or ')' or '(' or '+' or '-' or '*' or '/' or '%' or
            '=' or '<' or '>' or '!' or '|' or '&' or '^' or '.' or ';';

    private static bool IsAllowedSqlPunctuation(char character) =>
        character is ',' or ')' or '(' or '+' or '-' or '*' or '/' or '%' or
            '=' or '<' or '>' or '!' or '|' or '&' or '^' or '.' or '~';

    private static bool IsAsciiLetter(char character) =>
        character is >= 'A' and <= 'Z' or >= 'a' and <= 'z';

    private static ArgumentException InvalidSql(string parameterName) =>
        new("The read query is invalid or ambiguous.", parameterName);

    private sealed record ParameterReference(char Marker, string Name);

    private sealed record ParsedSql(
        IReadOnlyList<string> Tokens,
        IReadOnlyList<ParameterReference> References);
}
