Inventory/docs/superpowers/plans/2026-03-12-json-to-tables-normalization.md

# JSON to Relational Tables Normalization — Implementation Plan

> **For agentic workers:** REQUIRED: Use superpowers:subagent-driven-development (if subagents available) or superpowers:executing-plans to implement this plan. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Replace all structural JSON columns (ModelType skeletons, Composant.structure, Piece.productIds) with proper relational tables, enabling referential integrity, queryability, and consistent data display.

**Architecture:** Three-phase migration following a dual-write/dual-read strategy to avoid breaking changes. Each phase creates new tables + entities, migrates existing data via a Doctrine migration, updates backend reads then writes, updates frontend, and finally drops the JSON column.

**Tech Stack:** Symfony 8, Doctrine ORM (PHP 8 attributes), PostgreSQL 16, API Platform, Nuxt 4 frontend

---

## Scope — What Migrates vs. What Stays

| JSON Column | Action | Reason |
|---|---|---|
| `ModelType.componentSkeleton` | **MIGRATE** → `skeleton_piece_requirements`, `skeleton_product_requirements`, `skeleton_subcomponent_requirements` | Template bill-of-materials needs FK integrity |
| `ModelType.pieceSkeleton` | **MIGRATE** → reuse `skeleton_product_requirements` table (piece types can require product types too) | Contains `customFields` (already in `custom_fields` table) AND `products` array (product type requirements for piece types) |
| `ModelType.productSkeleton` | **MIGRATE** → verify no structural data beyond `customFields`, then DROP | May contain `products` or other structural data — verify before dropping |
| `Composant.structure` | **MIGRATE** → `composant_piece_slots`, `composant_subcomponent_slots`, `composant_product_slots` | Instance-level selections need FK integrity |
| `Piece.productIds` | **MIGRATE** → `piece_products` join table | Simple M2M stored as JSON array |
| `CustomField.options` | **KEEP JSON** | Truly polymorphic per-field-type config (selectOptions, min/max, step) |
| `AuditLog.diff/snapshot` | **KEEP JSON** | Schemaless by nature — stores arbitrary entity change diffs |
| `Profile.roles` | **KEEP JSON** | Standard Symfony UserInterface pattern |

---

## New Tables Design

### Layer 1 — Skeleton Requirements (what a ModelType needs)

> **Note:** `skeleton_product_requirements` is shared by BOTH component and piece ModelTypes.
> A component type can require piece types + product types + subcomponent types.
> A piece type can require product types (e.g., lubricant for a bearing).
> Denormalized fields like `typePieceLabel` are intentionally omitted — resolved at read-time via FK.

```sql
-- Piece types required by a component ModelType
CREATE TABLE skeleton_piece_requirements (
    id VARCHAR(36) PRIMARY KEY,
    model_type_id VARCHAR(36) NOT NULL REFERENCES model_types(id) ON DELETE CASCADE,
    type_piece_id VARCHAR(36) NOT NULL REFERENCES model_types(id) ON DELETE CASCADE,
    position INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_skel_piece_req_model ON skeleton_piece_requirements(model_type_id);

-- Product types required by a component OR piece ModelType
CREATE TABLE skeleton_product_requirements (
    id VARCHAR(36) PRIMARY KEY,
    model_type_id VARCHAR(36) NOT NULL REFERENCES model_types(id) ON DELETE CASCADE,
    type_product_id VARCHAR(36) NOT NULL REFERENCES model_types(id) ON DELETE CASCADE,
    family_code VARCHAR(255),
    position INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_skel_prod_req_model ON skeleton_product_requirements(model_type_id);

-- Subcomponent types required by a component ModelType
CREATE TABLE skeleton_subcomponent_requirements (
    id VARCHAR(36) PRIMARY KEY,
    model_type_id VARCHAR(36) NOT NULL REFERENCES model_types(id) ON DELETE CASCADE,
    alias VARCHAR(255) NOT NULL,
    family_code VARCHAR(255) NOT NULL,
    type_composant_id VARCHAR(36) REFERENCES model_types(id) ON DELETE SET NULL,
    position INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_skel_sub_req_model ON skeleton_subcomponent_requirements(model_type_id);
```

> **Design decision — no `quantity` on skeleton requirements:** The skeleton defines WHICH types are needed, not how many. Quantity is an instance-level concern stored on `MachinePieceLink.quantity` (for machine context) or `composant_piece_slots.quantity` (for composant context). The current skeleton JSON has no `quantity` field on piece entries.

### Layer 2 — Composant Slots (instance-level selections)

> **Design decision — `quantity` on piece slots:** The current `Composant.structure.pieces[].definition` JSON does NOT contain a `quantity` field. The actual quantity for machine pieces comes from `MachinePieceLink.quantity`. However, we add `quantity` to `composant_piece_slots` as the natural place to store "how many of this piece type does this composant need" — it will default to 1 from migration but can be set going forward.

