import pandas as pd
import json

def parse_excel_file(file_path):
    try:
        df = pd.read_excel(file_path, header=2)
    except Exception as e:
        raise ValueError(f"❌ خطا در خواندن فایل اکسل: {str(e)}")

    column_mapping = {
        "کد": ["کد فهرست بها", "کد"],
        "عنوان": ["عنوان", "شرح کالا", "نام کالا"],
        "تعداد": ["تعداد", "تعداد مورد نیاز"],
        "واحد": ["واحد", "واحد شمارش", "نوع واحد"]
    }

    resolved_columns = {}
    for key, options in column_mapping.items():
        found = next((col for col in options if col in df.columns), None)
        if not found:
            raise ValueError(f"❌ ستون «{key}» در فایل پیدا نشد.")
        resolved_columns[key] = found

    materials = []
    for _, row in df.iterrows():
        try:
            quantity = float(row[resolved_columns["تعداد"]])
            if pd.isna(quantity) or quantity <= 0:
                continue
        except:
            continue

        material = {
            "code": str(row[resolved_columns["کد"]]).strip(),
            "description": str(row[resolved_columns["عنوان"]]).strip(),
            "quantity": quantity,
            "unit": str(row[resolved_columns["واحد"]]).strip()
        }
        materials.append(material)

    return materials

def save_to_json(data, output_path="material_db.json"):
    try:
        with open(output_path, "w", encoding="utf-8") as f:
            json.dump(data, f, indent=2, ensure_ascii=False)
    except Exception as e:
        raise ValueError(f"❌ خطا در ذخیره فایل JSON: {str(e)}")