112 lines
3.7 KiB
JavaScript
112 lines
3.7 KiB
JavaScript
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');
|
||
}
|