# Multi-Language JSONB Implementation Guide

## Overview

This guide explains how to convert any module from the old `language_id` row-per-language pattern to the **per-field JSONB** pattern. Use the Achievement module as the reference implementation.

---

## The Problem with the Old Approach

The old pattern created a **separate row per language** for the same item, linked via `language_id`:

```
id | language_id | name                  | slug
1  | 1 (en)      | "Academic Excellence" | "academic-excellence"
2  | 2 (bn)      | "একাডেমিক শ্রেষ্ঠত্ব" | "academic-excellence-bn"
```

This caused:
- Duplicate `uid` values across language rows — ambiguous identity
- Status/media changes had to be synced across N rows
- Queries returned duplicate items without a language filter
- Deleting one item meant deleting N rows

---

## The Solution: Per-Field JSONB

Store **one row per item**. Each translatable text field is a JSONB column keyed by locale code:

```
id | name                                                | grade | status
1  | {"en": "Academic Excellence", "bn": "একাডেমিক..."} | "A+"  | 1
```

Non-translatable fields (`status`, `grade`, `awards`, `media_id`, etc.) stay as plain columns.

---

## Step-by-Step Implementation

### Step 1 — Identify Translatable Fields

Before changing anything, decide which fields need translation and which do not.

**Translatable** (content that changes per language — text shown on the website):
- `name`, `title`, `slug`, `description`, `tag`, `content`, `excerpt`, etc.

**Non-translatable** (structural data — same regardless of language):
- `status`, `grade`, `media_id`, `awards`, `price`, `type`, `created_at`, etc.

---

### Step 2 — Update the Migration

Replace the old `language_id` foreign key and plain string/text columns with `jsonb` columns.

```php
// database/migrations/YYYY_MM_DD_create_{module}_table.php

Schema::create('achievements', function (Blueprint $table) {
    $table->id();
    $table->string('uid')->unique();

    // Translatable fields — JSONB keyed by locale code
    // e.g. {"en": "Academic Excellence", "bn": "একাডেমিক শ্রেষ্ঠত্ব"}
    $table->jsonb('name')->nullable();
    $table->jsonb('slug')->nullable();
    $table->jsonb('description')->nullable();
    $table->jsonb('tag')->nullable();

    // Non-translatable fields — unchanged plain columns
    $table->string('grade')->nullable();
    $table->tinyInteger('status')->default(1);
    $table->unsignedBigInteger('media_id')->nullable();

    $table->softDeletes();
    $table->timestamps();
});
```

> **Note:** Remove `language_id` and its `$table->foreign(...)` entirely.
> Use `jsonb` (not `json`) for PostgreSQL — it supports indexing and the `@>` / `->>` operators.

---

### Step 3 — Add the `HasTranslations` Trait

This shared trait lives at `app/Traits/HasTranslations.php` and is already in the project. It provides `translate()` and `getTranslations()` to every model that uses it.

```php
// app/Traits/HasTranslations.php  (already exists — do not recreate)

trait HasTranslations
{
    /**
     * Get the value of a translatable field for the given locale.
     * Falls back: requested locale → fallback_locale → first non-empty value → null
     */
    public function translate(string $field, ?string $locale = null): ?string
    {
        $locale   = $locale ?? app()->getLocale();
        $fallback = config('app.fallback_locale', 'en');
        $data     = $this->{$field};

        if (! is_array($data)) {
            return $data; // graceful: field not yet cast
        }

        return $data[$locale]
            ?? $data[$fallback]
            ?? (array_values(array_filter($data))[0] ?? null);
    }

    public function getTranslations(string $field): array
    {
        $data = $this->{$field};
        return is_array($data) ? $data : [];
    }
}
```

---

### Step 4 — Update the Model

```php
use App\Traits\HasTranslations;
use Illuminate\Support\Str;

class Achievement extends Model
{
    use HasTranslations, SoftDeletes, /* ...other traits */;

    protected $fillable = [
        'uid',
        'name',         // jsonb — translatable
        'slug',         // jsonb — translatable
        'description',  // jsonb — translatable
        'tag',          // jsonb — translatable
        'grade',        // plain column
        'status',
        'media_id',
    ];

    protected $casts = [
        'name'        => 'array',   // JSONB ↔ PHP array
        'slug'        => 'array',
        'description' => 'array',
        'tag'         => 'array',
        'awards'      => 'array',
    ];

    protected static function booted(): void
    {
        static::creating(function ($model) {
            if (empty($model->uid)) {
                $model->uid = str_unique();
            }
            // Auto-generate slug for each locale that has a name but no slug
            if (is_array($model->name)) {
                $slugs = is_array($model->slug) ? $model->slug : [];
                foreach ($model->name as $locale => $nameValue) {
                    if (empty($slugs[$locale]) && ! empty($nameValue)) {
                        $slugs[$locale] = Str::slug($nameValue);
                    }
                }
                $model->slug = $slugs;
            }
        });
    }
}
```

