05Работа с Базами Данных

// Connection string с параметрами пула
        var connectionString = "Server=localhost;Database=MyDb;Trusted_Connection=True;" +
                               "TrustServerCertificate=True;" +
                               "Connection Timeout=30;" +
                               "Max Pool Size=100;" +
                               "Min Pool Size=5;" +
                               "Connection Lifetime=300;" +
                               "Pooling=true;";

        using var connection = new SqlConnection(connectionString);

// Параметризированный запрос — защита от SQL injection
        using var command = new SqlCommand(
            "SELECT Id, Name, Email FROM Users WHERE Email = @Email AND IsActive = @IsActive",
            connection);

        command.Parameters.AddWithValue("@Email", userEmail);
        command.Parameters.Add("@IsActive", SqlDbType.Bit).Value = true;

        // Лучше использовать SqlDbType явно
        command.Parameters.Add("@Email", SqlDbType.NVarChar, 256).Value = userEmail;

using var reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection);

        while (await reader.ReadAsync())
        {
            var user = new User
            {
                Id = reader.GetInt32(0),
                Name = reader.GetString(1),
                Email = reader.GetString(2)
            };
        }
        // reader.Dispose() закроет connection благодаря CommandBehavior.CloseConnection

Приложение                    SQL Server
            |                              |
            |--- Open() ----------------->| (создание нового соединения)
            |<-- готово ------------------|
            |                              |
            |--- Close() ---------------->| (возврат в пул, НЕ закрытие!)
            |                              |
            |--- Open() ----------------->| (повторное использование из пула)
            |<-- готово ------------------|

// Проверка состояния пула через Performance Counters
        // SQLServer:General Statistics -> User Connections
        // .NET Data Provider for SqlServer -> NumberOfPooledConnections

        // Включение логирования пула в connection string:
        "Server=localhost;...;Connection Lifetime=0;Load Balance Timeout=0;"

        // Очистка пула (экстренный случай)
        SqlConnection.ClearAllPools();
        SqlConnection.ClearPool(connection);

var options = new TransactionOptions
        {
            IsolationLevel = IsolationLevel.ReadCommitted,
            Timeout = TimeSpan.FromMinutes(5)
        };

        using var scope = new TransactionScope(
            TransactionScopeOption.Required,
            options,
            TransactionScopeAsyncFlowOption.Enabled); // Важно для async!

        try
        {
            using var connection = new SqlConnection(connectionString);
            await connection.OpenAsync();

            using var cmd1 = new SqlCommand("INSERT INTO ...", connection);
            await cmd1.ExecuteNonQueryAsync();

            using var cmd2 = new SqlCommand("UPDATE ...", connection);
            await cmd2.ExecuteNonQueryAsync();

            scope.Complete(); // Commit
        }
        catch
        {
            // Rollback automatic при выходе из scope без Complete()
            throw;
        }

using var connection = new SqlConnection(connectionString);
        await connection.OpenAsync();

        using var transaction = await connection.BeginTransactionAsync(
            IsolationLevel.ReadCommitted);

        try
        {
            using var cmd = new SqlCommand("INSERT INTO ...", connection, transaction);
            await cmd.ExecuteNonQueryAsync();

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

// Все основные методы имеют async версии
        await connection.OpenAsync(cancellationToken);
        await command.ExecuteNonQueryAsync(cancellationToken);
        await command.ExecuteReaderAsync(cancellationToken);
        await command.ExecuteScalarAsync(cancellationToken);

        // Чтение данных
        while (await reader.ReadAsync(cancellationToken))
        {
            var value = reader.GetString(0);
        }

        // Важно: CancellationToken для отмены долгих операций
        using var cts = new CancellationTokenSource(TimeSpan.FromSeconds(30));
        await command.ExecuteNonQueryAsync(cts.Token);

public interface IUserRepository
        {
            Task<User?> GetByIdAsync(int id, CancellationToken ct = default);
            Task<IReadOnlyList<User>> GetAllAsync(CancellationToken ct = default);
            Task<int> CreateAsync(User user, CancellationToken ct = default);
            Task UpdateAsync(User user, CancellationToken ct = default);
            Task DeleteAsync(int id, CancellationToken ct = default);
        }

        public class SqlUserRepository : IUserRepository, IDisposable
        {
            private readonly SqlConnection _connection;

            public SqlUserRepository(string connectionString)
            {
                _connection = new SqlConnection(connectionString);
            }

            public async Task<User?> GetByIdAsync(int id, CancellationToken ct = default)
            {
                await EnsureConnectionOpenAsync();

                using var cmd = new SqlCommand(
                    "SELECT Id, Name, Email, CreatedAt FROM Users WHERE Id = @Id",
                    _connection);

                cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;

                using var reader = await cmd.ExecuteReaderAsync(ct);
                if (!await reader.ReadAsync(ct))
                    return null;

                return MapUser(reader);
            }

            public async Task<int> CreateAsync(User user, CancellationToken ct = default)
            {
                await EnsureConnectionOpenAsync();

                using var cmd = new SqlCommand(
                    @"INSERT INTO Users (Name, Email, CreatedAt)
                      VALUES (@Name, @Email, @CreatedAt);
                      SELECT CAST(SCOPE_IDENTITY() AS INT);",
                    _connection);

                cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 100).Value = user.Name;
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 256).Value = user.Email;
                cmd.Parameters.Add("@CreatedAt", SqlDbType.DateTime2).Value = user.CreatedAt;

                return (int)await cmd.ExecuteScalarAsync(ct);
            }

            private async Task EnsureConnectionOpenAsync()
            {
                if (_connection.State != ConnectionState.Open)
                    await _connection.OpenAsync();
            }

            private static User MapUser(SqlDataReader reader) => new()
            {
                Id = reader.GetInt32(0),
                Name = reader.GetString(1),
                Email = reader.GetString(2),
                CreatedAt = reader.GetDateTime(3)
            };

            public void Dispose() => _connection?.Dispose();
        }

// Результаты типичного benchmark (1000 запросов):
        //
        // Synchronous:  ~2.5s (блокирует thread pool)
        // Asynchronous: ~2.8s (не блокирует thread pool)
        //
        // Async немного медленнее из-за overhead, но:
        // - Не блокирует thread pool threads
        // - Позволяет обрабатывать больше concurrent запросов
        // - Критично для web-приложений (ASP.NET Core)
        //
        // Правило: ВСЕГДА используй async в web-приложениях

// При выполнении query EF Core:
        // 1. Выполняет SQL запрос
        // 2. Материализует объекты из DataReader
        // 3. Регистрирует их в Change Tracker
        // 4. При SaveChanges сравнивает текущие значения с оригинальными

        using var context = new AppDbContext();

        // Entity регистрируется в Change Tracker
        var user = await context.Users.FirstAsync(u => u.Id == 1);
        // EntityState: Unchanged

        user.Name = "New Name";
        // EntityState: Modified (EF Core детектит изменения)

        await context.SaveChangesAsync();
        // Генерирует: UPDATE Users SET Name = 'New Name' WHERE Id = 1
        // EntityState: Unchanged

// Два запроса к одному entity — один объект в памяти
        var user1 = await context.Users.FirstAsync(u => u.Id == 1);
        var user2 = await context.Users.FirstAsync(u => u.Id == 1);

        ReferenceEquals(user1, user2); // True — один и тот же объект!

// Read-only query — не нужно отслеживать
        var users = await context.Users
            .AsNoTracking()
            .Where(u => u.IsActive)
            .ToListAsync();

        // AsNoTrackingWithIdentityResolution — если нужны уникальные объекты
        // но без overhead tracking
        var blogs = await context.Blogs
            .Include(b => b.Posts)
            .AsNoTrackingWithIdentityResolution()
            .ToListAsync();

        // Глобально для всего контекста (read-only сценарии)
        context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

// Этот LINQ транслируется в SQL
        var query = context.Users
            .Where(u => u.Age >= 18 && u.IsActive)
            .OrderBy(u => u.LastName)
            .Select(u => new { u.Id, u.FullName = u.FirstName + " " + u.LastName });

        // SQL:
        // SELECT Id, FirstName + ' ' + LastName AS FullName
        // FROM Users
        // WHERE Age >= 18 AND IsActive = 1
        // ORDER BY LastName

// EF Core 3+ выбрасывает exception для client evaluation
        // Это НЕ выполнится на сервере:
        var users = context.Users
            .Where(u => MyCustomMethod(u.Name)) // InvalidOperationException!
            .ToList();

        // Решение: сначала материализуй, потом фильтруй
        var users = context.Users
            .Where(u => u.Name.StartsWith("A")) // Server-side
            .AsEnumerable()                     // Переключение на client
            .Where(u => MyCustomMethod(u.Name)) // Client-side
            .ToList();

// IQueryable — фильтрация на стороне БД
        IQueryable<User> queryable = context.Users.Where(u => u.IsActive);
        queryable = queryable.Where(u => u.Age > 18);
        // SQL: SELECT ... FROM Users WHERE IsActive = 1 AND Age > 18

        // IEnumerable — фильтрация в памяти
        IEnumerable<User> enumerable = context.Users.Where(u => u.IsActive).AsEnumerable();
        enumerable = enumerable.Where(u => u.Age > 18);
        // SQL: SELECT ... FROM Users WHERE IsActive = 1
        // Фильтрация по Age выполняется в памяти после загрузки!

        // Правило: используй IQueryable до последнего момента
        var result = context.Users
            .Where(u => u.IsActive)
            .Where(u => u.Age > 18)
            .Select(u => new { u.Id, u.Name })  // Projection уменьшает данные
            .ToList();                          // Только здесь выполняется запрос

// Один SQL запрос с JOIN
        var blogs = await context.Blogs
            .Include(b => b.Posts)
            .ThenInclude(p => p.Author)
            .ToListAsync();

        // Split Queries — несколько запросов без Cartesian explosion
        var blogs = await context.Blogs
            .Include(b => b.Posts)
            .ThenInclude(p => p.Comments)
            .AsSplitQuery()  // EF Core 5+
            .ToListAsync();

        // Глобальная настройка (EF Core 5+)
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(connectionString)
                          .UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery);
        }

// Загрузка связанных данных по требованию
        var blog = await context.Blogs.FirstAsync(b => b.Id == 1);

        await context.Entry(blog)
            .Collection(b => b.Posts)
            .LoadAsync();

        // С фильтрацией
        await context.Entry(blog)
            .Collection(b => b.Posts)
            .Query()
            .Where(p => p.IsPublished)
            .LoadAsync();

// Включается через пакеты и конфигурацию
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseLazyLoadingProxies();
        }

        // Навигационные свойства должны быть virtual
        public class Blog
        {
            public int Id { get; set; }
            public virtual ICollection<Post> Posts { get; set; }
        }

        // ОСТОРОЖНО: N+1 problem!
        foreach (var blog in context.Blogs)  // 1 запрос
        {
            foreach (var post in blog.Posts) // N запросов!
            {
                Console.WriteLine(post.Title);
            }
        }

// ПЛОХО: N+1 запросов
        var users = await context.Users.ToListAsync();  // 1 запрос
        foreach (var user in users)
        {
            var orders = await context.Orders
                .Where(o => o.UserId == user.Id)
                .ToListAsync();  // N запросов
        }

        // ХОРОШО: 1 запрос с Include
        var users = await context.Users
            .Include(u => u.Orders)
            .ToListAsync();

        // ХОРОШО: 1 запрос с Join
        var usersWithOrders = await context.Users
            .Join(context.Orders,
                u => u.Id,
                o => o.UserId,
                (u, o) => new { User = u, Order = o })
            .ToListAsync();

// Логирование SQL запросов
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);
        }

        // MiniProfiler для detection N+1
        // https://miniprofiler.com/dotnet/

        // EF Core logging с чувствительными данными
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.EnableSensitiveDataLogging()
                          .EnableDetailedErrors();
        }

// FromSqlRaw — параметризированный raw SQL
        var users = await context.Users
            .FromSqlRaw("SELECT * FROM Users WHERE Age > {0}", 18)
            .ToListAsync();

        // FromSqlInterpolated — интерполированный синтаксис
        var users = await context.Users
            .FromSqlInterpolated($"SELECT * FROM Users WHERE Age > {age}")
            .ToListAsync();

        // ExecuteSqlRaw для non-query операций
        await context.Database.ExecuteSqlRawAsync(
            "UPDATE Users SET IsActive = 0 WHERE LastLogin < {0}",
            cutoffDate);

        // Важно: FromSql возвращает tracked entities!
        // Используй AsNoTracking() если не нужно отслеживание
        var users = await context.Users
            .FromSqlRaw("SELECT * FROM Users")
            .AsNoTracking()
            .ToListAsync();

public class AuditingCommandInterceptor : DbCommandInterceptor
        {
            private readonly ILogger<AuditingCommandInterceptor> _logger;

            public AuditingCommandInterceptor(ILogger<AuditingCommandInterceptor> logger)
            {
                _logger = logger;
            }

            public override InterceptionResult<DbDataReader> ReaderExecuting(
                DbCommand command,
                CommandEventData eventData,
                InterceptionResult<DbDataReader> result)
            {
                LogCommand(command, "Executing");
                return result;
            }

            public override async ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
                DbCommand command,
                CommandEventData eventData,
                InterceptionResult<DbDataReader> result,
                CancellationToken cancellationToken = default)
            {
                LogCommand(command, "ExecutingAsync");
                return result;
            }

            private void LogCommand(DbCommand command, string operation)
            {
                _logger.LogInformation(
                    "{Operation}: {CommandText} [{Parameters}]",
                    operation,
                    command.CommandText,
                    string.Join(", ", command.Parameters.Cast<DbParameter>()
                        .Select(p => $"{p.ParameterName}={p.Value}")));
            }
        }

        // Регистрация
        services.AddDbContext<AppDbContext>(options =>
        {
            options.UseSqlServer(connectionString);
            options.AddInterceptors(new AuditingCommandInterceptor(logger));
        });

