server/src/scripts/init_db.ts

import database from "better-sqlite3";
import dotenv from "dotenv";
import fs from "fs";

dotenv.config();

const db_path = process.env.DB_NAME as string;
console.log(db_path);
fs.unlink(db_path, (err) => {
    if (err) {
        console.error(err);
    }
    else {
        console.log(`deleted old database:\t${db_path}`)


        const db = database(db_path);

        db.exec(`
            create table users(
                id Text not null PRIMARY KEY,
                name varchar(40) NOT NULL,
                password varchar(20) NOT NULL
            );
        `);

        db.exec(`
            create table sessions(
                id Text not null PRIMARY KEY,
                expires_at INTEGER NOT NULL,
                user_id TEXT NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
            );
        `);


        db.exec(`
            create table messages(
                id Text not null primary key,
                content Text not null,
                type Text NOT NULL DEFAULT "text",
                sender_id TEXT NOT NULL,
                receiver_id TEXT NOT NULL,
                timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                status DEFAULT "sent", /* "sent", "received", "seen" */
                FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
                FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE
            );
        `);




    }
})