require 'dotenv/load'
require 'mysql2'

class ArtRelations
  attr_reader :dbh, :sql, :upc_label_cache_hits

  def initialize
    @dbh = Mysql2::Client.new(
      host:     ENV["ar_hostname"],
      username: ENV["ar_username"],
      password: ENV["ar_password"],
      database: ENV["ar_database"],
    )

    @sql = _sql(dbh)
    @upc_label_cache = {}
    @upc_label_cache_hits = 0
  end

  def _sql(dbh)
    {
      cache_upc_display_upc: dbh.prepare("SELECT display_upc, upc FROM releases WHERE upc != display_upc ORDER BY display_upc"),
      verify_vendor_upc_ownership: dbh.prepare("SELECT v.vendor_id, r.upc, r.display_upc FROM releases r \
                                                INNER JOIN project p ON p.project_id = r.project_id \
                                                INNER JOIN vendor v ON v.vendor_id = p.vendor_id \
                                                WHERE v.vendor_id = ?
                                                AND (r.upc = ? OR r.display_upc = ?)"),
    }
  end

  def label_owns_upc_ignore_leading_zero?(label_id, upc)
    label_owns_upc?(label_id, upc, true)
  end

  def label_owns_upc?(label_id, upc, ignore_leading_zero = false)
    upc = upc.gsub(/^0/, '') if ignore_leading_zero

    found = @upc_label_cache.fetch(upc.to_s, nil)

    if found
      @upc_label_cache_hits += 1
      return true
    end

    res = sql[:verify_vendor_upc_ownership].execute(label_id, upc, upc)
    res.each do | r |
      @upc_label_cache[r['upc'].to_s] = r['vendor_id']
      @upc_label_cache[r['display_upc'].to_s] = r['vendor_id']
      found = r['vendor_id']
    end
    found ? true : false
  end
end