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

from ppms import ppms
from ppms.ppms_ import DatabaseError
from ppms.migration import SQLPacket, PacketCategory, MinVersionDependency, MaxVersionDependency, VersionType, \
                            PacketDependency, PacketState

USAGE_QUERIES = {'USERS':"""SELECT COUNT(*)
                             FROM DT511
                            WHERE
                             DI025834 = '{id}' OR
                             DI025835 = '{id}' OR
                             DI025836 = '{id}'""",
                 'SKINS':"""SELECT COUNT(*)
                             FROM DT030
                            WHERE
                             DI025692 = '{id}' OR
                             DI025693 = '{id}' OR
                             DI025694 = '{id}'""",
                 'DATAITEMS':"""SELECT COUNT(*)
                                 FROM DT412
                                WHERE
                                 DI000257 = '{id}'""",
                 'DATAFIELDS':"""SELECT COUNT(*)
                                  FROM DT411
                                 WHERE
                                  DI000258 = '{id}'"""}

class A_FixTestFormats(SQLPacket):
    '''Delete unused test-formats'''
    version = 1
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 9718

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

    def fix(self, runner):
        formats = ('000060', '000212', '000217', '000218')
        usage = []
        errors = 0

        for area, query in USAGE_QUERIES.items():
            for format in formats:
                if ppms.db_select(query.format(id=format))[0][0]:
                    usage.append(format)
                    self.log("Format '{format}' is in use in the following area: '{area}'".format(format=format, area=area))

        for format in formats:
            if format not in usage:
                if not self.modify_and_log("DELETE FROM DT408 WHERE DI000165 = '{format}'".format(format=format)):
                    errors += 1

        if not errors:
            return self.success()
        return self.fail()


class B_FixDuplicateFormats(SQLPacket):
    '''Replace duplicate formats and deletes the unused ones'''
    version = 1
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 9718

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

    def fix(self, runner):
        dataitems = {'DT511':('DI025834', 'DI025835', 'DI025836'),
                     'DT030':('DI025692', 'DI025693', 'DI025694'),
                     'DT412':('DI000257',),
                     'DT411':('DI000258',)}
        formats = {'000003':('000137',),
                   '000065':('000073',),
                   '000070':('000120',),
                   '000158':('000159', '000161'),
                   '000126':('000127',),
                   '000129':('000154',),
                   '000215':('000216',)}
        in_use = []
        to_delete = []
        errors = 0

        for dt, dis in dataitems.items():
            for di in dis:
                for format, duplicates in formats.items():
                    for duplicate in duplicates:
                        if ppms.db_select("SELECT COUNT(*) FROM {dt} WHERE {di} = '{duplicate}'".format(dt=dt, di=di, duplicate=duplicate))[0][0]:
                            if duplicate not in in_use:
                                in_use.append(duplicate)
                            if self.modify_and_log("UPDATE {dt} SET {di} = '{format}' WHERE {di} = '{duplicate}'".format(dt=dt, di=di, format=format, duplicate=duplicate)):
                                if duplicate not in to_delete:
                                    to_delete.append(duplicate)
                            else:
                                errors += 1
        for duplicates in formats.values():
            for duplicate in duplicates:
                if duplicate not in in_use and duplicate not in to_delete:
                    to_delete.append(duplicate)
        for duplicate in to_delete:
            if not self.modify_and_log("DELETE FROM DT408 WHERE DI000165 = '{duplicate}'".format(duplicate=duplicate)):
                errors += 1

        if not errors:
            return self.success()
        return self.fail()


class FixFormatLength(SQLPacket):
    '''Adjust formats that have an insufficient length'''
    version = 1
    done_after_success = False
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 9718

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

    def fix(self, runner):
        query = "SELECT DI000165 FROM DT408 WHERE DI000192 = '{id}'"
        types = {'time':'T',
                 'number':'N',
                 'currency':'W'}
        errors = 0

        for type, id in types.items():
            formats = [item for sublist in ppms.db_select(query.format(id=id)) for item in sublist]
            for format in formats:
                rec = ppms.search_record(408, [format], [], True)
                children = rec.get_children(409)
                length = 0
                for child in children:
                    length += child.length.get_value()
                    separator = child.separator.get_value()
                    if separator and separator != '@':
                        length += len(separator)
                    elif not separator and len(children) > 1:
                        length += 1
                if length > rec.total_length.get_value():
                    if not self.modify_and_log("UPDATE DT408 SET DI000193 = {length} WHERE DI000165 = '{format}'".format(length=length, format=format)):
                        errors += 1

        if not errors:
            return self.success()
        return self.fail()

