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

async function investigateNameField() {
  try {
    console.log('Investigating potential name field sources...');
    
    // Check if there are any views that might have a name field
    const [views] = await db.execute(`
      SELECT TABLE_NAME, VIEW_DEFINITION 
      FROM INFORMATION_SCHEMA.VIEWS 
      WHERE TABLE_SCHEMA = DATABASE()
    `);
    console.log('Database views:', views);
    
    // Check if there are any computed columns or generated columns
    const [columns] = await db.execute(`
      SELECT TABLE_NAME, COLUMN_NAME, GENERATION_EXPRESSION, IS_GENERATED
      FROM INFORMATION_SCHEMA.COLUMNS 
      WHERE TABLE_SCHEMA = DATABASE() 
      AND TABLE_NAME = 'children'
      AND IS_GENERATED = 'ALWAYS'
    `);
    console.log('Generated columns:', columns);
    
    // Check if there's any parent_children table that might have a name field
    try {
      const [parentChildrenTable] = await db.execute('DESCRIBE parent_children');
      console.log('parent_children table structure:', parentChildrenTable);
    } catch (e) {
      console.log('parent_children table does not exist');
    }
    
    // Let's also check what the actual /parent/children endpoint would return
    console.log('\nTesting /parent/children endpoint logic:');
    
    // Test with parent ID 4 (which we know has children)
    const parentId = 4;
    const query = `
      SELECT c.id, c.first_name, c.last_name, c.standard, c.division, c.roll_number,
             c.device_id, c.parent_mobile_1, c.parent_mobile_2,
             'SchoolTrack' as school_name
      FROM children c
      WHERE c.parent_id = ?
      ORDER BY c.first_name
    `;
    
    const [rows] = await db.execute(query, [parentId]);
    console.log('Raw result from /parent/children query:', rows);
    
    // Check if any row has a name property that's numeric
    rows.forEach(row => {
      console.log(`Row ID: ${row.id}, keys: ${Object.keys(row)}`);
      if ('name' in row) {
        console.log(`  -> Has name field: ${row.name} (type: ${typeof row.name})`);
      }
    });
    
    // Let's also check if there's any data that could cause the reported issue
    console.log('\nLooking for any child with ID 51 or with numeric data:');
    const [problematicChildren] = await db.execute(`
      SELECT * FROM children 
      WHERE id = 51 
         OR first_name REGEXP '^[0-9]+$' 
         OR last_name REGEXP '^[0-9]+$'
         OR first_name IS NULL 
         OR last_name IS NULL
    `);
    console.log('Potentially problematic children:', problematicChildren);
    
    process.exit(0);
  } catch (error) {
    console.error('Error:', error.message);
    process.exit(1);
  }
}

investigateNameField();
