import_whmcs_domains.js

Go to the documentation of this file.

#!/usr/bin/env node
 
/**
 * Import domains from WHMCS export to PostgreSQL
 * Links domains to imported customers in the Precise PCs tenant
 * Imports ALL domains, even if not registered through us
 */
 
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
/**
 *
 */
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: ${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 || '00000000-0000-0000-0000-000000000001';
}
 
/**
 * Map WHMCS status to our domain status
 * @param whmcsStatus
 */
function mapDomainStatus(whmcsStatus) {
  const statusMap = {
    'Active': 'active',
    'Pending': 'pending',
    'Pending Transfer': 'pending_transfer',
    'Expired': 'expired',
    'Cancelled': 'cancelled',
    'Fraud': 'cancelled',
    'Transferred Away': 'transferred_out',
    'Redemption': 'redemption'
  };
  
  return statusMap[whmcsStatus] || 'unknown';
}
 
/**
 * Parse date from WHMCS format
 * @param dateString
 */
function parseDate(dateString) {
  if (!dateString || dateString === 'Invalid Date') {
    return null;
  }
  const date = new Date(dateString);
  return isNaN(date.getTime()) ? null : date.toISOString();
}
 
/**
 * Create customer lookup map from WHMCS user ID to our customer ID
 * @param tenantId
 */
async function createCustomerLookup(tenantId) {
  const importLogPath = path.join(EXPORT_DIR, 'customer_import_log.json');
  
  if (fs.existsSync(importLogPath)) {
    const log = JSON.parse(fs.readFileSync(importLogPath, 'utf8'));
    const lookup = {};
    
    // Handle both array format and object with 'imported' property
    const entries = Array.isArray(log) ? log : (log.imported || []);
    
    for (const entry of entries) {
      if (entry.whmcs_id && entry.postgres_id) {
        lookup[entry.whmcs_id] = entry.postgres_id;
      } else if (entry.whmcs_id && entry.customer_id) {
        lookup[entry.whmcs_id] = entry.customer_id;
      }
    }
    
    console.log(`📋 Customer lookup: ${Object.keys(lookup).length} mappings loaded`);
    return lookup;
  }
  
  // Fallback: query database for customers with WHMCS IDs in notes
  const result = await pool.query(
    `SELECT customer_id, notes FROM customers WHERE tenant_id = $1 AND notes LIKE '%WHMCS ID:%'`,
    [tenantId]
  );
  
  const lookup = {};
  for (const row of result.rows) {
    const match = row.notes.match(/WHMCS ID:\s*(\d+)/);
    if (match) {
      lookup[parseInt(match[1])] = row.customer_id;
    }
  }
  
  console.log(`📋 Customer lookup from database: ${Object.keys(lookup).length} mappings found`);
  return lookup;
}
 
/**
 * Import domains from WHMCS data
 */
