-- نمایش/سانس
CREATE TABLE Shows (
    Id           INT IDENTITY PRIMARY KEY,
    MovieTitle   NVARCHAR(200) NOT NULL,
    StartsAt     DATETIME2(0)  NOT NULL
);

-- صندلی‌ها
CREATE TABLE Seats (
    Id         INT IDENTITY PRIMARY KEY,
    SeatNumber NVARCHAR(10) NOT NULL,
    RowVer     ROWVERSION -- برای Optimistic Lock (اختیاری در این جدول)
);

-- نگاشت صندلی به سانس
CREATE TABLE ShowSeats (
    Id         INT IDENTITY PRIMARY KEY,
    ShowId     INT NOT NULL REFERENCES Shows(Id),
    SeatId     INT NOT NULL REFERENCES Seats(Id),
    IsReserved BIT NOT NULL DEFAULT 0,
    RowVer     ROWVERSION -- کلید خوش‌بینانه
);
CREATE UNIQUE INDEX UX_ShowSeats_Show_Seat ON ShowSeats(ShowId, SeatId);

-- بلیط
CREATE TABLE Tickets (
    Id        BIGINT IDENTITY PRIMARY KEY,
    ShowId    INT NOT NULL REFERENCES Shows(Id),
    SeatId    INT NOT NULL REFERENCES Seats(Id),
    UserId    INT NOT NULL,
    CreatedAt DATETIME2(0) NOT NULL DEFAULT SYSUTCDATETIME()
);
CREATE UNIQUE INDEX UX_Tickets_Show_Seat ON Tickets(ShowId, SeatId);

BEGIN TRY
    BEGIN TRANSACTION;

    -- 1) سطر هدف را قفل کن (رزرو برای آپدیت + جلوگیری از فانتوم)
    SELECT IsReserved
    FROM ShowSeats WITH (ROWLOCK, UPDLOCK, HOLDLOCK)
    WHERE ShowId = 10 AND SeatId = 12;

    IF @@ROWCOUNT = 0
        RAISERROR (N'Seat not found for this show', 16, 1);

    -- 2) اگر قبلاً رزرو شده، خطا بده
    IF EXISTS (
        SELECT 1 FROM ShowSeats
        WHERE ShowId = 10 AND SeatId = 12 AND IsReserved = 1
    )
        RAISERROR (N'Seat already reserved', 16, 1);

    -- 3) رزرو کن
    UPDATE ShowSeats
       SET IsReserved = 1
     WHERE ShowId = 10 AND SeatId = 12;

    -- 4) صدور بلیط (Unique Index خط دفاع نهایی)
    INSERT INTO Tickets(ShowId, SeatId, UserId)
    VALUES (10, 12, 501);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    RAISERROR(ERROR_MESSAGE(), 16, 1);
END CATCH

DECLARE @rv BINARY(8);

-- خواندن نسخه فعلی
SELECT @rv = RowVer
FROM ShowSeats
WHERE ShowId = 10 AND SeatId = 12;

IF @rv IS NULL
    RAISERROR(N'Not found', 16, 1);

BEGIN TRY
    BEGIN TRANSACTION;

    -- آپدیت مشروط به عدم تغییر RowVersion
    UPDATE ShowSeats
       SET IsReserved = 1
     WHERE ShowId = 10 AND SeatId = 12 AND RowVer = @rv;

    IF @@ROWCOUNT = 0
        RAISERROR(N'Concurrent modification detected', 16, 1);

    INSERT INTO Tickets(ShowId, SeatId, UserId)
    VALUES (10, 12, 501);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    RAISERROR(ERROR_MESSAGE(), 16, 1);
END CATCH

public class Show
{
    public int Id { get; set; }
    public string MovieTitle { get; set; } = null!;
    public DateTime StartsAt { get; set; }
}

public class Seat
{
    public int Id { get; set; }
    public string SeatNumber { get; set; } = null!;
    [Timestamp] public byte[] RowVer { get; set; } = default!; // اختیاری
}

public class ShowSeat
{
    public int Id { get; set; }
    public int ShowId { get; set; }
    public int SeatId { get; set; }
    public bool IsReserved { get; set; }
    [Timestamp] public byte[] RowVer { get; set; } = default!;
}

public class Ticket
{
    public long Id { get; set; }
    public int ShowId { get; set; }
    public int SeatId { get; set; }
    public int UserId { get; set; }
    public DateTime CreatedAt { get; set; }
}

public class CinemaDbContext : DbContext
{
    public DbSet<Show> Shows => Set<Show>();
    public DbSet<Seat> Seats => Set<Seat>();
    public DbSet<ShowSeat> ShowSeats => Set<ShowSeat>();
    public DbSet<Ticket> Tickets => Set<Ticket>();

    public CinemaDbContext(DbContextOptions<CinemaDbContext> options) : base(options) {}

    protected override void OnModelCreating(ModelBuilder mb)
    {
        mb.Entity<ShowSeat>()
          .HasIndex(x => new { x.ShowId, x.SeatId })
          .IsUnique();

        mb.Entity<Ticket>()
          .HasIndex(x => new { x.ShowId, x.SeatId })
          .IsUnique();

        mb.Entity<ShowSeat>()
          .Property(p => p.RowVer)
          .IsRowVersion()
          .IsConcurrencyToken();
    }
}

