import csv
from datetime import datetime
import psycopg2



#path to csv_file, make sure you save the file as a .csv
csv_file = ''

#scrutinizer ip address
scrutinizer_ip = ''

#remote user for postgres
scrutinizer_user = 'scrutremote'

#password, this will be whatever the password is to login to webui as admin.
scrutinizer_password = ''

#function used to convert the date in the CSV into Epoch.
def convert_to_epoch(date):
    p = '%m/%d/%Y %H:%M'
    epoch = datetime(1970, 1, 1)
    if date.count(":") == 1:
        return date.split(":")[0]
    else:
        date = ":".join(date.split(":", 2)[:2])
        return int(((datetime.strptime(date, p) - epoch).total_seconds()))



try:
    conn = psycopg2.connect("dbname='plixer' user={} host={} password={}".format(scrutinizer_user,scrutinizer_ip, scrutinizer_password))
    print('connected to database, updating')
except:
    print("I am unable to connect to the database")


cur = conn.cursor()

#open up the CSV and iterate through it.

record_counter = 0

with open(csv_file, newline='') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        record_counter +=1
        #find the IP Address from the spreadsheet
        ip_address = row['framedipaddress']

        #find the device_id from the spreadsheet
        device_id = row['imsi']

        #find the start time from the spreadsheet
        first_time_stamp = convert_to_epoch(row['acctstarttime'])

        #find the last login time from the spreadsheet
        last_time_stamp = convert_to_epoch(row['acctupdatetime'])

        #execute the SQL query. We update the last_login to be the most recent here, this is only in the case if there are duplicates.
        cur.execute("""INSERT INTO plixer.summary_authentication_ip
        (ipaddress, username, domain, machine_name, datasource, first_seen, last_login, last_logoff)
         VALUES
         (inet_a2b('{}'), '{}','','','dave_newbern_data',{},{},'0') ON CONFLICT ON CONSTRAINT summary_authentication_ip_pkey DO UPDATE SET last_login = {} ; """.format(ip_address,device_id, first_time_stamp, last_time_stamp, last_time_stamp )  )



cur.execute("""SELECT COUNT(*) FROM plixer.summary_authentication_ip WHERE datasource='dave_newbern_data'""")
record_count = cur.fetchall()
record_count = record_count[0][0]
print('There were {} rows in the spreadsheet, the table now holds {} unique users. This means we updated the last login for {} users'.format(record_counter, record_count, (record_counter - record_count)))
conn.commit()

conn.close()