
-- chat_module/sql/create_chat_tables.sql
-- You can run this manually in phpMyAdmin, or let install.php handle it.

CREATE TABLE IF NOT EXISTS conversations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user1_id INT UNSIGNED NOT NULL,
  user2_id INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  last_message_id BIGINT UNSIGNED DEFAULT NULL,
  unread_user1 INT UNSIGNED NOT NULL DEFAULT 0,
  unread_user2 INT UNSIGNED NOT NULL DEFAULT 0,
  pinned_user1 TINYINT(1) NOT NULL DEFAULT 0,
  pinned_user2 TINYINT(1) NOT NULL DEFAULT 0,
  archived_user1 TINYINT(1) NOT NULL DEFAULT 0,
  archived_user2 TINYINT(1) NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_pair (user1_id, user2_id),
  INDEX idx_user1 (user1_id),
  INDEX idx_user2 (user2_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  conversation_id INT UNSIGNED NOT NULL,
  sender_id INT UNSIGNED NOT NULL,
  body TEXT,
  media_url VARCHAR(255) DEFAULT NULL,
  status ENUM('sent','delivered','seen') NOT NULL DEFAULT 'sent',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  seen_at DATETIME DEFAULT NULL,
  INDEX idx_conv_created (conversation_id, created_at),
  INDEX idx_conv_sender (sender_id),
  CONSTRAINT fk_msg_conv FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_presence (
  user_id INT UNSIGNED PRIMARY KEY,
  last_active DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  is_typing TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
