contractBillingWorker.js

Go to the documentation of this file.

// Contract Billing Worker: Automatically generates invoices for contracts
const { Queue, Worker } = require('bullmq');
const Redis = require('ioredis');
const pool = require('./services/db');
 
const redisConfig = require('./config/redis');
const connection = new Redis({
  ...redisConfig,
  maxRetriesPerRequest: null // BullMQ requires this
});
 
// Create queue for billing jobs
const billingQueue = new Queue('contract-billing', { connection });
 
// Process billing jobs
const billingWorker = new Worker('contract-billing', async job => {
  console.log(`[ContractBilling] Processing job ${job.id} for contract ${job.data.contractId}`);
  
  const { contractId } = job.data;
  const client = await pool.connect();
  
  try {
    await client.query('BEGIN');
    
    // Get contract details with line items
    const contractRes = await client.query(`
      SELECT 
        c.*,
        cu.name as customer_name,
        cu.email as customer_email,
        t.name as tenant_name
      FROM contracts c
      LEFT JOIN customers cu ON c.customer_id = cu.customer_id
      LEFT JOIN tenants t ON c.tenant_id = t.tenant_id
      WHERE c.contract_id = $1
        AND c.auto_invoice_enabled = true
        AND c.status = 'active'
    `, [contractId]);
    
    if (contractRes.rows.length === 0) {
      console.log(`[ContractBilling] Contract ${contractId} not found or not eligible for auto-billing`);
      return { success: false, reason: 'Contract not eligible' };
    }
    
    const contract = contractRes.rows[0];
    
    // Get contract line items
    const lineItemsRes = await client.query(`
      SELECT 
        cli.*,
        p.name as product_name,
        p.description as product_description
      FROM contract_line_items cli
      LEFT JOIN products p ON cli.product_id = p.product_id
      WHERE cli.contract_id = $1
      ORDER BY cli.line_item_id
    `, [contractId]);
    
    if (lineItemsRes.rows.length === 0) {
      console.log(`[ContractBilling] Contract ${contractId} has no line items, skipping`);
      return { success: false, reason: 'No line items' };
    }
    
    // Calculate invoice total and subtotal
    const subtotal = lineItemsRes.rows.reduce((sum, item) => {
      return sum + (parseFloat(item.quantity) * parseFloat(item.unit_price));
    }, 0);
    
    const taxRate = 0.10; // 10% GST (can be made configurable per contract)
    const taxAmount = subtotal * taxRate;
    const totalAmount = subtotal + taxAmount;
    
    // Generate payment token for public payment links
    const crypto = require('crypto');
    const paymentToken = crypto.randomBytes(32).toString('hex');
    
    // Create invoice using correct column names matching invoices table schema
    const invoiceRes = await client.query(`
      INSERT INTO invoices (
        customer_id,
        tenant_id,
        amount,
        currency,
        status,
        issued_date,
        due_date,
        description,
        subtotal,
        tax_rate,
        tax_amount,
        total,
        payment_token
      ) VALUES (
        $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13
      ) RETURNING invoice_id
    `, [
      contract.customer_id,
      contract.tenant_id,
      totalAmount,
      contract.currency || 'AUD',
      'draft',
      new Date(), // issued_date
      new Date(Date.now() + 30 * 24 * 60 * 60 * 1000), // due_date (+30 days)
      `Auto-generated from contract: ${contract.title}`,
      subtotal,
      taxRate,
      taxAmount,
      totalAmount,
      paymentToken
    ]);
    
    const invoiceId = invoiceRes.rows[0].invoice_id;
    
    // Copy line items to invoice
    for (const lineItem of lineItemsRes.rows) {
      await client.query(`
        INSERT INTO invoice_items (
          invoice_id,
          product_id,
          description,
          quantity,
          unit_price,
          tax_rate,
          line_total
        ) VALUES ($1, $2, $3, $4, $5, $6, $7)
      `, [
        invoiceId,
        lineItem.product_id,
        lineItem.description || lineItem.product_name,
        lineItem.quantity,
        lineItem.unit_price,
        lineItem.tax_rate || 0,
        parseFloat(lineItem.quantity) * parseFloat(lineItem.unit_price)
      ]);
    }
    
    // Calculate next billing date
    let nextBillingDate = null;
    const currentBillingDate = contract.next_billing_date || contract.start_date;
    
    if (contract.billing_interval === 'monthly') {
      const date = new Date(currentBillingDate);
      date.setMonth(date.getMonth() + 1);
      if (contract.billing_day) {
        const daysInNextMonth = new Date(date.getFullYear(), date.getMonth() + 1, 0).getDate();
        date.setDate(Math.min(contract.billing_day, daysInNextMonth));
      }
      nextBillingDate = date.toISOString().split('T')[0];
    } else if (contract.billing_interval === 'yearly') {
      const date = new Date(currentBillingDate);
      date.setFullYear(date.getFullYear() + 1);
      nextBillingDate = date.toISOString().split('T')[0];
    } else if (contract.billing_interval === 'custom' && contract.custom_billing_dates) {
      const dates = JSON.parse(contract.custom_billing_dates);
      const today = new Date().toISOString().split('T')[0];
      const futureDates = dates.filter(d => d > today).sort();
      if (futureDates.length > 0) {
        nextBillingDate = futureDates[0];
      }
    }
    
    // Update contract with next billing date and last billing date
    await client.query(`
      UPDATE contracts 
      SET 
        next_billing_date = $1,
        last_billing_date = CURRENT_DATE,
        updated_at = CURRENT_TIMESTAMP
      WHERE contract_id = $2
    `, [nextBillingDate, contractId]);
    
    // Record billing history
    await client.query(`
      INSERT INTO contract_billing_history (
        contract_id,
        invoice_id,
        billing_date,
        amount,
        status
      ) VALUES ($1, $2, CURRENT_DATE, $3, 'generated')
    `, [contractId, invoiceId, totalAmount]);
    
    await client.query('COMMIT');
    
    console.log(`[ContractBilling] ✅ Generated invoice #${invoiceId} for contract ${contractId} (${contract.title})`);
    console.log(`[ContractBilling] Amount: ${contract.currency || 'AUD'} ${totalAmount.toFixed(2)}`);
    console.log(`[ContractBilling] Next billing date: ${nextBillingDate || 'N/A'}`);
    
    return {
      success: true,
      invoiceId,
      amount: totalAmount,
      nextBillingDate
    };
    
  } catch (error) {
    await client.query('ROLLBACK');
    console.error(`[ContractBilling] Error processing contract ${contractId}:`, error);
    throw error;
  } finally {
    client.release();
  }
}, { connection });
 
