import express from 'express'
import { DatabaseSync } from 'node:sqlite'
import { mkdirSync, readdirSync, readFileSync, existsSync, createWriteStream, renameSync, unlinkSync } from 'node:fs'
import { dirname, join, extname } from 'node:path'
import { fileURLToPath } from 'node:url'
import { randomBytes } from 'node:crypto'

const root = dirname(dirname(fileURLToPath(import.meta.url)))
const dataDir = join(root, 'data')
const migrationsDir = join(root, 'migrations')
mkdirSync(dataDir, { recursive: true })

const dbPath = process.env.DATABASE_PATH || join(dataDir, 'reporter-station.db')
const db = new DatabaseSync(dbPath)
db.exec(`PRAGMA journal_mode = WAL; PRAGMA foreign_keys = ON;`)

// 运行所有待执行迁移（幂等性由 schema_migrations 表保障）
const applied = new Set(db.prepare('SELECT name FROM schema_migrations').all().map(r => r.name))
for (const file of readdirSync(migrationsDir).filter(f => f.endsWith('.sql') && !f.startsWith('_')).sort()) {
  const name = file.replace('.sql', '')
  if (applied.has(name)) continue
  db.exec(readFileSync(join(migrationsDir, file), 'utf-8'))
  db.prepare('INSERT INTO schema_migrations (name) VALUES (?)').run(name)
}

// ── Express 基础 ────────────────────────────────────────────────────────────
const app = express()
app.use(express.json({ limit: '1mb' }))
app.use('/attachments', express.static(join(root, 'public', 'attachments')))

// ── 身份认证（支持 token 会话 + header 模拟回退） ─────────────────────────
const identities = {
  headquarters: { name: '林致远', station: null },
  station:      { name: '苏明远', station: '北京记者站' },
  reporter:     { name: '林晓',   station: '北京记者站' },
}

/** 记录系统操作日志 */
function logSystem(actorRole, actorName, module, action, targetType, targetId, detail) {
  try {
    db.prepare(`INSERT INTO system_logs (actor_role, actor_name, module, action, target_type, target_id, detail)
      VALUES (?,?,?,?,?,?,?)`).run(actorRole, actorName, module, action, targetType || null, String(targetId || ''), detail || null)
  } catch (e) { console.error('logSystem error:', e.message) }
}

app.use((req, res, next) => {
  const token = req.header('x-auth-token')
  if (token) {
    const session = db.prepare(`
      SELECT s.*, p.station FROM login_sessions s
      LEFT JOIN people p ON p.code = s.user_code
      WHERE s.token = ? AND s.revoked_at IS NULL AND s.expires_at > datetime('now','localtime')
    `).get(token)
    if (session) {
      req.user = { role: session.role, name: session.user_name, station: session.station || identities[session.role]?.station || null }
      req.authToken = token
      return next()
    }
    return res.status(401).json({ message: '会话已过期，请重新登录' })
  }
  // 回退：header 模拟（演示用）
  const role = req.header('x-user-role') || 'headquarters'
  req.user = identities[role] ? { role, ...identities[role] } : { role: 'headquarters', ...identities.headquarters }
  next()
})

// ── 通用 SQL 片段 ───────────────────────────────────────────────────────────
const recordSelect = `
  SELECT id,title,type,reporter,station,occurred_date AS date,platform,status,score,
         description,review_note AS reviewNote,attachments,
         datetime(created_at,'localtime') AS createdAt,
         datetime(updated_at,'localtime') AS updatedAt
  FROM work_records WHERE deleted_at IS NULL`

// ── 辅助函数 ────────────────────────────────────────────────────────────────
function canAccessRecord(user, record) {
  if (user.role === 'headquarters') return true
  if (user.role === 'station')     return record.station === user.station
  return record.reporter === user.name
}

// ═══════════════════════════════════════════════════════════════════════════
// 公共路由
// ═══════════════════════════════════════════════════════════════════════════

app.get('/api/health', (_req, res) => res.json({ status: 'ok', database: 'sqlite' }))

// ═══════════════════════════════════════════════════════════════════════════
// 认证 API
// ═══════════════════════════════════════════════════════════════════════════

app.post('/api/auth/login', (req, res) => {
  const { code, password } = req.body
  if (!code?.trim()) return res.status(400).json({ message: '请输入工号' })

  // 查找人员（支持用工号或姓名登录）
  const person = db.prepare(`
    SELECT id, code, name, station, title, status FROM people
    WHERE (code = ? OR name = ?) AND deleted_at IS NULL
  `).get(code.trim(), code.trim())

  if (!person) return res.status(404).json({ message: '用户不存在' })
  if (person.status === 'inactive') return res.status(403).json({ message: '账号已停用，请联系管理员' })

  // 根据职务推断角色
  let role = 'reporter'
  if (person.station === '总部' || person.title === '总部管理员') role = 'headquarters'
  else if (person.title?.includes('负责人') || person.title?.includes('站长')) role = 'station'

  // 生成会话 token
  const token = randomBytes(32).toString('hex')
  const expiresAt = new Date(Date.now() + 2 * 60 * 60 * 1000).toISOString().replace('T', ' ').slice(0, 19)
  db.prepare(`INSERT INTO login_sessions (token, user_code, user_name, role, station, expires_at)
    VALUES (?,?,?,?,?,?)`).run(token, person.code, person.name, role, person.station, expiresAt)

  logSystem(role, person.name, 'auth', 'login', 'user', person.id, `用户登录，角色: ${role}`)
  res.json({ token, role, name: person.name, station: person.station, code: person.code })
})

app.post('/api/auth/logout', (req, res) => {
  if (req.authToken) {
    db.prepare(`UPDATE login_sessions SET revoked_at = datetime('now','localtime') WHERE token = ?`).run(req.authToken)
    logSystem(req.user.role, req.user.name, 'auth', 'logout', 'user', null, '用户退出')
  }
  res.json({ message: '已退出登录' })
})

app.get('/api/auth/check', (req, res) => {
  if (!req.authToken) return res.json({ authenticated: false, role: req.user.role, name: req.user.name, demo: true })
  res.json({ authenticated: true, role: req.user.role, name: req.user.name, station: req.user.station })
})

// 系统操作日志查询
app.get('/api/system-logs', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可查看系统日志' })
  const { module, action, actor, page = '1', pageSize = '50' } = req.query
  let sql = `SELECT id, actor_role AS actorRole, actor_name AS actorName, module, action,
    target_type AS targetType, target_id AS targetId, detail,
    datetime(created_at,'localtime') AS createdAt
    FROM system_logs WHERE 1=1`
  const params = []
  if (module) { sql += ' AND module = ?'; params.push(module) }
  if (action) { sql += ' AND action = ?'; params.push(action) }
  if (actor)  { sql += ' AND actor_name LIKE ?'; params.push(`%${actor}%`) }
  sql += ' ORDER BY created_at DESC LIMIT ? OFFSET ?'
  const limit = Number(pageSize)
  const offset = (Number(page) - 1) * limit
  params.push(limit, offset)
  res.json(db.prepare(sql).all(...params))
})

// ═══════════════════════════════════════════════════════════════════════════
// 工作记录（已有）
// ═══════════════════════════════════════════════════════════════════════════

app.get('/api/records', (req, res) => {
  let sql = recordSelect
  const params = []
  if (req.user.role === 'station')  { sql += ' AND station = ?'; params.push(req.user.station) }
  if (req.user.role === 'reporter') { sql += ' AND reporter = ?'; params.push(req.user.name) }
  if (req.query.status)  { sql += ' AND status = ?';  params.push(req.query.status) }
  if (req.query.type)    { sql += ' AND type = ?';    params.push(req.query.type) }
  if (req.query.startDate) { sql += ' AND occurred_date >= ?'; params.push(req.query.startDate) }
  if (req.query.endDate)   { sql += ' AND occurred_date <= ?'; params.push(req.query.endDate) }
  if (req.query.keyword)   { sql += ' AND title LIKE ?'; params.push(`%${req.query.keyword}%`) }
  sql += ' ORDER BY updated_at DESC'
  res.json(db.prepare(sql).all(...params))
})

app.get('/api/records/:id/audit', (req, res) => {
  const record = db.prepare(`${recordSelect} AND id = ?`).get(req.params.id)
  if (!record || !canAccessRecord(req.user, record)) return res.status(404).json({ message: '记录不存在' })
  const logs = db.prepare(`
    SELECT actor_role AS actorRole, actor_name AS actorName, action,
           from_status AS fromStatus, to_status AS toStatus, score, note,
           created_at AS createdAt
    FROM audit_logs WHERE record_id = ? ORDER BY id`).all(req.params.id)
  res.json(logs)
})

