import requests
import mysql.connector
import logging
import os
from jproperties import Properties

root_path = os.path.dirname(__file__)


configs = Properties()
with open(root_path + '/app-config.properties', 'rb') as config_file:
    configs.load(config_file)


logger = logging.getLogger(__name__)
logging.basicConfig(filename='sync.log', encoding='utf-8', level=logging.DEBUG)

def fetch_data_from_api():
    url = "https://www.udemy.com/api-2.0/users/127950842/taught-profile-courses/?page=1&organizationCoursesOnly=false&fields[course]=id,what_you_will_learn_data,created,title,description,instructional_level_simple,content_info_short,locale,image_750x422,url,is_private,is_recently_published,headline,num_lectures,id,rating,badges,price_detail,course_has_labels&filter_hq_courses=true,-course_performance__revenue_30days&page_size=1000"
    response = requests.get(url)

    if response.status_code == 200:   
        api_data = response.json()
        extracted_data = []

        for course in api_data['results']:
            locale_title = course.get('locale', {}).get('title', '')
            
            chapters = course.get('what_you_will_learn_data', {}).get('items', [])
            chapters_string = '<br> '.join(chapters)
            
            tags_title = [tag.get('label', {}).get('title', '') for tag in course.get('course_has_labels', [])]
            tags_string = ', '.join(tags_title)
            
            difficulty_mapping = {
                'Beginner': 'Level_Beginner',
                'Intermediate': 'Level_Intermediate',
                'Expert': 'Level_Expert'
            }

            description = course.get('description').replace('<p><br></p>', '')
            difficulty_level = course.get('instructional_level_simple')
            mapped_difficulty_level = difficulty_mapping.get(difficulty_level, difficulty_level)

            # Modifica per ottenere il formato desiderato per la durata
            duration = course.get('content_info_short')
            if duration:
                duration = duration.replace(' ', '')  # Rimuovi gli spazi
                duration = duration[0] + duration[1:].replace('hours', 'h').replace('hour', 'h').replace('mins', 'm').replace('min', 'm')

            extracted_data.append({
                'name': course.get('title'), 
                'description': description,
                'difficult_level': mapped_difficulty_level,
                'chapters': chapters_string,
                'duration': duration,  # Utilizza la nuova durata formattata
                'language': locale_title,
                'image_name': course.get('image_750x422'),
                'udemy_url': 'https://www.udemy.com' + course.get('url'),
                'deleted': 0,  
                'is_new': course.get('is_recently_published'),
                'creation_date': course.get('created').replace('T', ' ').replace('Z', ''),
                'short_description': course.get('headline'),
                'num_chapters': course.get('num_lectures'),
                'tags': tags_string,  
                'udemyId': course.get('id'),
                'score': course.get('rating')
            })
        return extracted_data
    else:
        print("Failed to fetch data from API.")
        return None

def create_db_connection():
    try:
        connection = mysql.connector.connect(
            host=configs.get("DB_HOST").data,
            user=configs.get("DB_USER").data,
            password=configs.get("DB_PWD").data,
            database=configs.get("DB_SCHEMA").data
        )
        return connection
    except mysql.connector.Error as e:
        print("Error connecting to database:", e)
        return None


def set_all_as_deleted(connection): #Così facendo tutti i corsi di base sono a deleted=1, quando poi si farà la update, verrà settato a 0, se invece il corso è stato eliminato da udemy, resterà a 1
    try:
        cursor = connection.cursor()
        cursor.execute("UPDATE courses SET deleted=1")
        connection.commit()
        print("All courses marked as deleted.")
        cursor.execute("UPDATE tags SET deleted=1")
        connection.commit()
        print("All tags marked as deleted.")

    except mysql.connector.Error as e:
        print("Error marking courses as deleted:", e)

