# -*- coding: utf-8 -*-
from ppms import ppms
from ppms.constants import *
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()


def get_column_type_and_precision(table_name, column_name):
    # Words can't describe my hate for MSSQL right now
    data_type_stmt = r"""select DATA_TYPE from INFORMATION_SCHEMA.COLUMNS IC where TABLE_NAME = '{table_name}' and
    COLUMN_NAME = '{column_name}'""".format(
        **locals())
    data_type = ppms.db_select(data_type_stmt)[0][0].upper()

    if data_type == 'VARCHAR':
        stmt = r"""SELECT CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS IC where TABLE_NAME = '{
        table_name}' and COLUMN_NAME = '{column_name}'""".format(
            **locals())
        precision = ppms.db_select(stmt)[0][0]

        return 'VARCHAR(%d)' % precision

    elif data_type == 'NUMERIC':
        stmt = r"""SELECT CAST(NUMERIC_PRECISION AS int), NUMERIC_SCALE from INFORMATION_SCHEMA.COLUMNS IC where
        TABLE_NAME = '{table_name}' and COLUMN_NAME = '{column_name}'""".format(
            **locals())
        precision, scale = ppms.db_select(stmt)[0]

        return 'NUMERIC(%d,%d)' % (precision, scale)

    else:
        raise NotImplementedError(data_type)


def is_nullable(table_name, column_name):
    if DBMS == DBMS_ORACLE:
        stmt = """select nullable from user_tab_columns
where table_name = '{table_name}'
and column_name = '{column_name}'""".format(**locals())
        result = ppms.db_select(stmt)

        return result[0][0].upper() == 'Y'

    elif DBMS == DBMS_MSSQL:
        stmt = "SELECT COLUMNPROPERTY(OBJECT_ID('{table_name}', 'U'), '{column_name}', 'AllowsNull')"
        result = ppms.db_select(stmt)

        return bool(result[0][0])

    else:
        raise NotImplementedError(DBMS)


def get_alter_table_statement(table_name, column_name):
    if DBMS == DBMS_ORACLE:
        stmt = 'ALTER TABLE {table_name} MODIFY ({column_name} NOT NULL)'.format(**locals())

    elif DBMS == DBMS_MSSQL:
        data_type = get_column_type_and_precision(table_name, column_name)
        stmt = 'ALTER TABLE {table_name} ALTER COLUMN {column_name} {data_type} NOT NULL'.format(**locals())

    else:
        raise NotImplementedError(DBMS)

    return stmt