# Установка инструментов
        dotnet tool install --global dotnet-ef
        dotnet add package Microsoft.EntityFrameworkCore.Design

        # Создание миграции
        dotnet ef migrations add AddUserEmailIndex

        # Применение миграции
        dotnet ef database update

        # Откат к конкретной миграции
        dotnet ef database update PreviousMigrationName

        # Откат всех миграций (чистая БД)
        dotnet ef database update 0

        # Удаление последней миграции (если не применена)
        dotnet ef migrations remove

[Migration("20260521120000_AddUserEmailIndex")]
        partial class AddUserEmailIndex
        {
            protected override void Up(MigrationBuilder migrationBuilder)
            {
                migrationBuilder.CreateIndex(
                    name: "IX_Users_Email",
                    table: "Users",
                    column: "Email",
                    unique: true);
            }

            protected override void Down(MigrationBuilder migrationBuilder)
            {
                migrationBuilder.DropIndex(
                    name: "IX_Users_Email",
                    table: "Users");
            }
        }

# SQL скрипт от начальной до целевой миграции
        dotnet ef migrations script -o migrations.sql

        # От конкретной миграции до конкретной
        dotnet ef migrations script FromMigration ToMigration -o migrations.sql

        # Idempotent скрипт (проверяет что уже применено)
        dotnet ef migrations script --idempotent -o migrations.sql

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Role>().HasData(
                new Role { Id = 1, Name = "Admin", CreatedAt = DateTime.UtcNow },
                new Role { Id = 2, Name = "User", CreatedAt = DateTime.UtcNow }
            );

            modelBuilder.Entity<User>().HasData(
                new User
                {
                    Id = 1,
                    Username = "admin",
                    Email = "admin@example.com",
                    RoleId = 1,
                    CreatedAt = DateTime.UtcNow
                }
            );
        }

public static class SeedData
        {
            public static async Task InitializeAsync(IServiceProvider serviceProvider)
            {
                using var scope = serviceProvider.CreateScope();
                var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

                // Миграции
                await context.Database.MigrateAsync();

                // Seed только если БД пустая
                if (!await context.Roles.AnyAsync())
                {
                    context.Roles.AddRange(
                        new Role { Name = "Admin" },
                        new Role { Name = "User" },
                        new Role { Name = "Moderator" }
                    );
                    await context.SaveChangesAsync();
                }

                // Seed для development
                if (context.Environment.IsDevelopment())
                {
                    await SeedDevelopmentDataAsync(context);
                }
            }

            private static async Task SeedDevelopmentDataAsync(AppDbContext context)
            {
                // Тестовые данные только для development
                if (!await context.Users.AnyAsync())
                {
                    context.Users.AddRange(
                        new User { Username = "test1", Email = "test1@example.com" },
                        new User { Username = "test2", Email = "test2@example.com" }
                    );
                    await context.SaveChangesAsync();
                }
            }
        }

        // В Program.cs
        var app = builder.Build();
        await SeedData.InitializeAsync(app.Services);

Server A: Миграция 1 -> Миграция 2 (выполняется)
        Server B: Миграция 1 -> Миграция 2 (пытается выполниться)
        Результат: Conflict, duplicate operations, errors

public static class MigrationExtensions
        {
            public static async Task ApplyMigrationsAsync(this IApplicationBuilder app)
            {
                using var scope = app.ApplicationServices.CreateScope();
                var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

                // Проверка ожидающих миграций
                var pendingMigrations = await context.Database.GetPendingMigrationsAsync();
                var pendingList = pendingMigrations.ToList();

                if (pendingList.Any())
                {
                    // Distributed lock через PostgreSQL advisory lock
                    // или SQL Server sp_getapplock
                    var lockResult = await AcquireMigrationLockAsync(context);

                    if (lockResult)
                    {
                        try
                        {
                            // Повторная проверка после получения lock
                            var pendingNow = await context.Database.GetPendingMigrationsAsync();
                            if (pendingNow.Any())
                            {
                                await context.Database.MigrateAsync();
                            }
                        }
                        finally
                        {
                            await ReleaseMigrationLockAsync(context);
                        }
                    }
                    else
                    {
                        // Другой инстанс выполняет миграцию — ждём
                        await WaitForMigrationsAsync(context);
                    }
                }
            }

            private static async Task<bool> AcquireMigrationLockAsync(AppDbContext context)
            {
                // SQL Server: sp_getapplock
                var result = await context.Database.ExecuteSqlRawAsync(
                    @"EXEC sp_getapplock
                      @Resource = 'MigrationLock',
                      @LockMode = 'Exclusive',
                      @LockOwner = 'Session',
                      @LockTimeout = 30000");

                return result == 0;
            }

            private static async Task ReleaseMigrationLockAsync(AppDbContext context)
            {
                await context.Database.ExecuteSqlRawAsync(
                    @"EXEC sp_releaseapplock
                      @Resource = 'MigrationLock',
                      @LockOwner = 'Session'");
            }

            private static async Task WaitForMigrationsAsync(AppDbContext context)
            {
                var timeout = DateTime.UtcNow.AddMinutes(5);
                while (DateTime.UtcNow < timeout)
                {
                    var pending = await context.Database.GetPendingMigrationsAsync();
                    if (!pending.Any())
                        return;

                    await Task.Delay(TimeSpan.FromSeconds(5));
                }

                throw new TimeoutException("Migration timeout — another instance may be stuck");
            }
        }

// Фаза 1: EXPAND — добавляем новый column (nullable)
        // Миграция: Add_NewColumn
        migrationBuilder.AddColumn<string>(
            name: "NewEmail",
            table: "Users",
            nullable: true);

        // Deploy новой версии приложения (пишет в оба поля)
        // Старая версия продолжает работать

        // Фаза 2: MIGRATE DATA — копируем данные
        // Отдельный скрипт или background job
        UPDATE Users SET NewEmail = Email WHERE NewEmail IS NULL;

        // Фаза 3: SWITCH — приложение читает из нового поля
        // Deploy версии которая читает NewEmail

        // Фаза 4: CONTRACT — удаляем старое поле
        // Миграция: Drop_OldColumn
        migrationBuilder.DropColumn(
            name: "Email",
            table: "Users");

[Migration("20260521_AddUserStatus")]
        partial class AddUserStatus
        {
            protected override void Up(MigrationBuilder migrationBuilder)
            {
                // 1. Добавляем column с default value
                migrationBuilder.AddColumn<int>(
                    name: "Status",
                    table: "Users",
                    type: "int",
                    nullable: false,
                    defaultValue: 1); // 1 = Active

                // 2. Добавляем index
                migrationBuilder.CreateIndex(
                    name: "IX_Users_Status",
                    table: "Users",
                    column: "Status");
            }

            protected override void Down(MigrationBuilder migrationBuilder)
            {
                migrationBuilder.DropIndex("IX_Users_Status", "Users");
                migrationBuilder.DropColumn("Status", "Users");
            }
        }

        // Приложение должно работать со старой и новой схемой:
        public class User
        {
            public int Id { get; set; }
            public string Email { get; set; }

            // Новое поле — nullable для совместимости
            public int? Status { get; set; }

            // Property для backward compatibility
            [NotMapped]
            public bool IsActive => Status == null || Status == 1;
        }

// Rollback миграция
        [Migration("20260521_Rollback_UserStatus")]
        partial class RollbackUserStatus
        {
            protected override void Up(MigrationBuilder migrationBuilder)
            {
                // Восстанавливаем старую схему
                migrationBuilder.DropColumn("Status", "Users");
                migrationBuilder.AddColumn<string>(
                    name: "LegacyStatus",
                    table: "Users",
                    nullable: true);
            }

            protected override void Down(MigrationBuilder migrationBuilder)
            {
                // Возвращаем новую схему
                migrationBuilder.DropColumn("LegacyStatus", "Users");
                migrationBuilder.AddColumn<int>(
                    name: "Status",
                    table: "Users",
                    nullable: false,
                    defaultValue: 1);
            }
        }

        // Checklist для rollback:
        // 1. Остановить deploy новой версии
        // 2. Откатить миграцию: dotnet ef database update PreviousMigration
        // 3. Откатить приложение к предыдущей версии
        // 4. Проверить работоспособность
        // 5. Проанализировать причину failure

# Создание self-contained executable
        dotnet ef migrations bundle

        # Кастомизация
        dotnet ef migrations bundle \
            --self-contained \
            -r win-x64 \
            -o ef-bundle.exe

        # Применение миграций
        ./ef-bundle.exe --connection "Server=prod;Database=MyDb;..."

        # CI/CD integration
        dotnet ef migrations bundle -o ef-bundle.exe
        # В deployment pipeline:
        ./ef-bundle.exe --connection "$CONNECTION_STRING"

-- Clustered Index — физический порядок данных (обычно PK)
        CREATE CLUSTERED INDEX IX_Users_Id ON Users(Id);

        -- Nonclustered Index — отдельная структура
        CREATE NONCLUSTERED INDEX IX_Users_Email ON Users(Email);

        -- Composite Index — несколько колонок
        CREATE NONCLUSTERED INDEX IX_Users_Status_CreatedAt
            ON Users(Status, CreatedAt);

        -- Порядок колонок важен!
        -- WHERE Status = 1 AND CreatedAt > '2026-01-01'  -- использует индекс
        -- WHERE CreatedAt > '2026-01-01'                   -- НЕ использует (Status первый)

-- Covering Index включает все columns для query
        -- INCLUDE добавляет non-key columns
        CREATE NONCLUSTERED INDEX IX_Users_Email_Include
            ON Users(Email)
            INCLUDE (FirstName, LastName, CreatedAt);

        -- Query использует только index (без lookup в таблицу)
        SELECT Email, FirstName, LastName, CreatedAt
        FROM Users
        WHERE Email = 'test@example.com';

-- Index только для subset данных
        CREATE NONCLUSTERED INDEX IX_Users_Active
            ON Users(Email)
            WHERE IsActive = 1;

        -- Меньше размер, быстрее maintenance
        -- Query должен matching filter:
        SELECT * FROM Users WHERE Email = 'test@x.com' AND IsActive = 1;

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>()
                .HasIndex(u => u.Email)
                .IsUnique();

            modelBuilder.Entity<User>()
                .HasIndex(u => new { u.Status, u.CreatedAt })
                .HasDatabaseName("IX_Users_Status_CreatedAt");

            modelBuilder.Entity<User>()
                .HasIndex(u => u.Email)
                .IsUnique()
                .HasFilter("IsActive = 1")  // Filtered index
                .HasDatabaseName("IX_Users_Active_Email");
        }

-- Включить actual execution plan
        SET STATISTICS IO ON;
        SET STATISTICS TIME ON;

        -- Выполнить query
        SELECT * FROM Users WHERE Email = 'test@example.com';

        -- Смотреть результаты:
        -- Table 'Users'. Scan count 0, logical reads 2
        -- SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms

        -- Index Seek — хорошо (использует индекс)
        -- Index Scan — нормально (читает весь индекс)
        -- Table Scan — плохо (читает всю таблицу, нужен индекс)
        -- Key Lookup — плохо (дополнительный lookup, нужен covering index)

// Без tracking — на 20-50% быстрее
        var users = await context.Users
            .AsNoTracking()
            .Where(u => u.IsActive)
            .ToListAsync();

        // Глобально для read-only context
        public class ReadDbContext : AppDbContext
        {
            public ReadDbContext(DbContextOptions options) : base(options)
            {
                ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
            }
        }

// ПЛОХО: загружает все columns
        var users = await context.Users.ToListAsync();

        // ХОРОШО: только нужные columns
        var userDtos = await context.Users
            .Where(u => u.IsActive)
            .Select(u => new UserDto
            {
                Id = u.Id,
                Name = u.FullName,
                Email = u.Email
            })
            .ToListAsync();

        // SQL: SELECT Id, FirstName + ' ' + LastName, Email FROM Users WHERE IsActive = 1

// EF Core 7+ ExecuteUpdate/ExecuteDelete
        await context.Users
            .Where(u => u.LastLogin < DateTime.UtcNow.AddYears(-1))
            .ExecuteUpdateAsync(u => u.SetProperty(x => x.IsActive, false));

        await context.Users
            .Where(u => u.LastLogin < DateTime.UtcNow.AddYears(-2))
            .ExecuteDeleteAsync();

        // Bulk Operations (EF Core Extensions)
        // NuGet: EFCore.BulkExtensions
        await context.BulkInsertAsync(users);
        await context.BulkUpdateAsync(users);
        await context.BulkDeleteAsync(users);

        // Для 1M строк:
        // SaveChanges: ~30 минут
        // BulkInsert: ~2 минуты

public async Task BenchmarkBulkInsert()
        {
            var users = Enumerable.Range(1, 1_000_000)
                .Select(i => new User
                {
                    Username = $"user{i}",
                    Email = $"user{i}@example.com",
                    CreatedAt = DateTime.UtcNow
                })
                .ToList();

            // EF Core SaveChanges (batch by 1000)
            var sw = Stopwatch.StartNew();
            for (int i = 0; i < users.Count; i += 1000)
            {
                context.Users.AddRange(users.Skip(i).Take(1000));
                await context.SaveChangesAsync();
                context.ChangeTracker.Clear();
            }
            Console.WriteLine($"SaveChanges: {sw.Elapsed}");  // ~30 min

            // Bulk Insert
            sw.Restart();
            await context.BulkInsertAsync(users);
            Console.WriteLine($"BulkInsert: {sw.Elapsed}");   // ~2 min
        }

// Оптимизированная connection string
        var connectionString = @"
            Server=localhost;
            Database=MyDb;
            Trusted_Connection=True;
            TrustServerCertificate=True;
            Connection Timeout=30;
            Max Pool Size=200;
            Min Pool Size=10;
            Connection Lifetime=600;
            Pooling=true;
            Enlist=false;
        ";

        // Параметры:
        // Max Pool Size: увеличить для high-concurrency
        // Min Pool Size:预热 пул для снижения latency
        // Connection Lifetime: recycle stale connections
        // Enlist=false: отключить auto-enlist в distributed transactions

public class SlowQueryInterceptor : DbCommandInterceptor
        {
            private readonly ILogger<SlowQueryInterceptor> _logger;
            private readonly TimeSpan _threshold;

            public SlowQueryInterceptor(ILogger<SlowQueryInterceptor> logger, TimeSpan? threshold = null)
            {
                _logger = logger;
                _threshold = threshold ?? TimeSpan.FromMilliseconds(500);
            }

            public override async ValueTask<DbDataReader> ReaderExecutedAsync(
                DbCommand command,
                CommandExecutedEventData eventData,
                DbDataReader result,
                CancellationToken cancellationToken = default)
            {
                await LogSlowQueryAsync(command, eventData, cancellationToken);
                return result;
            }

            public override async ValueTask<int> NonQueryExecutedAsync(
                DbCommand command,
                CommandExecutedEventData eventData,
                int result,
                CancellationToken cancellationToken = default)
            {
                await LogSlowQueryAsync(command, eventData, cancellationToken);
                return result;
            }

            private async Task LogSlowQueryAsync(DbCommand command, CommandExecutedEventData eventData)
            {
                if (eventData.Duration > _threshold)
                {
                    _logger.LogWarning(
                        "Slow query detected ({Duration}ms): {CommandText} [{Parameters}]",
                        eventData.Duration.TotalMilliseconds,
                        command.CommandText,
                        string.Join(", ", command.Parameters.Cast<DbParameter>()
                            .Select(p => $"{p.ParameterName}={p.Value}")));

                    // Можно отправить в Application Insights, Prometheus, etc.
                }
            }
        }

        // Регистрация
        services.AddDbContext<AppDbContext>(options =>
        {
            options.UseSqlServer(connectionString);
            options.AddInterceptors(new SlowQueryInterceptor(logger, TimeSpan.FromMilliseconds(100)));
        });

public class QueryStatisticsInterceptor : DbCommandInterceptor
        {
            public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
                DbCommand command,
                CommandEventData eventData,
                InterceptionResult<DbDataReader> result,
                CancellationToken cancellationToken = default)
            {
                // SQL Server: включить статистику IO
                command.CommandText = "SET STATISTICS IO ON;" + Environment.NewLine + command.CommandText;
                return new(result);
            }
        }

