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

ORACLE = '000002'
MSSQL = '000004'

dbms = ppms.db_get_dbms_name()
db_dialect = ppms.db_get_current_dialect()

QUERY_TABLES_WITHOUT_UUID = {ORACLE: '''SELECT "DI000265", "DI000216" FROM "DT415"
WHERE NOT (
  "DT415"."DI000265" IN ('400')
  OR EXISTS (
    SELECT "DI000238" FROM "DT412"
    WHERE "DT412"."DI000239" = "DT415"."DI000265"
    AND "DI000215" = 'UUID')
)
AND "DT415"."DI001883" = '{license}'
AND EXISTS (
  SELECT 1 FROM USER_TABLES WHERE USER_TABLES.TABLE_NAME = "DT415"."DI000216"
)
AND NOT EXISTS (
  SELECT 1 FROM USER_MVIEWS WHERE USER_MVIEWS.MVIEW_NAME = "DT415"."DI000216"
)
ORDER BY "DI000265" ASC''',

                             MSSQL: '''SELECT "DI000265", "DI000216" FROM "DT415"
WHERE NOT (
  "DT415"."DI000265" IN ('400')
  OR EXISTS (
    SELECT "DI000238" FROM "DT412"
    WHERE "DT412"."DI000239" = "DT415"."DI000265"
    AND "DI000215" = 'UUID')
)
AND "DT415"."DI001883" = '{license}'
AND EXISTS (
  SELECT 1 FROM sys.tables WHERE sys.tables.name = "DT415"."DI000216"
)
AND NOT EXISTS (
  SELECT 1 FROM sys.views WHERE sys.views.name = "DT415"."DI000216"
)
ORDER BY "DI000265" ASC'''}


def get_tables_without_uuid():
    license = ppms.uvar_get('@3')

    query = QUERY_TABLES_WITHOUT_UUID[dbms].format(**locals())
    return ppms.db_select(query)


def table_is_active(dt_no):
    record = ppms.search_record(415, [dt_no], ['enabled'])
    if record is None:
        return False

    return record.enabled.get_value()

def add_uuid_column_to_table(dt_no):
    dt_no = str(dt_no)

    attributes = {'column_name': 'UUID',
                  'db_length': 16,
                  'db_position': 0,
                  'db_typ': 'ID',
                  'df_behavior': 'o',
                  'df_length': 36,
                  'df_type': 'A',
                  'di_class': 0,
                  'di_customizing_name': 'uuid',
                  'di_name': 'UUID',
                  'dt': dt_no,
                  '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)
    assert dt412_record.save(), attributes

    # Be sure that new DIP exists on DB, or else,
    # next auto-id for 238 may return the same DIP ID if there's more than one DT to check
    ppms.db_commit()

    # di_num has to be int for ppms.db_custom_lines_create() - columntype is "Alpha, Big" 
    di_num = dt412_record.di.get_value()

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

    ppms.db_commit()

    return di_num


def update_dt415_uuid_column(dt_no, di_num):
    record = ppms.search_record(415, [dt_no], [61211], True)
    retval = record.uuid_di.set_value(di_num)
    if retval not in [DI_VALUE_SETTER_SUCCESS, DI_VALUE_SETTER_UNCHANGED]:
        assert retval in [DI_VALUE_SETTER_SUCCESS, DI_VALUE_SETTER_UNCHANGED]

    retval = record.save()
    assert retval


class CreateUUIDColumnsPacket(BasePacket):
    '''Add the UUID column to customer tables

    The UUID column is used in Venus for the drag & drop functionality. If you
    want to test your system with UUID columns you can run this packet, but it
    does not enable any new functionality.
    '''
    version = 1
    done_after_success = True
    run_on_startup = False
    category = PacketCategory.CATEGORY_HELPER
    wi_number = 10491

    return_values = { DI_VALUE_SETTER_SUCCESS:   'DI_VALUE_SETTER_SUCCESS',
                      DI_VALUE_SETTER_FAILURE:   'DI_VALUE_SETTER_FAILURE',
                      DI_VALUE_SETTER_WRONG_FMT: 'DI_VALUE_SETTER_WRONG_FMT',
                      DI_VALUE_SETTER_KEY:       'DI_VALUE_SETTER_KEY',
                      DI_VALUE_SETTER_UNCHANGED: 'DI_VALUE_SETTER_UNCHANGED' }

    def fix(self, runner):
        self.log('Looking for custom tables without a UUID')

        dt_tuples = get_tables_without_uuid()
        self.log('Found %i tables' % len(dt_tuples))
        for dt_no, dt_sql_id in dt_tuples:

            if not table_is_active(dt_no):
                self.log('Table DT{} is not active and will be ignored!'.format(dt_no))
                continue

            self.log('Checking for UUID column in DT%s ("%s")' % (dt_no, dt_sql_id))

            assert dt_sql_id, dt_sql_id
            assert not dt_sql_id.startswith('V_'), dt_sql_id

            self.log('Adding UUID column to DT%s ("%s")' % (dt_no, dt_sql_id))
            di_num = add_uuid_column_to_table(dt_no)

            self.log('Updating the UUID column in DT415')

            update_dt415_uuid_column(dt_no, di_num)

        ppms.db_commit()

        return self.success()