class FixInterfaceDefaultColumns(SQLPacket):
    """Removes invalid default values from interface tables and removes the corrupt data"""
    version = 1
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 12540

    DBMS_QUERY = {'000002': ['ALTER TABLE INTERFACE_MAPPING MODIFY PARENT_ID DEFAULT NULL',
                             'ALTER TABLE INTERFACE_CONFIG MODIFY COPY_OF DEFAULT NULL',
                             'ALTER TABLE INTERFACE_LOGGING MODIFY CONFIG_ID DEFAULT NULL',
                             'ALTER TABLE INTERFACE_MAPPING MODIFY CONFIG_ID DEFAULT NULL',
                             'ALTER TABLE INTERFACE_PARAMETER MODIFY CONFIG_ID DEFAULT NULL',
                             'ALTER TABLE MAPPING_PARAMETER MODIFY MAPPING_ID DEFAULT NULL'],

                  '000004': [SQL_SERVER_DROP_DEFAULT % ('INTERFACE_MAPPING', 'PARENT_ID'),
                             'ALTER TABLE INTERFACE_MAPPING ADD DEFAULT NULL FOR PARENT_ID',
                             SQL_SERVER_DROP_DEFAULT % ('INTERFACE_CONFIG', 'COPY_OF'),
                             'ALTER TABLE INTERFACE_CONFIG ADD DEFAULT NULL FOR COPY_OF',
                             SQL_SERVER_DROP_DEFAULT % ('INTERFACE_LOGGING', 'CONFIG_ID'),
                             'ALTER TABLE INTERFACE_LOGGING ADD DEFAULT NULL FOR CONFIG_ID',
                             SQL_SERVER_DROP_DEFAULT % ('INTERFACE_MAPPING', 'CONFIG_ID'),
                             'ALTER TABLE INTERFACE_MAPPING ADD DEFAULT NULL FOR CONFIG_ID',
                             SQL_SERVER_DROP_DEFAULT % ('INTERFACE_PARAMETER', 'CONFIG_ID'),
                             'ALTER TABLE INTERFACE_PARAMETER ADD DEFAULT NULL FOR CONFIG_ID',
                             SQL_SERVER_DROP_DEFAULT % ('MAPPING_PARAMETER', 'MAPPING_ID'),
                             'ALTER TABLE MAPPING_PARAMETER ADD DEFAULT NULL FOR MAPPING_ID']}

    FIX_QUERY = {'000002': ['UPDATE INTERFACE_MAPPING a SET PARENT_ID = NULL WHERE NOT EXISTS (SELECT UUID FROM ' \
                            'INTERFACE_MAPPING b where a.PARENT_ID=b.UUID)',
                            'UPDATE INTERFACE_CONFIG a SET COPY_OF = NULL WHERE NOT EXISTS (SELECT UUID FROM ' \
                            'INTERFACE_CONFIG b where a.COPY_OF=b.UUID)',
                            'UPDATE INTERFACE_LOGGING a SET CONFIG_ID = NULL WHERE NOT EXISTS (SELECT UUID FROM ' \
                            'INTERFACE_CONFIG b where a.CONFIG_ID=b.UUID)',
                            'UPDATE INTERFACE_MAPPING a SET CONFIG_ID = NULL WHERE NOT EXISTS (SELECT UUID FROM ' \
                            'INTERFACE_CONFIG b where a.CONFIG_ID=b.UUID)',
                            'UPDATE INTERFACE_PARAMETER a SET CONFIG_ID = NULL WHERE NOT EXISTS (SELECT UUID FROM ' \
                            'INTERFACE_CONFIG b where a.CONFIG_ID=b.UUID)',
                            'UPDATE MAPPING_PARAMETER a SET MAPPING_ID = NULL WHERE NOT EXISTS (SELECT UUID FROM ' \
                            'INTERFACE_MAPPING b where a.MAPPING_ID=b.UUID)'],

                 '000004': ['UPDATE a SET PARENT_ID = NULL FROM INTERFACE_MAPPING a WHERE NOT EXISTS (SELECT UUID '
                            'FROM INTERFACE_MAPPING b where a.PARENT_ID=b.UUID)',
                            'UPDATE a SET COPY_OF = NULL FROM INTERFACE_CONFIG a WHERE NOT EXISTS (SELECT UUID FROM ' \
                            'INTERFACE_CONFIG b where a.COPY_OF=b.UUID)',
                            'UPDATE a SET CONFIG_ID = NULL FROM INTERFACE_LOGGING a WHERE NOT EXISTS (SELECT UUID '
                            'FROM ' \
                            'INTERFACE_CONFIG b where a.CONFIG_ID=b.UUID)',
                            'UPDATE a SET CONFIG_ID = NULL FROM INTERFACE_MAPPING a WHERE NOT EXISTS (SELECT UUID '
                            'FROM ' \
                            'INTERFACE_CONFIG b where a.CONFIG_ID=b.UUID)',
                            'UPDATE a SET CONFIG_ID = NULL FROM INTERFACE_PARAMETER a WHERE NOT EXISTS (SELECT UUID '
                            'FROM INTERFACE_CONFIG b where a.CONFIG_ID=b.UUID)',
                            'UPDATE a SET MAPPING_ID = NULL FROM MAPPING_PARAMETER a WHERE NOT EXISTS (SELECT UUID '
                            'FROM ' \
                            'INTERFACE_MAPPING b where a.MAPPING_ID=b.UUID)']}

    SHOULD_NOT_BE_NULLABLE = [('INTERFACE_LOGGING', 'CONFIG_ID'),
                              ('MAPPING_PARAMETER', 'MAPPING_ID'),
                              ('INTERFACE_PARAMETER', 'CONFIG_ID'),
                              ('INTERFACE_MAPPING', 'CONFIG_ID')]

    def get_dependencies(self):
        return DEFAULT_CUSTOMIZING_DEPENDENCY

    def fix(self, runner):
        self.log('Setting default values')
        for default_query in self.DBMS_QUERY[ppms.db_get_dbms_name()]:
            self.modify_and_log(default_query)

        self.log('Fixing the corruptions now')
        for query in self.FIX_QUERY[ppms.db_get_dbms_name()]:
            self.modify_and_log(query)

        self.log('Setting columns not nullable')
        for table_name, column_name in self.SHOULD_NOT_BE_NULLABLE:
            if is_nullable(table_name=table_name, column_name=column_name):
                self.modify_and_log(query=get_alter_table_statement(table_name=table_name, column_name=column_name))

        return self.success()