import sqlite3
from concurrent.futures import ThreadPoolExecutor, as_completed


# Function for checking if record already exists (saves lotta time on updates)
def is_new_record(c, var_name) -> bool:
    c.execute('SELECT 1 FROM packages WHERE var_name = ?', (var_name,))
    return c.fetchone() is None


# Function for update or insert a record (for db_update)
def insert_records(c, records):
    c.executemany('''
        INSERT INTO packages (var_name, var_tags, var_deps, var_date, var_info, var_instr, var_thumbs, var_morphs, var_morphn, level, var_morpre, autoi, unp, fav, vam_fav, vam_hide )
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 0, 0, 0, 0, 0)
    ''', (records)
    )


# Extension of db_update
def update_db_deps(c): # pass cursor

    # Get all dependencies from packages
    c.execute('SELECT var_deps, level FROM packages')
    pkgdepslist = c.fetchall()

    # Store unique, no dupes
    unique_dependencies = set()
    for row in pkgdepslist:
        if row[0]:
            deps = row[0].split(', ')
            unique_dependencies.update(deps)
    
    # Insert unique deps in table dependencies
    c.executemany('''
        INSERT OR IGNORE INTO dependencies (var_dep) VALUES (?)
    ''', [(dep.strip(),) for dep in unique_dependencies])

    # stage 2 - dependencies availability

    # Reset all to 0
    c.execute('UPDATE dependencies SET level = 0')
    # Get lists
    c.execute('SELECT var_dep, level FROM dependencies')
    alldeps = c.fetchall()
    c.execute('SELECT var_name, level FROM packages')
    allpkgs= c.fetchall()

    p_dict = {}
    for pkg, level in allpkgs:
        p_dict[pkg] = level

    # Group versions by name
    pversion_dict = {}
    for pkg, level in allpkgs:
        name, version = pkg.rsplit('.',1)
        name = name+'.'
        version = int(version)
        # add new in package versions dictionary
        if name not in pversion_dict or version > pversion_dict[name][0]:
            pversion_dict[name] = (version, level)
    # Get key with highest version for each name; build dict with only max versions
    pmax_dict = { name: tup for name, tup in pversion_dict.items()}

    # Update levels of availability
    d_min_dict = {}
    for id, (dep, level) in enumerate(alldeps):
        dname, dversion = dep.rsplit('.',1)
        dname = dname+'.'
        if dversion == 'latest':
            if dname in pmax_dict:
                c.execute('UPDATE dependencies SET level = ? WHERE var_dep = ?', (pmax_dict[dname][1], dep))

        if dversion.startswith('min'):
            d_min_dict[dname] = (level, int(dversion[3:]))
            if dname in pmax_dict and d_min_dict[dname][1] <= pmax_dict[dname][1]:
                c.execute('UPDATE dependencies SET level = ? WHERE var_dep = ?', (pmax_dict[dname][1], dep))

        else:
            if dep in p_dict:
                c.execute('UPDATE dependencies SET level = ? WHERE var_dep = ?', (p_dict[dep], dep))

"""
# Extension of db_update for morphs
def update_db_morphs(c): # pass cursor
    # Connect to SQLite db
    # conn = sqlite3.connect(db_path)
    # c = conn.cursor()

    # Create dependencies table
    c.execute('''
        CREATE TABLE IF NOT EXISTS morphs (
            id TEXT UNIQUE,
            displayName TEXT,
            group TEXT,
            region TEXT,
            min INTEGER,
            max INTEGER,
            numDeltas INTEGER,
            isPoseControl BOOLEAN,
            nformulas INTEGER
        )
    ''')

    # Get all morphs from packages
    print("Updating morphs lists...")
    c.execute('SELECT id FROM morphs')
    pkgmorphslist = c.fetchall()

    # Store unique, no dupes
    unique_dependencies = set()
    for row in pkgdepslist:
        if row[0]:
            deps = row[0].split(', ')
            unique_dependencies.update(deps)
    
    # Insert unique deps in table dependencies
    c.executemany('''
        INSERT OR IGNORE INTO morphs (id) VALUES (?)
    ''', [(id.strip(),) for id in morphs])
"""

