#modules we use throughout this script
import csv
import mysql.connector
import fileinput
import urllib
import sys


#connection info for the database
cnx = mysql.connector.connect(host="localhost", user="test", password="test!", database="test")
query = cnx.cursor(prepared=True)


#Here we download the Robocallers .csv file, the string at the end of the URL are filters passed to the API to only pull down the data we need
download = urllib.URLopener()
download.retrieve("https://opendata.fcc.gov/resource/sr6c-syda.csv?$$app_token=someparamshere", "RoboCaller.csv")
#add test to see if file downloaded or not, if file did not then sys.exit()
#Here build a filterd .csv to prevent any duplicate records as well as skip the first line of the .csv file as it contains field names
map = {}
row_count = 0
with open(r'RoboCaller.csv', 'r') as infile:
    reader = csv.reader(infile)
    for row in reader:
        (caller_id_number, issue, issue_date) = row
        row_count = row_count + 1
        if row_count == 1:
            continue
        if map.has_key(caller_id_number):
            continue
        else:
            map[caller_id_number] = (issue, issue_date)

#at this point we count our Filtered CSV file to see if its massivly truncated.
#we check against a threshhold to see if the amount of records are less than it.
#if they are we close the program and throw an error if not we continue.
if row_count < 80000:
    print('this list is far to truncated, terminating program')
    sys.exit()

#Here we check the count of the table and store it
query.execute("Select count(*) from Numbers")
oldcount = query.fetchall()
print ("The old count is:" + str(oldcount))

#Here we query the db to build the whitelist as well as exclude numbers we own
query.execute("Select whitelist FROM whitelist")
whitelist = [entry for (entry,) in query.fetchall()]
#print("the numbers" + str(whitelist) + "are on the white list and will be skipped")

query.execute("Select TN from DID_Numbers")
didnumbers = [entry for (entry,) in query.fetchall()]
#print("the numbers" + str(didnumbers) + "are apart of the numbers we own and will be omitted from the black list")

#here were Purge the current value in the mysql DB table, so we dont build duplicates
query.execute("TRUNCATE TABLE Numbers")


#here we check every Key in the Dict(Caller_Id_Number is our Key) as well as the tuples (v) accociated each key (the tuples are issue & issue_date)
for caller_id_number, v in map.iteritems():
    caller_id_number = caller_id_number[:3] + caller_id_number[4:7] + caller_id_number[8:12]
    if caller_id_number in whitelist:
        print("Phone Number " + str(caller_id_number) + " is in the whitelist and will be ignored")
        continue
    elif caller_id_number in didnumbers:
        print("Phone Number " + str(caller_id_number) + " is a number SimplyBits Owns and will be ignored")
        continue
    else:
       query.execute("INSERT INTO Numbers ( Numbers, issue, date ) VALUES(%s,%s,%s)", (caller_id_number, v[0], v[1][:10])) #sql query to insert numbers into black list

#Here we check the new count
query.execute("Select count(*) from Numbers")
newcount = query.fetchall()
print ("The new Count is:" + str(newcount))