// apps/web/src/server/db/queries/chat-threads.ts
import { and, desc, eq, inArray, ne, sql } from 'drizzle-orm';
import { alias } from 'drizzle-orm/pg-core';
import type { DrizzleClient, TxDrizzleClient } from '@/server/db/client.js';
import {
  chatThreads,
  chatThreadParticipants,
  users,
  vendors,
  type ChatThreadRow,
} from '@/server/db/schema.js';

export async function createThread(
  db: DrizzleClient,
  args: { createdBy: string; title?: string | null; kind?: 'dm' | 'support' | 'group' },
): Promise<ChatThreadRow> {
  const [row] = await db
    .insert(chatThreads)
    .values({
      createdBy: args.createdBy,
      title: args.title ?? null,
      kind: args.kind ?? 'dm',
    })
    .returning();
  return row!;
}

export class IneligibleChatParticipantsError extends Error {
  constructor() {
    super('One or more chat participants are ineligible');
  }
}

export async function createThreadWithParticipants(
  db: TxDrizzleClient,
  args: { createdBy: string; title?: string | null; kind?: 'dm' | 'support' | 'group' },
  userIds: string[],
): Promise<ChatThreadRow> {
  return db.transaction(async (tx) => {
    const uniqueUserIds = Array.from(new Set(userIds));
    const eligibleUsers = await tx
      .select({ id: users.id })
      .from(users)
      .where(and(inArray(users.id, uniqueUserIds), eq(users.accountState, 'ACTIVE')));
    if (eligibleUsers.length !== uniqueUserIds.length) {
      throw new IneligibleChatParticipantsError();
    }
    const thread = await createThread(tx, args);
    await tx
      .insert(chatThreadParticipants)
      .values(uniqueUserIds.map((userId) => ({ threadId: thread.id, userId })));
    return thread;
  });
}

export async function listThreadsForUser(
  db: DrizzleClient,
  userId: string,
  opts: { limit?: number } = {},
): Promise<ChatThreadRow[]> {
  const limit = Math.min(Math.max(opts.limit ?? 50, 1), 100);
  const participantRows = await db
    .select({ threadId: chatThreadParticipants.threadId })
    .from(chatThreadParticipants)
    .where(eq(chatThreadParticipants.userId, userId));
  const ids = participantRows.map((r) => r.threadId);
  if (ids.length === 0) return [];
  return db
    .select()
    .from(chatThreads)
    .where(inArray(chatThreads.id, ids))
    .orderBy(desc(chatThreads.lastMessageAt))
    .limit(limit);
}

export interface EnrichedChatThread {
  id: string;
  title: string | null;
  kind: string;
  lastMessageAt: Date | null;
  lastMessagePreview: string | null;
  counterpartyName: string | null;
  createdAt: Date;
}

export async function listEnrichedThreadsForUser(
  db: DrizzleClient,
  userId: string,
  opts: { limit?: number } = {},
): Promise<EnrichedChatThread[]> {
  const limit = Math.min(Math.max(opts.limit ?? 50, 1), 100);
  const ctpMe = alias(chatThreadParticipants, 'ctp_me');
  const ctpOther = alias(chatThreadParticipants, 'ctp_other');

  const rows = await db
    .select({
      id: chatThreads.id,
      title: chatThreads.title,
      kind: chatThreads.kind,
      lastMessageAt: chatThreads.lastMessageAt,
      lastMessagePreview: chatThreads.lastMessagePreview,
      createdAt: chatThreads.createdAt,
      counterpartyName: sql<string | null>`COALESCE(${vendors.displayName}, ${users.displayName})`,
    })
    .from(chatThreads)
    .innerJoin(ctpMe, and(eq(ctpMe.threadId, chatThreads.id), eq(ctpMe.userId, userId)))
    .leftJoin(ctpOther, and(eq(ctpOther.threadId, chatThreads.id), ne(ctpOther.userId, userId)))
    .leftJoin(users, eq(users.id, ctpOther.userId))
    .leftJoin(vendors, eq(vendors.ownerUserId, users.id))
    .orderBy(sql`${chatThreads.lastMessageAt} DESC NULLS LAST`)
    .limit(limit);

  const sorted = [...rows].sort((a, b) => {
    if (a.id !== b.id) return 0;
    if (a.counterpartyName && !b.counterpartyName) return -1;
    if (!a.counterpartyName && b.counterpartyName) return 1;
    return 0;
  });
  const seen = new Set<string>();
  return sorted.filter((r) => !seen.has(r.id) && seen.add(r.id));
}

export async function bumpLastMessage(
  db: DrizzleClient,
  threadId: string,
  at: Date,
  preview?: string,
): Promise<void> {
  await db
    .update(chatThreads)
    .set({
      lastMessageAt: at,
      ...(preview !== undefined ? { lastMessagePreview: preview.slice(0, 200) } : {}),
    })
    .where(eq(chatThreads.id, threadId));
}

export async function getThreadIfMember(
  db: DrizzleClient,
  threadId: string,
  userId: string,
): Promise<ChatThreadRow | null> {
  const [row] = await db
    .select({ thread: chatThreads })
    .from(chatThreads)
    .innerJoin(
      chatThreadParticipants,
      and(
        eq(chatThreadParticipants.threadId, chatThreads.id),
        eq(chatThreadParticipants.userId, userId),
      ),
    )
    .where(eq(chatThreads.id, threadId))
    .limit(1);
  return row?.thread ?? null;
}