def db_update(db_path: str, packages: dict, autoinstall_set: set, favorites_set: set, reveal_missing, set_status, max_threads):
    from .var_functions import fetch_var_data
    with sqlite3.connect(db_path) as conn:
        c = conn.cursor()
        # Create table if not exists
        c.execute('''
            CREATE TABLE IF NOT EXISTS packages (
                var_name TEXT PRIMARY KEY,
                var_tags TEXT NOT NULL,
                var_deps TEXT NOT NULL,
                var_date DATE NOT NULL,
                var_info TEXT NOT NULL,
                var_instr TEXT NOT NULL,
                var_thumbs TEXT NOT NULL,
                var_morphs TEXT NOT NULL,
                var_morphn INT NOT NULL,
                var_morpre BOOLEAN NOT NULL,
                level INTEGER NOT NULL,
                autoi BOOLEAN NOT NULL,
                unp BOOLEAN NOT NULL,
                fav BOOLEAN NOT NULL,
                vam_fav BOOLEAN NOT NULL,
                vam_hide BOOLEAN NOT NULL
            )
        ''')

        # Create dependencies table
        c.execute('''
            CREATE TABLE IF NOT EXISTS dependencies (
                var_dep TEXT UNIQUE,
                level INTEGER
            )
        ''')

        c.execute('CREATE INDEX IF NOT EXISTS idx_packages_var_name ON packages(var_name)')
        c.execute('CREATE INDEX IF NOT EXISTS idx_dependencies_var_dep ON dependencies(var_dep)')


        if reveal_missing:
            # Reset package level, all zero (level 0 = missing package)
            c.execute('UPDATE packages SET level = 0')

        c.execute('SELECT var_name FROM packages')
        existent = {tup[0] for tup in c.fetchall() if (tup[0] + '.var') in packages}

        # Prepare package count
        total_packages = len(packages)
        set_status(f'Processing {total_packages} packages...')
        # Percent count
        percent_t = total_packages * 0.01
        percent = 0
        n = 0

        database = []; update = []
        success_count = 0
        error_count = 0
        futures = None
        # For multi threading, max threads from config.ini, or default 5
        with ThreadPoolExecutor(max_workers=max_threads) as executor:
            for filename, data in packages.items():
                n += 1
                if n >= (percent + 1) * percent_t:
                    percent += 1
                    set_status(f'Processing packages... {percent}%')
                filepath = data[0][0]
                level = data[1]
                var_name = filename.removesuffix('.var')

                # Verify if record exists
                if var_name not in existent:

                    # Avoid AddonPackages/dummyvars
                    if level == '1' and 'dummyvar' in filepath: continue

                    # Submit function to the thread pool executor for future processing
                    futures = {executor.submit(database.append, fetch_var_data(filepath, var_name, level))}
                    # Check complete processes if ok
                    # for future in as_completed(futures):
                    #     try:
                    #         result = future.result()
                    #         if result:
                    #             success_count += 1
                    #         else:
                    #             error_count += 1
                    #     except Exception as e:
                    #         print(f"Error in the future: {e}")
                    #         error_count += 1
                else:
                    update.append((level, var_name))
                # if futures:
                    # for future in as_completed(futures):

        # Every threads have finished here
        # print(f"Processing complete. Successes: {success_count}, Errors: {error_count}")
    
        # Update database
        set_status('Updating packages list...')
        insert_records(c, database)

        # Update dir level (1:vam 2:varbr 3:ext)
        if update:
            for level, var_name in update:
                c.execute('UPDATE packages SET level = ? WHERE var_name = ?', (level, var_name))

        # Update data from VarBrowser's autoinstall.txt (set to true )
        if autoinstall_set: # reset all installed & available to False
            c.execute('UPDATE packages SET autoi = 0 WHERE level = 1 or level = 2 ')
            for var_name in autoinstall_set: # set True from autoinstall.txt file
                c.execute('UPDATE packages SET autoi = ? WHERE var_name = ?', (1, var_name))

        # Update data from VarBrowser's favorites.txt (set to true )
        if favorites_set: # reset all favorites to False
            c.execute('UPDATE packages SET fav = 0')
            for var_name in favorites_set: # set True from favorites.txt file
                c.execute('UPDATE packages SET fav = ? WHERE var_name = ?', (1, var_name))

        # Update dependencies
        set_status("Updating dependencies lists...")
        update_db_deps(c)

        # Save changes
        # conn.commit()
        # and close (context handler does it)


def db_update_record(db_path, update_query, params):
    with sqlite3.connect(db_path) as conn:
        conn.cursor().execute('UPDATE ' + update_query, params)
        conn.commit()

def db_select_all(db_path, select_query, params='') -> list:
    with sqlite3.connect(db_path) as conn:
        c = conn.cursor()
        c.execute('SELECT ' + select_query, params)
        return c.fetchall()

def db_delete_record(db_path, delete_query, params):
    with sqlite3.connect(db_path) as conn:
        conn.cursor().execute('DELETE ' + delete_query, params)
        conn.commit()

