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): # 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) offset = 0 limit = 1000 sjreport_records = [] # print(f'event : {event}') # date = event['week_start_date'] # date = supabase.rpc('get_max_week_start_date').execute() date = "2024-12-23" print(f"date : {date}") # try: while True: sjreport = supabase.rpc("get_sjreport_data_pages",{ "target_date": date, "page_offset": offset, "limit_count": limit }).execute(); if sjreport.data is None: print("No data returned from sjreport.") break print(f"Number of records sj read : {len(sjreport.data)} + offset : {offset}") if len(sjreport.data) < limit: break # Exit if fewer records than limit are returned sjreport_records.extend(sjreport.data) offset += limit # Convert sjreport data to DataFrame sjreport_df = pd.DataFrame(sjreport_records) # Prepare to store the combined records insertData = [] # Iterate through sjreport and callsheet DataFrames to create the combined records for index, sjElement in sjreport_df.iterrows(): # Create the new combined object with NaN checks and .0 removal newRow = { 'chain': str(sjElement['channel']).replace('.0', '') if pd.notna(sjElement['channel']) else '', 'store_name': str(sjElement['store_name']).replace('.0', '') if pd.notna(sjElement['store_name']) else '', 'status': str(sjElement['status']).replace('.0', '') if pd.notna(sjElement['status']) else '', # 'region': str(sjElement['region']).replace('.0', '') if pd.notna(sjElement['region']) else '', # Uncomment if needed 'category': str(sjElement['category']).replace('.0', '') if pd.notna(sjElement['category']) else '', 'brand': str(sjElement['brand']).replace('.0', '') if pd.notna(sjElement['brand']) else '', 'product': str(sjElement['product']).replace('.0', '') if pd.notna(sjElement['product']) else '', 'variant': str(sjElement['variant']).replace('.0', '') if pd.notna(sjElement['variant']) else '', 'size': str(sjElement['size']).replace('.0', '') if pd.notna(sjElement['size']) else '', 'sjreport_id': sjElement['id'] if pd.notna(sjElement['id']) else None, # Assuming id can be None 'store_code': str(sjElement['store_code']).replace('.0', '') if pd.notna(sjElement['store_code']) and sjElement['store_code'] != '' else '', 'type': 'task', # This is a constant value 'name': str(sjElement['issue_for_feedback']).replace('.0', '') if pd.notna(sjElement['issue_for_feedback']) else '', 'store_soh': sjElement['store_soh'] if pd.notna(sjElement['store_soh']) else 0, # Assuming store_soh can be 0 'system_dros': sjElement['system_dros'] if pd.notna(sjElement['system_dros']) else 0, # Assuming system_dros can be 0 'scoring_system': str(sjElement['scoring_system']).replace('.0', '') if pd.notna(sjElement['scoring_system']) else '', 'last_sold': sjElement['last_sold'] if pd.notna(sjElement['last_sold']) else None, # Assuming last_sold can be None 'week_start_date': sjElement['week_start_date'] if pd.notna(sjElement['week_start_date']) else None, # Assuming this can be None 'product_id': str(sjElement['product_id']).replace('.0', '') if pd.notna(sjElement['product_id']) else None # Assuming product_id can be None } insertData.append(newRow) # Get distinct store and chain # distinct_stores = set() # for item in insertData: # distinct_stores.add((item['store_name'], item['chain'])) # # Convert the set of tuples to a list of dictionaries for Supabase # store_chain_data = [{'store_name': store, 'chain': chain} for store, chain in distinct_stores] # print(f"number of items on callsheet : {len(store_chain_data)}") # # Send data in chunks # response_map = {} # chunk_size=400 # for i in range(0, len(store_chain_data), chunk_size): # chunk = store_chain_data[i:i + chunk_size] # print(f"Sending chunk {i // chunk_size + 1} with {len(chunk)} items to Supabase...") # # Call the Supabase function with the current chunk # response = supabase.rpc('get_distinct_callsheet_data', { # 'store_chain_data': chunk # }).execute() # # Create a mapping for quick access # for resp in response.data: # key = (resp['matched_store_name'].strip(), resp['matched_chain'].strip()) # response_map[key] = resp # # Iterate over insertData and update with matched values # for item in insertData: # # Create a key for the current item # key = (item['store_name'].strip(), item['chain'].strip()) # # Check if the key exists in the response map # if key in response_map: # resp = response_map[key] # # Add new fields to the item # item['rep_full_name'] = resp['rep_full_name'] # item['app_user_mobile'] = resp['rep_cell_number'] # item['callsheet_id'] = resp['id'] batch_size = 1000 # batch size for insert # Iterate over the insert_data in chunks of batch_size for i in range(0, len(insertData), batch_size): batch = insertData[i:i + batch_size] # Get the current batch print(f'tasks : {batch}') # Perform the upsert operation supabase.table('task').upsert(batch, on_conflict='chain,store_name,product_id,week_start_date,name,sjreport_id').execute() print(f"Successfully inserted batch {i // batch_size + 1} with {len(batch)} records.") # print("Distinct Store and Chain length:", len(distinct_stores)) print(f"Number of combined records: {len(insertData)}") print(f"Number of records sjreport: {len(sjreport_records)}") # Update issues counts on store table supabase.rpc('update_store_issue_counts').execute() return { 'statusCode': 200, 'body': json.dumps({'message': 'Data inserted successfully'}) }