import os
import cea
import geopandas
import pandas as pd
from packaging import version

import cea.inputlocator
from cea.utilities.dbf import dbf_to_dataframe, dataframe_to_dbf

# Constants
SCENARIO_TO_MIGRATE = r"c:\Users\darthoma\Documents\CityEnergyAnalyst\projects\2.29.0\kleinalbis"
EXAMPLE_SCENARIO = r"c:\Users\darthoma\Documents\CityEnergyAnalyst\projects\working-with-databases\kleinalbis"

# the (new) database to use for migration - user should prepare this in advance...
DATABASE = r"c:\Users\darthoma\Documents\GitHub\CityEnergyAnalyst\cea\databases\CH"
CONSTRUCTION_STANDARD_XLSX = os.path.join(DATABASE, "archetypes", "CONSTRUCTION_STANDARD.xlsx")

# we can't use the input locator for the v2.29.0 scenario, but we _can_ (and should) use it for v2.31.1.
locator = cea.inputlocator.InputLocator(scenario=EXAMPLE_SCENARIO)

"""
Explanation: Migration script
Migrate the input databases from the 2.29 format to the 2.31.1 format.
This notebook assumes SCENARIO_TO_MIGRATE points to a scenario of Kleinalbis (which buildings?) in v2.29.0 format and EXAMPLE_SCENARIO to a scenario in v2.31.1 format. 
These are the v2.29.0 format files to migrate:

shapefiles 
inputs/building-geometry/zone.shp... checked. no changes!
inputs/building-geometry/site.shp... checked. no changes!
inputs/building-geometry/surroundings.shp... checked. no changes!
inputs to archetypes-mapper
inputs/building-properties/age.dbf... checked: merge to typology.dbf
inputs/building-properties/occupancy.dbf... checked: merge to typology.dbf
outputs of archetypes-mapper
inputs/building-properties/air_conditioning_systems.dbf
inputs/building-properties/architecture.dbf
inputs/building-properties/indoor_comfort.dbf
inputs/building-properties/internal_loads.dbf
inputs/building-properties/supply_systems.dbf
inputs/building-properties/schedules/B*.csv... checked: add ELECTROMOBILITY column?

Also, remember, the inputs/technology folder also changed and the v2.31.1 format references these possibly differently.
Assumptions
To make this easier, here are some assumptions this script makes:

use types in occupancy.dbf correspond to use types in USE_TYPE_PROPERTIES.XSLX (and corresponding *.csv schedules)
user edits the schedules in the database selected, unless user has defined their own schedules with the schedule-maker tool - in that case, don't create schedules with the archetypes-mapper!
no more than 3 uses per row in occupancy.dbf
End of explanation
"""


# let's first compare the two files (and practice reading/writing shape files)
old_zone_shp = geopandas.GeoDataFrame.from_file(
    os.path.join(SCENARIO_TO_MIGRATE, "inputs", "building-geometry", "zone.shp"))
old_zone_shp

new_zone_shp = geopandas.GeoDataFrame.from_file(locator.get_zone_geometry())
new_zone_shp

"""
Explanation: Migrate building-geometry files
End of explanation
"""


old_age_dbf = dbf_to_dataframe(os.path.join(SCENARIO_TO_MIGRATE, "inputs", "building-properties", "age.dbf"))
old_age_dbf

# i think that should be migrated to typology.dbf
new_typology_dbf = dbf_to_dataframe(locator.get_building_typology())
new_typology_dbf

"""
Explanation: The zone.shp file is the the same! (repeated this procedure with site.shp and surroundings.shp)
Migrate age.dbf file and occupancy.dbf file
End of explanation
"""


old_occupancy_dbf = dbf_to_dataframe(
    os.path.join(SCENARIO_TO_MIGRATE, "inputs", "building-properties", "occupancy.dbf"))
# old_occupancy_dbf

