xinghen.work/server/index.ts

import Fastify from 'fastify'
import type { FastifyError, FastifyReply, FastifyRequest } from 'fastify'
import cors from '@fastify/cors'
import multipart from '@fastify/multipart'
import staticPlugin from '@fastify/static'
import { createWriteStream } from 'node:fs'
import { mkdir, readFile, unlink } from 'node:fs/promises'
import { pipeline } from 'node:stream/promises'
import { resolve, extname, dirname, join } from 'node:path'
import { fileURLToPath } from 'node:url'
import { randomBytes, randomUUID, scrypt as scryptCb, timingSafeEqual, createHash } from 'node:crypto'
import { promisify } from 'node:util'
import { z } from 'zod'
import { mapCourse, mapDeliverable, mapInstructor, mapUser, pool } from './db.js'
import { COURSE_TEMPLATES, populateCourseFromTemplate } from './templates.js'
import { registerOrganizations } from './organizations.js'
import { registerDigitalInstructors } from './digital-instructors.js'

const scrypt = promisify(scryptCb)
const app = Fastify({ logger: true })
const port = Number(process.env.PORT || 3001)
const uploadDir = resolve(process.env.UPLOAD_DIR || './uploads')
const sampleDir = resolve(process.env.SAMPLE_DIR || './sample')
const maxBytes = Number(process.env.MAX_UPLOAD_MB || 200) * 1024 * 1024

await mkdir(uploadDir, { recursive: true })
const here = dirname(fileURLToPath(import.meta.url))
await pool.query(await readFile(join(here, 'schema.sql'), 'utf8'))

// 旧版管理员从 users 迁出。若曾拥有业务数据，停止启动并要求人工处理，避免级联删除。
async function initDefaultAdmin() {
  const client = await pool.connect()
  try {
    await client.query('BEGIN')
    const legacy = await client.query("SELECT * FROM users WHERE role = 'ADMIN' FOR UPDATE")
    for (const row of legacy.rows) {
      const owned = await client.query(`SELECT
        EXISTS(SELECT 1 FROM courses WHERE user_id=$1) OR
        EXISTS(SELECT 1 FROM organization_members WHERE user_id=$1) OR
        EXISTS(SELECT 1 FROM digital_instructors WHERE owner_id=$1 OR creator_id=$1 OR personal_user_id=$1) AS has_business_data`, [row.id])
      if (owned.rows[0].has_business_data) throw new Error(`Legacy admin ${row.id} owns business data; manual migration required`)
      const auditIds = (await client.query('SELECT id FROM audit_events WHERE actor_id=$1', [row.id])).rows.map(r => r.id)
      const ledgerIds = (await client.query("SELECT id FROM points_ledger WHERE actor_id=$1 AND NOT (owner_type='PERSONAL' AND owner_id=$1)", [row.id])).rows.map(r => r.id)
      const applicationIds = (await client.query('SELECT id FROM organization_applications WHERE reviewed_by=$1', [row.id])).rows.map(r => r.id)
      const instructorEventIds = (await client.query('SELECT id FROM digital_instructor_events WHERE actor_id=$1', [row.id])).rows.map(r => r.id)
      // 外键先释放，再用新的管理员外键恢复历史操作人的身份。
      await client.query('UPDATE audit_events SET actor_id=NULL WHERE actor_id=$1', [row.id])
      await client.query('UPDATE points_ledger SET actor_id=NULL WHERE actor_id=$1', [row.id])
      await client.query('UPDATE organization_applications SET reviewed_by=NULL WHERE reviewed_by=$1', [row.id])
      await client.query('UPDATE digital_instructor_events SET actor_id=NULL WHERE actor_id=$1', [row.id])
      await client.query("DELETE FROM points_ledger WHERE owner_type='PERSONAL' AND owner_id=$1", [row.id])
      await client.query('DELETE FROM users WHERE id=$1', [row.id])
      await client.query('INSERT INTO platform_admins(id,phone,password_hash,contact_name,created_at,updated_at) VALUES($1,$2,$3,$4,$5,$6)',
        [row.id,row.phone,row.password_hash,row.contact_name,row.created_at,row.updated_at])
      await client.query('UPDATE audit_events SET admin_actor_id=$1 WHERE id=ANY($2::uuid[])', [row.id,auditIds])
      await client.query('UPDATE points_ledger SET admin_actor_id=$1 WHERE id=ANY($2::uuid[])', [row.id,ledgerIds])
      await client.query('UPDATE organization_applications SET reviewed_by_admin=$1 WHERE id=ANY($2::uuid[])', [row.id,applicationIds])
      await client.query('UPDATE digital_instructor_events SET admin_actor_id=$1 WHERE id=ANY($2::uuid[])', [row.id,instructorEventIds])
    }
    const count = await client.query('SELECT 1 FROM platform_admins LIMIT 1')
    if (!count.rows[0]) {
      const phone = process.env.ADMIN_PHONE
      const password = process.env.ADMIN_PASSWORD
      if (!phone || !password) throw new Error('ADMIN_PHONE and ADMIN_PASSWORD are required to create the first platform administrator')
      const existing = await client.query('SELECT 1 FROM users WHERE phone=$1', [phone])
      if (existing.rows[0]) throw new Error('ADMIN_PHONE already belongs to an ordinary user')
      await client.query('INSERT INTO platform_admins(id,phone,password_hash,contact_name) VALUES($1,$2,$3,$4)',
        [randomUUID(),phone,await hashPassword(password),'系统管理员'])
    }
    await client.query('COMMIT')
  } catch (error) { await client.query('ROLLBACK'); throw error } finally { client.release() }
}
await initDefaultAdmin()

await app.register(cors, { origin: process.env.NODE_ENV === 'production' ? false : true })
await app.register(multipart, { limits: { fileSize: maxBytes, files: 20, fields: 30 } })
await app.register(staticPlugin, { root: uploadDir, serve: false })
await app.register(staticPlugin, { root: sampleDir, prefix: '/sample/', decorateReply: false })

const sha = (v: string) => createHash('sha256').update(v).digest('hex')

async function hashPassword(p: string) {
  const salt = randomBytes(16).toString('hex')
  const key = (await scrypt(p, salt, 64)) as Buffer
  return 'scrypt$' + salt + '$' + key.toString('hex')
}

async function verifyPassword(p: string, stored: string) {
  const [, salt, hex] = stored.split('$')
  if (!salt || !hex) return false
  const a = (await scrypt(p, salt, 64)) as Buffer
  const b = Buffer.from(hex, 'hex')
  return a.length === b.length && timingSafeEqual(a, b)
}

async function session(uid: string, admin = false) {
  const token = randomBytes(32).toString('hex')
  await pool.query(admin ? "INSERT INTO platform_admin_sessions(token_hash, admin_id, expires_at) VALUES($1, $2, NOW() + INTERVAL '30 days')" : "INSERT INTO sessions(token_hash, user_id, expires_at) VALUES($1, $2, NOW() + INTERVAL '30 days')", [
    sha(token),
    uid
  ])
  return token
}

async function auth(req: FastifyRequest, reply: FastifyReply) {
  const token = req.headers.authorization?.replace(/^Bearer\s+/i, '')
  if (!token) return reply.code(401).send({ message: '请先登录' })
  const { rows } = await pool.query(
    'SELECT u.* FROM sessions s JOIN users u ON u.id = s.user_id WHERE s.token_hash = $1 AND s.expires_at > NOW()',
    [sha(token)]
  )
  if (!rows[0]) return reply.code(401).send({ message: '登录已过期，请重新登录' })
  ;(req as any).user = rows[0]
  if (rows[0].must_change_password && !['/api/me', '/api/me/password', '/api/auth/logout'].includes(req.url.split('?')[0])) {
    return reply.code(403).send({ message: '请先修改初始密码' })
  }
}

