require 'sqlite3'

db_file_path = File.join(File.dirname(__FILE__), "jukebox.sqlite")
db = SQLite3::Database.new(db_file_path)

# Queries

def number_of_rows(db, table_name)
  # TODO: count number of rows in table table_name
  result = db.execute("SELECT COUNT(*) FROM #{table_name}")
  result[0][0]
end

def sorted_artists(db)

  # TODO: return array of artists' names sorted alphabetically
  result = db.execute("SELECT name FROM artists
  ORDER BY name ASC LIMIT(3)")
  result.map{|a| a[0]}

end

def love_tracks(db)
  # TODO: return array of love songs
  result = db.execute("SELECT name FROM tracks WHERE name LIKE '%love%'")
  result.flatten
end

def long_tracks(db, min_length)
  # TODO: return tracks verifying: duration > min_length (minutes)
  result = db.execute("SELECT name, milliseconds FROM tracks WHERE milliseconds > #{min_length*60000} ORDER BY milliseconds LIMIT(5)")
  result.map!{|r| {name: r[0], time: r[1]/60_000} }
end

# Joins

def detailed_tracks(db)
  # TODO: return the list of tracks with their album and artist

  result = db.execute("SELECT tracks.name, albums.title, artists.name from albums
    join tracks on tracks.album_id = albums.id
    join artists on albums.artist_id = artists.id
    limit 3 ")
end

def stats_on(db, genre_name)
  # TODO: For the given category of music, return the number of tracks and the average song length (as a stats hash)
  result = db.execute("SELECT COUNT(*), AVG(milliseconds)  FROM tracks

    JOIN genres ON genres.id = tracks.genre_id
    WHERE genres.name = '#{genre_name}'
    GROUP BY genres.name

    ")

  {count: result[0][0], time: (result[0][1]/60000).round(2)}
end

def top_five_artists(db, genre_name)
  # TODO: return list of top 5 rock artists
  result = db.execute("SELECT artists.name, COUNT(*) AS c
   FROM tracks JOIN albums ON albums.id = tracks.album_id
   JOIN artists ON artists.id = albums.artist_id
   JOIN genres ON genres.id = tracks.genre_id
   WHERE genres.name = '#{genre_name}'
   GROUP BY artists.name
   ORDER BY c DESC LIMIT(5)
    ")
end


# ['artists','albums','tracks','genres'].each do |table_name|
#  puts "A tabela #{table_name} tem #{number_of_rows(db, table_name)}"
# end

# p sorted_artists(db)

# p love_tracks(db)

#p long_tracks(db, 2)

 # p detailed_tracks(db)

 #p stats_on(db, 'Rock')

p top_five_artists(db, "Rock")