import pkg from 'pg';
const { Client, Pool } = pkg;

// Use the same external database connection
const pool = new Pool({
  connectionString: "postgresql://neondb_owner:Maniac20!@userza1004.openweb.direct/masterfile",
  ssl: { rejectUnauthorized: false }
});

async function inspectMTNMobileData() {
  console.log('=== MTN Mobile (GSM) Data Inspection ===');
  
  try {
    const result = await pool.query(`
      SELECT id, name, account_number, sim_serial_number, msisdn 
      FROM clients 
      WHERE category = 'MTN Mobile (GSM)' 
      ORDER BY id ASC 
      LIMIT 50
    `);
    
    console.log(`Found ${result.rows.length} MTN Mobile clients`);
    console.log('');
    
    let issuesFound = 0;
    
    for (const row of result.rows) {
      const { id, name, account_number, sim_serial_number, msisdn } = row;
      
      // Check if account_number looks like a SIM serial (typically numeric, no @mobile.is.co.za)
      const isAccountNumberSIM = account_number && 
        !account_number.includes('@mobile.is.co.za') && 
        account_number.length > 10 && 
        /^\d+$/.test(account_number);
      
      // Check if sim_serial_number looks like an account (contains @mobile.is.co.za)
      const isSimSerialAccount = sim_serial_number && 
        sim_serial_number.includes('@mobile.is.co.za');
      
      if (isAccountNumberSIM || isSimSerialAccount) {
        issuesFound++;
        console.log(`❌ ISSUE FOUND - Client ID ${id}: ${name}`);
        console.log(`   Account Number: ${account_number || 'NULL'}`);
        console.log(`   SIM Serial Number: ${sim_serial_number || 'NULL'}`);
        console.log(`   MSISDN: ${msisdn || 'NULL'}`);
        
        if (isAccountNumberSIM && isSimSerialAccount) {
          console.log('   📝 SUGGESTION: Swap Account Number and SIM Serial Number');
        } else if (isAccountNumberSIM && !sim_serial_number) {
          console.log('   📝 SUGGESTION: Move Account Number to SIM Serial Number field');
        }
        console.log('');
      } else {
        console.log(`✅ OK - Client ID ${id}: ${name}`);
        console.log(`   Account: ${account_number || 'NULL'} | SIM: ${sim_serial_number || 'NULL'}`);
        console.log('');
      }
    }
    
    console.log(`=== SUMMARY ===`);
    console.log(`Total clients checked: ${result.rows.length}`);
    console.log(`Issues found: ${issuesFound}`);
    
    if (issuesFound > 0) {
      console.log('');
      console.log('To fix these issues, you can run the correction function.');
    }
    
  } catch (error) {
    console.error('Error inspecting data:', error);
  }
}

async function correctMTNMobileData() {
  console.log('=== MTN Mobile (GSM) Data Correction ===');
  
  try {
    // Find clients where Account Number appears to be a SIM serial and SIM Serial appears to be an account
    const swapQuery = `
      SELECT id, name, account_number, sim_serial_number 
      FROM clients 
      WHERE category = 'MTN Mobile (GSM)' 
      AND account_number IS NOT NULL 
      AND sim_serial_number IS NOT NULL
      AND account_number !~ '@mobile\.is\.co\.za'
      AND sim_serial_number ~ '@mobile\.is\.co\.za'
      AND LENGTH(account_number) > 10
      AND account_number ~ '^[0-9]+$'
    `;
    
    const swapResult = await pool.query(swapQuery);
    console.log(`Found ${swapResult.rows.length} clients that need field swapping`);
    
    let fixedCount = 0;
    
    for (const row of swapResult.rows) {
      const { id, name, account_number, sim_serial_number } = row;
      
      console.log(`Fixing Client ID ${id}: ${name}`);
      console.log(`  Before: Account=${account_number}, SIM=${sim_serial_number}`);
      
      // Swap the values
      const updateQuery = `
        UPDATE clients 
        SET account_number = $1, sim_serial_number = $2 
        WHERE id = $3
      `;
      
      await pool.query(updateQuery, [sim_serial_number, account_number, id]);
      fixedCount++;
      
      console.log(`  After:  Account=${sim_serial_number}, SIM=${account_number}`);
      console.log('  ✅ Fixed!');
      console.log('');
    }
    
    // Find clients where Account Number looks like SIM serial but SIM Serial is empty
    const moveQuery = `
      SELECT id, name, account_number 
      FROM clients 
      WHERE category = 'MTN Mobile (GSM)' 
      AND account_number IS NOT NULL 
      AND (sim_serial_number IS NULL OR sim_serial_number = '')
      AND account_number !~ '@mobile\.is\.co\.za'
      AND LENGTH(account_number) > 10
      AND account_number ~ '^[0-9]+$'
    `;
    
    const moveResult = await pool.query(moveQuery);
    console.log(`Found ${moveResult.rows.length} clients that need Account Number moved to SIM Serial`);
    
    for (const row of moveResult.rows) {
      const { id, name, account_number } = row;
      
      console.log(`Moving data for Client ID ${id}: ${name}`);
      console.log(`  Before: Account=${account_number}, SIM=NULL`);
      
      // Move account_number to sim_serial_number and clear account_number
      const moveUpdateQuery = `
        UPDATE clients 
        SET account_number = NULL, sim_serial_number = $1 
        WHERE id = $2
      `;
      
      await pool.query(moveUpdateQuery, [account_number, id]);
      fixedCount++;
      
      console.log(`  After:  Account=NULL, SIM=${account_number}`);
      console.log('  ✅ Moved!');
      console.log('');
    }
    
    console.log(`=== CORRECTION COMPLETE ===`);
    console.log(`Total clients fixed: ${fixedCount}`);
    
  } catch (error) {
    console.error('Error correcting data:', error);
  }
}

// Allow running specific functions
const action = process.argv[2];

if (action === 'inspect') {
  inspectMTNMobileData().then(() => process.exit(0));
} else if (action === 'correct') {
  correctMTNMobileData().then(() => process.exit(0));
} else {
  console.log('Usage:');
  console.log('  node data-fix-tool.js inspect  - Check for data issues');
  console.log('  node data-fix-tool.js correct  - Fix data issues');
  process.exit(1);
}