dbquery.js

const db = require('better-sqlite3')('main.db');

const { isValid } = require('date-fns');
const { z } = require('zod');

function createReminderTableIfNotExists() {
    
    db.prepare(`
                    CREATE TABLE IF NOT EXISTS calendar (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        date TEXT,
                        message TEXT,
                        sent INTEGER DEFAULT 0
                    )
                    `).run();
}

module.exports = (target="",dyd={})=>{

    return new Promise(async (resolve, reject) => {

        try {

            switch(target){

                case "set-reminder":

                    createReminderTableIfNotExists()

                    const reminderDate = new Date(dyd.rdate);

                    if (!isValid(reminderDate)) {
                        throw new Error("Invalid reminder date");
                    }

                    const result = db.prepare(`
                    INSERT INTO calendar (date, message, sent)
                    VALUES (?, ?, 0)
                    `).run(reminderDate.toISOString(), dyd.message || "TEST: This reminder should trigger immediately");

                    resolve({
                        id: result.lastInsertRowid,
                        date: dyd.rdate
                    })

                break;

                case "list-reminders":

                    createReminderTableIfNotExists()

                    const reminders = db.prepare(`
                    SELECT date, message
                    FROM calendar WHERE sent = 0
                    `).all();

                    resolve(reminders)

                break;

                case "execute-query":

                    const query = dyd.query;
                    if (!query) {
                        throw new Error("Query is required");
                    }

                    const stmt = db.prepare(query);
                    let queryResult;
                    if (query.trim().toLowerCase().startsWith("select")) {
                        queryResult = stmt.all();
                    } else {
                        queryResult = stmt.run();
                    }

                    resolve(queryResult)

                break;

                default:

                    reject("Unknown target: " + target);
            }

        } catch (err) {
            reject(err);
        }
    })
}