migrate-whmcs-to-rmm.js

Go to the documentation of this file.

#!/usr/bin/env node
/**
 * WHMCS to RMM Backend Migration Script
 * 
 * Migrates all data from WHMCS to RMM PostgreSQL:
 * - Customers → customers table
 * - Products → products table
 * - Invoices → invoices table
 * - Client Services → contracts + contract_line_items tables
 */
 
const https = require('https');
const { Pool } = require('pg');
 
// ===========================
// CONFIGURATION
// ===========================
 
// WHMCS API Configuration
const WHMCS_CONFIG = {
  hostname: 'clientarea.precisewebhosting.com.au',
  identifier: '6o30xS9dkk1iQE6KjaPh1HwTFhTzgW5e',
  secret: 'oHw4epOsuhTJuJcdTyFZpvHrgT9bCZp3'
};
 
// RMM PostgreSQL Configuration
const PG_CONFIG = {
  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
  }
};
 
// Default tenant (Independent Business Group)
const DEFAULT_TENANT_NAME = 'Independent Business Group';
 
// ===========================
// WHMCS API HELPER
// ===========================
 
/**
 *
 * @param action
 * @param params
 */
function whmcsAPI(action, params = {}) {
  return new Promise((resolve, reject) => {
    const postData = new URLSearchParams({
      action,
      identifier: WHMCS_CONFIG.identifier,
      secret: WHMCS_CONFIG.secret,
      responsetype: 'json',
      ...params
    }).toString();
 
    const options = {
      hostname: WHMCS_CONFIG.hostname,
      path: '/includes/api.php',
      method: 'POST',
      headers: {
        'Content-Type': 'application/x-www-form-urlencoded',
        'Content-Length': postData.length
      },
      rejectUnauthorized: false
    };
 
    const req = https.request(options, (res) => {
      let data = '';
      res.on('data', (chunk) => data += chunk);
      res.on('end', () => {
        try {
          const json = JSON.parse(data);
          if (json.result === 'error') {
            reject(new Error(json.message || 'WHMCS API Error'));
          } else {
            resolve(json);
          }
        } catch (e) {
          reject(new Error(`Failed to parse WHMCS response: ${e.message}`));
        }
      });
    });
 
    req.on('error', (e) => reject(e));
    req.write(postData);
    req.end();
  });
}
 
// ===========================
// DATABASE HELPERS
// ===========================
 
/**
 *
 * @param pool
 * @param tenantName
 */
async function getTenantId(pool, tenantName) {
  const result = await pool.query(
    'SELECT tenant_id FROM tenants WHERE name = $1 OR subdomain = $2 LIMIT 1',
    [tenantName, tenantName.toLowerCase().replace(/\s+/g, '-')]
  );
  
  if (result.rows.length > 0) {
    return result.rows[0].tenant_id;
  }
  
  // Create tenant if doesn't exist
  console.log(`Creating tenant: ${tenantName}...`);
  const insertResult = await pool.query(`
    INSERT INTO tenants (name, subdomain, status, is_msp)
    VALUES ($1, $2, 'active', false)
    RETURNING tenant_id
  `, [tenantName, tenantName.toLowerCase().replace(/\s+/g, '-')]);
  
  return insertResult.rows[0].tenant_id;
}
 
// ===========================
// MIGRATION FUNCTIONS
// ===========================
 
/**
 *
 * @param pool
 * @param tenantId
 */
