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');

// Helper function to get school_id from authenticated user
const getSchoolId = async (req) => {
  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;
  }
  return schoolId;
};

// Get all students for school
router.get('/students', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    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,
        dlb.battery_level, dlb.timestamp as 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, 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 c.device_id = dlb.device_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;
    const schoolId = await getSchoolId(req);
    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, 
             dlb.battery_level
      FROM device_locations dl
      JOIN children c ON dl.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 dl.device_id = dlb.device_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 {
    const schoolId = await getSchoolId(req);
    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,
        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,
        dlb.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, 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 d.device_id = dlb.device_id
      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]);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching devices:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Test endpoint with authentication
router.get('/students-test', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    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,
        dlb.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 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 c.device_id = dlb.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' });
  }
});

// Get all devices for school (with auth)
router.get('/devices-test', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    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,
        dlb.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, 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 d.device_id = dlb.device_id
      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 endpoint with authentication
router.get('/live-tracking', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    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,
        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,
        dlb.battery_level,
        dl.latitude, dl.longitude, dl.address,
        dl.last_location_time as last_location_update,
        CASE 
          WHEN dl.last_location_time IS NULL THEN NULL
          ELSE TIMESTAMPDIFF(MINUTE, dl.last_location_time, NOW())
        END as minutes_ago
      FROM children c
      LEFT JOIN devices d ON c.device_id = d.id
      LEFT JOIN (
        SELECT 
          device_id, 
          latitude, 
          longitude, 
          address, 
          battery_level,
          timestamp as last_location_time,
          ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY timestamp DESC) as rn
        FROM device_locations
      ) dl ON c.device_id = dl.device_id AND dl.rn = 1
      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 c.device_id = dlb.device_id
      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 student location history with authentication
router.get('/students/:studentId/location-history', authenticateToken, async (req, res) => {
  try {
    const { studentId } = req.params;
    const { hours = 24 } = req.query;
    const schoolId = await getSchoolId(req);
    
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    // Verify student belongs to 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' });
    }
    
    const query = `
      SELECT 
        dl.latitude, 
        dl.longitude, 
        dl.address, 
        dl.timestamp,
        dl.battery_level
      FROM device_locations dl
      WHERE dl.device_id = ? 
      AND dl.timestamp >= DATE_SUB(NOW(), INTERVAL ? HOUR)
      ORDER BY dl.timestamp DESC
      LIMIT 50
    `;
    
    const [rows] = await db.execute(query, [studentCheck[0].device_id, hours]);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching location history:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get student location history with explicit start/end window
router.get('/students/:studentId/location-history-range', authenticateToken, async (req, res) => {
  try {
    const { studentId } = req.params;
    const { start, end, limit = 500 } = req.query;
    const schoolId = await getSchoolId(req);

    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }

    if (!start || !end) {
      return res.status(400).json({ message: 'start and end query parameters are required' });
    }

    const startDate = new Date(start);
    const endDate = new Date(end);

    if (Number.isNaN(startDate.getTime()) || Number.isNaN(endDate.getTime())) {
      return res.status(400).json({ message: 'Invalid date format for start or end' });
    }

    if (startDate > endDate) {
      return res.status(400).json({ message: 'start must be before end' });
    }

    // Verify student belongs to school and has a device
    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' });
    }

    const safeLimit = Math.min(parseInt(limit, 10) || 500, 2000);

    const query = `
      SELECT 
        latitude,
        longitude,
        address,
        speed,
        accuracy,
        battery_level,
        timestamp
      FROM device_locations
      WHERE device_id = ?
        AND timestamp BETWEEN ? AND ?
      ORDER BY timestamp ASC
      LIMIT ${safeLimit}
    `;

    const [rows] = await db.execute(query, [
      studentCheck[0].device_id,
      start,
      end
    ]);

    res.json(rows);
  } catch (error) {
    console.error('Error fetching ranged location history:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get subscription plans (authenticated)
router.get('/subscription-plans', 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);
    
    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
    }));
    
    res.json(plans);
  } catch (error) {
    console.error('Error fetching subscription plans:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Collect payment (authenticated)
router.post('/collect-payment', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }

    const { 
      student_id, 
      plan_id, 
      amount, 
      base_amount, 
      tax_amount, 
      tax_percentage, 
      payment_method, 
      payment_date 
    } = req.body;
    
    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' });
    }
    
    // Verify student belongs to the school
    const [studentCheck] = await db.execute('SELECT id FROM children WHERE id = ? AND school_id = ?', [student_id, schoolId]);
    if (studentCheck.length === 0) {
      return res.status(403).json({ message: 'Student not found or access denied' });
    }
    
    // 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];
    
    const transactionId = `TXN_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;
    const upiTransactionId = payment_method === 'upi' ? `UPI_${Date.now()}` : null;
    
    await db.query('START TRANSACTION');
    
    try {
      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)
      ]);
      
      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) {
        const lastEndDate = new Date(latestSubscription[0].end_date);
        if (lastEndDate > new Date()) {
          newStartDate = new Date(lastEndDate);
          newStartDate.setDate(newStartDate.getDate() + 1);
        } else {
          newStartDate = new Date();
        }
        newEndDate = new Date(newStartDate);
        newEndDate.setMonth(newEndDate.getMonth() + durationMonths);
      } else {
        newStartDate = new Date();
        newEndDate = new Date();
        newEndDate.setMonth(newEndDate.getMonth() + durationMonths);
      }
      
      await db.execute(
        'INSERT INTO student_subscriptions (student_id, school_id, plan_id, start_date, end_date, status, payment_id) VALUES (?, ?, ?, ?, ?, "active", ?)',
        [student_id, schoolId, plan_id, newStartDate.toISOString().split('T')[0], newEndDate.toISOString().split('T')[0], paymentResult.insertId]
      );
      
      await db.execute(
        'UPDATE children SET status = "active", updated_at = NOW() WHERE id = ?',
        [student_id]
      );
      
      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) {
      await db.query('ROLLBACK');
      throw error;
    }
  } catch (error) {
    console.error('Error collecting payment:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get payment reports (authenticated)
router.get('/payment-reports', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    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 = ?
      ${start_date ? 'AND p.payment_date >= ?' : ''}
      ${end_date ? 'AND p.payment_date <= ?' : ''}
      ORDER BY p.payment_date DESC
      LIMIT 50
    `;
    
    const params = [schoolId];
    if (start_date) params.push(start_date);
    if (end_date) params.push(end_date);
    
    const [rows] = await db.execute(query, params);
    
    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 standards (authenticated)
router.get('/standards', 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);
});

