import_whmcs_customers.js

Go to the documentation of this file.

#!/usr/bin/env node
/**
 * Import customers from WHMCS export to PostgreSQL
 * Reads from exports/whmcs_export.json or exports/whmcs_clients.csv
 */
 
require('dotenv').config();
const fs = require('fs');
const path = require('path');
const pool = require('../services/db');
 
const EXPORT_DIR = path.join(__dirname, '../exports');
 
// Get tenant ID from Precise PCs tenant file or environment variable
/**
 *
 */
function getTenantId() {
  const tenantFilePath = path.join(EXPORT_DIR, 'precise_pcs_tenant.json');
  
  if (fs.existsSync(tenantFilePath)) {
    try {
      const tenantData = JSON.parse(fs.readFileSync(tenantFilePath, 'utf8'));
      console.log(`📂 Using tenant from file: ${tenantData.name} (${tenantData.tenant_id})`);
      return tenantData.tenant_id;
    } catch (error) {
      console.warn('⚠️  Could not read tenant file, using default');
    }
  }
  
  return process.env.TENANT_ID || process.env.DEFAULT_TENANT_ID || '00000000-0000-0000-0000-000000000001';
}
 
const TENANT_ID = getTenantId();
 
/**
 * Import customers from WHMCS data
 */
async function importCustomers() {
  try {
    console.log('========================================');
    console.log('WHMCS Customer Import');
    console.log('========================================\n');
    
    // Check if export file exists
    const jsonPath = path.join(EXPORT_DIR, 'whmcs_export.json');
    if (!fs.existsSync(jsonPath)) {
      throw new Error(`Export file not found: ${jsonPath}\nRun: node migration-scripts/fetch_whmcs_data.js`);
    }
    
    // Load export data
    const exportData = JSON.parse(fs.readFileSync(jsonPath, 'utf8'));
    const clients = exportData.clients || [];
    
    console.log(`Found ${clients.length} clients to import`);
    console.log(`Target tenant: ${TENANT_ID}\n`);
    
    // Verify tenant exists
    const tenantCheck = await pool.query(
      'SELECT tenant_id, name FROM tenants WHERE tenant_id = $1',
      [TENANT_ID]
    );
    
    if (tenantCheck.rows.length === 0) {
      throw new Error(`Tenant ${TENANT_ID} not found in database`);
    }
    
    console.log(`✅ Tenant verified: ${tenantCheck.rows[0].name}\n`);
    console.log('Starting import...\n');
    
    let imported = 0;
    let skipped = 0;
    let errors = 0;
    const importLog = [];
    
    for (const whmcsClient of clients) {
      try {
        // Skip if not active (optional - you can import all)
        if (whmcsClient.status !== 'Active') {
          console.log(`⏭️  Skipping ${whmcsClient.email} - status: ${whmcsClient.status}`);
          skipped++;
          continue;
        }
        
        // Check if customer already exists
        const existing = await pool.query(
          'SELECT customer_id FROM customers WHERE email = $1 AND tenant_id = $2',
          [whmcsClient.email, TENANT_ID]
        );
        
        if (existing.rows.length > 0) {
          console.log(`⏭️  Skipping ${whmcsClient.email} - already exists (ID: ${existing.rows[0].customer_id})`);
          skipped++;
          continue;
        }
        
        // Map WHMCS data to PostgreSQL format
        const customerName = whmcsClient.companyname || 
          `${whmcsClient.firstname} ${whmcsClient.lastname}`.trim();
        
        const contactName = `${whmcsClient.firstname} ${whmcsClient.lastname}`.trim();
        
        // Build address
        const address = [whmcsClient.address1, whmcsClient.address2]
          .filter(Boolean)
          .join(', ');
        
        // Build migration notes
        const notes = [
          `Migrated from WHMCS (Client ID: ${whmcsClient.id})`,
          whmcsClient.notes,
          `Original status: ${whmcsClient.status}`,
          `Date created: ${whmcsClient.datecreated}`,
          `Currency: ${whmcsClient.currency || 'AUD'}`
        ].filter(Boolean).join('\n');
        
        // Insert customer
        const result = await pool.query(`
          INSERT INTO customers (
            tenant_id, name, contact_name, email, phone,
            address, city, state, postal_code, country,
            status, created_at, notes
          ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
          RETURNING customer_id, name, email
        `, [
          TENANT_ID,
          customerName,
          contactName,
          whmcsClient.email,
          whmcsClient.phonenumber || null,
          address || null,
          whmcsClient.city || null,
          whmcsClient.state || null,
          whmcsClient.postcode || null,
          whmcsClient.country || 'AU',
          'active', // Always set to active for now
          whmcsClient.datecreated || new Date(),
          notes
        ]);
        
        const newCustomer = result.rows[0];
        console.log(`✅ Imported: ${newCustomer.name} (${newCustomer.email}) - ID: ${newCustomer.customer_id}`);
        
        importLog.push({
          whmcs_id: whmcsClient.id,
          postgres_id: newCustomer.customer_id,
          name: newCustomer.name,
          email: newCustomer.email,
          status: 'imported'
        });
        
        imported++;
        
      } catch (err) {
        console.error(`❌ Error importing ${whmcsClient.email}:`, err.message);
        importLog.push({
          whmcs_id: whmcsClient.id,
          name: `${whmcsClient.firstname} ${whmcsClient.lastname}`,
          email: whmcsClient.email,
          status: 'error',
          error: err.message
        });
        errors++;
      }
    }
    
    // Save import log
    const logPath = path.join(EXPORT_DIR, 'customer_import_log.json');
    fs.writeFileSync(logPath, JSON.stringify(importLog, null, 2));
    
    // Summary
    console.log('\n========================================');
    console.log('Import Summary');
    console.log('========================================');
    console.log(`✅ Imported: ${imported}`);
    console.log(`⏭️  Skipped:  ${skipped}`);
    console.log(`❌ Errors:   ${errors}`);
    console.log(`\nImport log saved: ${logPath}`);
    console.log('========================================\n');
    
    if (imported > 0) {
      console.log('Next steps:');
      console.log('1. Review imported customers in dashboard');
      console.log('2. Import domains: node migration-scripts/import_whmcs_domains.js');
      console.log('3. Link products/services to customers\n');
    }
    
  } catch (error) {
    console.error('\n❌ Import failed:', error.message);
    console.error(error.stack);
    process.exit(1);
  } finally {
    await pool.end();
  }
}
 
// Run if called directly
if (require.main === module) {
  importCustomers();
}
 
module.exports = { importCustomers };