import os
import pandas as pd
from backend.SearchEpc import SearchEpc
from dotenv import load_dotenv
from tqdm import tqdm

load_dotenv(dotenv_path="backend/.env")
EPC_AUTH_TOKEN = os.getenv("EPC_AUTH_TOKEN")


def app():
    """
    Simple script to pull the EPC data for the Cleethorpes Portfolio
    :return:
    """

    asset_list = pd.read_excel(
        "/Users/khalimconn-kowlessar/Documents/hestia/Customers/sfr/Cleethorpes Portoflio/Updated Tenancy Schedule "
        "Portfolio.xlsx",
    )
    asset_list["row_id"] = asset_list.index
    asset_list[" Street No."] = asset_list[" Street No."].astype(str)

    epc_data = []
    for _, property in tqdm(asset_list.iterrows(), total=len(asset_list)):

        if property[" Street No."] == "Ground Floor Commercial":
            continue
        uprn = property["Uprn"]
        if not pd.isnull(uprn):
            searcher = SearchEpc(
                address1="",
                postcode="",
                auth_token=EPC_AUTH_TOKEN,
                os_api_key="",
                uprn=int(uprn)
            )
            searcher.find_property(skip_os=True)
        else:

            if not pd.isnull(property[" Flat No."]) and property[" Flat No."] not in ["", " "]:
                address1 = property[" Flat No."].strip() + ", " + property[" Street No."].strip()
            else:
                address1 = property[" Street No."].strip()

            if address1 == "1a Mews House  30":
                address1 = "1a Rear of"
            searcher = SearchEpc(
                address1=address1,
                postcode=property[" Postcode"].strip(),
                auth_token=EPC_AUTH_TOKEN,
                os_api_key="",
                uprn=None,
            )
            searcher.get_epc()
            # Get the newest record on lodgement-date
            sorted_epcs = sorted(
                searcher.data["rows"], key=lambda x: x["lodgement-date"]
            )
            searcher.newest_epc = sorted_epcs[-1]

        if searcher.newest_epc is None:
            raise ValueError(f"No EPC found for UPRN: {uprn}")

        epc_data.append(
            {
                "row_id": property["row_id"],
                **searcher.newest_epc
            }
        )

    epc_df = pd.DataFrame(epc_data)

    # Merge on data
    asset_list_with_epc = asset_list.merge(
        epc_df[["row_id", "address", "current-energy-rating", "current-energy-efficiency", "lodgement-date"]],
        how="left",
        left_on="row_id",
        right_on="row_id",
    ).rename(
        columns={
            "address": "EPC Address",
            "current-energy-rating": "Current EPC Rating",
            "current-energy-efficiency": "Current SAP Score",
            "lodgement-date": "EPC Date"
        }
    )

    asset_list_with_epc.to_excel(
        "/Users/khalimconn-kowlessar/Documents/hestia/Customers/sfr/Cleethorpes Portoflio/Portfolio with EPCs.xlsx",
        index=False
    )

    epc_df.to_csv(
        "/Users/khalimconn-kowlessar/Documents/hestia/Customers/sfr/Cleethorpes Portoflio/epc_data.csv",
        index=False
    )
