GestionLocative/backend/src/routes/charges.js

const express = require('express');
const path = require('path');
const fs = require('fs');
const multer = require('multer');
const PDFDocument = require('pdfkit');
const { PDFDocument: LibPDFDocument, rgb, StandardFonts } = require('pdf-lib');
const db = require('../db');
const { authMiddleware } = require('../auth');

const router = express.Router();
router.use(authMiddleware);

// Configure multer for invoice uploads
const uploadDir = path.join(__dirname, '../../uploads');
if (!fs.existsSync(uploadDir)) fs.mkdirSync(uploadDir, { recursive: true });

const storage = multer.diskStorage({
  destination: (req, file, cb) => cb(null, uploadDir),
  filename: (req, file, cb) => {
    const ext = path.extname(file.originalname);
    cb(null, `charge_${Date.now()}_${Math.random().toString(36).slice(2)}${ext}`);
  }
});
const upload = multer({
  storage,
  limits: { fileSize: 10 * 1024 * 1024 }, // 10MB
  fileFilter: (req, file, cb) => {
    const allowed = ['.pdf', '.jpg', '.jpeg', '.png', '.webp'];
    if (allowed.includes(path.extname(file.originalname).toLowerCase())) cb(null, true);
    else cb(new Error('Fichier non autorisé (PDF, JPG, PNG)'));
  }
});

// List charges (filter by property_id, year)
router.get('/', (req, res) => {
  const { property_id, year } = req.query;
  let query = `
    SELECT c.*, p.name as property_name
    FROM charges c
    JOIN properties p ON c.property_id = p.id
    WHERE c.user_id = ?
  `;
  const params = [req.userId];
  if (property_id) { query += ' AND c.property_id = ?'; params.push(property_id); }
  if (year) { query += ' AND c.year = ?'; params.push(year); }
  query += ' ORDER BY c.date DESC';
  res.json(db.prepare(query).all(...params));
});

// Create charge (with optional invoice file)
router.post('/', upload.single('invoice'), (req, res) => {
  const { property_id, category, label, amount, date, year, notes, recoverable } = req.body;
  if (!property_id || !category || !label || !amount || !date || !year)
    return res.status(400).json({ error: 'Champs requis manquants' });

  const prop = db.prepare('SELECT id FROM properties WHERE id = ? AND user_id = ?').get(property_id, req.userId);
  if (!prop) return res.status(403).json({ error: 'Bien non autorisé' });

  const invoice_path = req.file ? req.file.filename : null;
  const invoice_name = req.file ? req.file.originalname : null;

  const result = db.prepare(`
    INSERT INTO charges (user_id, property_id, category, label, amount, date, year, invoice_path, invoice_name, notes, recoverable)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `).run(req.userId, property_id, category, label, parseFloat(amount), date, parseInt(year), invoice_path, invoice_name, notes || null, recoverable === 'false' || recoverable === '0' ? 0 : 1);

  res.status(201).json({ id: result.lastInsertRowid });
});

// Update charge (with optional new invoice)
router.put('/:id', upload.single('invoice'), (req, res) => {
  const charge = db.prepare('SELECT * FROM charges WHERE id = ? AND user_id = ?').get(req.params.id, req.userId);
  if (!charge) return res.status(404).json({ error: 'Charge non trouvée' });

  const { category, label, amount, date, year, notes, recoverable } = req.body;

  let invoice_path = charge.invoice_path;
  let invoice_name = charge.invoice_name;

  if (req.file) {
    // Delete old file
    if (charge.invoice_path) {
      const oldPath = path.join(uploadDir, charge.invoice_path);
      if (fs.existsSync(oldPath)) fs.unlinkSync(oldPath);
    }
    invoice_path = req.file.filename;
    invoice_name = req.file.originalname;
  }

  db.prepare(`
    UPDATE charges SET category=?, label=?, amount=?, date=?, year=?, invoice_path=?, invoice_name=?, notes=?, recoverable=?
    WHERE id=?
  `).run(category, label, parseFloat(amount), date, parseInt(year), invoice_path, invoice_name, notes || null, recoverable === 'false' || recoverable === '0' ? 0 : 1, req.params.id);

  res.json({ success: true });
});

// Delete charge
router.delete('/:id', (req, res) => {
  const charge = db.prepare('SELECT * FROM charges WHERE id = ? AND user_id = ?').get(req.params.id, req.userId);
  if (!charge) return res.status(404).json({ error: 'Charge non trouvée' });

  if (charge.invoice_path) {
    const filePath = path.join(uploadDir, charge.invoice_path);
    if (fs.existsSync(filePath)) fs.unlinkSync(filePath);
  }

  db.prepare('DELETE FROM charges WHERE id = ?').run(req.params.id);
  res.json({ success: true });
});

