📄 Source: database.php
<?php
class ChatDatabase {
private $db;
private $dbPath;
public function __construct() {
$this->dbPath = __DIR__ . '/chats.db';
$this->db = new SQLite3($this->dbPath);
$this->db->enableExceptions(true);
$this->initTables();
}
private function initTables() {
$this->db->exec("
CREATE TABLE IF NOT EXISTS conversations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_email TEXT NOT NULL,
title TEXT DEFAULT 'New Chat',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
");
$this->db->exec("
CREATE TABLE IF NOT EXISTS messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
conversation_id INTEGER NOT NULL,
role TEXT NOT NULL,
content TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
)
");
// Check if tables exist
$result = $this->db->query("SELECT name FROM sqlite_master WHERE type='table'");
$tables = [];
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$tables[] = $row['name'];
}
error_log("Database tables: " . implode(", ", $tables));
}
public function createConversation($userEmail, $title = 'New Chat') {
$stmt = $this->db->prepare("
INSERT INTO conversations (user_email, title) VALUES (:email, :title)
");
$stmt->bindValue(':email', $userEmail, SQLITE3_TEXT);
$stmt->bindValue(':title', $title, SQLITE3_TEXT);
$stmt->execute();
return $this->db->lastInsertRowID();
}
public function getConversations($userEmail) {
$stmt = $this->db->prepare("
SELECT * FROM conversations WHERE user_email = :email ORDER BY updated_at DESC
");
$stmt->bindValue(':email', $userEmail, SQLITE3_TEXT);
$result = $stmt->execute();
$conversations = [];
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$conversations[] = $row;
}
return $conversations;
}
public function addMessage($conversationId, $role, $content) {
$stmt = $this->db->prepare("
INSERT INTO messages (conversation_id, role, content) VALUES (:cid, :role, :content)
");
$stmt->bindValue(':cid', $conversationId, SQLITE3_INTEGER);
$stmt->bindValue(':role', $role, SQLITE3_TEXT);
$stmt->bindValue(':content', $content, SQLITE3_TEXT);
$stmt->execute();
// Update conversation timestamp
$this->db->exec("UPDATE conversations SET updated_at = CURRENT_TIMESTAMP WHERE id = $conversationId");
// Auto-update title from first user message
$countStmt = $this->db->prepare("SELECT COUNT(*) as cnt FROM messages WHERE conversation_id = :cid");
$countStmt->bindValue(':cid', $conversationId, SQLITE3_INTEGER);
$countResult = $countStmt->execute();
$countRow = $countResult->fetchArray(SQLITE3_ASSOC);
if ($countRow['cnt'] == 1 && $role == 'user') {
$newTitle = substr($content, 0, 30) . (strlen($content) > 30 ? '...' : '');
$updateStmt = $this->db->prepare("UPDATE conversations SET title = :title WHERE id = :cid");
$updateStmt->bindValue(':title', $newTitle, SQLITE3_TEXT);
$updateStmt->bindValue(':cid', $conversationId, SQLITE3_INTEGER);
$updateStmt->execute();
}
}
public function getMessages($conversationId) {
$stmt = $this->db->prepare("
SELECT * FROM messages WHERE conversation_id = :cid ORDER BY created_at ASC
");
$stmt->bindValue(':cid', $conversationId, SQLITE3_INTEGER);
$result = $stmt->execute();
$messages = [];
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$messages[] = $row;
}
return $messages;
}
public function deleteConversation($conversationId) {
$this->db->exec("DELETE FROM messages WHERE conversation_id = $conversationId");
$this->db->exec("DELETE FROM conversations WHERE id = $conversationId");
}
public function renameConversation($conversationId, $newTitle) {
$stmt = $this->db->prepare("UPDATE conversations SET title = :title WHERE id = :cid");
$stmt->bindValue(':title', $newTitle, SQLITE3_TEXT);
$stmt->bindValue(':cid', $conversationId, SQLITE3_INTEGER);
$stmt->execute();
}
}
?>
← Back