-- HamBaa KAYA - WhatsApp chat session state
-- Stores the current conversational step and context per WhatsApp user.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS chat_sessions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  channel ENUM('whatsapp') NOT NULL DEFAULT 'whatsapp',
  state VARCHAR(64) NOT NULL DEFAULT 'START',
  context_json JSON NULL,
  last_msg_id VARCHAR(128) NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_chat_session_user (user_id, channel),
  KEY idx_chat_state (state),
  CONSTRAINT fk_chat_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
