xinghen.work/server/organizations.ts

import type { FastifyInstance, FastifyReply, FastifyRequest } from 'fastify'
import type pg from 'pg'
import { randomBytes, randomUUID, createHash } from 'node:crypto'
import { z } from 'zod'

type Pool = pg.Pool
type Deps = {
  pool: Pool
  auth: (req: FastifyRequest, reply: FastifyReply) => Promise<unknown>
  adminAuth: (req: FastifyRequest, reply: FastifyReply) => Promise<unknown>
  hashPassword: (password: string) => Promise<string>
  mapUser: (row: any) => any
}
const sha = (value: string) => createHash('sha256').update(value).digest('hex')
const uid = (req: FastifyRequest) => (req as any).user.id as string
const params = (req: FastifyRequest) => req.params as Record<string, string>
const json = (value: unknown) => JSON.stringify(value)

export function registerOrganizations(app: FastifyInstance, { pool, auth, adminAuth, hashPassword, mapUser }: Deps) {
  const membership = async (organizationId: string, userId: string) =>
    (await pool.query('SELECT role FROM organization_members WHERE organization_id = $1 AND user_id = $2', [organizationId, userId])).rows[0] as { role: 'MEMBER' | 'MANAGER' } | undefined
  const org = async (id: string) => (await pool.query('SELECT * FROM organizations WHERE id = $1', [id])).rows[0]
  const manager = async (req: FastifyRequest, reply: FastifyReply, organizationId: string) => {
    const member = await membership(organizationId, uid(req))
    if (member?.role !== 'MANAGER') {
      reply.code(403).send({ message: '没有该组织的管理权限' })
      return false
    }
    return true
  }
  const event = (organizationId: string | null, actorId: string, action: string, details: unknown, subjectUserId: string | null = null) =>
    pool.query(`INSERT INTO audit_events(id, organization_id, actor_id, admin_actor_id, subject_user_id, action, details)
      VALUES($1,$2,$3,$4,$5,$6,$7)`, [randomUUID(), organizationId, ['ORGANIZATION_CREATED','ORGANIZATION_UPDATED','MEMBER_ROLE_SET','MEMBER_REMOVED','MEMBER_APPROVED','MEMBER_REJECTED','ORGANIZATION_POINTS_ADJUSTED'].includes(action) ? null : actorId,
      ['ORGANIZATION_CREATED','ORGANIZATION_UPDATED','MEMBER_ROLE_SET','MEMBER_REMOVED','MEMBER_APPROVED','MEMBER_REJECTED','ORGANIZATION_POINTS_ADJUSTED'].includes(action) ? actorId : null,
      subjectUserId, action, json(details)])

  app.get('/api/me/organizations', { preHandler: auth }, async (req) => {
    const { rows } = await pool.query(`SELECT o.id, o.slug, o.name, o.is_active, o.points_balance, m.role
      FROM organization_members m JOIN organizations o ON o.id = m.organization_id
      WHERE m.user_id = $1 ORDER BY o.name`, [uid(req)])
    return { organizations: rows.map((r) => ({ id: r.id, slug: r.slug, name: r.name, isActive: r.is_active, pointsBalance: r.points_balance, role: r.role })) }
  })

  app.get('/api/organizations/:slug', { preHandler: auth }, async (req) => {
    const { rows } = await pool.query('SELECT id, name, is_active FROM organizations WHERE slug = $1', [params(req).slug])
    return { organization: rows[0] ? { id: rows[0].id, name: rows[0].name, isActive: rows[0].is_active } : null }
  })

  app.post('/api/organizations/:slug/apply', { preHandler: auth }, async (req, reply) => {
    const { rows } = await pool.query('SELECT id FROM organizations WHERE slug = $1 AND is_active=TRUE', [params(req).slug])
    if (!rows[0]) return reply.code(404).send({ message: '组织不存在' })
    const organizationId = rows[0].id
    if (await membership(organizationId, uid(req))) return reply.code(409).send({ message: '您已经是该组织成员' })
    await pool.query(`INSERT INTO organization_applications(id, organization_id, user_id, status)
      VALUES($1,$2,$3,'PENDING') ON CONFLICT (organization_id,user_id)
      DO UPDATE SET status = 'PENDING', reviewed_by = NULL, reviewed_at = NULL, created_at = NOW()`,
      [randomUUID(), organizationId, uid(req)])
    await event(organizationId, uid(req), 'MEMBERSHIP_APPLIED', {})
    return { ok: true }
  })

  app.post('/api/organizations/join/:token', { preHandler: auth }, async (req, reply) => {
    const { rows } = await pool.query('SELECT id FROM organizations WHERE invite_token_hash = $1 AND is_active=TRUE', [sha(params(req).token)])
    if (!rows[0]) return reply.code(404).send({ message: '注册链接无效或已停用' })
    await pool.query(`INSERT INTO organization_members(organization_id,user_id) VALUES($1,$2)
      ON CONFLICT (organization_id,user_id) DO NOTHING`, [rows[0].id, uid(req)])
    await pool.query(`UPDATE organization_applications SET status='APPROVED',reviewed_at=NOW()
      WHERE organization_id=$1 AND user_id=$2 AND status='PENDING'`, [rows[0].id, uid(req)])
    await event(rows[0].id, uid(req), 'INVITE_JOINED', {})
    return { organizationId: rows[0].id }
  })

  app.get('/api/organizations/:id/content', { preHandler: auth }, async (req, reply) => {
    const id = params(req).id
    const member = await membership(id, uid(req))
    if (!member) return reply.code(403).send({ message: '请先加入组织' })
    const organization = await org(id)
    if (!organization) return reply.code(404).send({ message: '组织不存在' })
    const [members, courses] = await Promise.all([
      pool.query(`SELECT u.id, u.contact_name, m.role FROM organization_members m
        JOIN users u ON u.id=m.user_id WHERE m.organization_id=$1 ORDER BY m.joined_at`, [id]),
      pool.query(`SELECT c.id,c.user_id,c.name,c.status,c.created_at,c.updated_at,c.submitted_at,c.completed_at,
        u.contact_name AS creator_name FROM courses c JOIN users u ON u.id=c.user_id
        WHERE c.organization_id=$1 ORDER BY c.created_at DESC`, [id])
    ])
    return { organization: { id, slug: organization.slug, name: organization.name, pointsBalance: organization.points_balance, role: member.role }, members: members.rows, courses: courses.rows }
  })

  app.get('/api/organizations/:id/manage', { preHandler: auth }, async (req, reply) => {
    const id = params(req).id
    if (!(await manager(req, reply, id))) return
    const [members, applications, audit, ledger] = await Promise.all([
      pool.query(`SELECT u.id,u.phone,u.contact_name,m.role,m.joined_at FROM organization_members m
        JOIN users u ON u.id=m.user_id WHERE m.organization_id=$1 ORDER BY m.joined_at DESC`, [id]),
      pool.query(`SELECT a.id,a.status,a.created_at,u.id AS user_id,u.phone,u.contact_name
        FROM organization_applications a JOIN users u ON u.id=a.user_id
        WHERE a.organization_id=$1 ORDER BY a.created_at DESC`, [id]),
      pool.query(`SELECT a.*,u.contact_name AS actor_name FROM audit_events a LEFT JOIN users u ON u.id=a.actor_id
        WHERE a.organization_id=$1 ORDER BY a.created_at DESC`, [id]),
      pool.query(`SELECT l.*,u.contact_name AS beneficiary_name FROM points_ledger l
        LEFT JOIN users u ON u.id=l.beneficiary_user_id
        WHERE l.owner_type='ORGANIZATION' AND l.owner_id=$1 ORDER BY l.created_at DESC`, [id])
    ])
    const organization = await org(id)
    return { members: members.rows, applications: applications.rows, audit: audit.rows, ledger: ledger.rows,
      inviteActive: Boolean(organization?.invite_token_hash),
      inviteToken: organization?.invite_token || null }
  })

  app.post('/api/organizations/:id/applications/:applicationId/review', { preHandler: auth }, async (req, reply) => {
    const id = params(req).id
    if (!(await manager(req, reply, id))) return
    const parsed = z.object({ approve: z.boolean() }).safeParse(req.body)
    if (!parsed.success) return reply.code(400).send({ message: '审批参数不正确' })
    const client = await pool.connect()
    try {
      await client.query('BEGIN')
      const application = await client.query(`UPDATE organization_applications SET status=$1,reviewed_by=$2,reviewed_at=NOW()
        WHERE id=$3 AND organization_id=$4 AND status='PENDING' RETURNING user_id`,
        [parsed.data.approve ? 'APPROVED' : 'REJECTED', uid(req), params(req).applicationId, id])
      if (!application.rows[0]) { await client.query('ROLLBACK'); return reply.code(404).send({ message: '待审批申请不存在' }) }
      if (parsed.data.approve) await client.query(`INSERT INTO organization_members(organization_id,user_id) VALUES($1,$2)
        ON CONFLICT DO NOTHING`, [id, application.rows[0].user_id])
      await client.query('COMMIT')
      await event(id, uid(req), parsed.data.approve ? 'MEMBER_APPROVED' : 'MEMBER_REJECTED',
        { applicationId: params(req).applicationId, before: 'PENDING', after: parsed.data.approve ? 'APPROVED' : 'REJECTED' }, application.rows[0].user_id)
      return { ok: true }
    } catch (error) { await client.query('ROLLBACK'); throw error } finally { client.release() }
  })

  app.post('/api/organizations/:id/users', { preHandler: auth }, async (req, reply) => {
    const id = params(req).id
    if (!(await manager(req, reply, id))) return
    if (!(await org(id))?.is_active) return reply.code(409).send({ message: '组织已停用，不能创建新成员' })
    const parsed = z.object({ phone: z.string().regex(/^1\d{10}$/), contactName: z.string().trim().min(1).max(80) }).safeParse(req.body)
    if (!parsed.success) return reply.code(400).send({ message: '请填写正确的手机号和姓名' })
    const client = await pool.connect()
    try {
      await client.query('BEGIN')
      const userId = randomUUID()
      const created = await client.query(`INSERT INTO users(id,phone,password_hash,role,contact_name,points_balance,must_change_password)
        VALUES($1,$2,$3,'USER',$4,10000,TRUE) RETURNING *`,
        [userId, parsed.data.phone, await hashPassword('888888'), parsed.data.contactName])
      await client.query('INSERT INTO organization_members(organization_id,user_id) VALUES($1,$2)', [id, userId])
      await client.query('COMMIT')
      await event(id, uid(req), 'USER_CREATED', { phone: parsed.data.phone, contactName: parsed.data.contactName }, userId)
      return reply.code(201).send({ user: mapUser(created.rows[0]), initialPassword: '888888' })
    } catch (error: any) {
      await client.query('ROLLBACK')
      if (error.code === '23505') return reply.code(409).send({ message: '手机号已注册，请让用户通过组织链接加入' })
      throw error
    } finally { client.release() }
  })

  app.patch('/api/organizations/:id/members/:userId', { preHandler: auth }, async (req, reply) => {
    const id = params(req).id
    if (!(await manager(req, reply, id))) return
    const parsed = z.object({ contactName: z.string().trim().min(1, '请填写姓名').max(80, '姓名不能超过 80 个字') }).safeParse(req.body)
    if (!parsed.success) return reply.code(400).send({ message: parsed.error.issues[0].message })
    const member = await membership(id, params(req).userId)
    if (!member) return reply.code(404).send({ message: '组织成员不存在' })
    const { rows } = await pool.query('UPDATE users SET contact_name=$1,updated_at=NOW() WHERE id=$2 RETURNING *', [parsed.data.contactName, params(req).userId])
    await event(id, uid(req), 'MEMBER_NAME_UPDATED', { contactName: parsed.data.contactName }, params(req).userId)
    return { user: mapUser(rows[0]) }
  })

  app.post('/api/organizations/:id/invite', { preHandler: auth }, async (req, reply) => {
    const id = params(req).id
    if (!(await manager(req, reply, id))) return
    if (!(await org(id))?.is_active) return reply.code(409).send({ message: '组织已停用，不能生成注册链接' })
    const token = randomBytes(24).toString('hex')
    await pool.query('UPDATE organizations SET invite_token_hash=$1,invite_token=$2,updated_at=NOW() WHERE id=$3', [sha(token), token, id])
    await event(id, uid(req), 'INVITE_ROTATED', {})
    return { token }
  })
  app.delete('/api/organizations/:id/invite', { preHandler: auth }, async (req, reply) => {
    const id = params(req).id
    if (!(await manager(req, reply, id))) return
    await pool.query('UPDATE organizations SET invite_token_hash=NULL,invite_token=NULL,updated_at=NOW() WHERE id=$1', [id])
    await event(id, uid(req), 'INVITE_DISABLED', {})
    return { ok: true }
  })

  app.get('/api/organizations/:id/points', { preHandler: auth }, async (req, reply) => {
    const id = params(req).id
    const member = await membership(id, uid(req))
    if (!member) return reply.code(403).send({ message: '请先加入组织' })
    const { rows: [organization] } = await pool.query('SELECT points_balance FROM organizations WHERE id=$1', [id])
    const { rows } = await pool.query(`SELECT l.id,l.course_id,COALESCE(c.name,l.course_name_snapshot) AS course_name,l.amount,l.reason,l.created_at
      FROM points_ledger l LEFT JOIN courses c ON c.id=l.course_id
      WHERE l.owner_type='ORGANIZATION' AND l.owner_id=$1 ${member.role === 'MANAGER' ? '' : 'AND l.beneficiary_user_id=$2'}
      ORDER BY l.created_at DESC`, member.role === 'MANAGER' ? [id] : [id, uid(req)])
    return { balance: organization?.points_balance ?? 0, transactions: rows }
  })

  app.get('/api/admin/organizations', { preHandler: adminAuth }, async () => {
    const { rows } = await pool.query(`SELECT o.*,COUNT(m.user_id)::int AS members_count FROM organizations o
      LEFT JOIN organization_members m ON m.organization_id=o.id GROUP BY o.id ORDER BY o.created_at DESC`)
    return { organizations: rows }
  })
  app.post('/api/admin/organizations', { preHandler: adminAuth }, async (req, reply) => {
    const parsed = z.object({ name: z.string().trim().min(1).max(120), slug: z.string().regex(/^[a-z0-9-]{3,80}$/) }).safeParse(req.body)
    if (!parsed.success) return reply.code(400).send({ message: '请填写名称和英文网址标识' })
    try {
      const { rows } = await pool.query('INSERT INTO organizations(id,name,slug) VALUES($1,$2,$3) RETURNING *',
        [randomUUID(), parsed.data.name, parsed.data.slug])
      await event(rows[0].id, uid(req), 'ORGANIZATION_CREATED', { name: rows[0].name })
      return reply.code(201).send({ organization: rows[0] })
    } catch (error: any) {
      if (error.code === '23505') return reply.code(409).send({ message: '网址标识已存在' })
      throw error
    }
  })
  app.get('/api/admin/organizations/:id', { preHandler: adminAuth }, async (req, reply) => {
    const id = params(req).id
    const organization = await org(id)
    if (!organization) return reply.code(404).send({ message: '组织不存在' })
    const [members, applications, ledger, audit, courses] = await Promise.all([
      pool.query(`SELECT u.id,u.phone,u.contact_name,m.role FROM organization_members m JOIN users u ON u.id=m.user_id
        WHERE m.organization_id=$1 ORDER BY m.joined_at DESC`, [id]),
      pool.query(`SELECT a.id,a.status,u.id AS user_id,u.phone,u.contact_name FROM organization_applications a
        JOIN users u ON u.id=a.user_id WHERE a.organization_id=$1 ORDER BY a.created_at DESC`, [id]),
      pool.query(`SELECT * FROM points_ledger WHERE owner_type='ORGANIZATION' AND owner_id=$1 ORDER BY created_at DESC`, [id]),
      pool.query(`SELECT a.*,u.contact_name AS actor_name FROM audit_events a LEFT JOIN users u ON u.id=a.actor_id
        WHERE a.organization_id=$1 ORDER BY a.created_at DESC`, [id]),
      pool.query(`SELECT c.id,c.name,c.status,c.created_at,c.updated_at,u.contact_name AS creator_name FROM courses c JOIN users u ON u.id=c.user_id
        WHERE c.organization_id=$1 ORDER BY c.created_at DESC`, [id])
    ])
    return { organization, members: members.rows, applications: applications.rows, ledger: ledger.rows, audit: audit.rows, courses: courses.rows }
  })
  app.patch('/api/admin/organizations/:id', { preHandler: adminAuth }, async (req, reply) => {
    const parsed = z.object({
      name: z.string().trim().min(1).max(120).optional(),
      slug: z.string().trim().regex(/^[a-z0-9-]{3,80}$/, '网址标识需为3-80位小写字母、数字或连字符').optional(),
      isActive: z.boolean().optional()
    }).refine((v) => v.name !== undefined || v.slug !== undefined || v.isActive !== undefined).safeParse(req.body)
    if (!parsed.success) return reply.code(400).send({ message: parsed.error.issues[0]?.message || '组织参数不正确' })
    const old = await org(params(req).id)
    try {
      const { rows } = await pool.query(
        'UPDATE organizations SET name=COALESCE($1,name),slug=COALESCE($2,slug),is_active=COALESCE($3,is_active),updated_at=NOW() WHERE id=$4 RETURNING *',
        [parsed.data.name ?? null, parsed.data.slug ?? null, parsed.data.isActive ?? null, params(req).id]
      )
      if (!rows[0]) return reply.code(404).send({ message: '组织不存在' })
      await event(rows[0].id, uid(req), 'ORGANIZATION_UPDATED', {
        before: { name: old?.name, slug: old?.slug, isActive: old?.is_active },
        after: { name: rows[0].name, slug: rows[0].slug, isActive: rows[0].is_active }
      })
      return { organization: rows[0] }
    } catch (error: any) {
      if (error.code === '23505') return reply.code(409).send({ message: '网址标识已存在' })
      throw error
    }
  })
  app.put('/api/admin/organizations/:id/members/:userId', { preHandler: adminAuth }, async (req, reply) => {
    const parsed = z.object({ role: z.enum(['MEMBER', 'MANAGER']) }).safeParse(req.body)
    if (!parsed.success) return reply.code(400).send({ message: '成员角色不正确' })
    const id = params(req).id
    const previous = await membership(id, params(req).userId)
    const { rows } = await pool.query(`INSERT INTO organization_members(organization_id,user_id,role) VALUES($1,$2,$3)
      ON CONFLICT (organization_id,user_id) DO UPDATE SET role=EXCLUDED.role RETURNING *`, [id, params(req).userId, parsed.data.role])
    await pool.query(`UPDATE organization_applications SET status='APPROVED',reviewed_by_admin=$1,reviewed_at=NOW()
      WHERE organization_id=$2 AND user_id=$3 AND status='PENDING'`, [uid(req), id, params(req).userId])
    await event(id, uid(req), 'MEMBER_ROLE_SET', { before: previous?.role || null, after: parsed.data.role }, params(req).userId)
    return { member: rows[0] }
  })
  app.delete('/api/admin/organizations/:id/members/:userId', { preHandler: adminAuth }, async (req) => {
    const id = params(req).id
    await pool.query('DELETE FROM organization_members WHERE organization_id=$1 AND user_id=$2', [id, params(req).userId])
    await event(id, uid(req), 'MEMBER_REMOVED', {}, params(req).userId)
    return { ok: true }
  })
  app.post('/api/admin/organizations/:id/applications/:applicationId/review', { preHandler: adminAuth }, async (req, reply) => {
    const parsed = z.object({ approve: z.boolean() }).safeParse(req.body)
    if (!parsed.success) return reply.code(400).send({ message: '审批参数不正确' })
    const id = params(req).id
    const client = await pool.connect()
    try {
      await client.query('BEGIN')
      const { rows } = await client.query(`UPDATE organization_applications SET status=$1,reviewed_by_admin=$2,reviewed_at=NOW()
        WHERE id=$3 AND organization_id=$4 AND status='PENDING' RETURNING user_id`,
        [parsed.data.approve ? 'APPROVED' : 'REJECTED', uid(req), params(req).applicationId, id])
      if (!rows[0]) { await client.query('ROLLBACK'); return reply.code(404).send({ message: '待审批申请不存在' }) }
      if (parsed.data.approve) await client.query('INSERT INTO organization_members(organization_id,user_id) VALUES($1,$2) ON CONFLICT DO NOTHING', [id, rows[0].user_id])
      await client.query('COMMIT')
      await event(id, uid(req), parsed.data.approve ? 'MEMBER_APPROVED' : 'MEMBER_REJECTED',
        { applicationId: params(req).applicationId, before: 'PENDING', after: parsed.data.approve ? 'APPROVED' : 'REJECTED' }, rows[0].user_id)
      return { ok: true }
    } catch (error) { await client.query('ROLLBACK'); throw error } finally { client.release() }
  })
  app.post('/api/admin/organizations/:id/points', { preHandler: adminAuth }, async (req, reply) => {
    const parsed = z.object({ amount: z.number().int().refine((n) => n !== 0), reason: z.string().trim().min(1).max(200) }).safeParse(req.body)
    if (!parsed.success) return reply.code(400).send({ message: '请填写非零积分和操作原因' })
    const id = params(req).id
    const client = await pool.connect()
    try {
      await client.query('BEGIN')
      const { rows } = await client.query(`UPDATE organizations SET points_balance=points_balance+$1,updated_at=NOW()
        WHERE id=$2 AND points_balance+$1>=0 RETURNING points_balance`, [parsed.data.amount, id])
      if (!rows[0]) { await client.query('ROLLBACK'); return reply.code(409).send({ message: '组织不存在或余额不足' }) }
      await client.query(`INSERT INTO points_ledger(id,owner_type,owner_id,admin_actor_id,amount,reason)
        VALUES($1,'ORGANIZATION',$2,$3,$4,$5)`, [randomUUID(), id, uid(req), parsed.data.amount, parsed.data.reason])
      await client.query('COMMIT')
      await event(id, uid(req), 'ORGANIZATION_POINTS_ADJUSTED', {
        amount: parsed.data.amount, reason: parsed.data.reason,
        before: rows[0].points_balance - parsed.data.amount, after: rows[0].points_balance
      })
      return { balance: rows[0].points_balance }
    } catch (error) { await client.query('ROLLBACK'); throw error } finally { client.release() }
  })
}