from ppms import ppms
from ppms.constants import *
from ppms.migration import SQLPacket, PacketCategory, MaxVersionDependency, MinVersionDependency, \
    get_packet_instance_from_path


OTHER_PACKET = 'migration.hotfix.S_39_5_15.wi14477_p_calc_periods.UpdatePeriodCalculationProcedureAgain'


class ChangeLanguageOfPeriodCreation(SQLPacket):
    "Change Language of Period creation tables and procedures"

    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 11721

    procedure_sql_file = "WI11721_change_language_P_CALC_PERIODS.sql"
    function_sql_file = "WI11721_change_language_PLANTA_F_CALC_PERIOD_FACTOR.sql"

    TABLE_SQL = {DBMS_ORACLE: ("""DROP TABLE DT468_TEMP""",
                               """CREATE TABLE DT468_TEMP (
                               "RES" VARCHAR2(20 CHAR),
                               "PERIOD" DATE,
                               "WORK_P" NUMBER,
                               "FACTOR_AMOUNT" NUMBER,
                               "HOLIDAY" NUMBER,
                               "ABSENCE" NUMBER,
                               "BASE_LOAD" NUMBER,
                               "FUNCTIONAL" NUMBER,
                               "MISC" NUMBER,
                               "UNITS_PERIOD" NUMBER,
                               "AVAIL_CAP" NUMBER,
                               "RSC" VARCHAR2(50 BYTE),
                               "COMMENTS" NUMBER,
                               "FAC_3" NUMBER,
                               "FAC_4" NUMBER,
                               "FAC_5" NUMBER )""",
                               """ALTER TABLE "DT468_TEMP" MODIFY ("RES" NOT NULL ENABLE)"""),
                 DBMS_MSSQL: ("""DROP TABLE DT468_TEMP""",
                              """DROP PROCEDURE P_CALC_PERIODS""",
                              """DROP FUNCTION PLANTA_F_CALC_PERIOD_FACTOR""",
                              """CREATE TABLE DT468_TEMP (
                                  "RES" VARCHAR(20) NOT NULL,
                                  "PERIOD" DATE,
                                  "WORK_P"      FLOAT,
                                  "FACTOR_AMOUNT"   FLOAT,
                                  "HOLIDAY"      FLOAT,
                                  "ABSENCE" FLOAT,
                                  "BASE_LOAD"   FLOAT,
                                  "FUNCTIONAL"    FLOAT,
                                  "MISC"   FLOAT,
                                  "UNITS_PERIOD"    FLOAT,
                                  "AVAIL_CAP"    FLOAT,
                                  "RSC"         VARCHAR(50),
                                  "COMMENTS"   FLOAT,
                                  "FAC_3"       FLOAT,
                                  "FAC_4"       FLOAT,
                                  "FAC_5"       FLOAT)""")}

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

    def fix(self, runner):
        other_packet = get_packet_instance_from_path(OTHER_PACKET)
        other_packet.create_migrationrule()
        if other_packet:
            self.log('Deleting obsolete packet "{}"'.format(other_packet))
            other_packet.delete()

        if ppms.db_get_dbms_name() == DBMS_ORACLE:
            for statement in self.TABLE_SQL[DBMS_ORACLE]:
                self.modify_and_log(statement)

        if ppms.db_get_dbms_name() == DBMS_MSSQL:
            for statement in self.TABLE_SQL[DBMS_MSSQL]:
                self.modify_and_log(statement)

        self.apply_fix_ddl_file(self.sql_planta_de_dbms, self.function_sql_file, False)
        self.apply_fix_ddl_file(self.sql_planta_de_dbms, self.procedure_sql_file, False)

        return self.success()
