import json
import pandas as pd
import traceback
# Function to convert square meters to acres
def sq_m_to_acres(sq_m):
    return sq_m * 0.000247105

# Load JSON data
with open('Downloads/full_gavl_polygons_obj.json') as f:
    data = json.load(f)
num = 0
# Function to create and save Excel sheets for each index
def create_index_sheet(index_name, data):
    records = []
    num = 0
    for polygonID, details in data.items():
        fieldID = details.get("fieldID", "")
        name = details.get("Name", "").strip()
        phone_number = details.get("PhoneNumber", "")
        area_sq_m = details.get("Area", 0)
        area_acres = sq_m_to_acres(area_sq_m)
        added_by_phone = details.get("AddedByPhoneNumber", "")
        
        row = {
            "polygonID": polygonID,
            "fieldID": fieldID,
            "Name": name,
            "PhoneNumber": phone_number,
            "Area (sq m)": area_sq_m,
            "Area (acres)": area_acres,
            "AddedByPhoneNumber": added_by_phone
        }
        if details.get("Health", None) is not None:
            num = num + 1
            print(num, polygonID)
            for date, health in details["Health"].items():
                try:
                    if int(date) > 20240501:
                        row[date] = health.get(index_name, "")
                except:
                    print(traceback.format_exc())
        
        records.append(row)
    
    df = pd.DataFrame(records)
    df.to_excel(f'{index_name}.xlsx', index=False)
    print(f"Excel file '{index_name}.xlsx' created successfully.")

# Create sheets for each health index
for index in ["NDRE", "NDVI", "NDWI", "RECL", "RSM", "RVI"]:
    create_index_sheet(index, data)