import pandas as pd

# 1. Load the datasets (Assuming you saved the Excel sheets as CSVs)
df_main = pd.read_csv(r'C:\Users\leeja\OneDrive\Desktop\datathon\Data - Main.csv', low_memory=False)
df_categories = pd.read_csv(r'C:\Users\leeja\OneDrive\Desktop\datathon\Others - Category Linkage.csv')
df_countries = pd.read_csv(r'C:\Users\leeja\OneDrive\Desktop\datathon\Others - Country Mapping.csv')
df_rates = pd.read_csv(r'C:\Users\leeja\OneDrive\Desktop\datathon\Others - Exchange Rate.csv')

# 2. Map Country and Category to the Main Data [cite: 12, 16]
# This links the raw codes to human-readable names
df_clean = df_main.merge(df_countries, left_on='Name', right_on='Code', how='left')
df_clean = df_clean.merge(df_categories, on='Category', how='left')

# 3. Identify Inflow vs. Outflow [cite: 82, 83]
# Positive amount = Inflow, Negative amount = Outflow
# First, convert Amount in USD to numeric, removing commas
df_clean['Amount in USD'] = df_clean['Amount in USD'].astype(str).str.replace(',', '').apply(pd.to_numeric, errors='coerce')

df_clean['Flow_Type'] = df_clean['Amount in doc. curr.'].apply(
    lambda x: 'Inflow' if x > 0 else 'Outflow'
)

# 4. Standardize all amounts to USD [cite: 78, 86]
# Use the absolute value for 'Amount in USD' so we can sum them easily in visuals
df_clean['Abs_Amount_USD'] = df_clean['Amount in USD'].abs()

# 5. Convert Posting Date to a proper datetime format [cite: 65, 66]
# 5. Convert Posting Date to a proper datetime format [cite: 65, 66]
df_clean['Pstng Date'] = pd.to_datetime(df_clean['Pstng Date'], errors='coerce')
df_clean = df_clean.dropna(subset=['Pstng Date'])
df_clean['Week'] = df_clean['Pstng Date'].dt.to_period('W').apply(lambda r: r.start_time)

# Verification Step: Check if the rates in Main Data match the Official Rate Table
# 1. Merge the official rates into your cleaned data
df_verify = df_clean.merge(df_rates[['Code', 'Rate (USD)']], 
                           left_on='Curr.', 
                           right_on='Code', 
                           suffixes=('', '_Official'))

# 2. Compare the rate used in the transaction vs the official rate
df_verify['Rate_Difference'] = df_verify['Rate (USD)'] - df_verify['Rate (USD)_Official']

# 3. Flag any discrepancies
fx_issues = df_verify[df_verify['Rate_Difference'].abs() > 0.01]
print(f"Found {len(fx_issues)} transactions with exchange rate discrepancies.")

# 6. Export cleaned data for Power BI
try:
    df_clean.to_csv('AstraZeneca_Cleaned_Data.csv', index=False)
    df_clean.to_excel('AstraZeneca_Cleaned_Data.xlsx', index=False)
    print("Phase 1 Complete: Cleaned data saved as CSV and Excel!")
    print(df_clean.head())
except PermissionError:
    print("\n[ERROR] Could not save the Excel file. Please close 'AstraZeneca_Cleaned_Data.xlsx' and try again.")
except Exception as e:
    print(f"\n[ERROR] An unexpected error occurred while saving: {e}")