import twitter
import mysql.connector
import json
import os

def collect_data_about(name, twitter_api, cursor):  
  MAX_ENTRY_PER_PERSON = 1000

  print(f'Quering twitter API for: {name}')
  search_results = twitter_api.search.tweets(q=name, count=100, lang='en')
  processStatusCounter = 0
  
  while True:
    try:
      statuses = search_results['statuses']
    except KeyError:
      return
    
    for status in statuses:
      tweet = status['text']      
      username = status['user']['screen_name']
      created_at = status['created_at']
      retweet_count = status['retweet_count']
      country = status['user']['location']
      tweet_id = status['id_str']

      query = '''
INSERT INTO tweets (character_id, `name`, tweet, tweet_id, username, created_at, retweet_count, country)
  VALUES (
    (SELECT id from characters WHERE %s = hero OR %s = alterego OR %s = actor),
    %s, %s, %s, %s, %s, %s, %s
  )
'''
      cursor.execute(query, (name, name, name, name, tweet, tweet_id, username, created_at, retweet_count, country))

      processStatusCounter += 1
      if processStatusCounter >= MAX_ENTRY_PER_PERSON:
        print(f'Received tweets for {name}: {processStatusCounter}')
        return
    
    try:
      next_results = search_results['search_metadata']['next_results']
    except KeyError:
      print(f'Received tweets for {name}: {processStatusCounter}')
      return

    kwargs = dict([ kv.split('=') for kv in next_results[1:].split("&") ])
    search_results = twitter_api.search.tweets(**kwargs)

def init_db(cursor):
  characters = [
    {
      'hero': 'Iron Man',
      'alterego': '<NAME>',
      'actor': '<NAME>r.'
    },
    {
      'hero': 'Hulk',
      'alterego': 'B<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': 'Spider-Man',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': 'Thor',
      'actor': '<NAME>'
    },
    {
      'hero': 'Loki',
      'actor': '<NAME>'
    },
    {
      'hero': 'Captain America',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': 'Deadpool',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': 'Winter Soldier',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': '<NAME>',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': '<NAME>',
      'alterego': "T'Challa",
      'actor': '<NAME>'
    },
    {
      'hero': 'Hawkeye',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': '<NAME>',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': 'Vision',
      'alterego': 'Jarvis',
      'actor': '<NAME>'
    },
    {
      'hero': 'Ant-Man',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': 'Thanos',
      'actor': '<NAME>'
    },
    {
      'hero': 'Star Lord',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': 'Groot',
      'actor': '<NAME>'
    },
    {
      'hero': 'Rocket Raccoon',
      'actor': '<NAME>'
    },
    {
      'hero': 'Gamora',
      'actor': '<NAME>dana'
    },
    {
      'hero': 'Nebula',
      'actor': '<NAME>'
    }
]

  cursor.execute('DROP TABLE IF EXISTS tweets')
  cursor.execute('DROP TABLE IF EXISTS characters')

  cursor.execute('''
CREATE TABLE characters (
  id INT AUTO_INCREMENT PRIMARY KEY,
  hero VARCHAR(255),
  alterego VARCHAR(255),
  actor VARCHAR(255)
)
  ''')

  for character in characters:
    query = 'INSERT INTO characters (hero, alterego, actor) VALUES (%s, %s, %s)'
    cursor.execute(query, (character.get('hero', ''), character.get('alterego', ''), character.get('actor', '')))

  cursor.execute('''
CREATE TABLE tweets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  character_id INT NOT NULL,
  name VARCHAR(255),
  tweet VARCHAR(255),
  tweet_id VARCHAR(255),
  username VARCHAR(255),
  created_at VARCHAR(255),
  retweet_count INT,
  country VARCHAR(255) CHARACTER SET utf8mb4,
  FOREIGN KEY (character_id) REFERENCES characters(id)
)
  ''')

def fill_db(twitter_api, cursor):
  cursor.execute('SELECT hero, alterego, actor FROM characters')
  rows = cursor.fetchall()
  for row in rows:
    for col in row:
      if col != '':
        collect_data_about(col, twitter_api, cursor)