class C_FixFormatDescriptions(SQLPacket):
    '''Fix descriptions that don't match their format'''
    version = 1
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 9718

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

    def fix(self, runner):
        descriptions = {'000143':{'DE':('4 Stellen', '2 Nachkommastellen - 9 Mrd.'),
                                  'EN':('4 digits', '2 dec.places - 9 bn.'),
                                  'FR':('4 chiffres', '2 AV - 9 milliards')},
                        '000172':{'DE':('% ohne NK bis 999', '% ohne NK bis 9999'),
                                  'EN':('% up to 999 w/o dec.places', '% up to 9999 w/o dec.places'),
                                  'FR':('% sans chif. AV jusque 999', '% sans chif. AV jusque 9999')},
                        '000210':{'DE':('J mit 4 Stellen & 1NK', 'J mit 4 Stellen & 2 NK'),
                                  'EN':('Yr with 4 digits & 1 after pt.', 'Yr with 4 digits & 2 after pt.'),
                                  'FR':('A avec 4 chiffres & 1 chif. AV', 'A avec 4 chiffres & 2 chif. AV')},
                        '000213':{'DE':('h mit 3 Stellen ohne NK', 'h mit 3 Stellen & 3 NK'),
                                  'EN':('Hrs, 3 digits, no dec.places', 'Hrs, 3 digits, 3 dec.places'),
                                  'FR':('h à 3 positions sans AV', 'h à 3 positions & 3 AV')},
                        '000214':{'DE':('h mit 3 Stellen ohne NK', 'h mit 4 Stellen ohne NK'),
                                  'EN':('Hrs, 3 digits, no dec.places', 'Hrs, 4 digits, no dec.places'),
                                  'FR':('h à 3 positions sans AV', 'h à 4 positions sans AV')},
                        '000215':{'DE':('PT mit 3 Stellen (8h)', 'PT mit 5 Stellen'),
                                  'EN':('PD with 3 dig. (8h)', 'PD with 5 dig.'),
                                  'FR':('EP à 3 positions (8h)', 'EP à 5 positions')},
                        '000220':{'DE':('PT mit 3 Stellen (8h)', 'PT mit 5 Stellen & 2 NK'),
                                  'EN':('PD with 3 dig. (8h)', 'PD with 5 dig. & 2 dec.places'),
                                  'FR':('EP à 3 positions (8h)', 'EP à 5 positions & 2 AV')},
                        '000232':{'DE':('km mit 3 Stellen ohne NK', 'km mit 4 Stellen ohne NK'),
                                  'EN':('km, 3 digits, no dec.places', 'km, 4 digits, no dec.places'),
                                  'FR':('km à 3 positions sans AV', 'km à 4 positions sans AV')},
                        '000245':{'DE':('s ohne NK bis 999', 's ohne NK bis 99 Tsd.'),
                                  'EN':('s w/o decimal places to 999', 's w/o decimal places to 99999'),
                                  'FR':('h sans AV jusque 999', 's sans AV jusque 99999')},
                        '000248':{'DE':('% ohne NK bis 999999 mit 0', '% ohne NK bis 9 Mio. mit 0'),
                                  'EN':('% up to 999999 w/o DP with 0', '% up to 9999999 w/o DP with 0'),
                                  'FR':('% sans chif. AV jus.999999 a.0', '% sans chif. AV jus.9999999 a.0')},
                        '000251':{'DE':('h ohne NK bis 999 MIO 3erGr', 'h ohne NK bis 99 Tsd.'),
                                  'EN':('h w/o decimal places to 999m', 'h w/o decimal places to 99999'),
                                  'FR':('h sans AV jusque 999MIO gr.de3', 'h sans AV jusque 99999')},
                        '000254':{'DE':('% ohne NK bis 999 ohne 0', '% ohne NK bis 9999 ohne 0'),
                                  'EN':('% up to999w/o dec.places w/o 0', '% up to 9999 w/o dec.places w/o 0'),
                                  'FR':('% sans chif. AV jqu 999 sans 0', '% sans chif. AV jqu 9999 sans 0')},
                        '000146':{'DE':('DEM o. WZ, 9VK,2NK', 'DEM o. WZ, 11VK, 2NK'),
                                  'EN':('DEM no curr.sym.9bef.2after pt', 'DEM no curr.sym.11bef.2after pt'),
                                  'FR':('DEM ss SM, 9DV, 2AV', 'DEM ss SM, 11DV, 2AV')},
                        '000174':{'DE':('Jahre mit 2 VK & 2NK', 'EUR mit 2 VK & 2NK'),
                                  'EN':('Years with 2bef.dec.pt.2 after', 'EUR with 2bef.dec.pt.2 after'),
                                  'FR':('Année avec 2DV & 2 AV', 'EUR avec 2DV & 2 AV')},
                        '000181':{'DE':('9 Stellen KByte', '9 Stellen EUR'),
                                  'EN':('9 digits kbytes', '9 digits EUR'),
                                  'FR':('9 chiffres Koctets', '9 chiffres EUR')},
                        '000184':{'DE':('EUR 4 VK 2 NK', 'EUR 3 VK 2 NK'),
                                  'EN':('EUR 4 places & 2 dec. pl.', 'EUR 3 places & 2 dec. pl.'),
                                  'FR':('EUR 4 DV 2 AV', 'EUR 3 DV 2 AV')}}
        errors = 0

        for id, desc in descriptions.items():
            for lang, (text_old, text_new) in desc.items():
                itext_nr = int(ppms.db_select("SELECT DI001570 FROM DT408 WHERE DI000165 = '{id}'".format(id=id))[0][0])
                if ppms.db_select("SELECT DI001549 FROM DT435 WHERE DI001538 = {itext_nr} AND DI001550 = '{lang}'".format(itext_nr=itext_nr, lang=lang))[0][0] == text_old:
                    if not self.modify_and_log("UPDATE DT435 SET DI001549 = '{text_new}' WHERE DI001538 = {itext_nr} AND DI001550 = '{lang}'".format(text_new=text_new, itext_nr=itext_nr, lang=lang)):
                        errors += 1

        if not errors:
            return self.success()
        return self.fail()

