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

const router = Router();

const VALID_AGENTS = ['main', 'sportsclaw', 'cryptoclaw'];

router.get('/', async (req: Request, res) => {
  try {
    const agent = req.query.agent as string | undefined;
    const days = Math.min(365, Math.max(1, parseInt(req.query.days as string) || 30));

    if (agent && !VALID_AGENTS.includes(agent)) {
      res.status(400).json({ error: 'Invalid agent. Must be one of: main, sportsclaw, cryptoclaw' });
      return;
    }

    // Build agent filter for session-joined queries
    const agentFilter = agent ? `AND s.agent = '${agent}'` : '';
    const agentFilterDirect = agent
      ? `AND c.id IN (SELECT DISTINCT contact_id FROM crm_sessions WHERE agent = '${agent}' AND contact_id IS NOT NULL)`
      : '';

    // ── Funnel ──
    const funnelResult = await pool.query(`
      SELECT
        (SELECT COUNT(*) FROM crm_contacts c WHERE 1=1 ${agentFilterDirect}) AS total,

        (SELECT COUNT(DISTINCT m.contact_id) FROM crm_messages m
         JOIN crm_sessions s ON m.session_id = s.id
         WHERE m.role = 'user' AND m.content_type = 'text' ${agentFilter}
        ) AS contacted,

        (SELECT COUNT(DISTINCT m.contact_id) FROM crm_messages m
         JOIN crm_sessions s ON m.session_id = s.id
         WHERE m.role = 'user' AND m.content_type = 'text'
           AND m.timestamp > NOW() - INTERVAL '7 days' ${agentFilter}
        ) AS active_7d,

        (SELECT COUNT(*) FROM (
          SELECT m.contact_id FROM crm_messages m
          JOIN crm_sessions s ON m.session_id = s.id
          WHERE m.role = 'user' AND m.content_type = 'text' ${agentFilter}
          GROUP BY m.contact_id HAVING COUNT(*) >= 10
        ) sub) AS engaged,

        (SELECT COUNT(*) FROM crm_contacts c
         WHERE (c.sc_user_id IS NOT NULL OR c.rm_user_id IS NOT NULL) ${agentFilterDirect}
        ) AS linked,

        (SELECT COUNT(*) FROM crm_contacts c
         WHERE (c.plan != 'free' OR EXISTS (
           SELECT 1 FROM rm_purchases p WHERE p.user_id = c.rm_user_id
         )) ${agentFilterDirect}
        ) AS paid
    `);

    const funnel = funnelResult.rows[0];
    const funnelData = {
      total: parseInt(funnel.total),
      contacted: parseInt(funnel.contacted),
      active_7d: parseInt(funnel.active_7d),
      engaged: parseInt(funnel.engaged),
      linked: parseInt(funnel.linked),
      paid: parseInt(funnel.paid),
    };

    // Compute rates with GREATEST(1) equivalent
    const funnelStages = [
      { key: 'total', label: 'Total Contacts', count: funnelData.total },
      { key: 'contacted', label: 'Contacted', count: funnelData.contacted },
      { key: 'active_7d', label: 'Active (7d)', count: funnelData.active_7d },
      { key: 'engaged', label: 'Engaged (10+)', count: funnelData.engaged },
      { key: 'linked', label: 'Linked Account', count: funnelData.linked },
      { key: 'paid', label: 'Paid', count: funnelData.paid },
    ];

    const funnelWithRates = funnelStages.map((stage, i) => ({
      ...stage,
      pctOfTotal: Math.round((stage.count / Math.max(funnelData.total, 1)) * 100),
      pctOfPrev: i === 0 ? 100 : Math.round((stage.count / Math.max(funnelStages[i - 1].count, 1)) * 100),
    }));

    // ── Bot Performance ──
    const botsResult = await pool.query(`
      SELECT
        s.agent,
        COUNT(DISTINCT s.contact_id) AS contacts,
        COUNT(DISTINCT CASE WHEN s.last_activity > NOW() - INTERVAL '7 days' THEN s.contact_id END) AS active_contacts,
        COALESCE(SUM(CASE WHEN m.role = 'user' THEN 1 ELSE 0 END), 0) AS user_messages,
        COALESCE(SUM(CASE WHEN m.role = 'assistant' THEN 1 ELSE 0 END), 0) AS bot_responses,
        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,
        COALESCE(SUM(CASE WHEN m.role = 'user' AND m.timestamp > NOW() - INTERVAL '24 hours' THEN 1 ELSE 0 END), 0) AS user_msgs_24h,
        COALESCE(SUM(CASE WHEN m.role = 'assistant' AND m.timestamp > NOW() - INTERVAL '24 hours' THEN 1 ELSE 0 END), 0) AS bot_responses_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')
      ${agent ? `AND s.agent = '${agent}'` : ''}
      GROUP BY s.agent
      ORDER BY s.agent
    `);

    const bots = botsResult.rows.map(b => {
      const userMsgs = parseInt(b.user_messages);
      const botResps = parseInt(b.bot_responses);
      const lastResp = b.last_bot_response ? new Date(b.last_bot_response) : null;
      const deltaMs = lastResp ? Date.now() - lastResp.getTime() : null;
      const deltaMin = deltaMs !== null ? Math.floor(deltaMs / 60000) : null;

      return {
        agent: b.agent,
        contacts: parseInt(b.contacts),
        active_contacts: parseInt(b.active_contacts),
        user_messages: userMsgs,
        bot_responses: botResps,
        response_rate: Math.round((botResps / Math.max(userMsgs, 1)) * 100),
        last_bot_response: b.last_bot_response,
        last_response_delta_min: deltaMin,
        health: deltaMin === null ? 'gray' : deltaMin < 10 ? 'green' : 'red',
        user_msgs_24h: parseInt(b.user_msgs_24h),
        bot_responses_24h: parseInt(b.bot_responses_24h),
      };
    });

    // ── Recent Signups ──
    const signupsResult = await pool.query(`
      SELECT c.id, c.display_name, c.username, c.first_seen, c.plan,
             (SELECT s.agent FROM crm_sessions s WHERE s.contact_id = c.id ORDER BY s.started_at ASC LIMIT 1) AS first_agent,
             (SELECT m.content FROM crm_messages m
              JOIN crm_sessions s ON m.session_id = s.id
              WHERE m.contact_id = c.id AND m.role = 'user' AND m.content_type = 'text'
              ORDER BY m.timestamp ASC LIMIT 1
             ) AS first_message
      FROM crm_contacts c
      ${agent ? `WHERE c.id IN (SELECT DISTINCT contact_id FROM crm_sessions WHERE agent = '${agent}' AND contact_id IS NOT NULL)` : ''}
      ORDER BY c.first_seen DESC
      LIMIT 20
    `);

    const recentSignups = signupsResult.rows.map(r => ({
      id: r.id,
      name: r.display_name || r.username || 'Unknown',
      agent: r.first_agent || 'unknown',
      first_message: r.first_message ? r.first_message.slice(0, 80) : null,
      signup_date: r.first_seen,
      plan: r.plan,
    }));

    // ── Signup Timeline ──
    const timelineResult = await pool.query(`
      SELECT DATE(c.first_seen) AS day, COUNT(*) AS count
      FROM crm_contacts c
      WHERE c.first_seen > NOW() - INTERVAL '${days} days'
      ${agentFilterDirect}
      GROUP BY DATE(c.first_seen)
      ORDER BY day
    `);

    const timeline = timelineResult.rows.map(r => ({
      day: r.day,
      count: parseInt(r.count),
    }));

    res.json({
      funnel: funnelWithRates,
      bots,
      recentSignups,
      timeline,
    });
  } catch (err: any) {
    console.error('Conversions error:', err);
    res.status(500).json({ error: err.message });
  }
});

