# -*- coding: utf-8 -*-
import itertools
from collections import namedtuple

import hql
from ppms import ppms
from ppms.constants import *
from ppms.migration import SQLPacket, PacketCategory, MaxVersionDependency

TuplyTable = namedtuple('TuplyTable', ('id', 'sql_name'))


class Table(TuplyTable):
    @property
    def table_name(self):
        table_record = ppms.search_record(415, [self.id], ['table_name'])
        return table_record.table_name.get_value()

    def __str__(self):
        return '{} "{}" ({})'.format(self.id, self.table_name, self.sql_name)


class CreateUUIDColumnsInKDB(SQLPacket):
    """Add the UUID column to customer tables

    This packet looks through all tables in the customer schema and
    adds UUID columns to tables that are enabled.
    """
    done_after_success = False
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 19304

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

    def get_enabled_customer_schemas(self):
        query = """SELECT
                     db.db_schema_id
                   FROM
                     DBSchema db
                   WHERE
                     (db.generated = 1
                     and db.enabled = 1) and
                     (db.own_lic NOT IN {}
                      or db.db_schema_id in ('K000', 'K{}'))"""

        system_license = ppms.uvar_get(SYS_VAR_LICENSE)
        result = hql.query(query.format(PLANTA_LICENSES, system_license))

        for schema in itertools.chain(*result):
            yield schema

    def get_enabled_tables_from_schema(self, schema):
        query = """SELECT
                     dt.dt_id,
                     dt.variable_name
                   FROM
                     DataTable dt
                   WHERE
                     dt.db_schema_id = '{}'
                     AND dt.enabled = 1"""
        result = hql.query(query.format(schema))

        for dt_id, variable_name in itertools.chain(result):
            yield Table(id=dt_id, sql_name=variable_name)

    def get_uuid_dataitem_number(self, table):
        query = """SELECT
                     di.di
                   FROM
                     DataItem di
                   WHERE
                     di.dt = '{}'
                     AND di.column_name = 'UUID'"""

        result = hql.query(query.format(table.id))

        try:
            di_number = result[0][0]
        except IndexError:
            di_number = None

        return di_number

    def get_uuid_number_from_DT415(self, table):
        query = """SELECT
                     dt.uuid_di
                   FROM
                     DataTable dt
                   WHERE
                     dt.dt_id = '{}'"""

        result = hql.query(query.format(table.id))

        try:
            di_number = result[0][0]
        except IndexError:
            di_number = None

        return di_number

    def uuid_column_exists(self, table):
        return self.column_exists(table_name=table.sql_name, column_name='UUID')

    def table_should_get_uuid_column(self, table):
        if not self.table_exists(table_name=table.sql_name):
            self.log('Table {} does not exist on the database and will be skipped'.format(table))
            return False

        if not self.object_is_table(object_name=table.sql_name):
            self.log('Table {} does not appear to be a table on the Database'.format(table))
            return False

        if self.get_uuid_dataitem_number(table=table) and self.uuid_column_exists(table=table):
            self.log('Table {} already has a complete UUID definition'.format(table))
            return False

        self.log('Table {} does not have a complete UUID definition'.format(table))
        return True

    def add_uuid_to_table(self, table):
        uuid_dataitem = self.get_uuid_dataitem_number(table=table)
        if uuid_dataitem is None:
            uuid_dataitem = self.add_uuid_dataitem(table=table)

        if not self.uuid_column_exists(table=table):
            self.add_column_for_dataitem(dataitem=uuid_dataitem)

    def add_column_for_dataitem(self, dataitem):
        self.log('Adding SQL column for dataitem %s (UUID)' % dataitem)

        creation_statements = ppms.db_custom_lines_create(ppms.db_get_current_dialect(), [int(dataitem)], True)
        for statement in creation_statements:
            if ppms.db_modify(statement) == -1:
                raise ppms.DatabaseError('Received -1 rows when executing "%s"' % statement)

        ppms.db_commit()

    def add_uuid_dataitem(self, table):
        self.log('Creating UUID Dataitem for table ' + table.id)

        attributes = {'column_name': 'UUID',
                      'db_length': 16,
                      'db_position': 0,
                      'db_typ': 'ID',
                      'df_behavior': 'o',
                      'df_length': 36,
                      'df_type': 'I',
                      'di_class': 0,
                      'di_customizing_name': 'uuid',
                      'di_name': 'UUID',
                      'dt': table.id,
                      'exit_id': '',
                      'ppms_type': 'UUID',
                      'server_relevant': 1,
                      'sql': 0,
                      'virtual': 0,
                      'wb_type': '',
                      'enabled': 1,
                      'column_type': 321}

        dt412_record = ppms.create_record(412, attributes)
        if dt412_record is None:
            return self.fail('Unable to create 412 record - something is fundamentally broken')

        if not dt412_record.save():
            return self.fail('Unable to save 412 record - something is fundamentally broken')

        ppms.db_commit()
        dataitem = dt412_record.di.get_value()

        if dataitem:
            # WB of DI041035 sets customizing-id to L<licensenumber>_value - eg, L708_uuid when calling create_record
            # -> Set the customizing-id in an update-statement after record has been created, because we want it be just 'uuid'
            ppms.db_modify("UPDATE DT412 SET DI041035 = 'uuid' WHERE DI000238 = '" + dataitem + "'")
            ppms.db_commit()

        if not self.uuid_column_exists(table=table):
            try:
                self.add_column_for_dataitem(dataitem=dataitem)
            except ppms.DatabaseError:
                dt412_record.delete()
                raise

        return dataitem

    def update_uuid_column_for_table(self, table, uuid_di):
        table_record = ppms.search_record(415, [table.id], ['uuid_di'])
        return_value = table_record.uuid_di.set_value(uuid_di)
        if return_value not in [DI_VALUE_SETTER_SUCCESS, DI_VALUE_SETTER_UNCHANGED]:
            raise ValueError('Unable to set uuid_di for table {}'.format(table))

        if not table_record.save():
            raise ValueError('Unable to save UUID DI information for table {}'.format(table))

    def fix(self, runner):
        self.log_heading('Looking for enabled customer schemas', level=1)
        for schema in self.get_enabled_customer_schemas():
            self.log_heading('Examining schema {}'.format(schema), level=2)
            for table in self.get_enabled_tables_from_schema(schema=schema):
                self.log_heading('Examining table {}'.format(table.id), level=3)

                if self.table_should_get_uuid_column(table=table):
                    self.add_uuid_to_table(table=table)

                uuid_number_from_dt415 = self.get_uuid_number_from_DT415(table=table)
                uuid_number_from_dt412 = self.get_uuid_dataitem_number(table=table)

                if (self.table_exists(table_name=table.sql_name)):
                    if (not uuid_number_from_dt415) or (uuid_number_from_dt415 != uuid_number_from_dt412):
                        self.update_uuid_column_for_table(table=table, uuid_di=uuid_number_from_dt412)

        self.log('No more schemas to examine')

        return self.success()
