ALTER TABLE telegram_messages ADD COLUMN delivery_attempts INTEGER NOT NULL DEFAULT 0;
ALTER TABLE telegram_messages ADD COLUMN next_delivery_attempt_at TEXT;
ALTER TABLE telegram_messages ADD COLUMN delivery_locked_until TEXT;
ALTER TABLE telegram_messages ADD COLUMN last_delivery_error TEXT;

CREATE INDEX IF NOT EXISTS idx_telegram_messages_next_delivery_attempt_at
  ON telegram_messages(next_delivery_attempt_at);
CREATE INDEX IF NOT EXISTS idx_telegram_messages_delivery_locked_until
  ON telegram_messages(delivery_locked_until);

CREATE TABLE IF NOT EXISTS whatsapp_threads (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  thread_id TEXT NOT NULL UNIQUE,
  chat_id TEXT NOT NULL UNIQUE,
  interaction_id TEXT NOT NULL,
  whatsapp_user_id TEXT,
  phone_number TEXT,
  display_name TEXT,
  queue_id TEXT,
  is_group INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL,
  claimed_by_user TEXT,
  claimed_at TEXT,
  ai_session_id TEXT,
  ai_state TEXT,
  ai_handoff_reason TEXT,
  ai_last_model_at TEXT,
  last_message_at TEXT NOT NULL,
  last_message_preview TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_thread_id ON whatsapp_threads(thread_id);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_chat_id ON whatsapp_threads(chat_id);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_interaction_id ON whatsapp_threads(interaction_id);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_whatsapp_user_id ON whatsapp_threads(whatsapp_user_id);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_phone_number ON whatsapp_threads(phone_number);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_display_name ON whatsapp_threads(display_name);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_queue_id ON whatsapp_threads(queue_id);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_is_group ON whatsapp_threads(is_group);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_status ON whatsapp_threads(status);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_claimed_by_user ON whatsapp_threads(claimed_by_user);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_claimed_at ON whatsapp_threads(claimed_at);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_ai_session_id ON whatsapp_threads(ai_session_id);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_ai_state ON whatsapp_threads(ai_state);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_ai_last_model_at ON whatsapp_threads(ai_last_model_at);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_last_message_at ON whatsapp_threads(last_message_at);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_created_at ON whatsapp_threads(created_at);
CREATE INDEX IF NOT EXISTS idx_whatsapp_threads_updated_at ON whatsapp_threads(updated_at);

CREATE TABLE IF NOT EXISTS whatsapp_messages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  message_id TEXT NOT NULL UNIQUE,
  thread_id TEXT,
  interaction_id TEXT,
  chat_id TEXT NOT NULL,
  text TEXT NOT NULL,
  customer_id TEXT,
  direction TEXT NOT NULL DEFAULT 'inbound',
  whatsapp_message_id_external TEXT,
  operator_user TEXT,
  author_type TEXT NOT NULL DEFAULT 'customer',
  author_id TEXT,
  delivery_status TEXT,
  delivery_attempts INTEGER NOT NULL DEFAULT 0,
  next_delivery_attempt_at TEXT,
  delivery_locked_until TEXT,
  last_delivery_error TEXT,
  payload_json TEXT NOT NULL DEFAULT '{}',
  created_at TEXT NOT NULL
);

UPDATE whatsapp_messages
SET author_type = CASE
  WHEN direction = 'outbound' THEN 'human'
  WHEN direction = 'system' THEN 'system'
  ELSE 'customer'
END
WHERE author_type IS NULL OR TRIM(author_type) = '';

UPDATE whatsapp_messages
SET author_id = operator_user
WHERE operator_user IS NOT NULL
  AND (
    direction = 'outbound'
    OR direction IS NULL
    OR TRIM(direction) = ''
  )
  AND (author_id IS NULL OR TRIM(author_id) = '');

CREATE UNIQUE INDEX IF NOT EXISTS ix_whatsapp_messages_chat_external_unique
  ON whatsapp_messages(chat_id, whatsapp_message_id_external);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_message_id ON whatsapp_messages(message_id);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_thread_id ON whatsapp_messages(thread_id);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_interaction_id ON whatsapp_messages(interaction_id);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_chat_id ON whatsapp_messages(chat_id);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_customer_id ON whatsapp_messages(customer_id);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_direction ON whatsapp_messages(direction);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_whatsapp_message_id_external
  ON whatsapp_messages(whatsapp_message_id_external);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_operator_user ON whatsapp_messages(operator_user);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_author_type ON whatsapp_messages(author_type);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_author_id ON whatsapp_messages(author_id);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_delivery_status ON whatsapp_messages(delivery_status);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_next_delivery_attempt_at
  ON whatsapp_messages(next_delivery_attempt_at);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_delivery_locked_until
  ON whatsapp_messages(delivery_locked_until);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_created_at ON whatsapp_messages(created_at);

CREATE INDEX IF NOT EXISTS idx_event_outbox_status_available_at_id
  ON event_outbox(status, available_at, id);
CREATE INDEX IF NOT EXISTS idx_ai_jobs_thread_status_id
  ON ai_jobs(thread_id, status, id);
CREATE INDEX IF NOT EXISTS idx_ai_jobs_thread_trigger_id
  ON ai_jobs(thread_id, trigger_message_id, id);
CREATE INDEX IF NOT EXISTS idx_telegram_messages_thread_direction_author_created_id
  ON telegram_messages(thread_id, direction, author_type, created_at, id);
CREATE INDEX IF NOT EXISTS idx_whatsapp_messages_thread_direction_author_created_id
  ON whatsapp_messages(thread_id, direction, author_type, created_at, id);
CREATE INDEX IF NOT EXISTS idx_voice_transcript_segments_session_speaker_final_id
  ON voice_transcript_segments(session_id, speaker, is_final, id);
CREATE UNIQUE INDEX IF NOT EXISTS uq_voice_transcript_segments_session_sequence
  ON voice_transcript_segments(session_id, sequence_no);
CREATE INDEX IF NOT EXISTS idx_asterisk_event_log_name_call_status_id
  ON asterisk_event_log(ami_event_name, call_id, forward_status, id);
CREATE INDEX IF NOT EXISTS idx_reporting_events_queue_channel_created_at
  ON reporting_events(queue_id, channel, created_at);