server/db.js

import Database from 'better-sqlite3';
import fs from 'fs';
import path from 'path';

// Using __dirname won't work easily in ES modules by default unless specified, 
// since we are using "type": "module" in package.json, we can use a relative path from execution dir:
const dbPath = path.resolve('./mandi.db');
const db = new Database(dbPath, { verbose: console.log });

// Initialize tables
db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    role TEXT NOT NULL,
    selfiePath TEXT NOT NULL
  );

  CREATE TABLE IF NOT EXISTS listings (
    id TEXT PRIMARY KEY,
    sellerId TEXT NOT NULL,
    sellerName TEXT NOT NULL,
    cropName TEXT NOT NULL,
    price TEXT,
    location TEXT NOT NULL,
    timestamp INTEGER NOT NULL,
    FOREIGN KEY (sellerId) REFERENCES users(id)
  );

  CREATE TABLE IF NOT EXISTS listing_images (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    listingId TEXT NOT NULL,
    imagePath TEXT NOT NULL,
    FOREIGN KEY (listingId) REFERENCES listings(id) ON DELETE CASCADE
  );
  CREATE TABLE IF NOT EXISTS support_messages (
    id TEXT PRIMARY KEY,
    senderId TEXT NOT NULL,
    message TEXT NOT NULL,
    isResolved INTEGER DEFAULT 0,
    timestamp INTEGER NOT NULL,
    FOREIGN KEY (senderId) REFERENCES users(id) ON DELETE CASCADE
  );

  CREATE TABLE IF NOT EXISTS messages (
    id TEXT PRIMARY KEY,
    senderId TEXT NOT NULL,
    receiverId TEXT NOT NULL,
    message TEXT NOT NULL,
    timestamp INTEGER NOT NULL,
    FOREIGN KEY (senderId) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (receiverId) REFERENCES users(id) ON DELETE CASCADE
  );
`);

try {
  db.exec(`ALTER TABLE listings ADD COLUMN status TEXT DEFAULT 'active'`);
} catch (err) {}

try {
  db.exec(`ALTER TABLE support_messages ADD COLUMN adminReply TEXT DEFAULT ''`);
} catch (err) {}

try {
  db.exec(`ALTER TABLE support_messages ADD COLUMN unreadAdminReply INTEGER DEFAULT 0`);
} catch (err) {}

try {
  db.exec(`ALTER TABLE listings ADD COLUMN quantity TEXT DEFAULT ''`);
} catch (err) {}

try {
  db.exec(`ALTER TABLE users ADD COLUMN contact TEXT DEFAULT ''`);
} catch (err) {}

try {
  db.exec(`ALTER TABLE users ADD COLUMN isBlocked INTEGER DEFAULT 0`);
} catch (err) {}

db.exec('PRAGMA foreign_keys = ON;');

console.log("Database initialized at:", dbPath);

export default db;