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

class DeleteStandardConstraintsAfterDBImport(SQLPacket):
    '''Delete all constraints on standard columns.

    Deletes all constraints on standard columns including unique constraints and primary key.
    All constraints will be recreated by the schema generation afterwards.
    '''
    wi_number = 17118
    done_after_success = False
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY

    standard_license = ['011', '100', 'ZZZ', '000']
    include_tables = ['REVINFO']

    oracle_helper_function = """create or replace function search_on_condition(cons_name in varchar2) return varchar2 is
                                temporary_varchar varchar2(4000);
                                begin
                                select SEARCH_CONDITION into temporary_varchar from USER_CONSTRAINTS where CONSTRAINT_NAME = cons_name;
                                return temporary_varchar;
                                end;
                                """

    get_constraints_unique_and_primary = {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
                             FULL JOIN DT415    on DT415.DI000216 = u.TABLE_NAME
                             FULL JOIN DT412   on DT412.DI000239 = DT415.DI000265
                                where (cast(u.CONSTRAINT_NAME as varchar(128)) is not null
                                    and (DT412.DI001869 in {licenses} or u.TABLE_NAME in {include_tables})
                                or u.TABLE_NAME in (select DT415.DI000216+'_HIS' from DT415))''',
                     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
                              FULL JOIN DT412   on DT412.DI000239 = DT415.DI000265
                              where (UC.CONSTRAINT_NAME is not null
                                and DT412.DI001869 in {licenses} or UCC1.TABLE_NAME in {include_tables})
                              or UCC1.TABLE_NAME in (select DT415.DI000216||'_HIS' from DT415)"""}

    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
                                    FULL JOIN DT415                    on DT415.DI000216 = u.table_name
                                    INNER JOIN sys.objects o           on o.name = u.constraint_name
                                    FULL JOIN DT412                    on DT412.DI000239 = DT415.DI000265
                                    LEFT JOIN sys.check_constraints c  on c.object_id = o.object_id
                            where (o.type not in ('UQ', 'PK')
                                and (DT412.DI001869 in {licenses} or u.TABLE_NAME in {include_tables}))
                            or (o.type = 'C'
                                and (c.definition is null or UPPER(c.definition) like '%NOT NULL%'))
                            or (u.table_name in (select DT415.DI000216+'_HIS' from DT415)
                                and o.type not in ('UQ', 'PK'))
                            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
                                FULL JOIN DT415                      on DT415.DI000216 = t.name
                                FULL JOIN DT412                      on DT412.DI000239 = DT415.DI000265
                                INNER JOIN sys.objects o             on o.name = c.name
                            where
                            (o.type not in ('UQ', 'PK')
                                and (DT412.DI001869 in {licenses} or cast(t.name as varchar(128)) in {include_tables}))
                            or (o.type = 'D')
                            or (t.name in (select DT415.DI000216+'_HIS' from DT415))''',
                 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
                                FULL JOIN DT412                    on DT412.DI000239 = DT415.DI000265
                            where (UC.CONSTRAINT_NAME is not null
                              and uc.CONSTRAINT_TYPE not in ('U', 'P')
                              and (DT412.DI001869 in {licenses} or UCC1.TABLE_NAME in {include_tables}))
                            or (UC.CONSTRAINT_TYPE = 'C'
                              and search_on_condition(UC.CONSTRAINT_NAME) like '%not null%')
                            or (UCC1.TABLE_NAME in (select DT415.DI000216||'_HIS' from DT415)
                              and uc.CONSTRAINT_TYPE not in ('U', 'P'))"""}

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

    def get_all_standard_constraints(self, primary=False):
        if primary:
            constraints = self.select_and_log(
                self.get_constraints_unique_and_primary[self.DBMS].format(licenses=str(tuple(self.standard_license)),
                                                                          include_tables=str(
                                                                              tuple(self.include_tables))))
        else:
            constraints = self.select_and_log(
                self.get_constraints[self.DBMS].format(licenses=str(tuple(self.standard_license)),
                                                       include_tables=str(tuple(self.include_tables))))
        return constraints

    def delete_old_packets(self):
        statements = ['''
        delete from MIGRATIONHISTORY where MIGRATIONRULE in (
        select UUID from MIGRATIONRULE where PYTHONPATH in (
            'migration.after_db_import.DB_39_5_7.WI17001_delete_constraints_after_db_import.DeleteStandardConstraintsAfterDBImport',
            'migration.after_db_import.DB_39_5_7.WI17118_delete_standard_constraints.DeleteStandardConstraintsUniqueAndPrimary'))''',
        '''
        delete from MIGRATIONRULE where PYTHONPATH in (
            'migration.after_db_import.DB_39_5_7.WI17001_delete_constraints_after_db_import.DeleteStandardConstraintsAfterDBImport',
            'migration.after_db_import.DB_39_5_7.WI17118_delete_standard_constraints.DeleteStandardConstraintsUniqueAndPrimary')'''
        ]

        for statement in statements:
            self.modify_and_log(statement)

    def delete_all_constraints_from_schema_generation(self):
        dialect = ppms.db_get_current_dialect()
        drop_constraints = ppms.db_custom_schema_create(dialect, dts_to_export=None,
                                                          get_create_strings=False,
                                                          get_table_strings=False,
                                                          get_constraint_strings=True)
        for constraint in drop_constraints:
            try:
                if self.DBMS == DBMS_MSSQL:
                    [self.modify_and_log(query) for query in re.split('GO[^\w]', constraint)]
                else:
                    for query in re.split(';/[^\w]', constraint):
                        if query.strip() != "":
                            self.modify_and_log(query)
            except ppms.DatabaseError as e:
                if "02443" in str(e) or "01418" in str(e) or "04080" in str(e) or "sqlserver" in str(e):
                    pass
                else:
                    raise e
                
    def fix(self, runner):
        self.delete_old_packets()
        if self.DBMS == DBMS_ORACLE:
            self.log("Creating helper function for Oracle.")
            self.modify_and_log(self.oracle_helper_function)

        # needed for right formating in sql statements
        if len(self.standard_license) == 1:
            self.standard_license.append(self.standard_license[0])
        if len(self.include_tables) == 1:
            self.include_tables.append(self.include_tables[0])

        constraints_to_disable = self.get_all_standard_constraints()
        for constraint, table in constraints_to_disable:
            if not table.upper().startswith('BIN'):
                self.modify_and_log(self.disable_constraints.format(dt=table, constraint=constraint))

        self.delete_all_constraints_from_schema_generation()

        constraints_to_disable_primaries = self.get_all_standard_constraints(primary=True)
        for constraint, table in constraints_to_disable_primaries:
            self.log(table)
            if not table.upper().startswith('BIN'):
                self.modify_and_log(self.disable_constraints.format(dt=table, constraint=constraint))

        if self.DBMS == DBMS_ORACLE:
            self.log("Removing helper function again.")
            self.modify_and_log("""DROP FUNCTION search_on_condition""")

        return self.success()