app.post('/api/records', (req, res) => {
  const { title, type, date, platform, description = '', attachments, isDraft } = req.body
  if (!title?.trim() || !type || !date || !platform?.trim())
    return res.status(400).json({ message: '请完整填写必填项' })
  if (req.user.role === 'headquarters')
    return res.status(403).json({ message: '总部管理员不能代替记者填报' })
  const reporter = req.user.role === 'reporter' ? req.user.name : '林晓'
  const station  = req.user.station
  const id      = `WK-${date.replaceAll('-', '')}-${String(Date.now()).slice(-4)}`
  const status  = isDraft ? 'draft' : 'station_review'
  db.prepare(`
    INSERT INTO work_records (id,title,type,reporter,station,occurred_date,platform,status,description,attachments)
    VALUES (?,?,?,?,?,?,?,?,?,?)`).run(id, title.trim(), type, reporter, station, date, platform.trim(), status, description, attachments ? JSON.stringify(attachments) : null)
  db.prepare(`
    INSERT INTO audit_logs (record_id,actor_role,actor_name,action,to_status,note)
    VALUES (?,?,?,?,?,?)`).run(id, req.user.role, req.user.name, isDraft ? 'save_draft' : 'submit', status, isDraft ? '保存草稿' : '提交工作记录')
  logSystem(req.user.role, req.user.name, 'record', isDraft ? 'save_draft' : 'create', 'work_record', id, `标题: ${title}`)
  res.status(201).json(db.prepare(`${recordSelect} AND id = ?`).get(id))
})

// 保存草稿
app.patch('/api/records/:id/draft', (req, res) => {
  const record = db.prepare(`${recordSelect} AND id = ?`).get(req.params.id)
  if (!record) return res.status(404).json({ message: '记录不存在' })
  if (record.status !== 'draft' && record.status !== 'returned')
    return res.status(409).json({ message: '仅草稿或退回状态可编辑' })
  if (record.reporter !== req.user.name && req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅填报人可编辑' })
  const { title, type, date, platform, description, attachments } = req.body
  const updates = []
  const params = []
  if (title !== undefined) { updates.push('title = ?'); params.push(title.trim()) }
  if (type !== undefined) { updates.push('type = ?'); params.push(type) }
  if (date !== undefined) { updates.push('occurred_date = ?'); params.push(date) }
  if (platform !== undefined) { updates.push('platform = ?'); params.push(platform.trim()) }
  if (description !== undefined) { updates.push('description = ?'); params.push(description) }
  if (attachments !== undefined) { updates.push('attachments = ?'); params.push(attachments ? JSON.stringify(attachments) : null) }
  if (updates.length === 0) return res.status(400).json({ message: '无有效更新字段' })
  updates.push('updated_at = CURRENT_TIMESTAMP')
  params.push(req.params.id)
  db.prepare(`UPDATE work_records SET ${updates.join(', ')} WHERE id = ?`).run(...params)
  res.json(db.prepare(`${recordSelect} AND id = ?`).get(req.params.id))
})

// 提交草稿为审核
app.post('/api/records/:id/submit', (req, res) => {
  const record = db.prepare(`${recordSelect} AND id = ?`).get(req.params.id)
  if (!record) return res.status(404).json({ message: '记录不存在' })
  if (record.status !== 'draft' && record.status !== 'returned')
    return res.status(409).json({ message: '仅草稿或退回状态可提交' })
  db.prepare(`UPDATE work_records SET status = 'station_review', updated_at = CURRENT_TIMESTAMP WHERE id = ?`).run(req.params.id)
  db.prepare(`INSERT INTO audit_logs (record_id,actor_role,actor_name,action,from_status,to_status,note)
    VALUES (?,?,?,?,?,?,?)`).run(record.id, req.user.role, req.user.name, 'submit', record.status, 'station_review', '提交审核')
  res.json(db.prepare(`${recordSelect} AND id = ?`).get(record.id))
})

// 工作记录版本历史
app.get('/api/records/:id/versions', (req, res) => {
  const record = db.prepare(`${recordSelect} AND id = ?`).get(req.params.id)
  if (!record || !canAccessRecord(req.user, record)) return res.status(404).json({ message: '记录不存在' })
  const versions = db.prepare(`SELECT id, version_no AS versionNo, title, type, platform, description, attachments, edited_by AS editedBy,
    datetime(edited_at,'localtime') AS editedAt FROM record_versions WHERE record_id = ? ORDER BY version_no DESC`).all(req.params.id)
  res.json(versions)
})

// 附件上传
app.post('/api/records/:id/attachments', (req, res) => {
  const record = db.prepare(`${recordSelect} AND id = ?`).get(req.params.id)
  if (!record) return res.status(404).json({ message: '记录不存在' })
  if (!canAccessRecord(req.user, record)) return res.status(403).json({ message: '无权操作' })
  const { files } = req.body
  if (!files?.length) return res.status(400).json({ message: '请提供文件信息' })
  const existing = record.attachments ? JSON.parse(record.attachments) : []
  const updated = [...existing, ...files]
  db.prepare(`UPDATE work_records SET attachments = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`).run(JSON.stringify(updated), req.params.id)
  logSystem(req.user.role, req.user.name, 'record', 'upload_attachment', 'work_record', req.params.id, `上传 ${files.length} 个附件`)
  res.json(db.prepare(`${recordSelect} AND id = ?`).get(req.params.id))
})

// 删除附件
app.delete('/api/records/:id/attachments/:index', (req, res) => {
  const record = db.prepare(`${recordSelect} AND id = ?`).get(req.params.id)
  if (!record) return res.status(404).json({ message: '记录不存在' })
  if (!canAccessRecord(req.user, record)) return res.status(403).json({ message: '无权操作' })
  const attachments = record.attachments ? JSON.parse(record.attachments) : []
  const idx = Number(req.params.index)
  if (idx < 0 || idx >= attachments.length) return res.status(400).json({ message: '附件索引无效' })
  attachments.splice(idx, 1)
  db.prepare(`UPDATE work_records SET attachments = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?`).run(attachments.length ? JSON.stringify(attachments) : null, req.params.id)
  res.json(db.prepare(`${recordSelect} AND id = ?`).get(req.params.id))
})

app.patch('/api/records/:id/review', (req, res) => {
  const record = db.prepare(`${recordSelect} AND id = ?`).get(req.params.id)
  if (!record || !canAccessRecord(req.user, record))
    return res.status(404).json({ message: '记录不存在' })
  const { decision, score, note = '' } = req.body
  if (!['pass', 'return'].includes(decision))
    return res.status(400).json({ message: '无效的审核决定' })
  if (decision === 'return' && !note.trim())
    return res.status(400).json({ message: '退回时必须填写原因' })

  const expected = req.user.role === 'station'
    ? 'station_review' : req.user.role === 'headquarters' ? 'headquarters_review' : null
  if (!expected || record.status !== expected)
    return res.status(409).json({ message: '无权处理当前状态' })

  const nextStatus = decision === 'return'
    ? 'returned'
    : req.user.role === 'station' ? 'headquarters_review' : 'archived'
  const finalScore = Number.isFinite(Number(score)) ? Number(score) : record.score

  db.exec('BEGIN')
  try {
    db.prepare(`
      UPDATE work_records
      SET status = ?, score = ?, review_note = ?, updated_at = CURRENT_TIMESTAMP
      WHERE id = ?`).run(nextStatus, finalScore, note.trim() || '材料完整，审核通过。', record.id)
    db.prepare(`
      INSERT INTO audit_logs (record_id,actor_role,actor_name,action,from_status,to_status,score,note)
      VALUES (?,?,?,?,?,?,?,?)`)
      .run(record.id, req.user.role, req.user.name, decision, record.status, nextStatus, finalScore, note)
    db.exec('COMMIT')
  } catch (e) {
    db.exec('ROLLBACK')
    res.status(500).json({ message: '服务端处理失败' })
  }
  res.json(db.prepare(`${recordSelect} AND id = ?`).get(record.id))
})

// ═══════════════════════════════════════════════════════════════════════════
// TASK-BE-001：人员 CRUD API
// ═══════════════════════════════════════════════════════════════════════════

app.get('/api/people', (req, res) => {
  let sql = `SELECT id,code,name,station,title,phone,
             joined_at AS joinedAt,status,
             datetime(created_at,'localtime') AS createdAt,
             datetime(updated_at,'localtime') AS updatedAt
             FROM people WHERE deleted_at IS NULL`
  const params = []
  if (req.user.role === 'station') { sql += ' AND station = ?'; params.push(req.user.station) }
  if (req.query.station)  { sql += ' AND station = ?';  params.push(req.query.station) }
  if (req.query.status)   { sql += ' AND status = ?';   params.push(req.query.status) }
  if (req.query.name)     { sql += ' AND name LIKE ?';  params.push(`%${req.query.name}%`) }
  sql += ' ORDER BY station, name'
  res.json(db.prepare(sql).all(...params))
})

app.get('/api/people/:id', (req, res) => {
  const person = db.prepare(`SELECT id,code,name,station,title,phone,
    joined_at AS joinedAt,status,
    datetime(created_at,'localtime') AS createdAt,
    datetime(updated_at,'localtime') AS updatedAt
    FROM people WHERE id = ? AND deleted_at IS NULL`).get(req.params.id)
  if (!person) return res.status(404).json({ message: '人员不存在' })
  res.json(person)
})

app.post('/api/people', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可新增人员' })
  const { name, station, title, phone, joinedAt } = req.body
  if (!name?.trim() || !station?.trim())
    return res.status(400).json({ message: '姓名和所属站点为必填项' })

  const code = `PERSON_${Date.now()}`
  try {
    db.prepare(`
      INSERT INTO people (code,name,station,title,phone,joined_at)
      VALUES (?,?,?,?,?,?)`).run(code, name.trim(), station.trim(), title?.trim() || null,
      phone?.trim() || null, joinedAt || null)
    const person = db.prepare(`SELECT id,code,name,station,title,phone,
      joined_at AS joinedAt,status,datetime(created_at,'localtime') AS createdAt,
      datetime(updated_at,'localtime') AS updatedAt FROM people WHERE code = ?`).get(code)
    res.status(201).json(person)
  } catch (e) {
    if (e.message.includes('UNIQUE')) return res.status(409).json({ message: '人员编码已存在' })
    throw e
  }
})

