WoW Hardcore Community Event

┌─────────────────────────────────────────────────────────────────────┐
│                       EXTERNAL APIs (OAuth 2.0)                     │
├─────────────────┬─────────────────────┬─────────────────────────────┤
│  Discord API    │    Twitch Helix     │  Blizzard Battle.net API    │
│  - Gateway      │    - /users         │  - Character Profiles       │
│  - REST API     │    - /streams       │  - Guild Roster             │
│  - Webhooks     │    - /clips         │  - German Locale (de_DE)    │
└────────┬────────┴──────────┬──────────┴──────────┬──────────────────┘
         │                   │                     │
         ▼                   ▼                     ▼
┌─────────────────────────────────────────────────────────────────────┐
│                         BACKEND (NestJS)                            │
├─────────────────────────────────────────────────────────────────────┤
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────────────────┐  │
│  │ Discord Bot  │  │ Twitch Svc   │  │ Blizzard Service         │  │
│  │ - 8 Commands │  │ - OAuth 2.0  │  │ - Character Sync         │  │
│  │ - Roles      │  │ - Batch API  │  │ - Death Detection        │  │
│  └──────┬───────┘  └──────┬───────┘  └──────┬───────────────────┘  │
│         │                 │                  │                      │
│         └─────────────────┼──────────────────┘                      │
│                           ▼                                         │
│  ┌────────────────────────────────────────────────────────────┐    │
│  │             Business Logic Layer                           │    │
│  │  - UsersService (CRUD, Linking, Stats)                     │    │
│  │  - DeathsService (Clip Validation, Recording)              │    │
│  │  - ClipsService (Twitch Clip Verification)                 │    │
│  │  - WowSyncService (Character Sync, Auto-Death-Detection)   │    │
│  │  - LiveStatusService (5min Polling)                        │    │
│  └──────────────────────────┬─────────────────────────────────┘    │
│                             ▼                                       │
│  ┌────────────────────────────────────────────────────────────┐    │
│  │            TypeORM (ORM Layer)                             │    │
│  │  - Entities: User, Death, WowCharacter, Archived, Guild    │    │
│  │  - Migrations (11 versions)                                │    │
│  │  - Relations & Constraints                                 │    │
│  └──────────────────────────┬─────────────────────────────────┘    │
│                             ▼                                       │
│  ┌────────────────────────────────────────────────────────────┐    │
│  │            MySQL Database (Persistence Layer)              │    │
│  │  - 5 Main Tables (User, Death, WowCharacter, etc.)         │    │
│  │  - Indexed Queries für Performance                         │    │
│  │  - Foreign Keys & Constraints                              │    │
│  └────────────────────────────────────────────────────────────┘    │
│                             ▲                                       │
│                             │ (SQL Queries)                         │
│                             │                                       │
│  ┌────────────────────────────────────────────────────────────┐    │
│  │            Cache Manager (TTL: 30s)                        │    │
│  │  - Page Cache (user_stats_page_X)                          │    │
│  │  - Total Count Cache                                       │    │
│  │  - API Token Cache (Twitch/Blizzard)                       │    │
│  └────────────────────────────────────────────────────────────┘    │
│                                                                     │
│  ┌────────────────────────────────────────────────────────────┐    │
│  │            REST API Endpoints (HTTP Server)                │    │
│  │  - GET  /api/users/stats (Pagination, Sorting)             │    │
│  │  - GET  /api/users/:username/deaths                        │    │
│  │  - GET  /api/users/recent-deaths                           │    │
│  │  - GET  /api/users/:username/wow-character                 │    │
│  │  - GET  /api/proxy/image (Avatar Proxy)                    │    │
│  └──────────────────────────┬─────────────────────────────────┘    │
└─────────────────────────────┼─────────────────────────────────────┘
                              │
                              │ (REST API - HTTP/HTTPS)
                              ▼
