crowdlending-app/_to_delete/dashboardPatrimoine.js.bak_round5

import { Router } from 'express';
import db from '../db/index.js';

const router = Router();
// requireAuth appliqué dans server.js

/**
 * routes/dashboardPatrimoine.js — vue consolidée du patrimoine par regroupement (workspace
 * "Patrimoine", chantier du 28/09/26, demande Olivier : "j'aimerais que l'on réfléchisse sur le
 * comment la restitution du patrimoine va se passer... un espace de travail particulier :
 * 'Patrimoine'"). Lecture seule, scope utilisateur (req.user.id) — pas de CRUD ici, seulement
 * de l'agrégation en lecture sur des données déjà gérées ailleurs (Paramètres > Mes comptes,
 * Emprunts, Admin > Enveloppes & regroupements).
 *
 * Cadrage validé par Olivier (AskUserQuestion, 28/09/26 (suite)) :
 *  - Respecter l'accès utilisateur : Crowdlending/Private Equity ne sont montrés/agrégés que
 *    si l'utilisateur a effectivement accès à ces workspaces (granted_by_admin=1 — pas
 *    seulement actif_by_user, cf. userHasWorkspaceAccess ci-dessous).
 *  - Un seul dashboard consolidé pour démarrer (pas de sous-pages par regroupement en v1).
 *
 * Périmètre :
 *  - Comptes courants / Comptes d'épargne / Comptes d'investissement / Immobilier / Crypto /
 *    Autres actifs : somme de comptes.solde_eur (saisie manuelle, cf. migration solde_eur dans
 *    db/index.js) rattachés via comptes.enveloppe_id → enveloppes_referentiel
 *    .regroupement_patrimonial_id (cf. plan_regroupements_patrimoniaux.md).
 *  - Passif : somme de emprunts.capital_restant_du.
 *  - Crowdlending et Private Equity : PAS des comptes.solde_eur (ces 2 regroupements n'ont
 *    aucune enveloppe eligible_compte=1, cf. `structurellement_vide` ci-dessous) — le total
 *    vient directement du portefeuille réel de leur workspace dédié, avec EXACTEMENT la même
 *    métrique que celle déjà affichée là-bas (crowdlendingCapitalInvesti /
 *    privateEquityCapitalInvesti ci-dessous — pas une nouvelle définition, un ré-affichage à
 *    l'identique). Ajouté le 29/09/26 suite à la remarque d'Olivier, qui s'attendait à voir
 *    cette valeur dès la v1 initiale (celle-ci se contentait d'un placeholder
 *    `portefeuille_a_venir`). N'apparaît que si l'utilisateur a effectivement accès au
 *    workspace correspondant (`access.crowdlending` / `access.private_equity`) — sinon le nœud
 *    reste `portefeuille_a_venir: true` avec un total à 0, et le frontend le masque entièrement.
 *
 * `structurellement_vide` (aucune enveloppe eligible_compte=1 dans ce regroupement, cf.
 * migration eligible_compte, db/index.js — le sélecteur "Enveloppe" de Mes comptes ne propose
 * QUE les enveloppes eligible_compte=1) signale un regroupement qui ne peut recevoir AUCUN
 * solde via comptes.solde_eur, quoi que l'utilisateur saisisse : aujourd'hui Immobilier et
 * Autres actifs (0 enveloppe éligible chacun — vérifié empiriquement ci-dessous, pas codé en
 * dur). Crowdlending et Private Equity ont eux aussi 0 enveloppe éligible mais ne portent PAS
 * ce flag : leur total vient du portefeuille réel (ci-dessus), pas d'une saisie manuelle en
 * attente. Distinct d'un total à 0 € réellement saisi : le frontend doit l'afficher
 * explicitement ("pas encore de saisie possible ici") plutôt qu'un "0 €" qui laisserait croire
 * que le patrimoine de cette famille est nul.
 *
 * `total_eur` d'un regroupement PARENT (aujourd'hui : "Comptes d'investissement", seul
 * regroupement à avoir un enfant, "Private Equity") est un total ROULÉ, cf. buildNode :
 * total propre du parent (ses comptes.solde_eur rattachés directement) + total de chacun de
 * ses enfants. Corrigé le 29/09/26 (retour d'Olivier : "la somme des sous-catégories doit se
 * retrouver remontée en total au niveau de 'comptes d'investissement'" — bug de la v1, qui
 * affichait le total du parent sans ses enfants).
 *
 * `lignes` (29/09/26, retour d'Olivier : "je voudrais... un système... pour développer soit par
 * plateforme ou par compte/livret/produit") — détail par regroupement pour l'affichage
 * dépliable côté frontend : liste des comptes (nom, établissement, détenteur, solde) pour les
 * regroupements alimentés par comptes.solde_eur, liste des emprunts pour Passif, et répartition
 * par plateforme (capital investi, nb d'investissements, détenteur agrégé) pour Crowdlending/Private Equity —
 * "je m'attendrais à voir au moins le capital investi par plateforme". Pas de colonne
 * variation/performance (contrairement aux captures d'écran d'app tierce qu'Olivier a
 * partagées en référence) : comptes.solde_eur et emprunts.capital_restant_du sont des valeurs
 * instantanées, sans historique conservé en base — un vrai suivi de performance nécessiterait
 * de capturer un solde à chaque date (hors périmètre de ce chantier).
 */

