chore(transport) : ajouter la cible make qualimat-sync (ERP-39)

Commande console app:qualimat:sync : récupère les opérateurs de transport agréés depuis l'API publique qualimat.org, normalise et synchronise une table référentielle (upsert sur le SIRET + soft-delete des absents + journal). Prévue pour un cron quotidien.

- migration : tables qualimat_carrier + qualimat_sync_log (COMMENT ON COLUMN sur chaque colonne)
- QualimatRowMapper : normalisation pure (SIRET sans espaces, date dd/mm/yyyy -> ISO, skip sans SIRET) + tests unitaires
- SyncQualimatCommand : options --file / --ppp / --dry-run, upsert DBAL transactionnel
- activation de framework.http_client
- tests fonctionnels de la commande (upsert/normalisation/journal/soft-delete)

config/packages/http_client.yaml

@@ -0,0 +1,9 @@
+# Active le composant HTTP Client (symfony/http-client) et enregistre
+# l'autowiring de HttpClientInterface. Utilise par les commandes de
+# synchronisation de referentiels externes (QUALIMAT, IDTF...).
+framework:
+    http_client:
+        default_options:
+            timeout: 30
+            headers:
+                User-Agent: 'Starseed-ERP (referentiel-sync)'

makefile

@@ -249,6 +249,14 @@ sync-permissions:
 seed-rbac:
 	$(SYMFONY_CONSOLE) --no-interaction app:seed-rbac
 
+# Synchronise le referentiel des transporteurs QUALIMAT (ERP-39) : upsert sur
+# le SIRET + soft-delete des absents + journal. Idempotent (refresh complet),
+# prevu pour un cron quotidien.
+# Options : --dry-run (analyse sans ecriture), --file=<chemin.json> (source
+# locale au lieu de l'API), --ppp=<n> (taille de page API, defaut 10000).
+qualimat-sync:
+	$(SYMFONY_CONSOLE) --no-interaction app:qualimat:sync
+
 # Attention, supprime votre bdd local
 db-reset:
 	$(DOCKER_COMPOSE) down -v

migrations/Version20260612150000.php

