const db = require('./config/database');

async function investigateAllData() {
  try {
    console.log('Investigating all parent-child data...');
    
    // Check all parents
    const [parents] = await db.execute('SELECT id, first_name, last_name, email FROM users WHERE user_type = "parent"');
    console.log('All parents:', parents);
    
    // Check all children with their parent IDs
    const [allChildren] = await db.execute('SELECT id, first_name, last_name, parent_id FROM children ORDER BY parent_id, id');
    console.log('\nAll children:');
    allChildren.forEach(child => {
      console.log(`Child ID: ${child.id}, Name: ${child.first_name} ${child.last_name}, Parent ID: ${child.parent_id}`);
    });
    
    // Look for any parent that has a child with ID 51 or any child that might have numeric names
    console.log('\nLooking for any problematic data:');
    const [problematicData] = await db.execute(`
      SELECT c.id as child_id, c.first_name, c.last_name, c.parent_id,
             u.first_name as parent_first_name, u.last_name as parent_last_name,
             CASE 
               WHEN c.first_name REGEXP '^[0-9]+$' THEN 'first_name is numeric'
               WHEN c.last_name REGEXP '^[0-9]+$' THEN 'last_name is numeric'
               WHEN c.first_name IS NULL THEN 'first_name is NULL'
               WHEN c.last_name IS NULL THEN 'last_name is NULL'
               ELSE 'OK'
             END as issue
      FROM children c
      LEFT JOIN users u ON c.parent_id = u.id
      WHERE c.first_name REGEXP '^[0-9]+$' 
         OR c.last_name REGEXP '^[0-9]+$' 
         OR c.first_name IS NULL 
         OR c.last_name IS NULL
         OR c.id = 51
    `);
    console.log('Problematic data found:', problematicData);
    
    // Test each parent with children to see the API response
    console.log('\nTesting API response for each parent with children:');
    const parentIdsWithChildren = [...new Set(allChildren.map(c => c.parent_id).filter(id => id))];
    
    for (const parentId of parentIdsWithChildren) {
      console.log(`\n=== Testing parent ID: ${parentId} ===`);
      
      const [rows] = await db.execute(`
        SELECT c.*, s.name as school_name
        FROM children c
        LEFT JOIN schools s ON c.school_id = s.id
        WHERE c.parent_id = ? AND c.parent_id > 0
      `, [parentId]);
      
      const children = rows.map(child => ({
        id: child.id,
        name: `${child.first_name} ${child.last_name}`
      }));
      
      console.log(`Parent ${parentId} children:`, children);
      
      // Check if any name is a number (this would be the bug)
      children.forEach(child => {
        if (typeof child.name === 'number') {
          console.log(`🐛 BUG FOUND: Child ID ${child.id} has name as number: ${child.name}`);
        }
      });
    }
    
    process.exit(0);
  } catch (error) {
    console.error('Error:', error.message);
    process.exit(1);
  }
}

investigateAllData();
