import sqlite3
from time import sleep

import praw


# connect to the database. if it doesn't exist, automatically create.
conn = sqlite3.connect('storage.db', isolation_level=None)
cur = conn.cursor()

processed_submissions = set()
comments_to_process = set()

def process_submission(submission):
    if submission.id in processed_submissions:
        return
    response = submission.reply("Upvote if this submission is good and follows the rules! Downvote if it doesn't..   *If this comment reaches 0 score, it'll be removed automatically.")
    # distinguish the post and sticky it.
    response.mod.distinguish(how='yes', sticky='True')
    processed_submissions.add(submission.id)
    comments_to_process.add(response.id)
    cur.execute('INSERT INTO stuffToPlot (commentID, submissionID) VALUES (?, ?)',
        (response.id, submission.id))

def process_commented_submissions():
    for comment_id in comments_to_process:
        comment = reddit.comment(comment_id)
        if comment.score < 1:
            print("A submission has 0 points. Removing now.")
            reddit.subreddit('USEFULREDCIRCLEBOT').message("A comment with less than one point has been detected.  Please review: %s" % comment.permalink, 'Review Script Name')
            parent = comment.parent()
            comments_to_process.remove(comment_id)
            cur.execute("DELETE FROM stuffToPlot WHERE commentID=?", (comment_id,))
            submission = reddit.submission(id=parent)
            submission.mod.remove(spam=False)

reddit = praw.Reddit(user_agent='x',
                    client_id='x', client_secret='x',
                    username='x', password='x')
print('reddit logon succesful')

cur.execute('CREATE TABLE IF NOT EXISTS stuffToPlot(commentID TEXT, submissionID TEXT)') # create tables if they dont exist
cur.execute("SELECT commentID, submissionID FROM stuffToPlot")
for row in cur.fetchall():
    comments_to_process.add(row[0])
    processed_submissions.append(row[1])
print('data loaded from database')

while True:
    for submission in reddit.subreddit("USEFULREDCIRCLEBOT").new(limit=35):
        process_submission(submission)

    process_commented_submissions()
    # change pause to whatever seems useful
    sleep(10)