const express = require('express');
const db = require('../config/database');
const jwt = require('jsonwebtoken');
const router = express.Router();

// Middleware to verify JWT token
const verifyToken = (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  console.log('Token received:', token ? 'Present' : 'Missing');
  
  if (!token) {
    console.log('No token provided in request');
    return res.status(401).json({ message: 'No token provided' });
  }
  
  try {
    const decoded = jwt.verify(token, process.env.JWT_SECRET);
    console.log('Token decoded successfully:', { id: decoded.id, email: decoded.email, role: decoded.role });
    req.user = decoded;
    next();
  } catch (error) {
    console.log('Token verification failed:', error.message);
    return res.status(401).json({ message: 'Invalid token: ' + error.message });
  }
};

// Debug endpoint to check parent-child relationship
router.get('/debug-parent', verifyToken, async (req, res) => {
  try {
    console.log('Debug: Parent ID from token:', req.user.id);
    
    // Check parent user
    const [parentRows] = await db.execute('SELECT * FROM users WHERE id = ?', [req.user.id]);
    console.log('Debug: Parent user:', parentRows[0]);
    
    // Check all children with this parent_id
    const [childRows] = await db.execute('SELECT * FROM children WHERE parent_id = ?', [req.user.id]);
    console.log('Debug: Children with parent_id:', childRows);
    
    // Check all children in the school
    if (parentRows[0]?.school_id) {
      const [allChildren] = await db.execute('SELECT id, first_name, last_name, parent_id FROM children WHERE school_id = ?', [parentRows[0].school_id]);
      console.log('Debug: All children in school:', allChildren);
    }
    
    res.json({ parent: parentRows[0], children: childRows });
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Comprehensive debug endpoint for parent-child issues
router.get('/debug-full', verifyToken, async (req, res) => {
  try {
    const parentId = req.user.id;
    console.log('\n=== FULL DEBUG FOR PARENT ID:', parentId, '===');
    
    // 1. Check parent user details
    const [parentUser] = await db.execute('SELECT * FROM users WHERE id = ?', [parentId]);
    console.log('1. Parent user:', parentUser[0]);
    
    // 2. Check children table structure
    const [tableStructure] = await db.execute('DESCRIBE children');
    console.log('2. Children table structure:', tableStructure.map(col => `${col.Field}: ${col.Type}`));
    
    // 3. Check all children with parent_id
    const [childrenWithParent] = await db.execute('SELECT id, first_name, last_name, parent_id, school_id, device_id FROM children WHERE parent_id = ?', [parentId]);
    console.log('3. Children with parent_id', parentId, ':', childrenWithParent);
    
    // 4. Check all children in same school
    if (parentUser[0]?.school_id) {
      const [schoolChildren] = await db.execute('SELECT id, first_name, last_name, parent_id, school_id FROM children WHERE school_id = ?', [parentUser[0].school_id]);
      console.log('4. All children in school', parentUser[0].school_id, ':', schoolChildren);
    }
    
    // 5. Check parent_details table
    try {
      const [parentDetails] = await db.execute('SELECT * FROM parent_details WHERE user_id = ?', [parentId]);
      console.log('5. Parent details:', parentDetails);
    } catch (e) {
      console.log('5. Parent details table error:', e.message);
    }
    
    // 6. Test the exact query used in /children endpoint
    const [testQuery] = await db.execute(`
      SELECT c.*, s.name as school_name, d.device_id as device_device_id, d.type as device_type, 
             d.status as device_status, d.battery_level, d.last_update,
             latest_sub.max_end_date as subscription_end_date
      FROM children c
      LEFT JOIN schools s ON c.school_id = s.id
      LEFT JOIN devices d ON c.device_id = d.device_id
      LEFT JOIN (
        SELECT student_id, MAX(end_date) as max_end_date
        FROM student_subscriptions
        GROUP BY student_id
      ) latest_sub ON c.id = latest_sub.student_id
      WHERE c.parent_id = ?
    `, [parentId]);
    console.log('6. Test children query result:', testQuery);
    
    res.json({
      parent_id: parentId,
      parent_user: parentUser[0],
      children_with_parent: childrenWithParent,
      school_children: parentUser[0]?.school_id ? await db.execute('SELECT id, first_name, last_name, parent_id FROM children WHERE school_id = ?', [parentUser[0].school_id]).then(r => r[0]) : [],
      test_query_result: testQuery,
      debug_complete: true
    });
  } catch (error) {
    console.error('Debug error:', error);
    res.status(500).json({ message: 'Debug error: ' + error.message });
  }
});

// Get children for logged-in parent
router.get('/children', verifyToken, async (req, res) => {
  try {
    console.log('\n=== GETTING CHILDREN FOR PARENT ===');
    console.log('Token verification passed');
    console.log('Parent ID from token:', req.user.id);
    console.log('Parent email from token:', req.user.email);
    console.log('User role from token:', req.user.role);
    
    // First check if parent exists
    const [parentCheck] = await db.execute('SELECT id, email, school_id, user_type FROM users WHERE id = ?', [req.user.id]);
    console.log('Parent check result:', parentCheck[0]);
    
    if (parentCheck.length === 0) {
      console.log('ERROR: Parent not found in database');
      return res.status(404).json({ message: 'Parent not found' });
    }
    
    // Check direct children relationship
    const [directChildren] = await db.execute('SELECT id, first_name, last_name, parent_id, school_id, device_id FROM children WHERE parent_id = ?', [req.user.id]);
    console.log('Direct children found:', directChildren.length, directChildren);
    
    const [rows] = await db.execute(`
      SELECT c.*, s.name as school_name, d.device_id as device_device_id, d.type as device_type, 
             d.status as device_status, dlb.battery_level, dlb.timestamp as last_update,
             latest_sub.max_end_date as subscription_end_date
      FROM children c
      LEFT JOIN schools s ON c.school_id = s.id
      LEFT JOIN devices d ON c.device_id = d.device_id
      LEFT JOIN (
        SELECT device_id, battery_level, timestamp
        FROM (
          SELECT 
            device_id,
            battery_level,
            timestamp,
            ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY timestamp DESC) as rn
          FROM device_locations
        ) x
        WHERE x.rn = 1
      ) dlb ON dlb.device_id = c.device_id
      LEFT JOIN (
        SELECT student_id, MAX(end_date) as max_end_date
        FROM student_subscriptions
        GROUP BY student_id
      ) latest_sub ON c.id = latest_sub.student_id
      WHERE c.parent_id = ? AND c.parent_id > 0
    `, [req.user.id]);
    
    console.log('Query result - Found children:', rows.length);
    console.log('Raw query results:', rows);
    
    const children = rows.map(child => ({
      id: child.id,
      name: `${child.first_name} ${child.last_name}`,
      grade: `${child.standard} Grade`,
      standard: child.standard,
      division: child.division,
      rollNumber: child.roll_number?.toString(),
      school: child.school_name,
      device: {
        id: child.device_id,
        type: child.device_type || 'GPS Tracker',
        status: child.device_online_status || 'Offline',
        battery: child.battery_level ? `${child.battery_level}%` : '85%',
        lastUpdate: child.last_update ? new Date(child.last_update).toLocaleString() : '2 mins ago',
        subscriptionDays: child.subscription_end_date ? 
          (() => {
            const endDate = new Date(child.subscription_end_date);
            const today = new Date();
            const daysLeft = Math.ceil((endDate - today) / (1000 * 60 * 60 * 24));
            if (daysLeft > 0) {
              return `${daysLeft} days left`;
            } else if (daysLeft === 0) {
              return 'Expires today';
            } else {
              return 'Expired';
            }
          })() : 
          'No Plan'
      }
    }));
    
    console.log('Formatted children response:', children);
    console.log('=== END CHILDREN REQUEST ===\n');
    
    res.json(children);
  } catch (error) {
    console.error('Error in /children endpoint:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Fix parent-child relationship (debug endpoint)
router.post('/fix-parent-child', verifyToken, async (req, res) => {
  try {
    const { student_id } = req.body;
    
    console.log('Fixing parent-child relationship for parent:', req.user.id, 'student:', student_id);
    
    const [result] = await db.execute(
      'UPDATE children SET parent_id = ? WHERE id = ?',
      [req.user.id, student_id]
    );
    
    console.log('Update result:', result);
    
    res.json({ 
      message: 'Parent-child relationship updated',
      affected_rows: result.affectedRows
    });
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Manual assignment endpoint for testing
router.post('/manual-assign', verifyToken, async (req, res) => {
  try {
    const { student_ids } = req.body;
    const parentId = req.user.id;
    
    console.log('Manual assignment - Parent:', parentId, 'Students:', student_ids);
    
    let assigned = 0;
    for (const studentId of student_ids) {
      const [result] = await db.execute(
        'UPDATE children SET parent_id = ? WHERE id = ?',
        [parentId, studentId]
      );
      assigned += result.affectedRows;
      console.log(`Student ${studentId}: ${result.affectedRows} rows updated`);
    }
    
    res.json({ 
      message: 'Manual assignment completed',
      total_assigned: assigned
    });
  } catch (error) {
    console.error('Manual assignment error:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Update child profile
router.put('/children/:childId', verifyToken, async (req, res) => {
  try {
    const { childId } = req.params;
    const { standard, division, rollNumber } = req.body;
    
    // Verify child belongs to logged-in parent
    const [childCheck] = await db.execute(
      'SELECT id FROM children WHERE id = ? AND parent_id = ?',
      [childId, req.user.id]
    );
    
    if (childCheck.length === 0) {
      return res.status(403).json({ message: 'Child not found or access denied' });
    }
    
    // Update child profile
    await db.execute(`
      UPDATE children 
      SET standard = ?, division = ?, roll_number = ?, updated_at = NOW()
      WHERE id = ? AND parent_id = ?
    `, [standard, division, parseInt(rollNumber), childId, req.user.id]);
    
    res.json({ message: 'Profile updated successfully' });
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Test parent login and get sample data
router.get('/test-login-data', verifyToken, async (req, res) => {
  try {
    const parentId = req.user.id;
    
    // Get all relevant data for debugging
    const [parentData] = await db.execute('SELECT * FROM users WHERE id = ?', [parentId]);
    const [allParents] = await db.execute('SELECT id, email, first_name, last_name, school_id FROM users WHERE user_type = "parent"');
    const [allChildren] = await db.execute('SELECT id, first_name, last_name, parent_id, school_id FROM children');
    const [schoolData] = await db.execute('SELECT * FROM schools');
    
    res.json({
      current_parent: parentData[0],
      all_parents: allParents,
      all_children: allChildren,
      schools: schoolData
    });
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// New parent children API - with authentication
router.get('/parent-children/:parentId', verifyToken, async (req, res) => {
  try {
    const { parentId } = req.params;
    console.log('Getting children for parent ID:', parentId);
    
    // Verify parent exists and matches authenticated user
    const [parentCheck] = await db.execute('SELECT id, first_name, last_name, email FROM users WHERE id = ? AND user_type = "parent"', [parentId]);
    if (parentCheck.length === 0) {
      return res.status(404).json({ message: 'Parent not found' });
    }
    
    // Ensure authenticated user can only access their own children
    if (req.user.id != parentId) {
      return res.status(403).json({ message: 'Access denied - can only view your own children' });
    }
    
    const [rows] = await db.execute(`
      SELECT c.*, s.name as school_name, d.device_id as device_device_id, d.type as device_type, 
             d.status as device_status, dlb.battery_level, dlb.timestamp as last_update,
             CASE WHEN ds.last_seen > DATE_SUB(NOW(), INTERVAL 15 MINUTE) THEN 'Online' ELSE 'Offline' END as device_online_status,
             latest_sub.max_end_date as subscription_end_date,
             CASE WHEN pl.id IS NOT NULL THEN 'on' ELSE 'off' END as position_lock_status
      FROM children c
      LEFT JOIN schools s ON c.school_id = s.id
      LEFT JOIN devices d ON c.device_id = d.device_id
      LEFT JOIN (
        SELECT device_id, MAX(last_seen) as last_seen
        FROM device_status
        GROUP BY device_id
      ) ds ON ds.device_id = c.device_id
      LEFT JOIN (
        SELECT device_id, battery_level, timestamp
        FROM (
          SELECT 
            device_id,
            battery_level,
            timestamp,
            ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY timestamp DESC) as rn
          FROM device_locations
        ) x
        WHERE x.rn = 1
      ) dlb ON dlb.device_id = c.device_id
      LEFT JOIN (
        SELECT student_id, MAX(end_date) as max_end_date
        FROM student_subscriptions
        GROUP BY student_id
      ) latest_sub ON c.id = latest_sub.student_id
      LEFT JOIN position_lock pl ON pl.device_id = c.device_id AND pl.active = TRUE
      WHERE c.parent_id = ? AND c.parent_id > 0
    `, [parentId]);
    
    console.log('Found children:', rows.length);
    
    const children = rows.map(child => ({
      id: child.id,
      name: `${child.first_name} ${child.last_name}`,
      grade: `${child.standard} Grade`,
      standard: child.standard,
      division: child.division,
      rollNumber: child.roll_number?.toString(),
      school: child.school_name,
      device: {
        id: child.device_id,
        type: child.device_type || 'GPS Tracker',
        status: child.device_online_status || 'Offline',
        battery: child.battery_level ? `${child.battery_level}%` : '85%',
        lastUpdate: child.last_update ? new Date(child.last_update).toLocaleString() : '2 mins ago',
        subscriptionDays: child.subscription_end_date ? 
          (() => {
            const endDate = new Date(child.subscription_end_date);
            const today = new Date();
            const daysLeft = Math.ceil((endDate - today) / (1000 * 60 * 60 * 24));
            if (daysLeft > 0) {
              return `${daysLeft} days left`;
            } else if (daysLeft === 0) {
              return 'Expires today';
            } else {
              return 'Expired';
            }
          })() : 
          'No Plan'
      }
    }));
    
    res.json({
      parent: parentCheck[0],
      children: children,
      count: children.length
    });
  } catch (error) {
    console.error('Parent children API error:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Get FCM tokens for authenticated user
router.get('/fcm-tokens', verifyToken, async (req, res) => {
  try {
    const user_id = req.user.id;
    
    const [tokens] = await db.execute(`
      SELECT 
        id,
        fcm_token,
        device_id,
        platform,
        app_version,
        created_at,
        updated_at
      FROM fcm_tokens 
      WHERE user_id = ? 
      ORDER BY updated_at DESC
    `, [user_id]);
    
    if (tokens.length === 0) {
      return res.json({
        success: true,
        message: 'No FCM tokens found for this user',
        tokens: [],
        total_devices: 0
      });
    }
    
    // Format the response
    const formattedTokens = tokens.map(token => ({
      id: token.id,
      fcm_token: token.fcm_token,
      device_id: token.device_id,
      platform: token.platform,
      app_version: token.app_version,
      is_active: true, // Assuming all are active since no is_active column
      created_at: token.created_at,
      updated_at: token.updated_at,
      token_preview: token.fcm_token.substring(0, 30) + '...'
    }));
    
    res.json({
      success: true,
      message: 'FCM tokens retrieved successfully',
      tokens: formattedTokens,
      total_devices: formattedTokens.length
    });
  } catch (error) {
    console.error('Error retrieving FCM tokens:', error);
    res.status(500).json({
      success: false,
      message: 'Failed to retrieve FCM tokens',
      error: error.message
    });
  }
});

// Position Lock APIs

// Activate position lock
router.post('/position-lock', verifyToken, async (req, res) => {
  try {
    const { device_id, distance_meters } = req.body;
    
    if (!device_id || !distance_meters) {
      return res.status(400).json({ message: 'device_id and distance_meters are required' });
    }
    
    // Get current location for device
    const [locations] = await db.execute(
      'SELECT latitude, longitude FROM device_locations WHERE device_id = ? ORDER BY timestamp DESC LIMIT 1',
      [device_id]
    );
    
    if (locations.length === 0) {
      return res.status(404).json({ message: 'No location data found for device' });
    }
    
    const { latitude, longitude } = locations[0];
    
    // Insert or update position lock
    await db.execute(
      'INSERT INTO position_lock (device_id, latitude, longitude, distance_meters, user_id) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE latitude = VALUES(latitude), longitude = VALUES(longitude), distance_meters = VALUES(distance_meters), updated_at = CURRENT_TIMESTAMP',
      [device_id, latitude, longitude, distance_meters, req.user.id]
    );
    
    res.json({ message: 'Position lock activated successfully' });
  } catch (error) {
    console.error('Position lock activation error:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Cancel position lock
router.delete('/position-lock/:deviceId', verifyToken, async (req, res) => {
  try {
    const { deviceId } = req.params;
    
    const [result] = await db.execute(
      'DELETE FROM position_lock WHERE device_id = ? AND user_id = ?',
      [deviceId, req.user.id]
    );
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Position lock not found for this device' });
    }
    
    res.json({ message: 'Position lock cancelled successfully' });
  } catch (error) {
    console.error('Position lock cancel error:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Get position lock status for user's devices
router.get('/position-lock', async (req, res) => {
  try {
    const [locks] = await db.execute(
      'SELECT device_id, latitude, longitude, distance_meters, created_at FROM position_lock WHERE user_id = ? AND active = TRUE',
      [req.user.id]
    );
    
    res.json({ locks: locks });
  } catch (error) {
    console.error('Get position locks error:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Register FCM token for push notifications
router.post('/register-fcm-token', verifyToken, async (req, res) => {
  try {
    const { fcm_token, device_id, platform, app_version } = req.body;
    const user_id = req.user.id; // Use authenticated user ID
    
    if (!fcm_token) {
      return res.status(400).json({ message: 'fcm_token is required' });
    }
    
    const query = `
      INSERT INTO fcm_tokens (user_id, device_id, fcm_token, platform, app_version)
      VALUES (?, ?, ?, ?, ?)
      ON DUPLICATE KEY UPDATE
        fcm_token = VALUES(fcm_token),
        platform = VALUES(platform),
        app_version = VALUES(app_version),
        updated_at = CURRENT_TIMESTAMP
    `;
    
    await db.execute(query, [user_id, device_id, fcm_token, platform, app_version]);
    
    res.json({ message: 'FCM token registered successfully' });
  } catch (error) {
    console.error('Error registering FCM token:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Create test token for parent (debugging)
router.get('/create-test-token/:parentId', async (req, res) => {
  try {
    const { parentId } = req.params;
    const jwt = require('jsonwebtoken');
    
    // Get parent data
    const [parentRows] = await db.execute('SELECT * FROM users WHERE id = ? AND user_type = "parent"', [parentId]);
    if (parentRows.length === 0) {
      return res.status(404).json({ message: 'Parent not found' });
    }
    
    const user = parentRows[0];
    
    // Create token
    const token = jwt.sign(
      { id: user.id, email: user.email, role: user.user_type, school_id: user.school_id },
      process.env.JWT_SECRET,
      { expiresIn: '24h' }
    );
    
    res.json({
      token,
      user: {
        id: user.id,
        email: user.email,
        name: `${user.first_name} ${user.last_name}`,
        role: user.user_type,
        school_id: user.school_id
      },
      message: 'Test token created - copy this token to localStorage as authToken'
    });
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Test children endpoint without auth (for debugging)
router.get('/test-children/:parentId', async (req, res) => {
  try {
    const { parentId } = req.params;
    console.log('Testing children for parent ID:', parentId);
    
    const [rows] = await db.execute(`
      SELECT c.*, s.name as school_name, d.device_id as device_device_id, d.type as device_type, 
             d.status as device_status, dlb.battery_level, dlb.timestamp as last_update,
             latest_sub.max_end_date as subscription_end_date
      FROM children c
      LEFT JOIN schools s ON c.school_id = s.id
      LEFT JOIN devices d ON c.device_id = d.device_id
      LEFT JOIN (
        SELECT device_id, battery_level, timestamp
        FROM (
          SELECT 
            device_id,
            battery_level,
            timestamp,
            ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY timestamp DESC) as rn
          FROM device_locations
        ) x
        WHERE x.rn = 1
      ) dlb ON dlb.device_id = c.device_id
      LEFT JOIN (
        SELECT student_id, MAX(end_date) as max_end_date
        FROM student_subscriptions
        GROUP BY student_id
      ) latest_sub ON c.id = latest_sub.student_id
      WHERE c.parent_id = ? AND c.parent_id > 0
    `, [parentId]);
    
    console.log('Found children:', rows.length);
    
    const children = rows.map(child => ({
      id: child.id,
      name: `${child.first_name} ${child.last_name}`,
      grade: `${child.standard} Grade`,
      standard: child.standard,
      division: child.division,
      rollNumber: child.roll_number?.toString(),
      school: child.school_name,
      device: {
        id: child.device_id,
        type: child.device_type || 'GPS Tracker',
        status: child.device_online_status || 'Offline',
        battery: child.battery_level ? `${child.battery_level}%` : '85%',
        lastUpdate: child.last_update ? new Date(child.last_update).toLocaleString() : '2 mins ago',
        subscriptionDays: child.subscription_end_date ? 
          (() => {
            const endDate = new Date(child.subscription_end_date);
            const today = new Date();
            const daysLeft = Math.ceil((endDate - today) / (1000 * 60 * 60 * 24));
            if (daysLeft > 0) {
              return `${daysLeft} days left`;
            } else if (daysLeft === 0) {
              return 'Expires today';
            } else {
              return 'Expired';
            }
          })() : 
          'No Plan'
      }
    }));
    
    res.json(children);
  } catch (error) {
    console.error('Test children error:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Debug endpoint without authentication for testing
router.get('/debug-all-data', async (req, res) => {
  try {
    console.log('\n=== DEBUGGING ALL PARENT-CHILD DATA ===');
    
    // 1. Check all parent users
    const [parents] = await db.execute('SELECT id, first_name, last_name, email, user_type, school_id FROM users WHERE user_type = "parent"');
    console.log('1. PARENT USERS:', parents.length);
    parents.forEach(p => console.log(`   - ID: ${p.id}, Name: ${p.first_name} ${p.last_name}, Email: ${p.email}, School: ${p.school_id}`));
    
    // 2. Check all children
    const [children] = await db.execute('SELECT id, first_name, last_name, parent_id, school_id, device_id FROM children');
    console.log('\n2. ALL CHILDREN:', children.length);
    children.forEach(c => console.log(`   - ID: ${c.id}, Name: ${c.first_name} ${c.last_name}, Parent ID: ${c.parent_id}, School: ${c.school_id}, Device: ${c.device_id}`));
    
    // 3. Check children with parent_id assigned
    const [childrenWithParent] = await db.execute('SELECT id, first_name, last_name, parent_id, school_id FROM children WHERE parent_id IS NOT NULL');
    console.log('\n3. CHILDREN WITH PARENT_ID:', childrenWithParent.length);
    childrenWithParent.forEach(c => console.log(`   - Child ID: ${c.id}, Name: ${c.first_name} ${c.last_name}, Parent ID: ${c.parent_id}`));
    
    // 4. Check parent-child relationships
    const [relationships] = await db.execute(`
      SELECT 
        u.id as parent_id,
        u.first_name as parent_first_name,
        u.last_name as parent_last_name,
        u.email as parent_email,
        c.id as child_id,
        c.first_name as child_first_name,
        c.last_name as child_last_name,
        c.school_id
      FROM users u
      LEFT JOIN children c ON c.parent_id = u.id
      WHERE u.user_type = 'parent'
      ORDER BY u.id
    `);
    console.log('\n4. PARENT-CHILD RELATIONSHIPS:', relationships.length);
    relationships.forEach(r => console.log(`   - Parent: ${r.parent_first_name} ${r.parent_last_name} (ID: ${r.parent_id}) -> Child: ${r.child_first_name || 'NONE'} ${r.child_last_name || ''} (ID: ${r.child_id || 'NULL'})`));
    
    // 5. Test the exact query used in parent dashboard for each parent
    console.log('\n5. TESTING PARENT DASHBOARD QUERIES:');
    for (const parent of parents) {
      const [dashboardResult] = await db.execute(`
        SELECT c.*, s.name as school_name, d.device_id as device_device_id, d.type as device_type, 
               d.status as device_status, d.battery_level, d.last_update,
               latest_sub.max_end_date as subscription_end_date
        FROM children c
        LEFT JOIN schools s ON c.school_id = s.id
        LEFT JOIN devices d ON c.device_id = d.device_id
        LEFT JOIN (
          SELECT student_id, MAX(end_date) as max_end_date
          FROM student_subscriptions
          GROUP BY student_id
        ) latest_sub ON c.id = latest_sub.student_id
        WHERE c.parent_id = ?
      `, [parent.id]);
      
      console.log(`   - Parent ${parent.first_name} ${parent.last_name} (ID: ${parent.id}): ${dashboardResult.length} children found`);
      dashboardResult.forEach(child => console.log(`     * ${child.first_name} ${child.last_name} (Device: ${child.device_id || 'None'})`))
    }
    
    // 6. Check children without parent_id
    const [orphanChildren] = await db.execute('SELECT id, first_name, last_name, school_id FROM children WHERE parent_id IS NULL');
    console.log('\n6. CHILDREN WITHOUT PARENT_ID:', orphanChildren.length);
    orphanChildren.forEach(c => console.log(`   - ID: ${c.id}, Name: ${c.first_name} ${c.last_name}, School: ${c.school_id}`));
    
    console.log('\n=== END DEBUG ===\n');
    
    res.json({
      parents: parents,
      children: children,
      children_with_parent: childrenWithParent,
      relationships: relationships,
      orphan_children: orphanChildren,
      debug_complete: true
    });
  } catch (error) {
    console.error('Debug error:', error);
    res.status(500).json({ message: 'Debug error: ' + error.message });
  }
});

// Fix data inconsistencies
router.post('/fix-data', async (req, res) => {
  try {
    console.log('Fixing data inconsistencies...');
    
    // Fix parent_id = 0 to NULL
    const [result1] = await db.execute('UPDATE children SET parent_id = NULL WHERE parent_id = 0');
    console.log('Fixed parent_id = 0:', result1.affectedRows, 'rows');
    
    // Fix school_id for parent ID 4
    const [result2] = await db.execute('UPDATE users SET school_id = 1 WHERE id = 4 AND user_type = "parent"');
    console.log('Fixed parent school_id:', result2.affectedRows, 'rows');
    
    res.json({
      message: 'Data fixed successfully',
      parent_id_fixes: result1.affectedRows,
      school_id_fixes: result2.affectedRows
    });
  } catch (error) {
    console.error('Fix data error:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Quick fix endpoint to assign a child to a parent (for testing)
router.post('/quick-assign', async (req, res) => {
  try {
    const { parent_id, child_id } = req.body;
    
    if (!parent_id || !child_id) {
      return res.status(400).json({ message: 'parent_id and child_id are required' });
    }
    
    console.log(`Quick assign: Child ${child_id} to Parent ${parent_id}`);
    
    // Check if parent exists
    const [parentCheck] = await db.execute('SELECT id, first_name, last_name FROM users WHERE id = ? AND user_type = "parent"', [parent_id]);
    if (parentCheck.length === 0) {
      return res.status(404).json({ message: 'Parent not found' });
    }
    
    // Check if child exists
    const [childCheck] = await db.execute('SELECT id, first_name, last_name FROM children WHERE id = ?', [child_id]);
    if (childCheck.length === 0) {
      return res.status(404).json({ message: 'Child not found' });
    }
    
    // Update child's parent_id
    const [result] = await db.execute('UPDATE children SET parent_id = ? WHERE id = ?', [parent_id, child_id]);
    
    console.log('Assignment result:', result.affectedRows, 'rows updated');
    
    // Verify the assignment
    const [verification] = await db.execute('SELECT parent_id FROM children WHERE id = ?', [child_id]);
    
    res.json({
      message: 'Child assigned to parent successfully',
      parent: parentCheck[0],
      child: childCheck[0],
      rows_affected: result.affectedRows,
      verification: verification[0]
    });
  } catch (error) {
    console.error('Quick assign error:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

module.exports = router;