public class AuditInterceptor : ISaveChangesInterceptor
        {
            private readonly IAuditService _auditService;
            private AuditRecord? _auditRecord;

            public AuditInterceptor(IAuditService auditService)
            {
                _auditService = auditService;
            }

            public async ValueTask<InterceptionResult<int>> SavingChangesAsync(
                DbContextEventData eventData,
                InterceptionResult<int> result,
                CancellationToken cancellationToken = default)
            {
                _auditRecord = CreateAudit(eventData.Context);
                await _auditService.SaveAuditAsync(_auditRecord, cancellationToken);
                return result;
            }

            public async ValueTask<int> SavedChangesAsync(
                SaveChangesCompletedEventData eventData,
                int result,
                CancellationToken cancellationToken = default)
            {
                if (_auditRecord != null)
                {
                    _auditRecord.Succeeded = true;
                    _auditRecord.EndTime = DateTime.UtcNow;
                    await _auditService.UpdateAuditAsync(_auditRecord, cancellationToken);
                }
                return result;
            }

            public async ValueTask SaveChangesFailedAsync(
                DbContextErrorEventData eventData,
                CancellationToken cancellationToken = default)
            {
                if (_auditRecord != null)
                {
                    _auditRecord.Succeeded = false;
                    _auditRecord.EndTime = DateTime.UtcNow;
                    _auditRecord.ErrorMessage = eventData.Exception.Message;
                    await _auditService.UpdateAuditAsync(_auditRecord, cancellationToken);
                }
            }

            private static AuditRecord CreateAudit(DbContext? context)
            {
                var audit = new AuditRecord
                {
                    StartTime = DateTime.UtcNow,
                    Entities = new List<AuditEntity>()
                };

                if (context != null)
                {
                    foreach (var entry in context.ChangeTracker.Entries())
                    {
                        if (entry.State is EntityState.Added or EntityState.Modified or EntityState.Deleted)
                        {
                            audit.Entities.Add(new AuditEntity
                            {
                                EntityType = entry.Entity.GetType().Name,
                                State = entry.State.ToString(),
                                Changes = entry.State == EntityState.Modified
                                    ? entry.Properties
                                        .Where(p => p.IsModified)
                                        .ToDictionary(p => p.Metadata.Name, p => p.CurrentValue)
                                    : null
                            });
                        }
                    }
                }

                return audit;
            }
        }

        // Регистрация
        services.AddDbContext<AppDbContext>(options =>
        {
            options.UseSqlServer(connectionString);
            options.AddInterceptors(new AuditInterceptor(auditService));
        });

public enum UserStatus
        {
            Active,
            Inactive,
            Suspended
        }

        // Конвертер
        public class UserStatusConverter : ValueConverter<UserStatus, string>
        {
            public UserStatusConverter(ConverterMappingHints mappingHints = null)
                : base(
                    v => v.ToString(),
                    v => Enum.Parse<UserStatus>(v),
                    mappingHints)
            {
            }
        }

        // Использование в конфигурации
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>()
                .Property(u => u.Status)
                .HasConversion<string>();

            // Или через конвертер
            modelBuilder.Entity<User>()
                .Property(u => u.Status)
                .HasConversion(new UserStatusConverter());
        }

        // В БД: 'Active', 'Inactive', 'Suspended' вместо 0, 1, 2

public class Money
        {
            public decimal Amount { get; set; }
            public string Currency { get; set; } = "USD";

            public override string ToString() => $"{Amount}:{Currency}";

            public static Money Parse(string value)
            {
                var parts = value.Split(':');
                return new Money
                {
                    Amount = decimal.Parse(parts[0]),
                    Currency = parts[1]
                };
            }
        }

        // Конфигурация
        modelBuilder.Entity<Order>()
            .Property(o => o.Total)
            .HasConversion(
                v => v.ToString(),
                v => Money.Parse(v));

public class Address
        {
            public string Street { get; set; }
            public string City { get; set; }
            public string PostalCode { get; set; }
            public string Country { get; set; }
        }

        public class User
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public Address Address { get; set; }
        }

        // Конфигурация
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>()
                .OwnsOne(u => u.Address, a =>
                {
                    a.Property(x => x.Street).HasColumnName("Address_Street").HasMaxLength(200);
                    a.Property(x => x.City).HasColumnName("Address_City").HasMaxLength(100);
                    a.Property(x => x.PostalCode).HasColumnName("Address_PostalCode").HasMaxLength(20);
                    a.Property(x => x.Country).HasColumnName("Address_Country").HasMaxLength(100);
                });
        }

        // Результат в БД:
        // Users table: Id, Name, Address_Street, Address_City, Address_PostalCode, Address_Country

public class ContactInfo
        {
            public string Email { get; set; }
            public string Phone { get; set; }
            public Address Address { get; set; }
        }

        public class Author
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public ContactInfo Contact { get; set; }
        }

        // Конфигурация
        modelBuilder.Entity<Author>()
            .OwnsOne(a => a.Contact, c => c.ToJson());

        // Query с фильтрацией по JSON property
        var authors = await context.Authors
            .Where(a => a.Contact.Address.City == "Chigley")
            .ToListAsync();

        // SQL: WHERE CAST(JSON_VALUE([Contact],'$.Address.City') AS nvarchar(max)) = N'Chigley'

public interface ISoftDelete
        {
            bool IsDeleted { get; set; }
            DateTime? DeletedAt { get; set; }
        }

        public class Blog : ISoftDelete
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public bool IsDeleted { get; set; }
            public DateTime? DeletedAt { get; set; }
        }

        // Глобальный фильтр
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Blog>()
                .HasQueryFilter(b => !b.IsDeleted);
        }

        // Автоматически применяется ко всем query
        var blogs = await context.Blogs.ToListAsync();
        // SQL: SELECT ... FROM Blogs WHERE IsDeleted = 0

        // Игнорировать фильтр
        var allBlogs = await context.Blogs
            .IgnoreQueryFilters()
            .ToListAsync();

public class TenantContext
        {
            public int TenantId { get; set; }
        }

        public interface ITenantEntity
        {
            int TenantId { get; set; }
        }

        // Конфигурация
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Применить фильтр ко всем ITenantEntity
            foreach (var entityType in modelBuilder.Model.GetEntityTypes())
            {
                if (typeof(ITenantEntity).IsAssignableFrom(entityType.ClrType))
                {
                    var parameter = Expression.Parameter(entityType.ClrType, "e");
                    var property = Expression.Property(parameter, nameof(ITenantEntity.TenantId));
                    var tenantId = Expression.Constant(_tenantContext.TenantId);
                    var filter = Expression.Lambda(Expression.Equal(property, tenantId), parameter);

                    modelBuilder.Entity(entityType.ClrType).HasQueryFilter(filter);
                }
            }
        }

// Свойства без CLR property в entity
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>()
                .Property<DateTime>("CreatedAt")
                .HasDefaultValueSql("GETUTCDATE()");

            modelBuilder.Entity<User>()
                .Property<DateTime>("UpdatedAt")
                .HasDefaultValueSql("GETUTCDATE()");

            modelBuilder.Entity<User>()
                .Property<string>("CreatedBy")
                .HasMaxLength(100);
        }

        // Использование
        var user = new User { Name = "Test" };
        context.Entry(user).Property("CreatedAt").CurrentValue = DateTime.UtcNow;

        // В query
        var users = await context.Users
            .Where(e => EF.Property<DateTime>(e, "CreatedAt") > DateTime.UtcNow.AddDays(-7))
            .ToListAsync();

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Order>()
                .Property(o => o.TotalWithTax)
                .HasComputedColumnSql("[Total] * (1 + [TaxRate])");

            // Persisted — вычисляется при insert/update, хранится в таблице
            modelBuilder.Entity<Order>()
                .Property(o => o.TotalWithTax)
                .HasComputedColumnSql("[Total] * (1 + [TaxRate])", stored: true);
        }

// Embedding vs Referencing
        public class Order
        {
            [BsonId]
            [BsonRepresentation(BsonType.ObjectId)]
            public string Id { get; set; }

            public string CustomerId { get; set; }

            // Embedding — когда данные всегда нужны вместе
            public List<OrderItem> Items { get; set; }

            // Referencing — когда данные используются отдельно
            public string ShippingAddressId { get; set; }
        }

        public class OrderItem
        {
            public string ProductId { get; set; }
            public string ProductName { get; set; }
            public decimal Price { get; set; }
            public int Quantity { get; set; }
        }

        // Embedding когда:
        // - Данные всегда читаются вместе
        // - Не нужно обновлять отдельно
        // - Размер документа < 16MB (MongoDB limit)

        // Referencing когда:
        // - Данные используются в разных контекстах
        // - Часто обновляются отдельно
        // - Большой размер (избегай document growth)

// Cosmos DB: Partition Key — критичный выбор
        // Плохой partition key: hot partition, uneven distribution
        // Хороший partition key: равномерное распределение, логичная группировка

        // Примеры:
        // /customerId — для multi-tenant SaaS
        // /orderId — для e-commerce
        // /deviceId — для IoT

        // Consistency Levels (Cosmos DB):
        // Strong — линейная консистентность, highest latency
        // Bounded Staleness — отставание до K операций или T секунд
        // Session — read-your-writes, default для большинства
        // Consistent Prefix — monotonic reads
        // Eventual — no ordering guarantee, lowest latency

// Document model для e-commerce
        public class ProductDocument
        {
            [BsonId]
            public string Id { get; set; }

            public string Name { get; set; }
            public string Description { get; set; }
            public decimal Price { get; set; }
            public string CategoryId { get; set; }

            // Embedding variants
            public List<ProductVariant> Variants { get; set; }

            // Embedding reviews (последние 100)
            public List<Review> RecentReviews { get; set; }

            // Tags для поиска
            public List<string> Tags { get; set; }

            // Inventory — referencing если нужно отдельно
            public string InventoryId { get; set; }
        }

        // Indexes для query patterns
        // { CategoryId: 1, Price: 1 } — фильтрация по категории + сортировка
        // { Tags: 1 } — поиск по тегам
        // { Name: "text" } — full-text search