async function adminAuth(req: FastifyRequest, reply: FastifyReply) {
  const token = req.headers.authorization?.replace(/^Bearer\s+/i, '')
  if (!token) return reply.code(401).send({ message: '请先登录管理后台' })
  const { rows } = await pool.query('SELECT a.* FROM platform_admin_sessions s JOIN platform_admins a ON a.id=s.admin_id WHERE s.token_hash=$1 AND s.expires_at>NOW()', [sha(token)])
  if (!rows[0]) return reply.code(401).send({ message: '管理员登录已过期' })
  ;(req as any).user = { ...rows[0], role: 'ADMIN' }
}
async function accountAuth(req: FastifyRequest, reply: FastifyReply) {
  const token = req.headers.authorization?.replace(/^Bearer\s+/i, '')
  if (token) {
    const { rows } = await pool.query('SELECT a.* FROM platform_admin_sessions s JOIN platform_admins a ON a.id=s.admin_id WHERE s.token_hash=$1 AND s.expires_at>NOW()', [sha(token)])
    if (rows[0]) { (req as any).user = { ...rows[0], role: 'ADMIN' }; return }
  }
  return auth(req, reply)
}

const userId = (r: any) => r.user.id
const phonePassword = z.object({
  phone: z.string().regex(/^1\d{10}$/, '请输入正确的手机号码'),
  password: z.string().min(6, '密码至少 6 位').max(72)
})

const fullCourse = async (row: any, includeCreator = false) => {
  const latest = await pool.query('SELECT * FROM courses WHERE id = $1', [row.id])
  row = { ...row, ...(latest.rows[0] || {}) }
  if (row.organization_id) {
    const organization = await pool.query('SELECT name FROM organizations WHERE id=$1', [row.organization_id])
    row.organization_name = organization.rows[0]?.name || ''
  }
  const [a, i, instructorImages, d, e, progress] = await Promise.all([
    pool.query('SELECT * FROM course_assets WHERE course_id = $1 ORDER BY created_at', [row.id]),
    pool.query('SELECT * FROM instructors WHERE course_id = $1 ORDER BY created_at', [row.id]),
    pool.query(`SELECT ii.* FROM instructor_images ii JOIN instructors i ON i.id = ii.instructor_id
      WHERE i.course_id = $1 ORDER BY ii.sort_order, ii.created_at`, [row.id]),
    pool.query('SELECT * FROM course_deliverables WHERE course_id = $1 ORDER BY created_at', [row.id]),
    pool.query('SELECT * FROM episodes WHERE course_id = $1 ORDER BY episode_number ASC, created_at ASC', [row.id]),
    pool.query('SELECT * FROM course_progress_events WHERE course_id = $1 ORDER BY created_at DESC', [row.id])
  ])

  let episodesRows = e.rows
  // 确保课程永远至少有 1 个分集（自愈保底）
  if (episodesRows.length === 0) {
    const defaultEpId = randomUUID()
    const defaultTitle = '第 1 集：' + (row.name || '核心内容讲解')
    const insertRes = await pool.query(
      'INSERT INTO episodes(id, course_id, episode_number, title, summary, lecture_notes) VALUES($1, $2, 1, $3, $4, $5) RETURNING *',
      [defaultEpId, row.id, defaultTitle, '本集核心内容与制作说明', '']
    )
    episodesRows = insertRes.rows
  }

  let creatorUser = null
  if (includeCreator && row.user_id) {
    const u = await pool.query('SELECT * FROM users WHERE id = $1', [row.user_id])
    creatorUser = u.rows[0] || null
  }
  let instructors = i.rows.map((instructor) => ({
    ...instructor,
    image_urls: instructorImages.rows
      .filter((image) => image.instructor_id === instructor.id)
      .map((image) => `/uploads/${image.stored_name}`)
  }))
  if (row.digital_instructor_id) {
    const digital = await pool.query('SELECT * FROM digital_instructors WHERE id=$1', [row.digital_instructor_id])
    if (digital.rows[0]) {
      const imageRows = await pool.query("SELECT stored_name FROM digital_instructor_files WHERE instructor_id=$1 AND kind='IMAGE' AND is_active=TRUE ORDER BY sort_order", [row.digital_instructor_id])
      const audioRows = await pool.query("SELECT stored_name FROM digital_instructor_files WHERE instructor_id=$1 AND kind='AUDIO' AND is_active=TRUE ORDER BY sort_order", [row.digital_instructor_id])
      instructors = [{
        ...digital.rows[0],
        image_urls: imageRows.rows.map(f => `/uploads/${f.stored_name}`),
        audio_urls: audioRows.rows.map(f => `/uploads/${f.stored_name}`)
      }]
    }
  }
  if (row.status !== 'DRAFT' && row.digital_instructor_snapshot) {
    const snapshot = row.digital_instructor_snapshot
    instructors = [{
      ...snapshot,
      image_urls: (snapshot.images || []).map((image: any) => image.url),
      audio_urls: (snapshot.audio || []).map((audio: any) => audio.url)
    }]
  }
  return mapCourse({ ...row, progress_events: progress.rows }, a.rows, instructors, d.rows, creatorUser, episodesRows)
}

const addProgressEvent = (courseId: string, actorType: 'USER' | 'ADMIN' | 'SYSTEM', eventType: string, title: string, description = '', metadata: any = {}) =>
  pool.query(
    'INSERT INTO course_progress_events(id, course_id, actor_type, event_type, title, description, metadata) VALUES($1,$2,$3,$4,$5,$6,$7)',
    [randomUUID(), courseId, actorType, eventType, title, description, JSON.stringify(metadata)]
  )

async function refreshCoursePoints(courseId: string) {
  const { rows } = await pool.query('SELECT COUNT(*)::int AS count FROM episodes WHERE course_id = $1', [courseId])
  const episodeCount = Math.max(1, Number(rows[0]?.count) || 0)
  const points = episodeCount * 1000
  await pool.query(
    'UPDATE courses SET estimated_points = $1, updated_at = NOW() WHERE id = $2',
    [points, courseId]
  )
  return { episodeCount, points }
}

const ownedCourse = async (id: string, uid: string) =>
  (await pool.query(`SELECT c.* FROM courses c WHERE c.id = $1 AND
    ((c.organization_id IS NULL AND c.user_id=$2) OR (c.organization_id IS NOT NULL AND EXISTS (
      SELECT 1 FROM organization_members m WHERE m.organization_id=c.organization_id AND m.user_id=$2
      AND (c.user_id=$2 OR m.role='MANAGER'))))`, [id, uid])).rows[0]

registerOrganizations(app, { pool, auth, adminAuth, hashPassword, mapUser })
const digitalInstructors = registerDigitalInstructors(app, { pool, auth: accountAuth, uploadDir })

async function courseSnapshot(id: string) {
  const { rows: [course] } = await pool.query('SELECT * FROM courses WHERE id=$1', [id])
  if (!course) return null
  const [episodes, assets, instructors, deliverables] = await Promise.all([
    pool.query('SELECT * FROM episodes WHERE course_id=$1 ORDER BY episode_number', [id]),
    pool.query('SELECT id,episode_id,original_name,size,mime_type FROM course_assets WHERE course_id=$1 ORDER BY created_at', [id]),
    pool.query('SELECT id,name,organization,introduction FROM instructors WHERE course_id=$1 ORDER BY created_at', [id]),
    pool.query('SELECT id,episode_id,original_name,size,mime_type FROM course_deliverables WHERE course_id=$1 ORDER BY created_at', [id])
  ])
  return { course, episodes: episodes.rows, assets: assets.rows, instructors: instructors.rows, deliverables: deliverables.rows }
}

app.addHook('onRequest', async (req) => {
  if (!['POST', 'PATCH', 'PUT', 'DELETE'].includes(req.method)) return
  const match = req.url.match(/^\/api\/(?:admin\/)?courses\/([0-9a-f-]{36})(?:\/|\?|$)/i)
  if (!match) return
  ;(req as any).auditCourseId = match[1]
  ;(req as any).auditBefore = await courseSnapshot(match[1])
})