// Download invoice
router.get('/:id/invoice', (req, res) => {
  const charge = db.prepare('SELECT * FROM charges WHERE id = ? AND user_id = ?').get(req.params.id, req.userId);
  if (!charge || !charge.invoice_path) return res.status(404).json({ error: 'Facture non trouvée' });

  const filePath = path.join(uploadDir, charge.invoice_path);
  if (!fs.existsSync(filePath)) return res.status(404).json({ error: 'Fichier introuvable' });

  res.setHeader('Content-Disposition', `attachment; filename="${charge.invoice_name}"`);
  res.sendFile(filePath);
});

// Annual bilan for a property
router.get('/bilan/:property_id/:year', (req, res) => {
  const { property_id, year } = req.params;

  // Verify property ownership
  const prop = db.prepare('SELECT * FROM properties WHERE id = ? AND user_id = ?').get(property_id, req.userId);
  if (!prop) return res.status(403).json({ error: 'Bien non autorisé' });

  // Total recoverable charges for this property/year (for tenant billing)
  const totalChargesRow = db.prepare(`
    SELECT SUM(amount) as total, COUNT(*) as count
    FROM charges WHERE property_id = ? AND year = ? AND user_id = ? AND recoverable = 1
  `).get(property_id, year, req.userId);

  // Total non-recoverable (landlord's own charges, for info)
  const totalNonRecovRow = db.prepare(`
    SELECT SUM(amount) as total, COUNT(*) as count
    FROM charges WHERE property_id = ? AND year = ? AND user_id = ? AND recoverable = 0
  `).get(property_id, year, req.userId);

  const totalChargesReelles = totalChargesRow?.total || 0;
  const totalNonRecoverable = totalNonRecovRow?.total || 0;

  // Charges by category (recoverable only)
  const chargesByCategory = db.prepare(`
    SELECT category, SUM(amount) as total
    FROM charges WHERE property_id = ? AND year = ? AND user_id = ? AND recoverable = 1
    GROUP BY category ORDER BY total DESC
  `).all(property_id, year, req.userId);

  // Non-recoverable by category (for info)
  const chargesNonRecovByCategory = db.prepare(`
    SELECT category, SUM(amount) as total
    FROM charges WHERE property_id = ? AND year = ? AND user_id = ? AND recoverable = 0
    GROUP BY category ORDER BY total DESC
  `).all(property_id, year, req.userId);

  // All leases on this property (active or that were active during the year)
  const leases = db.prepare(`
    SELECT l.*, t.first_name, t.last_name, t.email
    FROM leases l
    JOIN tenants t ON l.tenant_id = t.id
    WHERE l.property_id = ? AND l.user_id = ?
      AND (l.start_date <= ? AND (l.end_date IS NULL OR l.end_date >= ?))
  `).all(property_id, req.userId, `${year}-12-31`, `${year}-01-01`);

  // For each lease, get provisions perçues for the year
  const leaseBilans = leases.map(lease => {
    const provisions = db.prepare(`
      SELECT SUM(charges_paid) as total
      FROM payments
      WHERE lease_id = ? AND period_year = ?
    `).get(lease.id, year);

    return {
      lease_id: lease.id,
      tenant_name: `${lease.first_name} ${lease.last_name}`,
      tenant_email: lease.email,
      monthly_provision: lease.charges_amount,
      provisions_percues: provisions?.total || 0
    };
  });

  // Calculate total provisions across all tenants for proportional split
  const totalProvisions = leaseBilans.reduce((s, l) => s + l.provisions_percues, 0);

  // Compute each tenant's share
  const bilans = leaseBilans.map(l => {
    const ratio = totalProvisions > 0 ? l.provisions_percues / totalProvisions : (leaseBilans.length > 0 ? 1 / leaseBilans.length : 0);
    const quote_part_charges = totalChargesReelles * ratio;
    const trop_percu = l.provisions_percues - quote_part_charges;
    return {
      ...l,
      ratio_percent: Math.round(ratio * 100 * 100) / 100,
      quote_part_charges: Math.round(quote_part_charges * 100) / 100,
      trop_percu: Math.round(trop_percu * 100) / 100
    };
  });

  res.json({
    property: prop,
    year: parseInt(year),
    total_charges_reelles: Math.round(totalChargesReelles * 100) / 100,
    total_non_recoverable: Math.round(totalNonRecoverable * 100) / 100,
    charges_count: totalChargesRow?.count || 0,
    charges_by_category: chargesByCategory,
    charges_non_recov_by_category: chargesNonRecovByCategory,
    total_provisions_percues: Math.round(totalProvisions * 100) / 100,
    bilans
  });
});