app.patch('/api/people/:id', (req, res) => {
  const person = db.prepare('SELECT * FROM people WHERE id = ?').get(req.params.id)
  if (!person) return res.status(404).json({ message: '人员不存在' })
  if (req.user.role === 'station' && person.station !== req.user.station)
    return res.status(403).json({ message: '无权编辑其他站点人员' })

  const { title, phone, status } = req.body
  const updates = []
  const params  = []
  if (title  !== undefined) { updates.push('title = ?');       params.push(title.trim()) }
  if (phone  !== undefined) { updates.push('phone = ?');      params.push(phone?.trim() || null) }
  if (status !== undefined) {
    if (!['active', 'inactive'].includes(status))
      return res.status(400).json({ message: 'status 只能是 active 或 inactive' })
    updates.push('status = ?'); params.push(status)
  }
  if (updates.length === 0) return res.status(400).json({ message: '无有效更新字段' })

  updates.push('updated_at = CURRENT_TIMESTAMP')
  params.push(req.params.id)
  db.prepare(`UPDATE people SET ${updates.join(', ')} WHERE id = ?`).run(...params)
  const updated = db.prepare(`SELECT id,code,name,station,title,phone,
    joined_at AS joinedAt,status,datetime(created_at,'localtime') AS createdAt,
    datetime(updated_at,'localtime') AS updatedAt FROM people WHERE id = ?`).get(req.params.id)
  res.json(updated)
})

app.delete('/api/people/:id', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可删除人员' })
  const person = db.prepare('SELECT * FROM people WHERE id = ? AND deleted_at IS NULL').get(req.params.id)
  if (!person) return res.status(404).json({ message: '人员不存在' })
  db.prepare('UPDATE people SET deleted_at = datetime(\'now\',\'localtime\') WHERE id = ?').run(req.params.id)
  logSystem(req.user.role, req.user.name, 'people', 'delete', 'person', req.params.id, `删除人员: ${person.name}`)
  res.json({ message: '删除成功' })
})

// ═══════════════════════════════════════════════════════════════════════════
// TASK-BE-002：记者站 CRUD API
// ═══════════════════════════════════════════════════════════════════════════

app.get('/api/stations', (req, res) => {
  let sql = `SELECT id,code,name,region,address,leader,phone,status,
             established_at AS establishedAt,
             datetime(created_at,'localtime') AS createdAt,
             datetime(updated_at,'localtime') AS updatedAt
             FROM stations WHERE deleted_at IS NULL`
  const params = []
  if (req.query.status) { sql += ' AND status = ?'; params.push(req.query.status) }
  if (req.query.region) { sql += ' AND region = ?'; params.push(req.query.region) }
  sql += ' ORDER BY region, name'
  res.json(db.prepare(sql).all(...params))
})

app.get('/api/stations/:id', (req, res) => {
  const station = db.prepare(`SELECT id,code,name,region,address,leader,phone,status,
    established_at AS establishedAt,
    datetime(created_at,'localtime') AS createdAt,
    datetime(updated_at,'localtime') AS updatedAt
    FROM stations WHERE id = ?`).get(req.params.id)
  if (!station) return res.status(404).json({ message: '记者站不存在' })
  res.json(station)
})

app.post('/api/stations', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可新增记者站' })
  const { name, code, region, address, leader, phone, establishedAt } = req.body
  if (!name?.trim() || !code?.trim())
    return res.status(400).json({ message: '站点名称和编码为必填项' })

  try {
    db.prepare(`
      INSERT INTO stations (code,name,region,address,leader,phone,established_at)
      VALUES (?,?,?,?,?,?,?)`)
      .run(code.trim(), name.trim(), region?.trim() || null, address?.trim() || null,
           leader?.trim() || null, phone?.trim() || null, establishedAt || null)
    const station = db.prepare(`SELECT id,code,name,region,address,leader,phone,status,
      established_at AS establishedAt,datetime(created_at,'localtime') AS createdAt,
      datetime(updated_at,'localtime') AS updatedAt FROM stations WHERE code = ?`).get(code.trim())
    res.status(201).json(station)
  } catch (e) {
    if (e.message.includes('UNIQUE')) return res.status(409).json({ message: '站点编码已存在' })
    throw e
  }
})

app.patch('/api/stations/:id', (req, res) => {
  const station = db.prepare('SELECT * FROM stations WHERE id = ?').get(req.params.id)
  if (!station) return res.status(404).json({ message: '记者站不存在' })
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可编辑记者站' })

  const { name, region, address, leader, phone, status, establishedAt } = req.body
  const updates = []
  const params  = []
  if (name          !== undefined) { updates.push('name = ?');           params.push(name.trim()) }
  if (region        !== undefined) { updates.push('region = ?');         params.push(region?.trim() || null) }
  if (address       !== undefined) { updates.push('address = ?');        params.push(address?.trim() || null) }
  if (leader        !== undefined) { updates.push('leader = ?');         params.push(leader?.trim() || null) }
  if (phone         !== undefined) { updates.push('phone = ?');          params.push(phone?.trim() || null) }
  if (status        !== undefined) {
    if (!['active', 'inactive'].includes(status))
      return res.status(400).json({ message: 'status 只能是 active 或 inactive' })
    updates.push('status = ?'); params.push(status)
  }
  if (establishedAt !== undefined) { updates.push('established_at = ?'); params.push(establishedAt || null) }
  if (updates.length === 0) return res.status(400).json({ message: '无有效更新字段' })

  updates.push('updated_at = CURRENT_TIMESTAMP')
  params.push(req.params.id)
  db.prepare(`UPDATE stations SET ${updates.join(', ')} WHERE id = ?`).run(...params)
  const updated = db.prepare(`SELECT id,code,name,region,address,leader,phone,status,
    established_at AS establishedAt,datetime(created_at,'localtime') AS createdAt,
    datetime(updated_at,'localtime') AS updatedAt FROM stations WHERE id = ?`).get(req.params.id)
  res.json(updated)
})

app.delete('/api/stations/:id', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可删除记者站' })
  const station = db.prepare('SELECT * FROM stations WHERE id = ? AND deleted_at IS NULL').get(req.params.id)
  if (!station) return res.status(404).json({ message: '记者站不存在' })
  const used = db.prepare('SELECT COUNT(*) AS count FROM people WHERE station = ? AND deleted_at IS NULL').get(station.name).count
  if (used > 0) return res.status(409).json({ message: `该站点下有 ${used} 名人员，请先移除后再删除` })
  db.prepare('UPDATE stations SET deleted_at = datetime(\'now\',\'localtime\') WHERE id = ?').run(req.params.id)
  logSystem(req.user.role, req.user.name, 'stations', 'delete', 'station', req.params.id, `删除站点: ${station.name}`)
  res.json({ message: '删除成功' })
})

// ═══════════════════════════════════════════════════════════════════════════
// TASK-BE-003：通知公告 API
// ═══════════════════════════════════════════════════════════════════════════

app.get('/api/notices', (req, res) => {
  let sql = `SELECT id,code,title,content,priority,scope,stations,roles,attachment,
             published_by AS publishedBy,
             datetime(published_at,'localtime') AS publishedAt,
             datetime(created_at,'localtime') AS createdAt
             FROM notices WHERE 1=1`
  const params = []
  if (req.query.priority) { sql += ' AND priority = ?'; params.push(req.query.priority) }
  if (req.query.scope)    { sql += ' AND scope = ?';    params.push(req.query.scope) }
  sql += ' ORDER BY published_at DESC'
  res.json(db.prepare(sql).all(...params))
})

app.get('/api/notices/:id', (req, res) => {
  const notice = db.prepare(`SELECT id,code,title,content,priority,scope,stations,roles,attachment,
    published_by AS publishedBy,
    datetime(published_at,'localtime') AS publishedAt,
    datetime(created_at,'localtime') AS createdAt
    FROM notices WHERE id = ?`).get(req.params.id)
  if (!notice) return res.status(404).json({ message: '通知不存在' })
  res.json(notice)
})

