const pool = require("../config/db");

/**
 * Unified activity log from users_logs, sat_form_logs, tickets_logs.
 * Returns { timestamp, user, action, type, details, ip }[] ordered by timestamp DESC.
 */
const getActivityLog = async (limit = 500) => {
  const limitNum = Math.min(Math.max(1, parseInt(limit, 10) || 500), 1000);

  // users_logs: has employee_name, action, created_at
  const [userLogs] = await pool.query(
    `SELECT 
       ul.created_at as ts,
       COALESCE(ul.employee_name, 'System') as user_name,
       COALESCE(ul.action, 'User action') as action_text,
       'User' as log_type,
       CONCAT(COALESCE(ul.action, ''), IF(ul.employee_id IS NOT NULL, CONCAT(' (', ul.employee_id, ')'), '')) as details
     FROM users_logs ul
     WHERE ul.created_at IS NOT NULL
     ORDER BY ul.created_at DESC
     LIMIT ?`,
    [limitNum]
  ).catch(() => [[]]);

  // sat_form_logs: join users for name, use created_date or created_at if available
  const [formLogs] = await pool.query(
    `SELECT 
       COALESCE(fl.created_date, fl.created_at) as ts,
       COALESCE(u.employee_name, 'Unknown') as user_name,
       COALESCE(fl.action, 'Form action') as action_text,
       'Form' as log_type,
       CONCAT(COALESCE(fl.title, 'Form'), IF(fl.description IS NOT NULL AND fl.description != '', CONCAT(': ', LEFT(fl.description, 100)), '')) as details
     FROM sat_form_logs fl
     LEFT JOIN users u ON fl.user_id = u.user_id
     WHERE (fl.created_date IS NOT NULL OR fl.created_at IS NOT NULL)
     ORDER BY COALESCE(fl.created_date, fl.created_at) DESC
     LIMIT ?`,
    [limitNum]
  ).catch(() => [[]]);

  // tickets_logs: join users, tickets for context
  const [ticketLogs] = await pool.query(
    `SELECT 
       tl.created_at as ts,
       COALESCE(u.employee_name, 'Unknown') as user_name,
       'Ticket update' as action_text,
       'Ticket' as log_type,
       CONCAT(COALESCE(t.subject, 'Ticket'), IF(tl.description IS NOT NULL AND tl.description != '', CONCAT(' - ', LEFT(tl.description, 80)), '')) as details
     FROM tickets_logs tl
     LEFT JOIN users u ON tl.user_id = u.user_id
     LEFT JOIN tickets t ON tl.ticket_id = t.id
     WHERE tl.created_at IS NOT NULL
     ORDER BY tl.created_at DESC
     LIMIT ?`,
    [limitNum]
  ).catch(() => [[]]);

  const all = [
    ...userLogs.map((r) => ({
      timestamp: formatTimestamp(r.ts),
      user: r.user_name || "Unknown",
      action: r.action_text || "—",
      type: r.log_type || "System",
      details: r.details || "—",
      ip: null,
    })),
    ...formLogs.map((r) => ({
      timestamp: formatTimestamp(r.ts),
      user: r.user_name || "Unknown",
      action: r.action_text || "—",
      type: r.log_type || "Form",
      details: (r.details || "—").substring(0, 200),
      ip: null,
    })),
    ...ticketLogs.map((r) => ({
      timestamp: formatTimestamp(r.ts),
      user: r.user_name || "Unknown",
      action: r.action_text || "—",
      type: r.log_type || "Ticket",
      details: (r.details || "—").substring(0, 200),
      ip: null,
    })),
  ];

  all.sort((a, b) => new Date(b.timestamp) - new Date(a.timestamp));
  return all.slice(0, limitNum);
};

function formatTimestamp(ts) {
  if (!ts) return null;
  const d = new Date(ts);
  const y = d.getFullYear();
  const m = String(d.getMonth() + 1).padStart(2, "0");
  const day = String(d.getDate()).padStart(2, "0");
  const h = String(d.getHours()).padStart(2, "0");
  const min = String(d.getMinutes()).padStart(2, "0");
  const s = String(d.getSeconds()).padStart(2, "0");
  return `${y}-${m}-${day} ${h}:${min}:${s}`;
}

module.exports = {
  getActivityLog,
};
