// 数据存储层（SQLite 持久化，基于 better-sqlite3）
// 该层是数据访问的唯一入口：对外导出的函数签名与返回结构保持稳定，
// 路由层/前端无需感知底层是 JSON 还是数据库。
import Database from 'better-sqlite3'
import fs from 'node:fs'
import path from 'node:path'
import crypto from 'node:crypto'
import { fileURLToPath } from 'node:url'
import { buildSeed, buildConfessions } from './seed.js'

const __dirname = path.dirname(fileURLToPath(import.meta.url))
const DATA_DIR = path.join(__dirname, '..', 'data')
const DB_FILE = path.join(DATA_DIR, 'app.db')
const LEGACY_JSON = path.join(DATA_DIR, 'db.json') // 旧的 JSON 数据，用于一次性迁移

let db = null

const STATUS_TEXT = {
  waiting: '待接单',
  running: '配送中',
  done: '已完成',
  canceled: '已取消'
}
export const ALLOWED_STATUS = Object.keys(STATUS_TEXT)
export const statusText = (s) => STATUS_TEXT[s] || s

const CONFESSION_STATUS = ['pending', 'approved', 'rejected']

// ---------------- 初始化 / 建表 / 迁移 ----------------
export function initStore() {
  if (!fs.existsSync(DATA_DIR)) fs.mkdirSync(DATA_DIR, { recursive: true })
  db = new Database(DB_FILE)
  db.pragma('journal_mode = WAL')
  createSchema()
  ensureColumns()
  // 首次运行（services 表为空）：优先迁移旧 db.json，否则用种子
  const count = db.prepare('SELECT COUNT(*) AS n FROM services').get().n
  if (count === 0) seedDatabase(loadSeedSource())
  ensureDefaultAdmin()
}

// 为旧库补齐客户密码列（SQLite 不支持 ADD COLUMN IF NOT EXISTS，手动检查）
function ensureColumns() {
  const cols = db.prepare('PRAGMA table_info(customers)').all().map((c) => c.name)
  if (!cols.includes('passwordHash')) db.exec('ALTER TABLE customers ADD COLUMN passwordHash TEXT')
  if (!cols.includes('passwordSalt')) db.exec('ALTER TABLE customers ADD COLUMN passwordSalt TEXT')
}

// 默认管理员（admins 表为空时创建 admin/admin888）
function ensureDefaultAdmin() {
  const n = db.prepare('SELECT COUNT(*) AS n FROM admins').get().n
  if (n === 0) {
    const { hash, salt } = hashPassword('admin888')
    db.prepare(
      'INSERT INTO admins (id,username,passwordHash,passwordSalt,createdAt) VALUES (?,?,?,?,?)'
    ).run('A1', 'admin', hash, salt, new Date().toISOString())
  }
}

function createSchema() {
  db.exec(`
    CREATE TABLE IF NOT EXISTS services (
      id TEXT PRIMARY KEY,
      key TEXT UNIQUE NOT NULL,
      name TEXT NOT NULL,
      icon TEXT,
      base REAL DEFAULT 4,
      sort INTEGER DEFAULT 999,
      status TEXT DEFAULT 'active',
      descr TEXT DEFAULT '',
      createdAt TEXT
    );
    CREATE TABLE IF NOT EXISTS notices (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      text TEXT NOT NULL,
      sort INTEGER DEFAULT 0
    );
    CREATE TABLE IF NOT EXISTS customers (
      id TEXT PRIMARY KEY,
      name TEXT, phone TEXT, campus TEXT,
      balance REAL DEFAULT 0, coupons INTEGER DEFAULT 0, points INTEGER DEFAULT 0,
      status TEXT DEFAULT 'active',
      orderCount INTEGER DEFAULT 0, totalSpend REAL DEFAULT 0,
      passwordHash TEXT, passwordSalt TEXT,
      createdAt TEXT
    );
    CREATE TABLE IF NOT EXISTS orders (
      id TEXT PRIMARY KEY,
      customerId TEXT, customerName TEXT, customerPhone TEXT, campus TEXT,
      serviceKey TEXT, type TEXT, status TEXT,
      fromAddr TEXT, toAddr TEXT, goods TEXT, note TEXT,
      baseFee REAL, reward REAL, total REAL,
      riderName TEXT,
      createdAt TEXT, updatedAt TEXT
    );
    CREATE TABLE IF NOT EXISTS confessions (
      id TEXT PRIMARY KEY,
      toTarget TEXT, content TEXT, author TEXT,
      anonymous INTEGER DEFAULT 0,
      theme INTEGER DEFAULT 0,
      likes INTEGER DEFAULT 0,
      status TEXT DEFAULT 'pending',
      createdAt TEXT, updatedAt TEXT
    );
    CREATE TABLE IF NOT EXISTS comments (
      id TEXT PRIMARY KEY,
      confessionId TEXT,
      name TEXT, content TEXT, createdAt TEXT
    );
    CREATE TABLE IF NOT EXISTS meta ( k TEXT PRIMARY KEY, v TEXT );
    CREATE TABLE IF NOT EXISTS admins (
      id TEXT PRIMARY KEY,
      username TEXT UNIQUE NOT NULL,
      passwordHash TEXT, passwordSalt TEXT,
      createdAt TEXT
    );
    CREATE TABLE IF NOT EXISTS sessions (
      token TEXT PRIMARY KEY,
      subjectId TEXT NOT NULL,
      role TEXT NOT NULL,
      createdAt TEXT
    );
    CREATE INDEX IF NOT EXISTS idx_orders_service ON orders(serviceKey);
    CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customerId);
    CREATE INDEX IF NOT EXISTS idx_comments_conf ON comments(confessionId);
  `)
}

