Functions/busqueda/funko.cr

require "json"
require "kemal"
require "db"
require "pg"
require "pool/connection"

# get credentials from secrets
USER   = File.read("/secrets/user").strip
PASS   = File.read("/secrets/pass").strip
DBHOST = File.read("/secrets/dbhost").strip

DBURL = "postgres://#{USER}:#{PASS}@#{DBHOST}:5432/nombres"
puts "Connnecting to #{DBURL}"

def normalize(s : String) : String
  s.unicode_normalize(:nfkd)
    .chars.reject! { |character|
    !character.ascii_letter? && (character != ' ')
  }.join("").downcase
end

# A basic hello world get endpoint
post "/" do |env|
  db = DB.open DBURL
  prefijo = env.params.json["p"].as(String)
  genero = env.params.json["g"].as(String)
  year = env.params.json["a"].as(String)

  prefijo = normalize(prefijo)
  if !["f", "m"].includes?(genero)
    genero = nil
  end
  datos = [] of Tuple(String, Int32 | String)
  # Connect using credentials provided
  if prefijo.empty? && year.empty?
    result_set = db.query("
        SELECT nombre, total::integer
        FROM totales
        ORDER BY total DESC
        LIMIT 50")
  elsif prefijo.empty? && !year.empty?
    # Per-year totals
    result_set = db.query("
        SELECT nombre, contador::integer
        FROM nombres
        WHERE
          anio = $1
        ORDER BY contador DESC
        LIMIT 50", year)
  elsif !prefijo.empty? && year.empty?
    # Filter only by prefix
    result_set = db.query("
        SELECT nombre, total::integer
        FROM totales
        WHERE
            nombre LIKE $1
        ORDER BY total DESC
        LIMIT 50", prefijo + "%")
  elsif !prefijo.empty? && !year.empty?
    # We have both
    result_set = db.query("
        SELECT nombre, contador::integer
        FROM nombres
        WHERE
            anio = $1 AND
            nombre LIKE $2
        ORDER BY contador DESC
        LIMIT 50", year, prefijo + "%")
  end

  if !result_set.nil?
    result_set.each do
      nombre = result_set.read(String)
      valor = result_set.read(Int32)
      datos.push({nombre, valor})
    end
    result_set.close
  end

  if datos.empty?
    raise "No data found"
  end
  # In this context, remove all composite names
  datos.reject! { |row|
    row[0].to_s.includes? " "
  }
  datos.insert(0, {"Nombre", "Cuantos?"})

  if genero
    datos.reject! { |row|
      # How feminine is this name?
      # Yes this database is upper case
      puts "Checking #{row[1]} #{row[0]}"
      feminidad = 0
      sql = %(
            SELECT COALESCE((SELECT frecuencia FROM mujeres WHERE nombre='#{row[0]?.to_s.upcase}'), 0) AS mujeres,
                   COALESCE((SELECT frecuencia FROM hombres WHERE nombre='#{row[0]?.to_s.upcase}'), 0) AS hombres
          )
      puts "SQL: #{sql}"
      db.query sql do |result_set|
        result_set.each do
          mujeres = result_set.read(Int32)
          hombres = result_set.read(Int32)
          puts "frecuencias: #{mujeres} #{hombres}"
          if hombres == mujeres == 0
            feminidad = 0.5
          else
            feminidad = mujeres / (hombres + mujeres)
          end
        end
      end
      # El overlap en 0.5 es intencional!
      if (feminidad >= 0.5 && genero == "f") ||
         (feminidad <= 0.5 && genero == "m")
        false
      else
        true
      end
    }
    puts "Data split by gender"
  end
  datos = datos[..10].map { |row|
    [row[0].capitalize, row[1]]
  }

  if datos.size > 2
    title = "¿Puede ser ... #{datos[1][0].to_s.titleize}? ¿O capaz que #{datos[2][0].to_s.titleize}? ¡Contame más!"
  elsif datos.size == 2
    title = "Me parece que ... #{datos[1][0].to_s.titleize}!"
  else
    title = "No tengo idea!"
  end
  {
    "title" => title,
    "data"  => datos,
  }.to_json
ensure
  db.try &.close
end

get "/ping/" do
  db = DB.open DBURL
  db.exec("SELECT 42")
  "OK"
ensure
  db.try &.close
end