```sql
-- Actual piece selections for a composant instance
CREATE TABLE composant_piece_slots (
    id VARCHAR(36) PRIMARY KEY,
    composant_id VARCHAR(36) NOT NULL REFERENCES composants(id) ON DELETE CASCADE,
    type_piece_id VARCHAR(36) REFERENCES model_types(id) ON DELETE SET NULL,
    selected_piece_id VARCHAR(36) REFERENCES pieces(id) ON DELETE SET NULL,
    quantity INT NOT NULL DEFAULT 1,
    position INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_comp_piece_slot_composant ON composant_piece_slots(composant_id);
CREATE INDEX idx_comp_piece_slot_piece ON composant_piece_slots(selected_piece_id);

-- Actual subcomponent selections for a composant instance
CREATE TABLE composant_subcomponent_slots (
    id VARCHAR(36) PRIMARY KEY,
    composant_id VARCHAR(36) NOT NULL REFERENCES composants(id) ON DELETE CASCADE,
    alias VARCHAR(255),
    family_code VARCHAR(255),
    type_composant_id VARCHAR(36) REFERENCES model_types(id) ON DELETE SET NULL,
    selected_composant_id VARCHAR(36) REFERENCES composants(id) ON DELETE SET NULL,
    position INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_comp_sub_slot_composant ON composant_subcomponent_slots(composant_id);

-- Actual product selections for a composant instance
CREATE TABLE composant_product_slots (
    id VARCHAR(36) PRIMARY KEY,
    composant_id VARCHAR(36) NOT NULL REFERENCES composants(id) ON DELETE CASCADE,
    type_product_id VARCHAR(36) REFERENCES model_types(id) ON DELETE SET NULL,
    selected_product_id VARCHAR(36) REFERENCES products(id) ON DELETE SET NULL,
    family_code VARCHAR(255),
    position INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_comp_prod_slot_composant ON composant_product_slots(composant_id);
```

### Layer 3 — Piece-Product Join Table

```sql
CREATE TABLE piece_products (
    piece_id VARCHAR(36) NOT NULL REFERENCES pieces(id) ON DELETE CASCADE,
    product_id VARCHAR(36) NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    PRIMARY KEY (piece_id, product_id)
);
```

---

## Chunk 1: Phase 1 — Skeleton Requirements (ModelType)

### Task 1.1: Create Skeleton Entities

**Files:**
- Create: `src/Entity/SkeletonPieceRequirement.php`
- Create: `src/Entity/SkeletonProductRequirement.php`
- Create: `src/Entity/SkeletonSubcomponentRequirement.php`
- Modify: `src/Entity/ModelType.php`

- [ ] **Step 1: Create `SkeletonPieceRequirement` entity**

```php
<?php

namespace App\Entity;

use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
#[ORM\Table(name: 'skeleton_piece_requirements')]
#[ORM\HasLifecycleCallbacks]
class SkeletonPieceRequirement
{
    #[ORM\Id]
    #[ORM\Column(type: Types::STRING, length: 36)]
    private string $id;

    #[ORM\ManyToOne(targetEntity: ModelType::class, inversedBy: 'skeletonPieceRequirements')]
    #[ORM\JoinColumn(name: 'model_type_id', referencedColumnName: 'id', nullable: false, onDelete: 'CASCADE')]
    private ModelType $modelType;

    #[ORM\ManyToOne(targetEntity: ModelType::class)]
    #[ORM\JoinColumn(name: 'type_piece_id', referencedColumnName: 'id', nullable: false, onDelete: 'CASCADE')]
    private ModelType $typePiece;

    #[ORM\Column(type: Types::INTEGER, options: ['default' => 0])]
    private int $position = 0;

    #[ORM\Column(type: Types::DATETIME_IMMUTABLE)]
    private \DateTimeImmutable $createdAt;

    #[ORM\Column(type: Types::DATETIME_IMMUTABLE)]
    private \DateTimeImmutable $updatedAt;

    public function __construct()
    {
        $this->id = 'cl' . bin2hex(random_bytes(12));
    }

    // Getters & setters (id, modelType, typePiece, position, createdAt, updatedAt)

    #[ORM\PrePersist]
    public function onPrePersist(): void
    {
        $this->createdAt = new \DateTimeImmutable();
        $this->updatedAt = new \DateTimeImmutable();
    }

    #[ORM\PreUpdate]
    public function onPreUpdate(): void
    {
        $this->updatedAt = new \DateTimeImmutable();
    }

    // ... all standard getters/setters
}
```

- [ ] **Step 2: Create `SkeletonProductRequirement` entity** (same pattern with `VARCHAR(36)` IDs, `typeProduct` FK, `familyCode` varchar column; shared by component AND piece ModelTypes)

- [ ] **Step 3: Create `SkeletonSubcomponentRequirement` entity** (same pattern, with `alias`, `familyCode`, `typeComposant` nullable FK)

- [ ] **Step 4: Add OneToMany collections to `ModelType`**

```php
// In ModelType.php — add these properties + constructor init + getters

#[ORM\OneToMany(targetEntity: SkeletonPieceRequirement::class, mappedBy: 'modelType', cascade: ['persist', 'remove'], orphanRemoval: true)]
#[ORM\OrderBy(['position' => 'ASC'])]
private Collection $skeletonPieceRequirements;

#[ORM\OneToMany(targetEntity: SkeletonProductRequirement::class, mappedBy: 'modelType', cascade: ['persist', 'remove'], orphanRemoval: true)]
#[ORM\OrderBy(['position' => 'ASC'])]
private Collection $skeletonProductRequirements;

#[ORM\OneToMany(targetEntity: SkeletonSubcomponentRequirement::class, mappedBy: 'modelType', cascade: ['persist', 'remove'], orphanRemoval: true)]
#[ORM\OrderBy(['position' => 'ASC'])]
private Collection $skeletonSubcomponentRequirements;
```

- [ ] **Step 5: Run `make php-cs-fixer-allow-risky`**

