import pandas as pd
import json

file_path = "aisc-shapes-database-v16.0.xlsx"
output_path = "src/data/steelShapes.json"

def clean_value(val):
    if pd.isna(val):
        return 0
    return float(val)

try:
    print("Reading Excel file...")
    # Read the main sheet
    df = pd.read_excel(file_path, sheet_name="Database v16.0")
    
    # Filter for W-Shapes
    w_shapes_df = df[df['Type'] == 'W'].copy()
    
    # Select and rename columns
    # We need: designation, d, tw, bf, tf, type
    mapped_data = []
    
    for _, row in w_shapes_df.iterrows():
        shape = {
            "designation": row['AISC_Manual_Label'],
            "d": clean_value(row['d']),
            "tw": clean_value(row['tw']),
            "bf": clean_value(row['bf']),
            "tf": clean_value(row['tf']),
            "type": "W",
            # Add k_des (design k) if available, might be useful for detailed detailing later
            # "k_des": clean_value(row.get('kdes', 0)) 
        }
        mapped_data.append(shape)
        
    # Sort by depth (d) descending, then by weight (implied by designation usually)
    # Actually designations are usually sorted in the DB.
    
    full_json = {
        "W-Shapes": mapped_data
    }
    
    print(f"Found {len(mapped_data)} W-Shapes.")
    
    with open(output_path, 'w', encoding='utf-8') as f:
        json.dump(full_json, f, indent=4)
        
    print(f"Successfully wrote to {output_path}")

except Exception as e:
    print(f"Error: {e}")