def get_stats(cursor):
  stats = {}
  cursor.execute('''
SELECT characters.hero AS hero, COUNT(DISTINCT tweets.tweet_id)
  AS tweet_count FROM characters
  INNER JOIN tweets ON characters.id = tweets.character_id
  GROUP BY hero
''')
  rows = cursor.fetchall()
  for row in rows:
    stats[row[0]] = row[1]
  return stats

if __name__ == "__main__":
  marvelDB = mysql.connector.connect(
    host = "localhost",
    user = "marietamarvel",
    passwd = "<PASSWORD>",
    database = "marvelDB"
  )
  cursor = marvelDB.cursor()

  CONSUMER_KEY = '<KEY>'
  CONSUMER_SECRET = '<KEY>'
  OAUTH_TOKEN = '<PASSWORD>'
  OAUTH_TOKEN_SECRET = 'dddddddddddddd'
  twitter_auth = twitter.oauth.OAuth(
    OAUTH_TOKEN,
    OAUTH_TOKEN_SECRET,
    CONSUMER_KEY,
    CONSUMER_SECRET
  )
  twitter_api = twitter.Twitter(auth = twitter_auth)

  init_db(cursor)
  marvelDB.commit()

  fill_db(twitter_api, cursor)
  marvelDB.commit()

  stats = get_stats(cursor)

  print(f'Total unique tweets for each hero (and their alter ego or actor) in last week:')
  for name in stats:
    print(f'{name}: {stats[name]}')

import twitter
import mysql.connector
import json
import os

def collect_data_about(name, twitter_api, cursor):  
  MAX_ENTRY_PER_PERSON = 1000

  print(f'Quering twitter API for: {name}')
  search_results = twitter_api.search.tweets(q=name, count=100, lang='en')
  processStatusCounter = 0
  
  while True:
    try:
      statuses = search_results['statuses']
    except KeyError:
      return
    
    for status in statuses:
      tweet = status['text']      
      username = status['user']['screen_name']
      created_at = status['created_at']
      retweet_count = status['retweet_count']
      country = status['user']['location']
      tweet_id = status['id_str']

      query = '''
INSERT INTO tweets (character_id, `name`, tweet, tweet_id, username, created_at, retweet_count, country)
  VALUES (
    (SELECT id from characters WHERE %s = hero OR %s = alterego OR %s = actor),
    %s, %s, %s, %s, %s, %s, %s
  )
'''
      cursor.execute(query, (name, name, name, name, tweet, tweet_id, username, created_at, retweet_count, country))

      processStatusCounter += 1
      if processStatusCounter >= MAX_ENTRY_PER_PERSON:
        print(f'Received tweets for {name}: {processStatusCounter}')
        return
    
    try:
      next_results = search_results['search_metadata']['next_results']
    except KeyError:
      print(f'Received tweets for {name}: {processStatusCounter}')
      return

    kwargs = dict([ kv.split('=') for kv in next_results[1:].split("&") ])
    search_results = twitter_api.search.tweets(**kwargs)

