import mysql.connector
import phpserialize
import json, os, time
from collections import defaultdict
from operator import itemgetter
from importer.details import get_details_movies
from importer.macher import get_links_movie
from importer.importer import main_importer
from functions import movie_update_text, doble, has_dubbed, old_txt, update_date


def get_db_connection():
    """Create and return a new database connection."""
    try:
        return mysql.connector.connect(
            host="localhost",
            user="imoviee_new",
            password="A&jH*#![Af!p{y@v",
            database="imoviee_new",
            connection_timeout=60
        )
    except mysql.connector.Error as e:
        print(f"Failed to connect: {e}")
        return None


def search_and_decode_meta(meta_value):
    connection = get_db_connection()
    if not connection:
        return
    try:
        cursor = connection.cursor()
        query = "SELECT post_id FROM wp_postmeta WHERE meta_value = %s"
        cursor.execute(query, (meta_value,))
        result = cursor.fetchone()
        if result:
            post_id = result[0]
            print(f"Post ID: {post_id}")
            query_meta_keys = "SELECT meta_key, meta_value FROM wp_postmeta WHERE post_id = %s"
            cursor.execute(query_meta_keys, (post_id,))
            meta_data = cursor.fetchall()
            for meta_key, meta_value in meta_data:
                if meta_key in ['movies_dlbox', 'series_dlbox']:
                    try:
                        decoded_data = phpserialize.loads(meta_value.encode('utf-8'), decode_strings=True)
                        json_data = json.dumps(decoded_data, ensure_ascii=False, indent=4)
                        json_data = json.loads(json_data)
                        print(f"Meta Key: {meta_key}")
                    except Exception as e:
                        print(f"Failed to decode meta_value for {meta_key}: {e}")
        else:
            print("Not found")
    except mysql.connector.Error as e:
        print(f"Database error in search_and_decode_meta: {e}")
    finally:
        try:
            cursor.close()
            connection.close()
        except:
            pass


# ############################################
# os.system("python3.9 importer/ids.py")
# time.sleep(3)
# print("Ids")
# os.system("python3.9 importer/zarids.py")
# time.sleep(3)
# print("zarids")
# os.system("python3.9 linkupdater.py")
# time.sleep(3)
# print("link updater")
# #############################################


movies = []
serials = []
with open('linkss.txt', 'r', encoding='utf-8') as l:
    for line in l:
        if len(line.split(';')) == 4:
            link = line.split(';')[-1]
            quality = ''
            if '1080' in link:
                quality = '1080p'
            if '720' in link:
                quality = '720p'
            if '480' in link:
                quality = '480p'
            Dubbed = False
            if 'Dubbed' in link:
                Dubbed = True
            line = line + ';' + quality + ';' + str(Dubbed)
            movies.append([x for x in line.split(';')])
        if len(line.split(';')) == 7:
            link = line.split(';')[-1]
            quality = ''
            if '1080' in link:
                quality = '1080p'
            if '720' in link:
                quality = '720p'
            if '480' in link:
                quality = '480p'
            Dubbed = False
            if 'Dubbed' in link:
                Dubbed = True
            line = line + ';' + quality + ';' + str(Dubbed)
            serials.append([x for x in line.split(';')])

passed_ids = []
with open('links.txt', 'r', encoding='utf-8') as lines:
    for line in lines:
        passed_ids.append(str(line.split(';')[0]))

passed_ids = list(set(passed_ids))
print("len items : " + str(len(passed_ids)))

# For Series    سریال‌ها 
serials_collenction = defaultdict(list)
for id, name, episode, season, imdb, srt, link, quality, Dubbed in serials:
    serials_collenction[id].append([id, name, season, episode, link, quality, Dubbed, srt])

# Define quality_order before sorting
quality_order = {"1080p": 3, "720p": 2, "480p": 1}
for key in serials_collenction:
    serials_collenction[key].sort(key=lambda x: (int(x[2]), int(x[3]), -quality_order.get(x[5], 0)))