@@ -0,0 +1,111 @@
+<?php
+
+declare(strict_types=1);
+
+namespace DoctrineMigrations;
+
+use Doctrine\DBAL\Schema\Schema;
+use Doctrine\Migrations\AbstractMigration;
+
+/**
+ * ERP-39 (Module Transport) : referentiel des transporteurs agrees QUALIMAT.
+ *
+ * Tables alimentees par la commande de synchronisation `app:qualimat:sync`
+ * (upsert sur le SIRET + soft-delete des absents + journal). Aucune FK
+ * cross-module (referentiel autonome) : migration posee au namespace racine
+ * `DoctrineMigrations`, comme les autres migrations de creation de tables.
+ */
+final class Version20260612150000 extends AbstractMigration
+{
+    public function getDescription(): string
+    {
+        return 'ERP-39 : tables qualimat_carrier + qualimat_sync_log (referentiel transporteurs QUALIMAT, synchro console).';
+    }
+
+    public function up(Schema $schema): void
+    {
+        $this->addSql(<<<'SQL'
+            CREATE TABLE qualimat_carrier (
+                id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
+                siret VARCHAR(20) NOT NULL,
+                name VARCHAR(255) NOT NULL,
+                address VARCHAR(255) DEFAULT NULL,
+                postal_code VARCHAR(10) DEFAULT NULL,
+                city VARCHAR(255) DEFAULT NULL,
+                phone VARCHAR(32) DEFAULT NULL,
+                department VARCHAR(64) DEFAULT NULL,
+                status VARCHAR(32) NOT NULL,
+                validity_date DATE DEFAULT NULL,
+                is_active BOOLEAN DEFAULT TRUE NOT NULL,
+                last_synced_at TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
+                PRIMARY KEY (id),
+                CONSTRAINT uq_qualimat_carrier_siret UNIQUE (siret)
+            )
+            SQL);
+        $this->addSql('CREATE INDEX idx_qualimat_carrier_active ON qualimat_carrier (is_active)');
+
+        $this->comment('qualimat_carrier', '_table', "Referentiel des transporteurs agrees QUALIMAT, synchronise quotidiennement depuis l'API qualimat.org (type=operateur_transport).");
+        $this->comment('qualimat_carrier', 'id', 'Cle technique auto-incrementee.');
+        $this->comment('qualimat_carrier', 'siret', 'SIRET normalise (chiffres sans espaces). Cle naturelle de synchro (unique). Source parfois incomplete (longueur variable), non contrainte a 14.');
+        $this->comment('qualimat_carrier', 'name', 'Raison sociale du transporteur (champs Nom = Societe de la source, identiques).');
+        $this->comment('qualimat_carrier', 'address', 'Adresse postale (voie). Nullable.');
+        $this->comment('qualimat_carrier', 'postal_code', 'Code postal. Nullable.');
+        $this->comment('qualimat_carrier', 'city', 'Ville. Nullable.');
+        $this->comment('qualimat_carrier', 'phone', 'Telephone au format source "indicatif|numero" (ex: +33|0608890316). Nullable.');
+        $this->comment('qualimat_carrier', 'department', 'Departement au format source "code - libelle" (ex: 65 - Hautes-Pyrenees). Nullable.');
+        $this->comment('qualimat_carrier', 'status', "Statut d'agrement QUALIMAT (valeurs connues : Audite, Valide, Suspendu). Valeur brute de la source, non contrainte.");
+        $this->comment('qualimat_carrier', 'validity_date', 'Date de fin de validite de la certification (convertie depuis dd/mm/yyyy). Nullable.');
+        $this->comment('qualimat_carrier', 'is_active', 'Faux = transporteur absent du dernier import (soft-delete). Toute ligne non revue par le dernier run passe a FALSE.');
+        $this->comment('qualimat_carrier', 'last_synced_at', 'Horodatage du run de synchro ayant vu cette ligne en dernier (soft-delete : last_synced_at < run courant).');
+
+        $this->addSql(<<<'SQL'
+            CREATE TABLE qualimat_sync_log (
+                id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
+                fetched_at TIMESTAMP(6) WITHOUT TIME ZONE NOT NULL,
+                rows_total INT NOT NULL,
+                rows_upserted INT NOT NULL,
+                rows_skipped INT NOT NULL,
+                rows_deactivated INT NOT NULL,
+                created_at TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT NOW() NOT NULL,
+                PRIMARY KEY (id)
+            )
+            SQL);
+
+        $this->comment('qualimat_sync_log', '_table', 'Journal des synchronisations QUALIMAT (une ligne par run de la commande app:qualimat:sync).');
+        $this->comment('qualimat_sync_log', 'id', 'Cle technique auto-incrementee.');
+        $this->comment('qualimat_sync_log', 'fetched_at', "Horodatage de l'appel a l'API source (= run de synchro).");
+        $this->comment('qualimat_sync_log', 'rows_total', "Nombre d'items renvoyes par l'API.");
+        $this->comment('qualimat_sync_log', 'rows_upserted', 'Nombre de transporteurs inseres ou mis a jour.');
+        $this->comment('qualimat_sync_log', 'rows_skipped', "Nombre d'items ignores (sans SIRET exploitable).");
+        $this->comment('qualimat_sync_log', 'rows_deactivated', 'Nombre de transporteurs passes a is_active=false (absents de cet import).');
+        $this->comment('qualimat_sync_log', 'created_at', 'Horodatage de fin du run (insertion du journal).');
+    }
+
+    public function down(Schema $schema): void
+    {
+        $this->addSql('DROP TABLE IF EXISTS qualimat_sync_log');
+        $this->addSql('DROP TABLE IF EXISTS qualimat_carrier');
+    }
+
+    /**
+     * Pose un COMMENT ON TABLE/COLUMN en dollar-quoting Postgres ($_$...$_$)
+     * pour eviter tout echappement d'apostrophes dans les descriptions.
+     */
+    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,
+        ));
+    }
+}