// Schedule daily check at 2am
/**
 *
 */
async function scheduleDailyBillingCheck() {
  // Remove existing repeatable jobs
  const repeatableJobs = await billingQueue.getRepeatableJobs();
  for (const job of repeatableJobs) {
    await billingQueue.removeRepeatableByKey(job.key);
  }
  
  // Schedule daily at 2am
  await billingQueue.add(
    'daily-check',
    {},
    {
      repeat: {
        pattern: '0 2 * * *', // 2am daily
        tz: 'Australia/Sydney'
      }
    }
  );
  
  console.log('[ContractBilling] Scheduled daily billing check at 2am Sydney time');
}
 
// Process all contracts due for billing
/**
 * Checks for contracts due for billing, respecting per-tenant invoice advance days setting.
 * Default advance period is 14 days (invoices generated 14 days before renewal).
 */
async function processAllDueContracts() {
  console.log('[ContractBilling] Checking for contracts due for billing...');
  
  const client = await pool.connect();
  try {
    // First, ensure tenant_settings table exists
    await client.query(`
      CREATE TABLE IF NOT EXISTS tenant_settings (
        tenant_id INTEGER NOT NULL,
        setting_key TEXT NOT NULL,
        setting_value TEXT,
        description TEXT,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (tenant_id, setting_key)
      )
    `);
 
    // Get all tenant-specific invoice_advance_days settings
    const tenantSettingsRes = await client.query(`
      SELECT tenant_id, setting_value 
      FROM tenant_settings 
      WHERE setting_key = 'invoice_advance_days'
    `);
    
    const tenantAdvanceDays = {};
    tenantSettingsRes.rows.forEach(row => {
      const days = parseInt(row.setting_value, 10);
      if (!isNaN(days) && days >= 0) {
        tenantAdvanceDays[row.tenant_id] = days;
      }
    });
    
    const defaultAdvanceDays = 14;
    console.log(`[ContractBilling] Using default advance days: ${defaultAdvanceDays}`);
    if (Object.keys(tenantAdvanceDays).length > 0) {
      console.log(`[ContractBilling] Tenant overrides:`, tenantAdvanceDays);
    }
 
    // Find contracts where next_billing_date is within the advance period
    // We'll check if next_billing_date <= CURRENT_DATE + advance_days
    const result = await client.query(`
      SELECT 
        c.contract_id, 
        c.title, 
        c.next_billing_date, 
        c.customer_id,
        c.tenant_id
      FROM contracts c
      WHERE c.auto_invoice_enabled = true
        AND c.status = 'active'
        AND (c.end_date IS NULL OR c.end_date >= CURRENT_DATE)
      ORDER BY c.next_billing_date
    `);
    
    let queuedCount = 0;
    
    for (const contract of result.rows) {
      // Get advance days for this tenant (default 14 if not set)
      const advanceDays = tenantAdvanceDays[contract.tenant_id] ?? defaultAdvanceDays;
      
      // Calculate the threshold date (today + advance days)
      const today = new Date();
      const thresholdDate = new Date(today);
      thresholdDate.setDate(thresholdDate.getDate() + advanceDays);
      
      const nextBillingDate = new Date(contract.next_billing_date);
      
      // Queue if the billing date is on or before the threshold
      if (nextBillingDate <= thresholdDate) {
        console.log(`[ContractBilling] Queueing contract ${contract.contract_id}: ${contract.title} (due: ${contract.next_billing_date}, tenant advance: ${advanceDays} days)`);
        await billingQueue.add(
          'generate-invoice',
          { contractId: contract.contract_id },
          { priority: 1 }
        );
        queuedCount++;
      }
    }
    
    console.log(`[ContractBilling] Queued ${queuedCount} out of ${result.rows.length} active contracts`);
    return queuedCount;
  } finally {
    client.release();
  }
}
 
