const mysql = require('mysql2/promise');
require('dotenv').config();

async function checkChildren() {
  const connection = await mysql.createConnection({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'sts_v2'
  });

  try {
    console.log('Checking children table...');
    
    const [children] = await connection.execute('SELECT * FROM children');
    console.log('📋 Current children records:');
    children.forEach(child => {
      console.log(`  ID: ${child.id}, Name: ${child.first_name} ${child.last_name}, School: ${child.school_id}, Parent: ${child.parent_id}`);
    });

    console.log('\n🔍 Checking for school_id = 1:');
    const [school1Children] = await connection.execute('SELECT * FROM children WHERE school_id = 1');
    console.log(`Found ${school1Children.length} children for school_id = 1`);

    // Clear and add fresh data
    console.log('\n🔄 Clearing and adding fresh student data...');
    
    // Delete existing children for school_id = 1
    await connection.execute('DELETE FROM children WHERE school_id = 1');
    
    // Add new children
    await connection.execute(`
      INSERT INTO children (parent_id, first_name, last_name, standard, division, roll_number, school_id, device_id, status) VALUES 
      (4, 'Emma', 'Johnson', '5th', 'A', 15, 1, 'DEV001', 'active'),
      (4, 'Alex', 'Johnson', '3rd', 'B', 22, 1, 'DEV002', 'active'),
      (4, 'Sarah', 'Smith', '7th', 'A', 8, 1, 'DEV003', 'active')
    `);

    console.log('✅ Added 3 new students for school_id = 1');

    // Verify
    const [newChildren] = await connection.execute('SELECT * FROM children WHERE school_id = 1');
    console.log('\n📋 New children for school_id = 1:');
    newChildren.forEach(child => {
      console.log(`  ${child.first_name} ${child.last_name} - ${child.standard}${child.division} - Roll: ${child.roll_number}`);
    });

  } catch (error) {
    console.error('Error:', error);
  } finally {
    await connection.end();
  }
}

checkChildren();