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


def column_exists(table_name, column_name):
    """Check if a column exists"""
    dbms = ppms.db_get_dbms_name()

    if dbms == DBMS_ORACLE:
        query = """select count(*) from user_tab_cols
                   where
                     column_name = '{column_name}' and
                     table_name = '{table_name}'"""

    elif dbms == DBMS_MSSQL:
        query = """select COUNT(*) from sys.columns
                   where
                     Name = N'{column_name}' and
                     Object_ID = Object_ID(N'{table_name}')"""

    else:
        raise NotImplementedError(dbms)

    return ppms.db_select(query.format(table_name=table_name, column_name=column_name))[0][0] == 1


class RemoveObjectProtectionFromLoggingTable(SQLPacket, ChangeDTPPacket):
    """Remove the object protection columns from the Interface Logging table"""
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 18082

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

    def fix(self, packet_runner):
        self.drop_column(table_name='INTERFACE_LOGGING', column_name='CREATED_ON')
        self.drop_column(table_name='INTERFACE_LOGGING', column_name='MODIFIED_ON')
        self.drop_column(table_name='INTERFACE_LOGGING', column_name='RWDU')
        self.drop_column(table_name='INTERFACE_LOGGING', column_name='CREATED_BY')
        self.drop_column(table_name='INTERFACE_LOGGING', column_name='MODIFIED_BY')
        self.drop_column(table_name='INTERFACE_LOGGING', column_name='OWN_LIC')
        self.drop_column(table_name='INTERFACE_LOGGING', column_name='OPC')
        self.drop_column(table_name='INTERFACE_LOGGING', column_name='OWNER')

        if self.DBMS == DBMS_ORACLE and column_exists(table_name='INTERFACE_LOGGING', column_name='GROUP'):
            query = 'alter table INTERFACE_LOGGING drop column "GROUP"'
            self.modify_and_log(query, log_rows=False)
        else:
            self.drop_column(table_name='INTERFACE_LOGGING', column_name='GROUP')

        if self.DBMS == DBMS_ORACLE and column_exists(table_name='INTERFACE_LOGGING', column_name='ANY'):
            query = 'alter table INTERFACE_LOGGING drop column "ANY"'
            self.modify_and_log(query, log_rows=False)
        else:
            self.drop_column(table_name='INTERFACE_LOGGING', column_name='ANY')

        record = self.get_record(dt_num=415, key_list=['564'], di_list=['op_pos'])
        if not self.change_value(record=record, attribute='op_pos', old_value=2, new_value=0):
            return self.fail('Could not change object protection position')

        return self.success()
