Spaces:
Sleeping
Sleeping
File size: 7,322 Bytes
f4656a0 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 | 'use strict';
const path = require('path');
const fs = require('fs');
const logger = require('../utils/logger');
let db = null;
let dbType = null;
/**
* Initialize database connection based on DB_TYPE env variable.
* Uses SQLite for development and PostgreSQL for production.
*/
async function initDatabase() {
// Idempotent — safe to call multiple times
if (db) return db;
dbType = process.env.DB_TYPE || 'sqlite';
if (dbType === 'sqlite') {
return initSQLite();
} else {
return initPostgres();
}
}
function initSQLite() {
const Database = require('better-sqlite3');
const dbPath = process.env.DB_PATH || './data/database.db';
const resolvedPath = path.resolve(dbPath);
// Ensure data directory exists
const dir = path.dirname(resolvedPath);
if (!fs.existsSync(dir)) {
fs.mkdirSync(dir, { recursive: true });
}
db = new Database(resolvedPath);
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');
logger.info(`SQLite database initialized at: ${resolvedPath}`);
createTables();
return db;
}
async function initPostgres() {
const { Pool } = require('pg');
const pool = new Pool({
connectionString: process.env.DATABASE_URL,
ssl: process.env.DB_SSL === 'true' ? { rejectUnauthorized: false } : false,
max: 20,
idleTimeoutMillis: 30000,
connectionTimeoutMillis: 2000,
});
// Test connection
const client = await pool.connect();
await client.query('SELECT NOW()');
client.release();
db = pool;
logger.info('PostgreSQL database connected');
await createTablesPg();
return db;
}
/**
* Create all tables for SQLite.
*/
function createTables() {
db.exec(`
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
description TEXT,
price REAL NOT NULL,
category TEXT NOT NULL,
tags TEXT DEFAULT '[]',
features TEXT DEFAULT '[]',
images TEXT DEFAULT '[]',
stock INTEGER DEFAULT 100,
active INTEGER DEFAULT 1,
created_at TEXT DEFAULT (datetime('now')),
updated_at TEXT DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS sessions (
id TEXT PRIMARY KEY,
channel TEXT NOT NULL,
user_id TEXT NOT NULL,
current_question_index INTEGER DEFAULT 0,
responses TEXT DEFAULT '[]',
completed INTEGER DEFAULT 0,
recommendations TEXT,
metadata TEXT DEFAULT '{}',
created_at TEXT DEFAULT (datetime('now')),
updated_at TEXT DEFAULT (datetime('now')),
expires_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sessions_user_channel ON sessions(user_id, channel);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_products_category ON products(category);
CREATE INDEX IF NOT EXISTS idx_products_active ON products(active);
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
created_at TEXT DEFAULT (datetime('now')),
updated_at TEXT DEFAULT (datetime('now')),
last_seen_at TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE TABLE IF NOT EXISTS user_activity (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id TEXT NOT NULL,
session_id TEXT,
action TEXT NOT NULL,
category TEXT,
data TEXT DEFAULT '{}',
created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_activity_user ON user_activity(user_id);
CREATE INDEX IF NOT EXISTS idx_activity_action ON user_activity(action);
CREATE TABLE IF NOT EXISTS temp_users (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
otp TEXT NOT NULL,
otp_expires_at TEXT NOT NULL,
otp_attempts INTEGER DEFAULT 0,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_temp_users_email ON temp_users(email);
CREATE INDEX IF NOT EXISTS idx_temp_users_expires ON temp_users(otp_expires_at);
CREATE TABLE IF NOT EXISTS password_resets (
id TEXT PRIMARY KEY,
email TEXT NOT NULL,
otp TEXT NOT NULL,
otp_expires_at TEXT NOT NULL,
otp_attempts INTEGER DEFAULT 0,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_pw_resets_email ON password_resets(email);
CREATE TABLE IF NOT EXISTS google_users (
id TEXT PRIMARY KEY,
email TEXT UNIQUE NOT NULL,
google_uid TEXT UNIQUE NOT NULL,
name TEXT,
user_id INTEGER,
created_at TEXT DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_google_users_email ON google_users(email);
CREATE INDEX IF NOT EXISTS idx_google_users_uid ON google_users(google_uid);
`);
logger.info('SQLite tables created/verified');
}
async function createTablesPg() {
await db.query(`
CREATE TABLE IF NOT EXISTS products (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
category VARCHAR(100) NOT NULL,
tags JSONB DEFAULT '[]',
features JSONB DEFAULT '[]',
images JSONB DEFAULT '[]',
stock INTEGER DEFAULT 100,
active BOOLEAN DEFAULT true,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW()
);
CREATE TABLE IF NOT EXISTS sessions (
id UUID PRIMARY KEY,
channel VARCHAR(50) NOT NULL,
user_id VARCHAR(255) NOT NULL,
current_question_index INTEGER DEFAULT 0,
responses JSONB DEFAULT '[]',
completed BOOLEAN DEFAULT false,
recommendations JSONB,
metadata JSONB DEFAULT '{}',
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
expires_at TIMESTAMP NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sessions_user_channel ON sessions(user_id, channel);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_products_category ON products(category);
CREATE INDEX IF NOT EXISTS idx_products_active ON products(active);
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash TEXT NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMP DEFAULT NOW(),
last_seen_at TIMESTAMP
);
CREATE TABLE IF NOT EXISTS user_activity (
id SERIAL PRIMARY KEY,
user_id VARCHAR(255) NOT NULL,
session_id VARCHAR(255),
action VARCHAR(100) NOT NULL,
category VARCHAR(100),
data JSONB DEFAULT '{}',
created_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_activity_user ON user_activity(user_id);
CREATE INDEX IF NOT EXISTS idx_activity_action ON user_activity(action);
`);
logger.info('PostgreSQL tables created/verified');
}
function getDb() {
if (!db) throw new Error('Database not initialized. Call initDatabase() first.');
return db;
}
function getDbType() {
return dbType || process.env.DB_TYPE || 'sqlite';
}
module.exports = { initDatabase, getDb, getDbType };
|