slork/cron.py

import json
import requests
import sqlite3
import sys
import time
import settings
from pprint import pprint, pformat


def send_notification(maintainer, player, property, old_value, new_value):
    name = _name(maintainer, player)
    message = f"{name} [{player}] property '{property}' changed from {old_value} to {new_value}"
    _send(maintainer, message)
    conn.close()


def _name(maintainer, player):
    query = """SELECT value FROM player
        WHERE maintainer = ?
        AND player = ?
        AND property = 'name';"""
    conn = sqlite3.connect(settings.db_path + '/slork.sqlite')
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()
    cur.execute(query, (maintainer, player))
    name = '?'
    val = cur.fetchone()
    if val:
        name = val['value']
    conn.close()
    return name

def _send(maintainer, message):
    query = """SELECT discord FROM discord WHERE maintainer = ?;"""
    conn = sqlite3.connect(settings.db_path + '/slork.sqlite')
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()
    cur.execute(query, (maintainer,))
    discord = cur.fetchone()['discord']
    conn.close()
    message_json = {"content": message}
    response = requests.post(discord, json=message_json)
    if response.status_code in [200, 204]:
        print("Webhook executed")
        time.sleep(1)
    else:
        print("status code {}: {}".format(response.status_code, response.content.decode("utf-8")))
        time.sleep(10)

def update_value(maintainer, player, property, new_value):
    print(f"update_value({maintainer}, {player}, {property}, {new_value})")
    query = """INSERT OR REPLACE INTO player (maintainer, player, property, value)
        VALUES (?, ?, ?, ?)"""
    conn = sqlite3.connect(settings.db_path + '/slork.sqlite')
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()
    cur.execute(query, (maintainer, player, property, new_value))
    conn.commit()
    conn.close()

def update_missing_value(maintainer, player, property, value):
    print(f"update_missing_value({maintainer}, {player}, {property}, {value})")
    query = """INSERT OR REPLACE INTO player (maintainer, player, property, value)
        VALUES (?, ?, ?, ?);"""
    conn = sqlite3.connect(settings.db_path + '/slork.sqlite')
    cur = conn.cursor()
    cur.execute(query, (maintainer, player, property, value))
    conn.commit()
    conn.close()

def cron():
    query_zero = """SELECT DISTINCT maintainer FROM maintainer;"""
    conn = sqlite3.connect(settings.db_path + '/slork.sqlite')
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()
    
    query_first = """SELECT player_properties.maintainer,
        maintainer.api, player_properties.player,
        player_properties.property
        FROM player_properties
        JOIN maintainer ON player_properties.maintainer = maintainer.maintainer
        WHERE player_properties.maintainer = ?;"""

    cur.execute(query_zero)
    maintainers = cur.fetchall()
    for maintainer in maintainers:
        # maintainers
        cur.execute(query_first, (maintainer['maintainer'],))
        players = cur.fetchall()
        messages = ''
        for player in players:
            # Rows for this maintainer
            print(f"{player['maintainer']} - {player['api']} - {player['player']} - {player['property']}")
            url = f"https://api.torn.com/user/{player['player']}?selections=personalstats,basic&key={player['api']}"
            print(url)
            response = requests.get(url)
            player_json = None
            print(response.text[:10])
            if response and response.text:
                player_json = json.loads(response.text)
                # pprint(faction_json)
            if 'error' in player_json:
                print(f"{player['maintainer']}")
                pprint(player_json)
                _send(player['maintainer'], '```'+pformat(player_json)+'```')
                break

            # Check for changes
            properties = json.loads(player['property'])
            if 'name' not in properties:
                properties.append(name)
            # pprint(properties)
            query_second = """SELECT player.value, properties.parent
                FROM player
                JOIN properties ON player.property = properties.property
                WHERE maintainer = ?
                AND player = ?
                AND player.property = ?;"""
            retrieved_properties = []
            for property in properties:
                # pprint((player['maintainer'], player['player'], property,))
                cur.execute(query_second, (player['maintainer'], player['player'], property,))
                p_row = cur.fetchone()
                if not p_row:
                    continue
                # print(f"property: '{property}' parent: '{p_row['parent']}' -> value: '{p_row['value']}'")
                retrieved_properties.append(property)
                new_value = ''
                try:
                    if (p_row['parent']):
                        new_value = player_json[p_row['parent']][property]
                    else:
                        new_value = player_json[property]
                except KeyError:
                    new_value = 0
                # print(f"p_row['value']: {p_row['value']} != new_value: '{new_value}'")
                if str(p_row['value']) != str(new_value):
                    if len(messages) >1750:
                        _send(player['maintainer'], messages[:2000])
                        messages = ''
                    if messages:
                        messages += '\n'
                    name = _name(player['maintainer'], player['player'])
                    messages += f"{name} [{player['player']}] property '{property}' changed from {p_row['value']} to {new_value}"
                    # send_notification(player['maintainer'], player['player'], property, p_row['value'], new_value)
                    update_value(player['maintainer'], player['player'], property, new_value)
            new_properties = []
            # print(f"len(properties): {len(properties)}")
            # print(f"len(retrieved_properties): {len(retrieved_properties)}")
            for item in properties:
                if item not in retrieved_properties:
                    new_properties.append(item)
            if len(new_properties) > 0:
                # print(f"len(new_properties): {len(new_properties)}")
                for item in new_properties:
                    query_third = """SELECT parent FROM properties where property = ?;"""
                    cur.execute(query_third, (item,))
                    parent = cur.fetchone()['parent']
                    try:
                        if (parent):
                            value = player_json[parent][item]
                        else:
                             value = player_json[item]
                    except KeyError:
                        value = 0
                    print(f"item: {item} -> value: {value}")
                    update_missing_value(player['maintainer'], player['player'], item, value)
            time.sleep(2)
        _send(maintainer['maintainer'], messages)
    conn.close()

if __name__ == '__main__':
    cron()