using StackExchange.Redis;

        var redis = ConnectionMultiplexer.Connect("localhost:6379");
        var db = redis.GetDatabase();

        // String — simple key-value
        await db.StringSetAsync("user:1:name", "John");
        var name = await db.StringGetStringAsync("user:1:name");

        // Hash — object fields
        await db.HashSetAsync("user:1", new HashEntry[]
        {
            new("Name", "John"),
            new("Email", "john@example.com"),
            new("Age", "30")
        });
        var hash = await db.HashGetAllAsync("user:1");

        // List — ordered collection
        await db.ListRightPushAsync("queue:emails", "email1.json");
        await db.ListRightPushAsync("queue:emails", "email2.json");
        var item = await db.ListLeftPopAsync("queue:emails");  // FIFO

        // Set — unique unordered collection
        await db.SetAddAsync("tags:post:1", "csharp");
        await db.SetAddAsync("tags:post:1", "dotnet");
        await db.SetAddAsync("tags:post:1", "csharp");  // duplicate ignored
        var tags = await db.SetMembersAsync("tags:post:1");

        // Sorted Set — ranked collection
        await db.SortedSetAddAsync("leaderboard", "player1", 1000);
        await db.SortedSetAddAsync("leaderboard", "player2", 1500);
        var topPlayers = await db.SortedSetRangeByRankWithScoresAsync("leaderboard", 0, 9, Order.Descending);

public async Task<User> GetUserAsync(int userId)
        {
            var cacheKey = $"user:{userId}";

            // 1. Проверить cache
            var cached = await db.StringGetAsync(cacheKey);
            if (cached.HasValue)
                return JsonSerializer.Deserialize<User>(cached!);

            // 2. Cache miss — загрузить из БД
            var user = await _userRepository.GetByIdAsync(userId);
            if (user != null)
            {
                // 3. Сохранить в cache с TTL
                await db.StringSetAsync(cacheKey,
                    JsonSerializer.Serialize(user),
                    TimeSpan.FromMinutes(30));
            }

            return user;
        }

public async Task UpdateUserAsync(User user)
        {
            // 1. Обновить БД
            await _userRepository.UpdateAsync(user);

            // 2. Обновить cache
            var cacheKey = $"user:{user.Id}";
            await db.StringSetAsync(cacheKey,
                JsonSerializer.Serialize(user),
                TimeSpan.FromMinutes(30));
        }

public class WriteBehindCache
        {
            private readonly ConcurrentDictionary<string, object> _pendingWrites = new();
            private readonly Timer _flushTimer;

            public WriteBehindCache()
            {
                // Flush каждые 5 секунд
                _flushTimer = new Timer(FlushPendingWrites, null, TimeSpan.Zero, TimeSpan.FromSeconds(5));
            }

            public async Task SetAsync(string key, object value)
            {
                // 1. Обновить cache immediately
                await _redisDb.StringSetAsync(key, Serialize(value), TimeSpan.FromMinutes(30));

                // 2. Queue для background write to DB
                _pendingWrites[key] = value;
            }

            private async void FlushPendingWrites(object state)
            {
                var writes = _pendingWrites.ToArray();
                _pendingWrites.Clear();

                foreach (var (key, value) in writes)
                {
                    // Write to database
                    await _dbRepository.UpsertAsync(key, value);
                }
            }
        }

// Publisher
        await db.PublishAsync("notifications:user:1", "New message!");

        // Subscriber
        var subscriber = redis.GetSubscriber();
        await subscriber.SubscribeAsync("notifications:user:1", (channel, message) =>
        {
            Console.WriteLine($"Received: {message}");
            // Отправить push notification, email, etc.
        });

public class RedisRateLimiter
        {
            private readonly IDatabase _redis;

            public RedisRateLimiter(IConnectionMultiplexer redis)
            {
                _redis = redis.GetDatabase();
            }

            // Fixed Window
            public async Task<bool> TryAcquireFixedAsync(string key, int limit, TimeSpan window)
            {
                var windowKey = $"{key}:{DateTime.UtcNow.Ticks / window.Ticks}";
                var count = await _redis.StringIncrementAsync(windowKey);

                if (count == 1)
                    await _redis.KeyExpireAsync(windowKey, window);

                return count <= limit;
            }

            // Sliding Window Log (Sorted Sets)
            public async Task<bool> TryAcquireSlidingAsync(string key, int limit, TimeSpan window)
            {
                var now = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();
                var windowStart = now - window.TotalMilliseconds;

                // Удалить старые entries
                await _redis.SortedSetRemoveRangeByScoreAsync(key, 0, windowStart);

                // Посчитать текущие
                var count = await _redis.SortedSetLengthAsync(key);

                if (count < limit)
                {
                    await _redis.SortedSetAddAsync(key, now.ToString(), now);
                    await _redis.KeyExpireAsync(key, window);
                    return true;
                }

                return false;
            }

            // Token Bucket
            public async Task<bool> TryAcquireTokenBucketAsync(string key, int capacity, int refillRate)
            {
                var now = DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();

                var result = await _redis.ScriptEvaluateAsync(LuaScripts.TokenBucket,
                    new RedisKey[] { key },
                    new RedisValue[] { capacity, refillRate, now });

                return (bool)result;
            }
        }

        public static class LuaScripts
        {
            public const string TokenBucket = @"
                local key = KEYS[1]
                local capacity = tonumber(ARGV[1])
                local refill_rate = tonumber(ARGV[2])
                local now = tonumber(ARGV[3])

                local bucket = redis.call('HMGET', key, 'tokens', 'last_refill')
                local tokens = tonumber(bucket[1]) or capacity
                local last_refill = tonumber(bucket[2]) or now

                -- Refill tokens
                local elapsed = math.max(0, now - last_refill)
                tokens = math.min(capacity, tokens + elapsed * refill_rate / 1000)

                if tokens >= 1 then
                    tokens = tokens - 1
                    redis.call('HMSET', key, 'tokens', tokens, 'last_refill', now)
                    redis.call('EXPIRE', key, 60)
                    return true
                else
                    return false
                end
            ";
        }

public class NotificationService
        {
            private readonly ISubscriber _subscriber;

            public NotificationService(IConnectionMultiplexer redis)
            {
                _subscriber = redis.GetSubscriber();
            }

            public async Task SendNotificationAsync(int userId, string message)
            {
                await _subscriber.PublishAsync($"notifications:{userId}", message);
            }

            public async Task SubscribeAsync(int userId, Func<string, Task> handler)
            {
                await _subscriber.SubscribeAsync($"notifications:{userId}", async (channel, message) =>
                {
                    await handler(message);
                });
            }
        }

using InfluxDB.Client;

        var client = InfluxDBClientFactory.Create("http://localhost:8086", "token");
        var writeApi = client.GetWriteApi();

        // Write point
        var point = PointData.Measurement("cpu_usage")
            .Tag("host", "server01")
            .Tag("region", "us-east")
            .Field("value", 78.5)
            .Timestamp(DateTime.UtcNow, WritePrecision.Ns);

        await writeApi.WritePointAsync("my-bucket", "my-org", point);

        // Query
        var queryApi = client.GetQueryApi();
        var tables = await queryApi.QueryAsync(
            "from(bucket:\"my-bucket\")" +
            "  |> range(start: -1h)" +
            "  |> filter(fn: (r) => r._measurement == \"cpu_usage\")" +
            "  |> filter(fn: (r) => r.host == \"server01\")");

-- Создать hypertable
        SELECT create_hypertable('metrics', 'time');

        -- Continuous aggregate для pre-computation
        CREATE MATERIALIZED VIEW metrics_hourly
        WITH (timescaledb.continuous) AS
        SELECT
            time_bucket('1 hour', time) AS bucket,
            device_id,
            AVG(temperature) AS avg_temp,
            MAX(temperature) AS max_temp
        FROM metrics
        GROUP BY bucket, device_id;

        -- Compression
        ALTER TABLE metrics SET (
            timescaledb.compress,
            timescaledb.compress_segmentby = 'device_id',
            timescaledb.compress_orderby = 'time DESC'
        );

        SELECT add_compression_policy('metrics', INTERVAL '7 days');

В distributed system можно выбрать только 2 из 3:

        CP (Consistency + Partition Tolerance):
        - MongoDB, Redis, HBase
        - При partition — отказ от availability
        - Гарантирует consistency ценой availability

        AP (Availability + Partition Tolerance):
        - Cassandra, DynamoDB, CouchDB
        - При partition — eventual consistency
        - Гарантирует availability ценой consistency

        CA (Consistency + Availability):
        - Single-node RDBMS (PostgreSQL, MySQL)
        - Невозможно в distributed system с partitions

// SQL Server — CP по умолчанию
        // При network partition:
        // - Primary replica: продолжает работать (write)
        // - Secondary replicas: недоступны для reads (если sync)

        // Cosmos DB — настраиваемый consistency
        // Strong: CP (linearizable reads)
        // Session: AP (read-your-writes)
        // Eventual: AP (no ordering)

        // Выбор зависит от business requirements:
        // Финансовые транзакции → Strong consistency (CP)
        // Social media likes → Eventual consistency (AP)
        // Shopping cart → Session consistency (AP)

Atomicity — все или ничего
        Consistency — валидное состояние
        Isolation — независимые транзакции
        Durability — сохранено после commit

Basically Available — система всегда отвечает
        Soft state — состояние может меняться без input
        Eventually consistent — consistency со временем

// Shard Key — критичный выбор
        // Плохой: sequential ID (hot shard)
        // Хороший: hash(user_id), tenant_id, geographic

        public class ShardResolver
        {
            private readonly int _shardCount;

            public ShardResolver(int shardCount)
            {
                _shardCount = shardCount;
            }

            // Hash-based sharding
            public int GetShardId(string tenantId)
            {
                var hash = ComputeHash(tenantId);
                return hash % _shardCount;
            }

            // Range-based sharding
            public int GetShardIdByRange(int userId)
            {
                return userId switch
                {
                    < 1_000_000 => 0,
                    < 2_000_000 => 1,
                    < 3_000_000 => 2,
                    _ => 3
                };
            }

            // Consistent Hashing — для dynamic shard count
            private readonly ConsistentHashRing _hashRing;

            public int GetShardIdConsistent(string key)
            {
                return _hashRing.GetNode(key);
            }

            private static int ComputeHash(string input)
            {
                using var sha256 = SHA256.Create();
                var bytes = sha256.ComputeHash(Encoding.UTF8.GetBytes(input));
                return BitConverter.ToInt32(bytes, 0) & int.MaxValue;
            }
        }

public class ShardConnectionFactory
        {
            private readonly Dictionary<int, string> _shardConnections;

            public ShardConnectionFactory(Dictionary<int, string> shardConnections)
            {
                _shardConnections = shardConnections;
            }

            public SqlConnection GetConnection(int shardId)
            {
                if (!_shardConnections.TryGetValue(shardId, out var connectionString))
                    throw new KeyNotFoundException($"Shard {shardId} not found");

                return new SqlConnection(connectionString);
            }

            // Cross-shard query — fan-out
            public async Task<IEnumerable<User>> GetAllUsersAsync()
            {
                var tasks = _shardConnections.Select(async kvp =>
                {
                    using var connection = new SqlConnection(kvp.Value);
                    await connection.OpenAsync();

                    using var cmd = new SqlCommand("SELECT * FROM Users", connection);
                    using var reader = await cmd.ExecuteReaderAsync();

                    var users = new List<User>();
                    while (await reader.ReadAsync())
                    {
                        users.Add(MapUser(reader));
                    }
                    return users;
                });

                var results = await Task.WhenAll(tasks);
                return results.SelectMany(u => u);
            }
        }

// Когда shards unbalanced:
        // 1. Добавить новый shard
        // 2. Переместить данные (consistent hashing minimizes moves)
        // 3. Обновить routing table

        public class ShardRebalancer
        {
            public async Task RebalanceAsync(int newShardCount)
            {
                var oldShardCount = _currentShardCount;

                for (int i = 0; i < oldShardCount; i++)
                {
                    // Найти данные для перемещения
                    var keysToMove = await GetKeysForShardAsync(i, newShardCount);

                    foreach (var key in keysToMove)
                    {
                        var newShardId = ComputeNewShardId(key, newShardCount);
                        await MoveKeyAsync(key, i, newShardId);
                    }
                }

                _currentShardCount = newShardCount;
            }
        }

Primary (write) → Replica 1 (read)
                       → Replica 2 (read)
                       → Replica 3 (read)

        // SQL Server Always On Availability Groups
        // PostgreSQL Streaming Replication
        // MySQL Master-Slave Replication

Master 1 ↔ Master 2 ↔ Master 3
           ↓          ↓          ↓
          App 1      App 2      App 3

        // Все мастера принимают writes
        // Conflict resolution required
        // Cosmos DB multi-region writes
        // Cassandra multi-datacenter

public class ReadWriteSplittingDbContext : DbContext
        {
            private readonly DbContextOptions _writeOptions;
            private readonly DbContextOptions _readOptions;

            public ReadWriteSplittingDbContext(
                DbContextOptions writeOptions,
                DbContextOptions readOptions)
                : base(writeOptions)
            {
                _writeOptions = writeOptions;
                _readOptions = readOptions;
            }

            // Read context — replica
            public AppDbContext CreateReadContext()
            {
                return new AppDbContext(_readOptions);
            }

            // Write context — primary
            // Используется this (base context)
        }

        // В Program.cs
        services.AddDbContext<AppDbContext>(options =>
            options.UseSqlServer(primaryConnectionString));

        services.AddDbContext<AppDbContext>(
            "ReadDbContext",
            options => options.UseSqlServer(replicaConnectionString));