> Remove the `language()` `belongsTo` relationship — it is no longer needed.

---

### Step 5 — Update the ModelFilter

The old filter used plain `LIKE` on string columns. JSONB requires PostgreSQL's `->>` operator.

```php
// app/ModelFilters/AchievementFilter.php

public function search($value): self
{
    $locale   = app()->getLocale();
    $fallback = config('app.fallback_locale', 'en');

    return $this->where(function ($q) use ($value, $locale, $fallback) {
        $q->whereRaw('name->>? ILIKE ?', [$locale, "%{$value}%"])
          ->orWhereRaw('name->>? ILIKE ?', [$fallback, "%{$value}%"])
          ->orWhereRaw('description->>? ILIKE ?', [$locale, "%{$value}%"])
          ->orWhereRaw('description->>? ILIKE ?', [$fallback, "%{$value}%"]);
    });
}
```

---

### Step 6 — Update the FormRequest

Translatable fields are submitted as locale-keyed arrays: `name[en]`, `name[bn]`, etc.

```php
// app/Http/Requests/AchievementRequest.php

public function rules(): array
{
    $defaultLocale = config('app.fallback_locale', 'en');

    return [
        'name'                      => 'required|array',
        "name.{$defaultLocale}"     => 'required|string|max:255',  // default locale is required
        'name.*'                    => 'nullable|string|max:255',

        'slug'                      => 'nullable|array',
        'slug.*'                    => 'nullable|string|max:255',

        'description'               => 'nullable|array',
        'description.*'             => 'nullable|string',

        'tag'                       => 'nullable|array',
        'tag.*'                     => 'nullable|string|max:255',

        // Non-translatable — unchanged
        'status'                    => 'required|int',
        'media_id'                  => 'required|int',
        // ... other plain fields
    ];
}
```

---

### Step 7 — Update the Resource (Transformer)

Return **both** the raw JSON objects (for admin edit forms) and resolved display strings (for table display and public pages).

```php
// app/Transformers/AchievementResource.php

public function toArray(Request $request): array
{
    $locale = $request->header('X-Locale', app()->getLocale());

    return [
        'id'  => $this->id,
        'uid' => $this->uid,

        // Full JSONB objects — admin forms use these to populate language tabs
        'name'        => $this->name,
        'slug'        => $this->slug,
        'description' => $this->description,
        'tag'         => $this->tag,

        // Resolved single-locale strings — table display and public pages
        'display_name'        => $this->translate('name',        $locale),
        'display_slug'        => $this->translate('slug',        $locale),
        'display_description' => $this->translate('description', $locale),
        'display_tag'         => $this->translate('tag',         $locale),

        // Non-translatable — unchanged
        'grade'       => $this->grade,
        'status'      => StatusEnum::from($this->status)->value,
        'awards'      => $this->awards,
        'media'       => $this->media,
        'created_at'  => $this->created_at->toDateTimeString(),
        'updated_at'  => $this->updated_at->toDateTimeString(),
    ];
}
```

---

### Step 8 — Update the Service

#### 8a. Slug uniqueness

Slug is now inside JSONB so it cannot have a DB-level `UNIQUE` index. Enforce uniqueness in the service.

```php
private function assertSlugUnique(array $slugs, ?int $excludeId = null): void
{
    foreach ($slugs as $locale => $slug) {
        if (empty($slug)) continue;

        $exists = Achievement::whereRaw('slug->>? = ?', [$locale, $slug])
            ->when($excludeId, fn ($q) => $q->where('id', '!=', $excludeId))
            ->exists();

        if ($exists) {
            throw new \InvalidArgumentException(
                "Slug '{$slug}' already exists for locale '{$locale}'."
            );
        }
    }
}

// In create():
if (! empty($data['slug'])) {
    $this->assertSlugUnique($data['slug']);
}

// In update():
if (! empty($data['slug'])) {
    $this->assertSlugUnique($data['slug'], excludeId: (int) $id);
}
```

#### 8b. Activity log — use `translate()` instead of the raw field

```php
// Before (broken after migration):
'name' => $item->name,        // returns an array now

// After:
'name' => $item->translate('name'),
```

---

### Step 9 — Update the Controller

Remove the `AcademicSettingService` dependency used for language dropdown options. Pass active languages directly from the `Language` model for the tab UI.

```php
use App\Models\Language;

// Remove: protected AcademicSettingService $academicSettingService

// In index() and show():
$languages = Language::where('status', 1)
    ->orderByDesc('is_default')
    ->orderBy('id')
    ->get(['id', 'name', 'code', 'is_default'])
    ->toArray();

// Pass as:
'languages' => $languages,
```

