File size: 3,910 Bytes
3c7e34b 6ec7384 6bab161 6ec7384 3c7e34b 6ec7384 6bab161 6ec7384 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 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 | 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
);
CREATE TABLE IF NOT EXISTS whitelist (
user_id TEXT PRIMARY KEY,
max_drops INTEGER DEFAULT 3,
added_by TEXT NOT NULL,
added_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS drop_log (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL,
title TEXT NOT NULL,
channel_id TEXT NOT NULL,
dropped_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
`);
// ── 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 = ?'),
// Whitelist
addWhitelist: db.prepare('INSERT OR REPLACE INTO whitelist (user_id, max_drops, added_by) VALUES (?, ?, ?)'),
removeWhitelist: db.prepare('DELETE FROM whitelist WHERE user_id = ?'),
getWhitelist: db.prepare('SELECT * FROM whitelist WHERE user_id = ?'),
getAllWhitelist: db.prepare('SELECT * FROM whitelist'),
// Drop log
logDrop: db.prepare('INSERT INTO drop_log (user_id, title, channel_id) VALUES (?, ?, ?)'),
getDropCount24h: db.prepare(`SELECT COUNT(*) as count FROM drop_log WHERE user_id = ? AND dropped_at > datetime('now', '-24 hours')`),
getLastDrop: db.prepare(`SELECT dropped_at FROM drop_log WHERE user_id = ? ORDER BY dropped_at DESC LIMIT 1`),
};
module.exports = { db, stmts };
|