Class: Crm::AmazonProductsController

Inherits:
CrmController show all
Includes:
Controllers::AmazonProductsGrid
Defined in:
app/controllers/crm/amazon_products_controller.rb

Overview

Controller: amazon products.

Constant Summary

Constants included from Controllers::AmazonProductsGrid

Controllers::AmazonProductsGrid::AMAZON_SELLER_IDS, Controllers::AmazonProductsGrid::GRID_FILTER_PARAMS

Constants included from Controllers::ReferenceFindable

Controllers::ReferenceFindable::ID_EMBEDDED_PATTERNS

Constants included from Controllers::AnalyticsEvents

Controllers::AnalyticsEvents::MAX_QUEUED_EVENTS, Controllers::AnalyticsEvents::SESSION_KEY

Constants included from Controllers::ErrorRendering

Controllers::ErrorRendering::NON_CONTENT_PATH_PREFIXES

Instance Method Summary collapse

Methods inherited from CrmController

#access_denied, #context_id, #context_object, #crm_home_path, #current_ability, #default_url_options, #download_temp, #get_tempfile_path_for_download, #init_status_job_collector, #initialize_crm_lazy_chunks, #persist_enqueued_status_jobs, #record_not_found, #redirect_to_job_or_fallback, #render_edit_action, #set_context, #set_download_path, #stash_file_for_temp_download, #sync_admin_presence_cookie, #touch_employee_last_seen

Methods inherited from ApplicationController

#account_impersonated?, #add_to_flash, #after_sign_in_path_for, #bypass_forgery_protection?, #chat_enabled?, #cloudflare_cleared?, #default_catalog, #default_url_options, #enable_turbo_frames, #find_publication, #fix_invalid_accept_header, #init_js_utils, #is_globals_call?, #layout_by_resource, #locale_store, #redirect_to, #require_employee_for_crm, #set_base_host, #set_real_ip, #set_report_errors_for, #should_render_layout?, #skip_layout_for_turbo_frame?, #stamp_impersonation_context, #tab_frame_breakout_request?, #warmlyyours_canada_ip?, #warmlyyours_ip?, #y

Methods included from Controllers::ReturnPathHandling

#check_for_return_path, #redirect_to_return_path_or_default

Methods included from Controllers::AnalyticsEvents

#consume_queued_analytics_events, #registration_lead_type, #track_event

Methods included from Controllers::DeviceDetection

#device_detector, #is_ie?

Methods included from Controllers::SubdomainDetection

#is_crm_request?, #is_www_request?, #json_request?

Methods included from Controllers::TurboSafeRedirect

#redirect_to

Methods included from Controllers::TrackingDetection

#bot_request?, #gdpr_country?, #gdpr_country_data, #prevent_bots, #set_tracking_cookie, #track_visitor?

Methods included from Controllers::AcceleratedFileSending

#send_file_accelerated, #send_upload_accelerated

Methods included from Controllers::ErrorRendering

#excp_string, #mail_to_for_error_reporting, #render_400, #render_404, #render_406, #render_410, #render_500, #render_invalid_authenticity_token, #render_ip_spoof_error, #render_unpermitted_parameters, #safe_referer_or_fallback

Methods included from Controllers::TurnstileVerification

#load_turnstile_script_tag, #turnstile_lazy_widget, #turnstile_script_tag, #turnstile_widget, #validate_turnstile!

Methods included from Controllers::CloudflareCaching

edge_cached, #edge_cached_action?, #reset_cloudflare_cache, #set_cloudflare_cache, #skip_edge_cache!, #skip_session

Methods included from Controllers::Webpackable

#preload_webpack_fonts, #webpack_css_include, #webpack_css_url, #webpack_js_include, #wpd_is_running?

Methods included from Controllers::Localizable

#cloudflare_country_locale, #determine_request_locale, #geocoder_locale, #guest_user_locale_check, #locale_optional_www_auth_path?, #param_locale, #set_locale, #set_request_locale, #skip_localization?, #warmlyyours_ip_locale

Methods included from Controllers::Authenticable

#access_denied, #authenticate_account, #authenticate_account!, #authenticate_account_from_login_token!, #check_is_a_manager, #check_is_a_sales_manager, #check_is_an_admin, #check_is_an_employee, #check_party, #clear_mismatched_guest_user, #create_guest_user, #credentials?, #current_or_guest_user, #current_or_guest_user_id_read_only, #current_user, #devise_mapping, #fully_logged_in?, #generate_bot_id, #guest_user, #identifiable?, #init_current_user, #initialize_guest, #load_context_user, #logging_in, #resource, #resource_name, #restrict_access_for_non_employees, #scrubbed_request_path, #user_object, #warn_on_session_guest_id_leak

