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 setupLiveTrackingTest() {
  let connection;
  
  try {
    connection = await mysql.createConnection(dbConfig);
    console.log('Connected to database');

    // First, let's check if we have the required tables
    const [tables] = await connection.execute("SHOW TABLES");
    console.log('Available tables:', tables.map(t => Object.values(t)[0]));

    // Check if we have schools
    const [schools] = await connection.execute('SELECT * FROM schools LIMIT 1');
    if (schools.length === 0) {
      console.log('Creating test school...');
      await connection.execute(`
        INSERT INTO schools (id, name, address, phone, email) VALUES 
        (1, 'Test School', '123 School Street', '555-0123', 'test@school.edu')
      `);
    }

    // Check if we have a school user
    const [schoolUsers] = await connection.execute(`
      SELECT * FROM users WHERE user_type = 'school' LIMIT 1
    `);
    
    if (schoolUsers.length === 0) {
      console.log('Creating test school user...');
      await connection.execute(`
        INSERT INTO users (id, name, email, password, user_type, phone) VALUES 
        (10, 'Test School Admin', 'school@test.com', '$2b$10$hash', 'school', '555-0123')
      `);
    }

    // Update devices to belong to school
    await connection.execute(`
      UPDATE devices SET school_id = 1 WHERE school_id IS NULL
    `);

    // Update children to belong to school
    await connection.execute(`
      UPDATE children SET school_id = 1 WHERE school_id IS NULL
    `);

    // Add some test students if none exist
    const [students] = await connection.execute(`
      SELECT * FROM children WHERE school_id = 1
    `);

    if (students.length === 0) {
      console.log('Creating test students...');
      
      // First create a parent user
      await connection.execute(`
        INSERT IGNORE INTO users (id, name, email, password, user_type, phone) VALUES 
        (11, 'Test Parent', 'parent@test.com', '$2b$10$hash', 'parent', '555-0124')
      `);

      // Create test students
      await connection.execute(`
        INSERT INTO children (parent_id, first_name, last_name, standard, division, roll_number, school_id, device_id) VALUES 
        (11, 'John', 'Doe', '5th', 'A', 1, 1, 'DEV001'),
        (11, 'Jane', 'Smith', '4th', 'B', 2, 1, 'DEV002'),
        (11, 'Mike', 'Johnson', '6th', 'A', 3, 1, 'DEV003'),
        (11, 'Sarah', 'Wilson', '5th', 'C', 4, 1, 'DEV004')
      `);
    }

    // Add fresh location data
    console.log('Adding fresh location data...');
    
    const locationData = [
      {
        device_id: 'DEV001',
        latitude: 19.076090,
        longitude: 72.877426,
        address: 'Andheri West, Mumbai, Maharashtra, India'
      },
      {
        device_id: 'DEV002',
        latitude: 19.017615,
        longitude: 72.856164,
        address: 'Bandra West, Mumbai, Maharashtra, India'
      },
      {
        device_id: 'DEV003',
        latitude: 19.054489,
        longitude: 72.833336,
        address: 'Juhu, Mumbai, Maharashtra, India'
      },
      {
        device_id: 'DEV004',
        latitude: 19.103498,
        longitude: 72.827847,
        address: 'Malad West, Mumbai, Maharashtra, India'
      }
    ];

    for (const location of locationData) {
      await connection.execute(`
        INSERT INTO device_locations (device_id, latitude, longitude, address, timestamp) 
        VALUES (?, ?, ?, ?, NOW())
      `, [location.device_id, location.latitude, location.longitude, location.address]);
      
      // Update device last_update
      await connection.execute(`
        UPDATE devices SET last_update = NOW() WHERE id = ?
      `, [location.device_id]);
    }

    // Test the live tracking query
    console.log('Testing live tracking query...');
    const [trackingData] = await connection.execute(`
      SELECT 
        c.id as student_id,
        c.first_name,
        c.last_name,
        c.standard,
        c.division,
        c.roll_number,
        d.id as device_id,
        d.type as device_type,
        d.status as device_status,
        d.battery_level,
        dl.latitude,
        dl.longitude,
        dl.address,
        dl.timestamp as last_location_update,
        TIMESTAMPDIFF(MINUTE, dl.timestamp, NOW()) as minutes_ago
      FROM children c
      LEFT JOIN devices d ON c.device_id = d.id
      LEFT JOIN (
        SELECT device_id, latitude, longitude, address, timestamp,
               ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY timestamp DESC) as rn
        FROM device_locations
      ) dl ON d.id = dl.device_id AND dl.rn = 1
      WHERE c.school_id = 1 AND c.status = 'active'
      ORDER BY c.standard, c.division, c.roll_number
    `);

    console.log('Live tracking data:', trackingData);
    console.log('Setup completed successfully!');

  } catch (error) {
    console.error('Error setting up live tracking test:', error);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

setupLiveTrackingTest();