import pool from '../db';
import { ParsedMessage, SessionIndex } from './parser';
import * as fs from 'fs';
import * as path from 'path';

// Extract telegram ID and display info from session origin
function parseOrigin(origin: SessionIndex['origin']): {
  telegramId: bigint | null;
  username: string | null;
  displayName: string | null;
  groupId: bigint | null;
  groupName: string | null;
} {
  let telegramId: bigint | null = null;
  let username: string | null = null;
  let displayName: string | null = null;
  let groupId: bigint | null = null;
  let groupName: string | null = null;

  // Parse label like "Danny (@dan955nyse) id:8498231383" or "BotFather id:-1003819529858"
  const label = origin.label || '';
  const idMatch = label.match(/id:(-?\d+)/);
  const usernameMatch = label.match(/@(\w+)/);

  // Parse from field like "telegram:8498231383"
  const fromMatch = origin.from?.match(/telegram:(\d+)/);

  if (origin.chatType === 'group' && idMatch) {
    groupId = BigInt(idMatch[1]);
    groupName = label.replace(/\s*id:-?\d+/, '').trim() || null;
  } else {
    if (fromMatch) {
      telegramId = BigInt(fromMatch[1]);
    } else if (idMatch) {
      const id = BigInt(idMatch[1]);
      if (id > 0) telegramId = id;
    }
  }

  if (usernameMatch) username = usernameMatch[1];

  // Display name is the text before the parenthesized username
  const nameMatch = label.match(/^([^(@]+)/);
  if (nameMatch) {
    displayName = nameMatch[1].trim().replace(/\s*id:\d+/, '').trim() || null;
  }

  return { telegramId, username, displayName, groupId, groupName };
}

// Extract telegram user info from message text header
// Format: "[Telegram BotFather id:-1003819529858 2026-02-07 21:06 UTC] R B (5619415559): message"
function parseMessageUser(content: string): {
  telegramId: bigint | null;
  displayName: string | null;
} {
  const match = content.match(/\[Telegram[^\]]*\]\s*(.+?)\s*\((\d+)\):/);
  if (match) {
    return {
      displayName: match[1].trim(),
      telegramId: BigInt(match[2]),
    };
  }
  return { telegramId: null, displayName: null };
}

export async function upsertContact(
  telegramId: bigint,
  username: string | null,
  displayName: string | null,
  timestamp: Date
): Promise<string> {
  const result = await pool.query(
    `INSERT INTO crm_contacts (telegram_id, username, display_name, first_seen, last_active, updated_at)
     VALUES ($1, $2, $3, $4, $4, NOW())
     ON CONFLICT (telegram_id) DO UPDATE SET
       username = COALESCE(EXCLUDED.username, crm_contacts.username),
       display_name = COALESCE(EXCLUDED.display_name, crm_contacts.display_name),
       last_active = GREATEST(crm_contacts.last_active, EXCLUDED.last_active),
       updated_at = NOW()
     RETURNING id`,
    [telegramId.toString(), username, displayName, timestamp]
  );
  return result.rows[0].id;
}

export async function upsertSession(
  sessionId: string,
  agent: string,
  contactId: string | null,
  sessionInfo: SessionIndex,
  sourceFile: string,
  messageCount: number,
  startedAt: Date,
  lastActivity: Date,
  groupId: bigint | null,
  groupName: string | null
): Promise<void> {
  await pool.query(
    `INSERT INTO crm_sessions (id, agent, contact_id, chat_type, channel, group_id, group_name,
       started_at, last_activity, message_count, total_tokens, input_tokens, output_tokens,
       model, source_file)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
     ON CONFLICT (id) DO UPDATE SET
       last_activity = GREATEST(crm_sessions.last_activity, EXCLUDED.last_activity),
       message_count = EXCLUDED.message_count,
       total_tokens = EXCLUDED.total_tokens,
       input_tokens = EXCLUDED.input_tokens,
       output_tokens = EXCLUDED.output_tokens,
       model = COALESCE(EXCLUDED.model, crm_sessions.model)`,
    [
      sessionId, agent, contactId, sessionInfo.chatType, sessionInfo.channel,
      groupId?.toString() || null, groupName,
      startedAt, lastActivity, messageCount,
      sessionInfo.totalTokens, sessionInfo.inputTokens, sessionInfo.outputTokens,
      sessionInfo.model, sourceFile,
    ]
  );
}

