import pdfplumber
import pandas as pd
import gradio as gr

# Define function to extract data
def extract_data(pdf_file):
    data = []
    columns = ["Purchase Order No", "Date", "SI No", "Material Number", "Material Description", "HSN Code", "IGST", "Unit", "Quantity", "Dely Qty", "Dely Date", "Unit Rate", "Value"]

    # Example Purchase Order Details (Adjust accordingly or add dynamic extraction if possible)
    purchase_order_no = "PO12345"
    purchase_order_date = "04.11.2024"

    with pdfplumber.open(pdf_file) as pdf:
        for page in pdf.pages:
            text = page.extract_text().splitlines()
            for i, line in enumerate(text):
                parts = line.split()
                try:
                    si_no = int(parts[0])  # Extract SI No
                    # Check if the line follows the expected format for a row
                    if si_no % 10 == 0:  # Assuming SI numbers are in multiples of 10 as per sample
                        # Extract each field based on position and format
                        material_desc = " ".join(parts[1:3])  # Adjust indexing if necessary
                        material_number = parts[3] if "Material" in parts else "220736540000"  # Default if not found
                        hsn_code = "8310"  # Fixed as per example; can be extracted if available
                        igst = "18%"       # Fixed as per example; can be extracted if available
                        unit = parts[4]
                        quantity = int(parts[5])
                        dely_qty = int(parts[6])
                        dely_date = parts[7]
                        unit_rate = float(parts[8])
                        value = float(parts[9])

                        # Append extracted data in specified order
                        data.append([
                            purchase_order_no,
                            purchase_order_date,
                            si_no,
                            material_number,
                            material_desc,
                            hsn_code,
                            igst,
                            unit,
                            quantity,
                            dely_qty,
                            dely_date,
                            unit_rate,
                            value
                        ])
                except (ValueError, IndexError):
                    continue  # Skip lines that don't match the format

    # Convert to DataFrame with specified columns
    df = pd.DataFrame(data, columns=columns)
    excel_path = "/tmp/Extracted_Purchase_Order_Data.xlsx"
    df.to_excel(excel_path, index=False)
    return excel_path

# Set up Gradio interface
iface = gr.Interface(
    fn=extract_data,
    inputs=gr.File(label="Upload PDF"),
    outputs=gr.File(label="Download Excel"),
    title="PDF Data Extractor"
)

# Launch the app
iface.launch()