// 迁移数据源：存在旧 db.json 则用它（保留历史订单/客户），否则用种子
function loadSeedSource() {
  if (fs.existsSync(LEGACY_JSON)) {
    try {
      const j = JSON.parse(fs.readFileSync(LEGACY_JSON, 'utf-8'))
      if (!Array.isArray(j.confessions)) j.confessions = buildConfessions()
      return j
    } catch (e) {
      console.warn('[store] 旧 db.json 解析失败，改用种子数据')
    }
  }
  return buildSeed()
}

function seedDatabase(src) {
  const insSvc = db.prepare(
    `INSERT INTO services (id,key,name,icon,base,sort,status,descr,createdAt)
     VALUES (@id,@key,@name,@icon,@base,@sort,@status,@descr,@createdAt)`
  )
  const insNotice = db.prepare('INSERT INTO notices (text,sort) VALUES (?,?)')
  const insCust = db.prepare(
    `INSERT INTO customers (id,name,phone,campus,balance,coupons,points,status,orderCount,totalSpend,createdAt)
     VALUES (@id,@name,@phone,@campus,@balance,@coupons,@points,@status,@orderCount,@totalSpend,@createdAt)`
  )
  const insOrder = db.prepare(
    `INSERT INTO orders (id,customerId,customerName,customerPhone,campus,serviceKey,type,status,fromAddr,toAddr,goods,note,baseFee,reward,total,riderName,createdAt,updatedAt)
     VALUES (@id,@customerId,@customerName,@customerPhone,@campus,@serviceKey,@type,@status,@fromAddr,@toAddr,@goods,@note,@baseFee,@reward,@total,@riderName,@createdAt,@updatedAt)`
  )
  const insConf = db.prepare(
    `INSERT INTO confessions (id,toTarget,content,author,anonymous,theme,likes,status,createdAt,updatedAt)
     VALUES (@id,@toTarget,@content,@author,@anonymous,@theme,@likes,@status,@createdAt,@updatedAt)`
  )
  const insComment = db.prepare(
    'INSERT INTO comments (id,confessionId,name,content,createdAt) VALUES (@id,@confessionId,@name,@content,@createdAt)'
  )
  const tx = db.transaction(() => {
    for (const s of src.services || []) {
      insSvc.run({
        id: s.id || 'S' + Date.now(),
        key: s.key,
        name: s.name,
        icon: s.icon || s.key,
        base: Number(s.base) || 0,
        sort: s.sort !== undefined ? s.sort : 999,
        status: s.status || 'active',
        descr: s.desc || '',
        createdAt: s.createdAt || new Date().toISOString()
      })
    }
    ;(src.notices || []).forEach((t, i) => insNotice.run(String(t), i))
    for (const c of src.customers || []) {
      insCust.run({
        id: c.id,
        name: c.name,
        phone: c.phone,
        campus: c.campus,
        balance: Number(c.balance) || 0,
        coupons: Number(c.coupons) || 0,
        points: Number(c.points) || 0,
        status: c.status || 'active',
        orderCount: Number(c.orderCount) || 0,
        totalSpend: Number(c.totalSpend) || 0,
        createdAt: c.createdAt || new Date().toISOString()
      })
    }
    for (const o of src.orders || []) {
      insOrder.run({
        id: o.id,
        customerId: o.customerId,
        customerName: o.customerName,
        customerPhone: o.customerPhone,
        campus: o.campus,
        serviceKey: o.serviceKey,
        type: o.type,
        status: o.status,
        fromAddr: o.from || '',
        toAddr: o.to || '',
        goods: o.goods || '',
        note: o.note || '',
        baseFee: Number(o.baseFee) || 0,
        reward: Number(o.reward) || 0,
        total: Number(o.total) || 0,
        riderName: o.riderName || null,
        createdAt: o.createdAt,
        updatedAt: o.updatedAt || o.createdAt
      })
    }
    for (const cf of src.confessions || []) {
      insConf.run({
        id: cf.id,
        toTarget: cf.to || '',
        content: cf.content,
        author: cf.author || '匿名',
        anonymous: cf.anonymous ? 1 : 0,
        theme: Number(cf.theme) || 0,
        likes: Number(cf.likes) || 0,
        status: cf.status || 'pending',
        createdAt: cf.createdAt,
        updatedAt: cf.updatedAt || cf.createdAt
      })
      for (const m of cf.comments || []) {
        insComment.run({
          id: m.id || 'CM' + Date.now(),
          confessionId: cf.id,
          name: m.name || '匿名',
          content: m.content,
          createdAt: m.createdAt
        })
      }
    }
    setMeta('seqCounter', String(src.seqCounter || 1))
  })
  tx()
}

