
import arcpy
import sys
import os
sys.path.append(os.path.join(os.path.split(os.path.abspath(__file__))[0],'..'))
from src import config as cfg
from src import build_database as bd
from src import validate_database as vd

#TODO: Could these be accomplished from reading information in config?
DATATABLES = [ 'modelelementvaluelines',
                'modelelementvaluepoints',
                'modelelementsurfaces',
                'deviatedboreholetrackpicks',
                'verticalboreholepicks',
                'novaluelines'
                ]

DOMAINABLE_FIELDS = ['datasourceid',
                  'modelelement',
                  'elementtype',
                  'valueunits',
                  'type',
                  'deviatedboreholetracksid',
                  'verticalboreholepointsid'
                  ]

DOMAIN_MAPPING = {'datasourceid': ('datasources','datasources_id','source'),
                  'modelelement': ('descriptionofmodelelements','modelelement','name'),
                  'elementtype': ('elementtypedict','elementtype','elementtype'),
                  'valueunits': ('measurementunits','unit','unit'),
                  'type': ('glossary','term','term'),
                  #Could imagine folks wanting to map borehole domains to the identifier... would just need to change the last item in the following
                  #tuples to: 'borehole_identifier' . That value is nullable though, which could cause some issues. 
                  'deviatedboreholetracksid': ('deviatedboreholetracks', 'deviatedboreholetracks_id','deviatedboreholetracks_id'),
                  'verticalboreholepointsid': ('verticalboreholepoints', 'verticalboreholepoints_id', 'verticalboreholepoints_id')
                  }

class Toolbox:
    def __init__(self):
        """Define the toolbox (the name of the toolbox is the name of the
        .pyt file)."""
        self.label = "Toolbox"
        self.alias = "toolbox"

        # List of tool classes associated with this toolbox
        self.tools = [BuildDatabase,ValidateDatabase,AddDomains,AppendDatabase]


class BuildDatabase:
    def __init__(self):
        """Define the tool (tool name is the name of the class)."""
        self.label = "Build database"
        self.description = "Build a database according to the defined standard with the requested tables. It will also add all required dependencies of the requested tables."

    def getParameterInfo(self):
        """Define the tool parameters."""
        #Path to database
        param0 = arcpy.Parameter(
            displayName="New Database",
            name="new_database_path",
            datatype="DEWorkspace",
            parameterType="Required",
            direction="Output")
        
        param0.filter.list = ["gdb"]

        #Tables to add to database
        param1 = arcpy.Parameter(
            displayName="Tables to include",
            name="tables_to_add",
            datatype="GPString",
            parameterType="Required",
            direction="Input",
            multiValue=True)
        
        param1.filter.type = "ValueList"
        param1.filter.list = [v for v in DATATABLES] #In the event some strange behavior is incurred in this class, instead just copy values

        #Coordinate system for spatial layers
        param2 = arcpy.Parameter(
            displayName="Coordinate system",
            name="crs",
            datatype="GPCoordinateSystem",
            parameterType="Required",
            direction="Input")


        #Whether or not to enforce relational integrity
        param3 = arcpy.Parameter(
            displayName="Enforce relational integrity?",
            name="doEnforceRelationalIntegrity",
            datatype="GPBoolean",
            parameterType="Optional",
            direction="Input"
        )
        param3.value = False

        params = [param0, param1, param2, param3]
        return params

    def isLicensed(self):
        """Set whether the 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."""
        return

    def updateMessages(self, parameters):
        """Modify the messages created by internal validation for each tool
        parameter. This method is called after internal validation."""

        if not os.path.splitext(parameters[0].valueAsText)[-1] == ".gdb":
            parameters[0].setWarningMessage("When working in ArcPro please create a geodatabase (.gdb) for this output")
        return

    def execute(self, parameters, messages):
        """The source code of the tool."""
        db_path = parameters[0].valueAsText
        table_names = parameters[1].valueAsText.split(";")
        arcCRS = parameters[2].valueAsText #This comes in as a string representation of an arcpy CRS object
        doEnforceRelationalIntegrity = parameters[3].value #AsText.lower() in ['true','1','t','y','yes'] #the result of me not having confidence in arc parameters - is a 

        #get EPSG code from the input CRS
        spatial_ref=arcpy.SpatialReference()
        spatial_ref.loadFromString(str(arcCRS))
        epsg=spatial_ref.factoryCode

        #Actually call the build_database function
        bd.build_database(db_path,table_names,epsg,doEnforceRelationalIntegrity=doEnforceRelationalIntegrity)    
        return

    def postExecute(self, parameters):
        """This method takes place after outputs are processed and
        added to the display."""
        return