Also surface `InvalidArgumentException` (slug uniqueness) as a user-facing error:

```php
} catch (\InvalidArgumentException $e) {
    return redirect()->back()->with('error', $e->getMessage());
}
```

---

### Step 10 — Update the Frontend Forms (Create & Edit)

Replace the single `language_id` select dropdown with **language tabs** — one tab per active language.

#### Key changes

1. `languages` comes from `usePage().props.data.languages` (passed by controller)
2. `current_locale` comes from `usePage().props.current_locale` (shared Inertia prop — follows admin panel language switch)
3. Form fields become nested: `name.en`, `name.bn`, etc.
4. `SlugSync` runs per locale
5. Yup schema is built dynamically from the languages list

```tsx
// Active locale follows the admin panel language switcher
const { current_locale } = usePage().props as any;
const defaultLocale = current_locale
    ?? languages.find((l) => l.is_default)?.code
    ?? 'en';

// Default values — one key per locale for each translatable field
const defaultValues = {
    name:        Object.fromEntries(languages.map((l) => [l.code, ''])),
    slug:        Object.fromEntries(languages.map((l) => [l.code, ''])),
    description: Object.fromEntries(languages.map((l) => [l.code, ''])),
    tag:         Object.fromEntries(languages.map((l) => [l.code, ''])),
    // ... non-translatable fields as normal
};

// Form structure
<Tabs defaultValue={defaultLocale}>
    <TabsList>
        {languages.map((lang) => (
            <TabsTrigger key={lang.code} value={lang.code}>
                {lang.name} {lang.is_default ? '*' : ''}
            </TabsTrigger>
        ))}
    </TabsList>

    {languages.map((lang) => (
        <TabsContent key={lang.code} value={lang.code}>
            <FormField name={`name.${lang.code}`}        label={`Name (${lang.name})`} />
            <FormField name={`slug.${lang.code}`}        label={`Slug (${lang.name})`} />
            <FormField name={`tag.${lang.code}`}         label={`Tag (${lang.name})`} />
            <TextEditor name={`description.${lang.code}`} label={`Description (${lang.name})`} />
        </TabsContent>
    ))}
</Tabs>

{/* Non-translatable fields live outside the tabs */}
<FormField name="grade" ... />
<FormField name="status" ... />
```

#### Edit form — populate from existing data

```tsx
const buildDefaultValues = () => ({
    name: Object.fromEntries(languages.map((l) => [l.code, defaultValues.name?.[l.code] ?? ''])),
    slug: Object.fromEntries(languages.map((l) => [l.code, defaultValues.slug?.[l.code] ?? ''])),
    // ...
});
```

---

### Step 11 — Update the Index / List Page

#### TypeScript interface

```tsx
interface ItemData {
    // Raw JSON — passed to Edit modal
    name:        Record<string, string>;
    slug:        Record<string, string>;
    description: Record<string, string>;
    tag:         Record<string, string>;

    // Resolved strings — rendered in table cells
    display_name:        string;
    display_slug:        string;
    display_description: string;
    display_tag:         string;

    // Non-translatable — unchanged
    grade: string;
    status: number;
    // ...
}
```

#### Name column with locale coverage badges

```tsx
{
    accessorKey: 'display_name',
    header: 'Name',
    cell: ({ row }) => {
        const item = row.original;
        return (
            <div className="flex flex-col gap-1">
                <span className="font-medium">{item.display_name || '—'}</span>
                <div className="flex flex-wrap gap-1">
                    {languages.map((lang) => {
                        const hasValue = !!item.name?.[lang.code];
                        return (
                            <span
                                key={lang.code}
                                title={`${lang.name}: ${hasValue ? item.name[lang.code] : 'not translated'}`}
                                className={`rounded px-1.5 py-0.5 text-[10px] font-semibold uppercase ${
                                    hasValue ? 'bg-green-100 text-green-700' : 'bg-gray-100 text-gray-400'
                                }`}
                            >
                                {lang.code}
                            </span>
                        );
                    })}
                </div>
            </div>
        );
    },
}
```

---

### Step 12 — Update the Show / Detail Page

Use `current_locale` from shared Inertia props as the default active tab. Show all locales in tabs; non-translatable fields in a separate card.

```tsx
const { achievement, languages, current_locale } = usePage().props as any;

const defaultLocale = current_locale
    ?? languages.find((l) => l.is_default)?.code
    ?? 'en';

// Only render tabs for locales that have at least a name value
const tabLocales = languages.filter((l) => !!achievement.name?.[l.code]);

<Tabs defaultValue={defaultLocale}>
    {tabLocales.map((lang) => (
        <TabsContent key={lang.code} value={lang.code}>
            {/* name, slug, tag, description for this locale */}
        </TabsContent>
    ))}
</Tabs>

{/* Locale coverage badges in the card header */}
{languages.map((lang) => (
    <LocaleBadge key={lang.code} code={lang.code} filled={!!achievement.name?.[lang.code]} />
))}
```

