const { Router } = require('express');
const multer = require('multer');
const path = require('path');
const pool = require('../config/db');
const { authRequired, requireRole } = require('../middlewares/auth');
const { logAction } = require('../services/audit');
const { onTicketCreated, onStatusChange, onNewComment } = require('../services/notifications');
const ai = require('../services/ai');

const router = Router();

const storage = multer.diskStorage({
  destination: (r, f, cb) => cb(null, path.join(__dirname, '../../public/uploads')),
  filename: (r, f, cb) => cb(null, Date.now() + '_' + f.originalname.replace(/[^a-zA-Z0-9._-]/g, '_'))
});
const upload = multer({
  storage, limits: { fileSize: 10 * 1024 * 1024 },
  fileFilter: (r, f, cb) => { cb(null, /jpg|jpeg|png|gif|pdf|doc|docx|xls|xlsx|txt|zip|csv/.test(path.extname(f.originalname).toLowerCase())); }
});

// LIST
router.get('/', authRequired, async (req, res) => {
  try {
    const { status, priority, category, assigned, search, page = 1, limit = 20 } = req.query;
    let where = '1=1', params = [];

    if (req.user.role === 'client') { where += ' AND t.created_by=?'; params.push(req.user.sub); }
    if (status) { where += ' AND ts.name=?'; params.push(status); }
    if (priority) { where += ' AND t.priority=?'; params.push(priority); }
    if (category) { where += ' AND t.category_id=?'; params.push(category); }
    if (assigned) { where += ' AND t.assigned_to=?'; params.push(assigned); }
    if (search) { where += ' AND (t.subject LIKE ? OR t.description LIKE ?)'; params.push(`%${search}%`, `%${search}%`); }

    const off = (Math.max(1, +page) - 1) * +limit;
    const [cnt] = await pool.query(`SELECT COUNT(*) as total FROM tickets t JOIN ticket_statuses ts ON t.status_id=ts.id WHERE ${where}`, params);
    const [rows] = await pool.query(`
      SELECT t.*, ts.name as status_name, ts.color as status_color, ts.icon as status_icon, ts.is_closed,
             tc.name as category_name, tc.color as category_color,
             cr.name as creator_name, ag.name as agent_name,
             a.name as asset_name, a.asset_tag,
             (SELECT COUNT(*) FROM ticket_comments WHERE ticket_id=t.id) as comment_count,
             (SELECT COUNT(*) FROM ticket_attachments WHERE ticket_id=t.id) as attachment_count
      FROM tickets t
      JOIN ticket_statuses ts ON t.status_id=ts.id
      LEFT JOIN ticket_categories tc ON t.category_id=tc.id
      LEFT JOIN users cr ON t.created_by=cr.id
      LEFT JOIN users ag ON t.assigned_to=ag.id
      LEFT JOIN assets a ON t.asset_id=a.id
      WHERE ${where}
      ORDER BY FIELD(t.priority,'critical','high','medium','low'), t.created_at DESC
      LIMIT ? OFFSET ?`, [...params, +limit, off]);

    res.json({ data: rows, total: cnt[0].total, page: +page, pages: Math.ceil(cnt[0].total / +limit) });
  } catch (e) { console.error(e); res.status(500).json({ error: 'Error al obtener tickets' }); }
});