Phase 1: Prepare
          Coordinator → Participant 1: Prepare?
          Coordinator → Participant 2: Prepare?
          Participant 1 → Coordinator: Ready
          Participant 2 → Coordinator: Ready

        Phase 2: Commit
          Coordinator → Participant 1: Commit!
          Coordinator → Participant 2: Commit!
          Participant 1 → Coordinator: Committed
          Participant 2 → Coordinator: Committed

        // Проблема: blocking, slow, single point of failure
        // Использовать только когда необходимо

// Saga — последовательность локальных транзакций
        // Каждая транзакция имеет compensating action

        public class CreateOrderSaga
        {
            public async Task ExecuteAsync(Order order)
            {
                try
                {
                    // Step 1: Create order
                    await _orderService.CreateAsync(order);

                    // Step 2: Reserve inventory
                    await _inventoryService.ReserveAsync(order.Items);

                    // Step 3: Process payment
                    await _paymentService.ChargeAsync(order.Total);

                    // Step 4: Send notification
                    await _notificationService.SendAsync(order);
                }
                catch (Exception ex)
                {
                    // Compensating actions (rollback)
                    await CompensateAsync(order, ex);
                    throw;
                }
            }

            private async Task CompensateAsync(Order order, Exception originalException)
            {
                // Reverse order: last action first
                try { await _notificationService.CancelAsync(order); }
                catch { /* log and continue */ }

                try { await _paymentService.RefundAsync(order.Total); }
                catch { /* log and continue */ }

                try { await _inventoryService.ReleaseAsync(order.Items); }
                catch { /* log and continue */ }

                try { await _orderService.CancelAsync(order.Id); }
                catch { /* log and continue */ }
            }
        }

public class SagaOrchestrator
        {
            private readonly List<SagaStep> _steps = new();

            public SagaOrchestrator AddStep(
                Func<Task> action,
                Func<Task> compensate)
            {
                _steps.Add(new SagaStep(action, compensate));
                return this;
            }

            public async Task ExecuteAsync()
            {
                var completedSteps = new List<int>();

                try
                {
                    for (int i = 0; i < _steps.Count; i++)
                    {
                        await _steps[i].Action();
                        completedSteps.Add(i);
                    }
                }
                catch (Exception ex)
                {
                    // Compensate in reverse order
                    for (int i = completedSteps.Count - 1; i >= 0; i--)
                    {
                        try
                        {
                            await _steps[completedSteps[i]].Compensate();
                        }
                        catch (Exception compensateEx)
                        {
                            // Log compensation failure
                            _logger.LogError(compensateEx,
                                "Compensation failed for step {Step}",
                                completedSteps[i]);
                        }
                    }

                    throw new SagaExecutionException("Saga failed", ex);
                }
            }
        }

        // Usage
        var saga = new SagaOrchestrator()
            .AddStep(
                () => orderService.CreateAsync(order),
                () => orderService.CancelAsync(order.Id))
            .AddStep(
                () => inventoryService.ReserveAsync(order.Items),
                () => inventoryService.ReleaseAsync(order.Items))
            .AddStep(
                () => paymentService.ChargeAsync(order.Total),
                () => paymentService.RefundAsync(order.Total));

        await saga.ExecuteAsync();

