src/database.js

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 };