# 1. Import necessary libraries 
import requests
import pandas as pd
from sqlalchemy import create_engine

# 2. API and database credentials 
url = "https://api.apilayer.com/currency_data/change?latest"
params = {    "start_date": "2023-09-27",    "end_date": "2023-09-28",
    "base": "USD"
}
headers = {"apikey": "Give Your API key here"}

# API request
response = requests.get(url, params=params, headers=headers)# Check if the API request become successful
if response.status_code == 200:
    print("Connected")
    print("Successfully fetched the data")

    # Process the API response
    exchange_rate_data = response.json()
    quotes = exchange_rate_data['quotes']

    # 3. Prepare data for DataFrame
    data_list = []
    start_date = params['start_date']
    end_date = params['end_date']
    for currency, values in quotes.items():
        data_list.append({
            'Currency': currency,
            'Start_Rate': values['start_rate'],
            'End_Rate': values['end_rate'],
            'Change': values['change'],
            'Change_Pct': values['change_pct'],
            'Start_Date': start_date,
            'End_Date': end_date
        })

    # Create DataFrame
    df = pd.DataFrame(data_list)

    # 4. Normalize numeric columns
    numeric_cols = ['Start_Rate', 'End_Rate', 'Change', 'Change_Pct']
    df[numeric_cols] = (df[numeric_cols] - df[numeric_cols].min()) / (df[numeric_cols].max() - df[numeric_cols].min())

    df['Average_rate'] = (df['Start_Rate'] + df['End_Rate']) / 2

    # Display the normalized DataFrame  
    print(df)

    # 5. Database connection
    username = 'Your user name'
    password = 'your password'
    host = 'your host address'
    database_name = 'your db name'
    conn = f'postgresql://{username}:{password}@{host}/{database_name}'
    engine = create_engine(conn)

    # Load data into the PostgreSQL DB
    table_name = 'api_test'
    try:
        # Load the DataFrame data into the PostgreSQL DB
        df.to_sql(table_name, engine, if_exists='replace', index=False)
        print('Table created ')
    except Exception as e:
        print('Error loading data to database', e)
    finally:
        # Close the database connection
        engine.dispose()

else:
    print(f"Error: {response.status_code}")
    print(response.text)