# -*- coding: utf-8 -*-
from ppms import ppms
from ppms.constants import *
from ppms.migration import SQLPacket, PacketCategory, MaxVersionDependency, VersionType, PacketDependency, \
    PacketState, BasePacket

PACKET_PATH = 'migration.hotfix.S_39_5_4.wi11533_port_universal_interface_VoE.PortCustomizingUniversalInterface'
SERVER_PACKET = 'migration.hotfix.S_39_5_12.wi13337_new_column.PortPositionColumnToInterfaceLogging'

MIGRATE_OLD_DATA = {DBMS_ORACLE: '''MERGE
INTO    INTERFACE_LOGGING
USING   (
        SELECT  UUID "ID", ROW_NUMBER() OVER (PARTITION BY CONFIG_ID ORDER BY TIMESTAMP) AS "ROWN"
        FROM    INTERFACE_LOGGING
        )
ON      (UUID = "ID")
WHEN MATCHED THEN
UPDATE
SET     POSITION = "ROWN"''',
                    DBMS_MSSQL: '''MERGE
INTO    INTERFACE_LOGGING
USING   (
        SELECT  UUID "ID", ROW_NUMBER() OVER (PARTITION BY CONFIG_ID ORDER BY TIMESTAMP) AS "ROWN"
        FROM    INTERFACE_LOGGING
        ) AS MERGE_TABLE ("ID", "ROWN")
ON      (UUID = "ID")
WHEN MATCHED THEN
UPDATE
SET     POSITION = "ROWN";'''}


class PortPositionColumnToInterfaceLogging(SQLPacket):
    """Port the "POSITION" column to the "INTERFACE_LOGGING" table"""
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 13337

    QUERY = {DBMS_ORACLE: "alter table INTERFACE_LOGGING add POSITION number(10,0) default 0 not null",
             DBMS_MSSQL: "alter table INTERFACE_LOGGING add POSITION numeric(10,0) default 0 not null"}

    def get_dependencies(self):
        return (MaxVersionDependency(version='39.5.4', version_type=VersionType.CUSTOMIZING),
                PacketDependency(packet_path=PACKET_PATH, expected_state=PacketState.INSTALLED))

    def fix(self, runner):
        packet = BasePacket.from_packet_path(SERVER_PACKET)
        if packet is not None and packet.last_state == PacketState.INSTALLED:
            self.log('This packet is not necessary because the server packet that ports this column was already '
                     'installed')
            return self.success()

        query = self.QUERY[ppms.db_get_dbms_name()]
        self.modify_and_log(query)

        self.modify_and_log(MIGRATE_OLD_DATA[ppms.db_get_dbms_name()])

        return self.success()