"""
:authors: Riley Baird (OK), Emma Baker (OK)
"""

import os
import random
from typing import Optional

import arcpy
import numpy as np
import pandas as pd
# noinspection PyUnresolvedReferences
from arcgis.features import GeoAccessor, GeoSeriesAccessor

from ...lib.counties import CountyInfo
from ...lib.misc import na_in_list, na_eq, na_in_set
from ...lib.session import config

random_start_phrase = random.choice(['Make it so!', 'Hello World!', 'As the prophecy foretold...', 'Greetings earthlings.', 'All are bases are belong to us.', 'The Jig is up!'])

tool_switch: str = "MSAG_NG911_COMPARISON"
error_list = ["PLEASE SELECT FILE", "NO FIELDS FOUND IN SELECTED FILE", "--PLEASE SELECT FIELD--"]

county_info = CountyInfo

## Custom Tool Parameter Information
class CustomToolParameterInfo:
    def __init__(self):
        ## Parameter Indexes
        self.std_gdb_idx = 0
        self.tool_variant_idx = self.std_gdb_idx + 1
        self.msag_file_idx = self.tool_variant_idx + 1
        self.msag_fields_idx = self.msag_file_idx + 1
        self.tn_file_idx = self.msag_fields_idx + 1
        self.tn_fields_idx = self.tn_file_idx + 1
        self.phone_option_idx = self.tn_fields_idx + 1
        self.phone_con_table_idx = self.phone_option_idx + 1
        self.phone_sep_table_idx = self.phone_con_table_idx + 1
        ## File type options
        self.excel_type = ['.xls','.xlsx']
        self.csv_type = ['csv']
        ## Std Dataset Names
        self.required_dataset_name = config.gdb_info.required_dataset_name  # "NG911"
        self.optional_dataset_name = config.gdb_info.optional_dataset_name  # "OptionalLayers"
        ## Std FCs
        self.road_fc = config.feature_classes.road_centerline.name
        self.address_fc = config.feature_classes.address_point.name
        ## Tool options
        self.variant_list = [f"MSAG", f"TELEPHONE"]
        ## Field List
        self.msag_fields_list = {
            "Low": [config.fields.add_l_from.name, config.fields.add_r_from.name],
            "High": [config.fields.add_l_to.name, config.fields.add_r_to.name],
            "Parity (ODD/EVEN/BOTH)": [config.fields.parity_l.name, config.fields.parity_r.name],
            "Dir": [config.fields.lgcypredir.name],
            "Street": [config.fields.lgcypretyp.name, config.fields.pretypesep.name, config.fields.lgcystreet.name, config.fields.lgcytype.name, config.fields.lgcysufdir.name],
            "Community": [config.fields.msagcomm_l.name, config.fields.msagcomm_r.name],
            "County": [config.fields.county_l.name, config.fields.county_r.name],
            "State": [config.fields.state_l.name, config.fields.state_r.name],
            "ESN": [config.fields.esn_l.name, config.fields.esn_r.name]
        }
        self.tn_fields_list = {
            "House Number": [config.fields.addnumber.name],
            "Dir": [config.fields.lgcypredir.name],
            "Street": [config.fields.lgcypretyp.name, config.fields.pretypesep.name, config.fields.lgcystreet.name, config.fields.lgcytype.name, config.fields.lgcysufdir.name],
            "Community": [config.fields.msagcomm.name],
            "County": [config.fields.county.name],
            "State": [config.fields.state.name],
            "ESN": [config.fields.esn.name],
            "Service Class": ["SERVICECLASS"]
        }
        # TN - Address Points
        self.string_option_list = ["CONCATENATED", "SEPARATED"]
        self.concated_phone_line = ["FULL PHONELINE"]
        self.separated_phone_list = ["NPA", "NXX", "PHONELINE"]
        self.msag_csv_file_name = 'msag_final_match_unique_ids.csv'
        self.tn_csv_file_name = 'tn_final_match_unique_ids.csv'

custom_param = CustomToolParameterInfo


def get_file_df(path: str) -> Optional[pd.DataFrame]:
    file_type = f".{os.path.basename(path).split('.')[-1]}"
    if file_type in custom_param().excel_type: # '.xlsx', '.xls'
        df = pd.read_excel(path)
    elif file_type in custom_param().csv_type: # '.csv'
        df = pd.read_csv(path)
    else:
        df = pd.DataFrame()
    return df


