86 lines
2.5 KiB
JavaScript
86 lines
2.5 KiB
JavaScript
const Database = require('better-sqlite3');
|
|
const path = require('path');
|
|
const fs = require('fs');
|
|
|
|
const dataDir = path.join(__dirname, 'data');
|
|
if (!fs.existsSync(dataDir)) fs.mkdirSync(dataDir);
|
|
|
|
const db = new Database(path.join(dataDir, 'database.sqlite'));
|
|
|
|
db.pragma('journal_mode = WAL');
|
|
db.pragma('foreign_keys = ON');
|
|
|
|
db.exec(`
|
|
-- Таблица администраторов (логин/пароль)
|
|
CREATE TABLE IF NOT EXISTS admins (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
login TEXT UNIQUE NOT NULL,
|
|
password TEXT NOT NULL
|
|
);
|
|
|
|
-- Карточки клиентов
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
phone TEXT UNIQUE NOT NULL,
|
|
name TEXT DEFAULT '',
|
|
status TEXT DEFAULT 'Bronze',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Заявки на заселение
|
|
CREATE TABLE IF NOT EXISTS bookings (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
external_id INTEGER UNIQUE,
|
|
user_id INTEGER,
|
|
name TEXT,
|
|
phone_raw TEXT,
|
|
adults INTEGER DEFAULT 1,
|
|
children INTEGER DEFAULT 0,
|
|
checkin_date TEXT,
|
|
checkout_date TEXT,
|
|
status TEXT DEFAULT 'Новая',
|
|
comments TEXT DEFAULT '',
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id)
|
|
);
|
|
|
|
-- Журнал действий
|
|
CREATE TABLE IF NOT EXISTS audit_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
action TEXT NOT NULL,
|
|
details TEXT,
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
`);
|
|
|
|
function normalizePhone(phone) {
|
|
let digits = phone.replace(/\D/g, '');
|
|
if (/^[78]\d{10}$/.test(digits)) {
|
|
digits = '7' + digits.slice(1);
|
|
}
|
|
return digits;
|
|
}
|
|
|
|
function logAction(action, details = {}) {
|
|
const stmt = db.prepare('INSERT INTO audit_log (action, details) VALUES (?, ?)');
|
|
stmt.run(action, JSON.stringify(details));
|
|
}
|
|
|
|
function ensureSchema() {
|
|
try {
|
|
const info = db.prepare("PRAGMA table_info(users)").all();
|
|
const hasStatus = info.some(col => col.name.toLowerCase() === 'status');
|
|
if (!hasStatus) {
|
|
db.exec("ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'Bronze'");
|
|
db.prepare("UPDATE users SET status = 'Bronze' WHERE status IS NULL OR status = ''").run();
|
|
console.log('[MIGRATE] Added status column to users (Bronze default)');
|
|
}
|
|
} catch (e) {
|
|
console.error('[MIGRATE] Failed to ensure users.status column', e);
|
|
}
|
|
}
|
|
|
|
module.exports = { db, normalizePhone, logAction, ensureSchema };
|