app.get('/api/notices/:id/receipts', (req, res) => {
  const notice = db.prepare('SELECT * FROM notices WHERE id = ?').get(req.params.id)
  if (!notice) return res.status(404).json({ message: '通知不存在' })
  const receipts = db.prepare(`
    SELECT id,notice_id AS noticeId,receiver_name AS receiverName,receiver_role AS receiverRole,
           read,confirmed,datetime(read_at,'localtime') AS readAt,
           datetime(confirmed_at,'localtime') AS confirmedAt,
           datetime(created_at,'localtime') AS createdAt
    FROM notice_receipts WHERE notice_id = ?`).all(req.params.id)
  res.json(receipts)
})

app.post('/api/notices', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可发布通知' })
  const { title, content, priority, scope, stations, roles } = req.body
  if (!title?.trim()) return res.status(400).json({ message: '通知标题为必填项' })
  if (!['normal', 'high', 'urgent'].includes(priority || 'normal'))
    return res.status(400).json({ message: 'priority 只能是 normal / high / urgent' })
  if (!['all', 'station', 'role'].includes(scope || 'all'))
    return res.status(400).json({ message: 'scope 只能是 all / station / role' })

  const code = `NOTICE_${Date.now()}`
  db.prepare(`
    INSERT INTO notices (code,title,content,priority,scope,stations,roles,published_by)
    VALUES (?,?,?,?,?,?,?,?)`)
    .run(code, title.trim(), content?.trim() || null, priority || 'normal',
         scope || 'all', stations ? JSON.stringify(stations) : null,
         roles  ? JSON.stringify(roles)  : null, req.user.name)

  // 写入回执记录
  const noticeId = db.prepare('SELECT last_insert_rowid() AS id').get().id
  const receivers = buildReceipts(req.user, { scope: scope || 'all', stations, roles })
  if (receivers.length > 0) {
    const insertReceipt = db.prepare(`
      INSERT INTO notice_receipts (notice_id,receiver_name,receiver_role)
      VALUES (?,?,?)`)
    for (const r of receivers) insertReceipt.run(noticeId, r.name, r.role)
  }

  const notice = db.prepare(`SELECT id,code,title,content,priority,scope,stations,roles,attachment,
    published_by AS publishedBy,
    datetime(published_at,'localtime') AS publishedAt,
    datetime(created_at,'localtime') AS createdAt
    FROM notices WHERE id = ?`).get(noticeId)
  res.status(201).json(notice)
})

app.patch('/api/notices/:id', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可编辑通知' })
  const notice = db.prepare('SELECT * FROM notices WHERE id = ?').get(req.params.id)
  if (!notice) return res.status(404).json({ message: '通知不存在' })

  const { title, content, priority } = req.body
  const updates = []
  const params  = []
  if (title    !== undefined) { updates.push('title = ?');    params.push(title.trim()) }
  if (content  !== undefined) { updates.push('content = ?');  params.push(content?.trim() || null) }
  if (priority !== undefined) {
    if (!['normal', 'high', 'urgent'].includes(priority))
      return res.status(400).json({ message: 'priority 无效' })
    updates.push('priority = ?'); params.push(priority)
  }
  if (updates.length === 0) return res.status(400).json({ message: '无有效更新字段' })

  updates.push('updated_at = CURRENT_TIMESTAMP')
  params.push(req.params.id)
  db.prepare(`UPDATE notices SET ${updates.join(', ')} WHERE id = ?`).run(...params)
  const updated = db.prepare(`SELECT id,code,title,content,priority,scope,stations,roles,attachment,
    published_by AS publishedBy,
    datetime(published_at,'localtime') AS publishedAt,
    datetime(created_at,'localtime') AS createdAt
    FROM notices WHERE id = ?`).get(req.params.id)
  res.json(updated)
})

app.post('/api/notices/:id/read', (req, res) => {
  const notice = db.prepare('SELECT * FROM notices WHERE id = ?').get(req.params.id)
  if (!notice) return res.status(404).json({ message: '通知不存在' })
  const receipt = db.prepare(`
    SELECT * FROM notice_receipts
    WHERE notice_id = ? AND receiver_name = ? AND receiver_role = ?`)
    .get(req.params.id, req.user.name, req.user.role)
  if (!receipt) return res.status(404).json({ message: '无对应回执记录' })
  db.prepare(`UPDATE notice_receipts SET read = 1, read_at = CURRENT_TIMESTAMP
    WHERE id = ?`).run(receipt.id)
  res.json({ message: '已标记为已读' })
})

app.post('/api/notices/:id/confirm', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可确认回执' })
  const notice = db.prepare('SELECT * FROM notices WHERE id = ?').get(req.params.id)
  if (!notice) return res.status(404).json({ message: '通知不存在' })
  const { receiverName, receiverRole } = req.body
  if (!receiverName || !receiverRole)
    return res.status(400).json({ message: 'receiverName 和 receiverRole 为必填项' })
  const receipt = db.prepare(`
    SELECT * FROM notice_receipts
    WHERE notice_id = ? AND receiver_name = ? AND receiver_role = ?`)
    .get(req.params.id, receiverName, receiverRole)
  if (!receipt) return res.status(404).json({ message: '回执记录不存在' })
  db.prepare(`UPDATE notice_receipts SET confirmed = 1, confirmed_at = CURRENT_TIMESTAMP
    WHERE id = ?`).run(receipt.id)
  res.json({ message: '回执已确认' })
})

/** 根据通知范围构建应接收回执的人员列表 */
function buildReceipts(user, { scope, stations, roles }) {
  const receivers = []
  if (scope === 'all') {
    const people = db.prepare('SELECT name,station FROM people WHERE status = ?').all('active')
    for (const p of people) receivers.push({ name: p.name, role: 'reporter' })
  } else if (scope === 'station') {
    const stationList = stations || [user.station]
    for (const s of stationList) {
      const people = db.prepare('SELECT name FROM people WHERE station = ? AND status = ?').all(s, 'active')
      for (const p of people) receivers.push({ name: p.name, role: 'reporter' })
    }
  } else if (scope === 'role') {
    const roleList = roles || ['reporter', 'station']
    for (const r of roleList) {
      const people = db.prepare('SELECT name FROM people WHERE status = ?').all('active')
      for (const p of people) receivers.push({ name: p.name, role: r })
    }
  }
  return receivers
}

// ═══════════════════════════════════════════════════════════════════════════
// TASK-BE-004：数据统计 API
// ═══════════════════════════════════════════════════════════════════════════

app.get('/api/stats/overview', (req, res) => {
  const isHq = req.user.role === 'headquarters'
  const stationFilter = isHq ? '' : ' AND station = ?'
  const stationParams = isHq ? [] : [req.user.station]

  const total     = db.prepare(`SELECT COUNT(*) AS count FROM work_records WHERE deleted_at IS NULL${stationFilter}`).get(...stationParams).count
  const draft     = db.prepare(`SELECT COUNT(*) AS count FROM work_records WHERE deleted_at IS NULL${stationFilter} AND status = 'draft'`).get(...stationParams).count
  const reviewing = db.prepare(`SELECT COUNT(*) AS count FROM work_records WHERE deleted_at IS NULL${stationFilter} AND status IN ('station_review','headquarters_review')`).get(...stationParams).count
  const archived  = db.prepare(`SELECT COUNT(*) AS count FROM work_records WHERE deleted_at IS NULL${stationFilter} AND status = 'archived'`).get(...stationParams).count
  const returned  = db.prepare(`SELECT COUNT(*) AS count FROM work_records WHERE deleted_at IS NULL${stationFilter} AND status = 'returned'`).get(...stationParams).count
  const avgScore  = db.prepare(`SELECT ROUND(AVG(score),1) AS avg FROM work_records WHERE deleted_at IS NULL${stationFilter} AND score IS NOT NULL`).get(...stationParams).avg

  // 按月统计（最近 6 个月）
  const monthlyRows = db.prepare(`SELECT
    strftime('%Y-%m', occurred_date) AS month,
    COUNT(*) AS count,
    ROUND(AVG(score),1) AS avgScore
    FROM work_records WHERE deleted_at IS NULL${stationFilter} AND occurred_date >= date('now','-6 months')
    GROUP BY strftime('%Y-%m', occurred_date)
    ORDER BY month DESC`).all(...stationParams)

  res.json({ total, draft, reviewing, archived, returned, avgScore, monthly: monthlyRows })
})

