import pdfplumber
import pandas as pd
import tempfile

def format_material_description(description_series, si_no):
    # Placeholder for a formatting function; update with your logic
    return f"{description_series.iloc[0]} (SI No: {si_no})"

def extract_bhel_data(pdf_file):
    data = []
    columns = ["SI No", "Material Description", "Unit", "Quantity", "Dely Qty", "Dely Date", "Unit Rate", "Value", "Material Number", "HSN Code", "IGST"]
    start_si, end_si = 10, 1150

    with pdfplumber.open(pdf_file) as pdf:
        for page in pdf.pages:
            text = page.extract_text().splitlines()
            for line in text:
                parts = line.split()
                try:
                    si_no = int(parts[0])
                    if start_si <= si_no <= end_si:
                        material_desc = " ".join(parts[1:3])
                        unit = parts[3]
                        quantity = int(parts[4])
                        dely_qty = int(parts[5])
                        dely_date = parts[6]
                        unit_rate = float(parts[7])
                        value = float(parts[8])
                        material_number = parts[9] if len(parts) > 9 else ""
                        hsn_code = parts[10] if len(parts) > 10 else ""
                        igst = parts[11] if len(parts) > 11 else ""
                        data.append([si_no, material_desc, unit, quantity, dely_qty, dely_date, unit_rate, value, material_number, hsn_code, igst])
                except (ValueError, IndexError):
                    continue

    df = pd.DataFrame(data, columns=columns)

    # Correct the SI No column to follow increments of 10
    df['SI No'] = range(10, 10 + len(df) * 10, 10)

    # Reapply the Material Description formatting based on the corrected SI No
    df['Material Description'] = df['SI No'].apply(
        lambda si_no: format_material_description(df['Material Description'], si_no)
    )

    # Save to temporary file for download
    temp_file = tempfile.NamedTemporaryFile(delete=False, suffix=".xlsx")
    df.to_excel(temp_file.name, index=False)

    # Display the corrected data to the user
    import ace_tools as tools  # Replace with your preferred display method
    tools.display_dataframe_to_user(name="Corrected Data with Updated SI No", dataframe=df)

    return temp_file.name