fillna.py

import pandas as pd
from sqlalchemy.orm import Session
from backend.database import SessionLocal, engine
from backend import models

# ✅ Path to your dataset (adjust if needed)
DATASET_PATH = "framingham.csv"

# ✅ Load the dataset
df = pd.read_csv(DATASET_PATH)

# ✅ Optional: Clean or fill missing values
df = df.fillna(0)

# ✅ If gender is 0/1, map to numeric properly
if 'male' in df.columns:  # if dataset uses 'male' column
    df['gender'] = df['male']
    df = df.drop(columns=['male'])
elif 'sex' in df.columns:  # or if dataset uses 'sex'
    df['gender'] = df['sex']
    df = df.drop(columns=['sex'])
elif 'gender' in df.columns:
    df['gender'] = df['gender'].map({'male': 1, 'female': 0}).fillna(0)

# ✅ Calculate BMI if not present
if 'BMI' not in df.columns and 'weight' in df.columns and 'height' in df.columns:
    df['bmi'] = df['weight'] / ((df['height'] / 100) ** 2)
elif 'BMI' in df.columns:
    df['bmi'] = df['BMI']

# ✅ Ensure required columns match your DB model
expected_cols = {
    'name', 'age', 'gender', 'education', 'currentSmoker', 'cigsPerDay',
    'bpMeds', 'prevalentStroke', 'prevalentHyp', 'diabetes',
    'totChol', 'sysBP', 'diaBP', 'heartRate', 'glucose',
    'height', 'weight', 'bmi', 'chd_prediction'
}

# Add placeholder values for missing ones
for col in expected_cols:
    if col not in df.columns:
        if col == "name":
            df[col] = [f"User_{i}" for i in range(len(df))]
        elif col == "chd_prediction":
            df[col] = df.get("TenYearCHD", 0)
        else:
            df[col] = 0

# ✅ Create DB session
db: Session = SessionLocal()

# ✅ Insert data row by row
for _, row in df.iterrows():
    survey = models.SurveyResponse(
        name=row['name'],
        age=int(row['age']),
        gender=int(row['gender']),
        education=int(row['education']),
        currentSmoker=int(row['currentSmoker']),
        cigsPerDay=float(row['cigsPerDay']),
        bpMeds=int(row['bpMeds']),
        prevalentStroke=int(row['prevalentStroke']),
        prevalentHyp=int(row['prevalentHyp']),
        diabetes=int(row['diabetes']),
        totChol=float(row['totChol']),
        sysBP=float(row['sysBP']),
        diaBP=float(row['diaBP']),
        heartRate=float(row['heartRate']),
        glucose=float(row['glucose']),
        height=float(row['height']),
        weight=float(row['weight']),
        bmi=float(row['bmi']),
        chd_prediction=int(row['chd_prediction'])
    )
    db.add(survey)

# ✅ Commit changes
db.commit()
db.close()

print(f"✅ Successfully inserted {len(df)} records into the MySQL database!")