"""
BSD 2-Clause License
Copyright (c) 2018, DataSnaek
All rights reserved.
Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:
* Redistributions of source code must retain the above copyright notice, this
  list of conditions and the following disclaimer.
* Redistributions in binary form must reproduce the above copyright notice,
  this list of conditions and the following disclaimer in the documentation
  and/or other materials provided with the distribution.
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE
FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY,
OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
"""

import requests, sys, time, argparse
import pymysql.cursors 
import csv
import datetime

from datetime import date

try:
    connection = pymysql.connect(host='db5001224558.hosting-data.io',
                                user='dbu1233491',
                                password='368h/xLLU?tN',                             
                                db='dbs1046878',
                                charset='utf8mb4'
                                )
    print("connect successful!!")
except ValueError:
    print("Echec de la connection au serveur !")

# List of simple to collect features
snippet_features = ["title",
                    "publishedAt",
                    "channelId",
                    "channelTitle",
                    "categoryId"]

# Any characters to exclude, generally these are things that become problematic in CSV files
unsafe_characters = ['\n', '"']

# Used to identify columns, currently hardcoded order
header = ["video_id"] + snippet_features + ["trending_date", "tags", "view_count", "likes", "dislikes",
                                            "comment_count", "thumbnail_link", "comments_disabled",
                                            "ratings_disabled", "description","duration"]


def setup(code_path):
    with open(code_path) as file:
        country_codes = [x.rstrip() for x in file]

    return country_codes
    ''' 
    Define the country with their country_codes
    '''


def prepare_feature(feature):
    # Removes any character from the unsafe characters list and surrounds the whole item in quotes
    for ch in unsafe_characters:
        feature = str(feature).replace(ch, "")
    return f'"{feature}"'

    """
    Prepare the features, with removing the unsafe characters 
    """


def api_request(page_token, country_code):
    # Builds the URL and requests the JSON from it
    request_url = f"https://www.googleapis.com/youtube/v3/videos?part=id,statistics,contentDetails,snippet{page_token}chart=mostPopular&regionCode={country_code}&maxResults=50&key={api_key}"
    request = requests.get(request_url)
    if request.status_code == 429:
        print("Temp-Banned due to excess requests, please wait and continue later")
        sys.exit()
    return request.json()
    """
    Request the Youtube API with a country code and return a json from the request 
    """


def get_tags(tags_list):
    # Takes a list of tags, prepares each tag and joins them into a string by the pipe character
    return prepare_feature("|".join(tags_list))
    """
        Prepare the differents tags and add them to a string separated with the pipe 
    """


def get_videos(items):
    lines = []
    for video in items:
        comments_disabled = False
        ratings_disabled = False
		
		
        # We can assume something is wrong with the video if it has no statistics, often this means it has been deleted
        # so we can just skip it
        if "statistics" not in video:
            continue

        #print(video)
        # A full explanation of all of these features can be found on the GitHub page for this project
        video_id = prepare_feature(video['id'])

        # Snippet and statistics are sub-dicts of video, containing the most useful info
        snippet = video['snippet']
        statistics = video['statistics']
        contentDetails = video['contentDetails']
        

        # This list contains all of the features in snippet that are 1 deep and require no special processing
        features = [prepare_feature(snippet.get(feature, "")) for feature in snippet_features]

        # The following are special case features which require unique processing, or are not within the snippet dict
        #description = snippet.get("description", "")
        thumbnail_link = snippet.get("thumbnails", dict()).get("standard", dict()).get("url", "")
        trending_date = time.strftime("%y.%d.%m")
        tags = get_tags(snippet.get("tags", ["[none]"]))
        view_count = statistics.get("viewCount", 0)
        duration = contentDetails.get('duration')

        # This may be unclear, essentially the way the API works is that if a video has comments or ratings disabled
        # then it has no feature for it, thus if they don't exist in the statistics dict we know they are disabled
        if 'likeCount' in statistics and 'dislikeCount' in statistics:
            likes = statistics['likeCount']
            dislikes = statistics['dislikeCount']
        else:
            ratings_disabled = True
            likes = 0
            dislikes = 0

        if 'commentCount' in statistics:
            comment_count = statistics['commentCount']
        else:
            comments_disabled = True
            comment_count = 0

        # Compiles all of the various bits of info into one consistently formatted line
        line = [video_id] + features + [prepare_feature(x) for x in [trending_date, tags, view_count, likes, dislikes,
                                                                       comment_count, thumbnail_link, comments_disabled,
                                                                       ratings_disabled,duration]]
        lines.append(line)
    return lines
    """
    Gets all the informations about trend videos, like description, view count and more
    """


