backend/db/migrations/20260722120000_create_whatsapp_conversas_tables.cjs

exports.up = function (knex) {
  return knex.schema
    .createTable("whatsapp_conversas", (t) => {
      t.increments("Id").primary();
      t.string("Telefone", 30).notNullable();
      t.string("NomeContato", 255).nullable();
      t.integer("AtendenteId").unsigned().nullable();
      t.string("Status", 20).notNullable().defaultTo("aberta");
      t.timestamp("UltimaMensagemEm").nullable();
      t.timestamp("CreatedAt").notNullable().defaultTo(knex.fn.now());
      t.timestamp("UpdatedAt").notNullable().defaultTo(knex.fn.now());

      t.foreign("AtendenteId").references("usuarios.Id").onDelete("SET NULL");
      t.unique(["Telefone"]);
      t.index(["Status"]);
      t.index(["UltimaMensagemEm"]);
    })
    .createTable("whatsapp_mensagens", (t) => {
      t.increments("Id").primary();
      t.integer("ConversaId").unsigned().notNullable();
      t.string("Direcao", 10).notNullable(); // entrada (cliente) | saida (atendente)
      t.text("Corpo").notNullable();
      t.string("WhatsappMessageId", 100).nullable(); // id da mensagem na Meta; usado para não duplicar reenvios de webhook
      t.timestamp("Timestamp").notNullable();
      t.timestamp("CreatedAt").notNullable().defaultTo(knex.fn.now());

      t.foreign("ConversaId").references("whatsapp_conversas.Id").onDelete("CASCADE");
      t.unique(["WhatsappMessageId"]);
      t.index(["ConversaId"]);
      t.index(["Timestamp"]);
    });
};

exports.down = function (knex) {
  return knex.schema
    .dropTable("whatsapp_mensagens")
    .dropTable("whatsapp_conversas");
};