DB.js

import SQL from 'better-sqlite3'; // Load sqlite module
import fs from 'node:fs'; // import fs to check if db exists and read db init file
const DB_FILE = 'database/database.db'; // DB File name
const DB_INIT_FILE = 'db_creation.sql'; // DB creation instructions

class DB {
    static init_db() {
        // check if db exists, if not create it
        try {
            if (!fs.existsSync(DB_FILE)) {
                console.log('DB file does not exist, creating new DB.')
                // db does not exist, create it
                const sql = fs.readFileSync(DB_INIT_FILE, 'utf8')
                let db = this.connect()
                db.exec(sql)
                this.disconnect(db)
                console.log('new DB created at ' + DB_FILE)
            }
            this.migrateLegacySchema();
        } catch(v) {
            console.error(v)
        }
    }

    /**
     * Migration helper — For backward compatibility only.
     *
     * This will check for schema changes from legacy versions and apply necessary updates.
     * mark change date with comment and remove old upgrades when no longer needed.
     */
    static migrateLegacySchema() {
        const db = this.connect();
        try {
            // Added 2025-12-10: Add last_voice, last_message columns to users table if they don't exist
            const rows = db.prepare("PRAGMA table_info('users')").all();
            const hasLastVoice = rows.some(r => r.name === 'last_voice');
            const hasLastMessage = rows.some(r => r.name === 'last_message');
            if (!hasLastVoice) {
                console.log('Legacy DB detected: adding last_voice column to users table (schema upgrade).');
                db.prepare(`ALTER TABLE users ADD COLUMN last_voice INTEGER`).run();
            }
            if (!hasLastMessage) {
                console.log('Legacy DB detected: adding last_message column to users table (schema upgrade).');
                db.prepare(`ALTER TABLE users ADD COLUMN last_message INTEGER`).run();
            }
            // Added 2025-12-28: Create admins table if it doesn't exist
            // Ensure admins table exists for legacy DBs
            const adminRows = db.prepare("PRAGMA table_info('admins')").all();
            if (!adminRows || adminRows.length === 0) {
                console.log('Legacy DB detected: creating admins table (schema upgrade).');
                db.prepare(`CREATE TABLE IF NOT EXISTS admins (id INTEGER NOT NULL)`).run();
                db.prepare(`CREATE UNIQUE INDEX IF NOT EXISTS admins_id_IDX ON admins (id)`).run();
            }
        } catch (err) {
            console.error('Error while checking/updating DB schema:', err);
        } finally {
            this.disconnect(db);
        }
    }
    // Connect to DB
    static connect(){
        return new SQL(DB_FILE);
    }
    // Disconnect to DB
    static disconnect(db){
        db.close();
    }
    // set points for user
    static setPoints(uid, points){
        var sql = `UPDATE users
                    SET points = ?
                    WHERE id = ?`;
        var db = this.connect();
        db.prepare(sql).run(points, uid);
        this.disconnect(db);
    }
    // set birthday for user
    static setBirthday(uid, date){
        var sql = `UPDATE users
                    SET birthday = ?
                    WHERE id = ?`;
        var db = this.connect();
        db.prepare(sql).run(date, uid);
        this.disconnect(db);
    }

    // set last voice timestamp for user (milliseconds since epoch)
    static setLastVoice(uid, timestamp){
        var sql = `UPDATE users
                    SET last_voice = ?
                    WHERE id = ?`;
        var db = this.connect();
        try {
            db.prepare(sql).run(timestamp, uid);
        } catch (err) {
            // If the column doesn't exist or update fails, log and return.
            console.error('Failed to set last_voice (schema may be missing):', err.message);
        } finally {
            this.disconnect(db);
        }
    }
    // get points for user
    static getPoints(uid){
        var sql = `SELECT points
                    FROM users
                    WHERE id = ?`;
        var db = this.connect();
        var row = db.prepare(sql).get(uid);
        this.disconnect(db);
        return row.points;
    }
    /**
     * Get highscore list
     * @param {number} limit - Maximum number of highscore entries to return
     * @returns {Object[]} - Array of objects containing id and points
     */
    static getHighscore(limit){
        let sql = `SELECT CAST(id AS TEXT) id, points
                    FROM users
                    WHERE points > 0
                    ORDER BY points DESC
                    LIMIT ?`;
        var db = this.connect();
        var row = db.prepare(sql).all(limit);
        this.disconnect(db);
        return row;
    }
    // get birthday for user
    static getBirthday(uid){
        var sql = `SELECT birthday
                    FROM users
                    WHERE id = ?`;
        var db = this.connect();
        var row = db.prepare(sql).get(uid);
        this.disconnect(db);
        return row.birthday;
    }

