# build-database — Database layer generator

> **Builds:** migrations + models (+ translation models) + factories + seeders, for one feature/table set.
> **Runs AFTER** [`build-schema.md`](build-schema.md) — the schema must already be **design-led, reviewed, and approved**.
> **Source of truth (no duplication):**
> - Column → Blueprint/Faker mapping & JSON shape: [`../schema-json-contract.md`](../schema-json-contract.md)
> - This file owns: **Model / Translation model / Factory / Seeder** generation, exactly in base's conventions.
> **Consumed by:** the per-API plan (runs this **first** after the gate), and the setup pipeline.

---

## 0) Prerequisite — Developer review GATE (hard stop)

This builder **must not run** until the developer has reviewed the schema produced by [`build-schema.md`](build-schema.md).

1. The schema lives in `database/schema/<feature>.json` (per `features.json`) and is editable on `/schema-designer`.
2. **STOP and wait for the developer to confirm** the tables, columns, types, relations, indexes, `timestamps`/`soft_deletes`, translatable fields, the **reuse/alter/new** map, and the **files** decision.
3. Only after explicit approval → proceed to generation. Never infer-and-generate past an unreviewed schema.
4. **On resume (after "كمل"/"approved"): RE-READ `database/schema/<feature>.json` + `features.json` from disk and treat the on-disk file as the single source of truth.** The developer almost always edits the schema on `/schema-designer` during the GATE — the saved file wins over anything proposed before the stop. Diff it against your pre-gate version, honor every change (added/removed/renamed columns, types, relations, reuse/alter/new flags), and build from the current file — never from memory.

Inputs for **real** seed/factory data: `docs/project/analysis.md` + `docs/project/brand-identity.md`. If a value isn't derivable, use realistic domain data (Arabic for the `ar` locale) — never `lorem`/English placeholders for Arabic fields.

---

## 0.5) Respect the reuse / alter / new classification

The reviewed schema marks each table (from [`build-schema.md`](build-schema.md)). Generate accordingly — **do not blindly create every table**:

- **REUSE** (table marked `external: true`, e.g. `users`, `otps`, `media`, `pages`, `notifications`, `contact_messages`) → **do NOT create a migration/model**. Only add the relation/FK from the new tables to it.
- **ALTER** (existing table needs new columns) → generate an **ALTER migration** (`Schema::table('users', …)` adding the new columns) — never recreate the table or its model; just extend `$fillable`/`$casts` on the existing model.
- **NEW** → full generation (migration + model + factory + seeder) as below.
- **Files/images** → per the schema's decision: either wire **Spatie media** collections on the model (`registerMediaCollections`, no table) or generate the dedicated `<entity>_files` table + model. Never skip file representation.

---

## 1) Resolve generation order

From the schema `relations`, generate **reference tables first** (tables with no outgoing FK), then dependents. This guarantees migrations run and factories can pull real FK values from existing rows.

---

## 2) Migration

Follow [`../schema-json-contract.md`](../schema-json-contract.md) §3–4 for the `type → Blueprint` mapping, flag order (`unsigned → nullable → unique → default → index → comment`), and FK rules. Plus base placement rules:

