import { Router } from 'express';
import pool from '../db';

const router = Router();

router.get('/', async (_req, res) => {
  try {
    const [contacts, sessions, messages, tokens, agents] = await Promise.all([
      pool.query('SELECT COUNT(*) as count FROM crm_contacts'),
      pool.query('SELECT COUNT(*) as count FROM crm_sessions'),
      pool.query('SELECT COUNT(*) as count FROM crm_messages'),
      pool.query('SELECT COALESCE(SUM(total_tokens), 0) as total FROM crm_sessions'),
      pool.query(`
        SELECT agent,
          COUNT(DISTINCT contact_id) as contacts,
          COUNT(*) as sessions,
          COALESCE(SUM(message_count), 0) as messages,
          COALESCE(SUM(total_tokens), 0) as tokens
        FROM crm_sessions
        GROUP BY agent
        ORDER BY agent
      `),
    ]);

    // New signups: this week vs previous week
    const signupTrend = await pool.query(`
      SELECT
        COUNT(*) FILTER (WHERE first_seen > NOW() - INTERVAL '7 days') AS this_week,
        COUNT(*) FILTER (WHERE first_seen > NOW() - INTERVAL '14 days' AND first_seen <= NOW() - INTERVAL '7 days') AS prev_week
      FROM crm_contacts
    `);

    // Active in last 24h
    const active24h = await pool.query(`
      SELECT COUNT(DISTINCT contact_id) AS count
      FROM crm_messages
      WHERE role = 'user' AND content_type = 'text' AND timestamp > NOW() - INTERVAL '24 hours'
    `);

    // Bot health — per agent
    const botHealth = await pool.query(`
      SELECT
        s.agent,
        MAX(CASE WHEN m.role = 'user' THEN m.timestamp END) AS last_user_msg,
        MAX(CASE WHEN m.role = 'assistant' THEN m.timestamp END) AS last_bot_response,
        COUNT(*) FILTER (WHERE m.timestamp > NOW() - INTERVAL '24 hours') AS msgs_24h
      FROM crm_sessions s
      LEFT JOIN crm_messages m ON m.session_id = s.id AND m.content_type = 'text'
      WHERE s.agent IN ('main', 'sportsclaw', 'cryptoclaw')
      GROUP BY s.agent
      ORDER BY s.agent
    `);

    const thisWeek = parseInt(signupTrend.rows[0].this_week);
    const prevWeek = parseInt(signupTrend.rows[0].prev_week);
    const trendPct = prevWeek > 0 ? Math.round(((thisWeek - prevWeek) / prevWeek) * 100) : (thisWeek > 0 ? 100 : 0);

    res.json({
      stats: {
        totalContacts: parseInt(contacts.rows[0].count),
        totalSessions: parseInt(sessions.rows[0].count),
        totalMessages: parseInt(messages.rows[0].count),
        totalTokens: parseInt(tokens.rows[0].total),
        newSignups7d: thisWeek,
        newSignupsPrev7d: prevWeek,
        signupTrendPct: trendPct,
        active24h: parseInt(active24h.rows[0].count),
      },
      agents: agents.rows,
      botHealth: botHealth.rows.map(b => {
        const lastResp = b.last_bot_response ? new Date(b.last_bot_response) : null;
        const deltaMin = lastResp ? Math.floor((Date.now() - lastResp.getTime()) / 60000) : null;
        return {
          agent: b.agent,
          last_user_msg: b.last_user_msg,
          last_bot_response: b.last_bot_response,
          msgs_24h: parseInt(b.msgs_24h),
          health: deltaMin === null ? 'gray' : deltaMin < 10 ? 'green' : 'red',
          delta_min: deltaMin,
        };
      }),
    });
  } catch (err: any) {
    console.error('Dashboard error:', err);
    res.status(500).json({ error: err.message });
  }
});

export default router;