import psycopg2
import re

# from pprint import pprint


def get_events():
    """Simply retrieve events"""
    query = """SELECT * FROM
        (SELECT * FROM events 
            WHERE timestamp BETWEEN now() - INTERVAL '24 HOURS' AND now()
            AND (news LIKE '% sent an application to join the Faction.' 
            OR news LIKE '% left the faction.' 
            OR news LIKE '% has accepted %'
            OR news LIKE '% has declined %')
            ORDER BY timestamp DESC) tmp
        ORDER BY timestamp;"""
    return query


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


def database_insert(cur, query):
    try:
        cur.execute(query)
    except (Exception, psycopg2.Error) as error:
        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)