app.addHook('onSend', async (req, reply, payload) => {
  const id = (req as any).auditCourseId as string | undefined
  const before = (req as any).auditBefore
  if (!id || !before || reply.statusCode >= 400) return payload
  const authorization = req.headers.authorization?.replace(/^Bearer\s+/i, '')
  if (!authorization) return payload
  const actor = await pool.query(`SELECT s.user_id FROM sessions s WHERE s.token_hash=$1 AND s.expires_at>NOW()`, [sha(authorization)])
  const adminActor = actor.rows[0] ? null : (await pool.query(`SELECT s.admin_id FROM platform_admin_sessions s WHERE s.token_hash=$1 AND s.expires_at>NOW()`, [sha(authorization)])).rows[0]
  if (!actor.rows[0] && !adminActor) return payload
  const after = await courseSnapshot(id)
  await pool.query(`INSERT INTO audit_events(id,organization_id,course_id,actor_id,admin_actor_id,subject_user_id,action,details)
    VALUES($1,$2,$3,$4,$5,$6,$7,$8)`, [randomUUID(), before.course.organization_id, id, actor.rows[0]?.user_id || null,
    adminActor?.admin_id || null, before.course.user_id, `${req.method} ${req.routeOptions.url}`, JSON.stringify({ before, after })])
  return payload
})

app.addHook('preHandler', async (req, reply) => {
  const match = req.url.match(/^\/api\/courses\/([0-9a-f-]{36})(?:\/|\?|$)/i)
  if (!match) return
  const { rows } = await pool.query('SELECT organization_id FROM courses WHERE id=$1', [match[1]])
  if (!rows[0]) return
  const selected = req.headers['x-workspace-id']
  const workspace = typeof selected === 'string' && selected !== 'personal' ? selected : null
  if (rows[0].organization_id !== workspace) return reply.code(404).send({ message: '请切换到课程所属工作区' })
})

async function courseOperator(req: FastifyRequest, reply: FastifyReply) {
  await accountAuth(req, reply)
  if (reply.sent) return
  if ((req as any).user.role === 'ADMIN') return
  const id = (req.params as any).id
  const { rows } = await pool.query(`SELECT 1 FROM courses c JOIN organization_members m ON m.organization_id=c.organization_id
    WHERE c.id=$1 AND m.user_id=$2 AND m.role='MANAGER'`, [id, userId(req)])
  if (!rows[0]) return reply.code(403).send({ message: '没有该课程的组织管理权限' })
}

app.get('/api/files/:stored', { preHandler: accountAuth }, async (req, reply) => {
  const stored = (req.params as any).stored as string
  const digitalFile = await pool.query(`SELECT f.stored_name,d.organization_id,d.personal_user_id FROM digital_instructor_files f JOIN digital_instructors d ON d.id=f.instructor_id WHERE f.stored_name=$1`, [stored])
  if (digitalFile.rows[0]) {
    const file = digitalFile.rows[0]
    const permitted = file.personal_user_id === userId(req) || (file.organization_id && ((req as any).user.role === 'ADMIN' || (await pool.query('SELECT 1 FROM organization_members WHERE organization_id=$1 AND user_id=$2', [file.organization_id,userId(req)])).rows[0]))
    if (!permitted) return reply.code(403).send({ message: '无权访问该文件' })
    return reply.sendFile(stored, uploadDir)
  }
  const { rows } = await pool.query(`SELECT c.user_id,c.organization_id FROM courses c WHERE
    EXISTS(SELECT 1 FROM course_assets a WHERE a.course_id=c.id AND a.stored_name=$1)
    OR EXISTS(SELECT 1 FROM course_deliverables d WHERE d.course_id=c.id AND d.stored_name=$1)
    OR EXISTS(SELECT 1 FROM instructors i WHERE i.course_id=c.id AND i.image_stored_name=$1)
    OR EXISTS(SELECT 1 FROM instructor_images ii JOIN instructors i ON i.id=ii.instructor_id WHERE i.course_id=c.id AND ii.stored_name=$1)
    LIMIT 1`, [stored])
  const course = rows[0]
  if (!course) return reply.code(404).send({ message: '文件不存在' })
  if ((req as any).user.role !== 'ADMIN') {
    const member = await pool.query(`SELECT 1 FROM organization_members WHERE organization_id=$1 AND user_id=$2 AND role='MANAGER'`,
      [course.organization_id, userId(req)])
    const ownerAllowed = course.user_id === userId(req) && (!course.organization_id ||
      (await pool.query('SELECT 1 FROM organization_members WHERE organization_id=$1 AND user_id=$2', [course.organization_id, userId(req)])).rows[0])
    if (!member.rows[0] && !ownerAllowed) return reply.code(403).send({ message: '无权访问该文件' })
  }
  return reply.sendFile(stored, uploadDir)
})

app.get('/api/health', async () => ({ ok: true }))

// ==================== 认证相关 ====================

app.post('/api/auth/register', async (req, reply) => {
  const p = phonePassword.extend({
    contactName: z.string().trim().min(1, '请填写姓名').max(80, '姓名不能超过 80 个字'),
    inviteToken: z.string().optional()
  }).safeParse(req.body)
  if (!p.success) return reply.code(400).send({ message: p.error.issues[0].message })
  if ((await pool.query('SELECT 1 FROM platform_admins WHERE phone=$1', [p.data.phone])).rows[0]) return reply.code(409).send({ message: '该手机号不可注册普通用户' })
  let organizationId: string | null = null
  if (p.data.inviteToken) {
    const invite = await pool.query('SELECT id FROM organizations WHERE invite_token_hash=$1 AND is_active=TRUE', [sha(p.data.inviteToken)])
    if (!invite.rows[0]) return reply.code(404).send({ message: '注册链接无效或已停用' })
    organizationId = invite.rows[0].id
  }
  try {
    const id = randomUUID()
    const hash = await hashPassword(p.data.password)
    const client = await pool.connect()
    let user: any
    try {
      await client.query('BEGIN')
      const { rows } = await client.query(
        'INSERT INTO users(id, phone, password_hash, role, contact_name, points_balance) VALUES($1, $2, $3, $4, $5, 10000) RETURNING *',
        [id, p.data.phone, hash, 'USER', p.data.contactName]
      )
      user = rows[0]
      if (organizationId) await client.query('INSERT INTO organization_members(organization_id,user_id) VALUES($1,$2)', [organizationId, id])
      await client.query('COMMIT')
    } catch (error) { await client.query('ROLLBACK'); throw error } finally { client.release() }
    return reply.code(201).send({ token: await session(id), user: mapUser(user) })
  } catch (e: any) {
    if (e.code === '23505') return reply.code(409).send({ message: '该手机号已注册' })
    throw e
  }
})

app.post('/api/auth/login', async (req, reply) => {
  const p = phonePassword.safeParse(req.body)
  if (!p.success) return reply.code(400).send({ message: '手机号或密码格式不正确' })
  const { rows } = await pool.query('SELECT * FROM users WHERE phone = $1', [p.data.phone])
  if (!rows[0] || !(await verifyPassword(p.data.password, rows[0].password_hash))) {
    return reply.code(401).send({ message: '手机号或密码错误' })
  }
  return { token: await session(rows[0].id), user: mapUser(rows[0]) }
})

app.post('/api/admin/auth/login', async (req, reply) => {
  const p = phonePassword.safeParse(req.body)
  if (!p.success) return reply.code(400).send({ message: '手机号或密码格式不正确' })
  const { rows } = await pool.query('SELECT * FROM platform_admins WHERE phone = $1', [p.data.phone])
  if (!rows[0] || !(await verifyPassword(p.data.password, rows[0].password_hash))) {
    return reply.code(401).send({ message: '管理员账号或密码错误' })
  }
  return { token: await session(rows[0].id, true), user: mapUser({ ...rows[0], role: 'ADMIN' }) }
})

app.post('/api/auth/logout', { preHandler: accountAuth }, async (req) => {
  const token = req.headers.authorization!.replace(/^Bearer\s+/i, '')
  await pool.query('DELETE FROM sessions WHERE token_hash = $1', [sha(token)])
  await pool.query('DELETE FROM platform_admin_sessions WHERE token_hash = $1', [sha(token)])
  return { ok: true }
})

app.get('/api/me', { preHandler: accountAuth }, async (req) => ({ user: mapUser((req as any).user) }))