// Outbox — reliable event publishing
        // 1. Сохранить entity + event в одной транзакции
        // 2. Background process читает outbox и публикует events

        public class OutboxMessage
        {
            public Guid Id { get; set; }
            public string EventType { get; set; }
            public string Payload { get; set; }
            public DateTime CreatedAt { get; set; }
            public DateTime? ProcessedAt { get; set; }
            public int RetryCount { get; set; }
            public string? Error { get; set; }
        }

        // В той же транзакции что и business data
        public async Task CreateOrderAsync(Order order)
        {
            using var transaction = await _context.Database.BeginTransactionAsync();

            try
            {
                // 1. Сохранить order
                _context.Orders.Add(order);
                await _context.SaveChangesAsync();

                // 2. Сохранить event в outbox (та же транзакция!)
                _context.OutboxMessages.Add(new OutboxMessage
                {
                    Id = Guid.NewGuid(),
                    EventType = "OrderCreated",
                    Payload = JsonSerializer.Serialize(order),
                    CreatedAt = DateTime.UtcNow
                });
                await _context.SaveChangesAsync();

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

        // Background processor
        public class OutboxProcessor : BackgroundService
        {
            protected override async Task ExecuteAsync(CancellationToken stoppingToken)
            {
                while (!stoppingToken.IsCancellationRequested)
                {
                    await ProcessOutboxAsync(stoppingToken);
                    await Task.Delay(TimeSpan.FromSeconds(5), stoppingToken);
                }
            }

            private async Task ProcessOutboxAsync(CancellationToken ct)
            {
                var messages = await _context.OutboxMessages
                    .Where(m => m.ProcessedAt == null && m.RetryCount < 5)
                    .OrderBy(m => m.CreatedAt)
                    .Take(100)
                    .ToListAsync(ct);

                foreach (var message in messages)
                {
                    try
                    {
                        await _publisher.PublishAsync(message.EventType, message.Payload, ct);

                        message.ProcessedAt = DateTime.UtcNow;
                        await _context.SaveChangesAsync(ct);
                    }
                    catch (Exception ex)
                    {
                        message.RetryCount++;
                        message.Error = ex.Message;
                        await _context.SaveChangesAsync(ct);
                    }
                }
            }
        }

// Eventual consistency приемлемо когда:
        // - Данные не критичны для немедленного чтения
        // - Users могут tolerate stale data briefly
        // - Performance/availability важнее immediate consistency

        // Примеры:
        // - Social media likes/comments
        // - Product reviews
        // - Search index updates
        // - Analytics/aggregations

        // НЕ приемлемо для:
        // - Financial transactions
        // - Inventory (overselling risk)
        // - Authentication/authorization

// Outbox pattern гарантирует delivery
        // Eventual consistency через event-driven architecture

        public class EventualConsistencyHandler
        {
            // 1. Event published (outbox гарантирует delivery)
            // 2. Subscriber processes event
            // 3. Read model updated
            // 4. Eventual consistency achieved

            // Idempotency critical — subscriber может получить event multiple times
            public async Task HandleOrderCreatedAsync(OrderCreatedEvent @event)
            {
                // Idempotency check
                if (await _processedEvents.ExistsAsync(@event.Id))
                    return;

                // Process event
                await _readModel.UpdateOrderAsync(@event.Order);

                // Mark as processed
                await _processedEvents.AddAsync(@event.Id);
            }
        }

// Простейшая стратегия — последний write побеждает
        // Требует vector clock или timestamp

        public class LwwRegister<T>
        {
            private T _value;
            private long _timestamp;

            public void Set(T value, long timestamp)
            {
                if (timestamp > _timestamp)
                {
                    _value = value;
                    _timestamp = timestamp;
                }
            }

            public T Get() => _value;
        }

        // Проблема: concurrent writes могут lost updates
        // Решение: application-level merge logic

// G-Counter (Grow-only Counter)
        public class GCounter
        {
            private readonly Dictionary<string, int> _counts = new();

            public void Increment(string nodeId)
            {
                _counts[nodeId] = _counts.GetValueOrDefault(nodeId) + 1;
            }

            public int Value() => _counts.Values.Sum();

            // Merge — commutative, associative, idempotent
            public GCounter Merge(GCounter other)
            {
                var result = new GCounter();
                var allKeys = _counts.Keys.Union(other._counts.Keys);

                foreach (var key in allKeys)
                {
                    result._counts[key] = Math.Max(
                        _counts.GetValueOrDefault(key, 0),
                        other._counts.GetValueOrDefault(key, 0));
                }

                return result;
            }
        }

        // PN-Counter (Positive-Negative Counter)
        public class PNCounter
        {
            private readonly GCounter _positive = new();
            private readonly GCounter _negative = new();

            public void Increment(string nodeId) => _positive.Increment(nodeId);
            public void Decrement(string nodeId) => _negative.Increment(nodeId);
            public int Value() => _positive.Value() - _negative.Value();
        }

        // G-Set (Grow-only Set)
        public class GSet<T> where T : notnull
        {
            private readonly HashSet<T> _elements = new();

            public void Add(T element) => _elements.Add(element);
            public bool Contains(T element) => _elements.Contains(element);

            public GSet<T> Merge(GSet<T> other)
            {
                var result = new GSet<T>();
                foreach (var e in _elements.Union(other._elements))
                    result.Add(e);
                return result;
            }
        }

// Idempotency key гарантирует что operation выполняется только раз
        // Critical для payment processing, order creation

        public class IdempotencyMiddleware
        {
            private readonly IDatabase _redis;

            public IdempotencyMiddleware(IConnectionMultiplexer redis)
            {
                _redis = redis.GetDatabase();
            }

            public async Task<T> ExecuteAsync<T>(
                string idempotencyKey,
                Func<Task<T>> action,
                TimeSpan expiry)
            {
                var cacheKey = $"idempotency:{idempotencyKey}";

                // Try to acquire lock
                var acquired = await _redis.StringSetAsync(
                    cacheKey,
                    "processing",
                    expiry,
                    When.NotExists);

                if (!acquired)
                {
                    // Check if already completed
                    var existing = await _redis.StringGetAsync(cacheKey);
                    if (existing.HasValue && existing != "processing")
                    {
                        return Deserialize<T>(existing);
                    }

                    throw new IdempotencyConflictException(
                        "Request already in progress or completed");
                }

                try
                {
                    var result = await action();
                    await _redis.StringSetAsync(cacheKey, Serialize(result), expiry);
                    return result;
                }
                catch
                {
                    // Remove key on failure — allow retry
                    await _redis.KeyDeleteAsync(cacheKey);
                    throw;
                }
            }
        }

        // Usage в API
        [HttpPost("orders")]
        public async Task<IActionResult> CreateOrder(
            [FromBody] CreateOrderRequest request,
            [FromHeader(Name = "Idempotency-Key")] string idempotencyKey)
        {
            if (string.IsNullOrEmpty(idempotencyKey))
                return BadRequest("Idempotency-Key header required");

            var order = await _idempotency.ExecuteAsync(
                idempotencyKey,
                () => _orderService.CreateAsync(request),
                TimeSpan.FromDays(7));

            return Ok(order);
        }

-- Deduplication table для idempotency
        CREATE TABLE IdempotencyKeys
        (
            Key NVARCHAR(128) PRIMARY KEY,
            Response NVARCHAR(MAX),
            CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
            ExpiresAt DATETIME2
        );

        -- Index для cleanup
        CREATE INDEX IX_IdempotencyKeys_ExpiresAt
            ON IdempotencyKeys(ExpiresAt);

        -- Cleanup expired keys
        DELETE FROM IdempotencyKeys WHERE ExpiresAt < GETUTCDATE();

public class Product
        {
            public int Id { get; set; }
            public string Name { get; set; }
            public decimal Price { get; set; }

            // ROWVERSION — automatic timestamp для concurrency
            [Timestamp]
            public byte[] RowVersion { get; set; }
        }

        // EF Core конфигурация
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Product>()
                .Property(p => p.RowVersion)
                .IsRowVersion();
        }

        // При update EF Core добавляет WHERE RowVersion = @original
        // UPDATE Products SET Name = @p0, Price = @p1, RowVersion = ...
        // WHERE Id = @p2 AND RowVersion = @p3

public class Order
        {
            public int Id { get; set; }
            public string Status { get; set; }

            // Явный concurrency token
            [ConcurrencyCheck]
            public int Version { get; set; }
        }

        // Или через Fluent API
        modelBuilder.Entity<Order>()
            .Property(o => o.Version)
            .IsConcurrencyToken();

public class OptimisticConcurrencyHandler
        {
            private readonly AppDbContext _context;
            private readonly ILogger<OptimisticConcurrencyHandler> _logger;

            public OptimisticConcurrencyHandler(
                AppDbContext context,
                ILogger<OptimisticConcurrencyHandler> logger)
            {
                _context = context;
                _logger = logger;
            }

            public async Task<T> ExecuteWithRetryAsync<T>(
                Func<Task<T>> action,
                int maxRetries = 3)
            {
                for (int attempt = 0; attempt <= maxRetries; attempt++)
                {
                    try
                    {
                        return await action();
                    }
                    catch (DbUpdateConcurrencyException ex)
                    {
                        if (attempt == maxRetries)
                        {
                            _logger.LogError(ex,
                                "Concurrency conflict after {Attempts} retries",
                                maxRetries);
                            throw;
                        }

                        _logger.LogWarning(ex,
                            "Concurrency conflict on attempt {Attempt}, retrying...",
                            attempt + 1);

                        await HandleConcurrencyConflictAsync(ex);

                        // Exponential backoff
                        await Task.Delay(TimeSpan.FromMilliseconds(
                            Math.Pow(2, attempt) * 100));
                    }
                }

                throw new InvalidOperationException("Should not reach here");
            }

            private async Task HandleConcurrencyConflictAsync(DbUpdateConcurrencyException ex)
            {
                foreach (var entry in ex.Entries)
                {
                    // Refresh original values from database
                    var databaseValues = await entry.GetDatabaseValuesAsync();

                    if (databaseValues == null)
                    {
                        // Entity was deleted
                        entry.State = EntityState.Detached;
                        continue;
                    }

                    // Update original values to current database state
                    entry.OriginalValues.SetValues(databaseValues);

                    // Optional: merge current values with database values
                    // entry.CurrentValues.SetValues(MergeValues(
                    //     entry.CurrentValues, databaseValues, entry.GetDatabaseValues()));
                }
            }
        }

        // Usage
        var result = await _concurrencyHandler.ExecuteWithRetryAsync(async () =>
        {
            var product = await _context.Products.FindAsync(productId);
            product.Price = newPrice;
            await _context.SaveChangesAsync();
            return product;
        });

-- SQL Server: UPDLOCK, ROWLOCK
        BEGIN TRANSACTION;

        SELECT * FROM Products
        WITH (UPDLOCK, ROWLOCK)
        WHERE Id = @Id;

        -- Другие транзакции ждут release lock
        UPDATE Products SET Stock = Stock - 1
        WHERE Id = @Id AND Stock > 0;

        COMMIT TRANSACTION;

        -- PostgreSQL: SELECT ... FOR UPDATE
        BEGIN;
        SELECT * FROM Products WHERE Id = $1 FOR UPDATE;
        UPDATE Products SET Stock = Stock - 1 WHERE Id = $1;
        COMMIT;

public async Task<decimal> GetProductPriceWithLockAsync(int productId)
        {
            using var transaction = await _context.Database.BeginTransactionAsync(
                IsolationLevel.Serializable);

            try
            {
                var product = await _context.Products
                    .FromSqlRaw("SELECT * FROM Products WITH (UPDLOCK, ROWLOCK) WHERE Id = {0}", productId)
                    .FirstAsync();

                // Product locked — no other transaction can modify
                product.Price = CalculateNewPrice(product);
                await _context.SaveChangesAsync();

                await transaction.CommitAsync();
                return product.Price;
            }
            catch
            {
                await transaction.RollbackAsync();
                throw;
            }
        }

// Repository полезен когда:
        // - Нужно абстрагировать data access от business logic
        // - Нужно mock data access для unit testing
        // - Нужно centralize complex query logic
        // - Multiple data sources (SQL, NoSQL, API)

        public interface IUserRepository
        {
            Task<User?> GetByIdAsync(int id);
            Task<IReadOnlyList<User>> GetActiveUsersAsync();
            Task<User> CreateAsync(User user);
            Task UpdateAsync(User user);
            Task DeleteAsync(int id);
        }

        public class SqlUserRepository : IUserRepository
        {
            private readonly AppDbContext _context;

            public SqlUserRepository(AppDbContext context)
            {
                _context = context;
            }

            public async Task<User?> GetByIdAsync(int id)
            {
                return await _context.Users.FindAsync(id);
            }

            public async Task<IReadOnlyList<User>> GetActiveUsersAsync()
            {
                return await _context.Users
                    .Where(u => u.IsActive)
                    .AsNoTracking()
                    .ToListAsync();
            }

            public async Task<User> CreateAsync(User user)
            {
                _context.Users.Add(user);
                await _context.SaveChangesAsync();
                return user;
            }

            public async Task UpdateAsync(User user)
            {
                _context.Users.Update(user);
                await _context.SaveChangesAsync();
            }

            public async Task DeleteAsync(int id)
            {
                var user = await _context.Users.FindAsync(id);
                if (user != null)
                {
                    _context.Users.Remove(user);
                    await _context.SaveChangesAsync();
                }
            }
        }

// Repository над EF Core часто антипаттерн когда:
        // - DbContext уже abstraction
        // - Repository просто прокси к DbContext
        // - Теряется power of IQueryable
        // - Leak абстракции (EF Core specific types)

        // ПЛОХО: leaky abstraction
        public interface IUserRepository
        {
            IQueryable<User> Query();  // EF Core specific
            Task SaveChangesAsync();   // DbContext method
        }

        // ХОРОШО: специфичные методы
        public interface IUserRepository
        {
            Task<User?> GetByEmailAsync(string email);
            Task<IReadOnlyList<User>> GetByRoleAsync(string role);
            Task<bool> ExistsAsync(string email);
        }

        // Правило: Repository должен скрывать data access details
        // Если просто делегирует к DbContext — не нужен

// DbContext уже реализует Unit of Work pattern
        // Change Tracker tracks all changes
        // SaveChanges commits all changes atomically

        public class OrderService
        {
            private readonly AppDbContext _context;

            public OrderService(AppDbContext context)
            {
                _context = context;
            }

            public async Task CreateOrderAsync(Order order)
            {
                // Multiple operations in single unit of work
                _context.Orders.Add(order);

                foreach (var item in order.Items)
                {
                    var product = await _context.Products.FindAsync(item.ProductId);
                    product.Stock -= item.Quantity;
                }

                // Single SaveChanges — atomic commit
                await _context.SaveChangesAsync();
            }
        }

public interface IUnitOfWork : IDisposable
        {
            IUserRepository Users { get; }
            IOrderRepository Orders { get; }
            Task<int> CommitAsync(CancellationToken ct = default);
        }

        public class UnitOfWork : IUnitOfWork
        {
            private readonly AppDbContext _context;
            private IUserRepository? _users;
            private IOrderRepository? _orders;

            public UnitOfWork(AppDbContext context)
            {
                _context = context;
            }

            public IUserRepository Users =>
                _users ??= new SqlUserRepository(_context);

            public IOrderRepository Orders =>
                _orders ??= new SqlOrderRepository(_context);

            public async Task<int> CommitAsync(CancellationToken ct = default)
            {
                return await _context.SaveChangesAsync(ct);
            }

            public void Dispose() => _context.Dispose();
        }

// Commands — write model (EF Core)
        public class CreateOrderCommand
        {
            public int CustomerId { get; set; }
            public List<OrderItemDto> Items { get; set; }
        }

        public class CreateOrderHandler
        {
            private readonly AppDbContext _context;

            public CreateOrderHandler(AppDbContext context)
            {
                _context = context;
            }

            public async Task<int> HandleAsync(CreateOrderCommand command)
            {
                var order = new Order
                {
                    CustomerId = command.CustomerId,
                    Items = command.Items.Select(i => new OrderItem
                    {
                        ProductId = i.ProductId,
                        Quantity = i.Quantity,
                        Price = i.Price
                    }).ToList()
                };

                _context.Orders.Add(order);
                await _context.SaveChangesAsync();

                return order.Id;
            }
        }

        // Queries — read model (Dapper/raw SQL)
        public class GetOrderQuery
        {
            public int OrderId { get; set; }
        }

        public class GetOrderHandler
        {
            private readonly IDbConnection _connection;

            public GetOrderHandler(IDbConnection connection)
            {
                _connection = connection;
            }

            public async Task<OrderDto?> HandleAsync(GetOrderQuery query)
            {
                const string sql = @"
                    SELECT o.Id, o.CustomerId, o.Status, o.CreatedAt,
                           i.Id as ItemId, i.ProductId, i.Quantity, i.Price
                    FROM Orders o
                    LEFT JOIN OrderItems i ON o.Id = i.OrderId
                    WHERE o.Id = @OrderId";

                var orderDict = new Dictionary<int, OrderDto>();

                await _connection.QueryAsync<OrderDto, OrderItemDto, OrderDto>(
                    sql,
                    (order, item) =>
                    {
                        if (!orderDict.TryGetValue(order.Id, out var orderEntry))
                        {
                            orderEntry = order;
                            orderEntry.Items = new List<OrderItemDto>();
                            orderDict.Add(orderEntry.Id, orderEntry);
                        }

                        if (item.ItemId != 0)
                            orderEntry.Items.Add(item);

                        return orderEntry;
                    },
                    new { query.OrderId },
                    splitOn: "ItemId");

                return orderDict.Values.FirstOrDefault();
            }
        }

// Write database — normalized, ACID
        // Read database — denormalized, optimized for queries

        public class WriteDbContext : DbContext
        {
            // Normalized schema
            public DbSet<Order> Orders { get; set; }
            public DbSet<OrderItem> OrderItems { get; set; }
            public DbSet<Customer> Customers { get; set; }
        }

        // Read database — denormalized view
        public class ReadDbContext : DbContext
        {
            public DbSet<OrderReadModel> OrderReadModels { get; set; }
        }

        public class OrderReadModel
        {
            public int Id { get; set; }
            public int CustomerId { get; set; }
            public string CustomerName { get; set; }
            public string Status { get; set; }
            public decimal Total { get; set; }
            public DateTime CreatedAt { get; set; }
            public string ItemsJson { get; set; }  // Denormalized
        }

        // Event handler для sync read model
        public class OrderCreatedEventHandler
        {
            public async Task HandleAsync(OrderCreatedEvent @event)
            {
                await _readDbContext.OrderReadModels.AddAsync(new OrderReadModel
                {
                    Id = @event.OrderId,
                    CustomerId = @event.CustomerId,
                    CustomerName = @event.CustomerName,
                    Status = "Created",
                    Total = @event.Total,
                    CreatedAt = @event.CreatedAt,
                    ItemsJson = JsonSerializer.Serialize(@event.Items)
                });
                await _readDbContext.SaveChangesAsync();
            }
        }

// Dapper — когда:
        // - High-performance read queries
        // - Complex SQL с window functions, CTEs
        // - Bulk operations
        // - Existing database schema

        // EF Core — когда:
        // - Complex domain model
        // - Change tracking needed
        // - Migrations management
        // - Rapid development

        // Hybrid — лучшее из обоих миров

// Результаты типичного benchmark (10000 reads):
        //
        // ADO.NET:     ~50ms   (baseline)
        // Dapper:      ~60ms   (20% overhead vs ADO.NET)
        // EF Core:     ~120ms  (140% overhead vs ADO.NET)
        // EF Core NoTracking: ~80ms (60% overhead vs ADO.NET)
        //
        // Results (10000 writes):
        //
        // ADO.NET:     ~200ms
        // Dapper:      ~210ms
        // EF Core:     ~500ms  (change tracking overhead)
        // EF Core Bulk: ~100ms (bypasses change tracker)

        public class DataAccessBenchmark
        {
            public async Task RunAsync()
            {
                // Read benchmark
                await BenchmarkReadAsync("ADO.NET", async () =>
                {
                    using var cmd = new SqlCommand("SELECT * FROM Users", _connection);
                    using var reader = await cmd.ExecuteReaderAsync();
                    var users = new List<User>();
                    while (await reader.ReadAsync())
                    {
                        users.Add(new User
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1),
                            Email = reader.GetString(2)
                        });
                    }
                    return users;
                });

                await BenchmarkReadAsync("Dapper", async () =>
                {
                    return (await _connection.QueryAsync<User>("SELECT * FROM Users")).ToList();
                });

                await BenchmarkReadAsync("EF Core", async () =>
                {
                    return await _context.Users.ToListAsync();
                });

                await BenchmarkReadAsync("EF Core NoTracking", async () =>
                {
                    return await _context.Users.AsNoTracking().ToListAsync();
                });
            }

            private async Task BenchmarkReadAsync(string name, Func<Task<IList<User>>> action)
            {
                var sw = Stopwatch.StartNew();
                for (int i = 0; i < 10; i++)
                {
                    await action();
                }
                Console.WriteLine($"{name}: {sw.ElapsedMilliseconds}ms");
            }
        }

// EF Core для writes, Dapper для reads

        public class HybridDataAccess
        {
            private readonly AppDbContext _context;
            private readonly IDbConnection _dbConnection;

            public HybridDataAccess(AppDbContext context, IDbConnection dbConnection)
            {
                _context = context;
                _dbConnection = dbConnection;
            }

            // Write — EF Core
            public async Task<User> CreateUserAsync(User user)
            {
                _context.Users.Add(user);
                await _context.SaveChangesAsync();
                return user;
            }

            // Read — Dapper
            public async Task<UserDto?> GetUserDtoAsync(int id)
            {
                const string sql = @"
                    SELECT u.Id, u.Name, u.Email,
                           COUNT(o.Id) as OrderCount,
                           SUM(o.Total) as TotalSpent
                    FROM Users u
                    LEFT JOIN Orders o ON u.Id = o.UserId
                    WHERE u.Id = @Id
                    GROUP BY u.Id, u.Name, u.Email";

                return await _dbConnection.QueryFirstOrDefaultAsync<UserDto>(sql, new { Id = id });
            }

            // Complex read — Dapper
            public async Task<IReadOnlyList<MonthlyReportDto>> GetMonthlyReportAsync(
                DateTime startDate, DateTime endDate)
            {
                const string sql = @"
                    SELECT
                        YEAR(o.CreatedAt) as Year,
                        MONTH(o.CreatedAt) as Month,
                        COUNT(*) as OrderCount,
                        SUM(o.Total) as Revenue,
                        AVG(o.Total) as AverageOrderValue
                    FROM Orders o
                    WHERE o.CreatedAt >= @StartDate AND o.CreatedAt < @EndDate
                    GROUP BY YEAR(o.CreatedAt), MONTH(o.CreatedAt)
                    ORDER BY Year DESC, Month DESC";

                return (await _dbConnection.QueryAsync<MonthlyReportDto>(sql,
                    new { StartDate = startDate, EndDate = endDate })).ToList();
            }
        }

        // Read-optimized query layer с caching
        public class QueryService
        {
            private readonly IDbConnection _dbConnection;
            private readonly IDatabase _redis;

            public QueryService(IDbConnection dbConnection, IConnectionMultiplexer redis)
            {
                _dbConnection = dbConnection;
                _redis = redis.GetDatabase();
            }

            public async Task<IReadOnlyList<ProductDto>> GetProductsAsync(
                string category, int page, int pageSize)
            {
                var cacheKey = $"products:{category}:{page}:{pageSize}";

                var cached = await _redis.StringGetAsync(cacheKey);
                if (cached.HasValue)
                    return Deserialize<List<ProductDto>>(cached!);

                const string sql = @"
                    SELECT p.Id, p.Name, p.Price, p.Stock,
                           c.Name as CategoryName
                    FROM Products p
                    JOIN Categories c ON p.CategoryId = c.Id
                    WHERE c.Name = @Category
                    ORDER BY p.Name
                    OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

                var products = (await _dbConnection.QueryAsync<ProductDto>(sql,
                    new { Category = category, Offset = (page - 1) * pageSize, PageSize }))
                    .ToList();

                await _redis.StringSetAsync(cacheKey,
                    Serialize(products),
                    TimeSpan.FromMinutes(5));

                return products;
            }
        }