function userHasWorkspaceAccess(userId, type) {
  const row = db.prepare(`
    SELECT 1 FROM user_workspaces uw
    JOIN workspaces w ON w.id = uw.workspace_id
    WHERE uw.user_id = ? AND uw.granted_by_admin = 1 AND w.type = ?
    LIMIT 1
  `).get(userId, type);
  return !!row;
}

// Regroupements dont le total ne vient pas de comptes.solde_eur mais du portefeuille réel du
// workspace dédié — cf. commentaire d'en-tête. Identifiés par nom (pas d'autre marqueur en base
// à ce jour) plutôt que par une colonne dédiée : seul cas d'usage pour l'instant, ne justifie
// pas une migration.
const PORTEFEUILLE_NOMS = new Set(['Crowdlending', 'Private Equity']);

// "Capital investi" Crowdlending (29/09/26, suite à la remarque d'Olivier qui s'attendait à
// voir cette valeur dès la v1) — reprend EXACTEMENT la même métrique que le KPI "Capital
// investi" du Dashboard crowdlending (Dashboard.jsx : portfolio.encours + portfolio.en_defaut,
// cf. routes/dashboard.js) : capital actuellement engagé (statuts en_cours/en_retard/procedure
// uniquement — exclut rembourse/cloture), net des réinvestissements et remboursements de
// capital. Pas une nouvelle définition : un ré-affichage à l'identique, agrégé sur tous les
// investisseurs de l'utilisateur (comme comptes/emprunts ailleurs dans ce fichier).
const CL_CAPITAL_CASE = `CASE WHEN i.statut IN ('en_cours', 'en_retard', 'procedure') THEN
      i.montant_investi
      + COALESCE((SELECT SUM(rv.montant) FROM reinvestissements rv WHERE rv.investissement_id = i.id), 0)
      - COALESCE((SELECT SUM(rb.capital) FROM remboursements rb WHERE rb.investissement_id = i.id AND rb.type = 'normal'), 0)
    ELSE 0 END`;

function crowdlendingCapitalInvesti(userId) {
  const row = db.prepare(`
    SELECT COALESCE(SUM(${CL_CAPITAL_CASE}), 0) AS capital_investi
    FROM investissements i
    WHERE i.investisseur_id IN (SELECT id FROM investisseurs WHERE user_id = ?)
  `).get(userId);
  return row.capital_investi;
}

