# -*- coding: utf-8 -*-
import os

from ppms import ppms
from ppms.migration import SQLPacket, PacketCategory, MinVersionDependency, MaxVersionDependency, SucceedOldPacket, \
    BasePacket, StopMigrationProcessException


MODULES = "('009CEP', '009CEO', '009CF5', '009CF3', '009CEQ', '009CEV', '009CL9', '009BXV')"
SYMBOLS = "('001280', '001797', '002021', '002047', '002084', '001274', '001423', '002056', '002121', '001967', " \
          "'001966', '001965', '001966', '001964', '002119', '002120')"
OLES = "('001397', '001394', '001395', '001396', '001393')"
LISTBOX_VALUES = "('356', '357', '358', '359', '483', '484')"

DELETE_STATEMENTS = [# Dataitems/Datatable
                     "DELETE FROM DT412 WHERE DI000239 IN (314, 315)",
                     "DELETE FROM DT440 WHERE DI000286 IN (SELECT DI000238 FROM DT412 WHERE DI000239 IN (314, "
                     "315)) OR DI000364 IN (SELECT DI000238 FROM DT412 WHERE DI000239 IN (314, 315))",
                     "DELETE FROM DT435 WHERE DI023517 = '001591' AND DI001538 IN (SELECT DI001591 FROM DT412 WHERE "
                     "DI000239 IN (314, 315))",
                     "DELETE FROM DT435 WHERE DI023517 = '001571' AND DI001538 IN (SELECT DI001571 FROM DT415 WHERE "
                     "DI000265 IN (314, 315))",
                     "DELETE FROM DT415 WHERE DI000265 IN (314, 315)",
                     "DELETE FROM DT037 WHERE DI025503 IN (SELECT DI000238 FROM DT412 WHERE DI000239 IN (314, 315))",
                     "DELETE FROM DT416 WHERE DI000389 IN (314, 315)",
                     # MCU
                     "DELETE FROM DT435 WHERE DI023517 = '010200' AND DI001538 IN (SELECT DI010200 FROM DT411 WHERE "
                     "DI000306 IN (SELECT DI000969 FROM DT406 WHERE DI000970 IN %s))" % MODULES,
                     "DELETE FROM DT435 WHERE DI023517 IN ('001589', '029710') AND DI001538 IN ((SELECT DI001589 "
                     "FROM "
                     "DT410 WHERE DI000198 IN (SELECT DI000969 FROM DT406 WHERE DI000970 IN %s)) UNION (SELECT "
                     "DI029710 "
                     "FROM DT410 WHERE DI000198 IN (SELECT DI000969 FROM DT406 WHERE DI000970 IN %s)))" % (
                         MODULES, MODULES),
                     "DELETE FROM DT435 WHERE DI023517 IN ('001588') AND DI001538 IN (SELECT DI001588 FROM DT405 "
                     "WHERE DI000123 IN %s)" % MODULES,
                     "DELETE FROM DT435 WHERE DI023517 IN ('057816') AND DI001538 IN (SELECT DI057816 FROM DT405 "
                     "WHERE DI000123 IN %s)" % MODULES,
                     "DELETE FROM DT411 WHERE DI000306 IN (SELECT DI000969 FROM DT406 WHERE DI000970 IN %s)" %
                     MODULES,
                     "DELETE FROM DT410 WHERE DI000198 IN (SELECT DI000969 FROM DT406 WHERE DI000970 IN %s)" %
                     MODULES,
                     "DELETE FROM DT406 WHERE DI000970 IN %s" % MODULES,
                     "DELETE FROM DT404 WHERE DI028100 IN %s OR DI028040 IN %s" % (MODULES, MODULES),
                     "DELETE FROM DT405 WHERE DI000123 IN %s" % MODULES,
                     # New symbol
                     "DELETE FROM DT435 WHERE DI023517 = '001573' AND DI001538 IN (SELECT DI001573 FROM DT424 WHERE "
                     "DI000636 IN %s)" % SYMBOLS,
                     "DELETE FROM DT424 WHERE DI000636 IN %s" % SYMBOLS,
                     # OLE
                     "DELETE FROM DT435 WHERE DI023517 = '056987' AND DI001538 IN (SELECT DI056987 FROM DT031 WHERE "
                     "DI026133 IN %s)" % OLES,
                     "DELETE FROM DT435 WHERE DI023517 = '026171' AND DI001538 IN (SELECT DI026171 FROM DT031 WHERE "
                     "DI026133 IN %s)" % OLES,
                     "DELETE FROM DT031 WHERE DI026133 IN %s" % OLES,
                     # Listbox Values
                     "DELETE FROM DT435 WHERE DI023517 = '041276' AND DI001538 IN (SELECT DI041276 FROM DT447 WHERE "
                     "DI041264 IN %s)" % LISTBOX_VALUES,
                     "DELETE FROM DT435 WHERE DI023517 = '041277' AND DI001538 IN (SELECT DI041277 FROM DT447 WHERE "
                     "DI041264 IN %s)" % LISTBOX_VALUES,
                     "DELETE FROM DT447 WHERE DI041264 IN %s" % LISTBOX_VALUES,
]


