# -*- 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 AddNewIndexes(SQLPacket):
    '''Add indexes which will improve select performance in some cases.
    '''
    version = 1
    run_on_startup = True
    done_after_success = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 16508

    indexes = {
       'SID407DT417':'CREATE INDEX SID407DT417 ON DT417 (DI003265)',
       'SID408DT409':'CREATE INDEX SID408DT409 ON DT409 (DI000235)',
       'SID412BDT440':'CREATE INDEX SID412BDT440 ON DT440 (DI000286)',
       'SID412DT439':'CREATE INDEX SID412DT439 ON DT439 (DI000361)',
       'SID412DT440':'CREATE INDEX SID412DT440 ON DT440 (DI000364)',
       'SID415DT416':'CREATE INDEX SID415DT416 ON DT416 (DI000389)',
       'SID418DT430':'CREATE INDEX SID418DT430 ON DT430 (DI000475)',
       'SID422DT423':'CREATE INDEX SID422DT423 ON DT423 (DI000370)',
       'SID425DT409':'CREATE INDEX SID425DT409 ON DT409 (DI000206)',
       'SID443DT324':'CREATE INDEX SID443DT324 ON DT324 (DI004367)',
       'SID445DT412':'CREATE INDEX SID445DT412 ON DT412 (DI000252)',
       'SID459DT417':'CREATE INDEX SID459DT417 ON DT417 (DI003266)',
       'SID461DT461':'CREATE INDEX SID461DT461 ON DT461 (DI041317)',
       'SID463DT461':'CREATE INDEX SID463DT461 ON DT461 (DI001003, DI001004)',
       'SID463DT465':'CREATE INDEX SID463DT465 ON DT465 (DI001200, DI001201)',
       'SID511DT455':'CREATE INDEX SID511DT455 ON DT455 (DI001365)',
       'SID885DT886':'CREATE INDEX SID885DT886 ON DT886 (DI041336)',
       'DI001367':'CREATE INDEX DI001367 ON DT470 (DI001367)'}
    
    def get_dependencies(self):
        return (MinVersionDependency(version='39.5.6', 
                                     version_type=VersionType.CUSTOMIZING),
                MaxVersionDependency(version='39.5.6', 
                                     version_type=VersionType.CUSTOMIZING))
        
    def fix(self, runner):
        for index_name, stmnt in self.indexes.items():
            try:
                if not self.change_create_index_to_rename_index(stmnt):
                    self.modify_and_log(stmnt)
            except ppms.DatabaseError as e:
                error_str = str(e)
                if (self.DBMS == DBMS_ORACLE and 'ORA-00955' in error_str) or \
                    (self.DBMS == DBMS_MSSQL and \
                     'SQLServerException' in error_str and \
                     index_name in error_str):
                    self.log('Index {index_name} already exists.' \
                             ''.format(**locals()))
                else:
                    raise e
                
        self.success()