Methods included from UrlsHelper

#catalog_breadcrumb_links, #catalog_link, #catalog_link_for_product_line, #catalog_link_for_sku, #cms_link, #delocalized_path, #path_to_sales_product_sku, #path_to_sales_product_sku_for_product_line, #path_to_sales_product_sku_for_product_line_slug, #product_line_from_catalog_link, #protocol_neutral_url, #sanitize_external_url, #valid_external_url?

Instance Method Details

#add_competitor ⇒ Object

Add or update a competitor in the database



317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
 
# File 'app/controllers/crm/amazon_products_controller.rb', line 317

def add_competitor
  authorize!(:manage, AmazonCompetitor)

  # Find existing competitor (from auto-discovery) or create new one
  @competitor = AmazonCompetitor.find_or_initialize_by(seller_id: competitor_params[:seller_id])
  @competitor.assign_attributes(competitor_params)

  if @competitor.save
    action = @competitor.previously_new_record? ? 'Added' : 'Updated'
    flash[:success] = "#{action} competitor: #{@competitor.name} (#{@competitor.seller_id})"
  else
    flash[:error] = "Failed to save competitor: #{@competitor.errors.full_messages.join(', ')}"
  end

  redirect_to competitors_amazon_catalog_items_path
end

#competitors ⇒ Object

Show unknown competitors that have won Buy Box on our products



120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
 
# File 'app/controllers/crm/amazon_products_controller.rb', line 120

def competitors
  # Scan ALL Amazon Seller Central catalogs (US, Canada, etc.)
  @catalog_ids = CatalogConstants::AMAZON_SELLER_IDS
  @catalogs = Catalog.where(id: @catalog_ids)

  # Get merchant IDs for all catalogs
  @merchant_ids = @catalogs.filter_map { |c| c.load_orchestrator&.merchant_id }.uniq

  # OPTIMIZATION: Fetch ALL competitor data in a single query (cached for 10 minutes)
  all_competitor_data = Rails.cache.fetch('amazon_competitors_data', expires_in: 10.minutes) do
    fetch_all_competitor_data_optimized
  end

  # Get all competitors from database
  all_db_competitors = AmazonCompetitor.all.index_by(&:seller_id)
  named_seller_ids = AmazonCompetitor.named.pluck(:seller_id)

  # Build unified list of ALL competitors
  all_competitor_ids = all_competitor_data.keys
  db_seller_ids = all_db_competitors.keys

  # Combine all seller IDs (from EDI data + database)
  all_seller_ids = (all_competitor_ids + db_seller_ids).uniq

  @all_competitors = all_seller_ids.map do |seller_id|
    db_competitor = all_db_competitors[seller_id]
    edi_data = all_competitor_data[seller_id] || { count: 0, samples: [] }
    catalog_names = edi_data[:samples].filter_map { |s| s[:catalog_name] }.uniq

    # Determine status: known (named), discovered (in DB but unnamed), unknown (not in DB)
    status = if named_seller_ids.include?(seller_id)
               :known
             elsif db_seller_ids.include?(seller_id)
               :discovered
             else
               :unknown
             end

    {
      seller_id: seller_id,
      competitor: db_competitor,
      name: db_competitor&.name || seller_id,
      display_name: db_competitor&.name || "Unknown (#{seller_id[0..6]}...)",
      status: status,
      product_count: edi_data[:count],
      sample_products: edi_data[:samples],
      catalog_names: catalog_names,
      storefront_url: db_competitor&.storefront_url || "https://www.amazon.com/sp?seller=#{seller_id}",
      created_at: db_competitor&.created_at || Time.current,
      notes: db_competitor&.notes
    }
  end

  # Store totals before filtering
  @total_count = @all_competitors.count
  @known_count = @all_competitors.count { |c| c[:status] == :known }
  @unknown_count = @all_competitors.count { |c| c[:status] != :known }

  # Apply status filter (known vs unknown/discovered)
  @filter_status = params[:status]
  if @filter_status == 'known'
    @all_competitors = @all_competitors.select { |c| c[:status] == :known }
  elsif @filter_status == 'unknown'
    @all_competitors = @all_competitors.reject { |c| c[:status] == :known }
  end

  # Apply catalog filter
  @filter_catalog = params[:catalog]
  if @filter_catalog.present? && @filter_catalog != 'all'
    catalog_name_filter = @filter_catalog == 'usa' ? 'USA' : 'Canada'
    @all_competitors = @all_competitors.select do |c|
      c[:catalog_names].any? { |name| name.include?(catalog_name_filter) }
    end
  end

  # Apply competition status filter
  @filter_competing = params[:competing]
  if @filter_competing == 'active'
    @all_competitors = @all_competitors.select { |c| c[:product_count].positive? }
  elsif @filter_competing == 'inactive'
    @all_competitors = @all_competitors.select { |c| c[:product_count].zero? }
  end

  # Apply sorting
  @sort = params[:sort] || 'products_desc'
  @all_competitors = case @sort
                     when 'name_asc'
                       @all_competitors.sort_by { |c| c[:name].to_s.downcase }
                     when 'name_desc'
                       @all_competitors.sort_by { |c| c[:name].to_s.downcase }.reverse
                     when 'products_asc'
                       @all_competitors.sort_by { |c| c[:product_count] }
                     when 'products_desc'
                       @all_competitors.sort_by { |c| -c[:product_count] }
                     when 'recent'
                       @all_competitors.sort_by { |c| c[:created_at] }.reverse
                     when 'status'
                       # Unknown first, then discovered, then known
                       @all_competitors.sort_by { |c| [{ unknown: 0, discovered: 1, known: 2 }[c[:status]], -c[:product_count]] }
                     else
                       @all_competitors.sort_by { |c| -c[:product_count] }
                     end

  @filtered_count = @all_competitors.count