app.get('/api/stats/records', (req, res) => {
  const groupBy = req.query.groupBy || 'station'
  const isHq    = req.user.role === 'headquarters'
  const stationCon = isHq ? '' : ' AND station = ?'
  const stationParams = isHq ? [] : [req.user.station]
  let rows

  if (groupBy === 'station') {
    rows = db.prepare(`SELECT
      station AS name,
      COUNT(*) AS total,
      SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) AS archived,
      SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returned,
      ROUND(AVG(CASE WHEN score IS NOT NULL THEN score END),1) AS avgScore
      FROM work_records WHERE deleted_at IS NULL${stationCon}
      GROUP BY station ORDER BY total DESC`).all(...stationParams)
  } else if (groupBy === 'reporter') {
    const whereCon = isHq ? '' : ' AND reporter = ?'
    const whereParams = isHq ? [] : [req.user.name]
    rows = db.prepare(`SELECT
      reporter AS name,
      station,
      COUNT(*) AS total,
      SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) AS archived,
      SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returned,
      ROUND(AVG(CASE WHEN score IS NOT NULL THEN score END),1) AS avgScore
      FROM work_records WHERE deleted_at IS NULL${whereCon} GROUP BY reporter, station ORDER BY total DESC`).all(...whereParams)
  } else if (groupBy === 'type') {
    rows = db.prepare(`SELECT
      type AS name,
      COUNT(*) AS total,
      SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) AS archived,
      ROUND(AVG(CASE WHEN score IS NOT NULL THEN score END),1) AS avgScore
      FROM work_records WHERE deleted_at IS NULL${stationCon}
      GROUP BY type ORDER BY total DESC`).all(...stationParams)
  } else {
    return res.status(400).json({ message: 'groupBy 必须是 station / reporter / type' })
  }

  res.json(rows)
})

app.get('/api/stats/scores', (req, res) => {
  const isHq = req.user.role === 'headquarters'
  const whereCon = isHq ? '' : ' AND station = ?'
  const whereParams = isHq ? [] : [req.user.station]
  const rows = db.prepare(`SELECT
    reporter AS name,
    station,
    COUNT(*) AS submitted,
    SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) AS archived,
    SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returned,
    ROUND(SUM(score),1) AS totalScore,
    ROUND(AVG(score),1) AS avgScore,
    MAX(score) AS maxScore,
    MIN(score) AS minScore
    FROM work_records WHERE deleted_at IS NULL${whereCon} AND score IS NOT NULL
    GROUP BY reporter ORDER BY totalScore DESC`).all(...whereParams)
  res.json(rows)
})

// ═══════════════════════════════════════════════════════════════════════════
// TASK-RULE-004：考核规则 CRUD API
// ═══════════════════════════════════════════════════════════════════════════

app.get('/api/rules', (req, res) => {
  const { status, period_type } = req.query
  let sql = `SELECT id,code,name,description,period_type AS periodType,
             period_start AS periodStart,period_end AS periodEnd,
             status,version,parent_id AS parentId,
             created_by AS createdBy,
             datetime(created_at,'localtime') AS createdAt,
             datetime(updated_at,'localtime') AS updatedAt
             FROM rules WHERE 1=1`
  const params = []
  if (status)      { sql += ' AND status = ?';       params.push(status) }
  if (period_type)  { sql += ' AND period_type = ?';  params.push(period_type) }
  sql += ' ORDER BY created_at DESC'
  res.json(db.prepare(sql).all(...params))
})

app.get('/api/rules/:id', (req, res) => {
  const rule = db.prepare(`SELECT id,code,name,description,period_type AS periodType,
    period_start AS periodStart,period_end AS periodEnd,
    status,version,parent_id AS parentId,
    created_by AS createdBy,
    datetime(created_at,'localtime') AS createdAt,
    datetime(updated_at,'localtime') AS updatedAt
    FROM rules WHERE id = ?`).get(req.params.id)
  if (!rule) return res.status(404).json({ message: '规则不存在' })
  const items = db.prepare(`SELECT id,rule_id AS ruleId,category,name,metric_key AS metricKey,
    weight,min_score AS minScore,max_score AS maxScore,
    formula_type AS formulaType,formula_params AS formulaParams,
    display_order AS displayOrder,enabled,
    datetime(created_at,'localtime') AS createdAt,
    datetime(updated_at,'localtime') AS updatedAt
    FROM rule_items WHERE rule_id = ? ORDER BY display_order`).all(req.params.id)
  res.json({ ...rule, items })
})

app.post('/api/rules', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可创建考核规则' })
  const { name, description, period_type, period_start, period_end, items } = req.body
  if (!name?.trim()) return res.status(400).json({ message: '规则名称为必填项' })
  if (!period_type)   return res.status(400).json({ message: '考核周期类型为必填项' })

  const code = `RULE_${Date.now()}`
  db.exec('BEGIN')
  try {
    db.prepare(`INSERT INTO rules (code,name,description,period_type,period_start,period_end,status,created_by)
      VALUES (?,?,?,?,?,?,?,?)`)
      .run(code, name.trim(), description?.trim() || null,
           period_type, period_start || null, period_end || null,
           'draft', req.user.name)
    const { id } = db.prepare('SELECT last_insert_rowid() AS id').get()
    if (items?.length) {
      for (const item of items) {
        db.prepare(`INSERT INTO rule_items (rule_id,category,name,metric_key,weight,min_score,max_score,formula_type,formula_params,display_order)
          VALUES (?,?,?,?,?,?,?,?,?,?)`)
          .run(id, item.category, item.name, item.metric_key, item.weight,
               item.min_score ?? 0, item.max_score ?? 100,
               item.formula_type, JSON.stringify(item.formula_params ?? {}),
               item.display_order ?? 0)
      }
    }
    db.exec('COMMIT')
    const rule = db.prepare(`SELECT id,code,name,description,period_type AS periodType,
      period_start AS periodStart,period_end AS periodEnd,
      status,version,parent_id AS parentId,
      created_by AS createdBy,
      datetime(created_at,'localtime') AS createdAt,
      datetime(updated_at,'localtime') AS updatedAt
      FROM rules WHERE id = ?`).get(id)
    const ruleItems = db.prepare(`SELECT id,rule_id AS ruleId,category,name,metric_key AS metricKey,
      weight,min_score AS minScore,max_score AS maxScore,
      formula_type AS formulaType,formula_params AS formulaParams,
      display_order AS displayOrder,enabled,
      datetime(created_at,'localtime') AS createdAt,
      datetime(updated_at,'localtime') AS updatedAt
      FROM rule_items WHERE rule_id = ? ORDER BY display_order`).all(id)
    res.status(201).json({ ...rule, items: ruleItems })
  } catch (e) {
    db.exec('ROLLBACK')
    res.status(500).json({ message: '服务端处理失败' })
  }
})

app.patch('/api/rules/:id', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可编辑考核规则' })
  const rule = db.prepare('SELECT * FROM rules WHERE id = ?').get(req.params.id)
  if (!rule) return res.status(404).json({ message: '规则不存在' })
  if (rule.status === 'active')
    return res.status(409).json({ message: '已激活的规则不可直接编辑，请新建版本' })

  const { name, description, period_start, period_end, items } = req.body
  const updates = []
  const params  = []
  if (name         !== undefined) { updates.push('name = ?');         params.push(name.trim()) }
  if (description  !== undefined) { updates.push('description = ?');  params.push(description?.trim() || null) }
  if (period_start !== undefined) { updates.push('period_start = ?'); params.push(period_start || null) }
  if (period_end   !== undefined) { updates.push('period_end = ?');   params.push(period_end || null) }

  db.exec('BEGIN')
  try {
    if (updates.length > 0) {
      updates.push('updated_at = CURRENT_TIMESTAMP')
      params.push(req.params.id)
      db.prepare(`UPDATE rules SET ${updates.join(', ')} WHERE id = ?`).run(...params)
    }
    if (items !== undefined) {
      db.prepare('DELETE FROM rule_items WHERE rule_id = ?').run(req.params.id)
      for (const item of items) {
        db.prepare(`INSERT INTO rule_items (rule_id,category,name,metric_key,weight,min_score,max_score,formula_type,formula_params,display_order)
          VALUES (?,?,?,?,?,?,?,?,?,?)`)
          .run(req.params.id, item.category, item.name, item.metric_key, item.weight,
               item.min_score ?? 0, item.max_score ?? 100,
               item.formula_type, JSON.stringify(item.formula_params ?? {}),
               item.display_order ?? 0)
      }
    }
    db.exec('COMMIT')
    const updated = db.prepare(`SELECT id,code,name,description,period_type AS periodType,
      period_start AS periodStart,period_end AS periodEnd,
      status,version,parent_id AS parentId,
      created_by AS createdBy,
      datetime(created_at,'localtime') AS createdAt,
      datetime(updated_at,'localtime') AS updatedAt
      FROM rules WHERE id = ?`).get(req.params.id)
    const ruleItems = db.prepare(`SELECT id,rule_id AS ruleId,category,name,metric_key AS metricKey,
      weight,min_score AS minScore,max_score AS maxScore,
      formula_type AS formulaType,formula_params AS formulaParams,
      display_order AS displayOrder,enabled,
      datetime(created_at,'localtime') AS createdAt,
      datetime(updated_at,'localtime') AS updatedAt
      FROM rule_items WHERE rule_id = ? ORDER BY display_order`).all(req.params.id)
    res.json({ ...updated, items: ruleItems })
  } catch (e) {
    db.exec('ROLLBACK')
    res.status(500).json({ message: '服务端处理失败' })
  }
})