- Migrations are grouped by domain subfolder: `database/migrations/<domain>/...` (mirror existing folders like `settings/`, `more_pages/`).
- `id()` first, columns in order, FKs via `foreignId()->constrained('<ref>')->on{Delete|Update}(...)`, then `timestamps()` and `softDeletes()` per the table flags.
- **SoftDeletes ⇒ no DB `unique()`.** On a soft-deletable table, do **not** put a hard `unique()` on columns like `email`/`phone` — a trashed row keeps occupying the value and blocks re-registration at the DB level. Use `->index()` instead and enforce uniqueness in the Form Request, soft-delete-aware (`Rule::unique(...)->whereNull('deleted_at')` — see `build-api.md` rule #3).

### Translatable tables (when the entity has translatable fields)

Base uses **Astrotomic Translatable** (a separate `*_translations` table), NOT JSON columns. For a translatable model `Foo` with translated attributes `[title, body]`:

- Base table `foos`: only the **non-translatable** columns.
- Translation table `foo_translations`:
  ```php
  Schema::create('foo_translations', function (Blueprint $table) {
      $table->id();
      $table->foreignId('foo_id')->constrained()->onDelete('cascade');
      $table->string('locale')->index();
      $table->string('title');
      $table->text('body')->nullable();
      $table->unique(['foo_id', 'locale']);
      $table->timestamps();   // base translation tables DO keep timestamps
  });
  ```

---

## 3) Model

Pick the variant from the schema/feature flags. Mirror base exactly.

### 3a) Regular entity (no login) — the common case

```php
namespace App\Models;

use App\Traits\Models\BaseModelTrait;
use App\Traits\Models\BaseFileWithTranslations;   // ONLY if translatable + has files
use App\Traits\Models\CanRetrieve;                // ONLY if soft_deletes + restore
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\SoftDeletes;     // ONLY if soft_deletes
use Spatie\MediaLibrary\HasMedia;                 // MANDATORY (see note)

class {{Model}} extends \Illuminate\Database\Eloquent\Model implements HasMedia
{
    use BaseModelTrait, HasFactory; // + SoftDeletes, CanRetrieve, BaseFileWithTranslations as needed

    protected $fillable = [/* all columns except id/timestamps and translated attributes */];

    public $translatedAttributes = [/* translated columns — ONLY if translatable */];

    protected $casts = [/* datetimes => 'datetime', booleans => 'boolean', enums => EnumClass::class */];

    protected $attributes = [/* sane non-null defaults, e.g. 'is_active' => true */];

    protected const FILES            = [/* file/image column names, e.g. 'image' */];
    protected const UPLOAD_DIRECTORY = '{{entity_plural_snake}}';
    public    const RELATIONS        = [/* relation method names for eager-load in show()/edit() */];

    public const EXPORT_COLUMNS = [
        ['key' => 'id',   'label' => 'admin/main.id'],
        // ... only columns explicitly chosen for export
    ];

    // relations from schema `relations` (belongsTo/hasMany/...)

    // Override ONLY when a filter must be equality/scope/range instead of LIKE
    protected function applyColumnFilter($query, $column, $value): void
    {
        $query->where($column, 'like', '%' . $value . '%');
    }
}
```

> **`implements HasMedia` is mandatory.** `BaseModelTrait` pulls in `InteractsWithMedia`, which registers a Spatie `deleting` listener type-hinted `HasMedia`. Omitting it makes any (soft/hard) delete throw `Argument #1 must be of type HasMedia`.

**Trait selection:**
- `BaseModelTrait` → always (brings `GeneralTrait`, `InteractsWithMedia`, `FilterableTrait`, `HasDynamicRelations`, `ModelHasCacheTrait`).
- `BaseFileWithTranslations` → when the model is **both** translatable **and** has file columns (resolves the file-vs-translation `getAttribute`/`setAttribute` conflict). If translatable but no files, use the translatable trait alone per base; if files but not translatable, `BaseModelTrait` covers it.
- `SoftDeletes` (+ `CanRetrieve`) → when `soft_deletes:true`. `CanRetrieve` enables the "Retrieve deleted" filter and validates that every `RELATIONS` target is also soft-deletable.

### 3b) Auth-style entity (with login)

Extends `Illuminate\Foundation\Auth\User as Authenticatable`; uses `BaseAuthModelTrait, HasFactory, SoftDeletes, CanRetrieve, HasApiTokens`; adds `$hidden = ['password','remember_token']`, `password => 'hashed'` cast, `$availableNotificationTypes`, and the `is_blocked` handling in `applyColumnFilter`. (Full template: see the auth section retained in [`build-dashboard-crud.md`](build-dashboard-crud.md) for the controller/service side.)

### Rules
- Constants `UPLOAD_DIRECTORY`, `FILES`, `RELATIONS`, `EXPORT_COLUMNS` are required by the base service contract.
- `$casts` enforces types at the model layer — never rely on string coercion.
- **Money & time:** store money as **integer minor units** (or `decimal:2`) — never `float`; format via an accessor/cast. Timestamps stay **UTC** in the DB and are localized on display. Don't scatter currency/timezone math across services.
- **Enums used by the API** get a translated `label()` via the base's `App\Traits\Enums\GeneralEnumTrait` (every enum already uses it; adds `toValueLabel()` → `{value,label}`, `options()`, `values()`, `labels()`, `forSelect()`). Set `const PATH = '<lang-file>'` on the enum so `label()` resolves to `<lang-file>.<value>` (defaults to `enums.<value>`); mirror `LoginType` (`PATH = 'api/auth'`). The API Resource emits the enum via `->toValueLabel()`, and validation uses `Rule::enum(...)`/`<Enum>::values()` — see `build-api.md` rules #2/#8.
- **Derived/computed attributes** the API needs (e.g. `is_subscribed`, `has_used_free_plan`, `trusted_access`) live as **accessors** on the model (`Attribute::make(get: …)` from relations/state), so Resources just read them — never computed ad-hoc in the controller.
- **File columns** are declared in `FILES` and handled by `BaseFilesTrait` — assigning the uploaded file via `create/update($data)` uploads it automatically (no manual save in services).
- Custom scopes (`scopeStatus`, …) auto-resolve via `FilterableTrait` when the filter `column` isn't a real table column.
- **Translated columns are NOT real table columns** → to filter/search them add a scope using `whereTranslationLike`, e.g. (from base `Slider`):
  ```php
  public function scopeTitle($query, $value) {
      return $query->whereTranslationLike('title', '%' . $value . '%');
  }
  ```

---

## 4) Translation model (translatable entities only)

```php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class {{Model}}Translation extends Model
{
    protected $fillable = [/* the translated attributes, e.g. 'title', 'body' */];
    // keep default timestamps = true (base translation tables have timestamps)
}
```

---

## 5) Factory — REAL data

```php
namespace Database\Factories;

use Illuminate\Database\Eloquent\Factories\Factory;

class {{Model}}Factory extends Factory
{
    public function definition(): array
    {
        return [/* per-column values */];
    }
}
```

**Rules:**
- Use the global `fake()` helper. Apply heuristics from [`../schema-json-contract.md`](../schema-json-contract.md) §3 (`email→safeEmail`, `name→name`, `phone→PhoneNormalizer`, `price/amount→randomFloat`, `*_id→RelatedModel::inRandomOrder()->first()->id`).
- **FKs:** pull from existing rows (`RelatedModel::inRandomOrder()->first()->key`) — reference tables are seeded first (step 1).
- **Translatable fields:** create the parent then its translations per locale; **Arabic locale = real Arabic text** sourced from `analysis.md`/`brand-identity.md`, never English/lorem.
- Use project utilities where they exist (`PhoneNormalizer`, etc.) and spread `created_at` over time when stats need realistic distributions.

---

## 6) Seeder + DatabaseSeeder hook

```php
namespace Database\Seeders\{{Model}};

use App\Models\{{Model}};
use Illuminate\Database\Seeder;

class {{Model}}Seeder extends Seeder
{
    public function run(): void
    {
        {{Model}}::factory()->count({{SEED_ROWS}})->create();
    }
}
```

Register in `database/seeders/DatabaseSeeder.php::run()` via `$this->call([... {{Model}}Seeder::class]);` in dependency order.

**Rules:**
- **A seeder for EVERY table/model** the project has — none skipped.
- Seeder nested per entity: `database/seeders/{Model}/{Model}Seeder.php`.
- Seed data must be **real/representative** of the project (from `analysis.md`), not generic — this is what makes the dashboard demo meaningful.
- **Fully interconnected (relational) data:** seed in **dependency order**; child rows reference **existing** parent rows (`Parent::inRandomOrder()->first()`), not random ints — so every FK resolves and the graph is coherent (users↔debts↔payments↔links, etc.).
- **Adapt the EXISTING base seeders to the new project's identity** — don't just add new ones. Update the content of the inherited seeders to match `brand-identity.md`: `SettingSeeder` (app name / colors / logo / contact / VAT…), `PageSeeder` (terms / privacy / about text), any slider/FAQ/intro content. The shipped base demo content must not survive into the new project.
- For large counts (> 200 with relations) use bulk `DB::table()->insert($chunk)`.
- Do **not** add per-CRUD permission rows — `database/seeders/Admin/PermissionSeeder.php` auto-discovers every `admin.*` route.
- After wiring: `php artisan migrate:fresh --seed` must run **clean**, and every model has rows with resolved relations.

---

## 7) Verify (definition of done)

```
php artisan migrate:fresh --seed        # runs clean, in dependency order
```
- Each model: `$fillable`, `$casts`, constants present; relations resolve; `implements HasMedia`.
- Translatable: `$model->title` resolves per `app()->getLocale()`; `*_translations` rows exist for every locale in `languages()`.
- Factory/seeder produce **real** Arabic+English data; FK columns point to existing rows.
- **Non-interactive** spot-check (never open an interactive REPL — see `verify.md`): `php artisan tinker --execute="\$m = \App\Models\<Entity>::factory()->create(); \$m->delete(); \$m->restore(); dump('ok');"` — or a tiny feature test — confirms create + soft delete + restore without errors.

---

## References (single source — do not duplicate here)
- Schema JSON shape + type/flag/FK mapping → [`../schema-json-contract.md`](../schema-json-contract.md)
- Dashboard CRUD on top of these models → [`build-dashboard-crud.md`](build-dashboard-crud.md)
- API on top of these models → [`build-api.md`](build-api.md)