import openai
import tweepy
import mysql.connector
from mysql.connector import errorcode

# OpenAI API KEY
openai.api_key = "INSERT-YOUR-API-KEY-HERE"

# Twitter Bearer Token
client = tweepy.Client(
    bearer_token='INSERT-YOUR-TWITTER-BEARER-TOKEN-HERE')

# Twitter search query
query = "#capitaledellacultura OR #capitalecultura OR (capitale cultura)"
tweets = tweepy.Paginator(client.search_recent_tweets, query=query,
                          tweet_fields=['created_at', 'id'], max_results=100).flatten(limit=1000)


try:
    cnx = mysql.connector.connect(user='YOUR-USER-HERE', password='YOUR-PASSWORD-HERE',
                                  host='127.0.0.1',
                                  database='tweetsopenai')
    cursor = cnx.cursor()
except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
    else:
        print(err)
else:
    for tweet in tweets:
        try:
            cursor.execute("SELECT * FROM tweets WHERE tweet_id = (%s);", (tweet.id,))
            if cursor.fetchone() is None:
                response = openai.Completion.create(
                model = "text-davinci-003",
                prompt = "Decide whether a Tweet's sentiment is positive, neutral, or negative.\n\nTweet: \"" + tweet.text + "\"",
                temperature = 0,
                max_tokens = 60,
                top_p = 1.0,
                frequency_penalty = 0.5,
                presence_penalty = 0.0
                )

                sql = "INSERT INTO tweets (tweet_id, tweet_created_at, tweet_text, tweet_sentiment) VALUES (%s, %s, %s, %s);"
                val = (tweet.id, tweet.created_at, tweet.text, response['choices'][0]['text'])
                cursor.execute(sql, val)
                cnx.commit()
                print("Insert tweet with id", tweet.id, "in database.")

            else:
                print("Tweet already in database.")

        except:
            print('Something went wrong.')

    cursor.close()
    cnx.close()