# -*- coding: utf-8 -*-
from ppms.migration import SQLPacket, PacketCategory, MaxVersionDependency, \
    MinVersionDependency, VersionType
from ppms import ppms
from ppms.constants import DBMS_ORACLE, DBMS_MSSQL


class DeleteIndices(SQLPacket):
    '''Delete indices which have been changed, they are recreated by the schema extensions.
    '''
    version = 1
    run_on_startup = True
    done_after_success = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 17853

    get_constraints = {DBMS_MSSQL: """select distinct
                                    cast(u.CONSTRAINT_NAME as varchar(128)),
                                    cast(u.TABLE_NAME as varchar(128))
                                from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
                                    Inner Join sys.objects o on o.name = u.CONSTRAINT_NAME
                                where u.TABLE_NAME like '%_HIS'
                                and o.type in ('UQ', 'PK', 'F')
                                UNION
                                SELECT distinct
                                    cast(c.name as varchar(128)),
                                    cast(t.name as varchar(128))
                                FROM sys.all_columns col
                                    INNER JOIN sys.tables t ON col.object_id = t.object_id
                                    INNER JOIN sys.default_constraints c ON col.default_object_id = c.object_id
                                    Inner Join sys.objects o on o.name = c.name
                                where t.name like '%_HIS'""",
                       DBMS_ORACLE: """SELECT distinct UC.CONSTRAINT_NAME,
                                  UCC1.TABLE_NAME
                                FROM USER_CONSTRAINTS uc
                                INNER JOIN USER_CONS_COLUMNS ucc1 ON UC.CONSTRAINT_NAME = UCC1.CONSTRAINT_NAME
                                FULL JOIN DT415 on DT415.DI000216 = UCC1.TABLE_NAME
                                where UC.CONSTRAINT_NAME is not null
                                and ucc1.table_name like '%_HIS'
                                """}

    drop_all_remaining_indexes = {DBMS_ORACLE: """
                                    BEGIN
                                      FOR ind IN
                                        (SELECT INDEX_NAME FROM user_indexes WHERE table_name like '%_HIS' AND index_name NOT IN
                                               (SELECT unique index_name FROM user_constraints WHERE
                                                  table_name like '%_HIS' AND index_name IS NOT NULL)
                                                  and index_type != 'LOB')
                                      LOOP
                                          execute immediate 'DROP INDEX '||ind.index_name;
                                      END LOOP;
                                    END;
                                    """,
                                  DBMS_MSSQL: """declare @qry nvarchar(max);
                                    select @qry =
                                    (SELECT  'DROP INDEX [' + ix.name + '] ON ' + OBJECT_NAME(ID) + '; '
                                    FROM  sysindexes ix
                                    WHERE   ix.Name IS NOT null and OBJECT_NAME(ID) like '%_HIS' and ix.first is not null
                                    for xml path(''));
                                    exec sp_executesql @qry"""}

    disable_constraints = '''alter table {dt} drop constraint "{constraint}"'''

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


    def delete_indexes_on_history_tables(self):
        # first drop the constraints so the indexes are free to be dropped too
        constraints = self.select_and_log(self.get_constraints[self.DBMS])
        for result in constraints:
            table = result[1]
            constraint = result[0]
            self.modify_and_log(self.disable_constraints.format(dt=table, constraint=constraint))

        self.modify_and_log(self.drop_all_remaining_indexes[self.DBMS])

    def fix(self, runner):
        self.delete_indexes_on_history_tables()
        self.success()