┌─────────────────────────────────────────────────────────────────────┐
│                      FRONTEND (Next.js 16)                          │
├─────────────────────────────────────────────────────────────────────┤
│  ┌────────────────────────────────────────────────────────────┐    │
│  │              Pages (App Router)                            │    │
│  │  - / (Main Dashboard)                                      │    │
│  │  - /guild (Guild Roster)                                   │    │
│  │  - /obs/* (OBS Overlays)                                   │    │
│  └──────────────────────────┬─────────────────────────────────┘    │
│                             ▼                                       │
│  ┌────────────────────────────────────────────────────────────┐    │
│  │           React Components (15 Components)                 │    │
│  │  - DeathTable (Tabs, Sorting, Infinite Scroll)             │    │
│  │  - ClipModal (Twitch Embed mit GDPR Consent)               │    │
│  │  - EventCountdown, Snowfall, LiveStream                    │    │
│  └──────────────────────────┬─────────────────────────────────┘    │
│                             ▼                                       │
│  ┌────────────────────────────────────────────────────────────┐    │
│  │         Custom Hooks & State Management                    │    │
│  │  - useDeathStats() (Multi-Page State Map)                  │    │
│  │  - useGuildMembers()                                       │    │
│  │  - ConsentContext (GDPR)                                   │    │
│  └──────────────────────────┬─────────────────────────────────┘    │
│                             ▼                                       │
│  ┌────────────────────────────────────────────────────────────┐    │
│  │              API Client (lib/api.ts)                       │    │
│  │  - Axios/Fetch für Backend-Kommunikation                   │    │
│  │  - Error Handling & Retries                                │    │
│  │  - Response Parsing & Validation                           │    │
│  └──────────────────────────┬─────────────────────────────────┘    │
│                             │                                       │
└─────────────────────────────┼─────────────────────────────────────┘
                              │
                              ▼
                    ┌──────────────────────┐
                    │   End Users          │
                    │   - Desktop Browser  │
                    │   - Mobile Browser   │
                    │   - Discord Client   │
                    └──────────────────────┘

1. User: /link @DiscordUser TwitchUsername
   ↓
2. Discord Gateway → DiscordService empfängt Interaction
   ↓
3. Role-Guard prüft Admin-Berechtigung
   ↓
4. TwitchService.validateUsername() → Twitch API Call
   ↓
5. UsersService.linkUser() → Duplikat-Check in DB
   ↓
6. TypeORM speichert neuen User-Record
   ↓
7. Discord-Reply: "User erfolgreich verlinkt!"
   ↓
8. Frontend: Auto-Refresh nach 60s zeigt neuen User

Cron-Job (alle 5 Minuten):

1. LiveStatusService.updateLiveStatus()
   ↓
2. TwitchService.getAllLiveStreams() (alle Games)
   │ → Batch-Request für alle User (max 100 pro Request)
   ↓
3. TwitchService.getStreamDetails() (nur WoW-Streams)
   │ → Filter: game_name = "World of Warcraft"
   ↓
4. Für jeden User:
   │
   ├─ IST in WoW-Streams?
   │  ├─ JA:
   │  │  └─ DiscordService.assignLiveRole(user)
   │  │     DB: is_live=true, viewer_count=X, last_live_at=NOW()
   │  │
   │  └─ NEIN:
   │     └─ DiscordService.removeLiveRole(user)
   │        DB: is_live=false, viewer_count=null
   ↓
5. Cache-Invalidierung: user_stats_* Keys gelöscht
   ↓
6. Frontend: Nächster Auto-Refresh zeigt aktuellen Live-Status

Cron-Job (alle 10 Minuten):

1. WowSyncService.syncCharacters()
   ↓
2. User mit character_name abrufen
   ↓
3. Für jeden Character:
   │
   ├─ BlizzardService.getCharacterProfile(realm, name)
   │  │ → OAuth Token aus Cache (oder neu holen)
   │  │ → Rate-Limit: 1 Sekunde Delay zwischen Calls
   │  │ → API: /profile/wow/character/{realm}/{name}?locale=de_DE
   │  ↓
   ├─ Blizzard Character ID aus Response extrahieren
   │  ↓
   ├─ Vergleich mit DB Character ID:
   │  │
   │  ├─ IDs sind UNTERSCHIEDLICH?
   │  │  │ → CHARACTER IST TOT! (Neuer Character mit gleichem Namen)
   │  │  │
   │  │  ├─ ArchivedWowCharacter erstellen
   │  │  │  (archive_reason: 'auto_detected_death')
   │  │  │
   │  │  ├─ WowCharacter.is_alive = false
   │  │  │
   │  │  ├─ Discord Admin-Alert senden:
   │  │  │  "⚠️ Auto-Death erkannt: {character_name} ({username})"
   │  │  │
   │  │  └─ STOPPE Sync für diesen Character
   │  │     (User muss /tod-Command verwenden)
   │  │
   │  └─ IDs sind GLEICH (oder erstes Mal)?
   │     │ → Character lebt noch
   │     │
   │     └─ Update Character-Daten:
   │        - level, character_class, race, faction
   │        - is_alive (basierend auf is_ghost Flag)
   │        - last_synced_at = NOW()
   │        - blizzard_character_id speichern
   ↓
4. Frontend: Character-Modal zeigt aktuelles Level/Class

@Entity('users')
export class User {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ unique: true, length: 20 })
  @Index()
  discord_id: string;

  @Column({ unique: true, length: 50 })
  @Index()
  twitch_username: string;

  @Column({ default: false })
  banned: boolean;

  @Column({ default: false })
  is_live: boolean;

  @Column({ nullable: true })
  viewer_count: number;

  @Column({ length: 50, nullable: true })
  profession1: string;

  @Column({ length: 50, nullable: true })
  profession2: string;

  @Column({ length: 12, nullable: true })
  character_name: string;  // IMMUTABLE nach erstem Set

  @Column({ default: 0 })
  death_count: number;

  @Column({ type: 'text', nullable: true })
  ban_reason: string;

  @Column({ nullable: true })
  banned_at: Date;

  @Column({ length: 100, nullable: true })
  banned_by: string;

  @Column({ nullable: true })
  @Index()
  last_live_at: Date;  // Für Inactivity-Check

  @CreateDateColumn()
  linked_at: Date;

  @OneToMany(() => Death, death => death.user, { cascade: true })
  deaths: Death[];
}

@Entity('deaths')
export class Death {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column('uuid')
  @Index()
  user_id: string;

  @Column({ length: 500 })
  twitch_clip_url: string;

  @Column({ length: 255, nullable: true })
  clip_title: string;  // Von Twitch API

  @Column()
  level: number;  // 1-60 für WoW Classic

  @CreateDateColumn()
  @Index()
  recorded_at: Date;

  @ManyToOne(() => User, user => user.deaths, {
    onDelete: 'CASCADE'
  })
  user: User;
}

@Entity('wow_characters')
export class WowCharacter {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column('uuid', { unique: true })
  @Index()
  user_id: string;

  @Column({ length: 50 })
  character_name: string;

  @Column({ type: 'bigint', nullable: true })
  blizzard_character_id: number;  // KRITISCH für Death-Detection!

  @Column({ length: 50, default: 'soulseeker' })
  realm: string;

  @Column()
  level: number;

  @Column({ length: 50 })
  character_class: string;  // Deutsche Namen (Krieger, Magier, ...)

  @Column({ length: 50 })
  race: string;

  @Column({ length: 20 })
  faction: string;  // 'Alliance' oder 'Horde'

  @Column({ default: true })
  is_alive: boolean;

  @Column({ nullable: true })
  last_synced_at: Date;

  @OneToOne(() => User)
  @JoinColumn({ name: 'user_id' })
  user: User;
}

@Entity('archived_wow_characters')
export class ArchivedWowCharacter {
  // ... alle Felder von WowCharacter ...

  @Column({
    type: 'enum',
    enum: ['death', 'reset', 'auto_detected_death']
  })
  archive_reason: string;

  @Column({ default: true })
  was_alive_at_archive: boolean;

  @Column({ length: 100, nullable: true })
  archived_by: string;  // Admin-Name bei manuellem Reset

  @CreateDateColumn()
  archived_at: Date;
}

@Entity('guild_members')
export class GuildMember {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column({ length: 50 })
  @Index()
  character_name: string;

  @Column()
  level: number;

  @Column({ length: 50 })
  character_class: string;

  @Column({ length: 50 })
  race: string;

  @Column()
  rank: number;  // Guild Rank (0 = Guildmaster)

  @Column({ nullable: true })
  last_synced_at: Date;
}

export class TwitchService {
  async getAccessToken(): Promise<string> {
    // Token-Caching: Nur neu holen wenn abgelaufen
    if (this.cachedToken && this.tokenExpiresAt > Date.now() + 60000) {
      return this.cachedToken;
    }

    const response = await axios.post('https://id.twitch.tv/oauth2/token', {
      client_id: process.env.TWITCH_CLIENT_ID,
      client_secret: process.env.TWITCH_CLIENT_SECRET,
      grant_type: 'client_credentials'
    });

    this.cachedToken = response.data.access_token;
    this.tokenExpiresAt = Date.now() + (response.data.expires_in * 1000);
    return this.cachedToken;
  }

  async getAllLiveStreams(usernames: string[]): Promise<StreamInfo[]> {
    // Batch-Processing: Max 100 User pro Request
    const batches = this.chunkArray(usernames, 100);
    const allStreams = [];

    for (const batch of batches) {
      const params = batch.map(u => `user_login=${u.toLowerCase()}`).join('&');
      const response = await axios.get(
        `https://api.twitch.tv/helix/streams?${params}`,
        { headers: this.getHeaders() }
      );
      allStreams.push(...response.data.data);
    }

    return allStreams;
  }

  async getStreamDetails(usernames: string[]): Promise<LiveStream[]> {
    const allStreams = await this.getAllLiveStreams(usernames);

    // Filter: Nur "World of Warcraft" Streams
    return allStreams
      .filter(stream => stream.game_name === 'World of Warcraft')
      .map(stream => ({
        username: stream.user_login,
        viewerCount: stream.viewer_count,
        title: stream.title
      }));
  }
}

export class BlizzardService {
  async getCharacterProfile(realm: string, name: string) {
    const token = await this.getAccessToken();

    // Rate-Limiting: 1 Sekunde Delay zwischen Calls
    await this.delay(1000);

    try {
      const response = await axios.get(
        `https://eu.api.blizzard.com/profile/wow/character/${realm}/${name}`,
        {
          params: {
            namespace: 'profile-classic1x-eu',  // WoW Classic Hardcore
            locale: 'de_DE'  // Deutsche Klassennamen
          },
          headers: {
            'Authorization': `Bearer ${token}`
          }
        }
      );

      return {
        id: response.data.id,  // KRITISCH: Eindeutige Character-ID
        name: response.data.name,
        level: response.data.level,
        character_class: this.mapClassName(response.data.character_class.id),
        race: response.data.race.name,
        faction: response.data.faction.type,
        is_ghost: response.data.is_ghost  // Tod-Indikator
      };
    } catch (error) {
      if (error.response?.status === 404) {
        // Character nicht gefunden (gelöscht oder Server-Wechsel)
        return null;
      }
      throw error;
    }
  }

  mapClassName(classId: number): string {
    const CLASS_MAP = {
      1: 'Krieger', 2: 'Paladin', 3: 'Jäger', 4: 'Schurke',
      5: 'Priester', 7: 'Schamane', 8: 'Magier',
      9: 'Hexenmeister', 11: 'Druide'
    };
    return CLASS_MAP[classId] || 'Unbekannt';
  }
}

@Injectable()
export class DiscordService {
  private client: Client;

  async onModuleInit() {
    this.client = new Client({
      intents: [
        GatewayIntentBits.Guilds,
        GatewayIntentBits.GuildMembers
      ]
    });

    this.client.on('ready', () => {
      console.log(`Bot logged in as ${this.client.user.tag}`);
    });

    // Event: User verlässt Discord → Auto-Ban
    this.client.on('guildMemberRemove', async (member) => {
      const user = await this.usersService.findByDiscordId(member.id);
      if (user && !user.banned) {
        await this.usersService.banUser(
          user.twitch_username,
          'Discord leave',
          'System'
        );
      }
    });

    this.client.on('interactionCreate', async (interaction) => {
      if (!interaction.isChatInputCommand()) return;
      await this.handleCommand(interaction);
    });

    await this.client.login(process.env.DISCORD_BOT_TOKEN);
  }

  async assignLiveRole(userId: string) {
    const guild = this.client.guilds.cache.first();
    const member = await guild.members.fetch(userId);
    const liveRole = guild.roles.cache.find(r => r.name === 'Live');

    if (!member.roles.cache.has(liveRole.id)) {
      await member.roles.add(liveRole);
    }
  }

  async removeLiveRole(userId: string) {
    const guild = this.client.guilds.cache.first();
    const member = await guild.members.fetch(userId);
    const liveRole = guild.roles.cache.find(r => r.name === 'Live');

    if (member.roles.cache.has(liveRole.id)) {
      await member.roles.remove(liveRole);
    }
  }
}

export class RetryService {
  async withRetry<T>(
    operation: () => Promise<T>,
    maxRetries = 3,
    baseDelay = 1000
  ): Promise<T> {
    let lastError: Error;

    for (let attempt = 0; attempt <= maxRetries; attempt++) {
      try {
        return await operation();
      } catch (error) {
        lastError = error;

        // Nicht-retrybare Fehler sofort werfen (404, 401, etc.)
        if (error.response?.status < 500) {
          throw error;
        }

        if (attempt < maxRetries) {
          // Exponential Backoff: 1s, 2s, 4s, 8s
          const delay = baseDelay * Math.pow(2, attempt);

          // Jitter: ±20% Randomisierung (verhindert Thundering Herd)
          const jitter = delay * 0.2 * (Math.random() - 0.5);

          await this.sleep(delay + jitter);
        }
      }
    }

    throw new Error(`Operation failed after ${maxRetries} retries: ${lastError.message}`);
  }

  private sleep(ms: number): Promise<void> {
    return new Promise(resolve => setTimeout(resolve, ms));
  }
}

export class CircuitBreaker {
  private failures = 0;
  private lastFailTime: Date;
  private state: 'CLOSED' | 'OPEN' | 'HALF_OPEN' = 'CLOSED';

  private readonly FAILURE_THRESHOLD = 5;
  private readonly RESET_TIMEOUT = 60000;  // 60 Sekunden

  async execute<T>(operation: () => Promise<T>): Promise<T> {
    if (this.state === 'OPEN') {
      const timeSinceLastFail = Date.now() - this.lastFailTime.getTime();

      if (timeSinceLastFail < this.RESET_TIMEOUT) {
        throw new Error('Circuit breaker is OPEN');
      }

      // Nach Timeout: Versuche einen Test-Request (HALF_OPEN)
      this.state = 'HALF_OPEN';
    }

    try {
      const result = await operation();
      this.onSuccess();
      return result;
    } catch (error) {
      this.onFailure();
      throw error;
    }
  }

  private onSuccess() {
    this.failures = 0;
    this.state = 'CLOSED';
  }

  private onFailure() {
    this.failures++;
    this.lastFailTime = new Date();

    if (this.failures >= this.FAILURE_THRESHOLD) {
      this.state = 'OPEN';
      console.error(`Circuit breaker opened after ${this.failures} failures`);
    }
  }
}

async recordDeath(
  userId: number,
  clipUrl: string,
  level: number
): Promise<Death> {
  const queryRunner = this.dataSource.createQueryRunner();
  await queryRunner.connect();
  await queryRunner.startTransaction();

  try {
    // 1. Death-Eintrag erstellen
    const death = queryRunner.manager.create(Death, {
      user_id: userId,
      clip_url: clipUrl,
      level: level,
      created_at: new Date()
    });
    await queryRunner.manager.save(death);

    // 2. User-Stats aktualisieren
    await queryRunner.manager.update(User, userId, {
      death_count: () => 'death_count + 1',
      last_death_at: new Date()
    });

    // 3. Rank-Berechnung neu durchführen (SQL-basiert)
    await queryRunner.manager.query(`
      UPDATE users u
      SET ranking = (
        SELECT COUNT(*) + 1
        FROM users u2
        WHERE u2.total_level > u.total_level
          AND u2.banned = false
      )
      WHERE u.id = ?
    `, [userId]);

    // 4. Externe API-Calls (nicht transaktional, aber kompensierbar)
    try {
      await this.discordService.sendDeathNotification(userId, clipUrl, level);
    } catch (error) {
      // Discord-Fehler soll Death nicht verhindern (Best-Effort)
      console.error('Discord notification failed:', error);
    }

    // COMMIT: Alles erfolgreich
    await queryRunner.commitTransaction();
    return death;

  } catch (error) {
    // ROLLBACK: Bei Fehler alles rückgängig machen
    await queryRunner.rollbackTransaction();
    throw new Error(`Death recording failed: ${error.message}`);

  } finally {
    await queryRunner.release();
  }
}

// ❌ SCHLECHT: N+1 Problem
async getLeaderboard() {
  const users = await this.userRepo.find();  // 1 Query

  for (const user of users) {
    user.characters = await this.wowCharRepo.find({
      where: { user_id: user.id }
    });  // N Queries!
  }
  return users;
}

// ✅ GUT: Eager Loading
async getLeaderboard() {
  return this.userRepo.find({
    relations: ['wowCharacters', 'deaths'],  // 1 Query mit JOINs
    where: { banned: false },
    order: { total_level: 'DESC' }
  });
}

@Entity('users')
@Index('idx_twitch_username', ['twitch_username'])
@Index('idx_discord_id', ['discord_id'])
@Index('idx_total_level_banned', ['total_level', 'banned'])  // Composite für Leaderboard
export class User {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  twitch_username: string;

  @Column()
  total_level: number;

  @Column({ default: false })
  banned: boolean;
}

@Entity('deaths')
@Index('idx_user_created', ['user_id', 'created_at'])  // Für "letzte 10 Deaths"
export class Death {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  user_id: number;

  @Column()
  created_at: Date;
}

TypeOrmModule.forRoot({
  type: 'mysql',
  host: process.env.DB_HOST,
  port: 3306,
  username: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  entities: [User, Death, WowCharacter, GuildInvite],

  // Connection Pool Settings
  extra: {
    connectionLimit: 10,        // Max 10 gleichzeitige Connections
    waitForConnections: true,   // Queue bei Limit-Erreichen
    queueLimit: 0,              // Unbegrenzte Queue
    acquireTimeout: 10000,      // 10s Timeout für Connection-Acquisition
    connectTimeout: 10000       // 10s Timeout für neue Connections
  },

  // Logging nur in Development
  logging: process.env.NODE_ENV === 'development',
  synchronize: false  // Migrations statt Auto-Sync in Production
})

@Injectable()
export class LiveStatusService {
  @Cron('*/5 * * * *')  // Alle 5 Minuten
  async updateLiveStatus() {
    const users = await this.usersService.findAll();
    const usernames = users.map(u => u.twitch_username);

    // Alle Live-Streams (jedes Game)
    const allLiveStreams = await this.twitchService.getAllLiveStreams(usernames);

    // Nur WoW-Streams
    const wowLiveStreams = await this.twitchService.getStreamDetails(usernames);

    for (const user of users) {
      if (user.banned) continue;  // Skip banned users (verhindert 404)

      const isLiveAnywhere = allLiveStreams.some(
        s => s.user_login.toLowerCase() === user.twitch_username.toLowerCase()
      );

      const isLiveWoW = wowLiveStreams.find(
        s => s.username.toLowerCase() === user.twitch_username.toLowerCase()
      );

      if (isLiveWoW) {
        // User streamt WoW → Live-Role zuweisen
        await this.discordService.assignLiveRole(user.discord_id);
        await this.usersService.update(user.id, {
          is_live: true,
          viewer_count: isLiveWoW.viewerCount,
          last_live_at: new Date()
        });
      } else {
        // User streamt nicht WoW → Live-Role entfernen
        await this.discordService.removeLiveRole(user.discord_id);
        await this.usersService.update(user.id, {
          is_live: false,
          viewer_count: null
        });
      }
    }
  }
}

