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 pool = 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('/', async (req, res) => {
  const { property_id, year } = req.query;
  const params = [req.userId];
  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 = $1
  `;
  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((await pool.query(query, params)).rows);
});

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

  const prop = (await pool.query('SELECT id FROM properties WHERE id = $1 AND user_id = $2', [property_id, req.userId])).rows[0];
  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 rtype = ['recoverable', 'deductible', 'none'].includes(recoverable_type) ? recoverable_type : 'recoverable';

  const result = await pool.query(`
    INSERT INTO charges (user_id, property_id, category, label, amount, date, year, invoice_path, invoice_name, notes, recoverable, recoverable_type)
    VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) RETURNING id
  `, [req.userId, property_id, category, label, parseFloat(amount), date, parseInt(year), invoice_path, invoice_name, notes || null, rtype === 'recoverable' ? 1 : 0, rtype]);

  res.status(201).json({ id: result.rows[0].id });
});

// Update charge (with optional new invoice)
router.put('/:id', upload.single('invoice'), async (req, res) => {
  const charge = (await pool.query('SELECT * FROM charges WHERE id = $1 AND user_id = $2', [req.params.id, req.userId])).rows[0];
  if (!charge) return res.status(404).json({ error: 'Charge non trouvée' });

  const { category, label, amount, date, year, notes, recoverable_type } = 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;
  }

  const rtype = ['recoverable', 'deductible', 'none'].includes(recoverable_type) ? recoverable_type : 'recoverable';

  await pool.query(`
    UPDATE charges SET category=$1, label=$2, amount=$3, date=$4, year=$5, invoice_path=$6, invoice_name=$7, notes=$8, recoverable=$9, recoverable_type=$10
    WHERE id=$11
  `, [category, label, parseFloat(amount), date, parseInt(year), invoice_path, invoice_name, notes || null, rtype === 'recoverable' ? 1 : 0, rtype, req.params.id]);

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

// Delete charge
router.delete('/:id', async (req, res) => {
  const charge = (await pool.query('SELECT * FROM charges WHERE id = $1 AND user_id = $2', [req.params.id, req.userId])).rows[0];
  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);
  }

  await pool.query('DELETE FROM charges WHERE id = $1', [req.params.id]);
  res.json({ success: true });
});

// Download invoice
router.get('/:id/invoice', async (req, res) => {
  const charge = (await pool.query('SELECT * FROM charges WHERE id = $1 AND user_id = $2', [req.params.id, req.userId])).rows[0];
  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', async (req, res) => {
  const { property_id, year } = req.params;

  // Verify property ownership
  const prop = (await pool.query('SELECT * FROM properties WHERE id = $1 AND user_id = $2', [property_id, req.userId])).rows[0];
  if (!prop) return res.status(403).json({ error: 'Bien non autorisé' });

  // Total recoverable charges for this property/year (for tenant billing)
  const totalChargesRow = (await pool.query(`
    SELECT SUM(amount) as total, COUNT(*) as count
    FROM charges WHERE property_id = $1 AND year = $2 AND user_id = $3 AND recoverable_type = 'recoverable'
  `, [property_id, year, req.userId])).rows[0];

  // Total deductible (landlord fiscal charges, for info)
  const totalDeductibleRow = (await pool.query(`
    SELECT SUM(amount) as total
    FROM charges WHERE property_id = $1 AND year = $2 AND user_id = $3 AND recoverable_type = 'deductible'
  `, [property_id, year, req.userId])).rows[0];

  // Total non-recoverable / non-deductible (landlord's own charges, for info)
  const totalNonRecovRow = (await pool.query(`
    SELECT SUM(amount) as total
    FROM charges WHERE property_id = $1 AND year = $2 AND user_id = $3 AND recoverable_type = 'none'
  `, [property_id, year, req.userId])).rows[0];

  const totalChargesReelles = parseFloat(totalChargesRow?.total || 0);
  const totalDeductible = parseFloat(totalDeductibleRow?.total || 0);
  const totalNonRecoverable = parseFloat(totalNonRecovRow?.total || 0);

  // Charges by category (recoverable only)
  const chargesByCategory = (await pool.query(`
    SELECT category, SUM(amount) as total
    FROM charges WHERE property_id = $1 AND year = $2 AND user_id = $3 AND recoverable_type = 'recoverable'
    GROUP BY category ORDER BY total DESC
  `, [property_id, year, req.userId])).rows;

  // Non-recoverable by category (for info)
  const chargesNonRecovByCategory = (await pool.query(`
    SELECT category, SUM(amount) as total
    FROM charges WHERE property_id = $1 AND year = $2 AND user_id = $3 AND recoverable_type != 'recoverable'
    GROUP BY category ORDER BY total DESC
  `, [property_id, year, req.userId])).rows;

  // All leases on this property (active or that were active during the year)
  const leases = (await pool.query(`
    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 = $1 AND l.user_id = $2
      AND (l.start_date <= $3 AND (l.end_date IS NULL OR l.end_date >= $4))
  `, [property_id, req.userId, `${year}-12-31`, `${year}-01-01`])).rows;

  // For each lease, get provisions perçues for the year
  const leaseBilans = await Promise.all(leases.map(async lease => {
    const provisions = (await pool.query(`
      SELECT SUM(charges_paid) as total
      FROM payments
      WHERE lease_id = $1 AND period_year = $2
    `, [lease.id, year])).rows[0];

    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_deductible: Math.round(totalDeductible * 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 = (await pool.query('SELECT * FROM properties WHERE id = $1 AND user_id = $2', [property_id, req.userId])).rows[0];
  if (!prop) return res.status(403).json({ error: 'Bien non autorisé' });

  // Chercher le propriétaire lié au bail actif sur ce bien, sinon fallback profil gestionnaire
  const ownerRow = (await pool.query(`
    SELECT o.type as o_type, o.first_name, o.last_name, o.company_name, o.siret,
           o.address, o.zip_code, o.city, o.email, o.phone
    FROM leases l
    JOIN owners o ON l.owner_id = o.id
    WHERE l.property_id = $1 AND l.user_id = $2 AND o.id IS NOT NULL
    ORDER BY l.active DESC, l.start_date DESC
    LIMIT 1
  `, [property_id, req.userId])).rows[0];

  let ownerFullName, ownerAddress, ownerEmail, ownerPhone, ownerSiret;
  if (ownerRow) {
    ownerFullName = ownerRow.o_type === 'morale'
      ? ownerRow.company_name
      : `${ownerRow.first_name || ''} ${ownerRow.last_name || ''}`.trim();
    ownerSiret = ownerRow.siret;
    ownerAddress = [ownerRow.address, ownerRow.zip_code && ownerRow.city ? `${ownerRow.zip_code} ${ownerRow.city}` : (ownerRow.zip_code || ownerRow.city)].filter(Boolean).join(', ');
    ownerEmail = ownerRow.email;
    ownerPhone = ownerRow.phone;
  } else {
    const u = (await pool.query('SELECT name, first_name, last_name, email, address, phone FROM users WHERE id = $1', [req.userId])).rows[0];
    ownerFullName = (u.first_name && u.last_name) ? `${u.first_name} ${u.last_name}` : u.name;
    ownerSiret = null;
    ownerAddress = u.address;
    ownerEmail = u.email;
    ownerPhone = u.phone;
  }

  const totalChargesRow = (await pool.query(`
    SELECT SUM(amount) as total, COUNT(*) as count FROM charges
    WHERE property_id = $1 AND year = $2 AND user_id = $3 AND recoverable_type = 'recoverable'
  `, [property_id, year, req.userId])).rows[0];
  const totalChargesReelles = parseFloat(totalChargesRow?.total || 0);

  const chargesList = (await pool.query(`
    SELECT * FROM charges WHERE property_id = $1 AND year = $2 AND user_id = $3
    ORDER BY date ASC
  `, [property_id, year, req.userId])).rows;

  const chargesByCategory = (await pool.query(`
    SELECT category, SUM(amount) as total FROM charges
    WHERE property_id = $1 AND year = $2 AND user_id = $3 AND recoverable_type = 'recoverable'
    GROUP BY category ORDER BY total DESC
  `, [property_id, year, req.userId])).rows;

  const leases = (await pool.query(`
    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 = $1 AND l.user_id = $2
      AND (l.start_date <= $3 AND (l.end_date IS NULL OR l.end_date >= $4))
  `, [property_id, req.userId, `${year}-12-31`, `${year}-01-01`])).rows;

  const leaseBilans = await Promise.all(leases.map(async lease => {
    const provisions = (await pool.query(`SELECT SUM(charges_paid) as total FROM payments WHERE lease_id = $1 AND period_year = $2`, [lease.id, year])).rows[0];
    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 (ownerSiret) doc.fontSize(10).fillColor('#374151').text(`SIRET : ${ownerSiret}`);
      if (ownerAddress) doc.fontSize(10).fillColor('#374151').text(ownerAddress);
      if (ownerEmail) doc.fontSize(10).fillColor('#374151').text(ownerEmail);
      if (ownerPhone) doc.fontSize(10).fillColor('#374151').text(ownerPhone);
      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' });
  }
});

// Close annual exercise — create charge_regularization for each tenant
router.post('/bilan/:property_id/:year/close', async (req, res) => {
  const { property_id, year } = req.params;

  const prop = (await pool.query('SELECT * FROM properties WHERE id = $1 AND user_id = $2', [property_id, req.userId])).rows[0];
  if (!prop) return res.status(403).json({ error: 'Bien non autorisé' });

  // Check not already closed for this property/year
  const alreadyClosed = (await pool.query(
    'SELECT id FROM charge_regularizations WHERE user_id = $1 AND property_id = $2 AND year = $3',
    [req.userId, property_id, parseInt(year)]
  )).rows[0];
  if (alreadyClosed) return res.status(409).json({ error: `L'exercice ${year} a déjà été clôturé pour ce bien.` });

  // Recompute bilan (recoverable only)
  const totalRow = (await pool.query(
    `SELECT SUM(amount) as total FROM charges WHERE property_id = $1 AND year = $2 AND user_id = $3 AND recoverable_type = 'recoverable'`,
    [property_id, year, req.userId]
  )).rows[0];
  const totalCharges = totalRow?.total || 0;

  const leases = (await pool.query(`
    SELECT l.*, t.first_name, t.last_name FROM leases l
    JOIN tenants t ON l.tenant_id = t.id
    WHERE l.property_id = $1 AND l.user_id = $2
      AND (l.start_date <= $3 AND (l.end_date IS NULL OR l.end_date >= $4))
  `, [property_id, req.userId, `${year}-12-31`, `${year}-01-01`])).rows;

  if (leases.length === 0) return res.status(400).json({ error: 'Aucun locataire sur cette période.' });

  const leaseBilans = await Promise.all(leases.map(async lease => {
    const prov = (await pool.query('SELECT SUM(charges_paid) as total FROM payments WHERE lease_id = $1 AND period_year = $2', [lease.id, year])).rows[0];
    return { lease_id: lease.id, tenant: `${lease.first_name} ${lease.last_name}`, provisions: prov?.total || 0 };
  }));
  const totalProvisions = leaseBilans.reduce((s, l) => s + l.provisions, 0);

  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const results = [];
    for (const l of leaseBilans) {
      const ratio = totalProvisions > 0 ? l.provisions / totalProvisions : 1 / leaseBilans.length;
      const quote_part = totalCharges * ratio;
      // Positive = trop-perçu (credit tenant), Negative = complément dû (debit tenant)
      const trop_percu = Math.round((l.provisions - quote_part) * 100) / 100;
      const label = trop_percu >= 0
        ? `Régularisation charges ${year} : trop-perçu de ${trop_percu.toFixed(2)} € à déduire`
        : `Régularisation charges ${year} : complément de ${Math.abs(trop_percu).toFixed(2)} € à appeler`;
      await client.query(
        'INSERT INTO charge_regularizations (lease_id, user_id, property_id, year, amount, notes) VALUES ($1, $2, $3, $4, $5, $6)',
        [l.lease_id, req.userId, parseInt(property_id), parseInt(year), trop_percu, label]
      );
      results.push({ lease_id: l.lease_id, tenant: l.tenant, trop_percu });
    }
    await client.query('COMMIT');
    res.json({ success: true, year: parseInt(year), property: prop.name, regularizations: results });
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
});

module.exports = router;