import { getDb } from '../client'
import { channelBindings } from '../schema'
import { eq, and, asc, desc, sql } from 'drizzle-orm'
import { nowExpr } from '../shared/dialect-helpers'

/**
 * Channel Binding Repository。
 *
 * 封装 channel_bindings 表的所有查询。binding 表示 (channel_id, chat_id) → session 的映射，
 * 由 channel-router 在消息到来时解析/upsert。无独立删除——其生命周期跟随 im_channel，
 * 由 imChannelRepo.deleteById 事务级联清理。
 *
 * 设计约定：
 * - (channel_id, chat_id) 有唯一索引，upsert 用 findByIdByChannelAndChat + update/insert 组合
 * - 返回类型由 drizzle 推断，消费方无需 cast
 * - UPDATE 显式刷新 updated_at 用 nowExpr()
 */

export const channelBindingRepo = {
  /** 按 channel + chat 查完整 binding（channel-router 解析 session 用） */
  findByChannelAndChat(channelId: string, chatId: string) {
    return getDb()
      .select({
        id: channelBindings.id,
        sessionId: channelBindings.sessionId,
        workspaceId: channelBindings.workspaceId,
        workspace: channelBindings.workspace,
      })
      .from(channelBindings)
      .where(and(eq(channelBindings.channelId, channelId), eq(channelBindings.chatId, chatId)))
      .get()
  },

  /** 按 channel + chat 仅查 id（upsert 前判重用） */
  findIdByChannelAndChat(channelId: string, chatId: string) {
    return getDb()
      .select({ id: channelBindings.id })
      .from(channelBindings)
      .where(and(eq(channelBindings.channelId, channelId), eq(channelBindings.chatId, chatId)))
      .get()
  },

  /** 按 channel + chat 查 id + workspace 信息（/new 命令复用 workspace） */
  findWorkspaceByChannelAndChat(channelId: string, chatId: string) {
    return getDb()
      .select({
        id: channelBindings.id,
        workspaceId: channelBindings.workspaceId,
        workspace: channelBindings.workspace,
      })
      .from(channelBindings)
      .where(and(eq(channelBindings.channelId, channelId), eq(channelBindings.chatId, chatId)))
      .get()
  },

  /** 按 channel + chat 查 session + workspace 信息（/status 命令） */
  findSessionByChannelAndChat(channelId: string, chatId: string) {
    return getDb()
      .select({
        sessionId: channelBindings.sessionId,
        workspaceId: channelBindings.workspaceId,
        workspace: channelBindings.workspace,
      })
      .from(channelBindings)
      .where(and(eq(channelBindings.channelId, channelId), eq(channelBindings.chatId, chatId)))
      .get()
  },

  /** 列出 channel 绑定的全部 chat_id，按 created_at 倒序（cron 通知找目标 chat） */
  listChatIdsByChannel(channelId: string) {
    return getDb()
      .select({ chatId: channelBindings.chatId })
      .from(channelBindings)
      .where(eq(channelBindings.channelId, channelId))
      .orderBy(desc(channelBindings.createdAt))
      .all()
  },

  /** 按 workspace_id 列出 bindings（workspace 维度查询） */
  listByWorkspaceId(workspaceId: string) {
    return getDb()
      .select()
      .from(channelBindings)
      .where(eq(channelBindings.workspaceId, workspaceId))
      .orderBy(asc(channelBindings.createdAt))
      .all()
  },

  /** 创建 binding（channel-router auto-bind / /bind 命令） */
  create(data: {
    id: string
    channelId: string
    chatId: string
    workspace: string
    workspaceId: string
    sessionId?: string | null
    chatName?: string
  }) {
    getDb()
      .insert(channelBindings)
      .values({
        id: data.id,
        channelId: data.channelId,
        chatId: data.chatId,
        chatName: data.chatName ?? '',
        workspace: data.workspace,
        workspaceId: data.workspaceId,
        sessionId: data.sessionId ?? null,
      })
      .run()
  },

  /** 更新 session_id + workspace + workspace_id（绑定到具体 session） */
  updateSessionAndWorkspace(
    id: string,
    sessionId: string,
    workspace: string,
    workspaceId: string,
  ) {
    getDb()
      .update(channelBindings)
      .set({ sessionId, workspace, workspaceId, updatedAt: nowExpr() })
      .where(eq(channelBindings.id, id))
      .run()
  },

  /** 仅更新 session_id + workspace_id（保留 workspace，legacy binding 升级） */
  updateSession(id: string, sessionId: string, workspaceId: string) {
    getDb()
      .update(channelBindings)
      .set({ sessionId, workspaceId, updatedAt: nowExpr() })
      .where(eq(channelBindings.id, id))
      .run()
  },

  /**
   * 重置 channel 下所有 binding 的 workspace（PATCH default_workspace_id 触发）。
   * session_id 置 NULL，强制下次消息重建 session。
   */
  resetWorkspaceByChannel(channelId: string, workspace: string, workspaceId: string) {
    getDb()
      .update(channelBindings)
      .set({ workspace, workspaceId, sessionId: null, updatedAt: nowExpr() })
      .where(eq(channelBindings.channelId, channelId))
      .run()
  },

  /** 删除 channel + chat 的 binding（/unbind 命令） */
  deleteByChannelAndChat(channelId: string, chatId: string) {
    getDb()
      .delete(channelBindings)
      .where(and(eq(channelBindings.channelId, channelId), eq(channelBindings.chatId, chatId)))
      .run()
  },

  /** 删除 channel 的全部 binding（级联清理用，通常由 imChannelRepo.deleteById 事务调用） */
  deleteByChannel(channelId: string) {
    getDb().delete(channelBindings).where(eq(channelBindings.channelId, channelId)).run()
  },

  /** 统计 channel 的 binding 数量 */
  countByChannel(channelId: string): number {
    const row = getDb()
      .select({ c: sql<number>`count(*)` })
      .from(channelBindings)
      .where(eq(channelBindings.channelId, channelId))
      .get()
    return row?.c ?? 0
  },
}