CREATE TABLE IF NOT EXISTS whatsapp_connections (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  slot TINYINT UNSIGNED NOT NULL DEFAULT 1,
  node_id VARCHAR(40) NOT NULL DEFAULT 'local',
  preferred_mode VARCHAR(20) NOT NULL DEFAULT 'qr',
  label VARCHAR(80) NOT NULL DEFAULT '',
  enabled TINYINT NOT NULL DEFAULT 0,
  generation INT UNSIGNED NOT NULL DEFAULT 0,
  status VARCHAR(32) NOT NULL DEFAULT 'disconnected',
  phone VARCHAR(80) NULL,
  qr MEDIUMTEXT NULL,
  qr_expires BIGINT NOT NULL DEFAULT 0,
  connected_at BIGINT NOT NULL DEFAULT 0,
  heartbeat BIGINT NOT NULL DEFAULT 0,
  error_code VARCHAR(80) NULL,
  UNIQUE KEY uq_whatsapp_slot (user_id, slot),
  KEY idx_whatsapp_node (node_id, enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_account_knowledge (
  user_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  shared_knowledge TEXT NOT NULL,
  updated_at BIGINT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_channel_settings (
  connection_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  configured_phone VARCHAR(20) NOT NULL,
  channel_knowledge TEXT NOT NULL,
  profile_overrides_json MEDIUMTEXT NULL,
  ai_enabled TINYINT NOT NULL DEFAULT 0,
  rule_ids_json TEXT NOT NULL,
  updated_at BIGINT NOT NULL DEFAULT 0,
  KEY idx_whatsapp_channel_owner (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_auth (
  connection_id BIGINT UNSIGNED NOT NULL,
  category VARCHAR(80) NOT NULL,
  key_id VARCHAR(191) NOT NULL,
  value MEDIUMTEXT NOT NULL,
  PRIMARY KEY (connection_id, category, key_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_chat_state (
  connection_id BIGINT UNSIGNED NOT NULL,
  chat_id VARCHAR(160) NOT NULL,
  pause_until BIGINT NOT NULL DEFAULT 0,
  revision BIGINT NOT NULL DEFAULT 0,
  last_incoming BIGINT NOT NULL DEFAULT 0,
  PRIMARY KEY (connection_id, chat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_inbox (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  connection_id BIGINT UNSIGNED NOT NULL,
  chat_id VARCHAR(160) NOT NULL,
  message_id VARCHAR(160) NOT NULL,
  body TEXT NOT NULL,
  sender VARCHAR(255) NOT NULL DEFAULT '',
  received_at BIGINT NOT NULL,
  processed TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY uq_whatsapp_message (connection_id, chat_id, message_id),
  KEY idx_whatsapp_inbox_pending (connection_id, processed, received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_outbox (
  id CHAR(32) NOT NULL PRIMARY KEY,
  connection_id BIGINT UNSIGNED NOT NULL,
  chat_id VARCHAR(160) NOT NULL,
  revision BIGINT NOT NULL,
  input_json MEDIUMTEXT NOT NULL,
  actions_json MEDIUMTEXT NULL,
  action_index INT NOT NULL DEFAULT 0,
  state VARCHAR(20) NOT NULL DEFAULT 'pending',
  attempts INT NOT NULL DEFAULT 0,
  due_at BIGINT NOT NULL,
  created_at BIGINT NOT NULL,
  error_code VARCHAR(80) NULL,
  KEY idx_whatsapp_outbox_pending (connection_id, state, due_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_sent_ids (
  connection_id BIGINT UNSIGNED NOT NULL,
  chat_id VARCHAR(160) NOT NULL,
  message_id VARCHAR(160) NOT NULL,
  created_at BIGINT NOT NULL,
  PRIMARY KEY (connection_id, chat_id, message_id),
  KEY idx_whatsapp_sent_age (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_extension_presence (
  user_id BIGINT UNSIGNED NOT NULL,
  instance_id CHAR(36) NOT NULL,
  device_id CHAR(36) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  ready TINYINT NOT NULL DEFAULT 0,
  ready_since BIGINT NOT NULL DEFAULT 0,
  last_seen BIGINT NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id, instance_id),
  KEY idx_extension_ready (user_id, phone, ready, last_seen),
  KEY idx_extension_device (user_id, device_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_hybrid_routes (
  connection_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  owner_instance CHAR(36) NULL,
  lease_until BIGINT NOT NULL DEFAULT 0,
  busy_until BIGINT NOT NULL DEFAULT 0,
  accept_after BIGINT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_message_keys (
  connection_id BIGINT UNSIGNED NOT NULL,
  kind VARCHAR(8) NOT NULL,
  message_id VARCHAR(160) NOT NULL,
  chat_id VARCHAR(160) NOT NULL,
  PRIMARY KEY (connection_id, kind, message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_schema (
  id TINYINT PRIMARY KEY,
  version INT NOT NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS whatsapp_plans (
  code VARCHAR(20) PRIMARY KEY,
  name VARCHAR(60) NOT NULL,
  qr_limit TINYINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT IGNORE INTO whatsapp_plans (code,name,qr_limit) VALUES ('personal','Personal',0),('equipo','Equipo',0),('empresa','Empresa',2);
CREATE TABLE IF NOT EXISTS whatsapp_account_plans (
  user_id BIGINT UNSIGNED PRIMARY KEY,
  plan_code VARCHAR(20) NOT NULL,
  updated_at BIGINT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_nodes (
  id VARCHAR(40) PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  state VARCHAR(16) NOT NULL DEFAULT 'draining',
  capacity INT UNSIGNED NOT NULL DEFAULT 2,
  token_hash CHAR(64) NULL,
  heartbeat BIGINT NOT NULL DEFAULT 0,
  rss_mb INT UNSIGNED NOT NULL DEFAULT 0,
  host_free_mb INT UNSIGNED NOT NULL DEFAULT 0,
  session_count INT UNSIGNED NOT NULL DEFAULT 0,
  version INT UNSIGNED NOT NULL DEFAULT 0,
  created_at BIGINT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_phone_claims (
  phone VARCHAR(20) PRIMARY KEY,
  connection_id BIGINT UNSIGNED NOT NULL,
  UNIQUE KEY uq_whatsapp_claim (connection_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_admin_audit (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor VARCHAR(100) NOT NULL,
  action VARCHAR(40) NOT NULL,
  target VARCHAR(100) NOT NULL,
  created_at BIGINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_attention_settings (
  user_id BIGINT UNSIGNED NOT NULL,
  phone VARCHAR(20) NOT NULL,
  settings_json TEXT NOT NULL,
  updated_at BIGINT NOT NULL,
  PRIMARY KEY (user_id,phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_attention_chats (
  id CHAR(32) NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  phone VARCHAR(20) NOT NULL,
  chat_id VARCHAR(160) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'resolved',
  reason VARCHAR(40) NOT NULL DEFAULT '',
  sender VARCHAR(255) NOT NULL DEFAULT '',
  revision BIGINT UNSIGNED NOT NULL DEFAULT 0,
  note TEXT NULL,
  ack_state VARCHAR(20) NOT NULL DEFAULT 'none',
  ack_text TEXT NULL,
  ack_message_id VARCHAR(160) NULL,
  ack_owner CHAR(36) NULL,
  ack_started BIGINT NOT NULL DEFAULT 0,
  created_at BIGINT NOT NULL DEFAULT 0,
  updated_at BIGINT NOT NULL DEFAULT 0,
  last_event_at BIGINT NOT NULL DEFAULT 0,
  resolved_at BIGINT NOT NULL DEFAULT 0,
  PRIMARY KEY (user_id,phone,chat_id),
  UNIQUE KEY uq_attention_case (id),
  KEY idx_attention_pending (user_id,phone,status,created_at),
  KEY idx_attention_resolved (user_id,phone,status,resolved_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_attention_events (
  user_id BIGINT UNSIGNED NOT NULL,
  phone VARCHAR(20) NOT NULL,
  chat_id VARCHAR(160) NOT NULL,
  direction VARCHAR(3) NOT NULL,
  message_id VARCHAR(160) NOT NULL,
  kind VARCHAR(12) NOT NULL,
  body TEXT NOT NULL,
  received_at BIGINT NOT NULL,
  PRIMARY KEY (user_id,phone,direction,message_id),
  KEY idx_attention_history (user_id,phone,chat_id,received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS whatsapp_attention_aliases (
  user_id BIGINT UNSIGNED NOT NULL,
  phone VARCHAR(20) NOT NULL,
  alias VARCHAR(160) NOT NULL,
  chat_id VARCHAR(160) NOT NULL,
  PRIMARY KEY (user_id,phone,alias)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;