- [ ] **Step 6: Commit**
```bash
git add src/Entity/SkeletonPieceRequirement.php src/Entity/SkeletonProductRequirement.php src/Entity/SkeletonSubcomponentRequirement.php src/Entity/ModelType.php
git commit -m "feat(skeleton) : add skeleton requirement entities for ModelType"
```

---

### Task 1.2: Create Doctrine Migration for Skeleton Tables + Data Migration

**Files:**
- Create: `migrations/VersionYYYYMMDDHHMMSS.php`

- [ ] **Step 1: Generate migration**
```bash
docker exec -u www-data php-inventory-apache php bin/console doctrine:migrations:diff
```

- [ ] **Step 2: Add data migration SQL to the `up()` method**

After the CREATE TABLE statements, add SQL to migrate existing JSON data:

```sql
-- Migrate componentSkeleton.pieces → skeleton_piece_requirements
INSERT INTO skeleton_piece_requirements (id, model_type_id, type_piece_id, position, created_at, updated_at)
SELECT
    'cl' || encode(gen_random_bytes(12), 'hex'),
    mt.id,
    (piece->>'typePieceId'),
    ordinality - 1,
    NOW(), NOW()
FROM model_types mt,
LATERAL jsonb_array_elements(mt.componentskeleton->'pieces') WITH ORDINALITY AS t(piece, ordinality)
WHERE mt.category = 'component'
  AND mt.componentskeleton IS NOT NULL
  AND mt.componentskeleton->'pieces' IS NOT NULL
  AND jsonb_array_length(mt.componentskeleton->'pieces') > 0;

-- Migrate componentSkeleton.products → skeleton_product_requirements (component types)
INSERT INTO skeleton_product_requirements (id, model_type_id, type_product_id, family_code, position, created_at, updated_at)
SELECT
    'cl' || encode(gen_random_bytes(12), 'hex'),
    mt.id,
    (product->>'typeProductId'),
    (product->>'familyCode'),
    ordinality - 1,
    NOW(), NOW()
FROM model_types mt,
LATERAL jsonb_array_elements(mt.componentskeleton->'products') WITH ORDINALITY AS t(product, ordinality)
WHERE mt.category = 'component'
  AND mt.componentskeleton IS NOT NULL
  AND mt.componentskeleton->'products' IS NOT NULL
  AND jsonb_array_length(mt.componentskeleton->'products') > 0;

-- Migrate pieceSkeleton.products → skeleton_product_requirements (piece types)
INSERT INTO skeleton_product_requirements (id, model_type_id, type_product_id, family_code, position, created_at, updated_at)
SELECT
    'cl' || encode(gen_random_bytes(12), 'hex'),
    mt.id,
    (product->>'typeProductId'),
    (product->>'familyCode'),
    ordinality - 1,
    NOW(), NOW()
FROM model_types mt,
LATERAL jsonb_array_elements(mt.pieceskeleton->'products') WITH ORDINALITY AS t(product, ordinality)
WHERE mt.category = 'piece'
  AND mt.pieceskeleton IS NOT NULL
  AND mt.pieceskeleton->'products' IS NOT NULL
  AND jsonb_array_length(mt.pieceskeleton->'products') > 0;

-- Migrate componentSkeleton.subcomponents → skeleton_subcomponent_requirements
INSERT INTO skeleton_subcomponent_requirements (id, model_type_id, alias, family_code, type_composant_id, position, created_at, updated_at)
SELECT
    'cl' || encode(gen_random_bytes(12), 'hex'),
    mt.id,
    COALESCE(sub->>'alias', ''),
    COALESCE(sub->>'familyCode', ''),
    NULLIF(sub->>'typeComposantId', ''),
    ordinality - 1,
    NOW(), NOW()
FROM model_types mt,
LATERAL jsonb_array_elements(mt.componentskeleton->'subcomponents') WITH ORDINALITY AS t(sub, ordinality)
WHERE mt.category = 'component'
  AND mt.componentskeleton IS NOT NULL
  AND mt.componentskeleton->'subcomponents' IS NOT NULL
  AND jsonb_array_length(mt.componentskeleton->'subcomponents') > 0;
```

- [ ] **Step 3: Run migration**
```bash
docker exec -u www-data php-inventory-apache php bin/console doctrine:migrations:migrate --no-interaction
```

- [ ] **Step 4: Verify data migrated correctly**
```bash
docker exec -u www-data php-inventory-apache php bin/console dbal:run-sql "SELECT COUNT(*) FROM skeleton_piece_requirements"
docker exec -u www-data php-inventory-apache php bin/console dbal:run-sql "SELECT COUNT(*) FROM skeleton_product_requirements"
docker exec -u www-data php-inventory-apache php bin/console dbal:run-sql "SELECT COUNT(*) FROM skeleton_subcomponent_requirements"
```

- [ ] **Step 5: Commit**
```bash
git add migrations/
git commit -m "feat(skeleton) : create skeleton requirement tables and migrate data from JSON"
```

---

### Task 1.3: Update ModelType to Expose Relations via API

**Files:**
- Modify: `src/Entity/ModelType.php`

- [ ] **Step 1: Add serialization groups to new collections**

```php
#[Groups(['modelType:read', 'modelType:structure'])]
#[ORM\OneToMany(...)]
private Collection $skeletonPieceRequirements;
// Same for product and subcomponent requirements
```

- [ ] **Step 2: Add a `getStructureFromRelations()` method** that returns the same shape as `getStructure()` but built from the new relations (for dual-read during transition)