async function migrateCustomers(pool, tenantId) {
  console.log('\n=== MIGRATING CUSTOMERS ===');
  
  let imported = 0;
  let skipped = 0;
  let page = 0;
  const limit = 100;
  
  while (true) {
    const response = await whmcsAPI('GetClients', {
      limitstart: page * limit,
      limitnum: limit
    });
    
    const clients = response.clients?.client || [];
    if (clients.length === 0) break;
    
    console.log(`Processing page ${page + 1} (${clients.length} clients)...`);
    
    for (const client of clients) {
      try {
        // Check if customer already exists
        const existing = await pool.query(
          'SELECT customer_id FROM customers WHERE email = $1 AND tenant_id = $2',
          [client.email, tenantId]
        );
        
        if (existing.rows.length > 0) {
          skipped++;
          continue;
        }
        
        // Map WHMCS fields to RMM schema
        const customerName = client.companyname || 
          `${client.firstname} ${client.lastname}`.trim();
        const contactName = `${client.firstname} ${client.lastname}`.trim();
        const address = `${client.address1}\n${client.address2 || ''}`.trim();
        const status = client.status === 'Active' ? 'active' : 'inactive';
        
        // Insert customer
        const result = await pool.query(`
          INSERT INTO customers (
            tenant_id, name, contact_name, email, phone,
            address, city, state, postal_code, country, status
          ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
          RETURNING customer_id
        `, [
          tenantId, customerName, contactName, client.email, client.phonenumber,
          address, client.city, client.state, client.postcode, client.country, status
        ]);
        
        // Store mapping for later use
        client.rmm_customer_id = result.rows[0].customer_id;
        
        imported++;
        console.log(`✓ Imported: ${customerName} (${client.email})`);
        
      } catch (error) {
        console.error(`✗ Failed to import ${client.email}:`, error.message);
      }
    }
    
    page++;
    // Break if we got less than limit (last page)
    if (clients.length < limit) break;
  }
  
  console.log(`\n✓ Customers migration complete: ${imported} imported, ${skipped} skipped`);
}
 
/**
 *
 * @param pool
 * @param tenantId
 */
async function migrateProducts(pool, tenantId) {
  console.log('\n=== MIGRATING PRODUCTS ===');
  
  const response = await whmcsAPI('GetProducts');
  const products = response.products?.product || [];
  
  let imported = 0;
  let skipped = 0;
  
  for (const product of products) {
    try {
      // Check if product already exists
      const existing = await pool.query(
        'SELECT product_id FROM products WHERE name = $1 AND tenant_id = $2',
        [product.name, tenantId]
      );
      
      if (existing.rows.length > 0) {
        product.rmm_product_id = existing.rows[0].product_id;
        skipped++;
        continue;
      }
      
      // Determine product type
      const isService = product.type === 'hostingaccount' || 
                       product.type === 'reselleraccount' ||
                       product.type === 'server';
      
      // Insert product
      const result = await pool.query(`
        INSERT INTO products (
          tenant_id, name, description, unit_price, created_at
        ) VALUES ($1, $2, $3, $4, NOW())
        RETURNING product_id
      `, [
        tenantId,
        product.name,
        product.description || '',
        parseFloat(product.pricing?.USD?.monthly || 0)
      ]);
      
      product.rmm_product_id = result.rows[0].product_id;
      
      imported++;
      console.log(`✓ Imported: ${product.name}`);
      
    } catch (error) {
      console.error(`✗ Failed to import ${product.name}:`, error.message);
    }
  }
  
  console.log(`\n✓ Products migration complete: ${imported} imported, ${skipped} skipped`);
  return products; // Return for contract mapping
}
 
/**
 *
 * @param pool
 * @param tenantId
 */
