src/db/database.js

import initSqlJs from 'sql.js/dist/sql-wasm.js';
import wasmUrl from 'sql.js/dist/sql-wasm.wasm?url';
import { DEFAULT_STYLE_PROFILE } from './default-style.js';

class Database {
  constructor() {
    this.db = null;
    this.isReady = false;
    this.initPromise = this.init();
  }

  async init() {
    try {
      // Use the Vite-resolved URL for the WASM binary
      // This is the most reliable way to get the correct path in all environments
      const initPromise = initSqlJs({
        locateFile: () => wasmUrl
      });
      // Add a timeout to prevent hanging
      const timeoutPromise = new Promise((_, reject) => setTimeout(() => reject(new Error('initSqlJs timeout')), 30000));
      const SQL = await Promise.race([initPromise, timeoutPromise]);
      console.log('sql.js loaded from:', wasmUrl);
      
      const savedData = localStorage.getItem('schoolmind_db');
      if (savedData) {
        try {
          let uInt8Array;
          if (savedData.startsWith('SQLITE:')) {
            const base64 = savedData.substring(7);
            const binaryString = atob(base64);
            const len = binaryString.length;
            uInt8Array = new Uint8Array(len);
            for (let i = 0; i < len; i++) {
              uInt8Array[i] = binaryString.charCodeAt(i);
            }
          } else {
            uInt8Array = new Uint8Array(savedData.split(',').map(Number));
          }
          this.db = new SQL.Database(uInt8Array);
        } catch(e) {
          console.error("Corrupted DB in localStorage, clearing...", e);
          localStorage.removeItem('schoolmind_db');
          this.db = new SQL.Database();
          this.createSchema();
        }
        
        // Auto-Fixes and Migrations
        try {
          // Auto-Fix for the wrong model string from the first version
          this.db.run("UPDATE settings SET value = 'gemini-1.5-flash' WHERE key = 'model' AND value = 'gemini-3-flash'");
          // Auto-Fix for deprecated or rate-limited OpenRouter models -> openrouter/free
          this.db.run("UPDATE settings SET value = 'openrouter-openrouter/free' WHERE key = 'model' AND (value = 'openrouter-meta-llama/llama-3.1-8b-instruct:free' OR value = 'openrouter-meta-llama/llama-3.3-70b-instruct:free')");
          // Auto-Fix for stiff bullet point style profile
          this.db.run("UPDATE settings SET value = 'Passe deinen Schreibstil, die Ausführlichkeit und das Format dynamisch an die jeweilige Frage an. Antworte natürlich und frei. Vermeide ein starres Stichpunkt-Muster, es sei denn, es wird explizit gefordert oder ist für eine Übersicht sehr sinnvoll. Formuliere auf Oberstufen-Niveau.' WHERE key = 'style_profile' AND (value LIKE '%nutze Bulletpoints%' OR value LIKE '%PDF-Überschrift%')");
        } catch (e) {
          // Ignore errors if settings table doesn't exist yet or other schema mismatch
        }
        
        this.saveToStorage();
      } else {
        this.db = new SQL.Database();
      }
      
      // Ensure all tables exist with their latest schema definitions (catches cases where createSchema was old)
      this.createSchema();
      // Migrations for existing tables that might have been created without newer columns
      const migrations = [
        "ALTER TABLE flashcard_decks ADD COLUMN source_lang TEXT",
        "ALTER TABLE flashcard_decks ADD COLUMN target_lang TEXT",
        "ALTER TABLE flashcards ADD COLUMN image_url TEXT"
      ];
      
      for (const m of migrations) {
        try {
          this.db.run(m);
        } catch (e) {
          // Ignore, column likely already exists
        }
      }

      this.saveToStorage();
      this.isReady = true;
      console.log('SQLite Database initialized');
    } catch (err) {
      console.error('Failed to initialize SQLite', err);
    }
  }