```php
public function getStructureFromRelations(): array
{
    $structure = ['customFields' => [], 'pieces' => [], 'products' => [], 'subcomponents' => []];

    foreach ($this->skeletonPieceRequirements as $req) {
        $structure['pieces'][] = [
            'typePieceId' => $req->getTypePiece()->getId(),
        ];
    }
    foreach ($this->skeletonProductRequirements as $req) {
        $structure['products'][] = [
            'typeProductId' => $req->getTypeProduct()->getId(),
            'familyCode' => $req->getFamilyCode(),
        ];
    }
    foreach ($this->skeletonSubcomponentRequirements as $req) {
        $structure['subcomponents'][] = [
            'alias' => $req->getAlias(),
            'familyCode' => $req->getFamilyCode(),
            'typeComposantId' => $req->getTypeComposant()?->getId(),
        ];
    }

    return $structure;
}
```

- [ ] **Step 3: Update `getStructure()` to read from relations for ALL categories**

> **IMPORTANT:** Use `ModelCategory` enum, not string comparison. Piece types also have product requirements.

```php
use App\Enum\ModelCategory;

public function getStructure(): ?array
{
    return match ($this->category) {
        ModelCategory::COMPONENT => $this->getStructureFromRelations(),
        ModelCategory::PIECE => [
            'customFields' => [],
            'products' => array_map(fn($req) => [
                'typeProductId' => $req->getTypeProduct()->getId(),
                'familyCode' => $req->getFamilyCode(),
            ], $this->skeletonProductRequirements->toArray()),
        ],
        ModelCategory::PRODUCT => [
            'customFields' => [],
        ],
    };
}
```

- [ ] **Step 4: Create a `SkeletonStructureService` to handle writing skeleton requirements**

> The entity cannot resolve FKs (needs EntityManager). Create a service that handles `setStructure()` logic.

```php
// src/Service/SkeletonStructureService.php
class SkeletonStructureService
{
    public function __construct(private EntityManagerInterface $em) {}

    public function updateSkeletonRequirements(ModelType $modelType, array $structure): void
    {
        // Clear existing
        $modelType->getSkeletonPieceRequirements()->clear();
        $modelType->getSkeletonProductRequirements()->clear();
        $modelType->getSkeletonSubcomponentRequirements()->clear();

        // Create piece requirements
        foreach (($structure['pieces'] ?? []) as $i => $pieceData) {
            $req = new SkeletonPieceRequirement();
            $req->setModelType($modelType);
            $req->setTypePiece($this->em->getReference(ModelType::class, $pieceData['typePieceId']));
            $req->setPosition($i);
            $modelType->getSkeletonPieceRequirements()->add($req);
        }

        // Create product requirements (shared by component + piece types)
        foreach (($structure['products'] ?? []) as $i => $prodData) {
            $req = new SkeletonProductRequirement();
            $req->setModelType($modelType);
            $req->setTypeProduct($this->em->getReference(ModelType::class, $prodData['typeProductId']));
            $req->setFamilyCode($prodData['familyCode'] ?? null);
            $req->setPosition($i);
            $modelType->getSkeletonProductRequirements()->add($req);
        }

        // Create subcomponent requirements (component types only)
        foreach (($structure['subcomponents'] ?? []) as $i => $subData) {
            $req = new SkeletonSubcomponentRequirement();
            $req->setModelType($modelType);
            $req->setAlias($subData['alias'] ?? '');
            $req->setFamilyCode($subData['familyCode'] ?? '');
            if (!empty($subData['typeComposantId'])) {
                $req->setTypeComposant($this->em->getReference(ModelType::class, $subData['typeComposantId']));
            }
            $req->setPosition($i);
            $modelType->getSkeletonSubcomponentRequirements()->add($req);
        }
    }
}
```

- [ ] **Step 5: Run `make php-cs-fixer-allow-risky` + `make test`**

- [ ] **Step 6: Commit**
```bash
git add src/Entity/ModelType.php
git commit -m "feat(skeleton) : expose skeleton relations via API and update getStructure()"
```

---

### Task 1.4: Update Controllers That Read/Write Skeletons

**Files:**
- Modify: `src/Controller/MachineStructureController.php` (reads skeleton for piece quantity resolution)
- Modify: any controller that writes to ModelType skeleton

- [ ] **Step 1: Read `MachineStructureController.php` — identify all skeleton read points**

- [ ] **Step 2: Do NOT change `resolvePieceQuantity()` yet** — it reads `Composant.structure` JSON which is migrated in Phase 2. Keep the existing JSON-based implementation until Phase 2 is complete. Only update skeleton-related reads (e.g., ModelType `getStructure()` calls) in this phase.

- [ ] **Step 3: Update any API endpoint that WRITES to ModelType skeleton to use the new relation entities**

- [ ] **Step 4: Run `make test`**

- [ ] **Step 5: Commit**

---

### Task 1.5: Verify Skeleton Custom Fields Are Fully in CustomField Entities

**Files:**
- Check: `fixtures/data.sql` — compare skeleton `customFields` JSON entries with `custom_fields` table entries

- [ ] **Step 1: Write a verification SQL query**

