CREATE TABLE IF NOT EXISTS meta_settings (
  id TINYINT UNSIGNED PRIMARY KEY,
  app_id VARCHAR(64) NOT NULL,
  secret_ciphertext TEXT NOT NULL,
  verify_ciphertext TEXT NOT NULL,
  base_url VARCHAR(500) NOT NULL,
  api_version VARCHAR(12) NOT NULL DEFAULT 'v24.0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS instagram_accounts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  instagram_id VARCHAR(64) NOT NULL UNIQUE,
  username VARCHAR(191) NOT NULL,
  token_ciphertext TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS conversations (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  account_id BIGINT UNSIGNED NOT NULL,
  external_user_id VARCHAR(64) NOT NULL,
  status ENUM('open','closed') NOT NULL DEFAULT 'open',
  assigned_user_id BIGINT UNSIGNED NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY conversation_account_user (account_id, external_user_id),
  FOREIGN KEY (account_id) REFERENCES instagram_accounts(id) ON DELETE CASCADE,
  FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS messages (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  conversation_id BIGINT UNSIGNED NOT NULL,
  external_message_id VARCHAR(191) NULL UNIQUE,
  direction ENUM('in','out') NOT NULL,
  body TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY message_conversation (conversation_id,id),
  FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;