fix: base scope

Add a new reporting event that fires when a conversation is created.
This event serves as a denominator for calculating rates like deflection
rate and escalation rate directly from the reporting_events table.

app/listeners/reporting_event_listener.rb

@@ -1,6 +1,18 @@
 class ReportingEventListener < BaseListener
   include ReportingEventHelper
 
+  def conversation_created(event)
+    conversation = extract_conversation_and_account(event)[0]
+
+    ReportingEvent.create!(
+      name: 'conversation_created',
+      value: 0,
+      event_start_time: conversation.created_at,
+      event_end_time: conversation.created_at,
+      **base_event_attributes(conversation, from_state: nil)
+    )
+  end
+
   def conversation_resolved(event)
     conversation = extract_conversation_and_account(event)[0]
     time_to_resolve = conversation.updated_at.to_i - conversation.created_at.to_i
@@ -8,14 +20,10 @@ class ReportingEventListener < BaseListener
     reporting_event = ReportingEvent.new(
       name: 'conversation_resolved',
       value: time_to_resolve,
-      value_in_business_hours: business_hours(conversation.inbox, conversation.created_at,
-                                              conversation.updated_at),
-      account_id: conversation.account_id,
-      inbox_id: conversation.inbox_id,
-      user_id: conversation.assignee_id,
-      conversation_id: conversation.id,
+      value_in_business_hours: business_hours(conversation.inbox, conversation.created_at, conversation.updated_at),
       event_start_time: conversation.created_at,
-      event_end_time: conversation.updated_at
+      event_end_time: conversation.updated_at,
+      **base_event_attributes(conversation, from_state: 'handling')
     )
 
     create_bot_resolved_event(conversation, reporting_event)
@@ -27,20 +35,14 @@ class ReportingEventListener < BaseListener
     conversation = message.conversation
     first_response_time = message.created_at.to_i - last_non_human_activity(conversation).to_i
 
