Files
Emailsorter/server/services/database.mjs
ANDJ 6da8ce1cbd huhuih
hzgjuigik
2026-01-27 21:06:48 +01:00

731 lines
19 KiB
JavaScript

/**
* Database Service
* Centralized Appwrite database operations
*/
import { Client, Databases, Query, ID } from 'node-appwrite'
import { config } from '../config/index.mjs'
import { NotFoundError } from '../middleware/errorHandler.mjs'
// Initialize Appwrite client
const client = new Client()
.setEndpoint(config.appwrite.endpoint)
.setProject(config.appwrite.projectId)
.setKey(config.appwrite.apiKey)
const databases = new Databases(client)
const DB_ID = config.appwrite.databaseId
/**
* Collection names
*/
export const Collections = {
PRODUCTS: 'products',
QUESTIONS: 'questions',
SUBMISSIONS: 'submissions',
ANSWERS: 'answers',
ORDERS: 'orders',
EMAIL_ACCOUNTS: 'email_accounts',
EMAIL_STATS: 'email_stats',
EMAIL_DIGESTS: 'email_digests',
SUBSCRIPTIONS: 'subscriptions',
USER_PREFERENCES: 'user_preferences',
ONBOARDING_STATE: 'onboarding_state',
EMAIL_USAGE: 'email_usage',
REFERRALS: 'referrals',
ANALYTICS_EVENTS: 'analytics_events',
}
/**
* Generic database operations
*/
export const db = {
/**
* Create a document
*/
async create(collection, data, id = ID.unique()) {
return await databases.createDocument(DB_ID, collection, id, data)
},
/**
* Get a document by ID
*/
async get(collection, id) {
try {
return await databases.getDocument(DB_ID, collection, id)
} catch (error) {
if (error.code === 404) {
throw new NotFoundError(collection)
}
throw error
}
},
/**
* Update a document
*/
async update(collection, id, data) {
return await databases.updateDocument(DB_ID, collection, id, data)
},
/**
* Delete a document
*/
async delete(collection, id) {
return await databases.deleteDocument(DB_ID, collection, id)
},
/**
* List documents with optional queries
*/
async list(collection, queries = []) {
const response = await databases.listDocuments(DB_ID, collection, queries)
return response.documents
},
/**
* Find one document matching queries
*/
async findOne(collection, queries) {
const docs = await this.list(collection, [...queries, Query.limit(1)])
return docs[0] || null
},
/**
* Check if document exists
*/
async exists(collection, id) {
try {
await databases.getDocument(DB_ID, collection, id)
return true
} catch {
return false
}
},
/**
* Count documents
*/
async count(collection, queries = []) {
const response = await databases.listDocuments(DB_ID, collection, [
...queries,
Query.limit(1),
])
return response.total
},
}
/**
* Product operations
*/
export const products = {
async getBySlug(slug) {
return db.findOne(Collections.PRODUCTS, [
Query.equal('slug', slug),
Query.equal('isActive', true),
])
},
async getActive() {
return db.list(Collections.PRODUCTS, [Query.equal('isActive', true)])
},
}
/**
* Questions operations
*/
export const questions = {
async getByProduct(productId) {
return db.list(Collections.QUESTIONS, [
Query.equal('productId', productId),
Query.equal('isActive', true),
Query.orderAsc('step'),
Query.orderAsc('order'),
])
},
}
/**
* Submissions operations
*/
export const submissions = {
async create(data) {
return db.create(Collections.SUBMISSIONS, data)
},
async updateStatus(id, status) {
return db.update(Collections.SUBMISSIONS, id, { status })
},
async getByUser(userId) {
return db.list(Collections.SUBMISSIONS, [Query.equal('userId', userId)])
},
}
/**
* Email accounts operations
*/
export const emailAccounts = {
async create(data) {
return db.create(Collections.EMAIL_ACCOUNTS, data)
},
async getByUser(userId) {
return db.list(Collections.EMAIL_ACCOUNTS, [
Query.equal('userId', userId),
Query.equal('isActive', true),
])
},
async get(id) {
return db.get(Collections.EMAIL_ACCOUNTS, id)
},
async updateLastSync(id) {
return db.update(Collections.EMAIL_ACCOUNTS, id, {
lastSync: new Date().toISOString(),
})
},
async deactivate(id) {
return db.update(Collections.EMAIL_ACCOUNTS, id, { isActive: false })
},
}
/**
* Email stats operations
*/
export const emailStats = {
async getByUser(userId) {
return db.findOne(Collections.EMAIL_STATS, [Query.equal('userId', userId)])
},
async create(userId, data) {
return db.create(Collections.EMAIL_STATS, { userId, ...data })
},
async increment(userId, counts) {
const stats = await this.getByUser(userId)
if (stats) {
return db.update(Collections.EMAIL_STATS, stats.$id, {
totalSorted: (stats.totalSorted || 0) + (counts.total || 0),
todaySorted: (stats.todaySorted || 0) + (counts.today || 0),
weekSorted: (stats.weekSorted || 0) + (counts.week || 0),
timeSavedMinutes: (stats.timeSavedMinutes || 0) + (counts.timeSaved || 0),
})
} else {
return this.create(userId, {
totalSorted: counts.total || 0,
todaySorted: counts.today || 0,
weekSorted: counts.week || 0,
timeSavedMinutes: counts.timeSaved || 0,
categoriesJson: '{}',
})
}
},
async updateCategories(userId, categories) {
const stats = await this.getByUser(userId)
if (stats) {
return db.update(Collections.EMAIL_STATS, stats.$id, {
categoriesJson: JSON.stringify(categories),
})
}
return null
},
async resetDaily() {
// Reset daily counters - would be called by a cron job
const allStats = await db.list(Collections.EMAIL_STATS, [])
for (const stat of allStats) {
await db.update(Collections.EMAIL_STATS, stat.$id, { todaySorted: 0 })
}
},
async resetWeekly() {
// Reset weekly counters - would be called by a cron job
const allStats = await db.list(Collections.EMAIL_STATS, [])
for (const stat of allStats) {
await db.update(Collections.EMAIL_STATS, stat.$id, {
weekSorted: 0,
categoriesJson: '{}',
})
}
},
}
/**
* Email usage operations
*/
export const emailUsage = {
async getCurrentMonth(userId) {
const month = new Date().toISOString().slice(0, 7) // "2026-01"
return db.findOne(Collections.EMAIL_USAGE, [
Query.equal('userId', userId),
Query.equal('month', month),
])
},
async increment(userId, count) {
const month = new Date().toISOString().slice(0, 7)
const existing = await this.getCurrentMonth(userId)
if (existing) {
return db.update(Collections.EMAIL_USAGE, existing.$id, {
emailsProcessed: (existing.emailsProcessed || 0) + count,
lastReset: new Date().toISOString(),
})
}
return db.create(Collections.EMAIL_USAGE, {
userId,
month,
emailsProcessed: count,
lastReset: new Date().toISOString(),
})
},
async getUsage(userId) {
const usage = await this.getCurrentMonth(userId)
return {
emailsProcessed: usage?.emailsProcessed || 0,
month: new Date().toISOString().slice(0, 7),
}
},
}
/**
* Subscriptions operations
*/
export const subscriptions = {
async getByUser(userId) {
const subscription = await db.findOne(Collections.SUBSCRIPTIONS, [Query.equal('userId', userId)])
// If no subscription, user is on free tier
if (!subscription) {
const usage = await emailUsage.getUsage(userId)
return {
plan: 'free',
status: 'active',
isFreeTier: true,
emailsUsedThisMonth: usage.emailsProcessed,
emailsLimit: 500, // From config
}
}
// Check if subscription is active
const isActive = subscription.status === 'active'
const isFreeTier = !isActive || subscription.plan === 'free'
// Get usage for free tier users
let emailsUsedThisMonth = 0
let emailsLimit = -1 // Unlimited for paid
if (isFreeTier) {
const usage = await emailUsage.getUsage(userId)
emailsUsedThisMonth = usage.emailsProcessed
emailsLimit = 500 // From config
}
return {
...subscription,
plan: subscription.plan || 'free',
isFreeTier,
emailsUsedThisMonth,
emailsLimit,
}
},
async getByStripeId(stripeSubscriptionId) {
return db.findOne(Collections.SUBSCRIPTIONS, [
Query.equal('stripeSubscriptionId', stripeSubscriptionId),
])
},
async create(data) {
return db.create(Collections.SUBSCRIPTIONS, data)
},
async update(id, data) {
return db.update(Collections.SUBSCRIPTIONS, id, data)
},
async upsertByUser(userId, data) {
const existing = await this.getByUser(userId)
if (existing) {
return this.update(existing.$id, data)
}
return this.create({ userId, ...data })
},
}
/**
* User preferences operations
*/
export const userPreferences = {
/**
* Get default preferences structure
*/
getDefaults() {
return {
vipSenders: [],
enabledCategories: ['vip', 'customers', 'invoices', 'newsletters', 'promotions', 'social', 'security', 'calendar', 'review'],
categoryActions: {},
companyLabels: [],
autoDetectCompanies: true,
version: 1,
categoryAdvanced: {},
cleanup: {
enabled: false,
readItems: {
enabled: false,
action: 'archive_read',
gracePeriodDays: 7,
},
promotions: {
enabled: false,
matchCategoriesOrLabels: ['promotions', 'newsletters'],
action: 'archive_read',
deleteAfterDays: 30,
},
safety: {
requireConfirmForDelete: true,
dryRun: false,
maxDeletesPerRun: 100,
},
},
}
},
/**
* Merge preferences with defaults
*/
mergeWithDefaults(preferences) {
const defaults = this.getDefaults()
return {
...defaults,
...preferences,
vipSenders: preferences.vipSenders || defaults.vipSenders,
enabledCategories: preferences.enabledCategories || defaults.enabledCategories,
categoryActions: preferences.categoryActions || defaults.categoryActions,
companyLabels: preferences.companyLabels || defaults.companyLabels,
autoDetectCompanies: preferences.autoDetectCompanies !== undefined ? preferences.autoDetectCompanies : defaults.autoDetectCompanies,
}
},
async getByUser(userId) {
const pref = await db.findOne(Collections.USER_PREFERENCES, [
Query.equal('userId', userId),
])
if (pref?.preferencesJson) {
const parsed = JSON.parse(pref.preferencesJson)
return { ...pref, preferences: this.mergeWithDefaults(parsed) }
}
return { ...pref, preferences: this.getDefaults() }
},
async upsert(userId, preferences) {
const existing = await db.findOne(Collections.USER_PREFERENCES, [
Query.equal('userId', userId),
])
// Merge with existing preferences if updating
let mergedPreferences = preferences
if (existing?.preferencesJson) {
const existingPrefs = JSON.parse(existing.preferencesJson)
mergedPreferences = { ...existingPrefs, ...preferences }
}
const data = { preferencesJson: JSON.stringify(mergedPreferences) }
if (existing) {
return db.update(Collections.USER_PREFERENCES, existing.$id, data)
}
return db.create(Collections.USER_PREFERENCES, { userId, ...data })
},
}
/**
* Onboarding state operations
*/
export const onboardingState = {
async getByUser(userId) {
const state = await db.findOne(Collections.ONBOARDING_STATE, [
Query.equal('userId', userId),
])
if (state?.completed_steps_json) {
return {
...state,
completedSteps: JSON.parse(state.completed_steps_json),
}
}
return {
...state,
completedSteps: [],
onboarding_step: state?.onboarding_step || 'not_started',
}
},
async updateStep(userId, step, completedSteps = []) {
const existing = await db.findOne(Collections.ONBOARDING_STATE, [
Query.equal('userId', userId),
])
const data = {
onboarding_step: step,
completed_steps_json: JSON.stringify(completedSteps),
last_updated: new Date().toISOString(),
}
if (existing) {
return db.update(Collections.ONBOARDING_STATE, existing.$id, data)
}
return db.create(Collections.ONBOARDING_STATE, { userId, ...data })
},
async markValueSeen(userId) {
const existing = await db.findOne(Collections.ONBOARDING_STATE, [
Query.equal('userId', userId),
])
const data = {
first_value_seen_at: new Date().toISOString(),
last_updated: new Date().toISOString(),
}
if (existing) {
return db.update(Collections.ONBOARDING_STATE, existing.$id, data)
}
return db.create(Collections.ONBOARDING_STATE, {
userId,
onboarding_step: 'see_results',
completed_steps_json: JSON.stringify(['connect', 'first_rule', 'see_results']),
...data,
})
},
async skip(userId) {
const existing = await db.findOne(Collections.ONBOARDING_STATE, [
Query.equal('userId', userId),
])
const data = {
skipped_at: new Date().toISOString(),
last_updated: new Date().toISOString(),
}
if (existing) {
return db.update(Collections.ONBOARDING_STATE, existing.$id, data)
}
return db.create(Collections.ONBOARDING_STATE, {
userId,
onboarding_step: 'not_started',
completed_steps_json: JSON.stringify([]),
...data,
})
},
async resume(userId) {
const existing = await db.findOne(Collections.ONBOARDING_STATE, [
Query.equal('userId', userId),
])
if (existing) {
return db.update(Collections.ONBOARDING_STATE, existing.$id, {
skipped_at: null,
last_updated: new Date().toISOString(),
})
}
// If no state exists, create initial state
return db.create(Collections.ONBOARDING_STATE, {
userId,
onboarding_step: 'connect',
completed_steps_json: JSON.stringify([]),
last_updated: new Date().toISOString(),
})
},
}
/**
* Referrals operations
*/
export const referrals = {
async getOrCreateCode(userId) {
const existing = await db.findOne(Collections.REFERRALS, [
Query.equal('userId', userId),
])
if (existing) {
return existing
}
// Generate unique code: USER-ABC123
const randomPart = Math.random().toString(36).substring(2, 8).toUpperCase()
const code = `USER-${randomPart}`
// Ensure uniqueness
let uniqueCode = code
let attempts = 0
while (attempts < 10) {
const existingCode = await db.findOne(Collections.REFERRALS, [
Query.equal('referralCode', uniqueCode),
])
if (!existingCode) break
uniqueCode = `USER-${Math.random().toString(36).substring(2, 8).toUpperCase()}`
attempts++
}
return db.create(Collections.REFERRALS, {
userId,
referralCode: uniqueCode,
referralCount: 0,
createdAt: new Date().toISOString(),
})
},
async getByCode(code) {
return db.findOne(Collections.REFERRALS, [
Query.equal('referralCode', code),
])
},
async incrementCount(userId) {
const referral = await db.findOne(Collections.REFERRALS, [
Query.equal('userId', userId),
])
if (referral) {
return db.update(Collections.REFERRALS, referral.$id, {
referralCount: (referral.referralCount || 0) + 1,
})
}
return null
},
async getReferrals(userId) {
return db.list(Collections.REFERRALS, [
Query.equal('referredBy', userId),
])
},
}
/**
* Orders operations
*/
export const orders = {
async create(submissionId, orderData) {
return db.create(Collections.ORDERS, {
submissionId,
orderDataJson: JSON.stringify(orderData),
})
},
}
/**
* Email digests operations
*/
export const emailDigests = {
async create(userId, digestData) {
const date = new Date().toISOString().split('T')[0] // YYYY-MM-DD
return db.create(Collections.EMAIL_DIGESTS, {
userId,
date,
statsJson: JSON.stringify(digestData.stats || {}),
highlightsJson: JSON.stringify(digestData.highlights || []),
suggestionsJson: JSON.stringify(digestData.suggestions || []),
totalSorted: digestData.totalSorted || 0,
inboxCleared: digestData.inboxCleared || 0,
timeSavedMinutes: digestData.timeSavedMinutes || 0,
createdAt: new Date().toISOString(),
})
},
async getByUserToday(userId) {
const today = new Date().toISOString().split('T')[0]
const digest = await db.findOne(Collections.EMAIL_DIGESTS, [
Query.equal('userId', userId),
Query.equal('date', today),
])
if (digest) {
return {
...digest,
stats: JSON.parse(digest.statsJson || '{}'),
highlights: JSON.parse(digest.highlightsJson || '[]'),
suggestions: JSON.parse(digest.suggestionsJson || '[]'),
}
}
return null
},
async getByUserRecent(userId, days = 7) {
const startDate = new Date()
startDate.setDate(startDate.getDate() - days)
const digests = await db.list(Collections.EMAIL_DIGESTS, [
Query.equal('userId', userId),
Query.greaterThanEqual('date', startDate.toISOString().split('T')[0]),
Query.orderDesc('date'),
])
return digests.map(d => ({
...d,
stats: JSON.parse(d.statsJson || '{}'),
highlights: JSON.parse(d.highlightsJson || '[]'),
suggestions: JSON.parse(d.suggestionsJson || '[]'),
}))
},
async updateToday(userId, updates) {
const today = new Date().toISOString().split('T')[0]
const existing = await db.findOne(Collections.EMAIL_DIGESTS, [
Query.equal('userId', userId),
Query.equal('date', today),
])
if (existing) {
const updateData = {}
if (updates.stats) updateData.statsJson = JSON.stringify(updates.stats)
if (updates.highlights) updateData.highlightsJson = JSON.stringify(updates.highlights)
if (updates.suggestions) updateData.suggestionsJson = JSON.stringify(updates.suggestions)
if (updates.totalSorted !== undefined) {
updateData.totalSorted = (existing.totalSorted || 0) + updates.totalSorted
}
if (updates.inboxCleared !== undefined) {
updateData.inboxCleared = (existing.inboxCleared || 0) + updates.inboxCleared
}
if (updates.timeSavedMinutes !== undefined) {
updateData.timeSavedMinutes = (existing.timeSavedMinutes || 0) + updates.timeSavedMinutes
}
return db.update(Collections.EMAIL_DIGESTS, existing.$id, updateData)
}
// Create new digest for today
return this.create(userId, {
stats: updates.stats || {},
highlights: updates.highlights || [],
suggestions: updates.suggestions || [],
totalSorted: updates.totalSorted || 0,
inboxCleared: updates.inboxCleared || 0,
timeSavedMinutes: updates.timeSavedMinutes || 0,
})
},
}
export { Query }
export default {
db,
products,
questions,
submissions,
emailAccounts,
emailStats,
emailDigests,
subscriptions,
userPreferences,
orders,
Collections,
}