Node.js et MySQL avec mysql2 : requêtes préparées et transactions

-- sql/schema.sql : les tables, à exécuter dans une base existante
CREATE TABLE utilisateur (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  login        VARCHAR(30)  NOT NULL UNIQUE,
  nom          VARCHAR(50)  NOT NULL,
  prenom       VARCHAR(50)  NOT NULL,
  mot_de_passe VARCHAR(255) NOT NULL,            -- hachage argon2id, jamais le mot de passe
  role         ENUM('visiteur', 'comptable') NOT NULL DEFAULT 'visiteur'
) ENGINE = InnoDB;

CREATE TABLE frais_forfait (
  code    CHAR(3)      PRIMARY KEY,               -- ETP, KM, NUI, REP
  libelle VARCHAR(50)  NOT NULL,
  montant DECIMAL(6,2) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE fiche_frais (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  id_visiteur       INT     NOT NULL,
  mois              CHAR(7) NOT NULL,             -- '2026-10'
  etat              ENUM('CR', 'CL', 'VA', 'MP') NOT NULL DEFAULT 'CR',
  montant_valide    DECIMAL(8,2) NULL,
  date_modification DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT uq_fiche_visiteur_mois UNIQUE (id_visiteur, mois),
  CONSTRAINT fk_fiche_visiteur FOREIGN KEY (id_visiteur) REFERENCES utilisateur (id)
) ENGINE = InnoDB;

CREATE TABLE ligne_frais_forfait (
  id_fiche     INT     NOT NULL,
  code_forfait CHAR(3) NOT NULL,
  quantite     INT     NOT NULL DEFAULT 0 CHECK (quantite >= 0),
  PRIMARY KEY (id_fiche, code_forfait),
  CONSTRAINT fk_ligne_fiche FOREIGN KEY (id_fiche) REFERENCES fiche_frais (id) ON DELETE CASCADE,
  CONSTRAINT fk_ligne_forfait FOREIGN KEY (code_forfait) REFERENCES frais_forfait (code)
) ENGINE = InnoDB;

CREATE TABLE frais_hors_forfait (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  id_fiche     INT          NOT NULL,
  date_frais   DATE         NOT NULL,
  libelle      VARCHAR(100) NOT NULL,
  montant      DECIMAL(8,2) NOT NULL CHECK (montant > 0),
  justificatif VARCHAR(255) NULL,                -- nom du fichier stocké, pas le fichier
  CONSTRAINT fk_hf_fiche FOREIGN KEY (id_fiche) REFERENCES fiche_frais (id) ON DELETE CASCADE
) ENGINE = InnoDB;

-- sql/donnees.sql : jeu de démonstration et de test, rejouable (vide puis remplit)
DELETE FROM frais_hors_forfait;
DELETE FROM ligne_frais_forfait;
DELETE FROM fiche_frais;
DELETE FROM frais_forfait;
DELETE FROM utilisateur;

-- Mots de passe : Comptable#2026 pour adurand, Visiteur#2026 pour les deux autres
INSERT INTO utilisateur (id, login, nom, prenom, mot_de_passe, role) VALUES
  (1, 'adurand', 'Durand', 'Anne', '$argon2id$v=19$m=65536,p=4,t=3$0wk4bG2QzUP0uhzuSMo6oA$/WMVqyn1u8K8nbwnfLw7CwHZtcLEHOu5rKdnmVL1dPY', 'comptable'),
  (7, 'lmartin', 'Martin', 'Lucas', '$argon2id$v=19$m=65536,p=4,t=3$Dgts2gA9wpYgVIEfwBEhYQ$CPbE0ae6USdangrpcLEhpkWahdkUyvSEQ2WZba188lU', 'visiteur'),
  (8, 'svincent', 'Vincent', 'Sarah', '$argon2id$v=19$m=65536,p=4,t=3$fTKbOS3dBtCJVjmmn3L4AQ$76hP8Y5dtTRzbLf1beXjyiJmIQHG8YH6E9h5Z3LowYo', 'visiteur');

INSERT INTO frais_forfait (code, libelle, montant) VALUES
  ('ETP', 'Forfait étape', 110.00),
  ('KM', 'Frais kilométriques', 0.62),
  ('NUI', 'Nuitée hôtel', 80.00),
  ('REP', 'Repas restaurant', 25.00);

INSERT INTO fiche_frais (id, id_visiteur, mois, etat, montant_valide) VALUES
  (11, 7, '2026-09', 'VA', 389.20),
  (12, 7, '2026-10', 'CR', NULL),
  (13, 8, '2026-10', 'CL', NULL);

INSERT INTO ligne_frais_forfait (id_fiche, code_forfait, quantite) VALUES
  (11, 'ETP', 1), (11, 'KM', 160), (11, 'NUI', 1), (11, 'REP', 3),
  (12, 'ETP', 3), (12, 'KM', 420), (12, 'NUI', 2), (12, 'REP', 5),
  (13, 'ETP', 2), (13, 'KM', 250), (13, 'NUI', 1), (13, 'REP', 4);

INSERT INTO frais_hors_forfait (id, id_fiche, date_frais, libelle, montant) VALUES
  (57, 11, '2026-09-17', 'Taxi gare – hôtel', 25.00),
  (58, 12, '2026-10-14', 'Repas client à Lyon', 23.50),
  (59, 13, '2026-10-09', 'Parking gare de Grenoble', 12.00);

mysql -u root -p -e "CREATE DATABASE applifrais CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci"
mysql -u root -p applifrais < sql/schema.sql
mysql -u root -p applifrais < sql/donnees.sql

CREATE USER 'applifrais_api'@'%' IDENTIFIED BY 'un-mot-de-passe-long-et-aleatoire';
GRANT SELECT, INSERT, UPDATE, DELETE ON applifrais.* TO 'applifrais_api'@'%';

npm install mysql2

// src/db.js
import mysql from 'mysql2/promise';
import { config } from './config.js';

// Un pool partagé par toute l'application : des connexions ouvertes une fois, réutilisées.
export const pool = mysql.createPool({
  ...config.db,
  connectionLimit: 10,
  decimalNumbers: true, // DECIMAL renvoyé en nombre JavaScript, pas en chaîne
  dateStrings: true, // DATE et DATETIME renvoyés en chaînes, sans décalage de fuseau
});

const [lignes] = await pool.execute(
  'SELECT id, id_visiteur, mois, etat FROM fiche_frais WHERE id_visiteur = ? ORDER BY mois DESC',
  [7],
);
console.log(lignes);
// [ { id: 12, id_visiteur: 7, mois: '2026-10', etat: 'CR' },
//   { id: 11, id_visiteur: 7, mois: '2026-09', etat: 'VA' } ]

const [resultat] = await pool.execute(
  'INSERT INTO frais_hors_forfait (id_fiche, date_frais, libelle, montant) VALUES (?, ?, ?, ?)',
  [12, '2026-10-21', 'Péage A43', 12.2],
);
console.log(resultat.insertId);     // l'identifiant AUTO_INCREMENT généré, par exemple 60
console.log(resultat.affectedRows); // le nombre de lignes concernées : 1

// FAILLE : la saisie est collée dans le texte SQL
const [lignes] = await pool.query(`SELECT * FROM utilisateur WHERE login = '${req.body.login}'`);

// CORRECT : requête préparée, la valeur voyage à part
const [lignes] = await pool.execute('SELECT id, login, role FROM utilisateur WHERE login = ?', [req.body.login]);

const TRIS = { mois: 'mois', etat: 'etat', date: 'date_modification' };
const colonne = TRIS[req.valide.query.tri] ?? 'mois'; // seule une valeur prévue peut passer
const sql = `SELECT id, mois, etat FROM fiche_frais WHERE id_visiteur = ? ORDER BY ${colonne} DESC`;

// src/depots/fiches.depot.js
import { pool } from '../db.js';
import { ErreurApi } from '../erreurs.js';

// Une ligne de la table (snake_case) devient un objet de l'API (camelCase).
function versFiche(ligne) {
  return {
    id: ligne.id,
    visiteurId: ligne.id_visiteur,
    mois: ligne.mois,
    etat: ligne.etat,
    montantValide: ligne.montant_valide,
    dateModification: ligne.date_modification,
  };
}

export async function trouverParId(id) {
  const [lignes] = await pool.execute(
    `SELECT id, id_visiteur, mois, etat, montant_valide, date_modification
       FROM fiche_frais WHERE id = ?`,
    [id],
  );
  return lignes.length === 1 ? versFiche(lignes[0]) : null;
}

export async function lister({ visiteurId, mois, etat, page, limite }) {
  const conditions = [];
  const valeurs = [];
  if (visiteurId) {
    conditions.push('id_visiteur = ?');
    valeurs.push(visiteurId);
  }
  if (mois) {
    conditions.push('mois = ?');
    valeurs.push(mois);
  }
  if (etat) {
    conditions.push('etat = ?');
    valeurs.push(etat);
  }
  const where = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

  const [[{ total }]] = await pool.execute(`SELECT COUNT(*) AS total FROM fiche_frais ${where}`, valeurs);
  const [lignes] = await pool.execute(
    `SELECT id, id_visiteur, mois, etat, montant_valide, date_modification
       FROM fiche_frais ${where}
      ORDER BY mois DESC, id DESC
      LIMIT ? OFFSET ?`,
    [...valeurs, String(limite), String((page - 1) * limite)],
  );
  return { donnees: lignes.map(versFiche), pagination: { page, limite, total, pages: Math.ceil(total / limite) } };
}

export async function trouverDetail(id) {
  const fiche = await trouverParId(id);
  if (!fiche) return null;
  const [[visiteur]] = await pool.execute('SELECT id, nom, prenom FROM utilisateur WHERE id = ?', [fiche.visiteurId]);
  const [lignesForfait] = await pool.execute(
    `SELECT l.code_forfait AS code, f.libelle, l.quantite, f.montant AS montantUnitaire
       FROM ligne_frais_forfait l
       JOIN frais_forfait f ON f.code = l.code_forfait
      WHERE l.id_fiche = ?
      ORDER BY f.code`,
    [id],
  );
  const [fraisHorsForfait] = await pool.execute(
    `SELECT id, date_frais AS date, libelle, montant, justificatif IS NOT NULL AS justificatif
       FROM frais_hors_forfait WHERE id_fiche = ? ORDER BY date_frais, id`,
    [id],
  );
  const { visiteurId, ...reste } = fiche;
  return {
    ...reste,
    visiteur,
    lignesForfait,
    fraisHorsForfait: fraisHorsForfait.map((f) => ({ ...f, justificatif: Boolean(f.justificatif) })),
  };
}

{
  "id": 12, "mois": "2026-10", "etat": "CR", "montantValide": null,
  "dateModification": "2026-11-02 14:10:38",
  "visiteur": { "id": 7, "nom": "Martin", "prenom": "Lucas" },
  "lignesForfait": [
    { "code": "ETP", "libelle": "Forfait étape", "quantite": 3, "montantUnitaire": 110 },
    { "code": "KM", "libelle": "Frais kilométriques", "quantite": 420, "montantUnitaire": 0.62 },
    { "code": "NUI", "libelle": "Nuitée hôtel", "quantite": 2, "montantUnitaire": 80 },
    { "code": "REP", "libelle": "Repas restaurant", "quantite": 5, "montantUnitaire": 25 }
  ],
  "fraisHorsForfait": [
    { "id": 58, "date": "2026-10-14", "libelle": "Repas client à Lyon", "montant": 23.5, "justificatif": false }
  ],
  "total": 898.9
}

// Crée la fiche et ses quatre lignes de forfait : tout ou rien.
export async function creer(visiteurId, mois) {
  const connexion = await pool.getConnection();
  try {
    await connexion.beginTransaction();
    const [resultat] = await connexion.execute(
      'INSERT INTO fiche_frais (id_visiteur, mois) VALUES (?, ?)',
      [visiteurId, mois],
    );
    await connexion.execute(
      `INSERT INTO ligne_frais_forfait (id_fiche, code_forfait, quantite)
       SELECT ?, code, 0 FROM frais_forfait`,
      [resultat.insertId],
    );
    await connexion.commit();
    return resultat.insertId;
  } catch (erreur) {
    await connexion.rollback();
    if (erreur.code === 'ER_DUP_ENTRY') {
      throw new ErreurApi(409, 'fiche-existante', 'Fiche déjà créée', `Une fiche existe déjà pour le mois ${mois}.`);
    }
    throw erreur;
  } finally {
    connexion.release();
  }
}

// Change l'état seulement si la fiche est encore dans l'état attendu.
export async function changerEtat(id, etatAttendu, nouvelEtat, montantValide = null) {
  const [resultat] = await pool.execute(
    `UPDATE fiche_frais
        SET etat = ?, montant_valide = COALESCE(?, montant_valide)
      WHERE id = ? AND etat = ?`,
    [nouvelEtat, montantValide, id, etatAttendu],
  );
  return resultat.affectedRows === 1;
}

// src/server.js
function arreter(signal) {
  logger.info({ signal }, 'Arrêt demandé');
  serveur.close(async () => {
    await pool.end();
    process.exit(0);
  });
}
process.on('SIGTERM', arreter);
process.on('SIGINT', arreter);