-- 20260514_ai_chatbox_backend_v1.sql
-- Menambahkan tabel sesi dan pesan untuk AI chatbox per user.

CREATE TABLE IF NOT EXISTS tb_ai_chat_session (
  id_session BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_user INT UNSIGNED NOT NULL,
  judul VARCHAR(120) NOT NULL,
  last_message_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id_session),
  KEY idx_ai_chat_session_user (id_user),
  KEY idx_ai_chat_session_last_message (last_message_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tb_ai_chat_message (
  id_message BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_session BIGINT UNSIGNED NOT NULL,
  id_user INT UNSIGNED NOT NULL,
  role ENUM('user', 'assistant', 'system') NOT NULL,
  message_text LONGTEXT NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id_message),
  KEY idx_ai_chat_message_session (id_session),
  KEY idx_ai_chat_message_user (id_user),
  KEY idx_ai_chat_message_created (created_at),
  CONSTRAINT fk_ai_chat_message_session
    FOREIGN KEY (id_session) REFERENCES tb_ai_chat_session(id_session)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