def init_db(cursor):
  characters = [
    {
      'hero': 'Iron Man',
      'alterego': '<NAME>',
      'actor': '<NAME>r.'
    },
    {
      'hero': 'Hulk',
      'alterego': 'B<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': 'Spider-Man',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': 'Thor',
      'actor': '<NAME>'
    },
    {
      'hero': 'Loki',
      'actor': '<NAME>'
    },
    {
      'hero': 'Captain America',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': 'Deadpool',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': 'Winter Soldier',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': '<NAME>',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': '<NAME>',
      'alterego': "T'Challa",
      'actor': '<NAME>'
    },
    {
      'hero': 'Hawkeye',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': '<NAME>',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': 'Vision',
      'alterego': 'Jarvis',
      'actor': '<NAME>'
    },
    {
      'hero': 'Ant-Man',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': 'Thanos',
      'actor': '<NAME>'
    },
    {
      'hero': 'Star Lord',
      'alterego': '<NAME>',
      'actor': '<NAME>'
    },
    {
      'hero': 'Groot',
      'actor': '<NAME>'
    },
    {
      'hero': 'Rocket Raccoon',
      'actor': '<NAME>'
    },
    {
      'hero': 'Gamora',
      'actor': '<NAME>dana'
    },
    {
      'hero': 'Nebula',
      'actor': '<NAME>'
    }
]

  cursor.execute('DROP TABLE IF EXISTS tweets')
  cursor.execute('DROP TABLE IF EXISTS characters')

  cursor.execute('''
CREATE TABLE characters (
  id INT AUTO_INCREMENT PRIMARY KEY,
  hero VARCHAR(255),
  alterego VARCHAR(255),
  actor VARCHAR(255)
)
  ''')

  for character in characters:
    query = 'INSERT INTO characters (hero, alterego, actor) VALUES (%s, %s, %s)'
    cursor.execute(query, (character.get('hero', ''), character.get('alterego', ''), character.get('actor', '')))

  cursor.execute('''
CREATE TABLE tweets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  character_id INT NOT NULL,
  name VARCHAR(255),
  tweet VARCHAR(255),
  tweet_id VARCHAR(255),
  username VARCHAR(255),
  created_at VARCHAR(255),
  retweet_count INT,
  country VARCHAR(255) CHARACTER SET utf8mb4,
  FOREIGN KEY (character_id) REFERENCES characters(id)
)
  ''')

def fill_db(twitter_api, cursor):
  cursor.execute('SELECT hero, alterego, actor FROM characters')
  rows = cursor.fetchall()
  for row in rows:
    for col in row:
      if col != '':
        collect_data_about(col, twitter_api, cursor)

def get_stats(cursor):
  stats = {}
  cursor.execute('''
SELECT characters.hero AS hero, COUNT(DISTINCT tweets.tweet_id)
  AS tweet_count FROM characters
  INNER JOIN tweets ON characters.id = tweets.character_id
  GROUP BY hero
''')
  rows = cursor.fetchall()
  for row in rows:
    stats[row[0]] = row[1]
  return stats

if __name__ == "__main__":
  marvelDB = mysql.connector.connect(
    host = "localhost",
    user = "marietamarvel",
    passwd = "<PASSWORD>",
    database = "marvelDB"
  )
  cursor = marvelDB.cursor()

  CONSUMER_KEY = '<KEY>'
  CONSUMER_SECRET = '<KEY>'
  OAUTH_TOKEN = '<PASSWORD>'
  OAUTH_TOKEN_SECRET = 'dddddddddddddd'
  twitter_auth = twitter.oauth.OAuth(
    OAUTH_TOKEN,
    OAUTH_TOKEN_SECRET,
    CONSUMER_KEY,
    CONSUMER_SECRET
  )
  twitter_api = twitter.Twitter(auth = twitter_auth)

  init_db(cursor)
  marvelDB.commit()

  fill_db(twitter_api, cursor)
  marvelDB.commit()

  stats = get_stats(cursor)

  print(f'Total unique tweets for each hero (and their alter ego or actor) in last week:')
  for name in stats:
    print(f'{name}: {stats[name]}')

INSERT INTO tweets (character_id, `name`, tweet, tweet_id, username, created_at, retweet_count, country)   VALUES (     (SELECT id from characters WHERE %s = hero OR %s = alterego OR %s = actor),     %s, %s, %s, %s, %s, %s, %s   ) CREATE TABLE characters (   id INT AUTO_INCREMENT PRIMARY KEY,   hero VARCHAR(255),   alterego VARCHAR(255),   actor VARCHAR(255) ) CREATE TABLE tweets (   id INT AUTO_INCREMENT PRIMARY KEY,   character_id INT NOT NULL,   name VARCHAR(255),   tweet VARCHAR(255),   tweet_id VARCHAR(255),   username VARCHAR(255),   created_at VARCHAR(255),   retweet_count INT,   country VARCHAR(255) CHARACTER SET utf8mb4,   FOREIGN KEY (character_id) REFERENCES characters(id) ) SELECT characters.hero AS hero, COUNT(DISTINCT tweets.tweet_id)   AS tweet_count FROM characters   INNER JOIN tweets ON characters.id = tweets.character_id   GROUP BY hero