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

namespace Sonex.Data.Records;

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

    public long Id { get; set; }
    public DateTime ChangedAt { get; set; }
    public string ActionType { get; set; } = string.Empty;
    public string AccountName { get; set; } = string.Empty;
    public string TableName { get; set; } = string.Empty;
    public string? KeyName { get; set; }
    public string? KeyValue { get; set; }
    public string? ValueBefore { get; set; }
    public string? ValueAfter { get; set; }

    public static string FormatChangedAt(DateTime changedAt)
    {
        return changedAt.ToString(ChangedAtPattern, CultureInfo.InvariantCulture);
    }

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

    public static Task<DB.Result<DataChangeRecord>> GetAll(
        int limit,
        string? whereSql,
        IReadOnlyDictionary<string, object?>? whereParameters,
        CancellationToken ct = default)
    {
        var sql = new StringBuilder();
        sql.AppendLine(
            """
            SELECT id,
                   changed_at,
                   action_type,
                   account_name,
                   table_name,
                   key_name,
                   key_value,
                   value_before,
                   value_after
            FROM (
                SELECT id,
                       changed_at,
                       changed_at AS changed_at_filter,
                       action_type,
                       account_name,
                       table_name,
                       key_name,
                       key_value,
                       value_before::text AS value_before,
                       value_before::text AS value_before_text,
                       value_after::text AS value_after,
                       value_after::text AS value_after_text
                FROM sonex.log_data_changes
            ) logs
            """);

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

        sql.AppendLine(
            """
            ORDER BY changed_at_filter DESC, 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<DataChangeRecord>(
            sql.ToString(),
            parameters,
            ct: ct);
    }

    public static Task<DB.SingleResult<DataChangeRecord>> Get(string id, CancellationToken ct = default)
    {
        if (!long.TryParse(id, out var logId))
        {
            return Task.FromResult(new DB.SingleResult<DataChangeRecord>
            {
                Success = false,
                ErrorMessage = "Id must be a valid number."
            });
        }

        return Get(logId, ct);
    }

    public static Task<DB.SingleResult<DataChangeRecord>> Get(long id, CancellationToken ct = default)
    {
        return DB.QuerySingleAsync<DataChangeRecord>(
            """
            SELECT id,
                   changed_at,
                   action_type,
                   account_name,
                   table_name,
                   key_name,
                   key_value,
                   value_before::text AS value_before,
                   value_after::text AS value_after
            FROM sonex.log_data_changes
            WHERE id = @Id
            LIMIT 1;
            """,
            new
            {
                Id = id
            },
            ct: ct);
    }
}