import mysql.connector
import requests
import os
from PIL import Image
from io import BytesIO

# 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 """
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]
    
    image_url = os.path.join("https://prduplds.ffreedomapp.com/indianmo/financial_freedom_app/uploads/course_thumbnail_images/",image_name)

    # Define the folder where the image will be saved
    save_folder = "/home/lenovo/shankara/python/images/"

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

    # Extract the image name from the URL
    image_name = os.path.basename(image_url)

    # Define the full path to save the image
    image_path = os.path.join(save_folder, image_name)

    # Send a GET request to download the image
    response = requests.get(image_url, stream=True)

    # Check if the request was successful (status code 200)
    if response.status_code == 200:
        # Save the image to the specified path
        with open(image_path, 'wb') as file:
            for chunk in response.iter_content(1024):
                file.write(chunk)
        print(f"Image downloaded and saved as {image_path}")
    else:
        print(f"Failed to download image. Status code: {response.status_code}")

# 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=85)
                elif filename.lower().endswith('.png'):
                    # For PNG images, use the compress_level parameter
                    img_resized.save(resized_file_path, format='PNG', compress_level=9)
                else:
                    # For other formats, just save the image (no additional compression)
                    img_resized.save(resized_file_path,quality=85)

                # 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()