# -*- coding: utf-8 -*-
from ppms import ppms
from ppms.constants import DBMS_ORACLE, DBMS_MSSQL
from ppms.migration import SQLPacket, PacketCategory, MaxVersionDependency

# those statements look very similar ;) couldn't we just use one? are there differences w.r.t. naming limits etc?
ADD_INDEX_ORACLE = "CREATE UNIQUE INDEX IDX_UUID_UNIQUE_{dt_id} on {tablename} (UUID)"
ADD_INDEX_MSSQL = "CREATE UNIQUE INDEX IDX_UUID_UNIQUE_{dt_id} on {tablename} (UUID)"

CHECK_INDEX_ORACLE = """select b.table_name
from user_ind_columns a, user_indexes b
where a.index_name=b.index_name
and a.column_name='UUID' and not exists 
(select * from user_ind_columns sub where sub.index_name=a.index_name and sub.column_name != a.column_name)
"""

CHECK_INDEX_MSSQL = """select OBJECT_NAME(b.object_id)
from sys.index_columns a, sys.indexes b
where a.index_id = b.index_id
and COL_NAME(b.object_id, a.column_id) = 'UUID' and not exists 
(select * from sys.index_columns sub where sub.index_id = a.index_id and sub.column_id != a.column_id)
"""

ddl_statements = {DBMS_ORACLE: ADD_INDEX_ORACLE, DBMS_MSSQL: ADD_INDEX_MSSQL}
get_index_statements = {DBMS_ORACLE: CHECK_INDEX_ORACLE, DBMS_MSSQL: CHECK_INDEX_MSSQL}


def table_exists_in_database(sql_id):
    query = {DBMS_ORACLE: "select 1 from {} where rownum <= 1",
             DBMS_MSSQL: "select TOP 1 1 from {}"}[ppms.db_get_dbms_name()]
    try:
        ppms.db_select(query.format(sql_id))
    except ppms.DatabaseError:
        return False

    return True



class AddUUIDIndices(SQLPacket):
    """Add unique indices on all UUID key columns"""
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 13965

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

    def fix(self, runner):
        data_dictionary = ppms.get_data_dictionary()
        dbms = ppms.db_get_dbms_name()
        failures = False

        index_statement = ddl_statements[dbms]
        get_index_statement = get_index_statements[dbms]

        tables_with_uuid_index = [res[0] for res in ppms.db_select(get_index_statement)]

        for table in data_dictionary.data_tables.values():
            if table.uuid_di:
                tablename = table.sql_name
                tableid = 'DT{di:03d}'.format(di=table.id)

                try:
                    if tablename in tables_with_uuid_index:
                        self.log('UUID index exists for ' + tablename)
                        continue

                    if not table_exists_in_database(tablename):
                        self.log('Table {tablename} does not exist - skipping'.format(tablename=tablename))
                        continue

                    self.log('Adding index IDX_UUID_UNIQUE_{dt_id} to {tablename}...'.format(tablename=tablename, dt_id=tableid))
                    ppms.db_modify(index_statement.format(tablename=tablename, dt_id=tableid))
                    self.log('Succeeded')
                except ppms.DatabaseError as err:
                    self.log('Index check or creation failed:')
                    failures = True
                    self.log(err)

        return self.fail() if failures else self.success()
