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

// 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,
        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' });
  }
});

// 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,
        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' });
  }
});

// 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 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 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 with authentication
router.post('/collect-payment-test', 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;
    
    // 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' });
    }
    
    // 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];
    
    // 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 (?, ?, ?, ?, ?, "active", ?)',
        [student_id, schoolId, 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 {
    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,
        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 and authentication
router.get('/payment-reports-test', 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);
    
    // 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 renewal reminders with authentication
router.get('/renewal-reminders-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,
        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 = ? 
      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, [schoolId]);
    
    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' });
  }
});

// Add new device (test endpoint with auth)
router.post('/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 {
      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' });
  }
});

// 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
      ) 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, [
        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' });
  }
});

module.exports = router;