/**
 * Message Service - Database-backed implementation using Drizzle ORM.
 *
 * Provides CRUD operations for agent chat messages with:
 * - Type-safe database queries
 * - Efficient indexed queries
 * - Consistent with AgentStoredMessage interface from shared types
 */
import { randomUUID } from 'node:crypto';
import { eq, asc, and, count } from 'drizzle-orm';
import type { AgentRole, AgentStoredMessage } from 'webpage-mcp-shared';
import { getDb, messages, type MessageRow } from './db';
import { sanitizeAgentMessageForPublicRead } from './public-message-sanitizer';

// ============================================================
// Types
// ============================================================

export type { AgentStoredMessage };

export interface CreateAgentStoredMessageInput {
  projectId: string;
  role: AgentRole;
  messageType: AgentStoredMessage['messageType'];
  content: string;
  metadata?: Record<string, unknown>;
  sessionId?: string;
  conversationId?: string | null;
  cliSource?: string;
  requestId?: string;
  id?: string;
  createdAt?: string;
  upsertById?: boolean;
}

// ============================================================
// Type Conversion
// ============================================================

/**
 * Convert database row to AgentStoredMessage interface.
 */
function rowToMessage(row: MessageRow): AgentStoredMessage {
  const metadata = row.metadata ? (JSON.parse(row.metadata) as Record<string, unknown>) : undefined;
  const sanitized = sanitizeAgentMessageForPublicRead({
    content: row.content,
    metadata,
  });
  return {
    id: row.id,
    projectId: row.projectId,
    sessionId: row.sessionId,
    conversationId: row.conversationId,
    role: row.role as AgentRole,
    content: sanitized.content,
    messageType: row.messageType as AgentStoredMessage['messageType'],
    metadata: sanitized.metadata,
    cliSource: row.cliSource,
    requestId: row.requestId ?? undefined,
    createdAt: row.createdAt,
  };
}

// ============================================================
// Public API
// ============================================================

/**
 * Get messages by project ID with pagination.
 * Returns messages sorted by creation time (oldest first).
 */
export async function getMessagesByProjectId(
  projectId: string,
  limit = 50,
  offset = 0,
): Promise<AgentStoredMessage[]> {
  const db = getDb();

  const query = db
    .select()
    .from(messages)
    .where(eq(messages.projectId, projectId))
    .orderBy(asc(messages.createdAt));

  // Apply pagination if specified
  if (limit > 0) {
    query.limit(limit);
  }
  if (offset > 0) {
    query.offset(offset);
  }

  const rows = await query;
  return rows.map(rowToMessage);
}

/**
 * Get the total count of messages for a project.
 */
export async function getMessagesCountByProjectId(projectId: string): Promise<number> {
  const db = getDb();
  const result = await db
    .select({ count: count() })
    .from(messages)
    .where(eq(messages.projectId, projectId));
  return result[0]?.count ?? 0;
}

/**
 * Create a new message.
 */
export async function createMessage(
  input: CreateAgentStoredMessageInput,
): Promise<AgentStoredMessage> {
  const db = getDb();
  const now = new Date().toISOString();

  const messageData: MessageRow = {
    id: input.id?.trim() || randomUUID(),
    projectId: input.projectId,
    sessionId: input.sessionId || '',
    conversationId: input.conversationId ?? null,
    role: input.role,
    content: input.content,
    messageType: input.messageType,
    metadata: input.metadata ? JSON.stringify(input.metadata) : null,
    cliSource: input.cliSource ?? null,
    requestId: input.requestId ?? null,
    createdAt: input.createdAt || now,
  };

  if (input.upsertById === true) {
    await db
      .insert(messages)
      .values(messageData)
      .onConflictDoUpdate({
        target: messages.id,
        set: {
          role: messageData.role,
          messageType: messageData.messageType,
          content: messageData.content,
          metadata: messageData.metadata,
          sessionId: messageData.sessionId,
          conversationId: messageData.conversationId,
          cliSource: messageData.cliSource,
          requestId: messageData.requestId,
        },
      });
  } else {
    await db.insert(messages).values(messageData);
  }

  return rowToMessage(messageData);
}

/**
 * Delete messages by project ID.
 * Optionally filter by conversation ID.
 * Returns the number of deleted messages.
 */
export async function deleteMessagesByProjectId(
  projectId: string,
  conversationId?: string,
): Promise<number> {
  const db = getDb();

  // Get count before deletion
  const beforeCount = await getMessagesCountByProjectId(projectId);

  if (conversationId) {
    await db
      .delete(messages)
      .where(and(eq(messages.projectId, projectId), eq(messages.conversationId, conversationId)));
  } else {
    await db.delete(messages).where(eq(messages.projectId, projectId));
  }

  // Get count after deletion to calculate deleted count
  const afterCount = await getMessagesCountByProjectId(projectId);
  return beforeCount - afterCount;
}

/**
 * Get messages by session ID with optional pagination.
 * Returns messages sorted by creation time (oldest first).
 *
 * @param sessionId - The session ID to filter by
 * @param limit - Maximum number of messages to return (0 = no limit)
 * @param offset - Number of messages to skip
 */
export async function getMessagesBySessionId(
  sessionId: string,
  limit = 0,
  offset = 0,
  projectId?: string,
): Promise<AgentStoredMessage[]> {
  const db = getDb();
  const filter = projectId
    ? and(eq(messages.sessionId, sessionId), eq(messages.projectId, projectId))
    : eq(messages.sessionId, sessionId);

  const query = db.select().from(messages).where(filter).orderBy(asc(messages.createdAt));

  if (limit > 0) {
    query.limit(limit);
  }
  if (offset > 0) {
    query.offset(offset);
  }

  const rows = await query;
  return rows.map(rowToMessage);
}

/**
 * Get count of messages by session ID.
 */
export async function getMessagesCountBySessionId(
  sessionId: string,
  projectId?: string,
): Promise<number> {
  const db = getDb();
  const filter = projectId
    ? and(eq(messages.sessionId, sessionId), eq(messages.projectId, projectId))
    : eq(messages.sessionId, sessionId);
  const result = await db
    .select({ count: count() })
    .from(messages)
    .where(filter);
  return result[0]?.count ?? 0;
}

/**
 * Delete all messages for a session.
 * Returns the number of deleted messages.
 */
export async function deleteMessagesBySessionId(
  sessionId: string,
  projectId?: string,
): Promise<number> {
  const db = getDb();
  const filter = projectId
    ? and(eq(messages.sessionId, sessionId), eq(messages.projectId, projectId))
    : eq(messages.sessionId, sessionId);

  const beforeCount = await getMessagesCountBySessionId(sessionId, projectId);
  await db.delete(messages).where(filter);
  const afterCount = await getMessagesCountBySessionId(sessionId, projectId);

  return beforeCount - afterCount;
}

/**
 * Get messages by request ID.
 */
export async function getMessagesByRequestId(requestId: string): Promise<AgentStoredMessage[]> {
  const db = getDb();
  const rows = await db
    .select()
    .from(messages)
    .where(eq(messages.requestId, requestId))
    .orderBy(asc(messages.createdAt));
  return rows.map(rowToMessage);
}
