# -*- coding: utf-8 -*-
from ppms import ppms
from ppms.migration.packet import *
import os


class ChangeDiOfDtByDdiBackgroundHeadersPortfolio(SQLPacket):
    """
    Changes the effect and symbol of background datafields in custom portfolio data areas (DT090 and DT870) from blue bar to green border
    """
    '''
    Replace any value of any data item in any data field of any data area tied to any data table for any license
    its rather generic
    to use this change the changes list
    the "content" dictionary at the end is {di_of_DT411:[old value,new value]}
    changes = [{"license":"011","DI":"025581","DDI_DTs":["090","870"],"content":{"000234":["001937","001965"]}}]
    you can put as many changes as you like into the changes list
    if you use non existant values or the package cant find any entries it will abort.
    Use the other packet named *_revert to revert this package
    *_revert will be automatically generated on run.
    !!DO NOT run this package twice!!
    '''
    wi_number = 19955  # The workitem number this packet belongs to. Only relevant for packets by PLANTA
    version = 0
    done_after_success = True  # Sets the "Erledigt" flag after running successfully
    run_on_startup = False  # Runs this migration packet when the server is started in migration mode
    category = PacketCategory.CATEGORY_HELPER  # Categorizes the packet


    changes = [{"license":"0","DI":"025581","DDI_DTs":["090","870"],"content":{"000234":["001937","002130"],"029731":["0110004","0110017"]}}]

    def fix(self,runner):
        self.fill_changes_dicts_with_active_license()
        try:
            self.update_DFs_specified()
        except NoDFsFound as no_dfs:
            self.new_revert_sql_file()
            self.log(no_dfs)
        except Exception as error:
            self.new_revert_sql_file()
            self.log(error)
            return self.fail('Encountered errors')

        return self.success()

    def get_DDIs_for_DTs(self,ddi_dt_list):
        return ppms.db_select(
            """select DI000238 from DT412 where DI000239 in {dts}""".format(dts=ddi_dt_list).replace('[','(').replace(
                ']',')'))

    def get_DAs_for_DDIs(self,ddi_list):
        return ppms.db_select(
            """select DI000198 from DT410 where DI000391 in {ddis}""".format(ddis=ddi_list).replace('[','(').replace(
                ']',')'))

    def get_DFs_for_DAs(self,da_list,di,license,conditions):
        maybe_and = ""
        if len(conditions) > 0:
            maybe_and = "and"
        return ppms.db_select(
            """select DI000307,DI000306 from DT411 where DI000306 in {das} and DI000213 = {di} and DI001862 = {license} {maybe_and} {old_values}""".format(
                das=da_list,di=di,license=license,maybe_and=maybe_and,old_values=conditions).replace('[','(').replace(
                ']',')'))

    def get_DFs_needed(self,ddi_dt_list,di,license,content_dict):
        ddi_list = self.get_DDIs_for_DTs(ddi_dt_list=ddi_dt_list)
        da_list = self.get_DAs_for_DDIs(ddi_list=ddi_list)
        conditions = self.make_content_conditions(content_dict)
        return self.get_DFs_for_DAs(da_list=da_list,di=di,license=license,conditions=conditions)

    def make_content_conditions(self,content_dict):
        template = """DI{di} = '{value}'"""
        toreturn = """"""
        for di,value in content_dict.items():
            toadd = template.format(di=di,value=value[0])
            toreturn += toadd
            toreturn += " and "
        return toreturn[:-5]

    def make_condition(self,dfs_needed):
        template = """(DI000307 = '{df}' and DI000306 = '{da}') or """
        toreturn = """ where """
        for df in dfs_needed:
            toreturn += template.format(df=int(df[0]),da=df[1])
        return toreturn[:-4]

    def make_sets_statement(self,content_dictionary):
        toreturn = """set """
        template = """DI{dis} = '{contents}', """
        for di_411,content in content_dictionary.items():
            toreturn += template.format(dis=di_411,contents=content[1])
            #self.log(toreturn)
        return toreturn[:-2]

    def make_update_statement(self,condition,set_statement):
        return """update dt411 """ + set_statement + condition

    def update_DFs_specified(self):
        self.new_revert_sql_file()
        for change in self.changes:
            content_dictionary = change["content"]
            owner_license = change["license"]
            di_to_look_for = change["DI"]
            ddi_dts = change["DDI_DTs"]
            set_statement = self.make_sets_statement(content_dictionary)
            revert_set_statement = self.make_revert_set_statement(content_dictionary)
            dfs_needed = self.get_DFs_needed(ddi_dts,di_to_look_for,owner_license,content_dictionary)
            #self.raise_error_and_fail_on_no_dfs(dfs_needed)
            self.raise_error_and_dont_fail_if_no_dfs(dfs_needed)
            conditions = self.make_condition(dfs_needed)
            update_statement = self.make_update_statement(conditions,set_statement)
            revert_update_statement = self.make_update_statement(conditions,revert_set_statement)
            self.modify_and_log(update_statement)
            self.write_revert_sql_file(revert_update_statement)
            self.easily_readable(dfs_needed,content_dictionary)
        self.make_revert_migration_packet()
        self.push_button_to_refresh()

    def make_revert_set_statement(self,content_dictionary):
        revert_content_dictionary = {}
        for di,old_new_value in content_dictionary.items():
            revert_content_dictionary[di] = [old_new_value[1],old_new_value[0]]
        return self.make_sets_statement(revert_content_dictionary)

    def new_revert_sql_file(self):
        file_name = os.path.join(os.path.dirname(__file__),"revert_"+self.__name__ + ".sql")
        with open(file_name,'w') as sql_file:
            sql_file.write("")

    def write_revert_sql_file(self,revert_statement):
        file_name = os.path.join(os.path.dirname(__file__),"revert_"+self.__name__ + ".sql")
        revert_statement += ";\n"
        with open(file_name,'a') as sql_file:
            sql_file.write(revert_statement)
        self.log("created sql file {name}".format(name="revert_" + self.__name__ + ".sql"))

    def make_revert_migration_packet(self):
        revert_python_script = """
# -*- coding: utf-8 -*-
from ppms import ppms
from ppms.migration.packet import *
import os

class revert_{name}(SQLPacket):
    '''
    this packet reverts {name}
    '''
    version = {version}
    done_after_success = True
    run_on_startup = False
    category = PacketCategory.CATEGORY_HELPER

    def fix(self,runner):
        try:
            queries = self.get_queries_from_file(os.path.join(os.path.dirname(__file__),self.__name__ +  ".sql"))
            for query in queries:
                self.modify_and_log(query)
        except Exception as error:
            self.log(error)
            return self.fail('Encountered errors')

        return self.success()
"""

        path = os.path.join(os.path.dirname(__file__),self.__name__ + "_revert" + ".py")

        with open(path,'w') as file:
            file.write(revert_python_script.format(name=self.__name__,version=self.version))
        self.log("created migration package {name}".format(name = "revert_"+self.__name__))

    def easily_readable(self,DFs,content_dictionary):
        template = " {di} has been changed from {old} to {new}"
        di_string = ""
        for di,old_new_value in content_dictionary.items():
            di_string += template.format(di=di,old=old_new_value[0],new=old_new_value[1])
        for df in DFs:
            df[0] = str(int(df[0]))
            tolog = "DF {df} -".format(df=df) + di_string
            self.log(tolog)


    def push_button_to_refresh(self):
        for panel in ppms.get_panels():
            for module in panel.get_modules():
                if module.get_id() == "009CEP":
                    module.get_da_by_id("055114").get_records()[0].button.activate()
                    return


    def raise_error_and_fail_on_no_dfs(self,dfs_needed):
        if len(dfs_needed) == 0:
            raise ValueError("no datafields found. Something in your list of changes must be wrong!")

    def get_active_license(self):
        l = ppms.uvar_get("@3")
        self.log("license = %s"%l)
        return l

    def fill_changes_dicts_with_active_license(self):
        license = self.get_active_license()
        new_changes = []
        for change in self.changes:
            change["license"] = license
            new_changes.append(change)
        self.changes = new_changes

    def raise_error_and_dont_fail_if_no_dfs(self,dfs_needed):
        if len(dfs_needed) == 0:
            raise NoDFsFound("No DFs found to be changed")


class NoDFsFound(Exception):
    pass