using Dapper;
using System.Globalization;
using System.Text;
using Sonex.Data.Database;

namespace Sonex.Data.Records;

public sealed class AppLoggerRecord
{
    public const string OccurredAtPattern = "yyyy-MM-dd HH:mm:ss.fff";

    public string ErrorId { get; set; } = string.Empty;
    public DateTime OccurredAt { get; set; }
    public string Level { get; set; } = string.Empty;
    public string ApplicationName { get; set; } = string.Empty;
    public string AppVersion { get; set; } = string.Empty;
    public string InstanceId { get; set; } = string.Empty;
    public string UserName { get; set; } = string.Empty;
    public string MachineName { get; set; } = string.Empty;
    public string OsVersion { get; set; } = string.Empty;
    public string? CurrentPageTitle { get; set; }
    public string? CurrentPage { get; set; }
    public string Source { get; set; } = string.Empty;
    public string Operation { get; set; } = string.Empty;
    public bool IsUnhandled { get; set; }
    public string Message { get; set; } = string.Empty;
    public string? ReportedMessage { get; set; }
    public string? ExceptionType { get; set; }
    public string? Exception { get; set; }
    public string? Context { get; set; }
    public string? ExceptionData { get; set; }

    public static string FormatOccurredAt(DateTime occurredAt)
    {
        return occurredAt.ToString(OccurredAtPattern, CultureInfo.InvariantCulture);
    }

    public static Task<DB.Result<AppLoggerRecord>> GetAll(CancellationToken ct = default)
    {
        return GetAll(10000, null, null, ct);
    }

    public static Task<DB.Result<AppLoggerRecord>> GetAll(
        int limit,
        string? whereSql,
        IReadOnlyDictionary<string, object?>? whereParameters,
        CancellationToken ct = default)
    {
        var sql = new StringBuilder();
        sql.AppendLine(
            """
            SELECT error_id,
                   occurred_at,
                   level,
                   application_name,
                   app_version,
                   instance_id,
                   user_name,
                   machine_name,
                   os_version,
                   current_page_title,
                   current_page,
                   source,
                   operation,
                   is_unhandled,
                   message,
                   reported_message,
                   exception_type,
                   exception_text AS exception,
                   context,
                   exception_data
            FROM sonex.app_logs
            """);

        if (!string.IsNullOrWhiteSpace(whereSql))
        {
            sql.AppendLine();
            sql.Append("WHERE ");
            sql.AppendLine(whereSql.Trim());
        }

        sql.AppendLine(
            """
            ORDER BY occurred_at DESC, error_id DESC
            LIMIT @limit;
            """);

        var parameters = new DynamicParameters();
        parameters.Add("limit", limit);
        if (whereParameters != null)
        {
            foreach (var item in whereParameters)
            {
                parameters.Add(item.Key, item.Value);
            }
        }

        return DB.QueryListAsync<AppLoggerRecord>(
            sql.ToString(),
            parameters,
            ct: ct);
    }

    public static Task<DB.SingleResult<AppLoggerRecord>> Get(string errorId, CancellationToken ct = default)
    {
        if (string.IsNullOrWhiteSpace(errorId))
        {
            return Task.FromResult(new DB.SingleResult<AppLoggerRecord>
            {
                Success = false,
                ErrorMessage = "ErrorId cannot be empty."
            });
        }

        return DB.QuerySingleAsync<AppLoggerRecord>(
            """
            SELECT error_id,
                   occurred_at,
                   level,
                   application_name,
                   app_version,
                   instance_id,
                   user_name,
                   machine_name,
                   os_version,
                   current_page_title,
                   current_page,
                   source,
                   operation,
                   is_unhandled,
                   message,
                   reported_message,
                   exception_type,
                   exception_text AS exception,
                   context,
                   exception_data
            FROM sonex.app_logs
            WHERE error_id = @ErrorId
            LIMIT 1;
            """,
            new
            {
                ErrorId = errorId.Trim()
            },
            ct: ct);
    }

    public static async Task<DB.SingleResult<bool>> Create(AppLoggerRecord item)
    {
        ArgumentNullException.ThrowIfNull(item);

        if (string.IsNullOrWhiteSpace(item.ErrorId))
        {
            return new DB.SingleResult<bool>
            {
                Success = false,
                Item = false,
                ErrorMessage = "ErrorId cannot be empty."
            };
        }

        if (item.OccurredAt == default)
        {
            return new DB.SingleResult<bool>
            {
                Success = false,
                Item = false,
                ErrorMessage = "OccurredAt cannot be empty."
            };
        }

        return await DB.QuerySingleAsync<bool>(
            """
            INSERT INTO sonex.app_logs (
                error_id,
                occurred_at,
                level,
                application_name,
                app_version,
                instance_id,
                user_name,
                machine_name,
                os_version,
                current_page_title,
                current_page,
                source,
                operation,
                is_unhandled,
                message,
                reported_message,
                exception_type,
                exception_text,
                context,
                exception_data
            )
            VALUES (
                @ErrorId,
                @OccurredAt,
                @Level,
                @ApplicationName,
                @AppVersion,
                @InstanceId,
                @UserName,
                @MachineName,
                @OsVersion,
                @CurrentPageTitle,
                @CurrentPage,
                @Source,
                @Operation,
                @IsUnhandled,
                @Message,
                @ReportedMessage,
                @ExceptionType,
                @Exception,
                @Context,
                @ExceptionData
            )
            RETURNING TRUE;
            """,
            new
            {
                ErrorId = item.ErrorId.Trim(),
                item.OccurredAt,
                Level = Normalize(item.Level) ?? string.Empty,
                ApplicationName = Normalize(item.ApplicationName) ?? string.Empty,
                AppVersion = Normalize(item.AppVersion) ?? string.Empty,
                InstanceId = Normalize(item.InstanceId) ?? string.Empty,
                UserName = Normalize(item.UserName) ?? string.Empty,
                MachineName = Normalize(item.MachineName) ?? string.Empty,
                OsVersion = Normalize(item.OsVersion) ?? string.Empty,
                CurrentPageTitle = Normalize(item.CurrentPageTitle),
                CurrentPage = Normalize(item.CurrentPage),
                Source = Normalize(item.Source) ?? string.Empty,
                Operation = Normalize(item.Operation) ?? string.Empty,
                item.IsUnhandled,
                Message = Normalize(item.Message) ?? string.Empty,
                ReportedMessage = Normalize(item.ReportedMessage),
                ExceptionType = Normalize(item.ExceptionType),
                Exception = Normalize(item.Exception),
                Context = Normalize(item.Context),
                ExceptionData = Normalize(item.ExceptionData)
            }).ConfigureAwait(false);
    }

    private static string? Normalize(string? value)
    {
        return string.IsNullOrWhiteSpace(value)
            ? null
            : value.Trim();
    }
}