import type { Db } from './sqlite.js';

export interface StoredMessage {
  id: number;
  message_id: number;
  chat_id: number;
  user_id: number | null;
  username: string | null;
  first_name: string | null;
  text: string;
  date: number; // Unix timestamp
}

export interface MessageQuery {
  chatId: number;
  fromDate?: number; // Unix timestamp
  toDate?: number; // Unix timestamp
  userFilter?: string; // matches username or first_name (case-insensitive)
  limit?: number;
}

const MAX_LIMIT = 1000;

// LIMIT cannot be a bound parameter in these statements, so the value is interpolated.
// Callers derive it from LLM output, so anything not a plain positive integer is discarded.
function limitClause(limit: unknown, fallback: number | null): string {
  const n = typeof limit === 'number' ? limit : Number.NaN;
  if (Number.isInteger(n) && n > 0) return `LIMIT ${Math.min(n, MAX_LIMIT)}`;
  return fallback === null ? '' : `LIMIT ${fallback}`;
}

export function insertMessage(db: Db, msg: Omit<StoredMessage, 'id'>): void {
  db.prepare(
    `
    INSERT INTO messages (message_id, chat_id, user_id, username, first_name, text, date)
    VALUES (@message_id, @chat_id, @user_id, @username, @first_name, @text, @date)
  `,
  ).run(msg);
}

export interface SearchQuery {
  chatId: number;
  keywords: string[]; // searched with LIKE (OR across keywords)
  userFilter?: string;
  fromDate?: number;
  limit?: number;
}

export function searchMessages(db: Db, q: SearchQuery): StoredMessage[] {
  if (q.keywords.length === 0) return [];

  const conditions: string[] = ['chat_id = @chatId'];
  const params: Record<string, unknown> = { chatId: q.chatId };

  // OR across all keywords
  const kwConditions = q.keywords.map((kw, i) => {
    params[`kw${i}`] = `%${kw}%`;
    return `text LIKE @kw${i}`;
  });
  conditions.push(`(${kwConditions.join(' OR ')})`);

  if (q.userFilter) {
    conditions.push('(LOWER(username) = LOWER(@user) OR LOWER(first_name) = LOWER(@user))');
    params['user'] = q.userFilter;
  }
  if (q.fromDate !== undefined) {
    conditions.push('date >= @fromDate');
    params['fromDate'] = q.fromDate;
  }

  const sql = `
    SELECT * FROM messages
    WHERE ${conditions.join(' AND ')}
    ORDER BY date DESC
    ${limitClause(q.limit, 100)}
  `;

  return (db.prepare(sql).all(params) as StoredMessage[]).reverse();
}

export function queryMessages(db: Db, q: MessageQuery): StoredMessage[] {
  const conditions: string[] = ['chat_id = @chatId'];
  const params: Record<string, unknown> = { chatId: q.chatId };

  if (q.fromDate !== undefined) {
    conditions.push('date >= @fromDate');
    params['fromDate'] = q.fromDate;
  }
  if (q.toDate !== undefined) {
    conditions.push('date <= @toDate');
    params['toDate'] = q.toDate;
  }
  if (q.userFilter) {
    conditions.push('(LOWER(username) = LOWER(@user) OR LOWER(first_name) = LOWER(@user))');
    params['user'] = q.userFilter;
  }

  const sql = `
    SELECT * FROM messages
    WHERE ${conditions.join(' AND ')}
    ORDER BY date DESC
    ${limitClause(q.limit, null)}
  `;

  const rows = db.prepare(sql).all(params) as StoredMessage[];
  // Return in chronological order for summarization
  return rows.reverse();
}