# -*- coding: utf-8 -*-
from ppms import ppms
from ppms.migration.packet import *
from . import DEFAULT_CUSTOMIZING_DEPENDENCY


SQL_SERVER_DROP_DEFAULT = """declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
declare @Command  nvarchar(1000)

set @table_name = N'%s'
set @col_name = N'%s'

select @Command = 'ALTER TABLE ' + @table_name + ' drop constraint ' + d.name
 from sys.tables t
  join    sys.default_constraints d
   on d.parent_object_id = t.object_id
  join    sys.columns c
   on c.object_id = t.object_id
    and c.column_id = d.parent_column_id
 where t.name = @table_name
  and c.name = @col_name

execute (@Command)"""

DBMS = ppms.db_get_dbms_name()


class FixMitarbeiterPoolConstraints(ChangeDTPPacket, SQLPacket):
    """Fixes the constraints on the table 'MITARBEITER_POOL'"""
    version = 1
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 12854

    DROP_CONSTRAINT_QUERY = {'000002': ['ALTER TABLE MITARBEITER_POOL DROP CONSTRAINT DT566SKIN_ID_NOT_NULL',
                                        'ALTER TABLE MITARBEITER_POOL DROP CONSTRAINT DT566CONVERSION_FACTOR_ID_N'],
                             '000004': [SQL_SERVER_DROP_DEFAULT % ('MITARBEITER_POOL', 'SKIN_ID'),
                                        SQL_SERVER_DROP_DEFAULT % ('MITARBEITER_POOL', 'CONVERSION_FACTOR_ID')]}


    DBMS_QUERY = {'000002': ['ALTER TABLE MITARBEITER_POOL MODIFY (SKIN_ID DEFAULT NULL)',
                             'ALTER TABLE MITARBEITER_POOL MODIFY (CONVERSION_FACTOR_ID DEFAULT NULL)'],

                  '000004': ['ALTER TABLE MITARBEITER_POOL ADD DEFAULT NULL FOR SKIN_ID',
                             'ALTER TABLE MITARBEITER_POOL ADD DEFAULT NULL FOR CONVERSION_FACTOR_ID']}

    def get_dependencies(self):
        return DEFAULT_CUSTOMIZING_DEPENDENCY

    def fix(self, runner):
        sql_record = self.get_record(412, ['062900'], ['sql'])
        conversion_record = self.get_record(412, ['062858'], ['sql'])

        if not all((self.change_value(sql_record, 'sql', 1, 0), self.change_value(conversion_record, 'sql', 1, 0))):
            self.log('Couldn\'t change sql flag')

        self.log('Dropping constraints')
        for drop_query in self.DROP_CONSTRAINT_QUERY[DBMS]:
            try:
                self.modify_and_log(drop_query)
            except ppms.DatabaseError as err:
                self.log('Catched and ignoring %s' % err)

        self.log('Setting default values')
        for default_query in self.DBMS_QUERY[DBMS]:
            self.modify_and_log(default_query)

        return self.success()