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

const router = Router();

// GET /api/chaching/summary — user analytics & revenue tracking
router.get('/summary', async (req: Request, res) => {
  try {
    const agent = req.query.agent as string;

    // Build agent filter condition using messages → sessions linkage
    let agentJoin = '';
    let agentWhere = '';
    const agentParams: any[] = [];
    let paramOffset = 0;

    if (agent) {
      agentJoin = `
        JOIN crm_messages cm_filter ON cm_filter.contact_id = c.id
        JOIN crm_sessions cs_filter ON cm_filter.session_id = cs_filter.id AND cs_filter.agent = $1
      `;
      agentWhere = `
        AND EXISTS (
          SELECT 1 FROM crm_messages m2
          JOIN crm_sessions s2 ON m2.session_id = s2.id
          WHERE m2.contact_id = c.id AND s2.agent = $1
        )
      `;
      agentParams.push(agent);
      paramOffset = 1;
    }

    // Stats: totalUsers, newSignups7d, freeUsers, paidUsers, active24h, active7d, totalTokens
    const statsQuery = `
      SELECT
        COUNT(*) as total_users,
        COUNT(*) FILTER (WHERE c.first_seen > NOW() - INTERVAL '7 days') as new_signups_7d,
        COUNT(*) FILTER (WHERE c.plan = 'free' OR c.plan IS NULL) as free_users,
        COUNT(*) FILTER (WHERE c.plan IS NOT NULL AND c.plan != 'free') as paid_users,
        COUNT(*) FILTER (WHERE c.last_active > NOW() - INTERVAL '24 hours') as active_24h,
        COUNT(*) FILTER (WHERE c.last_active > NOW() - INTERVAL '7 days') as active_7d,
        COALESCE((
          SELECT SUM(s.total_tokens) FROM crm_sessions s
          ${agent ? 'WHERE s.agent = $1' : ''}
        ), 0) as total_tokens
      FROM crm_contacts c
      WHERE 1=1 ${agentWhere}
    `;

    // Users list with aggregated data
    const usersQuery = `
      SELECT
        c.id,
        c.display_name,
        c.username,
        c.plan,
        c.first_seen,
        c.last_active,
        COALESCE(msg_stats.message_count, 0) as message_count,
        COALESCE(msg_stats.token_usage, 0) as token_usage,
        msg_stats.last_message_at,
        msg_stats.agents
      FROM crm_contacts c
      LEFT JOIN LATERAL (
        SELECT
          COUNT(*) FILTER (WHERE m.role = 'user' AND m.content_type = 'text') as message_count,
          COALESCE(SUM(m.tokens), 0) as token_usage,
          MAX(m.timestamp) as last_message_at,
          array_agg(DISTINCT s.agent) FILTER (WHERE s.agent IS NOT NULL) as agents
        FROM crm_messages m
        JOIN crm_sessions s ON m.session_id = s.id
        WHERE m.contact_id = c.id
        ${agent ? 'AND s.agent = $1' : ''}
      ) msg_stats ON true
      WHERE 1=1 ${agentWhere}
      ORDER BY c.last_active DESC
    `;

    // Signup trend (last 30 days)
    const trendQuery = `
      SELECT DATE(c.first_seen) as day, COUNT(*) as signups
      FROM crm_contacts c
      WHERE c.first_seen > NOW() - INTERVAL '30 days'
      ${agentWhere}
      GROUP BY DATE(c.first_seen)
      ORDER BY day
    `;

    const [statsResult, usersResult, trendResult] = await Promise.all([
      pool.query(statsQuery, agentParams),
      pool.query(usersQuery, agentParams),
      pool.query(trendQuery, agentParams),
    ]);

    const stats = statsResult.rows[0];

    // Compute status server-side for each user
    const now = Date.now();
    const users = usersResult.rows.map((u: any) => {
      const lastActive = new Date(u.last_active).getTime();
      const firstSeen = new Date(u.first_seen).getTime();
      const msgsCount = parseInt(u.message_count) || 0;
      const diffMs = now - lastActive;
      const ageSinceFirst = now - firstSeen;

      let status = 'idle';
      if (diffMs < 24 * 60 * 60 * 1000 && msgsCount > 200) {
        status = 'abuse';
      } else if (diffMs < 24 * 60 * 60 * 1000) {
        status = 'active';
      } else if (ageSinceFirst < 7 * 24 * 60 * 60 * 1000) {
        status = 'new';
      } else if (diffMs > 30 * 24 * 60 * 60 * 1000) {
        status = 'churned';
      }

      return {
        id: u.id,
        display_name: u.display_name,
        username: u.username,
        plan: u.plan || 'free',
        first_seen: u.first_seen,
        last_active: u.last_active,
        message_count: msgsCount,
        token_usage: parseInt(u.token_usage) || 0,
        last_message_at: u.last_message_at,
        agents: u.agents || [],
        status,
      };
    });

    res.json({
      stats: {
        totalUsers: parseInt(stats.total_users),
        newSignups7d: parseInt(stats.new_signups_7d),
        freeUsers: parseInt(stats.free_users),
        paidUsers: parseInt(stats.paid_users),
        active24h: parseInt(stats.active_24h),
        active7d: parseInt(stats.active_7d),
        totalTokens: parseInt(stats.total_tokens),
      },
      users,
      signupTrend: trendResult.rows.map((r: any) => ({
        day: r.day,
        signups: parseInt(r.signups),
      })),
    });
  } catch (err: any) {
    console.error('Cha-Ching summary error:', err);
    res.status(500).json({ error: err.message });
  }
});

export default router;