@Injectable()
export class WowSyncService {
  @Cron('*/10 * * * *')  // Alle 10 Minuten
  async syncCharacters() {
    const users = await this.usersService.findUsersWithCharacters();

    for (const user of users) {
      try {
        // 1s Delay zwischen API-Calls (Rate-Limiting)
        await this.delay(1000);

        const profile = await this.blizzardService.getCharacterProfile(
          'soulseeker',
          user.character_name
        );

        if (!profile) {
          // Character nicht gefunden (404)
          continue;
        }

        const existingChar = await this.wowCharacterRepo.findOne({
          where: { user_id: user.id }
        });

        if (existingChar) {
          // Character existiert bereits → ID-Check
          if (existingChar.blizzard_character_id !== profile.id) {
            // ID hat sich geändert → ALTER CHARACTER IST TOT!
            await this.archiveCharacter(existingChar, 'auto_detected_death');

            // Discord-Alert an Admin-Channel
            await this.discordService.sendAdminAlert(
              `⚠️ Auto-Death erkannt: ${user.character_name} (${user.twitch_username})`
            );

            // STOPPE - neuer Character wird NICHT gesynced
            continue;
          }

          // ID ist gleich → Update Character-Daten
          await this.wowCharacterRepo.update(existingChar.id, {
            level: profile.level,
            character_class: profile.character_class,
            race: profile.race,
            faction: profile.faction,
            is_alive: !profile.is_ghost,
            last_synced_at: new Date()
          });
        } else {
          // Erstes Mal - Character erstellen
          await this.wowCharacterRepo.save({
            user_id: user.id,
            character_name: profile.name,
            blizzard_character_id: profile.id,
            realm: 'soulseeker',
            level: profile.level,
            character_class: profile.character_class,
            race: profile.race,
            faction: profile.faction,
            is_alive: !profile.is_ghost,
            last_synced_at: new Date()
          });
        }
      } catch (error) {
        console.error(`Sync failed for ${user.character_name}:`, error);
      }
    }
  }
}

