📄 Source: database.php
<?php
class ChatDatabase {
private $db;
public function __construct() {
$dbPath = __DIR__ . '/inboxzero.db';
$this->db = new SQLite3($dbPath);
$this->db->enableExceptions(true);
$this->initTables();
}
private function initTables() {
// Emails table
$this->db->exec("
CREATE TABLE IF NOT EXISTS emails (
id TEXT PRIMARY KEY,
thread_id TEXT,
from_address TEXT,
subject TEXT,
date_received DATETIME,
content TEXT,
is_read INTEGER DEFAULT 0,
labels TEXT,
synced_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
");
$this->db->exec("CREATE INDEX IF NOT EXISTS idx_date ON emails(date_received DESC)");
$this->db->exec("CREATE INDEX IF NOT EXISTS idx_read ON emails(is_read)");
$this->db->exec("CREATE INDEX IF NOT EXISTS idx_from ON emails(from_address)");
// Conversations table
$this->db->exec("
CREATE TABLE IF NOT EXISTS conversations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_email TEXT,
title TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
)
");
// Chat messages table
$this->db->exec("
CREATE TABLE IF NOT EXISTS chat_messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
conversation_id INTEGER,
role TEXT,
content TEXT,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY(conversation_id) REFERENCES conversations(id)
)
");
}
public function getEmailById($id) {
$stmt = $this->db->prepare("SELECT * FROM emails WHERE id = :id");
$stmt->bindValue(':id', $id, SQLITE3_TEXT);
$result = $stmt->execute();
return $result->fetchArray(SQLITE3_ASSOC);
}
public function saveEmail($email) {
$stmt = $this->db->prepare("
INSERT OR REPLACE INTO emails
(id, thread_id, from_address, subject, date_received, content, is_read, labels, synced_at)
VALUES (:id, :tid, :from, :subject, :date, :content, :is_read, :labels, datetime('now'))
");
$stmt->bindValue(':id', $email['id'], SQLITE3_TEXT);
$stmt->bindValue(':tid', $email['thread_id'], SQLITE3_TEXT);
$stmt->bindValue(':from', $email['from'], SQLITE3_TEXT);
$stmt->bindValue(':subject', $email['subject'], SQLITE3_TEXT);
$stmt->bindValue(':date', $email['date'], SQLITE3_TEXT);
$stmt->bindValue(':content', $email['content'], SQLITE3_TEXT);
$stmt->bindValue(':is_read', $email['is_read'], SQLITE3_INTEGER);
$stmt->bindValue(':labels', $email['labels'] ?? '', SQLITE3_TEXT);
return $stmt->execute();
}
public function getEmails($limit = 50, $onlyUnread = false, $offset = 0) {
$sql = "SELECT * FROM emails ORDER BY date_received DESC LIMIT :limit OFFSET :offset";
if ($onlyUnread) {
$sql = "SELECT * FROM emails WHERE is_read = 0 ORDER BY date_received DESC LIMIT :limit OFFSET :offset";
}
$stmt = $this->db->prepare($sql);
$stmt->bindValue(':limit', $limit, SQLITE3_INTEGER);
$stmt->bindValue(':offset', $offset, SQLITE3_INTEGER);
$result = $stmt->execute();
$emails = [];
while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
$emails[] = $row;
}
return $emails;
}
public function markAsRead($emailId) {
$stmt = $this->db->prepare("UPDATE emails SET is_read = 1 WHERE id = :id");
$stmt->bindValue(':id', $emailId, SQLITE3_TEXT);
return $stmt->execute();
}
public function countUnread() {
$result = $this->db->query("SELECT COUNT(*) as cnt FROM emails WHERE is_read = 0");
$row = $result->fetchArray();
return $row['cnt'];
}
public function deleteOldEmails($days = 90, $limit = 30) {
$stmt = $this->db->prepare("
DELETE FROM emails
WHERE julianday('now') - julianday(date_received) > :days
LIMIT :limit
");
$stmt->bindValue(':days', $days, SQLITE3_INTEGER);
$stmt->bindValue(':limit', $limit, SQLITE3_INTEGER);
$stmt->execute();
return $this->db->changes();
}
public function deleteEmail($emailId) {
$stmt = $this->db->prepare("DELETE FROM emails WHERE id = :id");
$stmt->bindValue(':id', $emailId, SQLITE3_TEXT);
return $stmt->execute();
}
public function getStats() {
$result = $this->db->query("
SELECT
COUNT(*) as total,
SUM(CASE WHEN is_read = 0 THEN 1 ELSE 0 END) as unread,
MIN(date_received) as oldest,
MAX(date_received) as newest
FROM emails
");
return $result->fetchArray(SQLITE3_ASSOC);
}
// Chat methods
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 chat_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();
$this->db->exec("UPDATE conversations SET updated_at = CURRENT_TIMESTAMP WHERE id = $conversationId");
}
public function getMessages($conversationId) {
$stmt = $this->db->prepare("
SELECT * FROM chat_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 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();
}
public function deleteConversation($conversationId) {
$this->db->exec("DELETE FROM chat_messages WHERE conversation_id = $conversationId");
$this->db->exec("DELETE FROM conversations WHERE id = $conversationId");
}
}
?>
← Back