"""
Explanation: OK. So. This is going to be interesting.

old_age_dbf.Name --> new_typology_dbf.Name
old_age_dbf.built --> new_typology_dbf.YEAR

(I'm assuming we just forget the other stuff for now - maybe when we look into the old technologies folder, we'll figure out a way to translate the HVAC, basement, envelope, partitions, roof and windows fields. Maybe.)
End of explanation
"""


# NOTE: this stuff is good for the migration script


def lookup_standard(year, standards_df):
    # find first standard that is similar to the year
    standard = standards_df[(standards_df.YEAR_START < year) & (year < standards_df.YEAR_END)].iloc[0]
    return standard.STANDARD

def convert_occupancy(name, occupancy_dbf):
    # FIXME: do this smorter
    row = occupancy_dbf[occupancy_dbf.Name == name].iloc[0]
    uses = set(row.to_dict().keys()) - set(["Name", "REFERENCE"])
    uses = sorted(uses, cmp=lambda a, b: cmp(float(row[a]), float(row[b])), reverse=True)
    result = {
        "1ST_USE": uses[0], 
        "1ST_USE_R": float(row[uses[0]]), 
        "2ND_USE": uses[1], 
        "2ND_USE_R": float(row[uses[1]]), 
        "3RD_USE": uses[2], 
        "3RD_USE_R": float(row[uses[2]])}
    if pd.np.isclose(result["2ND_USE_R"], 0.0):
        result["1ST_USE_R"] = 1.0
        result["2ND_USE_R"] = 0.0
        result["3RD_USE_R"] = 0.0
        result["2ND_USE"] = "NONE"
        result["3RD_USE"] = "NONE"
    elif pd.np.isclose(result["3RD_USE_R"], 0.0):
        result["1ST_USE_R"] = 1.0 - result["2ND_USE_R"]
        result["3RD_USE_R"] = 0.0
        result["3RD_USE"] = "NONE"
    
    result["1ST_USE_R"] = 1.0 - result["2ND_USE_R"] - result["3RD_USE_R"]
    return result

def merge_age_and_occupancy_to_typology(age_dbf, occupancy_dbf, standards_df):
    # merge age.dbf and occupancy.dbf to typology.dbf
    typology_dbf_columns = ["Name", "YEAR", "STANDARD", "1ST_USE", "1ST_USE_R", "2ND_USE", "2ND_USE_R", "3RD_USE", "3RD_USE_R"]
    typology_dbf = pd.DataFrame(columns=typology_dbf_columns)
    
    for rindex, row in age_dbf.iterrows():
        typology_row = {
            "Name": row.Name, 
            "YEAR": row.built,
            "STANDARD": lookup_standard(row.built, standards_df)}
        typology_row.update(convert_occupancy(row.Name, occupancy_dbf))
        
        typology_dbf = typology_dbf.append(typology_row, ignore_index=True)
    
    return typology_dbf


standards_df = pd.read_excel(CONSTRUCTION_STANDARD_XLSX, "STANDARD_DEFINITION")
typology_dbf = merge_age_and_occupancy_to_typology(old_age_dbf, old_occupancy_dbf, standards_df)
typology_dbf

"""
Explanation: Clearly, this information goes into the typology.dbf file - we just need to figure out how to map the uses.
End of explanation
"""


air_conditioning_systems_dbf = dbf_to_dataframe(
    os.path.join(SCENARIO_TO_MIGRATE, "inputs", "building-properties", "air_conditioning_systems.dbf"))
air_conditioning_systems_dbf

dbf_to_dataframe(locator.get_building_air_conditioning())

locator.get_database_construction_standards()

"""
Explanation: Migrate inputs/building-properties/air_conditioning_systems.dbf

copy Name, cool_ends, cool_starts, heat_ends, heat_starts
look up type_cs, type_ctrl, type_dhw, type_hs, type_vent (this is cool - maybe only the names changed?)
type_dhw: looks up (Tsww0_C, Qwwmax_Wm2) from database/assemblies/HVAC.xls
type_hs: trickier... i don't know if this can be done automatically?

My thinking right now is to use the archetypes-mapper tool to do the rest of the migration...
End of explanation
"""