// DETAIL
router.get('/:id', authRequired, async (req, res) => {
  try {
    const [rows] = await pool.query(`
      SELECT t.*, ts.name as status_name, ts.color as status_color, ts.is_closed,
             tc.name as category_name, sc.name as subcategory_name,
             cr.name as creator_name, cr.email as creator_email,
             ag.name as agent_name, a.name as asset_name, a.asset_tag,
             sp.name as sla_name, sp.response_hours as sla_resp_h, sp.resolution_hours as sla_res_h
      FROM tickets t
      JOIN ticket_statuses ts ON t.status_id=ts.id
      LEFT JOIN ticket_categories tc ON t.category_id=tc.id
      LEFT JOIN ticket_categories sc ON t.subcategory_id=sc.id
      LEFT JOIN users cr ON t.created_by=cr.id
      LEFT JOIN users ag ON t.assigned_to=ag.id
      LEFT JOIN assets a ON t.asset_id=a.id
      LEFT JOIN sla_policies sp ON t.sla_policy_id=sp.id
      WHERE t.id=?`, [req.params.id]);
    if (!rows.length) return res.status(404).json({ error: 'No encontrado' });
    if (req.user.role === 'client' && rows[0].created_by !== req.user.sub) return res.status(403).json({ error: 'No autorizado' });

    const [comments] = await pool.query('SELECT c.*, u.name as user_name, u.role as user_role FROM ticket_comments c JOIN users u ON c.user_id=u.id WHERE c.ticket_id=? ORDER BY c.created_at', [req.params.id]);
    const [attachments] = await pool.query('SELECT * FROM ticket_attachments WHERE ticket_id=?', [req.params.id]);
    const [audit] = await pool.query('SELECT * FROM audit_log WHERE entity_type="ticket" AND entity_id=? ORDER BY created_at DESC LIMIT 30', [req.params.id]);

    const filteredComments = req.user.role === 'client' ? comments.filter(c => !c.is_internal) : comments;
    res.json({ ...rows[0], comments: filteredComments, attachments, audit });
  } catch (e) { console.error(e); res.status(500).json({ error: 'Error' }); }
});

// CREATE
router.post('/', authRequired, upload.single('attachment'), async (req, res) => {
  try {
    const { subject, description, priority = 'medium', category_id, subcategory_id, assigned_to, asset_id } = req.body;
    if (!subject || !description) return res.status(400).json({ error: 'Asunto y descripción requeridos' });

    const [ds] = await pool.query('SELECT id FROM ticket_statuses WHERE is_default=1 LIMIT 1');
    const statusId = ds[0]?.id || 1;

    const [sla] = await pool.query('SELECT * FROM sla_policies WHERE priority=? OR is_default=1 ORDER BY (priority=?) DESC LIMIT 1', [priority, priority]);
    const sp = sla[0] || null;
    const now = Date.now();

    const [result] = await pool.query(`
      INSERT INTO tickets (subject, description, priority, status_id, category_id, subcategory_id, assigned_to, created_by, asset_id, sla_policy_id, sla_response_due, sla_resolution_due)
      VALUES (?,?,?,?,?,?,?,?,?,?,?,?)`,
      [subject, description, priority, statusId, category_id || null, subcategory_id || null, assigned_to || null, req.user.sub, asset_id || null,
       sp?.id || null,
       sp ? new Date(now + sp.response_hours * 36e5).toISOString().slice(0, 19).replace('T', ' ') : null,
       sp ? new Date(now + sp.resolution_hours * 36e5).toISOString().slice(0, 19).replace('T', ' ') : null]);

    const ticketId = result.insertId;

    if (req.file) {
      await pool.query('INSERT INTO ticket_attachments (ticket_id, filename, original_name, file_size, mime_type, uploaded_by) VALUES (?,?,?,?,?,?)',
        [ticketId, req.file.filename, req.file.originalname, req.file.size, req.file.mimetype, req.user.sub]);
    }

    await logAction(req.user.sub, req.user.name, 'ticket_created', 'ticket', ticketId, null, { subject, priority }, req.ip);
    onTicketCreated({ id: ticketId, subject, description, priority, assigned_to, created_by: req.user.sub }, req.user.name).catch(console.error);

    // AI analysis (non-blocking)
    if (ai.isEnabled()) {
      ai.analyzeTicket(ticketId).then(r => {
        if (r) pool.query('UPDATE tickets SET ai_summary=? WHERE id=?', [JSON.stringify(r), ticketId]).catch(() => {});
      }).catch(() => {});
    }

    res.status(201).json({ id: ticketId, message: 'Ticket creado' });
  } catch (e) { console.error(e); res.status(500).json({ error: 'Error al crear ticket' }); }
});

