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

type MemberRole = 'owner' | 'admin' | 'member' | 'viewer'

/** Workspace detail for auth checks — only fields needed for access resolution */
export interface WorkspaceAuthDetail {
  id: string
  path: string
  ownerUserId: string
}

/**
 * Workspace + WorkspaceMember Repository。
 * 封装 workspace_members 表的成员管理，以及 workspaces 表的权限相关查询。
 */

export const workspaceMemberRepo = {
  /** 按 workspace + user 查成员角色（权限检查用） */
  findRoleByWorkspaceAndUser(workspaceId: string, userId: string) {
    const row = getDb()
      .select({ role: workspaceMembers.role })
      .from(workspaceMembers)
      .where(
        and(
          eq(workspaceMembers.workspaceId, workspaceId),
          eq(workspaceMembers.userId, userId),
        ),
      )
      .get()
    return row?.role
  },

  /** 列出 workspace 的所有成员 */
  listByWorkspace(workspaceId: string) {
    return getDb()
      .select()
      .from(workspaceMembers)
      .where(eq(workspaceMembers.workspaceId, workspaceId))
      .all()
  },

  /** 列出 workspace 成员（含 user 信息），按 role 等级 + name + email 排序 */
  listMembersWithUser(workspaceId: string) {
    return getDb()
      .select({
        workspaceId: workspaceMembers.workspaceId,
        userId: workspaceMembers.userId,
        role: workspaceMembers.role,
        createdAt: workspaceMembers.createdAt,
        updatedAt: workspaceMembers.updatedAt,
        name: user.name,
        email: user.email,
        image: user.image,
      })
      .from(workspaceMembers)
      .leftJoin(user, eq(user.id, workspaceMembers.userId))
      .where(eq(workspaceMembers.workspaceId, workspaceId))
      .orderBy(
        sql`CASE ${workspaceMembers.role} WHEN 'owner' THEN 0 WHEN 'admin' THEN 1 WHEN 'member' THEN 2 ELSE 3 END`,
        asc(user.name),
        asc(user.email),
      )
      .all()
  },

  /** 按 workspace + user 查成员 */
  findMember(workspaceId: string, userId: string) {
    return getDb()
      .select()
      .from(workspaceMembers)
      .where(
        and(
          eq(workspaceMembers.workspaceId, workspaceId),
          eq(workspaceMembers.userId, userId),
        ),
      )
      .get()
  },

  /** upsert 成员（ON CONFLICT 更新 role），返回成员 */
  upsertMember(workspaceId: string, userId: string, role: MemberRole) {
    getDb()
      .insert(workspaceMembers)
      .values({ workspaceId, userId, role })
      .onConflictDoUpdate({
        target: [workspaceMembers.workspaceId, workspaceMembers.userId],
        set: { role, updatedAt: nowExpr() },
      })
      .run()
    return this.findMember(workspaceId, userId)
  },

  /** 更新成员角色 */
  updateMemberRole(workspaceId: string, userId: string, role: MemberRole): number {
    const result = getDb()
      .update(workspaceMembers)
      .set({ role, updatedAt: nowExpr() })
      .where(
        and(
          eq(workspaceMembers.workspaceId, workspaceId),
          eq(workspaceMembers.userId, userId),
        ),
      )
      .run()
    return result.changes
  },

  /** 删除成员 */
  deleteMember(workspaceId: string, userId: string) {
    getDb()
      .delete(workspaceMembers)
      .where(
        and(
          eq(workspaceMembers.workspaceId, workspaceId),
          eq(workspaceMembers.userId, userId),
        ),
      )
      .run()
  },

  // ── workspaces 查询 ──

  /** 列出所有 workspace（无 user 过滤，admin 用） */
  listAll() {
    return getDb()
      .select({ id: workspaces.id, path: workspaces.path, lastOpenedAt: workspaces.lastOpenedAt })
      .from(workspaces)
      .orderBy(desc(workspaces.lastOpenedAt))
      .all()
  },

  /** 列出用户可访问的 workspace（owner 或 workspace member） */
  listAccessibleByUser(userId?: string) {
    if (!userId) return this.listAll()
    return getDb()
      .selectDistinct({
        id: workspaces.id,
        path: workspaces.path,
        lastOpenedAt: workspaces.lastOpenedAt,
        createdAt: workspaces.createdAt,
        ownerUserId: workspaces.ownerUserId,
      })
      .from(workspaces)
      .leftJoin(
        workspaceMembers,
        eq(workspaceMembers.workspaceId, workspaces.id),
      )
      .where(
        sql`${workspaces.ownerUserId} = ${userId} OR ${workspaceMembers.userId} = ${userId}`,
      )
      .orderBy(desc(workspaces.lastOpenedAt))
      .all()
  },

  /** 按 path 查 workspace id（可选 user 过滤） */
  findIdByPath(projectPath: string, userId?: string) {
    if (!userId) {
      return getDb()
        .select({ id: workspaces.id })
        .from(workspaces)
        .where(eq(workspaces.path, projectPath))
        .get()
    }
    return getDb()
      .selectDistinct({ id: workspaces.id })
      .from(workspaces)
      .leftJoin(
        workspaceMembers,
        eq(workspaceMembers.workspaceId, workspaces.id),
      )
      .where(
        and(
          eq(workspaces.path, projectPath),
          sql`${workspaces.ownerUserId} = ${userId} OR ${workspaceMembers.userId} = ${userId}`,
        ),
      )
      .get()
  },

  /** 按 id 查 workspace（全部字段） */
  findById(id: string) {
    return getDb().select().from(workspaces).where(eq(workspaces.id, id)).get()
  },

  /** 按 path 查 workspace（全部字段） */
  findByPath(projectPath: string) {
    return getDb().select().from(workspaces).where(eq(workspaces.path, projectPath)).get()
  },

  /** 按 path + owner 查 workspace（marketplace/[id]/use 的精确 path+owner 匹配） */
  findByPathAndOwner(projectPath: string, ownerUserId: string) {
    return getDb()
      .select()
      .from(workspaces)
      .where(
        and(
          eq(workspaces.path, projectPath),
          eq(workspaces.ownerUserId, ownerUserId),
        ),
      )
      .get()
  },

  /**
   * 查某个 user 最近打开的 workspace path，附带 owner_user_id = '' 的公共回退
   * （POST /api/invite 的项目根推断：优先用户自己的 ws，回退共享 ws）。
   */
  findProjectPathByOwner(userId: string) {
    return getDb()
      .select({ path: workspaces.path })
      .from(workspaces)
      .where(
        sql`${workspaces.ownerUserId} = ${userId} OR ${workspaces.ownerUserId} = ''`,
      )
      .orderBy(desc(workspaces.lastOpenedAt))
      .limit(1)
      .get()
  },

  /** 查 workspace 详情（权限解析用） */
  findDetailForAuth(workspaceId: string): WorkspaceAuthDetail | undefined {
    return getDb()
      .select({
        id: workspaces.id,
        path: workspaces.path,
        ownerUserId: workspaces.ownerUserId,
      })
      .from(workspaces)
      .where(eq(workspaces.id, workspaceId))
      .get()
  },

  /** 按 owner 查第一个 workspace（provision 用） */
  findFirstByOwner(ownerUserId: string) {
    return getDb()
      .select({ id: workspaces.id })
      .from(workspaces)
      .where(eq(workspaces.ownerUserId, ownerUserId))
      .orderBy(workspaces.createdAt)
      .get()
  },

  /** 创建 workspace */
  create(data: { id: string; path: string; ownerUserId: string }) {
    getDb()
      .insert(workspaces)
      .values({
        id: data.id,
        path: data.path,
        ownerUserId: data.ownerUserId,
      })
      .run()
  },

  /** 更新 workspace 字段 */
  updateFields(id: string, fields: Partial<typeof workspaces.$inferInsert>) {
    getDb().update(workspaces).set(fields).where(eq(workspaces.id, id)).run()
    return getDb().select().from(workspaces).where(eq(workspaces.id, id)).get()
  },

  /** touch lastOpenedAt */
  touch(id: string) {
    getDb()
      .update(workspaces)
      .set({ lastOpenedAt: nowExpr() })
      .where(eq(workspaces.id, id))
      .run()
  },

  /** 删除 workspace：事务级联 workspace_members（无 FK，由 repo 承担） */
  deleteById(id: string) {
    getDb().transaction((tx) => {
      tx.delete(workspaceMembers).where(eq(workspaceMembers.workspaceId, id)).run()
      tx.delete(workspaces).where(eq(workspaces.id, id)).run()
    })
  },

  /** 按 id 查 path（IM commands 显示 workspace 名用） */
  findPathById(id: string) {
    return getDb()
      .select({ path: workspaces.path })
      .from(workspaces)
      .where(eq(workspaces.id, id))
      .get()
  },
}