import pandas as pd
from extensions import db
from models import PayrollComponent, Employee, EmployeePayroll, EmployeePayrollDetail

def import_salary_excel(file_path):
    df = pd.read_excel(file_path)

    for _, row in df.iterrows():
        emp = Employee.query.filter_by(employee_code=row['EmployeeCode']).first()
        if not emp:
            continue

        payroll = EmployeePayroll(
            employee_id=emp.id,
            month=row['Month'],
            year=row['Year'],
            gross_salary=row['GrossSalary'],
            total_deductions=row['Deductions'],
            net_salary=row['NetSalary']
        )
        db.session.add(payroll)
        db.session.flush()

        for comp_name in ['Basic', 'HRA', 'Conveyance', 'SpecialAllowance', 'PF', 'Tax']:
            component = PayrollComponent.query.filter_by(name=comp_name).first()
            if not component:
                continue
            amount = row.get(comp_name, 0)
            db.session.add(EmployeePayrollDetail(
                payroll_id=payroll.id,
                component_id=component.id,
                amount=amount
            ))

    db.session.commit()
    print("✅ Excel data imported successfully!")