// Détenteur agrégé pour une ligne groupée par plateforme (Crowdlending/PE) : GROUP_CONCAT
// DISTINCT ramène tous les détenteurs distincts séparés par virgule — un seul nom si tous les
// investissements de la plateforme partagent le même détenteur, "Plusieurs" s'ils divergent
// (foyer avec plusieurs investisseurs sur la même plateforme).
//
// Corrigé le 29/09/26 (suite, suite) : `inv.nom` seul, plus de concaténation avec `inv.prenom`.
// Olivier a signalé un second problème sur sa base réelle une fois la colonne Détenteur
// remplie ("Encore un pb sur l'affichage des détenteurs") : "Olivier Olivier CROGUENNEC" au
// lieu de "Olivier CROGUENNEC". Cause : investisseurs.nom stocke déjà le NOM COMPLET
// (prénom + nom de famille) pour un profil "famille" — cf. INSERT dans auth.js (création du
// profil principal à l'inscription : `nom = fullName`, `prenom` extrait séparément du même
// fullName) et FamilleEntreprises.jsx (le formulaire d'édition reconstruit `nom_famille` en
// retirant le prénom de `nom` : `m.nom.replace(m.prenom, '')`, preuve que `nom` contient déjà
// tout). Pour un profil "entreprise", `prenom` est NULL et `nom` est simplement la raison
// sociale. Dans les deux cas, `inv.nom` seul EST le nom d'affichage — le préfixer par
// `inv.prenom` (comme le faisaient encore `comptesLignesStmt`/`empruntsLignesStmt` plus bas,
// corrigés au même moment) dupliquait le prénom. Vérifié en lecture seule sur une copie locale
// de la base réelle d'Olivier (investisseurs.nom = "Olivier CROGUENNEC", "Capucine CROGUENNEC",
// "Marine CROGUENNEC" — jamais juste le nom de famille).
function toDetenteur(concat) {
  if (!concat) return null;
  const noms = [...new Set(concat.split(',').map(s => s.trim()).filter(Boolean))];
  if (noms.length === 0) return null;
  if (noms.length === 1) return noms[0];
  return 'Plusieurs';
}

// Répartition du capital investi Crowdlending par plateforme (même métrique que
// crowdlendingCapitalInvesti ci-dessus, groupée) — demande Olivier 29/09/26 : "je m'attendrais
// à voir au moins le capital investi par plateforme". `detenteur` ajouté le 29/09/26 (suite) :
// Olivier a signalé que la colonne Détenteur restait vide sur ces lignes ("Cela n'a pas l'air
// de fonctionner pour les détenteurs") — chaque investissement porte bien un investisseur_id
// (détenteur), l'agrégation par plateforme se contentait jusque-là de ne pas le remonter.
function crowdlendingLignes(userId) {
  const rows = db.prepare(`
    SELECT p.id AS id, p.nom AS label,
      COALESCE(SUM(${CL_CAPITAL_CASE}), 0) AS valeur_eur,
      COUNT(CASE WHEN i.statut IN ('en_cours', 'en_retard', 'procedure') THEN 1 END) AS nb_lignes,
      GROUP_CONCAT(DISTINCT inv.nom) AS detenteurs_concat
    FROM investissements i
    JOIN plateformes p ON p.id = i.plateforme_id
    JOIN investisseurs inv ON inv.id = i.investisseur_id
    WHERE i.investisseur_id IN (SELECT id FROM investisseurs WHERE user_id = ?)
    GROUP BY p.id, p.nom
    HAVING valeur_eur > 0
    ORDER BY valeur_eur DESC
  `).all(userId);
  return rows.map(({ detenteurs_concat, ...r }) => ({ ...r, detenteur: toDetenteur(detenteurs_concat) }));
}

// "Capital investi total" Private Equity — reprend EXACTEMENT la même métrique que le KPI
// "Capital investi total" du Dashboard PE (DashboardPe.jsx : somme de montant_investi sur tous
// les deals, tous statuts confondus — modèle PE différent du crowdlending, pas d'échéancier ni
// de notion d'encours net). Agrégé sur tous les investisseurs de l'utilisateur, tous workspaces
// de type private_equity confondus (le type peut avoir plusieurs exemplaires, cf.
// adminWorkspaces.js).
function privateEquityCapitalInvesti(userId) {
  const row = db.prepare(`
    SELECT COALESCE(SUM(montant_investi), 0) AS capital_investi
    FROM investissements_pe
    WHERE investisseur_id IN (SELECT id FROM investisseurs WHERE user_id = ?)
  `).get(userId);
  return row.capital_investi;
}

