using Dapper;
using SIGCM2.Application.Abstractions.Persistence;
using SIGCM2.Domain.Entities;

namespace SIGCM2.Infrastructure.Persistence;

public sealed class RolPermisoRepository : IRolPermisoRepository
{
    private readonly SqlConnectionFactory _connectionFactory;

    public RolPermisoRepository(SqlConnectionFactory connectionFactory)
    {
        _connectionFactory = connectionFactory;
    }

    public async Task<IReadOnlyList<Permiso>> GetByRolCodigoAsync(
        string rolCodigo,
        CancellationToken ct = default)
    {
        const string sql = """
            SELECT p.Id, p.Codigo, p.Nombre, p.Descripcion, p.Modulo, p.Activo, p.FechaCreacion
            FROM dbo.RolPermiso rp
            JOIN dbo.Rol     r ON r.Id = rp.RolId
            JOIN dbo.Permiso p ON p.Id = rp.PermisoId
            WHERE r.Codigo = @RolCodigo
            ORDER BY p.Id
            """;

        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(ct);

        var rows = await connection.QueryAsync<PermisoRow>(sql, new { RolCodigo = rolCodigo });
        return rows.Select(MapRow).ToList();
    }

    public async Task ReplaceForRolAsync(
        int rolId,
        IEnumerable<int> permisoIds,
        CancellationToken ct = default)
    {
        var ids = permisoIds.ToList();

        await using var connection = _connectionFactory.CreateConnection();
        await connection.OpenAsync(ct);

        await using var transaction = await connection.BeginTransactionAsync(ct);
        try
        {
            // Step 1: Delete all existing permisos for this rol
            await connection.ExecuteAsync(
                "DELETE FROM dbo.RolPermiso WHERE RolId = @RolId",
                new { RolId = rolId },
                transaction);

            // Step 2: Insert the new set (bulk via multi-row VALUES)
            if (ids.Count > 0)
            {
                foreach (var permisoId in ids)
                {
                    await connection.ExecuteAsync(
                        """
                        INSERT INTO dbo.RolPermiso (RolId, PermisoId)
                        VALUES (@RolId, @PermisoId)
                        """,
                        new { RolId = rolId, PermisoId = permisoId },
                        transaction);
                }
            }

            await transaction.CommitAsync(ct);
        }
        catch
        {
            await transaction.RollbackAsync(ct);
            throw;
        }
    }

    private static Permiso MapRow(PermisoRow row)
        => Permiso.ForRead(
            id: row.Id,
            codigo: row.Codigo,
            nombre: row.Nombre,
            descripcion: row.Descripcion,
            modulo: row.Modulo,
            activo: row.Activo,
            fechaCreacion: row.FechaCreacion);

    private sealed record PermisoRow(
        int Id,
        string Codigo,
        string Nombre,
        string? Descripcion,
        string Modulo,
        bool Activo,
        DateTime FechaCreacion);
}