    // get last voice timestamp (milliseconds since epoch) for user
    static getLastVoice(uid){
        var sql = `SELECT last_voice
                    FROM users
                    WHERE id = ?`;
        var db = this.connect();
        var row;
        try {
            row = db.prepare(sql).get(uid);
        } catch (err) {
            // If column doesn't exist, return null
            this.disconnect(db);
            return null;
        }
        this.disconnect(db);
        return row ? row.last_voice : null;
    }
    // cheak if user exists in db, returns bool
    static isUserExist(uid){
        var sql = `SELECT CAST(id AS TEXT) id
                    FROM users
                    WHERE id = ?`;
        var db = this.connect();
        var row = db.prepare(sql).get(uid);
        this.disconnect(db);
        if (row === undefined){     // accurding to better-sqlite3 returns undefined if results was empty
            return false;           // if row is undefined the sucssesful quary is empty, user doesnt exists
        }else{
            return true;            // else the user was retrieved from the db, user exists in db
        }
    }
    static newUser(uid){
        console.log("New User With ID: " + uid);
        var sql = `INSERT INTO users(id, points, birthday) VALUES(?,0,-1)`;
        var db = this.connect();
        db.prepare(sql).run(uid);
        this.disconnect(db);
    }

    /**
     * Find all users who have a birthday on a specified date
     * 
     * @param {Date} date - Date to search for users who have birthday
     * @returns {number[]} - Array of discord user ids who have birthday on provided date
     */
    static BDayOnDate(date){
        const result = [];
        const month = date.getMonth() + 1; // Add 1 to convert from 0-based to 1-based index
        const day = date.getDate();
        const sql = `SELECT id
                    FROM users
                    WHERE SUBSTR(birthday, 6, 2) = ?
                    AND SUBSTR(birthday, 9) = ?`;
        var db = this.connect();
        const row = db.prepare(sql).all(month.toString().padStart(2, '0'), day.toString().padStart(2, '0'));
        this.disconnect(db);
        row.forEach((row) => {
            result.push(row.id);
        });
        return result;
    }

    static isSelfAddChannel(channel_id){
        var sql = `SELECT id
                    FROM selfAddChannels
                    WHERE id = ?`;
        var db = this.connect();
        var row = db.prepare(sql).get(channel_id);
        this.disconnect(db);
        if (row === undefined){     // accurding to better-sqlite3 returns undefined if results was empty
            return false;           // if row is undefined the sucssesful quary is empty, user doesnt exists
        }else{
            return true;            // else the user was retrieved from the db, user exists in db
        }
    }

    // check if user id exists in admins table
    static isAdmin(uid){
        const sql = `SELECT id
                    FROM admins
                    WHERE id = ?`
        let db
        try {
            db = this.connect()
            const row = db.prepare(sql).get(uid)
            return row !== undefined
        } catch (err) {
            console.error('Failed to check admin status:', err.message)
            return false
        } finally {
            if (db) {
                try {
                    this.disconnect(db)
                } catch (closeErr) {
                    console.error('Failed to disconnect DB after admin check error:', closeErr.message)
                }
            }
        }
    }

    static getSelfAddChannels(){
        var result = [];
        var sql = `SELECT CAST(id AS TEXT) id
                    FROM selfAddChannels`;
        var db = this.connect();
        var row = db.prepare(sql).all();
        this.disconnect(db);
        row.forEach((row) => {
            result.push(row.id);
        });
        return result;
    }
    
    // set last message timestamp for user (milliseconds since epoch)
    static setLastMessage(uid, timestamp){
        var sql = `UPDATE users
                    SET last_message = ?
                    WHERE id = ?`;
        var db = this.connect();
        try {
            db.prepare(sql).run(timestamp, uid);
        } catch (err) {
            // If the column doesn't exist or update fails, log and return.
            console.error('Failed to set last_message (schema may be missing):', err.message);
        } finally {
            this.disconnect(db);
        }
    }

    // get last message timestamp (milliseconds since epoch) for user
    static getLastMessage(uid){
        var sql = `SELECT last_message
                    FROM users
                    WHERE id = ?`;
        var db = this.connect();
        var row;
        try {
            row = db.prepare(sql).get(uid);
        } catch (err) {
            // If column doesn't exist or query fails, return null (do not modify schema)
            console.error('Failed to read last_message (schema may be missing):', err.message);
            this.disconnect(db);
            return null;
        }
        this.disconnect(db);
        return row ? row.last_message : null;
    }
}

export default DB;