import matplotlib.pyplot as plt
import boto3

# Read the data from SAP SuccessFactors and Google Form/Microsoft Form
sap_data = pd.read_csv('sap_data.csv')  # Replace 'sap_data.csv' with the actual filename or path
form_data = pd.read_csv('form_data.csv')  # Replace 'form_data.csv' with the actual filename or path

# Merge the SAP SuccessFactors data with the form data based on Employee ID/PersonID
merged_data = pd.merge(sap_data, form_data, on='Employee ID', how='left')

# Aggregate the data to calculate headcount metrics
headcount_by_location = merged_data.groupby('Location')['Employee ID'].count()
headcount_by_work_status = merged_data.groupby('Work Status')['Employee ID'].count()

# Save the aggregated data to a new DataFrame
aggregated_data = pd.DataFrame({'Location': headcount_by_location.index, 'Headcount': headcount_by_location.values})

# Save the aggregated data to AWS S3
s3_bucket = 'your-s3-bucket-name'
s3_key = 'path/to/save/aggregated_data.csv'  # Replace with your desired S3 path and filename
aggregated_data.to_csv(s3_key, index=False)

# Display the headcount dashboard visualizations
plt.figure(figsize=(10, 6))

# Plotting headcount by location
plt.subplot(1, 2, 1)
headcount_by_location.plot(kind='bar', color='blue')
plt.title('Headcount by Location')
plt.xlabel('Location')
plt.ylabel('Headcount')

# Plotting headcount by work status
plt.subplot(1, 2, 2)
headcount_by_work_status.plot(kind='pie', autopct='%1.1f%%', colors=['green', 'orange', 'red'])
plt.title('Headcount by Work Status')
plt.legend(loc='best')

# Save the figure locally
figure_path = 'path/to/save/headcount_dashboard.png'  # Replace with your desired local path and filename
plt.savefig(figure_path)

# Upload the figure to AWS S3
s3_client = boto3.client('s3')
s3_client.upload_file(figure_path, s3_bucket, 'path/to/save/headcount_dashboard.png')  # Replace with your desired S3 path and filename

# Display the S3 paths for the aggregated data and the dashboard figure
print('Aggregated data saved to S3:', f's3://{s3_bucket}/{s3_key}')
print('Dashboard figure saved to S3:', f's3://{s3_bucket}/path/to/save/headcount_dashboard.png')