  saveToStorage() {
    if (!this.db) return;
    try {
      const data = this.db.export();
      let binary = '';
      const len = data.byteLength;
      // Chunk processing to avoid Maximum call stack size exceeded if we used String.fromCharCode.apply
      for (let i = 0; i < len; i++) {
        binary += String.fromCharCode(data[i]);
      }
      const b64 = btoa(binary);
      localStorage.setItem('schoolmind_db', 'SQLITE:' + b64);
    } catch (e) {
      console.error("Storage save failed, quota likely exceeded", e);
      if (e.name === 'QuotaExceededError' || e.message.includes('quota')) {
        alert("Achtung: Der lokale Speicher deines Browsers ist voll! Bitte lösche alte Chats oder Bilder, da dein Fortschritt sonst nicht mehr gespeichert werden kann.");
      }
    }
  }

  createSchema() {
    const schema = `
      CREATE TABLE IF NOT EXISTS profiles (
        id TEXT PRIMARY KEY,
        name TEXT,
        school TEXT,
        grade TEXT,
        subjects TEXT,
        context_info TEXT,
        is_anonymous INTEGER DEFAULT 0,
        is_active INTEGER DEFAULT 0
      );

      CREATE TABLE IF NOT EXISTS settings (
        key TEXT PRIMARY KEY,
        value TEXT
      );

      CREATE TABLE IF NOT EXISTS conversations (
        id TEXT PRIMARY KEY,
        title TEXT,
        emoji TEXT,
        preview TEXT,
        model_used TEXT,
        created_at TEXT,
        updated_at TEXT
      );

      CREATE TABLE IF NOT EXISTS messages (
        id TEXT PRIMARY KEY,
        conversation_id TEXT,
        role TEXT,
        content TEXT,
        model_used TEXT,
        style_used TEXT,
        created_at TEXT,
        FOREIGN KEY(conversation_id) REFERENCES conversations(id)
      );

      CREATE TABLE IF NOT EXISTS feedback_rules (
        id TEXT PRIMARY KEY,
        rule_text TEXT,
        source_feedback TEXT,
        is_active INTEGER DEFAULT 1,
        created_at TEXT
      );
      CREATE TABLE IF NOT EXISTS folder_metadata (
        path TEXT PRIMARY KEY,
        emoji TEXT,
        color TEXT
      );
      CREATE TABLE IF NOT EXISTS trash_metadata (
        id TEXT PRIMARY KEY,
        original_path TEXT,
        is_dir INTEGER,
        deleted_at TEXT
      );

      CREATE TABLE IF NOT EXISTS calendar_events (
        id TEXT PRIMARY KEY,
        title TEXT,
        type TEXT,
        date TEXT,
        time TEXT,
        subject TEXT,
        description TEXT,
        created_at TEXT
      );

      CREATE TABLE IF NOT EXISTS flashcard_decks (
        id TEXT PRIMARY KEY,
        title TEXT,
        description TEXT,
        color TEXT,
        source_lang TEXT,
        target_lang TEXT,
        created_at TEXT,
        updated_at TEXT
      );

      CREATE TABLE IF NOT EXISTS flashcards (
        id TEXT PRIMARY KEY,
        deck_id TEXT,
        front TEXT,
        back TEXT,
        image_url TEXT,
        next_review TEXT,
        ease_factor REAL,
        interval INTEGER,
        repetitions INTEGER,
        created_at TEXT,
        updated_at TEXT,
        FOREIGN KEY(deck_id) REFERENCES flashcard_decks(id) ON DELETE CASCADE
      );
    `;
    const statements = schema.split(';').filter(s => s.trim().length > 0);
    for (const stmt of statements) {
      try {
        this.db.run(stmt + ';');
      } catch (e) {
        console.error("Error creating table:", e, stmt);
      }
    }
    
    // Seed default settings
    this.db.run("INSERT OR IGNORE INTO settings (key, value) VALUES ('model', 'gemini-3.5-flash')");
    this.db.run("INSERT OR IGNORE INTO settings (key, value) VALUES ('style_profile', ?)", [DEFAULT_STYLE_PROFILE]);
    
    // Migration: Rebuild previews to new 150 character limit and clean out SiriHero tags
    try {
      if (this.isReady) {
        const convos = this.exec("SELECT id FROM conversations");
        for (const row of convos) {
          const cid = row.id;
          const lastMsg = this.exec("SELECT content FROM messages WHERE conversation_id = ? ORDER BY created_at DESC LIMIT 1", [cid]);
          if (lastMsg.length > 0) {
            let cleanContent = lastMsg[0].content.replace(/!\[.*?\]\((data:image\/[^)]+)\)/g, '[Bild] ');
            cleanContent = cleanContent.replace(/!\[(?:SiriHero|KnowledgeCard)[^\]]*\]\([^)]*\)/gi, '');
            cleanContent = cleanContent.replace(/!(?:SiriHero|KnowledgeCard):[^\n]+/gi, '');
            cleanContent = cleanContent.replace(/[*_#`\[\]]/g, '').replace(/\n/g, ' ').replace(/\s+/g, ' ').trim();
            const preview = cleanContent.length > 150 ? cleanContent.substring(0, 150) + '...' : cleanContent;
            this.exec("UPDATE conversations SET preview = ? WHERE id = ?", [preview, cid]);
          }
        }
      }
    } catch(e) { console.error('Preview Migration failed', e); }
    