@Injectable()
export class GuildSyncService {
  @Cron('0 * * * *')  // Jede volle Stunde
  async syncGuildRoster() {
    const roster = await this.blizzardService.getGuildRoster(
      'soulseeker',
      'SourKraut Reserve'
    );

    // Bestehende Member IDs
    const existingMembers = await this.guildMemberRepo.find();
    const existingNames = new Set(existingMembers.map(m => m.character_name));

    // Neue/Aktualisierte Members
    for (const member of roster.members) {
      await this.guildMemberRepo.save({
        character_name: member.character.name,
        level: member.character.level,
        character_class: this.mapClassName(member.character.playable_class.id),
        race: member.character.playable_race.name,
        rank: member.rank,
        last_synced_at: new Date()
      });
      existingNames.delete(member.character.name);
    }

    // Gelöschte Members (nicht mehr in Guild)
    for (const removedName of existingNames) {
      await this.guildMemberRepo.delete({ character_name: removedName });
    }
  }
}

@Injectable()
export class InactivityCheckService {
  @Cron('0 2 * * *', { timeZone: 'Europe/Berlin' })  // 2 Uhr morgens
  async checkInactiveUsers() {
    const activationDate = new Date('2025-12-28T02:00:00+01:00');
    if (new Date() < activationDate) {
      return;  // Job noch nicht aktiviert
    }

    const cutoffDate = new Date();
    cutoffDate.setDate(cutoffDate.getDate() - 5);  // 5 Tage zurück

    const inactiveUsers = await this.userRepo.find({
      where: {
        banned: false,
        last_live_at: LessThan(cutoffDate)
      }
    });

    for (const user of inactiveUsers) {
      await this.usersService.banUser(
        user.twitch_username,
        'Inaktivität (5+ Tage nicht live)',
        'System'
      );

      await this.discordService.removeLiveRole(user.discord_id);

      // Notification an User
      await this.discordService.sendDM(
        user.discord_id,
        'Du wurdest wegen Inaktivität gebannt (5+ Tage nicht live).'
      );
    }
  }
}

@Injectable()
export class UsersService {
  async getUserStats(page: number, limit: number, sortBy?: string) {
    const cacheKey = `user_stats_page_${page}_limit_${limit}_sort_${sortBy}`;

    // 1. Cache-Check
    const cached = await this.cacheManager.get(cacheKey);
    if (cached) return cached;

    // 2. DB-Query mit Pagination
    const [users, total] = await this.userRepo.findAndCount({
      skip: (page - 1) * limit,
      take: limit,
      order: this.buildSortOrder(sortBy)
    });

    // 3. Enrich mit Twitch-Daten (24h Cache)
    for (const user of users) {
      user.profileImageUrl = await this.twitchService.getProfileImage(
        user.twitch_username
      );
    }

    const result = { users, total, page, limit };

    // 4. Cache speichern (TTL: 30s)
    await this.cacheManager.set(cacheKey, result, 30000);

    return result;
  }

  async invalidateCache() {
    // Alle user_stats_* Keys löschen
    const keys = await this.cacheManager.store.keys();
    const userStatsKeys = keys.filter(k => k.startsWith('user_stats_'));

    for (const key of userStatsKeys) {
      await this.cacheManager.del(key);
    }
  }
}

src/app/
├── layout.tsx          # Root Layout (Metadaten, Providers)
├── page.tsx            # Haupt-Dashboard (Death-Table + Stats)
├── not-found.tsx       # 404-Seite
├── guild/
│   └── page.tsx        # Guild-Roster-Tabelle
├── giveaway/
│   └── page.tsx        # Giveaway-Funktionalität
└── obs/
    ├── page.tsx        # OBS Overlay (ohne Cookie-Banner)
    └── death/
        └── page.tsx    # OBS Death Overlay

export default function DeathTable() {
  const [activeTab, setActiveTab] = useState<'teilnehmer' | 'deaths'>('teilnehmer');
  const [recentDeaths, setRecentDeaths] = useState<Death[]>([]);
  const [selectedUsername, setSelectedUsername] = useState<string | null>(null);

  // Custom Hook für Multi-Page State
  const {
    allStats,        // Vereinigte Liste aller Seiten
    totalCount,
    currentPage,
    hasMore,
    isLoading,
    isLoadingMore,
    sortBy,
    sortOrder,
    loadNextPage,    // Infinite Scroll
    handleSort       // Sortierung ändern
  } = useDeathStats();

  // Intersection Observer für Infinite Scroll
  const observerRef = useRef<IntersectionObserver>();
  const lastElementRef = useCallback((node: HTMLElement) => {
    if (isLoadingMore) return;
    if (observerRef.current) observerRef.current.disconnect();

    observerRef.current = new IntersectionObserver(entries => {
      if (entries[0].isIntersecting && hasMore) {
        loadNextPage();
      }
    }, { rootMargin: '100px' });

    if (node) observerRef.current.observe(node);
  }, [isLoadingMore, hasMore]);

  return (
    <>
      {/* Tabs */}
      <div className="tabs">
        <button
          onClick={() => setActiveTab('teilnehmer')}
          className={activeTab === 'teilnehmer' ? 'active' : ''}
        >
          Teilnehmer ({totalCount})
        </button>
        <button
          onClick={() => setActiveTab('deaths')}
          className={activeTab === 'deaths' ? 'active' : ''}
        >
          Letzte Tode ({recentDeaths.length})
        </button>
      </div>

      {activeTab === 'teilnehmer' ? (
        <>
          {/* Desktop: Tabelle */}
          <div className="hidden md:block">
            <table>
              <thead>
                <tr>
                  <th onClick={() => handleSort('streamer')}>
                    Streamer {sortBy === 'streamer' && (sortOrder === 'asc' ? '▲' : '▼')}
                  </th>
                  <th onClick={() => handleSort('level')}>
                    Level {sortBy === 'level' && (sortOrder === 'asc' ? '▲' : '▼')}
                  </th>
                  <th onClick={() => handleSort('deaths')}>
                    Tode {sortBy === 'deaths' && (sortOrder === 'asc' ? '▲' : '▼')}
                  </th>
                </tr>
              </thead>
              <tbody>
                {allStats.map((user, index) => (
                  <tr
                    key={user.twitchUsername}
                    ref={index === allStats.length - 1 ? lastElementRef : null}
                  >
                    <td>
                      {user.isLive && (
                        <span className="live-badge">● Live</span>
                      )}
                      {user.twitchUsername}
                    </td>
                    <td>{user.characterLevel || '-'}</td>
                    <td>
                      {user.totalDeaths > 0 && (
                        <button onClick={() => setSelectedUsername(user.twitchUsername)}>
                          {user.totalDeaths} Tode
                        </button>
                      )}
                    </td>
                  </tr>
                ))}
              </tbody>
            </table>
          </div>

          {/* Mobile: Card Grid */}
          <div className="md:hidden grid grid-cols-1 gap-4">
            {allStats.map((user, index) => (
              <div
                key={user.twitchUsername}
                ref={index === allStats.length - 1 ? lastElementRef : null}
                className="user-card"
              >
                {/* Card Content */}
              </div>
            ))}
          </div>

          {/* Loading State */}
          {isLoadingMore && <LoadingSpinner />}
          {!hasMore && <p>Alle Teilnehmer geladen</p>}
        </>
      ) : (
        <RecentDeathsTable deaths={recentDeaths} />
      )}

      {/* Modals */}
      {selectedUsername && (
        <AllClipsModal
          username={selectedUsername}
          onClose={() => setSelectedUsername(null)}
        />
      )}
    </>
  );
}