class MSAGNG911Comparison:
    def __init__(self):
        """Define the tool (tool name is the name of the class)."""
        self.label = f"MSAG/TN-NG911 Comparison"
        self.description = f"MSAG/TN-NG911 Comparison"
        self.canRunInBackground = False
        self.category = "4 - MSAG"

    def getParameterInfo(self):
        """Define parameter definitions"""

        params = []

        std_gdb = arcpy.Parameter(
            displayName=f"Select Standard Geodatabase.",
            name="std_gdb",
            datatype="DEWorkspace",
            parameterType="Required",
            direction="Input")
        params += [std_gdb]

        analysis_variant_parameter = arcpy.Parameter(
            displayName=f"\nSelect analysis variant.",
            name="analysis_variant_parameter",
            datatype="GPString",
            parameterType="Required",
            direction="Input",
            multiValue=True)
        analysis_variant_parameter.controlCLSID = "{38C34610-C7F7-11D5-A693-0008C711C8C1}" # Choice list with Select All button
        analysis_variant_parameter.filter.type = "ValueList"
        analysis_variant_parameter.filter.list = custom_param().variant_list
        analysis_variant_parameter.value = custom_param().variant_list[0]
        params += [analysis_variant_parameter]

        msag_file = arcpy.Parameter(
            displayName=f"\nSelect MSAG File.",
            name="msag_file",
            datatype="DEFile",
            parameterType="Optional",
            direction="Input")
        params += [msag_file]

        msag_fields_parameter = arcpy.Parameter(
            displayName=f"\nSelect MSAG fields for analysis:",
            name="msag_fields_parameter",
            datatype="GPValueTable",
            parameterType="Optional",
            direction="Input")
        msag_fields_parameter.controlCLSID = '{1A1CA7EC-A47A-4187-A15C-6EDBA4FE0CF7}'  # Removes Add another button
        msag_fields_parameter.columns = [['GPString', f'{field}'] for field in custom_param().msag_fields_list.keys()]
        msag_fields_parameter.filters[0].type = "ValueList"
        msag_fields_parameter.filters[0].list = [error_list[0]]
        parameter_value_list = [error_list[0]] * len(custom_param().msag_fields_list.keys())
        msag_fields_parameter.values = [parameter_value_list]
        params += [msag_fields_parameter]

        tn_file = arcpy.Parameter(
            displayName=f"\nSelect Telephone File.",
            name="tn_file",
            datatype="DEFile",
            parameterType="Optional",
            direction="Input")
        params += [tn_file]

        tn_fields_parameter = arcpy.Parameter(
            displayName=f"\nSelect Telephone fields for analysis:",
            name="tn_fields_parameter",
            datatype="GPValueTable",
            parameterType="Optional",
            direction="Input")
        tn_fields_parameter.controlCLSID = '{1A1CA7EC-A47A-4187-A15C-6EDBA4FE0CF7}'  # Removes Add another button
        tn_fields_parameter.columns = [['GPString', f'{field}'] for field in custom_param().tn_fields_list.keys()]
        tn_fields_parameter.filters[0].type = "ValueList"
        tn_fields_parameter.filters[0].list = [error_list[0]]
        parameter_value_list = [error_list[0]] * len(custom_param().tn_fields_list.keys())
        tn_fields_parameter.values = [parameter_value_list]
        params += [tn_fields_parameter]

        # Concatenated Phone Line or NPA, NXX, Phone Line
        phone_option_parameter = arcpy.Parameter(
            displayName=f"\nSelect `Phone Line` Format.",
            name="phone_option_parameter",
            datatype="GPString",
            parameterType="Optional",
            direction="Input")
        phone_option_parameter.filter.type = "ValueList"
        phone_option_parameter.filter.list = custom_param().string_option_list
        phone_option_parameter.value = custom_param().string_option_list[0]
        params += [phone_option_parameter]

        concate_phone_field_parameter = arcpy.Parameter(
            displayName=f"\nSelect concatenated `Phone Line` field for analysis.",
            name="concate_phone_field_parameter",
            datatype="GPValueTable",
            parameterType="Optional",
            direction="Input")
        concate_phone_field_parameter.controlCLSID = '{1A1CA7EC-A47A-4187-A15C-6EDBA4FE0CF7}'  # Removes Add another button
        concate_phone_field_parameter.columns = [['GPString', f'{field}'] for field in custom_param().concated_phone_line]
        concate_phone_field_parameter.filters[0].type = "ValueList"
        concate_phone_field_parameter.filters[0].list = [error_list[0]]
        parameter_value_list = [error_list[0]] * len(custom_param().concated_phone_line)
        concate_phone_field_parameter.values = [parameter_value_list]
        params += [concate_phone_field_parameter]

        sep_phone_field_parameter = arcpy.Parameter(
            displayName=f"\nSelect separated `Phone Line` fields for analysis.",
            name="sep_phone_field_parameter",
            datatype="GPValueTable",
            parameterType="Optional",
            direction="Input")
        sep_phone_field_parameter.controlCLSID = '{1A1CA7EC-A47A-4187-A15C-6EDBA4FE0CF7}'  # Removes Add another button
        sep_phone_field_parameter.columns = [['GPString', f'{field}'] for field in custom_param().separated_phone_list]
        sep_phone_field_parameter.filters[0].type = "ValueList"
        sep_phone_field_parameter.filters[0].list = [error_list[0]]
        parameter_value_list = [error_list[0]] * len(custom_param().separated_phone_list)
        sep_phone_field_parameter.values = [parameter_value_list]
        params += [sep_phone_field_parameter]

        return params

    def isLicensed(self):
        """Set whether tool is licensed to execute."""
        return True

    def updateParameters(self, parameters):
        """Modify the values and properties of parameters before internal
        validation is performed.  This method is called whenever a parameter
        has been changed."""
        std_gdb = parameters[custom_param().std_gdb_idx]
        tool_variant = parameters[custom_param().tool_variant_idx]
        msag_file = parameters[custom_param().msag_file_idx]
        msag_field_table = parameters[custom_param().msag_fields_idx]
        tn_file = parameters[custom_param().tn_file_idx]
        tn_field_table = parameters[custom_param().tn_fields_idx]
        phone_option = parameters[custom_param().phone_option_idx]
        phone_concat_table = parameters[custom_param().phone_con_table_idx]
        phone_sep_table = parameters[custom_param().phone_sep_table_idx]

        if tool_variant.value and tool_variant.altered and not tool_variant.hasBeenValidated:
            std_gdb.value = std_gdb.value
            tool_variant.value = tool_variant.value
            tool_variant_val = tool_variant.valueAsText
            tool_variant_list = tool_variant_val.split(';') # "MSAG", "TELEPHONE"
            if custom_param().variant_list[0] in tool_variant_list: # "MSAG"
                msag_file.enabled = True
                msag_field_table.enabled = True
                msag_file.value = msag_file.value
                if msag_file.value:
                    file_df = get_file_df(msag_file.valueAsText)
                    if not file_df.empty:
                        value_list = [error_list[-1]] + file_df.columns.to_list()
                    else:
                        value_list = [error_list[1]]
                    for idx in range(0, len(msag_field_table.columns)):
                        msag_field_table.filters[idx].list = value_list
                    msag_field_table.values = [[value_list[0]] * len(msag_field_table.columns)]
                else:
                    value_list = [error_list[0]]
                    for idx in range(0, len(msag_field_table.columns)):
                        msag_field_table.filters[idx].list = value_list
                    msag_field_table.values = [[value_list[0]] * len(msag_field_table.columns)]
            else:
                msag_file.enabled = False
                msag_file.value = msag_file.value
                msag_field_table.enabled = False
                msag_field_table.values = msag_field_table.values
            if custom_param().variant_list[1] in tool_variant_list: # "TELEPHONE"
                tn_file.enabled = True
                tn_field_table.enabled = True
                tn_file.value = tn_file.value
                if tn_file.value:
                    file_df = get_file_df(tn_file.valueAsText)
                    if not file_df.empty:
                        value_list = [error_list[-1]] + file_df.columns.to_list()
                    else:
                        value_list = [error_list[1]]
                    for idx in range(0, len(tn_field_table.columns)):
                        tn_field_table.filters[idx].list = value_list
                    tn_field_table.values = [[value_list[0]] * len(tn_field_table.columns)]
                else:
                    value_list = [error_list[0]]
                    for idx in range(0, len(tn_field_table.columns)):
                        tn_field_table.filters[idx].list = value_list
                    tn_field_table.values = [[value_list[0]] * len(tn_field_table.columns)]
                phone_option.value = phone_option.value
                phone_option.enabled = True
                if phone_option.value == custom_param().string_option_list[0]:
                    phone_concat_table.filters[0].list = value_list
                    phone_concat_table.values = [[value_list[0]] * 1]
                    phone_concat_table.enabled = True
                    phone_sep_table.enabled = False
                else:
                    for idx in range(0, len(phone_sep_table.columns)):
                        phone_sep_table.filters[idx].list = value_list
                    phone_sep_table.values = [[value_list[0]] * len(phone_sep_table.columns)]
                    phone_sep_table.enabled = True
                    phone_concat_table.enabled = False
            else:
                tn_file.enabled = False
                tn_file.value = tn_file.value
                tn_field_table.enabled = False
                tn_field_table.values = tn_field_table.values
                phone_option.enabled = False
                phone_option.value = phone_option.value
                phone_concat_table.enabled = False
                phone_concat_table.values = phone_concat_table.values
                phone_sep_table.enabled = False
                phone_sep_table.values = phone_sep_table.values
        elif tool_variant.value and tool_variant.hasBeenValidated:
            std_gdb.value = std_gdb.value
            tool_variant.value = tool_variant.value

            msag_file.enabled = msag_file.enabled
            msag_field_table.enabled = msag_field_table.enabled
            if msag_file.value and msag_file.altered and not msag_file.hasBeenValidated:
                msag_file.value = msag_file.value
                file_path = msag_file.valueAsText
                file_df = get_file_df(file_path)
                if not file_df.empty:
                    value_list = [error_list[-1]] + file_df.columns.to_list()
                else:
                    value_list = [error_list[1]]
                for idx in range(0, len(msag_field_table.columns)):
                    msag_field_table.filters[idx].list = value_list
                msag_field_table.values = [[value_list[0]] * len(msag_field_table.columns)]
            elif msag_file.value and msag_file.hasBeenValidated:
                msag_file.value = msag_file.value
                msag_field_table.values = msag_field_table.values
            else:
                value_list = [error_list[0]]
                for idx in range(0, len(msag_field_table.columns)):
                    msag_field_table.filters[idx].list = value_list
                msag_field_table.values = [[value_list[0]] * len(msag_field_table.columns)]

            tn_file.enabled = tn_file.enabled
            tn_field_table.enabled = tn_field_table.enabled
            if tn_file.value and tn_file.altered and not tn_file.hasBeenValidated:
                tn_file.value = tn_file.value
                phone_option.value = phone_option.value
                phone_option.enabled = phone_option.enabled
                file_path = tn_file.valueAsText
                file_df = get_file_df(file_path)
                if not file_df.empty:
                    value_list = [error_list[-1]] + file_df.columns.to_list()
                else:
                    value_list = [error_list[1]]
                for idx in range(0, len(tn_field_table.columns)):
                    tn_field_table.filters[idx].list = value_list
                tn_field_table.values = [[value_list[0]] * len(tn_field_table.columns)]
                phone_concat_table.enabled = phone_concat_table.enabled
                phone_sep_table.enabled = phone_sep_table.enabled
                for idx in range(0, len(phone_concat_table.columns)):
                    phone_concat_table.filters[idx].list = value_list
                phone_concat_table.values = [[value_list[0]] * len(phone_concat_table.columns)]
                for idx in range(0, len(phone_sep_table.columns)):
                    phone_sep_table.filters[idx].list = value_list
                phone_sep_table.values = [[value_list[0]] * len(phone_sep_table.columns)]
            elif tn_file.value and tn_file.hasBeenValidated:
                tn_file.value = tn_file.value
                tn_field_table.values = tn_field_table.values
                phone_option.enabled = phone_option.enabled
                if phone_option.value and phone_option.altered and not phone_option.hasBeenValidated:
                    phone_option.value = phone_option.value
                    value_list = tn_field_table.filters[0].list
                    if phone_option.value == custom_param().string_option_list[0]:
                        phone_concat_table.filters[0].list = value_list
                        phone_concat_table.values = [[value_list[0]] * 1]
                        phone_concat_table.enabled = True
                        phone_sep_table.enabled = False
                    else:
                        for idx in range(0, len(phone_sep_table.columns)):
                            phone_sep_table.filters[idx].list = value_list
                        phone_sep_table.values = [[value_list[0]] * len(phone_sep_table.columns)]
                        phone_sep_table.enabled = True
                        phone_concat_table.enabled = False
                else:
                    phone_option.value = phone_option.value
                    phone_concat_table.enabled = phone_concat_table.enabled
                    phone_sep_table.enabled = phone_sep_table.enabled
                    phone_concat_table.values = phone_concat_table.values
                    phone_sep_table.values = phone_sep_table.values
            else:
                value_list = [error_list[0]]
                for idx in range(0, len(tn_field_table.columns)):
                    tn_field_table.filters[idx].list = value_list
                tn_field_table.values = [[value_list[0]] * len(tn_field_table.columns)]
                phone_option.enabled = phone_option.enabled
                if phone_option.value and phone_option.altered and not phone_option.hasBeenValidated:
                    phone_option.value = phone_option.value
                    if phone_option.value == custom_param().string_option_list[0]:
                        phone_concat_table.filters[0].list = value_list
                        phone_concat_table.values = [[value_list[0]] * 1]
                        phone_concat_table.enabled = True
                        phone_sep_table.enabled = False
                    else:
                        for idx in range(0, len(phone_sep_table.columns)):
                            phone_sep_table.filters[idx].list = value_list
                        phone_sep_table.values = [[value_list[0]] * len(phone_sep_table.columns)]
                        phone_sep_table.enabled = True
                        phone_concat_table.enabled = False
                else:
                    phone_option.value = phone_option.value
                    phone_concat_table.enabled = phone_concat_table.enabled
                    phone_sep_table.enabled = phone_sep_table.enabled
                    phone_concat_table.values = phone_concat_table.values
                    phone_sep_table.values = phone_sep_table.values
        else:
            if std_gdb.value:
                std_gdb.value = std_gdb.value
            if tool_variant.value:
                tool_variant.value = tool_variant.value
            msag_file.value = msag_file.value
            msag_file.enabled = False
            msag_field_table.values = msag_field_table.values
            msag_field_table.enabled = False
            tn_file.value = tn_file.value
            tn_file.enabled = False
            tn_field_table.values = tn_field_table.values
            tn_field_table.enabled = False
            phone_option.value = phone_option.value
            phone_option.enabled = False
            phone_concat_table.values = phone_concat_table.values
            phone_concat_table.enabled = False
            phone_sep_table.values = phone_sep_table.values
            phone_sep_table.enabled = False

        return

    def updateMessages(self, parameters):
        """Modify the messages created by internal validation for each tool
        parameter.  This method is called after internal validation."""
        ## table parameter
        # for idx in range(custom_param().selected_analysis_idx + 1, len(parameters)):
        #     if parameters[idx].hasBeenValidated and parameters[idx].enabled and parameters[idx].controlCLSID == "{38C34610-C7F7-11D5-A693-0008C711C8C1}" and not parameters[idx].value:
        #         parameters[idx].setErrorMessage(f"PLEASE SELECT AT LEAST ONE OPTION.")

        return

    def execute(self, parameters, messages):
        """The source code of the tool."""
        ## Process parameters from user-specified values
        std_gdb_path = parameters[custom_param().std_gdb_idx].valueAsText
        tool_variant_value = parameters[custom_param().tool_variant_idx].valueAsText
        tool_variant_list = tool_variant_value.split(';') # MSAG, TELEPHONE
        msag_file_path = None
        msag_field_dict = {}
        tn_file_path = None
        tn_field_dict = {}
        phone_field_dict = {}
        if custom_param().variant_list[0] in tool_variant_list:
            msag_file_path = parameters[custom_param().msag_file_idx].valueAsText
            msag_field_values = parameters[custom_param().msag_fields_idx].values
            msag_fields_list = list(custom_param().msag_fields_list.keys())
            for idx, msag_user_val in enumerate(msag_field_values[0]):
                if msag_user_val in error_list:
                    continue
                msag_dict_key = msag_fields_list[idx]
                msag_field_dict[msag_dict_key] = msag_user_val
        if custom_param().variant_list[1] in tool_variant_list:
            tn_file_path = parameters[custom_param().tn_file_idx].valueAsText
            tn_field_values = parameters[custom_param().tn_fields_idx].values
            tn_fields_list = list(custom_param().tn_fields_list.keys())
            for idx, tn_user_val in enumerate(tn_field_values[0]):
                if tn_user_val in error_list:
                    continue
                tn_dict_key = tn_fields_list[idx]
                tn_field_dict[tn_dict_key] = tn_user_val
            phone_option = parameters[custom_param().phone_option_idx].value
            if phone_option == custom_param().string_option_list[0]:
                phone_option_list = custom_param().concated_phone_line
                phone_field_values = parameters[custom_param().phone_con_table_idx].values
            else:
                phone_option_list = custom_param().separated_phone_list
                phone_field_values = parameters[custom_param().phone_sep_table_idx].values
            for idx, phone_user_val in enumerate(phone_field_values[0]):
                if phone_user_val in error_list:
                    continue
                phone_field_dict[phone_option_list[idx]] = phone_user_val

        arcpy.AddMessage(f"{random_start_phrase}")
        arcpy.AddMessage(f"\nComparing MSAG File to Standard Geodatabase...")
        arcpy.AddMessage(f"\nTOOL VARIANTS SELECTED:\t{tool_variant_list}")

        if msag_file_path:
            arcpy.AddMessage(f"\n\nMSAG-{custom_param().road_fc} COMPARISON:\n\nFIELDS:\n{msag_field_dict}")
            arcpy.AddMessage(f"\n\n===BEGINNING ANALYSIS===")
            msag_df = get_file_df(msag_file_path)
            if msag_df.empty:
                file_type = f".{os.path.basename(msag_file_path).split('.')[-1]}"
                arcpy.AddError(f"\nFile type `{file_type}` submitted.\nPlease submit an excel or csv MSAG file.")
                raise Exception(f"\nFile type `{file_type}` submitted.\nPlease submit an excel or csv MSAG file.")

            rd_cl_fc_path = os.path.join(std_gdb_path, config.gdb_info.required_dataset_name, custom_param().road_fc)
            if not arcpy.Exists(rd_cl_fc_path):
                arcpy.AddError(f'\nNo {custom_param().road_fc} found in submitted gdb.')
                raise Exception(f'\nNo {custom_param().road_fc} found in submitted gdb.')
            road_df = pd.DataFrame.spatial.from_featureclass(rd_cl_fc_path)

            if rd_cl_fc_path and len(msag_field_dict.keys()) > 0:
                msag_analysis = comparison_msag_ng911(msag_df, road_df, msag_field_dict, custom_param().road_fc)
                if isinstance(msag_analysis, str):
                    arcpy.AddWarning(f"\nNo `Street` Field provided. Please check tool parameters.")
                    raise Exception(f"\nNo `Street` Field provided. Please check tool parameters.")
                # match_unique_ids: 'LEFT:'NONE' or 'List of Unique IDs'|RIGHT:'NONE' or 'List of Unique IDs''
                msag_out_path = os.path.join(os.path.dirname(std_gdb_path), custom_param().msag_csv_file_name)
                msag_analysis.to_csv(msag_out_path)
                match_null_string = 'NONE'
                match_cnt = len(msag_analysis[msag_analysis[f'final_match_unique_ids'] != match_null_string].index.to_list())
                arcpy.AddMessage(f'\nOVERALL FINAL MSAG MATCHES FOUND:\t{match_cnt} out of {len(msag_analysis.index.to_list())}')
                for side in ["LEFT", "RIGHT"]:
                    match_cnt = len(msag_analysis[msag_analysis[f'final_match_ids_{side.lower()}'] != match_null_string].index.to_list())
                    arcpy.AddMessage(f'\t{side} FINAL MSAG MATCHES FOUND:\t{match_cnt} out of {len(msag_analysis.index.to_list())}')
                arcpy.AddMessage(f'\nExcel MSAG Output located:\n{msag_out_path}')

        if tn_file_path:
            arcpy.AddMessage(f"\n\nTN-{custom_param().address_fc} COMPARISON:\n\nFIELDS:\n{tn_field_dict}\n{phone_field_dict}")
            arcpy.AddMessage(f"\n===BEGINNING ANALYSIS===")
            tn_df = get_file_df(tn_file_path)
            if tn_df.empty:
                file_type = f".{os.path.basename(msag_file_path).split('.')[-1]}"
                arcpy.AddError(f"\nFile type `{file_type}` submitted.\nPlease submit an excel or csv MSAG file.")
                raise Exception(f"\nFile type `{file_type}` submitted.\nPlease submit an excel or csv MSAG file.")

            ap_fc_path = os.path.join(std_gdb_path, config.gdb_info.required_dataset_name, custom_param().address_fc)
            if not arcpy.Exists(ap_fc_path):
                arcpy.AddError(f'\nNo {custom_param().road_fc} found in submitted gdb.')
                raise Exception(f'\nNo {custom_param().road_fc} found in submitted gdb.')
            ap_df = pd.DataFrame.spatial.from_featureclass(ap_fc_path)

            if ap_fc_path and len(tn_field_dict.keys()) > 0:
                tn_analysis = comparison_tn_ng911(tn_df, ap_df, tn_field_dict, custom_param().address_fc)
                if isinstance(tn_analysis, str):
                    arcpy.AddWarning(f"\nNo `Street` Field provided. Please check tool parameters.")
                    raise Exception(f"\nNo `Street` Field provided. Please check tool parameters.")
                tn_out_path = os.path.join(os.path.dirname(std_gdb_path), custom_param().tn_csv_file_name)
                tn_analysis.to_csv(tn_out_path)
                # match_unique_ids: 'NONE' or 'Pipe-separated Unique IDs'
                match_null_string = 'NONE'
                match_cnt = len(tn_analysis[tn_analysis['final_match_ids'] != match_null_string].index.to_list())
                arcpy.AddMessage(f'\nFINAL TN MATCHES FOUND:\t{match_cnt} out of {len(tn_analysis.index.to_list())}\n\nExcel TN Output located:\n{tn_out_path}')


