const express = require('express');
const db = require('../db');
const { authMiddleware } = require('../auth');

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

router.get('/', (req, res) => {
  const { lease_id } = req.query;
  let query = `
    SELECT p.*, l.rent_amount, l.charges_amount,
      pr.name as property_name, pr.address as property_address, pr.city as property_city,
      t.first_name as tenant_first_name, t.last_name as tenant_last_name
    FROM payments p
    JOIN leases l ON p.lease_id = l.id
    JOIN properties pr ON l.property_id = pr.id
    JOIN tenants t ON l.tenant_id = t.id
    WHERE p.user_id = ?
  `;
  const params = [req.userId];
  if (lease_id) { query += ' AND p.lease_id = ?'; params.push(lease_id); }
  query += ' ORDER BY p.period_year DESC, p.period_month DESC';
  res.json(db.prepare(query).all(...params));
});

router.post('/', (req, res) => {
  const { lease_id, period_month, period_year, rent_paid, charges_paid, payment_date, payment_method, notes,
    is_prorata, prorata_days, prorata_total_days, charge_regularization } = req.body;
  if (!lease_id || !period_month || !period_year || rent_paid === undefined || !payment_date)
    return res.status(400).json({ error: 'Champs requis manquants' });

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

  const existing = db.prepare(
    'SELECT id FROM payments WHERE lease_id = ? AND period_month = ? AND period_year = ?'
  ).get(lease_id, period_month, period_year);
  if (existing) return res.status(409).json({ error: 'Un paiement existe déjà pour cette période' });

  const regularization = parseFloat(charge_regularization || 0);

  const result = db.prepare(`
    INSERT INTO payments (lease_id, user_id, period_month, period_year, rent_paid, charges_paid, payment_date, payment_method, notes,
      is_prorata, prorata_days, prorata_total_days, charge_regularization)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `).run(lease_id, req.userId, period_month, period_year, rent_paid, charges_paid || 0,
    payment_date, payment_method || 'virement', notes || null,
    is_prorata ? 1 : 0, prorata_days || null, prorata_total_days || null, regularization);

  // If a regularization was applied, mark it as used
  if (regularization !== 0) {
    db.prepare(`
      UPDATE charge_regularizations SET applied_payment_id = ?
      WHERE lease_id = ? AND applied_payment_id IS NULL
      ORDER BY created_at ASC LIMIT 1
    `).run(result.lastInsertRowid, lease_id);
  }

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

router.delete('/:id', (req, res) => {
  const payment = db.prepare('SELECT id FROM payments WHERE id = ? AND user_id = ?').get(req.params.id, req.userId);
  if (!payment) return res.status(404).json({ error: 'Paiement non trouvé' });
  db.prepare('DELETE FROM payments WHERE id = ?').run(req.params.id);
  res.json({ success: true });
});

// Pending charge regularization for a lease
router.get('/regularization/:lease_id', (req, res) => {
  const lease = db.prepare('SELECT id FROM leases WHERE id = ? AND user_id = ?').get(req.params.lease_id, req.userId);
  if (!lease) return res.status(404).json({ error: 'Bail non trouvé' });
  const rows = db.prepare(
    'SELECT * FROM charge_regularizations WHERE lease_id = ? AND applied_payment_id IS NULL ORDER BY year DESC'
  ).all(req.params.lease_id);
  const total = rows.reduce((s, r) => s + r.amount, 0);
  res.json({ pending: rows, total: Math.round(total * 100) / 100 });
});

// Dashboard stats
router.get('/stats', (req, res) => {
  const stats = db.prepare(`
    SELECT 
      COUNT(DISTINCT l.id) as active_leases,
      SUM(l.rent_amount + l.charges_amount) as monthly_expected,
      (SELECT SUM(rent_paid + charges_paid) FROM payments WHERE user_id = ? AND period_year = strftime('%Y', 'now') AND period_month = strftime('%m', 'now')) as current_month_collected
    FROM leases l WHERE l.user_id = ? AND l.active = 1
  `).get(req.userId, req.userId);
  res.json(stats);
});

module.exports = router;