export function useDeathStats() {
  // Multi-Page State (Map-basiert)
  const [loadedPages, setLoadedPages] = useState<Map<number, UserStat[]>>(new Map());
  const [loadedPageNumbers, setLoadedPageNumbers] = useState<Set<number>>(new Set());
  const [currentPage, setCurrentPage] = useState(1);
  const [totalCount, setTotalCount] = useState(0);
  const [hasMore, setHasMore] = useState(true);
  const [isLoadingMore, setIsLoadingMore] = useState(false);
  const [isBackgroundRefreshing, setIsBackgroundRefreshing] = useState(false);
  const [sortBy, setSortBy] = useState<string | undefined>(undefined);
  const [sortOrder, setSortOrder] = useState<'asc' | 'desc'>('asc');

  // Vereinigte Liste aller geladenen Seiten
  const allStats = useMemo(() => {
    const pages = Array.from(loadedPages.entries())
      .sort((a, b) => a[0] - b[0]);  // Nach Seitennummer sortieren
    return pages.flatMap(([_, stats]) => stats);
  }, [loadedPages]);

  // Initial Load
  useEffect(() => {
    loadPage(1);
  }, [sortBy, sortOrder]);

  // Auto-Refresh alle 60s (Background)
  useEffect(() => {
    const interval = setInterval(() => {
      refreshAllPages();
    }, 60000);
    return () => clearInterval(interval);
  }, [loadedPageNumbers, sortBy, sortOrder]);

  async function loadPage(page: number) {
    if (loadedPageNumbers.has(page)) return;  // Bereits geladen

    setIsLoadingMore(true);
    try {
      const response = await fetch(
        `/api/users/stats?page=${page}&limit=15&sortBy=${sortBy}&sortOrder=${sortOrder}`
      );
      const data = await response.json();

      setLoadedPages(prev => new Map(prev).set(page, data.users));
      setLoadedPageNumbers(prev => new Set(prev).add(page));
      setTotalCount(data.totalCount);
      setHasMore(data.hasNextPage);
      setCurrentPage(page);
    } catch (error) {
      console.error('Failed to load page:', error);
    } finally {
      setIsLoadingMore(false);
    }
  }

  async function loadNextPage() {
    if (!hasMore || isLoadingMore) return;
    await loadPage(currentPage + 1);
  }

  async function refreshAllPages() {
    setIsBackgroundRefreshing(true);

    // Parallel alle geladenen Seiten refreshen
    const pageNumbers = Array.from(loadedPageNumbers);
    const promises = pageNumbers.map(async (page) => {
      const response = await fetch(
        `/api/users/stats?page=${page}&limit=15&sortBy=${sortBy}&sortOrder=${sortOrder}`
      );
      const data = await response.json();
      return { page, users: data.users };
    });

    try {
      const results = await Promise.all(promises);

      const newMap = new Map();
      results.forEach(({ page, users }) => {
        newMap.set(page, users);
      });

      setLoadedPages(newMap);
    } catch (error) {
      console.error('Background refresh failed:', error);
    } finally {
      setIsBackgroundRefreshing(false);
    }
  }

  function handleSort(column: string) {
    if (sortBy === column) {
      // Toggle Richtung
      setSortOrder(sortOrder === 'asc' ? 'desc' : 'asc');
    } else {
      setSortBy(column);
      setSortOrder('asc');
    }

    // Reset: Zurück zu Page 1
    setLoadedPages(new Map());
    setLoadedPageNumbers(new Set());
    setCurrentPage(1);
  }

  return {
    allStats,
    totalCount,
    currentPage,
    hasMore,
    isLoading: isLoadingMore && currentPage === 1,
    isLoadingMore,
    isBackgroundRefreshing,
    sortBy,
    sortOrder,
    loadNextPage,
    handleSort
  };
}

export function CookieConsent() {
  const { hasConsent, grantConsent } = useConsent();
  const [isVisible, setIsVisible] = useState(false);

  useEffect(() => {
    if (hasConsent === null) {
      setIsVisible(true);
    }
  }, [hasConsent]);

  if (!isVisible || hasConsent !== null) return null;

  return (
    <div className="fixed bottom-0 left-0 right-0 bg-slate-900 border-t-2 border-yellow-500 p-4 z-50">
      <div className="container mx-auto flex flex-col md:flex-row items-center justify-between gap-4">
        <p className="text-sm">
          Diese Seite verwendet Twitch-Embeds, die Cookies setzen können.
          <a href="https://www.twitch.tv/p/legal/privacy-notice/" target="_blank">
            Datenschutzerklärung von Twitch
          </a>
        </p>
        <div className="flex gap-2">
          <button
            onClick={() => {
              grantConsent(true);
              setIsVisible(false);
            }}
            className="btn-primary"
          >
            Akzeptieren
          </button>
          <button
            onClick={() => {
              grantConsent(false);
              setIsVisible(false);
            }}
            className="btn-secondary"
          >
            Ablehnen
          </button>
        </div>
      </div>
    </div>
  );
}

export function CookieSettings() {
  const pathname = usePathname();
  const { hasConsent, grantConsent } = useConsent();
  const [isOpen, setIsOpen] = useState(false);

  // OBS-Seiten: Kein Settings-Button
  if (pathname?.startsWith('/obs')) return null;

  return (
    <>
      <button
        onClick={() => setIsOpen(true)}
        className="fixed bottom-4 right-4 bg-slate-800 p-3 rounded-full z-40"
        aria-label="Cookie-Einstellungen"
      >
        <i className="fas fa-cog"></i>
      </button>

      {isOpen && (
        <div className="modal">
          <h2>Cookie-Einstellungen</h2>
          <p>Status: {hasConsent ? 'Akzeptiert' : 'Abgelehnt'}</p>
          <button onClick={() => {
            grantConsent(!hasConsent);
            setIsOpen(false);
          }}>
            {hasConsent ? 'Ablehnen' : 'Akzeptieren'}
          </button>
        </div>
      )}
    </>
  );
}

export function ClipModal({ clipUrl, onClose }: Props) {
  const { hasConsent, grantConsent } = useConsent();

  if (!hasConsent) {
    return (
      <div className="modal">
        <h2>Twitch-Clip ansehen</h2>
        <p>
          Um Twitch-Clips anzusehen, musst du der Verwendung von Cookies zustimmen.
        </p>
        <button onClick={() => grantConsent(true)}>
          Akzeptieren & Clip ansehen
        </button>
        <button onClick={onClose}>Abbrechen</button>
      </div>
    );
  }

  return (
    <div className="modal">
      <iframe
        src={`${clipUrl}&parent=${window.location.hostname}`}
        width="100%"
        height="500px"
        allowFullScreen
      ></iframe>
      <button onClick={onClose}>Schließen</button>
    </div>
  );
}

const ConsentContext = createContext<ConsentContextType | undefined>(undefined);

export function ConsentProvider({ children }: { children: React.ReactNode }) {
  const [hasConsent, setHasConsent] = useState<boolean | null>(null);

  useEffect(() => {
    // Load from localStorage
    const stored = localStorage.getItem('twitch_embed_consent');
    setHasConsent(stored === 'true' ? true : stored === 'false' ? false : null);
  }, []);

  function grantConsent(consent: boolean) {
    setHasConsent(consent);
    localStorage.setItem('twitch_embed_consent', consent.toString());
  }

  return (
    <ConsentContext.Provider value={{ hasConsent, grantConsent }}>
      {children}
    </ConsentContext.Provider>
  );
}

export function useConsent() {
  const context = useContext(ConsentContext);
  if (!context) throw new Error('useConsent must be used within ConsentProvider');
  return context;
}

import { rateLimit } from 'express-rate-limit';

// Global Rate-Limiter
const limiter = rateLimit({
  windowMs: 15 * 60 * 1000,  // 15 Minuten
  max: 100,                   // Max 100 Requests pro IP
  message: 'Zu viele Requests von dieser IP, bitte später versuchen.',
  standardHeaders: true,
  legacyHeaders: false
});

// Strenger Limiter für kritische Endpoints
const strictLimiter = rateLimit({
  windowMs: 5 * 60 * 1000,   // 5 Minuten
  max: 10,                    // Max 10 Requests
  message: 'Rate-Limit überschritten. Warte 5 Minuten.'
});

// CORS-Konfiguration
app.enableCors({
  origin: [
    'https://sourkrautreserve.de',
    'https://www.sourkrautreserve.de',
    process.env.NODE_ENV === 'development' ? 'http://localhost:3000' : null
  ].filter(Boolean),
  credentials: true,
  methods: ['GET', 'POST', 'PATCH', 'DELETE'],
  allowedHeaders: ['Content-Type', 'Authorization']
});

// Routes mit Rate-Limiting
app.use('/api', limiter);
app.use('/api/deaths', strictLimiter);  // Death-Recording kritisch

import { IsString, IsUrl, IsInt, Min, Max, Matches } from 'class-validator';

export class RecordDeathDto {
  @IsUrl({}, { message: 'Ungültige Clip-URL' })
  @Matches(/^https:\/\/(clips\.twitch\.tv|www\.twitch\.tv)/, {
    message: 'Nur Twitch-Clips erlaubt'
  })
  clip_url: string;

