import re
from .details import get_details_movies
from .tt_scrapper import *
import pymysql
import urllib.parse
from .importer import main_importer

connection = pymysql.connect(
    host='127.0.0.1',
    user='sql_seo2024_ir',
    password='1bd0d20735dfd8',
    database='sql_seo2024_ir'
)

def update_post_title(connection, post_id, new_title):
    """Update the post_title for a given post ID."""
    query = """
        UPDATE wp_posts
        SET post_title = %s
        WHERE ID = %s
    """
    with connection.cursor() as cursor:
        cursor.execute(query, (new_title, post_id))
    connection.commit()

def update_post_meta(connection, post_id, meta_key, new_value):
    """
    Update a specific meta_key in wp_postmeta for a given post ID.
    """
    query = """
        UPDATE wp_postmeta
        SET meta_value = %s
        WHERE post_id = %s AND meta_key = %s
    """
    with connection.cursor() as cursor:
        cursor.execute(query, (new_value, post_id, meta_key))
    connection.commit()

def tag_adder(slug, kind, postID):
    with connection.cursor() as cursor:
        decode = urllib.parse.quote(slug)
        check_slug_query = "SELECT term_id FROM wp_terms WHERE slug = %s"
        cursor.execute(check_slug_query, (decode,))
        result = cursor.fetchone()
        if result:
            term_id = result[0]
            check_term_taxonomy_query = "SELECT term_taxonomy_id, count FROM wp_term_taxonomy WHERE term_id = %s"
            cursor.execute(check_term_taxonomy_query, (term_id,))
            taxonomy_result = cursor.fetchone()
            print(taxonomy_result)
            if taxonomy_result:
                term_taxonomy_id = taxonomy_result[0]
                current_count = taxonomy_result[1]
                new_count = current_count + 1
                update_count_query = "UPDATE wp_term_taxonomy SET count = %s WHERE term_taxonomy_id = %s"
                cursor.execute(update_count_query, (new_count, term_taxonomy_id))
                sql_insert_meta = """
                INSERT INTO wp_term_relationships (object_id, term_taxonomy_id)
                VALUES (%s, %s)
                """
                cursor.execute(sql_insert_meta, (str(postID), str(term_taxonomy_id)))
                connection.commit()

def update_post_meta_based_on_dlbox(post_id):
    
    try:
        with connection.cursor() as cursor:
            query = """
                SELECT meta_key, meta_value
                FROM wp_postmeta
                WHERE post_id = %s AND meta_key IN ('movies_dlbox', 'series_dlbox')
                ORDER BY FIELD(meta_key, 'movies_dlbox', 'series_dlbox')
                LIMIT 1
            """
            cursor.execute(query, (post_id,))
            result = cursor.fetchone()
            if not result:
                print(f"No 'movies_dlbox' or 'series_dlbox' found for post ID {post_id}")
                return
            
            meta_value = result[1]
            has_subtitle = 'on' if '.vtt' in meta_value else None
            has_dubbed = 'on' if 'Dubbed' in meta_value else None
            print(has_subtitle, has_dubbed)
                
            cursor.execute("""
                SELECT meta_id
                FROM wp_postmeta
                WHERE post_id = %s AND meta_key = 'has_subtitle'
            """, (post_id,))
            if cursor.fetchone():
                cursor.execute("""
                    UPDATE wp_postmeta
                    SET meta_value = %s
                    WHERE post_id = %s AND meta_key = 'has_subtitle'
                """, (has_subtitle, post_id))
            else:
                cursor.execute("""
                    INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
                    VALUES (%s, 'has_subtitle', %s)
                """, (post_id, has_subtitle))

            cursor.execute("""
                SELECT meta_id
                FROM wp_postmeta
                WHERE post_id = %s AND meta_key = 'has_dubbed'
            """, (post_id,))
            if cursor.fetchone():
                cursor.execute("""
                    UPDATE wp_postmeta
                    SET meta_value = %s
                    WHERE post_id = %s AND meta_key = 'has_dubbed'
                """, (has_dubbed, post_id))
            else:
                cursor.execute("""
                    INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
                    VALUES (%s, 'has_dubbed', %s)
                """, (post_id, has_dubbed))

            connection.commit()
            print(f"Post ID {post_id}: has_subtitle={has_subtitle}, has_dubbed={has_dubbed}")
    except Exception as e:
        print(f"Error: {e}")
    finally:
        connection.close()

def delete_line_by_id(file_path, id_to_delete):
    try:
        with open(file_path, 'r') as file:
            lines = file.readlines()
        updated_lines = [line for line in lines if id_to_delete not in line]
        with open(file_path, 'w') as file:
            file.writelines(updated_lines)
        print(f"Lines containing ID '{id_to_delete}' have been removed.")
    except FileNotFoundError:
        print(f"Error: File '{file_path}' not found.")
    except Exception as e:
        print(f"An error occurred: {e}")

def delete(connection, post_id):
    with connection.cursor() as cursor:
        queries = [
            "DELETE FROM wp_postmeta WHERE post_id = %s",
            "DELETE FROM wp_term_relationships WHERE object_id = %s",
            "DELETE FROM wp_posts WHERE ID = %s",
        ]
        try:
            for query in queries:
                cursor.execute(query, (post_id,))
            connection.commit()
            print(f"Successfully deleted all data related to post ID {post_id}")
            delete_line_by_id('passedids.txt', str(post_id))
        except pymysql.MySQLError as e:
            print(f"Error: {e}")
            connection.rollback()

def update(data, line):
    delete(connection, str(line.split(';')[0]))

def adder(id, name, link):  # ورودی‌ها از linkss.txt
    data = get_details_movies(id, name, link)  # id, name, link به details.py فرستاده می‌شه
    print(data)
    try:
        added = main_importer(data, data['imdb'])
        return added
    except Exception as e:
        print(e)
        pass

# خوندن از linkss.txt و اجرای ایمپورت
with open('linkss.txt', 'r', encoding='utf-8') as l:
    for line in l:
        parts = line.strip().split(';')
        if len(parts) >= 4:  # حداقل id, name, imdb, link
            id = parts[0]
            name = parts[1]
            link = parts[-1]  # لینک آخرین بخشه
            adder(id, name, link)