database.js

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database(':memory:'); // Using in-memory DB for simplicity and reset on restart

db.serialize(() => {
    // Users table - Storing passwords in plaintext (Vulnerability: No hashing)
    db.run(`CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE,
        password TEXT,
        role TEXT,
        bio TEXT,
        profile_pic TEXT
    )`);

    // Messages table
    db.run(`CREATE TABLE messages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        content TEXT,
        user_id INTEGER
    )`);

    // Seed data
    const stmt = db.prepare("INSERT INTO users (username, password, role, bio, profile_pic) VALUES (?, ?, ?, ?, ?)");
    
    // Admin user (Vulnerability: Hardcoded credentials in DB)
    stmt.run("admin", "admin123", "admin", "I am the administrator.", "/uploads/default.png");
    
    // Regular user
    stmt.run("alice", "password123", "user", "Just a regular employee.", "/uploads/default.png");
    stmt.run("bob", "qwerty", "user", "I like cats.", "/uploads/default.png");
    
    stmt.finalize();

    console.log("Database initialized with seed data.");
});

module.exports = db;