// ---------------- meta / 工具 ----------------
function getMeta(k, def) {
  const row = db.prepare('SELECT v FROM meta WHERE k=?').get(k)
  return row ? row.v : def
}
function setMeta(k, v) {
  db.prepare('INSERT INTO meta (k,v) VALUES (?,?) ON CONFLICT(k) DO UPDATE SET v=excluded.v').run(k, String(v))
}
function paginate(page, pageSize) {
  const p = Math.max(1, Number(page) || 1)
  const size = Math.min(100, Math.max(1, Number(pageSize) || 10))
  return { p, size, offset: (p - 1) * size }
}

// 行 -> 对象映射
function mapService(r) {
  if (!r) return null
  const { descr, ...rest } = r
  return { ...rest, desc: descr }
}
function mapOrder(r) {
  if (!r) return null
  const { fromAddr, toAddr, ...rest } = r
  return { ...rest, from: fromAddr, to: toAddr, statusText: statusText(r.status) }
}
function mapConfession(r, comments) {
  if (!r) return null
  const { toTarget, anonymous, ...rest } = r
  const o = { ...rest, to: toTarget, anonymous: !!anonymous }
  if (comments) o.comments = comments
  return o
}

// ---------------- 服务 ----------------
export function getServices() {
  const rows = db
    .prepare(
      `SELECT s.*, (SELECT COUNT(*) FROM orders o WHERE o.serviceKey = s.key) AS orderCount
       FROM services s ORDER BY sort ASC, rowid ASC`
    )
    .all()
  return rows.map((r) => ({ ...mapService(r), hasOrders: r.orderCount > 0 }))
}

export function getService(id) {
  return mapService(db.prepare('SELECT * FROM services WHERE id=?').get(id))
}

export function createService(payload) {
  const { id, key, name, icon, baseFee, base, sort, status, desc } = payload
  if (!key || !name || !icon) {
    return { error: 'MISSING_FIELD', msg: '缺少必要字段：key, name, icon' }
  }
  if (db.prepare('SELECT 1 FROM services WHERE key=?').get(key)) {
    return { error: 'DUPLICATE_KEY', msg: 'serviceKey 已存在' }
  }
  const svc = {
    id: id || 'S' + Date.now(),
    key,
    name,
    icon,
    base: Number(baseFee !== undefined ? baseFee : base) || 4,
    sort: Number(sort) || 999,
    status: status !== undefined ? status : 'active',
    descr: desc || '',
    createdAt: new Date().toISOString()
  }
  db.prepare(
    `INSERT INTO services (id,key,name,icon,base,sort,status,descr,createdAt)
     VALUES (@id,@key,@name,@icon,@base,@sort,@status,@descr,@createdAt)`
  ).run(svc)
  return mapService(svc)
}

