# Omnyx Backend Migrationsplan

## Ziel

Migration von PHP + MySQL zu Bun + Hono + Drizzle ORM für eine Hybrid-Architektur:
- **Lokal:** SQLite (Desktop/Mobile, offline-fähig)
- **Server:** PostgreSQL (Docker, Multi-User)
- **Desktop:** Tauri + Bun-Sidecar
- **Mobile:** Capacitor + SQLite via WASM

## Tech Stack

| Komponente | Technologie |
|---|---|
| **Runtime** | Bun (TypeScript-nativ, bun:sqlite built-in) |
| **Server** | Hono (leicht, Zod-Validation) |
| **ORM** | Drizzle ORM (bun:sqlite / node-postgres) |
| **DB lokal** | bun:sqlite |
| **DB Server** | PostgreSQL (Docker) |
| **Desktop** | Tauri + Bun-Sidecar |
| **Mobile** | Capacitor + @libsql/client-web (WASM) |

## Projektstruktur (separates Repo)

```
omnyx-backend/
├── src/
│   ├── index.ts               ← Hono-Server + Router
│   ├── db/
│   │   ├── schema.ts          ← Drizzle-Schema (alle Tabellen)
│   │   ├── index.ts           ← DB-Connection (bun:sqlite / pg)
│   │   └── migrate.ts         ← Migration runner
│   ├── routes/
│   │   ├── media.ts           ← CRUD /api/media
│   │   ├── cast.ts            ← CRUD /api/cast
│   │   └── settings.ts        ← CRUD /api/settings
│   ├── middleware/
│   │   ├── cors.ts
│   │   └── error.ts
│   └── lib/
│       └── json-fields.ts     ← Helper für JSON-Array-Felder
├── scripts/
│   └── migrate-from-php.ts    ← Import-Skript von alter MySQL-DB
├── package.json
├── tsconfig.json
├── Dockerfile
└── docker-compose.yml
```

## Datenbank-Tabellen

### media
| Spalte | Typ | Anmerkung |
|--------|-----|-----------|
| id | integer | PK autoIncrement |
| title | text | NOT NULL |
| year | integer | NOT NULL |
| poster | text | nullable |
| banner | text | nullable |
| description | text | nullable |
| rating | real | nullable |
| category | text | nullable |
| type | text | default 'Movie' |
| status | text | default 'Released' |
| aspect_ratio | text | nullable |
| runtime | integer | nullable |
| director | text | nullable |
| writer | text | nullable |
| release_date | text | nullable |
| source | text | nullable |
| created_at | text | default datetime() |
| updated_at | text | default datetime() |
| genres | text | JSON-Array |
| tags | text | JSON-Array |
| studios | text | JSON-Array |
| categories | text | JSON-Array |
| series | text | JSON-Array |
| platforms | text | JSON-Array |
| developers | text | JSON-Array |
| completion_status | text | nullable |
| play_count | integer | default 0 |
| last_activity | text | nullable |
| playtime | integer | default 0 |

### episodes
| Spalte | Typ |
|--------|-----|
| id | integer PK |
| media_id | integer FK → media |
| season | integer |
| episode_number | integer |
| title | text |
| description | text |
| air_date | text |
| duration | integer |
| thumbnail | text |

### tracks
| Spalte | Typ |
|--------|-----|
| id | integer PK |
| media_id | integer FK → media |
| track_number | integer |
| title | text |
| duration | text (format: "M:SS") |
| artist | text |

### cast (staff)
| Spalte | Typ |
|--------|-----|
| id | integer PK |
| name | text NOT NULL |
| cleanname | text nullable |
| photo | text nullable |
| bio | text nullable |
| birth_date | text nullable |
| birth_place | text nullable |
| occupations | text (JSON-Array) |
| created_at | text |
| updated_at | text |
| media_types | text (JSON-Array) |
| bust_size | integer nullable |
| cup_size | text nullable |
| waist_size | integer nullable |
| hip_size | integer nullable |
| height | integer nullable |
| weight | integer nullable |
| hair_color | text nullable |
| eye_color | text nullable |
| ethnicity | text nullable |

### adult_specifics
| Spalte | Typ |
|--------|-----|
| id | integer PK |
| cast_id | integer FK → cast |
| tattoos | text nullable |
| piercings | text nullable |
| measurements | text nullable |
| shoe_size | integer nullable |

### media_cast_staff (Junction)
| Spalte | Typ |
|--------|-----|
| media_id | integer FK |
| cast_id | integer FK |
| role | text |
| character_name | text nullable |
| character_image | text nullable |
| PK | (media_id, cast_id) |

### cast_media_filmography (Junction)
| Spalte | Typ |
|--------|-----|
| cast_id | integer FK |
| media_id | integer FK |
| role | text |
| character_name | text nullable |

### settings
| Spalte | Typ |
|--------|-----|
| id | integer PK |
| enabled_categories | text (JSON-Array) |
| items_per_page | integer default 20 |
| grid_item_size | integer default 5 |
| default_view | text default 'grid' |
| show_adult_content | integer (boolean) default 0 |
| auto_play_trailers | integer (boolean) default 0 |
| language | text default 'en' |
| theme | text default 'system' |
| jellyfin_library_mappings | text nullable |
| page_title | text nullable |
| favicon | text nullable |
| custom_colors | text nullable (JSON) |
| created_at | text |
| updated_at | text |

## API-Endpoints (1:1 von PHP)