// UPDATE
router.put('/:id', authRequired, requireRole('admin', 'agent'), async (req, res) => {
  try {
    const { status_id, priority, assigned_to, category_id, subcategory_id } = req.body;
    const [old] = await pool.query('SELECT t.*, ts.name as old_status FROM tickets t JOIN ticket_statuses ts ON t.status_id=ts.id WHERE t.id=?', [req.params.id]);
    if (!old.length) return res.status(404).json({ error: 'No encontrado' });

    const sets = [], vals = [];
    if (status_id !== undefined) { sets.push('status_id=?'); vals.push(status_id); }
    if (priority) { sets.push('priority=?'); vals.push(priority); }
    if (assigned_to !== undefined) { sets.push('assigned_to=?'); vals.push(assigned_to || null); }
    if (category_id) { sets.push('category_id=?'); vals.push(category_id); }
    if (subcategory_id !== undefined) { sets.push('subcategory_id=?'); vals.push(subcategory_id || null); }
    if (!sets.length) return res.status(400).json({ error: 'Nada que actualizar' });

    if (status_id) {
      const [ns] = await pool.query('SELECT is_closed, name FROM ticket_statuses WHERE id=?', [status_id]);
      if (ns[0]?.is_closed && !old[0].resolved_at) sets.push('resolved_at=NOW()');
      if (!old[0].first_response_at) sets.push('first_response_at=NOW()');
      if (status_id !== old[0].status_id) onStatusChange(old[0], req.user.name, old[0].old_status, ns[0]?.name).catch(console.error);
    }

    sets.push('updated_at=NOW()');
    vals.push(req.params.id);
    await pool.query(`UPDATE tickets SET ${sets.join(',')} WHERE id=?`, vals);
    await logAction(req.user.sub, req.user.name, 'ticket_updated', 'ticket', +req.params.id, { status_id: old[0].status_id, priority: old[0].priority }, { status_id, priority }, req.ip);
    res.json({ message: 'Ticket actualizado' });
  } catch (e) { console.error(e); res.status(500).json({ error: 'Error' }); }
});

// ADD COMMENT
router.post('/:id/comments', authRequired, upload.single('attachment'), async (req, res) => {
  try {
    const { comment, is_internal } = req.body;
    if (!comment?.trim()) return res.status(400).json({ error: 'Comentario requerido' });

    const [t] = await pool.query('SELECT * FROM tickets WHERE id=?', [req.params.id]);
    if (!t.length) return res.status(404).json({ error: 'No encontrado' });
    if (req.user.role === 'client' && t[0].created_by !== req.user.sub) return res.status(403).json({ error: 'No autorizado' });

    const internal = (is_internal === 'true' || is_internal === true) && req.user.role !== 'client' ? 1 : 0;
    const [r] = await pool.query('INSERT INTO ticket_comments (ticket_id, user_id, comment, is_internal) VALUES (?,?,?,?)', [req.params.id, req.user.sub, comment.trim(), internal]);

    if (req.file) {
      await pool.query('INSERT INTO ticket_attachments (ticket_id, filename, original_name, file_size, mime_type, uploaded_by) VALUES (?,?,?,?,?,?)',
        [req.params.id, req.file.filename, req.file.originalname, req.file.size, req.file.mimetype, req.user.sub]);
    }

    if (!t[0].first_response_at && req.user.role !== 'client') {
      await pool.query('UPDATE tickets SET first_response_at=NOW() WHERE id=?', [req.params.id]);
    }

    onNewComment(+req.params.id, req.user.name, req.user.sub).catch(console.error);
    await logAction(req.user.sub, req.user.name, 'comment_added', 'ticket', +req.params.id);
    res.status(201).json({ id: r.insertId, message: 'Comentario agregado' });
  } catch (e) { console.error(e); res.status(500).json({ error: 'Error' }); }
});

// RATE
router.post('/:id/rate', authRequired, async (req, res) => {
  const { rating, comment } = req.body;
  await pool.query('UPDATE tickets SET satisfaction_rating=?, satisfaction_comment=? WHERE id=? AND created_by=?',
    [Math.min(5, Math.max(1, +rating)), comment || null, req.params.id, req.user.sub]);
  res.json({ message: 'Gracias por tu calificación' });
});

// AI SUGGEST
router.get('/:id/ai-suggest', authRequired, requireRole('admin', 'agent'), async (req, res) => {
  const s = await ai.suggestResponse(+req.params.id);
  res.json({ suggestion: s || 'IA no disponible en este momento.' });
});

// AI ANALYZE
router.get('/:id/ai-analyze', authRequired, requireRole('admin', 'agent'), async (req, res) => {
  const a = await ai.analyzeTicket(+req.params.id);
  res.json(a || { error: 'IA no disponible' });
});

module.exports = router;