export function updateService(id, payload) {
  const cur = db.prepare('SELECT * FROM services WHERE id=?').get(id)
  if (!cur) return { error: 'NOT_FOUND', msg: '服务不存在' }
  if (payload.key && payload.key !== cur.key) {
    if (db.prepare('SELECT 1 FROM services WHERE key=? AND id<>?').get(payload.key, id)) {
      return { error: 'DUPLICATE_KEY', msg: 'serviceKey 已被占用' }
    }
  }
  const next = {
    id,
    key: payload.key || cur.key,
    name: payload.name || cur.name,
    icon: payload.icon || cur.icon,
    base: payload.base !== undefined ? Number(payload.base) : cur.base,
    sort: payload.sort !== undefined ? Number(payload.sort) : cur.sort,
    status: payload.status !== undefined ? payload.status : cur.status,
    descr: payload.desc !== undefined ? payload.desc : cur.descr
  }
  db.prepare(
    `UPDATE services SET key=@key,name=@name,icon=@icon,base=@base,sort=@sort,status=@status,descr=@descr WHERE id=@id`
  ).run(next)
  return mapService({ ...next, createdAt: cur.createdAt })
}

export function deleteService(id) {
  const svc = db.prepare('SELECT * FROM services WHERE id=?').get(id)
  if (!svc) return { error: 'NOT_FOUND', msg: '服务不存在' }
  const n = db.prepare('SELECT COUNT(*) AS n FROM orders WHERE serviceKey=?').get(svc.key).n
  if (n > 0) return { error: 'HAS_ORDERS', msg: `该服务下有 ${n} 条订单，无法删除` }
  db.prepare('DELETE FROM services WHERE id=?').run(id)
  return { success: true }
}

export function reorderServices(ids) {
  const total = db.prepare('SELECT COUNT(*) AS n FROM services').get().n
  const valid = ids.filter((id) => db.prepare('SELECT 1 FROM services WHERE id=?').get(id))
  if (valid.length !== total) return false
  const upd = db.prepare('UPDATE services SET sort=? WHERE id=?')
  db.transaction(() => valid.forEach((id, i) => upd.run(i, id)))()
  return true
}

export function getNotices() {
  return db.prepare('SELECT text FROM notices ORDER BY sort ASC, id ASC').all().map((r) => r.text)
}

// ---------------- 订单 ----------------
export function listOrders({ status, keyword, customerId, page, pageSize } = {}) {
  const where = []
  const params = {}
  if (status && status !== 'all') {
    where.push('status = @status')
    params.status = status
  }
  if (customerId) {
    where.push('customerId = @customerId')
    params.customerId = customerId
  }
  if (keyword) {
    where.push('(id LIKE @kw OR customerName LIKE @kw OR customerPhone LIKE @kw OR type LIKE @kw)')
    params.kw = '%' + String(keyword).trim() + '%'
  }
  const clause = where.length ? 'WHERE ' + where.join(' AND ') : ''
  const total = db.prepare(`SELECT COUNT(*) AS n FROM orders ${clause}`).get(params).n
  const { p, size, offset } = paginate(page, pageSize)
  const rows = db
    .prepare(`SELECT * FROM orders ${clause} ORDER BY createdAt DESC LIMIT @size OFFSET @off`)
    .all({ ...params, size, off: offset })
  return { list: rows.map(mapOrder), total, page: p, pageSize: size }
}

export function getOrder(id) {
  return mapOrder(db.prepare('SELECT * FROM orders WHERE id=?').get(id))
}

function nextOrderId() {
  const d = new Date()
  const ymd = `${d.getFullYear()}${String(d.getMonth() + 1).padStart(2, '0')}${String(d.getDate()).padStart(2, '0')}`
  const seq = (Number(getMeta('seqCounter', '1')) || 1) + 1
  setMeta('seqCounter', seq)
  return `PT${ymd}${String(seq).padStart(3, '0')}`
}

