path: root/packages/db/src/srs.ts

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);
  }
}