import logging
import mysql.connector
import os
from google.cloud import storage
from PIL import Image

# Logging configuration
logging.basicConfig(format='%(levelname)s:%(message)s', level=logging.DEBUG)
bucket_name = 'mybucket'
table_id = 'shakespeare'

# Initialize Google Cloud Storage client
storage_client = storage.Client.from_service_account_json('/google-cloud/keyfile/service_account.json')

# Define folder for download
folder = '/google-cloud/download/{}'.format(table_id)
delimiter = '/'
bucket = storage_client.get_bucket(bucket_name)

# List all objects that satisfy the filter
blobs = bucket.list_blobs(prefix=table_id, delimiter=delimiter)

# Establish the database connection
connection = mysql.connector.connect(
    host="34.93.254.53",
    user="root",
    password="Mysql8!=100",
    database="indianmo_imc_new"
)

# Create a cursor object
cursor = connection.cursor()

# Execute a SQL query
query = """SELECT t1.* FROM `ldt_ffw_course_images_log` t1
           JOIN sdt_ffw_courses t2 ON course_id = lfcl_course_id
           WHERE lfcl_type = 2 AND lfcl_status = 1 AND course_status = 1 
           AND course_workshop_flag = 1 LIMIT 1"""
cursor.execute(query)

# Fetch all the records
records = cursor.fetchall()

# Get column names
column_names = [desc[0] for desc in cursor.description]

# Example: Suppose you want to print a specific column, 'lfcl_image_name'
column_to_print = 'lfcl_image_name'
column_index = column_names.index(column_to_print)

# Use a standard for loop to iterate over the records (list of tuples)
for record in records:
    image_name = record[column_index]
    
	# The "folder" where the files you want to download are
    logging.info('File download Started…. Wait for the job to complete.')

    # Create this folder locally if it does not exist
    if not os.path.exists(folder):
        os.makedirs(folder)

    # Iterating through the blobs and downloading files
    for blob in blobs:
        logging.info('Blobs: {}'.format(blob.name))
        destination_uri = '{}/{}'.format(folder, blob.name) 
        blob.download_to_filename(destination_uri)
        logging.info('Exported {} to {}'.format(blob.name, destination_uri))

# Define the folder path where your images are located
folder_path = "/home/lenovo/shankara/python/images/"
# Define the new size for resizing (width, height)
new_size = (651, 367)

# Define resized folder
resized_folder_path = os.path.join(folder_path, "resized")

# Create the resized folder if it doesn't exist
if not os.path.exists(resized_folder_path):
    os.makedirs(resized_folder_path)

# Loop through all files in the folder
for filename in os.listdir(folder_path):
    # Create the full file path
    file_path = os.path.join(folder_path, filename)

    # Check if the file is an image (you can extend the list with other formats)
    if filename.lower().endswith(('.png', '.jpg', '.jpeg', '.gif', '.bmp', '.webp')):
        # Open the image using PIL
        with Image.open(file_path) as img:

            # Get the dimensions of the image
            width, height = img.size

            # If either dimension is larger than 650, resize the image
            if width > 700 or height > 500:
                # Calculate the new size maintaining aspect ratio
                if width > height:
                    new_width = 650
                    new_height = int((650 / width) * height)
                else:
                    new_height = 450
                    new_width = int((650 / height) * width)

                # Resize the image
                img_resized = img.resize((new_width, new_height), Image.Resampling.LANCZOS)
                
                
                # Define the resized file path
                resized_file_path = os.path.join(resized_folder_path, filename)

                # Save the resized image with compression
                if filename.lower().endswith('.jpg') or filename.lower().endswith('.jpeg'):
                    # For JPEG images, use the quality parameter to compress
                    img_resized.save(resized_file_path, format='JPEG', quality=75)
                elif filename.lower().endswith('.png'):
                    # For PNG images, use the compress_level parameter
                    img_resized.save(resized_file_path, format='PNG', compress_level=7)
                else:
                    # For other formats, just save the image (no additional compression)
                    img_resized.save(resized_file_path)

                # Print a message indicating the image was resized
                print(f"Resized and saved {filename}")
            else:
                # Print a message indicating the image was NOT resized
                print(f"Image {filename} was NOT resized")

# Close the cursor and connection
cursor.close()
connection.close()