# -*- coding: latin-1 -*-
from ppms.migration import SQLPacket, PacketCategory, MaxVersionDependency, MinVersionDependency
from ppms import ppms
import os
from ppms.constants import DBMS_MSSQL

DROP_FKS = [('DT007', 'SDT007ID466'), ('DT466', 'SDT466ID463'), ('DT472', 'SDT472ID466'), ('DT483', 'SDT483ID466'),
            ('DT801', 'SDT801ID466'), ('DT472', 'SDT472ID467'), ('DT469', 'SDT469ID467A'), ('DT469', 'SDT469ID467'),
            ('DT467', 'SDT467ID467S'), ('DT466', 'SDT466ID467'), ('DT485', 'SDT485ID467'), ('DT488', 'SDT488ID467'),
            ('DT804', 'SDT804ID467'), ('DT464', 'SDT464ID463'), ('DT826', 'SDT826ID467'), ('DT827', 'SDT827ID467'),
            ('DT833', 'SDT833ID467'), ('DT843', 'SDT843ID467'), ('DT844', 'SDT844ID467'), ('DT845', 'SDT845ID467'),
            ('DT887', 'SDT887ID818'), ('DT818', 'SDT818ID511'), ('DT461', 'SDT461ID463'), ('DT463', 'SDT463ID463S '),
            ('DT464', 'SDT464ID463'), ('DT465', 'SDT465ID463A'), ('DT465', 'SDT465ID463'), ('DT474', 'SDT474ID463'),
            ('DT804', 'SDT804ID463'), ('DT888', 'SDT888ID463'), ('DT888', 'SDT888ID463A '), ('MSPI_TASK', 'SMSPI_TASKID463'),
            ('DT463', 'SDT463ID461'), ('DT461', 'FKDT461ID463'), ('DT464', 'FKDT464ID463'), ('DT465', 'FKDT465ID463A'),
            ('DT465', 'FKDT465ID463'), ('DT466', 'FKDT466ID463'), ('DT474', 'FKDT474ID463'), ('DT804', 'FKDT804ID463'),
            ('DT888', 'FKDT888ID463'), ('DT888', 'FKDT888ID463A'), ('MSPI_TASK', 'FKMSPI_TASKID463'), ('DT007', 'FKDT007ID466'),
            ('DT472', 'FKDT472ID466'), ('DT483', 'FKDT483ID466'), ('DT801', 'FKDT801ID466'), ('DT466', 'FKDT466ID467'),
            ('DT469', 'FKDT469ID467'), ('DT469', 'FKDT469ID467A'), ('DT472', 'FKDT472ID467'), ('DT485', 'FKDT485ID467'),
            ('DT488', 'FKDT488ID467'), ('DT804', 'FKDT804ID467'), ('DT826', 'FKDT826ID467'), ('DT833', 'FKDT833ID467'),
            ('DT843', 'FKDT843ID467'), ('DT844', 'FKDT844ID467'), ('DT845', 'FKDT845ID467'), ('DT887', 'FKDT887ID818')]
DROP_PKS = [('DT463', 'PK_DT463'), ('DT466', 'PID466DT466'), ('DT467', 'PID467DT467'), ('DT472', 'PID472DT472'),
            ('DT818', 'PID818DT818'), ('DT866', 'PID866DT866'), ('DT874', 'PID874DT874'), ('DT875', 'PID875DT875')]
DROP_INDICES = [('DT463', 'PARENT_PR+PARENT_TASK+PR+TASK'), ('DT463', 'PK_covering_query'),
                ('DT463', 'RECURSION_PARENT'), ('DT467', 'IDX_DT467_EVA_RES_Type'), ('DT472', 'SID466DT472'),
                ('DT463', 'PK_DT463'), ('DT466', 'PID466DT466'), ('DT467', 'PID467DT467'), ('DT472', 'PID472DT472'),
                ('DT818', 'PID818DT818'), ('DT866', 'PID866DT866'), ('DT874', 'PID874DT874'), ('DT875', 'PID875DT875'),
                ('DT463', 'SID463DT463')]
DROP_CONSTRAINT = "ALTER TABLE [{table}] DROP CONSTRAINT [{constraint}]"
DROP_STATEMENT = '''drop index [{table}].[{index}]'''


class AdjustWrongColumnLength(SQLPacket):
    ''' Adjusts the column length of some columns.
    '''
    wi_number = 17737
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    sql_file = 'WI17737_differences_DB0_DB7.sql'

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

    def fix(self, runner):
        errors = 0
        path = self.get_sql_file_path(self.sql_planta_de_dbms, self.sql_file)

        # drop everything which prevents the columns from being changed (indices will be recreated afterwards and
        # constraints will be created from schema after the db import)
        if self.DBMS == DBMS_MSSQL:
            for table, constraint in DROP_FKS:
                try:
                    self.modify_and_log(DROP_CONSTRAINT.format(table=table, constraint=constraint))
                except ppms.DatabaseError:
                    pass
            for table, constraint in DROP_PKS:
                try:
                    self.modify_and_log(DROP_CONSTRAINT.format(table=table, constraint=constraint))
                except ppms.DatabaseError:
                    pass
            for table, index in DROP_INDICES:
                try:
                    self.modify_and_log(DROP_STATEMENT.format(table=table, index=index))
                except ppms.DatabaseError:
                    pass


        for statement in self.get_queries_from_file(file_path=path, split=True, separator=';'):
            try:
                self.modify_and_log(statement)
            except ppms.DatabaseError:
                errors += 1
                self.log(
                    """Following statement failed: """ + statement + """.\n The column most likely contains data which is longer than the column definition.""")

        if errors:
            return self.fail(
                "Not all column lengths could be adjusted. Most likely there is data which has to be shortened. Please check the errors above, correct your data and run this migration package again. ")
        return self.success()