def field_analysis_for_df(analysis_df: pd.DataFrame, user_field_list: list[str], std_field_list: list[str], current_field_key: str, analysis_field: str | list[str], ng911_suffix: str) -> pd.DataFrame:
    """
    Checks if analysis field(s) for a given object in the joined and processed analysis_df is a possible match between the MSAG/TN object and corresponding NG911 object(s). Returns "YES" if the values match and "NO" if the values do not match.
    :param analysis_df: A joined and processed dataframe between the MSAG/TN information and the appropriate NG911 feature class.
    :param user_field_list: Either a list of user-specified fields that are used to access viable possible matches.
    :param std_field_list: Either a list of NG911 fields that are used to access viable possible matches.
    :param current_field_key: Current analysis key (used for printing message).
    :param analysis_field: Dataframe field name(s) for return object information.
    :param ng911_suffix: Join field suffix str (when needed).
    :return: A Dataframe with analysis results.
    """
    field_analysis_df = analysis_df.copy()
    if user_field_list[0] in std_field_list:
        chg_idx = std_field_list.index(user_field_list[0])
        std_field_list[chg_idx] = f'{std_field_list[chg_idx]}{ng911_suffix}'
    field_analysis_list = user_field_list + std_field_list
    arcpy.AddMessage(f'`{current_field_key}` Analysis: {field_analysis_list}')
    if len(std_field_list) == 2:
        # left
        field_analysis_df[analysis_field[0]] = field_analysis_df[field_analysis_list].apply(lambda field_row: "YES" if (na_eq(field_row[field_analysis_list[0]], field_row[field_analysis_list[1]])) else "NO", axis = 1)
        field_analysis_df[analysis_field[1]] = field_analysis_df[field_analysis_list].apply(lambda field_row: "YES" if (na_eq(field_row[field_analysis_list[0]], field_row[field_analysis_list[2]])) else "NO", axis=1)

        arcpy.AddMessage(f'\tMatches found:\n\t\tLEFT: {len(field_analysis_df[field_analysis_df[analysis_field[0]] == "YES"].index.to_list())}\n\t\tRIGHT: {len(field_analysis_df[field_analysis_df[analysis_field[1]] == "YES"].index.to_list())}')
    else:
        if isinstance(analysis_field, list):
            analysis_field = analysis_field[0]
        field_analysis_df[analysis_field] = field_analysis_df[field_analysis_list].apply(lambda field_row: "YES" if (na_eq(field_row[field_analysis_list[0]], field_row[field_analysis_list[1]])) else "NO", axis=1)

        arcpy.AddMessage(f'\tMatches found:\t{len(field_analysis_df[field_analysis_df[analysis_field] == "YES"].index.to_list())}')

    return field_analysis_df