// ── GET /intel — paginated user intelligence list ──
router.get('/intel', async (req: Request, res) => {
  try {
    const search = (req.query.search as string || '').trim();
    const sort = (req.query.sort as string) || 'created_at';
    const order = (req.query.order as string || 'desc').toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
    const page = Math.max(1, parseInt(req.query.page as string) || 1);
    const limit = Math.min(100, Math.max(1, parseInt(req.query.limit as string) || 25));
    const offset = (page - 1) * limit;

    const allowedSorts: Record<string, string> = {
      email: 'u.email',
      created_at: 'u.created_at',
      updated_at: 'u.updated_at',
      visit_count: 'visit_count',
      event_count: 'event_count',
      survey_count: 'survey_count',
      purchase_count: 'purchase_count',
    };
    const sortCol = allowedSorts[sort] || 'u.created_at';

    const searchFilter = search ? `AND u.email ILIKE $3` : '';
    const params: any[] = [limit, offset];
    if (search) params.push(`%${search}%`);

    const query = `
      SELECT
        u.id, u.email, u.email_verified, u.is_weatherman, u.country_code,
        u.signup_ip, u.last_login_ip, u.marketing_consent, u.affiliate_code,
        u.preferences, u.created_at, u.updated_at,
        COALESCE(pv.cnt, 0)::int AS visit_count,
        COALESCE(pv.sessions, 0)::int AS session_count,
        COALESCE(bh.cnt, 0)::int AS event_count,
        COALESCE(bh.sessions, 0)::int AS behavior_sessions,
        COALESCE(cv.cnt, 0)::int AS conversion_count,
        COALESCE(sr.cnt, 0)::int AS survey_count,
        COALESCE(pr.cnt, 0)::int AS purchase_count,
        COALESCE(pr.total, 0)::int AS total_spent,
        COALESCE(pk.cnt, 0)::int AS pick_count
      FROM rm_users u
      LEFT JOIN LATERAL (
        SELECT COUNT(*)::int AS cnt, COUNT(DISTINCT session_id)::int AS sessions
        FROM site_pageviews WHERE user_id = u.id::text
      ) pv ON true
      LEFT JOIN LATERAL (
        SELECT COUNT(*)::int AS cnt, COUNT(DISTINCT session_id)::int AS sessions
        FROM rm_user_behavior WHERE user_id = u.id
      ) bh ON true
      LEFT JOIN LATERAL (
        SELECT COUNT(*)::int AS cnt FROM site_conversions WHERE user_id = u.id::text
      ) cv ON true
      LEFT JOIN LATERAL (
        SELECT COUNT(*)::int AS cnt FROM rm_survey_responses WHERE user_id = u.id
      ) sr ON true
      LEFT JOIN LATERAL (
        SELECT COUNT(*)::int AS cnt, COALESCE(SUM(amount_cents), 0)::int AS total
        FROM rm_purchases WHERE user_id = u.id
      ) pr ON true
      LEFT JOIN LATERAL (
        SELECT COUNT(*)::int AS cnt FROM rm_user_picks WHERE user_id = u.id
      ) pk ON true
      WHERE 1=1 ${searchFilter}
      ORDER BY ${sortCol} ${order}
      LIMIT $1 OFFSET $2
    `;

    const countQuery = `SELECT COUNT(*) FROM rm_users u WHERE 1=1 ${searchFilter}`;
    const countParams = search ? [`%${search}%`] : [];

    const [usersResult, countResult, summaryResult] = await Promise.all([
      pool.query(query, params),
      pool.query(countQuery, countParams),
      pool.query(`
        SELECT
          COUNT(*)::int AS total_users,
          COUNT(*) FILTER (WHERE email_verified)::int AS verified,
          COUNT(*) FILTER (WHERE updated_at > NOW() - INTERVAL '7 days')::int AS active_7d,
          COUNT(*) FILTER (WHERE is_weatherman)::int AS weathermen,
          (SELECT COUNT(*)::int FROM rm_survey_responses) AS total_survey_responses,
          (SELECT COUNT(DISTINCT user_id)::int FROM rm_survey_responses) AS survey_respondents,
          (SELECT COUNT(*)::int FROM rm_purchases) AS total_purchases
        FROM rm_users
      `),
    ]);

    res.json({
      users: usersResult.rows,
      total: parseInt(countResult.rows[0].count),
      page,
      limit,
      summary: summaryResult.rows[0],
    });
  } catch (err: any) {
    console.error('Intel list error:', err);
    res.status(500).json({ error: err.message });
  }
});

