import praw
import tweepy
import os
import re
import sqlite3
import threading
import queue

reddit = praw.Reddit(username='HabitsAndTraits',password='!@V3rYs3cur3!p4sssw0rD!!!', client_id='Lvk-tDVDT2YzqQ',client_secret='ICnAki6j6K9aZtse1xuB-317g3c', user_agent='A twitter cross post agent')
twitter = tweepy.OAuthHandler('tuX3uLpctUAhx8ppMt2IJBgfO','8R8TOloQodHFT8nAVfjgibLKpZN5ekdz3cABDylHDmE9u7cNcF')
twitter.set_access_token('4870773499-E6ngKNBKaLhENTeMv33ckM8yRThk72xCqKE6DMo', 'IuLZuLcmlhmDQD2F2DQ6I1dBucJhsjCjO6Ru8BqfRQlPy')
api = tweepy.API(twitter)
q = queue.Queue()

class Database(object):

	def __init__(self, dbFile):
		self.conn = sqlite3.connect(dbFile+'.db')
		self.cur = self.conn.cursor()

	def entry(self, tablename, values):
		print('Entering data to database...')
		if type(values) != list:
			values = values.split(' ') #convert to a list if necessary
		self.cur.execute('INSERT INTO {} VALUES({})'.format(tablename, ','.join(['?' for value in values])),values)
		self.conn.commit()
		print("Succesfully submitted and saved data!")

	def query(self, tablename, search_param=None, query=None, fetchall=False, limit='*'):
		print('Querying database...')
		if query is None:
			self.cur.execute('SELECT {} FROM {}'.format(limit, tablename))
		else:
			self.cur.execute('SELECT {} FROM {} WHERE {}=?'.format(limit, tablename, search_param), [query])
		print('Query sucessful')
		if fetchall is False:
			return self.cur.fetchone()
		else:
			return self.cur.fetchall()

	def create_table(self, tablename, values):
		if type(values) != list:
			values = values.split(' ')
		self.cur.execute('CREATE TABLE IF NOT EXISTS {}({})'.format(tablename, ','.join(values)))
		self.conn.commit()
		print('Created table: {}...'.format(tablename))


class twitterParser(object):

	def __init__(self, handle, queue=q):
		self.tweets = api.user_timeline(handle)
		self.queue = queue
		self.__call__()

	def __call__(self):
		tweets = [tweet for tweet in self.tweets if re.search('http', tweet.text) and not Database('twitter').query('retweeted',[tweet.id, tweet.author.screen_name])]
		for tweet in tweets:
			Database('twitter').entry('retweeted',[tweet.id, tweet.author.screen_name])
			self.queue.put(tweet)

class redditParser(object):

	def __init__(self, tweet, subreddit='Pubtips'):
		self.tweet = tweet
		self.subreddit = subreddit
		self.author = tweet.author.screen_name
		self.text = tweet.text
		self.url = self.getUrl()
		self.__call__(self.url)

	def getUrl(self):
		try:
			url = self.tweet.entities['media'][0]['media_url']
		except Exception as e:
			url = None
		print(url)
		return url

	def __call__(self, url):
		if self.url == None:
			pass
		else:
			submission = reddit.subreddit(self.subreddit).submit(self.text, url=url)
			submission.reply('Author: {}\n\ntweet: {}'.format(self.author, self.text))
			print('Submitted!')

class messageParser(object):

	def __init__(self, message):
		self.message = message
		self.author = str(message.author)
		self.subject = message.subject.lower()
		self.body = message.body
		self.action = self.messageType()
		self.__call__()

	def messageType(self):
		if self.subject == '!add':
			return True
		elif self.subject == '!remove':
			return False
		else:
			return None

	def __call__(self):
		if self.action == True:
			Database('twitter').entry('following',[re.sub('@','',self.body)])
			self.message.reply('*@{} has been succesfully added to the x-post list!*'.format(self.body))
			self.message.mark_read()
		else:
			pass


def main():
	Database('twitter').create_table('following',['handle'])
	Database('twitter').create_table('retweeted',['id','handle'])
	for handle in ['hughhowey','carlywatters','staceyigraham','KateBrauning','msmariavicente']:
		Database('twitter').entry('following',handle)
	while True:
		for message in reddit.inbox.unread(limit=3):
			messageParser(message)
		for handle in Database('twitter').query('following',fetchall=True):
			threading.Thread(target=twitterParser, kwargs={'handle':handle[0]}).start()
		threads = [thread for thread in threading.enumerate() if thread.is_alive()]
		while len(threads) > 1:
			threads = [thread for thread in threading.enumerate() if thread.is_alive()]
		while not q.empty():
			redditParser(q.get())

if __name__ == '__main__':
	os.system('chcp 65001')
	main()