def comparison_msag_ng911(msag_df: pd.DataFrame, ng911_df: pd.DataFrame, msag_user_dict: dict[str, str], fc_name: str) -> pd.DataFrame | str:
    """
    TEXT
    :param msag_df:
    :param ng911_df:
    :param msag_user_dict:
    :param fc_name:
    :return:
    """
    ng911_suffix = '_ng911'
    msag_df_copy = msag_df.copy()
    ng911_df_copy = ng911_df.copy()
    msag_analysis_dict = msag_user_dict.copy()
    unique_id = config.get_feature_class_by_name(fc_name).unique_id.name
    if "Street" not in msag_user_dict.keys():
        arcpy.AddError(f"No 'Street' Field provided for analysis.")
        return "STREET_FIELD_ERROR"
    msag_ng911_dict = custom_param().msag_fields_list.copy()
    # "Street": [config.fields.lgcyfulst.name]
    user_street = msag_user_dict["Street"]
    # std_street = msag_dict["Street"]
    std_street_list = msag_ng911_dict["Street"]
    user_street_for_output = 'Street (Original File)'

    arcpy.AddMessage(f"\n`{user_street}`:`{std_street_list}` Analysis")
    idx_tracker_param = 'idx_tracker'
    idx_options = list(range(1, len(msag_df_copy.index.to_list()) + 1))
    msag_df_copy[idx_tracker_param] = idx_options
    msag_df_copy[user_street] = msag_df_copy[user_street].str.strip()
    msag_df_copy[user_street_for_output] = msag_df_copy[user_street]
    ng911_df_copy['street_string'] = ng911_df_copy[std_street_list].apply(lambda row: ' '.join(row[field].strip() for field in std_street_list if pd.notna(row[field]) and row[field].strip() != ''), axis=1)

    arcpy.AddMessage(f"\tTotal TN file objects:\t{len(msag_df_copy.index.to_list())}")
    join_df = msag_df_copy.set_index(user_street).join(other=ng911_df_copy.set_index('street_string'), how='left', rsuffix=ng911_suffix)
    analysis_df = join_df[(join_df[unique_id].notna() & ~join_df[unique_id].isin([np.nan, pd.NA, None]))].copy()
    arcpy.AddMessage(f"\tNumber of TN file `Street` object matches:\t{len(analysis_df.index.to_list())}")

    field_analysis_dict = {"LEFT": [], "RIGHT": []}
    del msag_analysis_dict["Street"]
    del msag_analysis_dict['Parity (ODD/EVEN/BOTH)']
    del msag_analysis_dict['Low']
    del msag_analysis_dict['High']
    match_options = {}
    if not analysis_df.empty:
        for current_field_key, current_field_name in msag_analysis_dict.items():
            current_field_key_lwr = current_field_key.lower()
            if 'county' in current_field_key_lwr:
                field_analysis_df = analysis_df.copy()
                del analysis_df
                fips_list = [x.fips3 for x in CountyInfo.load()]
                county_fips = [msag_analysis_dict[current_field_key]]
                field_analysis_df[county_fips[0]] = field_analysis_df[county_fips].apply(lambda field_row: f'{field_row[county_fips[0]]:03}', axis=1)
                field_analysis_df['county_name'] = field_analysis_df[county_fips].apply(lambda field_row: f'{county_info.get_county(fips3=field_row[county_fips[0]]).name} COUNTY' if na_in_list(field_row[county_fips[0]], fips_list) else pd.NA, axis=1)
                std_field_list = msag_ng911_dict[current_field_key]
                if county_fips[0] in std_field_list:
                    chg_idx = std_field_list.index(county_fips[0])
                    std_field_list[chg_idx] = f'{std_field_list[chg_idx]}{ng911_suffix}'
                county_field_analysis_list = ['county_name'] + std_field_list
                arcpy.AddMessage(f'`{current_field_key}` TN Analysis: {[county_fips[0]]}->{county_field_analysis_list[0]}, {", ".join(county_field_analysis_list[1:])}')
                # analysis_field = 'county_match'
                analysis_field_l = f'{current_field_key_lwr.split(" ")[0]}_match_l'
                analysis_field_r = f'{current_field_key_lwr.split(" ")[0]}_match_r'
                field_analysis_dict['LEFT'] = field_analysis_dict['LEFT'] + [analysis_field_l]
                field_analysis_dict['RIGHT'] = field_analysis_dict['RIGHT'] + [analysis_field_r]
                # field_analysis_list.append(analysis_field)
                field_analysis_df[analysis_field_l] = field_analysis_df[county_field_analysis_list].apply(lambda field_row: "YES" if (na_eq(field_row[county_field_analysis_list[0]], field_row[county_field_analysis_list[1]])) else "NO", axis=1)
                field_analysis_df[analysis_field_r] = field_analysis_df[county_field_analysis_list].apply(lambda field_row: "YES" if (na_eq(field_row[county_field_analysis_list[0]], field_row[county_field_analysis_list[2]])) else "NO", axis=1)
                arcpy.AddMessage(f'\tMatches found:\n\t\tLEFT: {len(field_analysis_df[field_analysis_df[analysis_field_l] == "YES"].index.to_list())}\n\t\tRIGHT: {len(field_analysis_df[field_analysis_df[analysis_field_r] == "YES"].index.to_list())}')
                analysis_df = field_analysis_df.copy()
            else:
                user_field_list = [msag_analysis_dict[current_field_key]]
                std_field_list = msag_ng911_dict[current_field_key]
                if len(std_field_list) == 1:
                    analysis_field = f'{current_field_key_lwr.split(" ")[0]}_match'
                    field_analysis_dict['LEFT'] = field_analysis_dict['LEFT'] + [analysis_field]
                    field_analysis_dict['RIGHT'] = field_analysis_dict['RIGHT'] + [analysis_field]
                    analysis_field = [analysis_field]
                else:
                    analysis_field_l = f'{current_field_key_lwr.split(" ")[0]}_match_l'
                    analysis_field_r = f'{current_field_key_lwr.split(" ")[0]}_match_r'
                    field_analysis_dict['LEFT'] = field_analysis_dict['LEFT'] + [analysis_field_l]
                    field_analysis_dict['RIGHT'] = field_analysis_dict['RIGHT'] + [analysis_field_r]
                    analysis_field = [analysis_field_l, analysis_field_r]
                # field_analysis_list.append(analysis_field)
                analysis_df[user_field_list[0]] = analysis_df[user_field_list[0]].astype(str)
                analysis_df[user_field_list[0]] = analysis_df[user_field_list[0]].str.strip()
                # if 'esn' in current_field_key_lwr:
                #     analysis_df[user_field_list[0]] = analysis_df[user_field_list].apply(lambda field_row: pd.NA if na_eq(field_row[user_field_list[0]], '') else f'{field_row[user_field_list[0]]:03}', axis=1)
                # else:
                analysis_df[user_field_list[0]] = analysis_df[user_field_list].apply(lambda field_row: pd.NA if na_eq(field_row[user_field_list[0]], '') else field_row[user_field_list[0]], axis=1)
                field_analysis_df = field_analysis_for_df(analysis_df, user_field_list, std_field_list, current_field_key, analysis_field, ng911_suffix)
                analysis_df = field_analysis_df.copy()

        ## Range
        # "Low": [config.fields.add_l_from.name, config.fields.add_r_from.name]
        # "High": [config.fields.add_l_to.name, config.fields.add_r_to.name]
        # "Parity (ODD/EVEN/BOTH)": [config.fields.parity_l.name, config.fields.parity_r.name]
        if "Parity (ODD/EVEN/BOTH)" in msag_user_dict.keys() and "Low" in msag_user_dict.keys() and "High" in msag_user_dict.keys():
            range_df = analysis_df.copy()
            del analysis_df
            user_range_analysis = [msag_user_dict["Parity (ODD/EVEN/BOTH)"]] + [msag_user_dict["Low"]] + [msag_user_dict["High"]]
            range_analysis_l = [msag_ng911_dict["Parity (ODD/EVEN/BOTH)"][0]] + [msag_ng911_dict["Low"][0]] + [msag_ng911_dict["High"][0]]
            range_analysis_r = [msag_ng911_dict["Parity (ODD/EVEN/BOTH)"][1]] + [msag_ng911_dict["Low"][1]] + [msag_ng911_dict["High"][1]]
            arcpy.AddMessage(f'`Parity (ODD/EVEN/BOTH)` TN Analysis: {user_range_analysis + range_analysis_l + range_analysis_r}')
            range_df['range_user'] = range_df[user_range_analysis].apply(lambda row: pd.NA if row.isna().any() else frozenset(range(int(row[1]), (int(row[2]) + 2), 2)) if row[0] in ["ODD", "EVEN"] else frozenset(range(int(row[1]), (int(row[2]) + 1), 1)) if row[0] == "BOTH" else frozenset([0]), axis = 1)
            range_df['range_left'] = range_df[range_analysis_l].apply(lambda row: pd.NA if row.isna().any() else frozenset(range(int(row[1]), (int(row[2]) + 2), 2)) if row[0] in ["ODD", "EVEN"] else frozenset(range(int(row[1]), (int(row[2]) + 1), 1)) if row[0] == "BOTH" else frozenset([0]), axis = 1)
            range_df['range_right'] = range_df[range_analysis_r].apply(lambda row: pd.NA if row.isna().any() else frozenset(range(int(row[1]), (int(row[2]) + 2), 2)) if row[0] in ["ODD", "EVEN"] else frozenset(range(int(row[1]), (int(row[2]) + 1), 1)) if row[0] == "BOTH" else frozenset([0]), axis = 1)

            arcpy.AddMessage(f'\tMatches found:')
            for side in ["LEFT", "RIGHT"]:
                user_range_field = 'range_user'
                side_range_field = f'range_{side.lower()}'
                range_field_list = [user_range_field, side_range_field]
                range_df[f'match_range_{side.lower()}'] = range_df[range_field_list].apply(lambda field_row: 'YES' if na_in_set(field_row[range_field_list[0]], field_row[range_field_list[1]]) else "NO", axis = 1)
                field_analysis_dict[side] = field_analysis_dict[side] + [f'match_range_{side.lower()}']
                match_cnt = len(range_df[range_df[f'match_range_{side.lower()}'] == "YES"].index.to_list())
                arcpy.AddMessage(f'\t\t{side}: {match_cnt}')
            analysis_df = range_df.copy()

        for side, field_analysis_list in field_analysis_dict.items():
            if field_analysis_list:
                analysis_df[f'overall_match_check_{side.lower()}'] = analysis_df[field_analysis_list].apply(lambda row: 'YES' if row[field_analysis_list].eq('YES').all() else 'NO', axis = 1) # Fields will be YES/NO
            else:
                analysis_df[f'overall_match_check_{side.lower()}'] = 'YES' # Street match only with join

        for idx_opt in idx_options:
            match_tracker_options = {"LEFT": [], "RIGHT": []}
            for side in match_tracker_options.keys():
                overall_match_field = f'overall_match_check_{side.lower()}'
                indexes = analysis_df[(analysis_df[idx_tracker_param] == idx_opt) & (analysis_df[overall_match_field] == 'YES')].index.to_list()
                if indexes:
                    match_tracker_options[side] = analysis_df.loc[indexes, unique_id].to_list()
            match_options[idx_opt] = match_tracker_options

    final_df = msag_df_copy.copy()
    for side in ["LEFT", "RIGHT"]:
        final_df[f'final_match_ids_{side.lower()}'] = 'NONE'
        if match_options:
            for track_idx, option_dict in match_options.items():
                id_list = option_dict[side]
                if id_list:
                    final_df.loc[final_df[idx_tracker_param] == track_idx, f'final_match_ids_{side.lower()}'] = '|'.join(id_list)
    final_df[f'final_match_unique_ids'] = 'NONE'
    for track_idx, option_dict in match_options.items():
        overall_idx_list = list(set(option_dict["LEFT"] + option_dict["RIGHT"]))
        if overall_idx_list:
            final_df.loc[final_df[idx_tracker_param] == track_idx, f'final_match_unique_ids'] = '|'.join(overall_idx_list)

    return final_df


