require "json"
require "josie-health-lut"
require "josie-health-utils"
require "digest/sha256"
require "http/client"
require "../redis_client"
require "../range_parser"

class MetricHandler
  Log = JosieHealth::Utils::Log
  # Wrapped service URL (legacy Ruby service for video generation)
  WRAPPED_SERVICE_URL = ENV["WRAPPED_SERVICE_URL"]? || "http://wrapped:3002"
  # Wrapped-cr service URL (Crystal service for image generation)
  WRAPPED_CR_URL = ENV["WRAPPED_CR_URL"]? || "http://wrapped-cr:3002"

  def initialize(@redis_client : RedisClient)
  end

  # Call the wrapped service to generate image/video
  private def call_wrapped_service(endpoint : String, user_id : String, year : Int32, csv_data : String, callback_url : String? = nil, request_id : String? = nil) : HTTP::Client::Response?
    uri = URI.parse("#{WRAPPED_SERVICE_URL}#{endpoint}")

    payload = {
      "user_id"  => user_id,
      "year"     => year,
      "csv_data" => csv_data,
    }
    payload["callback_url"] = callback_url if callback_url
    payload["request_id"] = request_id if request_id

    begin
      client = HTTP::Client.new(uri.host.not_nil!, uri.port || 3002)
      client.connect_timeout = 10.seconds
      client.read_timeout = 300.seconds # 5 min for image generation

      headers = HTTP::Headers{"Content-Type" => "application/json"}
      client.post(endpoint, headers: headers, body: payload.to_json)
    rescue ex
      Log.error("metric", "Failed to call wrapped service: #{ex.message}")
      nil
    end
  end

  private def call_wrapped_cr_service(user_id : String, year : Int32, callback_url : String? = nil, request_id : String? = nil) : HTTP::Client::Response?
    uri = URI.parse(WRAPPED_CR_URL)

    payload = {
      "user_id" => user_id,
      "year"    => year,
    }
    payload["callback_url"] = callback_url if callback_url
    payload["request_id"] = request_id if request_id

    begin
      client = HTTP::Client.new(uri.host.not_nil!, uri.port || 3002)
      client.connect_timeout = 10.seconds
      client.read_timeout = 300.seconds

      headers = HTTP::Headers{"Content-Type" => "application/json"}
      client.post("/wrapped", headers: headers, body: payload.to_json)
    rescue ex
      Log.error("metric", "Failed to call wrapped-cr service: #{ex.message}")
      nil
    end
  end

  def redis_ping : Bool
    @redis_client.ping
  end

  # Helper method to fetch user entries from Redis
  private def fetch_user_entries(user_id : String) : Array(Hash(String, String))
    doses = @redis_client.get_user_doses(user_id, 1000)
    doses.map do |dose|
      substance = (dose["substance"]? || "").downcase.strip
      route = (dose["route"]? || "").downcase.strip
      normalized_substance = JosieHealth::LUT::DrugLUT.normalize(substance) || substance
      normalized_route = JosieHealth::LUT::RouteLUT.normalize(route) || route
      substance_short = JosieHealth::LUT::DrugLUT.short_name(substance) || ""
      route_short = JosieHealth::LUT::RouteLUT.short_name(route) || ""
      timestamp = dose["timestamp"]? || ""
      local_timestamp = dose["local_timestamp"]? || timestamp
      {
        "timestamp"            => timestamp,
        "local_timestamp"      => local_timestamp,
        "user_id"              => dose["user"]? || user_id,
        "canonical_substance"  => substance,
        "normalized_substance" => normalized_substance,
        "substance_short"      => substance_short,
        "dosage"               => dose["amount"]? || "",
        "canonical_route"      => route,
        "normalized_route"     => normalized_route,
        "route_short"          => route_short,
        "annotation"           => dose["annotation"]? || "",
        "source"               => "redis",
      }
    end
  end

  # Helper method to fetch user entries filtered by substance
  private def fetch_user_entries_for_substance(user_id : String, substance : String) : Array(Hash(String, String))
    normalized_search_substance = JosieHealth::LUT::DrugLUT.normalize(substance.downcase.strip) || substance.downcase.strip

    doses = @redis_client.get_user_doses(user_id, 1000)
    doses.select do |dose|
      raw_substance = (dose["substance"]? || "").downcase.strip
      normalized_substance = JosieHealth::LUT::DrugLUT.normalize(raw_substance) || raw_substance
      normalized_substance == normalized_search_substance
    end.map do |dose|
      substance = (dose["substance"]? || "").downcase.strip
      route = (dose["route"]? || "").downcase.strip
      normalized_substance = JosieHealth::LUT::DrugLUT.normalize(substance) || substance
      normalized_route = JosieHealth::LUT::RouteLUT.normalize(route) || route
      substance_short = JosieHealth::LUT::DrugLUT.short_name(substance) || ""
      route_short = JosieHealth::LUT::RouteLUT.short_name(route) || ""
      timestamp = dose["timestamp"]? || ""
      local_timestamp = dose["local_timestamp"]? || timestamp
      {
        "timestamp"            => timestamp,
        "local_timestamp"      => local_timestamp,
        "user_id"              => dose["user"]? || user_id,
        "canonical_substance"  => substance,
        "normalized_substance" => normalized_substance,
        "substance_short"      => substance_short,
        "dosage"               => dose["amount"]? || "",
        "canonical_route"      => route,
        "normalized_route"     => normalized_route,
        "route_short"          => route_short,
        "annotation"           => dose["annotation"]? || "",
        "source"               => "redis",
      }
    end
  end

  def handle(request : HTTP::Request, response : HTTP::Server::Response)
    case request.path
    when "/v1/metrics/substances"
      handle_substance_metrics(response, nil)
    when .starts_with?("/v1/metrics/substances/")
      year = request.path.split("/")[4]?
      handle_substance_metrics(response, year)
    when "/v1/metrics/timeline"
      handle_timeline_metrics(response)
    when "/v1/metrics/routes"
      handle_route_metrics(response)
    when "/v1/metrics/recent"
      handle_recent_metrics(response)
    when .starts_with?("/v1/metrics/users/")
      handle_user_metrics_endpoint(request, response)
    when .starts_with?("/v1/metrics/wrapped/")
      handle_wrapped_endpoint(request, response)
    when .starts_with?("/v1/metrics/vwrapped/")
      handle_vwrapped_endpoint(request, response)
    when .starts_with?("/v1/metrics/dose_stats/")
      path_parts = request.path.split("/")
      if path_parts.size >= 6
        substance = path_parts[4]
        route = path_parts[5]
        handle_dose_statistics(response, substance, route)
      else
        response.status_code = 400
        response.print({"error" => "Missing substance or route"}.to_json)
      end
    else
      response.status_code = 404
      response.print({"error" => "Metrics endpoint not found"}.to_json)
    end
  end

  def handle_analytics(request : HTTP::Request, response : HTTP::Server::Response)
    response.content_type = "application/json"
    case request.path
    when .match(/\/v1\/analytics\/wrapped\/([^\/]+)/)
      handle_analytics_wrapped(request, response)
    else
      response.status_code = 404
      response.print({"error" => "Analytics endpoint not found"}.to_json)
    end
  end

  private def handle_substance_metrics(response : HTTP::Server::Response, year : String?)
    # Generate metrics directly from Redis CSV, optionally filtered by year
    begin
      csv_content = @redis_client.fetch_csv_content
      lines = csv_content.split('\n')

      # Skip header and empty lines
      data_lines = lines[1..-1].reject(&.empty?)

      # Filter by year if specified
      if year
        data_lines = data_lines.select do |line|
          parts = line.split(',')
          parts.size >= 1 && parts[0].starts_with?(year)
        end
      end

      # Count substances
      substance_counts = Hash(String, Int32).new(0)

      data_lines.each do |line|
        parts = line.split(',')
        if parts.size >= 3
          substance = parts[2].downcase.strip
          substance_counts[substance] += 1 unless substance.empty?
        end
      end

      # Sort by tally and take top 10
      top_substances = substance_counts.to_a
        .sort_by { |_, tally| -tally }
        .first(10)
        .map { |name, tally| {"substance" => name, "tally" => tally} }

      meta = {"total_entries" => data_lines.size, "source" => "redis"}
      meta["year"] = year if year

      response.print({
        "data" => top_substances,
        "meta" => meta,
      }.to_json)
    rescue ex
      response.print({"error" => "Failed to fetch substance metrics from Redis: #{ex.message}"}.to_json)
    end
  end

  private def handle_timeline_metrics(response : HTTP::Server::Response)
    # Real timeline data based on your CSV
    timeline = [
      {"date" => "2025-03", "tally" => 45, "unique_substances" => 12},
      {"date" => "2025-04", "tally" => 18, "unique_substances" => 8},
      {"date" => "2025-05", "tally" => 22, "unique_substances" => 7},
      {"date" => "2025-06", "tally" => 28, "unique_substances" => 9},
      {"date" => "2025-07", "tally" => 67, "unique_substances" => 18},
      {"date" => "2025-08", "tally" => 248, "unique_substances" => 26},
    ]
    response.print({
      "data" => timeline,
      "meta" => {"count" => timeline.size},
    }.to_json)
  end

  private def handle_route_metrics(response : HTTP::Server::Response)
    # Generate route metrics directly from Redis CSV
    begin
      csv_content = @redis_client.fetch_csv_content
      lines = csv_content.split('\n')

      # Skip header and empty lines
      data_lines = lines[1..-1].reject(&.empty?)

      # Count routes
      route_counts = Hash(String, Int32).new(0)

      data_lines.each do |line|
        parts = line.split(',')
        if parts.size >= 5
          route = parts[4].downcase.strip
          route_counts[route] += 1 unless route.empty?
        end
      end

      # Sort by tally
      routes = route_counts.to_a
        .sort_by { |_, tally| -tally }
        .map { |name, tally| {"route" => name, "tally" => tally} }

      response.print({
        "data" => routes,
        "meta" => {"count" => routes.size},
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to fetch route metrics from Redis: #{ex.message}"}.to_json)
    end
  end

  private def handle_dose_statistics(response : HTTP::Server::Response, substance : String, route : String)
    # Generate detailed dose statistics for a specific substance/route combination
    begin
      csv_content = @redis_client.fetch_csv_content
      lines = csv_content.split('\n')

      # Skip header and empty lines
      data_lines = lines[1..-1].reject(&.empty?)

      # Filter for specific substance and route
      doses = [] of Float64
      data_lines.each do |line|
        parts = line.split(',')
        if parts.size >= 5
          line_substance = parts[2].downcase.strip
          line_route = parts[4].downcase.strip
          if line_substance == substance.downcase && line_route == route.downcase
            # Parse dosage amount
            amount_str = parts[3].strip
            if amount_str =~ /^(\d+(?:\.\d+)?)/
              doses << $1.to_f
            end
          end
        end
      end

      if doses.empty?
        response.status_code = 404
        response.print({"error" => "No data found for #{substance}/#{route}"}.to_json)
        return
      end

      # Calculate basic statistics
      sorted_doses = doses.sort
      tally = doses.size
      min_dose = sorted_doses.first
      max_dose = sorted_doses.last
      mean = doses.sum / doses.size
      median = if doses.size.odd?
                 sorted_doses[doses.size // 2]
               else
                 (sorted_doses[doses.size // 2 - 1] + sorted_doses[doses.size // 2]) / 2.0
               end

      # Calculate percentiles
      q1_index = (doses.size * 0.25).to_i
      q3_index = (doses.size * 0.75).to_i
      p95_index = (doses.size * 0.95).to_i

      q1 = sorted_doses[q1_index]
      q3 = sorted_doses[q3_index]
      p95 = sorted_doses[[p95_index, doses.size - 1].min]

      # Standard deviation
      variance = doses.map { |x| (x - mean) ** 2 }.sum / (doses.size - 1)
      std_dev = Math.sqrt(variance)

      response.print({
        "substance"  => substance,
        "route"      => route,
        "statistics" => {
          "tally"   => tally,
          "min"     => min_dose.round(2),
          "max"     => max_dose.round(2),
          "mean"    => mean.round(2),
          "median"  => median.round(2),
          "std_dev" => std_dev.round(2),
          "q1"      => q1.round(2),
          "q3"      => q3.round(2),
          "p95"     => p95.round(2),
        },
        "dose_ranges" => {
          "light"  => "≤#{q1.round(1)}mg",
          "common" => "#{q1.round(1)}-#{median.round(1)}mg",
          "strong" => "#{median.round(1)}-#{q3.round(1)}mg",
          "heavy"  => ">#{q3.round(1)}mg",
        },
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to fetch dose statistics from Redis: #{ex.message}"}.to_json)
    end
  end

  private def handle_recent_metrics(response : HTTP::Server::Response)
    # Get recent entries directly from Redis CSV
    begin
      csv_content = @redis_client.fetch_csv_content
      lines = csv_content.split('\n')

      # Skip header and empty lines
      data_lines = lines[1..-1].reject(&.empty?)

      # Get last 20 entries
      recent_entries = data_lines.last(20).reverse.map do |line|
        parts = line.split(',')
        if parts.size >= 5
          substance = parts[2].downcase.strip
          route = parts[4].downcase.strip
          normalized_substance = JosieHealth::LUT::DrugLUT.normalize(substance) || substance
          normalized_route = JosieHealth::LUT::RouteLUT.normalize(route) || route
          substance_short = JosieHealth::LUT::DrugLUT.short_name(substance)
          route_short = JosieHealth::LUT::RouteLUT.short_name(route)
          {
            "timestamp"            => parts[0],
            "canonical_substance"  => substance,
            "normalized_substance" => normalized_substance,
            "substance_short"      => substance_short,
            "dosage"               => parts[3],
            "canonical_route"      => route,
            "normalized_route"     => normalized_route,
            "route_short"          => route_short,
            "source"               => "redis",
          }
        else
          nil
        end
      end.compact

      response.print({
        "data" => recent_entries,
        "meta" => {"count" => recent_entries.size},
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to fetch recent metrics from Redis: #{ex.message}"}.to_json)
    end
  end

  private def handle_user_metrics_endpoint(request : HTTP::Request, response : HTTP::Server::Response)
    path_parts = request.path.split("/")
    Log.debug("metric", "handle_user_metrics_endpoint path: #{request.path}")

    # Expected format: /v1/metrics/users/{user_id}/{endpoint}
    if path_parts.size < 6
      Log.debug("metric", "Invalid path format - expected 6+ parts")
      response.status_code = 400
      response.print({"error" => "Invalid user metrics endpoint format. Expected: /v1/metrics/users/{user_id}/{endpoint}"}.to_json)
      return
    end

    user_id = URI.decode_www_form(path_parts[4])
    endpoint = path_parts[5]
    Log.debug("metric", "Routing to endpoint: #{endpoint}")

    # Parse query parameters
    query_params = URI::Params.parse(request.query || "")

    case endpoint
    when "lastdose"
      n = query_params["n"]?.try(&.to_i) || 1
      Log.debug("metric", "Routing to handle_user_lastdose with n=#{n}")
      handle_user_lastdose(response, user_id, n)
    when "listdose"
      n = query_params["n"]?.try(&.to_i) || 5
      handle_user_listdose(response, user_id, n)
    when "last_substance"
      substance = query_params["substance"]?
      if substance
        handle_user_last_substance(response, user_id, substance)
      else
        response.status_code = 400
        response.print({"error" => "Missing 'substance' query parameter"}.to_json)
      end
    when "grepdose"
      substance = query_params["substance"]?
      n_str = query_params["n"]?
      roa_str = query_params["roa"]?
      if substance
        # Cast n to int if exists or > 0
        n_int : Int32? = if n_str
          if n_val = n_str.to_i?
            if n_val > 0
              n_val
            else
              response.status_code = 400
              response.print({"error" => "Parameter 'n' must be a positive integer"}.to_json)
              return
            end
          else
            response.status_code = 400
            response.print({"error" => "Parameter 'n' must be a valid integer"}.to_json)
            return
          end
        end

        # Normalize ROA if provided
        roa : String? = if roa_str
          JosieHealth::LUT::RouteLUT.normalize(roa_str) || roa_str.downcase
        end

        handle_user_grepdose(response, user_id, substance, n_int, roa)
      else
        response.status_code = 400
        response.print({"error" => "Missing 'substance' query parameter"}.to_json)
      end
    when "howhighami"
      handle_user_howhighami(response, user_id)
    when "doseslogged"
      handle_user_doseslogged(response, user_id)
    when "listdrugs"
      handle_user_listdrugs(response, user_id)
    when "tally"
      substance = query_params["substance"]?
      if substance
        handle_user_tally(response, user_id, substance)
      else
        response.status_code = 400
        response.print({"error" => "Missing 'substance' query parameter"}.to_json)
      end
    when "active_bars"
      handle_user_active_bars(response, user_id)
    when "categories"
      Log.debug("metric", "Routing to handle_user_categories")
      handle_user_categories(response, user_id)
    when "substances"
      handle_user_substances(response, user_id)
    when "recent_frequency"
      handle_user_recent_frequency(response, user_id)
    else
      response.status_code = 404
      response.print({"error" => "User metric endpoint not found: #{endpoint}"}.to_json)
    end
  end

  private def handle_user_lastdose(response : HTTP::Server::Response, user_id : String, n : Int32)
    begin
      Log.debug("metric", "handle_user_lastdose called with n=#{n}")
      user_entries = fetch_user_entries(user_id)

      if user_entries.empty?
        response.status_code = 404
        response.print({"error" => "No dose entries found for user #{user_id}"}.to_json)
        return
      end

      # Get the nth entry (1-indexed)
      if n > user_entries.size
        response.status_code = 404
        response.print({"error" => "User #{user_id} only has #{user_entries.size} dose entries, requested #{n}"}.to_json)
        return
      end

      nth_entry = user_entries[n - 1]

      response.print({
        "data" => nth_entry,
        "meta" => {
          "user_id"       => user_id,
          "position"      => n,
          "total_entries" => user_entries.size,
        },
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to fetch user dose data: #{ex.message}"}.to_json)
    end
  end

  private def handle_user_listdose(response : HTTP::Server::Response, user_id : String, n : Int32)
    begin
      user_entries = fetch_user_entries(user_id)

      if user_entries.empty?
        response.status_code = 404
        response.print({"error" => "No dose entries found for user #{user_id}"}.to_json)
        return
      end

      # Get the first n entries
      result_entries = user_entries.first(n)

      response.print({
        "data" => result_entries,
        "meta" => {
          "user_id"       => user_id,
          "count"         => result_entries.size,
          "requested"     => n,
          "total_entries" => user_entries.size,
        },
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to fetch user dose list: #{ex.message}"}.to_json)
    end
  end

  private def handle_user_last_substance(response : HTTP::Server::Response, user_id : String, substance : String)
    begin
      substance_entries = fetch_user_entries_for_substance(user_id, substance)

      if substance_entries.empty?
        response.status_code = 404
        response.print({"error" => "No entries found for user #{user_id} and substance #{substance}"}.to_json)
        return
      end

      # Get the most recent entry
      last_entry = substance_entries.first

      response.print({
        "data" => last_entry,
        "meta" => {
          "user_id"                     => user_id,
          "substance"                   => substance,
          "total_entries_for_substance" => substance_entries.size,
        },
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to fetch user substance data: #{ex.message}"}.to_json)
    end
  end

  private def handle_user_grepdose(response : HTTP::Server::Response, user_id : String, substance : String, n : Int32? = nil, roa : String? = nil)
    begin
      substance_entries = fetch_user_entries_for_substance(user_id, substance)

      if substance_entries.empty?
        response.status_code = 404
        response.print({"error" => "No entries found for user #{user_id} and substance #{substance}"}.to_json)
        return
      end

      # Filter by ROA if provided
      filtered_entries = if roa
                           substance_entries.select do |entry|
                             entry_route = entry["route"]?.try(&.downcase.strip) || ""
                             normalized_entry_route = JosieHealth::LUT::RouteLUT.normalize(entry_route) || entry_route
                             normalized_entry_route == roa
                           end
                         else
                           substance_entries
                         end

      if filtered_entries.empty? && roa
        response.status_code = 404
        response.print({"error" => "No entries found for user #{user_id}, substance #{substance}, and route #{roa}"}.to_json)
        return
      end

      # If n is given, validate and slice, else return all
      if n
        if n <= 0
          response.status_code = 400
          response.print({"error" => "Parameter 'n' must be a positive integer"}.to_json)
          return
        end
        if n > filtered_entries.size
          n = filtered_entries.size
        end
        result_entries = filtered_entries.first(n)
      else
        result_entries = filtered_entries
      end

      meta = {
        "user_id"                     => user_id,
        "substance"                   => substance,
        "total_entries_for_substance" => substance_entries.size,
        "requested"                   => n || result_entries.size,
        "count"                       => result_entries.size,
      }
      meta = meta.merge({"roa" => roa, "filtered_count" => filtered_entries.size}) if roa

      response.print({
        "data" => result_entries,
        "meta" => meta,
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to fetch user substance data: #{ex.message}"}.to_json)
    end
  end

  private def handle_user_howhighami(response : HTTP::Server::Response, user_id : String)
    begin
      user_entries = fetch_user_entries(user_id)

      if user_entries.empty?
        response.print({
          "active_substances" => [] of String,
          "meta"              => {"message" => "No dose history found"},
        }.to_json)
        return
      end

      # Load duration and onset data from embedded drugs.json
      duration_estimates = {} of String => Float64
      onset_estimates = {} of String => Float64 # In minutes
      begin
        drugs_data = JosieHealth::LUT::DataFiles.drugs

        drugs_data.as_h.each do |drug_key, drug_info|
          name_json = drug_info["name"]?
          next unless name_json
          name = name_json.as_s

          # Process duration data
          if duration_info = drug_info["formatted_duration"]?
            duration_unit = duration_info["_unit"]?.try(&.as_s) || "hours"

            # Check for simple value format first
            if duration_value_json = duration_info["value"]?
              duration_value = duration_value_json.as_s

              if duration_unit == "hours"
                # Parse duration using simple string split - cleaner than regex
                duration_hours = RangeParser.parse_duration(duration_value)
                duration_estimates[name] = duration_hours if duration_hours
              end
            else
              # Check for route-specific durations (like methylphenidate)
              if duration_info.as_h?
                duration_info.as_h.each do |route, duration_value_json|
                  next if route == "_unit" # Skip the unit field

                  route_key = "#{name}_#{route.to_s.downcase}"
                  duration_value = duration_value_json.as_s

                  if duration_unit == "hours"
                    duration_hours = RangeParser.parse_duration(duration_value)
                    if duration_hours
                      duration_estimates[route_key] = duration_hours
                      # Also add a general entry for the substance
                      duration_estimates[name] = duration_hours unless duration_estimates[name]?
                    end
                  end
                end
              end
            end
          end

          # Process onset data
          if onset_info = drug_info["formatted_onset"]?
            onset_unit = onset_info["_unit"]?.try(&.as_s) || "minutes"

            # Check for simple value format first
            if onset_value_json = onset_info["value"]?
              # Single onset value for all routes
              onset_value = onset_value_json.as_s

              if onset_unit == "minutes"
                onset_minutes = RangeParser.parse_onset(onset_value)
                onset_estimates[name] = onset_minutes if onset_minutes
              end
            elsif onset_info.as_h?
              # Route-specific onsets (oral, insufflated, etc.)
              onset_info.as_h.each do |route, onset_data|
                next if route == "_unit" # Skip the unit field

                route_key = "#{name}_#{route.to_s.downcase}"

                # Handle both string values and nested objects
                onset_value = case onset_data.raw
                              when String
                                onset_data.as_s
                              when Hash
                                onset_data["value"]?.try(&.as_s)
                              else
                                nil
                              end

                if onset_value && onset_unit == "minutes"
                  onset_minutes = RangeParser.parse_onset(onset_value)
                  if onset_minutes
                    onset_estimates[route_key] = onset_minutes
                    # Also add a general entry for the substance
                    onset_estimates[name] = onset_minutes unless onset_estimates[name]?
                  end
                end
              end
            end
          end

          # Add aliases for both duration and onset
          if aliases_json = drug_info["aliases"]?
            aliases_json.as_a.each do |alias_json|
              alias_name = alias_json.as_s
              if duration_estimates.has_key?(name)
                duration_estimates[alias_name] = duration_estimates[name]
              end
              if onset_estimates.has_key?(name)
                onset_estimates[alias_name] = onset_estimates[name]
              end
              # Copy route-specific onset data for aliases
              onset_estimates.each do |key, value|
                if key.starts_with?("#{name}_")
                  route = key.split("_", 2)[1]
                  onset_estimates["#{alias_name}_#{route}"] = value
                end
              end
            end
          end
        end
      rescue ex
        Log.warn("metric", "Could not load drugs.json: #{ex.message}")
        duration_estimates = {} of String => Float64
        onset_estimates = {} of String => Float64
      end

      active_substances = [] of Hash(String, String | Float64 | Nil)
      current_time = Time.utc

      # Process all entries in chronological order to find 8-hour gaps
      recent_substances = [] of Hash(String, String)

      user_entries.each_with_index do |entry, index|
        begin
          dose_time = Time.parse_rfc3339(entry["timestamp"])
          hours_since = (current_time - dose_time).total_hours

          # Check if this is more than 8 hours after the previous entry
          if index > 0
            prev_entry = user_entries[index - 1]
            prev_time = Time.parse_rfc3339(prev_entry["timestamp"])
            gap_hours = (prev_time - dose_time).total_hours

            # If there's an 8+ hour gap, stop including earlier entries
            break if gap_hours >= 8.0
          end

          recent_substances << entry.transform_values(&.to_s)
        rescue Time::Format::Error
          # Skip entries with invalid timestamps
          next
        end
      end

      # Convert entries to library format for analysis
      dose_entries = recent_substances.map do |entry|
        begin
          timestamp = Time.parse_rfc3339(entry["timestamp"])
          # Parse numeric dosage from string (e.g., "100mg" -> 100.0)
          dosage_str = entry["dosage"].to_s
          dosage = if match = dosage_str.match(/^(\d+(?:\.\d+)?)/)
                     match[1].to_f
                   else
                     nil
                   end
          JosieHealth::LUT::DoseEntry.new(
            timestamp: timestamp,
            user: entry["user_id"] || user_id,
            substance: entry["normalized_substance"],
            dosage: dosage,
            unit: nil,
            route: entry["normalized_route"]
          )
        rescue
          nil
        end
      end.compact

      # Use library for session analysis
      analyzer = JosieHealth::LUT::DataAnalysis.new
      session_analysis = analyzer.get_recent_activity(dose_entries, user_id, 8)

      # Group by substance and route combination to handle redoses properly
      substances_data = {} of String => Array(Hash(String, String))
      recent_substances.each do |entry|
        substance = entry["normalized_substance"]
        route = entry["normalized_route"]
        key = "#{substance}_#{route}"
        substances_data[key] ||= [] of Hash(String, String)
        substances_data[key] << entry
      end

      substances_data.each do |substance_route_key, entries|
        # Sort entries by timestamp to get chronological order
        sorted_entries = entries.sort_by { |e| e["timestamp"] }
        most_recent_entry = sorted_entries.last
        substance = most_recent_entry["normalized_substance"]
        route = most_recent_entry["normalized_route"]

        # Calculate total dosage from all recent doses (redoses)
        total_amount = 0.0
        unit = ""
        sorted_entries.each do |entry|
          dosage_str = entry["dosage"]
          if match = dosage_str.match(/^(\d+(?:\.\d+)?)\s*([a-zA-Z]+)?/)
            total_amount += match[1].to_f
            unit = match[2]? || "mg" if unit.empty?
          end
        end

        # Create combined dosage string showing individual + total if redosed
        combined_dosage = if sorted_entries.size > 1
                            individual_doses = sorted_entries.map { |e| e["dosage"] }.join(" + ")
                            total_str = total_amount > 0 ? " (#{total_amount}#{unit} total)" : ""
                            "#{individual_doses}#{total_str}"
                          else
                            most_recent_entry["dosage"]
                          end

        # Check annotation for formulation hints (ir/xr/er)
        note_text = most_recent_entry["annotation"]?.try(&.downcase) || ""
        formulation_hint = if note_text.includes?("ir")
                             "_ir"
                           elsif note_text.includes?("xr") || note_text.includes?("er")
                             "_er"
                           else
                             nil
                           end

        # Track which formulation was used
        formulation_used : String? = nil

        # Get duration from JSON data
        # Try exact route match, then formulation-specific, then prefer XR/ER over IR
        route_key = "#{substance}_#{route}"
        duration_hours = duration_estimates[route_key]?

        unless duration_hours
          # If formulation specified in annotation, try that first
          if formulation_hint
            duration_hours = duration_estimates[route_key + formulation_hint]?
            # Normalize ER to XR for display (they mean the same thing)
            formulation_used = (formulation_hint == "_er" ? "XR" : "IR") if duration_hours
          end

          # Otherwise, try prefix match preferring ER/XR over IR
          unless duration_hours
            er_match : Float64? = nil
            ir_match : Float64? = nil
            duration_estimates.each do |key, value|
              if key.starts_with?(route_key + "_")
                if key.ends_with?("_er") || key.ends_with?("_xr")
                  er_match = value
                else
                  ir_match ||= value
                end
              end
            end
            if er_match
              duration_hours = er_match
              formulation_used = "XR"
            elsif ir_match
              duration_hours = ir_match
              formulation_used = "IR"
            end
          end
        end
        # Only use general substance duration if no route-specific data exists at all
        duration_hours ||= duration_estimates[substance]? unless duration_estimates.keys.any? { |k| k.starts_with?("#{substance}_") }

        # Get onset data
        onset_minutes = onset_estimates[route_key]?

        unless onset_minutes
          # If formulation specified in annotation, try that first
          if formulation_hint
            onset_minutes = onset_estimates[route_key + formulation_hint]?
          end

          # Otherwise, try prefix match preferring ER/XR over IR
          unless onset_minutes
            onset_er_match : Float64? = nil
            onset_ir_match : Float64? = nil
            onset_estimates.each do |key, value|
              if key.starts_with?(route_key + "_")
                if key.ends_with?("_er") || key.ends_with?("_xr")
                  onset_er_match = value
                else
                  onset_ir_match ||= value
                end
              end
            end
            onset_minutes = onset_er_match || onset_ir_match
          end
        end
        # Only use general substance onset if no route-specific data exists at all
        onset_minutes ||= onset_estimates[substance]? unless onset_estimates.keys.any? { |k| k.starts_with?("#{substance}_") }

        begin
          # Use the most recent dose time for time calculations
          dose_time = Time.parse_rfc3339(most_recent_entry["timestamp"])
          hours_since = (current_time - dose_time).total_hours
          minutes_since = hours_since * 60

          time_since_str = if hours_since < 1
                             "#{(hours_since * 60).to_i} minutes ago"
                           else
                             "#{hours_since.round(1)} hours ago"
                           end

          # Determine onset status based on most recent dose
          onset_status = if onset_minutes
                           if minutes_since < onset_minutes
                             "pre-onset"
                           elsif minutes_since < (onset_minutes + 30) # 30min window for coming up
                             "coming-up"
                           else
                             "active"
                           end
                         else
                           nil # No onset data available
                         end

          substance_info = {
            "name"           => substance,
            "route"          => route,
            "dosage"         => combined_dosage,
            "last_dose_time" => dose_time.to_s("%Y-%m-%d %H:%M"),
            "time_since"     => time_since_str,
            "onset_status"   => onset_status,
          } of String => String | Float64 | Nil

          # Add duration info if available
          if duration_hours
            substance_info["estimated_duration"] = "#{duration_hours} hours"
            hours_remaining = duration_hours - hours_since
            substance_info["hours_remaining"] = hours_remaining.round(1)
            substance_info["status"] = hours_remaining > 0 ? "active" : "worn-off"
            substance_info["formulation"] = formulation_used if formulation_used
          else
            substance_info["status"] = "unknown-duration"
          end

          # Add onset info if available
          if onset_minutes
            onset_str = if onset_minutes < 60
                          "#{onset_minutes.to_i} min"
                        else
                          "#{(onset_minutes / 60).round(1)} hr"
                        end
            substance_info["expected_onset"] = onset_str
          end

          active_substances << substance_info
        rescue Time::Format::Error
          # Skip entries with invalid timestamps
          next
        end
      end

      # Build inner data object
      inner_data = {
        "active_substances" => active_substances,
        "session_active"    => session_analysis.session_active,
      }

      # Add session totals if available
      if session_totals = session_analysis.current_session
        session_summary = {
          "entries_count"    => session_totals.entries_count,
          "substances"       => session_totals.substances,
          "duration_minutes" => session_totals.total_duration_minutes,
          "first_dose"       => session_totals.first_dose.to_s("%Y-%m-%d %H:%M UTC"),
          "last_dose"        => session_totals.last_dose.to_s("%Y-%m-%d %H:%M UTC"),
        }
        inner_data = inner_data.merge({"session_summary" => session_summary})
      end

      # Wrap in standard API response format
      response_data = {
        "data" => inner_data,
        "meta" => {
          "total_logged_entries"        => user_entries.size,
          "recent_entries_considered"   => recent_substances.size,
          "unique_substances_in_recent" => substances_data.size,
          "current_time"                => current_time.to_s("%Y-%m-%d %H:%M UTC"),
          "note"                        => "Includes recent substances until 8-hour gap",
        },
      }

      response.print(response_data.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to fetch user howhighami data: #{ex.message}"}.to_json)
    end
  end

  private def handle_user_doseslogged(response : HTTP::Server::Response, user_id : String)
    begin
      user_entries = fetch_user_entries(user_id)

      total_doses = user_entries.size
      since_date = if user_entries.any?
                     oldest_timestamp = user_entries.min_by { |entry| entry["timestamp"] }["timestamp"]
                     Time.parse_rfc3339(oldest_timestamp).to_s("%a %b %d %H:%M:%S %Y")
                   else
                     "No doses logged"
                   end

      response.print({
        "total_doses" => total_doses,
        "since_date"  => since_date,
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to fetch user doses logged data: #{ex.message}"}.to_json)
    end
  end

  private def handle_user_listdrugs(response : HTTP::Server::Response, user_id : String)
    begin
      user_entries = fetch_user_entries(user_id)

      # Extract unique substance names (already normalized by fetch_user_entries)
      substances = Set(String).new
      user_entries.each do |entry|
        normalized_name = entry["normalized_substance"]
        substances.add(normalized_name) unless normalized_name.empty?
      end

      # Categorize substances using drugs.json data
      categorized = {} of String => Array(String)

      # Load drugs data for category information
      begin
        drugs_data = JosieHealth::LUT::DataFiles.drugs

        substances.each do |substance|
          # Look up substance in drugs.json
          if drug_info = drugs_data[substance]?
            if categories = drug_info["categories"]?.try(&.as_a)
              # Use first category as primary
              primary_category = categories.first.as_s
              categorized[primary_category] ||= [] of String
              categorized[primary_category] << substance
            else
              categorized["unknown"] ||= [] of String
              categorized["unknown"] << substance
            end
          else
            categorized["unknown"] ||= [] of String
            categorized["unknown"] << substance
          end
        end
      rescue
        # If drugs.json loading fails, just put everything in unknown
        substances.each do |substance|
          categorized["unknown"] ||= [] of String
          categorized["unknown"] << substance
        end
      end

      # Sort substances within each category
      categorized.each do |category, substance_list|
        categorized[category] = substance_list.sort
      end

      response.print({
        "substances"  => substances.to_a.sort,
        "categorized" => categorized,
        "total_count" => substances.size,
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to fetch user substances data: #{ex.message}"}.to_json)
    end
  end

  private def handle_user_tally(response : HTTP::Server::Response, user_id : String, substance : String)
    begin
      csv_content = @redis_client.fetch_csv_content
      lines = csv_content.split('\n')

      # Skip header and empty lines
      data_lines = lines[1..-1].reject(&.empty?)

      # Normalize search term through LUT
      normalized_search_substance = JosieHealth::LUT::DrugLUT.normalize(substance.downcase.strip) || substance.downcase.strip

      # Filter by user_id and substance
      substance_entries = [] of Hash(String, String | Float64)
      total_amount = 0.0
      unit = ""

      data_lines.each do |line|
        parts = line.split(',')
        if parts.size >= 5 && parts[1] == user_id
          raw_substance = parts[2].downcase.strip
          normalized_substance = JosieHealth::LUT::DrugLUT.normalize(raw_substance) || raw_substance

          if normalized_substance == normalized_search_substance
            dosage_str = parts[3].strip
            amount = 0.0

            # Parse dosage amount and unit
            if match = dosage_str.match(/^(\d+(?:\.\d+)?)\s*([a-zA-Z]+)?/)
              amount = match[1].to_f
              detected_unit = match[2]? || "mg"
              unit = detected_unit if unit.empty?
            end

            raw_route = parts[4].downcase.strip
            normalized_route = JosieHealth::LUT::RouteLUT.normalize(raw_route) || raw_route
            entry = {
              "timestamp"        => parts[0],
              "dosage"           => dosage_str,
              "normalized_route" => normalized_route,
              "amount"           => amount,
            } of String => String | Float64

            substance_entries << entry
            total_amount += amount
          end
        end
      end

      if substance_entries.empty?
        response.status_code = 404
        response.print({"error" => "No entries found for user #{user_id} and substance #{substance}"}.to_json)
        return
      end

      # Calculate average
      entries_count = substance_entries.size
      average_amount = total_amount / entries_count

      # Get most recent entry for last dose info
      last_entry = substance_entries.sort_by { |e| e["timestamp"].as(String) }.last

      tally_data = {
        "normalized_substance" => normalized_search_substance,
        "entries"              => entries_count,
        "total_amount"         => total_amount,
        "average_amount"       => average_amount,
        "unit"                 => unit,
        "last_dose"            => {
          "dosage"           => last_entry["dosage"],
          "normalized_route" => last_entry["normalized_route"],
          "timestamp"        => last_entry["timestamp"],
        },
      }

      response.print({
        "data" => tally_data,
        "meta" => {
          "user_id"              => user_id,
          "substance"            => substance,
          "normalized_substance" => normalized_search_substance,
        },
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to fetch user tally data: #{ex.message}"}.to_json)
    end
  end

  private def handle_wrapped_endpoint(request : HTTP::Request, response : HTTP::Server::Response)
    path_parts = request.path.split("/")
    Log.debug("metric", "handle_wrapped_endpoint: #{request.method} #{request.path}")

    # Expected format: /v1/metrics/wrapped/{user_id}/{year?}/{image?}
    if path_parts.size < 5
      Log.debug("metric", "Invalid wrapped path format")
      response.status_code = 400
      response.print({"error" => "Invalid wrapped endpoint format. Expected: /v1/metrics/wrapped/{user_id}/[{year}]/[image]"}.to_json)
      return
    end

    user_id = URI.decode_www_form(path_parts[4])
    year_or_image = path_parts[5]?
    image_flag = path_parts[6]?

    # Handle POST requests for async processing
    if request.method == "POST"
      handle_wrapped_async_request(request, response, user_id, year_or_image, image_flag)
      return
    end

    # Parse query parameters for GET requests
    query_params = URI::Params.parse(request.query || "")

    # Check for alltime parameter
    alltime = query_params["alltime"]? == "true"

    # Determine if this is an image request
    is_image_request = (year_or_image == "image") || (image_flag == "image")

    if is_image_request
      # Handle image generation request
      year = if year_or_image == "image"
               nil # Current year
             elsif image_flag == "image"
               year_or_image.try(&.to_i)
             else
               nil
             end
      handle_wrapped_image(response, user_id, year)
    elsif alltime
      handle_wrapped_alltime(response, user_id)
    elsif year_or_image
      if year = year_or_image.try(&.to_i)
        handle_wrapped_year(response, user_id, year)
      else
        response.status_code = 400
        response.print({"error" => "Invalid year format"}.to_json)
      end
    else
      # Default to current year
      current_year = Time.utc.year
      handle_wrapped_year(response, user_id, current_year)
    end
  end

  private def handle_wrapped_year(response : HTTP::Server::Response, user_id : String, year : Int32)
    begin
      Log.debug("metric", "handle_wrapped_year: year=#{year}")
      csv_content = @redis_client.fetch_csv_content
      lines = csv_content.split('\n')

      # Skip header and empty lines
      data_lines = lines[1..-1].reject(&.empty?)

      # Filter by user_id and year
      user_entries = [] of Hash(String, String | Int32 | Float64)
      data_lines.each do |line|
        parts = line.split(',')
        if parts.size >= 5 && parts[1].strip == user_id
          begin
            timestamp = Time.parse_rfc3339(parts[0])
            if timestamp.year == year
              # Normalize substance through LUT
              raw_substance = parts[2].strip
              normalized_substance = JosieHealth::LUT::DrugLUT.normalize(raw_substance.downcase) || raw_substance.downcase

              # Parse dosage amount
              dosage_str = parts[3].strip
              amount = if match = dosage_str.match(/^(\d+(?:\.\d+)?)/)
                         match[1].to_f
                       else
                         0.0
                       end

              user_entries << {
                "timestamp"      => parts[0],
                "substance_name" => normalized_substance,
                "dosage"         => dosage_str,
                "amount"         => amount,
                "route"          => parts[4].downcase.strip,
                "month"          => timestamp.month,
                "day_of_week"    => timestamp.day_of_week.to_i,
                "hour"           => timestamp.hour,
              } of String => String | Int32 | Float64
            end
          rescue Time::Format::Error
            # Skip entries with invalid timestamps
            next
          end
        end
      end

      if user_entries.empty?
        response.print({
          "data" => {
            "wrapped_text" => "**#{user_id}'s Substance Wrapped #{year}**\n\nNo data found for #{year}.",
          },
          "meta" => {
            "user_id"       => user_id,
            "year"          => year,
            "total_entries" => 0,
          },
        }.to_json)
        return
      end

      # Generate wrapped statistics
      wrapped_data = generate_wrapped_statistics(user_entries, user_id, year)

      response.print({
        "data" => wrapped_data,
        "meta" => {
          "user_id"       => user_id,
          "year"          => year,
          "total_entries" => user_entries.size,
        },
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to generate wrapped data: #{ex.message}"}.to_json)
    end
  end

  private def handle_wrapped_alltime(response : HTTP::Server::Response, user_id : String)
    begin
      Log.debug("metric", "handle_wrapped_alltime")
      csv_content = @redis_client.fetch_csv_content
      lines = csv_content.split('\n')

      # Skip header and empty lines
      data_lines = lines[1..-1].reject(&.empty?)

      # Filter by user_id for all years
      user_entries = [] of Hash(String, String | Int32 | Float64)
      data_lines.each do |line|
        parts = line.split(',')
        if parts.size >= 5 && parts[1].strip == user_id
          begin
            timestamp = Time.parse_rfc3339(parts[0])

            # Normalize substance through LUT
            raw_substance = parts[2].strip
            normalized_substance = JosieHealth::LUT::DrugLUT.normalize(raw_substance.downcase) || raw_substance.downcase

            # Parse dosage amount
            dosage_str = parts[3].strip
            amount = if match = dosage_str.match(/^(\d+(?:\.\d+)?)/)
                       match[1].to_f
                     else
                       0.0
                     end

            user_entries << {
              "timestamp"      => parts[0],
              "substance_name" => normalized_substance,
              "dosage"         => dosage_str,
              "amount"         => amount,
              "route"          => parts[4].downcase.strip,
              "month"          => timestamp.month,
              "day_of_week"    => timestamp.day_of_week.to_i,
              "hour"           => timestamp.hour,
              "year"           => timestamp.year,
            } of String => String | Int32 | Float64
          rescue Time::Format::Error
            # Skip entries with invalid timestamps
            next
          end
        end
      end

      if user_entries.empty?
        response.print({
          "data" => {
            "wrapped_text" => "**#{user_id}'s All-Time Substance Wrapped**\n\nNo data found.",
          },
          "meta" => {
            "user_id"       => user_id,
            "type"          => "alltime",
            "total_entries" => 0,
          },
        }.to_json)
        return
      end

      # Generate wrapped statistics for all time
      wrapped_data = generate_wrapped_statistics(user_entries, user_id, nil, true)

      response.print({
        "data" => wrapped_data,
        "meta" => {
          "user_id"       => user_id,
          "type"          => "alltime",
          "total_entries" => user_entries.size,
          "years_covered" => user_entries.map { |e| e["year"].as(Int32) }.uniq.sort,
        },
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to generate alltime wrapped data: #{ex.message}"}.to_json)
    end
  end

  private def handle_wrapped_image(response : HTTP::Server::Response, user_id : String, year : Int32?)
    begin
      Log.debug("metric", "handle_wrapped_image: year=#{year}")

      year_param = year || Time.utc.year

      svc_response = call_wrapped_cr_service(user_id, year_param)

      if svc_response.nil?
        response.status_code = 503
        response.print({"error" => "Wrapped service unavailable"}.to_json)
        return
      end

      if svc_response.status_code == 200
        result = JSON.parse(svc_response.body)
        if result["success"]?.try(&.as_bool)
          response.print({
            "data" => {
              "image_url" => result["image_url"]?.try(&.as_s) || "",
              "user_id"   => user_id,
              "year"      => year_param,
            },
            "meta" => {
              "type"         => "wrapped_image",
              "generated_at" => Time.utc.to_rfc3339,
            },
          }.to_json)
        else
          error_msg = result["error"]?.try(&.as_s) || "Unknown error"
          if error_msg.includes?("No data")
            response.status_code = 404
            response.print({"error" => "No data found for #{user_id} in #{year_param}"}.to_json)
          else
            response.status_code = 500
            response.print({"error" => "Failed to generate wrapped image: #{error_msg}"}.to_json)
          end
        end
      else
        response.status_code = svc_response.status_code
        response.print(svc_response.body)
      end
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to generate wrapped image: #{ex.message}"}.to_json)
    end
  end

  # Helper method to generate safe filename for user (matching Ruby implementation)
  private def safe_filename_for_user(user : String) : String
    # Simple hash to prevent path traversal and keep filename safe
    Digest::SHA256.hexdigest(user)[0..12]
  end

  private def handle_vwrapped_endpoint(request : HTTP::Request, response : HTTP::Server::Response)
    path_parts = request.path.split("/")
    Log.debug("metric", "handle_vwrapped_endpoint: #{request.method} #{request.path}")

    # Expected format: /v1/metrics/vwrapped/{user_id}/{year?}
    if path_parts.size < 5
      response.status_code = 400
      response.print({"error" => "Invalid vwrapped endpoint format. Expected: /v1/metrics/vwrapped/{user_id}/[{year}]"}.to_json)
      return
    end

    user_id = URI.decode_www_form(path_parts[4])
    year_str = path_parts[5]?
    year = year_str.try(&.to_i?) || Time.utc.year

    # Check for async POST request
    if request.method == "POST"
      handle_vwrapped_async_request(request, response, user_id, year)
    else
      handle_vwrapped_video(response, user_id, year)
    end
  end

  private def handle_vwrapped_async_request(request : HTTP::Request, response : HTTP::Server::Response, user_id : String, year : Int32)
    begin
      body = request.body.try(&.gets_to_end) || ""
      data = JSON.parse(body)

      request_id = data["request_id"]?.try(&.as_s)
      callback_url = data["callback_url"]?.try(&.as_s)
      is_async = data["async"]?.try(&.as_bool) || false

      unless request_id && callback_url && is_async
        response.status_code = 400
        response.print({"error" => "Missing required fields for async processing: request_id, callback_url, async"}.to_json)
        return
      end

      # Check if user already has an active video generation
      if @redis_client.is_vwrapped_active(user_id)
        response.status_code = 429
        response.print({"error" => "Video generation already in progress for #{user_id}"}.to_json)
        return
      end

      # Try to acquire the lock (15 min TTL)
      unless @redis_client.set_vwrapped_active(user_id, 900)
        response.status_code = 429
        response.print({"error" => "Video generation already in progress for #{user_id}"}.to_json)
        return
      end

      Log.debug("metric", "Starting async vwrapped")

      # Spawn background task
      spawn do
        process_vwrapped_async(user_id, year, request_id, callback_url)
      end

      # Return 202 Accepted immediately
      response.status_code = 202
      response.print({
        "status"     => "accepted",
        "request_id" => request_id,
        "message"    => "Video generation started",
      }.to_json)
    rescue ex : JSON::ParseException
      response.status_code = 400
      response.print({"error" => "Invalid JSON"}.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to start async processing: #{ex.message}"}.to_json)
    end
  end

  private def process_vwrapped_async(user_id : String, year : Int32, request_id : String, callback_url : String)
    begin
      Log.debug("metric", "process_vwrapped_async started")

      # Fetch CSV data from Redis
      csv_content = @redis_client.fetch_csv_content

      # Call the wrapped service with callback_url - it will handle async processing and callback
      svc_response = call_wrapped_service("/vwrapped", user_id, year, csv_content, callback_url, request_id)

      if svc_response.nil?
        @redis_client.clear_vwrapped_active(user_id)
        send_vwrapped_callback(callback_url, request_id, "error", {"error" => "Wrapped service unavailable"})
      elsif svc_response.status_code != 202 && svc_response.status_code != 200
        @redis_client.clear_vwrapped_active(user_id)
        send_vwrapped_callback(callback_url, request_id, "error", {"error" => "Wrapped service error: #{svc_response.body}"})
      end
      # If 202/200, the wrapped service will send the callback when done
      # Note: Lock will be released by the callback handler in the bot

    rescue ex
      # Make sure to release lock on error
      @redis_client.clear_vwrapped_active(user_id)
      Log.error("metric", "Async vwrapped failed: #{ex.message}")
      send_vwrapped_callback(callback_url, request_id, "error", {"error" => ex.message || "Unknown error"})
    end
  end

  private def send_vwrapped_callback(callback_url : String, request_id : String, status : String, data : Hash(String, String | Int32))
    begin
      payload = {
        "request_id" => request_id,
        "status"     => status,
      }.merge(data)

      Log.debug("metric", "Sending vwrapped callback")

      response = HTTP::Client.post(
        callback_url,
        headers: HTTP::Headers{"Content-Type" => "application/json"},
        body: payload.to_json
      )

      Log.debug("metric", "Callback response: #{response.status_code}")
    rescue ex
      Log.error("metric", "Failed to send callback: #{ex.message}")
    end
  end

  private def handle_vwrapped_video(response : HTTP::Server::Response, user_id : String, year : Int32)
    begin
      Log.debug("metric", "handle_vwrapped_video: year=#{year}")

      # Check if user already has an active video generation
      if @redis_client.is_vwrapped_active(user_id)
        response.status_code = 429
        response.print({"error" => "Video generation already in progress for #{user_id}. Please wait for it to complete."}.to_json)
        return
      end

      # Try to acquire the lock (15 min TTL)
      unless @redis_client.set_vwrapped_active(user_id, 900)
        response.status_code = 429
        response.print({"error" => "Video generation already in progress for #{user_id}. Please wait for it to complete."}.to_json)
        return
      end

      Log.debug("metric", "Acquired vwrapped lock")

      # Fetch CSV data from Redis
      csv_content = @redis_client.fetch_csv_content

      # Call the wrapped service (video always returns 202 for async)
      svc_response = call_wrapped_service("/vwrapped", user_id, year, csv_content)

      if svc_response.nil?
        @redis_client.clear_vwrapped_active(user_id)
        response.status_code = 503
        response.print({"error" => "Wrapped service unavailable"}.to_json)
        return
      end

      # Video generation is async - service returns 202
      if svc_response.status_code == 202
        # Return accepted - video is being generated
        response.status_code = 202
        response.print({
          "data" => {
            "status"  => "processing",
            "message" => "Video generation started. This may take several minutes.",
            "user_id" => user_id,
            "year"    => year,
          },
          "meta" => {
            "type"       => "wrapped_video",
            "started_at" => Time.utc.to_rfc3339,
          },
        }.to_json)
        # Note: Lock will be held until video completes or times out
      else
        @redis_client.clear_vwrapped_active(user_id)
        response.status_code = svc_response.status_code
        response.print(svc_response.body)
      end
    rescue ex
      # Make sure to release lock on error
      @redis_client.clear_vwrapped_active(user_id)
      response.status_code = 500
      response.print({"error" => "Failed to generate wrapped video: #{ex.message}"}.to_json)
    end
  end

  private def handle_wrapped_async_request(request : HTTP::Request, response : HTTP::Server::Response, user_id : String, year_or_image : String?, image_flag : String?)
    begin
      # Parse request body
      body = request.body.try(&.gets_to_end) || ""
      data = JSON.parse(body)

      request_id = data["request_id"]?.try(&.as_s)
      callback_url = data["callback_url"]?.try(&.as_s)
      is_async = data["async"]?.try(&.as_bool) || false
      year_param = data["year"]?.try(&.as_i)

      unless request_id && callback_url && is_async
        response.status_code = 400
        response.print({"error" => "Missing required fields for async processing: request_id, callback_url, async"}.to_json)
        return
      end

      # Determine if this is an image request
      is_image_request = (year_or_image == "image") || (image_flag == "image")

      unless is_image_request
        response.status_code = 400
        response.print({"error" => "Async processing currently only supported for image generation"}.to_json)
        return
      end

      # Start async processing
      spawn do
        handle_wrapped_image_async(user_id, year_param, request_id, callback_url)
      end

      # Return immediately with 202 Accepted
      response.status_code = 202
      response.print({
        "message"            => "Async processing started",
        "request_id"         => request_id,
        "estimated_duration" => "2-5 minutes",
      }.to_json)
    rescue JSON::ParseException
      response.status_code = 400
      response.print({"error" => "Invalid JSON in request body"}.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to start async processing: #{ex.message}"}.to_json)
    end
  end

  private def handle_wrapped_image_async(user_id : String, year : Int32?, request_id : String, callback_url : String)
    begin
      Log.debug("metric", "Starting async wrapped image processing")

      year_param = year || Time.utc.year

      svc_response = call_wrapped_cr_service(user_id, year_param, callback_url, request_id)

      if svc_response.nil?
        # Service unavailable, send error callback
        callback_data = {
          "request_id" => request_id,
          "status"     => "error",
          "error"      => "Wrapped service unavailable",
        }
        send_callback(callback_url, callback_data)
      elsif svc_response.status_code != 202 && svc_response.status_code != 200
        # Error from wrapped service
        callback_data = {
          "request_id" => request_id,
          "status"     => "error",
          "error"      => "Wrapped service error: #{svc_response.body}",
        }
        send_callback(callback_url, callback_data)
      end
      # If 202/200, the wrapped service will send the callback when done
    rescue ex
      Log.error("metric", "Async wrapped image processing failed: #{ex.message}")

      # Send error callback
      callback_data = {
        "request_id" => request_id,
        "status"     => "error",
        "error"      => "Internal server error: #{ex.message}",
      }
      send_callback(callback_url, callback_data)
    end
  end

  private def send_callback(callback_url : String, data : Hash(String, String | Int32))
    begin
      Log.debug("metric", "Sending callback")

      headers = HTTP::Headers.new
      headers["Content-Type"] = "application/json"

      response = HTTP::Client.post(callback_url, headers: headers, body: data.to_json)

      if response.status_code == 200
        Log.debug("metric", "Callback sent successfully")
      else
        Log.warn("metric", "Callback failed with status #{response.status_code}")
      end
    rescue ex
      Log.error("metric", "Failed to send callback: #{ex.message}")
    end
  end

  # HRT substances always excluded from wrapped analytics
  HRT_SUBSTANCES = Set{
    "estradiol", "estradiol enanthate", "estradiol hemihydrate",
    "estradiol undecylate", "estradiol valerate", "progesterone",
    "triptorelin", "cyproterone acetate", "bicalutamide", "spironolactone",
  }

  NON_RECREATIONAL_CATEGORIES = Set{
    "supplement", "beta-blocker", "antiemetic",
    "cardiovascular", "prokinetic", "hormone",
    "estrogen", "medication", "analgesic", "antipyretic",
  }

  # Substances that should never be filtered (controlled/scheduled)
  CONTROLLED_SUBSTANCES = Set{
    "alprazolam", "clonazepam", "diazepam", "lorazepam", "temazepam",
    "oxazepam", "midazolam", "nitrazepam", "bromazepam", "zolpidem",
    "methylphenidate", "amphetamine", "dextroamphetamine", "lisdexamfetamine",
    "methamphetamine", "cocaine", "dextroamphetamine phosphate",
    "morphine", "codeine", "oxycodone", "hydrocodone", "hydromorphone",
    "fentanyl", "methadone", "buprenorphine", "heroin", "tramadol",
    "ketamine", "esketamine", "ghb", "pregabalin", "gabapentin",
    "modafinil", "armodafinil",
  }

  private def filter_doses(entries : Array(Hash(String, String | Int32 | Float64)), exclude : Array(String)) : Array(Hash(String, String | Int32 | Float64))
    filtered = entries

    return filtered if exclude.empty?

    begin
      drugs_data = JosieHealth::LUT::DataFiles.drugs
    rescue
      return filtered
    end

    filtered.reject do |entry|
      substance = entry["substance_name"].as(String).downcase
      next false if CONTROLLED_SUBSTANCES.includes?(substance)

      canonical = JosieHealth::LUT::DrugLUT.normalize(substance) || substance
      should_exclude = false
      if drug_info = drugs_data[canonical]?
        categories = drug_info["categories"]?.try(&.as_a.map(&.as_s)) || [] of String

        if exclude.includes?("supplements")
          should_exclude = true if categories.includes?("supplement")
        end

        if exclude.includes?("otc") && !should_exclude
          should_exclude = true if categories.includes?("medication") || categories.includes?("otc")
        end
      end
      should_exclude
    end
  end

  private def partition_entries(entries : Array(Hash(String, String | Int32 | Float64))) : NamedTuple(main: Array(Hash(String, String | Int32 | Float64)), supplements: Array(Hash(String, String | Int32 | Float64)))
    main = [] of Hash(String, String | Int32 | Float64)
    supplements = [] of Hash(String, String | Int32 | Float64)

    begin
      drugs_data = JosieHealth::LUT::DataFiles.drugs
    rescue
      return {main: entries, supplements: supplements}
    end

    entries.each do |entry|
      substance = entry["substance_name"].as(String).downcase
      if CONTROLLED_SUBSTANCES.includes?(substance)
        main << entry
        next
      end

      is_supplement = false
      canonical = JosieHealth::LUT::DrugLUT.normalize(substance) || substance
      if drug_info = drugs_data[canonical]?
        categories = drug_info["categories"]?.try(&.as_a.map(&.as_s)) || [] of String
        is_supplement = categories.any? { |c| NON_RECREATIONAL_CATEGORIES.includes?(c) }
      end

      if is_supplement
        supplements << entry
      else
        main << entry
      end
    end

    {main: main, supplements: supplements}
  end

  private def categorize_substance(substance : String) : String
    begin
      cats = JosieHealth::LUT::DataFiles.categories(substance.downcase)
      cats.first? || "other"
    rescue
      "other"
    end
  end

  private def compute_global_comparison(user_entries_count : Int32, user_unique_substances : Int32, year : Int32) : Hash(String, Int32 | Float64 | Hash(String, Int32))
    csv_content = @redis_client.fetch_csv_content
    lines = csv_content.split('\n')
    data_lines = lines[1..-1].reject(&.empty?)

    user_totals = Hash(String, Int32).new(0)
    user_active_days = Hash(String, Set(String)).new { |h, k| h[k] = Set(String).new }
    global_substance_counts = Hash(String, Int32).new(0)
    user_substance_sets = Hash(String, Set(String)).new { |h, k| h[k] = Set(String).new }

    data_lines.each do |line|
      parts = line.split(',')
      next unless parts.size >= 3
      begin
        timestamp = Time.parse_rfc3339(parts[0])
        next unless timestamp.year == year
      rescue
        next
      end
      user = parts[1].strip
      substance = parts[2].downcase.strip
      user_totals[user] += 1
      user_active_days[user] << timestamp.to_s("%Y-%m-%d")
      global_substance_counts[substance] += 1
      user_substance_sets[user] << substance
    end

    all_counts = user_totals.values.sort
    total_users = all_counts.size

    entries_percentile = if total_users > 0
                           rank = all_counts.count { |c| c < user_entries_count }
                           ((rank.to_f / total_users) * 100).round.to_i
                         else
                           0
                         end

    unique_counts = user_substance_sets.values.map(&.size).sort
    unique_percentile = if total_users > 0
                          rank = unique_counts.count { |c| c < user_unique_substances }
                          ((rank.to_f / total_users) * 100).round.to_i
                        else
                          0
                        end

    total_active_days = user_active_days.values.map(&.size).sum
    avg_doses_global = if total_active_days > 0
                         (all_counts.sum.to_f / total_active_days).round(1)
                       else
                         0.0
                       end

    top_global = global_substance_counts.to_a.sort_by { |_, c| -c }
    substance_rank = Hash(String, Int32).new
    top_global.each_with_index do |pair, idx|
      substance_rank[pair[0]] = idx + 1
    end

    {
      "total_entries_percentile"     => entries_percentile,
      "unique_substances_percentile" => unique_percentile,
      "top_substance_global_rank"    => substance_rank,
      "avg_doses_per_day_global"     => avg_doses_global,
    } of String => Int32 | Float64 | Hash(String, Int32)
  end

  private def handle_analytics_wrapped(request : HTTP::Request, response : HTTP::Server::Response)
    begin
      path_parts = request.path.split("/")
      if path_parts.size < 5
        response.status_code = 400
        response.print({"error" => "Expected: /v1/analytics/wrapped/{user_id}"}.to_json)
        return
      end

      user_id = URI.decode_www_form(path_parts[4])
      query_params = URI::Params.parse(request.query || "")
      year = (query_params["year"]?.try(&.to_i?) || Time.utc.year)
      exclude_param = query_params["exclude"]? || ""
      exclude_filters = exclude_param.split(',').map(&.strip).reject(&.empty?)

      csv_content = @redis_client.fetch_csv_content
      lines = csv_content.split('\n')
      data_lines = lines[1..-1].reject(&.empty?)

      user_entries = [] of Hash(String, String | Int32 | Float64)
      data_lines.each do |line|
        parts = line.split(',')
        if parts.size >= 5 && parts[1].strip == user_id
          begin
            timestamp = Time.parse_rfc3339(parts[0])
            if timestamp.year == year
              raw_substance = parts[2].strip
              normalized_substance = JosieHealth::LUT::DrugLUT.normalize(raw_substance.downcase) || raw_substance.downcase
              dosage_str = parts[3].strip

              user_entries << {
                "timestamp"      => parts[0],
                "substance_name" => normalized_substance,
                "dosage"         => dosage_str,
                "route"          => parts[4].downcase.strip,
                "month"          => timestamp.month,
                "day_of_week"    => timestamp.day_of_week.to_i,
                "hour"           => timestamp.hour,
              } of String => String | Int32 | Float64
            end
          rescue Time::Format::Error
            next
          end
        end
      end

      if user_entries.empty?
        response.print({
          "data" => {
            "user_id"       => user_id,
            "year"          => year,
            "total_entries" => 0,
          },
        }.to_json)
        return
      end

      all_filtered = filter_doses(user_entries, exclude_filters)
      partitioned = partition_entries(all_filtered)
      entries = partitioned[:main]

      supplement_counts = Hash(String, Int32).new(0)
      partitioned[:supplements].each { |e| supplement_counts[e["substance_name"].as(String)] += 1 }
      top_supplements = supplement_counts.to_a.sort_by { |_, c| -c }.first(10)

      all_unique_substances = Set(String).new
      all_filtered.each { |e| all_unique_substances << e["substance_name"].as(String) }
      all_total_entries = all_filtered.size
      all_unique_count = all_unique_substances.size
      all_dates = Set(String).new
      all_filtered.each do |e|
        if ts = e["timestamp"]?.try(&.as(String))
          begin
            all_dates << Time.parse_rfc3339(ts).to_s("%Y-%m-%d")
          rescue
          end
        end
      end
      all_days_active = all_dates.size

      # Substance counts
      substance_counts = Hash(String, Int32).new(0)
      entries.each { |e| substance_counts[e["substance_name"].as(String)] += 1 }

      top_substances = substance_counts.to_a.sort_by { |_, c| -c }.first(10)

      # Monthly breakdown
      monthly_counts = Hash(Int32, Int32).new(0)
      entries.each { |e| monthly_counts[e["month"].as(Int32)] += 1 }

      # Category distribution
      category_counts = Hash(String, Int32).new(0)
      entries.each do |e|
        cat = categorize_substance(e["substance_name"].as(String))
        category_counts[cat] += 1
      end

      # Monthly categories
      monthly_categories = Hash(Int32, Hash(String, Int32)).new { |h, k| h[k] = Hash(String, Int32).new(0) }
      entries.each do |e|
        month = e["month"].as(Int32)
        cat = categorize_substance(e["substance_name"].as(String))
        monthly_categories[month][cat] += 1
      end

      # Time of day analysis
      time_periods = {
        "morning"   => (6..11),
        "afternoon" => (12..17),
        "evening"   => (18..22),
        "night"     => [0, 1, 2, 3, 4, 5, 23],
      }
      time_analysis = Hash(String, Int32).new(0)
      entries.each do |e|
        hour = e["hour"].as(Int32)
        period = time_periods.find { |_, hours|
          hours.is_a?(Range) ? hours.includes?(hour) : hours.includes?(hour)
        }.try(&.first) || "unknown"
        time_analysis[period] += 1
      end

      # Day of week analysis (0=Monday, 6=Sunday)
      day_names = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
      day_analysis = Hash(String, Int32).new(0)
      entries.each do |e|
        day_num = e["day_of_week"].as(Int32)
        day_name = day_names[day_num]? || "Unknown"
        day_analysis[day_name] += 1
      end

      # Route analysis
      route_counts = Hash(String, Int32).new(0)
      entries.each { |e| route_counts[e["route"].as(String)] += 1 }

      # Parse timestamps and sort
      entries_with_ts = entries.compact_map do |entry|
        if ts = entry["timestamp"]?.try(&.as(String))
          begin
            time = Time.parse_rfc3339(ts)
            {entry: entry, time: time, date: time.to_s("%Y-%m-%d")}
          rescue
            nil
          end
        end
      end.sort_by { |e| e[:time] }

      unique_dates = entries_with_ts.map { |e| e[:date] }.uniq.sort
      days_active = unique_dates.size

      # First and last dose
      first_dose_entry = entries_with_ts.first?
      last_dose_entry = entries_with_ts.last?

      # Substance streaks (consecutive days per substance)
      substance_dates = Hash(String, Array(String)).new { |h, k| h[k] = [] of String }
      entries_with_ts.each do |e|
        sub = e[:entry]["substance_name"].as(String)
        d = e[:date]
        substance_dates[sub] << d unless substance_dates[sub].includes?(d)
      end

      streaks = Hash(String, Int32).new
      substance_dates.each do |substance, dates|
        sorted = dates.sort
        max_streak = 1
        current = 1
        (1...sorted.size).each do |i|
          begin
            prev = Time.parse(sorted[i - 1], "%Y-%m-%d", Time::Location::UTC)
            curr = Time.parse(sorted[i], "%Y-%m-%d", Time::Location::UTC)
            if (curr - prev).total_days.to_i == 1
              current += 1
              max_streak = current if current > max_streak
            else
              current = 1
            end
          rescue
            current = 1
          end
        end
        streaks[substance] = max_streak if max_streak > 1
      end

      # Longest sober streak
      longest_sober_streak = 0
      if unique_dates.size > 1
        (1...unique_dates.size).each do |i|
          begin
            prev = Time.parse(unique_dates[i - 1], "%Y-%m-%d", Time::Location::UTC)
            curr = Time.parse(unique_dates[i], "%Y-%m-%d", Time::Location::UTC)
            gap = (curr - prev).total_days.to_i - 1
            longest_sober_streak = gap if gap > longest_sober_streak
          rescue
          end
        end
      end

      # Hardest session (contiguous doses with no gap > 2 hours)
      hardest_session_data = nil
      if entries_with_ts.size > 0
        sessions = [] of Array(typeof(entries_with_ts.first))
        current_session = [entries_with_ts.first]
        (1...entries_with_ts.size).each do |i|
          gap = (entries_with_ts[i][:time] - entries_with_ts[i - 1][:time]).total_hours
          if gap > 2.0
            sessions << current_session
            current_session = [entries_with_ts[i]]
          else
            current_session << entries_with_ts[i]
          end
        end
        sessions << current_session

        biggest = sessions.max_by { |s| s.size }
        if biggest.size >= 2
          start_time = biggest.first[:time]
          end_time = biggest.last[:time]
          duration_h = (end_time - start_time).total_hours.round(1)
          doses = biggest.map do |e|
            {
              "time"      => e[:time].to_s("%H:%M"),
              "substance" => e[:entry]["substance_name"]?.try(&.as(String)) || "unknown",
              "dosage"    => e[:entry]["dosage"]?.try(&.as(String)) || "",
              "route"     => e[:entry]["route"]?.try(&.as(String)) || "",
            }
          end
          hardest_session_data = {
            "date"           => start_time.to_s("%Y-%m-%d"),
            "count"          => biggest.size,
            "duration_hours" => duration_h,
            "doses"          => doses,
          }
        end
      end

      # Daily substances for combo and poly-day analysis
      daily_substances = Hash(String, Set(String)).new { |h, k| h[k] = Set(String).new }
      entries_with_ts.each do |e|
        if sub = e[:entry]["substance_name"]?.try(&.as(String))
          daily_substances[e[:date]] << sub
        end
      end

      # Most common combo
      combo_counts = Hash(Array(String), Int32).new(0)
      daily_substances.each do |_, substances|
        if substances.size >= 2
          combo = substances.to_a.sort
          combo_counts[combo] += 1
        end
      end
      common_combo = combo_counts.max_by? { |_, c| c }

      # Poly-substance days
      poly_days = daily_substances.count { |_, subs| subs.size >= 3 }

      # Weekend warrior %
      weekend_doses = entries_with_ts.count { |e| [5, 6].includes?(e[:time].day_of_week.to_i) }
      weekend_pct = entries.size > 0 ? (weekend_doses.to_f / entries.size * 100).round(1) : 0.0

      # One-time substances
      one_time_substances = substance_counts.select { |_, c| c == 1 }.keys.sort

      # Average doses per active day
      avg_doses_per_active_day = days_active > 0 ? (entries.size.to_f / days_active).round(2) : 0.0

      # Diversity score (Shannon entropy)
      total = entries.size.to_f
      diversity_score = if total > 0 && substance_counts.size > 1
                          entropy = 0.0
                          substance_counts.each_value do |count|
                            p = count.to_f / total
                            entropy -= p * Math.log2(p) if p > 0
                          end
                          entropy.round(2)
                        else
                          0.0
                        end

      # Velocity trends (H1 vs H2 for top 3 substances)
      velocity = top_substances.first(3).map do |pair|
        substance = pair[0]
        h1 = entries.count { |e| e["substance_name"].as(String) == substance && e["month"].as(Int32) <= 6 }
        h2 = entries.count { |e| e["substance_name"].as(String) == substance && e["month"].as(Int32) > 6 }
        pct_change = h1 > 0 ? (((h2 - h1).to_f / h1) * 100).round(1) : (h2 > 0 ? 100.0 : 0.0)
        trend = h2 > h1 ? "up" : (h2 < h1 ? "down" : "flat")
        {"substance" => substance, "h1" => h1, "h2" => h2, "trend" => trend, "pct_change" => pct_change}
      end

      # Substance gaps (longest break per top-5 substance)
      substance_gaps = top_substances.first(5).map do |pair|
        substance = pair[0]
        dates = substance_dates[substance]?.try(&.sort) || [] of String
        longest_gap = 0
        if dates.size > 1
          (1...dates.size).each do |i|
            begin
              prev = Time.parse(dates[i - 1], "%Y-%m-%d", Time::Location::UTC)
              curr = Time.parse(dates[i], "%Y-%m-%d", Time::Location::UTC)
              gap = (curr - prev).total_days.to_i
              longest_gap = gap if gap > longest_gap
            rescue
            end
          end
        end
        {"substance" => substance, "longest_gap_days" => longest_gap}
      end

      # Peak month
      peak_month_entry = monthly_counts.max_by? { |_, c| c }

      # Global comparison
      global = compute_global_comparison(entries.size, substance_counts.size, year)

      # Limit global rank to user's top substances only
      top_substance_rank = Hash(String, Int32).new
      if rank_data = global["top_substance_global_rank"]?.as?(Hash(String, Int32))
        top_substances.first(5).each do |pair|
          sub_lower = pair[0].downcase
          if r = rank_data[sub_lower]?
            top_substance_rank[pair[0]] = r
          end
        end
      end

      response.print({
        "data" => {
          "user_id"                  => user_id,
          "year"                     => year,
          "filters_applied"          => exclude_filters,
          "total_entries"            => all_total_entries,
          "unique_substances"        => all_unique_count,
          "days_active"              => all_days_active,
          "top_substances"           => top_substances.map { |s, c| {"name" => s, "count" => c} },
          "monthly_breakdown"        => monthly_counts.transform_keys(&.to_s),
          "category_distribution"    => category_counts,
          "monthly_categories"       => monthly_categories.transform_keys(&.to_s),
          "time_analysis"            => time_analysis,
          "day_analysis"             => day_analysis,
          "route_analysis"           => route_counts,
          "one_time_substances"      => one_time_substances,
          "top_supplements"          => top_supplements.map { |s, c| {"name" => s, "count" => c} },
          "streaks"                  => streaks,
          "longest_sober_streak"     => longest_sober_streak,
          "hardest_session"          => hardest_session_data,
          "velocity"                 => velocity,
          "avg_doses_per_active_day" => avg_doses_per_active_day,
          "diversity_score"          => diversity_score,
          "common_combo"             => common_combo ? {"substances" => common_combo.first, "days" => common_combo.last} : nil,
          "poly_days"                => poly_days,
          "weekend_pct"              => weekend_pct,
          "first_dose"               => first_dose_entry ? {"date" => first_dose_entry[:date], "substance" => first_dose_entry[:entry]["substance_name"]?.try(&.as(String))} : nil,
          "last_dose"                => last_dose_entry ? {"date" => last_dose_entry[:date], "substance" => last_dose_entry[:entry]["substance_name"]?.try(&.as(String))} : nil,
          "substance_gaps"           => substance_gaps,
          "peak_month"               => peak_month_entry ? {"month" => peak_month_entry.first, "count" => peak_month_entry.last} : nil,
          "global_comparison"        => {
            "total_entries_percentile"     => global["total_entries_percentile"]?,
            "unique_substances_percentile" => global["unique_substances_percentile"]?,
            "top_substance_global_rank"    => top_substance_rank,
            "avg_doses_per_day_global"     => global["avg_doses_per_day_global"]?,
          },
        },
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to generate wrapped analytics: #{ex.message}"}.to_json)
    end
  end

  private def generate_wrapped_statistics(entries : Array(Hash(String, String | Int32 | Float64)), user_id : String, year : Int32?, alltime = false)
    # Substance counts
    substance_counts = Hash(String, Int32).new(0)
    entries.each do |entry|
      substance_counts[entry["substance_name"].as(String)] += 1
    end

    # Top substances
    top_substances = substance_counts.to_a.sort_by { |_, count| -count }.first(10)

    # Monthly breakdown
    monthly_counts = Hash(Int32, Int32).new(0)
    entries.each do |entry|
      monthly_counts[entry["month"].as(Int32)] += 1
    end

    # Time of day analysis
    time_periods = {
      "morning"   => (6..11),
      "afternoon" => (12..17),
      "evening"   => (18..22),
      "night"     => [0, 1, 2, 3, 4, 5, 23],
    }

    time_analysis = Hash(String, Int32).new(0)
    entries.each do |entry|
      hour = entry["hour"].as(Int32)
      period = time_periods.find { |_, hours|
        hours.is_a?(Range) ? hours.includes?(hour) : hours.includes?(hour)
      }.try(&.first) || "unknown"
      time_analysis[period] += 1
    end

    # Day of week analysis (0=Monday, 6=Sunday)
    day_names = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"]
    day_analysis = Hash(String, Int32).new(0)
    entries.each do |entry|
      day_num = entry["day_of_week"].as(Int32)
      day_name = day_names[day_num]? || "Unknown"
      day_analysis[day_name] += 1
    end

    # Route analysis
    route_counts = Hash(String, Int32).new(0)
    entries.each do |entry|
      route_counts[entry["route"].as(String)] += 1
    end

    # === Roast-friendly stats ===

    # Get unique dates and sort entries by timestamp
    entries_with_ts = entries.compact_map do |entry|
      if ts = entry["timestamp"]?.try(&.as(String))
        begin
          time = Time.parse_rfc3339(ts)
          {entry: entry, time: time, date: time.to_s("%Y-%m-%d")}
        rescue
          nil
        end
      end
    end.sort_by { |e| e[:time] }

    unique_dates = entries_with_ts.map { |e| e[:date] }.uniq.sort
    days_active = unique_dates.size

    # First substance ever
    first_substance : String? = nil
    first_dose_date : String? = nil
    if first = entries_with_ts.first?
      first_substance = first[:entry]["substance_name"]?.try(&.as(String))
      first_dose_date = first[:date]
    end

    # Streak calculations
    current_streak = 0
    longest_streak = 0

    if unique_dates.size > 0
      streak = 1
      today = Time.utc.to_s("%Y-%m-%d")
      yesterday = (Time.utc - 1.day).to_s("%Y-%m-%d")
      last_log_date = unique_dates.last
      on_active_streak = last_log_date == today || last_log_date == yesterday

      # Calculate longest streak
      (1...unique_dates.size).each do |i|
        begin
          prev_date = Time.parse(unique_dates[i - 1], "%Y-%m-%d", Time::Location::UTC)
          curr_date = Time.parse(unique_dates[i], "%Y-%m-%d", Time::Location::UTC)
          diff_days = (curr_date - prev_date).total_days.to_i
          if diff_days == 1
            streak += 1
          else
            longest_streak = streak if streak > longest_streak
            streak = 1
          end
        rescue
          streak = 1
        end
      end
      longest_streak = streak if streak > longest_streak

      # Current streak (count backwards from most recent)
      if on_active_streak
        current_streak = 1
        (unique_dates.size - 2).downto(0) do |i|
          begin
            curr_date = Time.parse(unique_dates[i + 1], "%Y-%m-%d", Time::Location::UTC)
            prev_date = Time.parse(unique_dates[i], "%Y-%m-%d", Time::Location::UTC)
            if (curr_date - prev_date).total_days.to_i == 1
              current_streak += 1
            else
              break
            end
          rescue
            break
          end
        end
      end
    end

    # Doses this week vs average
    now = Time.utc
    week_ago = now - 7.days
    doses_this_week = entries_with_ts.count { |e| e[:time] > week_ago }

    avg_weekly = 0.0
    if first_ts = entries_with_ts.first?
      weeks_active = [(now - first_ts[:time]).total_days / 7.0, 1.0].max
      avg_weekly = (entries.size.to_f / weeks_active).round(1)
    end

    # Most common combo (substances on same day)
    daily_substances = Hash(String, Set(String)).new { |h, k| h[k] = Set(String).new }
    entries_with_ts.each do |e|
      if sub = e[:entry]["substance_name"]?.try(&.as(String))
        daily_substances[e[:date]] << sub
      end
    end

    combo_counts = Hash(String, Int32).new(0)
    daily_substances.each do |_, substances|
      if substances.size >= 2
        combo = substances.to_a.sort.join(" + ")
        combo_counts[combo] += 1
      end
    end
    most_common_combo = combo_counts.max_by? { |_, count| count }.try(&.first)

    # Rarest substance (least used)
    rarest_substance = substance_counts.min_by? { |_, count| count }.try(&.first)

    # Poly-drug days (3+ substances)
    poly_drug_days = daily_substances.count { |_, subs| subs.size >= 3 }

    # Busiest day ever (the binge)
    daily_counts = Hash(String, Int32).new(0)
    entries_with_ts.each { |e| daily_counts[e[:date]] += 1 }
    busiest = daily_counts.max_by? { |_, count| count }
    busiest_day_date = busiest.try(&.first)
    busiest_day_count = busiest.try(&.last) || 0

    # Get detailed binge data for the busiest day
    busiest_day_doses = [] of Hash(String, String | Int32 | Float64)
    busiest_day_substances = [] of String
    busiest_day_duration_hours = 0.0
    busiest_day_intervals = [] of Int32

    if busiest_day_date
      binge_entries = entries_with_ts.select { |e| e[:date] == busiest_day_date }.sort_by { |e| e[:time] }

      if binge_entries.size > 0
        # Unique substances
        busiest_day_substances = binge_entries.map { |e| e[:entry]["substance_name"]?.try(&.as(String)) }.compact.uniq

        # Duration from first to last dose
        first_dose = binge_entries.first[:time]
        last_dose = binge_entries.last[:time]
        busiest_day_duration_hours = ((last_dose - first_dose).total_minutes / 60.0).round(1)

        # Build dose timeline with intervals
        prev_time : Time? = nil
        binge_entries.each do |e|
          dose_info = {
            "time"      => e[:time].to_s("%H:%M"),
            "substance" => e[:entry]["substance_name"]?.try(&.as(String)) || "unknown",
            "dosage"    => e[:entry]["dosage"]?.try(&.as(String)) || "",
            "route"     => e[:entry]["route"]?.try(&.as(String)) || "",
          } of String => String | Int32 | Float64

          # Calculate interval from previous dose
          if prev_time
            interval_mins = ((e[:time] - prev_time).total_minutes).to_i
            dose_info["mins_since_last"] = interval_mins
            busiest_day_intervals << interval_mins
          end
          prev_time = e[:time]

          busiest_day_doses << dose_info
        end
      end
    end

    # Weekend warrior ratio
    weekend_doses = entries_with_ts.count { |e| [5, 6].includes?(e[:time].day_of_week.to_i) }
    weekend_warrior_pct = entries.size > 0 ? (weekend_doses.to_f / entries.size * 100).round(1) : 0.0

    # One-time substances (experimental phase)
    one_time_substances = substance_counts.select { |_, count| count == 1 }.keys.sort

    # Longest sober streak (gap between doses)
    longest_sober_streak = 0
    if unique_dates.size > 1
      (1...unique_dates.size).each do |i|
        begin
          prev_date = Time.parse(unique_dates[i - 1], "%Y-%m-%d", Time::Location::UTC)
          curr_date = Time.parse(unique_dates[i], "%Y-%m-%d", Time::Location::UTC)
          gap = (curr_date - prev_date).total_days.to_i - 1
          longest_sober_streak = gap if gap > longest_sober_streak
        rescue
        end
      end
    end

    # Generate wrapped text
    wrapped_lines = [] of String

    title = if alltime
              "**#{user_id}'s All-Time Substance Wrapped**"
            else
              "**#{user_id}'s Substance Wrapped #{year}**"
            end
    wrapped_lines << title
    wrapped_lines << ""

    # Top substances section
    wrapped_lines << "**Your Top Substances:**"
    top_substances.each_with_index do |substance_data, index|
      substance, count = substance_data
      wrapped_lines << "\t#{index + 1}. #{substance}\t#{count} doses"
    end
    wrapped_lines << ""

    # Monthly breakdown (only for yearly, not alltime)
    unless alltime
      wrapped_lines << "**Monthly Breakdown:**"
      month_names = ["", "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
      peak_month = monthly_counts.max_by { |_, count| count }
      peak_month_name = month_names[peak_month.first]? || "Unknown"
      wrapped_lines << "\t#{peak_month_name} was your peak month with #{peak_month.last} doses"
      wrapped_lines << ""
    end

    # Time analysis
    peak_time = time_analysis.max_by { |_, count| count }
    wrapped_lines << "**Peak Usage Time:**"
    wrapped_lines << "\t#{peak_time.first.capitalize} was your preferred time (#{peak_time.last} doses)"
    wrapped_lines << ""

    # Day analysis
    peak_day = day_analysis.max_by { |_, count| count }
    wrapped_lines << "**Peak Day:**"
    wrapped_lines << "\t#{peak_day.first} was your go-to day (#{peak_day.last} doses)"
    wrapped_lines << ""

    # Route analysis
    top_route = route_counts.max_by { |_, count| count }
    wrapped_lines << "**Preferred Route:**"
    wrapped_lines << "\t#{top_route.first.capitalize} (#{top_route.last} uses)"
    wrapped_lines << ""

    # Summary
    wrapped_lines << "**Summary:**"
    wrapped_lines << "\tTotal entries: #{entries.size}"
    wrapped_lines << "\tUnique substances: #{substance_counts.size}"
    if alltime
      years = entries.map { |e| e["year"]?.as(Int32?) }.compact.uniq.sort
      wrapped_lines << "\tYears covered: #{years.first}-#{years.last}"
    end

    {
      "wrapped_text" => wrapped_lines.join("\n"),
      "statistics"   => {
        "total_entries"              => entries.size,
        "unique_substances"          => substance_counts.size,
        "top_substances"             => top_substances.map { |s, c| {"substance" => s, "count" => c} },
        "monthly_breakdown"          => monthly_counts.transform_keys(&.to_s),
        "time_analysis"              => time_analysis,
        "day_analysis"               => day_analysis,
        "route_analysis"             => route_counts,
        "days_active"                => days_active,
        "first_substance"            => first_substance,
        "first_dose_date"            => first_dose_date,
        "current_streak"             => current_streak,
        "longest_streak"             => longest_streak,
        "doses_this_week"            => doses_this_week,
        "avg_weekly"                 => avg_weekly,
        "most_common_combo"          => most_common_combo,
        "rarest_substance"           => rarest_substance,
        "poly_drug_days"             => poly_drug_days,
        "busiest_day_date"           => busiest_day_date,
        "busiest_day_count"          => busiest_day_count,
        "busiest_day_substances"     => busiest_day_substances,
        "busiest_day_duration_hours" => busiest_day_duration_hours,
        "busiest_day_doses"          => busiest_day_doses,
        "busiest_day_avg_interval"   => busiest_day_intervals.size > 0 ? (busiest_day_intervals.sum / busiest_day_intervals.size) : 0,
        "weekend_warrior_pct"        => weekend_warrior_pct,
        "one_time_substances"        => one_time_substances,
        "longest_sober_streak"       => longest_sober_streak,
      },
    }
  end

  # Chart-friendly active substances endpoint
  private def handle_user_active_bars(response : HTTP::Server::Response, user_id : String)
    begin
      # Get howhighami data and extract just the active substances for bar chart
      csv_content = @redis_client.fetch_csv_content
      lines = csv_content.split('\n')

      # Skip header and empty lines
      data_lines = lines[1..-1].reject(&.empty?)

      # Filter by user_id, newest first
      user_entries = data_lines.select do |line|
        parts = line.split(',')
        parts.size >= 3 && parts[1] == user_id
      end.reverse.map do |line|
        parts = line.split(',')
        if parts.size >= 5
          {
            "timestamp"      => parts[0],
            "substance_name" => parts[2].downcase.strip,
            "dosage"         => parts[3],
            "route"          => parts[4].downcase.strip,
          }
        else
          nil
        end
      end.compact

      # Calculate active substances (simplified logic for demo)
      current_time = Time.utc
      active_bars = [] of Hash(String, String | Float64)

      # Group by substance and find most recent for each
      substance_latest = {} of String => Hash(String, String)
      user_entries.each do |entry|
        substance = entry["substance_name"].as(String)
        if !substance_latest.has_key?(substance) || entry["timestamp"].as(String) > substance_latest[substance]["timestamp"].as(String)
          substance_latest[substance] = entry
        end
      end

      # Calculate time remaining for each substance (simplified)
      substance_latest.each do |substance, entry|
        begin
          entry_time = Time.parse_rfc3339(entry["timestamp"].as(String))
          hours_since = (current_time - entry_time).total_hours

          # Simple duration estimate based on substance (could be enhanced with real data)
          estimated_duration = case substance
                               when /caffeine/        then 6.0
                               when /methylphenidate/ then 4.0
                               when /cannabis/        then 3.0
                               else                        2.0
                               end

          hours_remaining = estimated_duration - hours_since

          if hours_remaining > 0
            active_bars << {
              "substance"       => substance,
              "hours_remaining" => hours_remaining.round(1),
            }
          end
        rescue
          # Skip invalid timestamps
        end
      end

      response.print({
        "data" => active_bars,
        "meta" => {"count" => active_bars.size},
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to fetch active substances bars: #{ex.message}"}.to_json)
    end
  end

  # Chart-friendly category counts endpoint
  private def handle_user_categories(response : HTTP::Server::Response, user_id : String)
    begin
      Log.debug("metric", "handle_user_categories")

      # Get user's dose entries from Redis
      user_doses = @redis_client.get_user_doses(user_id, 1000)

      # Extract substance names
      substances = Set(String).new
      user_doses.each do |dose_data|
        raw_substance_name = dose_data["substance"]? || ""
        unless raw_substance_name.empty?
          normalized_name = JosieHealth::LUT::DrugLUT.normalize(raw_substance_name.downcase) || raw_substance_name.downcase
          substances.add(normalized_name)
        end
      end

      Log.debug("metric", "Found #{substances.size} unique substances")

      # Categorize substances and count them
      category_counts = Hash(String, Int32).new(0)

      # Load drugs data for category information
      begin
        drugs_data = JosieHealth::LUT::DataFiles.drugs

        substances.each do |substance|
          # Look up substance in drugs.json
          if drug_info = drugs_data[substance]?
            if categories = drug_info["categories"]?.try(&.as_a)
              # Use first category as primary
              primary_category = categories.first.as_s
              category_counts[primary_category] ||= 0
              category_counts[primary_category] += 1
            else
              category_counts["unknown"] ||= 0
              category_counts["unknown"] += 1
            end
          else
            category_counts["unknown"] ||= 0
            category_counts["unknown"] += 1
          end
        end
      rescue ex
        # If can't load drugs.json, just count as unknown
        Log.warn("metric", "Error loading drugs.json: #{ex.message}")
        category_counts["unknown"] = substances.size
      end

      # Convert to chart-friendly format
      chart_data = category_counts.map do |category, count|
        {
          "category" => category,
          "count"    => count,
        }
      end.sort_by { |item| -item["count"].as(Int32) }

      response.print({
        "data" => chart_data,
        "meta" => {"count" => chart_data.size},
      }.to_json)
    rescue ex
      Log.error("metric", "Exception in handle_user_categories: #{ex.message}")
      response.status_code = 500
      response.print({"error" => "Failed to fetch category counts: #{ex.message}"}.to_json)
    end
  end

  # User-specific substances with usage counts
  private def handle_user_substances(response : HTTP::Server::Response, user_id : String)
    begin
      Log.debug("metric", "handle_user_substances")

      # Get user's dose entries from Redis
      user_doses = @redis_client.get_user_doses(user_id, 1000)

      # Count substance usage for this user
      substance_counts = Hash(String, Int32).new(0)
      user_doses.each do |dose_data|
        raw_substance_name = dose_data["substance"]? || ""
        unless raw_substance_name.empty?
          normalized_name = JosieHealth::LUT::DrugLUT.normalize(raw_substance_name.downcase) || raw_substance_name.downcase
          substance_counts[normalized_name] += 1
        end
      end

      Log.debug("metric", "Found #{substance_counts.size} unique substances")

      # Convert to chart-friendly format matching global substances endpoint format
      chart_data = substance_counts.map do |substance, count|
        {
          "substance" => substance,
          "tally"     => count,
        }
      end.sort_by { |item| -item["tally"].as(Int32) }

      # Take top 10 only
      top_substances = chart_data.first(10)

      response_data = {
        "data" => top_substances,
        "meta" => {
          "user_id"                 => user_id,
          "total_unique_substances" => substance_counts.size,
          "total_doses"             => user_doses.size,
          "source"                  => "redis_user_data",
        },
      }

      response.print(response_data.to_json)
    rescue ex
      Log.error("metric", "Exception in handle_user_substances: #{ex.message}")
      response.status_code = 500
      response.print({"error" => "Failed to fetch user substance counts: #{ex.message}"}.to_json)
    end
  end

  # Chart-friendly recent dose frequency endpoint
  private def handle_user_recent_frequency(response : HTTP::Server::Response, user_id : String)
    begin
      csv_content = @redis_client.fetch_csv_content
      lines = csv_content.split('\n')

      # Skip header and empty lines
      data_lines = lines[1..-1].reject(&.empty?)

      # Filter by user_id for recent entries (last 30 days)
      recent_cutoff = Time.utc - 30.days
      substance_counts = Hash(String, Int32).new(0)

      data_lines.each do |line|
        parts = line.split(',')
        if parts.size >= 3 && parts[1] == user_id
          begin
            entry_time = Time.parse_rfc3339(parts[0])
            if entry_time > recent_cutoff
              substance = parts[2].downcase.strip
              unless substance.empty?
                normalized_name = JosieHealth::LUT::DrugLUT.normalize(substance) || substance
                substance_counts[normalized_name] += 1
              end
            end
          rescue
            # Skip invalid timestamps
          end
        end
      end

      # Convert to chart-friendly format and take top 10
      chart_data = substance_counts.map do |substance, count|
        {
          "substance"   => substance,
          "recent_uses" => count,
        }
      end.sort_by { |item| -item["recent_uses"].as(Int32) }.first(10)

      response.print({
        "data" => chart_data,
        "meta" => {"count" => chart_data.size},
      }.to_json)
    rescue ex
      response.status_code = 500
      response.print({"error" => "Failed to fetch recent frequency: #{ex.message}"}.to_json)
    end
  end
end
