narasiahli-be/plan/old/init-db.js

const Database = require('better-sqlite3');
const path = require('path');

// Database file path
const dbPath = path.join(process.cwd(), 'lib', 'db', 'chat_history.db');

console.log('🚀 Initializing database at:', dbPath);

// Initialize database
const db = new Database(dbPath);

// Create tables if they don't exist
const initDatabase = () => {
  console.log('📋 Creating tables...');
  
  // Create chat_sessions table
  db.exec(`
    CREATE TABLE IF NOT EXISTS chat_sessions (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      session_id TEXT UNIQUE NOT NULL,
      user_id TEXT NOT NULL,
      agent_id TEXT NOT NULL,
      title TEXT,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      message_count INTEGER DEFAULT 0,
      status TEXT DEFAULT 'active'
    )
  `);
  console.log('✅ chat_sessions table created');

  // Create chat_messages table
  db.exec(`
    CREATE TABLE IF NOT EXISTS chat_messages (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      session_id TEXT NOT NULL,
      message_type TEXT NOT NULL CHECK (message_type IN ('user', 'assistant')),
      content TEXT NOT NULL,
      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
      FOREIGN KEY (session_id) REFERENCES chat_sessions (session_id) ON DELETE CASCADE
    )
  `);
  console.log('✅ chat_messages table created');

  // Create indexes for better performance
  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_sessions_user_id ON chat_sessions (user_id);
    CREATE INDEX IF NOT EXISTS idx_sessions_agent_id ON chat_sessions (agent_id);
    CREATE INDEX IF NOT EXISTS idx_sessions_created_at ON chat_sessions (created_at);
    CREATE INDEX IF NOT EXISTS idx_messages_session_id ON chat_messages (session_id);
    CREATE INDEX IF NOT EXISTS idx_messages_created_at ON chat_messages (created_at);
  `);
  console.log('✅ Indexes created');

  // Insert sample data (optional)
  const sampleSession = {
    session_id: 'sample-session-123',
    user_id: 'user_123',
    agent_id: 'd04bbced-ae93-4fb3-a015-9472e4e5e539',
    title: 'Sample Chat Session'
  };

  const existingSession = db.prepare('SELECT id FROM chat_sessions WHERE session_id = ?').get(sampleSession.session_id);
  
  if (!existingSession) {
    db.prepare(`
      INSERT INTO chat_sessions (session_id, user_id, agent_id, title, message_count)
      VALUES (?, ?, ?, ?, ?)
    `).run(sampleSession.session_id, sampleSession.user_id, sampleSession.agent_id, sampleSession.title, 2);
    
    console.log('✅ Sample session inserted');

    // Insert sample messages
    const sampleMessages = [
      { type: 'user', content: 'Halo, ada yang bisa saya tanyakan?' },
      { type: 'assistant', content: 'Halo! Tentu saja, saya siap membantu Anda dengan pertanyaan apapun.' }
    ];

    const insertMessage = db.prepare(`
      INSERT INTO chat_messages (session_id, message_type, content)
      VALUES (?, ?, ?)
    `);

    sampleMessages.forEach(msg => {
      insertMessage.run(sampleSession.session_id, msg.type, msg.content);
    });
    
    console.log('✅ Sample messages inserted');
  } else {
    console.log('ℹ️ Sample session already exists, skipping...');
  }
};

// Run initialization
try {
  initDatabase();
  console.log('🎉 Database initialization completed successfully!');
  
  // Show database info
  const sessionCount = db.prepare('SELECT COUNT(*) as count FROM chat_sessions').get();
  const messageCount = db.prepare('SELECT COUNT(*) as count FROM chat_messages').get();
  
  console.log('📊 Database Statistics:');
  console.log(`   - Sessions: ${sessionCount.count}`);
  console.log(`   - Messages: ${messageCount.count}`);
  
} catch (error) {
  console.error('❌ Error initializing database:', error);
} finally {
  db.close();
  console.log('🔒 Database connection closed');
}