diff options
Diffstat (limited to 'packages/db/src/srs.ts')
| -rw-r--r-- | packages/db/src/srs.ts | 402 |
1 files changed, 402 insertions, 0 deletions
diff --git a/packages/db/src/srs.ts b/packages/db/src/srs.ts new file mode 100644 index 0000000..0b80960 --- /dev/null +++ b/packages/db/src/srs.ts @@ -0,0 +1,402 @@ +import { Database } from 'bun:sqlite'; + +// Traditional SRS Types +export interface SRSProgress { + id?: number; + user_id: number; + card_id: number; + repetition_count: number; + ease_factor: number; + interval: number; + next_review_date: number | null; + last_reviewed: number | null; + is_mastered: boolean; + created_at?: number; + updated_at?: number; +} + +export interface SRSAttempt { + id?: number; + user_id: number; + timestamp: number; + card_id: number; + good: number; // 0 or 1 + created_at?: number; +} + +// Tone SRS Types +export interface ToneProgress { + id?: number; + user_id: number; + word_id: number; + tone_sequence: string; + syllable_sequence: string; + repetition_count: number; + ease_factor: number; + interval: number; + next_review_date: number; + last_reviewed: number; + is_mastered: boolean; + difficulty: number; + tone_accuracy: number; + created_at?: number; + updated_at?: number; +} + +export interface ToneAttempt { + id?: number; + user_id: number; + word_id: number; + timestamp: number; + accuracy: number; + tone_accuracy: number; + pronunciation_score: number; + review_time: number; + difficulty: number; + mode: 'exploration' | 'practice' | 'review'; + created_at?: number; +} + +export interface ToneMasteryStats { + id?: number; + user_id: number; + tone_pattern: string; + syllable_count: number; + total_attempts: number; + successful_attempts: number; + avg_accuracy: number; + avg_review_time: number; + best_accuracy: number; + last_attempted: number | null; + mastered_at: number | null; + created_at?: number; + updated_at?: number; +} + +export class SRSQueries { + constructor(private db: Database) {} + + // Traditional SRS Methods + + /** + * Get or create SRS progress for a user and card + */ + getOrCreateSRSProgress(userId: number, cardId: number): SRSProgress { + const query = this.db.query(` + INSERT INTO user_progress (user_id, card_id) + VALUES (?, ?) + ON CONFLICT (user_id, card_id) DO UPDATE SET + user_id = excluded.user_id, + card_id = excluded.card_id + RETURNING id, user_id, card_id, repetition_count, ease_factor, interval, + next_review_date, last_reviewed, is_mastered, created_at, updated_at + `); + + return query.get(userId, cardId) as SRSProgress; + } + + /** + * Update SRS progress after a review + */ + updateSRSProgress(progress: Omit<SRSProgress, 'id' | 'created_at' | 'updated_at'>): SRSProgress { + const query = this.db.query(` + UPDATE user_progress + SET repetition_count = ?, + ease_factor = ?, + interval = ?, + next_review_date = ?, + last_reviewed = ?, + is_mastered = ?, + updated_at = (strftime('%s', 'now') * 1000) + WHERE user_id = ? AND card_id = ? + RETURNING id, user_id, card_id, repetition_count, ease_factor, interval, + next_review_date, last_reviewed, is_mastered, created_at, updated_at + `); + + return query.get( + progress.repetition_count, + progress.ease_factor, + progress.interval, + progress.next_review_date, + progress.last_reviewed, + progress.is_mastered, + progress.user_id, + progress.card_id + ) as SRSProgress; + } + + /** + * Record a traditional SRS attempt + */ + recordSRSAttempt(attempt: Omit<SRSAttempt, 'id' | 'created_at'>): SRSAttempt { + const query = this.db.query(` + INSERT INTO attempts (user_id, timestamp, card_id, good) + VALUES (?, ?, ?, ?) + RETURNING id, user_id, timestamp, card_id, good, created_at + `); + + return query.get( + attempt.user_id, + attempt.timestamp, + attempt.card_id, + attempt.good + ) as SRSAttempt; + } + + /** + * Get due cards for a user + */ + getDueCards(userId: number, limit: number = 20): number[] { + const query = this.db.query(` + SELECT card_id + FROM user_progress + WHERE user_id = ? + AND (next_review_date IS NULL OR next_review_date <= ?) + AND is_mastered = 0 + ORDER BY next_review_date ASC + LIMIT ? + `); + + const results = query.all(userId, Date.now(), limit) as { card_id: number }[]; + return results.map(r => r.card_id); + } + + /** + * Get SRS progress statistics for a user + */ + getSRSStats(userId: number) { + const query = this.db.query(` + SELECT + COUNT(*) as total_cards, + COUNT(CASE WHEN is_mastered = 1 THEN 1 END) as mastered_cards, + COUNT(CASE WHEN next_review_date <= ? THEN 1 END) as due_cards, + COUNT(CASE WHEN next_review_date > ? THEN 1 END) as future_cards, + AVG(ease_factor) as avg_ease_factor, + AVG(interval) as avg_interval + FROM user_progress + WHERE user_id = ? + `); + + return query.get(Date.now(), Date.now(), userId); + } + + // Tone SRS Methods + + /** + * Get or create tone progress for a user and word + */ + getOrCreateToneProgress(userId: number, wordId: number, toneSequence: string, syllableSequence: string): ToneProgress { + const query = this.db.query(` + INSERT INTO tone_pattern_progress (user_id, word_id, tone_sequence, syllable_sequence, next_review_date, last_reviewed) + VALUES (?, ?, ?, ?, ?, ?) + ON CONFLICT (user_id, word_id) DO UPDATE SET + tone_sequence = excluded.tone_sequence, + syllable_sequence = excluded.syllable_sequence + RETURNING id, user_id, word_id, tone_sequence, syllable_sequence, repetition_count, + ease_factor, interval, next_review_date, last_reviewed, is_mastered, + difficulty, tone_accuracy, created_at, updated_at + `); + + return query.get( + userId, + wordId, + toneSequence, + syllableSequence, + Date.now(), // next_review_date + Date.now() // last_reviewed + ) as ToneProgress; + } + + /** + * Update tone progress after a practice session + */ + updateToneProgress(progress: Omit<ToneProgress, 'id' | 'created_at' | 'updated_at'>): ToneProgress { + const query = this.db.query(` + UPDATE tone_pattern_progress + SET repetition_count = ?, + ease_factor = ?, + interval = ?, + next_review_date = ?, + last_reviewed = ?, + is_mastered = ?, + difficulty = ?, + tone_accuracy = ?, + updated_at = (strftime('%s', 'now') * 1000) + WHERE user_id = ? AND word_id = ? + RETURNING id, user_id, word_id, tone_sequence, syllable_sequence, repetition_count, + ease_factor, interval, next_review_date, last_reviewed, is_mastered, + difficulty, tone_accuracy, created_at, updated_at + `); + + return query.get( + progress.repetition_count, + progress.ease_factor, + progress.interval, + progress.next_review_date, + progress.last_reviewed, + progress.is_mastered, + progress.difficulty, + progress.tone_accuracy, + progress.user_id, + progress.word_id + ) as ToneProgress; + } + + /** + * Record a tone attempt + */ + recordToneAttempt(attempt: Omit<ToneAttempt, 'id' | 'created_at'>): ToneAttempt { + const query = this.db.query(` + INSERT INTO tone_attempts (user_id, word_id, timestamp, accuracy, tone_accuracy, + pronunciation_score, review_time, difficulty, mode) + VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) + RETURNING id, user_id, word_id, timestamp, accuracy, tone_accuracy, + pronunciation_score, review_time, difficulty, mode, created_at + `); + + return query.get( + attempt.user_id, + attempt.word_id, + attempt.timestamp, + attempt.accuracy, + attempt.tone_accuracy, + attempt.pronunciation_score, + attempt.review_time, + attempt.difficulty, + attempt.mode + ) as ToneAttempt; + } + + /** + * Get due tone words for a user + */ + getDueToneWords(userId: number, limit: number = 20): number[] { + const query = this.db.query(` + SELECT word_id + FROM tone_pattern_progress + WHERE user_id = ? + AND next_review_date <= ? + AND is_mastered = 0 + ORDER BY next_review_date ASC, difficulty ASC + LIMIT ? + `); + + const results = query.all(userId, Date.now(), limit) as { word_id: number }[]; + return results.map(r => r.word_id); + } + + /** + * Get tone progress statistics for a user + */ + getToneStats(userId: number) { + const query = this.db.query(` + SELECT + COUNT(*) as total_tone_words, + COUNT(CASE WHEN is_mastered = 1 THEN 1 END) as mastered_tone_words, + COUNT(CASE WHEN next_review_date <= ? THEN 1 END) as due_tone_words, + AVG(difficulty) as avg_difficulty, + AVG(tone_accuracy) as avg_tone_accuracy, + AVG(ease_factor) as avg_ease_factor + FROM tone_pattern_progress + WHERE user_id = ? + `); + + return query.get(Date.now(), userId); + } + + /** + * Get recent tone attempts for a user + */ + getRecentToneAttempts(userId: number, limit: number = 10): ToneAttempt[] { + const query = this.db.query(` + SELECT id, user_id, word_id, timestamp, accuracy, tone_accuracy, + pronunciation_score, review_time, difficulty, mode, created_at + FROM tone_attempts + WHERE user_id = ? + ORDER BY timestamp DESC + LIMIT ? + `); + + return query.all(userId, limit) as ToneAttempt[]; + } + + /** + * Get tone mastery statistics for a user + */ + getToneMasteryStats(userId: number): ToneMasteryStats[] { + const query = this.db.query(` + SELECT id, user_id, tone_pattern, syllable_count, total_attempts, successful_attempts, + avg_accuracy, avg_review_time, best_accuracy, last_attempted, mastered_at, + created_at, updated_at + FROM tone_mastery_stats + WHERE user_id = ? + ORDER BY avg_accuracy DESC, last_attempted DESC + `); + + return query.all(userId) as ToneMasteryStats[]; + } + + /** + * Get tone words for practice (prioritizes due words, then random new words) + */ + getToneWordsForPractice(userId: number, limit: number = 20): number[] { + const query = this.db.query(` + WITH due_words AS ( + SELECT word_id, 0 as priority + FROM tone_pattern_progress + WHERE user_id = ? AND next_review_date <= ? AND is_mastered = 0 + LIMIT ? + ), + new_words AS ( + SELECT e.id as word_id, 1 as priority + FROM expressions e + LEFT JOIN tone_pattern_progress tpp ON e.id = tpp.word_id AND tpp.user_id = ? + WHERE tpp.word_id IS NULL + AND e.lang = 'th' + AND e.type = 'word' + ORDER BY e.frequency DESC + LIMIT ? + ) + SELECT word_id + FROM ( + SELECT word_id, priority FROM due_words + UNION ALL + SELECT word_id, priority FROM new_words + ) + ORDER BY priority, RANDOM() + LIMIT ? + `); + + const results = query.all(userId, Date.now(), limit, userId, limit, limit) as { word_id: number }[]; + return results.map(r => r.word_id); + } + + /** + * Get user dashboard data (combines traditional SRS and tone SRS) + */ + getUserDashboard(userId: number) { + const query = this.db.query(` + SELECT + u.id as user_id, + u.name as username, + COUNT(DISTINCT tpp.word_id) as total_tone_words, + COUNT(DISTINCT CASE WHEN tpp.is_mastered = 1 THEN tpp.word_id END) as mastered_tone_words, + COUNT(DISTINCT CASE WHEN tpp.next_review_date <= ? THEN tpp.word_id END) as due_tone_words, + COUNT(DISTINCT up.card_id) as total_cards, + COUNT(DISTINCT CASE WHEN up.is_mastered = 1 THEN up.card_id END) as mastered_cards, + COUNT(DISTINCT CASE WHEN up.next_review_date <= ? THEN up.card_id END) as due_cards, + (SELECT COUNT(*) FROM attempts WHERE user_id = u.id AND timestamp > ?) as reviews_today, + (SELECT AVG(ta.tone_accuracy) FROM tone_attempts ta WHERE ta.user_id = u.id AND ta.timestamp > ?) as weekly_tone_accuracy + FROM users u + LEFT JOIN tone_pattern_progress tpp ON tpp.user_id = u.id + LEFT JOIN user_progress up ON up.user_id = u.id + WHERE u.id = ? + GROUP BY u.id, u.name + `); + + const weekAgo = Date.now() - (7 * 24 * 60 * 60 * 1000); + const today = Date.now() - (24 * 60 * 60 * 1000); + + return query.get(Date.now(), Date.now(), today, weekAgo, userId); + } +}
\ No newline at end of file |