export function createOrder(payload) {
  const svc = db
    .prepare('SELECT * FROM services WHERE key=? OR name=?')
    .get(payload.serviceKey || '', payload.type || '')
  const baseFee = svc ? svc.base : Number(payload.baseFee) || 4
  const reward = Number(payload.reward) || 0
  const now = new Date().toISOString()
  let customer = null
  if (payload.customerId) customer = db.prepare('SELECT * FROM customers WHERE id=?').get(payload.customerId)

  const order = {
    id: nextOrderId(),
    customerId: payload.customerId || (customer ? customer.id : 'U0000'),
    customerName: payload.customerName || (customer ? customer.name : '游客'),
    customerPhone: payload.customerPhone || (customer ? customer.phone : ''),
    campus: payload.campus || (customer ? customer.campus : '东校区'),
    serviceKey: svc ? svc.key : payload.serviceKey || 'express',
    type: svc ? svc.name : payload.type || '代拿快递',
    status: 'waiting',
    fromAddr: payload.from || '',
    toAddr: payload.to || '',
    goods: payload.goods || '',
    note: payload.note || '',
    baseFee,
    reward,
    total: Number((baseFee + reward).toFixed(2)),
    riderName: null,
    createdAt: now,
    updatedAt: now
  }
  db.prepare(
    `INSERT INTO orders (id,customerId,customerName,customerPhone,campus,serviceKey,type,status,fromAddr,toAddr,goods,note,baseFee,reward,total,riderName,createdAt,updatedAt)
     VALUES (@id,@customerId,@customerName,@customerPhone,@campus,@serviceKey,@type,@status,@fromAddr,@toAddr,@goods,@note,@baseFee,@reward,@total,@riderName,@createdAt,@updatedAt)`
  ).run(order)
  if (customer) db.prepare('UPDATE customers SET orderCount = orderCount + 1 WHERE id=?').run(customer.id)
  return mapOrder(order)
}

export function updateOrderStatus(id, status) {
  if (!ALLOWED_STATUS.includes(status)) return { error: 'INVALID_STATUS' }
  const o = db.prepare('SELECT * FROM orders WHERE id=?').get(id)
  if (!o) return { error: 'NOT_FOUND' }
  const prev = o.status
  const riderName = status !== 'waiting' && !o.riderName ? '骑手·小林' : o.riderName
  db.prepare('UPDATE orders SET status=?, updatedAt=?, riderName=? WHERE id=?').run(
    status,
    new Date().toISOString(),
    riderName,
    id
  )
  if (status === 'done' && prev !== 'done') {
    db.prepare('UPDATE customers SET totalSpend = ROUND(totalSpend + ?, 2) WHERE id=?').run(o.total, o.customerId)
  }
  return { order: mapOrder(db.prepare('SELECT * FROM orders WHERE id=?').get(id)) }
}

// ---------------- 客户 ----------------
export function listCustomers({ keyword, status, page, pageSize } = {}) {
  const where = []
  const params = {}
  if (status && status !== 'all') {
    where.push('status = @status')
    params.status = status
  }
  if (keyword) {
    where.push('(name LIKE @kw OR phone LIKE @kw OR id LIKE @kw)')
    params.kw = '%' + String(keyword).trim() + '%'
  }
  const clause = where.length ? 'WHERE ' + where.join(' AND ') : ''
  const total = db.prepare(`SELECT COUNT(*) AS n FROM customers ${clause}`).get(params).n
  const { p, size, offset } = paginate(page, pageSize)
  const rows = db
    .prepare(`SELECT * FROM customers ${clause} ORDER BY createdAt DESC LIMIT @size OFFSET @off`)
    .all({ ...params, size, off: offset })
  return { list: rows, total, page: p, pageSize: size }
}

export function getCustomer(id) {
  return db.prepare('SELECT * FROM customers WHERE id=?').get(id) || null
}

export function findCustomerByPhone(phone) {
  return db.prepare('SELECT * FROM customers WHERE phone=?').get(phone) || null
}

// ---------------- 认证（客户 / 管理员 / 会话） ----------------
function hashPassword(password, salt = crypto.randomBytes(16).toString('hex')) {
  const hash = crypto.scryptSync(String(password), salt, 32).toString('hex')
  return { hash, salt }
}
function verifyPassword(password, salt, hash) {
  if (!salt || !hash) return false
  const h = crypto.scryptSync(String(password), salt, 32).toString('hex')
  const a = Buffer.from(h)
  const b = Buffer.from(hash)
  return a.length === b.length && crypto.timingSafeEqual(a, b)
}
function createSession(subjectId, role) {
  const token = crypto.randomBytes(24).toString('hex')
  db.prepare('INSERT INTO sessions (token,subjectId,role,createdAt) VALUES (?,?,?,?)').run(
    token,
    subjectId,
    role,
    new Date().toISOString()
  )
  return token
}
export function getSession(token) {
  if (!token) return null
  return db.prepare('SELECT token,subjectId,role FROM sessions WHERE token=?').get(token) || null
}
export function destroySession(token) {
  if (token) db.prepare('DELETE FROM sessions WHERE token=?').run(token)
  return { success: true }
}
export function isAdminToken(token) {
  const s = getSession(token)
  return !!s && s.role === 'admin'
}