public async Task ReserveSeatOptimisticAsync(
    CinemaDbContext db, int showId, int seatId, int userId, CancellationToken ct = default)
{
    var ss = await db.ShowSeats.FirstOrDefaultAsync(x => x.ShowId == showId && x.SeatId == seatId, ct);
    if (ss is null) throw new InvalidOperationException("Seat mapping not found.");
    if (ss.IsReserved) throw new InvalidOperationException("Seat already reserved.");

    ss.IsReserved = true;

    try
    {
        await using var tx = await db.Database.BeginTransactionAsync(ct);
        await db.SaveChangesAsync(ct); // روی RowVer حساس است

        db.Tickets.Add(new Ticket
        {
            ShowId = showId,
            SeatId = seatId,
            UserId = userId,
            CreatedAt = DateTime.UtcNow
        });

        await db.SaveChangesAsync(ct);
        await tx.CommitAsync(ct);
    }
    catch (DbUpdateConcurrencyException ex)
    {
        var entry = ex.Entries.Single();
        var dbValues = await entry.GetDatabaseValuesAsync(ct);
        var isReservedNow = dbValues?.GetValue<bool>(nameof(ShowSeat.IsReserved)) ?? false;

        throw new InvalidOperationException(
            isReservedNow ? "Someone else reserved this seat moments ago."
                          : "Concurrency conflict. Please retry.");
    }
}

public async Task ReserveSeatPessimisticAsync(
    CinemaDbContext db, int showId, int seatId, int userId, CancellationToken ct = default)
{
    await using var tx = await db.Database.BeginTransactionAsync(System.Data.IsolationLevel.ReadCommitted, ct);

    // قفل سطر هدف
    await db.Database.ExecuteSqlRawAsync(
        "SELECT 1 FROM ShowSeats WITH (ROWLOCK, UPDLOCK, HOLDLOCK) WHERE ShowId = {0} AND SeatId = {1}",
        showId, seatId);

    var ss = await db.ShowSeats.FirstOrDefaultAsync(x => x.ShowId == showId && x.SeatId == seatId, ct);
    if (ss is null) throw new InvalidOperationException("Seat mapping not found.");
    if (ss.IsReserved) throw new InvalidOperationException("Seat already reserved.");

    ss.IsReserved = true;
    await db.SaveChangesAsync(ct);

    db.Tickets.Add(new Ticket
    {
        ShowId = showId,
        SeatId = seatId,
        UserId = userId,
        CreatedAt = DateTime.UtcNow
    });

    await db.SaveChangesAsync(ct);
    await tx.CommitAsync(ct);
}

CREATE OR ALTER PROCEDURE sp_TransferAmount
    @FromAccountId INT,
    @ToAccountId   INT,
    @Amount        DECIMAL(18,2),
    @UserId        INT
AS
BEGIN
    SET NOCOUNT ON;
    IF @Amount <= 0 RAISERROR(N'Invalid amount', 16, 1);

    DECLARE @res INT;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- قفل‌های منطقی با ترتیب ثابت
        DECLARE @Key1 NVARCHAR(200) = CONCAT('Account:', CONVERT(VARCHAR(20), MIN(@FromAccountId, @ToAccountId)));
        DECLARE @Key2 NVARCHAR(200) = CONCAT('Account:', CONVERT(VARCHAR(20), MAX(@FromAccountId, @ToAccountId)));

        EXEC @res = sp_getapplock @Resource = @Key1, @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 5000;
        IF @res < 0 RAISERROR(N'Lock timeout (key1)', 16, 1);

        EXEC @res = sp_getapplock @Resource = @Key2, @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 5000;
        IF @res < 0 RAISERROR(N'Lock timeout (key2)', 16, 1);

        -- قفل ردیفی + اعتبارسنجی
        SELECT Balance INTO #accFrom
        FROM Accounts WITH (ROWLOCK, UPDLOCK, HOLDLOCK)
        WHERE Id = @FromAccountId;

        IF NOT EXISTS (SELECT 1 FROM #accFrom)
            RAISERROR(N'From account not found', 16, 1);

        IF (SELECT Balance FROM #accFrom) < @Amount
            RAISERROR(N'Insufficient funds', 16, 1);

        SELECT 1 FROM Accounts WITH (ROWLOCK, UPDLOCK, HOLDLOCK) WHERE Id = @ToAccountId;
        IF @@ROWCOUNT = 0 RAISERROR(N'To account not found', 16, 1);

        -- انتقال
        UPDATE Accounts SET Balance = Balance - @Amount WHERE Id = @FromAccountId;
        UPDATE Accounts SET Balance = Balance + @Amount WHERE Id = @ToAccountId;

        -- لاگ
        INSERT INTO AccountLogs(AccountId, Amount, Direction, PerformedBy, CreatedAt)
        VALUES (@FromAccountId, @Amount, 'DEBIT',  @UserId, SYSUTCDATETIME()),
               (@ToAccountId,   @Amount, 'CREDIT', @UserId, SYSUTCDATETIME());

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
        RAISERROR(ERROR_MESSAGE(), 16, 1);
    END CATCH
END