```sql
-- Find skeleton customFields that DON'T have a corresponding CustomField entity
-- Handles both JSON formats: {key: "Name", value: {type: "text"}} and {name: "Name", type: "text"}
SELECT mt.id, mt.name, mt.category,
       COALESCE(cf_json->>'key', cf_json->>'name') as field_name,
       COALESCE(cf_json->'value'->>'type', cf_json->>'type') as field_type
FROM model_types mt,
LATERAL jsonb_array_elements(
    CASE mt.category
        WHEN 'component' THEN mt.componentskeleton->'customFields'
        WHEN 'piece' THEN mt.pieceskeleton->'customFields'
        WHEN 'product' THEN mt.productskeleton->'customFields'
    END
) AS cf_json
WHERE NOT EXISTS (
    SELECT 1 FROM custom_fields cf
    WHERE (cf.typecomposantid = mt.id OR cf.typepieceid = mt.id OR cf.typeproductid = mt.id)
      AND cf.name = COALESCE(cf_json->>'key', cf_json->>'name')
);
```

- [ ] **Step 2: If any missing → write a migration to create the missing CustomField entities from skeleton JSON**

- [ ] **Step 3: Commit if changes needed**

---

### Task 1.6: Drop Skeleton JSON Columns

**Files:**
- Create: `migrations/VersionYYYYMMDDHHMMSS.php`
- Modify: `src/Entity/ModelType.php`

- [ ] **Step 1: Remove `componentSkeleton`, `pieceSkeleton`, `productSkeleton` properties from ModelType entity**

- [ ] **Step 2: Generate migration to drop the columns**

```sql
ALTER TABLE model_types DROP COLUMN IF EXISTS componentskeleton;
ALTER TABLE model_types DROP COLUMN IF EXISTS pieceskeleton;
ALTER TABLE model_types DROP COLUMN IF EXISTS productskeleton;
```

- [ ] **Step 3: Remove `getStructureFromRelations()` (merge logic into `getStructure()`)**

- [ ] **Step 4: Run `make test` + `make php-cs-fixer-allow-risky`**

- [ ] **Step 5: Commit**

---

## Chunk 2: Phase 2 — Composant Structure Slots

### Task 2.1: Create Composant Slot Entities

**Files:**
- Create: `src/Entity/ComposantPieceSlot.php`
- Create: `src/Entity/ComposantSubcomponentSlot.php`
- Create: `src/Entity/ComposantProductSlot.php`
- Modify: `src/Entity/Composant.php`

- [ ] **Step 1: Create `ComposantPieceSlot` entity**

```php
<?php

namespace App\Entity;

use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity]
#[ORM\Table(name: 'composant_piece_slots')]
#[ORM\HasLifecycleCallbacks]
class ComposantPieceSlot
{
    #[ORM\Id]
    #[ORM\Column(type: Types::STRING, length: 36)]
    private string $id;

    #[ORM\ManyToOne(targetEntity: Composant::class, inversedBy: 'pieceSlots')]
    #[ORM\JoinColumn(name: 'composant_id', referencedColumnName: 'id', nullable: false, onDelete: 'CASCADE')]
    private Composant $composant;

    #[ORM\ManyToOne(targetEntity: ModelType::class)]
    #[ORM\JoinColumn(name: 'type_piece_id', referencedColumnName: 'id', nullable: true, onDelete: 'SET NULL')]
    private ?ModelType $typePiece = null;

    #[ORM\ManyToOne(targetEntity: Piece::class)]
    #[ORM\JoinColumn(name: 'selected_piece_id', referencedColumnName: 'id', nullable: true, onDelete: 'SET NULL')]
    private ?Piece $selectedPiece = null;

    #[ORM\Column(type: Types::INTEGER, options: ['default' => 1])]
    private int $quantity = 1;

    #[ORM\Column(type: Types::INTEGER, options: ['default' => 0])]
    private int $position = 0;

    #[ORM\Column(type: Types::DATETIME_IMMUTABLE)]
    private \DateTimeImmutable $createdAt;

    #[ORM\Column(type: Types::DATETIME_IMMUTABLE)]
    private \DateTimeImmutable $updatedAt;

    public function __construct()
    {
        $this->id = 'cl' . bin2hex(random_bytes(12));
    }

    // ... lifecycle callbacks + getters/setters
}
```

- [ ] **Step 2: Create `ComposantSubcomponentSlot` entity** (`VARCHAR(36)` IDs, alias, familyCode, typeComposant nullable FK, selectedComposant nullable FK)

- [ ] **Step 3: Create `ComposantProductSlot` entity** (`VARCHAR(36)` IDs, typeProduct nullable FK, selectedProduct nullable FK, `familyCode` varchar column — preserves `definition.familyCode` from JSON)

- [ ] **Step 4: Add OneToMany collections to `Composant` entity**

```php
#[ORM\OneToMany(targetEntity: ComposantPieceSlot::class, mappedBy: 'composant', cascade: ['persist', 'remove'], orphanRemoval: true)]
#[ORM\OrderBy(['position' => 'ASC'])]
private Collection $pieceSlots;

#[ORM\OneToMany(targetEntity: ComposantSubcomponentSlot::class, mappedBy: 'composant', cascade: ['persist', 'remove'], orphanRemoval: true)]
#[ORM\OrderBy(['position' => 'ASC'])]
private Collection $subcomponentSlots;

#[ORM\OneToMany(targetEntity: ComposantProductSlot::class, mappedBy: 'composant', cascade: ['persist', 'remove'], orphanRemoval: true)]
#[ORM\OrderBy(['position' => 'ASC'])]
private Collection $productSlots;
```

- [ ] **Step 5: Run `make php-cs-fixer-allow-risky`**

- [ ] **Step 6: Commit**

---

### Task 2.2: Create Migration + Migrate Composant Structure Data