def comparison_tn_ng911(tn_df: pd.DataFrame, ng911_df: pd.DataFrame, tn_file_dict: dict[str, str], fc_name: str) -> pd.DataFrame | str:
    """
    TEXT
    :param tn_df:
    :param ng911_df:
    :param tn_file_dict:
    :param fc_name:
    :return:
    """
    ng911_suffix = '_ng911'
    tn_df_copy = tn_df.copy()
    ng911_df_copy = ng911_df.copy()
    tn_analysis_dict = tn_file_dict.copy()
    unique_id = config.get_feature_class_by_name(fc_name).unique_id.name
    if "Street" not in tn_file_dict.keys():
        arcpy.AddError(f"No 'Street' Field provided for analysis.")
        return "STREET_FIELD_ERROR"
    tn_ng911_dict = custom_param().tn_fields_list.copy()
    user_street = tn_file_dict["Street"]
    std_street_list = tn_ng911_dict["Street"]
    user_street_for_output = 'Street (Original File)'
    arcpy.AddMessage(f"\n`{user_street}`:`{std_street_list}` Analysis")
    idx_tracker_param = 'idx_tracker'
    idx_options = list(range(1, len(tn_df_copy.index.to_list()) + 1))
    tn_df_copy[idx_tracker_param] = idx_options
    tn_df_copy[user_street] = tn_df_copy[user_street].str.strip()
    tn_df_copy[user_street_for_output] = tn_df_copy[user_street]
    ng911_df_copy['street_string'] = ng911_df_copy[std_street_list].apply(lambda row: ' '.join(row[field].strip() for field in std_street_list if pd.notna(row[field]) and row[field].strip() != ''), axis = 1)

    arcpy.AddMessage(f"\tTotal TN file objects:\t{len(tn_df_copy.index.to_list())}")
    join_df = tn_df_copy.set_index(user_street).join(other=ng911_df_copy.set_index('street_string'), how='left', rsuffix=ng911_suffix)
    analysis_df = join_df[(join_df[unique_id].notna() & ~join_df[unique_id].isin([np.nan, pd.NA, None]))].copy()
    analysis_df.reset_index(inplace=True, drop=True)
    arcpy.AddMessage(f"\tNumber of TN file `Street` object matches:\t{len(analysis_df.index.to_list())}")

    field_analysis_list = []
    del tn_analysis_dict["Street"]
    del tn_analysis_dict['Service Class']
    if not analysis_df.empty:
        for current_field_key, current_field_name in tn_analysis_dict.items():
            current_field_key_lwr = current_field_key.lower()
            if 'county' in current_field_key_lwr:
                field_analysis_df = analysis_df.copy()
                del analysis_df
                fips_list = [x.fips3 for x in CountyInfo.load()]
                county_fips = [tn_analysis_dict[current_field_key]]
                field_analysis_df[county_fips[0]] = field_analysis_df[county_fips].apply(lambda field_row: f'{field_row[county_fips[0]]:03}', axis=1)
                field_analysis_df['county_name'] = field_analysis_df[county_fips].apply(lambda field_row: f'{county_info.get_county(fips3=field_row[county_fips[0]]).name} COUNTY' if na_in_list(field_row[county_fips[0]], fips_list) else pd.NA, axis=1)
                std_field_list = tn_ng911_dict[current_field_key]
                if county_fips[0] in std_field_list:
                    chg_idx = std_field_list.index(county_fips[0])
                    std_field_list[chg_idx] = f'{std_field_list[chg_idx]}{ng911_suffix}'
                county_field_analysis_list = ['county_name'] + std_field_list
                arcpy.AddMessage(f'`{current_field_key}` TN Analysis: {[county_fips[0]]}->{county_field_analysis_list[0]}, {", ".join(county_field_analysis_list[1:])}')
                analysis_field = 'county_match'
                field_analysis_list.append(analysis_field)
                field_analysis_df[analysis_field] = field_analysis_df[county_field_analysis_list].apply(lambda field_row: "YES" if (na_eq(field_row[county_field_analysis_list[0]], field_row[county_field_analysis_list[1]])) else "NO", axis=1)
                arcpy.AddMessage(f'\tMatches found:\t{len(field_analysis_df[field_analysis_df[analysis_field] == "YES"].index.to_list())}')
                analysis_df = field_analysis_df.copy()
            else:
                user_field_list = [tn_analysis_dict[current_field_key]]
                std_field_list = tn_ng911_dict[current_field_key]
                analysis_field = f'{current_field_key_lwr.split(" ")[0]}_match'
                field_analysis_list.append(analysis_field)
                if 'house' in current_field_key_lwr:
                    analysis_df[user_field_list[0]] = analysis_df[user_field_list[0]].astype(int)
                else:
                    analysis_df[user_field_list[0]] = analysis_df[user_field_list[0]].astype(str)
                    analysis_df[user_field_list[0]] = analysis_df[user_field_list[0]].str.strip()
                    # if 'esn' in current_field_key_lwr:
                    #     analysis_df[user_field_list[0]] = analysis_df[user_field_list].apply(lambda field_row: pd.NA if na_eq(field_row[user_field_list[0]], '') else f'{field_row[user_field_list[0]]:03}', axis=1)
                    # else:
                    analysis_df[user_field_list[0]] = analysis_df[user_field_list].apply(lambda field_row: pd.NA if na_eq(field_row[user_field_list[0]], '') else field_row[user_field_list[0]], axis=1)
                field_analysis_df = field_analysis_for_df(analysis_df, user_field_list, std_field_list, current_field_key, analysis_field, ng911_suffix)
                analysis_df = field_analysis_df.copy()

    analysis_df['match_unique_ids'] = f"NONE"
    if field_analysis_list:
        for idx in analysis_df.index:
            match_boolean = True
            for analysis_field in field_analysis_list:
                field_analysis_results = str(analysis_df.loc[idx, analysis_field])
                if field_analysis_results != 'YES':
                    match_boolean = False
                    break
            if not match_boolean:
                continue
            analysis_df.loc[idx, 'match_unique_ids'] = str(analysis_df.loc[idx, unique_id])

    match_options = {}
    for idx_opt in idx_options:
        match_idx = list(set(analysis_df.loc[analysis_df[idx_tracker_param] == idx_opt, 'match_unique_ids'].to_list()))
        if 'NONE' in match_idx:
            match_idx.remove('NONE')
        if match_idx:
            match_options[idx_opt] = '|'.join(match_idx)
        else:
            match_options[idx_opt] = 'NONE'

    final_df = tn_df_copy.copy()
    final_df['final_match_ids'] = final_df[[idx_tracker_param]].apply(lambda field_row: match_options[field_row[idx_tracker_param]] if na_in_list(field_row[idx_tracker_param], list(match_options.keys())) else 'NONE', axis=1)

    return final_df


if __name__ == "__main__":
    raise Exception("This module is a dependency of an ArcGIS Python Toolbox and should not be executed directly.")