export async function importMessages(
  messages: ParsedMessage[],
  sessionId: string,
  contactId: string | null
): Promise<number> {
  if (messages.length === 0) return 0;

  let imported = 0;
  // Batch insert in chunks of 100
  const chunkSize = 100;
  for (let i = 0; i < messages.length; i += chunkSize) {
    const chunk = messages.slice(i, i + chunkSize);

    const values: any[] = [];
    const placeholders: string[] = [];
    let paramIdx = 1;

    for (const msg of chunk) {
      // Determine contact_id: for user messages in groups, try to parse from content
      let msgContactId = contactId;
      if (msg.role === 'user' && msg.content) {
        const parsed = parseMessageUser(msg.content);
        if (parsed.telegramId && parsed.telegramId !== BigInt(0)) {
          try {
            msgContactId = await upsertContact(
              parsed.telegramId,
              null,
              parsed.displayName,
              new Date(msg.timestamp)
            );
          } catch {
            // Keep existing contactId
          }
        }
      }

      placeholders.push(
        `($${paramIdx}, $${paramIdx + 1}, $${paramIdx + 2}, $${paramIdx + 3}, $${paramIdx + 4}, $${paramIdx + 5}, $${paramIdx + 6}, $${paramIdx + 7}, $${paramIdx + 8})`
      );
      values.push(
        msg.id,
        sessionId,
        msgContactId,
        msg.role,
        msg.content,
        msg.contentType,
        msg.toolName,
        msg.totalTokens,
        msg.timestamp
      );
      paramIdx += 9;
    }

    const query = `INSERT INTO crm_messages (id, session_id, contact_id, role, content, content_type, tool_name, tokens, timestamp)
      VALUES ${placeholders.join(', ')}
      ON CONFLICT (id) DO NOTHING`;

    try {
      const result = await pool.query(query, values);
      imported += result.rowCount || 0;
    } catch (err: any) {
      console.error(`Failed to import chunk at offset ${i}:`, err.message);
    }
  }

  return imported;
}

export async function getSyncState(sourceFile: string): Promise<{ lastLine: number; lastByte: number } | null> {
  const result = await pool.query(
    'SELECT last_line, last_byte FROM crm_sync_state WHERE source_file = $1',
    [sourceFile]
  );
  if (result.rows.length === 0) return null;
  return { lastLine: result.rows[0].last_line, lastByte: Number(result.rows[0].last_byte) };
}

export async function updateSyncState(
  sourceFile: string,
  agent: string,
  lastLine: number,
  lastByte: number
): Promise<void> {
  await pool.query(
    `INSERT INTO crm_sync_state (source_file, agent, last_line, last_byte, last_synced)
     VALUES ($1, $2, $3, $4, NOW())
     ON CONFLICT (source_file) DO UPDATE SET
       last_line = EXCLUDED.last_line,
       last_byte = EXCLUDED.last_byte,
       last_synced = NOW()`,
    [sourceFile, agent, lastLine, lastByte]
  );
}

export async function importUserMemory(agent: string, memoryDir: string): Promise<void> {
  if (!fs.existsSync(memoryDir)) return;

  const files = fs.readdirSync(memoryDir).filter(f => f.match(/^\d+\.md$/));
  for (const file of files) {
    const telegramId = BigInt(path.basename(file, '.md'));
    const content = fs.readFileSync(path.join(memoryDir, file), 'utf-8');

    await pool.query(
      `UPDATE crm_contacts SET notes = $1, updated_at = NOW()
       WHERE telegram_id = $2`,
      [content, telegramId.toString()]
    );
  }
}

// Link crm_contacts to sc_users by matching telegram_chat_id
export async function linkScUsers(): Promise<number> {
  const result = await pool.query(
    `UPDATE crm_contacts c
     SET sc_user_id = u.id, plan = u.plan, updated_at = NOW()
     FROM sc_users u
     WHERE u.telegram_chat_id = c.telegram_id
       AND c.sc_user_id IS NULL`
  );
  return result.rowCount || 0;
}