**Files:**
- Create: `migrations/VersionYYYYMMDDHHMMSS.php`

- [ ] **Step 1: Generate migration**

- [ ] **Step 2: Add data migration SQL**

```sql
-- Migrate composant.structure.pieces → composant_piece_slots
-- Note: quantity defaults to 1 — the current JSON definition does NOT contain a quantity field.
-- The actual quantity comes from MachinePieceLink.quantity. The slot quantity column exists
-- for future use as the canonical place to store "how many of this piece type does this composant need".
INSERT INTO composant_piece_slots (id, composant_id, type_piece_id, selected_piece_id, quantity, position, created_at, updated_at)
SELECT
    'cl' || encode(gen_random_bytes(12), 'hex'),
    c.id,
    NULLIF(piece->'definition'->>'typePieceId', ''),
    NULLIF(piece->>'selectedPieceId', ''),
    1,
    ordinality - 1,
    NOW(), NOW()
FROM composants c,
LATERAL jsonb_array_elements(c.structure->'pieces') WITH ORDINALITY AS t(piece, ordinality)
WHERE c.structure IS NOT NULL
  AND c.structure->'pieces' IS NOT NULL
  AND jsonb_array_length(c.structure->'pieces') > 0;

-- Migrate composant.structure.subcomponents → composant_subcomponent_slots
INSERT INTO composant_subcomponent_slots (id, composant_id, alias, family_code, type_composant_id, selected_composant_id, position, created_at, updated_at)
SELECT
    'cl' || encode(gen_random_bytes(12), 'hex'),
    c.id,
    COALESCE(sub->'definition'->>'alias', ''),
    COALESCE(sub->'definition'->>'familyCode', ''),
    NULLIF(sub->'definition'->>'typeComposantId', ''),
    NULLIF(sub->>'selectedComponentId', ''),
    ordinality - 1,
    NOW(), NOW()
FROM composants c,
LATERAL jsonb_array_elements(c.structure->'subcomponents') WITH ORDINALITY AS t(sub, ordinality)
WHERE c.structure IS NOT NULL
  AND c.structure->'subcomponents' IS NOT NULL
  AND jsonb_array_length(c.structure->'subcomponents') > 0;

-- Migrate composant.structure.products → composant_product_slots (including familyCode)
INSERT INTO composant_product_slots (id, composant_id, type_product_id, selected_product_id, family_code, position, created_at, updated_at)
SELECT
    'cl' || encode(gen_random_bytes(12), 'hex'),
    c.id,
    NULLIF(prod->'definition'->>'typeProductId', ''),
    NULLIF(prod->>'selectedProductId', ''),
    prod->'definition'->>'familyCode',
    ordinality - 1,
    NOW(), NOW()
FROM composants c,
LATERAL jsonb_array_elements(c.structure->'products') WITH ORDINALITY AS t(prod, ordinality)
WHERE c.structure IS NOT NULL
  AND c.structure->'products' IS NOT NULL
  AND jsonb_array_length(c.structure->'products') > 0;
```

- [ ] **Step 3: Run migration + verify counts**

- [ ] **Step 4: Commit**

---

### Task 2.3: Update MachineStructureController to Read from Slot Tables

**Files:**
- Modify: `src/Controller/MachineStructureController.php`

This is the critical task — the controller currently reads `Composant.structure` JSON to:
1. Enrich structure with resolved piece data (`enrichStructureWithPieceData()`)
2. Resolve piece quantities (`resolvePieceQuantity()`)
3. Normalize composant data (`normalizeComposant()`)

- [ ] **Step 1: Read the full `MachineStructureController.php`**

- [ ] **Step 2: Update `normalizeComposant()` to build the `structure` response from slot relations**

```php
private function normalizeComposant(Composant $composant): array
{
    $pieces = [];
    foreach ($composant->getPieceSlots() as $slot) {
        $pieceData = [
            'typePieceId' => $slot->getTypePiece()?->getId(),
            'quantity' => $slot->getQuantity(),
            'selectedPieceId' => $slot->getSelectedPiece()?->getId(),
        ];
        if ($slot->getSelectedPiece()) {
            $pieceData['resolvedPiece'] = $this->normalizePiece($slot->getSelectedPiece());
        }
        $pieces[] = $pieceData;
    }

    $subcomponents = [];
    foreach ($composant->getSubcomponentSlots() as $slot) {
        $subcomponents[] = [
            'alias' => $slot->getAlias(),
            'familyCode' => $slot->getFamilyCode(),
            'typeComposantId' => $slot->getTypeComposant()?->getId(),
            'selectedComponentId' => $slot->getSelectedComposant()?->getId(),
        ];
    }

    $products = [];
    foreach ($composant->getProductSlots() as $slot) {
        $products[] = [
            'typeProductId' => $slot->getTypeProduct()?->getId(),
            'familyCode' => $slot->getFamilyCode(),
            'selectedProductId' => $slot->getSelectedProduct()?->getId(),
        ];
    }

    return [
        // ... existing fields (id, name, reference, prix, etc.)
        'structure' => [
            'pieces' => $pieces,
            'subcomponents' => $subcomponents,
            'products' => $products,
        ],
        // ... customFields, customFieldValues
    ];
}
```

- [ ] **Step 3: Update `resolvePieceQuantity()` to use slot relations**