app.get('/api/me/points-transactions', { preHandler: auth }, async (req) => {
  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='PERSONAL' AND l.owner_id=$1 ORDER BY l.created_at DESC`,
    [userId(req)]
  )
  return {
    balance: Number((req as any).user.points_balance) || 0,
    transactions: rows.map((row) => ({
      id: row.id,
      courseId: row.course_id,
      courseName: row.course_name || row.reason,
      points: Number(row.amount),
      createdAt: row.created_at
    }))
  }
})

app.patch('/api/me', { preHandler: auth }, async (req, reply) => {
  const p = z
    .object({
      wechat: z.string().max(80),
      contactName: z.string().max(80),
      bio: z.string().max(500)
    })
    .safeParse(req.body)
  if (!p.success) return reply.code(400).send({ message: '用户信息格式不正确' })
  const d = p.data
  const { rows } = await pool.query(
    'UPDATE users SET wechat = $1, contact_name = $2, bio = $3, updated_at = NOW() WHERE id = $4 RETURNING *',
    [d.wechat, d.contactName, d.bio, userId(req)]
  )
  return { user: mapUser(rows[0]) }
})

app.post('/api/me/password', { preHandler: auth }, async (req, reply) => {
  const p = z
    .object({
      currentPassword: z.string(),
      newPassword: z.string().min(6).max(72)
    })
    .safeParse(req.body)
  if (!p.success) return reply.code(400).send({ message: '新密码至少 6 位' })
  if (!(await verifyPassword(p.data.currentPassword, (req as any).user.password_hash))) {
    return reply.code(400).send({ message: '当前密码不正确' })
  }
  await pool.query('UPDATE users SET password_hash = $1, must_change_password = FALSE, updated_at = NOW() WHERE id = $2', [
    await hashPassword(p.data.newPassword),
    userId(req)
  ])
  return { ok: true }
})

// ==================== 普通用户课程操作 ====================

const courseInput = z.object({
  name: z.string().trim().min(1, '请输入课程名称').max(100),
  description: z.string().max(500).default(''),
  digitalInstructorId: z.string().uuid().nullable().optional()
})

async function validDigitalInstructor(id: string, organizationId: string | null, personalUserId: string) {
  const row = await digitalInstructors.get(id)
  if (!row || row.organization_id !== organizationId || (organizationId === null && row.personal_user_id !== personalUserId)) return false
  return true
}
async function readyDigitalInstructor(id: string) {
  const files = await digitalInstructors.files(id)
  return files.some(f => f.kind === 'IMAGE') && files.some(f => f.kind === 'AUDIO')
}

app.get('/api/course-templates', async () => ({ templates: COURSE_TEMPLATES }))

app.get('/api/courses', { preHandler: auth }, async (req, reply) => {
  const workspace = req.headers['x-workspace-id'] as string | undefined
  if (workspace && workspace !== 'personal') {
    const member = await pool.query('SELECT 1 FROM organization_members WHERE organization_id=$1 AND user_id=$2', [workspace, userId(req)])
    if (!member.rows[0]) return reply.code(403).send({ message: '您不属于该组织' })
  }
  const organizationWorkspace = workspace && workspace !== 'personal'
  const { rows } = organizationWorkspace
    ? await pool.query('SELECT * FROM courses WHERE organization_id=$1 AND user_id=$2 ORDER BY created_at DESC', [workspace, userId(req)])
    : await pool.query('SELECT * FROM courses WHERE organization_id IS NULL AND user_id=$1 ORDER BY created_at DESC', [userId(req)])
  return { courses: await Promise.all(rows.map((r) => fullCourse(r))) }
})

app.post('/api/courses', { preHandler: auth }, async (req, reply) => {
  const p = courseInput.safeParse(req.body)
  if (!p.success) return reply.code(400).send({ message: p.error.issues[0]?.message || '请完整填写课程信息' })
  const id = randomUUID()
  const d = p.data
  const workspace = req.headers['x-workspace-id'] as string | undefined
  const organizationId = workspace && workspace !== 'personal' ? workspace : null
  if (organizationId) {
    const member = await pool.query(`SELECT 1 FROM organization_members m JOIN organizations o ON o.id=m.organization_id
      WHERE m.organization_id=$1 AND m.user_id=$2 AND o.is_active=TRUE`, [organizationId, userId(req)])
    if (!member.rows[0]) return reply.code(403).send({ message: '您不属于该组织' })
  }
  if (d.digitalInstructorId && !await validDigitalInstructor(d.digitalInstructorId, organizationId, userId(req))) return reply.code(400).send({ message: '请选择本工作区的数字讲师' })
  const { rows } = await pool.query(
    'INSERT INTO courses(id, user_id, organization_id, name, description, digital_instructor_id) VALUES($1, $2, $3, $4, $5, $6) RETURNING *',
    [id, userId(req), organizationId, d.name, d.description, d.digitalInstructorId || null]
  )

  // 课程创建后默认创建第 1 集内容，确保分集不为空
  const defaultEpId = randomUUID()
  const defaultTitle = '第 1 集：' + d.name
  await pool.query(
    'INSERT INTO episodes(id, course_id, episode_number, title, summary, lecture_notes) VALUES($1, $2, 1, $3, $4, $5)',
    [defaultEpId, id, defaultTitle, '', '']
  )

  await addProgressEvent(id, 'USER', 'COURSE_CREATED', '课程已创建', '课程草稿创建成功。')
  await pool.query(`INSERT INTO audit_events(id,organization_id,course_id,actor_id,subject_user_id,action,details)
    VALUES($1,$2,$3,$4,$4,'COURSE_CREATED',$5)`, [randomUUID(), organizationId, id, userId(req), JSON.stringify({ name: d.name })])

  return reply.code(201).send({ course: await fullCourse(rows[0]) })
})

app.post('/api/courses/template', { preHandler: auth }, async (req, reply) => {
  const p = z.object({
    templateId: z.string().default('party-building-standard')
  }).safeParse(req.body || {})
  if (!p.success) return reply.code(400).send({ message: '模板参数不正确' })

  const template = COURSE_TEMPLATES.find((t) => t.id === p.data.templateId) || COURSE_TEMPLATES[0]
  const courseId = randomUUID()
  const workspace = req.headers['x-workspace-id'] as string | undefined
  const organizationId = workspace && workspace !== 'personal' ? workspace : null
  if (organizationId) {
    const member = await pool.query(`SELECT 1 FROM organization_members m JOIN organizations o ON o.id=m.organization_id
      WHERE m.organization_id=$1 AND m.user_id=$2 AND o.is_active=TRUE`, [organizationId, userId(req)])
    if (!member.rows[0]) return reply.code(403).send({ message: '您不属于该组织' })
  }

  const { rows } = await pool.query(
    'INSERT INTO courses(id, user_id, organization_id, name, description) VALUES($1, $2, $3, $4, $5) RETURNING *',
    [courseId, userId(req), organizationId, template.name, template.description]
  )

  await populateCourseFromTemplate(pool, courseId, template.id, uploadDir, sampleDir)

  await refreshCoursePoints(courseId)
  await addProgressEvent(courseId, 'USER', 'COURSE_CREATED', '从模板创建课程', '已按模板生成课程内容。')
  await pool.query(`INSERT INTO audit_events(id,organization_id,course_id,actor_id,subject_user_id,action,details)
    VALUES($1,$2,$3,$4,$4,'COURSE_CREATED_FROM_TEMPLATE',$5)`, [randomUUID(), organizationId, courseId, userId(req), JSON.stringify({ templateId: template.id })])

  return reply.code(201).send({ course: await fullCourse(rows[0]) })
})

app.get('/api/courses/:id', { preHandler: auth }, async (req, reply) => {
  const { id } = req.params as any
  const row = await ownedCourse(id, userId(req))
  if (!row) return reply.code(404).send({ message: '课程不存在' })
  return { course: await fullCourse(row) }
})

app.patch('/api/courses/:id', { preHandler: auth }, async (req, reply) => {
  const { id } = req.params as any
  const row = await ownedCourse(id, userId(req))
  if (!row) return reply.code(404).send({ message: '课程不存在' })

  const p = courseInput.pick({ name: true, description: true, digitalInstructorId: true }).safeParse(req.body)
  if (!p.success) {
    return reply.code(400).send({ message: p.error.issues[0]?.message || '请检查课程信息' })
  }

  if (p.data.digitalInstructorId !== undefined && row.status !== 'DRAFT') return reply.code(409).send({ message: '只有草稿课程可以更换讲师' })
  if (p.data.digitalInstructorId && !await validDigitalInstructor(p.data.digitalInstructorId,row.organization_id,userId(req))) return reply.code(400).send({ message: '请选择本工作区的数字讲师' })
  const { rows } = await pool.query(
    'UPDATE courses SET name = $1, description = $2, digital_instructor_id=COALESCE($4,digital_instructor_id), updated_at = NOW() WHERE id = $3 RETURNING *',
    [p.data.name, p.data.description, id, p.data.digitalInstructorId || null]
  )
  await refreshCoursePoints(id)
  await addProgressEvent(id, 'USER', 'CONTENT_UPDATED', '课程基本信息已修改', '课程名称或需求说明已更新。')
  return { course: await fullCourse(rows[0]) }
})

app.post('/api/courses/:id/cancel-submission', { preHandler: auth }, async (req, reply) => {
  const { id } = req.params as any
  const row = await ownedCourse(id, userId(req))
  if (!row) return reply.code(404).send({ message: '课程不存在' })
  if (row.status !== 'WAITING_PRODUCTION') {
    return reply.code(409).send({ message: '只有等待制作的课程可以恢复为草稿' })
  }

  const { rows } = await pool.query(
    "UPDATE courses SET status = 'DRAFT', submitted_at = NULL, updated_at = NOW() WHERE id = $1 RETURNING *",
    [id]
  )
  await addProgressEvent(id, 'USER', 'SUBMISSION_CANCELLED', '已撤回制作申请', '课程恢复为草稿，可继续修改后重新提交。')
  return { course: await fullCourse(rows[0]) }
})

app.delete('/api/courses/:id', { preHandler: auth }, async (req, reply) => {
  const { id } = req.params as any
  const row = await ownedCourse(id, userId(req))
  if (!row) return reply.code(404).send({ message: '课程不存在' })

  // 已经等待制作、制作中或者制作完成的课程不能删除
  const manager = row.organization_id && (await pool.query(`SELECT 1 FROM organization_members WHERE organization_id=$1 AND user_id=$2 AND role='MANAGER'`,
    [row.organization_id, userId(req)])).rows[0]
  if (!manager && (row.points_charged || row.status === 'WAITING_PRODUCTION' || row.status === 'IN_PRODUCTION' || row.status === 'COMPLETED')) {
    return reply.code(409).send({ message: '课程已进入制作排期或已制作完成，不可删除' })
  }

  // 1. 清理该课程关联的所有素材文件
  const assetsRes = await pool.query('SELECT stored_name FROM course_assets WHERE course_id = $1', [id])
  for (const ast of assetsRes.rows) {
    if (ast.stored_name) await unlink(resolve(uploadDir, ast.stored_name)).catch(() => {})
  }

  // 2. 清理讲师头像图片
  const instRes = await pool.query('SELECT image_stored_name FROM instructors WHERE course_id = $1', [id])
  const instImagesRes = await pool.query(
    'SELECT ii.stored_name FROM instructor_images ii JOIN instructors i ON i.id = ii.instructor_id WHERE i.course_id = $1',
    [id]
  )
  const instructorStoredNames = new Set(instImagesRes.rows.map((image) => image.stored_name))
  for (const ins of instRes.rows) {
    if (ins.image_stored_name) instructorStoredNames.add(ins.image_stored_name)
  }
  for (const storedName of instructorStoredNames) {
    const shared = await pool.query('SELECT 1 FROM digital_instructor_files WHERE stored_name=$1', [storedName])
    if (!shared.rows[0]) await unlink(resolve(uploadDir, storedName as string)).catch(() => {})
  }

  // 3. 清理交付成品文件
  const delivRes = await pool.query('SELECT stored_name FROM course_deliverables WHERE course_id = $1', [id])
  for (const del of delivRes.rows) {
    if (del.stored_name) await unlink(resolve(uploadDir, del.stored_name)).catch(() => {})
  }

  // 4. 清理 PPT 文件
  if (row.ppt_stored_name) {
    await unlink(resolve(uploadDir, row.ppt_stored_name)).catch(() => {})
  }

  // 5. 从数据库中删除课程（级联删除 episodes, course_assets, instructors 等）
  await pool.query('DELETE FROM courses WHERE id = $1', [id])

  return { ok: true, message: '课程已成功删除' }
})

app.post('/api/courses/:id/assets', { preHandler: auth }, async (req, reply) => {
  const { id } = req.params as any
  const row = await ownedCourse(id, userId(req))
  if (!row) return reply.code(404).send({ message: '课程不存在' })
  if (row.status !== 'DRAFT') return reply.code(409).send({ message: '课程已提交，不可再上传资产' })

  let episodeId: string | null = (req.query as any)?.episodeId || null
  const fileList: Array<{ filename: string; stored: string; bytes: number; mimetype: string }> = []

  for await (const part of req.parts()) {
    if (part.type === 'file') {
      const ext = extname(part.filename).slice(0, 16)
      const stored = id + '-' + randomUUID() + ext
      const target = resolve(uploadDir, stored)
      await pipeline(part.file, createWriteStream(target))
      if (part.file.truncated) {
        await unlink(target).catch(() => {})
        return reply.code(413).send({ message: '文件超过大小限制' })
      }
      fileList.push({
        filename: part.filename,
        stored,
        bytes: part.file.bytesRead,
        mimetype: part.mimetype
      })
    } else if (part.fieldname === 'episodeId' && part.value) {
      episodeId = String(part.value)
    }
  }

  if (episodeId) {
    const epCheck = await pool.query('SELECT id FROM episodes WHERE id = $1 AND course_id = $2', [episodeId, id])
    if (!epCheck.rows[0]) episodeId = null
  }

  if (!fileList.length) return reply.code(400).send({ message: '请选择课程资产文件' })

  for (const f of fileList) {
    await pool.query(
      'INSERT INTO course_assets(id, course_id, episode_id, original_name, stored_name, size, mime_type) VALUES($1, $2, $3, $4, $5, $6, $7)',
      [randomUUID(), id, episodeId, f.filename, f.stored, f.bytes, f.mimetype]
    )
  }

  await pool.query('UPDATE courses SET updated_at = NOW() WHERE id = $1', [id])
  return { course: await fullCourse(row) }
})

app.delete('/api/courses/:id/assets/:assetId', { preHandler: auth }, async (req, reply) => {
  const { id, assetId } = req.params as any
  const row = await ownedCourse(id, userId(req))
  if (!row) return reply.code(404).send({ message: '课程不存在' })
  if (row.status !== 'DRAFT') return reply.code(409).send({ message: '课程已提交，不可删除资产' })
  const { rows } = await pool.query(
    'DELETE FROM course_assets WHERE id = $1 AND course_id = $2 RETURNING stored_name',
    [assetId, id]
  )
  if (rows[0]) await unlink(resolve(uploadDir, rows[0].stored_name)).catch(() => {})
  await pool.query('UPDATE courses SET updated_at = NOW() WHERE id = $1', [id])
  return { course: await fullCourse(row) }
})

// ==================== 课程分集操作 (Episodes) ====================

// 1. 创建单集
app.post('/api/courses/:id/episodes', { preHandler: auth }, async (req, reply) => {
  const { id } = req.params as any
  const row = await ownedCourse(id, userId(req))
  if (!row) return reply.code(404).send({ message: '课程不存在' })
  if (row.status !== 'DRAFT') return reply.code(409).send({ message: '课程已提交，不可再添加分集' })

  const p = z.object({
    title: z.string().trim().min(1, '请输入分集标题').max(200),
    episodeNumber: z.number().int().min(1).optional(),
    summary: z.string().max(500).default(''),
    lectureNotes: z.string().default('')
  }).safeParse(req.body)

  if (!p.success) return reply.code(400).send({ message: p.error.issues[0]?.message || '分集信息填写不完整' })

  let epNumber = p.data.episodeNumber
  if (!epNumber) {
    const maxRes = await pool.query('SELECT COALESCE(MAX(episode_number), 0) AS max_num FROM episodes WHERE course_id = $1', [id])
    epNumber = (Number(maxRes.rows[0].max_num) || 0) + 1
  }

  const epId = randomUUID()
  await pool.query(
    'INSERT INTO episodes(id, course_id, episode_number, title, summary, lecture_notes) VALUES($1, $2, $3, $4, $5, $6)',
    [epId, id, epNumber, p.data.title, p.data.summary, p.data.lectureNotes]
  )

  await refreshCoursePoints(id)
  await addProgressEvent(id, 'USER', 'CONTENT_UPDATED', '新增课程分集', '课程结构已更新。')

  return reply.code(201).send({ course: await fullCourse(row) })
})

// 2. 批量创建分集（如快速生成 12 集）
app.post('/api/courses/:id/episodes/batch', { preHandler: auth }, async (req, reply) => {
  const { id } = req.params as any
  const row = await ownedCourse(id, userId(req))
  if (!row) return reply.code(404).send({ message: '课程不存在' })
  if (row.status !== 'DRAFT') return reply.code(409).send({ message: '课程已提交，不可再添加分集' })

  const p = z.object({
    count: z.number().int().min(1).max(100).optional(),
    episodes: z.array(z.object({
      title: z.string().trim().min(1).max(200),
      episodeNumber: z.number().int().min(1).optional(),
      summary: z.string().max(500).default(''),
      lectureNotes: z.string().default('')
    })).optional()
  }).safeParse(req.body)

  if (!p.success) return reply.code(400).send({ message: '批量创建参数不正确' })

  const maxRes = await pool.query('SELECT COALESCE(MAX(episode_number), 0) AS max_num FROM episodes WHERE course_id = $1', [id])
  let currentMax = Number(maxRes.rows[0].max_num) || 0

  if (p.data.episodes && p.data.episodes.length > 0) {
    for (const ep of p.data.episodes) {
      currentMax++
      await pool.query(
        'INSERT INTO episodes(id, course_id, episode_number, title, summary, lecture_notes) VALUES($1, $2, $3, $4, $5, $6)',
        [randomUUID(), id, ep.episodeNumber || currentMax, ep.title, ep.summary, ep.lectureNotes]
      )
    }
  } else if (p.data.count) {
    for (let i = 1; i <= p.data.count; i++) {
      currentMax++
      const title = `第 ${currentMax} 集：课程知识点精讲`
      await pool.query(
        'INSERT INTO episodes(id, course_id, episode_number, title, summary, lecture_notes) VALUES($1, $2, $3, $4, $5, $6)',
        [randomUUID(), id, currentMax, title, '', '']
      )
    }
  } else {
    return reply.code(400).send({ message: '请指定集数或分集列表' })
  }

  await refreshCoursePoints(id)
  await addProgressEvent(id, 'USER', 'CONTENT_UPDATED', '批量新增课程分集', '课程结构已更新。')

  return reply.code(201).send({ course: await fullCourse(row) })
})

// 3. 更新分集信息（标题、序号、讲稿文本等）
app.patch('/api/courses/:id/episodes/:episodeId', { preHandler: auth }, async (req, reply) => {
  const { id, episodeId } = req.params as any
  const row = await ownedCourse(id, userId(req))
  if (!row) return reply.code(404).send({ message: '课程不存在' })
  if (!['DRAFT', 'WAITING_PRODUCTION'].includes(row.status)) {
    return reply.code(409).send({ message: '课程已进入制作或完成归档，无法修改分集' })
  }

  const p = z.object({
    title: z.string().trim().min(1, '标题不能为空').max(200).optional(),
    episodeNumber: z.number().int().min(1).optional(),
    summary: z.string().max(500).optional(),
    lectureNotes: z.string().optional()
  }).safeParse(req.body)

  if (!p.success) return reply.code(400).send({ message: p.error.issues[0]?.message || '参数错误' })

  const existing = await pool.query('SELECT * FROM episodes WHERE id = $1 AND course_id = $2', [episodeId, id])
  if (!existing.rows[0]) return reply.code(404).send({ message: '分集不存在' })

  const cur = existing.rows[0]
  const newTitle = p.data.title !== undefined ? p.data.title : cur.title
  const newNumber = p.data.episodeNumber !== undefined ? p.data.episodeNumber : cur.episode_number
  const newSummary = p.data.summary !== undefined ? p.data.summary : cur.summary
  const newNotes = p.data.lectureNotes !== undefined ? p.data.lectureNotes : cur.lecture_notes

  await pool.query(
    'UPDATE episodes SET title = $1, episode_number = $2, summary = $3, lecture_notes = $4, updated_at = NOW() WHERE id = $5 AND course_id = $6',
    [newTitle, newNumber, newSummary, newNotes, episodeId, id]
  )

  await refreshCoursePoints(id)
  await addProgressEvent(id, 'USER', 'CONTENT_UPDATED', '课程内容已修改', '分集内容已更新。')

  return { course: await fullCourse(row) }
})

// 4. 删除分集及其关联的物理素材文件
app.delete('/api/courses/:id/episodes/:episodeId', { preHandler: auth }, async (req, reply) => {
  const { id, episodeId } = req.params as any
  const row = await ownedCourse(id, userId(req))
  if (!row) return reply.code(404).send({ message: '课程不存在' })
  if (row.status !== 'DRAFT') return reply.code(409).send({ message: '课程已提交，不可删除分集' })

  // 课程至少需要保留一个分集，不可删除唯一分集
  const countRes = await pool.query('SELECT COUNT(*)::int AS count FROM episodes WHERE course_id = $1', [id])
  if ((countRes.rows[0]?.count || 0) <= 1) {
    return reply.code(400).send({ message: '课程至少需要保留一个分集，不可删除唯一分集' })
  }

  // 查出该集下所有素材文件并清理磁盘
  const astRes = await pool.query('SELECT stored_name FROM course_assets WHERE episode_id = $1', [episodeId])
  for (const ast of astRes.rows) {
    await unlink(resolve(uploadDir, ast.stored_name)).catch(() => {})
  }

  await pool.query('DELETE FROM episodes WHERE id = $1 AND course_id = $2', [episodeId, id])
  await refreshCoursePoints(id)
  await addProgressEvent(id, 'USER', 'CONTENT_UPDATED', '删除课程分集', '课程结构已更新。')
  return { course: await fullCourse(row) }
})

app.post('/api/courses/:id/submit', { preHandler: auth }, async (req, reply) => {
  const { id } = req.params as any
  const row = await ownedCourse(id, userId(req))
  if (!row) return reply.code(404).send({ message: '课程不存在' })
  if (!row.digital_instructor_id || !await validDigitalInstructor(row.digital_instructor_id,row.organization_id,userId(req)) || !await readyDigitalInstructor(row.digital_instructor_id)) return reply.code(409).send({ message: '提交前请选择讲师，并补齐照片和录音' })
  const snapshot = await digitalInstructors.dto(await digitalInstructors.get(row.digital_instructor_id))
  const estimate = await refreshCoursePoints(id)
  const balance = row.organization_id
    ? Number((await pool.query('SELECT points_balance FROM organizations WHERE id=$1', [row.organization_id])).rows[0]?.points_balance) || 0
    : Number((req as any).user.points_balance) || 0
  const estimatedPoints = estimate.points
  if (balance < estimatedPoints) return reply.code(409).send({ message: `积分不足：本课程需要 ${estimatedPoints} 积分，当前余额 ${balance} 积分` })
  const { rows } = await pool.query(
    "UPDATE courses SET status = 'WAITING_PRODUCTION', digital_instructor_snapshot=$2, submitted_at = NOW(), updated_at = NOW() WHERE id = $1 AND status = 'DRAFT' AND digital_instructor_id=$3 RETURNING *",
    [id,JSON.stringify(snapshot),row.digital_instructor_id]
  )
  if (!rows[0]) return reply.code(409).send({ message: '课程已经提交或不存在' })
  await addProgressEvent(id, 'USER', 'SUBMITTED', '已提交制作', '课程已进入制作队列。')
  return { course: await fullCourse(rows[0]) }
})

// ==================== 管理员专属 ADMIN API ====================

// 1. 统计概览数据
app.get('/api/admin/stats', { preHandler: adminAuth }, async () => {
  const [uRes, cRes, sRes] = await Promise.all([
    pool.query('SELECT COUNT(*)::int AS count FROM users'),
    pool.query('SELECT COUNT(*)::int AS count FROM courses'),
    pool.query(`
      SELECT
        COUNT(*) FILTER (WHERE status = 'WAITING_PRODUCTION')::int AS waiting_count,
        COUNT(*) FILTER (WHERE status = 'IN_PRODUCTION')::int AS in_production_count,
        COUNT(*) FILTER (WHERE status = 'COMPLETED')::int AS completed_count,
        COUNT(*) FILTER (WHERE status = 'DRAFT')::int AS draft_count
      FROM courses
    `)
  ])

  return {
    totalUsers: uRes.rows[0].count,
    totalCourses: cRes.rows[0].count,
    waitingCount: sRes.rows[0].waiting_count || 0,
    inProductionCount: sRes.rows[0].in_production_count || 0,
    completedCount: sRes.rows[0].completed_count || 0,
    draftCount: sRes.rows[0].draft_count || 0
  }
})

// 2. 获取全量课程列表（支持状态筛选与搜索）
app.get('/api/admin/courses', { preHandler: adminAuth }, async (req) => {
  const query = (req.query || {}) as { status?: string; keyword?: string; page?: string; limit?: string }
  const params: any[] = []
  const conditions: string[] = []

  if (query.status && query.status !== 'ALL') {
    params.push(query.status)
    conditions.push(`c.status = $${params.length}`)
  }

  if (query.keyword && query.keyword.trim()) {
    params.push(`%${query.keyword.trim()}%`)
    const idx = params.length
    conditions.push(`(c.name ILIKE $${idx} OR u.phone ILIKE $${idx} OR u.contact_name ILIKE $${idx})`)
  }

  const whereClause = conditions.length ? 'WHERE ' + conditions.join(' AND ') : ''
  const sql = `
    SELECT
      c.*,
      u.phone AS user_phone,
      u.role AS user_role,
      u.wechat AS user_wechat,
      u.contact_name AS user_contact_name,
      u.bio AS user_bio,
      u.created_at AS user_created_at
    FROM courses c
    LEFT JOIN users u ON u.id = c.user_id
    ${whereClause}
    ORDER BY
      CASE
        WHEN c.status = 'WAITING_PRODUCTION' THEN 1
        WHEN c.status = 'IN_PRODUCTION' THEN 2
        WHEN c.status = 'DRAFT' THEN 3
        WHEN c.status = 'COMPLETED' THEN 4
        ELSE 5
      END,
      c.submitted_at DESC NULLS LAST,
      c.created_at DESC
  `

  const { rows } = await pool.query(sql, params)
  const courses = await Promise.all(rows.map((r) => fullCourse(r, true)))
  return { courses, total: courses.length }
})

// 3. 获取特定课程完整详情（供制作工作台使用）
app.get('/api/admin/courses/:id', { preHandler: courseOperator }, async (req, reply) => {
  const { id } = req.params as any
  const sql = `
    SELECT
      c.*,
      u.phone AS user_phone,
      u.role AS user_role,
      u.wechat AS user_wechat,
      u.contact_name AS user_contact_name,
      u.bio AS user_bio,
      u.created_at AS user_created_at
    FROM courses c
    LEFT JOIN users u ON u.id = c.user_id
    WHERE c.id = $1
  `
  const { rows } = await pool.query(sql, [id])
  if (!rows[0]) return reply.code(404).send({ message: '课程不存在' })
  const course = await fullCourse(rows[0], true)
  return { course }
})

// 4. 修改课程状态（例如接单转为 IN_PRODUCTION 或其他流转）
app.post('/api/admin/courses/:id/status', { preHandler: courseOperator }, async (req, reply) => {
  const { id } = req.params as any
  const p = z.object({
    status: z.enum(['DRAFT', 'WAITING_PRODUCTION', 'IN_PRODUCTION', 'COMPLETED', 'REJECTED'])
  }).safeParse(req.body)
  if (!p.success) return reply.code(400).send({ message: '无效的课程状态' })
  if (p.data.status === 'COMPLETED') return reply.code(400).send({ message: '请通过“确认交付”完成课程并结算积分' })

  const { rows } = await pool.query(
    'UPDATE courses SET status = $1, updated_at = NOW() WHERE id = $2 RETURNING *',
    [p.data.status, id]
  )
  if (!rows[0]) return reply.code(404).send({ message: '课程不存在' })
  const statusNames: Record<string, string> = { DRAFT: '草稿', WAITING_PRODUCTION: '等待制作', IN_PRODUCTION: '正在制作', COMPLETED: '已完成', REJECTED: '已驳回' }
  await addProgressEvent(id, 'ADMIN', 'STATUS_CHANGED', '制作状态已更新', `管理员将课程状态更新为“${statusNames[p.data.status]}”。`, { status: p.data.status })
  return { course: await fullCourse(rows[0], true) }
})

app.patch('/api/admin/courses/:id/points', { preHandler: courseOperator }, async (req, reply) => {
  const { id } = req.params as any
  const p = z.object({ points: z.number().int().min(1).max(100000), reason: z.string().max(500).default('根据实际制作需求调整') }).safeParse(req.body)
  if (!p.success) return reply.code(400).send({ message: '请输入有效的积分消耗（至少 1 积分）' })
  const previous = await pool.query('SELECT * FROM courses WHERE id = $1', [id])
  if (!previous.rows[0]) return reply.code(404).send({ message: '课程不存在' })
  if (previous.rows[0].points_charged) return reply.code(409).send({ message: '课程已完成扣费，不能再修改积分' })
  const { rows } = await pool.query('UPDATE courses SET actual_points = $1, updated_at = NOW() WHERE id = $2 RETURNING *', [p.data.points, id])
  const oldPoints = previous.rows[0].actual_points ?? previous.rows[0].estimated_points
  await addProgressEvent(id, 'ADMIN', 'POINTS_UPDATED', '积分消耗已调整', `管理员将制作消耗从 ${oldPoints} 积分调整为 ${p.data.points} 积分。${p.data.reason}`, { previousPoints: Number(oldPoints), points: p.data.points })
  return { course: await fullCourse(rows[0], true) }
})

// 5. 管理员上传制作交付成品文件
app.post('/api/admin/courses/:id/deliverables', { preHandler: courseOperator }, async (req, reply) => {
  const { id } = req.params as any
  const check = await pool.query('SELECT * FROM courses WHERE id = $1', [id])
  if (!check.rows[0]) return reply.code(404).send({ message: '课程不存在' })

  let episodeId: string | null = (req.query as any)?.episodeId || null
  const fileList: Array<{ filename: string; stored: string; bytes: number; mimetype: string }> = []

  for await (const part of req.parts()) {
    if (part.type === 'file') {
      const ext = extname(part.filename).slice(0, 16)
      const stored = 'deliverable-' + id + '-' + randomUUID() + ext
      const target = resolve(uploadDir, stored)
      await pipeline(part.file, createWriteStream(target))
      if (part.file.truncated) {
        await unlink(target).catch(() => {})
        return reply.code(413).send({ message: '文件超过大小限制' })
      }
      fileList.push({
        filename: part.filename,
        stored,
        bytes: part.file.bytesRead,
        mimetype: part.mimetype
      })
    } else if (part.fieldname === 'episodeId' && part.value) {
      episodeId = String(part.value)
    }
  }

  if (episodeId) {
    const epCheck = await pool.query('SELECT id FROM episodes WHERE id = $1 AND course_id = $2', [episodeId, id])
    if (!epCheck.rows[0]) episodeId = null
  }

  if (!fileList.length) return reply.code(400).send({ message: '请选择交付成果文件' })

  for (const f of fileList) {
    await pool.query(
      'INSERT INTO course_deliverables(id, course_id, episode_id, original_name, stored_name, size, mime_type) VALUES($1, $2, $3, $4, $5, $6, $7)',
      [randomUUID(), id, episodeId, f.filename, f.stored, f.bytes, f.mimetype]
    )
  }

  await addProgressEvent(id, 'ADMIN', 'DELIVERABLE_UPLOADED', '管理员上传了制作成果', `新增 ${fileList.length} 个交付文件。`)

  return { course: await fullCourse(check.rows[0], true) }
})

// 6. 删除已上传的交付成果文件
app.delete('/api/admin/courses/:id/deliverables/:delivId', { preHandler: courseOperator }, async (req, reply) => {
  const { id, delivId } = req.params as any
  const check = await pool.query('SELECT * FROM courses WHERE id = $1', [id])
  if (!check.rows[0]) return reply.code(404).send({ message: '课程不存在' })

  const { rows } = await pool.query(
    'DELETE FROM course_deliverables WHERE id = $1 AND course_id = $2 RETURNING stored_name',
    [delivId, id]
  )
  if (rows[0]) await unlink(resolve(uploadDir, rows[0].stored_name)).catch(() => {})
  return { course: await fullCourse(check.rows[0], true) }
})

// 7. 提交制作成果，结单交付（流转为 COMPLETED）
app.post('/api/admin/courses/:id/complete', { preHandler: courseOperator }, async (req, reply) => {
  const { id } = req.params as any
  const p = z.object({
    productionNotes: z.string().max(2000).default('')
  }).safeParse(req.body || {})
  if (!p.success) return reply.code(400).send({ message: '交付说明格式不正确' })

  const client = await pool.connect()
  let rows: any[] = []
  try {
    await client.query('BEGIN')
    const current = await client.query('SELECT * FROM courses WHERE id = $1 FOR UPDATE', [id])
    if (!current.rows[0]) { await client.query('ROLLBACK'); return reply.code(404).send({ message: '课程不存在' }) }
    const cost = Number(current.rows[0].actual_points ?? current.rows[0].estimated_points) || 100
    if (!current.rows[0].points_charged) {
      const organizationId = current.rows[0].organization_id
      const charged = organizationId
        ? await client.query('UPDATE organizations SET points_balance=points_balance-$1,updated_at=NOW() WHERE id=$2 AND points_balance >= $1 RETURNING points_balance', [cost, organizationId])
        : await client.query('UPDATE users SET points_balance=points_balance-$1,updated_at=NOW() WHERE id=$2 AND points_balance >= $1 RETURNING points_balance', [cost, current.rows[0].user_id])
      if (!charged.rows[0]) { await client.query('ROLLBACK'); return reply.code(409).send({ message: `工作区积分不足，完成制作需要扣除 ${cost} 积分` }) }
      await client.query(`INSERT INTO points_ledger(id,owner_type,owner_id,course_id,course_name_snapshot,beneficiary_user_id,admin_actor_id,amount,reason)
        VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9)`, [randomUUID(), organizationId ? 'ORGANIZATION' : 'PERSONAL', organizationId || current.rows[0].user_id,
        id, current.rows[0].name, current.rows[0].user_id, (req as any).user.role === 'ADMIN' ? userId(req) : null, -cost, '课程完成扣费'])
    }
    const updated = await client.query("UPDATE courses SET status = 'COMPLETED', production_notes = $1, completed_at = NOW(), points_charged = TRUE, updated_at = NOW() WHERE id = $2 RETURNING *", [p.data.productionNotes, id])
    rows = updated.rows
    await client.query('COMMIT')
  } catch (error) { await client.query('ROLLBACK'); throw error } finally { client.release() }
  const cost = Number(rows[0].actual_points ?? rows[0].estimated_points) || 100
  await addProgressEvent(id, 'ADMIN', 'COMPLETED', '课程制作完成', `课程已交付，实际扣除 ${cost} 积分。`, { points: cost })
  return { course: await fullCourse(rows[0], true) }
})

// 8. 获取全平台用户列表及统计
app.get('/api/admin/users', { preHandler: adminAuth }, async (req) => {
  const query = (req.query || {}) as { keyword?: string }
  const params: any[] = []
  let where = ''
  if (query.keyword && query.keyword.trim()) {
    params.push(`%${query.keyword.trim()}%`)
    where = 'WHERE (u.phone ILIKE $1 OR u.contact_name ILIKE $1 OR u.wechat ILIKE $1)'
  }

  const sql = `
    SELECT
      u.*,
      (SELECT string_agg(o.name, '、') FROM organization_members m JOIN organizations o ON o.id=m.organization_id WHERE m.user_id=u.id) AS organization_names,
      COUNT(c.id)::int AS courses_count,
      COUNT(c.id) FILTER (WHERE c.status = 'WAITING_PRODUCTION')::int AS waiting_courses_count,
      COUNT(c.id) FILTER (WHERE c.status = 'IN_PRODUCTION')::int AS in_production_courses_count,
      COUNT(c.id) FILTER (WHERE c.status = 'COMPLETED')::int AS completed_courses_count
    FROM users u
    LEFT JOIN courses c ON c.user_id = u.id
    ${where}
    GROUP BY u.id
    ORDER BY u.created_at DESC
  `
  const { rows } = await pool.query(sql, params)
  const users = rows.map((r) => ({
    ...mapUser(r),
    organization: r.organization_names || '',
    coursesCount: r.courses_count || 0,
    waitingCoursesCount: r.waiting_courses_count || 0,
    inProductionCoursesCount: r.in_production_courses_count || 0,
    completedCoursesCount: r.completed_courses_count || 0
  }))
  return { users }
})

// 9. 管理员创建普通用户
app.post('/api/admin/users', { preHandler: adminAuth }, async (req, reply) => {
  const p = z.object({
    phone: z.string().regex(/^1\d{10}$/, '请输入正确的手机号码'),
    password: z.string().min(6, '密码至少 6 位').max(72),
    contactName: z.string().trim().max(50, '姓名不能超过 50 个字').optional().default('')
  }).safeParse(req.body)
  if (!p.success) return reply.code(400).send({ message: p.error.issues[0].message })
  if ((await pool.query('SELECT 1 FROM platform_admins WHERE phone=$1', [p.data.phone])).rows[0]) return reply.code(409).send({ message: '该手机号属于平台管理员' })

  try {
    const id = randomUUID()
    const passwordHash = await hashPassword(p.data.password)
    const { rows } = await pool.query(
      `INSERT INTO users(id, phone, password_hash, role, contact_name, points_balance)
       VALUES($1, $2, $3, 'USER', $4, 10000) RETURNING *`,
      [id, p.data.phone, passwordHash, p.data.contactName]
    )
    return reply.code(201).send({ user: mapUser(rows[0]) })
  } catch (e: any) {
    if (e.code === '23505') return reply.code(409).send({ message: '该手机号已注册' })
    throw e
  }
})

// 10. 管理员重置用户密码；同时注销该用户已有会话
app.patch('/api/admin/users/:id/password', { preHandler: adminAuth }, async (req, reply) => {
  const { id } = req.params as any
  const p = z.object({
    password: z.string().min(6, '密码至少 6 位').max(72)
  }).safeParse(req.body)
  if (!p.success) return reply.code(400).send({ message: p.error.issues[0].message })

  const client = await pool.connect()
  try {
    await client.query('BEGIN')
    const { rowCount } = await client.query(
      'UPDATE users SET password_hash = $1, updated_at = NOW() WHERE id = $2',
      [await hashPassword(p.data.password), id]
    )
    if (!rowCount) {
      await client.query('ROLLBACK')
      return reply.code(404).send({ message: '用户不存在' })
    }
    await client.query('DELETE FROM sessions WHERE user_id = $1', [id])
    await client.query('COMMIT')
    return { ok: true }
  } catch (error) {
    await client.query('ROLLBACK')
    throw error
  } finally {
    client.release()
  }
})

// 平台管理员与普通用户永不互转。
app.patch('/api/admin/users/:id/role', { preHandler: adminAuth }, async (req, reply) => {
  return reply.code(403).send({ message: '平台管理员与普通用户不能互相转化' })
})

// ==================== 静态托管与错误处理 ====================

if (process.env.NODE_ENV === 'production') {
  const dist = resolve('./dist')
  await app.register(staticPlugin, { root: dist, prefix: '/', wildcard: false, decorateReply: false })
  app.setNotFoundHandler((req, reply) =>
    req.url.startsWith('/api/') || req.url.startsWith('/uploads/')
      ? reply.code(404).send({ message: '接口或文件不存在' })
      : reply.sendFile('index.html', dist)
  )
}

app.setErrorHandler((error: FastifyError, _req, reply) => {
  app.log.error(error)
  const status = error.statusCode && error.statusCode < 500 ? error.statusCode : 500
  reply.code(status).send({ message: status === 500 ? '服务暂时不可用，请稍后重试' : error.message })
})

await app.listen({ port, host: process.env.HOST || '0.0.0.0' })