import Database from 'better-sqlite3';
import { generateId } from '../utils.js';
// ─── Types ──────────────────────────────────────────────────────
export type ReminderFrequency = 'one_time' | 'weekly' | 'monthly' | 'yearly';
export type ReminderStatus = 'active' | 'snoozed' | 'completed' | 'dismissed';
export interface Reminder {
id: string;
contact_id: string;
title: string;
description: string | null;
reminder_date: string;
frequency: ReminderFrequency;
status: ReminderStatus;
is_auto_generated: boolean;
created_at: string;
updated_at: string;
deleted_at: string | null;
}
export interface CreateReminderInput {
contact_id: string;
title: string;
description?: string;
reminder_date: string;
frequency?: ReminderFrequency;
}
export interface UpdateReminderInput {
title?: string;
description?: string;
reminder_date?: string;
frequency?: ReminderFrequency;
}
export interface ListRemindersOptions {
contact_id?: string;
status?: ReminderStatus;
page?: number;
per_page?: number;
include_deleted?: boolean;
}
export interface PaginatedResult<T> {
data: T[];
total: number;
page: number;
per_page: number;
}
// ─── Service ────────────────────────────────────────────────────
export class ReminderService {
constructor(private db: Database.Database) {}
create(userId: string, input: CreateReminderInput, isAutoGenerated = false): Reminder {
// Verify the contact belongs to the user
this.verifyContactOwnership(userId, input.contact_id);
const id = generateId();
const now = new Date().toISOString();
this.db.prepare(`
INSERT INTO reminders (id, contact_id, title, description, reminder_date, frequency, is_auto_generated, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
`).run(id, input.contact_id, input.title, input.description ?? null,
input.reminder_date, input.frequency ?? 'one_time', isAutoGenerated ? 1 : 0, now, now);
return this.getById(userId, id)!;
}
get(userId: string, id: string): Reminder | null {
return this.getById(userId, id);
}
update(userId: string, id: string, input: UpdateReminderInput): Reminder | null {
const existing = this.getById(userId, id);
if (!existing) return null;
const fields: string[] = [];
const values: any[] = [];
if (input.title !== undefined) { fields.push('title = ?'); values.push(input.title); }
if (input.description !== undefined) { fields.push('description = ?'); values.push(input.description); }
if (input.reminder_date !== undefined) { fields.push('reminder_date = ?'); values.push(input.reminder_date); }
if (input.frequency !== undefined) { fields.push('frequency = ?'); values.push(input.frequency); }
if (fields.length > 0) {
fields.push("updated_at = datetime('now')");
values.push(id);
this.db.prepare(`UPDATE reminders SET ${fields.join(', ')} WHERE id = ? AND deleted_at IS NULL`).run(...values);
}
return this.getById(userId, id);
}
complete(userId: string, id: string): Reminder | null {
const existing = this.getById(userId, id);
if (!existing) return null;
if (existing.frequency !== 'one_time') {
// Advance to next occurrence
const nextDate = this.advanceDate(existing.reminder_date, existing.frequency);
this.db.prepare(`
UPDATE reminders SET reminder_date = ?, status = 'active', updated_at = datetime('now')
WHERE id = ? AND deleted_at IS NULL
`).run(nextDate, id);
} else {
this.db.prepare(`
UPDATE reminders SET status = 'completed', updated_at = datetime('now')
WHERE id = ? AND deleted_at IS NULL
`).run(id);
}
return this.getById(userId, id);
}
snooze(userId: string, id: string, newDate: string): Reminder | null {
const existing = this.getById(userId, id);
if (!existing) return null;
this.db.prepare(`
UPDATE reminders SET status = 'snoozed', reminder_date = ?, updated_at = datetime('now')
WHERE id = ? AND deleted_at IS NULL
`).run(newDate, id);
return this.getById(userId, id);
}
dismiss(userId: string, id: string): boolean {
// Verify ownership via getById
const existing = this.getById(userId, id);
if (!existing) return false;
const result = this.db.prepare(`
UPDATE reminders SET status = 'dismissed', updated_at = datetime('now')
WHERE id = ? AND deleted_at IS NULL
`).run(id);
return result.changes > 0;
}
softDelete(userId: string, id: string): boolean {
// Verify ownership via getById
const existing = this.getById(userId, id);
if (!existing) return false;
const result = this.db.prepare(`
UPDATE reminders SET deleted_at = datetime('now'), updated_at = datetime('now')
WHERE id = ? AND deleted_at IS NULL
`).run(id);
return result.changes > 0;
}
restore(userId: string, id: string): Reminder {
// Find the deleted reminder and verify ownership through contact
const row = this.db.prepare(
`SELECT r.* FROM reminders r
JOIN contacts c ON r.contact_id = c.id
WHERE r.id = ? AND r.deleted_at IS NOT NULL AND c.user_id = ?`
).get(id, userId) as any;
if (!row) {
throw new Error('Reminder not found or not deleted');
}
this.db.prepare(`
UPDATE reminders SET deleted_at = NULL, updated_at = datetime('now')
WHERE id = ?
`).run(id);
return this.getById(userId, id)!;
}
list(userId: string, options: ListRemindersOptions = {}): PaginatedResult<Reminder> {
const page = options.page ?? 1;
const perPage = options.per_page ?? 20;
const offset = (page - 1) * perPage;
const conditions: string[] = ['c.user_id = ?'];
const params: any[] = [userId];
if (!options.include_deleted) {
conditions.push('r.deleted_at IS NULL');
}
conditions.push('c.deleted_at IS NULL');
if (options.contact_id) {
conditions.push('r.contact_id = ?');
params.push(options.contact_id);
}
if (options.status) {
conditions.push('r.status = ?');
params.push(options.status);
}
const whereClause = conditions.join(' AND ');
const countResult = this.db.prepare(
`SELECT COUNT(*) as count FROM reminders r JOIN contacts c ON r.contact_id = c.id WHERE ${whereClause}`
).get(...params) as any;
const rows = this.db.prepare(
`SELECT r.* FROM reminders r JOIN contacts c ON r.contact_id = c.id WHERE ${whereClause} ORDER BY r.reminder_date ASC LIMIT ? OFFSET ?`
).all(...params, perPage, offset) as any[];
const data = rows.map((r) => this.mapRow(r));
return { data, total: countResult.count, page, per_page: perPage };
}
/**
* Get overdue and upcoming reminders within a time window.
*/
getUpcoming(userId: string, daysAhead = 7): Reminder[] {
const futureDate = new Date(Date.now() + daysAhead * 86400000).toISOString().split('T')[0];
const rows = this.db.prepare(`
SELECT r.* FROM reminders r
JOIN contacts c ON r.contact_id = c.id
WHERE r.deleted_at IS NULL AND c.deleted_at IS NULL AND c.user_id = ?
AND r.status = 'active'
AND r.reminder_date <= ?
ORDER BY r.reminder_date ASC
`).all(userId, futureDate) as any[];
return rows.map((r) => this.mapRow(r));
}
/**
* Get upcoming reminders across all contacts with overdue detection.
*/
getUpcomingReminders(userId: string, options: {
days_ahead?: number;
status?: 'active' | 'snoozed';
include_overdue?: boolean;
} = {}): {
data: Array<{
id: string;
title: string;
description: string | null;
reminder_date: string;
frequency: string;
status: string;
is_overdue: boolean;
days_until: number;
contact_id: string;
contact_name: string;
}>;
total: number;
} {
const daysAhead = options.days_ahead ?? 14;
const status = options.status ?? 'active';
const includeOverdue = options.include_overdue !== false; // default true
const today = new Date();
const todayStr = today.toISOString().split('T')[0];
const futureDate = new Date(today.getTime() + daysAhead * 86400000).toISOString().split('T')[0];
const conditions: string[] = [
'r.deleted_at IS NULL',
'c.deleted_at IS NULL',
'c.user_id = ?',
'r.status = ?',
];
const params: any[] = [userId, status];
if (includeOverdue) {
// Include overdue (past) + upcoming within window
conditions.push('r.reminder_date <= ?');
params.push(futureDate);
} else {
// Only upcoming, not overdue
conditions.push('r.reminder_date >= ?');
conditions.push('r.reminder_date <= ?');
params.push(todayStr);
params.push(futureDate);
}
const whereClause = conditions.join(' AND ');
const rows = this.db.prepare(`
SELECT r.*, c.first_name, c.last_name
FROM reminders r
JOIN contacts c ON r.contact_id = c.id
WHERE ${whereClause}
ORDER BY r.reminder_date ASC
`).all(...params) as any[];
const data = rows.map((row) => {
const reminderDate = new Date(row.reminder_date + 'T00:00:00');
const todayMidnight = new Date(todayStr + 'T00:00:00');
const diffMs = reminderDate.getTime() - todayMidnight.getTime();
const daysUntil = Math.round(diffMs / 86400000);
const contactName = [row.first_name, row.last_name].filter(Boolean).join(' ');
return {
id: row.id,
title: row.title,
description: row.description,
reminder_date: row.reminder_date,
frequency: row.frequency,
status: row.status,
is_overdue: daysUntil < 0,
days_until: daysUntil,
contact_id: row.contact_id,
contact_name: contactName,
};
});
return { data, total: data.length };
}
/**
* Create an auto-generated birthday reminder for a contact.
*/
createBirthdayReminder(userId: string, contactId: string, contactName: string, birthdayDate: string): Reminder {
return this.create(userId, {
contact_id: contactId,
title: `${contactName}'s birthday`,
reminder_date: birthdayDate,
frequency: 'yearly',
}, true);
}
/**
* Remove auto-generated reminders for a contact.
*/
removeAutoReminders(userId: string, contactId: string): number {
// Verify the contact belongs to the user
this.verifyContactOwnership(userId, contactId);
const result = this.db.prepare(`
UPDATE reminders SET deleted_at = datetime('now'), updated_at = datetime('now')
WHERE contact_id = ? AND is_auto_generated = 1 AND deleted_at IS NULL
`).run(contactId);
return result.changes;
}
private verifyContactOwnership(userId: string, contactId: string): void {
const contact = this.db.prepare(
'SELECT id FROM contacts WHERE id = ? AND user_id = ? AND deleted_at IS NULL'
).get(contactId, userId) as any;
if (!contact) {
throw new Error('Contact not found');
}
}
private getById(userId: string, id: string): Reminder | null {
const row = this.db.prepare(
`SELECT r.* FROM reminders r
JOIN contacts c ON r.contact_id = c.id
WHERE r.id = ? AND r.deleted_at IS NULL AND c.deleted_at IS NULL AND c.user_id = ?`
).get(id, userId) as any;
if (!row) return null;
return this.mapRow(row);
}
private mapRow(row: any): Reminder {
return {
...row,
is_auto_generated: Boolean(row.is_auto_generated),
};
}
private advanceDate(dateStr: string, frequency: ReminderFrequency): string {
const date = new Date(dateStr + 'T00:00:00Z');
switch (frequency) {
case 'weekly':
date.setDate(date.getDate() + 7);
break;
case 'monthly':
date.setMonth(date.getMonth() + 1);
break;
case 'yearly':
date.setFullYear(date.getFullYear() + 1);
break;
default:
break;
}
return date.toISOString().split('T')[0];
}
}
