File size: 2,700 Bytes
3c7e34b | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | const path = require('path');
const Database = require('better-sqlite3');
const fs = require('fs');
const DATA_DIR = path.join(__dirname, '..', 'data');
if (!fs.existsSync(DATA_DIR)) fs.mkdirSync(DATA_DIR, { recursive: true });
const db = new Database(path.join(DATA_DIR, 'wsb.db'));
// Enable WAL mode for better concurrency
db.pragma('journal_mode = WAL');
// ── Schema ────────────────────────────────────────────────────
db.exec(`
CREATE TABLE IF NOT EXISTS tickets (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL,
username TEXT NOT NULL,
channel_id TEXT,
status TEXT DEFAULT 'open',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
closed_at DATETIME
);
CREATE TABLE IF NOT EXISTS verification_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL,
username TEXT NOT NULL,
action TEXT NOT NULL,
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS bot_state (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
);
`);
// ── Prepared Statements ───────────────────────────────────────
const stmts = {
// Tickets
createTicket: db.prepare('INSERT INTO tickets (user_id, username, channel_id) VALUES (?, ?, ?)'),
closeTicket: db.prepare('UPDATE tickets SET status = ?, closed_at = CURRENT_TIMESTAMP WHERE channel_id = ?'),
getTicket: db.prepare('SELECT * FROM tickets WHERE channel_id = ?'),
getOpenTickets: db.prepare('SELECT * FROM tickets WHERE status = ?'),
getUserTicket: db.prepare('SELECT * FROM tickets WHERE user_id = ? AND status = ?'),
ticketStats: db.prepare(`
SELECT
COUNT(*) as total,
SUM(CASE WHEN status = 'open' THEN 1 ELSE 0 END) as open_count,
SUM(CASE WHEN status = 'closed' THEN 1 ELSE 0 END) as closed_count,
SUM(CASE WHEN status = 'deleted' THEN 1 ELSE 0 END) as deleted_count
FROM tickets
`),
// Verification log
logVerification: db.prepare('INSERT INTO verification_log (user_id, username, action) VALUES (?, ?, ?)'),
// Bot state (key-value)
setState: db.prepare('INSERT OR REPLACE INTO bot_state (key, value) VALUES (?, ?)'),
getState: db.prepare('SELECT value FROM bot_state WHERE key = ?'),
delState: db.prepare('DELETE FROM bot_state WHERE key = ?'),
};
module.exports = { db, stmts };
|