# TacForce Pro — Datenmodell (MySQL)

Verbindlicher Vertrag für Migrationen, Eloquent-Models und Controller. **Alle Discord-IDs als `string` (VARCHAR)** speichern — Snowflakes sind 64-bit und überlaufen bei manchen Integer-Grenzen; außerdem behandelt Discord sie als Strings.

Konventionen: `id` = auto-increment BIGINT PK. Timestamps (`created_at`,`updated_at`) überall. Fremdschlüssel mit `constrained()->cascadeOnDelete()` wo sinnvoll. Enums als `string` + Model-Konstanten.

## users
| Feld | Typ | Notiz |
|------|-----|------|
| id | bigint PK | |
| discord_id | string, unique | Discord-User-ID |
| username | string | |
| global_name | string, nullable | Anzeigename |
| avatar_url | string, nullable | |
| email | string, nullable | |
| is_admin | boolean, default false | Betreiber-Zugang |
| discord_access_token | text, nullable, **encrypted** | für Guild-Refresh |
| discord_refresh_token | text, nullable, **encrypted** | |
| timestamps | | |

## guilds
| Feld | Typ | Notiz |
|------|-----|------|
| id | bigint PK | |
| discord_guild_id | string, unique | |
| name | string | |
| icon_url | string, nullable | |
| owner_discord_id | string, nullable | |
| bot_joined | boolean, default false | ist TacForce Operator drauf? |
| plan | string, default 'free' | free/starter/pro/elite/custom |
| bot_id | fk → bots.id, nullable | welcher Bot betreut den Server (Multi-Bot) |
| timestamps | | |

## guild_member_permissions
| Feld | Typ | Notiz |
|------|-----|------|
| id | bigint PK | |
| user_id | fk → users | |
| guild_id | fk → guilds | |
| role | string, default 'manager' | owner/admin/manager |
| can_manage_settings | boolean, default true | |
| can_manage_tickets | boolean, default true | |
| can_manage_applications | boolean, default true | |
| can_view_logs | boolean, default true | |
| unique(user_id, guild_id) | | |

## guild_settings
| Feld | Typ | Notiz |
|------|-----|------|
| id | bigint PK | |
| guild_id | fk → guilds, unique | |
| language | string, default 'de' | |
| log_channel_id | string, nullable | |
| welcome_channel_id | string, nullable | |
| welcome_message | text, nullable | |
| auto_role_id | string, nullable | |
| brand_color | string, default '#3B82F6' | |
| branding_enabled | boolean, default true | TacForce-Branding an |
| modules_enabled | json | {tickets:true, applications:true, welcome:true, logs:true, guard:false, level:false, partner:false} |

## ticket_categories
| Feld | Typ | Notiz |
|------|-----|------|
| id | bigint PK | |
| guild_id | fk → guilds | |
| name | string | |
| description | string, nullable | |
| emoji | string, nullable | |
| staff_role_id | string, nullable | zuständige Teamrolle |
| parent_channel_id | string, nullable | Discord-Channel-Kategorie |
| log_channel_id | string, nullable | |
| is_enabled | boolean, default true | |
| timestamps | | |

## tickets
| Feld | Typ | Notiz |
|------|-----|------|
| id | bigint PK | |
| guild_id | fk → guilds | |
| ticket_category_id | fk → ticket_categories, nullable | |
| creator_discord_id | string | |
| channel_id | string, nullable | |
| number | unsignedInteger | fortlaufend pro Guild (#TF-1042) |
| status | string, default 'open' | open/in_progress/closed |
| claimed_by_discord_id | string, nullable | |
| transcript_url | string, nullable | |
| closed_at | timestamp, nullable | |
| timestamps | | |

## application_forms
| Feld | Typ | Notiz |
|------|-----|------|
| id | bigint PK | |
| guild_id | fk → guilds | |
| title | string | |
| description | text, nullable | |
| target_channel_id | string, nullable | Bewerbungen landen hier |
| staff_role_id | string, nullable | |
| accepted_role_id | string, nullable | Rolle bei Annahme |
| accept_message | text, nullable | |
| reject_message | text, nullable | |
| is_enabled | boolean, default true | |
| timestamps | | |

## application_questions
| Feld | Typ | Notiz |
|------|-----|------|
| id | bigint PK | |
| application_form_id | fk → application_forms | |
| label | string | |
| placeholder | string, nullable | |
| required | boolean, default true | |
| order | unsignedInteger, default 0 | |

## application_submissions
| Feld | Typ | Notiz |
|------|-----|------|
| id | bigint PK | |
| guild_id | fk → guilds | |
| application_form_id | fk → application_forms | |
| applicant_discord_id | string | |
| status | string, default 'pending' | pending/accepted/rejected/archived |
| answers_json | json | [{label, answer}] |
| reviewed_by_discord_id | string, nullable | |
| review_reason | text, nullable | |
| reviewed_at | timestamp, nullable | |
| timestamps | | |

## audit_logs
| Feld | Typ | Notiz |
|------|-----|------|
| id | bigint PK | |
| guild_id | fk → guilds | |
| type | string | member_join/member_leave/message_delete/…/ticket_created/application_accepted |
| actor_discord_id | string, nullable | |
| target_discord_id | string, nullable | |
| message | string | menschenlesbar |
| metadata_json | json, nullable | |
| created_at | timestamp | (nur created_at nötig) |

## subscriptions
| Feld | Typ | Notiz |
|------|-----|------|
| id | bigint PK | |
| guild_id | fk → guilds | |
| plan | string | free/starter/pro/elite/custom |
| status | string, default 'active' | active/canceled/past_due |
| provider | string, nullable | manual/stripe/discord |
| provider_customer_id | string, nullable | |
| provider_subscription_id | string, nullable | |
| current_period_end | timestamp, nullable | |
| timestamps | | |

## bots  (Multi-Bot-Registry)
| Feld | Typ | Notiz |
|------|-----|------|
| id | bigint PK | |
| name | string | „TacForce Operator" |
| discord_client_id | string | |
| discord_token | text, **encrypted** | via Crypt::encryptString |
| discord_public_key | string, nullable | |
| owner_user_id | fk → users, nullable | White-Label-Besitzer |
| is_active | boolean, default true | |
| avatar_url | string, nullable | |
| accent_color | string, default '#3B82F6' | |
| timestamps | | |

## bot_jobs  (Outbox — Laravel schreibt, Bot pollt & erledigt)
| Feld | Typ | Notiz |
|------|-----|------|
| id | bigint PK | |
| bot_id | fk → bots, nullable | welcher Bot |
| guild_id | fk → guilds, nullable | |
| type | string | send_ticket_panel/send_apply_panel/close_ticket/... |
| payload_json | json | Parameter |
| status | string, default 'pending' | pending/processing/done/failed |
| error | text, nullable | |
| processed_at | timestamp, nullable | |
| timestamps | | |

## sessions / cache / jobs
Standard-Laravel-Tabellen (via `session:table`, `cache:table`, `queue:table` — in Migrations enthalten), da `SESSION_DRIVER=database`.

---

## Plan-Limits (in Code, nicht DB) — `App\Support\PlanLimits`
```php
free    => ticket_categories:1, tickets_per_month:50,  application_forms:1,  branding_removable:false
starter => ticket_categories:3, tickets_per_month:250, application_forms:3,  branding_removable:false
pro     => ticket_categories:∞, tickets_per_month:∞,   application_forms:∞,  branding_removable:true, transcripts:true, role_automation:true
elite   => wie pro + multi_server:true, faction:true(später), shop:true(später)
custom  => alles, white_label:true
```