"""
  COMP364 Lecture 26
  Author: Mathieu Perreault
"""
import sqlite3, csv, os, sys

# Create the CSV reader.
csvreader = csv.reader(open(sys.argv[1]))

# (Re)create the database and connect to it.
if os.path.exists('database.sql'):
  os.remove('database.sql')

connection = sqlite3.connect('database.sql')

# Get the cursor and create a table.
cursor = connection.cursor()

cursor.execute('CREATE TABLE tags (artist_name TEXT PRIMARY KEY, taglist TEXT)')

for row in csvreader:
  # row = ['text_id', 'name', 'tag', 'number of occurrences']
  # Format: 000b1990-4dd8-4835-abcd-bb6038c13ac7,Hayden,indie rock,19
  if len(row) < 4:
    continue # Skip to the next row
    
  artist = row[1].lower()
  newtag = row[2].lower()
  score = int(row[3])
  
  if score < 40:
    # Relevance tweak. Only keep the best tags
    continue # Skip to the next row
  
  cursor.execute('SELECT * FROM tags WHERE artist_name=?', (artist,))
  fetched = cursor.fetchone()
  if fetched is not None:
      # We have an artist!
      # Update their record
      # Get the artist's current tag list, add our tag to it, and update the record.
      previoustags = fetched[1] # rock,techno
      taglist = previoustags.split(',') #['rock', 'techno']
      taglist.append(newtag) #['rock', 'techno', 'newtag']
      tags = ",".join(taglist) # "rock,techno,newtag"
      cursor.execute('UPDATE tags SET taglist=? WHERE artist_name=?', (tags, artist))
  else: 
      # No artist
      # Create one with the first tag
      cursor.execute('INSERT INTO tags VALUES (?, ?)', (artist, newtag))
  
connection.commit()