database.js

// database.js
const sqlite3 = require('sqlite3').verbose();
const path = require('path');

class Database {
  constructor() {
    this.db = new sqlite3.Database('./chat.db', (err) => {
      if (err) {
        console.error('Erro ao conectar com SQLite:', err);
      } else {
        console.log('Conectado ao SQLite');
        this.initTables();
      }
    });
  }

  initTables() {
    const createMessagesTable = `
      CREATE TABLE IF NOT EXISTS messages (
        id TEXT PRIMARY KEY,
        text TEXT NOT NULL,
        author TEXT NOT NULL,
        timestamp TEXT NOT NULL,
        createdAt INTEGER NOT NULL
      )
    `;
    
    this.db.run(createMessagesTable, (err) => {
      if (err) {
        console.error('Erro ao criar tabela:', err);
      } else {
        console.log('Tabela messages criada/verificada');
      }
    });
  }

  // Salvar mensagem
  saveMessage(messageData) {
    const { id, text, author, timestamp, createdAt } = messageData;
    
    return new Promise((resolve, reject) => {
      const stmt = this.db.prepare(`
        INSERT INTO messages (id, text, author, timestamp, createdAt)
        VALUES (?, ?, ?, ?, ?)
      `);
      
      stmt.run([id, text, author, timestamp, createdAt], function(err) {
        if (err) {
          reject(err);
        } else {
          resolve(this.lastID);
        }
      });
      
      stmt.finalize();
    });
  }

  // Buscar mensagens recentes
  getRecentMessages(limit = 50) {
    return new Promise((resolve, reject) => {
      this.db.all(
        `SELECT * FROM messages 
         ORDER BY createdAt DESC 
         LIMIT ?`,
        [limit],
        (err, rows) => {
          if (err) {
            reject(err);
          } else {
            resolve(rows.reverse()); // Reverter para ordem cronológica
          }
        }
      );
    });
  }

  // Limpar mensagens antigas
  cleanOldMessages() {
    const oneDayAgo = Date.now() - (24 * 60 * 60 * 1000);
    
    return new Promise((resolve, reject) => {
      this.db.run(
        `DELETE FROM messages WHERE createdAt < ?`,
        [oneDayAgo],
        function(err) {
          if (err) {
            reject(err);
          } else {
            console.log(`Limpeza: ${this.changes} mensagens antigas removidas`);
            resolve(this.changes);
          }
        }
      );
    });
  }

  close() {
    this.db.close();
  }
}

module.exports = Database;