src/Module/Transport/Application/Qualimat/QualimatRowMapper.php

@@ -0,0 +1,124 @@
+<?php
+
+declare(strict_types=1);
+
+namespace App\Module\Transport\Application\Qualimat;
+
+/**
+ * Mapping pur d'un item brut de l'API QUALIMAT vers une ligne normalisee
+ * prete a l'upsert dans `qualimat_carrier`. Sans dependance (testable en
+ * isolation). Voir ERP-39 § 2 pour les pieges qualite de la source.
+ */
+final class QualimatRowMapper
+{
+    /**
+     * Mappe un lot d'items. Les items sans SIRET exploitable sont ignores et
+     * comptes a part (cf. `rows_skipped` du journal).
+     *
+     * @param array<int, array<string, mixed>> $items
+     *
+     * @return array{rows: list<array<string, mixed>>, skipped: int}
+     */
+    public static function mapMany(array $items): array
+    {
+        $rows    = [];
+        $skipped = 0;
+
+        foreach ($items as $item) {
+            $row = self::mapOne($item);
+
+            if (null === $row) {
+                ++$skipped;
+
+                continue;
+            }
+
+            $rows[] = $row;
+        }
+
+        return ['rows' => $rows, 'skipped' => $skipped];
+    }
+
+    /**
+     * Mappe un item unique. Retourne null si le SIRET est absent ou vide
+     * (ligne inexploitable : pas de cle naturelle pour l'upsert).
+     *
+     * @param array<string, mixed> $item
+     *
+     * @return null|array<string, mixed>
+     */
+    public static function mapOne(array $item): ?array
+    {
+        $siret = self::normalizeSiret(self::str($item['Siret'] ?? null));
+
+        if (null === $siret) {
+            return null;
+        }
+
+        return [
+            'siret' => $siret,
+            // Nom et Societe sont identiques a la source : une seule colonne.
+            'name'        => self::str($item['Nom'] ?? null) ?? '',
+            'address'     => self::str($item['Adresse'] ?? null),
+            'postal_code' => self::str($item['CodePostal'] ?? null),
+            'city'        => self::str($item['Ville'] ?? null),
+            'phone'       => self::str($item['Telephone_1'] ?? null),
+            'department'  => self::str($item['Departement'] ?? null),
+            // Statut conserve brut (feed externe, valeurs non contraintes).
+            'status'        => self::str($item['Statut'] ?? null) ?? '',
+            'validity_date' => self::parseDate(self::str($item['Validite'] ?? null)),
+        ];
+    }
+
+    /**
+     * Normalise un SIRET : ne conserve que les chiffres. Null si vide.
+     * La source est "sale" (longueurs variables 7 a 14) : aucune contrainte
+     * de longueur, on stocke les chiffres tels quels.
+     */
+    public static function normalizeSiret(?string $raw): ?string
+    {
+        if (null === $raw) {
+            return null;
+        }
+
+        $digits = preg_replace('/\D+/', '', $raw) ?? '';
+
+        return '' === $digits ? null : $digits;
+    }
+
+    /**
+     * Convertit une date "dd/mm/yyyy" en "yyyy-mm-dd". Null si le format ne
+     * correspond pas ou si la date n'est pas un jour calendaire valide
+     * (garde-fou : evite un INSERT en erreur sur une date impossible).
+     */
+    public static function parseDate(?string $raw): ?string
+    {
+        if (null === $raw || !preg_match('#^(\d{2})/(\d{2})/(\d{4})$#', $raw, $m)) {
+            return null;
+        }
+
+        $day   = (int) $m[1];
+        $month = (int) $m[2];
+        $year  = (int) $m[3];
+
+        if (!checkdate($month, $day, $year)) {
+            return null;
+        }
+
+        return sprintf('%04d-%02d-%02d', $year, $month, $day);
+    }
+
+    /**
+     * Trim d'une valeur scalaire ; null si la chaine resultante est vide.
+     */
+    private static function str(mixed $value): ?string
+    {
+        if (null === $value) {
+            return null;
+        }
+
+        $trimmed = trim((string) $value);
+
+        return '' === $trimmed ? null : $trimmed;
+    }
+}

