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

async function updateDevicesTable() {
  try {
    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'
    });

    console.log('Connected to database');

    // Add columns one by one
    const columnsToAdd = [
      { name: 'school_id', type: 'INT' },
      { name: 'device_id', type: 'VARCHAR(50)' },
      { name: 'imei_number', type: 'VARCHAR(20)' },
      { name: 'sim_number', type: 'VARCHAR(15)' },
      { name: 'model', type: 'VARCHAR(50)' },
      { name: 'manufacturer', type: 'VARCHAR(50)' }
    ];

    for (const column of columnsToAdd) {
      try {
        await connection.execute(`ALTER TABLE devices ADD COLUMN ${column.name} ${column.type}`);
        console.log(`Added column: ${column.name}`);
      } catch (error) {
        if (error.code === 'ER_DUP_FIELDNAME') {
          console.log(`Column ${column.name} already exists`);
        } else {
          console.log(`Error adding column ${column.name}:`, error.message);
        }
      }
    }

    // Update existing devices with school_id and other data
    await connection.execute(`
      UPDATE devices SET 
        school_id = 1,
        device_id = id,
        imei_number = CONCAT('IMEI', LPAD(SUBSTRING(id, 4), 12, '0')),
        sim_number = CONCAT('98765432', LPAD(SUBSTRING(id, 4), 2, '0')),
        model = 'GT06N',
        manufacturer = 'Concox'
    `);
    console.log('Updated existing devices with school_id and additional data');

    // Insert additional sample devices
    try {
      await connection.execute(`
        INSERT IGNORE INTO devices (id, device_id, school_id, type, status, battery_level, imei_number, sim_number, model, manufacturer) VALUES 
        ('DEV005', 'DEV005', 1, 'GPS Tracker', 'active', 88, 'IMEI123456789005', '9876543205', 'GT06N', 'Concox'),
        ('DEV006', 'DEV006', 1, 'GPS Tracker', 'active', 95, 'IMEI123456789006', '9876543206', 'GT06N', 'Concox')
      `);
      console.log('Inserted additional sample devices');
    } catch (error) {
      console.log('Sample devices might already exist');
    }

    // Verify the data
    const [rows] = await connection.execute('SELECT * FROM devices WHERE school_id = 1');
    console.log('Current devices in database:', rows);

    await connection.end();
    console.log('Database update completed successfully!');
  } catch (error) {
    console.error('Error updating database:', error);
  }
}

updateDevicesTable();