import os
import shutil
import time
import datetime
import sqlite3
import threading

DB_NAME = 'my_data.db'

class FolderSynchronizer:
    def __init__(self, name, folder1, folder2, interval):
        self.name = name
        self.folder1 = folder1
        self.folder2 = folder2
        self.interval = interval
        self.local = threading.local()
        self.create_table()

    def get_connection(self):
            if not hasattr(self.local, 'conn'):
                self.local.conn = sqlite3.connect(DB_NAME)
            return self.local.conn

    def get_cursor(self):
        return self.get_connection().cursor()

    def create_table(self):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute(f'''
            CREATE TABLE IF NOT EXISTS {self.name} (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                rel_dir TEXT,
                file_name TEXT,
                modified1 REAL,
                exist1 INTEGER,
                modified2 REAL,
                exist2 INTEGER,
                UNIQUE(rel_dir, file_name)
            )
        ''')
        conn.commit()

    # def delete_job(self):
    #     conn = self.get_connection()
    #     cursor = conn.cursor()
    #     cursor.execute(f"DROP TABLE IF EXISTS {self.name}")
    #     conn.commit()
    #     print(f"Job '{self.name}' has been deleted.")

    def update_db(self, rel_dir, file_name, modified1, exist1, modified2, exist2):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute(f'''
            INSERT OR REPLACE INTO {self.name} 
            (rel_dir, file_name, modified1, exist1, modified2, exist2)
            VALUES (?, ?, ?, ?, ?, ?)
        ''', (rel_dir, file_name, modified1, exist1, modified2, exist2))
        conn.commit()

    def get_file_info(self, rel_dir, file_name):
        conn = self.get_connection()
        cursor = conn.cursor()
        cursor.execute(f'''
            SELECT * FROM {self.name}
            WHERE rel_dir = ? AND file_name = ?
        ''', (rel_dir, file_name))
        return cursor.fetchone()

    def sync_folders(self):
        def sync_files(src_folder, dest_folder):
            current_time = time.time()
            total_sync = 0
            count_sync = 0
            for root, _, files in os.walk(src_folder):
                for filename in files:
                    try:       
                        src_path = os.path.join(root, filename)
                        rel_dir = os.path.relpath(root, src_folder)
                        dest_path = os.path.join(dest_folder, rel_dir, filename)
                        total_sync += 1

                        src_modified = os.path.getmtime(src_path)
                        file_info = self.get_file_info(rel_dir, filename)

                        if not file_info:
                            # New file
                                os.makedirs(os.path.dirname(dest_path), exist_ok=True)
                                shutil.copy2(src_path, dest_path)
                                count_sync += 1
                                dest_modified = current_time
                                modified1, exist1, modified2, exist2 = src_modified, 1, dest_modified, 1
                        else:
                            # Existing file
                            if src_modified > file_info[3] or not file_info[4]:
                                shutil.copy2(src_path, dest_path)
                                count_sync += 1
                                dest_modified = current_time
                                modified1, exist1, modified2, exist2 = src_modified, 1, dest_modified, 1
                            else:
                                modified1, exist1, modified2, exist2 = src_modified, 1, file_info[5], file_info[6]
                    except:
                        pass
                    finally:
                        self.update_db(rel_dir, filename, modified1, exist1, modified2, exist2)

            return total_sync, count_sync

        def check_deleted_files():
            conn = self.get_connection()
            cursor = conn.cursor()
            current_time = time.time()
            count_sync = 0
            cursor.execute(f"SELECT rel_dir, file_name, modified1, exist1, modified2, exist2 FROM {self.name}")
            for row in cursor.fetchall():
                rel_dir, file_name, modified1, exist1, modified2, exist2 = row
                path1 = os.path.join(self.folder1, rel_dir, file_name)
                path2 = os.path.join(self.folder2, rel_dir, file_name)

                if not os.path.exists(path1) and not os.path.exists(path2):
                    count_sync += 1
                    cursor.execute(f"DELETE FROM {self.name} WHERE rel_dir = ? AND file_name = ?", (rel_dir, file_name))
                elif not os.path.exists(path1) and exist1:
                    modified1 = current_time
                    if modified1 > modified2:
                        count_sync += 1
                        os.remove(path2)
                        cursor.execute(f"DELETE FROM {self.name} WHERE rel_dir = ? AND file_name = ?", (rel_dir, file_name))
                    else:
                        self.update_db(rel_dir, file_name, modified1, 0, modified2, exist2)
                elif not os.path.exists(path2) and exist2:
                    modified2 = current_time
                    if modified2 > modified1:
                        count_sync += 1
                        os.remove(path1)
                        cursor.execute(f"DELETE FROM {self.name} WHERE rel_dir = ? AND file_name = ?", (rel_dir, file_name))
                    else:
                        self.update_db(rel_dir, file_name, modified1, exist1, modified2, 0)

            conn.commit()
            return count_sync

        total_sync1, count_sync1 = sync_files(self.folder1, self.folder2)
        total_sync2, count_sync2 = sync_files(self.folder2, self.folder1)
        count_sync3 = check_deleted_files()

        total_sync = max(total_sync1, total_sync2)
        count_sync = count_sync1 + count_sync2 + count_sync3

        # now = datetime.datetime.now()
        # formatted_datetime = now.strftime("[%Y-%m-%d %H:%M:%S]")
        
        # return f"[{self.name}] \'{self.folder1} <-> {self.folder2}\' Sync completed! (sync count: {count_sync}, total files: {total_sync})"
        return f"[{self.name}] Sync completed! (sync count: {count_sync}, total files: {total_sync})"