add-missing-invoice-line-items.js

Go to the documentation of this file.

#!/usr/bin/env node
/**
 * Add line items to invoices that don't have any
 * Typically needed for migrated WHMCS invoices
 */
 
const pool = require('../services/db');
 
async function addMissingLineItems() {
  console.log('[Add Line Items] Starting...\n');
  
  const client = await pool.connect();
  let processed = 0;
  let errors = 0;
  
  try {
    // Find all invoices without line items
    const invoicesResult = await client.query(`
      SELECT 
        i.invoice_id, 
        i.customer_id,
        i.total,
        i.subtotal,
        i.tax_amount,
        i.tax_rate,
        i.currency,
        i.description
      FROM invoices i
      LEFT JOIN invoice_items ii ON i.invoice_id = ii.invoice_id
      WHERE ii.item_id IS NULL
      ORDER BY i.invoice_id
    `);
    
    console.log(`Found ${invoicesResult.rows.length} invoices without line items\n`);
    
    for (const invoice of invoicesResult.rows) {
      try {
        await client.query('BEGIN');
        
        // Calculate unit price ex GST
        const subtotal = parseFloat(invoice.subtotal || invoice.total || 0);
        const taxAmount = parseFloat(invoice.tax_amount || 0);
        const total = parseFloat(invoice.total || 0);
        const taxRate = parseFloat(invoice.tax_rate || 10);
        
        // Generate description
        let description = invoice.description || 'Migrated Invoice Services';
        if (description.includes('Migrated from WHMCS')) {
          // Extract WHMCS invoice number if available
          const match = description.match(/Invoice #(\d+)/);
          if (match) {
            description = `WHMCS Invoice #${match[1]} - Services`;
          } else {
            description = 'Migrated Services';
          }
        }
        
        // Insert single line item representing the full invoice
        await client.query(`
          INSERT INTO invoice_items (
            invoice_id,
            description,
            quantity,
            unit_price,
            total
          ) VALUES ($1, $2, $3, $4, $5)
        `, [
          invoice.invoice_id,
          description,
          1, // quantity
          subtotal, // unit price ex GST
          subtotal // total (same as unit_price since quantity=1)
        ]);
        
        await client.query('COMMIT');
        processed++;
        
        if (processed % 50 === 0) {
          console.log(`Progress: ${processed}/${invoicesResult.rows.length} invoices processed`);
        }
      } catch (err) {
        await client.query('ROLLBACK');
        console.error(`Error processing invoice ${invoice.invoice_id}:`, err.message);
        errors++;
      }
    }
    
    console.log(`\n[Add Line Items] Complete!`);
    console.log(`✅ Successfully processed: ${processed} invoices`);
    if (errors > 0) {
      console.log(`❌ Errors: ${errors} invoices`);
    }
    
  } catch (err) {
    console.error('[Add Line Items] Fatal error:', err);
  } finally {
    client.release();
    await pool.end();
  }
}
 
// Run the script
addMissingLineItems().catch(console.error);