import sqlite3
import os
import json
from django.conf import settings
from PIL import Image
from admin_base.functions import *
from django.core.management import call_command
from django.utils.translation import get_language
import cv2
from skimage.metrics import structural_similarity
import tempfile
from django.core.files.storage import FileSystemStorage

def update_database(table_name, column_names, db_path):
    """
    Updates the database schema by adding new columns to a table if they don't exist, 
    or creates the table with the specified columns if it doesn't exist.
    
    Parameters:
    table_name (str): Name of the table to be updated or created.
    column_names (list): List of column names to ensure in the table.
    """
    
    # Connect to the database
    db_connection = sqlite3.connect(db_path, check_same_thread=False)
    db_connector = db_connection.cursor()
    change_occurred = False
    
    try:
        # Get existing columns in the table
        db_connector.execute(f"PRAGMA table_info({table_name});")
        db_connection.commit()
        existing_columns = {row[1] for row in db_connector.fetchall()}
        
        # Define new columns
        new_columns = [col for col in column_names if col not in existing_columns]
        
        # Create table if it does not exist
        if not existing_columns:
            columns_definition = ", ".join([f"{col} TEXT" for col in column_names])

            list_count = len(column_names)
            defult_value = ", ".join([f"\"Lorem Ipsum\"" for col in range(list_count)])

            create_table_sql = f"""
            CREATE TABLE IF NOT EXISTS {table_name} ({columns_definition});
            """ 
            print(f"Created table {table_name}")
            db_connector.execute(create_table_sql)
            db_connection.commit()
            
            insert_defult_values = f"INSERT INTO {table_name} VALUES ({defult_value});"

            db_connector.execute(insert_defult_values)
            db_connection.commit()
            log(f"Table '{table_name}' and it's content has been created and gave initial values.")
            
            change_occurred = True
        
        else:
            # Add new columns to the existing table
            for column in new_columns:
                add_column_sql = f"""ALTER TABLE {table_name} ADD COLUMN {column} TEXT;"""
                db_connector.execute(add_column_sql)
                db_connection.commit()
                    
                # Construct the SQL update statement dynamically
                update_statements = []
                update_statements.append(f"{column} = CASE WHEN {column} IS NULL THEN 'Lorem Ipsum' ELSE {column} END")
                update_query = f"UPDATE {table_name} SET {', '.join(update_statements)};"

                # Execute the update query
                db_connector.execute(update_query)
                db_connection.commit()
                log(f"Column '{column}' added to table '{table_name}'.")
                
                change_occurred = True
    
    except sqlite3.Error as e:
        log(f"An error occurred: {e}")
        
    finally:
        if not change_occurred:
            log("Attempt to change, No change occurred")
            db_connector.close()
            
        else:
            db_connector.close()

