# Datenbank-Dokumentation

## Übersicht

Das Jomblo-Projekt verwendet PostgreSQL als primäre Datenbank und Redis für Caching und Suche. Die Schemas sind in `schemas/db.ts` definiert.

## Docker Setup

Das Projekt verwendet Docker für die Datenbank-Infrastruktur:

```bash
# Start alle Dienste
pnpm run docker:up

# Setup Datenbank-Schema
pnpm run setup:schema

# Überprüfe Setup
pnpm run test:setup
```

### Services
- **PostgreSQL**: localhost:5435 (database: jomblo_dev)
- **Redis**: localhost:6380
- **pgAdmin**: http://localhost:5050 (admin@jomblo.de / admin123)

### Datenbank-Verbindung
```typescript
// In lib/db.ts
import { Pool } from 'pg';

const db = new Pool({
  host: process.env.DATABASE_HOST || 'localhost',
  port: parseInt(process.env.DATABASE_PORT || '5435'),
  database: process.env.DATABASE_NAME || 'jomblo_dev',
  user: process.env.DATABASE_USER || 'postgres',
  password: process.env.DATABASE_PASSWORD || 'passwort',
});
```

### Environment-Variablen
```env
# Datenbank
DATABASE_HOST=localhost
DATABASE_PORT=5435
DATABASE_NAME=jomblo_dev
DATABASE_USER=postgres
DATABASE_PASSWORD=passwort

# Redis
REDIS_HOST=localhost
REDIS_PORT=6380
```

## ER-Diagramm

```mermaid
erDiagram
    users {
        number id PK
        string firstname
        string lastname
        string email UK
        boolean emailVerified
        string password
        string image
        string name
        string role
        string customer_id FK
    }

    customers {
        string id PK
        string email
        string phone_number
        string name
        date created_at
        date last_updated_at
    }

    customer_addresses {
        string id PK
        string street
        string house_number
        string zip_code
        string city
        string country
        string state
        date created_at
        date last_updated_at
        string customer_id FK
        enum address_type
    }

    orders {
        string id PK
        string origin
        string origin_reference
        string customer_id FK
        number amount_total
        number items_total
        string shipping_address_id FK
        string billing_address_id FK
        enum payment_status
        enum order_status
        enum fulfilment_status
        date paid_at
        date created_at
        date last_updated_at
    }

    order_items {
        number id PK
        string order_id FK
        number product_id FK
        number order_item_price
        number order_item_quantity
        number order_item_total
        string order_item_status
        date created_at
        date last_updated_at
    }

    products {
        number id PK
        string mpn
        string ean
        string title
        string description
        number weight
        number height
        number length
        number width
        number manufacturer_id FK
        number supplier_id FK
        number brand_id FK
        number category_id FK
        number tax_class_id FK
        number eu_responsible_person_id FK
        number product_group_id FK
        date created_at
        date last_updated_at
    }

    categories {
        number id PK
        string name UK
        number parent_id FK
        string image_id
        date created_at
        date last_updated_at
    }

    brands {
        number id PK
        string name UK
        string image_id
        string description
        date created_at
        date last_updated_at
    }

    manufacturers {
        number id PK
        string company_name UK
        string address
        string email
        string phone_number
        date created_at
        date last_updated_at
    }

    suppliers {
        number id PK
        string company_name UK
        string registration_name
        string address
        string email
        string phone_number
        string website
        date created_at
        date last_updated_at
    }

    tax_classes {
        number id PK
        string name
        number percentage
        date created_at
        date last_updated_at
    }

    eu_responsible_persons {
        number id PK
        string company_name
        string email
        string phone_number
        string address
        date created_at
        date last_updated_at
    }

    product_groups {
        number id PK
        string name
        date created_at
        date last_updated_at
    }

    attributes {
        number id PK
        string name UK
        string type
        string description
        date created_at
        date last_updated_at
    }

    attribute_values {
        number id PK
        number attribute_id FK
        string value
        date created_at
        date last_updated_at
    }

    product_attributes {
        number id PK
        number product_id FK
        number attribute_id FK
        number attribute_value_id FK
        boolean is_variant
        date created_at
        date last_updated_at
    }

    shipment_tracking {
        string id PK
        string order_id FK
        string tracking_number
        string carrier
        enum shipment_type
        string status
        date created_at
        date last_updated_at
    }

    users ||--o{ customers : "has"
    customers ||--o{ customer_addresses : "has"
    customers ||--o{ orders : "places"
    customer_addresses ||--o{ orders : "billing/shipping"
    orders ||--o{ order_items : "contains"
    orders ||--o{ shipment_tracking : "has"
    products ||--o{ order_items : "item"
    products }o--o{ categories : "belongs"
    products }o--o{ brands : "belongs"
    products }o--o{ manufacturers : "made by"
    products }o--o{ suppliers : "supplied by"
    products }o--o{ tax_classes : "taxed as"
    products }o--o{ eu_responsible_persons : "responsible"
    product_groups ||--o{ products : "groups"
    categories ||--o{ categories : "parent"
    attributes ||--o{ attribute_values : "has"
    products }o--o{ product_attributes : "has"
    product_attributes }o--o{ attribute_values : "value"
    product_attributes }o--o{ attributes : "attribute"
```

## Tabellen-Beschreibungen

### users
Benutzerkonten für Authentifizierung und Customer-Verknüpfung.

### customers
Kunden aus Stripe-Checkouts, verknüpft mit users.

### customer_addresses
Adressen für Rechnung und Versand.

### orders
Bestellungen mit Status, Summen und Verknüpfung zur Sendungsverfolgung.

### order_items
Einzelpositionen einer Bestellung.

### products
Produkte mit Metadaten.

### categories, brands, manufacturers, suppliers
Katalog-Entitäten.

### tax_classes
Steuerkonfigurationen mit Name und Prozentsatz (`percentage`).

### eu_responsible_persons
EU-Bevollmächtigte mit Kontaktdaten (`company_name`, `email`, `phone_number`, `address`).

### product_groups
Gruppiert Produkte zu Varianten-Sets (z.B. ein Modell in verschiedenen Größen).

### shipment_tracking
Sendungsverfolgung für Bestellungen, verknüpft mit `orders`. Unterstützt verschiedene Typen wie `Original`, `Return` und `Replacement`.

### attributes, attribute_values, product_attributes
Produktattribute für Filter/Suche, einschließlich Variantenzuordnung (`is_variant`).

## Redis
- **Caching**: Produkt-Details in `product:{id}` Hashes.
- **Suche**: RediSearch-Index `products_idx` für Fuzzy-Suche.