sorted_keys = sorted(serials_collenction.keys(), key=lambda x: int(x))
sorted_grouped_data = {key: serials_collenction[key] for key in sorted_keys}
for count, x in enumerate(sorted_grouped_data):
    print("#"*100)
    zarid = sorted_grouped_data[x][0][0]
    if str(zarid) in passed_ids:
        print(f"Exists Updating.... zarid: {zarid}")
        # خواندن links.txt یه بار
        with open('links.txt', 'r', encoding='utf-8') as lines:
            existing_links = set(line.strip().split('/')[-1] for line in lines)
        
        new_links = []
        for y in sorted_grouped_data[x]:
            go = True
            if '.mp4' in y[4] or '.vtt' in y[-1]:
                final_filename = y[4].strip().split('/')[-1] if '.mp4' in y[4] else y[-1].strip().split('/')[-1]
                if final_filename in existing_links:
                    go = False
            if go:
                new_links.append(f"{y[0].strip()};{y[1].strip()};{y[2].strip()};{y[3].strip()};{y[-1].strip()};{y[4].strip()}\n")
                with open('new.txt', 'a', encoding='utf-8') as add:
                    add.write(y[4].strip() + '\n')
                    add.write(y[-1].strip() + '\n')
        
        # نوشتن لینک‌های جدید یه جا
        if new_links:
            with open('links.txt', 'a', encoding='utf-8') as add:
                add.writelines(new_links)
        
        # پیدا کردن imdb_id و id_imovie
        with open('importer/zarids.txt', 'r', encoding='utf-8') as zlines:
            with open('importer/imovieids.txt', 'r', encoding='utf-8') as ilines:
                imdb_id, id_imovie = '', ''
                for z in zlines:
                    if zarid == z.split(';')[0]:
                        imdb_id = z.split(';')[1].strip()
                        ilines.seek(0)
                        for i in ilines:
                            if str(imdb_id).strip() == str(i.split(';')[1]).strip():
                                id_imovie = i.split(';')[0].strip()
                                break
                        break
            
        if len(id_imovie) > 1:
            max_retries = 3
            connection = get_db_connection()
            for attempt in range(max_retries):
                if not connection or not connection.is_connected():
                    if connection:
                        try:
                            connection.close()
                        except:
                            pass
                    connection = get_db_connection()
                    if not connection:
                        print(f"Failed to connect for zarid {zarid}, attempt {attempt + 1}")
                        time.sleep(2)
                        continue
                
                try:
                    data = get_details_movies(zarid, sorted_grouped_data[x][0][1], '')
                    if data:
                        if not connection.is_connected():
                            connection.close()
                            connection = get_db_connection()
                            if not connection:
                                raise mysql.connector.Error("Failed to reconnect")
                        movie_update_text(str(id_imovie), data['updates_line'])
                        
                        if not connection.is_connected():
                            connection.close()
                            connection = get_db_connection()
                            if not connection:
                                raise mysql.connector.Error("Failed to reconnect")
                        doble(str(id_imovie), data['doble'])
                        
                        if not connection.is_connected():
                            connection.close()
                            connection = get_db_connection()
                            if not connection:
                                raise mysql.connector.Error("Failed to reconnect")
                        has_dubbed(sorted_grouped_data[x], str(id_imovie))
                        
                        if not connection.is_connected():
                            connection.close()
                            connection = get_db_connection()
                            if not connection:
                                raise mysql.connector.Error("Failed to reconnect")
                        update_date(str(id_imovie).strip())
                        
                        if not connection.is_connected():
                            connection.close()
                            connection = get_db_connection()
                            if not connection:
                                raise mysql.connector.Error("Failed to reconnect")
                        get_links_movie(imdb_id.strip(), id_imovie)
                        
                        print(f"===> {id_imovie}")
                        print(imdb_id.strip(), id_imovie)
                        print("***** New Quality *****")
                    break
                except mysql.connector.Error as e:
                    print(f"DB error for zarid {zarid}, attempt {attempt + 1}: {e}")
                    if attempt < max_retries - 1:
                        print(f"Retrying for zarid {zarid}...")
                        time.sleep(2)
                    else:
                        print(f"Failed to update zarid {zarid} after {max_retries} attempts")
                finally:
                    if connection:
                        try:
                            connection.close()
                        except:
                            pass
    else:
        print(f'new zarid: {zarid}')
        data = get_details_movies(zarid, sorted_grouped_data[x][0][1], '')
        if data:
            added = main_importer(data, data['imdb'])
            if added:
                with open('links.txt', 'r', encoding='utf-8') as lines:
                    existing_links = lines.readlines()
                # Write all links for each episode and quality
                written_links = set()
                for y in sorted_grouped_data[x]:
                    link_str = f"{y[0].strip()};{y[1].strip()};{y[2].strip()};{y[3].strip()};{y[-1].strip()};{y[4].strip()}\n"
                    if link_str not in written_links and link_str not in existing_links:
                        with open('links.txt', 'a', encoding='utf-8') as add:
                            add.write(link_str)
                        with open('new.txt', 'a', encoding='utf-8') as add:
                            add.write(y[4].strip() + '\n')
                            add.write(y[-1].strip() + '\n')
                        written_links.add(link_str)

