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

// Middleware to verify JWT token
const verifyToken = (req, res, next) => {
  const token = req.headers.authorization?.split(' ')[1];
  if (!token) {
    return res.status(401).json({ message: 'No token provided' });
  }
  
  try {
    const decoded = jwt.verify(token, process.env.JWT_SECRET);
    req.user = decoded;
    next();
  } catch (error) {
    return res.status(401).json({ message: 'Invalid token' });
  }
};

// Get all subscription plans
router.get('/plans', async (req, res) => {
  try {
    const [rows] = await db.execute(`
      SELECT id, name, description, price, duration_months, features, status
      FROM subscription_plans 
      WHERE status = 'active'
      ORDER BY price ASC
    `);
    res.json(rows);
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Get all user devices with subscription details
router.get('/my-subscriptions', verifyToken, async (req, res) => {
  try {
    const [rows] = await db.execute(`
      SELECT c.id as child_id, c.first_name, c.last_name,
             d.device_id as device_id, d.type as device_type, d.status as device_status,
             dlb.battery_level, dlb.timestamp as last_update,
             latest_sub.max_end_date as end_date,
             latest_sub.plan_name, latest_sub.price, latest_sub.duration_months,
             CASE 
               WHEN latest_sub.max_end_date IS NOT NULL THEN DATEDIFF(latest_sub.max_end_date, CURDATE())
               ELSE NULL 
             END as days_remaining
      FROM children c
      LEFT JOIN devices d ON c.device_id = d.device_id
      LEFT JOIN (
        SELECT device_id, battery_level, timestamp
        FROM (
          SELECT
            device_id,
            battery_level,
            timestamp,
            ROW_NUMBER() OVER (PARTITION BY device_id ORDER BY timestamp DESC) as rn
          FROM device_locations
        ) x
        WHERE x.rn = 1
      ) dlb ON dlb.device_id = c.device_id
      LEFT JOIN (
        SELECT ss.student_id, MAX(ss.end_date) as max_end_date, 
               sp.name as plan_name, sp.price, sp.duration_months
        FROM student_subscriptions ss
        JOIN subscription_plans sp ON ss.plan_id = sp.id
        WHERE ss.end_date = (
          SELECT MAX(end_date) FROM student_subscriptions WHERE student_id = ss.student_id
        )
        GROUP BY ss.student_id
      ) latest_sub ON c.id = latest_sub.student_id
      WHERE c.parent_id = ?
      GROUP BY d.device_id
      ORDER BY latest_sub.max_end_date DESC, c.id ASC
    `, [req.user.id]);
    
    res.json(rows);
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Create new subscription
router.post('/subscribe', verifyToken, async (req, res) => {
  try {
    const { device_id, plan_id } = req.body;
    
    // Get plan details
    const [planRows] = await db.execute(
      'SELECT * FROM subscription_plans WHERE id = ? AND status = "active"',
      [plan_id]
    );
    
    if (planRows.length === 0) {
      return res.status(404).json({ message: 'Subscription plan not found' });
    }
    
    const plan = planRows[0];
    const startDate = new Date();
    const endDate = new Date();
    endDate.setMonth(endDate.getMonth() + plan.duration_months);
    
    // Create subscription
    const [result] = await db.execute(`
      INSERT INTO device_subscriptions (device_id, user_id, plan_id, start_date, end_date)
      VALUES (?, ?, ?, ?, ?)
    `, [device_id, req.user.id, plan_id, startDate.toISOString().split('T')[0], endDate.toISOString().split('T')[0]]);
    
    res.json({ 
      message: 'Subscription created successfully',
      subscription_id: result.insertId
    });
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Cancel subscription
router.put('/cancel/:subscriptionId', verifyToken, async (req, res) => {
  try {
    const { subscriptionId } = req.params;
    
    await db.execute(`
      UPDATE device_subscriptions 
      SET status = 'cancelled', updated_at = NOW()
      WHERE id = ? AND user_id = ?
    `, [subscriptionId, req.user.id]);
    
    res.json({ message: 'Subscription cancelled successfully' });
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Renew subscription
router.put('/renew/:subscriptionId', verifyToken, async (req, res) => {
  try {
    const { subscriptionId } = req.params;
    const { plan_id } = req.body;
    
    // Get plan details
    const [planRows] = await db.execute(
      'SELECT * FROM subscription_plans WHERE id = ? AND status = "active"',
      [plan_id]
    );
    
    if (planRows.length === 0) {
      return res.status(404).json({ message: 'Subscription plan not found' });
    }
    
    const plan = planRows[0];
    const newStartDate = new Date();
    const newEndDate = new Date();
    newEndDate.setMonth(newEndDate.getMonth() + plan.duration_months);
    
    // Update subscription with new start date
    await db.execute(`
      UPDATE device_subscriptions 
      SET plan_id = ?, start_date = ?, end_date = ?, status = 'active', updated_at = NOW()
      WHERE id = ? AND user_id = ?
    `, [plan_id, newStartDate.toISOString().split('T')[0], newEndDate.toISOString().split('T')[0], subscriptionId, req.user.id]);
    
    res.json({ 
      message: 'Subscription renewed successfully',
      new_end_date: newEndDate.toISOString().split('T')[0]
    });
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Create student subscription with payment record
router.post('/create-student-subscription', verifyToken, async (req, res) => {
  try {
    const { student_id, plan_id, amount } = req.body;
    
    // Get plan details
    const [planRows] = await db.execute(
      'SELECT * FROM subscription_plans WHERE id = ?',
      [plan_id]
    );
    
    if (planRows.length === 0) {
      return res.status(404).json({ message: 'Plan not found' });
    }
    
    const plan = planRows[0];
    
    // Check for existing subscriptions
    const [existingSubscriptions] = await db.execute(
      'SELECT MAX(end_date) as max_end_date FROM student_subscriptions WHERE student_id = ?',
      [student_id]
    );
    
    let startDate, endDate;
    const today = new Date();
    
    if (existingSubscriptions.length > 0 && existingSubscriptions[0].max_end_date) {
      const maxEndDate = new Date(existingSubscriptions[0].max_end_date);
      
      // If max end date is in future, start from next day after max end date
      if (maxEndDate > today) {
        startDate = new Date(maxEndDate);
        startDate.setDate(startDate.getDate() + 1);
      } else {
        // If expired, start from today
        startDate = today;
      }
    } else {
      // No existing subscriptions, start from today
      startDate = today;
    }
    
    // Calculate end date
    endDate = new Date(startDate);
    endDate.setMonth(endDate.getMonth() + (plan.duration_months || 1));
    
    // Start transaction
    await db.query('START TRANSACTION');
    
    try {
      // Insert payment record first
      const [paymentResult] = await db.execute(`
        INSERT INTO payments (student_id, plan_id, amount, payment_method, payment_date, status)
        VALUES (?, ?, ?, 'UPI', NOW(), 'completed')
      `, [student_id, plan_id, amount]);
      
      // Insert into student_subscriptions with payment_id
      await db.execute(`
        INSERT INTO student_subscriptions (student_id, school_id, plan_id, start_date, end_date, status, auto_renew, payment_id)
        VALUES (?, 1, ?, ?, ?, 'active', 1, ?)
      `, [student_id, plan_id, startDate.toISOString().split('T')[0], endDate.toISOString().split('T')[0], paymentResult.insertId]);
      
      // Commit transaction
      await db.query('COMMIT');
      
      res.json({ 
        message: 'Subscription and payment created successfully',
        payment_id: paymentResult.insertId
      });
    } catch (error) {
      // Rollback transaction on error
      await db.query('ROLLBACK');
      throw error;
    }
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

// Record payment for parent purchases
router.post('/record-payment', verifyToken, async (req, res) => {
  try {
    const { student_id, plan_id, amount, payment_method = 'UPI' } = req.body;
    
    // Insert payment record
    const [result] = await db.execute(`
      INSERT INTO payments (student_id, plan_id, amount, payment_method, payment_date, status)
      VALUES (?, ?, ?, ?, NOW(), 'completed')
    `, [student_id, plan_id, amount, payment_method]);
    
    res.json({ 
      message: 'Payment recorded successfully',
      payment_id: result.insertId
    });
  } catch (error) {
    res.status(500).json({ message: 'Server error: ' + error.message });
  }
});

module.exports = router;