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

/**
 * IM Channel Repository。
 *
 * 封装 im_channels 表的所有查询。删除操作使用 db.transaction 显式级联清理：
 * weixin_context_tokens / weixin_sync_cursors / im_pairing_requests /
 * channel_bindings / channel_permission_links（原 schema 无 FK 约束）。
 *
 * 设计约定：
 * - 返回类型由 drizzle 推断，消费方无需 cast
 * - SELECT * 用 .select().from(imChannels)，JOIN 查询显式列出列以隔离绑定表
 * - UPDATE 显式刷新 updated_at 用 nowExpr()
 */

/** im_channels 全列 selection（JOIN 查询复用，避免混入 binding 列） */
const imChannelColumns = {
  id: imChannels.id,
  type: imChannels.type,
  name: imChannels.name,
  enabled: imChannels.enabled,
  status: imChannels.status,
  credentials: imChannels.credentials,
  defaultWorkspaceId: imChannels.defaultWorkspaceId,
  defaultModel: imChannels.defaultModel,
  dmPolicy: imChannels.dmPolicy,
  groupPolicy: imChannels.groupPolicy,
  triggerMode: imChannels.triggerMode,
  groupWhitelist: imChannels.groupWhitelist,
  senderWhitelist: imChannels.senderWhitelist,
  ownerUserId: imChannels.ownerUserId,
  createdAt: imChannels.createdAt,
  updatedAt: imChannels.updatedAt,
}

