workflow-app/backend/db.js

/**
 * Database abstraction layer (async).
 * 
 * Punkt 4: PostgreSQL for production, SQLite fallback for development.
 * Both modes expose the SAME async API: db.prepare(sql).run/get/all() return Promises.
 * 
 * - If DATABASE_URL starts with 'postgresql://' → PostgreSQL (pg)
 * - Otherwise → SQLite (better-sqlite3, wrapped in Promises for unified async API)
 */
const path = require('path');

const DATABASE_URL = process.env.DATABASE_URL || '';
const usePostgres = DATABASE_URL.startsWith('postgresql://') || DATABASE_URL.startsWith('postgres://');

// Helper: convert SQLite ? placeholders to PostgreSQL $1, $2, etc.
function convertPlaceholders(sql) {
  let idx = 0;
  return sql.replace(/\?/g, () => { idx++; return '$' + idx; });
}

let db;

if (usePostgres) {
  // ============ PostgreSQL mode (production) ============
  const { Pool } = require('pg');
  const pool = new Pool({
    connectionString: DATABASE_URL,
    max: 10,
    idleTimeoutMillis: 30000,
    connectionTimeoutMillis: 10000,
  });

  pool.on('error', (err) => {
    console.error('[DB] PostgreSQL Pool-Fehler:', err.message);
  });

  console.log('[DB] PostgreSQL-Verbindung hergestellt (Production-Modus).');

  db = {
    _pool: pool,
    _type: 'postgres',

    prepare(sql) {
      const pgSql = convertPlaceholders(sql);
      return {
        run: (...params) => {
          // For INSERT statements, append RETURNING id to get the generated ID
          const isInsert = pgSql.trim().toUpperCase().startsWith('INSERT');
          const finalSql = isInsert && !pgSql.toUpperCase().includes('RETURNING')
            ? pgSql.replace(/;?\s*$/, ' RETURNING id')
            : pgSql;
          return pool.query(finalSql, params).then(result => ({
            changes: result.rowCount,
            lastInsertRowid: result.rows[0]?.id || null,
          }));
        },
        get: (...params) => pool.query(pgSql, params).then(result => result.rows[0] || null),
        all: (...params) => pool.query(pgSql, params).then(result => result.rows),
      };
    },

    exec(sql) {
      return pool.query(sql);
    },

    pragma(_str) {
      return Promise.resolve({});
    },

    transaction(fn) {
      return async (...args) => {
        const client = await pool.connect();
        try {
          await client.query('BEGIN');
          
          const txDb = {
            prepare(sql) {
              const pgSql = convertPlaceholders(sql);
              return {
                run: (...params) => client.query(pgSql, params).then(result => ({
                  changes: result.rowCount,
                  lastInsertRowid: result.rows[0]?.id || null,
                })),
                get: (...params) => client.query(pgSql, params).then(result => result.rows[0] || null),
                all: (...params) => client.query(pgSql, params).then(result => result.rows),
              };
            },
            exec: (sql) => client.query(sql),
            pragma: () => Promise.resolve({}),
          };

          const result = await fn.call(txDb, ...args);
          await client.query('COMMIT');
          return result;
        } catch (err) {
          await client.query('ROLLBACK');
          throw err;
        } finally {
          client.release();
        }
      };
    },

    close() {
      return pool.end();
    },
  };
} else {
  // ============ SQLite mode (development) ============
  // Wrapped in Promises so the API is identical to PostgreSQL (async)
  const Database = require('better-sqlite3');
  const dbPath = path.join(__dirname, 'data', 'workflow.db');
  const sqliteDb = new Database(dbPath);

  sqliteDb.pragma('journal_mode = WAL');
  sqliteDb.pragma('foreign_keys = ON');

  console.log('[DB] SQLite-Datenbank verbunden (better-sqlite3, WAL-Modus, async-Wrapper).');

  db = {
    _type: 'sqlite',

    prepare(sql) {
      const stmt = sqliteDb.prepare(sql);
      return {
        run: (...params) => Promise.resolve(stmt.run(...params)),
        get: (...params) => Promise.resolve(stmt.get(...params)),
        all: (...params) => Promise.resolve(stmt.all(...params)),
      };
    },

    exec(sql) {
      sqliteDb.exec(sql);
      return Promise.resolve();
    },

    pragma(str) {
      sqliteDb.pragma(str);
      return Promise.resolve({});
    },

    transaction(fn) {
      const tx = sqliteDb.transaction(fn);
      return (...args) => Promise.resolve(tx(...args));
    },

    close() {
      sqliteDb.close();
      return Promise.resolve();
    },
  };
}

module.exports = db;