# Discord Bot Logging System - Implementierungs-Prompt

## Datenbank-Struktur

Tabelle: `discord_logs`

| Spalte       | Typ              | Beschreibung                                    |
|--------------|------------------|-------------------------------------------------|
| id           | INT AUTO_INCREMENT | Primary Key                                   |
| action       | VARCHAR(50)      | Log-Typ (siehe unten)                           |
| user_id      | VARCHAR(20)      | Discord User ID                                 |
| username     | VARCHAR(100)     | Username oder Display Name                      |
| user_avatar  | VARCHAR(255)     | Avatar URL                                      |
| target_id    | VARCHAR(20)      | Betroffene User/Channel/Role ID (optional)      |
| target_name  | VARCHAR(100)     | Name des Ziels (optional)                       |
| details      | TEXT (JSON)      | Zusaetzliche Details als JSON                   |
| guild_id     | VARCHAR(20)      | Server ID                                       |
| created_at   | INT/TIMESTAMP    | Unix Timestamp                                  |

---

## Action Types nach Kategorie

### MODERATION (/logs/moderation)

| Action              | Details (JSON)                                                        |
|---------------------|-----------------------------------------------------------------------|
| member_banned       | {"reason": "...", "moderator_id": "...", "moderator_name": "..."}     |
| member_kicked       | {"reason": "...", "moderator_id": "...", "moderator_name": "..."}     |
| member_unbanned     | {"moderator_id": "...", "moderator_name": "..."}                      |
| member_timeout      | {"duration": 3600, "reason": "...", "moderator_id": "..."}            |
| timeout_removed     | {"moderator_id": "...", "moderator_name": "..."}                      |
| mod_command         | {"command": "/warn", "args": "...", "channel_id": "..."}              |
| blacklist_triggered | {"word": "...", "message": "...", "channel_id": "..."}                |

### SECURITY (/logs/security)

| Action          | Details (JSON)                                          |
|-----------------|---------------------------------------------------------|
| security_alert  | {"type": "raid", "description": "..."}                  |
| security_action | {"action": "lockdown", "moderator_id": "..."}           |
| automod_action  | {"rule": "...", "action_taken": "delete/timeout"}       |

### CHANNELS (/logs/channels)

| Action          | Details (JSON)                                                                    |
|-----------------|-----------------------------------------------------------------------------------|
| channel_created | {"channel_id": "...", "channel_name": "...", "channel_type": "text/voice/category"} |
| channel_updated | {"channel_id": "...", "changes": {"name": ["old", "new"], "topic": ["old", "new"]}} |
| channel_deleted | {"channel_id": "...", "channel_name": "...", "channel_type": "..."}               |
| thread_created  | {"thread_id": "...", "thread_name": "...", "parent_id": "..."}                    |
| thread_updated  | {"thread_id": "...", "changes": {...}}                                            |
| thread_deleted  | {"thread_id": "...", "thread_name": "..."}                                        |

### MEMBERS (/logs/members)

| Action           | Details (JSON)                                                                 |
|------------------|--------------------------------------------------------------------------------|
| member_joined    | {"account_created": 1234567890, "invite_code": "abc123", "inviter_id": "..."}  |
| member_left      | {"roles": ["role_id1", "role_id2"], "joined_at": 1234567890}                   |
| invite_created   | {"code": "...", "max_uses": 10, "expires_at": 1234567890, "channel_id": "..."}  |
| invite_deleted   | {"code": "...", "uses": 5}                                                     |
| nickname_changed | {"old_nickname": "...", "new_nickname": "..."}                                 |

### MESSAGES (/logs/messages)

| Action          | Details (JSON)                                                                      |
|-----------------|-------------------------------------------------------------------------------------|
| message_deleted | {"content": "...", "channel_id": "...", "channel_name": "...", "attachments": [...]} |
| message_edited  | {"old_content": "...", "new_content": "...", "channel_id": "...", "message_id": "..."} |
| bulk_delete     | {"count": 50, "channel_id": "...", "channel_name": "..."}                           |

### ROLES (/logs/roles)

