841 lines
34 KiB
JavaScript
841 lines
34 KiB
JavaScript
// Storage 服务端数据层: DuckDB (@duckdb/node-api)
|
|
// 业务数据全部存储在服务端数据库文件 (DB_PATH, 生产建议挂持久化数据卷),
|
|
// 客户端不再持有数据库, 仅保留最近数据的读缓存。
|
|
// 采用版本化迁移机制 (schema_version), 禁止用裸 CREATE TABLE 演进。
|
|
//
|
|
// 重要约定: 由于 DuckDB 单写入者 + 全局 enqueue 串行化, 任何"enqueue"的公共函数
|
|
// 内部不得再调用其它 enqueue 函数 (会自锁)。因此所有底层读操作都实现为
|
|
// 内部函数 (直接 conn.run), 公共 API 一律是薄 enqueue 包装。
|
|
import { DuckDBInstance } from '@duckdb/node-api'
|
|
import path from 'node:path'
|
|
import fs from 'node:fs'
|
|
|
|
let instance = null
|
|
let conn = null
|
|
|
|
function dbFile() {
|
|
const p = process.env.DB_PATH || path.resolve(process.cwd(), 'data', 'storage.duckdb')
|
|
fs.mkdirSync(path.dirname(p), { recursive: true })
|
|
return p
|
|
}
|
|
|
|
let chain = Promise.resolve()
|
|
export function enqueue(fn) {
|
|
const p = chain.then(fn)
|
|
chain = p.catch(() => {})
|
|
return p
|
|
}
|
|
|
|
// ── 内部底层读 (非 enqueue) ──
|
|
async function rows(sql, params = []) {
|
|
const r = await conn.run(sql, params)
|
|
return (await r.getRowObjects()) ?? []
|
|
}
|
|
async function one(sql, params = []) {
|
|
const r = await rows(sql, params)
|
|
return r.length ? r[0] : null
|
|
}
|
|
async function count(sql, params = []) {
|
|
const r = await rows(sql, params)
|
|
return r.length ? Number(r[0].c) : 0
|
|
}
|
|
|
|
const nowIso = () => new Date().toISOString()
|
|
export const uu = () => globalThis.crypto.randomUUID()
|
|
|
|
function parseArr(v) {
|
|
if (Array.isArray(v)) return v
|
|
if (typeof v === 'string') {
|
|
try {
|
|
const p = JSON.parse(v)
|
|
return Array.isArray(p) ? p : []
|
|
} catch {
|
|
return []
|
|
}
|
|
}
|
|
return []
|
|
}
|
|
|
|
function mapBox(x) {
|
|
if (!x) return null
|
|
return {
|
|
...x,
|
|
pos_x: Number(x.pos_x) || 0,
|
|
pos_y: Number(x.pos_y) || 0,
|
|
width: x.width === null || x.width === undefined ? null : Number(x.width),
|
|
height: x.height === null || x.height === undefined ? null : Number(x.height),
|
|
direct_item_count: x.direct_item_count === undefined ? 0 : Number(x.direct_item_count || 0),
|
|
}
|
|
}
|
|
|
|
function mapItem(x) {
|
|
if (!x) return null
|
|
return {
|
|
...x,
|
|
tags: parseArr(x.tags),
|
|
images: parseArr(x.images),
|
|
quantity: Number(x.quantity || 1),
|
|
purchase_price: x.purchase_price === null || x.purchase_price === undefined ? null : Number(x.purchase_price),
|
|
is_favorite: !!x.is_favorite,
|
|
used_count: Number(x.used_count || 0),
|
|
}
|
|
}
|
|
|
|
// ── 迁移机制 ──
|
|
const MIGRATIONS = [
|
|
{
|
|
version: 1,
|
|
up: async (c) => {
|
|
await c.run(`CREATE TABLE IF NOT EXISTS users (
|
|
id VARCHAR PRIMARY KEY,
|
|
email VARCHAR UNIQUE,
|
|
phone VARCHAR UNIQUE,
|
|
nickname VARCHAR NOT NULL DEFAULT '',
|
|
avatar_path VARCHAR,
|
|
created_at VARCHAR NOT NULL
|
|
)`)
|
|
await c.run(`CREATE TABLE IF NOT EXISTS sessions (
|
|
token VARCHAR PRIMARY KEY,
|
|
user_id VARCHAR NOT NULL,
|
|
created_at VARCHAR NOT NULL
|
|
)`)
|
|
await c.run(`CREATE TABLE IF NOT EXISTS families (
|
|
id VARCHAR PRIMARY KEY,
|
|
name VARCHAR NOT NULL,
|
|
icon VARCHAR DEFAULT '🏠',
|
|
owner_user_id VARCHAR NOT NULL,
|
|
invite_code VARCHAR,
|
|
created_at VARCHAR NOT NULL,
|
|
updated_at VARCHAR NOT NULL
|
|
)`)
|
|
await c.run(`CREATE TABLE IF NOT EXISTS family_members (
|
|
family_id VARCHAR NOT NULL,
|
|
user_id VARCHAR NOT NULL,
|
|
role VARCHAR NOT NULL CHECK (role IN ('admin','member')),
|
|
joined_at VARCHAR NOT NULL,
|
|
PRIMARY KEY (family_id, user_id)
|
|
)`)
|
|
await c.run(`CREATE TABLE IF NOT EXISTS categories (
|
|
id VARCHAR PRIMARY KEY,
|
|
family_id VARCHAR NOT NULL,
|
|
name VARCHAR NOT NULL,
|
|
sort INTEGER DEFAULT 0,
|
|
created_at VARCHAR NOT NULL
|
|
)`)
|
|
await c.run(`CREATE TABLE IF NOT EXISTS boxes (
|
|
id VARCHAR PRIMARY KEY,
|
|
family_id VARCHAR NOT NULL,
|
|
parent_box_id VARCHAR,
|
|
name VARCHAR NOT NULL,
|
|
icon VARCHAR DEFAULT '📦',
|
|
color VARCHAR DEFAULT '#E8C4A0',
|
|
pos_x INTEGER DEFAULT 0 CHECK (pos_x BETWEEN 0 AND 1000),
|
|
pos_y INTEGER DEFAULT 0 CHECK (pos_y BETWEEN 0 AND 1000),
|
|
width INTEGER,
|
|
height INTEGER,
|
|
created_at VARCHAR NOT NULL,
|
|
updated_at VARCHAR NOT NULL
|
|
)`)
|
|
await c.run(`CREATE TABLE IF NOT EXISTS items (
|
|
id VARCHAR PRIMARY KEY,
|
|
family_id VARCHAR NOT NULL,
|
|
box_id VARCHAR,
|
|
name VARCHAR NOT NULL,
|
|
category_id VARCHAR,
|
|
tags VARCHAR DEFAULT '[]',
|
|
quantity INTEGER DEFAULT 1,
|
|
brand VARCHAR,
|
|
model VARCHAR,
|
|
serial_no VARCHAR,
|
|
purchase_date VARCHAR,
|
|
purchase_price DOUBLE,
|
|
expiry_date VARCHAR,
|
|
images VARCHAR DEFAULT '[]',
|
|
notes VARCHAR,
|
|
is_favorite BOOLEAN DEFAULT FALSE,
|
|
used_count INTEGER DEFAULT 0,
|
|
last_used VARCHAR,
|
|
created_at VARCHAR NOT NULL,
|
|
updated_at VARCHAR NOT NULL
|
|
)`)
|
|
await c.run(`CREATE TABLE IF NOT EXISTS transfer_logs (
|
|
id VARCHAR PRIMARY KEY,
|
|
entity_type VARCHAR NOT NULL,
|
|
entity_id VARCHAR NOT NULL,
|
|
src_family_id VARCHAR NOT NULL,
|
|
dst_family_id VARCHAR NOT NULL,
|
|
operator_user_id VARCHAR NOT NULL,
|
|
created_at VARCHAR NOT NULL
|
|
)`)
|
|
await c.run(`CREATE INDEX IF NOT EXISTS idx_members_user ON family_members(user_id)`)
|
|
await c.run(`CREATE INDEX IF NOT EXISTS idx_boxes_family ON boxes(family_id)`)
|
|
await c.run(`CREATE INDEX IF NOT EXISTS idx_boxes_parent ON boxes(parent_box_id)`)
|
|
await c.run(`CREATE INDEX IF NOT EXISTS idx_items_family ON items(family_id)`)
|
|
await c.run(`CREATE INDEX IF NOT EXISTS idx_items_box ON items(box_id)`)
|
|
await c.run(`CREATE INDEX IF NOT EXISTS idx_items_family_box ON items(family_id, box_id)`)
|
|
},
|
|
},
|
|
]
|
|
|
|
export async function initDb() {
|
|
const file = dbFile()
|
|
instance = await DuckDBInstance.create(file)
|
|
conn = await instance.connect()
|
|
await conn.run(`CREATE TABLE IF NOT EXISTS schema_version (version INTEGER PRIMARY KEY, applied_at VARCHAR NOT NULL)`)
|
|
const applied = await rows('SELECT version FROM schema_version ORDER BY version')
|
|
const appliedSet = new Set(applied.map((r) => Number(r.version)))
|
|
for (const m of MIGRATIONS) {
|
|
if (appliedSet.has(m.version)) continue
|
|
console.log(`[db] applying migration v${m.version} ...`)
|
|
await conn.run('BEGIN TRANSACTION')
|
|
try {
|
|
await m.up(conn)
|
|
await conn.run('INSERT INTO schema_version (version, applied_at) VALUES (?, ?)', [m.version, nowIso()])
|
|
await conn.run('COMMIT')
|
|
console.log(`[db] migration v${m.version} ok`)
|
|
} catch (e) {
|
|
await conn.run('ROLLBACK')
|
|
throw e
|
|
}
|
|
}
|
|
console.log(`[db] DuckDB ready at ${file} (schema v${applied.length ? Math.max(...applied.map((r) => Number(r.version))) : 0})`)
|
|
}
|
|
|
|
async function tx(fn) {
|
|
await conn.run('BEGIN TRANSACTION')
|
|
try {
|
|
const r = await fn()
|
|
await conn.run('COMMIT')
|
|
return r
|
|
} catch (e) {
|
|
try { await conn.run('ROLLBACK') } catch { /* ignore */ }
|
|
throw e
|
|
}
|
|
}
|
|
|
|
// ══════════════ users / sessions ══════════════
|
|
|
|
export function dbCreateUser(id, email, phone, nickname) {
|
|
return enqueue(async () => {
|
|
await conn.run('INSERT INTO users (id, email, phone, nickname, created_at) VALUES (?,?,?,?,?)', [id, email, phone, nickname, nowIso()])
|
|
return { id, email, phone, nickname, avatar_path: null, created_at: nowIso() }
|
|
})
|
|
}
|
|
|
|
export const dbGetUserByEmail = (email) => enqueue(() =>
|
|
one('SELECT id, email, phone, nickname, avatar_path, created_at FROM users WHERE email = ?', [email]))
|
|
|
|
export const dbGetUserByPhone = (phone) => enqueue(() =>
|
|
one('SELECT id, email, phone, nickname, avatar_path, created_at FROM users WHERE phone = ?', [phone]))
|
|
|
|
export const dbGetUserById = (id) => enqueue(() =>
|
|
one('SELECT id, email, phone, nickname, avatar_path, created_at FROM users WHERE id = ?', [id]))
|
|
|
|
export const dbUpdateUserNickname = (id, nickname) => enqueue(async () => {
|
|
await conn.run('UPDATE users SET nickname = ? WHERE id = ?', [nickname, id])
|
|
})
|
|
|
|
export const dbCreateSession = (token, userId) => enqueue(async () => {
|
|
await conn.run('INSERT INTO sessions (token, user_id, created_at) VALUES (?,?,?)', [token, userId, nowIso()])
|
|
})
|
|
|
|
export const dbGetSession = (token) => enqueue(() =>
|
|
one('SELECT token, user_id, created_at FROM sessions WHERE token = ?', [token]))
|
|
|
|
export const dbDeleteSession = (token) => enqueue(async () => {
|
|
await conn.run('DELETE FROM sessions WHERE token = ?', [token])
|
|
})
|
|
|
|
// ══════════════ families / members ══════════════
|
|
|
|
function genInviteCode() {
|
|
const chars = 'ABCDEFGHJKLMNPQRSTUVWXYZ23456789'
|
|
let s = ''
|
|
for (let i = 0; i < 8; i++) s += chars[Math.floor(Math.random() * chars.length)]
|
|
return s
|
|
}
|
|
|
|
export function dbCreateFamily(ownerId, name, icon) {
|
|
return enqueue(async () => {
|
|
const family = { id: uu(), name: name || '我的家庭', icon: icon || '🏠', owner_user_id: ownerId, invite_code: genInviteCode(), created_at: nowIso(), updated_at: nowIso() }
|
|
await tx(async () => {
|
|
await conn.run('INSERT INTO families (id, name, icon, owner_user_id, invite_code, created_at, updated_at) VALUES (?,?,?,?,?,?,?)',
|
|
[family.id, family.name, family.icon, family.owner_user_id, family.invite_code, family.created_at, family.updated_at])
|
|
await conn.run('INSERT INTO family_members (family_id, user_id, role, joined_at) VALUES (?,?,?,?)',
|
|
[family.id, ownerId, 'admin', nowIso()])
|
|
})
|
|
return family
|
|
})
|
|
}
|
|
|
|
export const dbListFamiliesForUser = (userId) => enqueue(async () => {
|
|
return rows(
|
|
`SELECT f.id, f.name, f.icon, f.owner_user_id, f.invite_code, f.created_at, m.role
|
|
FROM family_members m JOIN families f ON f.id = m.family_id
|
|
WHERE m.user_id = ? ORDER BY f.created_at`,
|
|
[userId],
|
|
)
|
|
})
|
|
|
|
export const dbGetFamily = (familyId) => enqueue(() =>
|
|
one('SELECT id, name, icon, owner_user_id, invite_code, created_at, updated_at FROM families WHERE id = ?', [familyId]))
|
|
|
|
export const dbUpdateFamily = (familyId, data) => enqueue(async () => {
|
|
await conn.run('UPDATE families SET name = ?, icon = ?, updated_at = ? WHERE id = ?',
|
|
[data.name, data.icon || '🏠', nowIso(), familyId])
|
|
})
|
|
|
|
export const dbDeleteFamily = (familyId) => enqueue(async () => {
|
|
await tx(async () => {
|
|
await conn.run('DELETE FROM family_members WHERE family_id = ?', [familyId])
|
|
await conn.run('DELETE FROM categories WHERE family_id = ?', [familyId])
|
|
const boxIds = (await rows('SELECT id FROM boxes WHERE family_id = ?', [familyId])).map((r) => r.id)
|
|
if (boxIds.length) {
|
|
const placeholders = boxIds.map(() => '?').join(',')
|
|
await conn.run(`DELETE FROM items WHERE family_id = ? OR box_id IN (${placeholders})`, [familyId, ...boxIds])
|
|
await conn.run(`DELETE FROM boxes WHERE family_id = ?`, [familyId])
|
|
} else {
|
|
await conn.run('DELETE FROM items WHERE family_id = ?', [familyId])
|
|
}
|
|
await conn.run('DELETE FROM transfer_logs WHERE src_family_id = ? OR dst_family_id = ?', [familyId, familyId])
|
|
await conn.run('DELETE FROM families WHERE id = ?', [familyId])
|
|
})
|
|
})
|
|
|
|
export const dbGetRole = (familyId, userId) => enqueue(async () => {
|
|
const r = await one('SELECT role FROM family_members WHERE family_id = ? AND user_id = ?', [familyId, userId])
|
|
return r ? r.role : null
|
|
})
|
|
|
|
export const dbAddMember = (familyId, userId, role) => enqueue(async () => {
|
|
await conn.run('INSERT INTO family_members (family_id, user_id, role, joined_at) VALUES (?,?,?,?)', [familyId, userId, role || 'member', nowIso()])
|
|
})
|
|
|
|
export const dbRemoveMember = (familyId, userId) => enqueue(async () => {
|
|
await conn.run('DELETE FROM family_members WHERE family_id = ? AND user_id = ?', [familyId, userId])
|
|
})
|
|
|
|
export const dbSetRole = (familyId, userId, role) => enqueue(async () => {
|
|
await conn.run('UPDATE family_members SET role = ? WHERE family_id = ? AND user_id = ?', [role, familyId, userId])
|
|
})
|
|
|
|
export const dbSetInviteCode = (familyId, code) => enqueue(async () => {
|
|
await conn.run('UPDATE families SET invite_code = ?, updated_at = ? WHERE id = ?', [code, nowIso(), familyId])
|
|
})
|
|
|
|
export const dbJoinByCode = (code, userId) => enqueue(async () => {
|
|
const family = await one('SELECT id FROM families WHERE invite_code = ?', [code])
|
|
if (!family) return { success: false, message: '邀请码无效' }
|
|
const existing = await one('SELECT role FROM family_members WHERE family_id = ? AND user_id = ?', [family.id, userId])
|
|
if (existing) return { success: false, message: '你已在该家庭中' }
|
|
await conn.run('INSERT INTO family_members (family_id, user_id, role, joined_at) VALUES (?,?,?,?)', [family.id, userId, 'member', nowIso()])
|
|
return { success: true, message: '加入成功', familyId: family.id }
|
|
})
|
|
|
|
export const dbListMembers = (familyId) => enqueue(async () => {
|
|
return rows(
|
|
`SELECT m.family_id, m.user_id, m.role, m.joined_at, u.nickname, u.email, u.phone
|
|
FROM family_members m JOIN users u ON u.id = m.user_id
|
|
WHERE m.family_id = ? ORDER BY m.joined_at`,
|
|
[familyId],
|
|
)
|
|
})
|
|
|
|
// ══════════════ categories ══════════════
|
|
|
|
export const dbListCategories = (familyId) => enqueue(async () => {
|
|
return rows('SELECT id, family_id, name, sort, created_at FROM categories WHERE family_id = ? ORDER BY sort, created_at', [familyId])
|
|
})
|
|
|
|
export const dbCreateCategory = (familyId, name) => enqueue(async () => {
|
|
const cat = { id: uu(), family_id: familyId, name, sort: 0, created_at: nowIso() }
|
|
await conn.run('INSERT INTO categories (id, family_id, name, sort, created_at) VALUES (?,?,?,?,?)',
|
|
[cat.id, cat.family_id, cat.name, cat.sort, cat.created_at])
|
|
return cat
|
|
})
|
|
|
|
export const dbUpdateCategory = (familyId, catId, name) => enqueue(async () => {
|
|
await conn.run('UPDATE categories SET name = ? WHERE id = ? AND family_id = ?', [name, catId, familyId])
|
|
})
|
|
|
|
export const dbDeleteCategory = (familyId, catId) => enqueue(async () => {
|
|
await tx(async () => {
|
|
await conn.run('UPDATE items SET category_id = NULL WHERE category_id = ? AND family_id = ?', [catId, familyId])
|
|
await conn.run('DELETE FROM categories WHERE id = ? AND family_id = ?', [catId, familyId])
|
|
})
|
|
})
|
|
|
|
// ══════════════ 盒子树内部工具 (非 enqueue) ══════════════
|
|
|
|
async function allBoxes(familyId) {
|
|
const r = await rows(
|
|
`SELECT b.*, (
|
|
SELECT COUNT(*) FROM items i WHERE i.box_id = b.id
|
|
) AS direct_item_count
|
|
FROM boxes b WHERE b.family_id = ?`,
|
|
[familyId],
|
|
)
|
|
return r.map(mapBox)
|
|
}
|
|
|
|
function buildPath(boxes, boxId) {
|
|
const byId = new Map(boxes.map((b) => [b.id, b]))
|
|
const path = []
|
|
let cur = byId.get(boxId)
|
|
const guard = new Set()
|
|
while (cur && !guard.has(cur.id)) {
|
|
guard.add(cur.id)
|
|
path.unshift({ id: cur.id, name: cur.name })
|
|
cur = cur.parent_box_id ? byId.get(cur.parent_box_id) : null
|
|
}
|
|
return path
|
|
}
|
|
|
|
function directChildren(boxes, parentId) {
|
|
return boxes.filter((b) => (b.parent_box_id ?? null) === (parentId ?? null)).sort((a, b) => a.pos_y - b.pos_y || a.pos_x - b.pos_x)
|
|
}
|
|
|
|
async function isDescendant(familyId, boxId, targetId) {
|
|
if (boxId === targetId) return true
|
|
const all = await rows('SELECT id, parent_box_id FROM boxes WHERE family_id = ?', [familyId])
|
|
const parentOf = new Map(all.map((b) => [b.id, b.parent_box_id]))
|
|
let cur = parentOf.get(targetId)
|
|
while (cur) {
|
|
if (cur === boxId) return true
|
|
cur = parentOf.get(cur)
|
|
}
|
|
return false
|
|
}
|
|
|
|
async function subtreeIds(familyId, boxId) {
|
|
const all = await rows('SELECT id, parent_box_id FROM boxes WHERE family_id = ?', [familyId])
|
|
const childrenOf = new Map()
|
|
for (const b of all) {
|
|
if (!childrenOf.has(b.parent_box_id)) childrenOf.set(b.parent_box_id, [])
|
|
childrenOf.get(b.parent_box_id).push(b.id)
|
|
}
|
|
const out = []
|
|
const stack = [boxId]
|
|
while (stack.length) {
|
|
const cur = stack.pop()
|
|
out.push(cur)
|
|
for (const c of childrenOf.get(cur) || []) stack.push(c)
|
|
}
|
|
return out
|
|
}
|
|
|
|
function subtreeItemCount(boxes, boxId) {
|
|
const childrenOf = new Map()
|
|
for (const b of boxes) {
|
|
const p = b.parent_box_id ?? 'ROOT'
|
|
if (!childrenOf.has(p)) childrenOf.set(p, [])
|
|
childrenOf.get(p).push(b)
|
|
}
|
|
let total = 0
|
|
const stack = [boxId]
|
|
const seen = new Set()
|
|
while (stack.length) {
|
|
const cur = stack.pop()
|
|
if (seen.has(cur)) continue
|
|
seen.add(cur)
|
|
const b = boxes.find((x) => x.id === cur)
|
|
if (b) total += b.direct_item_count || 0
|
|
for (const c of childrenOf.get(cur) || []) stack.push(c.id)
|
|
}
|
|
return total
|
|
}
|
|
|
|
function childBoxes(boxes) {
|
|
const childCounts = new Map()
|
|
for (const b of boxes) {
|
|
const key = b.parent_box_id ?? 'ROOT'
|
|
if (!childCounts.has(key)) childCounts.set(key, [])
|
|
childCounts.get(key).push(b)
|
|
}
|
|
return childCounts
|
|
}
|
|
|
|
async function buildTree(familyId) {
|
|
const boxes = await allBoxes(familyId)
|
|
const childrenOf = childBoxes(boxes)
|
|
return boxes.map((box) => {
|
|
const path = buildPath(boxes, box.id)
|
|
return {
|
|
...box,
|
|
depth: path.length,
|
|
path: path.map((p) => ({ id: p.id, name: p.name })),
|
|
total_item_count: subtreeItemCount(boxes, box.id),
|
|
}
|
|
})
|
|
}
|
|
|
|
// ══════════════ boxes (公共 enqueue API) ══════════════
|
|
|
|
export const dbAllBoxes = (familyId) => enqueue(() => allBoxes(familyId))
|
|
|
|
export const dbGetBox = (familyId, boxId) => enqueue(async () => {
|
|
const b = await one('SELECT * FROM boxes WHERE id = ? AND family_id = ?', [boxId, familyId])
|
|
return mapBox(b)
|
|
})
|
|
|
|
export const dbBoxPath = (familyId, boxId) => enqueue(async () => {
|
|
const boxes = await allBoxes(familyId)
|
|
return buildPath(boxes, boxId)
|
|
})
|
|
|
|
const clampPos = (v) => {
|
|
const n = Number(v)
|
|
if (!Number.isFinite(n)) return 0
|
|
return Math.max(0, Math.min(1000, Math.round(n)))
|
|
}
|
|
|
|
export function dbCreateBox(familyId, data) {
|
|
return enqueue(async () => {
|
|
const t = nowIso()
|
|
const box = {
|
|
id: uu(),
|
|
family_id: familyId,
|
|
parent_box_id: data.parent_box_id ?? null,
|
|
name: data.name,
|
|
icon: data.icon || '📦',
|
|
color: data.color || '#E8C4A0',
|
|
pos_x: clampPos(data.pos_x),
|
|
pos_y: clampPos(data.pos_y),
|
|
width: data.width ?? null,
|
|
height: data.height ?? null,
|
|
created_at: t,
|
|
updated_at: t,
|
|
}
|
|
if (box.parent_box_id) {
|
|
const parent = await one('SELECT id, family_id FROM boxes WHERE id = ?', [box.parent_box_id])
|
|
if (!parent || parent.family_id !== familyId) throw { status: 400, message: '父盒子不存在' }
|
|
}
|
|
await conn.run(
|
|
'INSERT INTO boxes (id, family_id, parent_box_id, name, icon, color, pos_x, pos_y, width, height, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)',
|
|
[box.id, box.family_id, box.parent_box_id, box.name, box.icon, box.color, box.pos_x, box.pos_y, box.width, box.height, box.created_at, box.updated_at],
|
|
)
|
|
return mapBox({ ...box, direct_item_count: 0 })
|
|
})
|
|
}
|
|
|
|
export function dbUpdateBox(familyId, boxId, data) {
|
|
return enqueue(async () => {
|
|
const sets = []
|
|
const params = []
|
|
for (const f of ['name', 'icon', 'color']) {
|
|
if (data[f] !== undefined) { sets.push(`${f} = ?`); params.push(data[f]) }
|
|
}
|
|
if (data.width !== undefined) { sets.push('width = ?'); params.push(data.width) }
|
|
if (data.height !== undefined) { sets.push('height = ?'); params.push(data.height) }
|
|
sets.push('updated_at = ?'); params.push(nowIso())
|
|
params.push(boxId, familyId)
|
|
await conn.run(`UPDATE boxes SET ${sets.join(', ')} WHERE id = ? AND family_id = ?`, params)
|
|
})
|
|
}
|
|
|
|
export function dbMoveBox(familyId, boxId, parentBoxId, posX, posY) {
|
|
return enqueue(async () => {
|
|
const box = await one('SELECT id FROM boxes WHERE id = ? AND family_id = ?', [boxId, familyId])
|
|
if (!box) throw { status: 404, message: '盒子不存在' }
|
|
if (parentBoxId) {
|
|
const parent = await one('SELECT id, family_id FROM boxes WHERE id = ?', [parentBoxId])
|
|
if (!parent || parent.family_id !== familyId) throw { status: 400, message: '目标父盒子不存在' }
|
|
if (await isDescendant(familyId, boxId, parentBoxId)) throw { status: 400, message: '不能移动到自身或其子盒中' }
|
|
}
|
|
await conn.run('UPDATE boxes SET parent_box_id = ?, pos_x = ?, pos_y = ?, updated_at = ? WHERE id = ?',
|
|
[parentBoxId ?? null, clampPos(posX), clampPos(posY), nowIso(), boxId])
|
|
})
|
|
}
|
|
|
|
export function dbSetBoxPosition(familyId, boxId, posX, posY) {
|
|
return enqueue(async () => {
|
|
await conn.run('UPDATE boxes SET pos_x = ?, pos_y = ?, updated_at = ? WHERE id = ? AND family_id = ?',
|
|
[clampPos(posX), clampPos(posY), nowIso(), boxId, familyId])
|
|
})
|
|
}
|
|
|
|
export function dbDeleteBox(familyId, boxId) {
|
|
return enqueue(async () => {
|
|
const ids = await subtreeIds(familyId, boxId)
|
|
await tx(async () => {
|
|
const ph = ids.map(() => '?').join(',')
|
|
await conn.run(`DELETE FROM items WHERE family_id = ? AND box_id IN (${ph})`, [familyId, ...ids])
|
|
await conn.run(`DELETE FROM boxes WHERE id IN (${ph})`, ids)
|
|
})
|
|
})
|
|
}
|
|
|
|
export function dbListChildBoxes(familyId, parentBoxId) {
|
|
return enqueue(async () => {
|
|
const boxes = await allBoxes(familyId)
|
|
return directChildren(boxes, parentBoxId).map((box) => ({
|
|
...box,
|
|
total_item_count: subtreeItemCount(boxes, box.id),
|
|
}))
|
|
})
|
|
}
|
|
|
|
export const dbBuildTree = (familyId) => enqueue(() => buildTree(familyId))
|
|
|
|
export const dbSubtreeIds = (familyId, boxId) => enqueue(() => subtreeIds(familyId, boxId))
|
|
|
|
// ══════════════ items ══════════════
|
|
|
|
export function dbCreateItem(familyId, data) {
|
|
return enqueue(async () => {
|
|
const t = nowIso()
|
|
const item = {
|
|
id: uu(),
|
|
family_id: familyId,
|
|
box_id: data.box_id ?? null,
|
|
name: data.name,
|
|
category_id: data.category_id ?? null,
|
|
tags: data.tags ?? [],
|
|
quantity: data.quantity ?? 1,
|
|
brand: data.brand ?? null,
|
|
model: data.model ?? null,
|
|
serial_no: data.serial_no ?? null,
|
|
purchase_date: data.purchase_date ?? null,
|
|
purchase_price: data.purchase_price === '' ? null : (data.purchase_price ?? null),
|
|
expiry_date: data.expiry_date ?? null,
|
|
images: data.images ?? [],
|
|
notes: data.notes ?? null,
|
|
is_favorite: !!data.is_favorite,
|
|
used_count: 0,
|
|
last_used: null,
|
|
created_at: t,
|
|
updated_at: t,
|
|
}
|
|
if (item.box_id && (await one('SELECT id FROM boxes WHERE id = ? AND family_id = ?', [item.box_id, familyId])) === null) throw { status: 400, message: '盒子不存在' }
|
|
await conn.run(
|
|
`INSERT INTO items (id, family_id, box_id, name, category_id, tags, quantity, brand, model, serial_no, purchase_date, purchase_price, expiry_date, images, notes, is_favorite, used_count, last_used, created_at, updated_at)
|
|
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
|
|
[
|
|
item.id, item.family_id, item.box_id, item.name, item.category_id,
|
|
JSON.stringify(item.tags), item.quantity, item.brand, item.model, item.serial_no,
|
|
item.purchase_date, item.purchase_price, item.expiry_date,
|
|
JSON.stringify(item.images), item.notes, item.is_favorite,
|
|
item.used_count, item.last_used, item.created_at, item.updated_at,
|
|
],
|
|
)
|
|
return mapItem(item)
|
|
})
|
|
}
|
|
|
|
export const dbGetItem = (familyId, itemId) => enqueue(async () => {
|
|
const it = await one('SELECT * FROM items WHERE id = ? AND family_id = ?', [itemId, familyId])
|
|
return mapItem(it)
|
|
})
|
|
|
|
// 内部 listItems: 建议在 enqueue 内使用 (不嵌套 enqueue)
|
|
async function listItems(familyId, filters = {}) {
|
|
let sql = 'SELECT * FROM items WHERE family_id = ?'
|
|
const params = [familyId]
|
|
if (filters.boxId) {
|
|
if (filters.recursive) {
|
|
const ids = await subtreeIds(familyId, filters.boxId)
|
|
if (!ids.length) return []
|
|
sql += ` AND box_id IN (${ids.map(() => '?').join(',')})`
|
|
params.push(...ids)
|
|
} else {
|
|
sql += ' AND box_id = ?'
|
|
params.push(filters.boxId)
|
|
}
|
|
} else if (filters.looseOnly) {
|
|
sql += ' AND box_id IS NULL'
|
|
}
|
|
if (filters.categoryId) { sql += ' AND category_id = ?'; params.push(filters.categoryId) }
|
|
if (filters.tag) {
|
|
// tags 存为 JSON 数组字符串: 去掉 [ ] " 后按逗号拆为列表再判断包含
|
|
sql += " AND list_contains(string_split(regexp_replace(tags, '[\\[\\]\" ]', '', 'g'), ','), ?)"
|
|
params.push(filters.tag)
|
|
}
|
|
if (filters.q) {
|
|
sql += ' AND (name ILIKE ? OR COALESCE(brand, \'\') ILIKE ? OR COALESCE(model, \'\') ILIKE ? OR COALESCE(notes, \'\') ILIKE ?)'
|
|
const like = `%${filters.q}%`
|
|
params.push(like, like, like, like)
|
|
}
|
|
if (filters.favorite) sql += ' AND is_favorite = TRUE'
|
|
if (filters.expiring) {
|
|
sql += " AND expiry_date IS NOT NULL AND CAST(expiry_date AS DATE) >= CURRENT_DATE AND CAST(expiry_date AS DATE) < CURRENT_DATE + INTERVAL '30 days'"
|
|
}
|
|
sql += ' ORDER BY (is_favorite = TRUE) DESC, updated_at DESC'
|
|
const limit = Math.min(Number(filters.limit) || 200, 1000)
|
|
sql += ` LIMIT ${limit}`
|
|
const r = await rows(sql, params)
|
|
return r.map(mapItem)
|
|
}
|
|
|
|
export const dbListItems = (familyId, filters = {}) => enqueue(() => listItems(familyId, filters))
|
|
|
|
const ITEM_SET_FIELDS = ['name', 'category_id', 'quantity', 'brand', 'model', 'serial_no', 'purchase_date', 'purchase_price', 'expiry_date', 'notes', 'is_favorite']
|
|
|
|
export function dbUpdateItem(familyId, itemId, data) {
|
|
return enqueue(async () => {
|
|
const item = await one('SELECT id FROM items WHERE id = ? AND family_id = ?', [itemId, familyId])
|
|
if (!item) throw { status: 404, message: '物品不存在' }
|
|
const sets = []
|
|
const params = []
|
|
for (const f of ITEM_SET_FIELDS) {
|
|
if (data[f] === undefined) continue
|
|
if (f === 'quantity') { sets.push('quantity = ?'); params.push(Number(data[f]) || 1) }
|
|
else if (f === 'is_favorite') { sets.push('is_favorite = ?'); params.push(!!data[f]) }
|
|
else if (f === 'category_id' || f === 'brand' || f === 'model' || f === 'serial_no' || f === 'purchase_date' || f === 'purchase_price' || f === 'expiry_date' || f === 'notes') {
|
|
sets.push(`${f} = ?`); params.push(data[f] === '' ? null : data[f])
|
|
}
|
|
else { sets.push(`${f} = ?`); params.push(data[f]) }
|
|
}
|
|
if (data.box_id !== undefined) { sets.push('box_id = ?'); params.push(data.box_id || null) }
|
|
if (data.tags !== undefined) { sets.push('tags = ?'); params.push(JSON.stringify(data.tags ?? [])) }
|
|
if (data.images !== undefined) { sets.push('images = ?'); params.push(JSON.stringify(data.images ?? [])) }
|
|
if (!sets.length) { sets.push('name = name') }
|
|
sets.push('updated_at = ?'); params.push(nowIso())
|
|
params.push(itemId, familyId)
|
|
await conn.run(`UPDATE items SET ${sets.join(', ')} WHERE id = ? AND family_id = ?`, params)
|
|
})
|
|
}
|
|
|
|
export function dbDeleteItem(familyId, itemId) {
|
|
return enqueue(async () => {
|
|
await conn.run('DELETE FROM items WHERE id = ? AND family_id = ?', [itemId, familyId])
|
|
})
|
|
}
|
|
|
|
export function dbMoveItem(familyId, itemId, boxId) {
|
|
return enqueue(async () => {
|
|
const item = await one('SELECT id FROM items WHERE id = ? AND family_id = ?', [itemId, familyId])
|
|
if (!item) throw { status: 404, message: '物品不存在' }
|
|
if (boxId) {
|
|
const box = await one('SELECT id FROM boxes WHERE id = ? AND family_id = ?', [boxId, familyId])
|
|
if (!box) throw { status: 400, message: '目标盒子不存在' }
|
|
}
|
|
await conn.run('UPDATE items SET box_id = ?, updated_at = ? WHERE id = ?', [boxId ?? null, nowIso(), itemId])
|
|
})
|
|
}
|
|
|
|
// ══════════════ transfer (跨家庭转移) ══════════════
|
|
|
|
export function dbTransfer(familyId, data) {
|
|
return enqueue(async () => {
|
|
const { type, entityId, dstFamilyId, dstParentBoxId, posX, posY, operatorUserId } = data
|
|
if (!['item', 'box'].includes(type)) throw { status: 400, message: 'type 非法' }
|
|
if (familyId === dstFamilyId) throw { status: 400, message: '目标家庭不能是源家庭' }
|
|
|
|
return tx(async () => {
|
|
if (type === 'item') {
|
|
const item = await one('SELECT * FROM items WHERE id = ? AND family_id = ?', [entityId, familyId])
|
|
if (!item) throw { status: 404, message: '物品不存在' }
|
|
if (dstParentBoxId && (await one('SELECT id FROM boxes WHERE id = ? AND family_id = ?', [dstParentBoxId, dstFamilyId])) === null) throw { status: 400, message: '目标盒子不存在于目标家庭' }
|
|
await conn.run('UPDATE items SET family_id = ?, box_id = ?, updated_at = ? WHERE id = ?', [dstFamilyId, dstParentBoxId ?? null, nowIso(), entityId])
|
|
await logTransfer('item', entityId, familyId, dstFamilyId, operatorUserId)
|
|
return { type, entityId, dstFamilyId, ok: true }
|
|
}
|
|
|
|
const roots = await rows('SELECT id, parent_box_id, pos_x, pos_y FROM boxes WHERE id = ? AND family_id = ?', [entityId, familyId])
|
|
if (!roots.length) throw { status: 404, message: '盒子不存在' }
|
|
if (dstParentBoxId && (await one('SELECT id FROM boxes WHERE id = ? AND family_id = ?', [dstParentBoxId, dstFamilyId])) === null) throw { status: 400, message: '目标盒子不存在于目标家庭' }
|
|
const ids = await subtreeIds(familyId, entityId)
|
|
const ph = ids.map(() => '?').join(',')
|
|
await conn.run(`UPDATE boxes SET family_id = ? WHERE id IN (${ph})`, [dstFamilyId, ...ids])
|
|
await conn.run('UPDATE boxes SET parent_box_id = ?, pos_x = ?, pos_y = ?, updated_at = ? WHERE id = ?',
|
|
[dstParentBoxId ?? null, clampPos(posX), clampPos(posY), nowIso(), entityId])
|
|
await conn.run(`UPDATE items SET family_id = ? WHERE family_id = ? AND box_id IN (${ph})`, [dstFamilyId, familyId, ...ids])
|
|
await logTransfer('box', entityId, familyId, dstFamilyId, operatorUserId)
|
|
return { type, entityId, dstFamilyId, ok: true }
|
|
})
|
|
})
|
|
}
|
|
|
|
async function logTransfer(type, entityId, srcFamilyId, dstFamilyId, operatorUserId) {
|
|
await conn.run('INSERT INTO transfer_logs (id, entity_type, entity_id, src_family_id, dst_family_id, operator_user_id, created_at) VALUES (?,?,?,?,?,?,?)',
|
|
[uu(), type, entityId, srcFamilyId, dstFamilyId, operatorUserId, nowIso()])
|
|
}
|
|
|
|
export const dbListTransferLogs = (familyId) => enqueue(async () => {
|
|
return rows('SELECT * FROM transfer_logs WHERE src_family_id = ? OR dst_family_id = ? ORDER BY created_at DESC LIMIT 100', [familyId, familyId])
|
|
})
|
|
|
|
// ══════════════ stats ══════════════
|
|
|
|
export function dbGetFamilyStats(familyId) {
|
|
return enqueue(async () => {
|
|
const [totalItems, totalBoxes] = await Promise.all([
|
|
count('SELECT COUNT(*) AS c FROM items WHERE family_id = ?', [familyId]),
|
|
count('SELECT COUNT(*) AS c FROM boxes WHERE family_id = ?', [familyId]),
|
|
])
|
|
const boxes = await allBoxes(familyId)
|
|
const rootBoxes = directChildren(boxes, null)
|
|
const valueRow = await one('SELECT COALESCE(SUM(purchase_price), 0) AS v FROM items WHERE family_id = ? AND purchase_price IS NOT NULL', [familyId])
|
|
const looseCount = await count('SELECT COUNT(*) AS c FROM items WHERE family_id = ? AND box_id IS NULL', [familyId])
|
|
const noImageCount = await count("SELECT COUNT(*) AS c FROM items WHERE family_id = ? AND images = '[]'", [familyId])
|
|
const maxDepth = boxes.reduce((m, b) => Math.max(m, buildPath(boxes, b.id).length), 0)
|
|
const topBoxes = boxes
|
|
.map((b) => ({ id: b.id, name: b.name, icon: b.icon, count: subtreeItemCount(boxes, b.id), path: buildPath(boxes, b.id) }))
|
|
.filter((b) => b.count > 0)
|
|
.sort((a, b) => b.count - a.count)
|
|
.slice(0, 5)
|
|
const catRows = await rows(
|
|
`SELECT COALESCE(c.name, '未分类') AS name, COUNT(i.id) AS c
|
|
FROM items i LEFT JOIN categories c ON c.id = i.category_id
|
|
WHERE i.family_id = ? GROUP BY COALESCE(c.name, '未分类') ORDER BY c DESC`,
|
|
[familyId],
|
|
)
|
|
const expiring = (await listItems(familyId, { expiring: true, limit: 50 }))
|
|
const recent = (await listItems(familyId, { limit: 8 }))
|
|
return {
|
|
total_items: totalItems,
|
|
total_boxes: totalBoxes,
|
|
root_boxes: rootBoxes.length,
|
|
max_depth: maxDepth,
|
|
total_value: Number(valueRow?.v || 0),
|
|
expiring_count: expiring.length,
|
|
expiring_items: expiring,
|
|
loose_items: looseCount,
|
|
no_image_count: noImageCount,
|
|
category_distribution: catRows.map((r) => ({ name: r.name, count: Number(r.c) })),
|
|
top_boxes: topBoxes,
|
|
recent_items: recent,
|
|
}
|
|
})
|
|
}
|
|
|
|
// ══════════════ MCP (P1: AI 助手查询) ══════════════
|
|
|
|
export function mcpTools() {
|
|
return [
|
|
{ name: 'list_my_families', description: '列出我加入的所有家庭', input_schema: '{"type":"object","properties":{},"required":[]}' },
|
|
{ name: 'search_items', description: '按关键字或标签搜索物品(需 family_id), 返回物品及所在位置', input_schema: '{"type":"object","properties":{"family_id":{"type":"string"},"q":{"type":"string"},"tag":{"type":"string"}},"required":["family_id"]}' },
|
|
{ name: 'get_item', description: '根据 item_id 和 family_id 获取物品详情与所在路径', input_schema: '{"type":"object","properties":{"item_id":{"type":"string"},"family_id":{"type":"string"}},"required":["item_id","family_id"]}' },
|
|
{ name: 'get_family_stats', description: '获取家庭物品统计概览', input_schema: '{"type":"object","properties":{"family_id":{"type":"string"}},"required":["family_id"]}' },
|
|
]
|
|
}
|
|
|
|
export async function mcpCall(toolName, args = {}, userId) {
|
|
switch (toolName) {
|
|
case 'list_my_families':
|
|
return { ok: true, value: await dbListFamiliesForUser(userId) }
|
|
case 'search_items': {
|
|
const { family_id, q, tag } = args
|
|
const role = await dbGetRole(family_id, userId)
|
|
if (!role) throw { status: 403, message: '非该家庭成员' }
|
|
return { ok: true, value: await dbListItems(family_id, { q, tag, limit: 100 }) }
|
|
}
|
|
case 'get_item': {
|
|
const { item_id, family_id } = args
|
|
const role = await dbGetRole(family_id, userId)
|
|
if (!role) throw { status: 403, message: '非该家庭成员' }
|
|
const item = await dbGetItem(family_id, item_id)
|
|
if (!item) throw { status: 404, message: '物品不存在' }
|
|
const boxes = await allBoxes(family_id)
|
|
return { ok: true, value: { ...item, box_path: item.box_id ? buildPath(boxes, item.box_id) : [] } }
|
|
}
|
|
case 'get_family_stats': {
|
|
const { family_id } = args
|
|
const role = await dbGetRole(family_id, userId)
|
|
if (!role) throw { status: 403, message: '非该家庭成员' }
|
|
return { ok: true, value: await dbGetFamilyStats(family_id) }
|
|
}
|
|
default:
|
|
throw { status: 400, message: `Unknown tool: ${toolName}` }
|
|
}
|
|
} |