import traceback
import praw
import time
import sqlite3

# Login info
USERNAME = "FreeFolkStewardBot"
PASSWORD = "qVQauofFb5li"
USERAGENT = "/r/FreeFolk Automaton"
SUBREDDIT = "FreeFolkMods"

# Timing
MAXPOSTS = 20
WAIT = 30
CLEANCYCLES = 20
numCycles = 1

sql = sqlite3.connect('mailing.db')
print('Loaded SQL Database')
cur = sql.cursor()
cur.execute('CREATE TABLE IF NOT EXISTS users (name TEXT)')
cur.execute('CREATE TABLE IF NOT EXISTS oldposts (id TEXT)')
sql.commit()

print('Logging in...')
r = praw.Reddit(USERAGENT)
r.login(USERNAME, PASSWORD,disable_warning=True)

def mailingbot():
    print('Cycle #%i' % numCycles)
    print('Searching %s.' % SUBREDDIT)
    subreddit = r.get_subreddit(SUBREDDIT)
    posts = list(subreddit.get_comments(limit=MAXPOSTS))
    posts.reverse()
    for post in posts:
        pid = post.id
        pauthor = post.author.name
        ptop = post.submission.id
        pbody = post.body.lower()

        try:
            post.author.name # in case user account is deleted or suspended
        except AttributeError:
            continue # user is not available

        cur.execute('SELECT * FROM oldposts WHERE ID=?', [pid])
        if cur.fetchone():
            # already checked this post
                continue

        # record post so we don't act on it multiple times
        cur.execute('INSERT INTO oldposts VALUES(?)', [pid])
        sql.commit()

        # check for trigger phrases
        beginning = pbody[:6]
        if beginning == '!addme':
            print('******* ADD triggered by /u/' + pauthor +' in COMMENT ' + pid + ' on POST ' + ptop)
            # add username to mailing list
            cur.execute('SELECT * FROM users WHERE name LIKE \'' + pauthor + '\'')
            if cur.fetchone():
                # already in database
                REPLY_SUBJECT = 'Couldn\'t add you'
                REPLY_MESSAGE = 'I see you tried to join the FreeFolk mailing list, but you were already on it. Were you trying to leave the list? You can say !DELME to be removed.'
                r.send_message(pauthor, REPLY_SUBJECT, REPLY_MESSAGE)
            else:
                # add to database
                cur.execute('INSERT INTO users VALUES (\'' + pauthor + '\')')
                sql.commit()
                REPLY_SUBJECT = 'I added you'
                REPLY_MESSAGE = 'I see you asked to join the FreeFolk mailing list. It was a great success! To leave the list in the future, you can say !DELME to be removed.'
                r.send_message(pauthor, REPLY_SUBJECT, REPLY_MESSAGE)
        elif beginning == '!delme':
            print('******* DEL triggered by /u/' + pauthor +' in COMMENT ' + pid + ' on POST ' + ptop)
            # remove username from mailing list
            cur.execute('SELECT * FROM users WHERE name LIKE \'' + pauthor + '\'')
            if cur.fetchone():
                # already in database
                cur.execute('DELETE FROM users WHERE name LIKE \'' + pauthor + '\'')
                sql.commit()
                REPLY_SUBJECT = 'I removed you'
                REPLY_MESSAGE = 'I see you asked to leave the FreeFolk mailing list. It was a great success! To join again in the future, you can say !ADDME to be put back on.'
                r.send_message(pauthor, REPLY_SUBJECT, REPLY_MESSAGE)
            else:
                # remove from database
                REPLY_SUBJECT = 'Couldn\'t remove you'
                REPLY_MESSAGE = 'I see you tried to leave the FreeFolk mailing list, but you weren\'t on it. Were you trying to join the list? You can say !ADDME to be included.'
                r.send_message(pauthor, REPLY_SUBJECT, REPLY_MESSAGE)

        elif beginning == '!pmall':
            print('******* PM triggered by /u/' + pauthor +' in COMMENT ' + pid + ' on POST ' + ptop)
            # check user is authorized to send
            if pauthor == 'leafeon123' or pauthor == 'itsCarraldo' or pauthor == 'rwjehs' or pauthor == 'Dunkcity239' or pauthor == 'Deadm1r' or pauthor == 'Jon_targaryen1' or pauthor == 'cgmcnama' or pauthor == '123hooha123' or pauthor == 'ghost-of-harrenhal' or pauthor == 'Darth_Birdperson' or pauthor == 'corduroyblack' or pauthor == '_varamyr_fourskins_' or pauthor == 'BladeofTarth':
                # remove first 7 characters of post -- should be "!pmall " -- note space, not case sensitive
                PM_MESSAGE = post.body[7:]
                # get list of users to send PM to
                print('SENDING!')
                cur.execute('SELECT name FROM users')
                usernames = cur.fetchall()
                counter = 0
                for username in usernames:
                    counter = counter + 1
                    name = username[0]
                    print(name)
                    r.send_message(name, 'A Raven Has Arrived', PM_MESSAGE)
                print('TOTAL: ' + str(counter) )
        # no triggers
        else:
            print(pid + ' - no trigger')

cycles = 0
while True:
    try:
        mailingbot()
        cycles += 1
        numCycles +=1
    except Exception as e:
        traceback.print_exc()
    # clean cycles
    if cycles >= CLEANCYCLES:
        print('Cleaning database')
        cur.execute('DELETE FROM oldposts WHERE id NOT IN (SELECT id FROM oldposts ORDER BY id DESC LIMIT ?)', [MAXPOSTS * 2])
        sql.commit()
        cycles = 0
    print('Running again in %d seconds \n' % WAIT)
    time.sleep(WAIT)