end

#delete_competitor ⇒ Object

Delete a competitor



350
351
352
353
354
355
356
357
358
359
360
361
362
363
 
# File 'app/controllers/crm/amazon_products_controller.rb', line 350

def delete_competitor
  authorize!(:manage, AmazonCompetitor)

  @competitor = AmazonCompetitor.find(params[:competitor_id])
  name = @competitor.display_name

  if @competitor.destroy
    flash[:success] = "Deleted competitor: #{name}"
  else
    flash[:error] = "Failed to delete competitor: #{@competitor.errors.full_messages.join(', ')}"
  end

  redirect_to competitors_amazon_catalog_items_path
end

#fetch_all_competitor_data_optimized ⇒ Hash

Fetch all competitor data in a SINGLE optimized query

Returns:

  • (Hash) —

    { seller_id => { count: N, samples: [...] }, ... }



228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
 
# File 'app/controllers/crm/amazon_products_controller.rb', line 228

def fetch_all_competitor_data_optimized
  catalog_ids_sql = CatalogConstants::AMAZON_SELLER_IDS.join(',')
  merchant_ids = Catalog.where(id: CatalogConstants::AMAZON_SELLER_IDS)
                        .filter_map { |c| c.load_orchestrator&.merchant_id }
  merchant_ids_sql = merchant_ids.map { |mid| ActiveRecord::Base.lease_connection.quote(mid) }.join(',')

  return {} if merchant_ids.empty?

  # Single query to get all competitor offers with catalog item info
  sql = <<~SQL.squish
    WITH latest_logs AS (
      SELECT DISTINCT ON (ed.catalog_item_id)
             ed.catalog_item_id,
             ecl.data,
             ci.catalog_id
      FROM edi_documents ed
      JOIN edi_communication_logs ecl ON ecl.id = ed.edi_communication_log_id
      JOIN catalog_items ci ON ci.id = ed.catalog_item_id
      WHERE ci.catalog_id IN (#{catalog_ids_sql})
        AND ecl.category = 'buy_box_status'
        AND ecl.state = 'processed'
        AND ecl.data IS NOT NULL
      ORDER BY ed.catalog_item_id, ecl.created_at DESC
    ),
    competitor_offers AS (
      SELECT
        ll.catalog_item_id,
        ll.catalog_id,
        offer->>'SellerId' as seller_id,
        (offer->'ListingPrice'->>'Amount')::numeric as price
      FROM latest_logs ll,
           LATERAL jsonb_array_elements(ll.data::jsonb -> 'payload' -> 'Offers') AS offer
      WHERE offer->>'SellerId' NOT IN (#{merchant_ids_sql})
    )
    SELECT
      co.seller_id,
      COUNT(DISTINCT co.catalog_item_id) as product_count,
      json_agg(
        json_build_object(
          'catalog_item_id', co.catalog_item_id,
          'catalog_id', co.catalog_id,
          'price', co.price
        )
      ) FILTER (WHERE co.catalog_item_id IS NOT NULL) as products
    FROM competitor_offers co
    GROUP BY co.seller_id
    ORDER BY product_count DESC
  SQL

  results = ActiveRecord::Base.lease_connection.execute(sql)

  # Pre-fetch all catalog items we'll need for sample data
  all_catalog_item_ids = results.flat_map do |row|
    products = row['products'] ? JSON.parse(row['products']) : []
    products.take(5).map { |p| p['catalog_item_id'] }
  end.compact.uniq

  catalog_items = CatalogItem.where(id: all_catalog_item_ids)
                             .includes(:catalog, store_item: :item)
                             .index_by(&:id)

  # Build the result hash
  results.each_with_object({}) do |row, hash|
    seller_id = row['seller_id']
    products = row['products'] ? JSON.parse(row['products']) : []

    # Take top 5 products as samples
    samples = products.take(5).filter_map do |p|
      ci = catalog_items[p['catalog_item_id']]
      next unless ci

      {
        sku: ci.sku,
        asin: ci.amazon_asin,
        name: ci.item&.name&.truncate(50),
        competitive_price: p['price'],
        catalog_item_id: ci.id,
        catalog_name: ci.catalog&.name
      }
    end

    hash[seller_id] = {
      count: row['product_count'].to_i,
      samples: samples
    }
  end
end

#full_refresh ⇒ Object

Full refresh: Pull Amazon data + trigger retailer probes for all sibling catalogs



62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
 
# File 'app/controllers/crm/amazon_products_controller.rb', line 62

def full_refresh
  authorize!(:update, @catalog_item)

  # 1. Queue Amazon buy box and catalog info pull (uses same worker as other actions for Turbo Stream broadcasts)
  amazon_options = {
    catalog_item_ids: [@catalog_item.id],
    operation: %w[PULL_BUY_BOX_STATUS PULL_LISTING_INFORMATION PULL_CATALOG_INFORMATION]
  }

  job_id = AmazonItemOperationWorker.perform_async(**amazon_options)
  job_id ||= SidekiqUniqueJobsJidLookup.active_jid_for_args(AmazonItemOperationWorker, amazon_options)

  # 2. Queue retailer probes for this item
  RetailerProbeWorker.perform_async(catalog_item_id: @catalog_item.id)

  # 3. Queue retailer probes for all sibling catalog items (same product, different retailers)
  sibling_probe_count = queue_sibling_retailer_probes(@catalog_item)

  # Build status message
  messages = ['Amazon data refresh queued']
  messages << "#{sibling_probe_count + 1} retailer probe(s) queued" if sibling_probe_count >= 0

  render json: {
    status: 'queued',
    job_id: job_id,
    message: messages.join('. ')
  }
end

#index ⇒ void

This method returns an undefined value.

GET /crm/amazon_products — CSV export of the Amazon catalog-items grid
(HTML is served by the retailer dashboard's Listings tab).



14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
# File 'app/controllers/crm/amazon_products_controller.rb', line 14

def index
  # GET HTML is served by the retailer dashboard's Listings tab (grid consolidation);
  # grid filters are forwarded so old bookmarked filter links keep working.
  # CSV export stays here.
  if request.format.html?
    catalog_id = parse_catalog_ids(params[:catalog_id]).first
    forwarded = request.query_parameters.symbolize_keys.slice(*Controllers::AmazonProductsGrid::GRID_FILTER_PARAMS)
    return redirect_to retailer_path(id: catalog_id, **forwarded, anchor: 'listings')
  end

  setup_amazon_products_grid

  respond_to do |format|
    format.csv do
      # For CSV export, get all results (no pagination) up to 10,000 records
      all_items = @q.result.limit(10_000)
      send_data generate_csv(all_items),
                filename: "amazon-catalog-items-#{Date.current}.csv",
                type: 'text/csv; charset=utf-8'
    end
  end
end

#job_status ⇒ Object

Check job status (for polling)



92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
 
# File 'app/controllers/crm/amazon_products_controller.rb', line 92

def job_status
  job = BackgroundJobStatus.find(params[:job_id])

  if job.nil?
    render json: { status: 'not_found', message: 'Job not found' }, status: :not_found
    return
  end

  status = if job.complete? || job.completed?
             'completed'
           elsif job.failed? || job.stopped? || job.interrupted?
             'failed'
           elsif job.working?
             'working'
           else
             'queued'
           end

  render json: {
    status: status,
    message: job.message || job.error_message,
    progress: job.pct_complete,
    catalog_item_id: @catalog_item.id,
    amazon_info_datetime: @catalog_item.reload.amazon_info_datetime&.iso8601
  }
end

#match_competitive_price ⇒ Object

Lower price to match competitive price (from Amazon's reported data)
Uses Catalog::UpdateCatalogItem to trigger EDI price message



367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
 
# File 'app/controllers/crm/amazon_products_controller.rb', line 367

def match_competitive_price
  authorize!(:update, @catalog_item)

  # Get competitive price from Amazon's reported data (NOT from retailer probes)
  competitive_data = get_best_competitor_for_catalog_item(@catalog_item)

  unless competitive_data&.dig(:price)
    flash[:error] = 'No competitive price data available from Amazon'
    redirect_back_or_to(amazon_catalog_items_path)
    return
  end

  competitive_price = competitive_data[:price]
  our_price = @catalog_item.amazon_price_with_tax
  min_price = @catalog_item.amazon_minimum_seller_allowed_price_with_tax

  # Don't lower if we're already at or below competitive price
  if our_price <= competitive_price
    flash[:info] = "Our price ($#{our_price}) is already at or below competitive price ($#{competitive_price})"
    redirect_back_or_to(amazon_catalog_items_path)
    return
  end

  # Determine target price based on competitor type:
  # - External competitors (Amazon threshold from other marketplaces): just match
  # - Seller competitors (other sellers on the listing): beat by $0.01
  is_external = competitive_data[:seller_id] == 'EXTERNAL_COMPETITOR'
  target_price = if is_external
                   competitive_price.round(2) # Match external threshold
                 else
                   (competitive_price - 0.01).round(2) # Beat seller by 1 cent
                 end
  action_verb = is_external ? 'match' : 'beat'

  # Check minimum price
  if target_price < min_price
    flash[:error] = "Target price $#{target_price} is below minimum allowed $#{min_price}"
    redirect_back_or_to(amazon_catalog_items_path)
    return
  end

  # Convert price with tax to amount (without tax) for storage
  new_amount = if @catalog_item.tax_rate.present? && @catalog_item.tax_rate.positive?
                 (target_price / (1 + @catalog_item.tax_rate)).round(2)
               else
                 target_price
               end

  # Use UpdateCatalogItem service to trigger EDI price message via audience_member
  PaperTrail.request(whodunnit: current_user&.name || 'AmazonProductsController') do
    result = Catalog::UpdateCatalogItem.new.process(
      @catalog_item,
      { amount: new_amount },
      force_coupon_sync: false
    )

    if result.catalog_item_updated?
      competitor_name = is_external ? 'external threshold' : "competitor #{competitive_data[:seller_id]}"
      flash[:success] = "Price lowered from $#{our_price} to $#{target_price} to #{action_verb} #{competitor_name}. EDI price update queued."
    else
      flash[:error] = "Failed to update price: #{result.messages.join(', ')}"
    end
  end

  redirect_back_or_to(amazon_catalog_items_path)
end

#pull_buy_box ⇒ Object

Pull buy box status for a single catalog item (async)



50
51
52
53
54
55
56
57
58
59
 
# File 'app/controllers/crm/amazon_products_controller.rb', line 50

def pull_buy_box
  authorize!(:update, CatalogItem)

  options = {
    catalog_item_ids: [@catalog_item.id],
    operation: ['PULL_BUY_BOX_STATUS']
  }

  perform_amazon_operation(options, 'Pulling buy box status...')
end

#pull_catalog_info ⇒ Object

Pull catalog information for a single catalog item (async)



38
39
40
41
42
43
44
45
46
47
 
# File 'app/controllers/crm/amazon_products_controller.rb', line 38

def pull_catalog_info
  authorize!(:update, CatalogItem)

  options = {
    catalog_item_ids: [@catalog_item.id],
    operation: %w[PULL_LISTING_INFORMATION PULL_CATALOG_INFORMATION]
  }

  perform_amazon_operation(options, 'Pulling catalog information...')
end

#update_competitor ⇒ Object

Update an existing competitor



335
336
337
338
339
340
341
342
343
344
345
346
347
 
# File 'app/controllers/crm/amazon_products_controller.rb', line 335

def update_competitor
  authorize!(:manage, AmazonCompetitor)

  @competitor = AmazonCompetitor.find(params[:competitor_id])

  if @competitor.update(competitor_params)
    flash[:success] = "Updated competitor: #{@competitor.name}"
  else
    flash[:error] = "Failed to update competitor: #{@competitor.errors.full_messages.join(', ')}"
  end

  redirect_to competitors_amazon_catalog_items_path
end