const express = require('express');
const bcrypt = require('bcryptjs');
const { encryptPassword } = require('../utils/crypto');
const router = express.Router();
const db = require('../config/database');
const { authenticateToken } = require('../middleware/auth');

// Get all students for school
router.get('/students', authenticateToken, async (req, res) => {
  try {
    // Use school_id from JWT token, fallback to database lookup if not present
    let schoolId = req.user.school_id;
    if (!schoolId) {
      const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
      schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : null;
    }
    
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    const query = `
      SELECT 
        c.id, c.first_name, c.last_name, c.standard, c.division, c.roll_number,
        c.device_id, d.type as device_type, 
        CASE 
          WHEN c.device_id IS NULL THEN 'no_device'
          WHEN dl.last_location_time IS NULL THEN 'inactive'
          WHEN TIMESTAMPDIFF(MINUTE, dl.last_location_time, NOW()) <= 10 THEN 'active'
          ELSE 'inactive'
        END as device_status,
        d.battery_level, d.last_update,
        CASE WHEN ss.end_date IS NOT NULL THEN 'active' ELSE NULL END as subscription_status,
        ss.end_date as subscription_end_date,
        latest_plan.name as plan_name,
        CASE 
          WHEN ss.end_date IS NULL THEN 'No Plan'
          WHEN ss.end_date < CURDATE() THEN 'Expired'
          WHEN DATEDIFF(ss.end_date, CURDATE()) <= 7 THEN 'Expiring Soon'
          ELSE 'Active'
        END as subscription_display_status,
        COALESCE(DATEDIFF(ss.end_date, CURDATE()), 0) as days_remaining
      FROM children c
      LEFT JOIN devices d ON c.device_id = d.id
      LEFT JOIN (
        SELECT device_id, MAX(timestamp) as last_location_time
        FROM device_locations
        GROUP BY device_id
      ) dl ON c.device_id = dl.device_id
      LEFT JOIN (
        SELECT student_id, MAX(end_date) as end_date
        FROM student_subscriptions
        GROUP BY student_id
      ) ss ON c.id = ss.student_id
      LEFT JOIN (
        SELECT ss1.student_id, sp1.name, ss1.plan_id
        FROM student_subscriptions ss1
        INNER JOIN subscription_plans sp1 ON ss1.plan_id = sp1.id
        INNER JOIN (
          SELECT student_id, MAX(end_date) as max_end_date
          FROM student_subscriptions
          GROUP BY student_id
        ) ss2 ON ss1.student_id = ss2.student_id AND ss1.end_date = ss2.max_end_date
      ) latest_plan ON c.id = latest_plan.student_id
      WHERE c.school_id = ?
      ORDER BY c.standard, c.division, c.roll_number
    `;
    
    const [rows] = await db.execute(query, [schoolId]);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching students:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get student location
router.get('/students/:studentId/location', authenticateToken, async (req, res) => {
  try {
    const { studentId } = req.params;
    
    // Use school_id from JWT token, fallback to database lookup if not present
    let schoolId = req.user.school_id;
    if (!schoolId) {
      const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
      schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : null;
    }
    
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    // First check if student exists and belongs to user's school
    const [studentCheck] = await db.execute(
      'SELECT device_id FROM children WHERE id = ? AND school_id = ?',
      [studentId, schoolId]
    );
    
    if (studentCheck.length === 0) {
      return res.status(404).json({ message: 'Student not found' });
    }
    
    if (!studentCheck[0].device_id) {
      return res.status(404).json({ message: 'No device assigned to student' });
    }
    
    // Get latest location for the device
    const query = `
      SELECT dl.latitude, dl.longitude, dl.address, dl.timestamp,
             c.first_name, c.last_name, 
             COALESCE(d.battery_level, 85) as battery_level
      FROM device_locations dl
      JOIN children c ON dl.device_id = c.device_id
      LEFT JOIN devices d ON dl.device_id = d.id
      WHERE c.id = ? AND c.school_id = ?
      ORDER BY dl.timestamp DESC
      LIMIT 1
    `;
    
    const [rows] = await db.execute(query, [studentId, schoolId]);
    
    if (rows.length === 0) {
      return res.status(404).json({ message: 'No location data found for this student' });
    }
    
    res.json(rows[0]);
  } catch (error) {
    console.error('Error fetching student location:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get all devices for school (authenticated)
router.get('/devices', authenticateToken, async (req, res) => {
  try {
    console.log('DEBUG: req.user =', req.user);
    // Use school_id from JWT token, fallback to database lookup if not present
    let schoolId = req.user.school_id;
    if (!schoolId) {
      const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
      schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : null;
    }
    
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    console.log('DEBUG: final schoolId =', schoolId);
    
    const query = `
      SELECT DISTINCT
        d.id,
        d.device_id as deviceId,
        d.type,
        CASE 
          WHEN dl.last_location_time IS NULL THEN 'inactive'
          WHEN TIMESTAMPDIFF(MINUTE, dl.last_location_time, NOW()) <= 10 THEN 'active'
          ELSE 'inactive'
        END as status,
        d.battery_level as battery,
        CASE 
          WHEN dl.last_location_time IS NULL THEN 'No data'
          ELSE CONCAT(TIMESTAMPDIFF(MINUTE, dl.last_location_time, NOW()), ' mins ago')
        END as lastUpdate,
        d.imei_number,
        d.sim_number,
        d.model,
        d.manufacturer
      FROM devices d 
      LEFT JOIN (
        SELECT device_id, MAX(timestamp) as last_location_time
        FROM device_locations
        GROUP BY device_id
      ) dl ON d.device_id = dl.device_id
      WHERE d.school_id = ?
      GROUP BY d.id, d.device_id
      ORDER BY d.created_at DESC
    `;
    
    const [rows] = await db.execute(query, [schoolId]);
    console.log('DEBUG: returning', rows.length, 'devices for schoolId', schoolId);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching devices:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get all devices for school (with auth)
router.get('/devices-test', authenticateToken, async (req, res) => {
  try {
    // Use school_id from JWT token, fallback to database lookup if not present
    let schoolId = req.user.school_id;
    if (!schoolId) {
      const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
      schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : null;
    }
    
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    const query = `
      SELECT DISTINCT
        d.id,
        d.device_id as deviceId,
        d.type,
        d.status,
        d.battery_level as battery,
        CASE 
          WHEN dl.last_location_time IS NULL THEN 'No data'
          ELSE CONCAT(TIMESTAMPDIFF(MINUTE, dl.last_location_time, NOW()), ' mins ago')
        END as lastUpdate,
        d.imei_number,
        d.sim_number,
        d.model,
        d.manufacturer
      FROM devices d 
      LEFT JOIN (
        SELECT device_id, MAX(timestamp) as last_location_time
        FROM device_locations
        GROUP BY device_id
      ) dl ON d.device_id = dl.device_id
      WHERE d.school_id = ?
      GROUP BY d.id
      ORDER BY d.device_id ASC
    `;
    
    const [rows] = await db.execute(query, [schoolId]);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching devices:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Live Tracking APIs

// Get all students with their current locations for live tracking
router.get('/live-tracking', authenticateToken, async (req, res) => {
  try {
    // Use school_id from JWT token, fallback to database lookup if not present
    let schoolId = req.user.school_id;
    if (!schoolId) {
      const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
      schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : null;
    }
    
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    const query = `
      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 = ? AND c.status = 'active'
      ORDER BY c.standard, c.division, c.roll_number
    `;
    
    const [rows] = await db.execute(query, [schoolId]);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching live tracking data:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get location history for a specific student
router.get('/students/:studentId/location-history', authenticateToken, async (req, res) => {
  try {
    const { studentId } = req.params;
    const { hours = 24 } = req.query; // Default to last 24 hours
    
    // Get user's school_id from users table
    const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
    const schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : req.user.id;
    
    const query = `
      SELECT 
        dl.latitude,
        dl.longitude,
        dl.address,
        dl.timestamp,
        d.battery_level
      FROM device_locations dl
      JOIN devices d ON dl.device_id = d.id
      JOIN children c ON c.device_id = d.id
      WHERE c.id = ? AND c.school_id = ? AND dl.timestamp >= DATE_SUB(NOW(), INTERVAL ? HOUR)
      ORDER BY dl.timestamp DESC
      LIMIT 100
    `;
    
    const [rows] = await db.execute(query, [studentId, schoolId, hours]);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching location history:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Update device location (for testing/simulation)
router.post('/devices/:deviceId/location', authenticateToken, async (req, res) => {
  try {
    const { deviceId } = req.params;
    const { latitude, longitude, address } = req.body;
    
    // Get user's school_id from users table
    const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
    const schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : req.user.id;
    
    // Verify device belongs to user's school
    const [deviceCheck] = await db.execute('SELECT id FROM devices WHERE id = ? AND school_id = ?', [deviceId, schoolId]);
    if (deviceCheck.length === 0) {
      return res.status(403).json({ message: 'Access denied to this device' });
    }
    
    const query = `
      INSERT INTO device_locations (device_id, latitude, longitude, address)
      VALUES (?, ?, ?, ?)
    `;
    
    await db.execute(query, [deviceId, latitude, longitude, address]);
    
    // Update device last_update timestamp
    await db.execute(
      'UPDATE devices SET last_update = NOW() WHERE id = ? AND school_id = ?',
      [deviceId, schoolId]
    );
    
    res.json({ message: 'Location updated successfully' });
  } catch (error) {
    console.error('Error updating location:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Test endpoint for student location without authentication
router.get('/students/:studentId/location-test', async (req, res) => {
  try {
    const { studentId } = req.params;
    
    // First check if student exists and has device
    const [studentCheck] = await db.execute(
      'SELECT device_id FROM children WHERE id = ?',
      [studentId]
    );
    
    if (studentCheck.length === 0) {
      return res.status(404).json({ message: 'Student not found' });
    }
    
    if (!studentCheck[0].device_id) {
      return res.status(404).json({ message: 'No device assigned to student' });
    }
    
    // Get latest location for the device
    const query = `
      SELECT dl.latitude, dl.longitude, dl.address, dl.timestamp,
             c.first_name, c.last_name, 
             COALESCE(d.battery_level, 85) as battery_level
      FROM device_locations dl
      JOIN children c ON dl.device_id = c.device_id
      LEFT JOIN devices d ON dl.device_id = d.id
      WHERE c.id = ?
      ORDER BY dl.timestamp DESC
      LIMIT 1
    `;
    
    const [rows] = await db.execute(query, [studentId]);
    
    if (rows.length === 0) {
      return res.status(404).json({ message: 'No location data found for this student' });
    }
    
    res.json(rows[0]);
  } catch (error) {
    console.error('Error fetching student location (test):', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Test endpoint with authentication
router.get('/students-test', authenticateToken, async (req, res) => {
  try {
    // Use school_id from JWT token, fallback to database lookup if not present
    let schoolId = req.user.school_id;
    if (!schoolId) {
      const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
      schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : null;
    }
    
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    const query = `
      SELECT 
        c.id, c.first_name, c.last_name, c.standard, c.division, c.roll_number, c.device_id,
        d.type as device_type, 
        CASE 
          WHEN c.device_id IS NULL THEN 'no_device'
          WHEN dl.last_location_time IS NULL THEN 'inactive'
          WHEN TIMESTAMPDIFF(MINUTE, dl.last_location_time, NOW()) <= 10 THEN 'active'
          ELSE 'inactive'
        END as device_status,
        d.battery_level,
        CASE WHEN ss.end_date IS NOT NULL THEN 'active' ELSE NULL END as subscription_status,
        ss.end_date as subscription_end_date,
        latest_plan.name as plan_name,
        CASE 
          WHEN ss.end_date IS NULL THEN 'No Plan'
          WHEN ss.end_date < CURDATE() THEN 'Expired'
          WHEN DATEDIFF(ss.end_date, CURDATE()) <= 7 THEN 'Expiring Soon'
          ELSE 'Active'
        END as subscription_display_status,
        COALESCE(DATEDIFF(ss.end_date, CURDATE()), 0) as days_remaining
      FROM children c
      LEFT JOIN devices d ON c.device_id = d.id
      LEFT JOIN (
        SELECT device_id, MAX(timestamp) as last_location_time
        FROM device_locations
        GROUP BY device_id
      ) dl ON c.device_id = dl.device_id
      LEFT JOIN (
        SELECT student_id, MAX(end_date) as end_date
        FROM student_subscriptions
        GROUP BY student_id
      ) ss ON c.id = ss.student_id
      LEFT JOIN (
        SELECT ss1.student_id, sp1.name, ss1.plan_id
        FROM student_subscriptions ss1
        INNER JOIN subscription_plans sp1 ON ss1.plan_id = sp1.id
        INNER JOIN (
          SELECT student_id, MAX(end_date) as max_end_date
          FROM student_subscriptions
          GROUP BY student_id
        ) ss2 ON ss1.student_id = ss2.student_id AND ss1.end_date = ss2.max_end_date
      ) latest_plan ON c.id = latest_plan.student_id
      WHERE c.school_id = ? AND c.status = 'active'
      ORDER BY c.id
    `;
    
    const [rows] = await db.execute(query, [schoolId]);
    console.log(`Found ${rows.length} students for school_id ${schoolId}:`, rows);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching students (test):', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Test endpoint for live tracking without authentication
router.get('/live-tracking-test', async (req, res) => {
  try {
    const schoolId = 1; // Hardcoded for testing - use school_id 1
    
    const query = `
      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 = ? AND c.status = 'active'
      ORDER BY c.standard, c.division, c.roll_number
    `;
    
    const [rows] = await db.execute(query, [schoolId]);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching live tracking data (test):', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Device Activation and Assignment APIs
router.post('/devices/:deviceId/activate', authenticateToken, async (req, res) => {
  try {
    const { deviceId } = req.params;
    const { student_id, activation_date } = req.body;
    // Get user's school_id from users table
    const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
    const schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : req.user.id;
    
    // Update device status
    await db.execute(
      'UPDATE devices SET status = "active", activation_date = ? WHERE id = ? AND school_id = ?',
      [activation_date || new Date(), deviceId, schoolId]
    );
    
    // Create device assignment record
    await db.execute(
      'INSERT INTO device_assignments (device_id, student_id, school_id, assigned_by) VALUES (?, ?, ?, ?)',
      [deviceId, student_id, schoolId, req.user.id]
    );
    
    // Update student record
    await db.execute(
      'UPDATE children SET device_id = ? WHERE id = ? AND school_id = ?',
      [deviceId, student_id, schoolId]
    );
    
    res.json({ message: 'Device activated and assigned successfully' });
  } catch (error) {
    console.error('Error activating device:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

router.post('/devices/:deviceId/deactivate', authenticateToken, async (req, res) => {
  try {
    const { deviceId } = req.params;
    // Get user's school_id from users table
    const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
    const schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : req.user.id;
    
    // Update device status
    await db.execute(
      'UPDATE devices SET status = "inactive", deactivation_date = NOW() WHERE id = ? AND school_id = ?',
      [deviceId, schoolId]
    );
    
    // Update assignment record
    await db.execute(
      'UPDATE device_assignments SET status = "inactive", unassigned_date = NOW() WHERE device_id = ? AND status = "active"',
      [deviceId]
    );
    
    // Remove device from student
    await db.execute(
      'UPDATE children SET device_id = NULL WHERE device_id = ? AND school_id = ?',
      [deviceId, schoolId]
    );
    
    res.json({ message: 'Device deactivated successfully' });
  } catch (error) {
    console.error('Error deactivating device:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Parent Management APIs
router.post('/parents', authenticateToken, async (req, res) => {
  try {
    const { first_name, last_name, email, phone_number, relationship, student_ids } = req.body;
    // Get user's school_id from users table
    const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
    const schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : req.user.id;
    
    // Create parent user account
    const hashedPassword = await bcrypt.hash('temp123', 10);
    const [userResult] = await db.execute(
      'INSERT INTO users (first_name, last_name, email, password_hash, user_type) VALUES (?, ?, ?, ?, "parent")',
      [first_name, last_name, email, hashedPassword]
    );
    
    const parentId = userResult.insertId;
    
    // Create parent profile
    await db.execute(
      'INSERT INTO parent_profiles (user_id, phone_number, relationship_to_child) VALUES (?, ?, ?)',
      [parentId, phone_number, relationship]
    );
    
    // Link students to parent
    for (const studentId of student_ids) {
      await db.execute(
        'UPDATE children SET parent_id = ? WHERE id = ? AND school_id = ?',
        [parentId, studentId, schoolId]
      );
    }
    
    res.json({ message: 'Parent account created successfully', parent_id: parentId });
  } catch (error) {
    console.error('Error creating parent account:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Subscription and Payment APIs
router.get('/subscription/status', authenticateToken, async (req, res) => {
  try {
    // Get user's school_id from users table
    const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
    const schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : req.user.id;
    
    const query = `
      SELECT s.subscription_start_date, s.subscription_end_date, sp.name as plan_name, sp.price_per_device,
             COUNT(d.id) as active_devices,
             DATEDIFF(s.subscription_end_date, CURDATE()) as days_remaining
      FROM schools s
      LEFT JOIN subscription_plans sp ON s.subscription_plan_id = sp.id
      LEFT JOIN devices d ON s.id = d.school_id AND d.status = 'active'
      WHERE s.id = ?
      GROUP BY s.id
    `;
    
    const [rows] = await db.execute(query, [schoolId]);
    res.json(rows[0] || {});
  } catch (error) {
    console.error('Error fetching subscription status:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

router.post('/payments/record', authenticateToken, async (req, res) => {
  try {
    const { amount, payment_method, payment_date, billing_period_start, billing_period_end, receipt_number } = req.body;
    // Get user's school_id from users table
    const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
    const schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : req.user.id;
    
    // Get school's subscription plan
    const [schoolRows] = await db.execute(
      'SELECT subscription_plan_id FROM schools WHERE id = ?',
      [schoolId]
    );
    
    if (schoolRows.length === 0) {
      return res.status(404).json({ message: 'School not found' });
    }
    
    await db.execute(
      `INSERT INTO payments (school_id, subscription_plan_id, amount, payment_method, 
       payment_date, billing_period_start, billing_period_end, receipt_number, 
       payment_status, collected_by) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'completed', ?)`,
      [schoolId, schoolRows[0].subscription_plan_id, amount, payment_method, 
       payment_date, billing_period_start, billing_period_end, receipt_number, req.user.id]
    );
    
    res.json({ message: 'Payment recorded successfully' });
  } catch (error) {
    console.error('Error recording payment:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Support Ticket APIs
router.post('/support/tickets', authenticateToken, async (req, res) => {
  try {
    const { type, priority, subject, description, device_id, student_id } = req.body;
    // Get user's school_id from users table
    const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
    const schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : req.user.id;
    
    // Generate ticket number
    const ticketNumber = 'TKT' + Date.now();
    
    await db.execute(
      `INSERT INTO support_tickets (ticket_number, type, priority, subject, description, 
       school_id, device_id, student_id, created_by) 
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [ticketNumber, type, priority, subject, description, schoolId, device_id, student_id, req.user.id]
    );
    
    res.json({ message: 'Support ticket created successfully', ticket_number: ticketNumber });
  } catch (error) {
    console.error('Error creating support ticket:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Geofencing APIs
router.post('/geofences', authenticateToken, async (req, res) => {
  try {
    const { name, type, center_latitude, center_longitude, radius_meters, student_id } = req.body;
    // Get user's school_id from users table
    const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
    const schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : req.user.id;
    
    await db.execute(
      'INSERT INTO geofences (name, type, center_latitude, center_longitude, radius_meters, school_id, student_id, created_by) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
      [name, type, center_latitude, center_longitude, radius_meters, schoolId, student_id, req.user.id]
    );
    
    res.json({ message: 'Geofence created successfully' });
  } catch (error) {
    console.error('Error creating geofence:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

router.get('/geofences', authenticateToken, async (req, res) => {
  try {
    // Get user's school_id from users table
    const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
    const schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : req.user.id;
    
    const query = `
      SELECT g.*, c.first_name, c.last_name
      FROM geofences g
      LEFT JOIN children c ON g.student_id = c.id
      WHERE g.school_id = ? AND g.status = 'active'
      ORDER BY g.created_at DESC
    `;
    
    const [rows] = await db.execute(query, [schoolId]);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching geofences:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Test endpoint for standards with authentication
router.get('/standards-test', authenticateToken, (req, res) => {
  const standards = [
    { id: 1, name: '1st', display_order: 1, status: 'active' },
    { id: 2, name: '2nd', display_order: 2, status: 'active' },
    { id: 3, name: '3rd', display_order: 3, status: 'active' },
    { id: 4, name: '4th', display_order: 4, status: 'active' },
    { id: 5, name: '5th', display_order: 5, status: 'active' },
    { id: 6, name: '6th', display_order: 6, status: 'active' },
    { id: 7, name: '7th', display_order: 7, status: 'active' },
    { id: 8, name: '8th', display_order: 8, status: 'active' },
    { id: 9, name: '9th', display_order: 9, status: 'active' },
    { id: 10, name: '10th', display_order: 10, status: 'active' },
    { id: 11, name: '11th', display_order: 11, status: 'active' },
    { id: 12, name: '12th', display_order: 12, status: 'active' }
  ];
  
  res.json(standards);
});

// Test endpoint for divisions
router.get('/divisions-test', (req, res) => {
  const divisions = [
    { id: 1, name: 'A', display_order: 1, status: 'active' },
    { id: 2, name: 'B', display_order: 2, status: 'active' },
    { id: 3, name: 'C', display_order: 3, status: 'active' },
    { id: 4, name: 'D', display_order: 4, status: 'active' },
    { id: 5, name: 'E', display_order: 5, status: 'active' }
  ];
  
  res.json(divisions);
});

// Test endpoint for teachers with authentication
router.get('/teachers-test', authenticateToken, (req, res) => {
  const teachers = [
    { id: 1, name: 'John Smith', first_name: 'John', last_name: 'Smith', email: 'john.smith@school.com' },
    { id: 2, name: 'Sarah Johnson', first_name: 'Sarah', last_name: 'Johnson', email: 'sarah.johnson@school.com' },
    { id: 3, name: 'Mike Wilson', first_name: 'Mike', last_name: 'Wilson', email: 'mike.wilson@school.com' },
    { id: 4, name: 'Emily Davis', first_name: 'Emily', last_name: 'Davis', email: 'emily.davis@school.com' },
    { id: 5, name: 'Robert Brown', first_name: 'Robert', last_name: 'Brown', email: 'robert.brown@school.com' }
  ];
  
  res.json(teachers);
});

// Get all standards
router.get('/standards', authenticateToken, async (req, res) => {
  try {
    const [rows] = await db.execute(
      'SELECT * FROM standards WHERE status = "active" ORDER BY display_order'
    );
    res.json(rows);
  } catch (error) {
    console.error('Error fetching standards:', error);
    res.status(500).json({ error: 'Failed to fetch standards' });
  }
});

// Get all divisions
router.get('/divisions', authenticateToken, async (req, res) => {
  try {
    const [rows] = await db.execute(
      'SELECT * FROM divisions WHERE status = "active" ORDER BY display_order'
    );
    res.json(rows);
  } catch (error) {
    console.error('Error fetching divisions:', error);
    res.status(500).json({ error: 'Failed to fetch divisions' });
  }
});

// Get all teachers
router.get('/teachers', authenticateToken, async (req, res) => {
  try {
    const [rows] = await db.execute(
      'SELECT id, CONCAT(first_name, " ", last_name) as name, first_name, last_name, email FROM users WHERE user_type = "teacher" AND status = "active" ORDER BY first_name, last_name'
    );
    res.json(rows);
  } catch (error) {
    console.error('Error fetching teachers:', error);
    res.status(500).json({ error: 'Failed to fetch teachers' });
  }
});

// Add new student
router.post('/students', authenticateToken, async (req, res) => {
  try {
    // Get user's school_id from users table
    const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
    const schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : req.user.id;
    const {
      first_name,
      last_name,
      academic_year,
      standard,
      division,
      roll_number,
      teacher_id,
      parent_mobile_1,
      parent_mobile_2,
      device_id,
      device_package_id
    } = req.body;

    const query = `
      INSERT INTO children (
        school_id, first_name, last_name, academic_year, standard, division,
        roll_number, teacher_id, parent_mobile_1, parent_mobile_2, device_id,
        device_package_id, status, created_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 'active', NOW())
    `;

    const [result] = await db.execute(query, [
      schoolId, first_name, last_name, academic_year, standard, division,
      roll_number, teacher_id, parent_mobile_1, parent_mobile_2, device_id,
      device_package_id
    ]);

    res.json({ 
      message: 'Student added successfully', 
      student_id: result.insertId 
    });
  } catch (error) {
    console.error('Error adding student:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Add new device
router.post('/devices', authenticateToken, async (req, res) => {
  try {
    // Get user's school_id from users table
    const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
    const schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : req.user.id;
    const {
      device_id,
      device_type,
      status
    } = req.body;

    const query = `
      INSERT INTO devices (
        school_id, device_id, type, status
      ) VALUES (?, ?, ?, ?)
    `;

    const [result] = await db.execute(query, [
      schoolId, device_id, device_type, status
    ]);

    res.json({ 
      message: 'Device added successfully', 
      device_id: result.insertId 
    });
  } catch (error) {
    console.error('Error adding device:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Add new device (test endpoint without auth)
router.post('/devices-test', async (req, res) => {
  try {
    const schoolId = 1; // Hardcoded for testing
    const {
      device_id,
      device_type,
      status,
      imei_number,
      sim_number,
      model,
      manufacturer
    } = req.body;

    console.log('Received device data:', { device_id, device_type, status, imei_number, sim_number, model, manufacturer });
    console.log('Will insert with status:', 'active');

    // Validate device_id is not empty
    if (!device_id || device_id.trim() === '') {
      console.log('Device ID validation failed:', device_id);
      return res.status(400).json({ message: 'Device ID cannot be empty' });
    }

    // Check if device already exists
    const [existing] = await db.execute(
      'SELECT id FROM devices WHERE device_id = ?',
      [device_id.trim()]
    );
    
    if (existing.length > 0) {
      return res.status(400).json({ message: 'Device ID already exists' });
    }

    // Generate unique ID for the device
    const uniqueId = `DEV_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;
    
    const query = `
      INSERT INTO devices (id, school_id, device_id, type, status, battery_level, imei_number, sim_number, model, manufacturer, created_at) 
      VALUES (?, ?, ?, ?, ?, 85, ?, ?, ?, ?, NOW())
    `;

    const [result] = await db.execute(query, [
      uniqueId, schoolId, device_id.trim(), device_type || 'GPS Tracker', 'active',
      imei_number || null, sim_number || null, model || null, manufacturer || null
    ]);

    res.json({ 
      message: 'Device added successfully', 
      device_id: result.insertId 
    });
  } catch (error) {
    console.error('Error adding device (test):', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Assign device to student (test endpoint without auth)
router.post('/assign-device-test', async (req, res) => {
  try {
    const { device_id, student_id } = req.body;
    
    // Update student with device assignment
    const updateQuery = `
      UPDATE children SET device_id = ? WHERE id = ?
    `;
    
    await db.execute(updateQuery, [device_id, student_id]);
    
    res.json({ 
      message: 'Device assigned successfully'
    });
  } catch (error) {
    console.error('Error assigning device (test):', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Disconnect device from student (test endpoint without auth)
router.post('/disconnect-device-test', async (req, res) => {
  try {
    const { student_id } = req.body;
    
    // Remove device assignment from student
    const updateQuery = `
      UPDATE children SET device_id = NULL WHERE id = ?
    `;
    
    await db.execute(updateQuery, [student_id]);
    
    res.json({ 
      message: 'Device disconnected successfully'
    });
  } catch (error) {
    console.error('Error disconnecting device (test):', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Add new student (test endpoint without auth)
router.post('/students-test', async (req, res) => {
  try {
    const schoolId = 1; // Hardcoded for testing
    const {
      first_name,
      last_name,
      standard,
      division,
      roll_number,
      device_id,
      parent_mobile_1
    } = req.body;

    // Insert student
    const studentQuery = `
      INSERT INTO children (
        school_id, first_name, last_name, standard, division, roll_number, device_id
      ) VALUES (?, ?, ?, ?, ?, ?, ?)
    `;

    const [studentResult] = await db.execute(studentQuery, [
      schoolId, first_name, last_name, standard, division, roll_number, device_id
    ]);

    // Insert parent details if provided
    if (parent_mobile_1) {
      const parentQuery = `
        INSERT INTO parent_details (
          user_id, student_name, standard, division, roll_number, emergency_contact
        ) VALUES (?, ?, ?, ?, ?, ?)
      `;

      await db.execute(parentQuery, [
        1, // Hardcoded user_id for testing
        `${first_name} ${last_name}`,
        standard,
        division,
        roll_number,
        parent_mobile_1
      ]);
    }

    res.json({ 
      message: 'Student added successfully', 
      student_id: studentResult.insertId 
    });
  } catch (error) {
    console.error('Error adding student (test):', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Subscription Management APIs

// Get subscription plans with authentication
router.get('/subscription-plans-test', authenticateToken, async (req, res) => {
  try {
    const query = `SELECT id, name, description, price, duration_months, features FROM subscription_plans WHERE status = 'active' ORDER BY price ASC`;
    const [rows] = await db.execute(query);
    
    // If no plans exist, create default ones
    if (rows.length === 0) {
      const defaultPlans = [
        { name: 'Basic Plan', description: 'Basic GPS tracking', price: 100, duration_months: 1 },
        { name: 'Standard Plan', description: 'GPS tracking + Alerts', price: 250, duration_months: 3 },
        { name: 'Premium Plan', description: 'Full features + Support', price: 450, duration_months: 6 },
        { name: 'Annual Plan', description: 'Best value - Full year', price: 800, duration_months: 12 }
      ];
      
      for (const plan of defaultPlans) {
        await db.execute(
          'INSERT INTO subscription_plans (name, description, price, duration_months, status) VALUES (?, ?, ?, ?, "active")',
          [plan.name, plan.description, plan.price, plan.duration_months]
        );
      }
      
      // Fetch the newly created plans
      const [newRows] = await db.execute(query);
      const plans = newRows.map(plan => ({
        id: plan.id,
        name: plan.name,
        description: plan.description,
        price: plan.price,
        duration: `${plan.duration_months} Month${plan.duration_months > 1 ? 's' : ''}`,
        duration_months: plan.duration_months
      }));
      
      console.log(`Created and found ${plans.length} subscription plans`);
      return res.json(plans);
    }
    
    const plans = rows.map(plan => ({
      id: plan.id,
      name: plan.name,
      description: plan.description,
      price: plan.price,
      duration: `${plan.duration_months} Month${plan.duration_months > 1 ? 's' : ''}`,
      duration_months: plan.duration_months
    }));
    
    console.log(`Found ${plans.length} subscription plans`);
    res.json(plans);
  } catch (error) {
    console.error('Error fetching subscription plans:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Collect payment
router.post('/collect-payment-test', async (req, res) => {
  try {
    const { 
      student_id, 
      plan_id, 
      amount, 
      base_amount, 
      tax_amount, 
      tax_percentage, 
      payment_method, 
      payment_date 
    } = req.body;
    
    // Validate required fields
    if (!student_id || !plan_id || !amount || !payment_method) {
      return res.status(400).json({ message: 'All fields are required: student_id, plan_id, amount, payment_method' });
    }
    
    // Get plan details
    const [planRows] = await db.execute('SELECT duration_months FROM subscription_plans WHERE id = ?', [plan_id]);
    if (planRows.length === 0) {
      return res.status(400).json({ message: 'Invalid plan selected' });
    }
    
    const durationMonths = planRows[0].duration_months;
    const startDate = payment_date || new Date().toISOString().split('T')[0];
    
    // Generate transaction ID for UPI/payment tracking
    const transactionId = `TXN_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;
    const upiTransactionId = payment_method === 'upi' ? `UPI_${Date.now()}` : null;
    
    // Start transaction
    await db.query('START TRANSACTION');
    
    try {
      // Insert payment record with transaction details
      const paymentQuery = `
        INSERT INTO payments (
          student_id, plan_id, amount, base_amount, tax_amount, tax_percentage,
          payment_method, payment_date, status, transaction_id, upi_transaction_id,
          gateway_response, created_at
        )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, 'completed', ?, ?, ?, NOW())
      `;
      
      const gatewayResponse = {
        payment_method: payment_method,
        transaction_id: transactionId,
        upi_transaction_id: upiTransactionId,
        status: 'success',
        timestamp: new Date().toISOString()
      };
      
      const [paymentResult] = await db.execute(paymentQuery, [
        student_id, 
        plan_id, 
        amount, 
        base_amount || amount, 
        tax_amount || 0, 
        tax_percentage || 0,
        payment_method, 
        startDate, 
        transactionId, 
        upiTransactionId,
        JSON.stringify(gatewayResponse)
      ]);
      
      // Check for latest subscription (active or expired)
      const [latestSubscription] = await db.execute(
        'SELECT end_date FROM student_subscriptions WHERE student_id = ? ORDER BY end_date DESC LIMIT 1',
        [student_id]
      );
      
      let newStartDate, newEndDate;
      
      if (latestSubscription.length > 0) {
        // Start new subscription after the latest subscription ends
        const lastEndDate = new Date(latestSubscription[0].end_date);
        
        // If current subscription is still active, start after it ends
        if (lastEndDate > new Date()) {
          newStartDate = new Date(lastEndDate);
          newStartDate.setDate(newStartDate.getDate() + 1); // Start next day
        } else {
          // If expired, start from today
          newStartDate = new Date();
        }
        
        newEndDate = new Date(newStartDate);
        newEndDate.setMonth(newEndDate.getMonth() + durationMonths);
      } else {
        // First subscription - start from today
        newStartDate = new Date();
        newEndDate = new Date();
        newEndDate.setMonth(newEndDate.getMonth() + durationMonths);
      }
      
      // Always create new subscription entry with payment_id
      await db.execute(
        'INSERT INTO student_subscriptions (student_id, school_id, plan_id, start_date, end_date, status, payment_id) VALUES (?, 1, ?, ?, ?, "active", ?)',
        [student_id, plan_id, newStartDate.toISOString().split('T')[0], newEndDate.toISOString().split('T')[0], paymentResult.insertId]
      );
      
      // Update student status to active if payment successful
      await db.execute(
        'UPDATE children SET status = "active", updated_at = NOW() WHERE id = ?',
        [student_id]
      );
      
      // Commit transaction
      await db.query('COMMIT');
      
      res.json({ 
        message: 'Payment collected and subscription updated successfully',
        payment_id: paymentResult.insertId,
        transaction_id: transactionId,
        upi_transaction_id: upiTransactionId
      });
    } catch (error) {
      // Rollback transaction on error
      await db.query('ROLLBACK');
      throw error;
    }
  } catch (error) {
    console.error('Error collecting payment:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get subscription status with authentication
router.get('/subscription-status-test', authenticateToken, async (req, res) => {
  try {
    // Use school_id from JWT token, fallback to database lookup if not present
    let schoolId = req.user.school_id;
    if (!schoolId) {
      const [userRows] = await db.execute('SELECT school_id FROM users WHERE id = ?', [req.user.id]);
      schoolId = userRows.length > 0 && userRows[0].school_id ? userRows[0].school_id : null;
    }
    
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    const query = `
      SELECT 
        c.id, c.first_name, c.last_name, c.standard, c.division,
        ss.status, ss.end_date, ss.start_date,
        DATEDIFF(ss.end_date, CURDATE()) as days_left,
        sp.name as plan_name, sp.price,
        CASE 
          WHEN ss.end_date IS NULL THEN 'No Subscription'
          WHEN ss.end_date < CURDATE() THEN 'Expired'
          WHEN DATEDIFF(ss.end_date, CURDATE()) <= 7 THEN 'Expiring Soon'
          ELSE 'Active'
        END as subscription_status
      FROM children c
      LEFT JOIN student_subscriptions ss ON c.id = ss.student_id AND ss.status = 'active'
      LEFT JOIN subscription_plans sp ON ss.plan_id = sp.id
      WHERE c.school_id = ?
      ORDER BY 
        CASE 
          WHEN ss.end_date IS NULL THEN 1
          WHEN ss.end_date < CURDATE() THEN 2
          ELSE 3
        END,
        ss.end_date ASC
    `;
    
    const [rows] = await db.execute(query, [schoolId]);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching subscription status:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get payment reports with transaction details
router.get('/payment-reports-test', async (req, res) => {
  try {
    const { start_date, end_date } = req.query;
    
    const query = `
      SELECT 
        p.id as payment_id,
        p.payment_date, 
        p.amount, 
        p.base_amount,
        p.tax_amount,
        p.tax_percentage,
        p.payment_method,
        p.status as payment_status,
        p.transaction_id,
        p.upi_transaction_id,
        p.gateway_response,
        c.first_name, 
        c.last_name,
        c.standard,
        c.division,
        sp.name as plan_name,
        sp.duration_months,
        ss.start_date as subscription_start_date,
        ss.end_date as subscription_end_date
      FROM payments p
      JOIN children c ON p.student_id = c.id
      JOIN subscription_plans sp ON p.plan_id = sp.id
      LEFT JOIN student_subscriptions ss ON p.id = ss.payment_id
      WHERE c.school_id = 1
      ${start_date ? 'AND p.payment_date >= ?' : ''}
      ${end_date ? 'AND p.payment_date <= ?' : ''}
      ORDER BY p.payment_date DESC
      LIMIT 50
    `;
    
    const params = [];
    if (start_date) params.push(start_date);
    if (end_date) params.push(end_date);
    
    const [rows] = await db.execute(query, params);
    
    // Calculate total revenue
    const totalRevenue = rows.reduce((sum, row) => sum + parseFloat(row.amount), 0);
    
    res.json({ 
      payments: rows,
      total_revenue: totalRevenue,
      total_payments: rows.length
    });
  } catch (error) {
    console.error('Error fetching payment reports:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get transaction details for verification
router.get('/transaction-details/:transactionId', async (req, res) => {
  try {
    const { transactionId } = req.params;
    
    const query = `
      SELECT 
        p.*,
        c.first_name,
        c.last_name,
        c.standard,
        c.division,
        sp.name as plan_name,
        sp.price as plan_price
      FROM payments p
      JOIN children c ON p.student_id = c.id
      JOIN subscription_plans sp ON p.plan_id = sp.id
      WHERE p.transaction_id = ?
    `;
    
    const [rows] = await db.execute(query, [transactionId]);
    
    if (rows.length === 0) {
      return res.status(404).json({ message: 'Transaction not found' });
    }
    
    res.json(rows[0]);
  } catch (error) {
    console.error('Error fetching transaction details:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get all UPI transactions for reconciliation
router.get('/upi-transactions', async (req, res) => {
  try {
    const { date_from, date_to } = req.query;
    
    const query = `
      SELECT 
        p.transaction_id,
        p.upi_transaction_id,
        p.payment_date,
        p.amount,
        p.base_amount,
        p.tax_amount,
        p.status,
        p.gateway_response,
        CONCAT(c.first_name, ' ', c.last_name) as student_name,
        c.standard,
        c.division,
        sp.name as plan_name
      FROM payments p
      JOIN children c ON p.student_id = c.id
      JOIN subscription_plans sp ON p.plan_id = sp.id
      WHERE p.payment_method = 'upi'
      ${date_from ? 'AND p.payment_date >= ?' : ''}
      ${date_to ? 'AND p.payment_date <= ?' : ''}
      ORDER BY p.payment_date DESC
    `;
    
    const params = [];
    if (date_from) params.push(date_from);
    if (date_to) params.push(date_to);
    
    const [rows] = await db.execute(query, params);
    
    res.json({
      transactions: rows,
      total_amount: rows.reduce((sum, row) => sum + parseFloat(row.amount), 0),
      total_count: rows.length
    });
  } catch (error) {
    console.error('Error fetching UPI transactions:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get renewal reminders
router.get('/renewal-reminders-test', async (req, res) => {
  try {
    const query = `
      SELECT 
        c.id, c.first_name, c.last_name, c.standard, c.division,
        ss.end_date,
        DATEDIFF(ss.end_date, CURDATE()) as days_left,
        sp.name as plan_name
      FROM children c
      JOIN student_subscriptions ss ON c.id = ss.student_id
      JOIN subscription_plans sp ON ss.plan_id = sp.id
      WHERE c.school_id = 1 
      AND ss.status = 'active'
      AND (DATEDIFF(ss.end_date, CURDATE()) <= 7 OR ss.end_date < CURDATE())
      ORDER BY ss.end_date ASC
    `;
    
    const [rows] = await db.execute(query);
    
    const expiringSoon = rows.filter(row => row.days_left > 0 && row.days_left <= 7);
    const expired = rows.filter(row => row.days_left <= 0);
    
    res.json({ expiring_soon: expiringSoon, expired: expired });
  } catch (error) {
    console.error('Error fetching renewal reminders:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Check if parent exists
router.get('/check-parent', async (req, res) => {
  try {
    const { email } = req.query;
    
    if (!email) {
      return res.status(400).json({ message: 'Email is required' });
    }
    
    const [rows] = await db.execute(
      `SELECT u.*, pd.address FROM users u 
       LEFT JOIN parent_details pd ON u.id = pd.user_id 
       WHERE u.email = ? AND u.user_type = ?`,
      [email, 'parent']
    );
    
    if (rows.length > 0) {
      const parent = rows[0];
      res.json({
        exists: true,
        parent: {
          first_name: parent.first_name,
          last_name: parent.last_name,
          phone: parent.phone,
          address: parent.address
        }
      });
    } else {
      res.json({ exists: false });
    }
  } catch (error) {
    console.error('Error checking parent:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Add student with parent
router.post('/students-with-parent', async (req, res) => {
  try {
    const {
      first_name,
      last_name,
      standard,
      division,
      roll_number,
      device_id,
      parent_data
    } = req.body;
    
    let parentId = null;
    
    // Handle parent creation/linking
    if (parent_data) {
      if (!parent_data.exists) {
        // Create new parent
        const hashedPassword = await bcrypt.hash(parent_data.password, 10);
        
        const [parentResult] = await db.execute(
          `INSERT INTO users (email, password_hash, first_name, last_name, phone, user_type) 
           VALUES (?, ?, ?, ?, ?, 'parent')`,
          [
            parent_data.email || null,
            hashedPassword,
            parent_data.first_name || null,
            parent_data.last_name || null,
            parent_data.phone || null
          ]
        );
        parentId = parentResult.insertId;
        
        // Also insert into parent_details table
        await db.execute(
          `INSERT INTO parent_details (user_id, emergency_contact, address) VALUES (?, ?, ?)`,
          [parentId, parent_data.phone || null, parent_data.address || null]
        );
      } else {
        // Get existing parent ID
        const [parentRows] = await db.execute(
          'SELECT id FROM users WHERE email = ? AND user_type = ?',
          [parent_data.email, 'parent']
        );
        if (parentRows.length > 0) {
          parentId = parentRows[0].id;
        }
      }
    }
    
    // Create student
    const [result] = await db.execute(
      `INSERT INTO children (first_name, last_name, standard, division, roll_number, device_id, parent_id, school_id) 
       VALUES (?, ?, ?, ?, ?, ?, ?, 1)`,
      [
        first_name,
        last_name,
        standard,
        division,
        roll_number || null,
        device_id || null,
        parentId || null
      ]
    );
    
    res.json({
      message: 'Student and parent added successfully',
      studentId: result.insertId,
      parentId: parentId
    });
    
  } catch (error) {
    console.error('Error adding student with parent:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Parent Management APIs

// Get all parent accounts
router.get('/parent-accounts-test', async (req, res) => {
  try {
    const query = `
      SELECT 
        u.id, u.first_name, u.last_name, u.email, u.phone, u.status,
        COUNT(c.id) as children_count
      FROM users u
      LEFT JOIN children c ON u.id = c.parent_id
      WHERE u.user_type = 'parent'
      GROUP BY u.id
      ORDER BY u.first_name, u.last_name
    `;
    
    const [rows] = await db.execute(query);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching parent accounts:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Create parent account
router.post('/create-parent-test', async (req, res) => {
  try {
    const { first_name, last_name, email, phone, address, children_ids } = req.body;
    
    // Check if parent already exists
    const [existing] = await db.execute('SELECT id FROM users WHERE email = ?', [email]);
    if (existing.length > 0) {
      return res.status(400).json({ message: 'Parent with this email already exists' });
    }
    
    // Generate temporary password
    const tempPassword = Math.random().toString(36).slice(-8);
    const hashedPassword = await bcrypt.hash(tempPassword, 10);
    
    // Create parent user
    const [result] = await db.execute(
      'INSERT INTO users (first_name, last_name, email, phone, password_hash, user_type, status) VALUES (?, ?, ?, ?, ?, "parent", "active")',
      [first_name, last_name, email, phone, hashedPassword]
    );
    
    const parentId = result.insertId;
    
    // Link children to parent
    if (children_ids && children_ids.length > 0) {
      for (const childId of children_ids) {
        await db.execute('UPDATE children SET parent_id = ? WHERE id = ?', [parentId, childId]);
      }
    }
    
    res.json({ 
      message: 'Parent account created successfully',
      parent_id: parentId,
      temporary_password: tempPassword
    });
  } catch (error) {
    console.error('Error creating parent account:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Reset parent password
router.post('/reset-parent-password-test', async (req, res) => {
  try {
    const { parent_id, new_password } = req.body;
    
    // Generate new password if not provided
    const password = new_password || Math.random().toString(36).slice(-8);
    const hashedPassword = await bcrypt.hash(password, 10);
    
    await db.execute(
      'UPDATE users SET password_hash = ? WHERE id = ? AND user_type = "parent"',
      [hashedPassword, parent_id]
    );
    
    res.json({ 
      message: 'Password reset successfully',
      new_password: password
    });
  } catch (error) {
    console.error('Error resetting password:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Send notification to parents
router.post('/send-notification-test', async (req, res) => {
  try {
    const { title, message, type, selectedParents } = req.body;
    
    // Insert notification for each selected parent
    for (const parentId of selectedParents) {
      await db.execute(
        'INSERT INTO notifications (user_id, title, message, type, status) VALUES (?, ?, ?, ?, "unread")',
        [parentId, title, message, type]
      );
    }
    
    res.json({ 
      message: 'Notifications sent successfully',
      sent_to: selectedParents.length
    });
  } catch (error) {
    console.error('Error sending notifications:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get parent details with children
router.get('/parent-details/:parentId', async (req, res) => {
  try {
    const { parentId } = req.params;
    
    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 [childrenRows] = await db.execute(
      'SELECT id, first_name, last_name, standard, division FROM children WHERE parent_id = ?',
      [parentId]
    );
    
    res.json({
      parent: parentRows[0],
      children: childrenRows
    });
  } catch (error) {
    console.error('Error fetching parent details:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

module.exports = router;