app.post('/api/rules/:id/activate', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可激活考核规则' })
  const rule = db.prepare('SELECT * FROM rules WHERE id = ?').get(req.params.id)
  if (!rule) return res.status(404).json({ message: '规则不存在' })
  if (rule.status === 'active')
    return res.status(409).json({ message: '该规则已激活' })

  db.exec('BEGIN')
  try {
    // 同一周期类型的已激活规则全部归档
    db.prepare(`UPDATE rules SET status = 'archived', updated_at = CURRENT_TIMESTAMP
      WHERE period_type = ? AND status = 'active'`).run(rule.period_type)
    // 激活当前规则
    db.prepare(`UPDATE rules SET status = 'active', updated_at = CURRENT_TIMESTAMP WHERE id = ?`)
      .run(req.params.id)
    db.exec('COMMIT')
    const updated = db.prepare(`SELECT id,code,name,description,period_type AS periodType,
      period_start AS periodStart,period_end AS periodEnd,
      status,version,parent_id AS parentId,
      created_by AS createdBy,
      datetime(created_at,'localtime') AS createdAt,
      datetime(updated_at,'localtime') AS updatedAt
      FROM rules WHERE id = ?`).get(req.params.id)
    res.json(updated)
  } catch (e) {
    db.exec('ROLLBACK')
    res.status(500).json({ message: '服务端处理失败' })
  }
})

// ═══════════════════════════════════════════════════════════════════════════
// TASK-RULE-005：评分 API + 计算引擎
// ═══════════════════════════════════════════════════════════════════════════

// 指标聚合查询：从 work_records 提取各项指标数据
function buildMetricQuery(ruleId, reporter, station, periodStart, periodEnd) {
  const sql = `SELECT
    COUNT(*) AS count_total,
    SUM(CASE WHEN type = 'video' THEN 1 ELSE 0 END) AS count_video,
    ROUND(AVG(CASE WHEN score IS NOT NULL THEN score END), 1) AS avg_score,
    SUM(CASE WHEN type = 'article' THEN 1 ELSE 0 END) * 1.0 /
      NULLIF(COUNT(*), 0) AS original_ratio,
    SUM(CASE WHEN occurred_date <= datetime(occurred_date, '+7 days') AND status = 'archived' THEN 1 ELSE 0 END) * 1.0 /
      NULLIF(COUNT(*), 0) AS on_time_rate,
    SUM(CASE WHEN status = 'archived' THEN 1 ELSE 0 END) * 1.0 /
      NULLIF(COUNT(*), 0) AS pass_rate
    FROM work_records
    WHERE reporter = ? AND station = ?
      AND occurred_date >= ? AND occurred_date <= ?`
  return db.prepare(sql).get(reporter, station, periodStart, periodEnd)
}

// 计算单个指标项得分
function computeItemScore(item, metricValue) {
  if (metricValue === null || metricValue === undefined) return 0
  const { formula_type, formula_params: rawParams } = item
  const params = typeof rawParams === 'string' ? JSON.parse(rawParams) : rawParams
  let score = 0
  if (formula_type === 'count') {
    const { threshold = 10, base_score = 60, bonus_per_item = 4, max_bonus = 20 } = params
    const over = Math.max(0, metricValue - threshold)
    score = Math.min(base_score + over * bonus_per_item, base_score + max_bonus)
  } else if (formula_type === 'avg_score') {
    score = Math.min(Math.max(metricValue, 0), 100)
  } else if (formula_type === 'rate') {
    const { target = 0.85, base_score = 60, bonus_per_pct = 40 } = params
    const ratio = Math.min(metricValue / target, 1.5)
    score = Math.min(base_score + (ratio - 1) * bonus_per_pct, 100)
  } else {
    score = Math.min(Math.max(metricValue, 0), 100)
  }
  return Math.round(score * 100) / 100
}

app.get('/api/scores', (req, res) => {
  const { reporter, station, period, rule_id } = req.query
  let sql = `SELECT s.id,s.code,s.rule_id AS ruleId,s.reporter,s.station,
             s.period,s.period_type AS periodType,
             s.total_score AS totalScore,
             s.quality_score AS qualityScore,
             s.quantity_score AS quantityScore,
             s.efficiency_score AS efficiencyScore,
             s.compliance_score AS complianceScore,
             s.item_details AS itemDetails,
             datetime(s.computed_at,'localtime') AS computedAt,
             datetime(s.created_at,'localtime') AS createdAt,
             datetime(s.updated_at,'localtime') AS updatedAt,
             r.name AS ruleName
             FROM scores s
             LEFT JOIN rules r ON s.rule_id = r.id
             WHERE 1=1`
  const params = []
  if (!req.user.role.includes('headquarters')) {
    sql += ' AND (s.reporter = ? OR s.station = ?)'
    params.push(req.user.name, req.user.station)
  }
  if (reporter)  { sql += ' AND s.reporter = ?';  params.push(reporter) }
  if (station)    { sql += ' AND s.station = ?';    params.push(station) }
  if (period)    { sql += ' AND s.period = ?';    params.push(period) }
  if (rule_id)   { sql += ' AND s.rule_id = ?';   params.push(rule_id) }
  sql += ' ORDER BY s.computed_at DESC'
  const rows = db.prepare(sql).all(...params)
  // 反序列化 item_details
  for (const r of rows) {
    if (r.itemDetails) { r.items = JSON.parse(r.itemDetails); delete r.itemDetails }
  }
  res.json(rows)
})

app.get('/api/scores/:id', (req, res) => {
  const score = db.prepare(`SELECT s.id,s.code,s.rule_id AS ruleId,s.reporter,s.station,
    s.period,s.period_type AS periodType,
    s.total_score AS totalScore,
    s.quality_score AS qualityScore,
    s.quantity_score AS quantityScore,
    s.efficiency_score AS efficiencyScore,
    s.compliance_score AS complianceScore,
    s.item_details AS itemDetails,
    datetime(s.computed_at,'localtime') AS computedAt,
    datetime(s.created_at,'localtime') AS createdAt,
    datetime(s.updated_at,'localtime') AS updatedAt,
    r.name AS ruleName
    FROM scores s LEFT JOIN rules r ON s.rule_id = r.id
    WHERE s.id = ?`).get(req.params.id)
  if (!score) return res.status(404).json({ message: '评分记录不存在' })
  if (req.user.role !== 'headquarters' &&
      score.reporter !== req.user.name && score.station !== req.user.station)
    return res.status(403).json({ message: '无权查看该评分记录' })
  if (score.itemDetails) { score.items = JSON.parse(score.itemDetails); delete score.itemDetails }
  res.json(score)
})

app.post('/api/scores/compute', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可触发评分计算' })
  const { rule_id, reporters, period, period_type } = req.body
  if (!rule_id)    return res.status(400).json({ message: 'rule_id 为必填项' })
  if (!period)     return res.status(400).json({ message: 'period 为必填项' })
  if (!period_type) return res.status(400).json({ message: 'period_type 为必填项' })

  const rule = db.prepare('SELECT * FROM rules WHERE id = ?').get(rule_id)
  if (!rule) return res.status(404).json({ message: '规则不存在' })

  // 确定考核时间范围
  const periodStart = rule.period_start || period
  const periodEnd   = rule.period_end   || period

  // 获取待计算记者列表
  let targetReporters
  if (reporters?.length) {
    targetReporters = reporters
  } else {
    targetReporters = db.prepare(`SELECT DISTINCT reporter,station FROM work_records
      WHERE occurred_date >= ? AND occurred_date <= ?
      ${req.user.role === 'station' ? 'AND station = ?' : ''}`)
      .all(...(req.user.role === 'station' ? [periodStart, periodEnd, req.user.station] : [periodStart, periodEnd]))
      .map(r => ({ reporter: r.reporter, station: r.station }))
  }

  if (targetReporters.length === 0)
    return res.status(201).json({ message: `指定范围（${period}）内无记者数据`, results: [] })

  const items = db.prepare(`SELECT * FROM rule_items WHERE rule_id = ? AND enabled = 1 ORDER BY display_order`)
    .all(rule_id)

  // 按维度分组权重
  const catWeights = {}
  for (const it of items) {
    catWeights[it.category] = (catWeights[it.category] || 0) + it.weight
  }

  const results = []
  db.exec('BEGIN')
  try {
    for (const { reporter, station } of targetReporters) {
      const metrics = buildMetricQuery(rule_id, reporter, station, periodStart, periodEnd)
      let totalScore = 0
      const itemDetails = []
      const catScores = { quantity: 0, quality: 0, efficiency: 0, compliance: 0 }

      for (const item of items) {
        const metricValue = metrics[item.metric_key] ?? null
        const raw = computeItemScore(item, metricValue)
        const weighted = raw * item.weight
        totalScore += weighted
        catScores[item.category] = (catScores[item.category] || 0) + weighted
        itemDetails.push({
          item_id: item.id,
          category: item.category,
          name: item.name,
          metric_key: item.metric_key,
          metric_value: metricValue,
          raw_score: raw,
          weight: item.weight,
          weighted_score: Math.round(weighted * 100) / 100,
        })
      }

      const code = `SCORE_${Date.now()}_${Math.random().toString(36).slice(2, 6)}`
      db.prepare(`INSERT OR REPLACE INTO scores
        (code,rule_id,reporter,station,period,period_type,total_score,
         quality_score,quantity_score,efficiency_score,compliance_score,item_details,computed_at)
        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP)`)
        .run(code, rule_id, reporter, station, period, period_type,
             Math.round(totalScore * 100) / 100,
             Math.round((catScores.quality || 0) * 100) / 100,
             Math.round((catScores.quantity || 0) * 100) / 100,
             Math.round((catScores.efficiency || 0) * 100) / 100,
             Math.round((catScores.compliance || 0) * 100) / 100,
             JSON.stringify(itemDetails))
      results.push({ reporter, station, totalScore: Math.round(totalScore * 100) / 100 })
    }
    db.exec('COMMIT')
    res.status(201).json({ message: `已为 ${results.length} 名记者计算评分`, results })
  } catch (e) {
    db.exec('ROLLBACK')
    res.status(500).json({ message: '服务端处理失败' })
  }
})

