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

class FixPerformanceDT430(SQLPacket):
    '''Create some indexes and changes a View in DT430

    Only necessary on Oracle DB'''
    version = 1
    run_on_startup = True
    done_after_success = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 14630

    sql_file = 'wi14630_bad_performance_DT430.sql'

    sql_statements = {"IDX_CAL_DT472":"CREATE INDEX IDX_CAL_DT472 ON DT472 (DI001519, DI001395, DI001516, DI001517)",
                      "IDX_ACTIVE_PR_DT461":"CREATE UNIQUE INDEX IDX_ACTIVE_PR_DT461 ON DT461 (DI001001, DI001042)",
                      "IDX_TASK_DONE_DT466":"CREATE INDEX IDX_TASK_DONE_DT466 ON DT466 (DI001422)"}

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

    def fix(self, runner):
        if self.DBMS == DBMS_ORACLE:
            for index_name,stmnt in self.sql_statements.items():
                try:
                    self.modify_and_log(stmnt)
                except ppms.DatabaseError as e:
                    if 'ORA-00955' in str(e):
                        self.log('Index {index_name} already exists... Trying to delete'.format(**locals()))
                        deleted = self.modify_and_log("""DROP INDEX {index_name}""".format(**locals()))
                        if deleted > -1:
                            self.log('Index deleted... trying to recreate')
                            self.modify_and_log(stmnt)
                        else:
                            return self.fail('Could not delete existing index "{index_name}"'.format(**locals()))
                    else:
                        raise e

            self.apply_fix_ddl_file(self.sql_planta_de_dbms, self.sql_file, split=True, separator='^/$')

            return self.success()
        else:
            return self.success() # Only necessary with an Oracle DB -> other DBS (read: MSSQL) are always OK