src/Module/Transport/Infrastructure/Console/SyncQualimatCommand.php

@@ -0,0 +1,250 @@
+<?php
+
+declare(strict_types=1);
+
+namespace App\Module\Transport\Infrastructure\Console;
+
+use App\Module\Transport\Application\Qualimat\QualimatRowMapper;
+use DateTimeImmutable;
+use Doctrine\DBAL\Connection;
+use RuntimeException;
+use Symfony\Component\Console\Attribute\AsCommand;
+use Symfony\Component\Console\Command\Command;
+use Symfony\Component\Console\Input\InputInterface;
+use Symfony\Component\Console\Input\InputOption;
+use Symfony\Component\Console\Output\OutputInterface;
+use Symfony\Component\Console\Style\SymfonyStyle;
+use Symfony\Contracts\HttpClient\HttpClientInterface;
+use Throwable;
+
+use function array_slice;
+use function count;
+use function is_array;
+
+use const JSON_THROW_ON_ERROR;
+
+/**
+ * ERP-39 : synchronise le referentiel des transporteurs QUALIMAT.
+ *
+ * Recupere la liste des operateurs de transport depuis l'API publique (ou un
+ * fichier local), normalise chaque ligne et synchronise `qualimat_carrier` de
+ * facon transactionnelle : upsert sur le SIRET, soft-delete des absents,
+ * journal dans `qualimat_sync_log`. Idempotente (refresh complet) : prevue
+ * pour un cron quotidien.
+ */
+#[AsCommand(
+    name: 'app:qualimat:sync',
+    description: 'Synchronise le referentiel des transporteurs QUALIMAT (upsert + soft-delete + journal).',
+)]
+final class SyncQualimatCommand extends Command
+{
+    private const string API_URL  = 'https://www.qualimat.org/wp-json/qualimat/v1/getOperateurs';
+    private const int DEFAULT_PPP = 10000;
+
+    public function __construct(
+        private readonly Connection $connection,
+        private readonly HttpClientInterface $httpClient,
+    ) {
+        parent::__construct();
+    }
+
+    protected function configure(): void
+    {
+        $this
+            ->addOption('file', null, InputOption::VALUE_REQUIRED, "Chemin d'un JSON local (court-circuite l'appel HTTP, utile pour tests/rejeu).")
+            ->addOption('ppp', null, InputOption::VALUE_REQUIRED, "Taille de page demandee a l'API.", (string) self::DEFAULT_PPP)
+            ->addOption('dry-run', null, InputOption::VALUE_NONE, 'Analyse sans ecriture en base.')
+        ;
+    }
+
+    protected function execute(InputInterface $input, OutputInterface $output): int
+    {
+        $io     = new SymfonyStyle($input, $output);
+        $ppp    = max(1, (int) $input->getOption('ppp'));
+        $dryRun = (bool) $input->getOption('dry-run');
+        $file   = $input->getOption('file');
+
+        // 1. Recuperation des items (fichier local ou API).
+        try {
+            $items = null !== $file ? $this->readLocal((string) $file) : $this->fetchRemote($ppp);
+        } catch (Throwable $e) {
+            $io->error('Recuperation impossible : '.$e->getMessage());
+
+            return Command::FAILURE;
+        }
+
+        $total = count($items);
+        $io->section(sprintf('QUALIMAT — %d items recus', $total));
+
+        // Garde-fou troncature : un retour egal a ppp signale un dataset coupe.
+        if (null === $file && $total === $ppp) {
+            $io->warning(sprintf("Le nombre d'items recus (%d) egale --ppp : resultat potentiellement tronque, augmente --ppp.", $ppp));
+        }
+
+        // 2. Mapping / normalisation (les items sans SIRET sont ignores).
+        ['rows' => $rows, 'skipped' => $skipped] = QualimatRowMapper::mapMany($items);
+        $io->writeln(sprintf('%d lignes exploitables, %d ignorees (sans SIRET).', count($rows), $skipped));
+
+        if ($dryRun) {
+            $this->renderPreview($io, $rows);
+            $io->note(sprintf('Dry-run : aucune ecriture. (%d lignes au total)', count($rows)));
+
+            return Command::SUCCESS;
+        }
+
+        // 3. Sync transactionnelle : upsert -> soft-delete -> journal.
+        $run = new DateTimeImmutable()->format('Y-m-d H:i:s.u');
+
+        $this->connection->beginTransaction();
+
+        try {
+            $upserted    = $this->upsertAll($rows, $run);
+            $deactivated = $this->deactivateMissing($run);
+            $this->log($run, $total, $upserted, $skipped, $deactivated);
+            $this->connection->commit();
+        } catch (Throwable $e) {
+            $this->connection->rollBack();
+            $io->error('Sync annulee (rollback) : '.$e->getMessage());
+
+            return Command::FAILURE;
+        }
+
+        $io->success(sprintf('%d upsert, %d ignore(s), %d desactive(s).', $upserted, $skipped, $deactivated));
+
+        return Command::SUCCESS;
+    }
+
+    /**
+     * Rejoue l'appel GET de l'API QUALIMAT et retourne le tableau d'items.
+     *
+     * @return array<int, array<string, mixed>>
+     */
+    private function fetchRemote(int $ppp): array
+    {
+        $response = $this->httpClient->request('GET', self::API_URL, [
+            'query'   => ['type' => 'operateur_transport', 'ppp' => $ppp],
+            'timeout' => 60,
+        ]);
+
+        // toArray() leve une exception sur un statut non-2xx ou un corps non-JSON.
+        $data = $response->toArray();
+
+        return array_is_list($data) ? $data : [];
+    }
+
+    /**
+     * Lit un export JSON local (tableau d'objets).
+     *
+     * @return array<int, array<string, mixed>>
+     */
+    private function readLocal(string $path): array
+    {
+        $raw = @file_get_contents($path);
+
+        if (false === $raw) {
+            throw new RuntimeException(sprintf('Fichier illisible : %s', $path));
+        }
+
+        $data = json_decode($raw, true, 512, JSON_THROW_ON_ERROR);
+
+        if (!is_array($data) || !array_is_list($data)) {
+            throw new RuntimeException("Le JSON doit etre un tableau d'objets.");
+        }
+
+        return $data;
+    }
+
+    /**
+     * Upsert de toutes les lignes valides (cle naturelle = siret). Marque
+     * is_active=TRUE et tamponne last_synced_at avec le run courant.
+     *
+     * @param list<array<string, mixed>> $rows
+     */
+    private function upsertAll(array $rows, string $run): int
+    {
+        $sql = <<<'SQL'
+            INSERT INTO qualimat_carrier
+                (siret, name, address, postal_code, city, phone, department, status, validity_date, is_active, last_synced_at)
+            VALUES
+                (:siret, :name, :address, :postal_code, :city, :phone, :department, :status, :validity_date, TRUE, :run)
+            ON CONFLICT (siret) DO UPDATE SET
+                name           = EXCLUDED.name,
+                address        = EXCLUDED.address,
+                postal_code    = EXCLUDED.postal_code,
+                city           = EXCLUDED.city,
+                phone          = EXCLUDED.phone,
+                department     = EXCLUDED.department,
+                status         = EXCLUDED.status,
+                validity_date  = EXCLUDED.validity_date,
+                is_active      = TRUE,
+                last_synced_at = EXCLUDED.last_synced_at
+            SQL;
+
+        $count = 0;
+
+        foreach ($rows as $r) {
+            $this->connection->executeStatement($sql, [
+                'siret'         => $r['siret'],
+                'name'          => $r['name'],
+                'address'       => $r['address'],
+                'postal_code'   => $r['postal_code'],
+                'city'          => $r['city'],
+                'phone'         => $r['phone'],
+                'department'    => $r['department'],
+                'status'        => $r['status'],
+                'validity_date' => $r['validity_date'],
+                'run'           => $run,
+            ]);
+            ++$count;
+        }
+
+        return $count;
+    }
+
+    /**
+     * Soft-delete : toute ligne active non revue par ce run (tampon anterieur)
+     * passe a is_active=false.
+     */
+    private function deactivateMissing(string $run): int
+    {
+        return (int) $this->connection->executeStatement(
+            'UPDATE qualimat_carrier SET is_active = FALSE WHERE is_active = TRUE AND last_synced_at < :run',
+            ['run' => $run],
+        );
+    }
+
+    private function log(string $run, int $total, int $upserted, int $skipped, int $deactivated): void
+    {
+        $this->connection->executeStatement(
+            <<<'SQL'
+                INSERT INTO qualimat_sync_log (fetched_at, rows_total, rows_upserted, rows_skipped, rows_deactivated)
+                VALUES (:run, :total, :upserted, :skipped, :deactivated)
+                SQL,
+            [
+                'run'         => $run,
+                'total'       => $total,
+                'upserted'    => $upserted,
+                'skipped'     => $skipped,
+                'deactivated' => $deactivated,
+            ],
+        );
+    }
+
+    /**
+     * @param list<array<string, mixed>> $rows
+     */
+    private function renderPreview(SymfonyStyle $io, array $rows): void
+    {
+        $io->table(
+            ['SIRET', 'Nom', 'CP', 'Ville', 'Statut', 'Validite'],
+            array_map(static fn (array $r): array => [
+                (string) $r['siret'],
+                mb_strimwidth((string) $r['name'], 0, 40, '…'),
+                (string) ($r['postal_code'] ?? ''),
+                mb_strimwidth((string) ($r['city'] ?? ''), 0, 25, '…'),
+                (string) $r['status'],
+                (string) ($r['validity_date'] ?? ''),
+            ], array_slice($rows, 0, 15)),
+        );
+    }
+}

