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

const router = Router();

// GET /api/contacts — paginated list
router.get('/', async (req: Request, res) => {
  try {
    const page = Math.max(1, parseInt(req.query.page as string) || 1);
    const limit = Math.min(100, parseInt(req.query.limit as string) || 25);
    const offset = (page - 1) * limit;
    const agent = req.query.agent as string;
    const plan = req.query.plan as string;
    const search = req.query.search as string;
    const sort = req.query.sort as string || 'last_active';
    const order = (req.query.order as string)?.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';

    const conditions: string[] = [];
    const params: any[] = [];
    let paramIdx = 1;

    if (agent) {
      conditions.push(`EXISTS (SELECT 1 FROM crm_sessions s WHERE s.contact_id = c.id AND s.agent = $${paramIdx})`);
      params.push(agent);
      paramIdx++;
    }

    if (plan) {
      conditions.push(`c.plan = $${paramIdx}`);
      params.push(plan);
      paramIdx++;
    }

    if (search) {
      conditions.push(`(c.display_name ILIKE $${paramIdx} OR c.username ILIKE $${paramIdx} OR c.telegram_id::text LIKE $${paramIdx})`);
      params.push(`%${search}%`);
      paramIdx++;
    }

    const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
    const sortColumn = ['last_active', 'display_name', 'first_seen', 'created_at'].includes(sort) ? sort : 'last_active';

    const [countResult, dataResult] = await Promise.all([
      pool.query(`SELECT COUNT(*) FROM crm_contacts c ${whereClause}`, params),
      pool.query(`
        SELECT c.*,
          (SELECT COUNT(*) FROM crm_sessions s WHERE s.contact_id = c.id) as session_count,
          (SELECT COUNT(*) FROM crm_messages m WHERE m.contact_id = c.id AND m.content_type = 'text') as message_count,
          (SELECT array_agg(DISTINCT s.agent) FROM crm_sessions s WHERE s.contact_id = c.id) as agents
        FROM crm_contacts c
        ${whereClause}
        ORDER BY c.${sortColumn} ${order}
        LIMIT $${paramIdx} OFFSET $${paramIdx + 1}
      `, [...params, limit, offset]),
    ]);

    res.json({
      contacts: dataResult.rows,
      total: parseInt(countResult.rows[0].count),
      page,
      limit,
      totalPages: Math.ceil(parseInt(countResult.rows[0].count) / limit),
    });
  } catch (err: any) {
    console.error('Contacts list error:', err);
    res.status(500).json({ error: err.message });
  }
});

// GET /api/contacts/:id — detail
router.get('/:id', async (req, res) => {
  try {
    const { id } = req.params;

    const [contact, sessions, stats] = await Promise.all([
      pool.query('SELECT * FROM crm_contacts WHERE id = $1', [id]),
      pool.query(`
        SELECT s.*,
          (SELECT content FROM crm_messages m WHERE m.session_id = s.id AND m.content_type = 'text' ORDER BY m.timestamp DESC LIMIT 1) as last_message
        FROM crm_sessions s
        WHERE s.contact_id = $1
        ORDER BY s.last_activity DESC
        LIMIT 50
      `, [id]),
      pool.query(`
        SELECT
          COUNT(*) FILTER (WHERE m.role = 'user' AND m.content_type = 'text') as user_messages,
          COUNT(*) FILTER (WHERE m.role = 'assistant' AND m.content_type = 'text') as assistant_messages,
          COALESCE(SUM(m.tokens), 0) as total_tokens
        FROM crm_messages m
        WHERE m.contact_id = $1
      `, [id]),
    ]);

    if (contact.rows.length === 0) {
      res.status(404).json({ error: 'Contact not found' });
      return;
    }

    res.json({
      contact: contact.rows[0],
      sessions: sessions.rows,
      stats: stats.rows[0],
    });
  } catch (err: any) {
    console.error('Contact detail error:', err);
    res.status(500).json({ error: err.message });
  }
});