class D_FixFormatCustomizing(SQLPacket):
    '''Fix messed up format customizing'''
    version = 1
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 9718

    def get_dependencies(self):
        return (MinVersionDependency(version='39.4', version_type=VersionType.CUSTOMIZING),
                MaxVersionDependency(version='39.5.4', version_type=VersionType.CUSTOMIZING),
                PacketDependency(packet_path='migration.server.S_39_5_0.wi10734_not_null_constraints'
                                             '.FixNotNullConstraints', expected_state=PacketState.INSTALLED))

    def fix(self, runner):
        formats = {'000229':("UPDATE DT409 SET DI000196 = 0 WHERE DI000235 = '000229' AND DI000179 = 2",),
                   '000097':("UPDATE DT409 SET DI000201 = 3, DI000199 = '.' WHERE DI000235 = '000097' AND DI000179 = 4",
                             "UPDATE DT409 SET DI000196 = 6, DI000199 = '@', DI000201 = 1, DI000226 = NULL WHERE DI000235 = '000097' AND DI000179 = 9",
                             "INSERT INTO DT409 (DI000235, DI000179, DI000196, DI000199, DI000201, DI000226) VALUES ('000097', 10, 99, NULL, 3, 'DEM')"),
                   '000098':("UPDATE DT409 SET DI000201 = 3, DI000199 = '.' WHERE DI000235 = '000098' AND DI000179 = 3",
                             "UPDATE DT409 SET DI000196 = 6, DI000199 = '@', DI000201 = 1, DI000226 = NULL WHERE DI000235 = '000098' AND DI000179 = 5",
                             "INSERT INTO DT409 (DI000235, DI000179, DI000196, DI000199, DI000201, DI000226) VALUES ('000098', 6, 99, NULL, 3, 'EUR')"),
                   '000162':("UPDATE DT409 SET DI000201 = 0 WHERE DI000235 = '000162' AND DI000179 = 3",),
                   'GJ 07/08':("DELETE FROM DT408 WHERE DI000165 = 'GJ 07/08'",),
                   '000165':("DELETE FROM DT408 WHERE DI000165 = '000165'",),
                   '000126':("UPDATE DT409 SET DI000201 = 2 WHERE DI000235 = '000126' AND DI000179 = 1",
                             "UPDATE DT409 SET DI000226 = 'EUR' WHERE DI000235 = '000126' AND DI000179 = 4"),
                   '000152':("UPDATE DT409 SET DI000199 = '.', DI000201 = 3 WHERE DI000235 = '000152' AND DI000179 = 3",
                             "UPDATE DT409 SET DI000196 = 9, DI000199 = '@', DI000226 = NULL WHERE DI000235 = '000152' AND DI000179 = 4",
                             "INSERT INTO DT409 (DI000235, DI000179, DI000196, DI000199, DI000201, DI000226) VALUES ('000152', 5, 99, NULL, 1, 'EUR')"),
                   '000067':("UPDATE DT409 SET DI000201 = 3 WHERE DI000235 = '000067' AND DI000179 = 1",
                             "UPDATE DT409 SET DI000201 = 1 WHERE DI000235 = '000067' AND DI000179 = 3"),
                   '000174':("UPDATE DT409 SET DI000201 = 3 WHERE DI000235 = '000174' AND DI000179 = 3",),
                   '000181':("UPDATE DT409 SET DI000201 = 3 WHERE DI000235 = '000181' AND DI000179 = 4",)}
        descriptions = {'000097':{'DE':('DEM mit WZ bis 999 TSD', 'DEM mit WZ bis 9 MIO'),
                                  'EN':('DEM+curr.sym.to 999k', 'DEM+curr.sym.to 9m'),
                                  'FR':('DEM avec SM jusque 999 MILLE', 'DEM avec SM jusque 9 MIO')},
                        '000098':{'DE':('EUR mit WKZ bis 999 TSD', 'EUR mit WKZ bis 9 MIO'),
                                  'EN':('EUR+curr.sym. to 999k', 'EUR+curr.sym. to 9m'),
                                  'FR':('EUR avec SM jusque 999 MILLE', 'EUR avec SM jusque 9 MIO')},
                        '000152':{'DE':('EUR ohne NK bis 999 MIO 3erGr', 'EUR ohne NK bis 9 MRD 3erGr'),
                                  'EN':('EUR,no decimal places to 999m', 'EUR,no decimal places to 9bn'),
                                  'FR':('EUR ss AV jusq 999MIO gr. de 3', 'EUR ss AV jusq 9 MILLIARDS gr. de 3')}}
        usage = []
        errors = 0

        for area, usage_query in USAGE_QUERIES.items():
            for id, queries in formats.items():
                for query in queries:
                    if re.search(query, "DELETE FROM DT408"):
                        if ppms.db_select(usage_query.format(id=id))[0][0]:
                            usage.append(format)
                            self.log("Format '{format}' is in use in the following area: '{area}'".format(format=format, area=area))

        for id, queries in formats.items():
            for query in queries:
                if id not in usage:
                    try:
                        if self.modify_and_log(query):
                            if id in descriptions.keys():
                                for lang, (text_old, text_new) in descriptions[id].items():
                                    itext_nr = int(ppms.db_select("SELECT DI001570 FROM DT408 WHERE DI000165 = '{id}'".format(id=id))[0][0])
                                    text_current = ppms.db_select("SELECT DI001549 FROM DT435 WHERE DI001538 = {itext_nr} AND DI001550 = '{lang}'".format(itext_nr=itext_nr, lang=lang))[0][0]
                                    if text_current == text_old:
                                        if not self.modify_and_log("UPDATE DT435 SET DI001549 = '{text_new}' WHERE DI001538 = {itext_nr} AND DI001550 = '{lang}'".format(text_new=text_new, itext_nr=itext_nr, lang=lang)):
                                            self.log("Updating I-Text '{lang}' of format '{id}' failed.".format(lang=lang, id=id))
                                            errors += 1
                                    elif not text_current == text_new:
                                        self.log("I-Text '{lang}' of format '{id}' neither had the expected (old) state '{text_old}' nor the changed (new) one '{text_new}'.".format(lang=lang, id=id, text_old=text_old, text_new=text_new))
                                        errors += 1
                        else:
                            self.log("Updating format '{id}' failed.".format(id=id))
                            errors += 1
                    except DatabaseError as e:
                        existing = ppms.db_select("SELECT 1 FROM DT409 WHERE DI000235 = '{id}'".format(id=id))
                        if existing and re.search(query, "INSERT"):
                            self.log("Insert for format '{id}' failed because it already exists.".format(id=id))
                        else:
                            self.log("Update/Insert for format '{id}' failed because of a DatabaseError: {exception}".format(id=id, exception=e))
                        errors += 1
        if not errors:
            return self.success()
        return self.fail()