async function migrateInvoices(pool, tenantId) {
  console.log('\n=== MIGRATING INVOICES ===');
  
  let imported = 0;
  let skipped = 0;
  let page = 0;
  const limit = 100;
  
  while (true) {
    const response = await whmcsAPI('GetInvoices', {
      limitstart: page * limit,
      limitnum: limit
    });
    
    const invoices = response.invoices?.invoice || [];
    if (invoices.length === 0) break;
    
    console.log(`Processing page ${page + 1} (${invoices.length} invoices)...`);
    
    for (const invoice of invoices) {
      try {
        // Get customer by email
        const customerResult = await pool.query(
          'SELECT customer_id FROM customers WHERE tenant_id = $1 LIMIT 1 OFFSET $2',
          [tenantId, parseInt(invoice.userid) - 1]
        );
        
        if (customerResult.rows.length === 0) {
          console.log(`⏭️  Skipping invoice ${invoice.id} - customer not found`);
          skipped++;
          continue;
        }
        
        const customerId = customerResult.rows[0].customer_id;
        
        // Check if invoice already exists
        const existing = await pool.query(
          'SELECT invoice_id FROM invoices WHERE customer_id = $1 AND date = $2 AND total = $3',
          [customerId, invoice.date, parseFloat(invoice.total)]
        );
        
        if (existing.rows.length > 0) {
          skipped++;
          continue;
        }
        
        // Map invoice status
        let status = 'draft';
        if (invoice.status === 'Paid') status = 'paid';
        else if (invoice.status === 'Unpaid') status = 'sent';
        else if (invoice.status === 'Cancelled') status = 'void';
        
        // Insert invoice
        await pool.query(`
          INSERT INTO invoices (
            tenant_id, customer_id, invoice_number, date, due_date,
            subtotal, tax, total, status, notes
          ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
        `, [
          tenantId,
          customerId,
          invoice.invoicenum || `INV-${invoice.id}`,
          invoice.date,
          invoice.duedate,
          parseFloat(invoice.subtotal || 0),
          parseFloat(invoice.tax || 0),
          parseFloat(invoice.total || 0),
          status,
          `Migrated from WHMCS Invoice #${invoice.id}`
        ]);
        
        imported++;
        
      } catch (error) {
        console.error(`✗ Failed to import invoice ${invoice.id}:`, error.message);
      }
    }
    
    page++;
    if (invoices.length < limit) break;
  }
  
  console.log(`\n✓ Invoices migration complete: ${imported} imported, ${skipped} skipped`);
}
 
/**
 *
 * @param pool
 * @param tenantId
 * @param products
 */