// GET /api/contacts/:id/queries — recent user queries (cleaned)
router.get('/:id/queries', async (req, res) => {
  try {
    const { id } = req.params;
    const page = Math.max(1, parseInt(req.query.page as string) || 1);
    const limit = Math.min(200, parseInt(req.query.limit as string) || 50);
    const offset = (page - 1) * limit;

    // Get user text messages for this contact (across all sessions)
    const [countResult, messagesResult] = await Promise.all([
      pool.query(
        `SELECT COUNT(*) FROM crm_messages m
         WHERE m.contact_id = $1 AND m.role = 'user' AND m.content_type = 'text'
         AND m.content IS NOT NULL AND LENGTH(m.content) > 0`,
        [id]
      ),
      pool.query(
        `SELECT m.id, m.timestamp, m.content, s.agent,
                CASE WHEN s.chat_type = 'group' THEN COALESCE(s.group_name, 'group') ELSE 'DM' END as channel
         FROM crm_messages m
         JOIN crm_sessions s ON m.session_id = s.id
         WHERE m.contact_id = $1 AND m.role = 'user' AND m.content_type = 'text'
         AND m.content IS NOT NULL AND LENGTH(m.content) > 0
         ORDER BY m.timestamp DESC
         LIMIT $2 OFFSET $3`,
        [id, limit, offset]
      ),
    ]);

    // Also get group messages where this contact might be sender (via content metadata)
    const contact = await pool.query('SELECT telegram_id, username, display_name FROM crm_contacts WHERE id = $1', [id]);
    let groupMessages: any[] = [];
    if (contact.rows.length > 0) {
      const c = contact.rows[0];
      // Search group session messages that contain this user's telegram_id in metadata
      if (c.telegram_id) {
        const groupResult = await pool.query(
          `SELECT m.id, m.timestamp, m.content, s.agent,
                  COALESCE(s.group_name, 'group') as channel
           FROM crm_messages m
           JOIN crm_sessions s ON m.session_id = s.id
           WHERE s.chat_type = 'group' AND s.agent = 'sportsclaw'
           AND m.role = 'user' AND m.content_type = 'text'
           AND m.content IS NOT NULL AND LENGTH(m.content) > 0
           AND m.content LIKE $1
           ORDER BY m.timestamp DESC
           LIMIT $2`,
          [`%${c.telegram_id}%`, limit]
        );
        groupMessages = groupResult.rows;
      }
    }

    // Merge DM + group messages, sort by timestamp DESC, dedup
    const allMessages = [...messagesResult.rows, ...groupMessages]
      .sort((a, b) => new Date(b.timestamp).getTime() - new Date(a.timestamp).getTime());

    // Clean content and dedup
    const cleaned: any[] = [];
    const seen = new Set<string>();
    for (const msg of allMessages) {
      const text = cleanMessageContent(msg.content);
      if (!text) continue;
      // Dedup by text similarity (skip if same as previous within 5 min)
      const key = text.slice(0, 100).toLowerCase().trim();
      if (seen.has(key)) continue;
      seen.add(key);
      cleaned.push({
        id: msg.id,
        timestamp: msg.timestamp,
        content: text,
        agent: msg.agent,
        channel: msg.channel,
      });
    }

    const total = parseInt(countResult.rows[0].count) + groupMessages.length;
    res.json({
      queries: cleaned.slice(0, limit),
      total,
      page,
      limit,
    });
  } catch (err: any) {
    console.error('Contact queries error:', err);
    res.status(500).json({ error: err.message });
  }
});

/** Strip metadata wrappers and telegram prefixes from message content */
function cleanMessageContent(content: string): string | null {
  if (!content) return null;
  let text = content.trim();

  // Skip system/internal messages
  if (text.startsWith('System:') || text.startsWith('Pre-compaction') ||
      /^\[(Mon|Tue|Wed|Thu|Fri|Sat|Sun) /.test(text)) {
    return null;
  }

  // Handle [media attached]
  if (text.startsWith('[media attached')) {
    return '[sent image/media]';
  }

  // Handle [Queued messages] — extract first real message
  if (text.startsWith('[Queued messages')) {
    const chunks = text.split(/---\s*\nQueued #\d+\s*\n/);
    for (const chunk of chunks.slice(1)) {
      const clean = cleanMessageContent(chunk.trim());
      if (clean) return clean;
    }
    return null;
  }

  // Strip "Conversation info" and "Sender" metadata blocks
  if (text.startsWith('Conversation info')) {
    const blocks = [...text.matchAll(/```/g)];
    if (blocks.length >= 2) {
      const lastClose = (blocks[blocks.length - 1] as any).index + 3;
      text = text.slice(lastClose).trim();
    }
    if (!text) return null;
  }

  // Strip Telegram group prefix: [Telegram X id:Y ...] Name (id): message
  const tgMatch = text.match(/^\[Telegram \S+ id:\S+[^\]]*\]\s*.+?\(\d+\):\s*([\s\S]*)/);
  if (tgMatch) {
    text = tgMatch[1].trim();
  }

  // Strip [message_id: ...]
  text = text.replace(/\[message_id:\s*\d+\]/g, '').trim();

  // Strip [Replying to ...]
  text = text.replace(/\[Replying to \S+ id:\d+\]\s*/g, '').trim();

  // Remove blank lines
  text = text.split('\n').filter(l => l.trim()).join('\n');

  return text || null;
}

// PATCH /api/contacts/:id — update notes/role/plan
router.patch('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const { notes, role, plan } = req.body;

    const updates: string[] = [];
    const params: any[] = [];
    let idx = 1;

    if (notes !== undefined) {
      updates.push(`notes = $${idx}`);
      params.push(notes);
      idx++;
    }
    if (role !== undefined) {
      updates.push(`role = $${idx}`);
      params.push(role);
      idx++;
    }
    if (plan !== undefined) {
      updates.push(`plan = $${idx}`);
      params.push(plan);
      idx++;
    }

    if (updates.length === 0) {
      res.status(400).json({ error: 'No fields to update' });
      return;
    }

    updates.push('updated_at = NOW()');
    params.push(id);

    const result = await pool.query(
      `UPDATE crm_contacts SET ${updates.join(', ')} WHERE id = $${idx} RETURNING *`,
      params
    );

    if (result.rows.length === 0) {
      res.status(404).json({ error: 'Contact not found' });
      return;
    }

    res.json({ contact: result.rows[0] });
  } catch (err: any) {
    console.error('Contact update error:', err);
    res.status(500).json({ error: err.message });
  }
});

export default router;