### Media
```
GET    /api/media?page=1&limit=10000  → PaginatedResponse<ApiMediaItem>
GET    /api/media/:id                 → ApiResponse<ApiMediaItem>
POST   /api/media                     → ApiResponse<ApiMediaItem>
PUT    /api/media/:id                  → ApiResponse<ApiMediaItem>
DELETE /api/media/:id                  → ApiResponse<{message}>
```

### Cast
```
GET    /api/cast?page=1&limit=100000  → PaginatedResponse<ApiCastItem>
GET    /api/cast/:id                  → ApiResponse<ApiCastItem>
GET    /api/cast/:id/media            → PaginatedResponse<ApiMediaItem>
POST   /api/cast                      → ApiResponse<ApiCastItem>
PUT    /api/cast/:id                  → ApiResponse<ApiCastItem>
DELETE /api/cast/:id                  → ApiResponse<{message}>
```

### Settings
```
GET    /api/settings   → ApiResponse<ApiSettingsItem>
POST   /api/settings   → ApiResponse<ApiSettingsItem>
PUT    /api/settings   → ApiResponse<ApiSettingsItem>
```

## Response-Format (identisch zu PHP)
```json
{
  "success": true,
  "data": { ... }
}
```

## DB-Connection (Driver-Abstraktion)

```typescript
if (process.env.DATABASE_URL) {
  // Server-Modus → PostgreSQL
  const { Pool } = await import('pg');
  const { drizzle } = await import('drizzle-orm/node-postgres');
  db = drizzle(new Pool({ connectionString: process.env.DATABASE_URL }));
} else {
  // Lokaler Modus → bun:sqlite
  const { Database } = await import('bun:sqlite');
  const { drizzle } = await import('drizzle-orm/bun-sqlite');
  const sqlite = new Database('./data/omnyx.db', { create: true });
  sqlite.exec('PRAGMA journal_mode=WAL;');
  db = drizzle(sqlite);
}
```

## Desktop-Bundle (Tauri)

- Bun-Binary via `bun build --compile` (~50MB Single-File)
- Tauri startet Binary als Sidecar
- React-Frontend (produktions-Build) in Tauri-WebView
- API auf `localhost:3001`

## Mobile (Capacitor + WASM SQLite)

- SQLite läuft direkt im WebView via `@libsql/client/web`
- Drizzle-Queries identisch zum Backend
- Kein separater Server nötig
- Optional: Sync mit Server bei Internet-Verbindung

## Docker-Deployment

```yaml
services:
  backend:
    build: .
    environment:
      DATABASE_URL: postgres://user:pass@db:5432/omnyx
  db:
    image: postgres:16-alpine
```

## Status (23.05.2026)

| Phase | Beschreibung | Status |
|---|---|---|
| P1 | Backend-Projekt (Bun + Hono + Drizzle) | ✅ **Fertig** |
| P2 | Hono-Routen (Media, Cast, Settings) | ✅ **Fertig** |
| P3 | Tests + TypeScript-Lint | ✅ **Fertig** |
| P4 | Docker-Config aktualisiert | ✅ **Fertig** |
| P5 | Tauri Desktop (Bun-Sidecar) | ✅ **Fertig** |
| P6 | PostgreSQL-Support + Deployment | ⏳ Ausstehend |
| P7 | Sync-Logik (Hybrid) | ⏳ Optional |
| P8 | Capacitor Mobile (lokales SQLite) | ⏳ Ausstehend |

### Aktuelle Struktur

```
G:\kyoo\
├── backend/           ← Bun + Hono + Drizzle
│   ├── src/
│   │   ├── index.ts           ← Hono-Server (Port 3001)
│   │   ├── db/
│   │   │   ├── schema.ts      ← Drizzle-Schema (9 Tabellen)
│   │   │   ├── index.ts       ← DB-Connection (bun:sqlite / pg-fähig)
│   │   │   └── migrate.ts     ← Auto-Migration (CREATE TABLE IF NOT EXISTS)
│   │   ├── routes/
│   │   │   ├── media.ts       ← CRUD /api/media
│   │   │   ├── cast.ts        ← CRUD /api/cast + POST /adult
│   │   │   ├── settings.ts    ← CRUD /api/settings
│   │   │   └── images.ts      ← Proxy-/Cache-Endpunkt /api/images/proxy
│   │   ├── lib/converters.ts  ← Type-Converter
│   │   └── types.ts           ← API-Types
│   ├── uploads/               ← Gecachte Bilder (offline-fähig)
│   ├── dist-exe/              ← Compiled binary (~50MB)
│   ├── Dockerfile             ← Bun-basiert
│   ├── package.json
│   └── README.md
├── frontend/          ← React SPA + Tauri-Desktop-Shell
│   ├── src-tauri/             ← Tauri v2 Desktop-Konfiguration
│   │   ├── src/lib.rs         ← Sidecar-Spawn (omnyx-backend)
│   │   ├── binaries/          ← Kompilierte Binaries pro Target
│   │   ├── tauri.conf.json    ← Window 1280x800, externalBin
│   │   ├── capabilities/      ← shell:default, shell:allow-spawn
│   │   └── Cargo.toml         ← tauri-plugin-shell
│   └── .env.tauri             ← VITE_API_URL=http://localhost:3001
└── docker-compose.yml
```

### Nächste Schritte

1. **PostgreSQL-Support** (P6) — pg-Treiber für Drizzle, docker-compose mit Postgres
2. **Capacitor Mobile** (P8) — Android-App mit embedded SQLite via WASM
3. **Sticky Footer Bug** — Footer klebt nicht am unteren Rand bei kurzen Seiten
4. **PUT /api/cast/:id** um adult-Felder erweitern (height, weight etc. aktuell nicht updatbar)