lan-reader-chat/lib/chat-store.ts

import Database from "better-sqlite3";
import { existsSync, mkdirSync, rmSync } from "node:fs";
import { readFile } from "node:fs/promises";
import path from "node:path";

export type ChatAttachment = {
  name: string;
  meta: string;
  kind: "image" | "file";
  url?: string;
  storageKey?: string;
};

export type ChatMessage = {
  id: string;
  user: string;
  time: string;
  content: string;
  file?: ChatAttachment;
};

type MessageRow = {
  id: string;
  user: string;
  time: string;
  content: string;
  file_name: string | null;
  file_meta: string | null;
  file_kind: "image" | "file" | null;
  file_storage_key: string | null;
  created_at: number;
};

const storageRoot = path.join(process.cwd(), "storage");
const uploadRoot = path.join(storageRoot, "chat-uploads");
const dbPath = path.join(storageRoot, "chat.sqlite");

function ensureStorage() {
  if (!existsSync(storageRoot)) {
    mkdirSync(storageRoot, { recursive: true });
  }

  if (!existsSync(uploadRoot)) {
    mkdirSync(uploadRoot, { recursive: true });
  }
}

ensureStorage();

const db = new Database(dbPath);
db.pragma("journal_mode = WAL");

db.exec(`
  CREATE TABLE IF NOT EXISTS chat_messages (
    id TEXT PRIMARY KEY,
    user TEXT NOT NULL,
    time TEXT NOT NULL,
    content TEXT NOT NULL DEFAULT '',
    file_name TEXT,
    file_meta TEXT,
    file_kind TEXT,
    file_storage_key TEXT,
    created_at INTEGER NOT NULL
  );

  CREATE TABLE IF NOT EXISTS chat_presence (
    user TEXT PRIMARY KEY,
    last_seen INTEGER NOT NULL
  );
`);

const selectMessagesStmt = db.prepare(`
  SELECT id, user, time, content, file_name, file_meta, file_kind, file_storage_key, created_at
  FROM chat_messages
  ORDER BY created_at ASC
`);

const insertMessageStmt = db.prepare(`
  INSERT INTO chat_messages (
    id,
    user,
    time,
    content,
    file_name,
    file_meta,
    file_kind,
    file_storage_key,
    created_at
  ) VALUES (
    @id,
    @user,
    @time,
    @content,
    @file_name,
    @file_meta,
    @file_kind,
    @file_storage_key,
    @created_at
  )
`);

const trimMessagesStmt = db.prepare(`
  DELETE FROM chat_messages
  WHERE id IN (
    SELECT id
    FROM chat_messages
    ORDER BY created_at DESC
    LIMIT -1 OFFSET 500
  )
`);

const touchPresenceStmt = db.prepare(`
  INSERT INTO chat_presence (user, last_seen)
  VALUES (?, ?)
  ON CONFLICT(user) DO UPDATE SET last_seen = excluded.last_seen
`);

const deleteExpiredPresenceStmt = db.prepare(`
  DELETE FROM chat_presence
  WHERE last_seen < ?
`);

const listPresenceStmt = db.prepare(`
  SELECT user
  FROM chat_presence
  ORDER BY last_seen DESC
`);

function formatCurrentTime() {
  return new Intl.DateTimeFormat("zh-CN", {
    hour: "2-digit",
    minute: "2-digit",
    hour12: false
  }).format(new Date());
}

function toFileUrl(storageKey: string) {
  return `/api/chat/files/${encodeURIComponent(storageKey)}`;
}

function mapMessage(row: MessageRow): ChatMessage {
  return {
    id: row.id,
    user: row.user,
    time: row.time,
    content: row.content,
    file: row.file_name && row.file_meta && row.file_kind && row.file_storage_key
      ? {
          name: row.file_name,
          meta: row.file_meta,
          kind: row.file_kind,
          storageKey: row.file_storage_key,
          url: toFileUrl(row.file_storage_key)
        }
      : undefined
  };
}

export function formatUploadedFileMeta(fileName: string, fileType: string, fileSize: number) {
  const sizeMb = fileSize / (1024 * 1024);
  const size = sizeMb >= 1 ? `${sizeMb.toFixed(1)} MB` : `${Math.max(1, Math.round(fileSize / 1024))} KB`;
  return `${size} · ${fileType || "application/octet-stream"}`;
}

export function touchPresence(user?: string) {
  const name = user?.trim();

  if (!name) {
    return;
  }

  touchPresenceStmt.run(name, Date.now());
}

export function getOnlineUsers() {
  deleteExpiredPresenceStmt.run(Date.now() - 45_000);
  return (listPresenceStmt.all() as Array<{ user: string }>).map((row) => row.user);
}

export function listMessages() {
  return (selectMessagesStmt.all() as MessageRow[]).map(mapMessage);
}

export function addMessage(input: {
  user: string;
  content?: string;
  file?: {
    name: string;
    meta: string;
    kind: "image" | "file";
    storageKey: string;
  };
}) {
  const record = {
    id: crypto.randomUUID(),
    user: input.user.trim() || "局域网设备",
    time: formatCurrentTime(),
    content: input.content?.trim() || "",
    file_name: input.file?.name || null,
    file_meta: input.file?.meta || null,
    file_kind: input.file?.kind || null,
    file_storage_key: input.file?.storageKey || null,
    created_at: Date.now()
  };

  insertMessageStmt.run(record);
  trimMessagesStmt.run();

  return mapMessage(record);
}

export async function clearMessages(clearedBy: string) {
  const rows = selectMessagesStmt.all() as MessageRow[];
  const storageKeys = rows
    .map((row) => row.file_storage_key)
    .filter((value): value is string => Boolean(value));

  for (const storageKey of storageKeys) {
    try {
      rmSync(path.join(uploadRoot, storageKey), { force: true });
    } catch {
      // ignore missing files during cleanup
    }
  }

  db.prepare("DELETE FROM chat_messages").run();

  const systemMessage = addMessage({
    user: "系统消息",
    content: `${clearedBy} 清理了服务器历史聊天记录。`
  });

  return systemMessage;
}

export function resolveUploadPath(storageKey: string) {
  return path.join(uploadRoot, storageKey);
}

export async function readUploadBuffer(storageKey: string) {
  return readFile(resolveUploadPath(storageKey));
}

export function createStorageKey(originalName: string) {
  const extension = path.extname(originalName);
  return `${Date.now()}-${crypto.randomUUID()}${extension}`;
}