// Get teachers (authenticated)
router.get('/teachers', 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);
});

// Schools cannot add devices - only admin can assign devices to schools

// Add new student (authenticated)
router.post('/students', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }

    const {
      first_name,
      last_name,
      standard,
      division,
      roll_number,
      device_id,
      parent_mobile_1
    } = req.body;

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

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

    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, [
        req.user.id,
        `${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:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Add new student (test endpoint with auth)
router.post('/students-test', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }

    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, parent_id
      ) VALUES (?, ?, ?, ?, ?, ?, ?, 0)
    `;

    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, [
        req.user.id, // Use authenticated user's ID
        `${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' });
  }
});

// Assign device to student (authenticated)
router.post('/assign-device', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }

    const { device_id, student_id } = req.body;
    
    // Verify student belongs to school
    const [studentCheck] = await db.execute(
      'SELECT id FROM children WHERE id = ? AND school_id = ?',
      [student_id, schoolId]
    );
    
    if (studentCheck.length === 0) {
      return res.status(403).json({ message: 'Student not found or access denied' });
    }
    
    // Update student with device
    await db.execute(
      'UPDATE children SET device_id = ? WHERE id = ? AND school_id = ?',
      [device_id, student_id, schoolId]
    );
    
    res.json({ message: 'Device assigned successfully' });
  } catch (error) {
    console.error('Error assigning device:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Disconnect device from student (authenticated)
router.post('/disconnect-device', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }

    const { student_id } = req.body;
    
    // Verify student belongs to school
    const [studentCheck] = await db.execute(
      'SELECT id FROM children WHERE id = ? AND school_id = ?',
      [student_id, schoolId]
    );
    
    if (studentCheck.length === 0) {
      return res.status(403).json({ message: 'Student not found or access denied' });
    }
    
    // Remove device from student
    await db.execute(
      'UPDATE children SET device_id = NULL WHERE id = ? AND school_id = ?',
      [student_id, schoolId]
    );
    
    res.json({ message: 'Device disconnected successfully' });
  } catch (error) {
    console.error('Error disconnecting device:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get divisions (authenticated)
router.get('/divisions', authenticateToken, (req, res) => {
  const divisions = [
    { id: 1, name: 'A' }, { id: 2, name: 'B' }, { id: 3, name: 'C' },
    { id: 4, name: 'D' }, { id: 5, name: 'E' }
  ];
  res.json(divisions);
});

// Get parent accounts (authenticated)
router.get('/parent-accounts', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    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 c.parent_id = u.id AND c.school_id = ?
      WHERE u.user_type = 'parent' AND u.school_id = ?
      GROUP BY u.id, u.first_name, u.last_name, u.email, u.phone, u.status
      ORDER BY u.first_name, u.last_name
    `;
    
    const [rows] = await db.execute(query, [schoolId, schoolId]);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching parent accounts:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Reset parent password (authenticated)
router.post('/reset-parent-password', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    const { parent_id, new_password } = req.body;
    
    if (!parent_id || !new_password) {
      return res.status(400).json({ message: 'Parent ID and new password are required' });
    }
    
    // Hash the new password
    const hashedPassword = await bcrypt.hash(new_password, 10);
    
    // Update parent password in database
    const [result] = await db.execute(
      'UPDATE users SET password_hash = ? WHERE id = ? AND user_type = "parent"',
      [hashedPassword, parent_id]
    );
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Parent not found' });
    }
    
    res.json({ 
      message: 'Password reset successfully',
      new_password: new_password
    });
  } catch (error) {
    console.error('Error resetting parent password:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Send notification to parents (authenticated)
router.post('/send-notification', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    const { title, message, type, selectedParents } = req.body;
    
    // In real implementation, send notifications via email/SMS
    // For now, just return success
    res.json({ message: 'Notification sent successfully' });
  } catch (error) {
    console.error('Error sending notification:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Check if parent exists (authenticated)
router.get('/check-parent', authenticateToken, async (req, res) => {
  try {
    const { email } = req.query;
    
    if (!email) {
      return res.status(400).json({ message: 'Email is required' });
    }
    
    // In real implementation, check if parent exists in database
    // For now, return mock data
    const mockParent = {
      first_name: 'John',
      last_name: 'Doe',
      phone: '+1234567890',
      address: '123 Main St'
    };
    
    // Simulate parent exists for demo emails
    const exists = email.includes('parent') || email.includes('demo');
    
    res.json({ 
      exists: exists,
      parent: exists ? mockParent : null
    });
  } catch (error) {
    console.error('Error checking parent:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Get all students for parent selection (authenticated)
router.get('/students-for-parent', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    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
      FROM children c
      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 students for parent:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Assign students to parent (authenticated)
router.post('/assign-students-to-parent', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    const { parent_id, student_ids } = req.body;
    
    if (!parent_id || !student_ids || !Array.isArray(student_ids) || student_ids.length === 0) {
      return res.status(400).json({ message: 'Parent ID and student IDs are required' });
    }
    
    console.log('Assigning students to parent:', parent_id, 'students:', student_ids, 'school:', schoolId);
    
    // Verify parent exists and belongs to school
    const [parentCheck] = await db.execute(
      'SELECT id FROM users WHERE id = ? AND user_type = "parent" AND school_id = ?',
      [parent_id, schoolId]
    );
    
    console.log('Parent check result:', parentCheck);
    
    if (parentCheck.length === 0) {
      return res.status(404).json({ message: 'Parent not found' });
    }
    
    let actualAssigned = 0;
    
    // Update students to assign them to the parent
    for (const student_id of student_ids) {
      console.log('Processing student:', student_id);
      
      // Verify student belongs to school
      const [studentCheck] = await db.execute(
        'SELECT id FROM children WHERE id = ? AND school_id = ?',
        [student_id, schoolId]
      );
      
      console.log('Student check for', student_id, ':', studentCheck.length > 0 ? 'found' : 'not found');
      
      if (studentCheck.length > 0) {
        const [updateResult] = await db.execute(
          'UPDATE children SET parent_id = ? WHERE id = ? AND school_id = ?',
          [parent_id, student_id, schoolId]
        );
        console.log('Update result for student', student_id, ':', updateResult.affectedRows, 'rows affected');
        actualAssigned += updateResult.affectedRows;
      }
    }
    
    res.json({ 
      message: 'Students assigned to parent successfully',
      requested_count: student_ids.length,
      actual_assigned: actualAssigned
    });
  } catch (error) {
    console.error('Error assigning students to parent:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Update parent account (authenticated)
router.put('/update-parent/:parentId', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    const { parentId } = req.params;
    const { first_name, last_name, email, phone, status } = req.body;
    
    if (!parentId) {
      return res.status(400).json({ message: 'Parent ID is required' });
    }
    
    // Verify parent exists and belongs to school
    const [parentCheck] = await db.execute(
      'SELECT id FROM users WHERE id = ? AND user_type = "parent" AND school_id = ?',
      [parentId, schoolId]
    );
    
    if (parentCheck.length === 0) {
      return res.status(404).json({ message: 'Parent not found or access denied' });
    }
    
    // Update parent details
    const [result] = await db.execute(
      'UPDATE users SET first_name = ?, last_name = ?, email = ?, phone = ?, status = ? WHERE id = ? AND user_type = "parent" AND school_id = ?',
      [first_name, last_name, email, phone, status, parentId, schoolId]
    );
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Parent not found or no changes made' });
    }
    
    res.json({ 
      message: 'Parent account updated successfully',
      affected_rows: result.affectedRows
    });
  } catch (error) {
    console.error('Error updating parent account:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
 });

 // Delete parent account (authenticated)
router.delete('/delete-parent/:parentId', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    const { parentId } = req.params;
    
    if (!parentId) {
      return res.status(400).json({ message: 'Parent ID is required' });
    }
    
    // Verify parent exists and belongs to school
    const [parentCheck] = await db.execute(
      'SELECT id FROM users WHERE id = ? AND user_type = "parent" AND school_id = ?',
      [parentId, schoolId]
    );
    
    if (parentCheck.length === 0) {
      return res.status(404).json({ message: 'Parent not found or access denied' });
    }
    
    await db.query('START TRANSACTION');
    
    try {
      // Remove parent_id from children
      await db.execute(
        'UPDATE children SET parent_id = 0 WHERE parent_id = ? AND school_id = ?',
        [parentId, schoolId]
      );
      
      // Delete parent details
      await db.execute(
        'DELETE FROM parent_details WHERE user_id = ?',
        [parentId]
      );
      
      // Delete parent user account
      await db.execute(
        'DELETE FROM users WHERE id = ? AND user_type = "parent" AND school_id = ?',
        [parentId, schoolId]
      );
      
      await db.query('COMMIT');
      
      res.json({ 
        message: 'Parent account deleted successfully'
      });
    } catch (error) {
      await db.query('ROLLBACK');
      throw error;
    }
  } catch (error) {
    console.error('Error deleting parent account:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Quick fix to link student to parent (debug endpoint)
router.post('/link-student-to-parent', authenticateToken, async (req, res) => {
  try {
    const { parent_id, student_id } = req.body;
    
    console.log('Linking student', student_id, 'to parent', parent_id);
    
    const [result] = await db.execute(
      'UPDATE children SET parent_id = ? WHERE id = ?',
      [parent_id, student_id]
    );
    
    console.log('Link result:', result.affectedRows, 'rows affected');
    
    res.json({ 
      message: 'Student linked to parent successfully',
      affected_rows: result.affectedRows
    });
  } catch (error) {
    console.error('Error linking student to parent:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Test parent-student assignment (debug)
router.post('/test-parent-assignment', authenticateToken, async (req, res) => {
  try {
    const { parent_id, student_id } = req.body;
    
    // Check parent exists
    const [parentCheck] = await db.execute('SELECT * FROM users WHERE id = ? AND user_type = "parent"', [parent_id]);
    console.log('Parent check:', parentCheck);
    
    // Check student exists
    const [studentCheck] = await db.execute('SELECT * FROM children WHERE id = ?', [student_id]);
    console.log('Student check:', studentCheck);
    
    if (parentCheck.length === 0) {
      return res.status(404).json({ message: 'Parent not found' });
    }
    
    if (studentCheck.length === 0) {
      return res.status(404).json({ message: 'Student not found' });
    }
    
    // Update student parent_id
    const [updateResult] = await db.execute(
      'UPDATE children SET parent_id = ? WHERE id = ?',
      [parent_id, student_id]
    );
    
    console.log('Update result:', updateResult);
    
    // Verify update
    const [verifyResult] = await db.execute('SELECT parent_id FROM children WHERE id = ?', [student_id]);
    console.log('Verify result:', verifyResult);
    
    res.json({ 
      message: 'Assignment test completed',
      parent_found: parentCheck.length > 0,
      student_found: studentCheck.length > 0,
      rows_updated: updateResult.affectedRows,
      current_parent_id: verifyResult[0]?.parent_id
    });
  } catch (error) {
    console.error('Error in test assignment:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Get students assigned to a parent (authenticated)
router.get('/parent-students/:parentId', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    const { parentId } = req.params;
    
    // Verify parent exists and belongs to school
    const [parentCheck] = await db.execute(
      'SELECT id FROM users WHERE id = ? AND user_type = "parent" AND school_id = ?',
      [parentId, schoolId]
    );
    
    if (parentCheck.length === 0) {
      return res.status(404).json({ message: 'Parent not found or access denied' });
    }
    
    // Get assigned students
    const [students] = await db.execute(
      'SELECT id, first_name, last_name, standard, division, roll_number FROM children WHERE parent_id = ? AND school_id = ?',
      [parentId, schoolId]
    );
    
    res.json(students);
  } catch (error) {
    console.error('Error getting parent students:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Unassign student from parent (authenticated)
router.post('/unassign-student-from-parent', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    const { student_id } = req.body;
    
    if (!student_id) {
      return res.status(400).json({ message: 'Student ID is required' });
    }
    
    // Verify student belongs to school
    const [studentCheck] = await db.execute(
      'SELECT id FROM children WHERE id = ? AND school_id = ?',
      [student_id, schoolId]
    );
    
    if (studentCheck.length === 0) {
      return res.status(404).json({ message: 'Student not found or access denied' });
    }
    
    // Remove parent assignment
    const [result] = await db.execute(
      'UPDATE children SET parent_id = NULL WHERE id = ? AND school_id = ?',
      [student_id, schoolId]
    );
    
    res.json({ 
      message: 'Student unassigned from parent successfully',
      affected_rows: result.affectedRows
    });
  } catch (error) {
    console.error('Error unassigning student from parent:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Create parent with students (authenticated)
router.post('/create-parent-with-students', authenticateToken, async (req, res) => {
  try {
    const schoolId = await getSchoolId(req);
    if (!schoolId) {
      return res.status(403).json({ message: 'No school access assigned' });
    }
    
    const { first_name, last_name, email, phone, address, password, selected_students } = req.body;
    
    if (!first_name || !last_name || !email || !password) {
      return res.status(400).json({ message: 'All required fields must be provided' });
    }
    
    // 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' });
    }
    
    const hashedPassword = await bcrypt.hash(password, 10);
    
    await db.query('START TRANSACTION');
    
    try {
      // Create parent user
      const [result] = await db.execute(
        'INSERT INTO users (first_name, last_name, email, username, phone, password_hash, user_type, school_id, status) VALUES (?, ?, ?, ?, ?, ?, "parent", ?, "active")',
        [first_name, last_name, email, email, phone, hashedPassword, schoolId]
      );
      
      const parentId = result.insertId;
      
      // Create parent details if address provided
      if (address) {
        await db.execute(
          'INSERT INTO parent_details (user_id, student_name, standard, address, emergency_contact) VALUES (?, ?, ?, ?, ?)',
          [parentId, `${first_name} ${last_name}`, 'N/A', address, phone]
        );
      }
      
      // Link children to parent
      let assignedCount = 0;
      if (selected_students && selected_students.length > 0) {
        console.log('Assigning students to parent:', parentId, 'students:', selected_students);
        for (const studentId of selected_students) {
          // Verify student belongs to school
          const [studentCheck] = await db.execute(
            'SELECT id FROM children WHERE id = ? AND school_id = ?',
            [studentId, schoolId]
          );
          
          console.log('Student check for ID', studentId, ':', studentCheck.length > 0 ? 'found' : 'not found');
          
          if (studentCheck.length > 0) {
            const [updateResult] = await db.execute(
              'UPDATE children SET parent_id = ? WHERE id = ? AND school_id = ?',
              [parentId, studentId, schoolId]
            );
            console.log('Update result for student', studentId, ':', updateResult.affectedRows, 'rows affected');
            assignedCount++;
          }
        }
      }
      
      await db.query('COMMIT');
      
      res.json({ 
        message: 'Parent account created successfully',
        parent_id: parentId,
        students_assigned: assignedCount
      });
    } catch (error) {
      await db.query('ROLLBACK');
      throw error;
    }
  } catch (error) {
    console.error('Error creating parent account:', error);
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

module.exports = router;