tests/Module/Transport/Application/Qualimat/QualimatRowMapperTest.php

@@ -0,0 +1,90 @@
+<?php
+
+declare(strict_types=1);
+
+namespace App\Tests\Module\Transport\Application\Qualimat;
+
+use App\Module\Transport\Application\Qualimat\QualimatRowMapper;
+use PHPUnit\Framework\TestCase;
+
+/**
+ * @internal
+ */
+final class QualimatRowMapperTest extends TestCase
+{
+    public function testNormalizeSiretStripsNonDigits(): void
+    {
+        self::assertSame('44415628500025', QualimatRowMapper::normalizeSiret('444 156 285 000 25'));
+        self::assertNull(QualimatRowMapper::normalizeSiret(null));
+        self::assertNull(QualimatRowMapper::normalizeSiret('   '));
+        self::assertNull(QualimatRowMapper::normalizeSiret(''));
+    }
+
+    public function testParseDate(): void
+    {
+        self::assertSame('2027-05-14', QualimatRowMapper::parseDate('14/05/2027'));
+        self::assertNull(QualimatRowMapper::parseDate(null));
+        self::assertNull(QualimatRowMapper::parseDate('2027-05-14'));
+        self::assertNull(QualimatRowMapper::parseDate('14-05-2027'));
+        // Date calendaire impossible : evite un INSERT en erreur.
+        self::assertNull(QualimatRowMapper::parseDate('31/02/2027'));
+    }
+
+    public function testMapOneNormalizesAndTrims(): void
+    {
+        $row = QualimatRowMapper::mapOne([
+            'Nom'         => '  2C TRANS ',
+            'Societe'     => '2C TRANS',
+            'Adresse'     => '66 Impasse Mendi',
+            'CodePostal'  => '65500',
+            'Ville'       => 'VIC EN BIGORRE',
+            'Telephone_1' => '+33|0608890316',
+            'Siret'       => '444 156 285 000 25',
+            'Validite'    => '14/05/2027',
+            'Statut'      => 'Audité',
+            'Departement' => '65 - Hautes-Pyrénées',
+        ]);
+
+        self::assertNotNull($row);
+        self::assertSame('44415628500025', $row['siret']);
+        self::assertSame('2C TRANS', $row['name']);
+        self::assertSame('2027-05-14', $row['validity_date']);
+        self::assertSame('+33|0608890316', $row['phone']);
+        self::assertSame('Audité', $row['status']);
+        self::assertSame('65 - Hautes-Pyrénées', $row['department']);
+    }
+
+    public function testMapOneReturnsNullWithoutSiret(): void
+    {
+        self::assertNull(QualimatRowMapper::mapOne(['Nom' => 'X', 'Siret' => null]));
+        self::assertNull(QualimatRowMapper::mapOne(['Nom' => 'X']));
+        self::assertNull(QualimatRowMapper::mapOne(['Nom' => 'X', 'Siret' => '   ']));
+    }
+
+    public function testMapManyCountsSkipped(): void
+    {
+        $result = QualimatRowMapper::mapMany([
+            ['Nom' => 'A', 'Siret' => '111 111 111 00011', 'Statut' => 'Audité', 'Validite' => '01/01/2030'],
+            ['Nom' => 'B', 'Siret' => null],
+            ['Nom' => 'C', 'Siret' => '   '],
+        ]);
+
+        self::assertCount(1, $result['rows']);
+        self::assertSame(2, $result['skipped']);
+    }
+
+    public function testEmptyOptionalFieldsBecomeNull(): void
+    {
+        $row = QualimatRowMapper::mapOne([
+            'Siret'   => '111 111 111 00011',
+            'Nom'     => 'A',
+            'Adresse' => '',
+            'Ville'   => '  ',
+        ]);
+
+        self::assertNotNull($row);
+        self::assertNull($row['address']);
+        self::assertNull($row['city']);
+        self::assertNull($row['validity_date']);
+    }
+}