async function importDomains() {
  const tenantId = getTenantId();
  
  try {
    console.log('========================================');
    console.log('WHMCS Domain 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 domains = exportData.domains || [];
    
    console.log(`Found ${domains.length} domains to import`);
    console.log(`Target tenant: ${tenantId}\n`);
    
    // Create customer lookup
    const customerLookup = await createCustomerLookup(tenantId);
    
    console.log('Starting domain import...\n');
    
    let imported = 0;
    let skipped = 0;
    let errors = 0;
    const importLog = {
      tenant_id: tenantId,
      imported_at: new Date().toISOString(),
      imported: [],
      skipped: [],
      errors: []
    };
    
    for (const domain of domains) {
      try {
        // Skip if no domain name
        if (!domain.domain || domain.domain.trim() === '') {
          skipped++;
          importLog.skipped.push({
            whmcs_id: domain.id,
            reason: 'No domain name'
          });
          continue;
        }
        
        // Get customer ID
        const customerId = customerLookup[domain.userid];
        if (!customerId) {
          console.log(`⚠️  Skipping ${domain.domain}: Customer not found (WHMCS user ${domain.userid})`);
          skipped++;
          importLog.skipped.push({
            whmcs_id: domain.id,
            domain: domain.domain,
            whmcs_user_id: domain.userid,
            reason: 'Customer not found in system'
          });
          continue;
        }
        
        // Check if domain already exists
        const existing = await pool.query(
          'SELECT domain_id FROM domains WHERE tenant_id = $1 AND domain_name = $2',
          [tenantId, domain.domain.toLowerCase()]
        );
        
        if (existing.rows.length > 0) {
          console.log(`⏭️  Skipping ${domain.domain}: Already exists`);
          skipped++;
          importLog.skipped.push({
            whmcs_id: domain.id,
            domain: domain.domain,
            reason: 'Already exists'
          });
          continue;
        }
        
        // Prepare domain data
        const domainData = {
          tenant_id: tenantId,
          customer_id: customerId,
          domain_name: domain.domain.toLowerCase(),
          registrar: domain.registrar || 'unknown',
          registrar_domain_id: domain.id.toString(),
          status: mapDomainStatus(domain.status),
          registration_date: parseDate(domain.registrationdate),
          expiration_date: parseDate(domain.expirydate),
          auto_renew: domain.donotrenew ? false : true,
          whois_privacy: domain.idprotection ? true : false,
          renewal_price: parseFloat(domain.recurringamount) || 0,
          currency: 'AUD',
          notes: `Migrated from WHMCS\nWHMCS Domain ID: ${domain.id}\nRegistration Type: ${domain.type || 'Unknown'}\nPayment Method: ${domain.paymentmethod || 'N/A'}`
        };
        
        // Insert domain
        const insertQuery = `
          INSERT INTO domains (
            tenant_id, customer_id, domain_name, registrar, registrar_domain_id,
            status, registration_date, expiration_date, auto_renew, whois_privacy,
            renewal_price, currency, notes, created_at, updated_at
          ) VALUES (
            $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, NOW(), NOW()
          )
          RETURNING domain_id, domain_name, status
        `;
        
        const result = await pool.query(insertQuery, [
          domainData.tenant_id,
          domainData.customer_id,
          domainData.domain_name,
          domainData.registrar,
          domainData.registrar_domain_id,
          domainData.status,
          domainData.registration_date,
          domainData.expiration_date,
          domainData.auto_renew,
          domainData.whois_privacy,
          domainData.renewal_price,
          domainData.currency,
          domainData.notes
        ]);
        
        const insertedDomain = result.rows[0];
        
        // Format expiry date for display
        const expiryDisplay = domain.expirydate && domain.expirydate !== 'Invalid Date' 
          ? new Date(domain.expirydate).toLocaleDateString() 
          : 'N/A';
        
        console.log(`✅ Imported: ${insertedDomain.domain_name} (${insertedDomain.status}) - Expires: ${expiryDisplay}`);
        
        imported++;
        importLog.imported.push({
          domain_id: insertedDomain.domain_id,
          domain_name: insertedDomain.domain_name,
          whmcs_id: domain.id,
          whmcs_user_id: domain.userid,
          customer_id: customerId,
          status: insertedDomain.status,
          registrar: domainData.registrar,
          expiration_date: domainData.expiration_date
        });
        
      } catch (error) {
        console.error(`❌ Error importing ${domain.domain}:`, error.message);
        errors++;
        importLog.errors.push({
          whmcs_id: domain.id,
          domain: domain.domain,
          error: error.message
        });
      }
    }
    
    // Save import log
    const logPath = path.join(EXPORT_DIR, 'domain_import_log.json');
    fs.writeFileSync(logPath, JSON.stringify(importLog, null, 2));
    
    console.log('\n========================================');
    console.log('Domain 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');
    
    // Show import status by domain status
    if (imported > 0) {
      const statusCounts = {};
      for (const entry of importLog.imported) {
        statusCounts[entry.status] = (statusCounts[entry.status] || 0) + 1;
      }
      
      console.log('Domains by status:');
      for (const [status, count] of Object.entries(statusCounts)) {
        console.log(`  ${status}: ${count}`);
      }
      console.log('');
    }
    
    console.log('Next steps:');
    console.log('1. Review domains in dashboard');
    console.log('2. Verify domain-customer relationships');
    console.log('3. Set up domain renewal notifications');
    console.log('4. Import hosting products/contracts');
    
  } catch (error) {
    console.error('❌ Domain import failed:', error.message);
    throw error;
  } finally {
    await pool.end();
  }
}
 
// Run if executed directly
if (require.main === module) {
  importDomains()
    .then(() => {
      console.log('\n✅ Domain import complete');
      process.exit(0);
    })
    .catch((error) => {
      console.error('\n❌ Domain import failed:', error);
      process.exit(1);
    });
}
 
module.exports = { importDomains };