// Worker event handlers
billingWorker.on('completed', (job, result) => {
  if (result.success) {
    console.log(`[ContractBilling] Job ${job.id} completed: Invoice #${result.invoiceId} generated`);
  } else {
    console.log(`[ContractBilling] Job ${job.id} completed with skip: ${result.reason}`);
  }
});
 
billingWorker.on('failed', (job, err) => {
  console.error(`[ContractBilling] Job ${job?.id} failed:`, err.message);
});
 
// Create a separate worker for daily check jobs
const dailyCheckWorker = new Worker('contract-billing', async job => {
  if (job.name === 'daily-check') {
    console.log('[ContractBilling] Running daily billing check...');
    const count = await processAllDueContracts();
    return { checked: count };
  }
}, { connection });
 
dailyCheckWorker.on('completed', (job, result) => {
  if (job.name === 'daily-check') {
    console.log(`[ContractBilling] Daily check completed. Processed ${result.checked} contracts`);
  }
});
 
dailyCheckWorker.on('failed', (job, err) => {
  if (job.name === 'daily-check') {
    console.error(`[ContractBilling] Daily check failed:`, err.message);
  }
});
 
// Initialize on startup
scheduleDailyBillingCheck().catch(err => {
  console.error('[ContractBilling] Failed to schedule daily check:', err);
});
 
// Also run immediately on startup
processAllDueContracts().catch(err => {
  console.error('[ContractBilling] Failed to process contracts on startup:', err);
});
 
console.log('[ContractBilling] Contract Billing Worker started');
 
module.exports = { billingQueue, processAllDueContracts };