| Action       | Details (JSON)                                                                  |
|--------------|---------------------------------------------------------------------------------|
| role_created | {"role_id": "...", "role_name": "...", "color": "#ff0000", "permissions": "..."} |
| role_updated | {"role_id": "...", "changes": {"name": ["old", "new"], "color": ["#fff", "#000"]}} |
| role_deleted | {"role_id": "...", "role_name": "..."}                                          |
| role_given   | {"role_id": "...", "role_name": "...", "moderator_id": "..."}                   |
| role_removed | {"role_id": "...", "role_name": "...", "moderator_id": "..."}                   |

### VOICE (/logs/voice)

| Action         | Details (JSON)                                              |
|----------------|-------------------------------------------------------------|
| voice_joined   | {"channel_id": "...", "channel_name": "..."}                |
| voice_left     | {"channel_id": "...", "channel_name": "...", "duration": 3600} |
| voice_moved    | {"from_channel": "...", "to_channel": "...", "moderator_id": "..."} |
| voice_muted    | {"type": "server/self", "moderator_id": "..."}              |
| voice_deafened | {"type": "server/self", "moderator_id": "..."}              |

---

## Discord.js Events -> Actions Mapping

guildBanAdd           -> member_banned
guildBanRemove        -> member_unbanned
guildMemberRemove     -> member_kicked (wenn aus AuditLog) / member_left
guildMemberUpdate     -> nickname_changed, role_given, role_removed, member_timeout
guildMemberAdd        -> member_joined
channelCreate         -> channel_created
channelUpdate         -> channel_updated
channelDelete         -> channel_deleted
threadCreate          -> thread_created
threadUpdate          -> thread_updated
threadDelete          -> thread_deleted
messageDelete         -> message_deleted
messageUpdate         -> message_edited
messageDeleteBulk     -> bulk_delete
roleCreate            -> role_created
roleUpdate            -> role_updated
roleDelete            -> role_deleted
voiceStateUpdate      -> voice_joined, voice_left, voice_moved, voice_muted, voice_deafened
inviteCreate          -> invite_created
inviteDelete          -> invite_deleted

---

## Beispiel Insert (Node.js / Discord.js)

```javascript
await db.execute(`
  INSERT INTO discord_logs (action, user_id, username, user_avatar, target_id, target_name, details, guild_id, created_at)
  VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
`, [
  'member_banned',
  moderator.id,
  moderator.username,
  moderator.displayAvatarURL(),
  bannedUser.id,
  bannedUser.username,
  JSON.stringify({ reason: 'Spam', moderator_id: moderator.id, moderator_name: moderator.username }),
  guild.id,
  Math.floor(Date.now() / 1000)
]);
```

---

## Beispiel Event Handler (guildBanAdd)

```javascript
client.on('guildBanAdd', async (ban) => {
  const auditLogs = await ban.guild.fetchAuditLogs({ type: AuditLogEvent.MemberBanAdd, limit: 1 });
  const banLog = auditLogs.entries.first();

  const moderator = banLog?.executor;
  const reason = banLog?.reason || 'Kein Grund angegeben';

  await db.execute(`
    INSERT INTO discord_logs (action, user_id, username, user_avatar, target_id, target_name, details, guild_id, created_at)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  `, [
    'member_banned',
    moderator?.id || 'unknown',
    moderator?.username || 'Unknown',
    moderator?.displayAvatarURL() || '',
    ban.user.id,
    ban.user.username,
    JSON.stringify({ reason, moderator_id: moderator?.id, moderator_name: moderator?.username }),
    ban.guild.id,
    Math.floor(Date.now() / 1000)
  ]);
});
```

---
---

## Hinweise

1. Alle Timestamps werden als Unix Timestamp (Sekunden) gespeichert
2. Details werden als JSON String gespeichert fuer Flexibilitaet
3. Bei Moderation-Events immer versuchen, den Moderator aus den Audit Logs zu holen
4. Avatar URLs koennen sich aendern - ggf. nur Hash speichern
5. target_id/target_name sind optional und nur relevant wenn eine andere Entitaet betroffen ist
6. guild_id immer mitspeichern fuer Multi-Server Support