export const imChannelRepo = {
  /** 按 id 查（不限 owner，用于内部流程如 bridge-manager） */
  findById(id: string) {
    return getDb().select().from(imChannels).where(eq(imChannels.id, id)).get()
  },

  /** 按 id + owner 查（route 层鉴权用） */
  findByIdAndOwner(id: string, ownerUserId: string) {
    return getDb()
      .select()
      .from(imChannels)
      .where(and(eq(imChannels.id, id), eq(imChannels.ownerUserId, ownerUserId)))
      .get()
  },

  /** 列出 owner 的全部 channel，按 type/created_at/name/id 排序 */
  listByOwner(ownerUserId: string) {
    return getDb()
      .select(imChannelColumns)
      .from(imChannels)
      .where(eq(imChannels.ownerUserId, ownerUserId))
      .orderBy(asc(imChannels.type), asc(imChannels.createdAt), asc(imChannels.name), asc(imChannels.id))
      .all()
  },

  /**
   * 列出 owner 的 connected channel，过滤条件：
   * 存在 channel_binding 指向该 workspace，或 channel 默认 workspace 为该值。
   * DISTINCT 防止多 binding 导致重复。
   */
  listConnectedByOwnerAndWorkspace(ownerUserId: string, workspaceId: string) {
    return getDb()
      .selectDistinct(imChannelColumns)
      .from(imChannels)
      .leftJoin(channelBindings, eq(imChannels.id, channelBindings.channelId))
      .where(
        and(
          eq(imChannels.ownerUserId, ownerUserId),
          eq(imChannels.status, 'connected'),
          or(eq(channelBindings.workspaceId, workspaceId), eq(imChannels.defaultWorkspaceId, workspaceId)),
        ),
      )
      .orderBy(asc(imChannels.type), asc(imChannels.createdAt), asc(imChannels.name), asc(imChannels.id))
      .all()
  },

  /** 按 type + owner 统计 channel 数量（用于生成默认 Bot 名称） */
  countByTypeAndOwner(
    type: 'feishu' | 'telegram' | 'discord' | 'weixin' | 'dingtalk' | 'wecom',
    ownerUserId: string,
  ): number {
    const row = getDb()
      .select({ c: sql<number>`count(*)` })
      .from(imChannels)
      .where(and(eq(imChannels.type, type), eq(imChannels.ownerUserId, ownerUserId)))
      .get()
    return row?.c ?? 0
  },

  /** 创建 channel（默认 enabled=1, status='not_configured', credentials='{}'） */
  create(data: {
    id: string
    type: 'feishu' | 'telegram' | 'discord' | 'weixin' | 'dingtalk' | 'wecom'
    name: string
    ownerUserId: string
  }) {
    getDb()
      .insert(imChannels)
      .values({
        id: data.id,
        type: data.type,
        name: data.name,
        enabled: 1,
        status: 'not_configured',
        credentials: '{}',
        ownerUserId: data.ownerUserId,
      })
      .run()
    return this.findById(data.id)
  },

  /**
   * 更新指定字段并校验 owner，刷新 updated_at。
   * fields 只允许传入 schema 列（类型安全），消费方按需构造。
   */
  updateFieldsAndOwner(
    id: string,
    ownerUserId: string,
    fields: Partial<typeof imChannels.$inferInsert>,
  ) {
    getDb()
      .update(imChannels)
      .set({ ...fields, updatedAt: nowExpr() })
      .where(and(eq(imChannels.id, id), eq(imChannels.ownerUserId, ownerUserId)))
      .run()
    return this.findByIdAndOwner(id, ownerUserId)
  },

  /** 更新 status（connected/disconnected/error/not_configured），刷新 updated_at */
  updateStatus(
    id: string,
    status: 'connected' | 'disconnected' | 'not_configured' | 'error',
  ) {
    getDb()
      .update(imChannels)
      .set({ status, updatedAt: nowExpr() })
      .where(eq(imChannels.id, id))
      .run()
  },

  /** 更新 sender_whitelist（pairing 审批通过后追加） */
  updateSenderWhitelist(id: string, senderWhitelist: string) {
    getDb()
      .update(imChannels)
      .set({ senderWhitelist })
      .where(eq(imChannels.id, id))
      .run()
  },

  /** 取 type + credentials（bridge-manager 启动适配器用） */
  findTypeAndCredentials(id: string) {
    return getDb()
      .select({ type: imChannels.type, credentials: imChannels.credentials })
      .from(imChannels)
      .where(eq(imChannels.id, id))
      .get()
  },

  /** 取 credentials（feishu card-config 读取） */
  findCredentials(id: string) {
    return getDb()
      .select({ credentials: imChannels.credentials })
      .from(imChannels)
      .where(eq(imChannels.id, id))
      .get()
  },

  /** 取 owner_user_id（channel-router 鉴权） */
  findOwnerUserId(id: string) {
    return getDb()
      .select({ ownerUserId: imChannels.ownerUserId })
      .from(imChannels)
      .where(eq(imChannels.id, id))
      .get()
  },

  /** 取 default_workspace_id（channel-router 解析可写 workspace） */
  findDefaultWorkspaceId(id: string) {
    return getDb()
      .select({ defaultWorkspaceId: imChannels.defaultWorkspaceId })
      .from(imChannels)
      .where(eq(imChannels.id, id))
      .get()
  },

  /** 取 default_model（channel-router 模型回退） */
  findDefaultModel(id: string) {
    return getDb()
      .select({ defaultModel: imChannels.defaultModel })
      .from(imChannels)
      .where(eq(imChannels.id, id))
      .get()
  },

  /** 取 default_workspace_id + owner（PATCH 鉴权） */
  findDefaultWorkspaceIdAndOwner(id: string, ownerUserId: string) {
    return getDb()
      .select({ defaultWorkspaceId: imChannels.defaultWorkspaceId })
      .from(imChannels)
      .where(and(eq(imChannels.id, id), eq(imChannels.ownerUserId, ownerUserId)))
      .get()
  },

  /** 列出 enabled=1 且 status != 'not_configured' 的 channel（启动时自动重连） */
  listEnabledConfigured() {
    return getDb()
      .select({ id: imChannels.id, type: imChannels.type })
      .from(imChannels)
      .where(and(eq(imChannels.enabled, 1), sql`${imChannels.status} != 'not_configured'`))
      .all()
  },

  /**
   * 删除 channel：事务级联清理子表（替代原 FK ON DELETE CASCADE）。
   * 按 channel_id 清理 weixin_context_tokens / weixin_sync_cursors /
   * im_pairing_requests / channel_bindings / channel_permission_links，
   * 最后删 im_channels 本身。不限 owner（内部清理用）。
   */
  deleteById(id: string) {
    getDb().transaction((tx) => {
      tx.delete(weixinContextTokens).where(eq(weixinContextTokens.channelId, id)).run()
      tx.delete(weixinSyncCursors).where(eq(weixinSyncCursors.channelId, id)).run()
      tx.delete(imPairingRequests).where(eq(imPairingRequests.channelId, id)).run()
      tx.delete(channelBindings).where(eq(channelBindings.channelId, id)).run()
      tx.delete(channelPermissionLinks).where(eq(channelPermissionLinks.channelId, id)).run()
      tx.delete(imChannels).where(eq(imChannels.id, id)).run()
    })
  },

  /**
   * 删除 channel（校验 owner）：与 deleteById 相同级联，但 im_channels 删除带 owner 条件。
   * 子表仍按 channel_id 无条件清理（owner 校验在父表即可阻断）。
   */
  deleteByIdAndOwner(id: string, ownerUserId: string) {
    getDb().transaction((tx) => {
      tx.delete(weixinContextTokens).where(eq(weixinContextTokens.channelId, id)).run()
      tx.delete(weixinSyncCursors).where(eq(weixinSyncCursors.channelId, id)).run()
      tx.delete(imPairingRequests).where(eq(imPairingRequests.channelId, id)).run()
      tx.delete(channelBindings).where(eq(channelBindings.channelId, id)).run()
      tx.delete(channelPermissionLinks).where(eq(channelPermissionLinks.channelId, id)).run()
      tx.delete(imChannels)
        .where(and(eq(imChannels.id, id), eq(imChannels.ownerUserId, ownerUserId)))
        .run()
    })
  },
}