import { Pool, PoolClient } from 'pg';
import { databaseConfig } from '../config';
import {
User,
Reminder,
UserPreferences,
ConnectedCalendar,
CalendarProvider,
ReminderStatus,
ReminderPriority
} from '../types';
import { ReminderFilter } from '../services/ReminderService';
export class DatabaseManager {
private pool: Pool;
constructor() {
this.pool = new Pool({
connectionString: databaseConfig.url,
ssl: databaseConfig.ssl ? { rejectUnauthorized: false } : false,
min: databaseConfig.pool.min,
max: databaseConfig.pool.max,
acquireTimeoutMillis: databaseConfig.pool.acquireTimeoutMillis,
idleTimeoutMillis: databaseConfig.pool.idleTimeoutMillis
});
this.pool.on('error', (err) => {
console.error('Database pool error:', err);
});
}
async initialize(): Promise<void> {
const client = await this.pool.connect();
try {
await this.createTables(client);
console.log('Database initialized successfully');
} finally {
client.release();
}
}
private async createTables(client: PoolClient): Promise<void> {
// Users table
await client.query(`
CREATE TABLE IF NOT EXISTS users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) UNIQUE NOT NULL,
display_name VARCHAR(255),
timezone VARCHAR(100) DEFAULT 'UTC',
preferences JSONB DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
last_active TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
)
`);
// Connected calendars table
await client.query(`
CREATE TABLE IF NOT EXISTS connected_calendars (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
calendar_id VARCHAR(255) NOT NULL,
provider VARCHAR(50) NOT NULL,
access_token TEXT NOT NULL,
refresh_token TEXT,
token_expiry TIMESTAMP WITH TIME ZONE,
is_default BOOLEAN DEFAULT false,
sync_enabled BOOLEAN DEFAULT true,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
UNIQUE(user_id, calendar_id, provider)
)
`);
// Reminders table
await client.query(`
CREATE TABLE IF NOT EXISTS reminders (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
title VARCHAR(500) NOT NULL,
description TEXT,
due_date_time TIMESTAMP WITH TIME ZONE,
priority VARCHAR(20) DEFAULT 'medium',
status VARCHAR(20) DEFAULT 'pending',
tags TEXT[] DEFAULT '{}',
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
)
`);
// User sessions table (for authentication)
await client.query(`
CREATE TABLE IF NOT EXISTS user_sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
session_token VARCHAR(255) UNIQUE NOT NULL,
refresh_token VARCHAR(255) UNIQUE NOT NULL,
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
last_used TIMESTAMP WITH TIME ZONE DEFAULT NOW()
)
`);
// Create indexes for better performance
await client.query(`
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_connected_calendars_user_id ON connected_calendars(user_id);
CREATE INDEX IF NOT EXISTS idx_connected_calendars_calendar_id ON connected_calendars(calendar_id);
CREATE INDEX IF NOT EXISTS idx_reminders_user_id ON reminders(user_id);
CREATE INDEX IF NOT EXISTS idx_reminders_due_date ON reminders(due_date_time);
CREATE INDEX IF NOT EXISTS idx_reminders_status ON reminders(status);
CREATE INDEX IF NOT EXISTS idx_reminders_priority ON reminders(priority);
CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id ON user_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_user_sessions_session_token ON user_sessions(session_token);
`);
// Create trigger to automatically update updated_at timestamps
await client.query(`
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ language 'plpgsql';
`);
const tables = ['users', 'connected_calendars', 'reminders', 'user_sessions'];
for (const table of tables) {
await client.query(`
DROP TRIGGER IF EXISTS update_${table}_updated_at ON ${table};
CREATE TRIGGER update_${table}_updated_at
BEFORE UPDATE ON ${table}
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
`);
}
}
// User management
async createUser(userData: {
email: string;
displayName?: string;
timezone?: string;
preferences?: UserPreferences;
}): Promise<User> {
const client = await this.pool.connect();
try {
const result = await client.query(
`INSERT INTO users (email, display_name, timezone, preferences)
VALUES ($1, $2, $3, $4)
RETURNING *`,
[
userData.email,
userData.displayName,
userData.timezone || 'UTC',
JSON.stringify(userData.preferences || {})
]
);
return this.mapUserFromDb(result.rows[0]);
} finally {
client.release();
}
}
async getUser(userId: string): Promise<User | null> {
const client = await this.pool.connect();
try {
// Get user data
const userResult = await client.query(
'SELECT * FROM users WHERE id = $1',
[userId]
);
if (userResult.rows.length === 0) {
return null;
}
// Get connected calendars
const calendarsResult = await client.query(
'SELECT * FROM connected_calendars WHERE user_id = $1',
[userId]
);
const user = this.mapUserFromDb(userResult.rows[0]);
user.connectedCalendars = calendarsResult.rows.map(this.mapConnectedCalendarFromDb);
return user;
} finally {
client.release();
}
}
async getUserByEmail(email: string): Promise<User | null> {
const client = await this.pool.connect();
try {
const result = await client.query(
'SELECT * FROM users WHERE email = $1',
[email]
);
if (result.rows.length === 0) {
return null;
}
return this.mapUserFromDb(result.rows[0]);
} finally {
client.release();
}
}
async updateUser(userId: string, updates: Partial<User>): Promise<User> {
const client = await this.pool.connect();
try {
const setParts: string[] = [];
const values: any[] = [userId];
let paramCount = 1;
if (updates.displayName !== undefined) {
setParts.push(`display_name = $${++paramCount}`);
values.push(updates.displayName);
}
if (updates.timezone !== undefined) {
setParts.push(`timezone = $${++paramCount}`);
values.push(updates.timezone);
}
if (updates.preferences !== undefined) {
setParts.push(`preferences = $${++paramCount}`);
values.push(JSON.stringify(updates.preferences));
}
if (setParts.length === 0) {
throw new Error('No fields to update');
}
const result = await client.query(
`UPDATE users SET ${setParts.join(', ')} WHERE id = $1 RETURNING *`,
values
);
return this.mapUserFromDb(result.rows[0]);
} finally {
client.release();
}
}
// Connected calendar management
async addConnectedCalendar(
userId: string,
calendarData: Omit<ConnectedCalendar, 'id'>
): Promise<ConnectedCalendar> {
const client = await this.pool.connect();
try {
const result = await client.query(
`INSERT INTO connected_calendars
(user_id, calendar_id, provider, access_token, refresh_token, token_expiry, is_default, sync_enabled)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
RETURNING *`,
[
userId,
calendarData.calendarId,
calendarData.provider,
calendarData.accessToken,
calendarData.refreshToken,
calendarData.tokenExpiry,
calendarData.isDefault,
calendarData.syncEnabled
]
);
return this.mapConnectedCalendarFromDb(result.rows[0]);
} finally {
client.release();
}
}
async updateConnectedCalendar(
userId: string,
calendarId: string,
updates: Partial<ConnectedCalendar>
): Promise<ConnectedCalendar> {
const client = await this.pool.connect();
try {
const setParts: string[] = [];
const values: any[] = [userId, calendarId];
let paramCount = 2;
if (updates.accessToken !== undefined) {
setParts.push(`access_token = $${++paramCount}`);
values.push(updates.accessToken);
}
if (updates.refreshToken !== undefined) {
setParts.push(`refresh_token = $${++paramCount}`);
values.push(updates.refreshToken);
}
if (updates.tokenExpiry !== undefined) {
setParts.push(`token_expiry = $${++paramCount}`);
values.push(updates.tokenExpiry);
}
if (updates.isDefault !== undefined) {
setParts.push(`is_default = $${++paramCount}`);
values.push(updates.isDefault);
}
if (updates.syncEnabled !== undefined) {
setParts.push(`sync_enabled = $${++paramCount}`);
values.push(updates.syncEnabled);
}
if (setParts.length === 0) {
throw new Error('No fields to update');
}
const result = await client.query(
`UPDATE connected_calendars SET ${setParts.join(', ')}
WHERE user_id = $1 AND calendar_id = $2
RETURNING *`,
values
);
return this.mapConnectedCalendarFromDb(result.rows[0]);
} finally {
client.release();
}
}
// Reminder management
async createReminder(
userId: string,
reminderData: Omit<Reminder, 'id' | 'created' | 'updated'>
): Promise<Reminder> {
const client = await this.pool.connect();
try {
const result = await client.query(
`INSERT INTO reminders
(user_id, title, description, due_date_time, priority, status, tags)
VALUES ($1, $2, $3, $4, $5, $6, $7)
RETURNING *`,
[
userId,
reminderData.title,
reminderData.description,
reminderData.dueDateTime,
reminderData.priority,
reminderData.status,
reminderData.tags || []
]
);
return this.mapReminderFromDb(result.rows[0]);
} finally {
client.release();
}
}
async getReminder(userId: string, reminderId: string): Promise<Reminder | null> {
const client = await this.pool.connect();
try {
const result = await client.query(
'SELECT * FROM reminders WHERE id = $1 AND user_id = $2',
[reminderId, userId]
);
if (result.rows.length === 0) {
return null;
}
return this.mapReminderFromDb(result.rows[0]);
} finally {
client.release();
}
}
async getReminders(userId: string, filter: ReminderFilter = {}): Promise<Reminder[]> {
const client = await this.pool.connect();
try {
let query = 'SELECT * FROM reminders WHERE user_id = $1';
const values: any[] = [userId];
let paramCount = 1;
if (filter.startDate) {
query += ` AND (due_date_time IS NULL OR due_date_time >= $${++paramCount})`;
values.push(filter.startDate);
}
if (filter.endDate) {
query += ` AND (due_date_time IS NULL OR due_date_time <= $${++paramCount})`;
values.push(filter.endDate);
}
if (filter.status) {
query += ` AND status = $${++paramCount}`;
values.push(filter.status);
}
if (filter.priority) {
query += ` AND priority = $${++paramCount}`;
values.push(filter.priority);
}
if (filter.tags && filter.tags.length > 0) {
query += ` AND tags && $${++paramCount}`;
values.push(filter.tags);
}
query += ' ORDER BY created_at DESC';
const result = await client.query(query, values);
return result.rows.map(this.mapReminderFromDb);
} finally {
client.release();
}
}
async updateReminder(
userId: string,
reminderId: string,
reminder: Reminder
): Promise<Reminder> {
const client = await this.pool.connect();
try {
const result = await client.query(
`UPDATE reminders SET
title = $1, description = $2, due_date_time = $3,
priority = $4, status = $5, tags = $6
WHERE id = $7 AND user_id = $8
RETURNING *`,
[
reminder.title,
reminder.description,
reminder.dueDateTime,
reminder.priority,
reminder.status,
reminder.tags || [],
reminderId,
userId
]
);
if (result.rows.length === 0) {
throw new Error('Reminder not found or access denied');
}
return this.mapReminderFromDb(result.rows[0]);
} finally {
client.release();
}
}
async deleteReminder(userId: string, reminderId: string): Promise<boolean> {
const client = await this.pool.connect();
try {
const result = await client.query(
'DELETE FROM reminders WHERE id = $1 AND user_id = $2',
[reminderId, userId]
);
return result.rowCount > 0;
} finally {
client.release();
}
}
// Session management
async createSession(
userId: string,
sessionToken: string,
refreshToken: string,
expiresAt: Date
): Promise<void> {
const client = await this.pool.connect();
try {
await client.query(
`INSERT INTO user_sessions (user_id, session_token, refresh_token, expires_at)
VALUES ($1, $2, $3, $4)`,
[userId, sessionToken, refreshToken, expiresAt]
);
} finally {
client.release();
}
}
async getSessionByToken(sessionToken: string): Promise<{ userId: string; expiresAt: Date } | null> {
const client = await this.pool.connect();
try {
const result = await client.query(
'SELECT user_id, expires_at FROM user_sessions WHERE session_token = $1',
[sessionToken]
);
if (result.rows.length === 0) {
return null;
}
return {
userId: result.rows[0].user_id,
expiresAt: result.rows[0].expires_at
};
} finally {
client.release();
}
}
async deleteSession(sessionToken: string): Promise<boolean> {
const client = await this.pool.connect();
try {
const result = await client.query(
'DELETE FROM user_sessions WHERE session_token = $1',
[sessionToken]
);
return result.rowCount > 0;
} finally {
client.release();
}
}
async cleanupExpiredSessions(): Promise<number> {
const client = await this.pool.connect();
try {
const result = await client.query(
'DELETE FROM user_sessions WHERE expires_at < NOW()'
);
return result.rowCount;
} finally {
client.release();
}
}
// Health check
async ping(): Promise<boolean> {
const client = await this.pool.connect();
try {
await client.query('SELECT 1');
return true;
} catch (error) {
console.error('Database ping failed:', error);
return false;
} finally {
client.release();
}
}
async close(): Promise<void> {
await this.pool.end();
}
// Private mapping methods
private mapUserFromDb(row: any): User {
return {
id: row.id,
email: row.email,
displayName: row.display_name,
timezone: row.timezone,
preferences: row.preferences || {},
connectedCalendars: [], // Will be populated separately
created: row.created_at,
lastActive: row.last_active
};
}
private mapConnectedCalendarFromDb(row: any): ConnectedCalendar {
return {
calendarId: row.calendar_id,
provider: row.provider as CalendarProvider,
accessToken: row.access_token,
refreshToken: row.refresh_token,
tokenExpiry: row.token_expiry,
isDefault: row.is_default,
syncEnabled: row.sync_enabled
};
}
private mapReminderFromDb(row: any): Reminder {
return {
id: row.id,
title: row.title,
description: row.description,
dueDateTime: row.due_date_time,
priority: row.priority as ReminderPriority,
status: row.status as ReminderStatus,
tags: row.tags || [],
created: row.created_at,
updated: row.updated_at
};
}
}