// Export PDF bilan + invoices
router.get('/bilan/:property_id/:year/pdf', async (req, res) => {
  const { property_id, year } = req.params;

  const prop = db.prepare('SELECT * FROM properties WHERE id = ? AND user_id = ?').get(property_id, req.userId);
  if (!prop) return res.status(403).json({ error: 'Bien non autorisé' });

  const owner = db.prepare('SELECT name, first_name, last_name, email, address, phone FROM users WHERE id = ?').get(req.userId);
  const ownerFullName = (owner.first_name && owner.last_name) ? `${owner.first_name} ${owner.last_name}` : owner.name;

  const totalChargesRow = db.prepare(`
    SELECT SUM(amount) as total, COUNT(*) as count FROM charges
    WHERE property_id = ? AND year = ? AND user_id = ? AND recoverable = 1
  `).get(property_id, year, req.userId);
  const totalChargesReelles = totalChargesRow?.total || 0;

  const chargesList = db.prepare(`
    SELECT * FROM charges WHERE property_id = ? AND year = ? AND user_id = ?
    ORDER BY date ASC
  `).all(property_id, year, req.userId);

  const chargesByCategory = db.prepare(`
    SELECT category, SUM(amount) as total FROM charges
    WHERE property_id = ? AND year = ? AND user_id = ? AND recoverable = 1
    GROUP BY category ORDER BY total DESC
  `).all(property_id, year, req.userId);

  const leases = db.prepare(`
    SELECT l.*, t.first_name, t.last_name, t.email FROM leases l
    JOIN tenants t ON l.tenant_id = t.id
    WHERE l.property_id = ? AND l.user_id = ?
      AND (l.start_date <= ? AND (l.end_date IS NULL OR l.end_date >= ?))
  `).all(property_id, req.userId, `${year}-12-31`, `${year}-01-01`);

  const leaseBilans = leases.map(lease => {
    const provisions = db.prepare(`SELECT SUM(charges_paid) as total FROM payments WHERE lease_id = ? AND period_year = ?`).get(lease.id, year);
    return {
      lease_id: lease.id,
      tenant_name: `${lease.first_name} ${lease.last_name}`,
      tenant_email: lease.email,
      provisions_percues: provisions?.total || 0
    };
  });
  const totalProvisions = leaseBilans.reduce((s, l) => s + l.provisions_percues, 0);
  const bilans = leaseBilans.map(l => {
    const ratio = totalProvisions > 0 ? l.provisions_percues / totalProvisions : (leaseBilans.length > 0 ? 1 / leaseBilans.length : 0);
    const quote_part = totalChargesReelles * ratio;
    const trop_percu = l.provisions_percues - quote_part;
    return { ...l, ratio_percent: Math.round(ratio * 100 * 100) / 100, quote_part_charges: Math.round(quote_part * 100) / 100, trop_percu: Math.round(trop_percu * 100) / 100 };
  });

  const catLabels = { eau: 'Eau', gaz: 'Gaz', electricite: 'Électricité', entretien: 'Entretien / Réparations', ascenseur: 'Ascenseur', ordures: 'Ordures ménagères', assurance: 'Assurance', autre: 'Autre' };
  const fmtEur = v => `${Number(v).toFixed(2)} EUR`;
  const fmtDate = d => d ? new Date(d + 'T00:00:00').toLocaleDateString('fr-FR') : '—';
  const solde = totalProvisions - totalChargesReelles;

  try {
    // --- Step 1: Generate bilan PDF with pdfkit ---
    const bilanBuf = await new Promise((resolve, reject) => {
      const doc = new PDFDocument({ margin: 50, size: 'A4' });
      const chunks = [];
      doc.on('data', c => chunks.push(c));
      doc.on('end', () => resolve(Buffer.concat(chunks)));
      doc.on('error', reject);

      const W = doc.page.width - 100;
      const blue = '#2563eb'; const red = '#dc2626'; const green = '#15803d'; const orange = '#c2410c'; const gray = '#6b7280';

      // Header
      doc.fontSize(20).fillColor('#111827').text(`Bilan annuel de charges ${year}`, { align: 'left' });
      doc.fontSize(11).fillColor(gray).text(`${prop.name} — ${prop.address || ''}`, { align: 'left' });
      doc.moveDown(0.5);
      doc.fontSize(10).fillColor(gray).text(`Document généré le ${new Date().toLocaleDateString('fr-FR')}`, { align: 'right' });
      doc.moveDown(0.8);

      // Bailleur info
      doc.moveTo(50, doc.y).lineTo(50 + W, doc.y).strokeColor('#e5e7eb').lineWidth(1).stroke();
      doc.moveDown(0.5);
      doc.fontSize(9).fillColor(gray).text('BAILLEUR', { continued: false });
      doc.fontSize(10).fillColor('#111827').font('Helvetica-Bold').text(ownerFullName);
      doc.font('Helvetica');
      if (owner.address) doc.fontSize(10).fillColor('#374151').text(owner.address);
      doc.fontSize(10).fillColor('#374151').text(owner.email || '');
      if (owner.phone) doc.fontSize(10).fillColor('#374151').text(owner.phone);
      doc.moveDown(0.5);
      doc.moveTo(50, doc.y).lineTo(50 + W, doc.y).strokeColor('#e5e7eb').lineWidth(1).stroke();
      doc.moveDown(1);

      // Summary boxes
      doc.fontSize(11).fillColor('#111827').text('Synthèse', { underline: true });
      doc.moveDown(0.5);
      const bx = 50; const bw = (W - 20) / 3;
      [[`Charges réelles`, fmtEur(totalChargesReelles), `${totalChargesRow?.count || 0} facture(s)`, red],
       [`Provisions perçues`, fmtEur(totalProvisions), 'de tous les locataires', blue],
       [solde >= 0 ? 'Trop-perçu global' : 'Solde insuffisant', `${solde >= 0 ? '+' : '-'}${fmtEur(Math.abs(solde))}`, solde >= 0 ? 'à restituer' : 'à appeler', solde >= 0 ? green : orange]
      ].forEach(([label, val, note, color], i) => {
        const x = bx + i * (bw + 10); const y = doc.y;
        doc.rect(x, y, bw, 60).stroke('#e5e7eb');
        doc.fontSize(9).fillColor(gray).text(label, x + 8, y + 8, { width: bw - 16 });
        doc.fontSize(14).fillColor(color).text(val, x + 8, y + 22, { width: bw - 16 });
        doc.fontSize(8).fillColor(gray).text(note, x + 8, y + 42, { width: bw - 16 });
      });
      doc.moveDown(4.5);

      // Charges by category
      if (chargesByCategory.length > 0) {
        doc.fontSize(11).fillColor('#111827').text('Répartition par catégorie', { underline: true });
        doc.moveDown(0.5);
        const colWidths = [220, 100, 80];
        const headers = ['Catégorie', 'Montant', '%'];
        let tx = 50; let ty = doc.y;
        doc.fontSize(9).fillColor(gray);
        headers.forEach((h, i) => { doc.text(h, tx, ty, { width: colWidths[i] }); tx += colWidths[i]; });
        doc.moveDown(0.3);
        doc.moveTo(50, doc.y).lineTo(50 + W, doc.y).strokeColor('#e5e7eb').stroke();
        doc.moveDown(0.3);
        chargesByCategory.forEach(c => {
          const pct = totalChargesReelles > 0 ? (c.total / totalChargesReelles * 100).toFixed(1) : '0.0';
          tx = 50; ty = doc.y;
          doc.fontSize(9).fillColor('#111827');
          [catLabels[c.category] || c.category, fmtEur(c.total), `${pct}%`].forEach((v, i) => { doc.text(v, tx, ty, { width: colWidths[i] }); tx += colWidths[i]; });
          doc.moveDown(0.4);
        });
        tx = 50; ty = doc.y;
        doc.fontSize(9).fillColor('#111827').font('Helvetica-Bold');
        ['Total', fmtEur(totalChargesReelles), ''].forEach((v, i) => { doc.text(v, tx, ty, { width: colWidths[i] }); tx += colWidths[i]; });
        doc.font('Helvetica');
        doc.moveDown(1.5);
      }

      // Per tenant bilan
      if (bilans.length > 0) {
        doc.fontSize(11).fillColor('#111827').text('Décompte par locataire', { underline: true });
        doc.moveDown(0.5);
        bilans.forEach(b => {
          const isPos = b.trop_percu >= 0;
          const color = isPos ? green : orange;
          const bY = doc.y;
          doc.rect(50, bY, W, 90).stroke('#e5e7eb');
          doc.fontSize(11).fillColor('#111827').font('Helvetica-Bold').text(b.tenant_name, 62, bY + 10, { width: W - 24 });
          doc.font('Helvetica');
          if (b.tenant_email) doc.fontSize(9).fillColor(gray).text(b.tenant_email, 62, bY + 24);
          const badge = isPos ? 'Remboursement' : 'Appel de fonds';
          doc.fontSize(9).fillColor(color).text(badge, 62, bY + 38);
          const cols = [['Provisions perçues', fmtEur(b.provisions_percues)], [`Quote-part (${b.ratio_percent}%)`, fmtEur(b.quote_part_charges)], [isPos ? 'Trop-perçu' : 'Solde dû', fmtEur(Math.abs(b.trop_percu))]];
          const cw = (W - 24) / 3;
          cols.forEach(([lbl, val], i) => {
            const cx = 62 + i * (cw + 8);
            doc.fontSize(8).fillColor(gray).text(lbl, cx, bY + 54, { width: cw });
            doc.fontSize(10).fillColor(i === 2 ? color : '#111827').font('Helvetica-Bold').text(val, cx, bY + 66, { width: cw });
            doc.font('Helvetica');
          });
          doc.y = bY + 100;
          doc.moveDown(0.3);
        });
      }

      // Charges list
      if (chargesList.length > 0) {
        doc.addPage();
        doc.fontSize(13).fillColor('#111827').text(`Détail des charges ${year}`, { underline: true });
        doc.moveDown(0.5);
        const hdrs = ['Date', 'Catégorie', 'Libellé', 'Montant', 'Facture'];
        const cws2 = [70, 100, 180, 80, 100];
        let hx = 50; const hy = doc.y;
        doc.fontSize(9).fillColor(gray);
        hdrs.forEach((h, i) => { doc.text(h, hx, hy, { width: cws2[i] }); hx += cws2[i]; });
        doc.moveDown(0.3);
        doc.moveTo(50, doc.y).lineTo(50 + W, doc.y).strokeColor('#e5e7eb').stroke();
        doc.moveDown(0.3);
        chargesList.forEach(c => {
          if (doc.y > doc.page.height - 80) { doc.addPage(); }
          hx = 50; const row = doc.y;
          doc.fontSize(9).fillColor('#111827');
          [fmtDate(c.date), catLabels[c.category] || c.category, c.label, fmtEur(c.amount), c.invoice_name ? `📎 Voir annexe` : '—'].forEach((v, i) => {
            doc.text(v, hx, row, { width: cws2[i] }); hx += cws2[i];
          });
          doc.moveDown(0.5);
        });
      }

      doc.end();
    });

    // --- Step 2: Merge bilan + invoice files with pdf-lib ---
    const mergedPdf = await LibPDFDocument.create();

    // Copy bilan pages
    const bilanPdf = await LibPDFDocument.load(bilanBuf);
    const bilanPages = await mergedPdf.copyPages(bilanPdf, bilanPdf.getPageIndices());
    bilanPages.forEach(p => mergedPdf.addPage(p));

    // Append each invoice
    for (const charge of chargesList) {
      if (!charge.invoice_path) continue;
      const filePath = path.join(uploadDir, charge.invoice_path);
      if (!fs.existsSync(filePath)) continue;
      const ext = path.extname(charge.invoice_path).toLowerCase();
      const fileBytes = fs.readFileSync(filePath);

      if (ext === '.pdf') {
        try {
          const invPdf = await LibPDFDocument.load(fileBytes, { ignoreEncryption: true });
          const invPages = await mergedPdf.copyPages(invPdf, invPdf.getPageIndices());
          invPages.forEach(p => mergedPdf.addPage(p));
        } catch { /* skip unreadable PDFs */ }
      } else if (['.jpg', '.jpeg'].includes(ext)) {
        try {
          const img = await mergedPdf.embedJpg(fileBytes);
          const page = mergedPdf.addPage([img.width, img.height]);
          page.drawImage(img, { x: 0, y: 0, width: img.width, height: img.height });
        } catch { /* skip unreadable images */ }
      } else if (ext === '.png') {
        try {
          const img = await mergedPdf.embedPng(fileBytes);
          const page = mergedPdf.addPage([img.width, img.height]);
          page.drawImage(img, { x: 0, y: 0, width: img.width, height: img.height });
        } catch { /* skip unreadable images */ }
      }
      // webp not supported by pdf-lib natively — skipped
    }

    const pdfBytes = await mergedPdf.save();
    const filename = `bilan_charges_${year}_${prop.name.replace(/[^a-z0-9]/gi, '_')}.pdf`;
    res.setHeader('Content-Type', 'application/pdf');
    res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
    res.send(Buffer.from(pdfBytes));
  } catch (err) {
    console.error('PDF generation error:', err);
    res.status(500).json({ error: 'Erreur génération PDF' });
  }
});

module.exports = router;