import { getDb } from '../client'
import { sessions, messages } from '../schema'
import { eq, and, desc, asc, sql, not, lt, ne, isNotNull, isNull } from 'drizzle-orm'
import { nowExpr } from '../shared/dialect-helpers'

/**
 * Session Repository。
 * 封装 sessions 表的所有查询。messages 的级联删除由 deleteById 事务承担（无 FK）。
 */

export const sessionRepo = {
  /** 列出用户的所有 active 会话，按 updated_at 倒序 */
  listActiveByUser(userId: string, opts?: { extOnly?: boolean }) {
    const conditions = [eq(sessions.userId, userId), eq(sessions.status, 'active')]
    if (opts?.extOnly === true) {
      conditions.push(isNotNull(sessions.externalUserId))
    } else if (opts?.extOnly === false) {
      conditions.push(isNull(sessions.externalUserId))
    }
    return getDb()
      .select({
        id: sessions.id,
        title: sessions.title,
        workspace: sessions.workspace,
        model: sessions.model,
        permissionMode: sessions.permissionMode,
        status: sessions.status,
        externalUserId: sessions.externalUserId,
        createdAt: sessions.createdAt,
        updatedAt: sessions.updatedAt,
      })
      .from(sessions)
      .where(and(...conditions))
      .orderBy(desc(sessions.updatedAt))
      .all()
  },

  /** 按 id + user 查单个会话 */
  findByIdAndUser(id: string, userId: string) {
    return getDb()
      .select()
      .from(sessions)
      .where(and(eq(sessions.id, id), eq(sessions.userId, userId)))
      .get()
  },

  /** 按 id 查（不限 user，用于内部流程如 chat 引擎） */
  findById(id: string) {
    return getDb().select().from(sessions).where(eq(sessions.id, id)).get()
  },

  /** 创建会话 */
  create(data: {
    id: string
    title: string
    workspace: string
    model: string
    userId: string
    permissionMode?: string
    backend?: string
    externalUserId?: string | null
  }) {
    getDb()
      .insert(sessions)
      .values({
        id: data.id,
        title: data.title,
        workspace: data.workspace,
        model: data.model,
        userId: data.userId,
        permissionMode: data.permissionMode ?? '',
        backend: data.backend ?? 'claude',
        externalUserId: data.externalUserId ?? null,
      })
      .run()
    return this.findById(data.id)
  },

  /** 刷新 updated_at（轻量 touch） */
  touch(id: string) {
    getDb().update(sessions).set({ updatedAt: nowExpr() }).where(eq(sessions.id, id)).run()
  },

  /** 更新标题并刷新 updated_at */
  updateTitle(id: string, title: string) {
    getDb()
      .update(sessions)
      .set({ title, updatedAt: nowExpr() })
      .where(eq(sessions.id, id))
      .run()
  },
  /** 更新标题（带归属校验：仅 externalUserId + workspace 匹配时才更新，H5 重命名用） */
  updateTitleByIdAndExternalUser(id: string, title: string, externalUserId: string, workspace: string) {
    getDb()
      .update(sessions)
      .set({ title, updatedAt: nowExpr() })
      .where(
        and(
          eq(sessions.id, id),
          eq(sessions.externalUserId, externalUserId),
          eq(sessions.workspace, workspace),
        ),
      )
      .run()
  },

  /** 更新指定字段并刷新 updated_at */
  updateFields(id: string, fields: Partial<typeof sessions.$inferInsert>) {
    getDb()
      .update(sessions)
      .set({ ...fields, updatedAt: nowExpr() })
      .where(eq(sessions.id, id))
      .run()
    return this.findById(id)
  },

  /** 归档会话 */
  archive(id: string, userId: string) {
    getDb()
      .update(sessions)
      .set({ status: 'archived', updatedAt: nowExpr() })
      .where(and(eq(sessions.id, id), eq(sessions.userId, userId)))
      .run()
  },

  /** 删除会话：事务级联删 messages（替代原 FK CASCADE） */
  deleteByIdAndUser(id: string, userId: string) {
    getDb().transaction((tx) => {
      tx.delete(messages).where(eq(messages.sessionId, id)).run()
      tx.delete(sessions)
        .where(and(eq(sessions.id, id), eq(sessions.userId, userId)))
        .run()
    })
  },

  /** 删除外部用户会话：必须同时校验 externalUserId + workspace */
  deleteByIdAndExternalUser(id: string, externalUserId: string, workspace: string) {
    getDb().transaction((tx) => {
      tx.delete(messages).where(eq(messages.sessionId, id)).run()
      tx.delete(sessions)
        .where(
          and(
            eq(sessions.id, id),
            eq(sessions.externalUserId, externalUserId),
            eq(sessions.workspace, workspace),
          ),
        )
        .run()
    })
  },

  /** 删除会话（不限 user，内部清理用） */
  deleteById(id: string) {
    getDb().transaction((tx) => {
      tx.delete(messages).where(eq(messages.sessionId, id)).run()
      tx.delete(sessions).where(eq(sessions.id, id)).run()
    })
  },

  /** 列出 updated_at 早于 cutoff（ISO 字符串）的会话 id（用于过期清理） */
  listExpired(cutoff: string): { id: string }[] {
    return getDb().select({ id: sessions.id }).from(sessions).where(lt(sessions.updatedAt, cutoff)).all()
  },

  /** 删除指定 workspace + user 的所有会话（workspace 删除时的清理） */
  deleteByWorkspaceAndUser(workspace: string, userId: string) {
    getDb().transaction((tx) => {
      const ids = tx.select({ id: sessions.id }).from(sessions)
        .where(and(eq(sessions.workspace, workspace), eq(sessions.userId, userId))).all()
      for (const { id } of ids) {
        tx.delete(messages).where(eq(messages.sessionId, id)).run()
      }
      tx.delete(sessions)
        .where(and(eq(sessions.workspace, workspace), eq(sessions.userId, userId)))
        .run()
    })
  },

  /** 统计会话的消息数 */
  countMessages(sessionId: string): number {
    const row = getDb()
      .select({ c: sql<number>`count(*)` })
      .from(messages)
      .where(eq(messages.sessionId, sessionId))
      .get()
    return row?.c ?? 0
  },

  /** 列出非 active 会话（用于清理） */
  listInactiveByUser(userId: string) {
    return getDb()
      .select({ id: sessions.id, updatedAt: sessions.updatedAt })
      .from(sessions)
      .where(and(eq(sessions.userId, userId), not(eq(sessions.status, 'active'))))
      .all()
  },

  /** 列出指定 workspace 的会话 */
  listByWorkspace(workspace: string, userId: string) {
    return getDb()
      .select({
        id: sessions.id,
        title: sessions.title,
        workspace: sessions.workspace,
        model: sessions.model,
        createdAt: sessions.createdAt,
        updatedAt: sessions.updatedAt,
      })
      .from(sessions)
      .where(and(eq(sessions.workspace, workspace), eq(sessions.userId, userId)))
      .orderBy(desc(sessions.updatedAt))
      .all()
  },

  /** 导出用：按 updated_at 倒序列出会话（可选 workspace 过滤） */
  listForExport(workspace?: string) {
    const conds = workspace ? [eq(sessions.workspace, workspace)] : []
    return getDb()
      .select({
        id: sessions.id,
        title: sessions.title,
        workspace: sessions.workspace,
        model: sessions.model,
        createdAt: sessions.createdAt,
        updatedAt: sessions.updatedAt,
      })
      .from(sessions)
      .where(conds.length ? and(...conds) : undefined)
      .orderBy(desc(sessions.updatedAt))
      .all()
  },

  /** 按 workspace 统计 session 数（IM /projects 命令） */
  countByWorkspace(workspaceId: string): number {
    const row = getDb()
      .select({ c: sql<number>`count(*)` })
      .from(sessions)
      .where(eq(sessions.workspace, workspaceId))
      .get()
    return row?.c ?? 0
  },

  /** 找 workspace 下最近更新的 session（可选 userId 过滤，IM /switch 复用） */
  findRecentByWorkspace(workspaceId: string, userId?: string) {
    const conds = userId
      ? and(eq(sessions.workspace, workspaceId), eq(sessions.userId, userId))
      : eq(sessions.workspace, workspaceId)
    return getDb()
      .select({ id: sessions.id })
      .from(sessions)
      .where(conds)
      .orderBy(desc(sessions.updatedAt))
      .limit(1)
      .get()
  },

  /** 按 id 查紧凑字段（title/model/updatedAt，IM /status 命令） */
  findCompactById(id: string) {
    return getDb()
      .select({ title: sessions.title, model: sessions.model, updatedAt: sessions.updatedAt })
      .from(sessions)
      .where(eq(sessions.id, id))
      .get()
  },

  /** 列出最近的 session（可选 userId 过滤，ChannelRouter 用） */
  listRecent(limit: number, userId?: string) {
    const baseQuery = getDb()
      .select({
        id: sessions.id,
        title: sessions.title,
        workspace: sessions.workspace,
        updatedAt: sessions.updatedAt,
      })
      .from(sessions)
      .orderBy(desc(sessions.updatedAt))
      .limit(limit)
    return userId
      ? baseQuery.where(eq(sessions.userId, userId)).all()
      : baseQuery.all()
  },

  // ── 外部用户会话查询（H5 / 小程序 / IM 用，所有查询都以 external_user_id 为隔离键）──

  /** 按 external_user_id + workspace 查最近会话（外部用户默认进入最近会话） */
  findRecentByExternalUser(externalUserId: string, workspaceId: string) {
    return getDb()
      .select()
      .from(sessions)
      .where(
        and(
          eq(sessions.externalUserId, externalUserId),
          eq(sessions.workspace, workspaceId),
          eq(sessions.status, 'active'),
        ),
      )
      .orderBy(desc(sessions.updatedAt))
      .limit(1)
      .get()
  },

  /** 按 id + external_user_id + workspace 查（安全校验：防跨用户越权） */
  findByIdAndExternalUser(id: string, externalUserId: string, workspaceId: string) {
    return getDb()
      .select()
      .from(sessions)
      .where(
        and(
          eq(sessions.id, id),
          eq(sessions.externalUserId, externalUserId),
          eq(sessions.workspace, workspaceId),
        ),
      )
      .get()
  },

  /** 列出 external_user_id 的所有 active 会话（外部用户侧栏用） */
  listByExternalUser(externalUserId: string, workspaceId: string) {
    return getDb()
      .select({
        id: sessions.id,
        title: sessions.title,
        updatedAt: sessions.updatedAt,
      })
      .from(sessions)
      .where(
        and(
          eq(sessions.externalUserId, externalUserId),
          eq(sessions.workspace, workspaceId),
          eq(sessions.status, 'active'),
        ),
      )
      .orderBy(desc(sessions.updatedAt))
      .all()
  },
}