using FluentValidation;
using GB5Shared.Connection;
using GB5Shared.DTO.Framework.Logging;
using GB5Shared.DTO.Framework.Login;
using GB5Shared.QueryExecutor;
using GB5Shared.Telemetry.Database;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GB5Shared.Validation
{
    /// <summary>
    /// Centralized validation library for DTOs and entities.
    /// Provides asynchronous, exception-safe, developer-friendly validation utilities.
    /// </summary>
    public class Validation : IValidation
    {
        private readonly ILogger<Validation> _Logger;
        private readonly IApplicationConnection _ApplicationConnection;

        public Validation(ILogger<Validation> logger, IApplicationConnection applicationConnection)
        {
            _Logger = logger ?? throw new ArgumentNullException(nameof(logger));
            _ApplicationConnection = applicationConnection ?? throw new ArgumentNullException(nameof(applicationConnection));

            // Previously built its own IConfiguration and used "Gb5SystemDTO:Gb5System" as a raw,
            // unencrypted ADO.NET connection string — inconsistent with every real deployed
            // environment, where that same config key's Password= segment is AES-256-GCM
            // encrypted (confirmed live: FrameworkSL/appsettings.json's own committed value decrypts
            // correctly via ApplicationConnection.Decrypt, but fails outright if used as-is). Now
            // reuses IApplicationConnection.Gb5SystemConnectionString() — the single already-correct
            // decrypt path — instead of duplicating (and getting wrong) that logic here.
        }


        public async Task NotNull(object value, string? fieldName = null)
        {
            await Task.Run(() =>
            {
                try
                {
                    if (value == null)
                        throw new ArgumentNullException(GetFieldName(fieldName, value), $"{GetFieldName(fieldName, value)} cannot be null.");
                }
                catch (Exception ex)
                {
                    throw new Exception($"Validation failed: {ex.Message}", ex);
                }
            });
        }


        public async Task NotEmpty(string value, string? fieldName = null)
        {
            await Task.Run(() =>
            {
                try
                {
                    string field = GetFieldName(fieldName, value);

                    if (string.IsNullOrWhiteSpace(value))
                        throw new ValidationException($"{field} cannot be empty or whitespace.");
                }
                catch (ValidationException)
                {
                    // Directly rethrow validation errors
                    throw;
                }
                catch (Exception ex)
                {
                    throw new Exception($"Validation failed: {ex.Message}", ex);
                }
            });
        }



        public async Task NotNullOrEmpty(string? value, string? fieldName = null)
        {
            await Task.Run(() =>
            {
                try
                {
                    string field = GetFieldName(fieldName, value);
                    if (value == null)
                        throw new ArgumentNullException(field, $"{field} cannot be null.");
                    if (string.IsNullOrWhiteSpace(value))
                        throw new ArgumentException($"{field} cannot be empty or whitespace.");
                }
                catch (Exception ex)
                {
                    throw new Exception($"Validation failed: {ex.Message}", ex);
                }
            });
        }


        public async Task MaxLength(string tableName, string columnName, string? value)
        {
            if (value == null) return;

            int? maxLength = await GetColumnMaxLengthAsync(tableName, columnName);

            if (maxLength == null)
                throw new Exception($"Could not determine max length for {tableName}.{columnName}");

            if (value.Length > maxLength)
                throw new Exception($"{columnName} exceeds maximum length of {maxLength} characters.");
        }


        private async Task<int?> GetColumnMaxLengthAsync(string tableName, string columnName)
        {
            const string sql = @"
                SELECT CHARACTER_MAXIMUM_LENGTH
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName";

            var queryParams = new Dictionary<string, object?> { ["TableName"] = tableName, ["ColumnName"] = columnName };
            using var dbActivity = DatabaseActivityHelper.StartDbActivity(sql, parameters: queryParams);
            try
            {
                var connectionString = await _ApplicationConnection.Gb5SystemConnectionString();
                await using var conn = new SqlConnection(connectionString);
                await conn.OpenAsync();

                await using var cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@TableName", tableName);
                cmd.Parameters.AddWithValue("@ColumnName", columnName);

                object result = await cmd.ExecuteScalarAsync();
                dbActivity?.SetStatus(System.Diagnostics.ActivityStatusCode.Ok);
                return result == DBNull.Value ? null : Convert.ToInt32(result);
            }
            catch (Exception dbEx)
            {
                DatabaseActivityHelper.RecordDbError(dbActivity, dbEx);
                throw;
            }
        }

        public async Task Range<T>(T min, T max, T value, string? fieldName = null) where T : IComparable
        {
            await Task.Run(() =>
            {
                if (min.CompareTo(value) > 0 || max.CompareTo(value) < 0)
                {
                    throw new Exception($"{fieldName ?? "Value"} is out of range ({min} to {max}).");
                }
            });
        }


        public async Task<string> HandleException(Exception ex, string contextMessage = "")
        {
            if (ex == null) throw new ArgumentNullException(nameof(ex));

            try
            {
                string finalMessage;

                switch (ex)
                {
                    case SqlException sqlEx:
                        finalMessage = ParseSqlError(sqlEx);
                        _Logger.LogError(sqlEx, "[SQL ERROR] {Context}: {Error}", contextMessage, finalMessage);
                        break;

                    case AggregateException aggEx when aggEx.InnerException is SqlException innerSql:
                        finalMessage = ParseSqlError(innerSql);
                        _Logger.LogError(innerSql, "[SQL ERROR] {Context}: {Error}", contextMessage, finalMessage);
                        break;

                    case ArgumentNullException argEx:
                        finalMessage = $"Missing parameter: {argEx.ParamName ?? "Unknown"}. {argEx.Message}";
                        _Logger.LogWarning(argEx, "[ARGUMENT ERROR] {Context}: {Error}", contextMessage, finalMessage);
                        break;

                    case ArgumentException argEx:
                        finalMessage = $"Invalid argument: {argEx.Message}";
                        _Logger.LogWarning(argEx, "[ARGUMENT ERROR] {Context}: {Error}", contextMessage, finalMessage);
                        break;

                    case InvalidOperationException invalidOp:
                        finalMessage = $"Operation not valid in current state: {invalidOp.Message}";
                        _Logger.LogError(invalidOp, "[INVALID OPERATION] {Context}: {Error}", contextMessage, finalMessage);
                        break;

                    case TimeoutException timeoutEx:
                        finalMessage = $"The request timed out: {timeoutEx.Message}";
                        _Logger.LogError(timeoutEx, "[TIMEOUT] {Context}: {Error}", contextMessage, finalMessage);
                        break;

                    // .NET checked-arithmetic overflow (e.g. numeric/date conversion in app code).
                    // Distinguished from SqlException so this never gets mistaken for a database-side
                    // (e.g. column precision) issue — the wording of both is nearly identical otherwise.
                    case OverflowException overflowEx:
                        finalMessage = $"Numeric conversion overflow in application code: {overflowEx.Message}";
                        _Logger.LogError(overflowEx, "[OVERFLOW ERROR - .NET] {Context}: {Error}", contextMessage, finalMessage);
                        break;

                    default:
                        finalMessage = ex.InnerException?.Message ?? ex.Message;
                        _Logger.LogError(ex, "[GENERAL ERROR] {Context}: {Error}", contextMessage, finalMessage);
                        break;
                }

                // Optional: Log to database (if needed)
                await LogExceptionToDatabaseAsync(ex, contextMessage, finalMessage);

                return finalMessage;
            }
            catch (Exception handlerEx)
            {
                _Logger.LogCritical(handlerEx, "Critical error while handling exception.");
                return "Unexpected error while processing exception.";
            }
        }


        private async Task LogExceptionToDatabaseAsync(Exception ex, string context, string userMessage)
        {
            try
            {
                const string sql = @"
                    INSERT INTO GB5_ExceptionLog (LogDate, ExceptionType, Context, Message, StackTrace)
                    VALUES (GETDATE(), @Type, @Context, @Message, @StackTrace)";

                var queryParams = new Dictionary<string, object?>
                {
                    ["Type"]       = ex.GetType().FullName ?? "UnknownException",
                    ["Context"]    = context ?? "N/A",
                    ["Message"]    = userMessage ?? ex.Message,
                    ["StackTrace"] = ex.StackTrace ?? "",
                };
                using var dbActivity = DatabaseActivityHelper.StartDbActivity(sql, parameters: queryParams);

                var connectionString = await _ApplicationConnection.Gb5SystemConnectionString();
                await using var conn = new SqlConnection(connectionString);
                await conn.OpenAsync();

                await using var cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@Type", queryParams["Type"]);
                cmd.Parameters.AddWithValue("@Context", queryParams["Context"]);
                cmd.Parameters.AddWithValue("@Message", queryParams["Message"]);
                cmd.Parameters.AddWithValue("@StackTrace", queryParams["StackTrace"]);

                await cmd.ExecuteNonQueryAsync();
                dbActivity?.SetStatus(System.Diagnostics.ActivityStatusCode.Ok);
            }
            catch (Exception dbEx)
            {
                _Logger.LogError(dbEx, "Failed to log exception to database.");
            }
        }


        /// <summary>
        /// Extracts exact SQL Server message dynamically — fully generic.
        /// Example:
        /// "Cannot insert the value NULL into column 'CREATEDBYID', table 'dbo.MPERIOD'..."
        /// </summary>
        private static string ParseSqlError(SqlException ex)
        {
            if (ex.Errors.Count == 0)
                return ex.Message;

            var sb = new StringBuilder();

            foreach (SqlError error in ex.Errors)
            {
                // SQL provides full descriptive text — we return that directly, tagged with the
                // error number/procedure/line so identical-looking messages (e.g. overflow) can
                // be traced back to the exact statement instead of being guessed at afterward.
                sb.Append(error.Message.Trim());
                sb.Append(" [SQL Error ").Append(error.Number);
                if (!string.IsNullOrEmpty(error.Procedure))
                    sb.Append(", Procedure: ").Append(error.Procedure);
                sb.Append(", Line: ").Append(error.LineNumber).Append(']');
                sb.Append(' ');
            }

            return sb.ToString().Trim();
        }
    

        /// <summary>
        /// Helper to safely extract substring between two patterns.
        /// </summary>
        private static string? ExtractBetween(string input, string start, string end)
        {
            int startIndex = input.IndexOf(start, StringComparison.OrdinalIgnoreCase);
            if (startIndex < 0) return null;

            startIndex += start.Length;
            int endIndex = input.IndexOf(end, startIndex, StringComparison.OrdinalIgnoreCase);
            if (endIndex < 0) return null;

            return input[startIndex..endIndex];
        }











        public static async Task<string> GetConnectionStringAsync(LoginDTO LoginDTO)
        {
            try
            {
                if (LoginDTO == null)
                    throw new ArgumentNullException(nameof(LoginDTO));
                if (string.IsNullOrWhiteSpace(LoginDTO.ServerIP))
                    throw new ArgumentException("ServerIP cannot be null or empty.", nameof(LoginDTO.ServerIP));
                if (string.IsNullOrWhiteSpace(LoginDTO.DatabaseName))
                    throw new ArgumentException("DatabaseName cannot be null or empty.", nameof(LoginDTO.DatabaseName));

                // Simulate async for consistency
                string connectionString = $"Server={LoginDTO.ServerIP};Database={LoginDTO.DatabaseName};User Id=Developer;Password=devuser@123;TrustServerCertificate=True;";
                return await Task.FromResult(connectionString);
            }
            catch (Exception ex)
            {
                throw new Exception("Error generating connection string: " + ex.Message, ex);
            }
        }


        private string GetFieldName(string? fieldName, object? value)
        {
            return !string.IsNullOrWhiteSpace(fieldName)
                ? fieldName
                : value?.GetType().Name ?? "UnknownField";
        }
    }
}