verify-migration.js

Go to the documentation of this file.

#!/usr/bin/env node
const { Pool } = require('pg');
 
const pool = new Pool({
  host: 'rmm-psa-db-do-user-28531160-0.i.db.ondigitalocean.com',
  port: 25060,
  database: 'defaultdb',
  user: 'doadmin',
  password: 'AVNS_J8RJAmsEwsHFG52_-F2',
  ssl: { rejectUnauthorized: false }
});
 
/**
 *
 */
async function verifyContracts() {
  const tenantId = '00000000-0000-0000-0000-000000000001';
  
  console.log('╔══════════════════════════════════════════════════════════╗');
  console.log('║   WHMCS Migration Verification Report                   ║');
  console.log('╚══════════════════════════════════════════════════════════╝\n');
  
  // Overall stats
  const stats = await pool.query(`
    SELECT 
      (SELECT COUNT(*) FROM customers WHERE tenant_id = $1) as customers,
      (SELECT COUNT(*) FROM products WHERE tenant_id = $1) as products,
      (SELECT COUNT(*) FROM invoices WHERE tenant_id = $1) as invoices,
      (SELECT COUNT(*) FROM contracts WHERE tenant_id = $1) as contracts,
      (SELECT COUNT(*) FROM contract_line_items cli 
       JOIN contracts c ON cli.contract_id = c.contract_id 
       WHERE c.tenant_id = $1) as line_items
  `, [tenantId]);
  
  console.log('MIGRATION SUMMARY');
  console.log('=================');
  console.log(`✓ Customers:      ${stats.rows[0].customers}`);
  console.log(`✓ Products:       ${stats.rows[0].products}`);
  console.log(`✓ Invoices:       ${stats.rows[0].invoices}`);
  console.log(`✓ Contracts:      ${stats.rows[0].contracts}`);
  console.log(`✓ Line Items:     ${stats.rows[0].line_items}\n`);
  
  // Contract details
  const contracts = await pool.query(`
    SELECT 
      c.contract_id,
      c.title,
      cust.name as customer_name,
      c.status,
      c.billing_interval,
      c.next_billing_date,
      c.auto_invoice_enabled,
      cli.unit_price,
      cli.description as product_description
    FROM contracts c
    JOIN customers cust ON c.customer_id = cust.customer_id
    LEFT JOIN contract_line_items cli ON c.contract_id = cli.contract_id
    WHERE c.tenant_id = $1
    ORDER BY c.contract_id
  `, [tenantId]);
  
  console.log('CONTRACT DETAILS');
  console.log('================\n');
  
  let totalMonthlyRevenue = 0;
  
  for (const contract of contracts.rows) {
    const price = parseFloat(contract.unit_price || 0);
    
    // Convert to monthly equivalent
    let monthlyEquiv = price;
    if (contract.billing_interval === 'yearly') monthlyEquiv = price / 12;
    else if (contract.billing_interval === 'quarterly') monthlyEquiv = price / 3;
    else if (contract.billing_interval === 'semi_annually') monthlyEquiv = price / 6;
    
    totalMonthlyRevenue += monthlyEquiv;
    
    console.log(`Contract #${contract.contract_id}: ${contract.title}`);
    console.log(`  Customer:        ${contract.customer_name}`);
    console.log(`  Status:          ${contract.status}`);
    console.log(`  Billing:         $${price.toFixed(2)}/${contract.billing_interval}`);
    console.log(`  Next Billing:    ${contract.next_billing_date || 'Not set'}`);
    console.log(`  Auto Invoice:    ${contract.auto_invoice_enabled ? 'Yes' : 'No'}`);
    console.log(`  Product:         ${contract.product_description}`);
    console.log('');
  }
  
  console.log('REVENUE ANALYSIS');
  console.log('================');
  console.log(`Total Monthly Recurring Revenue (MRR): $${totalMonthlyRevenue.toFixed(2)}`);
  console.log(`Projected Annual Recurring Revenue:    $${(totalMonthlyRevenue * 12).toFixed(2)}\n`);
  
  // Status breakdown
  const statusBreakdown = await pool.query(`
    SELECT status, COUNT(*) as count
    FROM contracts
    WHERE tenant_id = $1
    GROUP BY status
    ORDER BY count DESC
  `, [tenantId]);
  
  console.log('CONTRACT STATUS BREAKDOWN');
  console.log('=========================');
  for (const row of statusBreakdown.rows) {
    console.log(`  ${row.status.padEnd(15)} ${row.count} contracts`);
  }
  console.log('');
  
  // Customers with most contracts
  const topCustomers = await pool.query(`
    SELECT 
      cust.name,
      COUNT(c.contract_id) as contract_count,
      SUM(cli.unit_price) as total_value
    FROM customers cust
    JOIN contracts c ON cust.customer_id = c.customer_id
    LEFT JOIN contract_line_items cli ON c.contract_id = cli.contract_id
    WHERE cust.tenant_id = $1
    GROUP BY cust.customer_id, cust.name
    ORDER BY contract_count DESC, total_value DESC
    LIMIT 10
  `, [tenantId]);
  
  console.log('TOP 10 CUSTOMERS BY CONTRACT COUNT');
  console.log('===================================');
  for (const customer of topCustomers.rows) {
    console.log(`  ${customer.name.padEnd(40)} ${customer.contract_count} contracts ($${parseFloat(customer.total_value || 0).toFixed(2)})`);
  }
  console.log('');
  
  console.log('✓ Migration verification complete!\n');
  
  await pool.end();
}
 
verifyContracts().catch(console.error);