// ── GET /intel/:id — full single-user profile ──
router.get('/intel/:id', async (req: Request, res) => {
  try {
    const userId = req.params.id;

    const [userResult, pageviewsResult, behaviorResult, conversionsResult, surveysResult, purchasesResult] = await Promise.all([
      // 1. Core user
      pool.query('SELECT * FROM rm_users WHERE id = $1', [userId]),
      // 2. Pageviews (authenticated)
      pool.query(
        `SELECT path, referrer, ip_address, user_agent, session_id, created_at
         FROM site_pageviews WHERE user_id = $1 ORDER BY created_at DESC LIMIT 200`,
        [userId]
      ),
      // 3. Behavior events
      pool.query(
        `SELECT event_type, event_data, session_id, ip_address, created_at
         FROM rm_user_behavior WHERE user_id = $1 ORDER BY created_at DESC LIMIT 200`,
        [userId]
      ),
      // 4. Conversions
      pool.query(
        `SELECT event, event_data, ip_address, created_at
         FROM site_conversions WHERE user_id = $1 ORDER BY created_at DESC LIMIT 100`,
        [userId]
      ),
      // 5. Survey responses with survey info
      pool.query(
        `SELECT sr.id, sr.answers, sr.credit_awarded, sr.created_at,
                s.title AS survey_title, s.questions AS survey_questions
         FROM rm_survey_responses sr
         JOIN rm_surveys s ON sr.survey_id = s.id
         WHERE sr.user_id = $1
         ORDER BY sr.created_at DESC`,
        [userId]
      ),
      // 6. Purchases
      pool.query(
        `SELECT id, product_type, amount_cents, picks_granted, created_at
         FROM rm_purchases WHERE user_id = $1 ORDER BY created_at DESC`,
        [userId]
      ),
    ]);

    if (!userResult.rows[0]) {
      res.status(404).json({ error: 'User not found' });
      return;
    }

    const user = userResult.rows[0];

    // 7. Find linked visitor_ids from pageviews/behavior, then fetch anonymous pre-signup pages
    const visitorIds = new Set<string>();
    for (const pv of pageviewsResult.rows) {
      // Find visitor_ids from this user's pageviews via a subquery
    }

    const visitorResult = await pool.query(
      `SELECT DISTINCT visitor_id FROM site_pageviews
       WHERE user_id = $1 AND visitor_id IS NOT NULL`,
      [userId]
    );
    for (const r of visitorResult.rows) visitorIds.add(r.visitor_id);

    let anonymousPageviews: any[] = [];
    if (visitorIds.size > 0) {
      const vids = Array.from(visitorIds);
      const anonResult = await pool.query(
        `SELECT path, referrer, ip_address, visitor_id, session_id, created_at
         FROM site_pageviews
         WHERE visitor_id = ANY($1) AND (user_id IS NULL OR user_id = '')
         ORDER BY created_at DESC LIMIT 200`,
        [vids]
      );
      anonymousPageviews = anonResult.rows;
    }

    // Collect all known IPs
    const knownIps = new Set<string>();
    if (user.signup_ip) knownIps.add(user.signup_ip);
    if (user.last_login_ip) knownIps.add(user.last_login_ip);
    for (const pv of pageviewsResult.rows) {
      if (pv.ip_address) knownIps.add(pv.ip_address);
    }
    for (const bh of behaviorResult.rows) {
      if (bh.ip_address) knownIps.add(String(bh.ip_address));
    }
    for (const anon of anonymousPageviews) {
      if (anon.ip_address) knownIps.add(anon.ip_address);
    }

    // Behavior summary (counts by event_type)
    const behaviorSummary: Record<string, number> = {};
    for (const bh of behaviorResult.rows) {
      behaviorSummary[bh.event_type] = (behaviorSummary[bh.event_type] || 0) + 1;
    }

    res.json({
      user: {
        id: user.id,
        email: user.email,
        email_verified: user.email_verified,
        is_weatherman: user.is_weatherman,
        country_code: user.country_code,
        signup_ip: user.signup_ip,
        last_login_ip: user.last_login_ip,
        marketing_consent: user.marketing_consent,
        affiliate_code: user.affiliate_code,
        preferences: user.preferences,
        created_at: user.created_at,
        updated_at: user.updated_at,
      },
      pageviews: pageviewsResult.rows,
      anonymousPageviews,
      behavior: behaviorResult.rows,
      behaviorSummary,
      conversions: conversionsResult.rows,
      surveys: surveysResult.rows,
      purchases: purchasesResult.rows,
      knownIps: Array.from(knownIps),
    });
  } catch (err: any) {
    console.error('Intel detail error:', err);
    res.status(500).json({ error: err.message });
  }
});

