Datenbanken & ORM

@Entity(table: "users")
struct User {
    @Id
    @Generated  // Auto-Increment oder UUID
    id: string,

    @Column(unique: true)
    email: string,
    
    name: string,
    
    @Column(name: "created_at")
    createdAt: string,
    
    @Column(default: true)
    isActive: boolean,
}

@Entity(table: "orders")
struct Order {
    @Id
    @Generated
    id: string,
    
    @Column(name: "user_id")
    @ForeignKey(User, "id")
    userId: string,
    
    total: number,
    status: string,
    createdAt: string,
}

// Filter und Sortierung
fn findActiveUsers(): List<User> {
    return db.query(User)
        .where("isActive", "=", true)
        .where("role", "!=", "banned")
        .orderBy("createdAt", "DESC")
        .limit(10)
        .findAll();
}

// Mehrere Bedingungen
fn searchUsers(term: string, minAge: number): List<User> {
    return db.query(User)
        .where("name", "LIKE", "%" + term + "%")
        .where("age", ">=", minAge)
        .where("isActive", "=", true)
        .orderBy("name", "ASC")
        .findAll();
}

// Pagination
@GET("/api/users")
fn getUsersPaginated(page: number = 1, limit: number = 20): List<User> {
    let offset = (page - 1) * limit;
    
    return db.query(User)
        .orderBy("createdAt", "DESC")
        .limit(limit)
        .offset(offset)
        .findAll();
}

// Einfacher Join
fn getUsersWithOrders(): List<UserWithOrders> {
    return db.query(User)
        .join(Order, "userId")
        .select(["User.*", "COUNT(Order.id) as orderCount"])
        .groupBy("User.id")
        .findAll();
}

// Eager Loading (N+1 Problem vermeiden)
@GET("/api/orders")
fn getOrdersWithDetails(): List<Order> {
    return db.query(Order)
        .include("user")           // User mitladen
        .include("items.product")  // Items und deren Products
        .findAll();
}

// Left Join
fn getUsersWithOptionalOrders(): List<UserWithOrders> {
    return db.query(User)
        .leftJoin(Order, "userId")
        .findAll();
}

fn transferMoney(fromId: string, toId: string, amount: number): Result<void, Error> {
    try {
        db.transaction(|tx| {
            // Alle Operationen auf 'tx' statt 'db' ausführen
            let sender = tx.find(Account, fromId);
            let receiver = tx.find(Account, toId);
            
            if (sender.balance < amount) {
                throw Error("Nicht genug Guthaben");
            }
            
            sender.balance -= amount;
            receiver.balance += amount;
            
            tx.save(sender);
            tx.save(receiver);
            
            // Audit Log
            tx.save(Transaction {
                id: utils.uuid(),
                fromAccount: fromId,
                toAccount: toId,
                amount: amount,
                createdAt: datetime.now(),
            });
        });
        
        return Ok(());
    } catch (e) {
        log.error("Transaktion fehlgeschlagen, Rollback: " + e.message);
        return Err(e);
    }
}