tests/Module/Transport/Infrastructure/Console/SyncQualimatCommandTest.php

@@ -0,0 +1,138 @@
+<?php
+
+declare(strict_types=1);
+
+namespace App\Tests\Module\Transport\Infrastructure\Console;
+
+use Doctrine\DBAL\Connection;
+use Symfony\Bundle\FrameworkBundle\Console\Application;
+use Symfony\Bundle\FrameworkBundle\Test\KernelTestCase;
+use Symfony\Component\Console\Tester\CommandTester;
+
+use const JSON_THROW_ON_ERROR;
+
+/**
+ * Test fonctionnel de `app:qualimat:sync` via l'option --file (pas d'appel
+ * reseau) : verifie l'upsert normalise, le journal et le soft-delete.
+ *
+ * @internal
+ */
+final class SyncQualimatCommandTest extends KernelTestCase
+{
+    private Connection $connection;
+
+    protected function setUp(): void
+    {
+        self::bootKernel();
+
+        /** @var Connection $connection */
+        $connection       = self::getContainer()->get('doctrine.dbal.default_connection');
+        $this->connection = $connection;
+        $this->purge();
+    }
+
+    protected function tearDown(): void
+    {
+        $this->purge();
+        parent::tearDown();
+    }
+
+    public function testFirstSyncInsertsNormalizesAndLogs(): void
+    {
+        $tester = $this->runSync([
+            [
+                'Nom'         => '2C TRANS',
+                'Societe'     => '2C TRANS',
+                'Adresse'     => '66 Impasse Mendi',
+                'CodePostal'  => '65500',
+                'Ville'       => 'VIC EN BIGORRE',
+                'Telephone_1' => '+33|0608890316',
+                'Siret'       => '444 156 285 000 25',
+                'Validite'    => '14/05/2027',
+                'Statut'      => 'Audité',
+                'Departement' => '65 - Hautes-Pyrénées',
+            ],
+            // Item sans SIRET : doit etre ignore (compte dans rows_skipped).
+            ['Nom' => 'SANS SIRET', 'Siret' => null, 'Validite' => '01/01/2030', 'Statut' => 'Valide'],
+        ]);
+
+        $tester->assertCommandIsSuccessful();
+
+        self::assertSame(1, $this->countRows('SELECT COUNT(*) FROM qualimat_carrier'));
+
+        $row = $this->connection->fetchAssociative('SELECT * FROM qualimat_carrier');
+        self::assertNotFalse($row);
+        self::assertSame('44415628500025', $row['siret']);
+        self::assertSame('2C TRANS', $row['name']);
+        self::assertSame('2027-05-14', $row['validity_date']);
+        self::assertSame('+33|0608890316', $row['phone']);
+        self::assertSame(1, $this->countRows('SELECT COUNT(*) FROM qualimat_carrier WHERE is_active = TRUE'));
+
+        $log = $this->connection->fetchAssociative('SELECT * FROM qualimat_sync_log ORDER BY id DESC LIMIT 1');
+        self::assertNotFalse($log);
+        self::assertSame(2, (int) $log['rows_total']);
+        self::assertSame(1, (int) $log['rows_upserted']);
+        self::assertSame(1, (int) $log['rows_skipped']);
+        self::assertSame(0, (int) $log['rows_deactivated']);
+    }
+
+    public function testSecondSyncUpdatesAndSoftDeletesMissing(): void
+    {
+        $a = ['Nom' => 'A', 'Siret' => '111 111 111 00011', 'Validite' => '01/01/2030', 'Statut' => 'Audité'];
+        $b = ['Nom' => 'B', 'Siret' => '222 222 222 00022', 'Validite' => '01/01/2030', 'Statut' => 'Audité'];
+
+        $this->runSync([$a, $b])->assertCommandIsSuccessful();
+        self::assertSame(2, $this->countRows('SELECT COUNT(*) FROM qualimat_carrier WHERE is_active = TRUE'));
+
+        // 2e run sans B et avec A renomme : A est mis a jour, B est soft-delete.
+        $aRenamed = ['Nom' => 'A BIS', 'Siret' => '111 111 111 00011', 'Validite' => '02/02/2031', 'Statut' => 'Valide'];
+        $tester   = $this->runSync([$aRenamed]);
+        $tester->assertCommandIsSuccessful();
+
+        // Toujours 2 lignes en base, mais une seule active.
+        self::assertSame(2, $this->countRows('SELECT COUNT(*) FROM qualimat_carrier'));
+        self::assertSame(1, $this->countRows('SELECT COUNT(*) FROM qualimat_carrier WHERE is_active = TRUE'));
+        self::assertSame(1, $this->countRows("SELECT COUNT(*) FROM qualimat_carrier WHERE siret = '22222222200022' AND is_active = FALSE"));
+
+        // A a bien ete mis a jour (nom + statut + date).
+        $a = $this->connection->fetchAssociative("SELECT * FROM qualimat_carrier WHERE siret = '11111111100011'");
+        self::assertNotFalse($a);
+        self::assertSame('A BIS', $a['name']);
+        self::assertSame('Valide', $a['status']);
+        self::assertSame('2031-02-02', $a['validity_date']);
+
+        $log = $this->connection->fetchAssociative('SELECT * FROM qualimat_sync_log ORDER BY id DESC LIMIT 1');
+        self::assertNotFalse($log);
+        self::assertSame(1, (int) $log['rows_upserted']);
+        self::assertSame(1, (int) $log['rows_deactivated']);
+        self::assertSame(0, (int) $log['rows_skipped']);
+    }
+
+    /**
+     * @param array<int, array<string, mixed>> $items
+     */
+    private function runSync(array $items): CommandTester
+    {
+        $path = tempnam(sys_get_temp_dir(), 'qualimat_').'.json';
+        file_put_contents($path, json_encode($items, JSON_THROW_ON_ERROR));
+
+        $application = new Application(self::$kernel);
+        $tester      = new CommandTester($application->find('app:qualimat:sync'));
+        $tester->execute(['--file' => $path]);
+
+        @unlink($path);
+
+        return $tester;
+    }
+
+    private function countRows(string $sql): int
+    {
+        return (int) $this->connection->fetchOne($sql);
+    }
+
+    private function purge(): void
+    {
+        $this->connection->executeStatement('DELETE FROM qualimat_carrier');
+        $this->connection->executeStatement('DELETE FROM qualimat_sync_log');
+    }
+}