async function migrateServicesAndContracts(pool, tenantId, products) {
  console.log('\n=== MIGRATING CLIENT SERVICES & CREATING CONTRACTS ===');
  
  let imported = 0;
  let skipped = 0;
  let page = 0;
  const limit = 100;
  
  // Build product lookup map
  const productMap = {};
  products.forEach(p => {
    if (p.id && p.rmm_product_id) {
      productMap[p.id] = p.rmm_product_id;
    }
  });
  
  while (true) {
    const response = await whmcsAPI('GetClientsProducts', {
      limitstart: page * limit,
      limitnum: limit
    });
    
    const services = response.products?.product || [];
    if (services.length === 0) break;
    
    console.log(`Processing page ${page + 1} (${services.length} services)...`);
    
    for (const service of services) {
      try {
        // Get customer by userid
        const customerResult = await pool.query(
          'SELECT customer_id, name FROM customers WHERE tenant_id = $1 LIMIT 1 OFFSET $2',
          [tenantId, parseInt(service.clientid) - 1]
        );
        
        if (customerResult.rows.length === 0) {
          console.log(`⏭️  Skipping service ${service.id} - customer not found`);
          skipped++;
          continue;
        }
        
        const customer = customerResult.rows[0];
        
        // Skip if service is terminated
        if (service.status === 'Terminated' || service.status === 'Cancelled') {
          skipped++;
          continue;
        }
        
        // Check if contract already exists
        const existing = await pool.query(
          'SELECT contract_id FROM contracts WHERE customer_id = $1 AND title ILIKE $2',
          [customer.customer_id, `%${service.name}%`]
        );
        
        if (existing.rows.length > 0) {
          skipped++;
          continue;
        }
        
        // Map billing cycle to interval
        let billingInterval = 'monthly';
        if (service.billingcycle === 'Annually') billingInterval = 'yearly';
        else if (service.billingcycle === 'Quarterly') billingInterval = 'quarterly';
        else if (service.billingcycle === 'Semi-Annually') billingInterval = 'semi_annually';
        
        // Calculate next billing date
        const nextBillingDate = service.nextduedate || 
          new Date(Date.now() + 30 * 24 * 60 * 60 * 1000).toISOString().split('T')[0];
        
        // Determine contract status
        let contractStatus = 'active';
        if (service.status === 'Pending') contractStatus = 'draft';
        else if (service.status === 'Suspended') contractStatus = 'suspended';
        
        // Create contract
        const contractResult = await pool.query(`
          INSERT INTO contracts (
            tenant_id, customer_id, title, description, status,
            start_date, billing_interval, next_billing_date,
            auto_invoice_enabled, labor_rate, notes
          ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
          RETURNING contract_id
        `, [
          tenantId,
          customer.customer_id,
          service.name || service.product || 'Service Contract',
          service.product || '',
          contractStatus,
          service.regdate || new Date().toISOString().split('T')[0],
          billingInterval,
          nextBillingDate,
          true, // auto_invoice_enabled
          0, // labor_rate (can be updated later)
          `Migrated from WHMCS Service #${service.id}\nDomain: ${service.domain || 'N/A'}`
        ]);
        
        const contractId = contractResult.rows[0].contract_id;
        
        // Add contract line item for the service
        const productId = productMap[service.pid] || null;
        const amount = parseFloat(service.amount || 0);
        
        await pool.query(`
          INSERT INTO contract_line_items (
            contract_id, product_id, description, quantity, unit_price, recurring
          ) VALUES ($1, $2, $3, $4, $5, $6)
        `, [
          contractId,
          productId,
          service.product || service.name || 'Service',
          1,
          amount,
          true
        ]);
        
        imported++;
        console.log(`✓ Created contract: ${service.name} for ${customer.name} ($${amount}/${billingInterval})`);
        
      } catch (error) {
        console.error(`✗ Failed to create contract for service ${service.id}:`, error.message);
      }
    }
    
    page++;
    if (services.length < limit) break;
  }
  
  console.log(`\n✓ Contracts migration complete: ${imported} created, ${skipped} skipped`);
}
 
// ===========================
// MAIN MIGRATION
// ===========================
 
/**
 *
 */
async function main() {
  console.log('╔══════════════════════════════════════════════════════════╗');
  console.log('║   WHMCS → RMM Backend Migration Script                  ║');
  console.log('╚══════════════════════════════════════════════════════════╝\n');
  
  const pool = new Pool(PG_CONFIG);
  
  try {
    // Test database connection
    console.log('Testing database connection...');
    await pool.query('SELECT NOW()');
    console.log('✓ Database connected\n');
    
    // Get or create tenant
    console.log(`Getting tenant ID for: ${DEFAULT_TENANT_NAME}...`);
    const tenantId = await getTenantId(pool, DEFAULT_TENANT_NAME);
    console.log(`✓ Tenant ID: ${tenantId}\n`);
    
    // Test WHMCS API
    console.log('Testing WHMCS API connection...');
    await whmcsAPI('GetClients', { limitnum: 1 });
    console.log('✓ WHMCS API connected\n');
    
    // Run migrations in sequence
    await migrateCustomers(pool, tenantId);
    const products = await migrateProducts(pool, tenantId);
    await migrateInvoices(pool, tenantId);
    await migrateServicesAndContracts(pool, tenantId, products);
    
    console.log('\n╔══════════════════════════════════════════════════════════╗');
    console.log('║   MIGRATION COMPLETE ✓                                   ║');
    console.log('╚══════════════════════════════════════════════════════════╝\n');
    
    // Summary query
    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
    `, [tenantId]);
    
    console.log('Final Statistics:');
    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('');
    
  } catch (error) {
    console.error('\n❌ MIGRATION FAILED:', error.message);
    console.error(error.stack);
    process.exit(1);
  } finally {
    await pool.end();
  }
}
 
// Run migration
if (require.main === module) {
  main();
}
 
module.exports = { main };