📂 File Browser

📁 Directories

📄 Files

📄 Source: database.php

<?php
class ChatDatabase {
    private $db;
    
    public function __construct() {
        $dbPath = __DIR__ . '/chats.db';
        $this->db = new SQLite3($dbPath);
        $this->db->enableExceptions(true);
        $this->initTables();
    }
    
    private function initTables() {
        // Tabel conversații
        $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
            )
        ");
        
        // Tabel mesaje
        $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
            )
        ");
        
        // Tabel emailuri stocate
        $this->db->exec("
            CREATE TABLE IF NOT EXISTS stored_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
            )
        ");
        
        // Creează indexuri
        $this->db->exec("CREATE INDEX IF NOT EXISTS idx_stored_date ON stored_emails(date_received DESC)");
        $this->db->exec("CREATE INDEX IF NOT EXISTS idx_stored_from ON stored_emails(from_address)");
    }
    
    // Metode pentru conversații
    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();
        $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 ? '...' : '');
            $this->renameConversation($conversationId, $newTitle);
        }
    }
    
    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 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 messages WHERE conversation_id = $conversationId");
        $this->db->exec("DELETE FROM conversations WHERE id = $conversationId");
    }
    
    // Metode pentru emailuri stocate
    public function saveEmail($email) {
        $stmt = $this->db->prepare("
            INSERT OR REPLACE INTO stored_emails 
            (id, thread_id, from_address, subject, date_received, content, is_read, labels)
            VALUES (:id, :tid, :from, :subject, :date, :content, :is_read, :labels)
        ");
        $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'] ?? 0, SQLITE3_INTEGER);
        $stmt->bindValue(':labels', $email['labels'] ?? '', SQLITE3_TEXT);
        return $stmt->execute();
    }
    
    public function getStoredEmails($limit = 50) {
        $result = $this->db->query("
            SELECT * FROM stored_emails ORDER BY date_received DESC LIMIT $limit
        ");
        $emails = [];
        while ($row = $result->fetchArray(SQLITE3_ASSOC)) {
            $emails[] = $row;
        }
        return $emails;
    }
}
?>