function publicProfile(row) {
  if (!row) return null
  const { passwordHash, passwordSalt, ...rest } = row
  return rest
}

export function registerCustomer({ phone, password, name, campus } = {}) {
  phone = String(phone || '').trim()
  password = String(password || '')
  name = String(name || '').trim()
  if (!/^1\d{10}$/.test(phone)) return { error: 'BAD_PHONE', msg: '请输入正确的手机号' }
  if (password.length < 6) return { error: 'BAD_PWD', msg: '密码至少 6 位' }
  if (!name) return { error: 'NO_NAME', msg: '请填写昵称' }
  if (db.prepare('SELECT 1 FROM customers WHERE phone=?').get(phone)) {
    return { error: 'DUP_PHONE', msg: '该手机号已注册' }
  }
  const { hash, salt } = hashPassword(password)
  const row = {
    id: 'U' + Date.now(),
    name,
    phone,
    campus: campus || '东校区',
    balance: 0,
    coupons: 0,
    points: 0,
    status: 'active',
    orderCount: 0,
    totalSpend: 0,
    passwordHash: hash,
    passwordSalt: salt,
    createdAt: new Date().toISOString()
  }
  db.prepare(
    `INSERT INTO customers (id,name,phone,campus,balance,coupons,points,status,orderCount,totalSpend,passwordHash,passwordSalt,createdAt)
     VALUES (@id,@name,@phone,@campus,@balance,@coupons,@points,@status,@orderCount,@totalSpend,@passwordHash,@passwordSalt,@createdAt)`
  ).run(row)
  const token = createSession(row.id, 'customer')
  return { token, profile: publicProfile(row) }
}

export function loginCustomer({ phone, password } = {}) {
  phone = String(phone || '').trim()
  const row = db.prepare('SELECT * FROM customers WHERE phone=?').get(phone)
  if (!row) return { error: 'NOT_FOUND', msg: '账号不存在，请先注册' }
  if (!verifyPassword(password, row.passwordSalt, row.passwordHash)) {
    return { error: 'BAD_CRED', msg: '手机号或密码错误' }
  }
  const token = createSession(row.id, 'customer')
  return { token, profile: publicProfile(row) }
}

export function loginAdmin({ username, password } = {}) {
  username = String(username || '').trim()
  const row = db.prepare('SELECT * FROM admins WHERE username=?').get(username)
  if (!row || !verifyPassword(password, row.passwordSalt, row.passwordHash)) {
    return { error: 'BAD_CRED', msg: '用户名或密码错误' }
  }
  const token = createSession(row.id, 'admin')
  return { token, username: row.username }
}

// ---------------- 表白墙 ----------------
export function listConfessions({ status, page, pageSize } = {}) {
  const where = []
  const params = {}
  if (status && status !== 'all') {
    where.push('status = @status')
    params.status = status
  }
  const clause = where.length ? 'WHERE ' + where.join(' AND ') : ''
  const total = db.prepare(`SELECT COUNT(*) AS n FROM confessions ${clause}`).get(params).n
  const { p, size, offset } = paginate(page, pageSize)
  const rows = db
    .prepare(
      `SELECT c.*, (SELECT COUNT(*) FROM comments m WHERE m.confessionId = c.id) AS commentCount
       FROM confessions c ${clause} ORDER BY createdAt DESC LIMIT @size OFFSET @off`
    )
    .all({ ...params, size, off: offset })
  const list = rows.map((r) => {
    const { commentCount } = r
    return { ...mapConfession(r), commentCount }
  })
  return { list, total, page: p, pageSize: size }
}

export function getConfession(id) {
  const r = db.prepare('SELECT * FROM confessions WHERE id=?').get(id)
  if (!r) return null
  const comments = db
    .prepare('SELECT id,name,content,createdAt FROM comments WHERE confessionId=? ORDER BY createdAt ASC')
    .all(id)
  return mapConfession(r, comments)
}

