WebService/modules/databaseGlobals.js

const fs = require("node:fs")
const path = require("node:path")
const Database = require("better-sqlite3")
const databasePath = path.join(process.cwd(), "data", "database.db")

if (!fs.existsSync(path.parse(databasePath).dir)) {
    fs.mkdirSync(path.parse(databasePath).dir, { recursive: true })
}

const db = new Database(databasePath)

function initializeDatabase() {
    db.exec("PRAGMA foreign_keys = ON")
    db.exec(`
        CREATE TABLE IF NOT EXISTS accounts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            firstName TEXT,
            lastName TEXT,
            username TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL,
            createdAt TEXT DEFAULT CURRENT_TIMESTAMP
        )
    `)
    db.exec(`
        CREATE TABLE IF NOT EXISTS products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            owner INTEGER NOT NULL,
            createdAt TEXT DEFAULT CURRENT_TIMESTAMP,
            
            FOREIGN KEY(owner)
                REFERENCES accounts(id)
                ON DELETE CASCADE
        )
    `)

    db.exec(`
        CREATE TABLE IF NOT EXISTS permissions (
            id INTEGER PRIMARY KEY,
            userId INTEGER NOT NULL,
            productId INTEGER NOT NULL,
            channel TEXT DEFAULT '*', 
            permission TEXT NOT NULL,
            
            FOREIGN KEY(userId)
                REFERENCES accounts(id)
                ON DELETE CASCADE,
            FOREIGN KEY(productId)
                REFERENCES products(id)
                ON DELETE CASCADE
            
            UNIQUE(
                userId,
                channel,
                productId,
                permission
            )
        )
    `)

    db.exec(`
        CREATE TABLE IF NOT EXISTS licenses (
            id INTEGER PRIMARY KEY,
            key TEXT NOT NULL UNIQUE,
            productId INTEGER NOT NULL,
            targetIhannel TEXT NOT NULL,
            usedAt TEXT,
            
            FOREIGN KEY(productId)
                REFERENCES products(id)
                ON DELETE CASCADE
        )
    `)
}

module.exports = {
    initializeDatabase
}