backend/services/MapChatService.js - master - Krams/nodemon2

backend/services/MapChatService.js
253 строки9 KB
Иван Новиков
init
11 июн 2026, 13:08
11 июн 2026, 13:08900f716
import db from '../database.js';

db.run(`CREATE TABLE IF NOT EXISTS map_chat_message (
  id TEXT PRIMARY KEY,
  ts INTEGER NOT NULL,
  user_id TEXT,
  name TEXT NOT NULL,
  text TEXT NOT NULL,
  reply_to_id TEXT,
  reply_to_name TEXT,
  reply_to_text TEXT,
  reply_to_ts INTEGER
)`);

db.run('CREATE INDEX IF NOT EXISTS idx_map_chat_message_ts ON map_chat_message(ts DESC)');

function ensureColumn(table, column, ddl) {
  db.hasColumn(table, column)
    .then((hasColumn) => {
      if (hasColumn) return;
      db.run(ddl);
    })
    .catch(() => {});
}

ensureColumn('map_chat_message', 'edited_at', 'ALTER TABLE map_chat_message ADD COLUMN edited_at INTEGER');
ensureColumn('map_chat_message', 'deleted_at', 'ALTER TABLE map_chat_message ADD COLUMN deleted_at INTEGER');
ensureColumn('map_chat_message', 'system_ref_key', 'ALTER TABLE map_chat_message ADD COLUMN system_ref_key TEXT');
ensureColumn('map_chat_message', 'system_ref_title', 'ALTER TABLE map_chat_message ADD COLUMN system_ref_title TEXT');
ensureColumn('map_chat_message', 'system_ref_text', 'ALTER TABLE map_chat_message ADD COLUMN system_ref_text TEXT');
ensureColumn('map_chat_message', 'system_ref_occurred_at', 'ALTER TABLE map_chat_message ADD COLUMN system_ref_occurred_at INTEGER');

function parseTimestampMs(value) {
  if (value === null || typeof value === 'undefined') return null;
  if (value instanceof Date) {
    const ms = value.getTime();
    if (!Number.isFinite(ms) || ms <= 0) return null;
    return ms;
  }
  if (typeof value === 'number' && Number.isFinite(value)) {
    if (value <= 0) return null;
    const ms = value > 1e12 ? value : value > 1e10 ? value : value * 1000;
    return ms > 0 ? Math.floor(ms) : null;
  }
  const text = String(value).trim();
  if (!text) return null;
  if (/^\d+$/.test(text)) {
    const num = Number(text);
    if (!Number.isFinite(num)) return null;
    if (num <= 0) return null;
    const ms = num > 1e12 ? num : num > 1e10 ? num : num * 1000;
    return ms > 0 ? Math.floor(ms) : null;
  }
  const parsed = Date.parse(text);
  if (!Number.isFinite(parsed) || parsed <= 0) return null;
  return parsed;
}

function parseTimestampMsFromMessageId(id) {
  const raw = String(id || '').trim();
  if (!raw) return null;
  const m = raw.match(/^([0-9a-z]+)-/i);
  if (!m) return null;
  const prefix = m[1].toLowerCase();
  if (!/^[0-9a-z]+$/.test(prefix)) return null;
  const ms = Number.parseInt(prefix, 36);
  if (!Number.isFinite(ms) || ms <= 0) return null;
  if (ms > Date.now() + 365 * 24 * 60 * 60 * 1000) return null;
  return ms;
}

function normalizeRow(row) {
  const tsMs = parseTimestampMs(row?.ts) ?? parseTimestampMsFromMessageId(row?.id);
  const editedAtMs = parseTimestampMs(row?.edited_at);
  const deletedAtMs = parseTimestampMs(row?.deleted_at);
  const systemRefOccurredAtMs = parseTimestampMs(row?.system_ref_occurred_at);
  const base = {
    id: String(row?.id || ''),
    ts: tsMs === null ? '' : new Date(tsMs).toISOString(),
    userId: row?.user_id === null || typeof row?.user_id === 'undefined' ? null : row.user_id,
    name: String(row?.name || 'Гость'),
    text: String(row?.text || ''),
    isEdited: editedAtMs !== null,
    isDeleted: deletedAtMs !== null,
  };
  if (editedAtMs !== null) {
    base.editedAt = new Date(editedAtMs).toISOString();
  }
  if (deletedAtMs !== null) {
    base.deletedAt = new Date(deletedAtMs).toISOString();
  }
  if (row?.reply_to_id && row?.reply_to_text) {
    const replyTsMs = parseTimestampMs(row?.reply_to_ts) ?? tsMs;
    base.replyTo = {
      id: String(row.reply_to_id),
      name: String(row.reply_to_name || 'Гость'),
      text: String(row.reply_to_text),
      ts: replyTsMs === null ? '' : new Date(replyTsMs).toISOString(),
    };
  }
  if (row?.system_ref_key) {
    base.systemRef = {
      key: String(row.system_ref_key),
      title: String(row?.system_ref_title || 'Системное сообщение'),
      text: String(row?.system_ref_text || ''),
      occurredAt: systemRefOccurredAtMs,
    };
  }
  return base;
}

