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

class DeleteMSPI_TASKConstraint(SQLPacket):
    '''Removes an erronous constraint in MSPI_TASK that references MSPI_TASK

    Only necessary on Oracle DB (since MSSQL does not support constraints from the same table to the same table)'''
    version = 1
    run_on_startup = True
    done_after_success = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 16434

    get_constraint_stmt = "SELECT c.constraint_name \
  FROM all_cons_columns a \
  JOIN all_constraints c ON a.owner = c.owner \
   AND a.constraint_name = c.constraint_name \
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner \
   AND c.r_constraint_name = c_pk.constraint_name \
  JOIN user_cons_columns uc on uc.constraint_name = c.r_constraint_name \
  WHERE c.owner = (select user from dual) \
     AND upper(a.table_name) = 'MSPI_TASK' \
     AND upper(uc.table_name) = 'MSPI_TASK' \
     AND upper(a.COLUMN_NAME) = 'PARENT_TASK' \
     AND upper(uc.COLUMN_NAME) = 'MSP_ID'"

    delete_constraint_stmt = "BEGIN p_execute_ignoring_exception('alter table MSPI_TASK drop constraint {}'); END;"

    def get_dependencies(self):
        return MaxVersionDependency(version='39.5.7'),

    def fix(self, runner):
        if self.DBMS == DBMS_ORACLE:

            # Just to be exxxtra safe: Figure out the name of the constraint by its properties
            # in case there's some other constraint from MSPI_TASK to MSPI_TASK or whatever
            constraint = (ppms.db_select(self.get_constraint_stmt))

            if (len(constraint) > 0):
                # Found it -> delete it
                ppms.db_modify(self.delete_constraint_stmt.format(constraint[0][0]))

        return self.success() # For MSSQL, or if the constraint doesn't exist at all -> OK

