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

TABLE_TO_DATAITEM = {'150': '064044',
                     '151': '064045',
                     '152': '064046',
                     '153': '064047',
                     '154': '064048',
                     }

DELETE_STATEMENTS = [
    "DELETE FROM DT435 WHERE DI023517 = '001591' AND DI001538 IN (SELECT DI001591 FROM DT412 WHERE DI000238 IN ("
    "'064044', '064045', '064046', '064047', '064048'))",
    "DELETE FROM DT435 WHERE DI023517 = '007566' AND DI001538 IN (SELECT DI007566 FROM DT412 WHERE DI000238 IN ("
    "'064044', '064045', '064046', '064047', '064048'))",
    "DELETE FROM DT412 WHERE DI000238 IN ('064044', '064045', '064046', '064047', '064048')"]

ALTER_STATEMENTS = {DBMS_ORACLE: ['ALTER TABLE {table_name} ADD UUID RAW(16)',
                                  'ALTER TABLE {table_name} MODIFY ("UUID" DEFAULT SYS_GUID())',
                                  'UPDATE {table_name} SET UUID = DEFAULT',
                                  'ALTER TABLE {table_name} ADD CONSTRAINT {table_name}UUID_NOT_NULL CHECK("UUID" IS '
                                  'NOT NULL)',
                                  'ALTER TABLE {table_name} ADD CONSTRAINT UUID_UNIQUE_{table_num} UNIQUE (UUID)'],
                    DBMS_MSSQL: ['ALTER TABLE {table_name} ADD UUID uniqueidentifier',
                                 'ALTER TABLE {table_name} ADD CONSTRAINT {table_name}DI{di_num}_DEFAULT DEFAULT '
                                 'newid() FOR [UUID]',
                                 'UPDATE {table_name} SET UUID = DEFAULT',
                                 'ALTER TABLE {table_name} ADD CONSTRAINT {table_name}UUID_NOT_NULL CHECK([UUID] IS '
                                 'NOT NULL)',
                                 'ALTER TABLE {table_name} ADD CONSTRAINT UUID_UNIQUE_{table_num} UNIQUE (UUID)']}


# In case we're running a old migration framework version
def table_exists(table_name):
    """Check if a table exists"""
    dbms = ppms.db_get_dbms_name()

    if dbms == DBMS_ORACLE:
        query = """SELECT count(*) FROM user_tables
                   WHERE
                     table_name = '{table_name}'"""

    elif dbms == DBMS_MSSQL:
        query = """SELECT COUNT(*) FROM sys.tables
                   WHERE
                     Object_ID = Object_ID(N'{table_name}')"""

    else:
        raise NotImplementedError(dbms)

    return ppms.db_select(query.format(table_name=table_name))[0][0] == 1


class AddUUIDColumnsToKDB(SQLPacket):
    """Adds UUID columns to the tables in the KDB"""
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 19001

    sql_script = 'WI19001_uuid_columns.sql'

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

    def uuid_columns_already_exist(self):
        # We assume that if one exists they all exist
        for di in ('064044', '064045', '064046', '064047', '064048'):
            record = ppms.search_record(412, [di], ['di'])

            if record is None:
                return False

        return True

    def add_uuid_definitions(self):
        self.apply_fix_ddl_file(self.sql_planta_de_dbms, self.sql_script, split=True)

    def wipe_potential_conflicts(self):
        for statement in DELETE_STATEMENTS:
            self.modify_and_log(statement)

    def set_uuid_column_in_dt415(self):
        for table, dataitem in TABLE_TO_DATAITEM.items():
            table_record = ppms.search_record(415, [table], ['uuid_di'])
            table_record.uuid_di.set_value(dataitem)
            table_record.save()

    def add_uuid_columns_to_existing_tables(self):
        for table, di_num in TABLE_TO_DATAITEM.items():
            table_name = 'DT%s' % table

            if not table_exists(table_name):
                self.log('Table "%s" does not exist on the database - nothing to do' % table_name)
                continue

            if self.column_exists(table_name=table_name, column_name='UUID'):
                self.log('Table "%s" already has a UUID column - nothing to do' % table_name)
                continue

            for statement in ALTER_STATEMENTS[self.DBMS]:
                formatted_statement = statement.format(table_name=table_name, table_num=table,
                                                       di_num=di_num)

                self.modify_and_log(formatted_statement, log_rows=False)

    def fix(self, packet_runner):
        if not self.uuid_columns_already_exist():
            self.log('Wiping potential conflicts')
            self.wipe_potential_conflicts()
            self.log('Adding UUID definitions to 412')
            self.add_uuid_definitions()
            self.log('Setting the UUID column value in DT 415')
            self.set_uuid_column_in_dt415()
            self.log('Adding the UUID column to the tables')
            self.add_uuid_columns_to_existing_tables()

        return self.success()
