-- Schéma dirCops — suivi facturation / échéancier / paiement -- MariaDB/MySQL -- Factures : type_produit (alevins, larves), cycle (24-25, 25-26), qte_livree SET NAMES utf8mb4; CREATE TABLE IF NOT EXISTS clients ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, nom VARCHAR(255) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS factures ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, client_id INT UNSIGNED NOT NULL, n_facture VARCHAR(64) NOT NULL, n_vente VARCHAR(64) DEFAULT NULL, date_facture DATE NOT NULL, type_produit VARCHAR(32) DEFAULT NULL COMMENT 'alevins, larves', cycle VARCHAR(16) DEFAULT NULL COMMENT 'ex: 24-25, 25-26', quantites DECIMAL(12,2) DEFAULT NULL, qte_livree DECIMAL(12,2) DEFAULT NULL, montant_ht DECIMAL(12,2) NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_factures_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE ); CREATE TABLE IF NOT EXISTS paiements ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, facture_id INT UNSIGNED NOT NULL, montant DECIMAL(12,2) NOT NULL, date_paiement DATE NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_paiements_facture FOREIGN KEY (facture_id) REFERENCES factures(id) ON DELETE CASCADE, CONSTRAINT chk_paiements_montant CHECK (montant > 0) ); CREATE TABLE IF NOT EXISTS echeanciers ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, facture_id INT UNSIGNED NOT NULL, nb_echeances INT UNSIGNED NOT NULL, date_debut_echeancier DATE NOT NULL, montant_par_echeance DECIMAL(12,2) NOT NULL COMMENT 'Montant calculé au moment de la création (encours / nb_echeances)', created_at DATETIME DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_echeanciers_facture FOREIGN KEY (facture_id) REFERENCES factures(id) ON DELETE CASCADE, CONSTRAINT chk_echeanciers_nb_echeances CHECK (nb_echeances > 0), CONSTRAINT chk_echeanciers_montant CHECK (montant_par_echeance > 0), UNIQUE KEY uk_echeanciers_facture_id (facture_id) COMMENT 'Une facture = un écheancier maximum' ); CREATE INDEX idx_factures_client_id ON factures(client_id); CREATE INDEX idx_factures_type_produit ON factures(type_produit); CREATE INDEX idx_factures_cycle ON factures(cycle); CREATE INDEX idx_paiements_facture_id ON paiements(facture_id); CREATE INDEX idx_paiements_date ON paiements(date_paiement); CREATE INDEX idx_echeanciers_facture_id ON echeanciers(facture_id);