// ═══════════════════════════════════════════════════════════════════════════
// 积分排行榜
// ═══════════════════════════════════════════════════════════════════════════

app.get('/api/leaderboard', (req, res) => {
  const { period, limit = '50', group_by = 'reporter' } = req.query
  if (!['reporter', 'station'].includes(group_by))
    return res.status(400).json({ message: 'group_by 必须是 reporter 或 station' })

  // 先找最新评分记录对应的规则 ID
  const ruleRow = db.prepare(
    'SELECT DISTINCT rule_id FROM scores WHERE period = ? ORDER BY computed_at DESC LIMIT 1'
  ).get(period || '2026-Q3')
  if (!ruleRow) return res.json({ period, groupBy: group_by, ranks: [] })

  const ruleId = ruleRow.rule_id

  if (group_by === 'reporter') {
    // 记者排名：取每位记者 latest score，关联 station 名称
    const rows = db.prepare(`
      SELECT s.reporter AS name, s.station,
             MAX(s.total_score) AS totalScore,
             MAX(s.computed_at) AS latestAt
      FROM scores s
      WHERE s.period = ? AND s.rule_id = ? AND s.reporter IS NOT NULL
      GROUP BY s.reporter, s.station
      ORDER BY totalScore DESC, latestAt ASC
      LIMIT ?`
    ).all(period || '2026-Q3', ruleId, Number(limit))

    // 标注当前登录人是否在榜上
    const me = rows.findIndex(r => r.name === req.user.name)
    const ranked = rows.map((r, i) => ({ rank: i + 1, ...r }))
    res.json({ period: period || '2026-Q3', groupBy: 'reporter', myRank: me >= 0 ? me + 1 : null, ranks: ranked })
  } else {
    // 记者站排名：取每站平均总分
    const rows = db.prepare(`
      SELECT s.station AS name,
             ROUND(AVG(s.total_score), 2) AS avgScore,
             COUNT(DISTINCT s.reporter) AS reporterCount,
             ROUND(AVG(s.quality_score),2)  AS avgQuality,
             ROUND(AVG(s.quantity_score),2) AS avgQuantity,
             ROUND(AVG(s.efficiency_score),2) AS avgEfficiency,
             ROUND(AVG(s.compliance_score),2) AS avgCompliance
      FROM scores s
      WHERE s.period = ? AND s.rule_id = ? AND s.station IS NOT NULL
      GROUP BY s.station
      ORDER BY avgScore DESC
      LIMIT ?`
    ).all(period || '2026-Q3', ruleId, Number(limit))

    const me = rows.findIndex(r => r.name === req.user.station)
    const ranked = rows.map((r, i) => ({ rank: i + 1, ...r }))
    res.json({ period: period || '2026-Q3', groupBy: 'station', myRank: me >= 0 ? me + 1 : null, ranks: ranked })
  }
})

app.get('/api/me/summary', (req, res) => {
  // 当前登录人的考核汇总：最新周期得分 + 排名
  const period = req.query.period || '2026-Q3'
  const role   = req.user.role

  if (role === 'headquarters') {
    return res.json({ period, score: null, rank: null, total: 0, message: '总部管理员无个人考核数据' })
  }

  const meField  = role === 'station' ? 'station' : 'reporter'
  const meValue  = role === 'station' ? req.user.station : req.user.name

  const myScore = db.prepare(`
    SELECT s.total_score AS totalScore, s.quality_score, s.quantity_score,
           s.efficiency_score, s.compliance_score, s.period, s.computed_at AS computedAt,
           r.name AS ruleName
    FROM scores s LEFT JOIN rules r ON s.rule_id = r.id
    WHERE s.${meField} = ? AND s.period = ?
    ORDER BY s.computed_at DESC LIMIT 1`
  ).get(meValue, period)

  if (!myScore) return res.json({ period, score: null, rank: null, total: 0 })

  const total = db.prepare(`
    SELECT COUNT(*) AS cnt FROM (
      SELECT DISTINCT reporter FROM scores WHERE period = ? AND rule_id = ?
    )`
  ).get(period, myScore.ruleName ? db.prepare('SELECT id FROM rules WHERE name = ?').get(myScore.ruleName)?.id : 0)

  const rank = db.prepare(`
    SELECT COUNT(*) + 1 AS rank FROM scores
    WHERE period = ? AND rule_id = ?
      AND (total_score > ? OR (total_score = ? AND computed_at < ?))`
  ).get(period, myScore.ruleName ? db.prepare('SELECT id FROM rules WHERE name = ?').get(myScore.ruleName)?.id : 0,
        myScore.totalScore, myScore.totalScore, myScore.computedAt)

  res.json({
    period,
    score: myScore,
    rank:   rank?.rank ?? null,
    total:  total?.cnt ?? 0,
  })
})

// ═══════════════════════════════════════════════════════════════════════════
// 通知删除 + 撤回
// ═══════════════════════════════════════════════════════════════════════════

app.delete('/api/notices/:id', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可删除通知' })
  const notice = db.prepare('SELECT * FROM notices WHERE id = ?').get(req.params.id)
  if (!notice) return res.status(404).json({ message: '通知不存在' })
  db.exec('BEGIN')
  try {
    db.prepare('DELETE FROM notice_receipts WHERE notice_id = ?').run(req.params.id)
    db.prepare('DELETE FROM notices WHERE id = ?').run(req.params.id)
    db.exec('COMMIT')
    logSystem(req.user.role, req.user.name, 'notices', 'delete', 'notice', req.params.id, `删除通知: ${notice.title}`)
    res.json({ message: '删除成功' })
  } catch (e) {
    db.exec('ROLLBACK')
    res.status(500).json({ message: '删除失败' })
  }
})

// 撤回通知
app.post('/api/notices/:id/withdraw', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可撤回通知' })
  const notice = db.prepare('SELECT * FROM notices WHERE id = ?').get(req.params.id)
  if (!notice) return res.status(404).json({ message: '通知不存在' })
  if (notice.status === 'withdrawn')
    return res.status(409).json({ message: '该通知已撤回' })
  db.prepare(`UPDATE notices SET status = 'withdrawn', withdrawn_at = datetime('now','localtime'), updated_at = datetime('now','localtime') WHERE id = ?`).run(req.params.id)
  logSystem(req.user.role, req.user.name, 'notices', 'withdraw', 'notice', req.params.id, `撤回通知: ${notice.title}`)
  res.json({ message: '通知已撤回' })
})

// ═══════════════════════════════════════════════════════════════════════════
// 申诉复议 API
// ═══════════════════════════════════════════════════════════════════════════

app.get('/api/appeals', (req, res) => {
  let sql = `SELECT id,code,record_id AS recordId,appellant,station,reason,status,
    handler,handler_role AS handlerRole,response,
    datetime(created_at,'localtime') AS createdAt,
    datetime(updated_at,'localtime') AS updatedAt,
    datetime(handled_at,'localtime') AS handledAt
    FROM appeals WHERE 1=1`
  const params = []
  if (req.user.role === 'reporter') { sql += ' AND appellant = ?'; params.push(req.user.name) }
  if (req.user.role === 'station')  { sql += ' AND station = ?';  params.push(req.user.station) }
  if (req.query.status) { sql += ' AND status = ?'; params.push(req.query.status) }
  sql += ' ORDER BY created_at DESC'
  res.json(db.prepare(sql).all(...params))
})

