import dateutil.parser
yourdate = dateutil.parser.parse(datestring)

import youtube_dl

ydl = youtube_dl.YoutubeDL({'outtmpl': '%(id)s%(ext)s'})

with ydl:
    result = ydl.extract_info(
        'http://www.youtube.com/watch?v=BaW_jenozKc',
        download=False # We just want to extract the info
    )

if 'entries' in result:
    # Can be a playlist or a list of videos
    video = result['entries'][0]
else:
    # Just a video
    video = result

print(video)
video_url = video['url']
print(video_url)

from datetime import datetime
import dateutil.parser
import json
import os
import requests
import sqlite3
import youtube_dl

json_file_path = '/Users/jswang/Downloads/Takeout-2/YouTube/history/watch-history.json'
sqlite_file = '/Users/jswang/Desktop/youtube.db'
video_detail_cache_folder = '/Users/jswang/Desktop/cache/%s'
video_url = 'http://www.youtube.com/watch?v=%s'

conn = sqlite3.connect(sqlite_file)

def insert_channel(name, channel_id):  
  c = conn.cursor()
  c.execute("INSERT OR IGNORE INTO channel (channel_id, name) VALUES (?, ?)", (channel_id, name))

def insert_video(title, video_id, channel_id):
  c = conn.cursor()
  c.execute("INSERT OR IGNORE INTO video (video_id, channel_id, title) VALUES (?, ?, ?)", (video_id, channel_id, title))

def insert_history(timestamp, video_id):
  c = conn.cursor()
  c.execute("INSERT OR IGNORE INTO history (timestamp, video_id) VALUES (?, ?)", (timestamp, video_id))
  
def update_video_info(video_id, duration):
  c = conn.cursor()
  c.execute("UPDATE video SET duration=? WHERE video_id=?", (duration, video_id))

def populate_database():
  ydl = youtube_dl.YoutubeDL({'outtmpl': '%(id)s%(ext)s'})
  
  with open(json_file_path) as json_file:
    data = json.load(json_file)
    for history in data:
      if "subtitles" not in history or "titleUrl" not in history or not history["title"].startswith("Watched "):
        continue
      
      channel_id = ""
      for subtitle in history["subtitles"]:
        channel_id = subtitle["url"].replace("https://www.youtube.com/channel/", "")
        insert_channel(subtitle["name"], channel_id)
      
      title = history["title"].replace("Watched ", "")
      video_id = history["titleUrl"].replace("https://www.youtube.com/watch?v=", "")
      insert_video(title, video_id, channel_id)

      duration = get_video_duration(ydl, video_id)
      update_video_info(video_id, duration)

      timestamp = int(dateutil.parser.parse(history["time"]).timestamp())
      insert_history(timestamp, video_id)

def get_video_duration(ydl, video_id):
  file_path = video_detail_cache_folder % video_id
  url = video_url % video_id
  
  if not os.path.exists(file_path):
    try:
      result = ydl.extract_info(url, download=False)
    except:
      return -1
      
    with open(file_path, 'w') as f:
      f.write(json.dumps(result))

  with open(file_path) as json_file:
    data = json.load(json_file)
    return data["duration"]

populate_database()

conn.commit()
conn.close()

import matplotlib.pyplot as plt
import sqlite3

def plot_video_length():
  x = []
  y = []

  c = conn.cursor()
  c.execute("""
  SELECT 
    duration / 60, COUNT(1) 
    FROM video 
    WHERE duration > 0
    GROUP BY 1;
  """
  )

  rows = c.fetchall()

  for row in rows:
    x.append(row[0])
    y.append(row[1])

  plt.plot(x,y, marker='x')
  # plt.xticks(x)
  plt.xlim(0, 180)
  plt.xlabel('Length')
  plt.ylabel('Video Count')
  plt.title('YouTube Length Distribution')
  plt.legend()

def plot_yearly_count():
  x = []
  y = []

  c = conn.cursor()
  c.execute("""
  SELECT
    STRFTIME('%Y', DATE(timestamp, 'unixepoch')) AS year,
    COUNT(1)
  FROM
    history
  GROUP BY 1;
  """
  )
   
  rows = c.fetchall()

  for row in rows:
    x.append(row[0])
    y.append(row[1])

  plt.bar(x,y)
  plt.xticks(x)
  plt.xlabel('Year')
  plt.ylabel('Video')
  plt.title('Yearly Watched YouTube Videos')
  plt.legend()

def plot_watch_hour():
  x = []
  y = []

  c = conn.cursor()
  c.execute("""
  SELECT
    STRFTIME('%Y', DATETIME(timestamp, 'unixepoch', 'localtime')) AS year,
    STRFTIME('%H', DATETIME(timestamp, 'unixepoch', 'localtime')) AS hour,
    COUNT(1)
  FROM history GROUP BY 1, 2 HAVING year = '2019';
  """
  )
   
  rows = c.fetchall()

  for row in rows:
    x.append(int(row[1]))
    y.append(int(row[2]))

  plt.bar(x,y)
  plt.xlabel('Hour')
  plt.ylabel('Video')
  plt.title('Watch Hour (2019)')
  plt.legend()

sqlite_file = '/Users/jswang/Desktop/youtube.db'
conn = sqlite3.connect(sqlite_file)

plt.rcParams["figure.figsize"] = (12, 4)

plt.subplot(1, 2, 1)
plot_yearly_count()

plt.subplot(1, 2, 2)
plot_watch_hour()

plt.subplots_adjust(wspace=0.2)

# plt.show()
plt.savefig("/Users/jswang/Desktop/graphs.svg", format="svg")

plt.clf()

plot_video_length()
# plt.show()
plt.savefig("/Users/jswang/Desktop/video_length.svg", format="svg")

conn.commit()
conn.close()

import sqlite3

sqlite_file = '/Users/jswang/Desktop/youtube.db'

conn = sqlite3.connect(sqlite_file)

c = conn.cursor()
c.execute("""
SELECT "|" || ROW_NUMBER() OVER (ORDER BY c DESC) || "|" || "[" || name || "](https://www.youtube.com/channel/" || channel_id || ")|" || c || "|"
FROM (
  SELECT 
    channel.name, 
    channel.channel_id, 
    COUNT(1) AS c FROM history
  LEFT JOIN video ON
    history.video_id = video.video_id
  LEFT JOIN channel ON
    video.channel_id = channel.channel_id
  GROUP BY 1
  ORDER BY c DESC
  LIMIT 25
)
""")

rows = c.fetchall()

for row in rows:
  print(row)

conn.commit()
conn.close()

import sqlite3

sqlite_file = '/Users/jswang/Desktop/youtube.db'

conn = sqlite3.connect(sqlite_file)

c = conn.cursor()
c.execute("""
SELECT "|" || ROW_NUMBER() OVER (ORDER BY c DESC) || "|" || "[" || name || "](https://www.youtube.com/channel/" || channel_id || ")|" || printf("%.2f", c) || "|"
FROM (
  SELECT channel.name, channel.channel_id, SUM(video.duration) / 3600.0 AS c FROM history
  LEFT JOIN video on history.video_id = video.video_id
  LEFT JOIN channel on video.channel_id = channel.channel_id
  WHERE video.duration > 0
  GROUP BY 1, 2
  ORDER BY c DESC
  LIMIT 25
)
""")

rows = c.fetchall()

for row in rows:
  print(row)

conn.commit()
conn.close()