// ── GET /surveys — aggregate survey insights ──
router.get('/surveys', async (req: Request, res) => {
  try {
    const [surveysResult, responsesResult, statsResult] = await Promise.all([
      pool.query(`
        SELECT s.*, (SELECT COUNT(*)::int FROM rm_survey_responses WHERE survey_id = s.id) AS response_count
        FROM rm_surveys s ORDER BY s.created_at DESC
      `),
      pool.query(`
        SELECT sr.id, sr.survey_id, sr.answers, sr.credit_awarded, sr.created_at,
               u.email, s.title AS survey_title, s.questions AS survey_questions
        FROM rm_survey_responses sr
        JOIN rm_users u ON sr.user_id = u.id
        JOIN rm_surveys s ON sr.survey_id = s.id
        ORDER BY sr.created_at DESC
      `),
      pool.query(`
        SELECT
          (SELECT COUNT(*)::int FROM rm_surveys) AS total_surveys,
          (SELECT COUNT(*)::int FROM rm_survey_responses) AS total_responses,
          (SELECT COUNT(DISTINCT user_id)::int FROM rm_survey_responses) AS unique_respondents,
          (SELECT COUNT(*)::int FROM rm_users WHERE email_verified) AS verified_users
      `),
    ]);

    const stats = statsResult.rows[0];
    const responseRate = stats.verified_users > 0
      ? Math.round((stats.unique_respondents / stats.verified_users) * 100)
      : 0;

    res.json({
      surveys: surveysResult.rows,
      responses: responsesResult.rows,
      stats: {
        ...stats,
        response_rate: responseRate,
      },
    });
  } catch (err: any) {
    console.error('Survey insights error:', err);
    res.status(500).json({ error: err.message });
  }
});

export default router;