<commit_before>directory = "/home/inaturalist"
test_count = 30
val_percent = 0.1
val_thres = 10
root_id = 48460

# Pick the root of some branch to export
root = Taxon.find( root_id )
ancestry_string = root.rank_level == Taxon::ROOT_LEVEL ?
  "#{ root.id }" : "#{ root.ancestry }/#{ root.id }"

# Part 1: determine the taxonomy

# Create a hash of the obs photo count for each node descending from the root
sql_query = <<-SQL
  SELECT t.id AS taxa_id, COUNT(*)
  FROM observations o
  JOIN taxa t ON t.id = o.taxon_id
  JOIN observation_photos op ON o.id = op.observation_id
  JOIN photos p ON p.id = op.photo_id
  WHERE t.is_active = true
  AND ( t.id IN ( #{ ancestry_string.gsub( "/","," ) } ) OR t.ancestry = '#{ ancestry_string }' OR t.ancestry LIKE ( '#{ ancestry_string }/%' ) )
  AND ( select count(*) from conservation_statuses ct where ct.taxon_id=t.id AND ct.iucn=70 AND ct.place_id IS NULL ) = 0
  AND p.type = 'LocalPhoto'
  GROUP BY t.id;
SQL
taxonomy = ActiveRecord::Base.connection.execute( sql_query )
photo_counts = taxonomy.map{|row| [row["taxa_id"], row["count"].to_i]}.to_h

# Keep only the leaves with enough downstream data
enough = []
ranks = Taxon::RANK_LEVELS.values.uniq.sort
standard_ranks = Taxon::RANK_LEVELS.select{ |k,v| ( Taxon::PREFERRED_RANKS.include? k ) && v > Taxon::SUBSPECIES_LEVEL && v != Taxon::SUPERFAMILY_LEVEL }.values.reverse
ranks[1..-1].each do |i|
  puts i
  enough_set = enough.map{ |row| row[:taxon_id] }.to_set
  Taxon.where("( ancestry = '#{ ancestry_string }' OR ancestry LIKE ( '#{ ancestry_string }/%' ) ) AND is_active = true AND rank_level = ?", i ).
  where("( select count(*) from conservation_statuses ct where ct.taxon_id=taxa.id AND ct.iucn=70 AND ct.place_id IS NULL ) = 0").each do |t|
    puts "\t#{t.id}"
    dset = t.descendants.pluck( :id ).to_set
    if ( dset & enough_set ).count > 0
      # internode
      enough << {
        taxon_id: t.id
      } 
    else
      # leaf
      if [
          photo_counts[t.id.to_s],
          t.descendants.pluck( :id ).map{ |j|
            photo_counts[j.to_s]
          }
        ].flatten.compact.sum >= 60
        enough << {
          taxon_id: t.id
        } 
      end
    end
  end
end
#enough_set = [enough.map{ |row| row[:taxon_id] },root.id].flatten.to_set
enough_set = [enough.map{ |row| row[:taxon_id] }].flatten.to_set; nil

# Fetch the taxa
taxa = Taxon.find( enough_set.to_a ); nil
results = taxa.map{|i|
    {
      id: i[:id],
      name: i[:name],
      rank_level: i[:rank_level],
      ancestors: i[:ancestry].split( "/" ).select{ |j| enough_set.include? j.to_i }.map{ |j| j.to_i }
    }
  }; nil


# Add 'missing' nodes (e.g. if Class->Family, insert Order)
results_hash = results.map{ |i| [i[:id],i[:rank_level]] }.to_h; nil
kntr=0
fake_key = {}
continue = true
while continue
  if row = results.select{ |i| ( standard_ranks.select{ |sr| sr > i[:rank_level] && sr < root.rank_level } - i[:ancestors].map{ |k| results_hash[k].to_i } ).count > 0 }.first
    puts kntr
    puts row[:id]
    ancestors = row[:ancestors].map{ |i| results.select{ |j| j[:id]==i }.first }
    standard_rank_levels = standard_ranks.select{ |sr| sr>row[:rank_level] && sr < root.rank_level }
    actual_rank_levels = row[:ancestors].map{ |k| results.select{ |j| j[:id]==k }.first[:rank_level].to_i }
    missing_rank_level = ( standard_rank_levels - actual_rank_levels ).first
    puts missing_rank_level
    child_rank_level = actual_rank_levels.select{ |a| a < missing_rank_level }.first
    if child_rank_level.nil?
      child_rank_level = row[:rank_level].to_i
      child = row
    else
      child = ancestors.select{ |a| a[:rank_level] == child_rank_level }.first
    end
    parent_rank_level = actual_rank_levels.select{ |a| a > missing_rank_level }.last

    new_node = {
      id: ( 0...7 ).map { ( 65 + rand( 26 ) ).chr }.join,
      name: nil,
      rank_level: missing_rank_level,
      ancestors: ancestors.select{ |a| a[:rank_level] >= parent_rank_level }.map{ |a| a[:id] }
    }
    results_hash = results_hash.merge( { new_node[:id] => new_node[:rank_level] } )
    results.select{ |i| i[:ancestors].include? child[:id] }.map{ |i| i[:ancestors].insert( i[:ancestors].index( child[:id] ), new_node[:id] ) }
    results.select{ |i| i[:id]==child[:id] }.map{ |i| i[:ancestors].append( new_node[:id] ) }
    results << new_node
    fake_key[new_node[:id]] = child[:id]
    kntr+=1
  else
    continue = false
  end
end

# Remove non standard nodes and update ancestry
to_del = results.select{ |a| !standard_ranks.include? a[:rank_level].to_i }; nil
to_del_set = to_del.map{ |a| a[:id] }.to_set; nil
new_res = results.select{ |a| standard_ranks.include? a[:rank_level].to_i }.
  map{ |a| 
    {
      id: a[:id],
      name: a[:name],
      rank_level: a[:rank_level],
      ancestors: [root.ancestor_ids[1..-1], a[:ancestors].select{ |i| !to_del_set.include? i }].flatten.compact
    }
    }; nil
unless root.rank_level == Taxon::ROOT_LEVEL
  root.ancestors[1..-1].each do |row|
    new_res << {
      id: row.id,
      name: row.name,
      rank_level: row.rank_level,
      ancestors: row.ancestor_ids[1..-1]
    }
  end
end

internodes = new_res.map{ |a| a[:ancestors] }.flatten.uniq.to_set; nil

# Map between taxon_id and class id
class_hash = {}
standard_ranks.each do |j|
  taxon_ids = new_res.select{ |i| i[:rank_level] == j }.map{ |i| i[:id] }
  indexes = ( 0..( taxon_ids.count - 1 ) ).step( 1 ).to_a
  class_hash = class_hash.merge( taxon_ids.zip( indexes ).to_h )
end
unless root.rank_level == Taxon::ROOT_LEVEL
  class_hash = class_hash.merge( root.ancestor_ids[1..-1].map{ |a| a }.zip( [0,0] ).to_h ); nil
end

# Export the taxonomy
CSV.open( "/home/inaturalist/taxonomy_data.csv", "wb" ) do |csv|
  csv << ["parent_taxon_id", "taxon_id", "class_id", "rank_level", "name"]
  new_res.each do |row|
    csv << [ row[:ancestors].last, row[:id], class_hash[row[:id]], row[:rank_level].to_i, row[:name] ]
  end
end

# Some code for rendering out json to vizualize here http://loarie.github.io/treenew5.html
=begin
def go_deeper( row, nr )<commit_msg>minor change to multitier vision export script<commit_after>directory = "/home/inaturalist"
test_count = 30
val_percent = 0.1
val_thres = 10
root_id = 48460

# Pick the root of some branch to export
root = Taxon.find( root_id )
ancestry_string = root.rank_level == Taxon::ROOT_LEVEL ?
  "#{ root.id }" : "#{ root.ancestry }/#{ root.id }"

# Part 1: determine the taxonomy

# Create a hash of the obs photo count for each node descending from the root
sql_query = <<-SQL
  SELECT t.id AS taxa_id, COUNT(*)
  FROM observations o
  JOIN taxa t ON t.id = o.taxon_id
  JOIN observation_photos op ON o.id = op.observation_id
  JOIN photos p ON p.id = op.photo_id
  WHERE t.is_active = true
  AND ( t.id IN ( #{ ancestry_string.gsub( "/","," ) } ) OR t.ancestry = '#{ ancestry_string }' OR t.ancestry LIKE ( '#{ ancestry_string }/%' ) )
  AND ( select count(*) from conservation_statuses ct where ct.taxon_id=t.id AND ct.iucn=70 AND ct.place_id IS NULL ) = 0
  AND p.type = 'LocalPhoto'
  GROUP BY t.id;
SQL
taxonomy = ActiveRecord::Base.connection.execute( sql_query )
photo_counts = taxonomy.map{|row| [row["taxa_id"], row["count"].to_i]}.to_h

# Keep only the leaves with enough downstream data
enough = []
ranks = Taxon::RANK_LEVELS.values.uniq.sort
standard_ranks = Taxon::RANK_LEVELS.select{ |k,v| ( Taxon::PREFERRED_RANKS.include? k ) && v > Taxon::SUBSPECIES_LEVEL && v != Taxon::SUPERFAMILY_LEVEL }.values.reverse
ranks[1..-1].each do |i|
  puts i
  enough_set = enough.map{ |row| row[:taxon_id] }.to_set
  Taxon.where("( ancestry = '#{ ancestry_string }' OR ancestry LIKE ( '#{ ancestry_string }/%' ) ) AND is_active = true AND rank_level = ?", i ).
  where("( select count(*) from conservation_statuses ct where ct.taxon_id=taxa.id AND ct.iucn=70 AND ct.place_id IS NULL ) = 0").each do |t|
    puts "\t#{t.id}"
    dset = t.descendants.pluck( :id ).to_set
    if ( dset & enough_set ).count > 0
      # internode
      enough << {
        taxon_id: t.id
      } 
    else
      # leaf
      if [
          photo_counts[t.id.to_s],
          t.descendants.pluck( :id ).map{ |j|
            photo_counts[j.to_s]
          }
        ].flatten.compact.sum >= 60
        enough << {
          taxon_id: t.id
        } 
      end
    end
  end
end
if root.rank_level == Taxon::ROOT_LEVEL
  enough_set = [enough.map{ |row| row[:taxon_id] }].flatten.to_set; nil
else
  enough_set = [enough.map{ |row| row[:taxon_id] },root.id].flatten.to_set
end


# Fetch the taxa
taxa = Taxon.find( enough_set.to_a ); nil
results = taxa.map{|i|
    {
      id: i[:id],
      name: i[:name],
      rank_level: i[:rank_level],
      ancestors: i[:ancestry].split( "/" ).select{ |j| enough_set.include? j.to_i }.map{ |j| j.to_i }
    }
  }; nil


# Add 'missing' nodes (e.g. if Class->Family, insert Order)
results_hash = results.map{ |i| [i[:id],i[:rank_level]] }.to_h; nil
kntr=0
fake_key = {}
continue = true
while continue
  if row = results.select{ |i| ( standard_ranks.select{ |sr| sr > i[:rank_level] && sr < root.rank_level } - i[:ancestors].map{ |k| results_hash[k].to_i } ).count > 0 }.first
    puts kntr
    puts row[:id]
    ancestors = row[:ancestors].map{ |i| results.select{ |j| j[:id]==i }.first }
    standard_rank_levels = standard_ranks.select{ |sr| sr>row[:rank_level] && sr < root.rank_level }
    actual_rank_levels = row[:ancestors].map{ |k| results.select{ |j| j[:id]==k }.first[:rank_level].to_i }
    missing_rank_level = ( standard_rank_levels - actual_rank_levels ).first
    puts missing_rank_level
    child_rank_level = actual_rank_levels.select{ |a| a < missing_rank_level }.first
    if child_rank_level.nil?
      child_rank_level = row[:rank_level].to_i
      child = row
    else
      child = ancestors.select{ |a| a[:rank_level] == child_rank_level }.first
    end
    parent_rank_level = actual_rank_levels.select{ |a| a > missing_rank_level }.last

    new_node = {
      id: ( 0...7 ).map { ( 65 + rand( 26 ) ).chr }.join,
      name: nil,
      rank_level: missing_rank_level,
      ancestors: ancestors.select{ |a| a[:rank_level] >= parent_rank_level }.map{ |a| a[:id] }
    }
    results_hash = results_hash.merge( { new_node[:id] => new_node[:rank_level] } )
    results.select{ |i| i[:ancestors].include? child[:id] }.map{ |i| i[:ancestors].insert( i[:ancestors].index( child[:id] ), new_node[:id] ) }
    results.select{ |i| i[:id]==child[:id] }.map{ |i| i[:ancestors].append( new_node[:id] ) }
    results << new_node
    fake_key[new_node[:id]] = child[:id]
    kntr+=1
  else
    continue = false
  end
end

# Remove non standard nodes and update ancestry
to_del = results.select{ |a| !standard_ranks.include? a[:rank_level].to_i }; nil
to_del_set = to_del.map{ |a| a[:id] }.to_set; nil
new_res = results.select{ |a| standard_ranks.include? a[:rank_level].to_i }.
  map{ |a| 
    {
      id: a[:id],
      name: a[:name],
      rank_level: a[:rank_level],
      ancestors: [root.ancestor_ids[1..-1], a[:ancestors].select{ |i| !to_del_set.include? i }].flatten.compact
    }
    }; nil
unless root.rank_level == Taxon::ROOT_LEVEL
  root.ancestors[1..-1].each do |row|
    new_res << {
      id: row.id,
      name: row.name,
      rank_level: row.rank_level,
      ancestors: row.ancestor_ids[1..-1]
    }
  end
end

internodes = new_res.map{ |a| a[:ancestors] }.flatten.uniq.to_set; nil

# Map between taxon_id and class id
class_hash = {}
standard_ranks.each do |j|
  taxon_ids = new_res.select{ |i| i[:rank_level] == j }.map{ |i| i[:id] }
  indexes = ( 0..( taxon_ids.count - 1 ) ).step( 1 ).to_a
  class_hash = class_hash.merge( taxon_ids.zip( indexes ).to_h )
end
unless root.rank_level == Taxon::ROOT_LEVEL
  class_hash = class_hash.merge( root.ancestor_ids[1..-1].map{ |a| a }.zip( [0,0] ).to_h ); nil
end

# Export the taxonomy
CSV.open( "#{directory}/taxonomy_data.csv", "wb" ) do |csv|
  csv << ["parent_taxon_id", "taxon_id", "class_id", "rank_level", "name"]
  new_res.each do |row|
    csv << [ row[:ancestors].last, row[:id], class_hash[row[:id]], row[:rank_level].to_i, row[:name] ]
  end
end

# Some code for rendering out json to vizualize here http://loarie.github.io/treenew5.html
=begin
def go_deeper( row, nr )
{'fraction_non_alphanumeric': 0.1598986845330501, 'fraction_numerical': 0.007516953999509764, 'mean_word_length': 3.3981315127560188, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 23, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '959579', 'n_tokens_mistral': 4842, 'n_tokens_neox': 4686, 'n_words': 1089}