import psycopg
import re

from pprint import pprint


def get_events():
    """Simply retrieve events"""
    query = """SELECT * FROM
        (SELECT hash, timestamp, news, faction, faction_id
            FROM events
            WHERE (timestamp BETWEEN now() - INTERVAL '24 HOURS' AND now())
            AND (news LIKE '% has applied to join the faction.' 
            OR news LIKE '% left the faction%' 
            OR news LIKE '% has accepted %'
            OR news LIKE '% has declined %'
            OR news LIKE '% was kicked out of the faction %')
            ORDER BY timestamp DESC) tmp
        ORDER BY timestamp;"""
    return query


def database_select(cur, query, debug=True):
    results = None
    try:
        cur.execute(query)
        results = cur.fetchall()
        if debug:
            pprint(results)
    except (Exception, psycopg.Error) as error:
        print(cur.mogrify(query))
        print("database_select():")
        print("PostgreSQL error from psycopg2:", error)
    return results


def database_insert(cur, query, debug=True):
    try:
        cur.execute(query)
    except (Exception, psycopg.Error) as error:
        print(cur.mogrify(query))
        print("database_insert:")
        print("PostgreSQL error from psycopg2:", error)


def create_links(news_string):
    regex = re.compile(r'<a href = "?https?://www\.torn\.com/profiles\.php\?XID=(\d+)"?>([^<]+)</a>')
    return_string = re.sub(regex, r'[\2 [\1]](<https://www.torn.com/profiles.php?XID=\1>)', news_string)
    return return_string


def escape_markdown(news_string):
    regex_underscore = re.compile(r'_.+_')
    regex_star = re.compile(r'\*.+\*')
    regex_tilde = re.compile(r'~.+~')
    boolean = re.search(regex_underscore, news_string) or \
        re.search(regex_star, news_string) or \
        re.search(regex_tilde, news_string)
    if boolean:
        news_string = news_string.translate(str.maketrans({'_': '\_', '*': '\*', '~': '\~'}))
    return news_string


def is_broadcast(cur, this_id):
    query = f"""SELECT COUNT(*) FROM human_resource WHERE event = '{this_id}';"""
    res = database_select(cur, query)
    # pprint(res)
    boolean = True if res[0][0] == 1 else False
    return boolean


def record_broadcast(cur, event_id):
    query = f"""INSERT INTO human_resource(event) VALUES('{event_id}');"""
    database_insert(cur, query)