/**
 * Telegram bot query helpers — telegram-bot (wave-8 leaf9).
 *
 * All helpers are tenant-scoped: every statement carries a tenant_id WHERE clause.
 * Routes MUST NOT import raw Drizzle tables — they call these helpers.
 */
import { and, eq } from 'drizzle-orm'
import type { Db } from '../client'
import { telegramChats } from '../schema/telegram'

// ── Domain object types ───────────────────────────────────────────────────────

export interface TelegramChatObject {
  id: string
  tenant_id: string
  chat_id: string
  chat_type: string
  chat_title: string | null
  linked_user_id: string | null
  is_active: boolean
  created_at: string
}

// ── Serializer ────────────────────────────────────────────────────────────────

function serialize(row: typeof telegramChats.$inferSelect): TelegramChatObject {
  return {
    id: row.id,
    tenant_id: row.tenantId,
    chat_id: row.chatId,
    chat_type: row.chatType,
    chat_title: row.chatTitle ?? null,
    linked_user_id: row.linkedUserId ?? null,
    is_active: row.isActive ?? true,
    created_at: row.createdAt.toISOString(),
  }
}

// ── getTelegramChats ──────────────────────────────────────────────────────────

export async function getTelegramChats(
  db: Db,
  tenantId: string,
): Promise<TelegramChatObject[]> {
  const rows = await db
    .select()
    .from(telegramChats)
    .where(eq(telegramChats.tenantId, tenantId))
    .orderBy(telegramChats.createdAt)
  return rows.map(serialize)
}

// ── linkTelegramChat ──────────────────────────────────────────────────────────

/**
 * Insert or reactivate a Telegram chat link for a tenant.
 * Uses ON CONFLICT DO UPDATE to handle re-linking a previously unlinked chat.
 */
export async function linkTelegramChat(
  db: Db,
  tenantId: string,
  chatId: string,
  chatType: string,
  chatTitle: string | null,
  userId: string | null,
): Promise<TelegramChatObject> {
  const [row] = await db
    .insert(telegramChats)
    .values({
      tenantId,
      chatId,
      chatType,
      chatTitle,
      linkedUserId: userId,
      isActive: true,
    })
    .onConflictDoUpdate({
      target: [telegramChats.tenantId, telegramChats.chatId],
      set: {
        chatType,
        chatTitle,
        linkedUserId: userId,
        isActive: true,
      },
    })
    .returning()
  return serialize(row!)
}

// ── unlinkTelegramChat ────────────────────────────────────────────────────────

/**
 * Soft-deactivate a Telegram chat link (sets is_active = false).
 * Returns true if found and updated.
 */
export async function unlinkTelegramChat(
  db: Db,
  tenantId: string,
  chatId: string,
): Promise<boolean> {
  const result = await db
    .update(telegramChats)
    .set({ isActive: false })
    .where(and(eq(telegramChats.tenantId, tenantId), eq(telegramChats.chatId, chatId)))
    .returning({ id: telegramChats.id })
  return result.length > 0
}

// ── getTelegramChatByChatId ───────────────────────────────────────────────────

export async function getTelegramChatByChatId(
  db: Db,
  chatId: string,
): Promise<TelegramChatObject | null> {
  const [row] = await db
    .select()
    .from(telegramChats)
    .where(and(eq(telegramChats.chatId, chatId), eq(telegramChats.isActive, true)))
    .limit(1)
  return row ? serialize(row) : null
}