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


class ShortenCRCColumns(SQLPacket):
    ''' Shorten the crc columns in all schemas to 10 since it is the correct length '''

    wi_number = 17004
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY

    dt_column_list = [('DT003', 'DI059203'), ('DT035', 'DI059197'), ('DT397', 'DI059199'),
        ('DT460', 'DI059202'), ('DT546', 'DI059200'), ('DT952', 'DI059201')]
    length_check = {DBMS_MSSQL: """SELECT CAST(NUMERIC_PRECISION as numeric(10,0))
                            FROM INFORMATION_SCHEMA.COLUMNS
                            WHERE TABLE_NAME = '{dt}'
                            and COLUMN_NAME = '{column}'""",
                    DBMS_ORACLE: """select data_precision
                            from user_tab_columns
                            where TABLE_NAME = '{dt}'
                            and COLUMN_NAME = '{column}'"""
    }

    update_statement = {DBMS_MSSQL: """update {dt} set {column} = 0
                                        where len({column}) > 10""",
                    DBMS_ORACLE: """update {dt} set {column} = 0
                                        where Length({column}) > 10"""
    }

    rename_column = {DBMS_MSSQL: """EXEC sp_rename '{dt}.{column}', '{column}_TEMP', 'COLUMN'""",
                    DBMS_ORACLE: """ALTER TABLE {dt}
                                    RENAME COLUMN {column} to {column}_TEMP"""
    }

    create_new_column = {DBMS_MSSQL: """ALTER TABLE {dt}
                                        ADD {column} numeric(10,0) default 0""",
                    DBMS_ORACLE: """ALTER TABLE {dt}
                                        ADD ({column} number(10,0) default 0)"""
    }

    fill_new_column = """update {dt} set {column} = {column}_TEMP"""

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

    def get_data_precision_crc_columns_to_change(self):
        change_list = []
        for dt, column in self.dt_column_list:
            result = self.select_and_log(self.length_check[self.DBMS].format(dt=dt, column=column))
            if result:
                if int(result[0][0]) != 10:
                    change_list.append((dt, column))
        return change_list

    def shorten_column(self, dt, column):
        self.modify_and_log(self.update_statement[self.DBMS].format(dt=dt, column=column))
        self.modify_and_log(self.rename_column[self.DBMS].format(dt=dt, column=column))
        self.modify_and_log(self.create_new_column[self.DBMS].format(dt=dt, column=column))
        self.modify_and_log(self.fill_new_column.format(dt=dt, column=column))
        self.drop_column(table_name=dt, column_name=column+'_TEMP')


    def fix(self, runner):
        change_list = self.get_data_precision_crc_columns_to_change()
        for dt, column in change_list:
            self.shorten_column(dt, column)
        return self.success()