  @IsInt({ message: 'Level muss eine Zahl sein' })
  @Min(1, { message: 'Level muss mindestens 1 sein' })
  @Max(60, { message: 'Level kann maximal 60 sein' })
  level: number;
}

// Verwendung im Controller
@Post('/deaths')
async recordDeath(@Body() dto: RecordDeathDto) {
  // Automatische Validierung durch NestJS ValidationPipe
  return this.deathsService.create(dto);
}

// TypeORM Parameterized Query (verhindert SQL-Injection)
async findByUsername(username: string) {
  return this.userRepo.findOne({
    where: { twitch_username: username }  // Automatisch escaped
  });

  // NIEMALS so (SQL-Injection-Risiko):
  // await this.userRepo.query(`SELECT * FROM users WHERE username = '${username}'`);
}

// Discord OAuth 2.0 Callback
@Get('/auth/discord/callback')
async discordCallback(@Query('code') code: string) {
  // 1. Exchange Code für Access Token
  const tokenResponse = await axios.post(
    'https://discord.com/api/oauth2/token',
    new URLSearchParams({
      client_id: process.env.DISCORD_CLIENT_ID,
      client_secret: process.env.DISCORD_CLIENT_SECRET,
      grant_type: 'authorization_code',
      code: code,
      redirect_uri: process.env.DISCORD_REDIRECT_URI
    })
  );

  // 2. Access Token für User-Info nutzen
  const userResponse = await axios.get('https://discord.com/api/users/@me', {
    headers: {
      Authorization: `Bearer ${tokenResponse.data.access_token}`
    }
  });

  // 3. JWT erstellen (für Frontend-Auth)
  const jwt = this.jwtService.sign({
    discord_id: userResponse.data.id,
    username: userResponse.data.username
  });

  return { access_token: jwt };
}

// Role-Guard für Admin-Commands
export class AdminRoleGuard implements CanActivate {
  async canActivate(context: ExecutionContext): Promise<boolean> {
    const interaction = context.switchToRpc().getContext();
    const member = interaction.member;

    const adminRole = interaction.guild.roles.cache.find(
      r => r.name === 'Admin' || r.name === 'Mod'
    );

    if (!member.roles.cache.has(adminRole.id)) {
      await interaction.reply({
        content: '❌ Dieser Command benötigt Admin-Rechte.',
        ephemeral: true
      });
      return false;
    }

    return true;
  }
}

# NGINX Security Headers
add_header X-Frame-Options "SAMEORIGIN" always;
add_header X-Content-Type-Options "nosniff" always;
add_header X-XSS-Protection "1; mode=block" always;
add_header Referrer-Policy "strict-origin-when-cross-origin" always;
add_header Content-Security-Policy "default-src 'self'; script-src 'self' 'unsafe-inline' https://embed.twitch.tv; frame-src https://clips.twitch.tv https://www.twitch.tv; img-src 'self' data: https://static-cdn.jtvnw.net;" always;

# CSRF-Token für State-Changing Requests
# (In diesem Projekt: Nicht nötig, da nur GET-Requests vom Frontend)
# ABER: Discord-Commands nutzen Interaction-Tokens (CSRF-sicher)

import { sanitize } from 'dompurify';

// Twitch-Username-Validierung
function validateTwitchUsername(username: string): boolean {
  // Nur Alphanumerisch + Underscore (Twitch-Regel)
  const twitchUsernameRegex = /^[a-zA-Z0-9_]{4,25}$/;
  return twitchUsernameRegex.test(username);
}

// Character-Name-Sanitization (WoW erlaubt nur Buchstaben)
function sanitizeCharacterName(name: string): string {
  // Nur Buchstaben, keine Sonderzeichen/Zahlen/Emojis
  return name.replace(/[^a-zA-ZäöüÄÖÜß]/g, '');
}

// Discord-Command-Input wird automatisch von Discord.js escaped
// ABER: Bei Weitergabe an DB/API trotzdem validieren
@SlashCommand('charakter')
async setCharacter(
  @CommandOption('character_name') characterName: string
) {
  // Validierung
  if (!sanitizeCharacterName(characterName)) {
    return interaction.reply({
      content: '❌ Ungültiger Character-Name. Nur Buchstaben erlaubt.',
      ephemeral: true
    });
  }

  // Safe to use
  await this.usersService.setCharacter(userId, characterName);
}

// Snowfall Animation (Dezember-Januar)
export function Snowfall() {
  const [isMobile, setIsMobile] = useState(false);

  useEffect(() => {
    const checkMobile = () => setIsMobile(window.innerWidth < 768);
    checkMobile();

    const observer = new ResizeObserver(checkMobile);
    observer.observe(document.body);
    return () => observer.disconnect();
  }, []);

  if (isMobile) return null;  // Performance: Kein Schnee auf Mobile

  const snowflakes = Array.from({ length: 50 }, (_, i) => ({
    id: i,
    left: Math.random() * 100,
    animationDuration: 10 + Math.random() * 20,
    opacity: 0.3 + Math.random() * 0.7,
    size: 5 + Math.random() * 10
  }));

  return (
    <div className="fixed inset-0 pointer-events-none z-10">
      {snowflakes.map(flake => (
        <div
          key={flake.id}
          className="absolute animate-fall"
          style={{
            left: `${flake.left}%`,
            animationDuration: `${flake.animationDuration}s`,
            opacity: flake.opacity,
            width: `${flake.size}px`,
            height: `${flake.size}px`
          }}
        >
          ❄️
        </div>
      ))}
    </div>
  );
}

// Pulsing Live Badge
<style jsx>{`
  .live-badge {
    background: linear-gradient(135deg, #ef4444, #dc2626);
    color: white;
    padding: 2px 8px;
    border-radius: 12px;
    font-size: 0.75rem;
    font-weight: 600;
    animation: pulse 2s infinite;
  }

  @keyframes pulse {
    0%, 100% { opacity: 1; }
    50% { opacity: 0.7; }
  }
`}</style>

// lib/image-proxy.ts
export function getProxiedImageUrl(originalUrl: string | null): string {
  if (!originalUrl) return '/default-avatar.png';

  const encodedUrl = encodeURIComponent(originalUrl);
  return `/api/proxy/image?url=${encodedUrl}`;
}

// Verwendung in Komponente
<img
  src={getProxiedImageUrl(user.profileImageUrl)}
  alt={`${user.twitchUsername} Avatar`}
  loading="lazy"
/>

