`lib/yiddish-dictionary/dictionary-api.ts`

342 lines
/**
 * Yiddish Dictionary API for Dear Bubbe
 * Provides lookup and management functions for the Yiddish dictionary database
 */

import Database from 'better-sqlite3';
import path from 'path';

export interface DictionaryEntry {
  id: number;
  yiddish: string;
  yiddish_latin?: string;
  english: string;
  pos?: string;
  gender?: string;
  plural?: string;
  tags?: string;
  usage_example?: string;
  cultural_note?: string;
  source_name?: string;
  bubbe_approved: number;
}

export interface Phrase {
  id: number;
  yiddish: string;
  yiddish_latin?: string;
  english: string;
  literal_meaning?: string;
  usage_context?: string;
  cultural_note?: string;
  bubbe_approved: number;
}

export interface BubbeFavorite {
  id: number;
  yiddish: string;
  english: string;
  context?: string;
  intensity?: number;
  usage_example?: string;
}

class YiddishDictionary {
  private db: Database.Database;
  private readonly dbPath: string;

  constructor(dbPath?: string) {
    this.dbPath = dbPath || path.join(__dirname, 'yiddish_dictionary.sqlite');
    this.db = new Database(this.dbPath, { readonly: false });
    
    // Enable foreign keys
    this.db.exec('PRAGMA foreign_keys = ON');
  }

  /**
   * Search for entries by English word
   */
  searchEnglish(query: string, limit: number = 10): DictionaryEntry[] {
    const stmt = this.db.prepare(`
      SELECT 
        e.*,
        s.name as source_name
      FROM entries e
      LEFT JOIN sources s ON e.source_id = s.id
      WHERE e.english LIKE ? AND e.bubbe_approved = 1
      ORDER BY 
        CASE WHEN e.english = ? THEN 1
             WHEN e.english LIKE ? THEN 2
             ELSE 3 END,
        LENGTH(e.english)
      LIMIT ?
    `);
    
    return stmt.all(`%${query}%`, query, `${query}%`, limit) as DictionaryEntry[];
  }

  /**
   * Search for entries by Yiddish word (supports both Hebrew script and transliteration)
   */
  searchYiddish(query: string, limit: number = 10): DictionaryEntry[] {
    const stmt = this.db.prepare(`
      SELECT 
        e.*,
        s.name as source_name
      FROM entries e
      LEFT JOIN sources s ON e.source_id = s.id
      WHERE (e.yiddish LIKE ? OR e.yiddish_latin LIKE ?) 
        AND e.bubbe_approved = 1
      ORDER BY 
        CASE WHEN e.yiddish = ? OR e.yiddish_latin = ? THEN 1
             WHEN e.yiddish LIKE ? OR e.yiddish_latin LIKE ? THEN 2
             ELSE 3 END,
        LENGTH(e.yiddish)
      LIMIT ?
    `);
    
    return stmt.all(
      `%${query}%`, `%${query}%`,
      query, query,
      `${query}%`, `${query}%`,
      limit
    ) as DictionaryEntry[];
  }

  /**
   * Get a random insult/scolding term for Bubbe to use
   */
  getRandomInsult(): DictionaryEntry | null {
    const stmt = this.db.prepare(`
      SELECT e.* FROM entries e
      WHERE e.bubbe_approved = 1
        AND (e.tags LIKE '%insult%' 
          OR e.tags LIKE '%scold%'
          OR e.pos = 'interjection'
          OR e.english LIKE '%fool%'
          OR e.english LIKE '%stupid%'
          OR e.english LIKE '%idiot%')
      ORDER BY RANDOM()
      LIMIT 1
    `);
    
    return stmt.get() as DictionaryEntry | null;
  }

  /**
   * Get a random endearment term (used sarcastically by Bubbe)
   */
  getRandomEndearment(): DictionaryEntry | null {
    const stmt = this.db.prepare(`
      SELECT e.* FROM entries e
      WHERE e.bubbe_approved = 1
        AND (e.tags LIKE '%endearment%'
          OR e.tags LIKE '%affection%'
          OR e.english LIKE '%dear%'
          OR e.english LIKE '%sweet%')
      ORDER BY RANDOM()
      LIMIT 1
    `);
    
    return stmt.get() as DictionaryEntry | null;
  }

  /**
   * Get phrases for a specific context
   */
  getPhrasesByContext(context: string, limit: number = 5): Phrase[] {
    const stmt = this.db.prepare(`
      SELECT * FROM phrases
      WHERE usage_context LIKE ?
        AND bubbe_approved = 1
      ORDER BY RANDOM()
      LIMIT ?
    `);
    
    return stmt.all(`%${context}%`, limit) as Phrase[];
  }

  /**
   * Get Bubbe's favorite expressions
   */
  getBubbeFavorites(context?: string, minIntensity?: number): BubbeFavorite[] {
    let query = 'SELECT * FROM bubbe_favorites WHERE 1=1';
    const params: any[] = [];
    
    if (context) {
      query += ' AND context = ?';
      params.push(context);
    }
    
    if (minIntensity !== undefined) {
      query += ' AND intensity >= ?';
      params.push(minIntensity);
    }
    
    query += ' ORDER BY intensity DESC, RANDOM()';
    
    const stmt = this.db.prepare(query);
    return stmt.all(...params) as BubbeFavorite[];
  }

