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



def lambda_handler(event, context):
    # Create a Boto3 client for Lambda
    lambda_client = boto3.client('lambda')

    # Initialize Supabase client 
    # Change to paramenter store
    url: str = os.environ.get("SUPABASE_URL")
    key: str = os.environ.get("SUPABASE_KEY")
    
    supabase: Client = create_client(url, key)

    upload_response = supabase.table("sjreport_uploads").select("*").eq("processed","FALSE").limit(1).execute()

    print(f'Upload respose : {upload_response}')


    # 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)
                # 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("sjreport_uploads").update({"processed": True}).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  # Assuming the file name can be used as a URL or adjust as necessary
        insert_amount = 1000 # after 2000 the request to insert starts to time out.
        last_week_start_date = None  # Initialize variable to hold last week's start date
        print(f"Table Name : {table_name}")
        
        # 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():

                # Convert each row to a dictionary and add additional fields
                row_data = row.to_dict()
                modified_row_data = {}

                for col, value in row_data.items():
                    if isinstance(value, pd.Timestamp):
                        modified_row_data[col] = value.strftime('%Y-%m-%d')
                    elif pd.isna(value):
                        modified_row_data[col] = ""  
                    else:
                        modified_row_data[col] = value  # Keep original value for other
                # replace row_data with modified_row_data
                row_data = modified_row_data

                # # Convert Timestamp columns to string format
                # for col, value in row_data.items():
                #     if isinstance(value, pd.Timestamp):
                #         row_data[col] = value.strftime('%Y-%m-%d')
                    
                #     # Replace NaN values
                #     if pd.isna(value):
                #         row_data[col] = ""  
                
                # Calculate the week start date based on the date field
                try: # Attempt to parse the date     
        
                    date = datetime.strptime(row_data["date"], '%Y-%m-%d').date()
                    week_start_date = date + timedelta(days=(7 - date.weekday())) # Calculate the next Monday
                    last_week_start_date = week_start_date.strftime('%Y-%m-%d') # week start date to send to the create task function

                    row_data['row'] = index + 1  # Adding the row number (1-based index)
                    row_data['week_start_date'] = week_start_date.strftime('%Y-%m-%d')
                    row_data['file'] = url
                    row_data['sheetname'] = sheet_name            
                    
                    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, week_start_date, 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 if the date format is incorrect
                    print(f"Invalid date format")
                    
            # 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, week_start_date, file, sheetname').execute()        
                print(f"Inserted {len(data_to_insert)} records into the table.")


        #  update the record to set processed to true
        supabase.table("sjreport_uploads").update({"processed": True}).eq("id", record_id).execute()
        payload = {
                "week_start_date": last_week_start_date
            }
        response = lambda_client.invoke(
            FunctionName='datafy-CreateTasks-EWGo39rP5O56',  
            InvocationType='Event',                # Set to 'Event' for asynchronous invocation
            Payload=json.dumps(payload)                 # Send an empty JSON object as payload
        )
    else:
        return {
            'statusCode': 400,
            'body': json.dumps({'error': 'Wrong upload folder'})
        }
   
        
    
    
    
    
    
    

    return {
        'statusCode': 200,
        'body': json.dumps({'message': 'Data inserted successfully'})
    }