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

const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'sts_v2'
};

async function fixStudentData() {
  let connection;
  
  try {
    connection = await mysql.createConnection(dbConfig);
    console.log('Connected to database');

    // Update children to belong to school user ID 100
    await connection.execute(`
      UPDATE children SET school_id = 100 WHERE school_id IS NULL OR school_id != 100
    `);

    // Check current students
    const [students] = await connection.execute(`
      SELECT c.id, c.first_name, c.last_name, c.standard, c.division, c.roll_number,
             c.school_id, d.id as device_id, d.type as device_type, d.status as device_status,
             d.battery_level, d.last_update
      FROM children c
      LEFT JOIN devices d ON c.device_id = d.id
      WHERE c.school_id = 100
      ORDER BY c.standard, c.division, c.roll_number
    `);

    console.log('Current students for school ID 100:');
    console.table(students);

    if (students.length === 0) {
      console.log('No students found. Creating test students...');
      
      // Create test students for school ID 100
      await connection.execute(`
        INSERT INTO children (parent_id, first_name, last_name, standard, division, roll_number, school_id, device_id) VALUES 
        (4, 'John', 'Doe', '5th', 'A', 1, 100, 'DEV001'),
        (4, 'Jane', 'Smith', '4th', 'B', 2, 100, 'DEV002'),
        (4, 'Mike', 'Johnson', '6th', 'A', 3, 100, 'DEV003'),
        (4, 'Sarah', 'Wilson', '5th', 'C', 4, 100, 'DEV004')
        ON DUPLICATE KEY UPDATE school_id = VALUES(school_id)
      `);

      console.log('Test students created successfully!');
    }

    // Verify the fix
    const [updatedStudents] = await connection.execute(`
      SELECT c.id, c.first_name, c.last_name, c.standard, c.division, c.roll_number,
             c.school_id, d.id as device_id, d.type as device_type, d.status as device_status,
             d.battery_level, d.last_update
      FROM children c
      LEFT JOIN devices d ON c.device_id = d.id
      WHERE c.school_id = 100
      ORDER BY c.standard, c.division, c.roll_number
    `);

    console.log('Updated students for school ID 100:');
    console.table(updatedStudents);

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

fixStudentData();