const express = require('express');
const router = express.Router();
const db = require('../config/database');
const { authenticateToken, requireRole } = require('../middleware/auth');

// Middleware to ensure only super admin access
const requireSuperAdmin = (req, res, next) => {
  if (req.user.role !== 'admin') {
    return res.status(403).json({ message: 'Super Admin access required' });
  }
  next();
};

// School Management APIs
router.get('/schools', authenticateToken, requireSuperAdmin, async (req, res) => {
  try {
    const query = `
      SELECT s.*, sp.name as plan_name, sp.price_per_device,
             COUNT(DISTINCT c.id) as total_students,
             COUNT(DISTINCT d.id) as total_devices
      FROM schools s
      LEFT JOIN subscription_plans sp ON s.subscription_plan_id = sp.id
      LEFT JOIN children c ON s.id = c.school_id AND c.status = 'active'
      LEFT JOIN devices d ON s.id = d.school_id AND d.status = 'active'
      GROUP BY s.id
      ORDER BY s.created_at DESC
    `;
    const [rows] = await db.execute(query);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching schools:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

router.post('/schools', authenticateToken, requireSuperAdmin, async (req, res) => {
  try {
    const { name, address, phone, email, contact_person, subscription_plan_id } = req.body;
    
    const query = `
      INSERT INTO schools (name, address, phone, email, contact_person, subscription_plan_id, company_id)
      VALUES (?, ?, ?, ?, ?, ?, 1)
    `;
    
    const [result] = await db.execute(query, [name, address, phone, email, contact_person, subscription_plan_id]);
    res.json({ id: result.insertId, message: 'School created successfully' });
  } catch (error) {
    console.error('Error creating school:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Device Inventory Management
router.get('/devices/inventory', authenticateToken, requireSuperAdmin, async (req, res) => {
  try {
    const query = `
      SELECT d.*, s.name as school_name,
             CASE 
               WHEN da.student_id IS NOT NULL THEN 'assigned'
               WHEN d.school_id IS NOT NULL THEN 'allocated'
               ELSE 'available'
             END as assignment_status,
             c.first_name, c.last_name
      FROM devices d
      LEFT JOIN schools s ON d.school_id = s.id
      LEFT JOIN device_assignments da ON d.id = da.device_id AND da.status = 'active'
      LEFT JOIN children c ON da.student_id = c.id
      ORDER BY d.created_at DESC
    `;
    
    const [rows] = await db.execute(query);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching device inventory:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

router.post('/devices/bulk-add', authenticateToken, requireSuperAdmin, async (req, res) => {
  try {
    const { devices } = req.body; // Array of device objects
    
    const query = `
      INSERT INTO devices (id, imei_number, sim_number, model, manufacturer, type, purchase_date)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `;
    
    for (const device of devices) {
      await db.execute(query, [
        device.device_id,
        device.imei_number,
        device.sim_number,
        device.model,
        device.manufacturer,
        device.type || 'GPS Tracker',
        device.purchase_date
      ]);
    }
    
    res.json({ message: `${devices.length} devices added successfully` });
  } catch (error) {
    console.error('Error adding devices:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

router.post('/devices/:deviceId/assign-school', authenticateToken, requireSuperAdmin, async (req, res) => {
  try {
    const { deviceId } = req.params;
    const { school_id } = req.body;
    
    await db.execute(
      'UPDATE devices SET school_id = ? WHERE id = ?',
      [school_id, deviceId]
    );
    
    res.json({ message: 'Device assigned to school successfully' });
  } catch (error) {
    console.error('Error assigning device:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Billing and Subscription Management
router.get('/billing/overview', authenticateToken, requireSuperAdmin, async (req, res) => {
  try {
    const query = `
      SELECT 
        COUNT(DISTINCT s.id) as total_schools,
        COUNT(DISTINCT CASE WHEN s.subscription_end_date > CURDATE() THEN s.id END) as active_subscriptions,
        COUNT(DISTINCT CASE WHEN s.subscription_end_date <= CURDATE() THEN s.id END) as expired_subscriptions,
        SUM(CASE WHEN p.payment_status = 'completed' AND p.payment_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) THEN p.amount ELSE 0 END) as monthly_revenue,
        COUNT(DISTINCT d.id) as total_devices,
        COUNT(DISTINCT CASE WHEN d.status = 'active' THEN d.id END) as active_devices
      FROM schools s
      LEFT JOIN payments p ON s.id = p.school_id
      LEFT JOIN devices d ON s.id = d.school_id
    `;
    
    const [rows] = await db.execute(query);
    res.json(rows[0]);
  } catch (error) {
    console.error('Error fetching billing overview:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

router.get('/payments', authenticateToken, requireSuperAdmin, async (req, res) => {
  try {
    const { page = 1, limit = 50, status, school_id } = req.query;
    const offset = (page - 1) * limit;
    
    let whereClause = '1=1';
    let params = [];
    
    if (status) {
      whereClause += ' AND p.payment_status = ?';
      params.push(status);
    }
    
    if (school_id) {
      whereClause += ' AND p.school_id = ?';
      params.push(school_id);
    }
    
    const query = `
      SELECT p.*, s.name as school_name, sp.name as plan_name,
             u.first_name, u.last_name
      FROM payments p
      JOIN schools s ON p.school_id = s.id
      JOIN subscription_plans sp ON p.subscription_plan_id = sp.id
      JOIN users u ON p.collected_by = u.id
      WHERE ${whereClause}
      ORDER BY p.created_at DESC
      LIMIT ? OFFSET ?
    `;
    
    params.push(parseInt(limit), offset);
    const [rows] = await db.execute(query, params);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching payments:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Support Ticket Management
router.get('/tickets', authenticateToken, requireSuperAdmin, async (req, res) => {
  try {
    const { status = 'open', priority, type } = req.query;
    
    let whereClause = '1=1';
    let params = [];
    
    if (status !== 'all') {
      whereClause += ' AND st.status = ?';
      params.push(status);
    }
    
    if (priority) {
      whereClause += ' AND st.priority = ?';
      params.push(priority);
    }
    
    if (type) {
      whereClause += ' AND st.type = ?';
      params.push(type);
    }
    
    const query = `
      SELECT st.*, s.name as school_name, 
             creator.first_name as creator_first_name, creator.last_name as creator_last_name,
             assignee.first_name as assignee_first_name, assignee.last_name as assignee_last_name
      FROM support_tickets st
      LEFT JOIN schools s ON st.school_id = s.id
      JOIN users creator ON st.created_by = creator.id
      LEFT JOIN users assignee ON st.assigned_to = assignee.id
      WHERE ${whereClause}
      ORDER BY 
        CASE st.priority 
          WHEN 'urgent' THEN 1 
          WHEN 'high' THEN 2 
          WHEN 'medium' THEN 3 
          ELSE 4 
        END,
        st.created_at DESC
    `;
    
    const [rows] = await db.execute(query, params);
    res.json(rows);
  } catch (error) {
    console.error('Error fetching tickets:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

router.put('/tickets/:ticketId/assign', authenticateToken, requireSuperAdmin, async (req, res) => {
  try {
    const { ticketId } = req.params;
    const { assigned_to } = req.body;
    
    await db.execute(
      'UPDATE support_tickets SET assigned_to = ?, status = "in_progress" WHERE id = ?',
      [assigned_to, ticketId]
    );
    
    res.json({ message: 'Ticket assigned successfully' });
  } catch (error) {
    console.error('Error assigning ticket:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

// Analytics and Reports
router.get('/analytics/dashboard', authenticateToken, requireSuperAdmin, async (req, res) => {
  try {
    const queries = {
      totalSchools: 'SELECT COUNT(*) as count FROM schools WHERE status = "active"',
      totalDevices: 'SELECT COUNT(*) as count FROM devices',
      activeDevices: 'SELECT COUNT(*) as count FROM devices WHERE status = "active"',
      totalStudents: 'SELECT COUNT(*) as count FROM children WHERE status = "active"',
      monthlyRevenue: `
        SELECT SUM(amount) as revenue 
        FROM payments 
        WHERE payment_status = 'completed' 
        AND payment_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
      `,
      openTickets: 'SELECT COUNT(*) as count FROM support_tickets WHERE status IN ("open", "in_progress")'
    };
    
    const results = {};
    for (const [key, query] of Object.entries(queries)) {
      const [rows] = await db.execute(query);
      results[key] = rows[0].count || rows[0].revenue || 0;
    }
    
    res.json(results);
  } catch (error) {
    console.error('Error fetching analytics:', error);
    res.status(500).json({ message: 'Server error' });
  }
});

module.exports = router;