dynasqy/backend/routes/presentation.js

const { Router } = require('express');
const pool = require('../config/db');
const { authenticate } = require('../middleware/auth');

const router = Router();

router.get('/:date', authenticate, async (req, res) => {
  if (req.user.role !== 'admin') return res.status(403).json({ error: 'Admin seulement' });
  try {
    const { date } = req.params;
    if (!/^\d{4}-\d{2}-\d{2}$/.test(date)) {
      return res.status(400).json({ error: 'Format de date invalide (AAAA-MM-JJ)' });
    }

    const [absences] = await pool.query(`
      SELECT a.membre_id, a.prevenu, a.motif,
             a.remplace_par_type, a.remplace_par_id, a.remplace_par_guest_id,
             r.prenom AS remplace_prenom, r.nom AS remplace_nom,
             rg.prenom AS remplace_guest_prenom, rg.nom AS remplace_guest_nom
      FROM absences a
      LEFT JOIN users r ON r.id = a.remplace_par_id AND (a.remplace_par_type = 'member' OR a.remplace_par_type IS NULL)
      LEFT JOIN invites rg ON rg.id = a.remplace_par_guest_id AND a.remplace_par_type = 'guest'
      WHERE a.date_absence = ?
    `, [date]);

    const [allMembers] = await pool.query(
      `SELECT id, prenom, nom, societe, activite, photo_url, merged_display_name, fonction, fonction_emoji,
              telephone, site_web, email
       FROM users WHERE actif = 1 ORDER BY fonction IS NULL, fonction, prenom`
    );

    const absentWithoutReplacement = new Set(
      absences.filter(a => !a.remplace_par_id && !a.remplace_par_guest_id).map(a => a.membre_id)
    );
    const members = allMembers.filter(m => !absentWithoutReplacement.has(m.id));

    const [guests] = await pool.query(
      `SELECT i.id, i.prenom, i.nom, i.societe, i.activite, i.photo_url,
              i.telephone, i.site_web, i.email, i.statut,
              u.prenom AS invite_par_prenom, u.nom AS invite_par_nom
       FROM invites i
       LEFT JOIN users u ON u.id = i.invite_par_id
       WHERE i.date_club = ? AND (i.statut != 'refusee' OR i.statut IS NULL)
       ORDER BY i.prenom`,
      [date]
    );

    const [currentHonorRow] = await pool.query(
      `SELECT CONCAT(u.prenom, ' ', u.nom) AS currentHonor
       FROM mises_en_honneur mh
       JOIN users u ON u.id = mh.membre_id
       WHERE mh.date = ?
       LIMIT 1`,
      [date]
    );
    const currentHonor = currentHonorRow[0]?.currentHonor || null;

    const [nextHonorRow] = await pool.query(
      `SELECT CONCAT(u.prenom, ' ', u.nom) AS nextHonor
       FROM mises_en_honneur mh
       JOIN users u ON u.id = mh.membre_id
       WHERE mh.date > ?
       ORDER BY mh.date ASC LIMIT 1`,
      [date]
    );
    const nextHonor = nextHonorRow[0]?.nextHonor || null;

    const [events] = await pool.query(
      `SELECT nom_evenement AS titre, date AS date_event, horaire AS heure_debut, lieu
       FROM evenements WHERE date >= ? ORDER BY date ASC LIMIT 5`,
      [date]
    );

    const [y, m] = date.split('-')
    const monthStart = `${y}-${m}-01`
    const lastDay = new Date(y, m, 0).getDate()
    const monthEnd = `${y}-${m}-${lastDay}`
    const [recommendations] = await pool.query(
      `SELECT r.id, r.type, r.nom_prospect, r.ca_realise, r.created_at,
              u.prenom, u.nom, u.societe
       FROM recommandations r
       JOIN users u ON u.id = r.membre_id
       WHERE r.created_at >= ? AND r.created_at <= ?
       ORDER BY r.created_at DESC LIMIT 10`,
      [monthStart, monthEnd]
    );

    const [yr, mo, dy] = date.split('-')
    const dateKey = `${dy}${mo}${yr}`
    const [memberSlides] = await pool.query(
      'SELECT id, member_id, filename FROM member_slides WHERE date_key = ?',
      [dateKey]
    );

    const [topInviters] = await pool.query(
      `SELECT u.id, u.prenom, u.nom, u.photo_url, COUNT(i.id) AS total
       FROM users u
       LEFT JOIN invites i ON i.invite_par_id = u.id AND i.created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
       WHERE u.actif = 1
       GROUP BY u.id, u.prenom, u.nom, u.photo_url
       ORDER BY total DESC LIMIT 3`
    );

    const [topDonnees] = await pool.query(
      `SELECT u.id, u.prenom, u.nom, u.photo_url, COUNT(r.id) AS total
       FROM users u
       LEFT JOIN recommandations r ON r.membre_id = u.id
       WHERE u.actif = 1
       GROUP BY u.id, u.prenom, u.nom, u.photo_url
       ORDER BY total DESC LIMIT 3`
    );

    const [topConversion] = await pool.query(
      `SELECT u.id, u.prenom, u.nom, u.photo_url, COUNT(m2.id) AS total
       FROM users u
       LEFT JOIN invites i ON i.invite_par_id = u.id AND i.statut = 'acceptee'
       LEFT JOIN users m2 ON m2.email = i.email
       WHERE u.actif = 1
       GROUP BY u.id, u.prenom, u.nom, u.photo_url
       HAVING total > 0
       ORDER BY total DESC LIMIT 3`
    );

    const [overrides] = await pool.query(
      'SELECT slide_key, content, hidden, sort_order FROM presentation_overrides WHERE date_key = ?',
      [dateKey]
    );

    res.json({ members, guests, absences, currentHonor: currentHonor || null, nextHonor: nextHonor || null, events, recommendations, memberSlides, topInviters, topDonnees, topConversion, overrides });
  } catch (err) {
    console.error(err);
    res.status(500).json({ error: 'Erreur serveur' });
  }
});

module.exports = router;