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'

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'))

// 检查是否存在管理员账号，若不存在则创建默认演示管理员
async function initDefaultAdmin() {
  try {
    const { rows } = await pool.query("SELECT id FROM users WHERE role = 'ADMIN' LIMIT 1")
    if (rows.length === 0) {
      const defaultPhone = process.env.ADMIN_PHONE || '18888888888'
      const defaultPassword = process.env.ADMIN_PASSWORD || 'admin123456'
      const existing = await pool.query('SELECT id FROM users WHERE phone = $1', [defaultPhone])
      const salt = randomBytes(16).toString('hex')
      const key = (await scrypt(defaultPassword, salt, 64)) as Buffer
      const hash = 'scrypt$' + salt + '$' + key.toString('hex')

      if (existing.rows.length > 0) {
        await pool.query("UPDATE users SET role = 'ADMIN' WHERE id = $1", [existing.rows[0].id])
        app.log.info(`Updated existing user ${defaultPhone} to ADMIN role`)
      } else {
        const id = randomUUID()
        await pool.query(
          "INSERT INTO users(id, phone, password_hash, role, contact_name, organization, bio) VALUES($1, $2, $3, 'ADMIN', $4, $5, $6)",
          [id, defaultPhone, hash, '系统管理员', '星痕课程工坊管理中心', '默认初始化管理员账号']
        )
        app.log.info(`Initialized default admin account for ${defaultPhone}`)
      }
    }
  } catch (err) {
    app.log.error(err, 'Failed to initialize default admin account')
  }
}
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, prefix: '/uploads/', decorateReply: 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) {
  const token = randomBytes(32).toString('hex')
  await pool.query("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]
}

async function adminAuth(req: FastifyRequest, reply: FastifyReply) {
  await auth(req, reply)
  if (reply.sent) return
  const user = (req as any).user
  if (!user || user.role !== 'ADMIN') {
    return reply.code(403).send({ message: '权限不足：仅管理员可访问此接口' })
  }
}

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] || {}) }
  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
  }
  const instructors = i.rows.map((instructor) => ({
    ...instructor,
    image_urls: instructorImages.rows
      .filter((image) => image.instructor_id === instructor.id)
      .map((image) => `/uploads/${image.stored_name}`)
  }))
  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 * FROM courses WHERE id = $1 AND user_id = $2', [id, uid])).rows[0]

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

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

app.post('/api/auth/register', async (req, reply) => {
  const p = phonePassword.safeParse(req.body)
  if (!p.success) return reply.code(400).send({ message: p.error.issues[0].message })
  try {
    const id = randomUUID()
    const hash = await hashPassword(p.data.password)
    const { rows } = await pool.query(
      'INSERT INTO users(id, phone, password_hash, role, points_balance) VALUES($1, $2, $3, $4, 10000) RETURNING *',
      [id, p.data.phone, hash, 'USER']
    )
    return reply.code(201).send({ token: await session(id), user: mapUser(rows[0]) })
  } 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 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: '管理员账号或密码错误' })
  }
  if (rows[0].role !== 'ADMIN') {
    return reply.code(403).send({ message: '该账号不是管理员，无权登录后台' })
  }
  return { token: await session(rows[0].id), user: mapUser(rows[0]) }
})

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

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