export function getAllMapChatMessages() {
  return new Promise((resolve, reject) => {
    db.all(
      `SELECT id, ts, user_id, name, text, reply_to_id, reply_to_name, reply_to_text, reply_to_ts, edited_at, deleted_at,
              system_ref_key, system_ref_title, system_ref_text, system_ref_occurred_at
       FROM map_chat_message
       WHERE COALESCE(deleted_at, 0) = 0
       ORDER BY ts ASC`,
      (err, rows) => {
        if (err) return reject(err);
        resolve((rows || []).map(normalizeRow));
      }
    );
  });
}

export function getMapChatMessageById(id) {
  return new Promise((resolve, reject) => {
    db.get(
      `SELECT id, ts, user_id, name, text, reply_to_id, reply_to_name, reply_to_text, reply_to_ts, edited_at, deleted_at,
              system_ref_key, system_ref_title, system_ref_text, system_ref_occurred_at
       FROM map_chat_message
       WHERE id = ?`,
      [String(id || '')],
      (err, row) => {
        if (err) return reject(err);
        resolve(row ? normalizeRow(row) : null);
      }
    );
  });
}

export function updateMapChatMessage(id, text) {
  const messageId = String(id || '').trim();
  const nextText = String(text || '').trim();
  const editedAt = Math.floor(Date.now() / 1000);
  return new Promise((resolve, reject) => {
    db.run(
      `UPDATE map_chat_message
       SET text = ?, edited_at = ?, deleted_at = NULL
       WHERE id = ?`,
      [nextText, editedAt, messageId],
      (err) => {
        if (err) return reject(err);
        getMapChatMessageById(messageId).then(resolve).catch(reject);
      }
    );
  });
}

export function deleteMapChatMessage(id) {
  const messageId = String(id || '').trim();
  return new Promise((resolve, reject) => {
    db.run(
      `DELETE FROM map_chat_message
       WHERE id = ?`,
      [messageId],
      (err) => {
        if (err) return reject(err);
        resolve({ id: messageId });
      }
    );
  });
}

export function addMapChatMessage(message) {
  const tsSeconds = Math.floor(Date.now() / 1000);
  const tsMs = tsSeconds * 1000;
  const replyTo = message?.replyTo || null;
  const systemRef = message?.systemRef || null;
  const replyToTsMs = parseTimestampMs(replyTo?.ts);
  const replyToTsSeconds = replyToTsMs === null ? null : Math.floor(replyToTsMs / 1000);
  const systemRefOccurredAtMs = parseTimestampMs(systemRef?.occurredAt);
  const systemRefOccurredAtSeconds = systemRefOccurredAtMs === null ? null : Math.floor(systemRefOccurredAtMs / 1000);
  const baseParams = [
    String(message?.id || ''),
    tsSeconds,
    message?.userId === null || typeof message?.userId === 'undefined' ? null : String(message.userId),
    String(message?.name || 'Гость'),
    String(message?.text || ''),
    replyTo?.id ? String(replyTo.id) : null,
    replyTo?.name ? String(replyTo.name) : null,
    replyTo?.text ? String(replyTo.text) : null,
    replyToTsSeconds,
  ];
  return new Promise((resolve, reject) => {
    const resolveMessage = () => resolve({
      ...message,
      ts: new Date(tsMs).toISOString(),
      ...(replyTo ? { replyTo: { ...replyTo, ts: new Date(replyToTsMs || tsMs).toISOString() } } : {}),
      ...(systemRef ? {
        systemRef: {
          key: String(systemRef?.key || ''),
          title: String(systemRef?.title || 'Системное сообщение'),
          text: String(systemRef?.text || ''),
          occurredAt: systemRefOccurredAtMs,
        },
      } : {}),
    });

    const insertLegacySchema = () => {
      db.run(
        `INSERT INTO map_chat_message (
          id, ts, user_id, name, text, reply_to_id, reply_to_name, reply_to_text, reply_to_ts
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        baseParams,
        (legacyErr) => {
          if (legacyErr) return reject(legacyErr);
          resolveMessage();
        }
      );
    };

    db.run(
      `INSERT INTO map_chat_message (
        id, ts, user_id, name, text, reply_to_id, reply_to_name, reply_to_text, reply_to_ts,
        system_ref_key, system_ref_title, system_ref_text, system_ref_occurred_at
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        ...baseParams,
        systemRef?.key ? String(systemRef.key) : null,
        systemRef?.title ? String(systemRef.title) : null,
        systemRef?.text ? String(systemRef.text) : null,
        systemRefOccurredAtSeconds,
      ],
      (err) => {
        if (err) {
          const errorText = String(err?.message || '').toLowerCase();
          const isMissingSystemRefColumn = errorText.includes('system_ref_')
            && (errorText.includes('no such column') || errorText.includes('does not exist'));
          if (isMissingSystemRefColumn) {
            insertLegacySchema();
            return;
          }
          reject(err);
          return;
        }
        resolveMessage();
      }
    );
  });
}