class A_ReplaceOldFrameworkCustomizing(SQLPacket):
    """Replace the old framework customizing with the new"""
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 15618

    OLES = {'001397': 'lamp_white.png',
            '001394': '0131-lamp_green.png',
            '001395': '0132-lamp_red.png',
            '001396': 'lamp_gray.png',
            '001393': '0130-lamp.png'}

    FILENAME = 'wi15618_new_migration_framework.sql'

    def get_dependencies(self):
        return (MinVersionDependency(version='39.4'),
                MaxVersionDependency(version='39.4'))

    def log_deletion_candidates(self, message):
        self.log(message)
        select_statements = [delete.replace('DELETE FROM', 'SELECT UUID FROM') for delete in DELETE_STATEMENTS]
        for statement in select_statements:
            self.log(statement)
            result_set = ppms.db_select(statement)
            for row in result_set:
                self.log(str(row[0]).replace("UUID('", "").replace("')", ""))

    def fix(self, packet_runner):
        import_started = False
        try:
            self.log_deletion_candidates("What we find before cleanup:")

            self.log('Wipe old migration framework components')
            for statement in DELETE_STATEMENTS:
                self.modify_and_log(query=statement)

            self.log_deletion_candidates("What we find after cleanup / before import:")

            self.log('Import new migration framework components')
            import_started = True

            # Use apply_rollback_ddl_file since it ignores exceptions
            self.apply_rollback_ddl_file(self.sql_planta_de_dbms, self.FILENAME, split=True)
        except BaseException as err:
            if import_started:
                self.log_deletion_candidates("Let's see if any of the supposedly deleted rows are still in our way:")
            text = 'Stopping migration due to error in CRITICAL packet!'
            raise StopMigrationProcessException(text)

        self.replace_oles()

        return self.success()

    def replace_oles(self):
        errors = 0

        for ident, filename in self.OLES.items():
            f_path = os.path.join(os.path.dirname(__file__), filename)
            ole_rec = ppms.search_record(31, [ident], ['standard_ole'])

            hl_di = ole_rec.standard_ole
            hyperlink = hl_di.get_hyperlink()
            if hyperlink is None:
                self.log('Inserting new hyperlink')
                hl_di.insert_hyperlink()
                hyperlink = hl_di.get_hyperlink()
            else:
                self.log('Using existing hyperlink')

            with open(f_path, 'rb') as f:
                self.log('Changing OLE %s' % ident)
                barray = bytearray(f.read())
                if not hyperlink.set_value(binary=barray, url=filename):
                    self.log('Could not write hyperlink!!')
                    errors += 1
                    continue

                if not ole_rec.save():
                    self.log('Could not save!!')
                    errors += 1

        return errors == 0


class B_SucceedPreviousMigrationFramework(SucceedOldPacket):
    """Mark previous packets for the migration framework as done"""
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 15618

    PORT_VALUERANGES = r'migration.hotfix.S_39_5_11.wi12687_new_migration_packets.C_PortMigrationValueranges'
    REMOVE_OLD_MODULE = r'migration.hotfix.S_39_5_11.wi12687_new_migration_packets.D_RemoveOldModuleFromUserMenu'

    def fix(self, packet_runner):
        self.mark_packet_as_done(self.PORT_VALUERANGES)

        module_remover_packet = BasePacket.from_packet_path(packet_path=self.REMOVE_OLD_MODULE)
        if not module_remover_packet.done:
            module_remover_packet.fix(packet_runner)
            module_remover_packet.save_history()

        return self.success()