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

class ReworkSessionTablesVoE(SQLPacket):
    '''Remove superfluous columns in session tables'''
    version = 1
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 13126

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

    def fix(self, runner):
        queries = ("""DELETE FROM DT412 WHERE DI000238 IN ('001605', '001609', '001610', '001611', '001612',
                                                           '026207', '026208', '026209', '026210', '026219',
                                                           '026220', '026221', '026222', '010632', '059712',
                                                           '059339', '004359', '004364', '004365', '004361',
                                                           '004357', '004358', '004360', '004362', '004363',
                                                           '021885', '021886', '021887', '021888', '022963',
                                                           '022964', '022751', '004389', '004391', '004393',
                                                           '004390', '026762', '010669', '010668', '010596',
                                                           '010597', '010598', '010599', '001607')""",
                   """UPDATE DT415 SET DI000928 = 0 WHERE DI000265 IN ('324', '443')""")
        to_drop = {'DT324': ('DI059712', 'DI059339', 'DI004359', 'DI004364', 'DI004365', 'DI004361',
                             'DI004357', 'DI004358', 'DI004360', 'DI004362', 'DI004363'),
                   'DT443': ('DI001605', 'DI001607', 'DI001609', 'DI001610', 'DI001611', 'DI001612'),
                   'DT511': ('DI010632',)}

        for query in queries:
            self.modify_and_log(query)
        for dt, dis in to_drop.items():
            for di in dis:
                self.modify_and_log("""ALTER TABLE %s DROP COLUMN %s""" % (dt, di))
        return self.success()

class ReworkSessionTables(SQLPacket):
    '''Rename former object protection fields in session tables

    CAUTION: also changes names/Python-IDs of some former object protection fields
    '''
    version = 1
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 13126

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

    def fix(self, runner):
        descriptions = {'001603':{'DE':('Datum', 'Startdatum'),
                                  'EN':('Date', 'Start date'),
                                  'FR':('Date', '???'),
                                  'PT':('Data', '???')},
                        '001604':{'DE':('Geändert am', 'Enddatum'),
                                  'EN':('Modified on', 'End date'),
                                  'FR':('Date de modif.', '???'),
                                  'PT':('Data modif.', '???')},
                        '001606':{'DE':('Angelegt von', 'Benutzer'),
                                  'EN':('User', 'User'),
                                  'FR':('Utilisateur', 'Utilisateur'),
                                  'PT':('Usuário', 'Usuário')},
                        '001608':{'DE':('Owner-Lizenz', 'Lizenz'),
                                  'EN':('Own. lic.', 'License'),
                                  'FR':('Lic.prop.', '???'),
                                  'PT':('Own.lic.', '???')}}
        python_ids = {'001603': ('date', 'start_date'),
                      '001604': ('modified_on', 'end_date'),
                      '001606': ('created_by', 'user'),
                      '001608': ('own_lic', 'license')}

        for id, desc in descriptions.items():
            for lang, (text_old, text_new) in desc.items():
                itext_nr = int(ppms.db_select("""SELECT DI001591 FROM DT412 WHERE DI000238 = '%s'""" % id)[0][0])
                self.modify_and_log("""UPDATE DT435 SET DI001549 = '%s' WHERE DI001538 = %i AND DI001550 = '%s'""" % (text_new, itext_nr, lang))
        for id, (python_id_old, python_id_new) in python_ids.items():
            self.modify_and_log("""UPDATE DT412 SET DI041035 = '%s' WHERE DI000238 = '%s' AND DI041035 = '%s'""" % (python_id_new, id, python_id_old))        
        return self.success()