import json
import pandas as pd
from data import data_json

data = json.loads(data_json)

bank_statement_dfs = []
investment_statement_dfs = []
debts_dfs = []
user_info_dfs = []

for user_data in data:
    user_id = user_data["user_id"]

    bank_statement_data = user_data["bankStatement"]
    bank_transactions = bank_statement_data["transactions"]
    
    bank_data_with_user_info = {
        "UserId": user_id,
        "AccountHolder": bank_statement_data["accountHolder"],
        "Bank": bank_statement_data["bank"],
        "AccountNumber": bank_statement_data["accountNumber"],
        "StatementPeriod": bank_statement_data["statementPeriod"]
    }
    for transaction in bank_transactions:
        transaction.update(bank_data_with_user_info)
    
    bank_df = pd.DataFrame(bank_transactions)
    
    bank_statement_dfs.append(bank_df)

    investment_statement_data = user_data["investmentStatement"]
    investment_transactions = investment_statement_data["transactions"]
    
    investment_data_with_user_info = {
        "UserId": user_id,
        "AccountHolder": investment_statement_data["accountHolder"],
        "InvestmentFirm": investment_statement_data["investmentFirm"],
        "AccountNumber": investment_statement_data["accountNumber"],
        "StatementPeriod": investment_statement_data["statementPeriod"]
    }
    
    for transaction in investment_transactions:
        transaction.update(investment_data_with_user_info)
    
    investment_df = pd.DataFrame(investment_transactions)
       
    investment_statement_dfs.append(investment_df)

    debts = user_data["debts"]
    debts_df = pd.DataFrame(debts)
    debts_df["UserId"] = user_id
    debts_dfs.append(debts_df)
    
    user_info = {
        "UserId": user_id,
        "SavingsRate": user_data["savingsRate"],
        "RetirementAge": user_data["retirementAge"],
        "LifeExpectancy": user_data["lifeExpectancy"],
        "CurrentAge": user_data["currentAge"],
        "InvestmentReturns": user_data["investmentReturns"]
    }
    user_info_df = pd.DataFrame([user_info])
    user_info_dfs.append(user_info_df)
    
bank_statement_df = pd.concat(bank_statement_dfs, ignore_index=True)
investment_statement_df = pd.concat(investment_statement_dfs, ignore_index=True)
debts_df = pd.concat(debts_dfs, ignore_index=True)
user_info_df = pd.concat(user_info_dfs, ignore_index=True)