summaryrefslogtreecommitdiff
path: root/packages/db/src/users.ts
diff options
context:
space:
mode:
Diffstat (limited to 'packages/db/src/users.ts')
-rw-r--r--packages/db/src/users.ts305
1 files changed, 305 insertions, 0 deletions
diff --git a/packages/db/src/users.ts b/packages/db/src/users.ts
new file mode 100644
index 0000000..ebf715c
--- /dev/null
+++ b/packages/db/src/users.ts
@@ -0,0 +1,305 @@
+import { Database } from "bun:sqlite";
+
+export interface User {
+ id: number;
+ name: string;
+ creds: string;
+}
+
+export interface Session {
+ id: string;
+ user_id: number;
+ created_at: number;
+ expires_at: number;
+ last_activity?: number;
+ ip_address?: string;
+ user_agent?: string;
+ data?: any;
+}
+
+export interface UserSettings {
+ id?: number;
+ user_id: number;
+ preferred_session_length: number;
+ target_daily_reviews: number;
+ difficulty_preference: number;
+ audio_enabled: boolean;
+ tone_visualization_enabled: boolean;
+ auto_advance: boolean;
+ created_at?: number;
+ updated_at?: number;
+}
+
+export class UserQueries {
+ constructor(private db: Database) {}
+
+ /**
+ * Create a new user
+ */
+ createUser(name: string, creds: string): User {
+ const query = this.db.query(`
+ INSERT INTO users (name, creds)
+ VALUES (?, ?)
+ RETURNING id, name, creds
+ `);
+
+ return query.get(name, creds) as User;
+ }
+
+ /**
+ * Get user by ID
+ */
+ getUserById(id: number): User | null {
+ const query = this.db.query(`
+ SELECT id, name, creds
+ FROM users
+ WHERE id = ?
+ `);
+
+ return query.get(id) as User | null;
+ }
+
+ /**
+ * Get user by name
+ */
+ getUserByName(name: string): User | null {
+ const query = this.db.query(`
+ SELECT id, name, creds
+ FROM users
+ WHERE name = ?
+ `);
+
+ return query.get(name) as User | null;
+ }
+
+ /**
+ * Create a new session for a user
+ */
+ createSession(userId: number, sessionToken: string, expiresAt: number): Session {
+ const query = this.db.query(`
+ INSERT INTO sessions (id, user_id, created_at, expires_at, last_activity)
+ VALUES (?, ?, ?, ?, ?)
+ RETURNING id, user_id, created_at, expires_at, last_activity, ip_address, user_agent, data
+ `);
+
+ const now = Date.now();
+ return query.get(sessionToken, userId, now, expiresAt, now) as Session;
+ }
+
+ /**
+ * Get session by token
+ */
+ getSessionByToken(sessionToken: string): Session | null {
+ const query = this.db.query(`
+ SELECT id, user_id, created_at, expires_at, last_activity, ip_address, user_agent, data
+ FROM sessions
+ WHERE id = ? AND expires_at > ?
+ `);
+
+ return query.get(sessionToken, Date.now()) as Session | null;
+ }
+
+ /**
+ * Get user from session token
+ */
+ getUserFromSession(sessionToken: string): User | null {
+ const query = this.db.query(`
+ SELECT u.id, u.name, u.creds
+ FROM users u
+ JOIN sessions s ON u.id = s.user_id
+ WHERE s.id = ? AND s.expires_at > ?
+ `);
+
+ return query.get(sessionToken, Date.now()) as User | null;
+ }
+
+ /**
+ * Delete session by token
+ */
+ deleteSession(sessionToken: string): boolean {
+ const query = this.db.query(`
+ DELETE FROM sessions
+ WHERE id = ?
+ `);
+
+ const result = query.run(sessionToken);
+ return result.changes > 0;
+ }
+
+ /**
+ * Clean up expired sessions
+ */
+ cleanupExpiredSessions(): number {
+ const query = this.db.query(`
+ DELETE FROM sessions
+ WHERE expires_at <= ?
+ `);
+
+ const result = query.run(Date.now());
+ return result.changes;
+ }
+
+ /**
+ * Update session last activity
+ */
+ updateSessionActivity(sessionToken: string): boolean {
+ const query = this.db.query(`
+ UPDATE sessions
+ SET last_activity = ?
+ WHERE id = ? AND expires_at > ?
+ `);
+
+ const result = query.run(Date.now(), sessionToken, Date.now());
+ return result.changes > 0;
+ }
+
+ /**
+ * Extend session expiry
+ */
+ extendSessionExpiry(sessionToken: string, newExpiry: number): boolean {
+ const query = this.db.query(`
+ UPDATE sessions
+ SET expires_at = ?, last_activity = ?
+ WHERE id = ? AND expires_at > ?
+ `);
+
+ const result = query.run(newExpiry, Date.now(), sessionToken, Date.now());
+ return result.changes > 0;
+ }
+
+ /**
+ * Get all active sessions for a user
+ */
+ getUserSessions(userId: number): Session[] {
+ const query = this.db.query(`
+ SELECT id, user_id, created_at, expires_at, last_activity, ip_address, user_agent, data
+ FROM sessions
+ WHERE user_id = ? AND expires_at > ?
+ ORDER BY last_activity DESC
+ `);
+
+ return query.all(userId, Date.now()) as Session[];
+ }
+
+ /**
+ * Delete all sessions for a user (for logout all devices)
+ */
+ deleteAllUserSessions(userId: number): number {
+ const query = this.db.query(`
+ DELETE FROM sessions
+ WHERE user_id = ?
+ `);
+
+ const result = query.run(userId);
+ return result.changes;
+ }
+
+ /**
+ * Get user settings
+ */
+ getUserSettings(userId: number): UserSettings | null {
+ const query = this.db.query(`
+ SELECT id, user_id, preferred_session_length, target_daily_reviews,
+ difficulty_preference, audio_enabled, tone_visualization_enabled,
+ auto_advance, created_at, updated_at
+ FROM tone_learning_settings
+ WHERE user_id = ?
+ `);
+
+ return query.get(userId) as UserSettings | null;
+ }
+
+ /**
+ * Create or update user settings
+ */
+ upsertUserSettings(
+ settings: Omit<UserSettings, "id" | "created_at" | "updated_at">,
+ ): UserSettings {
+ const query = this.db.query(`
+ INSERT INTO tone_learning_settings
+ (user_id, preferred_session_length, target_daily_reviews,
+ difficulty_preference, audio_enabled, tone_visualization_enabled, auto_advance)
+ VALUES (?, ?, ?, ?, ?, ?, ?)
+ ON CONFLICT (user_id) DO UPDATE SET
+ preferred_session_length = excluded.preferred_session_length,
+ target_daily_reviews = excluded.target_daily_reviews,
+ difficulty_preference = excluded.difficulty_preference,
+ audio_enabled = excluded.audio_enabled,
+ tone_visualization_enabled = excluded.tone_visualization_enabled,
+ auto_advance = excluded.auto_advance,
+ updated_at = (strftime('%s', 'now') * 1000)
+ RETURNING id, user_id, preferred_session_length, target_daily_reviews,
+ difficulty_preference, audio_enabled, tone_visualization_enabled,
+ auto_advance, created_at, updated_at
+ `);
+
+ return query.get(
+ settings.user_id,
+ settings.preferred_session_length,
+ settings.target_daily_reviews,
+ settings.difficulty_preference,
+ settings.audio_enabled,
+ settings.tone_visualization_enabled,
+ settings.auto_advance,
+ ) as UserSettings;
+ }
+
+ /**
+ * Update user settings partially
+ */
+ updateUserSettings(
+ userId: number,
+ updates: Partial<UserSettings>,
+ ): UserSettings | null {
+ const setClause = Object.keys(updates)
+ .filter(
+ (key) => key !== "id" && key !== "user_id" && key !== "created_at",
+ )
+ .map((key) => `${key} = ?`)
+ .join(", ");
+
+ if (!setClause) return null;
+
+ const values = Object.values(updates).filter((_, index) => {
+ const key = Object.keys(updates)[index];
+ return key !== "id" && key !== "user_id" && key !== "created_at";
+ });
+
+ const query = this.db.query(`
+ UPDATE tone_learning_settings
+ SET ${setClause}, updated_at = (strftime('%s', 'now') * 1000)
+ WHERE user_id = ?
+ RETURNING id, user_id, preferred_session_length, target_daily_reviews,
+ difficulty_preference, audio_enabled, tone_visualization_enabled,
+ auto_advance, created_at, updated_at
+ `);
+
+ return query.get(...values, userId) as UserSettings | null;
+ }
+
+ /**
+ * Get all users (admin function)
+ */
+ getAllUsers(): User[] {
+ const query = this.db.query(`
+ SELECT id, name, creds
+ FROM users
+ ORDER BY name
+ `);
+
+ return query.all() as User[];
+ }
+
+ /**
+ * Delete user (admin function)
+ */
+ deleteUser(userId: number): boolean {
+ const query = this.db.query(`
+ DELETE FROM users
+ WHERE id = ?
+ `);
+
+ const result = query.run(userId);
+ return result.changes > 0;
+ }
+}
generated by cgit v1.2.3 (git 2.25.1) at 2025-11-25 23:19:41 +0000