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

# 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_{table_id} on {table_name} (UUID)"
ADD_INDEX_MSSQL = "CREATE UNIQUE INDEX IDX_UUID_UNIQUE_{table_id} on {table_name} (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 DISTINCT OBJECT_NAME(b.object_id) table_name
FROM sys.index_columns a,
  sys.indexes b
WHERE a.object_id = b.object_id
AND a.index_id = b.index_id
AND b.is_unique = 1
AND COL_NAME(b.object_id , a.column_id)='UUID'
"""

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 {}"}[planta.db_get_dbms_name()]
    try:
        planta.db_select(query.format(sql_id))
    except planta.DatabaseError:
        return False

    return True


class AddUUIDIndices2(SQLPacket):
    """Add unique indices on all UUID key columns
    Fixes the Problems with the Packet from S14
    """
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 16280

    def get_dependencies(self):
        return (MaxVersionDependency(version='39.5.7', version_type=VersionType.CUSTOMIZING))

    def fix(self, packet_runner):
        data_dictionary = planta.get_data_dictionary()
        dbms = planta.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 planta.db_select(get_index_statement)]

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

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

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

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

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