import itertools
import requests
import xml.etree.ElementTree as ET
import sqlite3
import json
import sys
import traceback

# Open save file and get UID of last saved user
f = open('lastid.txt', 'r')
lastcount = int(f.read())
f.close()

# Create database connection database tables
dbconn = sqlite3.connect('roster.db', check_same_thread=False)
db = dbconn.cursor()
db.execute('''CREATE TABLE IF NOT EXISTS members(userid int, handle text, joindate text, gender text, roles text)''')

# Start session to preserve across future requests
s = requests.Session()
s.post('https://example.com/api/account/signin', data={'username':'username', 'password' : 'password', 'remember' : '1'})

# Main function for collecting, parsing, and formatting XML data
def dataget(count):
    try:
        xml = s.get('http://example.com/api/users/get.ext', params={'user' : count}, timeout=5)
    # If any connection related exceptions occur retry until connection restored
    except requests.exceptions.RequestException as e:
        print('Connection Error:', e)
        print('Retrying #'+count)
        dataget(count)
        return

    if xml.status_code == 200: # 200 = Successful load of XML data
        userdata = ET.fromstring(xml.content) # Full XML data

        # Get just the data we want to save from the XML data
        userid = userdata.find('Profile/UserID').text
        handle = userdata.find('Profile/Name').text
        joindate = userdata.find('Profile/DateFirstVisit').text
        gender = userdata.find('Profile/Gender').text
        roles = [elem.text for elem in userdata.findall('UserRoles/Item')]
        rolelist = json.dumps(roles)

        # List to be inserted into the database
        userinsert = [userid, handle, joindate, gender, rolelist]
        # Run function to write data into database
        datawrite(count,userid, handle, userinsert)
    # Gracefully handle missing user entries
    elif xml.status_code == 410 or 404:
        print('404: User', count, 'not found')
    # Catch any other unexpected responses and halt script
    else:
        print('Unexpected response for user', count+'. Halting')
        sys.exit()

    return

# Write data from dataget function to SQLite database
def datawrite(count, userid, handle, userinsert):
    print('Writing', '#'+userid+":", handle, 'to the database...\t\t', end='')

    db.execute("INSERT INTO members VALUES (?,?,?,?,?)", userinsert) # "?" placeholder is REQUIRED for SQL-safe data
    dbconn.commit()
    print('DONE')

# Main loop that counts up from lastcount integer infinitely running dataget function on each count
for i in itertools.count(lastcount):
    try:
        count = str(i)
        dataget(count)

    # Capture keyboard interrupt, update UID save file, and exit for graceful closes
    except KeyboardInterrupt as e:
        f = open('lastid.txt', 'w')
        print('Saving last user ID')
        f.write(count)
        f.close
        print('Exiting')
        sys.exit()

    # Capture all other possible exceptions, save last UID if possible, log exception, then allow failure
    except Exception as e:
        f = open('lastid.txt', 'w')
        f.write(count)
        f.close
        ef = open('lastexception.txt', 'w')
        traceback.print_exc(file = ef)
        ef.close()
        raise e