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

class SetINITRANS(SQLPacket):
    """Changes INITRANS for all active Database tables to prevent ITL shortage deadlocks if long-running delete- and update-processes access the same table at the same time

    Only necessary on Oracle DB"""
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 20422

    active_tables_sql = "SELECT di000216 FROM DT415 INNER JOIN DT420 \
ON di000266 = di000289 \
WHERE dt415.enabled = 1 AND dt420.enabled = 1 AND generated = 1 AND di000265 != 400 \
AND di000216 IN (SELECT table_name FROM all_tables)"
    history_tables_sql = "SELECT concat(di000216, '_HIS') FROM DT415 INNER JOIN DT420 \
ON di000266 = di000289 \
WHERE dt415.enabled = 1 AND dt420.enabled = 1 AND generated = 1 AND di000265 != 400 AND audited = 1 \
AND di000216 IN (SELECT table_name FROM all_tables)"
    modify_table_sql = "ALTER TABLE {table} INITRANS 5 MAXTRANS 255"


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

    def fix(self, packet_runner):
        if self.DBMS == DBMS_ORACLE:
            active_tables = self.select_and_log(self.active_tables_sql)
            history_tables = self.select_and_log(self.history_tables_sql)

            for tab_name in active_tables + history_tables:
                self.modify_and_log(self.modify_table_sql.format(table=tab_name[0]))

            return self.success()
        else:
            return self.success() # Oracle-specific table modification -> other DBs (read: MSSQL) are always OK
