import os
import json
from supabase import create_client, Client
import boto3
import pandas as pd
from io import BytesIO
from datetime import datetime, timedelta

# Initialize AWS SSM client
ssm = boto3.client('ssm')
def get_parameter(name):
    """Get a parameter from AWS SSM Parameter Store"""
    response = ssm.get_parameter(
        Name=name,
        WithDecryption=True
    )
    return response['Parameter']['Value']

def lambda_handler(event, context):    

    # Initialize Supabase client 
    url: str = get_parameter('/supabase/url')
    key: str = get_parameter('/supabase/anon')
    
    supabase: Client = create_client(url, key)

    upload_response = supabase.table("promo_kaching_uploads").select("*").eq("processed","FALSE").limit(1).execute()
    # upload_response = supabase.table("promo_kaching_uploads").select("*").eq("processed","TRUE").eq("test","TRUE").limit(1).execute() # for testing only will run the testing file muliple times


    # Extracting details from the response
    if upload_response.data:
        # Assuming you want to process the first item in the data list
        record = upload_response.data[0]
        # Extract the ID of the record
        record_id = record['id']

        bucket_name = "public/" + record['payload']['bucket_name']  # Prepend "public/"
        file_name = record['payload']['file_name']
        table_name = record['payload']['path_tokens'][0]  # First element of path_tokens

        print(f'Bucket: {bucket_name}, File Name: {file_name}, Table Name: {table_name}')

        # Read a file from Supabase storage
    
        response = supabase.storage.from_(bucket_name).download(file_name)

        file_content = response

        # Load the file content into a Pandas DataFrame   
        if file_name.endswith('.xlsx'):
            try:
                sheets = pd.read_excel(BytesIO(file_content), sheet_name=None, dtype=str) # get file and always make columns string as some of the fields are converted incorectly
                
                # Change column names to lowercase for each sheet
                for sheet_name in sheets.keys():
                    sheets[sheet_name].columns = sheets[sheet_name].columns.str.lower().str.replace(' ', '_')
            except:
                    # Handle the error if the date format is incorrect
                    print(f"Invalid file")
                    supabase.table("promo_kaching_uploads").update({"processed": True,"error":"Unsupported file format"}).eq("id", record_id).execute()
                    return {
                        'statusCode': 400,
                        'body': json.dumps({'error': 'Unsupported file format'})
                    }
        else:
            return {
                'statusCode': 400,
                'body': json.dumps({'error': 'Unsupported file format'})
            }
        
        # Specify the data to add to a row   
        url = file_name
        insert_amount = 1000 # after 1000 the request to insert starts to time out.        
        
        # Iterate through each sheet and its rows
        for sheet_name, sheet_df  in sheets.items():
            print(f"Processing sheet: {sheet_name}")
            
            # Prepare data for insertion
            data_to_insert = []
            for index, row in sheet_df.iterrows():
                try:
                    # Convert each row to a dictionary and add additional fields
                    row_data = row.to_dict()
                    modified_row_data = {}

                    # Remove unwanted columns
                    row_data.pop('id', None)  # Remove 'id' if it exists
                    row_data.pop('created_at', None)  # Remove 'created_at' if it exists

                    for col, value in row_data.items():
                        if pd.isna(value):
                            modified_row_data[col] = ""  
                        else:
                            modified_row_data[col] = value  # Keep original value for other

                    modified_row_data['row'] = index + 1  # Adding the row number (1-based index)                    
                    modified_row_data['file'] = url
                    modified_row_data['sheetname'] = sheet_name       

                    # replace row_data with modified_row_data
                    row_data = modified_row_data
                    data_to_insert.append(row_data)                        
                    
                 
                    # Insert in batches 
                    if len(data_to_insert) == insert_amount:
                        supabase.table(table_name).upsert(data_to_insert, on_conflict='row,file, sheetname').execute()                
                        print(f"Inserted {len(data_to_insert)} records into the table.")
                        data_to_insert = []  # Reset the list for the next batch
                except:
                    # Handle the error when inserting data
                    print(f"Error inserting data")
                    #  update the record to set processed to true
                    supabase.table("sjreport_uploads").update({"processed": True,"error":"Error inserting data"}).eq("id", record_id).execute()
                    return {
                        'statusCode': 400,
                        'body': json.dumps({'error': 'Error inserting data'})
                    }


            # Insert any remaining records that didn't make a full batch
            if data_to_insert:
                supabase.table(table_name).upsert(data_to_insert, on_conflict='row, file, sheetname').execute()        
                print(f"Inserted {len(data_to_insert)} records into the table.")
                print(f"Data : {data_to_insert}")


        #  update the record to set processed to true
        supabase.table("promo_kaching_uploads").update({"processed": True,"error":''}).eq("id", record_id).execute()
        
    else:
        return {
            'statusCode': 400,
            'body': json.dumps({'error': 'Wrong upload folder'})
        }
    return {
        'statusCode': 200,
        'body': json.dumps({'message': 'Data inserted successfully'})
    }