migrate-whmcs-direct.js

Go to the documentation of this file.

#!/usr/bin/env node
/**
 * WHMCS to RMM Backend Migration Script (Direct Database)
 * 
 * Migrates all data from WHMCS MySQL to RMM PostgreSQL:
 * - tblclients → customers table
 * - tblproducts → products table  
 * - tblinvoices → invoices table
 * - tblhosting → contracts + contract_line_items tables
 */
 
const mysql = require('mysql2/promise');
const { Pool } = require('pg');
 
// ===========================
// CONFIGURATION
// ===========================
 
// WHMCS MySQL Configuration (via SSH tunnel on localhost:3307)
const MYSQL_CONFIG = {
  host: 'localhost',
  port: 3307,
  user: 'precisewebhostin_migration',
  password: 'Zrepowfg1-cFS,TP',
  database: 'precisewebhostin_whmcs'
};
 
// 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';
 
// ===========================
// 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 mysqlConn
 * @param pgPool
 * @param tenantId
 */
async function migrateCustomers(mysqlConn, pgPool, tenantId) {
  console.log('\n=== MIGRATING CUSTOMERS ===');
  
  const [customers] = await mysqlConn.query(`
    SELECT id, firstname, lastname, companyname, email, phonenumber,
           address1, address2, city, state, postcode, country, status
    FROM tblclients
    ORDER BY id
  `);
  
  console.log(`Found ${customers.length} customers in WHMCS`);
  
  let imported = 0;
  let skipped = 0;
  const customerMap = {}; // Map WHMCS ID to RMM ID
  
  for (const customer of customers) {
    try {
      // Check if customer already exists
      const existing = await pgPool.query(
        'SELECT customer_id FROM customers WHERE email = $1 AND tenant_id = $2',
        [customer.email, tenantId]
      );
      
      if (existing.rows.length > 0) {
        customerMap[customer.id] = existing.rows[0].customer_id;
        skipped++;
        continue;
      }
      
      // Map WHMCS fields to RMM schema
      const customerName = customer.companyname || 
        `${customer.firstname} ${customer.lastname}`.trim();
      const contactName = `${customer.firstname} ${customer.lastname}`.trim();
      const address = `${customer.address1}\n${customer.address2 || ''}`.trim();
      const status = customer.status === 'Active' ? 'active' : 'inactive';
      
      // Insert customer
      const result = await pgPool.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, customer.email, customer.phonenumber,
        address, customer.city, customer.state, customer.postcode, customer.country, status
      ]);
      
      customerMap[customer.id] = result.rows[0].customer_id;
      
      imported++;
      if (imported % 10 === 0) {
        process.stdout.write(`\r  Processed: ${imported}/${customers.length}`);
      }
      
    } catch (error) {
      console.error(`\n✗ Failed to import ${customer.email}:`, error.message);
    }
  }
  
  console.log(`\n✓ Customers migration complete: ${imported} imported, ${skipped} skipped`);
  return customerMap;
}
 
/**
 *
 * @param mysqlConn
 * @param pgPool
 * @param tenantId
 */
async function migrateProducts(mysqlConn, pgPool, tenantId) {
  console.log('\n=== MIGRATING PRODUCTS ===');
  
  const [products] = await mysqlConn.query(`
    SELECT id, name, description, type
    FROM tblproducts
    WHERE hidden = 0
    ORDER BY id
  `);
  
  console.log(`Found ${products.length} products in WHMCS`);
  
  let imported = 0;
  let skipped = 0;
  const productMap = {}; // Map WHMCS ID to RMM ID
  
  for (const product of products) {
    try {
      // Check if product already exists
      const existing = await pgPool.query(
        'SELECT product_id FROM products WHERE name = $1 AND tenant_id = $2',
        [product.name, tenantId]
      );
      
      if (existing.rows.length > 0) {
        productMap[product.id] = existing.rows[0].product_id;
        skipped++;
        continue;
      }
      
      // Get pricing for this product
      const [pricing] = await mysqlConn.query(`
        SELECT monthly, quarterly, semiannually, annually
        FROM tblpricing
        WHERE relid = ? AND type = 'product'
        LIMIT 1
      `, [product.id]);
      
      const monthlyPrice = pricing.length > 0 ? parseFloat(pricing[0].monthly || 0) : 0;
      
      // Insert product
      const result = await pgPool.query(`
        INSERT INTO products (
          tenant_id, name, description, unit_price, price_retail, price_ex_tax, created_at
        ) VALUES ($1, $2, $3, $4, $4, $4, NOW())
        RETURNING product_id
      `, [
        tenantId,
        product.name,
        product.description || '',
        monthlyPrice
      ]);
      
      productMap[product.id] = result.rows[0].product_id;
      
      imported++;
      
    } catch (error) {
      console.error(`✗ Failed to import ${product.name}:`, error.message);
    }
  }
  
  console.log(`\n✓ Products migration complete: ${imported} imported, ${skipped} skipped`);
  return productMap;
}
 