class ValidateDatabase:
    def __init__(self):
        """Define the tool (tool name is the name of the class)."""
        self.label = "Validate database"
        self.description = "This is a simple tool for validate a database to a specific data standard. In its current state it can produce simple text reports of where the standard deviates from requirements.‌"

    def getParameterInfo(self):
        """Define the tool parameters."""
        #Path to database
        param0 = arcpy.Parameter(
            displayName="Database to validate",
            name="database_path",
            datatype="DEWorkspace",
            parameterType="Required",
            direction="Input")
        
        param0.filter.list = ["Local Database"]

        #Error log output file (optional)
        param1 = arcpy.Parameter(
            displayName="Error log (optional)",
            name="error_log",
            datatype="DEFile",
            parameterType="Optional",
            direction="Output")
        
        param0.filter.list = ['txt']

        params = [param0, param1]
        return params

    def isLicensed(self):
        """Set whether the 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."""
        return

    def updateMessages(self, parameters):
        """Modify the messages created by internal validation for each tool
        parameter. This method is called after internal validation."""
        return

    def execute(self, parameters, messages):
        db_path = parameters[0].valueAsText
        outfile = None
        doLog = False
        if not parameters[1].value is None:
            outfile = parameters[1].valueAsText
            doLog = True

        #Ensure there is a database at the specified path
        assert os.path.exists(db_path), f"Could not identify a dataset located at {db_path}"

        #Run the validation
        errs, excpts = vd.validate_database(db_path)

        #Report the results encountered during the validation to the script tool window
        #both errors (e.g., problems with the data) and exceptions (e.g., problems during rule evaluation)
        if len(excpts) > 0:
            arcpy.AddError('{} Exceptions encountered during evalution. See output log'.format(len(excpts)))

        if len(errs) > 0:
            arcpy.AddWarning('{} Validation errors encountered, see output log.'.format(len(errs)))

        #Add a little whimsy for a job well done
        if len(errs)==0 and len(excpts)==0:
            excpts.append('No issues found with this database! Great job! ヘ( ^o^)ノ＼(^_^ ).')

        #TODO: This is a kludge for the time being to do some early testing. 
        #Can clean up error reports a bit more later, but want to get something out the door
        #for testing
        if doLog:
            with open(outfile,'w',encoding="utf-8") as f:
                if len(excpts)>0:
                    f.write('The following exceptions were encountered during validation: \n')
                    f.write('_____________________________________________________ \n')
                    for excpt in excpts:
                        f.write(excpt)
                
                if len(errs)>0:
                    f.write('The following validation errors were observed during validation: \n')
                    f.write('_____________________________________________________ \n')
                    for err in errs:
                        f.write(err.get_printable_error())


        if len(excpts)>0:
            arcpy.AddMessage('The following exceptions were encountered during validation: \n')
            arcpy.AddMessage('_____________________________________________________ \n')
            for excpt in excpts:
                arcpy.AddMessage(excpt)

        if len(errs)>0:
            arcpy.AddMessage('The following validation errors were observed during validation: \n')
            arcpy.AddMessage('_____________________________________________________ \n')
            for err in errs:
                arcpy.AddMessage(err.get_printable_error())
        return

    def postExecute(self, parameters):
        """This method takes place after outputs are processed and
        added to the display."""
        return