public class QueryPerformanceInterceptor : DbCommandInterceptor
        {
            private readonly ILogger<QueryPerformanceInterceptor> _logger;
            private readonly IMetricsCollector _metrics;
            private readonly TimeSpan _slowThreshold;

            public QueryPerformanceInterceptor(
                ILogger<QueryPerformanceInterceptor> logger,
                IMetricsCollector metrics,
                TimeSpan? slowThreshold = null)
            {
                _logger = logger;
                _metrics = metrics;
                _slowThreshold = slowThreshold ?? TimeSpan.FromMilliseconds(100);
            }

            public override async ValueTask<DbDataReader> ReaderExecutedAsync(
                DbCommand command,
                CommandExecutedEventData eventData,
                DbDataReader result,
                CancellationToken cancellationToken = default)
            {
                await RecordQueryAsync(command, eventData, cancellationToken);
                return result;
            }

            public override async ValueTask<int> NonQueryExecutedAsync(
                DbCommand command,
                CommandExecutedEventData eventData,
                int result,
                CancellationToken cancellationToken = default)
            {
                await RecordQueryAsync(command, eventData, cancellationToken);
                return result;
            }

            private async Task RecordQueryAsync(DbCommand command, CommandExecutedEventData eventData)
            {
                var duration = eventData.Duration.TotalMilliseconds;

                // Record metric (Prometheus, Application Insights, etc.)
                _metrics.RecordQueryDuration(
                    commandType: command.CommandType.ToString(),
                    commandText: GetQuerySignature(command.CommandText),
                    duration: duration,
                    succeeded: true);

                // Log slow queries
                if (duration > _slowThreshold.TotalMilliseconds)
                {
                    _logger.LogWarning(
                        "Slow query ({Duration}ms): {Signature} [{Parameters}]",
                        duration,
                        GetQuerySignature(command.CommandText),
                        string.Join(", ", command.Parameters.Cast<DbParameter>()
                            .Select(p => $"{p.ParameterName}={p.Value}")));
                }
            }

            // Normalize query text for grouping
            private static string GetQuerySignature(string sql)
            {
                // Replace literals with placeholders for grouping
                return Regex.Replace(sql, @"'[^']*'", "'?'");
            }
        }

-- SQL Server: Query execution plan cache
        SELECT
            qs.execution_count,
            qs.total_worker_time / qs.execution_count AS avg_cpu_time,
            qs.total_elapsed_time / qs.execution_count AS avg_duration,
            qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
            SUBSTRING(st.text,
                (qs.statement_start_offset / 2) + 1,
                (CASE qs.statement_end_offset
                    WHEN -1 THEN DATALENGTH(st.text)
                    ELSE qs.statement_end_offset
                END - qs.statement_start_offset) / 2 + 1) AS query_text,
            qp.query_plan
        FROM sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
        CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
        ORDER BY avg_duration DESC;

        -- Top 10 most expensive queries
        SELECT TOP 10
            qs.total_worker_time AS total_cpu_time,
            qs.total_elapsed_time,
            qs.execution_count,
            st.text
        FROM sys.dm_exec_query_stats qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
        ORDER BY qs.total_worker_time DESC;

public class ConnectionPoolHealthCheck : IHealthCheck
        {
            private readonly string _connectionString;

            public ConnectionPoolHealthCheck(string connectionString)
            {
                _connectionString = connectionString;
            }

            public async Task<HealthCheckResult> CheckHealthAsync(
                HealthCheckContext context,
                CancellationToken cancellationToken = default)
            {
                var sw = Stopwatch.StartNew();

                try
                {
                    using var connection = new SqlConnection(_connectionString);
                    await connection.OpenAsync(cancellationToken);
                    sw.Stop();

                    var data = new Dictionary<string, object>
                    {
                        ["ConnectionOpenTimeMs"] = sw.ElapsedMilliseconds,
                        ["ServerVersion"] = connection.ServerVersion,
                        ["State"] = connection.State.ToString()
                    };

                    return sw.ElapsedMilliseconds > 1000
                        ? HealthCheckResult.Degraded(
                            "Connection open time is high", data: data)
                        : HealthCheckResult.Healthy(data: data);
                }
                catch (Exception ex)
                {
                    return HealthCheckResult.Unhealthy(
                        "Failed to open connection", ex);
                }
            }
        }

        // Performance counters для пула
        // .NET Data Provider for SqlServer:
        // - NumberOfActiveConnectionPools
        // - NumberOfPooledConnections
        // - NumberOfNonPooledConnections
        // - NumberOfReclaimedConnections

        // В production monitoring:
        // - Connection open time > 1s — pool exhaustion warning
        // - Reclaimed connections — connection leak indicator
        // - Timeout errors — pool size too small

-- SQL Server: Deadlock detection
        -- Enable deadlock graph in error log
        DBCC TRACEON(1222, -1);  -- Deadlock graph
        DBCC TRACEON(1204, -1);  -- Deadlock details

        -- Query current locks
        SELECT
            request_session_id AS spid,
            resource_type,
            resource_description,
            resource_associated_entity_id,
            request_mode,
            request_status
        FROM sys.dm_tran_locks
        WHERE resource_database_id = DB_ID();

        -- Blocking chain
        SELECT
            blocking.session_id AS blocking_session_id,
            blocked.session_id AS blocked_session_id,
            waitstats.wait_type AS blocking_resource,
            waitstats.wait_duration_ms,
            waitstats.resource_description,
            blocked_query.text AS blocked_query_text,
            blocking_query.text AS blocking_query_text
        FROM sys.dm_exec_requests blocked
        JOIN sys.dm_exec_requests blocking
            ON blocked.blocking_session_id = blocking.session_id
        JOIN sys.dm_os_waiting_tasks waitstats
            ON waitstats.session_id = blocked.session_id
        CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_query
        CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) blocking_query;

public class DeadlockMonitor : BackgroundService
        {
            private readonly ILogger<DeadlockMonitor> _logger;
            private readonly IAlertService _alertService;
            private readonly string _connectionString;

            public DeadlockMonitor(
                ILogger<DeadlockMonitor> logger,
                IAlertService alertService,
                string connectionString)
            {
                _logger = logger;
                _alertService = alertService;
                _connectionString = connectionString;
            }

            protected override async Task ExecuteAsync(CancellationToken stoppingToken)
            {
                while (!stoppingToken.IsCancellationRequested)
                {
                    try
                    {
                        var deadlocks = await GetRecentDeadlocksAsync(stoppingToken);

                        if (deadlocks.Count > 0)
                        {
                            _logger.LogWarning(
                                "Detected {Count} deadlocks in last interval",
                                deadlocks.Count);

                            await _alertService.SendAlertAsync(
                                "Database Deadlock Detected",
                                $"Found {deadlocks.Count} deadlocks. " +
                                $"Victims: {string.Join(", ", deadlocks.Select(d => d.VictimSessionId))}");
                        }
                    }
                    catch (Exception ex)
                    {
                        _logger.LogError(ex, "Error checking for deadlocks");
                    }

                    await Task.Delay(TimeSpan.FromMinutes(5), stoppingToken);
                }
            }

            private async Task<List<DeadlockInfo>> GetRecentDeadlocksAsync(CancellationToken ct)
            {
                using var connection = new SqlConnection(_connectionString);
                await connection.OpenAsync(ct);

                // Query system_health extended events for deadlocks
                const string sql = @"
                    SELECT
                        xed.value('@timestamp', 'datetime2') AS DeadlockTime,
                        xed.query('.').value('.', 'nvarchar(max)') AS DeadlockGraph
                    FROM (
                        SELECT CAST(target_data AS XML) AS target_data
                        FROM sys.dm_xe_sessions AS s
                        JOIN sys.dm_xe_session_targets AS t
                            ON s.address = t.event_session_address
                        WHERE s.name = 'system_health'
                        AND t.target_name = 'ring_buffer'
                    ) AS data
                    CROSS APPLY target_data.nodes('RingBufferTarget/event[@name=""xml_deadlock_report""]') AS x(xed)
                    WHERE xed.value('@timestamp', 'datetime2') > DATEADD(minute, -10, GETUTCDATE())
                    ORDER BY DeadlockTime DESC";

                using var cmd = new SqlCommand(sql, connection);
                using var reader = await cmd.ExecuteReaderAsync(ct);

                var deadlocks = new List<DeadlockInfo>();
                while (await reader.ReadAsync(ct))
                {
                    deadlocks.Add(new DeadlockInfo
                    {
                        DeadlockTime = reader.GetDateTime(0),
                        DeadlockGraph = reader.GetString(1),
                        VictimSessionId = ExtractVictimSessionId(reader.GetString(1))
                    });
                }

                return deadlocks;
            }

            private static int ExtractVictimSessionId(string deadlockGraph)
            {
                // Parse XML deadlock graph for victim session ID
                var match = Regex.Match(deadlockGraph, @"victim=""process(\d+)""");
                return match.Success ? int.Parse(match.Groups[1].Value) : 0;
            }
        }

