# -*- coding: utf-8 -*-
from ppms.migration import SQLPacket, PacketCategory, \
    VersionType, MinVersionDependency, MaxVersionDependency
from ppms.constants import *
from ppms import ppms

class ReplaceHistogramDIS(SQLPacket):
    """Helper package which replaces the old histogram DIs for resource capacity
    DI023419 / DI002491 and planned effort DI023362 / DI002508 with the
    new DI063981, which combines these histogram bars.
    This will set default (standard) tooltips+effect to the new data fields.
    """
    done_after_success = True
    run_on_startup = False
    category = PacketCategory.CATEGORY_HELPER
    wi_number = 18430

    def get_dependencies(self):
        return(MinVersionDependency(version='39.5.7',
                                    version_type=VersionType.CUSTOMIZING),
               MaxVersionDependency(version='39.5.7',
                                    version_type=VersionType.CUSTOMIZING))

    def fix(self, packet_runner):
        self.sys_license = '011' #ppms.uvar_get('@3')
        self.dbms_timestamp = {DBMS_ORACLE: 'SYSTIMESTAMP',
                          DBMS_MSSQL: 'GETDATE()'}[ppms.db_get_dbms_name()]

        get_df_combinations = """with other_df as
                (select DI000213, DI000306,
                        DI000307, DI001992, DI001706
                    from DT411
                  where DI000213 in ('023419','023362', '002491', '002508')
                 )
                select DT411.DI000306,
                       DT411.DI000307 first_df,
                       other_df.DI000307 second_df,
                       DT411.DI001992 first_df_pos,
                       DT411.DI001706 raster,
                       DT411.DI001995 window,
                       DT411.DI000221 df_behaviour
                from DT411
                join other_df on (-- find a related histogram di in the same da
                                  other_df.DI000306 = DT411.DI000306
                                  and other_df.DI000213 != DT411.DI000213)
                where DT411.DI000213 in ('023419', '023362', '002491', '002508')
                -- check that the raster is the same
                and DT411.DI001706 = other_df.DI001706
                -- change custom cu only
                and DT411.DI001862 = '{sys_license}'
                -- check if the related df is customized directly after the
                -- found df. This makes sure they belong together and removes
                -- the set from the result if it matches by position the other
                -- way round
                and other_df.DI001992 = DT411.DI001992 + 1
                group by DT411.DI000306, DT411.DI000307, other_df.DI000307,
                         DT411.DI001992, DT411.DI001706,
                         DT411.DI001995, DT411.DI000221
                         """.format(**self.__dict__)

        df_combinations = self.select_and_log(get_df_combinations)

        # get standard tooltip
        sql = "select DI001550, DI001549 from dt435 where DI001538='824994'"
        tooltip_i_text = self.select_and_log(sql)

        new_di_id = "063981"
        effect_id = "0110015"
        # replace old DIs with the new one
        dbms_timestamp = self.dbms_timestamp
        sys_license = self.sys_license
        log_summary = {}
        for combination in df_combinations:
            da_id = combination[0]
            first_df_id = int(combination[1])
            second_df_id = int(combination[2])
            first_df_pos = int(combination[3])
            raster = int(combination[4])
            window = int(combination[5])
            df_behaviour = combination[6]

            mv_info = self.get_mv_settings(da_id, new_di_id,
                                              first_df_id, second_df_id)

            # delete old data fields
            delete_old_dfs = """delete from dt411
                            where DI000306='{da_id}'
                            and DI000307 in ('{first_df_id}','{second_df_id}')
                            """.format(**locals())
            self.log('Deleting old data field from data area: ')
            self.modify_and_log(delete_old_dfs)

            # add the new di to the data area where the old ones were found and
            # reuse the df id, df pos and raster
            get_i_text_nr = """select max(DI001538)+1
                               from DT435"""
            i_text_nr = int(ppms.db_select(get_i_text_nr)[0][0])

            for tooltip_info in tooltip_i_text:
                language = tooltip_info[0]
                tooltip_txt = tooltip_info[1]
                create_new_i_text = """insert into dt435
                            (DI001538, DI001550,
                            DI001539, DI001540, DI001541, DI001542,
                            DI001543, DI001544, DI001545, DI001546,
                            DI001547, DI001548,
                            DI001549) values
                            ('{i_text_nr}', '{language}',
                             {dbms_timestamp}, {dbms_timestamp}, 7, 'MIGRATION',
                             'MIGRATION', '{sys_license}', Null, 7, 7, 7,
                             '{tooltip_txt}')""".format(**locals())
                self.log('Creating new i-text for the tooltip: ')
                self.modify_and_log(create_new_i_text)

            create_new_df = """insert into dt411
                    (DI000306, DI000307, DI000213, DI001992,
                     DI000415, DI000416, DI001859, DI001860, DI001861, DI001862,
                     DI000547, DI001863, DI001864, DI001865,
                     DI001706, DI040918, DI029731, DI001995, DI000221)
                    values
                    ('{da_id}', '{first_df_id}', '{new_di_id}', {first_df_pos},
                     {dbms_timestamp}, {dbms_timestamp}, 7,
                     'MIGRATION', 'MIGRATION', '{sys_license}', Null, 7, 7, 7,
                     {raster}, '{i_text_nr}', '{effect_id}',
                     {window}, '{df_behaviour}')""".format(**locals())

            mod_list = ppms.db_select("select DI000970 "
                                       "from DT406 "
                                       "where DI000969 = '{da_id}'"
                                       "".format(**locals()))
            mod_str = ', '.join(['MOD ' + str(mod_id[0]) for mod_id in mod_list])
            self.log('Creating new data field (DA {da_id}, DF {first_df_id}).' \
                     ' The area is used in: {mod_str}' \
                     ''.format(**locals()))
            for mod_id in mod_list:
                mod_id = mod_id[0]
                log_str = ''
                if mod_id in log_summary.keys():
                    log_str = log_summary[mod_id] + '\n'
                log_summary[mod_id] = '{log_str}  DA {da_id}, DF {first_df_id}' \
                                      ''.format(**locals())
            mod_has_been_changed = True
            self.modify_and_log(create_new_df)

            self.recreate_mv_data(mv_info)

        if log_summary:
            log_summary_txt = 'The following modules have been changed:\n'

            for mod_id, df_info  in log_summary.items():
                log_summary_txt = '{log_summary_txt}MOD {mod_id}:\n' \
                                  '{df_info}\n'.format(**locals())

            self.log(log_summary_txt)

        return self.success()

    def recreate_mv_data(self, mv_info):
        '''Re-create the mv data for the new df using the old df's setting'''
        dbms_timestamp = self.dbms_timestamp
        sys_license = self.sys_license
        for mv_data in mv_info:
            da_id = mv_data[0]
            mv_id = mv_data[1]
            df_id = int(mv_data[2])
            mv_setting = mv_data[3]

            sql_create ="""insert into DT503 (DI024189, DI024190, DI024191,
            DI024195, DI024196, DI024197, DI024198, DI024199, DI024200,
            DI024201, DI024202, DI024203, DI024204, DI028074) values
            ('{mv_id}', '{da_id}', {df_id}, {dbms_timestamp}, {dbms_timestamp},
            7, 'MIGRATION', 'MIGRATION', '{sys_license}', Null, 7,7,7,
            '{mv_setting}')""".format(**locals())
            self.modify_and_log(sql_create)

            self.log('New MV datafield setting created: variant: {mv_id} ' \
                     'DA{da_id} DF{df_id} Setting: ' \
                     '{mv_setting}'.format(**locals()))


    def get_mv_settings(self, da_id, new_di_id, first_df_id, second_df_id):
        ''' Checks all module variants which use this df combo and swap safe
        the mv setting to recreate it for the new df. This will only be done if
        the mv setting of both old DIs matches.'''
        get_mvs = """select DI024189
                            from DT503
                            where DI024191 in ('{first_df_id}','{second_df_id}')
                            and DI024190 = '{da_id}'
                            """.format(**locals())
        all_mvs_with_both_df = self.select_and_log(get_mvs)
        mv_info = []
        for mv in all_mvs_with_both_df:
            mv_id = mv[0]
            # check if mv settings of the old histogram DIs matches
            check_setting = """select DI028074, DI024191 from dt503
                                where DI024190 = '{da_id}'
                                and DI024191 in ('{first_df_id}',
                                                 '{second_df_id}')
                                and DI024189 = '{mv_id}'
                                """.format(**locals())
            check_result = self.select_and_log(check_setting)
            if check_result:
                self.log('check_result: ' + str(check_result))
                df_mv_setting_1 = check_result[0][0]

                if len(check_result) == 2:
                    df_mv_setting_2 = check_result[1][0]
                else:
                    # if only one of the searched DIs is used in this mv,
                    # use this setting for the new DI
                    df_mv_setting_2 = df_mv_setting_1

                if df_mv_setting_1 == df_mv_setting_2:
                    # save the mv setting to recreate it for the new di
                    mv_info.append([da_id, mv_id, first_df_id, df_mv_setting_1])
                else:
                    self.log("The module variant data of mv {mv_id} for data "
                             "area {da_id} could not be migrated to the new df,"
                             "as the settings don't match for both histogram "
                             "DIs. Please create your own "
                             "setting".format(**locals()))

            self.delete_df_mv_setting(da_id, (first_df_id, second_df_id))

        self.log('mv_info: ' + str(mv_info))
        return mv_info

    def delete_df_mv_setting(self, da_id, df_ids):
        # delete old data fields MV settings
        delete_old_mv_data = """delete from dt503
                                  where DI024190 = '{da_id}'
                                  and DI024191 in {df_ids}
                                  """.format(**locals())
        self.log('Deleting old data field variant data: ')
        self.modify_and_log(delete_old_mv_data)