import pandas as pd
import os
import re
import json
import requests

def get_county_from_latlon(lat, lon):
    try:
        url = f"https://geo.fcc.gov/api/census/block/find?latitude={lat}&longitude={lon}&format=json"
        response = requests.get(url, timeout=5)
        response.raise_for_status()
        data = response.json()
        raw_county = data.get("County", {}).get("name")
        if raw_county:
            # Remove " County" if present
            return raw_county.replace(" County", "").strip()
        return None
    except Exception as e:
        print("Error calling FCC API:", e)
        return None





def lookup_market_info(county, market_data):
    """
    Looks up the market order, engineering vendor, and construction vendor
    based on the provided county name.
    """
    for entry in market_data:
        if county in entry.get("counties", []):
            return {
                "MO": entry.get("market_order"),
                "Engineering_Vendor": entry.get("engineering_vendor"),
                "Construction_Vendor": entry.get("construction_vendor")
            }
    return {}  # If county not found



def process_file(input_file, output_file, starting_wo, project_meta, market_data_path=None):
    try:
        # Determine absolute path to the market_orders.json file
        if market_data_path is None:
            market_data_path = os.path.join(os.path.dirname(__file__), "market_orders.json")

        with open(market_data_path) as f:
            market_data = json.load(f)

        ext = os.path.splitext(input_file)[1].lower()
        df = pd.read_csv(input_file) if ext == '.csv' else pd.read_excel(input_file)

        group_col = 'fdh_equipment_name'
        sum_col = 'number_of_households'
        grouped_data = df.groupby(group_col)[sum_col].sum().reset_index()
        # Set Distribution Area once from original df
        if "service_area" in df.columns and pd.notna(df["service_area"].iloc[0]):
            project_meta["Distribution_Area"] = df["service_area"].iloc[0]

        result = pd.DataFrame(columns=[
            "Project Template",
            "LLD WO Number",
            "MO #",
            "Round",
            "County",
            "Job Type",
            "HHP",
            "FDH",
            "Distribution Area",
            "Engineering Vendor",
            "Construction Vendor",
            "Comments"
        ])

        # Patch metadata if needed
        if not project_meta.get("MO") or not project_meta.get("County"):
            lat = df["lat"].iloc[0]
            lon = df["longitude"].iloc[0]
            county = get_county_from_latlon(lat, lon)

            if county:
                project_meta["County"] = county
                market_info = lookup_market_info(county, market_data)
                print("County:", county)
                print("Market info:", market_info)

                for k, v in market_info.items():
                    print("k", k)
                    print("v", v)
                    project_meta[k] = v  # ← direct assignment instead of setdefault

        match = re.match(r"(D-H[A-Z]{2}\d)(\d+)$", starting_wo)
        if not match:
            raise ValueError(f"Invalid LLD WO format: {starting_wo}")

        prefix = match.group(1)
        start_num = int(match.group(2))

        print("Full Prefixssssss: ", prefix)
        print("Next Suffix: ", start_num)


        for i, row in grouped_data.iterrows():
            full_wo = f"{prefix}{start_num + i}"
            result.at[i, "Project Template"] = "Work Order Project Template"
            result.at[i, "LLD WO Number"] = full_wo
            result.at[i, "MO #"] = project_meta.get("MO", "N/A")
            result.at[i, "Round"] = project_meta.get("Round", "0")
            result.at[i, "County"] = project_meta.get("County", "N/A")
            result.at[i, "Job Type"] = "Distribution"
            result.at[i, "HHP"] = int(row[sum_col])
            result.at[i, "FDH"] = full_wo.split("-")[1] if "-" in full_wo else full_wo
            result.at[i, "Distribution Area"] = project_meta.get("Distribution_Area", "N/A")
            result.at[i, "Engineering Vendor"] = project_meta.get("Engineering_Vendor", "N/A")
            result.at[i, "Construction Vendor"] = project_meta.get("Construction_Vendor", "N/A")
            result.at[i, "Comments"] = row[group_col]

        result.to_excel(output_file, index=False)
        print(f"Saved to {output_file}")


    except Exception as e:
        print("Error:", e)