export function createConfession(payload) {
  const content = String(payload.content || '').trim()
  if (!content) return { error: 'EMPTY', msg: '表白内容不能为空' }
  const now = new Date().toISOString()
  const anonymous = !!payload.anonymous
  const conf = {
    id: 'CF' + Date.now(),
    toTarget: String(payload.to || '').trim(),
    content,
    author: anonymous ? '匿名' : String(payload.author || '').trim() || '匿名',
    anonymous: anonymous ? 1 : 0,
    theme: Number(payload.theme) || 0,
    likes: 0,
    status: 'pending',
    createdAt: now,
    updatedAt: now
  }
  db.prepare(
    `INSERT INTO confessions (id,toTarget,content,author,anonymous,theme,likes,status,createdAt,updatedAt)
     VALUES (@id,@toTarget,@content,@author,@anonymous,@theme,@likes,@status,@createdAt,@updatedAt)`
  ).run(conf)
  return { ...mapConfession(conf), comments: [], commentCount: 0 }
}

export function likeConfession(id) {
  const r = db.prepare('SELECT likes FROM confessions WHERE id=?').get(id)
  if (!r) return { error: 'NOT_FOUND' }
  const likes = r.likes + 1
  db.prepare('UPDATE confessions SET likes=? WHERE id=?').run(likes, id)
  return { likes }
}

export function addConfessionComment(id, payload) {
  const exists = db.prepare('SELECT 1 FROM confessions WHERE id=?').get(id)
  if (!exists) return { error: 'NOT_FOUND' }
  const content = String(payload.content || '').trim()
  if (!content) return { error: 'EMPTY', msg: '评论不能为空' }
  const comment = {
    id: 'CM' + Date.now(),
    confessionId: id,
    name: String(payload.name || '').trim() || '匿名',
    content,
    createdAt: new Date().toISOString()
  }
  db.prepare(
    'INSERT INTO comments (id,confessionId,name,content,createdAt) VALUES (@id,@confessionId,@name,@content,@createdAt)'
  ).run(comment)
  db.prepare('UPDATE confessions SET updatedAt=? WHERE id=?').run(comment.createdAt, id)
  const { confessionId, ...rest } = comment
  return rest
}

export function updateConfessionStatus(id, status) {
  if (!CONFESSION_STATUS.includes(status)) return { error: 'INVALID_STATUS' }
  const r = db.prepare('SELECT 1 FROM confessions WHERE id=?').get(id)
  if (!r) return { error: 'NOT_FOUND' }
  db.prepare('UPDATE confessions SET status=?, updatedAt=? WHERE id=?').run(status, new Date().toISOString(), id)
  return { confession: getConfession(id) }
}

export function deleteConfession(id) {
  const r = db.prepare('SELECT 1 FROM confessions WHERE id=?').get(id)
  if (!r) return { error: 'NOT_FOUND' }
  db.transaction(() => {
    db.prepare('DELETE FROM comments WHERE confessionId=?').run(id)
    db.prepare('DELETE FROM confessions WHERE id=?').run(id)
  })()
  return { success: true }
}

// ---------------- 统计（仪表盘） ----------------
export function getStats() {
  const totalOrders = db.prepare('SELECT COUNT(*) AS n FROM orders').get().n
  const byStatus = { waiting: 0, running: 0, done: 0, canceled: 0 }
  for (const row of db.prepare('SELECT status, COUNT(*) AS n FROM orders GROUP BY status').all()) {
    byStatus[row.status] = row.n
  }
  const gmv = db.prepare("SELECT COALESCE(SUM(total),0) AS s FROM orders WHERE status='done'").get().s
  const todayStr = new Date().toDateString()
  let todayOrders = 0
  for (const row of db.prepare('SELECT createdAt FROM orders').all()) {
    if (new Date(row.createdAt).toDateString() === todayStr) todayOrders += 1
  }
  return {
    totalOrders,
    byStatus,
    gmv: Number(Number(gmv).toFixed(2)),
    todayOrders,
    totalCustomers: db.prepare('SELECT COUNT(*) AS n FROM customers').get().n,
    activeCustomers: db.prepare("SELECT COUNT(*) AS n FROM customers WHERE status='active'").get().n,
    totalConfessions: db.prepare('SELECT COUNT(*) AS n FROM confessions').get().n,
    pendingConfessions: db.prepare("SELECT COUNT(*) AS n FROM confessions WHERE status='pending'").get().n
  }
}