CentralBackend/repositories/invoice/InvoiceRepository.js

const pool = require('../../database/database');
const Invoice = require('../../models/Invoice');

function genInvoiceNumber() {
  const now = new Date();
  const y = now.getFullYear();
  const m = String(now.getMonth() + 1).padStart(2, '0');
  const d = String(now.getDate()).padStart(2, '0');
  const rand = Math.floor(Math.random() * 1e6).toString().padStart(6, '0');
  return `INV-${y}${m}${d}-${rand}`;
}

class InvoiceRepository {
  async createInvoiceWithItems({
    source_type,
    source_id,
    user_id,
    buyer_name,
    buyer_email,
    buyer_street,
    buyer_postal_code,
    buyer_city,
    buyer_country,
    currency,
    items = [],
    status = 'draft',
    issued_at = null,
    due_at = null,
    context = null,
    vat_rate = null, // NEW: default VAT for invoice and lines
  }) {
    const conn = await pool.getConnection();
    try {
      await conn.beginTransaction();
      const invoice_number = genInvoiceNumber();

      // compute totals
      let total_net = 0;
      let total_tax = 0;
      let total_gross = 0;
      for (const it of items) {
        const qty = Number(it.quantity || 1);
        const unit = Number(it.unit_price || 0);
        const rate = it.tax_rate != null ? Number(it.tax_rate) : (vat_rate != null ? Number(vat_rate) : null); // CHANGED
        const line_net = qty * unit;
        const line_tax = rate != null ? +(line_net * (rate / 100)).toFixed(2) : 0;
        const line_gross = +(line_net + line_tax).toFixed(2);
        total_net += line_net;
        total_tax += line_tax;
        total_gross += line_gross;
      }
      total_net = +total_net.toFixed(2);
      total_tax = +total_tax.toFixed(2);
      total_gross = +total_gross.toFixed(2);

      const [res] = await conn.query(
        `INSERT INTO invoices
         (invoice_number, user_id, source_type, source_id, buyer_name, buyer_email, buyer_street, buyer_postal_code, buyer_city, buyer_country,
          currency, total_net, total_tax, total_gross, vat_rate, status, issued_at, due_at, pdf_storage_key, context, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NULL, ?, NOW(), NOW())`,
        [
          invoice_number,
          user_id || null,
          source_type,
          source_id,
          buyer_name || null,
          buyer_email || null,
          buyer_street || null,
          buyer_postal_code || null,
          buyer_city || null,
          buyer_country || null,
          currency,
          total_net,
          total_tax,
          total_gross,
          vat_rate != null ? Number(vat_rate) : null, // CHANGED
          status,
          issued_at || null,
          due_at || null,
          context ? JSON.stringify(context) : null,
        ],
      );

      const invoiceId = res.insertId;

      for (const it of items) {
        const qty = Number(it.quantity || 1);
        const unit = Number(it.unit_price || 0);
        const rate = it.tax_rate != null ? Number(it.tax_rate) : (vat_rate != null ? Number(vat_rate) : null); // CHANGED
        const line_net = +(qty * unit).toFixed(2);
        const line_tax = rate != null ? +(line_net * (rate / 100)).toFixed(2) : 0;
        const line_gross = +(line_net + line_tax).toFixed(2);

        await conn.query(
          `INSERT INTO invoice_items
           (invoice_id, product_id, sku, description, quantity, unit_price, tax_rate, line_net, line_tax, line_gross, created_at)
           VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())`,
          [
            invoiceId,
            it.product_id || null,
            it.sku || null,
            it.description || 'Subscription',
            qty,
            unit,
            rate, // CHANGED
            line_net,
            line_tax,
            line_gross,
          ],
        );
      }

      await conn.commit();
      return this.getById(invoiceId);
    } catch (e) {
      await conn.rollback();
      throw e;
    } finally {
      conn.release();
    }
  }

  async markPaid(invoiceId, { payment_method, transaction_id, amount, paid_at, details }) {
    const conn = await pool.getConnection();
    try {
      await conn.beginTransaction();
      await conn.query(`UPDATE invoices SET status='paid', updated_at=NOW() WHERE id = ?`, [invoiceId]);
      await conn.query(
        `INSERT INTO invoice_payments
         (invoice_id, payment_method, transaction_id, amount, paid_at, status, details, created_at)
         VALUES (?, ?, ?, ?, ?, 'succeeded', ?, NOW())`,
        [invoiceId, payment_method || 'manual', transaction_id || null, amount || null, paid_at || new Date(), details ? JSON.stringify(details) : null],
      );
      await conn.commit();
      return this.getById(invoiceId);
    } catch (e) {
      await conn.rollback();
      throw e;
    } finally {
      conn.release();
    }
  }

  async getById(id) {
    const [rows] = await pool.query(`SELECT * FROM invoices WHERE id = ?`, [id]);
    return rows[0] ? new Invoice(rows[0]) : null;
  }

  async listByUser(userId, { status, limit = 50, offset = 0 } = {}) {
    const params = [userId];
    let sql = `SELECT * FROM invoices WHERE user_id = ?`;
    if (status) {
      sql += ` AND status = ?`;
      params.push(status);
    }
    sql += ` ORDER BY issued_at DESC, created_at DESC LIMIT ? OFFSET ?`;
    params.push(Number(limit), Number(offset));
    const [rows] = await pool.query(sql, params);
    return rows.map((r) => new Invoice(r));
  }

  async listAll({ status, limit = 200, offset = 0 } = {}) {
    const params = [];
    let sql = `SELECT * FROM invoices`;
    if (status) {
      sql += ` WHERE status = ?`;
      params.push(status);
    }
    sql += ` ORDER BY issued_at DESC, created_at DESC LIMIT ? OFFSET ?`;
    params.push(Number(limit), Number(offset));
    const [rows] = await pool.query(sql, params);
    return rows.map((r) => new Invoice(r));
  }

  async findByAbonement(abonementId) {
    const [rows] = await pool.query(
      `SELECT * FROM invoices WHERE source_type='subscription' AND source_id = ? ORDER BY issued_at DESC, id DESC`,
      [abonementId],
    );
    return rows.map((r) => new Invoice(r));
  }
}

module.exports = InvoiceRepository;