def check_form_fields(table_name, language_code, remove=None):
    """
    Checks form fields from a JSON file and updates the corresponding database table with new fields.
    If an image field is found, saves a default image and updates the database with image name.
    
    Parameters:
    db_name (str): Name of the SQLite database.
    table_name (str): Name of the table to check and update.
    """
    db_path = f"cms/storage/content_{language_code}.sqlite3"
    
    if remove:
        if os.path.exists(db_path):
            os.remove(db_path)
        
        img_path = os.path.join(settings.WEBSITE_MEDIA_ROOT, table_name)
        
        if os.path.exists(img_path):
            for imgs in os.listdir(img_path):
                os.remove(os.path.join(settings.WEBSITE_MEDIA_ROOT, table_name ,imgs))
            
        return 'remove'
    
    file_directory= os.path.join(settings.BASE_DIR, f"cms/content_index/{get_language()}/{table_name}_content_index.json")
    
    with open(file_directory) as file:
        data = json.load(file)
    
    database_model_fields = []
    fields_proccesed = [] 
            
    for form_fields in data:
        for fields_unproccesed in data[form_fields]:
            # Single Field Values
            try:
                fields_unproccesed["title"]
                fields_proccesed.append(fields_unproccesed)

            # Mult-Value Fields
            except:
                for post_fields in fields_unproccesed:
                    for fields_modified in fields_unproccesed[post_fields]:
                        fields_proccesed.append(fields_modified)
    
    for content in fields_proccesed:
        
        if content["type"] == "image":
            # Get default image from CMS folder
            default_image = os.path.join(settings.CMS_MEDIA_ROOT, "defaults/default_image.png")
            
            # Get website save directory and folder name
            save_directory = os.path.join(settings.WEBSITE_MEDIA_ROOT, table_name)
            
            if not os.path.exists(save_directory):
                os.makedirs(save_directory)
            
            try: 
                initial_db_value = database.read(db_path, f"{table_name}", content['form_name'])
                initial_db_value = initial_db_value[0][0]
                
            except:
                database_model_fields.append(content["form_name"])
                
                update_database(f"{table_name}", database_model_fields, db_path)
                
                log("Field was not found in database so it has been updated")
                initial_db_value = database.read(db_path, f"{table_name}", content['form_name'])
                
                initial_db_value = initial_db_value[0][0]
            
            with Image.open(default_image) as img:

                # Construct the new file path
                new_image_path = os.path.join(save_directory, f"{content['form_name']}.png")
                
                os.makedirs(os.path.dirname(new_image_path), exist_ok=True)
                
                # Check if the file already exists
                if not os.path.exists(new_image_path):
                    # Save the image with the new name
                    img.save(new_image_path)
                    log(f"Image saved as {new_image_path}")
                    
                    if initial_db_value != content['form_name']:
                        database.update(db_path, f"{table_name}", content['form_name'], f"{content['form_name']}.png")
                        log("Updated Field Value in Database")
                else:                        
                    log(f"Attempt to write file. File {new_image_path} already exists. Skipping.")
        
        else:                       
            database_model_fields.append(content["form_name"])
    
    # Add, Update or Pass form Fields and Tables              
    update_database(f"{table_name}", database_model_fields, db_path)
   
def update_text_field(db_path, db_table_attr, field, new_value):
    """
    Handles updating text fields in the database.
    
    db_path (str): Database Path.
    field (str): Field where the edits will occur to.
    db_table_attr (str): The database table attribute.
    """

    database.update(db_path, db_table_attr, field, new_value)
    update_content_cache_index()
        

def preprocess_image(image):
    gray_image = cv2.cvtColor(image, cv2.COLOR_BGR2GRAY)
    blurred_image = cv2.GaussianBlur(gray_image, (5, 5), 0)
    resized_image = cv2.resize(blurred_image, (256, 256))
    return resized_image

def compare_images(local_image, uploaded_image):
    """
    Compare two images and calculate their similarity.
    CMS: used to compare form fields
    """
    fs = FileSystemStorage()
    with tempfile.NamedTemporaryFile(delete=False) as tmp_file:
        tmp_file.write(uploaded_image.read())  # Write the uploaded file to temporary storage
        tmp_file_path = tmp_file.name
        
    # Load the images
    image1 = cv2.imread(local_image)
    image2 = cv2.imread(tmp_file_path)  # Load the uploaded image

    # Get the shape of the images
    height1, width1, _ = image1.shape
    height2, width2, _ = image2.shape

    # # Resize the images to match each other's shape
    if height1 > height2 or width1 > width2:
        image1 = cv2.resize(image1, (width2, height2))
    else:
        image2 = cv2.resize(image2, (width1, height1))
        
    # Convert the images to grayscale for SSIM calculation
    gray1 = cv2.cvtColor(image1, cv2.COLOR_BGR2GRAY)
    gray2 = cv2.cvtColor(image2, cv2.COLOR_BGR2GRAY)

    # Calculate the Structural Similarity Index Measure (SSIM)
    ssim = structural_similarity(gray1, gray2)

    #return ssim
    return ssim