┌─────────────────────────────────────────────────────────────────┐
│ 1. USER-AKTION                                                  │
│    Discord: /tod https://clips.twitch.tv/abc123 47             │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 2. DISCORD BOT (DeathCommand)                                   │
│    ├─ Role-Check: Hat User "Death-Role"?                        │
│    ├─ Link-Check: Ist User verknüpft?                           │
│    ├─ Ban-Check: Ist User gebannt?                              │
│    └─ Validation OK → Weiter                                    │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 3. CLIPS SERVICE (Twitch-Validierung)                           │
│    ├─ Clip-URL Format prüfen (Regex)                            │
│    ├─ Twitch API: GET /clips?id=abc123                          │
│    ├─ Broadcaster-Name extrahieren                              │
│    ├─ Vergleich: broadcaster === user.twitch_username?          │
│    ├─ Duplikat-Check: Clip bereits in DB?                       │
│    └─ Validation OK → Clip-Titel speichern                      │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 4. DEATHS SERVICE (Death-Record erstellen)                      │
│    ├─ Death-Entity speichern:                                   │
│    │  - user_id                                                 │
│    │  - twitch_clip_url                                         │
│    │  - clip_title (von Twitch API)                             │
│    │  - level (aus Command)                                     │
│    │  - recorded_at (NOW())                                     │
│    ├─ User.death_count++ (inkrementieren)                       │
│    └─ Transaction committed                                     │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 5. USERS SERVICE (Character aufräumen)                          │
│    ├─ WowCharacter archivieren:                                 │
│    │  - archive_reason: 'death'                                 │
│    │  - archived_at: NOW()                                      │
│    │  - was_alive_at_archive: true                              │
│    ├─ User-Felder löschen:                                      │
│    │  - profession1 = null                                      │
│    │  - profession2 = null                                      │
│    │  - character_name = null                                   │
│    └─ WowCharacter.is_alive = false                             │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 6. DISCORD SERVICE (Notification)                               │
│    ├─ Embed-Message erstellen:                                  │
│    │  - Titel: "💀 RIP {character_name}"                        │
│    │  - Felder: Level, Twitch-Clip, User                        │
│    │  - Farbe: Rot (#ef4444)                                    │
│    │  - Thumbnail: Twitch-Avatar                                │
│    ├─ An Death-Channel senden                                   │
│    └─ User erwähnen (@mention)                                  │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 7. CACHE INVALIDATION                                           │
│    ├─ Alle user_stats_* Keys löschen                            │
│    └─ Total-Count-Cache invalidieren                            │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 8. FRONTEND (Auto-Update)                                       │
│    ├─ Background-Refresh läuft nach 60s                         │
│    ├─ Neue Death erscheint in "Letzte Tode" Tab                 │
│    ├─ User.death_count aktualisiert                             │
│    ├─ Character-Name entfernt                                   │
│    └─ Toast-Notification: "💀 {username} ist gestorben!"        │
└─────────────────────────────────────────────────────────────────┘

Gesamtdauer: ~2-3 Sekunden (inkl. API-Calls)

┌─────────────────────────────────────────────────────────────────┐
│ TRIGGER: Cron-Job alle 5 Minuten                                │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 1. LIVE STATUS SERVICE                                          │
│    ├─ Query: SELECT * FROM users                                │
│    ├─ Result: 124 User                                          │
│    ├─ Extract: twitch_usernames[]                               │
│    └─ Twitch API Calls (2 verschiedene)                         │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 2. TWITCH API CALL #1: Alle Live-Streams                        │
│    GET /helix/streams?user_login=user1&user_login=user2&...     │
│    (Batch: 100 User pro Request, also 2 Requests)               │
│                                                                 │
│    Response: [                                                  │
│      {                                                          │
│        "user_login": "sourkraut_tv",                            │
│        "game_name": "World of Warcraft",                        │
│        "viewer_count": 42,                                      │
│        "title": "HC Leveling - Level 47!"                       │
│      },                                                         │
│      {                                                          │
│        "user_login": "streamer123",                             │
│        "game_name": "Just Chatting",  ← Nicht WoW!             │
│        "viewer_count": 15                                       │
│      }                                                          │
│    ]                                                            │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 3. TWITCH API CALL #2: Nur WoW-Streams filtern                  │
│    Filter: stream.game_name === "World of Warcraft"             │
│                                                                 │
│    Result: [                                                    │
│      {                                                          │
│        username: "sourkraut_tv",                                │
│        viewerCount: 42                                          │
│      }                                                          │
│    ]                                                            │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 4. FÜR JEDEN USER (124 Iterationen)                             │
│                                                                 │
│    User A: "sourkraut_tv"                                       │
│    ├─ Banned? NEIN                                              │
│    ├─ In allLiveStreams? JA                                     │
│    ├─ In wowLiveStreams? JA                                     │
│    └─ Aktion: LIVE-ROLE ZUWEISEN                                │
│                                                                 │
│    User B: "streamer123"                                        │
│    ├─ Banned? NEIN                                              │
│    ├─ In allLiveStreams? JA                                     │
│    ├─ In wowLiveStreams? NEIN (streamt Just Chatting)          │
│    └─ Aktion: LIVE-ROLE ENTFERNEN                               │
│                                                                 │
│    User C: "offline_user"                                       │
│    ├─ Banned? NEIN                                              │
│    ├─ In allLiveStreams? NEIN                                   │
│    ├─ In wowLiveStreams? NEIN                                   │
│    └─ Aktion: LIVE-ROLE ENTFERNEN (falls hatte)                 │
│                                                                 │
│    User D: "banned_user"                                        │
│    ├─ Banned? JA                                                │
│    └─ Aktion: SKIP (verhindert 404-Fehler bei Discord API)      │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 5. DISCORD API (Role Assignment für User A)                     │
│    ├─ Guild: SourKraut Reserve                                  │
│    ├─ Member: discord_id "XXXXXXXXXXXXXXXXXX"                   │
│    ├─ Role: "Live" (ID: "YYYYYYYYYYYYYYYYYY")                   │
│    ├─ Check: member.roles.has(liveRole.id)?                     │
│    │  └─ NEIN → roles.add(liveRole)                             │
│    └─ Discord-Response: 204 No Content (Success)                │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 6. DATABASE UPDATE (für User A)                                 │
│    UPDATE users SET                                             │
│      is_live = true,                                            │
│      viewer_count = 42,                                         │
│      last_live_at = NOW()                                       │
│    WHERE id = 'user-a-uuid'                                     │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 7. DISCORD API (Role Removal für User B)                        │
│    ├─ Member.roles.has(liveRole.id)? JA                         │
│    └─ roles.remove(liveRole)                                    │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 8. DATABASE UPDATE (für User B)                                 │
│    UPDATE users SET                                             │
│      is_live = false,                                           │
│      viewer_count = NULL                                        │
│    WHERE id = 'user-b-uuid'                                     │
│    (last_live_at bleibt erhalten für Inactivity-Check!)         │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 9. CACHE INVALIDATION                                           │
│    ├─ Alle user_stats_* Keys löschen                            │
│    └─ Frontend holt Updates beim nächsten Refresh               │
└──────────────────────────┬──────────────────────────────────────┘
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│ 10. FRONTEND AUTO-UPDATE (nach 60s)                             │
│     ├─ Background-Refresh läuft                                 │
│     ├─ User A: Live-Badge erscheint (● Live - 42 Zuschauer)     │
│     ├─ User B: Live-Badge verschwindet                          │
│     └─ Sortierung: Live-User automatisch nach oben              │
└─────────────────────────────────────────────────────────────────┘

Besonderheit: Live-Role NUR für WoW-Streams!
Verhindert falsche Live-Anzeige wenn User andere Games streamt

┌────────────────────────────────────┬─────────┬─────────┬─────────┐
│ Endpoint                           │ P50     │ P95     │ P99     │
├────────────────────────────────────┼─────────┼─────────┼─────────┤
│ GET /api/users/stats               │ 120ms   │ 380ms   │ 650ms   │
│ GET /api/users/:username           │  65ms   │ 150ms   │ 280ms   │
│ GET /api/users/:username/deaths    │  80ms   │ 180ms   │ 320ms   │
│ GET /api/users (paginated)         │ 145ms   │ 420ms   │ 780ms   │
│ POST /api/deaths (mit Clip)        │ 250ms   │ 580ms   │ 1200ms  │
│ GET /api/guild-invites             │  45ms   │ 120ms   │ 210ms   │
└────────────────────────────────────┴─────────┴─────────┴─────────┘

Cache Hit-Rate: 85% (TTL: 30s für User-Stats)
Cache-Miss-Penalty: +200ms durchschnittlich (DB-Query + Processing)

┌────────────────────────────────────────┬──────────────┬──────────┐
│ Operation                              │ Avg Time     │ Rows     │
├────────────────────────────────────────┼──────────────┼──────────┤
│ Death-Insert (Single)                  │ 15ms         │ 1        │
│ User-Stats Query (mit Relations)       │ 45ms         │ 100      │
│ WoW-Character-Sync (Single)            │ 2.3s         │ 1        │
│   └─ Davon: Blizzard API               │ 2.2s (95%)   │ -        │
│   └─ Davon: DB-Update                  │ 0.1s (5%)    │ -        │
│ Leaderboard Query (Paginated, Cached)  │ 120ms        │ 20       │
│ Leaderboard Query (No Cache)           │ 380ms        │ 20       │
└────────────────────────────────────────┴──────────────┴──────────┘

Connection Pool Stats:
- Pool Size: 10 Connections
- Avg Utilization: 3.2 Connections (32%)
- Peak Utilization: 8 Connections (Event-Start)
- Queue Wait Time: 0ms (nie ausgelastet)

┌─────────────────────────────────────┬──────────┬──────────┐
│ Metric                              │ Desktop  │ Mobile   │
├─────────────────────────────────────┼──────────┼──────────┤
│ First Contentful Paint (FCP)        │ 800ms    │ 1.2s     │
│ Largest Contentful Paint (LCP)      │ 1.4s     │ 2.1s     │
│ Time to Interactive (TTI)           │ 1.8s     │ 2.8s     │
│ Cumulative Layout Shift (CLS)       │ 0.02     │ 0.03     │
│ Total Blocking Time (TBT)           │ 180ms    │ 320ms    │
│ Speed Index                         │ 1.6s     │ 2.4s     │
├─────────────────────────────────────┼──────────┼──────────┤
│ Lighthouse Score (Performance)      │ 92/100   │ 85/100   │
└─────────────────────────────────────┴──────────┴──────────┘

Bundle Sizes (Production, gzipped):
- Initial Bundle: 245KB
- Vendor Bundle (React, Next): 180KB
- Page Bundles: 15-40KB each
- Total Transfer: ~320KB (First Load)

Image Optimization:
- Next.js Image Component (WebP conversion)
- Lazy Loading für Screenshots (loading="lazy")
- Twitch Profile Images via CDN (cached 24h)

Test-Szenario: Event-Start (Peak-Traffic)
Duration: 10 Minuten
Virtual Users: 100 (ramp-up über 30s)

┌───────────────────────────────────────┬──────────────┐
│ Metric                                │ Value        │
├───────────────────────────────────────┼──────────────┤
│ Total Requests                        │ 24,350       │
│ Successful Requests                   │ 24,350 (100%)│
│ Failed Requests                       │ 0 (0%)       │
│ Avg Response Time                     │ 185ms        │
│ Max Response Time                     │ 1.8s         │
│ Requests/Second (Sustained)           │ 40.5 req/s   │
│ Peak Requests/Second                  │ 120 req/s    │
│ CPU Usage (Server)                    │ 45% avg      │
│ Memory Usage (Server)                 │ 2.8GB / 8GB  │
│ Database Connections (Peak)           │ 8 / 10       │
└───────────────────────────────────────┴──────────────┘

Error-Rate: 0% (keine 5xx-Fehler, keine Timeouts)

Endpoint-Breakdown (während Load Test):
- 60% GET /api/users/stats
- 20% GET /api/users (paginated)
- 10% GET /api/users/:username
- 10% GET /api/guild-invites

┌────────────────────────────────────┬──────────┬───────────┐
│ API                                │ Avg Time │ Reliability│
├────────────────────────────────────┼──────────┼───────────┤
│ Twitch Helix API (Streams)         │ 280ms    │ 99.8%     │
│ Twitch Helix API (Users)           │ 180ms    │ 99.9%     │
│ Blizzard WoW Profile API           │ 2.2s     │ 98.5%     │
│ Discord REST API (Role Assign)     │ 150ms    │ 99.7%     │
│ Discord Gateway (WebSocket)        │ 80ms RTT │ 99.9%     │
└────────────────────────────────────┴──────────┴───────────┘

Rate-Limiting-Respekt:
- Twitch: 800 req/min (wir nutzen ~50 req/min = 6%)
- Blizzard: 36k req/hour (wir nutzen ~720 req/hour = 2%)
- Discord: 50 req/s (wir nutzen ~5 req/s = 10%)

Retry-Strategie (bei 5xx-Fehlern):
- Attempt 1: Sofort
- Attempt 2: +1s (Exponential Backoff)
- Attempt 3: +2s
- Attempt 4: +4s (Max Retries)
- Success-Rate nach Retry: 99.95%

version: '3.8'

services:
  backend:
    build:
      context: ./backend
      dockerfile: Dockerfile
      target: production  # Multi-Stage Build
    ports:
      - "3001:3001"
    environment:
      - NODE_ENV=production
      - DB_HOST=mysql
    depends_on:
      - mysql
    restart: unless-stopped
    logging:
      driver: "json-file"
      options:
        max-size: "10m"
        max-file: "3"

  frontend:
    build:
      context: ./frontend
      dockerfile: Dockerfile
      target: production
    ports:
      - "3000:3000"
    environment:
      - NEXT_PUBLIC_API_URL=https://api.sourkrautreserve.de
    restart: unless-stopped

  mysql:
    image: mysql:8.0
    volumes:
      - mysql-data:/var/lib/mysql
      - ./backups:/backups  # Backup-Mount
    environment:
      - MYSQL_ROOT_PASSWORD=${MYSQL_ROOT_PASSWORD}
      - MYSQL_DATABASE=sourkraut_hc
    restart: unless-stopped
    command: --default-authentication-plugin=mysql_native_password

volumes:
  mysql-data:

# Stage 1: Builder
FROM node:18-alpine AS builder
WORKDIR /app
COPY package*.json ./
RUN npm ci --only=production
COPY . .
RUN npm run build

# Stage 2: Production
FROM node:18-alpine AS production
WORKDIR /app
COPY --from=builder /app/dist ./dist
COPY --from=builder /app/node_modules ./node_modules
COPY package*.json ./
USER node
EXPOSE 3001
CMD ["node", "dist/main.js"]

server {
    listen 443 ssl http2;
    server_name sourkrautreserve.de www.sourkrautreserve.de;

    ssl_certificate /etc/letsencrypt/live/sourkrautreserve.de/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/sourkrautreserve.de/privkey.pem;

    # Security Headers
    add_header X-Frame-Options "SAMEORIGIN" always;
    add_header X-Content-Type-Options "nosniff" always;
    add_header X-XSS-Protection "1; mode=block" always;

    # Frontend (Next.js)
    location / {
        proxy_pass http://localhost:3000;
        proxy_http_version 1.1;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    }

    # Backend API
    location /api {
        proxy_pass http://localhost:3001;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;

        # CORS Headers (für externe Requests)
        add_header 'Access-Control-Allow-Origin' 'https://sourkrautreserve.de' always;
    }
}

import * as winston from 'winston';

export const logger = winston.createLogger({
  level: process.env.LOG_LEVEL || 'info',
  format: winston.format.combine(
    winston.format.timestamp(),
    winston.format.errors({ stack: true }),
    winston.format.json()
  ),
  transports: [
    // Console Output (Development)
    new winston.transports.Console({
      format: winston.format.simple()
    }),
    // File Output (Production)
    new winston.transports.File({
      filename: 'logs/error.log',
      level: 'error',
      maxsize: 5242880,  // 5MB
      maxFiles: 5
    }),
    new winston.transports.File({
      filename: 'logs/combined.log',
      maxsize: 5242880,
      maxFiles: 7
    })
  ]
});

// Beispiel-Logging
logger.error('Death recording failed', {
  userId,
  error: error.message,
  stack: error.stack
});

@Controller('health')
export class HealthController {
  constructor(
    private readonly dataSource: DataSource,
    private readonly twitchService: TwitchService
  ) {}

  @Get()
  async check(): Promise<HealthCheckResult> {
    const checks = await Promise.allSettled([
      this.checkDatabase(),
      this.checkTwitchAPI(),
      this.checkDiskSpace()
    ]);

    const dbOk = checks[0].status === 'fulfilled';
    const twitchOk = checks[1].status === 'fulfilled';
    const diskOk = checks[2].status === 'fulfilled';

    const overall = dbOk && twitchOk && diskOk;

    return {
      status: overall ? 'healthy' : 'degraded',
      timestamp: new Date(),
      checks: {
        database: dbOk ? 'ok' : 'failed',
        twitch: twitchOk ? 'ok' : 'failed',
        disk: diskOk ? 'ok' : 'failed'
      }
    };
  }

  private async checkDatabase(): Promise<void> {
    await this.dataSource.query('SELECT 1');
  }

  private async checkTwitchAPI(): Promise<void> {
    await this.twitchService.getAccessToken();  // Token-Refresh-Test
  }

  private async checkDiskSpace(): Promise<void> {
    const { execSync } = require('child_process');
    const diskUsage = execSync("df -h / | tail -1 | awk '{print $5}'").toString().trim();
    const usagePercent = parseInt(diskUsage);

    if (usagePercent > 85) {
      throw new Error(`Disk usage critical: ${usagePercent}%`);
    }
  }
}

#!/bin/bash
# backup-mysql.sh

BACKUP_DIR="/backups"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
FILENAME="sourkraut_hc_${DATE}.sql.gz"

# MySQL-Dump mit Kompression
docker exec sourkraut-mysql mysqldump \
  -u root \
  -p${MYSQL_ROOT_PASSWORD} \
  sourkraut_hc \
  | gzip > ${BACKUP_DIR}/${FILENAME}

# Alte Backups löschen (älter als 7 Tage)
find ${BACKUP_DIR} -name "*.sql.gz" -mtime +7 -delete

# Upload zu Hetzner Storage Box (Weekly)
if [ $(date +%u) -eq 7 ]; then
  scp ${BACKUP_DIR}/${FILENAME} user@storage-box.de:/backups/
fi

echo "Backup completed: ${FILENAME}"

# Migration ausführen
npm run migration:run

# Bei Fehler: Rollback
npm run migration:revert

# Backup wiederherstellen (falls nötig)
docker exec -i sourkraut-mysql mysql -u root -p${MYSQL_ROOT_PASSWORD} sourkraut_hc \
  < /backups/sourkraut_hc_2025-12-15_03-00-00.sql

# MySQL Slow-Query-Log aktivieren
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;  # 2 Sekunden Threshold
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';

# Top 10 langsamste Queries analysieren
pt-query-digest /var/log/mysql/slow-query.log | head -100