# For Movies    فیلم‌ها
movies_collenction = defaultdict(list)
for id, name, imdb, link, quality, Dubbed in movies:
    movies_collenction[imdb].append([id, name, link, quality, Dubbed])

# Define quality_order for movies
quality_order = {"1080p": 3, "720p": 2, "480p": 1}
for x in movies_collenction:
    # Sort movies by quality (1080p > 720p > 480p)
    movies_collenction[x].sort(key=lambda y: -quality_order.get(y[3], 0))
    if str(movies_collenction[x][0][0]) not in passed_ids:
        data = get_details_movies(movies_collenction[x][0][0], movies_collenction[x][0][1], '')
        if data:
            added = main_importer(data, data['imdb'])
            if added:
                with open('links.txt', 'r', encoding='utf-8') as lines:
                    existing_links = lines.readlines()
                # Write all links for the movie
                written_links = set()
                for l in movies_collenction[x]:
                    link_str = f"{l[0].strip()};{l[1].strip()};;{l[2].strip()}\n"
                    if link_str not in written_links and link_str not in existing_links:
                        with open('links.txt', 'a', encoding='utf-8') as add:
                            add.write(link_str)
                        with open('new.txt', 'a', encoding='utf-8') as add:
                            add.write(l[2].strip() + '\n')
                        written_links.add(link_str)
    else:
        print(f"Exists Updating.... zarid: {movies_collenction[x][0][0]}")
        with open('links.txt', 'r', encoding='utf-8') as lines:
            existing_links = lines.readlines()
        new_subtitle = ''
        # Re-sort the movie links by quality before updating
        movies_collenction[x].sort(key=lambda y: -quality_order.get(y[3], 0))
        for y in movies_collenction[x]:
            if '.vtt' in y[2]:
                new_subtitle = y[2].strip()
                break
        for y in movies_collenction[x]:
            if '.mp4' in y[2] or '.vtt' in y[2]:
                go = True
                final_filename = y[2].strip().split('/')[-1]
                for l in existing_links:
                    existing_filename = l.strip().split('/')[-1]
                    if final_filename == existing_filename:
                        go = False
                        break
                if go:
                    with open('new.txt', 'a', encoding='utf-8') as add:
                        add.write(y[2].strip() + '\n')
                    with open('links.txt', 'a', encoding='utf-8') as add:
                        add.write(f"{y[0].strip()};{y[1].strip()};;{y[2].strip()}\n")
        
        with open('importer/zarids.txt', 'r', encoding='utf-8') as zlines, open('importer/imovieids.txt', 'r', encoding='utf-8') as ilines:
            imdb_id, id_imovie = '', ''
            for z in zlines:
                if str(movies_collenction[x][0][0]) == z.split(';')[0]:
                    imdb_id = z.split(';')[1].strip()
                    for i in ilines:
                        if str(imdb_id).strip() == str(i.split(';')[1]).strip():
                            id_imovie = i.split(';')[0].strip()
            ilines.seek(0)  # ریست کردن فایل برای سریال بعدی
        if len(id_imovie) > 1:
            data = get_details_movies(movies_collenction[x][0][0], movies_collenction[x][0][1], '')
            if data:
                movie_update_text(str(id_imovie), data['updates_line'])
                doble(str(id_imovie), data['doble'])
                has_dubbed(movies_collenction[x], str(id_imovie))
                old_txt(str(id_imovie))
                update_date(str(id_imovie))
                get_links_movie(imdb_id.strip(), id_imovie)

# os.system("python3.9 upload-new.py")
# time.sleep(3)

# os.system("python3.9 vtt.py")
# time.sleep(3)