def insert_courses(connection, data):
    try:
        cursor = connection.cursor()
        for item in reversed(data): #Reversed perché l'api di udemy consente solamente la visualizzazione decrescente, in questo modo i più nuovi vengono messi sopra al database.
            cursor.execute("SELECT COUNT(*) FROM courses WHERE udemyId = %s", (item['udemyId'],))
            result = cursor.fetchone()
            count = result[0] if result else 0

            if count == 0:
                # Se non trova udemyId uguali:
                cursor.execute("""
                    INSERT INTO courses (name, description, difficult_level, chapters, duration, language, image_name, udemy_url, deleted, is_new, creation_date, short_description, num_chapters, tags, udemyId, score)
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
                """, (
                    item['name'], 
                    item['description'], 
                    item['difficult_level'], 
                    item['chapters'], 
                    item['duration'], 
                    item['language'], 
                    item['image_name'], 
                    item['udemy_url'], 
                    item['deleted'], 
                    item['is_new'], 
                    item['creation_date'], 
                    item['short_description'], 
                    item['num_chapters'], 
                    item['tags'], 
                    item['udemyId'],
                    item['score']
                ))
            else:
                # Se trova udemyId uguali:
                cursor.execute("""
                    UPDATE courses
                    SET name=%s, description=%s, difficult_level=%s, chapters=%s, duration=%s, language=%s, image_name=%s, deleted=%s, is_new=%s, creation_date=%s, short_description=%s, num_chapters=%s, tags=%s, score=%s, last_updated=NOW()
                    WHERE udemyId=%s
                """, (
                    item['name'], 
                    item['description'], 
                    item['difficult_level'], 
                    item['chapters'], 
                    item['duration'], 
                    item['language'], 
                    item['image_name'], 
                    item['deleted'], 
                    item['is_new'], 
                    item['creation_date'], 
                    item['short_description'], 
                    item['num_chapters'], 
                    item['tags'], 
                    item['score'],
                    item['udemyId']
                ))
        connection.commit()
        print("Data inserted/updated successfully.")

    except mysql.connector.Error as e:
        print("Error inserting/updating data:", e)

def insert_tags(connection, tags):
    try:
        cursor = connection.cursor()
        for tag in tags:
            if tag.strip():  # Controlla se il tag non è vuoto
                cursor.execute("SELECT COUNT(*) FROM tags WHERE description = %s", (tag,))
                result = cursor.fetchone()
                count = result[0] if result else 0

                if count == 0:
                    # Se il tag non esiste, inseriscilo
                    cursor.execute("INSERT INTO tags (description, deleted) VALUES (%s, 0)", (tag,))
                else:
                    # Se il tag esiste, aggiornalo
                    cursor.execute("UPDATE tags SET deleted=0 WHERE description = %s", (tag,))
        connection.commit()
        print("Tags inserted/updated successfully.")
    except mysql.connector.Error as e:
        print("Error inserting/updating tags:", e)
        
def course_tags(connection):
    try:
        cursor = connection.cursor()
        cursor.execute("SELECT id, tags FROM courses WHERE tags IS NOT NULL AND tags != ''")
        courses_with_tags = cursor.fetchall()

        for course_id, tags_string in courses_with_tags:
            tags = tags_string.split(', ')  # Dividi la stringa di tag in una lista
            
            for tag in tags:
                # Trova l'id del tag corrente
                cursor.execute("SELECT id FROM tags WHERE description = %s", (tag,))
                tag_id = cursor.fetchone()
                
                if tag_id:
                    tag_id = tag_id[0]
                    # Controlla se la relazione corrente esiste già
                    cursor.execute("SELECT COUNT(*) FROM courses_tags WHERE id_course = %s AND id_tag = %s", (course_id, tag_id))
                    result = cursor.fetchone()
                    count = result[0] if result else 0
                    
                    if count == 0:
                        # Se la relazione non esiste, inseriscila
                        cursor.execute("INSERT INTO courses_tags (id_course, id_tag) VALUES (%s, %s)", (course_id, tag_id))
        
        connection.commit()
        print("Course tags updated successfully.")
    except mysql.connector.Error as e:
        print("Error updating course tags:", e)

def main():
    api_data = fetch_data_from_api()
    if not api_data:
        return
    
    connection = create_db_connection()
    if not connection:
        return

    set_all_as_deleted(connection)
    insert_courses(connection, api_data)
    # Estrai i tag da api_data
    tags = set()
    for item in api_data:
        tags.update(item['tags'].split(', '))  # Dividi la stringa di tag e aggiungi ciascun tag all'insieme
    #Inserisci o aggiorna i tag nella tabella tags2
    insert_tags(connection, tags)
    course_tags(connection)

    connection.close()
    print("Database connection closed.")

if __name__ == "__main__":
    main()