-    reporting_event = ReportingEvent.new(
+    ReportingEvent.create!(
       name: 'first_response',
       value: first_response_time,
-      value_in_business_hours: business_hours(conversation.inbox, last_non_human_activity(conversation),
-                                              message.created_at),
-      account_id: conversation.account_id,
-      inbox_id: conversation.inbox_id,
-      user_id: message.sender_id,
-      conversation_id: conversation.id,
+      value_in_business_hours: business_hours(conversation.inbox, last_non_human_activity(conversation), message.created_at),
       event_start_time: last_non_human_activity(conversation),
-      event_end_time: message.created_at
+      event_end_time: message.created_at,
+      **base_event_attributes(conversation, from_state: 'waiting', user_id: message.sender_id)
     )
-
-    reporting_event.save!
   end
 
   def reply_created(event)
@@ -50,21 +52,16 @@ class ReportingEventListener < BaseListener
 
     return if waiting_since.blank?
 
-    # When waiting_since is nil, set reply_time to 0
     reply_time = message.created_at.to_i - waiting_since.to_i
 
-    reporting_event = ReportingEvent.new(
+    ReportingEvent.create!(
       name: 'reply_time',
       value: reply_time,
       value_in_business_hours: business_hours(conversation.inbox, waiting_since, message.created_at),
-      account_id: conversation.account_id,
-      inbox_id: conversation.inbox_id,
-      user_id: conversation.assignee_id,
-      conversation_id: conversation.id,
       event_start_time: waiting_since,
-      event_end_time: message.created_at
+      event_end_time: message.created_at,
+      **base_event_attributes(conversation, from_state: 'waiting')
     )
-    reporting_event.save!
   end
 
   def conversation_bot_handoff(event)
@@ -76,18 +73,14 @@ class ReportingEventListener < BaseListener
 
     time_to_handoff = conversation.updated_at.to_i - conversation.created_at.to_i
 
-    reporting_event = ReportingEvent.new(
+    ReportingEvent.create!(
       name: 'conversation_bot_handoff',
       value: time_to_handoff,
       value_in_business_hours: business_hours(conversation.inbox, conversation.created_at, conversation.updated_at),
-      account_id: conversation.account_id,
-      inbox_id: conversation.inbox_id,
-      user_id: conversation.assignee_id,
-      conversation_id: conversation.id,
       event_start_time: conversation.created_at,
-      event_end_time: conversation.updated_at
+      event_end_time: conversation.updated_at,
+      **base_event_attributes(conversation, from_state: 'bot_handling')
     )
-    reporting_event.save!
   end
 
   def conversation_opened(event)
@@ -99,36 +92,47 @@ class ReportingEventListener < BaseListener
       name: 'conversation_resolved'
     ).order(event_end_time: :desc).first
 
-    # For first-time openings, value is 0
-    # For reopenings, calculate time since resolution
+    # For first-time openings, value is 0, from_state is nil
+    # For reopenings, calculate time since resolution, from_state is 'resolved'
     if last_resolved_event
       time_since_resolved = conversation.updated_at.to_i - last_resolved_event.event_end_time.to_i
       business_hours_value = business_hours(conversation.inbox, last_resolved_event.event_end_time, conversation.updated_at)
       start_time = last_resolved_event.event_end_time
+      from_state = 'resolved'
     else
       time_since_resolved = 0
       business_hours_value = 0
       start_time = conversation.created_at
+      from_state = nil
     end
 
-    create_conversation_opened_event(conversation, time_since_resolved, business_hours_value, start_time)
+    create_conversation_opened_event(conversation, time_since_resolved, business_hours_value, start_time, from_state)
   end
 
   private
 
-  def create_conversation_opened_event(conversation, time_since_resolved, business_hours_value, start_time)
-    reporting_event = ReportingEvent.new(
+  def base_event_attributes(conversation, from_state:, user_id: nil)
+    {
+      account_id: conversation.account_id,
+      inbox_id: conversation.inbox_id,
+      user_id: user_id || conversation.assignee_id,
+      conversation_id: conversation.id,
+      conversation_created_at: conversation.created_at,
+      team_id: conversation.team_id,
+      channel_type: conversation.inbox.channel_type,
+      from_state: from_state
+    }
+  end
+
+  def create_conversation_opened_event(conversation, time_since_resolved, business_hours_value, start_time, from_state)
+    ReportingEvent.create!(
       name: 'conversation_opened',
       value: time_since_resolved,
       value_in_business_hours: business_hours_value,
-      account_id: conversation.account_id,
-      inbox_id: conversation.inbox_id,
-      user_id: conversation.assignee_id,
-      conversation_id: conversation.id,
       event_start_time: start_time,
-      event_end_time: conversation.updated_at
+      event_end_time: conversation.updated_at,
+      **base_event_attributes(conversation, from_state: from_state)
     )
-    reporting_event.save!
   end
 
   def create_bot_resolved_event(conversation, reporting_event)
@@ -138,6 +142,7 @@ class ReportingEventListener < BaseListener
 
     bot_resolved_event = reporting_event.dup
     bot_resolved_event.name = 'conversation_bot_resolved'
+    bot_resolved_event.from_state = 'bot_handling'
     bot_resolved_event.save!
   end
 end

app/models/reporting_event.rb

@@ -3,8 +3,11 @@
 # Table name: reporting_events
 #
 #  id                      :bigint           not null, primary key
+#  channel_type            :string
+#  conversation_created_at :datetime
 #  event_end_time          :datetime
 #  event_start_time        :datetime
+#  from_state              :string
 #  name                    :string
 #  value                   :float
 #  value_in_business_hours :float
@@ -13,17 +16,22 @@
 #  account_id              :integer
 #  conversation_id         :integer
 #  inbox_id                :integer
+#  team_id                 :bigint
 #  user_id                 :integer
 #
 # Indexes
 #
-#  index_reporting_events_on_account_id            (account_id)
-#  index_reporting_events_on_conversation_id       (conversation_id)
-#  index_reporting_events_on_created_at            (created_at)
-#  index_reporting_events_on_inbox_id              (inbox_id)
-#  index_reporting_events_on_name                  (name)
-#  index_reporting_events_on_user_id               (user_id)
-#  reporting_events__account_id__name__created_at  (account_id,name,created_at)
+#  idx_reporting_events_on_account_channel_name_created     (account_id,channel_type,name,created_at)
+#  idx_reporting_events_on_account_conv_created_at_name     (account_id,conversation_created_at,name)
+#  idx_reporting_events_on_account_from_state_name_created  (account_id,from_state,name,created_at)
+#  idx_reporting_events_on_account_team_name_created        (account_id,team_id,name,created_at)
+#  index_reporting_events_on_account_id                     (account_id)
+#  index_reporting_events_on_conversation_id                (conversation_id)
+#  index_reporting_events_on_created_at                     (created_at)
+#  index_reporting_events_on_inbox_id                       (inbox_id)
+#  index_reporting_events_on_name                           (name)
+#  index_reporting_events_on_user_id                        (user_id)
+#  reporting_events__account_id__name__created_at           (account_id,name,created_at)
 #
 
 class ReportingEvent < ApplicationRecord
@@ -35,6 +43,7 @@ class ReportingEvent < ApplicationRecord
   belongs_to :user, optional: true
   belongs_to :inbox, optional: true
   belongs_to :conversation, optional: true
+  belongs_to :team, optional: true
 
   # Scopes for filtering
   scope :filter_by_date_range, lambda { |range|
@@ -52,4 +61,12 @@ class ReportingEvent < ApplicationRecord
   scope :filter_by_name, lambda { |name|
     where(name: name) if name.present?
   }
+
+  scope :filter_by_team_id, lambda { |team_id|
+    where(team_id: team_id) if team_id.present?
+  }
+
+  scope :filter_by_channel_type, lambda { |channel_type|
+    where(channel_type: channel_type) if channel_type.present?
+  }
 end

db/migrate/20260121121101_add_fields_to_reporting_events.rb

@@ -0,0 +1,19 @@
+class AddFieldsToReportingEvents < ActiveRecord::Migration[7.1]
+  disable_ddl_transaction!
+
+  def change
+    add_column :reporting_events, :conversation_created_at, :datetime
+    add_column :reporting_events, :from_state, :string
+    add_column :reporting_events, :team_id, :bigint
+    add_column :reporting_events, :channel_type, :string
+
+    add_index :reporting_events, [:account_id, :conversation_created_at, :name],
+              algorithm: :concurrently, name: 'idx_reporting_events_on_account_conv_created_at_name'
+    add_index :reporting_events, [:account_id, :from_state, :name, :created_at],
+              algorithm: :concurrently, name: 'idx_reporting_events_on_account_from_state_name_created'
+    add_index :reporting_events, [:account_id, :team_id, :name, :created_at],
+              algorithm: :concurrently, name: 'idx_reporting_events_on_account_team_name_created'
+    add_index :reporting_events, [:account_id, :channel_type, :name, :created_at],
+              algorithm: :concurrently, name: 'idx_reporting_events_on_account_channel_name_created'
+  end
+end

db/schema.rb

@@ -10,7 +10,7 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema[7.1].define(version: 2026_01_20_121402) do
+ActiveRecord::Schema[7.1].define(version: 2026_01_21_121101) do
   # These extensions should be enabled to support this database
   enable_extension "pg_stat_statements"
   enable_extension "pg_trgm"
@@ -1114,7 +1114,15 @@ ActiveRecord::Schema[7.1].define(version: 2026_01_20_121402) do
     t.float "value_in_business_hours"
     t.datetime "event_start_time", precision: nil
     t.datetime "event_end_time", precision: nil
+    t.datetime "conversation_created_at"
+    t.string "from_state"
+    t.bigint "team_id"
+    t.string "channel_type"
+    t.index ["account_id", "channel_type", "name", "created_at"], name: "idx_reporting_events_on_account_channel_name_created"
+    t.index ["account_id", "conversation_created_at", "name"], name: "idx_reporting_events_on_account_conv_created_at_name"
+    t.index ["account_id", "from_state", "name", "created_at"], name: "idx_reporting_events_on_account_from_state_name_created"
     t.index ["account_id", "name", "created_at"], name: "reporting_events__account_id__name__created_at"
+    t.index ["account_id", "team_id", "name", "created_at"], name: "idx_reporting_events_on_account_team_name_created"
     t.index ["account_id"], name: "index_reporting_events_on_account_id"
     t.index ["conversation_id"], name: "index_reporting_events_on_conversation_id"
     t.index ["created_at"], name: "index_reporting_events_on_created_at"

lib/scripts/sankey_data_generator.rb

@@ -0,0 +1,234 @@
+# Usage: bundle exec rails runner lib/scripts/sankey_data_generator.rb [account_id] [days]
+# Example: bundle exec rails runner lib/scripts/sankey_data_generator.rb 2 30
+#
+# Generates Sankey diagram data from reporting_events for conversation flow visualization.
+# The flow shows: Conversations → Bot/Direct → Resolution paths
+
+class SankeyDataGenerator
+  def initialize(account_id:, days: 90)
+    @account_id = account_id
+    @days = days
+  end
+
+  def generate
+    {
+      nodes: build_nodes,
+      links: build_links
+    }
+  end
+
+  def to_json(*_args)
+    JSON.pretty_generate(generate)
+  end
+
+  private
+
+  def base_scope
+    ReportingEvent.where(account_id: @account_id).where('conversation_created_at >= ?', @days.days.ago)
+  end
+
+  def conversation_created
+    @conversation_created ||= base_scope.where(name: 'conversation_created').distinct.count(:conversation_id)
+  end
+
+  def bot_handoff
+    @bot_handoff ||= base_scope.where(name: 'conversation_bot_handoff').distinct.count(:conversation_id)
+  end
+
+  def bot_resolved
+    @bot_resolved ||= begin
+      # Exclude conversations that were later handed off (they go through handoff flow instead)
+      bot_resolved_ids = base_scope.where(name: 'conversation_bot_resolved').distinct.pluck(:conversation_id)
+      handoff_ids = base_scope.where(name: 'conversation_bot_handoff').distinct.pluck(:conversation_id)
+      (bot_resolved_ids - handoff_ids).count
+    end
+  end
+
+  def agent_resolved
+    @agent_resolved ||= base_scope.where(name: 'conversation_resolved').distinct.count(:conversation_id)
+  end
+
+  def reopened_conversation_ids
+    @reopened_conversation_ids ||= base_scope
+                                   .where(name: 'conversation_opened', from_state: 'resolved')
+                                   .distinct
+                                   .pluck(:conversation_id)
+  end
+
+  def reopened
+    @reopened ||= reopened_conversation_ids.count
+  end
+
+  # Reopened from bot_resolved (exclusive - not handed off after)
+  def reopened_from_bot
+    @reopened_from_bot ||= begin
+      bot_resolved_ids = base_scope.where(name: 'conversation_bot_resolved').pluck(:conversation_id)
+      handoff_ids = base_scope.where(name: 'conversation_bot_handoff').pluck(:conversation_id)
+      # Only count if bot_resolved but NOT handed off (stayed in bot flow)
+      bot_only_ids = bot_resolved_ids - handoff_ids
+      (reopened_conversation_ids & bot_only_ids).count
+    end
+  end
+
+  # Reopened from handoff_resolved (went through handoff path)
+  def reopened_from_handoff
+    @reopened_from_handoff ||= begin
+      handoff_ids = base_scope.where(name: 'conversation_bot_handoff').pluck(:conversation_id)
+      resolved_after_handoff_ids = base_scope
+                                   .where(name: 'conversation_resolved', conversation_id: handoff_ids)
+                                   .pluck(:conversation_id)
+      (reopened_conversation_ids & resolved_after_handoff_ids).count
+    end
+  end
+
+  # Reopened from direct agent resolution (no bot involvement)
+  def reopened_from_direct
+    @reopened_from_direct ||= [reopened - reopened_from_bot - reopened_from_handoff, 0].max
+  end
+
+  # Conversations assigned to bot (created in bot-enabled inboxes)
+  def bot_assigned
+    @bot_assigned ||= begin
+      return 0 if bot_inbox_ids.empty?
+
+      base_scope.where(name: 'conversation_created', inbox_id: bot_inbox_ids).distinct.count(:conversation_id)
+    end
+  end
+
+  # Conversations that went directly to agent (no bot involvement)
+  def direct_to_agent
+    @direct_to_agent ||= [conversation_created - bot_assigned, 0].max
+  end
+
+  # Count conversations that have both bot_handoff AND conversation_resolved events
+  def resolved_after_handoff
+    @resolved_after_handoff ||= begin
+      handoff_conversation_ids = base_scope
+                                 .where(name: 'conversation_bot_handoff')
+                                 .distinct
+                                 .pluck(:conversation_id)
+
+      return 0 if handoff_conversation_ids.empty?
+
+      base_scope
+        .where(name: 'conversation_resolved')
+        .where(conversation_id: handoff_conversation_ids)
+        .distinct
+        .count(:conversation_id)
+    end
+  end
+
+  # Agent resolutions from direct conversations (no bot involvement)
+  def resolved_direct_by_agent
+    @resolved_direct_by_agent ||= begin
+      # Get conversation IDs that went direct to agent (not in bot inboxes)
+      direct_conversation_ids = base_scope
+                                .where(name: 'conversation_created')
+                                .where.not(inbox_id: bot_inbox_ids)
+                                .distinct
+                                .pluck(:conversation_id)
+
+      return 0 if direct_conversation_ids.empty?
+
+      base_scope
+        .where(name: 'conversation_resolved')
+        .where(conversation_id: direct_conversation_ids)
+        .distinct
+        .count(:conversation_id)
+    end
+  end
+
+  def bot_inbox_ids
+    @bot_inbox_ids ||= Inbox.where(account_id: @account_id).select(&:active_bot?).map(&:id)
+  end
+
+  # Unresolved at bot level (assigned to bot but not resolved or handed off yet)
+  def unresolved_at_bot
+    @unresolved_at_bot ||= [bot_assigned - bot_resolved - bot_handoff, 0].max
+  end
+
+  # Unresolved after handoff (handed off but not yet resolved)
+  def unresolved_after_handoff
+    @unresolved_after_handoff ||= [bot_handoff - resolved_after_handoff, 0].max
+  end
+
+  # Unresolved direct (went to agent but not yet resolved)
+  def unresolved_direct
+    @unresolved_direct ||= [direct_to_agent - resolved_direct_by_agent, 0].max
+  end
+
+  def build_nodes
+    [
+      { id: 'conversations', label: 'Conversations', value: conversation_created, subgroups: %w[bot_flow agent_direct] },
+
+      { id: 'bot_flow', label: 'Assigned to Bot', value: bot_assigned,
+        subgroups: %w[bot_resolved bot_handoff bot_unresolved] },
+
+      { id: 'agent_direct', label: 'Direct to Agent', value: direct_to_agent,
+        subgroups: %w[agent_direct_resolved agent_direct_unresolved] },
+
+      { id: 'bot_resolved', label: 'Resolved by Bot', value: bot_resolved,
+        subgroups: %w[bot_resolved_final bot_resolved_reopened] },
+
+      { id: 'bot_resolved_final', label: 'Stayed Resolved', value: bot_resolved - reopened_from_bot, subgroups: [] },
+      { id: 'bot_resolved_reopened', label: 'Reopened', value: reopened_from_bot, subgroups: [] },
+
+      { id: 'bot_handoff', label: 'Handed off to Agent', value: bot_handoff,
+        subgroups: %w[handoff_resolved handoff_unresolved] },
+
+      { id: 'bot_unresolved', label: 'Unresolved (Bot)', value: unresolved_at_bot, subgroups: [] },
+
+      { id: 'handoff_resolved', label: 'Resolved by Agent', value: resolved_after_handoff,
+        subgroups: %w[handoff_resolved_final handoff_resolved_reopened] },
+
+      { id: 'handoff_resolved_final', label: 'Stayed Resolved', value: resolved_after_handoff - reopened_from_handoff, subgroups: [] },
+      { id: 'handoff_resolved_reopened', label: 'Reopened', value: reopened_from_handoff, subgroups: [] },
+
+      { id: 'handoff_unresolved', label: 'Unresolved', value: unresolved_after_handoff, subgroups: [] },
+
+      { id: 'agent_direct_resolved', label: 'Resolved by Agent', value: resolved_direct_by_agent,
+        subgroups: %w[agent_direct_resolved_final agent_direct_resolved_reopened] },
+
+      { id: 'agent_direct_resolved_final', label: 'Stayed Resolved', value: resolved_direct_by_agent - reopened_from_direct, subgroups: [] },
+      { id: 'agent_direct_resolved_reopened', label: 'Reopened', value: reopened_from_direct, subgroups: [] },
+
+      { id: 'agent_direct_unresolved', label: 'Unresolved', value: unresolved_direct, subgroups: [] }
+    ]
+  end
+
+  def build_links
+    [
+      { source: 'root', target: 'conversations', value: conversation_created },
+
+      { source: 'conversations', target: 'bot_flow', value: bot_assigned },
+      { source: 'conversations', target: 'agent_direct', value: direct_to_agent },
+
+      { source: 'bot_flow', target: 'bot_resolved', value: bot_resolved },
+      { source: 'bot_flow', target: 'bot_handoff', value: bot_handoff },
+      { source: 'bot_flow', target: 'bot_unresolved', value: unresolved_at_bot },
+
+      { source: 'bot_resolved', target: 'bot_resolved_final', value: bot_resolved - reopened_from_bot },
+      { source: 'bot_resolved', target: 'bot_resolved_reopened', value: reopened_from_bot },
+
+      { source: 'bot_handoff', target: 'handoff_resolved', value: resolved_after_handoff },
+      { source: 'bot_handoff', target: 'handoff_unresolved', value: unresolved_after_handoff },
+
+      { source: 'handoff_resolved', target: 'handoff_resolved_final', value: resolved_after_handoff - reopened_from_handoff },
+      { source: 'handoff_resolved', target: 'handoff_resolved_reopened', value: reopened_from_handoff },
+
+      { source: 'agent_direct', target: 'agent_direct_resolved', value: resolved_direct_by_agent },
+      { source: 'agent_direct', target: 'agent_direct_unresolved', value: unresolved_direct },
+
+      { source: 'agent_direct_resolved', target: 'agent_direct_resolved_final', value: resolved_direct_by_agent - reopened_from_direct },
+      { source: 'agent_direct_resolved', target: 'agent_direct_resolved_reopened', value: reopened_from_direct }
+    ]
+  end
+end
+
+if __FILE__ == $PROGRAM_NAME
+  account_id = ARGV[0]&.to_i || 2
+  days = ARGV[1]&.to_i || 90
+
+  generator = SankeyDataGenerator.new(account_id: account_id, days: days)
+  puts generator.to_json
+end

lib/seeders/reports/conversation_creator.rb

@@ -13,6 +13,7 @@ class Seeders::Reports::ConversationCreator
     @teams = resources[:teams]
     @labels = resources[:labels]
     @agents = resources[:agents]
+    @captain_assistant = resources[:captain_assistant]
     @priorities = [nil, 'urgent', 'high', 'medium', 'low']
   end
 
@@ -28,7 +29,13 @@ class Seeders::Reports::ConversationCreator
         conversation.save!
 
         add_labels_to_conversation(conversation)
-        create_messages_for_conversation(conversation)
+
+        # Check if this is a bot inbox or human-only inbox
+        if inbox_has_captain?(conversation.inbox)
+          create_bot_conversation_flow(conversation, created_at)
+        else
+          create_human_conversation_flow(conversation)
+        end
 
         # Determine if should resolve but don't update yet
         should_resolve = rand > 0.3
@@ -100,15 +107,80 @@ class Seeders::Reports::ConversationCreator
     conversation.update_labels(labels_to_add.map(&:title))
   end
 
-  def create_messages_for_conversation(conversation)
+  def create_messages_for_conversation(conversation, include_bot: false)
     message_creator = Seeders::Reports::MessageCreator.new(
       account: @account,
       agents: @agents,
-      conversation: conversation
+      conversation: conversation,
+      captain_assistant: include_bot ? @captain_assistant : nil
     )
     message_creator.create_messages
   end
 
+  def inbox_has_captain?(inbox)
+    return false unless @captain_assistant && inbox.respond_to?(:captain_assistant)
+
+    inbox.captain_assistant.present?
+  end
+
+  def create_bot_conversation_flow(conversation, _created_at)
+    message_creator = Seeders::Reports::MessageCreator.new(
+      account: @account,
+      agents: @agents,
+      conversation: conversation,
+      captain_assistant: @captain_assistant
+    )
+
+    # Create initial customer message
+    message_creator.create_incoming_message
+
+    # Add delay for bot response
+    travel(rand((5.seconds)..(30.seconds)))
+
+    # Create bot response
+    message_creator.create_bot_message
+
+    # Randomly decide outcome: 30% bot resolves, 50% handoff to human, 20% abandoned
+    outcome = rand(100)
+
+    if outcome < 30
+      # Bot resolves - no more messages needed, will be resolved later
+      nil
+    elsif outcome < 80
+      # Bot hands off to human - use travel to move forward from current time
+      travel(rand((1.minute)..(10.minutes)))
+      trigger_bot_handoff_event(conversation)
+      # Continue with human agent messages
+      create_human_messages_after_handoff(message_creator)
+    end
+    # else: 20% abandoned - no further action
+  end
+
+  def create_human_conversation_flow(conversation)
+    create_messages_for_conversation(conversation)
+  end
+
+  def create_human_messages_after_handoff(message_creator)
+    # Create a few more human agent messages after handoff
+    message_count = rand(2..4)
+    message_count.times do |i|
+      travel(rand((1.minute)..(30.minutes)))
+      if i.even?
+        message_creator.create_incoming_message
+      else
+        message_creator.create_human_outgoing_message
+      end
+    end
+  end
+
+  def trigger_bot_handoff_event(conversation)
+    event_data = { conversation: conversation }
+
+    ReportingEventListener.instance.conversation_bot_handoff(
+      Events::Base.new('conversation_bot_handoff', Time.current, event_data)
+    )
+  end
+
   def trigger_conversation_resolved_event(conversation)
     event_data = { conversation: conversation }
 

lib/seeders/reports/message_creator.rb

@@ -8,10 +8,11 @@ class Seeders::Reports::MessageCreator
 
   MESSAGES_PER_CONVERSATION = 5
 
-  def initialize(account:, agents:, conversation:)
+  def initialize(account:, agents:, conversation:, captain_assistant: nil)
     @account = account
     @agents = agents
     @conversation = conversation
+    @captain_assistant = captain_assistant
   end
 
   def create_messages
@@ -37,6 +38,44 @@ class Seeders::Reports::MessageCreator
     false # No longer first reply after any agent message
   end
 
+  def create_incoming_message
+    @conversation.messages.create!(
+      account: @account,
+      inbox: @conversation.inbox,
+      message_type: :incoming,
+      content: generate_customer_message_content,
+      sender: @conversation.contact
+    )
+  end
+
+  def create_bot_message
+    return unless @captain_assistant
+
+    @conversation.messages.create!(
+      account: @account,
+      inbox: @conversation.inbox,
+      message_type: :outgoing,
+      content: generate_bot_response_content,
+      sender: @captain_assistant
+    )
+  end
+
+  def create_human_outgoing_message
+    sender = @conversation.assignee || @agents.sample
+
+    message = @conversation.messages.create!(
+      account: @account,
+      inbox: @conversation.inbox,
+      message_type: :outgoing,
+      content: generate_message_content,
+      sender: sender
+    )
+
+    # Trigger reply events for human messages after handoff
+    trigger_reply_event(message)
+    message
+  end
+
   private
 
   def add_realistic_delay(_message_index, is_incoming)
@@ -64,16 +103,6 @@ class Seeders::Reports::MessageCreator
     end
   end
 
-  def create_incoming_message
-    @conversation.messages.create!(
-      account: @account,
-      inbox: @conversation.inbox,
-      message_type: :incoming,
-      content: generate_message_content,
-      sender: @conversation.contact
-    )
-  end
-
   def create_outgoing_message
     sender = @conversation.assignee || @agents.sample
 
@@ -90,6 +119,39 @@ class Seeders::Reports::MessageCreator
     Faker::Lorem.paragraph(sentence_count: rand(1..5))
   end
 
+  def generate_customer_message_content
+    customer_messages = [
+      'Hi, I need help with my order.',
+      'Can you assist me with a billing issue?',
+      'I have a question about my account.',
+      "I'm having trouble logging in.",
+      'How do I change my password?',
+      'When will my order arrive?',
+      "I'd like to request a refund.",
+      'Can you help me update my shipping address?',
+      'I received the wrong item.',
+      'Is there a way to track my package?',
+      Faker::Lorem.paragraph(sentence_count: rand(1..3))
+    ]
+    customer_messages.sample
+  end
+
+  def generate_bot_response_content
+    bot_responses = [
+      "Hello! I'm here to help. Let me look into that for you.",
+      'Thank you for reaching out. I can assist you with this.',
+      'I understand your concern. Let me check our records.',
+      'Sure, I can help with that! Could you provide more details?',
+      "I've found some information that might help.",
+      'Let me connect you with a specialist who can better assist you.',
+      'Based on your question, here are some options available to you.',
+      'I can see your account details. Let me review this for you.',
+      "Thank you for your patience. I'm processing your request now.",
+      'Is there anything else I can help you with today?'
+    ]
+    bot_responses.sample
+  end
+
   def handle_agent_reply_events(message, is_first_reply)
     if is_first_reply
       trigger_first_reply_event(message)

lib/seeders/reports/report_data_seeder.rb

@@ -8,7 +8,7 @@
 # to generate historical data with realistic timestamps.
 #
 # Usage:
-#   ACCOUNT_ID=1 ENABLE_ACCOUNT_SEEDING=true bundle exec rake db:seed:reports_data
+#   ENABLE_ACCOUNT_SEEDING=true bundle exec rake db:seed:reports_data
 #
 # This will create:
 #   - 1000 conversations with realistic message exchanges
@@ -25,29 +25,36 @@ require 'faker'
 require_relative 'conversation_creator'
 require_relative 'message_creator'
 
-# rubocop:disable Rails/Output
+# rubocop:disable Rails/Output, Metrics/ClassLength
 class Seeders::Reports::ReportDataSeeder
   include ActiveSupport::Testing::TimeHelpers
 
-  TOTAL_CONVERSATIONS = 1000
-  TOTAL_CONTACTS = 100
-  TOTAL_AGENTS = 20
-  TOTAL_TEAMS = 5
-  TOTAL_LABELS = 30
   TOTAL_INBOXES = 3
-  MESSAGES_PER_CONVERSATION = 5
-  START_DATE = 3.months.ago # rubocop:disable Rails/RelativeDateConstant
-  END_DATE = Time.current
 
-  def initialize(account:)
+  def initialize(account:, config: {})
     raise 'Account Seeding is not allowed.' unless ENV.fetch('ENABLE_ACCOUNT_SEEDING', !Rails.env.production?)
 
     @account = account
+    initialize_config(config)
     @teams = []
     @agents = []
     @labels = []
     @inboxes = []
     @contacts = []
+    @captain_assistant = nil
+  end
+
+  def initialize_config(config)
+    defaults = { total_conversations: 1000, total_contacts: 100, total_agents: 20,
+                 total_teams: 5, total_labels: 30, days_range: 90 }
+    config = defaults.merge(config)
+
+    @total_conversations = config[:total_conversations]
+    @total_contacts = config[:total_contacts]
+    @total_agents = config[:total_agents]
+    @total_teams = config[:total_teams]
+    @total_labels = config[:total_labels]
+    @days_range = config[:days_range]
   end
 
   def perform!
@@ -56,6 +63,7 @@ class Seeders::Reports::ReportDataSeeder
     # Clear existing data
     clear_existing_data
 
+    create_captain_assistant
     create_teams
     create_agents
     create_labels
@@ -78,26 +86,38 @@ class Seeders::Reports::ReportDataSeeder
     @account.contacts.destroy_all
     @account.agents.destroy_all
     @account.reporting_events.destroy_all
+    Captain::Assistant.where(account: @account).destroy_all if defined?(Captain::Assistant)
+  end
+
+  def create_captain_assistant
+    return unless defined?(Captain::Assistant)
+
+    @captain_assistant = Captain::Assistant.create!(
+      account: @account,
+      name: 'Support Bot',
+      description: 'AI assistant for customer support'
+    )
+    puts 'Created Captain assistant: Support Bot'
   end
 
   def create_teams
-    TOTAL_TEAMS.times do |i|
+    @total_teams.times do |i|
       team = @account.teams.create!(
         name: "#{Faker::Company.industry} Team #{i + 1}"
       )
       @teams << team
-      print "\rCreating teams: #{i + 1}/#{TOTAL_TEAMS}"
+      print "\rCreating teams: #{i + 1}/#{@total_teams}"
     end
 
     print "\n"
   end
 
   def create_agents
-    TOTAL_AGENTS.times do |i|
+    @total_agents.times do |i|
       user = create_single_agent(i)
       assign_agent_to_teams(user)
       @agents << user
-      print "\rCreating agents: #{i + 1}/#{TOTAL_AGENTS}"
+      print "\rCreating agents: #{i + 1}/#{@total_agents}"
     end
 
     print "\n"
@@ -134,22 +154,33 @@ class Seeders::Reports::ReportDataSeeder
   end
 
   def create_labels
-    TOTAL_LABELS.times do |i|
+    @total_labels.times do |i|
       label = @account.labels.create!(
         title: "Label-#{i + 1}-#{Faker::Lorem.word}",
         description: Faker::Company.catch_phrase,
         color: Faker::Color.hex_color
       )
       @labels << label
-      print "\rCreating labels: #{i + 1}/#{TOTAL_LABELS}"
+      print "\rCreating labels: #{i + 1}/#{@total_labels}"
     end
 
     print "\n"
   end
 
   def create_inboxes
-    TOTAL_INBOXES.times do |_i|
-      inbox = create_single_inbox
+    # Create inboxes: 2/3 with Captain (bot inboxes) and 1/3 without (human-only)
+    bot_inbox_count = (TOTAL_INBOXES * 2 / 3.0).ceil
+    human_inbox_count = TOTAL_INBOXES - bot_inbox_count
+
+    bot_inbox_count.times do |i|
+      inbox = create_web_inbox_with_captain("Support Bot Inbox #{i + 1}")
+      assign_agents_to_inbox(inbox)
+      @inboxes << inbox
+      print "\rCreating inboxes: #{@inboxes.size}/#{TOTAL_INBOXES}"
+    end
+
+    human_inbox_count.times do |i|
+      inbox = create_web_inbox("Human Support Inbox #{i + 1}")
       assign_agents_to_inbox(inbox)
       @inboxes << inbox
       print "\rCreating inboxes: #{@inboxes.size}/#{TOTAL_INBOXES}"
@@ -158,14 +189,27 @@ class Seeders::Reports::ReportDataSeeder
     print "\n"
   end
 
-  def create_single_inbox
+  def create_web_inbox_with_captain(name)
+    inbox = create_web_inbox(name)
+
+    if @captain_assistant && defined?(CaptainInbox)
+      CaptainInbox.create!(
+        captain_assistant: @captain_assistant,
+        inbox: inbox
+      )
+    end
+
+    inbox
+  end
+
+  def create_web_inbox(name)
     channel = Channel::WebWidget.create!(
       website_url: "https://#{Faker::Internet.domain_name}",
       account_id: @account.id
     )
 
     @account.inboxes.create!(
-      name: "#{Faker::Company.name} Website",
+      name: name,
       channel: channel
     )
   end
@@ -176,9 +220,10 @@ class Seeders::Reports::ReportDataSeeder
                          @agents
                        else
                          # Subsequent inboxes get random selection with some overlap
-                         min_agents = [@agents.size / TOTAL_INBOXES, 10].max
-                         max_agents = [(@agents.size * 0.8).to_i, 50].min
-                         @agents.sample(rand(min_agents..max_agents))
+                         min_agents = [(@agents.size / TOTAL_INBOXES), 1].max
+                         max_agents = [(@agents.size * 0.8).to_i, @agents.size].min
+                         sample_count = rand([min_agents, max_agents].min..[min_agents, max_agents].max)
+                         @agents.sample(sample_count)
                        end
 
     agents_to_assign.each do |agent|
@@ -187,7 +232,7 @@ class Seeders::Reports::ReportDataSeeder
   end
 
   def create_contacts
-    TOTAL_CONTACTS.times do |i|
+    @total_contacts.times do |i|
       contact = @account.contacts.create!(
         name: Faker::Name.name,
         email: Faker::Internet.email,
@@ -202,7 +247,7 @@ class Seeders::Reports::ReportDataSeeder
       )
       @contacts << contact
 
-      print "\rCreating contacts: #{i + 1}/#{TOTAL_CONTACTS}"
+      print "\rCreating contacts: #{i + 1}/#{@total_contacts}"
     end
 
     print "\n"
@@ -216,19 +261,20 @@ class Seeders::Reports::ReportDataSeeder
         inboxes: @inboxes,
         teams: @teams,
         labels: @labels,
-        agents: @agents
+        agents: @agents,
+        captain_assistant: @captain_assistant
       }
     )
 
-    TOTAL_CONVERSATIONS.times do |i|
-      created_at = Faker::Time.between(from: START_DATE, to: END_DATE)
+    @total_conversations.times do |i|
+      created_at = Faker::Time.between(from: @days_range.days.ago, to: Time.current)
       conversation_creator.create_conversation(created_at: created_at)
 
-      completion_percentage = ((i + 1).to_f / TOTAL_CONVERSATIONS * 100).round
-      print "\rCreating conversations: #{i + 1}/#{TOTAL_CONVERSATIONS} (#{completion_percentage}%)"
+      completion_percentage = ((i + 1).to_f / @total_conversations * 100).round
+      print "\rCreating conversations: #{i + 1}/#{@total_conversations} (#{completion_percentage}%)"
     end
 
     print "\n"
   end
 end
-# rubocop:enable Rails/Output
+# rubocop:enable Rails/Output, Metrics/ClassLength

lib/tasks/seed_reports_data.rake

@@ -1,24 +1,53 @@
+# rubocop:disable Rails/Output, Metrics/BlockLength
 namespace :db do
   namespace :seed do
     desc 'Seed test data for reports with conversations, contacts, agents, teams, and realistic reporting events'
     task reports_data: :environment do
-      if ENV['ACCOUNT_ID'].blank?
-        puts 'Please provide an ACCOUNT_ID environment variable'
-        puts 'Usage: ACCOUNT_ID=1 ENABLE_ACCOUNT_SEEDING=true bundle exec rake db:seed:reports_data'
-        exit 1
-      end
+      abort 'This task can only be run in development environment.' unless Rails.env.development?
+      abort 'ENABLE_ACCOUNT_SEEDING must be set to true.' unless ENV['ENABLE_ACCOUNT_SEEDING'] == 'true'
 
-      ENV['ENABLE_ACCOUNT_SEEDING'] = 'true' if ENV['ENABLE_ACCOUNT_SEEDING'].blank?
+      account = prompt_for_account
+      config = prompt_for_config
+      abort 'Seeding cancelled.' unless confirm_seeding(account)
 
-      account_id = ENV.fetch('ACCOUNT_ID', nil)
-      account = Account.find(account_id)
-
-      puts "Starting reports data seeding for account: #{account.name} (ID: #{account.id})"
-
-      seeder = Seeders::Reports::ReportDataSeeder.new(account: account)
+      seeder = Seeders::Reports::ReportDataSeeder.new(account: account, config: config)
       seeder.perform!
 
       puts "Finished seeding reports data for account: #{account.name}"
     end
+
+    def prompt(message)
+      print "#{message}: "
+      $stdin.gets.chomp
+    end
+
+    def prompt_with_default(message, default)
+      input = prompt("#{message} (default: #{default})")
+      input.blank? ? default : input.to_i
+    end
+
+    def prompt_for_account
+      input = prompt('Enter Account ID')
+      account = Account.find_by(id: input)
+      abort "Account with ID #{input} not found." if account.nil?
+      account
+    end
+
+    def prompt_for_config
+      {
+        total_conversations: prompt_with_default('Total conversations', 1000),
+        total_contacts: prompt_with_default('Total contacts', 100),
+        total_agents: prompt_with_default('Total agents', 20),
+        total_teams: prompt_with_default('Total teams', 5),
+        total_labels: prompt_with_default('Total labels', 30),
+        days_range: prompt_with_default('Days of data to generate', 90)
+      }
+    end
+
+    def confirm_seeding(account)
+      puts "\nWARNING: This will DELETE all existing data for account '#{account.name}' (ID: #{account.id})"
+      prompt('Are you sure you want to proceed? (yes/no)').downcase == 'yes'
+    end
   end
 end
+# rubocop:enable Rails/Output, Metrics/BlockLength

spec/listeners/reporting_event_listener_spec.rb

@@ -10,6 +10,57 @@ describe ReportingEventListener do
                      account: account, inbox: inbox, conversation: conversation)
   end
 
+  describe '#conversation_created' do
+    it 'creates conversation_created event' do
+      expect(account.reporting_events.where(name: 'conversation_created').count).to be 0
+      event = Events::Base.new('conversation.created', Time.zone.now, conversation: conversation)
+      listener.conversation_created(event)
+      expect(account.reporting_events.where(name: 'conversation_created').count).to be 1
+    end
+
+    it 'sets correct attributes for conversation_created event' do
+      event = Events::Base.new('conversation.created', Time.zone.now, conversation: conversation)
+      listener.conversation_created(event)
+
+      created_event = account.reporting_events.find_by(name: 'conversation_created')
+      expect(created_event.value).to eq 0
+      expect(created_event.account_id).to eq(account.id)
+      expect(created_event.inbox_id).to eq(inbox.id)
+      expect(created_event.conversation_id).to eq(conversation.id)
+      expect(created_event.user_id).to eq(user.id)
+      expect(created_event.event_start_time).to be_within(1.second).of(conversation.created_at)
+      expect(created_event.event_end_time).to be_within(1.second).of(conversation.created_at)
+      expect(created_event.from_state).to be_nil
+      expect(created_event.conversation_created_at).to be_within(1.second).of(conversation.created_at)
+      expect(created_event.channel_type).to eq(inbox.channel_type)
+    end
+
+    context 'when conversation has no assignee' do
+      let(:unassigned_conversation) { create(:conversation, account: account, inbox: inbox, assignee: nil) }
+
+      it 'creates conversation_created event with nil user_id' do
+        event = Events::Base.new('conversation.created', Time.zone.now, conversation: unassigned_conversation)
+        listener.conversation_created(event)
+
+        created_event = account.reporting_events.find_by(name: 'conversation_created', conversation_id: unassigned_conversation.id)
+        expect(created_event.user_id).to be_nil
+      end
+    end
+
+    context 'when conversation has team assigned' do
+      let(:team) { create(:team, account: account) }
+      let(:team_conversation) { create(:conversation, account: account, inbox: inbox, assignee: user, team: team) }
+
+      it 'sets team_id on the event' do
+        event = Events::Base.new('conversation.created', Time.zone.now, conversation: team_conversation)
+        listener.conversation_created(event)
+
+        created_event = account.reporting_events.find_by(name: 'conversation_created', conversation_id: team_conversation.id)
+        expect(created_event.team_id).to eq(team.id)
+      end
+    end
+  end
+
   describe '#conversation_resolved' do
     it 'creates conversation_resolved event' do
       expect(account.reporting_events.where(name: 'conversation_resolved').count).to be 0
@@ -18,6 +69,16 @@ describe ReportingEventListener do
       expect(account.reporting_events.where(name: 'conversation_resolved').count).to be 1
     end
 
+    it 'sets from_state to handling' do
+      event = Events::Base.new('conversation.resolved', Time.zone.now, conversation: conversation)
+      listener.conversation_resolved(event)
+
+      resolved_event = account.reporting_events.find_by(name: 'conversation_resolved')
+      expect(resolved_event.from_state).to eq('handling')
+      expect(resolved_event.conversation_created_at).to be_within(1.second).of(conversation.created_at)
+      expect(resolved_event.channel_type).to eq(inbox.channel_type)
+    end
+
     context 'when business hours enabled for inbox' do
       let(:created_at) { Time.zone.parse('March 20, 2022 00:00') }
       let(:updated_at) { Time.zone.parse('March 26, 2022 23:59') }
@@ -246,6 +307,9 @@ describe ReportingEventListener do
       listener.conversation_bot_handoff(event)
       expect(account.reporting_events.where(name: 'conversation_bot_handoff').count).to be 1
 
+      handoff_event = account.reporting_events.find_by(name: 'conversation_bot_handoff')
+      expect(handoff_event.from_state).to eq('bot_handling')
+
       # add extra handoff event for the same and ensure it's not created
       event = Events::Base.new('conversation.bot_handoff', Time.zone.now, conversation: conversation)
       listener.conversation_bot_handoff(event)
@@ -272,7 +336,7 @@ describe ReportingEventListener do
     context 'when conversation is opened for the first time' do
       let(:new_conversation) { create(:conversation, account: account, inbox: inbox, assignee: user) }
 
-      it 'creates conversation_opened event with value 0' do
+      it 'creates conversation_opened event with value 0 and nil from_state' do
         expect(account.reporting_events.where(name: 'conversation_opened').count).to be 0
         event = Events::Base.new('conversation.opened', Time.zone.now, conversation: new_conversation)
         listener.conversation_opened(event)
@@ -283,6 +347,7 @@ describe ReportingEventListener do
         expect(opened_event.value_in_business_hours).to eq 0
         expect(opened_event.event_start_time).to be_within(1.second).of(new_conversation.created_at)
         expect(opened_event.event_end_time).to be_within(1.second).of(new_conversation.updated_at)
+        expect(opened_event.from_state).to be_nil
       end
     end
 
@@ -332,6 +397,7 @@ describe ReportingEventListener do
         expect(reopened_event.inbox_id).to eq(inbox.id)
         expect(reopened_event.conversation_id).to eq(reopened_conversation.id)
         expect(reopened_event.user_id).to eq(user.id)
+        expect(reopened_event.from_state).to eq('resolved')
       end
 
       context 'when business hours enabled for inbox' do