import mysql.connector as mysql
import requests 
#from googletrans import Translator
import translators as ts



##ssh -N -L 3306:127.0.0.1:3306 -i k5spore.pem ubuntu@singapore.k5software.com
 
 
db = mysql.connect(
    host = "127.0.0.1",
    user = "w3_admin",
    passwd = "$a7!1BoHVa7#GEAYs1",
    database = "worship"
)


cursor = db.cursor(buffered=True)

query = "SELECT * FROM songs where language = 'zh-Hans' and converted = 0"

## getting records from the table
cursor.execute(query)

## fetching all records from the 'cursor' object
records = cursor.fetchall()

cursor.close()

#translator = Translator()

## Showing the data
for record in records:
	cursor = db.cursor(buffered=True)
	
	print("processing " + str(record[0]))

	#trans = translator.translate(record[1], dest='zh-TW')
	newTitle = ts.google(record[1], to_language='zh-TW' ) # default: from_language='auto', to_language='en'
	
	#newTitle = trans.text
	#trans = translator.translate(record[2], dest='zh-TW')
	newContent = ts.google(record[2], to_language='zh-TW' )
	#newContent = trans.text
	#print(newContent)

	
	
	print("inserting song ")
	
	query = "CREATE TEMPORARY TABLE tmptable SELECT * FROM songs WHERE id =" + str(record[0])
	cursor.execute(query)
	query = "UPDATE tmptable SET language='zh-Hant', name = '" + newTitle + "', content='" + newContent + "'"
	cursor.execute(query)
	query = "INSERT INTO songs (name, content, `key`, language, small_cover, time, instructions, capo, alt_name, alt_name_2, converted)  SELECT name, content, `key`, language, small_cover, time, instructions, capo, alt_name, alt_name_2, converted  FROM tmptable;"
	cursor.execute(query)
	query = "drop table tmptable;"
	cursor.execute(query)
	query = "select * from songs order by id desc limit 1;"
	cursor.execute(query)
	allSongs = cursor.fetchall()
	insertedID = allSongs[0][0]
	print("new inserted record" + str(insertedID))
	print("inserting artists and song_artist ")
	query = "select * from song_artist sa inner join artists a on a.id = sa.artist_id where sa.song_id = " + str(record[0]) 
	cursor.execute(query)
	allArtists = cursor.fetchall()
	
	for r in allArtists:
		artist = r[3]
		#artist = translator.translate(artist, dest='zh-TW').text
		artist =  ts.google(artist, to_language='zh-TW' )
		print(artist)
		query = "select * from artists where name = '" + artist + "'"
	
		cursor.execute(query)
		rr = cursor.fetchall()
	
		if cursor.rowcount > 0 :
			newArtistID = rr[0][0]
		else:
			query = "insert into artists (name) VALUES ('" + artist + "')"
		
			cursor.execute(query)
			newArtistID = cursor.lastrowid
			db.commit()
		
		query = "INSERT INTO song_artist (song_id, artist_id) VALUES (" + str(insertedID) + ", " + str(newArtistID) +")"
		cursor.execute(query)
		db.commit()
		
		
	print("inserting song_category ")
	
	query = "SELECT * FROM song_category WHERE song_id =" + str(record[0]) 
	cursor.execute(query)
	allSongCat = cursor.fetchall()
	
	
	for r in allSongCat:
		catID = r[1]

		if (catID == 1):
			catID = 7
		elif catID == 3:
			catID = 8
		elif catID == 6:
			catID = 9
		else:
			catID = catID
			
		query = "INSERT INTO song_category (song_id, category_id) VALUES (" + str(insertedID) + ", " + str(catID) +")"
		cursor.execute(query)
		db.commit()
		
	

	query = "CREATE TEMPORARY TABLE tmptable SELECT * FROM song_theme WHERE song_id =" + str(record[0]) 
	cursor.execute(query)
	query = "UPDATE tmptable SET song_id = " + str(insertedID)
	cursor.execute(query)
	query = "INSERT INTO song_theme SELECT * FROM tmptable;"
	cursor.execute(query)
	query = "drop table tmptable;"
	cursor.execute(query)


	query = "CREATE TEMPORARY TABLE tmptable SELECT * FROM song_resource WHERE song_id =" + str(record[0]) 
	cursor.execute(query)
	query = "UPDATE tmptable SET song_id = " + str(insertedID)
	cursor.execute(query)
	query = "INSERT INTO song_resource(song_id, resource, timeMatched, source_id) SELECT song_id, resource, timeMatched, source_id FROM tmptable;"
	cursor.execute(query)
	query = "drop table tmptable;"
	cursor.execute(query)

	query = "select * from related order by related_id desc limit 1;"
	cursor.execute(query)
	allRelated = cursor.fetchall()
	relatedID = allRelated[0][0] +1
	print(relatedID)

	query = "INSERT INTO related (related_id, song_id, language_id) VALUES (" + str(relatedID) + "," + str(record[0])    +",2) "
	print(query)
	cursor.execute(query)
	db.commit()
	query = "INSERT INTO related (related_id, song_id, language_id) VALUES (" + str(relatedID) + "," +  str(insertedID)   +",3) "
	print(query)
	cursor.execute(query)
	db.commit()
	
	
	query = "Update songs set converted = 1 where id = " + str(record[0])
	cursor.execute(query)
	db.commit()
		
	cursor.close()
    
db.close()