/**
 *
 * @param mysqlConn
 * @param pgPool
 * @param tenantId
 * @param customerMap
 */
async function migrateInvoices(mysqlConn, pgPool, tenantId, customerMap) {
  console.log('\n=== MIGRATING INVOICES ===');
  
  const [invoices] = await mysqlConn.query(`
    SELECT id, userid, invoicenum, date, duedate, subtotal, tax, total, status
    FROM tblinvoices
    ORDER BY id
    LIMIT 1000
  `);
  
  console.log(`Found ${invoices.length} invoices in WHMCS`);
  
  let imported = 0;
  let skipped = 0;
  
  for (const invoice of invoices) {
    try {
      // Get RMM customer ID
      const rmmCustomerId = customerMap[invoice.userid];
      if (!rmmCustomerId) {
        skipped++;
        continue;
      }
      
      // Check if invoice already exists
      const existing = await pgPool.query(
        'SELECT invoice_id FROM invoices WHERE customer_id = $1 AND issued_date = $2 AND total = $3 AND tenant_id = $4',
        [rmmCustomerId, invoice.date, parseFloat(invoice.total || 0), tenantId]
      );
      
      if (existing.rows.length > 0) {
        skipped++;
        continue;
      }
      
      // Map invoice status
      let status = 'draft';
      let paymentStatus = 'unpaid';
      if (invoice.status === 'Paid') {
        status = 'paid';
        paymentStatus = 'paid';
      } else if (invoice.status === 'Unpaid') {
        status = 'sent';
        paymentStatus = 'unpaid';
      } else if (invoice.status === 'Cancelled') {
        status = 'void';
        paymentStatus = 'cancelled';
      }
      
      // Insert invoice
      await pgPool.query(`
        INSERT INTO invoices (
          tenant_id, customer_id, issued_date, due_date,
          subtotal, tax_total, total, status, payment_status, description
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
      `, [
        tenantId,
        rmmCustomerId,
        invoice.date,
        invoice.duedate,
        parseFloat(invoice.subtotal || 0),
        parseFloat(invoice.tax || 0),
        parseFloat(invoice.total || 0),
        status,
        paymentStatus,
        `Migrated from WHMCS Invoice #${invoice.id} - ${invoice.invoicenum || ''}`
      ]);
      
      imported++;
      if (imported % 50 === 0) {
        process.stdout.write(`\r  Processed: ${imported}/${invoices.length}`);
      }
      
    } catch (error) {
      console.error(`\n✗ Failed to import invoice ${invoice.id}:`, error.message);
    }
  }
  
  console.log(`\n✓ Invoices migration complete: ${imported} imported, ${skipped} skipped`);
}
 
/**
 *
 * @param mysqlConn
 * @param pgPool
 * @param tenantId
 * @param customerMap
 * @param productMap
 */