// `detenteur` ajouté le 29/09/26 — même correction que crowdlendingLignes ci-dessus.
function privateEquityLignes(userId) {
  const rows = db.prepare(`
    SELECT p.id AS id, p.nom AS label,
      COALESCE(SUM(ipe.montant_investi), 0) AS valeur_eur,
      COUNT(*) AS nb_lignes,
      GROUP_CONCAT(DISTINCT inv.nom) AS detenteurs_concat
    FROM investissements_pe ipe
    JOIN plateformes p ON p.id = ipe.plateforme_id
    JOIN investisseurs inv ON inv.id = ipe.investisseur_id
    WHERE ipe.investisseur_id IN (SELECT id FROM investisseurs WHERE user_id = ?)
    GROUP BY p.id, p.nom
    HAVING valeur_eur > 0
    ORDER BY valeur_eur DESC
  `).all(userId);
  return rows.map(({ detenteurs_concat, ...r }) => ({ ...r, detenteur: toDetenteur(detenteurs_concat) }));
}

router.get('/', (req, res) => {
  const userId = req.user.id;

  const roots = db.prepare(`
    SELECT id, nom, parent_id, type, ordre_affichage
    FROM regroupements_patrimoniaux
    WHERE parent_id IS NULL
    ORDER BY ordre_affichage, nom
  `).all();
  const children = db.prepare(`
    SELECT id, nom, parent_id, type, ordre_affichage
    FROM regroupements_patrimoniaux
    WHERE parent_id IS NOT NULL
    ORDER BY ordre_affichage, nom
  `).all();

  const soldeStmt = db.prepare(`
    SELECT
      COALESCE(SUM(c.solde_eur), 0) AS total,
      SUM(CASE WHEN c.solde_eur IS NOT NULL THEN 1 ELSE 0 END) AS nb_avec_solde,
      SUM(CASE WHEN c.solde_eur IS NULL THEN 1 ELSE 0 END) AS nb_sans_solde
    FROM comptes c
    JOIN enveloppes_referentiel er ON er.id = c.enveloppe_id
    WHERE c.user_id = ? AND er.regroupement_patrimonial_id = ?
  `);
  // Lignes de détail "par compte" — établissement (institution du référentiel, sinon le champ
  // texte libre banque) et détenteur, pour l'affichage dépliable (cf. commentaire d'en-tête).
  // Ne remonte que les comptes avec un solde saisi (NULL exclu, non pertinent en liste).
  const comptesLignesStmt = db.prepare(`
    SELECT c.id AS id, c.nom AS label,
      COALESCE(ir.nom, c.banque) AS sous_label,
      c.solde_eur AS valeur_eur,
      inv.nom AS detenteur
    FROM comptes c
    LEFT JOIN institutions_referentiel ir ON ir.id = c.institution_id
    LEFT JOIN investisseurs inv ON inv.id = c.investisseur_id
    JOIN enveloppes_referentiel er ON er.id = c.enveloppe_id
    WHERE c.user_id = ? AND er.regroupement_patrimonial_id = ? AND c.solde_eur IS NOT NULL
    ORDER BY c.solde_eur DESC
  `);
  const eligibleCountStmt = db.prepare(`
    SELECT COUNT(*) AS n FROM enveloppes_referentiel
    WHERE regroupement_patrimonial_id = ? AND eligible_compte = 1
  `);
  const passifStmt = db.prepare(`
    SELECT COALESCE(SUM(capital_restant_du), 0) AS total, COUNT(*) AS n
    FROM emprunts WHERE user_id = ? AND regroupement_patrimonial_id = ?
  `);
  const empruntsLignesStmt = db.prepare(`
    SELECT e.id AS id, e.nom AS label,
      ir.nom AS sous_label,
      e.capital_restant_du AS valeur_eur,
      inv.nom AS detenteur
    FROM emprunts e
    LEFT JOIN institutions_referentiel ir ON ir.id = e.institution_id
    LEFT JOIN investisseurs inv ON inv.id = e.investisseur_id
    WHERE e.user_id = ? AND e.regroupement_patrimonial_id = ?
    ORDER BY e.capital_restant_du DESC
  `);

  const accessCache = {
    crowdlending: userHasWorkspaceAccess(userId, 'crowdlending'),
    private_equity: userHasWorkspaceAccess(userId, 'private_equity'),
  };

  function buildNode(r) {
    const isPortefeuille = PORTEFEUILLE_NOMS.has(r.nom);
    const eligibleCount = eligibleCountStmt.get(r.id).n;
    const structurellementVide = !isPortefeuille && eligibleCount === 0;

    let ownTotalEur = 0, nbAvecSolde = 0, nbSansSolde = 0, sourcePortefeuille = false, portefeuilleAVenir = false;
    let lignes = [];

    if (r.nom === 'Crowdlending') {
      if (accessCache.crowdlending) {
        ownTotalEur = crowdlendingCapitalInvesti(userId);
        sourcePortefeuille = true;
        lignes = crowdlendingLignes(userId);
      } else {
        portefeuilleAVenir = true;
      }
    } else if (r.nom === 'Private Equity') {
      if (accessCache.private_equity) {
        ownTotalEur = privateEquityCapitalInvesti(userId);
        sourcePortefeuille = true;
        lignes = privateEquityLignes(userId);
      } else {
        portefeuilleAVenir = true;
      }
    } else if (r.type === 'actif') {
      const s = soldeStmt.get(userId, r.id);
      ownTotalEur = s.total; nbAvecSolde = s.nb_avec_solde; nbSansSolde = s.nb_sans_solde;
      lignes = comptesLignesStmt.all(userId, r.id);
    } else {
      const p = passifStmt.get(userId, r.id);
      ownTotalEur = p.total; nbAvecSolde = p.n; nbSansSolde = 0;
      lignes = empruntsLignesStmt.all(userId, r.id);
    }

    const enfants = children.filter(ch => ch.parent_id === r.id).map(buildNode);
    // Total ROULÉ (29/09/26, correctif) : propre total + total de chaque enfant — un enfant
    // (ex. Private Equity) n'a lui-même pas d'enfant (hiérarchie à 2 niveaux max, cf.
    // regroupementsPatrimoniaux.js), donc pas de risque de double-comptage plus profond.
    const totalEur = ownTotalEur + enfants.reduce((s, e) => s + e.total_eur, 0);

    return {
      id: r.id,
      nom: r.nom,
      type: r.type,
      ordre_affichage: r.ordre_affichage,
      total_eur: totalEur,
      nb_comptes_avec_solde: nbAvecSolde,
      nb_comptes_sans_solde: nbSansSolde,
      structurellement_vide: structurellementVide,
      portefeuille_a_venir: portefeuilleAVenir,
      source_portefeuille: sourcePortefeuille,
      lignes,
      enfants,
    };
  }

  const tree = roots.map(buildNode);

  // Les totaux racine sont désormais roulés (ils incluent déjà leurs enfants, cf. buildNode) —
  // une simple somme des racines par type suffit, plus besoin de descendre récursivement dans
  // l'arbre (la hiérarchie ne dépasse de toute façon pas 2 niveaux, cf. plus haut).
  const totalActif = tree.filter(n => n.type === 'actif').reduce((s, n) => s + n.total_eur, 0);
  const totalPassif = tree.filter(n => n.type === 'passif').reduce((s, n) => s + n.total_eur, 0);

  res.json({
    access: accessCache,
    total_actif_eur: totalActif,
    total_passif_eur: totalPassif,
    net_eur: totalActif - totalPassif,
    regroupements: tree,
  });
});

export default router;