class AddDomains:
    def __init__(self):
        """Define the tool (tool name is the name of the class)."""
        self.label = "Update/Apply domains to fields"
        self.description = "This table creates and applies domains to certain fields defined as having relationships to other fields. This helps maintain relational integrity during data creation, and makes data entry a bit simpler.  Domains can be 'refreshed' by re-applying this tool (e.g., after adding more data sources to the datasources table)."

    def create_domain_from_table(self, gdb:str,table:str,key_field:str,value_field:str) -> str:
        """Create and populate a domain in the geodatabase from a table.

        Args:
            gdb (str): Path to the geodatabase
            table (str): name of the table containing the domain values
            key_field (str): name of the field containing the domain keys
            value_field (str): name of the field containing the domain values

        Returns:
            str: The name of the created domain.
        """

        domain_name = f"{table}_{key_field}_domain"

        #Query out any places where the key or value field is null
        arcpy.management.MakeTableView(os.path.join(gdb, table), "temp_view",
                                    "{} IS NOT NULL AND {} IS NOT NULL".format(key_field, value_field))
        
        #Create the domain (replacing existing values) from the not-null values in the table
        arcpy.management.TableToDomain("temp_view",
                                    key_field,
                                    value_field,
                                    gdb,
                                    domain_name,
                                    f"Domain based on table: {table}",
                                    "REPLACE")
        return domain_name


    def apply_domain_to_fields(self,gdb:str, field:str, tables_to_check:list[str]):
        """Creates and applies domains to all fields named 'field' contained across
        a list list of tables.

        Args:
            gdb (str): The path to the geodatabase
            field (str): The name of the field to apply the domain to
            tables_to_check (list[str]): A list of table paths to check for the field
        """

        parent_table, key_field, value_field = DOMAIN_MAPPING[field]

        #Create the domain
        self.create_domain_from_table(gdb, parent_table, key_field, value_field)

        # arcpy.AddMessage('Planning to check tables {}'.format(tables_to_check))

        #Apply the domain to the specified field in all tables
        for table in tables_to_check:
            # arcpy.AddMessage(f"Checking table: {table}")
            if os.path.split(table)[-1] != parent_table:
                #Get all the fields in this table
                fields = [f.name for f in arcpy.ListFields(os.path.join(gdb, table))]
                arcpy.AddMessage(f"Fields in table {table}: {fields}")
                #If the field is present, apply the domain
                if field in fields:
                    arcpy.AddMessage(f"Applying domain '{parent_table}_{key_field}_domain' to field '{field}' in table '{table}'")
                    arcpy.management.AssignDomainToField(os.path.join(gdb, table), field, f"{parent_table}_{key_field}_domain")

    def getParameterInfo(self):
        """Define the tool parameters."""
        #Path to database
        param0 = arcpy.Parameter(
            displayName="Database to add domains to",
            name="database_path",
            datatype="DEWorkspace",
            parameterType="Required",
            direction="Input")
        
        param0.filter.list = ["Local Database"]

        #Tables to add to database
        param1 = arcpy.Parameter(
            displayName="Fields to domain-ify",
            name="fields",
            datatype="GPString",
            parameterType="Required",
            direction="Input",
            multiValue=True)
        
        param1.filter.type = "ValueList"
        param1.filter.list = [v for v in DOMAINABLE_FIELDS] #In the event some strange behavior is incurred in this class, instead just copy values

        params = [param0, param1]
        return params

    def isLicensed(self):
        """Set whether the 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."""
        return

    def updateMessages(self, parameters):
        """Modify the messages created by internal validation for each tool
        parameter. This method is called after internal validation."""
        return

    def execute(self, parameters, messages):
        """The source code of the tool."""
        db_path = parameters[0].valueAsText
        fields_to_domain = parameters[1].valueAsText.split(";")
        
        #Get tables and feature classes in the geodatabase... really? This is the simplest way to do this...?
        tables_to_check = [] #Initialize an empty list
        for dirpath, dirnames, filenames in arcpy.da.Walk(db_path, datatype=["Table", "FeatureClass"]): #Search all feature classes and tables
            for filename in filenames:
                tables_to_check.append(os.path.join(dirpath, filename)) #Add the full path to the list

        #Apply the domains to the requested fields
        for field in fields_to_domain:
            #Double check that this field has a mapping defined for which table defines domain values and which key/value pair to use
            if field in DOMAIN_MAPPING:
                self.apply_domain_to_fields(db_path, field, tables_to_check)
            else:
                arcpy.AddWarning(f"Field '{field}' does not have a defined domain mapping. Skipping.")

        return

    def postExecute(self, parameters):
        """This method takes place after outputs are processed and
        added to the display."""
        return
    
class AppendDatabase:
    def __init__(self):
        """Define the tool (tool name is the name of the class)."""
        self.label = "Add tables to existing database"
        self.description = "This calls the s3ds 'builddatabase.py' function to add tables to an existing database. It will also add all required dependencies of the requested tables."

    def getParameterInfo(self):
        """Define the tool parameters."""
        #Path to database
        param0 = arcpy.Parameter(
            displayName="Existing Database",
            name="database_path",
            datatype="DEWorkspace",
            parameterType="Required",
            direction="Input")
        
        param0.filter.list = ["Local Database"]
        #Tables to add to database
        param1 = arcpy.Parameter(
            displayName="Tables to include",
            name="tables_to_add",
            datatype="GPString",
            parameterType="Required",
            direction="Input",
            multiValue=True)
        
        param1.filter.type = "ValueList"
        param1.filter.list = [v for v in DATATABLES] #In the event some strange behavior is incurred in this class, instead just copy values

        #Coordinate system for spatial layers
        param2 = arcpy.Parameter(
            displayName="Coordinate system",
            name="crs",
            datatype="GPCoordinateSystem",
            parameterType="Required",
            direction="Input")


        #Whether or not to enforce relational integrity
        param3 = arcpy.Parameter(
            displayName="Enforce relational integrity?",
            name="doEnforceRelationalIntegrity",
            datatype="GPBoolean",
            parameterType="Optional",
            direction="Input"
        )
        param3.value = False

        params = [param0, param1, param2, param3]
        return params
    
    def isLicensed(self):
        """Set whether the 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."""
        return

    def updateMessages(self, parameters):
        """Modify the messages created by internal validation for each tool
        parameter. This method is called after internal validation."""
        return

    def execute(self, parameters, messages):
        """The source code of the tool."""
        db_path = parameters[0].valueAsText
        table_names = parameters[1].valueAsText.split(";")
        arcCRS = parameters[2].valueAsText #This comes in as a string representation of an arcpy CRS object
        doEnforceRelationalIntegrity = parameters[3].valueAsText.lower() in ['true','1','t','y','yes'] #

        #get EPSG code from the input CRS
        spatial_ref=arcpy.SpatialReference()
        spatial_ref.loadFromString(str(arcCRS))
        epsg=spatial_ref.factoryCode

        #Actually call the build_database function
        bd.build_database(db_path,table_names,epsg,doEnforceRelationalIntegrity=doEnforceRelationalIntegrity)    
        return

    def postExecute(self, parameters):
        """This method takes place after outputs are processed and
        added to the display."""
        return