app.post('/api/appeals', (req, res) => {
  const { recordId, reason } = req.body
  if (!recordId || !reason?.trim())
    return res.status(400).json({ message: '记录ID和申诉原因为必填项' })
  const record = db.prepare(`${recordSelect} AND id = ?`).get(recordId)
  if (!record) return res.status(404).json({ message: '工作记录不存在' })
  if (record.reporter !== req.user.name && req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅填报人可申诉' })
  if (record.status !== 'archived' && record.status !== 'returned')
    return res.status(409).json({ message: '仅已归档或已退回的记录可申诉' })

  const code = `APPEAL_${Date.now()}`
  db.prepare(`INSERT INTO appeals (code,record_id,appellant,station,reason,status)
    VALUES (?,?,?,?,?,?)`).run(code, recordId, req.user.name, record.station, reason.trim(), 'pending')
  logSystem(req.user.role, req.user.name, 'appeals', 'create', 'appeal', code, `申诉记录: ${recordId}`)
  res.status(201).json({ message: '申诉已提交', code })
})

app.patch('/api/appeals/:id', (req, res) => {
  if (req.user.role === 'reporter')
    return res.status(403).json({ message: '记者无权处理申诉' })
  const appeal = db.prepare('SELECT * FROM appeals WHERE id = ?').get(req.params.id)
  if (!appeal) return res.status(404).json({ message: '申诉不存在' })
  if (appeal.status !== 'pending')
    return res.status(409).json({ message: '该申诉已处理' })
  const { decision, response } = req.body
  if (!['uphold', 'overturn'].includes(decision))
    return res.status(400).json({ message: 'decision 必须是 uphold 或 overturn' })

  const status = decision === 'uphold' ? 'rejected' : 'accepted'
  db.prepare(`UPDATE appeals SET status = ?, handler = ?, handler_role = ?, response = ?, handled_at = datetime('now','localtime'), updated_at = datetime('now','localtime') WHERE id = ?`)
    .run(status, req.user.name, req.user.role, response?.trim() || null, req.params.id)

  // 如果复议成功，退回记录到填报人修改
  if (decision === 'overturn') {
    db.prepare(`UPDATE work_records SET status = 'returned', updated_at = CURRENT_TIMESTAMP WHERE id = ?`).run(appeal.record_id)
  }

  logSystem(req.user.role, req.user.name, 'appeals', decision, 'appeal', req.params.id, `处理申诉: ${appeal.code}`)
  res.json({ message: decision === 'uphold' ? '申诉已驳回' : '申诉已通过，记录已退回' })
})

// ═══════════════════════════════════════════════════════════════════════════
// 人员调站
// ═══════════════════════════════════════════════════════════════════════════

app.post('/api/people/:id/transfer', (req, res) => {
  if (req.user.role !== 'headquarters')
    return res.status(403).json({ message: '仅总部管理员可调站' })
  const person = db.prepare('SELECT * FROM people WHERE id = ? AND deleted_at IS NULL').get(req.params.id)
  if (!person) return res.status(404).json({ message: '人员不存在' })
  const { toStation, reason } = req.body
  if (!toStation?.trim()) return res.status(400).json({ message: '目标站点为必填项' })

  const fromStation = person.station
  db.exec('BEGIN')
  try {
    db.prepare('UPDATE people SET station = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?').run(toStation.trim(), req.params.id)
    db.prepare(`INSERT INTO person_transfers (person_id,from_station,to_station,reason,operated_by)
      VALUES (?,?,?,?,?)`).run(req.params.id, fromStation, toStation.trim(), reason?.trim() || null, req.user.name)
    db.exec('COMMIT')
    logSystem(req.user.role, req.user.name, 'people', 'transfer', 'person', req.params.id, `${person.name}: ${fromStation} -> ${toStation}`)
    res.json({ message: `已将 ${person.name} 从 ${fromStation} 调至 ${toStation}` })
  } catch (e) {
    db.exec('ROLLBACK')
    res.status(500).json({ message: '调站失败' })
  }
})

app.get('/api/people/:id/transfers', (req, res) => {
  const transfers = db.prepare(`SELECT id,from_station AS fromStation,to_station AS toStation,reason,operated_by AS operatedBy,
    datetime(transferred_at,'localtime') AS transferredAt
    FROM person_transfers WHERE person_id = ? ORDER BY transferred_at DESC`).all(req.params.id)
  res.json(transfers)
})

// ═══════════════════════════════════════════════════════════════════════════
// 导出 API（CSV 格式）
// ═══════════════════════════════════════════════════════════════════════════

function csvEscape(value) {
  if (value === null || value === undefined) return ''
  const str = String(value)
  if (str.includes(',') || str.includes('"') || str.includes('\n')) {
    return `"${str.replace(/"/g, '""')}"`
  }
  return str
}

app.get('/api/export/records', (req, res) => {
  let sql = recordSelect
  const params = []
  if (req.user.role === 'station')  { sql += ' AND station = ?'; params.push(req.user.station) }
  if (req.user.role === 'reporter') { sql += ' AND reporter = ?'; params.push(req.user.name) }
  if (req.query.startDate) { sql += ' AND occurred_date >= ?'; params.push(req.query.startDate) }
  if (req.query.endDate)   { sql += ' AND occurred_date <= ?'; params.push(req.query.endDate) }
  if (req.query.type)      { sql += ' AND type = ?'; params.push(req.query.type) }
  if (req.query.status)    { sql += ' AND status = ?'; params.push(req.query.status) }
  sql += ' ORDER BY updated_at DESC'
  const rows = db.prepare(sql).all(...params)
  const headers = ['ID', '标题', '类型', '填报人', '站点', '日期', '平台', '状态', '得分', '说明']
  const csv = [headers.join(',')]
  for (const r of rows) {
    csv.push([r.id, r.title, r.type, r.reporter, r.station, r.date, r.platform, r.status, r.score ?? '', r.description ?? ''].map(csvEscape).join(','))
  }
  logSystem(req.user.role, req.user.name, 'export', 'records', 'work_records', null, `导出 ${rows.length} 条记录`)
  res.setHeader('Content-Type', 'text/csv; charset=utf-8')
  res.setHeader('Content-Disposition', `attachment; filename="work_records_${Date.now()}.csv"`)
  res.send('\ufeff' + csv.join('\n'))
})

app.get('/api/export/people', (req, res) => {
  if (req.user.role === 'reporter')
    return res.status(403).json({ message: '无权导出人员数据' })
  let sql = `SELECT id,code,name,station,title,phone,joined_at AS joinedAt,status FROM people WHERE deleted_at IS NULL`
  const params = []
  if (req.user.role === 'station') { sql += ' AND station = ?'; params.push(req.user.station) }
  sql += ' ORDER BY station, name'
  const rows = db.prepare(sql).all(...params)
  const headers = ['ID', '工号', '姓名', '站点', '职务', '手机号', '入站时间', '状态']
  const csv = [headers.join(',')]
  for (const r of rows) {
    csv.push([r.id, r.code, r.name, r.station, r.title ?? '', r.phone ?? '', r.joinedAt ?? '', r.status].map(csvEscape).join(','))
  }
  logSystem(req.user.role, req.user.name, 'export', 'people', 'people', null, `导出 ${rows.length} 条人员`)
  res.setHeader('Content-Type', 'text/csv; charset=utf-8')
  res.setHeader('Content-Disposition', `attachment; filename="people_${Date.now()}.csv"`)
  res.send('\ufeff' + csv.join('\n'))
})

app.get('/api/export/scores', (req, res) => {
  const { period } = req.query
  let sql = `SELECT s.reporter,s.station,s.period,s.total_score AS totalScore,
    s.quality_score AS qualityScore,s.quantity_score AS quantityScore,
    s.efficiency_score AS efficiencyScore,s.compliance_score AS complianceScore,
    r.name AS ruleName
    FROM scores s LEFT JOIN rules r ON s.rule_id = r.id WHERE 1=1`
  const params = []
  if (!req.user.role.includes('headquarters')) {
    sql += ' AND (s.reporter = ? OR s.station = ?)'
    params.push(req.user.name, req.user.station)
  }
  if (period) { sql += ' AND s.period = ?'; params.push(period) }
  sql += ' ORDER BY s.total_score DESC'
  const rows = db.prepare(sql).all(...params)
  const headers = ['记者', '站点', '周期', '总分', '质量得分', '数量得分', '效率得分', '合规得分', '规则']
  const csv = [headers.join(',')]
  for (const r of rows) {
    csv.push([r.reporter, r.station, r.period, r.totalScore ?? '', r.qualityScore ?? '', r.quantityScore ?? '', r.efficiencyScore ?? '', r.complianceScore ?? '', r.ruleName ?? ''].map(csvEscape).join(','))
  }
  logSystem(req.user.role, req.user.name, 'export', 'scores', 'scores', null, `导出 ${rows.length} 条评分`)
  res.setHeader('Content-Type', 'text/csv; charset=utf-8')
  res.setHeader('Content-Disposition', `attachment; filename="scores_${Date.now()}.csv"`)
  res.send('\ufeff' + csv.join('\n'))
})

// ═══════════════════════════════════════════════════════════════════════════
// 错误处理
// ═══════════════════════════════════════════════════════════════════════════

app.use((err, _req, res, _next) => {
  console.error(err)
  res.status(500).json({ message: '服务端处理失败' })
})

// ── 启动 ──────────────────────────────────────────────────────────────────
const port = Number(process.env.API_PORT || 8787)
app.listen(port, '127.0.0.1', () => console.log(`API ready at http://127.0.0.1:${port}`))