public class DatabaseHealthCheck : IHealthCheck
        {
            private readonly string _connectionString;
            private readonly ILogger<DatabaseHealthCheck> _logger;

            public DatabaseHealthCheck(string connectionString, ILogger<DatabaseHealthCheck> logger)
            {
                _connectionString = connectionString;
                _logger = logger;
            }

            public async Task<HealthCheckResult> CheckHealthAsync(
                HealthCheckContext context,
                CancellationToken cancellationToken = default)
            {
                var data = new Dictionary<string, object>();

                try
                {
                    using var connection = new SqlConnection(_connectionString);
                    var sw = Stopwatch.StartNew();
                    await connection.OpenAsync(cancellationToken);
                    sw.Stop();

                    data["ConnectionTimeMs"] = sw.ElapsedMilliseconds;
                    data["ServerVersion"] = connection.ServerVersion;

                    // Check database size
                    using var cmd = new SqlCommand(@"
                        SELECT
                            CAST(SUM(size) * 8.0 / 1024 AS DECIMAL(10,2)) AS SizeMB
                        FROM sys.database_files", connection);

                    var sizeMb = await cmd.ExecuteScalarAsync(cancellationToken);
                    data["DatabaseSizeMB"] = sizeMb;

                    // Check active connections
                    using var cmd2 = new SqlCommand(@"
                        SELECT COUNT(*)
                        FROM sys.dm_exec_sessions
                        WHERE database_id = DB_ID()", connection);

                    var activeConnections = await cmd2.ExecuteScalarAsync(cancellationToken);
                    data["ActiveConnections"] = activeConnections;

                    return HealthCheckResult.Healthy(data: data);
                }
                catch (Exception ex)
                {
                    return HealthCheckResult.Unhealthy(
                        "Database health check failed", ex, data);
                }
            }
        }

        // Registration
        builder.Services.AddHealthChecks()
            .AddCheck<DatabaseHealthCheck>("database")
            .AddCheck<ConnectionPoolHealthCheck>("connection-pool");

        // Endpoint
        app.MapHealthChecks("/health", new HealthCheckOptions
        {
            ResponseWriter = async (context, report) =>
            {
                context.Response.ContentType = "application/json";
                var result = JsonSerializer.Serialize(new
                {
                    status = report.Status.ToString(),
                    checks = report.Entries.Select(e => new
                    {
                        name = e.Key,
                        status = e.Value.Status.ToString(),
                        duration = e.Value.Duration.TotalMilliseconds,
                        data = e.Value.Data
                    })
                });
                await context.Response.WriteAsync(result);
            }
        });

-- Full Backup — полный backup
        BACKUP DATABASE MyDb
        TO DISK = 'C:\Backups\MyDb_Full.bak'
        WITH COMPRESSION, STATS = 10;

        -- Differential Backup — изменения с последнего full
        BACKUP DATABASE MyDb
        TO DISK = 'C:\Backups\MyDb_Diff.bak'
        WITH DIFFERENTIAL, COMPRESSION, STATS = 10;

        -- Transaction Log Backup — log records
        BACKUP LOG MyDb
        TO DISK = 'C:\Backups\MyDb_Log.trn'
        WITH COMPRESSION, STATS = 10;

        -- Backup Strategy:
        -- Full: Weekly (Sunday 2 AM)
        -- Differential: Daily (2 AM)
        -- Transaction Log: Every 15 minutes
        --
        -- Recovery:
        -- 1. Restore last Full WITH NORECOVERY
        -- 2. Restore last Differential WITH NORECOVERY
        -- 3. Restore all Transaction Logs in order WITH NORECOVERY
        -- 4. Restore last Transaction Log WITH RECOVERY

-- SQL Server Agent Job для automated backups
        -- Full backup weekly
        EXEC msdb.dbo.sp_add_job @job_name = 'Full Backup';
        EXEC msdb.dbo.sp_add_jobstep
            @job_name = 'Full Backup',
            @step_name = 'Backup',
            @subsystem = 'TSQL',
            @command = 'BACKUP DATABASE MyDb TO DISK = ''C:\Backups\MyDb_Full_$(DATE).bak'' WITH COMPRESSION';

        EXEC msdb.dbo.sp_add_schedule
            @schedule_name = 'Weekly Sunday 2AM',
            @freq_type = 8,  -- Weekly
            @freq_interval = 1,  -- Sunday
            @active_start_time = 020000;  -- 2 AM

RPO (Recovery Point Objective) — максимальная потеря данных
        - RPO = 1 hour: допустимо потерять до 1 часа данных
        - Определяет frequency backups

        RTO (Recovery Time Objective) — максимальное время downtime
        - RTO = 4 hours: система должна быть up в течение 4 часов
        - Определяет restore strategy

        RPO/RTO Matrix:
        | Criticality | RPO    | RTO    | Strategy                    |
        |-------------|--------|--------|-----------------------------|
        | Critical    | 5 min  | 1 hour | Log shipping, Always On     |
        | High        | 1 hour | 4 hours | Transaction log backups    |
        | Medium      | 24h    | 24h    | Daily full backups          |
        | Low         | 1 week | 1 week | Weekly full backups         |

public class DisasterRecoveryPlan
        {
            // 1. Определение criticality
            // - Какие databases critical?
            // - Какие данные можно потерять?
            // - Какое допустимое downtime?

            // 2. Backup strategy
            // - Full backups: frequency, retention
            // - Differential backups: frequency
            // - Transaction log backups: frequency

            // 3. Restore procedure
            // - Documented step-by-step
            // - Tested regularly

            // 4. Testing
            // - Monthly restore test
            // - Quarterly disaster drill
            // - Annual full disaster recovery test

            // 5. Monitoring
            // - Backup success/failure alerts
            // - Storage capacity monitoring
            // - Restore time measurement
        }

        // Automated restore test
        public class RestoreTestService
        {
            public async Task<bool> TestRestoreAsync(string backupPath, string testDbName)
            {
                try
                {
                    // Restore to test database
                    await ExecuteSqlAsync($@"
                        RESTORE DATABASE {testDbName}
                        FROM DISK = '{backupPath}'
                        WITH MOVE 'MyDb' TO 'C:\Data\{testDbName}.mdf',
                             MOVE 'MyDb_log' TO 'C:\Data\{testDbName}_log.ldf',
                             REPLACE, STATS = 10");

                    // Verify
                    var integrity = await ExecuteSqlAsync($"DBCC CHECKDB({testDbName}) WITH NO_INFOMSGS");

                    // Cleanup
                    await ExecuteSqlAsync($"DROP DATABASE {testDbName}");

                    return integrity.Succeeded;
                }
                catch (Exception ex)
                {
                    _logger.LogError(ex, "Restore test failed");
                    return false;
                }
            }
        }

// ПЛОХО — SQL injection vulnerability
        var sql = $"SELECT * FROM Users WHERE Email = '{email}'";
        // Email: ' OR '1'='1 — вернёт всех users!

        // ХОРОШО — параметризированный запрос
        using var cmd = new SqlCommand(
            "SELECT * FROM Users WHERE Email = @Email",
            connection);
        cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 256).Value = email;

        // EF Core — автоматически параметризирует
        var user = await context.Users
            .FirstOrDefaultAsync(u => u.Email == email);

        // Dapper — автоматически параметризирует
        var user = await connection.QueryFirstOrDefaultAsync<User>(
            "SELECT * FROM Users WHERE Email = @Email",
            new { Email = email });

-- Stored procedure с параметрами
        CREATE PROCEDURE GetUserByEmail
            @Email NVARCHAR(256)
        AS
        BEGIN
            SET NOCOUNT ON;
            SELECT Id, Name, Email FROM Users WHERE Email = @Email;
        END

        -- EXECUTE AS для permissions
        CREATE PROCEDURE DeleteUser
            @UserId INT
        WITH EXECUTE AS 'db_executor'
        AS
        BEGIN
            SET NOCOUNT ON;
            DELETE FROM Users WHERE Id = @UserId;
        END

-- 1. Создать security predicate function
        CREATE SECURITY POLICY TenantSecurityPolicy
        ADD FILTER PREDICATE dbo.fn_TenantAccessPredicate(TenantId) ON dbo.Orders,
        ADD BLOCK PREDICATE dbo.fn_TenantAccessPredicate(TenantId) ON dbo.Orders;

        -- Predicate function
        CREATE FUNCTION dbo.fn_TenantAccessPredicate(@TenantId INT)
        RETURNS TABLE
        WITH SCHEMABINDING
        AS
        RETURN SELECT 1 AS Result
        WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS INT);

        -- 2. Установить tenant context в приложении

public class TenantContextMiddleware
        {
            private readonly RequestDelegate _next;
            private readonly string _connectionString;

            public TenantContextMiddleware(RequestDelegate next, string connectionString)
            {
                _next = next;
                _connectionString = connectionString;
            }

            public async Task InvokeAsync(HttpContext context)
            {
                var tenantId = GetTenantIdFromRequest(context);

                // Установить tenant context для RLS
                using var connection = new SqlConnection(_connectionString);
                await connection.OpenAsync();

                using var cmd = new SqlCommand(
                    "EXEC sp_set_session_context @key = N'TenantId', @value = @tenantId",
                    connection);

                cmd.Parameters.Add("@tenantId", SqlDbType.Int).Value = tenantId;
                await cmd.ExecuteNonQueryAsync();

                // Сохранить connection для запросов
                context.Items["SqlConnection"] = connection;

                await _next(context);
            }

            private static int GetTenantIdFromRequest(HttpContext context)
            {
                // Из JWT token, subdomain, header, etc.
                return int.Parse(context.User.FindFirst("tenant_id")?.Value
                    ?? throw new UnauthorizedAccessException());
            }
        }

public class TenantDbContext : DbContext
        {
            private readonly int _tenantId;

            public TenantDbContext(DbContextOptions options, int tenantId)
                : base(options)
            {
                _tenantId = tenantId;
            }

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                // Global query filter для tenant isolation
                modelBuilder.Entity<Order>()
                    .HasQueryFilter(o => o.TenantId == _tenantId);

                modelBuilder.Entity<Product>()
                    .HasQueryFilter(p => p.TenantId == _tenantId);

                // Apply to all ITenantEntity
                foreach (var entityType in modelBuilder.Model.GetEntityTypes())
                {
                    if (typeof(ITenantEntity).IsAssignableFrom(entityType.ClrType))
                    {
                        var parameter = Expression.Parameter(entityType.ClrType, "e");
                        var property = Expression.Property(parameter, nameof(ITenantEntity.TenantId));
                        var tenantId = Expression.Constant(_tenantId);
                        var filter = Expression.Lambda(Expression.Equal(property, tenantId), parameter);

                        modelBuilder.Entity(entityType.ClrType).HasQueryFilter(filter);
                    }
                }
            }
        }

-- Mask sensitive data for non-privileged users
        CREATE TABLE Patients
        (
            Id INT PRIMARY KEY,
            Name NVARCHAR(100),
            Email NVARCHAR(100) MASKED WITH (FUNCTION = 'email()'),
            Phone NVARCHAR(20) MASKED WITH (FUNCTION = 'default()'),
            SSN NVARCHAR(11) MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)'),
            BirthDate DATE MASKED WITH (FUNCTION = 'default()')
        );

        -- Grant unmask permission to specific roles
        GRANT UNMASK TO db_securityadmin;

        -- Results for non-privileged user:
        -- Name: John Doe
        -- Email: JXXX@XXXX.com
        -- Phone: xxxx
        -- SSN: XXX-XX-1234
        -- BirthDate: 1900-01-01

-- Transparent Data Encryption (TDE)
        USE master;
        CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';

        CREATE CERTIFICATE TDECert
        WITH SUBJECT = 'TDE Certificate';

        USE MyDb;
        CREATE DATABASE ENCRYPTION KEY
        WITH ALGORITHM = AES_256
        ENCRYPTION BY SERVER CERTIFICATE TDECert;

        ALTER DATABASE MyDb SET ENCRYPTION ON;

        -- Always Encrypted (column-level)
        -- Client-side encryption — server never sees plaintext

// Connection string с TLS
        var connectionString = @"
            Server=localhost;
            Database=MyDb;
            Trusted_Connection=True;
            Encrypt=True;                    -- Require encryption
            TrustServerCertificate=False;    -- Validate certificate
            HostNameInCertificate=myserver.example.com;
        ";

        // Validate certificate
        public class CertificateValidator
        {
            public static void ValidateCertificate(object sender, X509Certificate certificate,
                X509Chain chain, SslPolicyErrors sslPolicyErrors)
            {
                if (sslPolicyErrors != SslPolicyErrors.None)
                {
                    throw new SecurityException(
                        $"Certificate validation failed: {sslPolicyErrors}");
                }
            }
        }

// Program.cs
        builder.Host.ConfigureAppConfiguration((context, config) =>
        {
            var builtConfig = config.Build();

            var keyVaultUrl = builtConfig["AzureKeyVault:Url"];
            if (!string.IsNullOrEmpty(keyVaultUrl))
            {
                config.AddAzureKeyVault(
                    new Uri(keyVaultUrl),
                    new DefaultAzureCredential());
            }
        });

        // Connection string из Key Vault
        var connectionString = builder.Configuration["DatabaseConnectionString"];

        // Или individual secrets
        var server = builder.Configuration["DbServer"];
        var database = builder.Configuration["DbName"];
        var userId = builder.Configuration["DbUserId"];
        var password = builder.Configuration["DbPassword"];

        var connectionString = new SqlConnectionStringBuilder
        {
            DataSource = server,
            InitialCatalog = database,
            UserID = userId,
            Password = password,
            Encrypt = true,
            TrustServerCertificate = false
        }.ConnectionString;

using VaultSharp;

        public class VaultSecretManager
        {
            private readonly IVaultClient _vaultClient;

            public VaultSecretManager(string vaultAddress, string vaultToken)
            {
                var authMethod = new TokenAuthMethodInfo(vaultToken);
                var vaultClientSettings = new VaultClientSettings(vaultAddress, authMethod);
                _vaultClient = new VaultClient(vaultClientSettings);
            }

            public async Task<string> GetDatabaseConnectionStringAsync()
            {
                var secret = await _vaultClient.V1.Secrets.KeyValue.V2
                    .ReadSecretAsync("database/connection-string");

                return secret.Data.Data["value"].ToString()!;
            }

            public async Task<Dictionary<string, string>> GetDatabaseCredentialsAsync()
            {
                var secret = await _vaultClient.V1.Secrets.KeyValue.V2
                    .ReadSecretAsync("database/credentials");

                return secret.Data.Data.ToDictionary(
                    kvp => kvp.Key,
                    kvp => kvp.Value.ToString()!);
            }
        }

        // Dynamic credentials (Vault generates temporary DB credentials)
        public async Task<SqlConnection> GetConnectionWithDynamicCredentialsAsync()
        {
            var creds = await _vaultClient.V1.Secrets.Database
                .GetCredentialsAsync("my-db-role");

            var connectionString = new SqlConnectionStringBuilder
            {
                DataSource = _server,
                InitialCatalog = _database,
                UserID = creds.Data.Username,
                Password = creds.Data.Password,
                Encrypt = true
            }.ConnectionString;

            return new SqlConnection(connectionString);
        }

// Azure SQL с Managed Identity — no passwords needed
        var credential = new DefaultAzureCredential();
        var accessToken = await credential.GetTokenAsync(
            new TokenRequestContext(new[] { "https://database.windows.net/.default" }));

        var connectionString = new SqlConnectionStringBuilder
        {
            DataSource = "myserver.database.windows.net",
            InitialCatalog = "mydb",
            Encrypt = true,
            TrustServerCertificate = false
        }.ConnectionString;

        using var connection = new SqlConnection(connectionString);
        connection.AccessToken = accessToken.Token;
        await connection.OpenAsync();

public class SecurityChecklist
        {
            // 1. SQL Injection Prevention
            // [ ] Все queries параметризированы
            // [ ] No string concatenation в SQL
            // [ ] ORM используется (EF Core, Dapper)
            // [ ] Stored procedures для complex operations

            // 2. Authentication & Authorization
            // [ ] Managed Identity или Key Vault для credentials
            // [ ] No hardcoded passwords в code/config
            // [ ] Least privilege principle для DB users
            // [ ] Separate users for read/write operations

            // 3. Encryption
            // [ ] TLS для всех connections (Encrypt=True)
            // [ ] Certificate validation (TrustServerCertificate=False)
            // [ ] TDE для encryption at rest
            // [ ] Always Encrypted для sensitive columns

            // 4. Data Protection
            // [ ] Row-Level Security для multi-tenant
            // [ ] Dynamic Data Masking для sensitive data
            // [ ] Audit logging для data access
            // [ ] Data retention policies

            // 5. Network Security
            // [ ] Private endpoints для Azure SQL
            // [ ] Firewall rules ограничены
            // [ ] VNet integration для Azure services
            // [ ] No public access к database

            // 6. Monitoring & Alerting
            // [ ] SQL audit logging enabled
            // [ ] Failed login attempts monitored
            // [ ] Unusual query patterns detected
            // [ ] Data exfiltration alerts
        }