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

__all__ = ['AlterColumnMakeNullable']

DBMS = ppms.db_get_dbms_name()

def get_column_type_and_precision(table_name, di_name):
    data_type_stmt = r"""select DATA_TYPE from INFORMATION_SCHEMA.COLUMNS IC where TABLE_NAME = '{table_name}' and COLUMN_NAME = '{di_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 = '{di_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 = '{di_name}'""".format(**locals())
        precision, scale = ppms.db_select(stmt)[0]

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

    else:
        raise NotImplementedError(data_type)

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

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

    else:
        raise NotImplementedError(DBMS)

    return stmt

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)


class AlterColumnMakeNullable(SQLPacket):
    '''Alter DI 008414 "ID übergeordnetes Projekt" to allow Null values'''
    version = 1
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 8486

    def fix(self, runner):
        data_dictionary = ppms.get_data_dictionary()
        table = data_dictionary.data_tables[823]
        di = data_dictionary.data_items[8414]

        should_alter_schema = not is_nullable(table.sql_name, di.sql_name)
        if should_alter_schema:
            stmt = get_alter_table_statement(table.sql_name, di.sql_name)
            self.modify_and_log(stmt)

        return self.success()
