"""
We have found, within the Peabody data, a large volume of properties with missing and incorrects
UPRNS and incorrect address data. We want to flag these records and also find missings where we can

We also have duplicate UPRNS that should be flagged
"""
import json
import time
import os
import pandas as pd
import numpy as np
from tqdm import tqdm
from dotenv import load_dotenv
from asset_list.utils import get_data_for_property
from utils.logger import setup_logger
from utils.s3 import read_io_from_s3, save_dataframe_to_s3_parquet, read_dataframe_from_s3_parquet

logger = setup_logger()

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

sustainability_data = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/2025_11_11 - Peabody "
    "- Data Extracts for Domna.xlsx",
    sheet_name="Sustainability"
)
property_data = pd.read_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting Project/2025_11_11 - Peabody "
    "- Data Extracts for Domna.xlsx",
    sheet_name="Properties"
)

missing_uprns = sustainability_data[pd.isnull(sustainability_data['UPRN'])].copy()

# Any non-numeric UPRNS or leading with 0s are invalid
non_numeric_uprns = sustainability_data[
    ~sustainability_data['UPRN'].astype(str).str.match(r'^[1-9][0-9]*$') & ~pd.isnull(sustainability_data['UPRN'])
    ].copy()
# 70 properties
leading_zero_uprns = sustainability_data[
    sustainability_data['UPRN'].astype(str).str.startswith('0')
].copy()

# Flag duplicates
duplicate_uprns = sustainability_data[
    sustainability_data.duplicated(subset=['UPRN'], keep=False) & ~pd.isnull(sustainability_data['UPRN'])
    ].copy()

# Store this data
# missing_uprns.to_csv("/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting
# Project/data_validation/missing_uprns.csv", index=False)
# non_numeric_uprns.to_csv("/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting
# Project/data_validation/non_numeric_uprns.csv", index=False)
# leading_zero_uprns.to_csv("/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting
# Project/data_validation/leading_zero_uprns.csv", index=False)
# duplicate_uprns.to_csv("/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting
# Project/data_validation/duplicate_uprns.csv", index=False)

# Take everything remaining
data_needing_validation = sustainability_data[
    ~sustainability_data["Org Ref"].isin(
        missing_uprns["Org Ref"].values.tolist() + non_numeric_uprns["Org Ref"].values.tolist() +
        leading_zero_uprns["Org Ref"].values.tolist() + duplicate_uprns["Org Ref"].values.tolist()
    )
].copy()

# TODO: We should build a SAL for UPRNS that are missing, invalid or duplicated

# We check UPRN validity against our OS data
uprn_filenames = read_dataframe_from_s3_parquet(
    bucket_name="retrofit-data-dev", file_key="spatial/filename_meta.parquet"
)

# We're going to:
# 1) Grab a filename
# 2) Read it in
# 3) Check which UPRNS from our data are in that file
# 4) Keep a record of which UPRNS were found where

for uprn_file in tqdm(uprn_filenames['filenames'].values, total=len(uprn_filenames)):
    spatial_data = read_dataframe_from_s3_parquet(
        bucket_name="retrofit-data-dev", file_key=f"spatial/{uprn_file}"
    )

    uprns_in_file = data_needing_validation[
        data_needing_validation['UPRN'].astype('Int64').isin(spatial_data['UPRN'].astype('Int64').values)
    ].copy()

    print("Found {} UPRNS in file {}".format(len(uprns_in_file), uprn_file))
    if len(uprns_in_file) > 0:
        # Store the found UPRNS in the validation cache
        data_to_store = uprns_in_file[["Org Ref", "UPRN"]].copy()
        data_to_store["Source File"] = uprn_file
        # Store
        data_to_store.to_csv(
            "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting "
            f"Project/data_validation/validation_cache/{uprn_file.split('.parquet')[0]}_found_uprns.csv",
            index=False
        )

# Get all of the files:
storage_locations = ("/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting "
                     "Project/data_validation/validation_cache")
# List contents
folder_contents = os.listdir(storage_locations)
# Grab files and concatenate
all_found_uprns = []
for file in folder_contents:
    if file.endswith("_found_uprns.csv"):
        df = pd.read_csv(os.path.join(storage_locations, file))
        all_found_uprns.append(df)

all_found_uprns = pd.concat(all_found_uprns)

# We now flag any UPRNS that were not found in any of the OS datasets
os_missed_uprns = data_needing_validation[
    ~data_needing_validation['Org Ref'].isin(all_found_uprns['Org Ref'].values.tolist())
].copy()

# store
os_missed_uprns.to_csv(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting "
    "Project/data_validation/os_missed_uprns.csv",
    index=False
)

# Now build a larger table for standardisation
to_standardised = pd.concat(
    [missing_uprns, non_numeric_uprns, leading_zero_uprns, duplicate_uprns, os_missed_uprns]
)

to_standardised.to_excel(
    "/Users/khalimconn-kowlessar/Documents/hestia/Customers/Peabody/Nov 2025 Consulting "
    "Project/data_validation/to_standardise_uprns.xlsx",
    index=False)

# We prepare a finalised dataset to work with, that excludes all problematic properties and leaves us with
# properties for which we have the data we need

finalised_data = sustainability_data[
    ~sustainability_data["Org Ref"].isin(
        to_standardised["Org Ref"].values.tolist()
    )
].copy()

# Prepare with the column formats we need, as analogous to a_data_prep where we defined an initial working sample