def get_pages(country_code, next_page_token="&"):
    country_data = []

    # Because the API uses page tokens (which are literally just the same function of numbers everywhere) it is much
    # more inconvenient to iterate over pages, but that is what is done here.
    while next_page_token is not None:
        # A page of data i.e. a list of videos and all needed data
        video_data_page = api_request(next_page_token, country_code)
        #print(video_data_page)
        # Get the next page token and build a string which can be injected into the request with it, unless it's None,
        # then let the whole thing be None so that the loop ends after this cycle
        next_page_token = video_data_page.get("nextPageToken", None)
        next_page_token = f"&pageToken={next_page_token}&" if next_page_token is not None else next_page_token

        # Get all of the items as a list and let get_videos return the needed features
        items = video_data_page.get('items', [])
        country_data += get_videos(items)
        

    return country_data

def write_to_file(country_code, country_data):

    #print(f"Writing {country_code} data to file...")

    if not os.path.exists(output_dir):
        os.makedirs(output_dir)

    with open(f"{output_dir}/{time.strftime('%y.%d.%m')}_{country_code}_videos.csv", "w+", encoding='utf-8') as file:
        for row in country_data:
            file.write(f"{row}\n")

    """
    Printing a file with all the data
    """

def get_data():
    for country_code in country_codes:
        country_data = get_pages(country_code)
        #write_to_file(country_code, country_data)
        insert_data(country_code,country_data)
        
    """
    Return all the data for each country
    """

def dateTreatment(DateDebut, DateFin):
        delta = DateFin - DateDebut
        return delta

def insert_data(country_code,country_data): 
    i=0
    for country_dat in country_data:
        i+=1
        country_datas = country_dat
        with connection.cursor() as cursor:
            #ADD data in tags table
            sqlTags = "INSERT into TAG (tag_name) Values('"+country_datas[7].replace('\'','').replace('\"','')+"')"
            cursor.execute(sqlTags)
            sqltagID = "select tag_id from TAG where tag_name = '" + country_datas[7].replace('\'','').replace('\"','')+"'"
            cursor.execute(sqltagID)
            idTag = cursor.fetchone()
            #ADD data in channel table 
            sqlChannel = "INSERT ignore into CHANNEL Values('"+country_datas[3].replace('\'','').replace('\"','')+"','"+country_datas[4].replace('\'','').replace('\"','')+"','"+country_code+"')"
            #ADD data in Video table
            sqlVideo = "INSERT ignore into VIDEO (video_id,title_video,published_date,count_like,count_dislike,count_comment,category_id,trending_date,miniature_link,tag_id,channel_id,country,classement,duration,count_view) Values ('"+country_datas[0].replace('\'','').replace('\"','')+"','"+country_datas[1].replace('\'','').replace('\"','')+"','"+country_datas[2].replace('\'','').replace('\"','')+"','"+country_datas[9].replace('\'','').replace('\"','')+"','"+country_datas[10].replace('\'','').replace('\"','')+"','"+country_datas[11].replace('\'','').replace('\"','')+"','"+country_datas[5].replace('\'','').replace('\"','')+"','"+str(date.today())+"','"+country_datas[12].replace('\'','').replace('\"','')+"','"+str(idTag[0])+"','"+country_datas[3].replace('\'','').replace('\"','')+"','"+country_code+"','"+str(i)+"','"+country_datas[15].replace('\'','').replace('\"','')+"','"+country_datas[8].replace('\'','').replace('\"','')+"')"
            cursor.execute(sqlChannel)
            cursor.execute(sqlVideo)
    print("DATA ADDED")        
    
if __name__ == "__main__":

    parser = argparse.ArgumentParser()
    parser.add_argument('--key_path', help='Path to the file containing the api key, by default will use api_key.txt in the same directory', default="AIzaSyDJVSLUhxuwT-uQcUufMH8xVsTTtvVLQ98")
    parser.add_argument('--country_code_path', help='Path to the file containing the list of country codes to scrape, by default will use country_codes.txt in the same directory', default='country_codes.txt')
    parser.add_argument('--output_dir', help='Path to save the outputted files in', default='output/')

    args = parser.parse_args()
    api_key = args.key_path
    output_dir = args.output_dir
    country_codes = setup(args.country_code_path)
    try:
        get_data()
    finally:
        connection.commit()
        # Closez la connexion (Close connection).      
        connection.close()
        print("ALL DATA INSERTED")