import os
import time

import pandas as pd
from tqdm import tqdm

from dotenv import load_dotenv
from utils.s3 import read_excel_from_s3
from backend.SearchEpc import SearchEpc
from epc_api.client import EpcClient
from utils.s3 import save_csv_to_s3

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


def app():
    """
    This app is satisying an adhoc request to retrieve EPC data for properties owned by Guiness, to help plan the
    route march

    These properties were provided to us by Ecosurv
    :return:
    """
    asset_list = read_excel_from_s3(
        bucket_name="retrofit-datalake-dev",
        file_key="customers/Places For People/PFP ROUTE MARCH PHASE 1.xlsx",
        header_row=1
    )

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

        lst = [
            pfp_property["ADDRESS"],
            pfp_property["ADDRESS.1"],
            # pfp_property["ADDRESS.2"],
            pfp_property["POSTCODE"]
        ]
        lst = [str(x).strip() for x in lst if not pd.isnull(x)]

        full_address = ", ".join(lst)

        searcher = SearchEpc(
            address1=str(pfp_property["ADDRESS"]),
            postcode=pfp_property["POSTCODE"],
            auth_token=EPC_AUTH_TOKEN,
            os_api_key="",
            property_type=None,
            fast=True,
            full_address=full_address
        )
        # Force the skipping of estimating the EPC
        searcher.ordnance_survey_client.property_type = None
        searcher.ordnance_survey_client.built_form = None

        searcher.find_property(skip_os=True)
        if searcher.newest_epc is None:
            # We try with a different address 1
            add1 = str(pfp_property["ADDRESS"]).lower()
            add1 = add1.replace("ft", "").replace("t", "").strip()

            searcher = SearchEpc(
                address1=add1,
                postcode=pfp_property["POSTCODE"],
                auth_token=EPC_AUTH_TOKEN,
                os_api_key="",
                property_type=None,
                fast=True,
                full_address=full_address
            )
            # Force the skipping of estimating the EPC
            searcher.ordnance_survey_client.property_type = None
            searcher.ordnance_survey_client.built_form = None

            searcher.find_property(skip_os=True)

            if searcher.newest_epc is None:
                continue

        epc = {
            "asset_list_address": pfp_property["ADDRESS"],
            "asset_list_address1": pfp_property["ADDRESS.1"],
            "asset_list_postcode": pfp_property["POSTCODE"],
            **searcher.newest_epc.copy()
        }

        epc_data.append(epc)

    epc_df = pd.DataFrame(epc_data)

    # 702

    # Retrieve just the data we need
    epc_df = epc_df[
        [
            "asset_list_address",
            "asset_list_address1",
            "asset_list_postcode",
            "uprn",
            "address",
            "property-type",
            "built-form",
            "inspection-date",
            "current-energy-rating",
            "current-energy-efficiency",
            "roof-description",
            "walls-description",
            "transaction-type"
        ]
    ].rename(columns={"address": "Matched EPC Address"})

    asset_list = asset_list.merge(
        epc_df,
        how="left",
        left_on=["ADDRESS", "ADDRESS.1", "POSTCODE"],
        right_on=["asset_list_address", "asset_list_address1", "asset_list_postcode"]
    )

    # De-dupe on the address and postcode, since 137 Badger Avenue was duplicated
    asset_list = asset_list.drop_duplicates(subset=["ADDRESS", "ADDRESS.1", "POSTCODE"])
    asset_list = asset_list.drop(columns=["asset_list_address", "asset_list_address1", "asset_list_postcode"])

    # Rename the columns
    asset_list = asset_list.rename(columns={
        "property-type": "Property Type",
        "built-form": "Archetype",
        "inspection-date": "Last EPC Inspection Date",
        "current-energy-rating": "Last survey EPC Rating",
        "current-energy-efficiency": "Last survey SAP Score",
        "roof-description": "Roof Construction",
        "walls-description": "Wall Construction",
        "transaction-type": "Last EPC Reason"
    })

    # Store as an excel
    filename = "Places For People EPC data.xlsx"
    asset_list.to_excel(filename, index=False)


# TODO: TEMP
# This script takes in a a list of properties
# Will be postcode and address

import requests
import numpy as np
import pandas as pd
from bs4 import BeautifulSoup
from tqdm import tqdm

SEARCH_POSTCODE_URL = ("https://find-energy-certificate.service.gov.uk/find-a-certificate/search-by-postcode?postcode"
                       "={postcode_input}")
BASE_ENERGY_URL = "https://find-energy-certificate.service.gov.uk"


