#!/usr/bin/env python3
import pandas as pd
import numpy as np
import pathlib

# Load the cleaned CSV file
file_path = pathlib.Path('/home/user/bachelorarbeit_importer/data/', 'cleaned_st_neu_verlustausschluss.csv')
df = pd.read_csv(file_path)

# Select relevant columns for 2022 and drop rows with missing ek2022 or st2022
df_2022 = df[['name', 'ek2022', 'st2022']].dropna()

# Sort by Eigenkapital (ek2022) ascending
df_2022.sort_values('ek2022', inplace=True)

# Define 5 classes based on equity size (in EUR):
bins = [0, 500000, 2000000, 10000000, 50000000, np.inf]
labels = ['< 500K EK', '500K EK < x < 2M EK', '2M < 10M EK', '10M EK < 50M EK', '> 50M EK']
df_2022['Klasse'] = pd.cut(df_2022['ek2022'], bins=bins, labels=labels)

# Calculate percentage of companies with negative taxes per class
for klasse, group in df_2022.groupby('Klasse', observed=False):
    total_companies = len(group)
    negative_tax_companies = len(group[group['st2022'] < 0])
    percentage = (negative_tax_companies / total_companies * 100) if total_companies > 0 else 0
    print(f"Prozent Unternehmen mit negativen Steuern in {klasse}: {percentage:.2f}%")