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

ALTER_TABLE_STATEMENTS = {DBMS_ORACLE: ("alter table DT496 add CAN_INSERT number(5,0) default 1",
                                        "update DT496 set CAN_INSERT = 1",
                                        "alter table DT496 add constraint DT496CAN_INSERT_NOT_NULL check(CAN_INSERT "
                                        "is not null)",
                                        "alter table DT497 add CAN_INSERT number(5,0) default 1",
                                        "update DT497 set CAN_INSERT = 1",
                                        "alter table DT497 add constraint DT497CAN_INSERT_NOT_NULL check(CAN_INSERT "
                                        "is not null)",
                                        "alter table DT496 add CAN_DELETE number(5,0) default 1",
                                        "update DT496 set CAN_DELETE = 1",
                                        "alter table DT496 add constraint DT496CAN_DELETE_NOT_NULL check(CAN_DELETE "
                                        "is not null)",
                                        "alter table DT497 add CAN_DELETE number(5,0) default 1",
                                        "update DT497 set CAN_DELETE = 1",
                                        "alter table DT497 add constraint DT497CAN_DELETE_NOT_NULL check(CAN_DELETE "
                                        "is not null)",
                                        "alter table DT498 add CAN_DELETE number(5,0) default 1",
                                        "update DT498 set CAN_DELETE = 0",
                                        "alter table DT498 add constraint DT498CAN_DELETE_NOT_NULL check(CAN_DELETE "
                                        "is not null)",
                                        "alter table DT499 add CAN_DELETE number(5,0) default 1",
                                        "update DT499 set CAN_DELETE = 0",
                                        "alter table DT499 add constraint DT499CAN_DELETE_NOT_NULL check(CAN_DELETE "
                                        "is not null)",
                                        "alter table DT498 add PLACEHOLDER varchar2(80 char)",
                                        "alter table DT499 add PLACEHOLDER varchar2(80 char)"),

                          DBMS_MSSQL: ("alter table DT496 add CAN_INSERT numeric(5,0) default 1",
                                       "update DT496 set CAN_INSERT = 1",
                                       "alter table DT496 add constraint DT496CAN_INSERT_NOT_NULL check([CAN_INSERT] "
                                       "is not null)",
                                       "alter table DT497 add CAN_INSERT numeric(5,0) default 1",
                                       "update DT497 set CAN_INSERT = 1",
                                       "alter table DT497 add constraint DT497CAN_INSERT_NOT_NULL check([CAN_INSERT] "
                                       "is not null)",
                                       "alter table DT496 add CAN_DELETE numeric(5,0) default 1",
                                       "update DT496 set CAN_DELETE = 1",
                                       "alter table DT496 add constraint DT496CAN_DELETE_NOT_NULL check([CAN_DELETE] "
                                       "is not null)",
                                       "alter table DT497 add CAN_DELETE numeric(5,0) default 1",
                                       "update DT497 set CAN_DELETE = 1",
                                       "alter table DT497 add constraint DT497CAN_DELETE_NOT_NULL check([CAN_DELETE] "
                                       "is not null)",
                                       "alter table DT498 add CAN_DELETE numeric(5,0) default 1",
                                       "update DT498 set CAN_DELETE = 0",
                                       "alter table DT498 add constraint DT498CAN_DELETE_NOT_NULL check([CAN_DELETE] "
                                       "is not null)",
                                       "alter table DT499 add CAN_DELETE numeric(5,0) default 1",
                                       "update DT499 set CAN_DELETE = 0",
                                       "alter table DT499 add constraint DT499CAN_DELETE_NOT_NULL check([CAN_DELETE] "
                                       "is not null)",
                                       "alter table DT498 add PLACEHOLDER varchar(80)",
                                       "alter table DT499 add PLACEHOLDER varchar(80)")}


class AddNewProcessModelSchema(SQLPacket):
    """Add new columns for the process model

    DT496: CAN_INSERT, CAN_DELETE
    DT497: CAN_INSERT, CAN_DELETE
    DT498: CAN_DELETE, PLACEHOLDER
    DT499: CAN_DELETE, PLACEHOLDER
    """
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 16848

    def get_dependencies(self):
        return (MinVersionDependency(version='39.5.0'),
                MaxVersionDependency(version='39.5.5'))

    def fix(self, runner):
        for statement in ALTER_TABLE_STATEMENTS[ppms.db_get_dbms_name()]:
            try:
                self.modify_and_log(statement)
            except ppms.DatabaseError:
                self.log("The changes of statement: " + statement + "\n has already been made ans will be skipped.")

        return self.success()