app.patch('/api/me', { preHandler: auth }, async (req, reply) => {
  const p = z
    .object({
      organization: z.string().max(120),
      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 organization = $1, wechat = $2, contact_name = $3, bio = $4, updated_at = NOW() WHERE id = $5 RETURNING *',
    [d.organization, 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, 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),
  category: z.string().max(50).default(''),
  description: z.string().max(500).default('')
})

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

app.get('/api/courses', { preHandler: auth }, async (req) => {
  const { rows } = await pool.query('SELECT * FROM courses WHERE 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 { rows } = await pool.query(
    'INSERT INTO courses(id, user_id, name, category, description) VALUES($1, $2, $3, $4, $5) RETURNING *',
    [id, userId(req), d.name, d.category, d.description]
  )

  // 课程创建后默认创建第 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', '课程已创建', '课程草稿创建成功。')

  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 { rows } = await pool.query(
    'INSERT INTO courses(id, user_id, name, category, description) VALUES($1, $2, $3, $4, $5) RETURNING *',
    [courseId, userId(req), template.name, template.category, template.description]
  )

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

  await refreshCoursePoints(courseId)
  await addProgressEvent(courseId, 'USER', 'COURSE_CREATED', '从模板创建课程', '已按模板生成课程内容。')

  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, category: true, description: true }).safeParse(req.body)
  if (!p.success) {
    return reply.code(400).send({ message: p.error.issues[0]?.message || '请检查课程信息' })
  }

  const { rows } = await pool.query(
    'UPDATE courses SET name = $1, category = $2, description = $3, updated_at = NOW() WHERE id = $4 AND user_id = $5 RETURNING *',
    [p.data.name, p.data.category, p.data.description, id, userId(req)]
  )
  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 AND user_id = $2 RETURNING *",
    [id, userId(req)]
  )
  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: '课程不存在' })

  // 已经等待制作、制作中或者制作完成的课程不能删除
  if (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) {
    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 AND user_id = $2', [id, userId(req)])

  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]
    )
  }

  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(() => {})
  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/instructors', { 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 fields: any = {}
  const images: any[] = []
  for await (const part of req.parts()) {
    if (part.type === 'file') {
      if (!part.mimetype.startsWith('image/')) return reply.code(400).send({ message: '讲师图片必须是图片格式' })
      const ext = extname(part.filename).slice(0, 12)
      const stored = 'instructor-' + randomUUID() + ext
      await pipeline(part.file, createWriteStream(resolve(uploadDir, stored)))
      images.push({ original: part.filename, stored, mime: part.mimetype })
    } else {
      fields[part.fieldname] = part.value
    }
  }
  const p = z
    .object({
      name: z.string().trim().min(1, '请填写讲师姓名').max(80),
      organization: z.string().max(120).default(''),
      introduction: z.string().max(1000).default('')
    })
    .safeParse(fields)
  if (!p.success) {
    await Promise.all(images.map((image) => unlink(resolve(uploadDir, image.stored)).catch(() => {})))
    return reply.code(400).send({ message: '请填写讲师姓名' })
  }
  const d = p.data
  const instructorId = randomUUID()
  const image = images[0]
  const { rows } = await pool.query(
    'INSERT INTO instructors(id, course_id, name, organization, introduction, image_original_name, image_stored_name, image_mime_type) VALUES($1, $2, $3, $4, $5, $6, $7, $8) RETURNING *',
    [
      instructorId,
      id,
      d.name,
      d.organization,
      d.introduction,
      image?.original || null,
      image?.stored || null,
      image?.mime || null
    ]
  )
  for (const [index, uploadedImage] of images.entries()) {
    await pool.query(
      'INSERT INTO instructor_images(id, instructor_id, original_name, stored_name, mime_type, sort_order) VALUES($1, $2, $3, $4, $5, $6)',
      [randomUUID(), instructorId, uploadedImage.original, uploadedImage.stored, uploadedImage.mime, index]
    )
  }
  return reply.code(201).send({ instructor: mapInstructor(rows[0]), course: await fullCourse(row) })
})

app.delete('/api/courses/:id/instructors/:instructorId', { preHandler: auth }, async (req, reply) => {
  const { id, instructorId } = 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 imageRows = await pool.query('SELECT stored_name FROM instructor_images WHERE instructor_id = $1', [instructorId])
  const { rows } = await pool.query(
    'DELETE FROM instructors WHERE id = $1 AND course_id = $2 RETURNING image_stored_name',
    [instructorId, id]
  )
  const storedNames = new Set(imageRows.rows.map((image) => image.stored_name))
  if (rows[0]?.image_stored_name) storedNames.add(rows[0].image_stored_name)
  await Promise.all([...storedNames].map((storedName) => unlink(resolve(uploadDir, storedName as string)).catch(() => {})))
  return { course: await fullCourse(row) }
})

app.post('/api/courses/:id/submit', { preHandler: auth }, async (req, reply) => {
  const { id } = req.params as any
  const estimate = await refreshCoursePoints(id)
  const balance = 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', submitted_at = NOW(), updated_at = NOW() WHERE id = $1 AND user_id = $2 AND status = 'DRAFT' RETURNING *",
    [id, userId(req)]
  )
  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 c.category ILIKE $${idx} OR u.phone ILIKE $${idx} OR u.contact_name ILIKE $${idx} OR u.organization 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.organization AS user_organization,
      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: adminAuth }, async (req, reply) => {
  const { id } = req.params as any
  const sql = `
    SELECT
      c.*,
      u.phone AS user_phone,
      u.role AS user_role,
      u.organization AS user_organization,
      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: adminAuth }, 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: adminAuth }, 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: adminAuth }, 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: adminAuth }, 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: adminAuth }, 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 charged = 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} 积分` }) }
    }
    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.organization ILIKE $1 OR u.wechat ILIKE $1)'
  }

  const sql = `
    SELECT
      u.*,
      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),
    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(''),
    organization: z.string().trim().max(100, '单位不能超过 100 个字').optional().default('')
  }).safeParse(req.body)
  if (!p.success) return reply.code(400).send({ message: p.error.issues[0].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, organization, points_balance)
       VALUES($1, $2, $3, 'USER', $4, $5, 10000) RETURNING *`,
      [id, p.data.phone, passwordHash, p.data.contactName, p.data.organization]
    )
    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()
  }
})

// 11. 修改用户角色（赋予或撤销管理员权限）
app.patch('/api/admin/users/:id/role', { preHandler: adminAuth }, async (req, reply) => {
  const { id } = req.params as any
  const currentAdmin = (req as any).user
  const p = z.object({
    role: z.enum(['USER', 'ADMIN'])
  }).safeParse(req.body)
  if (!p.success) return reply.code(400).send({ message: '角色类型不正确' })

  if (id === currentAdmin.id && p.data.role !== 'ADMIN') {
    return reply.code(400).send({ message: '不能撤销当前登录账号的管理员权限' })
  }

  const { rows } = await pool.query(
    'UPDATE users SET role = $1, updated_at = NOW() WHERE id = $2 RETURNING *',
    [p.data.role, id]
  )
  if (!rows[0]) return reply.code(404).send({ message: '用户不存在' })
  return { user: mapUser(rows[0]) }
})

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

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

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' })