    this.saveToStorage();
  }

  // --- Utility Methods ---

  exec(query, params = []) {
    if (!this.isReady) throw new Error("DB not ready");
    const stmt = this.db.prepare(query);
    stmt.bind(params);
    const results = [];
    while (stmt.step()) {
      results.push(stmt.getAsObject());
    }
    stmt.free();
    if (!query.trim().toUpperCase().startsWith("SELECT")) {
      this.saveToStorage();
    }
    return results;
  }

  // Settings
  getSetting(key) {
    try {
      const res = this.exec("SELECT value FROM settings WHERE key = ?", [key]);
      return res.length > 0 ? res[0].value : null;
    } catch (e) {
      console.error("Failed to read setting, attempting auto-fix...", key, e);
      try {
        this.db.run("CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT);");
        this.db.run("INSERT OR IGNORE INTO settings (key, value) VALUES ('model', 'gemini-3.5-flash')");
        this.db.run("INSERT OR IGNORE INTO settings (key, value) VALUES ('style_profile', 'Standard')");
        const res = this.exec("SELECT value FROM settings WHERE key = ?", [key]);
        return res.length > 0 ? res[0].value : null;
      } catch (innerErr) {
        console.error("Auto-fix failed:", innerErr);
        return null;
      }
    }
  }

  setSetting(key, value) {
    try {
      this.exec("INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)", [key, value]);
    } catch (e) {
      console.error("Failed to write setting, attempting auto-fix...", key, e);
      try {
        this.db.run("CREATE TABLE IF NOT EXISTS settings (key TEXT PRIMARY KEY, value TEXT);");
        this.exec("INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)", [key, value]);
      } catch (innerErr) {
        console.error("Auto-fix failed:", innerErr);
      }
    }
  }

  // Profiles
  getActiveProfile() {
    const res = this.exec("SELECT * FROM profiles WHERE is_active = 1");
    return res.length > 0 ? res[0] : null;
  }

  // Conversations
  getConversations() {
    return this.exec("SELECT * FROM conversations ORDER BY updated_at DESC");
  }

  createConversation(id, title, emoji) {
    const now = new Date().toISOString();
    this.exec(
      "INSERT INTO conversations (id, title, emoji, preview, model_used, created_at, updated_at) VALUES (?, ?, ?, '', '', ?, ?)",
      [id, title, emoji, now, now]
    );
  }

  updateConversationTitle(id, newTitle) {
    const now = new Date().toISOString();
    this.exec("UPDATE conversations SET title = ?, updated_at = ? WHERE id = ?", [newTitle, now, id]);
  }

  deleteConversation(id) {
    this.exec("DELETE FROM messages WHERE conversation_id = ?", [id]);
    this.exec("DELETE FROM conversations WHERE id = ?", [id]);
  }

  addMessage(id, conversation_id, role, content, model_used, style_used) {
    const now = new Date().toISOString();
    this.exec(
      "INSERT INTO messages (id, conversation_id, role, content, model_used, style_used, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)",
      [id, conversation_id, role, content, model_used, style_used, now]
    );
    // Update conversation preview. Remove base64 image markdown first
    let cleanContent = content.replace(/!\[.*?\]\((data:image\/[^)]+)\)/g, '[Bild] ');
    
    // Remove specific AI components like SiriHero and KnowledgeCard
    cleanContent = cleanContent.replace(/!\[(?:SiriHero|KnowledgeCard)[^\]]*\]\([^)]*\)/gi, '');
    cleanContent = cleanContent.replace(/!(?:SiriHero|KnowledgeCard):[^\n]+/gi, '');
    
    // Strip common markdown
    cleanContent = cleanContent.replace(/[*_#`\\[\\]]/g, '').replace(/\\n/g, ' ').replace(/\\s+/g, ' ').trim();

    const preview = cleanContent.length > 150 ? cleanContent.substring(0, 150) + '...' : cleanContent;
    this.exec("UPDATE conversations SET preview = ?, updated_at = ? WHERE id = ?", [preview, now, conversation_id]);
  }

  getMessages(conversation_id) {
    return this.exec("SELECT * FROM messages WHERE conversation_id = ? ORDER BY created_at ASC", [conversation_id]);
  }

  searchMessages(query) {
    if (!query || !query.trim()) return [];
    const likeQuery = `%${query}%`;
    const sql = `
      SELECT m.id as message_id, m.conversation_id, m.content, m.role, m.created_at,
             c.title, c.emoji, c.updated_at
      FROM messages m
      JOIN conversations c ON m.conversation_id = c.id
      WHERE m.content LIKE ? COLLATE NOCASE
      ORDER BY c.updated_at DESC, m.created_at DESC
    `;
    const rows = this.exec(sql, [likeQuery]);
    
    // Group all matches by conversation
    const grouped = new Map();
    for (const row of rows) {
      if (!grouped.has(row.conversation_id)) {
        grouped.set(row.conversation_id, {
          conversation_id: row.conversation_id,
          title: row.title,
          emoji: row.emoji,
          updated_at: row.updated_at,
          matches: []
        });
      }
      grouped.get(row.conversation_id).matches.push({
        message_id: row.message_id,
        content: row.content,
        role: row.role,
        created_at: row.created_at
      });
    }
    return Array.from(grouped.values());
  }

  // Feedback Rules
  getActiveFeedbackRules() {
    return this.exec("SELECT rule_text FROM feedback_rules WHERE is_active = 1");
  }

  addFeedbackRule(id, rule_text, source_feedback) {
    const now = new Date().toISOString();
    this.exec(
      "INSERT INTO feedback_rules (id, rule_text, source_feedback, is_active, created_at) VALUES (?, ?, ?, 1, ?)",
      [id, rule_text, source_feedback, now]
    );
  }
  // --- Folder Metadata Operations ---

  getFolderMetadata(path) {
    if (!this.db || !path) return null;
    const stmt = this.db.prepare("SELECT emoji, color FROM folder_metadata WHERE path = ?");
    stmt.bind([path]);
    if (stmt.step()) {
      const res = stmt.getAsObject();
      stmt.free();
      return res;
    }
    stmt.free();
    return null;
  }

  saveFolderMetadata(path, emoji, color) {
    if (!this.db || !path) return;
    try {
      this.exec("INSERT OR REPLACE INTO folder_metadata (path, emoji, color) VALUES (?, ?, ?)", [path, emoji || '', color || '#4285f4']);
    } catch (e) {
      console.error("saveFolderMetadata error:", e);
    }
  }

  deleteFolderMetadata(path) {
    if (!this.db || !path) return;
    // Delete this folder's metadata and any nested folders metadata
    try {
      this.exec("DELETE FROM folder_metadata WHERE path = ? OR path LIKE ?", [path, `${path}/%`]);
    } catch (e) {
      console.error("deleteFolderMetadata error:", e);
    }
  }

  // --- Trash Operations ---
  
  getTrashItems() {
    if (!this.db) return [];
    const res = this.exec("SELECT * FROM trash_metadata ORDER BY deleted_at DESC");
    return res;
  }

  addTrashItem(id, originalPath, isDir) {
    const now = new Date().toISOString();
    this.exec(
      "INSERT INTO trash_metadata (id, original_path, is_dir, deleted_at) VALUES (?, ?, ?, ?)",
      [id, originalPath, isDir ? 1 : 0, now]
    );
  }
  removeTrashItem(id) {
    if (!this.db) return;
    this.exec("DELETE FROM trash_metadata WHERE id = ?", [id]);
  }
  // --- FLASHCARDS & ANKI ---
  
  getDecks() {
    try {
      const decks = this.exec("SELECT * FROM flashcard_decks ORDER BY created_at DESC");
      
      // Get due cards count for each deck
      const now = new Date().toISOString();
      decks.forEach(deck => {
        const countRes = this.exec("SELECT COUNT(*) as count FROM flashcards WHERE deck_id = ? AND next_review <= ? AND back NOT LIKE '%<!-- PENDING_CORRECTION -->%'", [deck.id, now]);
        deck.due_count = countRes.length > 0 ? countRes[0].count : 0;
        
        const totalRes = this.exec("SELECT COUNT(*) as count FROM flashcards WHERE deck_id = ? AND back NOT LIKE '%<!-- PENDING_CORRECTION -->%'", [deck.id]);
        deck.total_count = totalRes.length > 0 ? totalRes[0].count : 0;
      });
      
      return decks;
    } catch(e) {
      console.error(e);
      return [];
    }
  }

  createDeck(title, description = '', color = '#8a53e6', sourceLang = 'Deutsch', targetLang = 'Englisch') {
    const id = 'deck_' + Date.now() + Math.random().toString(36).substring(2, 9);
    const now = new Date().toISOString();
    this.exec(
      "INSERT INTO flashcard_decks (id, title, description, color, source_lang, target_lang, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
      [id, title, description, color, sourceLang, targetLang, now, now]
    );
    return { id, title, description, color, source_lang: sourceLang, target_lang: targetLang, created_at: now, updated_at: now, due_count: 0, total_count: 0 };
  }
  
  updateDeckTitle(deckId, newTitle) {
    const now = new Date().toISOString();
    this.exec("UPDATE flashcard_decks SET title = ?, updated_at = ? WHERE id = ?", [newTitle, now, deckId]);
  }
  
  updateDeckLanguages(deckId, sourceLang, targetLang) {
    const now = new Date().toISOString();
    this.exec("UPDATE flashcard_decks SET source_lang = ?, target_lang = ?, updated_at = ? WHERE id = ?", [sourceLang, targetLang, now, deckId]);
  }
  
  deleteDeck(deckId) {
    this.exec("DELETE FROM flashcards WHERE deck_id = ?", [deckId]);
    this.exec("DELETE FROM flashcard_decks WHERE id = ?", [deckId]);
  }

  addFlashcard(deckId, front, back, imageUrl = null) {
    const id = 'card_' + Date.now() + Math.random().toString(36).substring(2, 9);
    const now = new Date().toISOString();
    // Default Anki SM-2 initial values
    this.exec(
      `INSERT INTO flashcards 
      (id, deck_id, front, back, image_url, next_review, ease_factor, interval, repetitions, created_at, updated_at) 
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [id, deckId, front, back, imageUrl, now, 2.5, 0, 0, now, now]
    );
    return id;
  }
  
  getDueCards(deckId) {
    const now = new Date().toISOString();
    return this.exec(
      "SELECT * FROM flashcards WHERE deck_id = ? AND next_review <= ? AND back NOT LIKE '%<!-- PENDING_CORRECTION -->%' ORDER BY next_review ASC",
      [deckId, now]
    );
  }

  getAllCards(deckId) {
    // For free practice, shuffle them or just order randomly
    return this.exec(
      "SELECT * FROM flashcards WHERE deck_id = ? AND back NOT LIKE '%<!-- PENDING_CORRECTION -->%' ORDER BY RANDOM()",
      [deckId]
    );
  }

  getCardsForDeck(deckId) {
    return this.exec(
      "SELECT * FROM flashcards WHERE deck_id = ? AND back NOT LIKE '%<!-- PENDING_CORRECTION -->%' ORDER BY created_at DESC",
      [deckId]
    );
  }

  getPendingCards() {
    return this.exec("SELECT * FROM flashcards WHERE back LIKE '%<!-- PENDING_CORRECTION -->%' OR back LIKE '%<!-- NEEDS_ENRICHMENT -->%'");
  }

  searchFlashcards(query) {
    return this.exec(`
      SELECT f.*, d.title as deck_title, d.color as deck_color
      FROM flashcards f
      JOIN flashcard_decks d ON f.deck_id = d.id
      WHERE f.front LIKE ? OR f.back LIKE ?
      ORDER BY f.created_at DESC
      LIMIT 50
    `, [`%${query}%`, `%${query}%`]);
  }

  deleteFlashcard(cardId) {
    this.exec("DELETE FROM flashcards WHERE id = ?", [cardId]);
  }
  
  editFlashcard(cardId, front, back, imageUrl = null) {
    const now = new Date().toISOString();
    if (imageUrl !== null) {
      this.exec("UPDATE flashcards SET front = ?, back = ?, image_url = ?, updated_at = ? WHERE id = ?", [front, back, imageUrl, now, cardId]);
    } else {
      this.exec("UPDATE flashcards SET front = ?, back = ?, updated_at = ? WHERE id = ?", [front, back, now, cardId]);
    }
  }
  
  // SM-2 Algorithm update
  updateCardProgress(cardId, quality) {
    // quality: 0 (Again), 1 (Hard), 2 (Good), 3 (Easy)
    const res = this.exec("SELECT ease_factor, interval, repetitions FROM flashcards WHERE id = ?", [cardId]);
    if (res.length === 0) return;
    
    let { ease_factor, interval, repetitions } = res[0];
    
    if (quality === 0) {
      repetitions = 0;
      interval = 1; // 1 day
      ease_factor = Math.max(1.3, ease_factor - 0.20);
    } else {
      if (repetitions === 0) {
        interval = 1;
      } else if (repetitions === 1) {
        interval = 6;
      } else {
        if (quality === 1) { // Hard
          interval = Math.round(interval * 1.2);
          ease_factor = Math.max(1.3, ease_factor - 0.15);
        } else if (quality === 2) { // Good
          interval = Math.round(interval * ease_factor);
        } else if (quality === 3) { // Easy
          interval = Math.round(interval * ease_factor * 1.3);
          ease_factor += 0.15;
        }
      }
      repetitions++;
    }
    
    // Calculate next review date
    const next_review = new Date();
    next_review.setDate(next_review.getDate() + interval);
    const next_review_iso = next_review.toISOString();
    const now = new Date().toISOString();
    
    this.exec(
      "UPDATE flashcards SET next_review = ?, ease_factor = ?, interval = ?, repetitions = ?, updated_at = ? WHERE id = ?",
      [next_review_iso, ease_factor, interval, repetitions, now, cardId]
    );
  }

  // --- Calendar ---
  getCalendarEvents() {
    return this.exec("SELECT * FROM calendar_events ORDER BY date ASC, time ASC");
  }

  getUpcomingEvents(limit = 4) {
    const today = new Date().toISOString().split('T')[0];
    return this.exec("SELECT * FROM calendar_events WHERE date >= ? ORDER BY date ASC, time ASC LIMIT ?", [today, limit]);
  }

  addCalendarEvent(title, type, date, time, subject, description) {
    const id = 'evt_' + Date.now() + '_' + Math.random().toString(36).substr(2, 5);
    const now = new Date().toISOString();
    this.exec(
      "INSERT INTO calendar_events (id, title, type, date, time, subject, description, created_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
      [id, title, type, date, time, subject, description, now]
    );
    return id;
  }

  deleteCalendarEvent(id) {
    this.exec("DELETE FROM calendar_events WHERE id = ?", [id]);
  }

}

export const db = new Database();