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

class DeleteStandardIndices(SQLPacket):
    """Remove indices from UUID columns, they are added by the schema generation after the DB import.

    Additionally some old standard indexes are dropped manually with this package."""
    done_after_success = False
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 17705

    drop_statement = {DBMS_ORACLE: '''drop index {index_name}''',
                      DBMS_MSSQL: '''drop index {table_name}.{index_name}'''}

    get_index_statements = {DBMS_ORACLE: """select b.TABLE_NAME, b.INDEX_NAME
                                        from user_ind_columns a, user_indexes b
                                        where a.index_name=b.index_name
                                        and a.column_name='UUID' and not exists
                                        (select * from user_ind_columns sub where sub.index_name=a.index_name and sub.column_name != a.column_name)
                                        """,
                        DBMS_MSSQL: """SELECT DISTINCT OBJECT_NAME(b.object_id) table_name, cast(b.name as varchar(50))
                                        FROM sys.index_columns a,
                                          sys.indexes b
                                        WHERE a.object_id = b.object_id
                                        AND a.index_id = b.index_id
                                        AND b.is_unique = 1
                                        AND COL_NAME(b.object_id , a.column_id)='UUID'
                                        """}

    drop_old_standard_index = {
        DBMS_ORACLE: ["""drop index PID462DT462""", """drop index PID887DT887""",
                      """drop index DT412_DATAITEM_ENABLED""",
                      """drop index DT415_TABLE_ENABLED""", """drop index DT415_DATATABLE_ENABLED""",
                      """drop index IDX_DT467_EVA_RES_TYPE"""],
        DBMS_MSSQL: ["""drop index DT462.PID462DT462""", """drop index DT887.PID887DT887""",
                     """drop index DT412.DT412_DATAITEM_ENABLED""", """drop index DT415.DT415_TABLE_ENABLED""",
                     """drop index DT415.DT415_DATATABLE_ENABLED""",
                     """drop index DT467.IDX_DT467_EVA_RES_TYPE"""]}

    def fix(self, runner):
        get_index_statement = self.get_index_statements[self.DBMS]

        tables_with_uuid_index = [(res[0], res[1]) for res in ppms.db_select(get_index_statement)]

        for table, index in tables_with_uuid_index:
            self.log(self.drop_statement[self.DBMS].format(index_name=index, table_name=table))
            try:
                self.modify_and_log(self.drop_statement[self.DBMS].format(index_name=index, table_name=table))
            except ppms.DatabaseError as e:
                # Primary keys on tables that do not exist in the datadictionary are not deleted and thus the deletion
                # of the unique index might fail for those tables.
                # Since those indices are not created bx the schema generation anyways i skip them here.
                if "02429" in str(e) or "sqlserver" in str(e):
                    pass
                else:
                    raise e

        for statement in self.drop_old_standard_index[self.DBMS]:
            try:
                self.modify_and_log(statement)
            except ppms.DatabaseError:
                pass

        return self.success()