def retrieve_find_my_epc_data(postcode: str, address: str):
    """
    For a post code and address, we pull out all the required data from the find my epc website
    """

    headers = {
        'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) '
                      'Chrome/111.0.0.0 Safari/537.36'}
    postcode_input = postcode.replace(" ", "+")
    postcode_search = SEARCH_POSTCODE_URL.format(postcode_input=postcode_input)
    postcode_response = requests.get(postcode_search, headers=headers)

    postcode_res = BeautifulSoup(postcode_response.text)
    address_links_full = postcode_res.findAll('a', {'class': 'govuk-link', 'rel': 'nofollow'})
    address_links = {element.text.lstrip().rstrip(): BASE_ENERGY_URL + element['href'] for element in
                     address_links_full}

    # TODO: to check the logic works for all cases but seems to be good
    index_of_address = [key.lower().startswith(address) for key in list(address_links.keys())]
    chosen_epc = address_links[list(address_links.keys())[np.where(index_of_address)[0][0]]]

    epc_certificate = chosen_epc.split('/')[-1]

    address_response = requests.get(chosen_epc, headers=headers)
    address_res = BeautifulSoup(address_response.text)

    # print("## Energy rating - current and potential")
    ratings = address_res.find('desc', {'id': 'svg-desc'}).text

    # print('### Current EPC rating')
    current_rating = ratings.split(".")[0]
    # print("##### " + current_rating)

    # print('### Potential EPC rating')
    potential_rating = ratings.split(".")[1]
    # print("##### " + potential_rating)

    new_property_df = pd.DataFrame(
        {'address': [address],
         'epc_certificate': [epc_certificate],
         'current_epc_rating': [current_rating.split(' ')[-6]],
         'current_epc_efficiency': [current_rating.split(' ')[-1]],
         'potential_epc_rating': [potential_rating.split(' ')[-6]],
         "potential_epc_efficiency": [potential_rating.split(' ')[-1]]}
    )

    # print("Find assessor")
    assessor_block = address_res.find('div', {'class': 'epc-contact-assessor'})
    assessor_fields = assessor_block.find_all('dd', {"class": 'govuk-summary-list__value govuk-!-width-one-half'})
    assessor_name = assessor_fields[0].text.strip()
    assessor_number = assessor_fields[1].text.strip()
    assessor_email = assessor_fields[2].text.strip()

    new_property_df['assessor_name'] = assessor_name
    new_property_df['assessor_number'] = assessor_number
    new_property_df['assessor_email'] = assessor_email

    return new_property_df

    # print('### Changes that can be made:')
    # improvements = address_res.find('div', {"class": "govuk-body printable-area epb-recommended-improvements"})

    # if improvements is None:
    #     print("No changes suggested")
    # else:
    #     changes = improvements.find_all('h3')
    #     changes_impact = improvements.find_all('dl', {"class": 'govuk-summary-list'})

    #     for element in zip(changes, changes_impact):
    #         improvement_header = element[0].text
    #         print("#### " + improvement_header)

    #         improvement_text = element[1].text
    #         print(improvement_text)

    #         col_name = improvement_header.split(":")[1]
    #         cost = element[1].find('dd', {"class": "govuk-summary-list__value"}).text.lstrip().rstrip()

    #         impact = element[1].find('text', {"class": "govuk-!-font-weight-bold"}).text.split(" ")
    #         impact_num = impact[0]
    #         impact_cat = impact[1]
    #         print(cost)
    #         new_property_df[col_name] = True
    #         # cost_column = col_name + '-cost'
    #         # new_property_df.assign(cost_column=cost)
    #         new_property_df[col_name + '-cost'] = cost
    #         new_property_df[col_name + '-impact_num'] = impact_num
    #         new_property_df[col_name + '-impact_cat'] = impact_cat

    #     data = pd.concat([data, new_property_df])
    #     data.to_csv('./portfolio.csv')


def main():
    """
    Main pipeline function to take in a predefined list of properties and extract names of contractors
    """

    # Load in list of properties
    addresses_df = pd.read_excel("/Users/khalimconn-kowlessar/Downloads/Places For People EPC data.xlsx")
    addresses_df["uprn"] = addresses_df["uprn"].astype("Int64").astype(str)
    # 1256

    find_my_epc_data_list = []
    for i, row in tqdm(addresses_df.iterrows(), total=addresses_df.shape[0]):

        if pd.isnull(row['Matched EPC Address']):
            continue
        # 10 second break every 50 iterations
        if (i % 50 == 0) and (i != 0):
            time.sleep(10)
        time.sleep(1)
        if row['Matched EPC Address'] == "6 CHURCHWOOD, CHURCH STREET, CRAMLINGTON":
            address_data = retrieve_find_my_epc_data(
                postcode=row['POSTCODE'],
                address=" ".join([str(row["ADDRESS"]), row["ADDRESS.1"]]).lower()
            )
        else:
            address_data = retrieve_find_my_epc_data(
                postcode=row['POSTCODE'],
                address=", ".join(row['Matched EPC Address'].split(", ")[:-1]).lower()
            )

        address_data.insert(0, "uprn", row["uprn"])

        find_my_epc_data_list.append(address_data)

    find_my_epc_data = pd.concat(find_my_epc_data_list)

    find_my_epc_data.to_csv('find_my_epc_data.csv')

    find_my_epc_data = find_my_epc_data.drop_duplicates("uprn")

    # Match back to addresses
    addresses_df2 = addresses_df.merge(
        find_my_epc_data,
        how="left",
        on="uprn"
    )

    addresses_df2.to_excel("Places For People EPC data with surveyor.xlsx", index=False)

    # Read in
    df = pd.read_excel("Places For People EPC data with surveyor.xlsx")
    df = df[
        df["assessor_name"].isin(
            [
                "Arsalan Khalid", "Kieran Bradnock", "Wayne Davies", "Lindsay Sands", "Bruce Nethercot",
                "Christopher Hearn", "Robert Sigerson", "Daniel Riddle", "Leroy Sands",
            ]
        )
    ]

    # Get the EPC
    heights = []
    for _, row in tqdm(df.iterrows(), total=len(df)):
        searcher = SearchEpc(
            address1=str(row["Matched EPC Address"]),
            postcode=str(row["POSTCODE"]),
            uprn=str(int(row["uprn"])),
            auth_token=EPC_AUTH_TOKEN,
            os_api_key="",
            property_type=None,
            fast=True,
        )
        # Force the skipping of estimating the EPC
        searcher.ordnance_survey_client.property_type = None
        searcher.ordnance_survey_client.built_form = None

        searcher.find_property(skip_os=True)

        height = {
            "uprn": row["uprn"],
            "floor_height": searcher.newest_epc["floor-height"]
        }
        heights.append(height)

    df = df.merge(
        pd.DataFrame(heights),
        how="left",
        on="uprn"
    )

    df.to_excel("WF surveyors with floor heights.xlsx", index=False)


if __name__ == "__main__":
    main()