  /**
   * Get a random Bubbe expression based on intensity
   */
  getRandomExpression(minIntensity: number = 1, maxIntensity: number = 10): BubbeFavorite | null {
    const stmt = this.db.prepare(`
      SELECT * FROM bubbe_favorites
      WHERE intensity BETWEEN ? AND ?
      ORDER BY RANDOM()
      LIMIT 1
    `);
    
    return stmt.get(minIntensity, maxIntensity) as BubbeFavorite | null;
  }

  /**
   * Record usage of a term (for learning patterns)
   */
  recordUsage(term: string, context?: string, entryId?: number): void {
    const stmt = this.db.prepare(`
      INSERT INTO usage_stats (entry_id, term, context, usage_count, last_used)
      VALUES (?, ?, ?, 1, CURRENT_TIMESTAMP)
      ON CONFLICT (term) DO UPDATE SET
        usage_count = usage_count + 1,
        last_used = CURRENT_TIMESTAMP
    `);
    
    stmt.run(entryId || null, term, context || null);
  }

  /**
   * Get most frequently used terms
   */
  getMostUsedTerms(limit: number = 20): any[] {
    const stmt = this.db.prepare(`
      SELECT 
        us.term,
        us.usage_count,
        us.last_used,
        e.yiddish,
        e.english
      FROM usage_stats us
      LEFT JOIN entries e ON us.entry_id = e.id
      ORDER BY us.usage_count DESC, us.last_used DESC
      LIMIT ?
    `);
    
    return stmt.all(limit);
  }

  /**
   * Check if a term is banned
   */
  isBanned(term: string, language: string = 'en'): boolean {
    const stmt = this.db.prepare(`
      SELECT COUNT(*) as count FROM banned_terms
      WHERE term = ? AND language = ?
    `);
    
    const result = stmt.get(term.toLowerCase(), language) as { count: number };
    return result.count > 0;
  }

  /**
   * Add a new banned term
   */
  addBannedTerm(
    term: string,
    language: string,
    category: string,
    severity: string,
    reason?: string,
    replacement?: string
  ): void {
    const stmt = this.db.prepare(`
      INSERT OR IGNORE INTO banned_terms 
      (term, language, category, severity, reason, replacement)
      VALUES (?, ?, ?, ?, ?, ?)
    `);
    
    stmt.run(term.toLowerCase(), language, category, severity, reason, replacement);
  }

  /**
   * Get suggestions for Yiddish terms based on context
   */
  getSuggestions(context: 'greeting' | 'insult' | 'complaint' | 'advice', limit: number = 5): DictionaryEntry[] {
    const contextMap = {
      greeting: ['hello', 'good', 'peace', 'welcome'],
      insult: ['fool', 'idiot', 'stupid', 'worthless'],
      complaint: ['complain', 'whine', 'grumble', 'moan'],
      advice: ['should', 'must', 'better', 'wise']
    };
    
    const keywords = contextMap[context] || [];
    const placeholders = keywords.map(() => '?').join(',');
    
    const stmt = this.db.prepare(`
      SELECT DISTINCT e.* FROM entries e
      WHERE e.bubbe_approved = 1
        AND (
          e.tags LIKE '%${context}%'
          OR e.english IN (${placeholders})
          OR ${keywords.map(() => 'e.english LIKE ?').join(' OR ')}
        )
      ORDER BY RANDOM()
      LIMIT ?
    `);
    
    const params = [
      ...keywords,
      ...keywords.map(k => `%${k}%`),
      limit
    ];
    
    return stmt.all(...params) as DictionaryEntry[];
  }

  /**
   * Close database connection
   */
  close(): void {
    this.db.close();
  }

  /**
   * Get database statistics
   */
  getStats(): any {
    const stats = {
      totalEntries: this.db.prepare('SELECT COUNT(*) as count FROM entries').get() as { count: number },
      approvedEntries: this.db.prepare('SELECT COUNT(*) as count FROM entries WHERE bubbe_approved = 1').get() as { count: number },
      needsReview: this.db.prepare('SELECT COUNT(*) as count FROM entries WHERE bubbe_approved = 0').get() as { count: number },
      totalPhrases: this.db.prepare('SELECT COUNT(*) as count FROM phrases').get() as { count: number },
      totalFavorites: this.db.prepare('SELECT COUNT(*) as count FROM bubbe_favorites').get() as { count: number },
      bannedTerms: this.db.prepare('SELECT COUNT(*) as count FROM banned_terms').get() as { count: number },
      sources: this.db.prepare('SELECT COUNT(*) as count FROM sources').get() as { count: number }
    };
    
    return {
      totalEntries: stats.totalEntries.count,
      approvedEntries: stats.approvedEntries.count,
      needsReview: stats.needsReview.count,
      totalPhrases: stats.totalPhrases.count,
      totalFavorites: stats.totalFavorites.count,
      bannedTerms: stats.bannedTerms.count,
      sources: stats.sources.count
    };
  }
}

// Singleton instance
let dictionaryInstance: YiddishDictionary | null = null;

export function getDictionary(dbPath?: string): YiddishDictionary {
  if (!dictionaryInstance) {
    dictionaryInstance = new YiddishDictionary(dbPath);
  }
  return dictionaryInstance;
}

export default YiddishDictionary;