---

### Step 13 — Update the Public Website API

The API already has `SetWebsiteLanguage` middleware on every route. It reads `X-Language-Id`, looks up the locale code, and calls `app()->setLocale()`. Your API controller just needs to return **flat resolved fields** using `translate()`.

```php
// ApiAchievementController.php

private function transform(Achievement $achievement): array
{
    $locale = app()->getLocale(); // already set by SetWebsiteLanguage middleware

    return [
        'id'          => $achievement->id,
        'uid'         => $achievement->uid,
        'name'        => $achievement->translate('name',        $locale),
        'slug'        => $achievement->translate('slug',        $locale),
        'tag'         => $achievement->translate('tag',         $locale),
        'description' => $achievement->translate('description', $locale),
        // non-translatable
        'grade'       => $achievement->grade,
        'status'      => $achievement->status,
        'media'       => $achievement->media ? [...] : null,
    ];
}
```

The website frontend sends the language on every request:

```js
// Website frontend fetch wrapper
const headers = new Headers(init?.headers);
headers.set('X-Language-Id', selectedLanguageId);  // e.g. 1 = English, 2 = Bengali
```

---

## Admin Panel Language Switching

The admin panel header has a language switcher. When switched:

1. `POST /switch-language { locale: 'bn' }` is sent
2. `Session::put('locale', 'bn')` stores the selection server-side
3. `SetLocale` middleware reads the session on every subsequent request
4. `app()->setLocale('bn')` is called before any controller runs
5. `HandleInertiaRequests` shares `current_locale: 'bn'` to all React pages
6. All `display_*` fields in resources resolve to Bengali automatically

**No frontend state management is needed for locale on the admin panel** — it is entirely session-driven and server-resolved.

---

## Modules to Apply This Pattern

Apply the same steps to every module that currently has a `language_id` column:

| Module | Translatable fields |
|---|---|
| Achievement | `name`, `slug`, `description`, `tag` |
| Blog | `title`, `slug`, `content`, `excerpt`, `tag` |
| Notice | `title`, `slug`, `description` |
| Syllabus | `title`, `description`, `objectives` |
| InstituteClass | `name`, `description` |
| Routine | `title`, `notes` |
| Testimonial | `name`, `designation`, `message` |
| Page | `title`, `slug`, `content`, `meta_title`, `meta_description` |
| Gallery | `title`, `description` |

For each module, the **backend steps** (Steps 2–9 and 13) are identical. The **frontend steps** (Steps 10–12) follow the same tab + `display_*` pattern.

---

## Quick Reference Checklist

Use this checklist when migrating a module:

### Backend
- [ ] Migration — remove `language_id`, change translatable columns to `jsonb`
- [ ] Model — add `use HasTranslations`, cast translatable fields as `array`, add slug auto-generator in `booted()`, remove `language()` relation
- [ ] ModelFilter — update `search()` to use `whereRaw('field->>? ILIKE ?', ...)`
- [ ] FormRequest — change translatable fields to `array` with `field.*` rules
- [ ] Resource — expose raw JSON objects + `display_*` resolved strings
- [ ] Service — add `assertSlugUnique()`, use `translate()` in activity logs, update `buildQuery()` search
- [ ] Controller — remove `AcademicSettingService` language options, pass `Language` model rows
- [ ] API Controller — add `transform()` method that calls `translate()` per field

### Frontend
- [ ] Create form — replace `language_id` select with `<Tabs>`, `defaultLocale` from `current_locale`
- [ ] Edit form — same tab structure, populate from `Record<string,string>` values
- [ ] Index — update TypeScript interface, add locale badges to Name column
- [ ] Show — add Multi-Language Content card with tabs, use `current_locale` as default tab

---

## Key Rules

1. **Never store translated text in plain string columns** — use `jsonb` for any field that shows on the website in multiple languages.
2. **Never filter by `language_id`** in modules migrated to JSONB — use `translate()` at read time instead.
3. **The default locale's name is always required** in form validation — other locales are optional.
4. **Slug uniqueness is enforced in the service layer** (`assertSlugUnique`) — it cannot be a DB `UNIQUE` index when stored inside JSONB.
5. **API responses must return flat strings** — the website frontend must never receive raw `{"en":..., "bn":...}` objects; always call `translate()` before returning.
6. **`current_locale` drives the admin panel tab default** — read it from shared Inertia props, not from `is_default` on the language list.