```php
private function resolvePieceQuantity(MachinePieceLink $pieceLink): int
{
    $parentLink = $pieceLink->getParentLink();
    if (!$parentLink) {
        return $pieceLink->getQuantity();
    }

    // Match on the specific selected piece — avoids ambiguity when
    // multiple slots share the same typePiece
    $composant = $parentLink->getComposant();
    $piece = $pieceLink->getPiece();

    foreach ($composant->getPieceSlots() as $slot) {
        if ($slot->getSelectedPiece()?->getId() === $piece->getId()) {
            return $slot->getQuantity();
        }
    }

    return $pieceLink->getQuantity();
}
```

- [ ] **Step 4: Remove `enrichStructureWithPieceData()` (no longer needed — piece data resolved inline in `normalizeComposant()`)**

- [ ] **Step 5: Run `make test`**

- [ ] **Step 6: Commit**

---

### Task 2.4: Update PATCH Structure Endpoint to Write to Slot Tables

**Files:**
- Modify: `src/Controller/MachineStructureController.php`

- [ ] **Step 1: Identify where `Composant.structure` is written/updated in the controller**

- [ ] **Step 2: Update any code that writes to `composant->setStructure(...)` to instead create/update slot entities**

- [ ] **Step 3: Run `make test`**

- [ ] **Step 4: Commit**

---

### Task 2.5: Update Frontend to Handle New Structure Format

**Files:**
- Modify: `frontend/app/composables/useMachineHierarchy.ts` — `buildMachineHierarchyFromLinks()`
- Modify: `frontend/app/shared/utils/structureDisplayUtils.ts`
- Modify: `frontend/app/shared/utils/structureSelectionUtils.ts`

**Note:** The API response shape for `structure` stays the same (pieces/subcomponents/products arrays), so frontend changes should be minimal. The main change is that `path` fields are removed and `resolvedPiece` is now always populated inline.

- [ ] **Step 1: Read the frontend files that consume structure data**

- [ ] **Step 2: Update `buildMachineHierarchyFromLinks()` if it references `path` fields**

- [ ] **Step 3: Update `structureDisplayUtils.ts` if it depends on old JSON shape**

- [ ] **Step 4: Update `structureSelectionUtils.ts` — `collectStructureSelections()` to use new format**

- [ ] **Step 5: Run `npm run lint:fix` + `npx nuxi typecheck`**

- [ ] **Step 6: Commit in frontend submodule, then update pointer in main repo**

---

### Task 2.6: Drop Composant.structure JSON Column

**Files:**
- Modify: `src/Entity/Composant.php`
- Create: `migrations/VersionYYYYMMDDHHMMSS.php`

- [ ] **Step 1: Remove `structure` property from Composant entity**

- [ ] **Step 2: Generate and run migration**

```sql
ALTER TABLE composants DROP COLUMN IF EXISTS structure;
```

- [ ] **Step 3: Run `make test`**

- [ ] **Step 4: Commit**

---

## Chunk 3: Phase 3 — Piece.productIds → Join Table

### Task 3.1: Create Join Table Entity + Migration

**Files:**
- Modify: `src/Entity/Piece.php`
- Modify: `src/Entity/Product.php`
- Create: `migrations/VersionYYYYMMDDHHMMSS.php`

- [ ] **Step 1: Add ManyToMany relation to `Piece` entity with explicit JoinColumn annotations**

> Must use explicit column names — Doctrine defaults would NOT match the migration SQL.

```php
#[ORM\ManyToMany(targetEntity: Product::class, inversedBy: 'pieces')]
#[ORM\JoinTable(name: 'piece_products')]
#[ORM\JoinColumn(name: 'piece_id', referencedColumnName: 'id', onDelete: 'CASCADE')]
#[ORM\InverseJoinColumn(name: 'product_id', referencedColumnName: 'id', onDelete: 'CASCADE')]
private Collection $products;
```

- [ ] **Step 2: Add inverse side to `Product` entity**

```php
#[ORM\ManyToMany(targetEntity: Piece::class, mappedBy: 'products')]
private Collection $pieces;
```

- [ ] **Step 3: Generate migration**

- [ ] **Step 4: Add data migration SQL to populate join table from JSON**

```sql
INSERT INTO piece_products (piece_id, product_id)
SELECT DISTINCT p.id, jsonb_array_elements_text(p.productids)
FROM pieces p
WHERE p.productids IS NOT NULL
  AND jsonb_array_length(p.productids) > 0;
```

- [ ] **Step 5: Run migration + verify**

- [ ] **Step 6: Commit**

---

### Task 3.2: Update Piece Entity to Use Relation

**Files:**
- Modify: `src/Entity/Piece.php`

- [ ] **Step 1: Update `getProductIds()` to return IDs from the relation**

```php
public function getProductIds(): array
{
    return $this->products->map(fn(Product $p) => $p->getId())->toArray();
}
```

- [ ] **Step 2: Remove `setProductIds()` — replace with `addProduct()`/`removeProduct()` methods**

- [ ] **Step 3: Update `setProduct()` to also add to the collection**

- [ ] **Step 4: Run `make test`**

- [ ] **Step 5: Commit**

---

### Task 3.3: Update Controllers + Drop JSON Column

**Files:**
- Modify: any controller that reads/writes `Piece.productIds`
- Create: `migrations/VersionYYYYMMDDHHMMSS.php`

- [ ] **Step 1: Search for all usages of `productIds` / `getProductIds` / `setProductIds` in controllers**

- [ ] **Step 2: Update each to use the new relation**

- [ ] **Step 3: Remove `productIds` JSON column from entity**

