# -*- coding: latin-1 -*-
from ppms.migration import SQLPacket, PacketCategory, MinVersionDependency, MaxVersionDependency, VersionType
from ppms import ppms
from ppms.constants import *
import os
        
class CreateSchemaTables(SQLPacket):
    ''' Add new columns to update VoE schema to Venus part2'''
    wi_number = 14040
    version = 1
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY

    sql_file = 'wi14040_create_tables2.sql'

    sql_stmnts_add = [{DBMS_ORACLE: '''alter table DT461 add DI063188 number(10,0) default 0''',
                       DBMS_MSSQL: '''alter table DT461 add DI063188 numeric(10,0) default 0'''},
                      {DBMS_ORACLE: '''alter table DT461 add DI063189 date default TO_DATE('01.01.1970','DD.MM.YYYY')''',
                       DBMS_MSSQL: '''alter table DT461 add DI063189 date default convert(date, '01.01.1970', 104)'''}]

    sql_stmnts_modify = [{DBMS_ORACLE: '''alter table DT461 modify DI063188 number(10,0) default 0''',
                       DBMS_MSSQL: '''alter table DT461 alter column DI063188 numeric(10,0)'''},
                      {DBMS_ORACLE: '''alter table DT461 modify DI063189 date default TO_DATE('01.01.1970','DD.MM.YYYY')''',
                       DBMS_MSSQL: '''alter table DT461 alter column DI063189 date'''}]
    
    update_null_stmnts = [{DBMS_ORACLE: "update DT461 set DI063188 = 0 where DI063188 is null"},
                          {DBMS_ORACLE: "update DT461 set DI063189 = TO_DATE('01.01.1970','DD.MM.YYYY') where DI063189 is null",
                           DBMS_MSSQL: "update DT461 set DI063189 = convert(date, '01.01.1970', 104) where DI063189 is null"},
                          {DBMS_ORACLE: "update DT461 set DI062551 = 0 where DI062551 is null"},
                          {DBMS_ORACLE: "update DT480 set DI063124 = TO_DATE('01.01.1970','DD.MM.YYYY') where DI063124 is null",
                           DBMS_MSSQL: "update DT480 set DI063124 = convert(date, '01.01.1970', 104) where DI063124 is null"},
                          {DBMS_ORACLE: "update DT810 set DI062994 = 0 where DI062994 is null"},
                          {DBMS_ORACLE: "update DT812 set DI062989 = 0 where DI062989 is null"},
                          {DBMS_ORACLE: "update DT812 set DI062995 = 0 where DI062995 is null"},
                          {DBMS_ORACLE: "update DT823 set DI062806 = 0 where DI062806 is null"}]
    
    def get_dependencies(self):
        return (MinVersionDependency(version='39.5.0', version_type=VersionType.CUSTOMIZING),
                MaxVersionDependency(version='39.5.2', version_type=VersionType.CUSTOMIZING))
    
    def fix(self, runner):
        for index in range(len(self.sql_stmnts_add)):
            stmnt = self.sql_stmnts_add[index][self.DBMS]
            try:
                self.modify_and_log(stmnt)
            except ppms.DatabaseError as e:
                if 'ORA-01430' in str(e) or 'sqlserver' in str(e):
                    self.log('DI already exists... Trying to modify')
                    self.modify_and_log(self.sql_stmnts_modify[index][self.DBMS])
                else:
                    raise e

        for stmnt in self.update_null_stmnts:
            if self.DBMS in stmnt:
                self.modify_and_log(stmnt[self.DBMS])
            else:
                self.modify_and_log(stmnt[DBMS_ORACLE])

        path = self.get_sql_file_path(self.sql_planta_de_dbms, self.sql_file)

        for query in self.get_queries_from_file(file_path=path, split=True):
            try:
                self.modify_and_log(query)

            except ppms.DatabaseError as e:
                if 'sqlserver' in str(e):
                    self.log(
                        'There was an error in statement: ' + query + '. The statement is probably not needed in this system, please check the logfiles.')
                else:
                    raise e
        
        return self.success()
