const { Router } = require('express');
const pool = require('../config/db');
const { authRequired } = require('../middlewares/auth');

const router = Router();

// MAIN DASHBOARD
router.get('/', authRequired, async (req, res) => {
  try {
    const isAgent = ['admin', 'agent'].includes(req.user.role);
    const uid = req.user.sub;
    const userFilter = isAgent ? '' : `AND t.created_by=${uid}`;

    const [[{openTickets}]] = await pool.query(`SELECT COUNT(*) as openTickets FROM tickets t JOIN ticket_statuses ts ON t.status_id=ts.id WHERE ts.is_closed=0 ${userFilter}`);
    const [[{resolvedToday}]] = await pool.query(`SELECT COUNT(*) as resolvedToday FROM tickets t WHERE DATE(t.resolved_at)=CURDATE() ${userFilter}`);
    const [[{slaBreached}]] = await pool.query(`SELECT COUNT(*) as slaBreached FROM tickets t JOIN ticket_statuses ts ON t.status_id=ts.id WHERE t.sla_resolution_due IS NOT NULL AND t.sla_resolution_due<NOW() AND ts.is_closed=0 ${userFilter}`);
    const [[{avgResp}]] = await pool.query(`SELECT AVG(TIMESTAMPDIFF(MINUTE,created_at,first_response_at)) as avgResp FROM tickets WHERE first_response_at IS NOT NULL AND created_at>=DATE_SUB(NOW(),INTERVAL 30 DAY)`);
    const [[{avgRes}]] = await pool.query(`SELECT AVG(TIMESTAMPDIFF(HOUR,created_at,resolved_at)) as avgRes FROM tickets WHERE resolved_at IS NOT NULL AND created_at>=DATE_SUB(NOW(),INTERVAL 30 DAY)`);

    const [byStatus] = await pool.query(`SELECT ts.name, ts.color, ts.icon, COUNT(t.id) as count FROM ticket_statuses ts LEFT JOIN tickets t ON t.status_id=ts.id ${isAgent ? '' : 'AND t.created_by=' + uid} GROUP BY ts.id ORDER BY ts.sort_order`);
    const [byPriority] = await pool.query(`SELECT priority, COUNT(*) as count FROM tickets t WHERE 1=1 ${userFilter} GROUP BY priority`);

    const [recent] = await pool.query(`SELECT t.id, t.subject, t.priority, ts.name as status_name, ts.color as status_color, cr.name as creator_name, ag.name as agent_name, t.created_at
      FROM tickets t JOIN ticket_statuses ts ON t.status_id=ts.id LEFT JOIN users cr ON t.created_by=cr.id LEFT JOIN users ag ON t.assigned_to=ag.id
      ${isAgent ? '' : 'WHERE t.created_by=' + uid} ORDER BY t.created_at DESC LIMIT 10`);

    const [perDay] = await pool.query('SELECT DATE(created_at) as date, COUNT(*) as count FROM tickets WHERE created_at>=DATE_SUB(NOW(),INTERVAL 14 DAY) GROUP BY DATE(created_at) ORDER BY date');

    let agentStats = [], satisfaction = { avg: 0, total: 0 };
    if (isAgent) {
      const [ag] = await pool.query(`SELECT u.id, u.name, COUNT(t.id) as total_tickets, SUM(CASE WHEN ts.is_closed=1 THEN 1 ELSE 0 END) as resolved,
        AVG(TIMESTAMPDIFF(HOUR,t.created_at,t.resolved_at)) as avg_hours
        FROM users u LEFT JOIN tickets t ON t.assigned_to=u.id LEFT JOIN ticket_statuses ts ON t.status_id=ts.id
        WHERE u.role IN ('admin','agent') AND u.active=1 GROUP BY u.id ORDER BY resolved DESC`);
      agentStats = ag;
      const [[sat]] = await pool.query('SELECT AVG(satisfaction_rating) as avg, COUNT(satisfaction_rating) as total FROM tickets WHERE satisfaction_rating IS NOT NULL');
      satisfaction = sat;
    }

    const [[{unread}]] = await pool.query('SELECT COUNT(*) as unread FROM notifications WHERE user_id=? AND is_read=0', [uid]);

    res.json({ openTickets, resolvedToday, slaBreached, avgResponseMinutes: Math.round(avgResp || 0), avgResolutionHours: Math.round(avgRes || 0),
      byStatus, byPriority, recent, perDay, agentStats, satisfaction, unreadNotifications: unread });
  } catch (e) { console.error(e); res.status(500).json({ error: 'Error' }); }
});

// NOTIFICATIONS
router.get('/notifications', authRequired, async (req, res) => {
  const [n] = await pool.query('SELECT * FROM notifications WHERE user_id=? ORDER BY created_at DESC LIMIT 30', [req.user.sub]);
  res.json(n);
});
router.put('/notifications/read', authRequired, async (req, res) => {
  await pool.query('UPDATE notifications SET is_read=1 WHERE user_id=?', [req.user.sub]);
  res.json({ message: 'OK' });
});

module.exports = router;
