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


class ReplaceSchemaObjects(SQLPacket):
    """Replace schema objects in VoE with according Venus ones"""

    wi_number = 14040
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY

    DROP_STATEMENTS = {DBMS_MSSQL: [],
                       DBMS_ORACLE: ("DROP MATERIALIZED VIEW V_EVA_PR",
                                     "DROP MATERIALIZED VIEW V_EVA_VG",
                                     "DROP MATERIALIZED VIEW V_EVA_VGR")}
    SQL_FILES = {DBMS_MSSQL: (### FUNCTIONS ###
                              ('f_get_new_inr_ssma.sql', False),
                              ('F_PLANTA_CALC_01_PRORESS.sql', False),
                              ('F_PLANTA_CALC_02_EV.sql', False),
                              ('p_get_new_inr_procedure.sql', False),
                              ('PLANTA_F_CALC_PERIOD_FACTOR.sql', False),
                              ('pr_psp_code_dt461.sql', False),
                              ### VIEWS ###
                              ('V_0MODULE_LIST_01.sql', False),
                              ('V_0MODULE_LIST_01_UEREL.sql', False),
                              ('V_0MODULE_LIST_02_39.sql', False),
                              ('V_0MODULE_LIST_02_39_UEREL.sql', False),
                              ('v_dt430_dt472.sql', False),
                              ('v_dt461_for_filter.sql', False),
                              ('V_EVA_PR.sql', False),
                              ('V_EVA_VG.sql', False),
                              ('V_EVA_VGR.sql', False),
                              ('V_ITEXT_Q1B.sql', False),
                              ('V_ITEXT_Q2B.sql', False),
                              ('V_ITEXT_Q3B.sql', False),
                              ('V_PLANTA_CUMU_DT827.sql', False),
                              ('V_PLANTA_DT827_PROGRAM.sql', False),
                              ('V_PPMS_DT080.sql', False),
                              ### PROCEDURES ###
                              ('00_CREATE_GLOBAL_TEMP_TABLES.sql', False),
                              ('10_p_drop_column_with_constraints.sql', False),
                              ('99_P_DELETE_EMPTY_ITEXT_Q1B.sql', False),
                              ('99_P_DELETE_EMPTY_ITEXT_Q2B.sql', False),
                              ('99_P_DELETE_EMPTY_ITEXT_Q3B.sql', False),
                              ('99_P_MATCHING_ITEXT_Q1B.sql', False),
                              ('99_P_MATCHING_ITEXT_Q2B.sql', False),    
                              ('99_P_MATCHING_ITEXT_Q3B.sql', False),
                              ('p_calc_periods.sql', False),
                              ('P_RunningSum_827.sql', False),
                              ### TRIGGERS ###
                              ('01_create_dt472.sql', False),
                              ('02_update_resourcename_from_person.sql', False)),
                 DBMS_ORACLE: (### SEQUENCES ###
                               ('SEQ_LOG.sql', True),
                               ### FUNCTIONS ###
                               ('01_PLANTA_CALC_PRORESS.sql', False),
                               ('02_PLANTA_CALC_EV.sql', False),
                               ('get_new_auto_id.sql', False),
                               ('PLANTA_F_CALC_PERIOD_FACTOR.sql', False),
                               ### VIEWS ###
                               ('PLANTA_ALIAS.sql', True),
                               ('PLANTA_PKS.sql', True),
                               ('PLANTA_FKS.sql', True),
                               ('PLANTA_NULL_FKS.sql', True),
                               ('PLANTA_FK_NULLS.sql', True),
                               ('PLANTA_PK_COLUMNS.sql', True),
                               ('PLANTA_FK_COLUMNS.sql', True),
                               ('PLANTA_PK_COLUMNS_UNUSED_BY_FK.sql', True),
                               ('PLANTA_FK_NULLS_UNUSED_BY_PK.sql', True),
                               ('PLANTA_FK_COLUMNS_UNUSED_BY_PK.sql', True),
                               ('V_DT430_DT472.sql', True),
                               ('V_EVA_PR.sql', True),
                               ('V_EVA_VG.sql', True),
                               ('V_EVA_VGR.sql', True),
                               ('V_ITEXT_Q1B.sql', True),
                               ('V_ITEXT_Q3B.sql', True),
                               ('V_MODULE_LIST_01.sql', True),
                               ('V_MODULE_LIST_01_39.sql', True),
                               ('V_MODULE_LIST_02_UEREL.sql', True),
                               ('V_MODULE_LIST_03_39_UEREL.sql', True),
                               ('V_PLANTA_CUMU_DT827.sql', True),
                               ('V_PLANTA_DT827_PROGRAM.sql', True),
                               ('V_PPMS_DT080.sql', True),
                               ### PROCEDURES ###
                               ('30_p_upd_budget_bl.sql', False),
                               ('99_P_DELETE_EMPTY_ITEXT_Q1B.sql', False),
                               ('P_CALC_PERIODS.sql', False),
                               ('P_RUNNINGSUM_827.sql', False),
                               ### PACKAGES ###
                               ('UPDATE_CASCADE.sql', False),
                               ('UPDATE_CASCADE_1.sql', False),
                               ('PLANTA_HELPERS.sql', False),
                               ('PLANTA_HELPERS_1.sql', False),
                               ('PLANTA_TMP_TABLES.sql', False),
                               ('PLANTA_TMP_TABLES_1.sql', False),
                               ('PLANTA_CONSTRAINTS.sql', False),
                               ('PLANTA_CONSTRAINTS_1.sql', False),
                               ('PLANTA_DATA_DICTIONARY.sql', False),
                               ('PLANTA_DATA_DICTIONARY_1.sql', False),
                               ### TRIGGERS ###
                               ('CREATE_DT472.sql', True),
                               ('PLANTA_COMPARISION_NAMES.sql', True),
                               ('USDT503ID411.sql', True),
                               ### MATERIALIZED VIEWS ###
                               ('V_ITEXT_Q2B.sql', True),
                               ('V_MODULE_LIST_M.sql', True))}

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

    def fix(self, runner):
        failures = []
        for drop_statement in self.DROP_STATEMENTS[self.DBMS]:
            try:
                self.modify_and_log(drop_statement)
            except ppms.DatabaseError as e:
                if 'ORA-12003' in str(e):
                    pass
                else:
                    failures.append((drop_statement, str(e)))

        self.apply_rollback_ddl_file(self.sql_planta_de_dbms, 'DT827_CONDENSED.sql', split=True, separator=';')

        for (sql_file_name, split) in self.SQL_FILES[self.DBMS]:
            try:
                if self.DBMS == DBMS_ORACLE:
                    if split:
                        self.apply_fix_ddl_file(self.sql_planta_de_dbms, sql_file_name, split=split, separator=';/')
                    else:
                        self.apply_fix_ddl_file(self.sql_planta_de_dbms, sql_file_name, split=split)
                else:
                    self.apply_fix_ddl_file(self.sql_planta_de_dbms, sql_file_name, split=True, separator='GO$')
            except ppms.DatabaseError as e:
                if 'ORA-12006' in str(e) or 'ORA-02260' in str(e) or 'ORA-00955' in str(e) or 'sqlserver' in str(e):
                    self.log('One or more of the statements in ' + sql_file_name + ' are not necessary and are skipped.')
                else:
                    failures.append((sql_file_name, str(e)))

        if failures:
            for sql_object, exception in failures:
                self.log("\nError while executing %s:\n %s\n" % (sql_object, exception))
            return self.fail(str(len(failures)) + " error(s) occured while replacing VoE-SQL-objects with Venus-SQL-objects.\nPlease see detailed infos above and correct where necessary")
        else:
            return self.success()
