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


class DeleteConstraintsBeforeDBImport(SQLPacket):
    ''' Delete all foreign key constraints.

    They will be recreated after the DB import.
    '''
    wi_number = 17001
    done_after_success = False
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY

    get_constraints = {DBMS_MSSQL: '''select
                                            cast(u.CONSTRAINT_NAME as varchar(128)),
                                            cast(u.TABLE_NAME as varchar(128)),
                                            cast(u.COLUMN_NAME as varchar(128))
                                        from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
                                                Inner Join DT415 on DT415.DI000216 = u.TABLE_NAME
                                                Inner Join sys.objects o on o.name = u.CONSTRAINT_NAME
                                        where o.type in ('F')
                                        or (u.TABLE_NAME in (select DT415.DI000216+'_HIS' from DT415))
                                        and o.type in ('F')
                                        UNION
                                        SELECT
                                            cast(c.name as varchar(128)),
                                            cast(t.name as varchar(128)),
                                            cast(col.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 DT415 on DT415.DI000216 = t.name
                                            Inner Join sys.objects o on o.name = c.name
                                        where o.type in ('F')
                                        or (o.type = 'C' and DT415.DI000266 in ('Q1B', 'Q2B'))''',
                       DBMS_ORACLE: """SELECT UC.CONSTRAINT_NAME,
                                            UCC1.TABLE_NAME
                                        FROM USER_CONSTRAINTS uc
                                        INNER JOIN USER_CONS_COLUMNS ucc1
                                        ON UC.CONSTRAINT_NAME = UCC1.CONSTRAINT_NAME
                                        INNER JOIN DT415
                                        ON DT415.DI000216 = UCC1.TABLE_NAME
                                        where uc.CONSTRAINT_TYPE = 'R'
                                        or (UCC1.TABLE_NAME in (select DT415.DI000216||'_HIS' from DT415)
                                        and uc.CONSTRAINT_TYPE = 'R')
                                        or (uc.CONSTRAINT_TYPE = 'C'
                                        and DT415.DI000266 in ('Q1B', 'Q2B'))
                        """}

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

    def delete_old_packets(self):
        statements = ['''
        delete from MIGRATIONHISTORY where MIGRATIONRULE in (
        select UUID from MIGRATIONRULE where PYTHONPATH in (
            'migration.before_db_import.DB_39_5_7.WI17001_delete_standard_constraints.DeleteStandardConstraintsQ1BandQ2B'))''',
        '''
        delete from MIGRATIONRULE where PYTHONPATH in (
            'migration.before_db_import.DB_39_5_7.WI17001_delete_standard_constraints.DeleteStandardConstraintsQ1BandQ2B')'''
        ]

        for statement in statements:
            self.modify_and_log(statement)

    def get_all_constraints(self):
        constraints = self.select_and_log(self.get_constraints[self.DBMS])
        standard_constraints = []

        for result in constraints:
            table = result[1]
            constraint = result[0]
            if (table, constraint) not in standard_constraints:
                standard_constraints.append((table, constraint))
        return standard_constraints

    def fix(self, runner):
        self.delete_old_packets()
        constraints_to_disable = self.get_all_constraints()
        for table, constraint in constraints_to_disable:
            self.modify_and_log(self.disable_constraints.format(dt=table,
                                                                constraint=constraint))

        return self.success()
