# -*- coding: utf-8 -*-
from ppms import ppms
from ppms.constants import *
from ppms.migration import MinVersionDependency, MaxVersionDependency, ChangeDTPPacket, SQLPacket, PacketCategory, \
    StopMigrationProcessException

__all__ = ['FixNotNullConstraints']

SHOULD_BE_NOT_NULL_IN_SCHEMA = 1 << 0
DBMS = ppms.db_get_dbms_name()


def get_column_type_and_precision(table_name, di_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 = '{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):
    """Check if a column is nullable

    Returns None if the table/column pair doesn't exist
    """
    dbms = ppms.db_get_dbms_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)

        try:
            return result[0][0].upper() == 'Y'
        except IndexError:
            return None

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

        try:
            return bool(result[0][0])
        except IndexError:
            return None

    else:
        raise NotImplementedError(dbms)


DIS_TO_IGNORE = ['059559', '059560']


class FixNotNullConstraints(ChangeDTPPacket, SQLPacket):
    '''Allow Null values in foreign key DIs that point to dummy record tables

    Back in the olden days we couldn't handle NULL values properly, so we
    used a row with the primary key "-" as our null record to refer to with
    a foreign key. With Venus PLANTA can now handle NULL values properly, so
    this migration packet tries to clean up in the database wherever a foreign
    key points to a column that could be null nowadays.
    '''
    version = 1
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 10734

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

    def fix_schema(self, data_dictionary):
        self.log_heading('Altering the schema to allow NULL values')

        failures = 0
        for relation in (r for r in data_dictionary.relations.values()
                               if not r.is_primary_key() and
                               r.foreign_table.has_dummies):

            for di in (di for di in relation.local_dis if not di.used_in_pk):
                if relation.table.id == 946:
                    self.log('Skipping DT 946: WI 10986')
                    continue

                self.log('Analyzing DI %s of relation %s' % (repr(di), repr(relation)))
                numeric_di_id = di.id
                di_id = str(numeric_di_id).zfill(6).upper()
                
                if di_id in DIS_TO_IGNORE:
                    self.log('Skipping this DI for internal reasons (OK)')
                    continue

                rec_412 = self.get_record(412, [di_id], ['sql'])
                sql = rec_412.sql.get_value()

                if sql & SHOULD_BE_NOT_NULL_IN_SCHEMA:
                    self.log('Removing the NOT NULL bitflag')
                    if not self.change_value(rec_412, 'sql', sql, sql ^ SHOULD_BE_NOT_NULL_IN_SCHEMA):
                        failures += 1
                else:
                    self.log('Bitflag already set correctly')

                column_is_nullable = is_nullable(relation.table.sql_name, di.sql_name)
                if column_is_nullable is None:
                    self.log('Table "{table_name}" with column "{column_name}" does not exist'.format(
                        table_name=relation.table.sql_name, column_name=di.sql_name))

                else:
                    should_alter_schema = not column_is_nullable
                    if should_alter_schema:
                        stmt = get_alter_table_statement(relation.table.sql_name, di.sql_name)

                        try:
                            self.modify_and_log(stmt)
                        except ppms.DatabaseError as exception:
                            if 'ORA-01732' in str(exception):
                                self.log('Materialized view detected - no change possible')

                    else:
                        self.log('Column %s is already nullable, no need to change' % di.sql_name)

        if failures:
            return False

        return True

    def fix_data(self, data_dictionary):
        self.log_heading('Setting rows refering to "-" to NULL')
        for relation in (r for r in data_dictionary.relations.values()
                            if not r.is_primary_key() and
                            r.foreign_table.has_dummies and
                            r.table.id != r.foreign_table.id):

            for foreign_di in relation.foreign_dis:
                if foreign_di.used_in_pk:
                    break
            else:
                self.log('Skipping relation %s' % repr(relation))
                continue

            stmt = """UPDATE %s SET """ % relation.table.sql_name

            local_dis = [di for di in relation.local_dis if not di.used_in_pk]
            if not local_dis:
                self.log('Skipping relation %s' % repr(relation))
                continue

            self.log('Analyzing relation %s' % repr(relation))
            for index, di in enumerate(local_dis):
                stmt += "%s = NULL" % di.sql_name

                if index < len(local_dis) - 1:
                    stmt += ', '

            stmt += ' WHERE '
            for index, di in enumerate(local_dis):
                if di.db_type[0] == 'N':
                    stmt += "%s = 0" % di.sql_name
                else:
                    stmt += "%s = '-'" % di.sql_name

                if index < len(local_dis) - 1:
                    stmt += ' AND '

            try:
                self.modify_and_log(stmt)
            except ppms.DatabaseError as exception:
                if 'ORA-01732' in str(exception):
                    self.log('Materialized view detected - no change possible')

    def delete_dummy_records(self, data_dictionary):
        self.log_heading('Deleting dummy records with "-" primary key')
        for datatable in data_dictionary.data_tables.values():
            if not datatable.has_dummies:
                continue

            self.log('Analyzing %s' % repr(datatable))
            for relation in (r for r in datatable.relations
                                if r.foreign_table == datatable and
                                   r.relation_type == '1:1'):
                self.log('Analyzing %s' % repr(relation))

                stmt = """DELETE FROM %s WHERE """ % datatable.sql_name
                for index, di in enumerate(relation.local_dis):
                    if di.db_type[0] == 'N':
                        stmt += "%s = 0" % di.sql_name
                    else:
                        stmt += "%s = '-'" % di.sql_name

                    if index < len(relation.local_dis) - 1:
                        stmt += ' AND '

                try:
                    self.modify_and_log(stmt)
                except ppms.DatabaseError as exception:
                    if 'ORA-01732' in str(exception):
                        self.log('Materialized view detected - no change possible')

    def fix(self, runner):
        try:
            data_dictionary = ppms.get_data_dictionary()

            if not self.fix_schema(data_dictionary):
                return self.fail('Failed altering schema')

            self.fix_data(data_dictionary)
            self.delete_dummy_records(data_dictionary)
        except BaseException:
            text = 'Stopping migration due to error in CRITICAL packet!'
            raise StopMigrationProcessException(text)

        return self.success()
