feat(catalog) : M7 — migration table storage (FK site/storage_type, unicité métier RG-7.01, états JSONB RG-7.04) (ERP-211)

migrations/Version20260629120000.php

@@ -0,0 +1,136 @@
+<?php
+
+declare(strict_types=1);
+
+namespace DoctrineMigrations;
+
+use App\Shared\Infrastructure\Database\ColumnCommentsCatalog;
+use Doctrine\DBAL\Schema\Schema;
+use Doctrine\Migrations\AbstractMigration;
+
+/**
+ * M7 — Stockage (ERP-211) : creation du schema BDD de la table `storage`.
+ *
+ * Un stockage = 1 site + 1 type de stockage (referentiel storage_type du M6) + 1
+ * numero, avec des etats multi-valeur (JSONB), soft-delete prepare et colonnes
+ * Timestampable/Blamable (spec-back § 3.2).
+ *
+ * Contraintes metier portees ici :
+ *  - RG-7.01 : unicite du couple (site, type, numero) parmi les stockages ACTIFS,
+ *    via l'index UNIQUE partiel uq_storage_site_type_numero_active (WHERE deleted_at
+ *    IS NULL) — un numero peut etre reutilise apres soft-delete.
+ *  - RG-7.04 : au moins un etat, via chk_storage_states_not_empty
+ *    (jsonb_array_length(states) >= 1). Comme pour product.states (M6), PAS de
+ *    DEFAULT '[]'::jsonb : un tableau vide violerait ce CHECK ; la colonne est
+ *    toujours renseignee par l'app (Processor/ORM).
+ *
+ * Namespace racine `DoctrineMigrations` (regle ABSOLUE n°11) et NON modulaire : la
+ * table storage porte des FK cross-module (site, storage_type, user). Le tri par
+ * timestamp au sein du namespace racine garantit l'ordre apres la creation de ces
+ * tables sur base vide ; un namespace modulaire trierait par FQCN alphabetique et
+ * casserait `make db-reset` (cf. Version20260625110000 pour le M6).
+ *
+ * Convention IDs (spec § 2.2) : `INT GENERATED BY DEFAULT AS IDENTITY`,
+ * horodatages `TIMESTAMP(0) WITHOUT TIME ZONE` (le TimestampableBlamableTrait mappe
+ * `datetime_immutable`). Chaque colonne porte son `COMMENT ON COLUMN` (regle n°12).
+ *
+ * NB schema:update (test-db-setup) : `storage` sera mappee en ORM au ticket suivant
+ * (entite Storage). D'ici la, `schema:update --force` la drope sur la base de TEST
+ * uniquement (sans impact : aucun test ne la reference encore, et dev/prod ne lancent
+ * jamais schema:update). Sa description sera ajoutee a ColumnCommentsCatalog au ticket
+ * entite (comme product / weighing_ticket).
+ */
+final class Version20260629120000 extends AbstractMigration
+{
+    public function getDescription(): string
+    {
+        return 'ERP-211 (M7) : creation de la table storage (FK site + storage_type, unicite metier partielle RG-7.01, etats JSONB RG-7.04, soft-delete + Timestampable/Blamable).';
+    }
+
+    public function up(Schema $schema): void
+    {
+        $this->addSql(<<<'SQL'
+            CREATE TABLE storage (
+                id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
+                site_id INT NOT NULL,
+                storage_type_id INT NOT NULL,
+                numero VARCHAR(50) NOT NULL,
+                -- Pas de DEFAULT : un tableau vide violerait chk_storage_states_not_empty
+                -- (RG-7.04). La colonne est toujours renseignee par l'app (Processor/ORM).
+                states JSONB NOT NULL,
+                deleted_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL,
+                created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
+                updated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
+                created_by INT DEFAULT NULL,
+                updated_by INT DEFAULT NULL,
+                PRIMARY KEY (id),
+                CONSTRAINT chk_storage_states_not_empty
+                    CHECK (jsonb_array_length(states) >= 1),
+                CONSTRAINT fk_storage_site
+                    FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE RESTRICT,
+                CONSTRAINT fk_storage_storage_type
+                    FOREIGN KEY (storage_type_id) REFERENCES storage_type (id) ON DELETE RESTRICT,
+                CONSTRAINT fk_storage_created_by
+                    FOREIGN KEY (created_by) REFERENCES "user" (id) ON DELETE SET NULL,
+                CONSTRAINT fk_storage_updated_by
+                    FOREIGN KEY (updated_by) REFERENCES "user" (id) ON DELETE SET NULL
+            )
+            SQL);
+
+        // RG-7.01 : unicite (site, type, numero) parmi les stockages actifs uniquement
+        // (index partiel) — un numero redevient disponible apres soft-delete.
+        $this->addSql('CREATE UNIQUE INDEX uq_storage_site_type_numero_active ON storage (site_id, storage_type_id, numero) WHERE deleted_at IS NULL');
+        $this->addSql('CREATE INDEX idx_storage_site ON storage (site_id)');
+        $this->addSql('CREATE INDEX idx_storage_storage_type ON storage (storage_type_id)');
+        $this->addSql('CREATE INDEX idx_storage_deleted_at ON storage (deleted_at)');
+        $this->addSql('CREATE INDEX idx_storage_created_by ON storage (created_by)');
+        $this->addSql('CREATE INDEX idx_storage_updated_by ON storage (updated_by)');
+
+        $this->comment('storage', '_table', 'Emplacements de stockage (M7 Catalog) — un stockage = 1 site + 1 type (storage_type) + 1 numero, etats multi-valeur JSONB, soft-delete + Timestampable/Blamable.');
+        $this->comment('storage', 'id', 'Identifiant interne auto-incremente.');
+        $this->comment('storage', 'site_id', 'Site du stockage. FK -> site.id, ON DELETE RESTRICT. Composante de l unicite metier (RG-7.01).');
+        $this->comment('storage', 'storage_type_id', 'Type de stockage (referentiel M6). FK -> storage_type.id, ON DELETE RESTRICT. Composante de l unicite metier (RG-7.01).');
+        $this->comment('storage', 'numero', 'Numero du stockage (≤ 50), saisi. Unique par (site, type) parmi les actifs (RG-7.01, uq_storage_site_type_numero_active). Normalise serveur.');
+        $this->comment('storage', 'states', 'Etats du stockage (JSON) : tableau non vide (>= 1 element, RG-7.04, chk_storage_states_not_empty). Multi-valeur.');
+        $this->comment('storage', 'deleted_at', 'Horodatage du soft-delete technique — null = ligne active. Une ligne supprimee sort de l unicite metier (index partiel uq_storage_site_type_numero_active).');
+        $this->addTimestampableBlamableComments('storage');
+    }
+
+    public function down(Schema $schema): void
+    {
+        $this->addSql('DROP TABLE storage');
+    }
+
+    /**
+     * Pose les 4 commentaires standardises Timestampable/Blamable sur une table,
+     * en reutilisant le catalogue partage (source unique, ERP-67).
+     */
+    private function addTimestampableBlamableComments(string $table): void
+    {
+        foreach (ColumnCommentsCatalog::timestampableBlamableComments() as $column => $description) {
+            $this->comment($table, $column, $description);
+        }
+    }
+
+    /**
+     * Emet un `COMMENT ON TABLE` (colonne speciale `_table`) ou `COMMENT ON COLUMN`
+     * en dollar-quoting Postgres ($_$...$_$) pour eviter tout echappement d apostrophe.
+     */
+    private function comment(string $table, string $column, string $description): void
+    {
+        $quotedTable = '"'.str_replace('"', '""', $table).'"';
+
+        if ('_table' === $column) {
+            $this->addSql(sprintf('COMMENT ON TABLE %s IS $_$%s$_$', $quotedTable, $description));
+
+            return;
+        }
+
+        $this->addSql(sprintf(
+            'COMMENT ON COLUMN %s.%s IS $_$%s$_$',
+            $quotedTable,
+            '"'.str_replace('"', '""', $column).'"',
+            $description,
+        ));
+    }
+}