async function migrateServicesAndContracts(mysqlConn, pgPool, tenantId, customerMap, productMap) {
  console.log('\n=== MIGRATING CLIENT SERVICES & CREATING CONTRACTS ===');
  
  const [services] = await mysqlConn.query(`
    SELECT id, userid, packageid, domain, regdate, nextduedate,
           billingcycle, amount, domainstatus
    FROM tblhosting
    WHERE domainstatus NOT IN ('Terminated', 'Cancelled')
    ORDER BY id
  `);
  
  console.log(`Found ${services.length} active services in WHMCS`);
  
  let imported = 0;
  let skipped = 0;
  
  for (const service of services) {
    try {
      // Get RMM customer ID
      const rmmCustomerId = customerMap[service.userid];
      if (!rmmCustomerId) {
        skipped++;
        continue;
      }
      
      // Get customer name for logging
      const customerResult = await pgPool.query(
        'SELECT name FROM customers WHERE customer_id = $1',
        [rmmCustomerId]
      );
      const customerName = customerResult.rows[0]?.name || 'Unknown';
      
      // Get product name
      const [productRows] = await mysqlConn.query(
        'SELECT name FROM tblproducts WHERE id = ?',
        [service.packageid]
      );
      const productName = productRows.length > 0 ? productRows[0].name : 'Service';
      
      // Check if contract already exists
      const existing = await pgPool.query(
        'SELECT contract_id FROM contracts WHERE customer_id = $1 AND title ILIKE $2',
        [rmmCustomerId, `%${productName}%`]
      );
      
      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';
      else if (service.billingcycle === 'Free Account') {
        skipped++;
        continue; // Skip free accounts
      }
      
      // 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.domainstatus === 'Pending') contractStatus = 'draft';
      else if (service.domainstatus === 'Suspended') contractStatus = 'suspended';
      
      // Create contract
      const contractResult = await pgPool.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,
        rmmCustomerId,
        productName,
        `${productName} - ${service.domain || ''}`,
        contractStatus,
        service.regdate || new Date().toISOString().split('T')[0],
        billingInterval,
        nextBillingDate,
        true, // auto_invoice_enabled
        0, // labor_rate
        `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 rmmProductId = productMap[service.packageid] || null;
      const amount = parseFloat(service.amount || 0);
      
      await pgPool.query(`
        INSERT INTO contract_line_items (
          contract_id, product_id, description, quantity, unit_price, recurring
        ) VALUES ($1, $2, $3, $4, $5, $6)
      `, [
        contractId,
        rmmProductId,
        productName,
        1,
        amount,
        true
      ]);
      
      imported++;
      if (imported % 10 === 0) {
        process.stdout.write(`\r  Processed: ${imported}/${services.length}`);
      }
      
    } catch (error) {
      console.error(`\n✗ Failed to create contract for service ${service.id}:`, error.message);
    }
  }
  
  console.log(`\n✓ Contracts migration complete: ${imported} created, ${skipped} skipped`);
}
 
// ===========================
// MAIN MIGRATION
// ===========================
 
/**
 *
 */
async function main() {
  console.log('╔══════════════════════════════════════════════════════════╗');
  console.log('║   WHMCS → RMM Backend Migration Script (Direct DB)      ║');
  console.log('╚══════════════════════════════════════════════════════════╝\n');
  
  let mysqlConn;
  let pgPool;
  
  try {
    // Connect to MySQL
    console.log('Connecting to WHMCS MySQL database...');
    mysqlConn = await mysql.createConnection(MYSQL_CONFIG);
    console.log('✓ WHMCS database connected\n');
    
    // Connect to PostgreSQL
    console.log('Connecting to RMM PostgreSQL database...');
    pgPool = new Pool(PG_CONFIG);
    await pgPool.query('SELECT NOW()');
    console.log('✓ RMM database connected\n');
    
    // Get or create tenant
    console.log(`Getting tenant ID for: ${DEFAULT_TENANT_NAME}...`);
    const tenantId = await getTenantId(pgPool, DEFAULT_TENANT_NAME);
    console.log(`✓ Tenant ID: ${tenantId}\n`);
    
    // Run migrations in sequence
    const customerMap = await migrateCustomers(mysqlConn, pgPool, tenantId);
    const productMap = await migrateProducts(mysqlConn, pgPool, tenantId);
    await migrateInvoices(mysqlConn, pgPool, tenantId, customerMap);
    await migrateServicesAndContracts(mysqlConn, pgPool, tenantId, customerMap, productMap);
    
    console.log('\n╔══════════════════════════════════════════════════════════╗');
    console.log('║   MIGRATION COMPLETE ✓                                   ║');
    console.log('╚══════════════════════════════════════════════════════════╝\n');
    
    // Summary query
    const stats = await pgPool.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 {
    if (mysqlConn) await mysqlConn.end();
    if (pgPool) await pgPool.end();
  }
}
 
// Run migration
if (require.main === module) {
  main();
}
 
module.exports = { main };