moreminimore-chat/app/models/conversation_daily_metric.rb

# == Schema Information
#
# Table name: conversation_daily_metrics
#
#  id                           :bigint           not null, primary key
#  account_id                   :bigint           not null
#  date                         :date             not null
#  timezone                     :string
#  message_count                :integer          default(0), not null
#  conversation_count           :integer          default(0), not null
#  resolved_count               :integer          default(0), not null
#  unresolved_count             :integer          default(0), not null
#  avg_session_duration_seconds :float            default(0.0), not null
#  top_tags                     :jsonb            default([]), not null
#  sale_tags                    :jsonb            default([]), not null
#  deal_outcomes                :jsonb            default({}), not null
#  peak_message_hour            :integer
#  peak_deal_hour               :integer
#  agent_breakdown              :jsonb            default([]), not null
#  team_breakdown               :jsonb            default([]), not null
#  channel_breakdown            :jsonb            default([]), not null
#  inbox_breakdown              :jsonb            default([]), not null
#  created_at                   :datetime         not null
#  updated_at                   :datetime         not null
#
class ConversationDailyMetric < ApplicationRecord
  belongs_to :account

  validates :account_id, presence: true
  validates :date, presence: true
  validates :date, uniqueness: { scope: :account_id }

  # JSONB columns default to {} / [] — store hashes/arrays; keep values normalized.
  # Safety rails: always return an Array/Hash even if nil slipped in.
  def top_tags
    self[:top_tags] || []
  end

  def sale_tags
    self[:sale_tags] || []
  end

  def deal_outcomes
    self[:deal_outcomes] || {}
  end

  def agent_breakdown
    self[:agent_breakdown] || []
  end

  def team_breakdown
    self[:team_breakdown] || []
  end

  def channel_breakdown
    self[:channel_breakdown] || []
  end

  def inbox_breakdown
    self[:inbox_breakdown] || []
  end

  # Rollup helpers used by monthly / yearly aggregation.
  def self.rollup(scope)
    total = scope.select('coalesce(sum(message_count),0) as msg').first.msg
    total
  end
end