Compare commits
1 Commits
| Author | SHA1 | Date | |
|---|---|---|---|
 Matthieu
|
1d9a656504 |
@@ -0,0 +1,438 @@
|
||||
<?php
|
||||
|
||||
declare(strict_types=1);
|
||||
|
||||
namespace DoctrineMigrations;
|
||||
|
||||
use App\Shared\Infrastructure\Database\ColumnCommentsCatalog;
|
||||
use Doctrine\DBAL\Schema\Schema;
|
||||
use Doctrine\Migrations\AbstractMigration;
|
||||
|
||||
/**
|
||||
* M2 — Repertoire fournisseurs (ERP-85) : creation de toute la structure BDD
|
||||
* des fournisseurs sous le module Commercial (jumeau du M1 client).
|
||||
*
|
||||
* Tables creees :
|
||||
* - Table principale : supplier (formulaire principal + Information +
|
||||
* Comptabilite + archive + soft-delete + Timestampable/Blamable).
|
||||
* - Sous-collections : supplier_category (M2M), supplier_contact (1:n),
|
||||
* supplier_address (1:n), supplier_rib (1:n).
|
||||
* - Jointures de supplier_address : supplier_address_site,
|
||||
* supplier_address_contact, supplier_address_category.
|
||||
*
|
||||
* Differences vs le M1 `client` (cf. spec M2 § 2.4 / § 3.1) :
|
||||
* - PAS de contact inline sur supplier (first_name / last_name / phone_* /
|
||||
* email retires en V0.2, refonte-contact ERP-106). Les contacts vivent
|
||||
* uniquement dans supplier_contact (onglet Contacts).
|
||||
* - PAS d'auto-reference distributor_id / broker_id (pas de CHECK associe).
|
||||
* - Ajout du champ Information volume_forecast (entier).
|
||||
* - supplier_address remplace les 3 booleens M1 (is_prospect / is_delivery /
|
||||
* is_billing + billing_email) par un seul enum address_type
|
||||
* (PROSPECT | DEPART | RENDU, radio exclusif, CHECK chk_supplier_address_type)
|
||||
* et ajoute bennes (int nullable) + triage_provider (bool).
|
||||
*
|
||||
* Referentiels comptables NON recrees : tva_mode / payment_delay / payment_type
|
||||
* / bank sont ceux du M1 (FK partagees, zero duplication — spec § 2.3).
|
||||
*
|
||||
* CategoryType FOURNISSEUR NON re-seede : il est cree par ERP-84
|
||||
* (Version20260605120000) avec ses categories de demonstration. Le M2M
|
||||
* supplier_category / supplier_address_category s'appuie sur ce type existant.
|
||||
*
|
||||
* Namespace racine `DoctrineMigrations` (regle ABSOLUE Starseed n°11) et NON
|
||||
* `App\Module\Commercial\...` : la migration cree un schema avec FK cross-module
|
||||
* (user, category, site, et les referentiels comptables M1). Avec plusieurs
|
||||
* migrations_paths, Doctrine Migrations 3.x trie par FQCN alphabetique — un
|
||||
* namespace modulaire s'executerait avant la creation de user/category/site sur
|
||||
* base vide -> echec des FK. Le namespace racine garantit l'ordre par timestamp.
|
||||
*
|
||||
* Style DDL aligne sur le M1 (Version20260601000000) : `INT GENERATED BY DEFAULT
|
||||
* AS IDENTITY` (et non SERIAL), `TIMESTAMP(0) WITHOUT TIME ZONE` (et non
|
||||
* TIMESTAMPTZ, car le TimestampableBlamableTrait mappe `datetime_immutable`).
|
||||
* Garantit que `schema:update` restera un no-op quand les entites arriveront
|
||||
* (ticket ERP-86).
|
||||
*
|
||||
* Decision unicite (Matthieu 02/06, alignee Q4 du M1) : unicite metier sur le
|
||||
* NOM DE SOCIETE uniquement (uq_supplier_company_name_active, partiel). Pas
|
||||
* d'index unique sur siren ni email.
|
||||
*
|
||||
* Chaque colonne porte un `COMMENT ON COLUMN` (regle ABSOLUE n°12, garde-fou
|
||||
* ColumnsHaveSqlCommentTest). Les tables n'etant pas encore mappees par l'ORM
|
||||
* (entites a ERP-86), ces commentaires survivent au `schema:update --force` du
|
||||
* setup de test (additif, ne drop pas les tables non mappees).
|
||||
*/
|
||||
final class Version20260605130000 extends AbstractMigration
|
||||
{
|
||||
public function getDescription(): string
|
||||
{
|
||||
return 'ERP-85 (M2) : tables supplier + sous-collections + jointures M2M (referentiels comptables et CategoryType FOURNISSEUR reutilises).';
|
||||
}
|
||||
|
||||
public function up(Schema $schema): void
|
||||
{
|
||||
$this->createSupplierTable();
|
||||
$this->createSupplierCategory();
|
||||
$this->createSupplierContact();
|
||||
$this->createSupplierAddress();
|
||||
$this->createSupplierAddressJoinTables();
|
||||
$this->createSupplierRib();
|
||||
}
|
||||
|
||||
public function down(Schema $schema): void
|
||||
{
|
||||
// Ordre inverse des dependances FK : jointures et sous-collections
|
||||
// d'abord, puis supplier. Les referentiels comptables et le
|
||||
// CategoryType FOURNISSEUR ne sont pas touches (crees ailleurs).
|
||||
$this->addSql('DROP TABLE supplier_address_category');
|
||||
$this->addSql('DROP TABLE supplier_address_contact');
|
||||
$this->addSql('DROP TABLE supplier_address_site');
|
||||
$this->addSql('DROP TABLE supplier_rib');
|
||||
$this->addSql('DROP TABLE supplier_address');
|
||||
$this->addSql('DROP TABLE supplier_contact');
|
||||
$this->addSql('DROP TABLE supplier_category');
|
||||
$this->addSql('DROP TABLE supplier');
|
||||
}
|
||||
|
||||
// =================================================================
|
||||
// Table principale `supplier`
|
||||
// =================================================================
|
||||
|
||||
private function createSupplierTable(): void
|
||||
{
|
||||
$this->addSql(<<<'SQL'
|
||||
CREATE TABLE supplier (
|
||||
id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||||
company_name VARCHAR(180) NOT NULL,
|
||||
description TEXT DEFAULT NULL,
|
||||
competitors VARCHAR(255) DEFAULT NULL,
|
||||
founded_at DATE DEFAULT NULL,
|
||||
employees_count INT DEFAULT NULL,
|
||||
revenue_amount NUMERIC(15, 2) DEFAULT NULL,
|
||||
director_name VARCHAR(120) DEFAULT NULL,
|
||||
profit_amount NUMERIC(15, 2) DEFAULT NULL,
|
||||
volume_forecast INT DEFAULT NULL,
|
||||
siren VARCHAR(20) DEFAULT NULL,
|
||||
account_number VARCHAR(40) DEFAULT NULL,
|
||||
tva_mode_id INT DEFAULT NULL,
|
||||
n_tva VARCHAR(40) DEFAULT NULL,
|
||||
payment_delay_id INT DEFAULT NULL,
|
||||
payment_type_id INT DEFAULT NULL,
|
||||
bank_id INT DEFAULT NULL,
|
||||
is_archived BOOLEAN DEFAULT FALSE NOT NULL,
|
||||
archived_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT 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 fk_supplier_tva_mode
|
||||
FOREIGN KEY (tva_mode_id) REFERENCES tva_mode (id) ON DELETE RESTRICT,
|
||||
CONSTRAINT fk_supplier_payment_delay
|
||||
FOREIGN KEY (payment_delay_id) REFERENCES payment_delay (id) ON DELETE RESTRICT,
|
||||
CONSTRAINT fk_supplier_payment_type
|
||||
FOREIGN KEY (payment_type_id) REFERENCES payment_type (id) ON DELETE RESTRICT,
|
||||
CONSTRAINT fk_supplier_bank
|
||||
FOREIGN KEY (bank_id) REFERENCES bank (id) ON DELETE RESTRICT,
|
||||
CONSTRAINT fk_supplier_created_by
|
||||
FOREIGN KEY (created_by) REFERENCES "user" (id) ON DELETE SET NULL,
|
||||
CONSTRAINT fk_supplier_updated_by
|
||||
FOREIGN KEY (updated_by) REFERENCES "user" (id) ON DELETE SET NULL
|
||||
)
|
||||
SQL);
|
||||
|
||||
$this->addSql('CREATE INDEX idx_supplier_is_archived ON supplier (is_archived)');
|
||||
$this->addSql('CREATE INDEX idx_supplier_deleted_at ON supplier (deleted_at)');
|
||||
$this->addSql('CREATE INDEX idx_supplier_created_by ON supplier (created_by)');
|
||||
$this->addSql('CREATE INDEX idx_supplier_updated_by ON supplier (updated_by)');
|
||||
|
||||
// Index sur les FK des referentiels comptables (Postgres n'indexe pas
|
||||
// automatiquement les colonnes portant une FOREIGN KEY).
|
||||
$this->addSql('CREATE INDEX idx_supplier_tva_mode_id ON supplier (tva_mode_id)');
|
||||
$this->addSql('CREATE INDEX idx_supplier_payment_delay_id ON supplier (payment_delay_id)');
|
||||
$this->addSql('CREATE INDEX idx_supplier_payment_type_id ON supplier (payment_type_id)');
|
||||
$this->addSql('CREATE INDEX idx_supplier_bank_id ON supplier (bank_id)');
|
||||
|
||||
// Unicite metier partielle : nom de societe insensible a la casse, parmi
|
||||
// les non-archives ET non soft-deletes uniquement (spec § 2.6). Pas
|
||||
// d'index unique sur siren ni email.
|
||||
$this->addSql(<<<'SQL'
|
||||
CREATE UNIQUE INDEX uq_supplier_company_name_active
|
||||
ON supplier (LOWER(company_name))
|
||||
WHERE is_archived = FALSE AND deleted_at IS NULL
|
||||
SQL);
|
||||
|
||||
$this->comment('supplier', '_table', 'Repertoire fournisseurs (M2 Commercial) — entites archivables (is_archived) et soft-deletables (deleted_at, HP M3).');
|
||||
$this->comment('supplier', 'id', 'Identifiant interne auto-incremente.');
|
||||
$this->comment('supplier', 'company_name', 'Raison sociale du fournisseur (stockee en MAJUSCULES). Unique case-insensitive parmi les actifs non archives/non supprimes (uq_supplier_company_name_active, § 2.6).');
|
||||
$this->comment('supplier', 'description', 'Onglet Information : description libre. Obligatoire pour le role Commerciale (RG-2.03), optionnel sinon.');
|
||||
$this->comment('supplier', 'competitors', 'Onglet Information : concurrents identifies (texte libre ≤ 255). Obligatoire role Commerciale (RG-2.03).');
|
||||
$this->comment('supplier', 'founded_at', 'Onglet Information : date de creation de l entreprise. Obligatoire role Commerciale (RG-2.03).');
|
||||
$this->comment('supplier', 'employees_count', 'Onglet Information : effectif (entier >= 0). Obligatoire role Commerciale (RG-2.03).');
|
||||
$this->comment('supplier', 'revenue_amount', 'Onglet Information : chiffre d affaires (NUMERIC 15,2). Obligatoire role Commerciale (RG-2.03).');
|
||||
$this->comment('supplier', 'director_name', 'Onglet Information : nom du dirigeant. Obligatoire role Commerciale (RG-2.03).');
|
||||
$this->comment('supplier', 'profit_amount', 'Onglet Information : resultat / benefice (NUMERIC 15,2). Obligatoire role Commerciale (RG-2.03).');
|
||||
$this->comment('supplier', 'volume_forecast', 'Onglet Information : volume previsionnel (entier >= 0) — specifique fournisseur. Obligatoire role Commerciale (RG-2.03).');
|
||||
$this->comment('supplier', 'siren', 'Onglet Comptabilite : SIREN (9 chiffres attendus). NON unique — peut etre partage entre etablissements (§ 2.6).');
|
||||
$this->comment('supplier', 'account_number', 'Onglet Comptabilite : numero de compte comptable du fournisseur.');
|
||||
$this->comment('supplier', 'tva_mode_id', 'Onglet Comptabilite : mode de TVA applique — FK -> tva_mode.id (referentiel partage M1), ON DELETE RESTRICT.');
|
||||
$this->comment('supplier', 'n_tva', 'Onglet Comptabilite : numero de TVA intracommunautaire.');
|
||||
$this->comment('supplier', 'payment_delay_id', 'Onglet Comptabilite : delai de reglement — FK -> payment_delay.id (M1), ON DELETE RESTRICT.');
|
||||
$this->comment('supplier', 'payment_type_id', 'Onglet Comptabilite : type de reglement — FK -> payment_type.id (M1), ON DELETE RESTRICT. Pilote RG-2.07 (Banque si VIREMENT) et RG-2.08 (RIB).');
|
||||
$this->comment('supplier', 'bank_id', 'Onglet Comptabilite : banque — FK -> bank.id (M1), ON DELETE RESTRICT. Obligatoire ssi payment_type = VIREMENT (RG-2.07), null sinon.');
|
||||
$this->comment('supplier', 'is_archived', 'Drapeau fonctionnel d archivage — masque par defaut dans la liste. Bascule via permission commercial.suppliers.archive.');
|
||||
$this->comment('supplier', 'archived_at', 'Horodatage de l archivage — pose quand is_archived passe a vrai, remis a null a la restauration.');
|
||||
$this->comment('supplier', 'deleted_at', 'Horodatage du soft-delete technique (HP M3) — non expose par l API au M2. Null = ligne active.');
|
||||
$this->addTimestampableBlamableComments('supplier');
|
||||
}
|
||||
|
||||
// =================================================================
|
||||
// M2M supplier <-> category (type FOURNISSEUR — RG-2.10)
|
||||
// =================================================================
|
||||
|
||||
private function createSupplierCategory(): void
|
||||
{
|
||||
$this->addSql(<<<'SQL'
|
||||
CREATE TABLE supplier_category (
|
||||
supplier_id INT NOT NULL,
|
||||
category_id INT NOT NULL,
|
||||
PRIMARY KEY (supplier_id, category_id),
|
||||
CONSTRAINT fk_supplier_category_supplier
|
||||
FOREIGN KEY (supplier_id) REFERENCES supplier (id) ON DELETE CASCADE,
|
||||
CONSTRAINT fk_supplier_category_category
|
||||
FOREIGN KEY (category_id) REFERENCES category (id) ON DELETE RESTRICT
|
||||
)
|
||||
SQL);
|
||||
$this->addSql('CREATE INDEX idx_supplier_category_category ON supplier_category (category_id)');
|
||||
|
||||
$this->comment('supplier_category', '_table', 'Jointure M2M supplier <-> category (Catalog) — categories de type FOURNISSEUR du fournisseur, au moins une obligatoire (RG-2.10).');
|
||||
$this->comment('supplier_category', 'supplier_id', 'FK -> supplier.id, ON DELETE CASCADE — fournisseur porteur de la categorie.');
|
||||
$this->comment('supplier_category', 'category_id', 'FK -> category.id, ON DELETE RESTRICT — categorie de type FOURNISSEUR rattachee au fournisseur (RG-2.10).');
|
||||
}
|
||||
|
||||
// =================================================================
|
||||
// Sous-collection : contacts (1:n)
|
||||
// =================================================================
|
||||
|
||||
private function createSupplierContact(): void
|
||||
{
|
||||
$this->addSql(<<<'SQL'
|
||||
CREATE TABLE supplier_contact (
|
||||
id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||||
supplier_id INT NOT NULL,
|
||||
first_name VARCHAR(120) DEFAULT NULL,
|
||||
last_name VARCHAR(120) DEFAULT NULL,
|
||||
job_title VARCHAR(120) DEFAULT NULL,
|
||||
phone_primary VARCHAR(20) DEFAULT NULL,
|
||||
phone_secondary VARCHAR(20) DEFAULT NULL,
|
||||
email VARCHAR(180) DEFAULT NULL,
|
||||
position INT DEFAULT 0 NOT 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_supplier_contact_name
|
||||
CHECK (first_name IS NOT NULL OR last_name IS NOT NULL),
|
||||
CONSTRAINT fk_supplier_contact_supplier
|
||||
FOREIGN KEY (supplier_id) REFERENCES supplier (id) ON DELETE CASCADE,
|
||||
CONSTRAINT fk_supplier_contact_created_by
|
||||
FOREIGN KEY (created_by) REFERENCES "user" (id) ON DELETE SET NULL,
|
||||
CONSTRAINT fk_supplier_contact_updated_by
|
||||
FOREIGN KEY (updated_by) REFERENCES "user" (id) ON DELETE SET NULL
|
||||
)
|
||||
SQL);
|
||||
$this->addSql('CREATE INDEX idx_supplier_contact_supplier ON supplier_contact (supplier_id)');
|
||||
|
||||
$this->comment('supplier_contact', '_table', 'Contacts d un fournisseur (1:n) — au moins firstName OU lastName par contact (RG-2.04).');
|
||||
$this->comment('supplier_contact', 'id', 'Identifiant interne auto-incremente.');
|
||||
$this->comment('supplier_contact', 'supplier_id', 'FK -> supplier.id, ON DELETE CASCADE — fournisseur proprietaire du contact.');
|
||||
$this->comment('supplier_contact', 'first_name', 'Prenom du contact (capitalise serveur). first_name OU last_name obligatoire (RG-2.04, chk_supplier_contact_name).');
|
||||
$this->comment('supplier_contact', 'last_name', 'Nom du contact (capitalise serveur). first_name OU last_name obligatoire (RG-2.04, chk_supplier_contact_name).');
|
||||
$this->comment('supplier_contact', 'job_title', 'Fonction / intitule de poste du contact (≤ 120 caracteres).');
|
||||
$this->comment('supplier_contact', 'phone_primary', 'Telephone principal du contact — chiffres uniquement (normalisation serveur).');
|
||||
$this->comment('supplier_contact', 'phone_secondary', 'Telephone secondaire du contact — chiffres uniquement (normalisation serveur).');
|
||||
$this->comment('supplier_contact', 'email', 'Email du contact (lowercase serveur).');
|
||||
$this->comment('supplier_contact', 'position', 'Ordre d affichage du contact dans la liste du fournisseur (croissant).');
|
||||
$this->addTimestampableBlamableComments('supplier_contact');
|
||||
}
|
||||
|
||||
// =================================================================
|
||||
// Sous-collection : adresses (1:n)
|
||||
// =================================================================
|
||||
|
||||
private function createSupplierAddress(): void
|
||||
{
|
||||
$this->addSql(<<<'SQL'
|
||||
CREATE TABLE supplier_address (
|
||||
id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||||
supplier_id INT NOT NULL,
|
||||
address_type VARCHAR(20) NOT NULL,
|
||||
country VARCHAR(80) DEFAULT 'France' NOT NULL,
|
||||
postal_code VARCHAR(20) NOT NULL,
|
||||
city VARCHAR(120) NOT NULL,
|
||||
street VARCHAR(255) NOT NULL,
|
||||
street_complement VARCHAR(255) DEFAULT NULL,
|
||||
bennes INT DEFAULT NULL,
|
||||
triage_provider BOOLEAN DEFAULT FALSE NOT NULL,
|
||||
position INT DEFAULT 0 NOT 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_supplier_address_type
|
||||
CHECK (address_type IN ('PROSPECT', 'DEPART', 'RENDU')),
|
||||
CONSTRAINT fk_supplier_address_supplier
|
||||
FOREIGN KEY (supplier_id) REFERENCES supplier (id) ON DELETE CASCADE,
|
||||
CONSTRAINT fk_supplier_address_created_by
|
||||
FOREIGN KEY (created_by) REFERENCES "user" (id) ON DELETE SET NULL,
|
||||
CONSTRAINT fk_supplier_address_updated_by
|
||||
FOREIGN KEY (updated_by) REFERENCES "user" (id) ON DELETE SET NULL
|
||||
)
|
||||
SQL);
|
||||
$this->addSql('CREATE INDEX idx_supplier_address_supplier ON supplier_address (supplier_id)');
|
||||
|
||||
$this->comment('supplier_address', '_table', 'Adresses d un fournisseur (1:n) — type PROSPECT/DEPART/RENDU exclusif (RG-2.09), >= 1 site rattache (RG-2.06).');
|
||||
$this->comment('supplier_address', 'id', 'Identifiant interne auto-incremente.');
|
||||
$this->comment('supplier_address', 'supplier_id', 'FK -> supplier.id, ON DELETE CASCADE — fournisseur proprietaire de l adresse.');
|
||||
$this->comment('supplier_address', 'address_type', 'Type d adresse : PROSPECT | DEPART | RENDU (radio exclusif par construction — RG-2.09, chk_supplier_address_type).');
|
||||
$this->comment('supplier_address', 'country', 'Pays de l adresse — defaut France.');
|
||||
$this->comment('supplier_address', 'postal_code', 'Code postal (4-5 chiffres attendus).');
|
||||
$this->comment('supplier_address', 'city', 'Ville — preremplie depuis le code postal via API BAN cote front.');
|
||||
$this->comment('supplier_address', 'street', 'Numero et voie de l adresse.');
|
||||
$this->comment('supplier_address', 'street_complement', 'Complement d adresse (etage, batiment...) — optionnel.');
|
||||
$this->comment('supplier_address', 'bennes', 'Nombre de bennes sur le site fournisseur (entier nullable) — specifique fournisseur.');
|
||||
$this->comment('supplier_address', 'triage_provider', 'Le fournisseur est prestataire de triage sur cette adresse. Faux par defaut.');
|
||||
$this->comment('supplier_address', 'position', 'Ordre d affichage de l adresse dans la liste du fournisseur (croissant).');
|
||||
$this->addTimestampableBlamableComments('supplier_address');
|
||||
}
|
||||
|
||||
// =================================================================
|
||||
// Jointures de supplier_address (M2M)
|
||||
// =================================================================
|
||||
|
||||
private function createSupplierAddressJoinTables(): void
|
||||
{
|
||||
$this->addSql(<<<'SQL'
|
||||
CREATE TABLE supplier_address_site (
|
||||
supplier_address_id INT NOT NULL,
|
||||
site_id INT NOT NULL,
|
||||
PRIMARY KEY (supplier_address_id, site_id),
|
||||
CONSTRAINT fk_supplier_address_site_address
|
||||
FOREIGN KEY (supplier_address_id) REFERENCES supplier_address (id) ON DELETE CASCADE,
|
||||
CONSTRAINT fk_supplier_address_site_site
|
||||
FOREIGN KEY (site_id) REFERENCES site (id) ON DELETE RESTRICT
|
||||
)
|
||||
SQL);
|
||||
$this->comment('supplier_address_site', '_table', 'Jointure M2M supplier_address <-> site (Sites) — sites rattaches a l adresse (>= 1 obligatoire, RG-2.06).');
|
||||
$this->comment('supplier_address_site', 'supplier_address_id', 'FK -> supplier_address.id, ON DELETE CASCADE — adresse concernee.');
|
||||
$this->comment('supplier_address_site', 'site_id', 'FK -> site.id, ON DELETE RESTRICT — site rattache a l adresse.');
|
||||
|
||||
$this->addSql(<<<'SQL'
|
||||
CREATE TABLE supplier_address_contact (
|
||||
supplier_address_id INT NOT NULL,
|
||||
supplier_contact_id INT NOT NULL,
|
||||
PRIMARY KEY (supplier_address_id, supplier_contact_id),
|
||||
CONSTRAINT fk_supplier_address_contact_address
|
||||
FOREIGN KEY (supplier_address_id) REFERENCES supplier_address (id) ON DELETE CASCADE,
|
||||
CONSTRAINT fk_supplier_address_contact_contact
|
||||
FOREIGN KEY (supplier_contact_id) REFERENCES supplier_contact (id) ON DELETE CASCADE
|
||||
)
|
||||
SQL);
|
||||
$this->comment('supplier_address_contact', '_table', 'Jointure M2M supplier_address <-> supplier_contact — contacts associes a une adresse.');
|
||||
$this->comment('supplier_address_contact', 'supplier_address_id', 'FK -> supplier_address.id, ON DELETE CASCADE — adresse concernee.');
|
||||
$this->comment('supplier_address_contact', 'supplier_contact_id', 'FK -> supplier_contact.id, ON DELETE CASCADE — contact associe a l adresse.');
|
||||
|
||||
$this->addSql(<<<'SQL'
|
||||
CREATE TABLE supplier_address_category (
|
||||
supplier_address_id INT NOT NULL,
|
||||
category_id INT NOT NULL,
|
||||
PRIMARY KEY (supplier_address_id, category_id),
|
||||
CONSTRAINT fk_supplier_address_category_address
|
||||
FOREIGN KEY (supplier_address_id) REFERENCES supplier_address (id) ON DELETE CASCADE,
|
||||
CONSTRAINT fk_supplier_address_category_category
|
||||
FOREIGN KEY (category_id) REFERENCES category (id) ON DELETE RESTRICT
|
||||
)
|
||||
SQL);
|
||||
$this->comment('supplier_address_category', '_table', 'Jointure M2M supplier_address <-> category — categories d adresse de type FOURNISSEUR (RG-2.10).');
|
||||
$this->comment('supplier_address_category', 'supplier_address_id', 'FK -> supplier_address.id, ON DELETE CASCADE — adresse concernee.');
|
||||
$this->comment('supplier_address_category', 'category_id', 'FK -> category.id, ON DELETE RESTRICT — categorie d adresse de type FOURNISSEUR (RG-2.10).');
|
||||
}
|
||||
|
||||
// =================================================================
|
||||
// Sous-collection : RIB (1:n)
|
||||
// =================================================================
|
||||
|
||||
private function createSupplierRib(): void
|
||||
{
|
||||
$this->addSql(<<<'SQL'
|
||||
CREATE TABLE supplier_rib (
|
||||
id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
|
||||
supplier_id INT NOT NULL,
|
||||
label VARCHAR(120) NOT NULL,
|
||||
bic VARCHAR(20) NOT NULL,
|
||||
iban VARCHAR(34) NOT NULL,
|
||||
position INT DEFAULT 0 NOT 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 fk_supplier_rib_supplier
|
||||
FOREIGN KEY (supplier_id) REFERENCES supplier (id) ON DELETE CASCADE,
|
||||
CONSTRAINT fk_supplier_rib_created_by
|
||||
FOREIGN KEY (created_by) REFERENCES "user" (id) ON DELETE SET NULL,
|
||||
CONSTRAINT fk_supplier_rib_updated_by
|
||||
FOREIGN KEY (updated_by) REFERENCES "user" (id) ON DELETE SET NULL
|
||||
)
|
||||
SQL);
|
||||
$this->addSql('CREATE INDEX idx_supplier_rib_supplier ON supplier_rib (supplier_id)');
|
||||
|
||||
$this->comment('supplier_rib', '_table', 'Coordonnees bancaires d un fournisseur (1:n) — >= 1 RIB attendu selon le type de reglement (RG-2.08). Tous les champs audites (pas d AuditIgnore).');
|
||||
$this->comment('supplier_rib', 'id', 'Identifiant interne auto-incremente.');
|
||||
$this->comment('supplier_rib', 'supplier_id', 'FK -> supplier.id, ON DELETE CASCADE — fournisseur proprietaire du RIB.');
|
||||
$this->comment('supplier_rib', 'label', 'Libelle du RIB (ex: compte principal).');
|
||||
$this->comment('supplier_rib', 'bic', 'Code BIC/SWIFT de la banque (8 ou 11 caracteres).');
|
||||
$this->comment('supplier_rib', 'iban', 'IBAN du compte (≤ 34 caracteres).');
|
||||
$this->comment('supplier_rib', 'position', 'Ordre d affichage du RIB dans la liste du fournisseur (croissant).');
|
||||
$this->addTimestampableBlamableComments('supplier_rib');
|
||||
}
|
||||
|
||||
// =================================================================
|
||||
// Helpers
|
||||
// =================================================================
|
||||
|
||||
/**
|
||||
* Pose les 4 commentaires standardises Timestampable/Blamable sur une table,
|
||||
* en reutilisant le catalogue partage (source unique, cf. 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,
|
||||
));
|
||||
}
|
||||
}
|
||||
Reference in New Issue
Block a user