using Dapper;
using SIGCM2.Application.Audit;
using SIGCM2.Infrastructure.Persistence;

namespace SIGCM2.Infrastructure.Audit;

public sealed class AuditEventRepository : IAuditEventRepository
{
    private readonly SqlConnectionFactory _factory;

    public AuditEventRepository(SqlConnectionFactory factory)
    {
        _factory = factory;
    }

    public async Task<long> InsertAsync(
        DateTime occurredAt,
        int? actorUserId,
        int? actorRoleId,
        string action,
        string targetType,
        string targetId,
        Guid? correlationId,
        string? ipAddress,
        string? userAgent,
        string? metadata,
        CancellationToken ct = default)
    {
        const string sql = """
            INSERT INTO dbo.AuditEvent
                (OccurredAt, ActorUserId, ActorRoleId, Action, TargetType, TargetId,
                 CorrelationId, IpAddress, UserAgent, Metadata)
            OUTPUT INSERTED.Id
            VALUES
                (@OccurredAt, @ActorUserId, @ActorRoleId, @Action, @TargetType, @TargetId,
                 @CorrelationId, @IpAddress, @UserAgent, @Metadata);
            """;

        await using var conn = _factory.CreateConnection();
        await conn.OpenAsync(ct);

        var cmd = new CommandDefinition(sql, new
        {
            OccurredAt = occurredAt,
            ActorUserId = actorUserId,
            ActorRoleId = actorRoleId,
            Action = action,
            TargetType = targetType,
            TargetId = targetId,
            CorrelationId = correlationId,
            IpAddress = ipAddress,
            UserAgent = userAgent,
            Metadata = metadata,
        }, cancellationToken: ct);

        return await conn.ExecuteScalarAsync<long>(cmd);
    }

    public async Task<AuditEventQueryResult> QueryAsync(AuditEventFilter filter, CancellationToken ct = default)
    {
        var limit = Math.Clamp(filter.Limit, 1, 100);

        var wheres = new List<string>();
        var parameters = new DynamicParameters();

        if (filter.ActorUserId is not null)
        {
            wheres.Add("e.ActorUserId = @ActorUserId");
            parameters.Add("ActorUserId", filter.ActorUserId.Value);
        }
        if (!string.IsNullOrWhiteSpace(filter.TargetType))
        {
            wheres.Add("e.TargetType = @TargetType");
            parameters.Add("TargetType", filter.TargetType);
        }
        if (!string.IsNullOrWhiteSpace(filter.TargetId))
        {
            wheres.Add("e.TargetId = @TargetId");
            parameters.Add("TargetId", filter.TargetId);
        }
        if (filter.From is not null)
        {
            wheres.Add("e.OccurredAt >= @FromDate");
            parameters.Add("FromDate", filter.From.Value);
        }
        if (filter.To is not null)
        {
            wheres.Add("e.OccurredAt <= @ToDate");
            parameters.Add("ToDate", filter.To.Value);
        }

        var cursor = AuditEventCursor.TryDecode(filter.Cursor);
        if (cursor is not null)
        {
            // DESC pagination: rows strictly older than the cursor.
            wheres.Add("(e.OccurredAt < @CursorOccurredAt OR (e.OccurredAt = @CursorOccurredAt AND e.Id < @CursorId))");
            parameters.Add("CursorOccurredAt", cursor.Value.OccurredAt);
            parameters.Add("CursorId", cursor.Value.Id);
        }

        parameters.Add("Limit", limit + 1); // fetch one extra to detect "more pages"

        var whereClause = wheres.Count > 0 ? "WHERE " + string.Join(" AND ", wheres) : string.Empty;
        var sql = $"""
            SELECT TOP (@Limit)
                e.Id,
                e.OccurredAt,
                e.ActorUserId,
                u.Username       AS ActorUsername,
                e.Action,
                e.TargetType,
                e.TargetId,
                e.CorrelationId,
                e.IpAddress,
                e.Metadata
            FROM dbo.AuditEvent e
            LEFT JOIN dbo.Usuario u ON u.Id = e.ActorUserId
            {whereClause}
            ORDER BY e.OccurredAt DESC, e.Id DESC;
            """;

        await using var conn = _factory.CreateConnection();
        await conn.OpenAsync(ct);

        var cmd = new CommandDefinition(sql, parameters, cancellationToken: ct);
        var rows = (await conn.QueryAsync<AuditEventDto>(cmd)).ToList();

        string? nextCursor = null;
        if (rows.Count > limit)
        {
            // We fetched N+1; drop the overflow and emit the cursor from the Nth row.
            var last = rows[limit - 1];
            nextCursor = AuditEventCursor.Encode(last.OccurredAt, last.Id);
            rows.RemoveAt(rows.Count - 1);
        }

        return new AuditEventQueryResult(rows, nextCursor);
    }
}