- [ ] **Step 4: Generate migration to drop column**

```sql
ALTER TABLE pieces DROP COLUMN IF EXISTS productids;
```

- [ ] **Step 5: Run `make test`**

- [ ] **Step 6: Commit**

---

## Chunk 4: Phase 4 — Frontend Consolidation + Test Suite

### Task 4.1: Update Frontend Types

**Files:**
- Modify: `frontend/app/shared/types/` (if type definitions reference structure/skeleton JSON shapes)

- [ ] **Step 1: Search frontend for all references to `structure.pieces`, `structure.subcomponents`, `structure.products`, `skeleton`, `productIds`**

- [ ] **Step 2: Update types to reflect the new API response shapes (should be minimal since we preserved response format)**

- [ ] **Step 3: Run `npm run lint:fix` + `npx nuxi typecheck` + `npm run build`**

- [ ] **Step 4: Commit in submodule + update pointer**

---

### Task 4.2: Update and Expand Backend Tests

**Files:**
- Modify: `tests/Api/Entity/ModelTypeTest.php` (or create if missing)
- Modify: `tests/Api/Entity/ComposantTest.php`
- Modify: `tests/Api/Entity/PieceTest.php`
- Modify: `tests/Api/Entity/MachineStructureTest.php` (or create)

- [ ] **Step 1: Write test for skeleton requirements CRUD via ModelType API**

```php
public function testCreateModelTypeWithSkeletonRequirements(): void
{
    $client = $this->createAdminClient();

    // Create a piece ModelType first
    // Create a component ModelType with skeleton requirements
    // GET the ModelType and verify structure is returned from relations
}
```

- [ ] **Step 2: Write test for composant slot creation and retrieval**

```php
public function testComposantStructureFromSlots(): void
{
    // Create composant with slots
    // GET machine structure
    // Verify pieces/subcomponents/products are returned correctly
}
```

- [ ] **Step 3: Write test for piece-product relation**

```php
public function testPieceProductRelation(): void
{
    // Create piece with products
    // Verify getProductIds() returns correct IDs
    // Verify products appear in piece API response
}
```

- [ ] **Step 4: Run full test suite: `make test`**

- [ ] **Step 5: Commit**

---

### Task 4.3: Update Fixtures

**Files:**
- Modify: `fixtures/data.sql`

- [ ] **Step 1: Add INSERT statements for the new tables matching existing JSON data**

- [ ] **Step 2: Remove JSON values from the fixture SQL for dropped columns**

- [ ] **Step 3: Verify fixtures load cleanly**
```bash
docker exec -u www-data php-inventory-apache php bin/console doctrine:fixtures:load --no-interaction
```

- [ ] **Step 4: Commit**

---

## Summary — Execution Order

| Phase | What | New Tables | JSON Columns Dropped |
|---|---|---|---|
| 1 | Skeleton Requirements | `skeleton_piece_requirements`, `skeleton_product_requirements`, `skeleton_subcomponent_requirements` | `componentSkeleton`, `pieceSkeleton`, `productSkeleton` |
| 2 | Composant Structure | `composant_piece_slots`, `composant_subcomponent_slots`, `composant_product_slots` | `structure` |
| 3 | Piece Products | `piece_products` | `productIds` |
| 4 | Frontend + Tests + Fixtures | — | — |

**Total new tables:** 7
**Total JSON columns removed:** 5
**JSON columns kept:** `AuditLog.diff`, `AuditLog.snapshot`, `CustomField.options`, `Profile.roles` (all legitimate JSON use cases)

---

## Risks & Mitigations

| Risk | Mitigation |
|---|---|
| Data loss during migration | SQL migrations use `INSERT ... SELECT` — original JSON untouched until explicit DROP. JSON columns only dropped AFTER all reads/writes are migrated and verified. |
| Frontend breaks during transition | API response shape preserved — `structure` key still returns same format, built from relations |
| Performance regression (N+1 queries) | Use `fetch: 'EAGER'` or explicit JOINs in DQL for slot collections |
| Existing tests fail | Run `make test` after each task — fix before proceeding |
| Composant.structure has edge cases (empty arrays, null entries) | Migration SQL uses `COALESCE`/`NULLIF` + `WHERE` guards |
| Phase ordering dependency | Phase 1 (skeletons) must NOT reference Phase 2 tables. `resolvePieceQuantity()` stays JSON-based until Phase 2 slot tables exist. |
| Denormalized labels lost (typePieceLabel, typeProductLabel) | Intentional — these are resolved at read-time via FK joins. No data loss, just a read pattern change. |

---

## Important Design Decisions

1. **No `quantity` on skeleton requirements:** The skeleton defines WHICH types are needed, not how many. Current JSON data confirms no quantity field exists in skeleton entries. Quantity is instance-level only.
2. **`quantity` on composant_piece_slots:** Will be 1 for all migrated data (current JSON has no quantity either). Exists as the future canonical location for "how many of this piece does this composant need." Real machine-level quantities remain on `MachinePieceLink.quantity`.
3. **`skeleton_product_requirements` shared across categories:** Both component and piece ModelTypes can require product types. Single table with `model_type_id` FK handles both.
4. **Denormalized labels dropped:** `typePieceLabel`, `typeProductLabel` etc. from JSON are not migrated — they're resolved at read-time via FK to the ModelType's `name` field.
5. **`SkeletonStructureService` for writes:** Entity can't resolve FKs (no access to EntityManager). A dedicated service handles creating requirement entities from array input.