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


class SetDfWithLengthOfResName(SQLPacket):
    """Changes and logs all DFs which have any DIs related to DI001275. Extends the df_length of Customer DIs.
        Sets the DF-width (DI003703) to the current width, if the DF doesn't have a df width.
        Extends the df_length (DI000246) to 200 of Customer DIs related to DI001275 (incarnation/exits)
    """
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 20827

    rel_dis = {'001275', '062118', '051887'}  # 062118 a py value range (WI 21591)
                                                # 051887 compute sql

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

    def fix(self, packet_runner):
        sys_lic = ppms.uvar_get('@3')

        relevant_dis_tmp2 = set([])

        while self.rel_dis != relevant_dis_tmp2:
            relevant_dis_tmp2 = set(self.rel_dis)
            self.rel_dis.update(get_dependant_exit_dis(self.rel_dis))
            self.rel_dis.update(get_dependant_co_dis(self.rel_dis))
            self.rel_dis.update(get_dependant_inc_dis(self.rel_dis))

        get_changed_fds_sql = """SELECT DI000307, DI000306 FROM DT411 
                                WHERE DI003703 = 0
                                AND DI000213 in {rel_dis}
                                AND DI001862 = '{sys_lic}'"""

        das = dict()
        for df, da in ppms.db_select(
                get_changed_fds_sql.format(rel_dis=format_list_for_sql_query(self.rel_dis), sys_lic=sys_lic)):
            try:
                das[da].append(df)
            except KeyError:
                das[da] = [df]

        self.log("Changes are made in following DAs")
        for da_id, df_ids in das.items():
            self.log('DA {da_id}: {df_id}.'.format(da_id=da_id, df_id=', '.join(str(x) for x in df_ids)))

        if self.DBMS == DBMS_MSSQL:
            set_df_width_sql_with_symbol = """UPDATE DT411 
                    SET DI003703=DI000246*DI029853+13+4*DI001744
                    From DT411 
                    Inner Join DT424 ON DI000234 = DI000636
                    Inner Join DT412 ON DI000238 = DI000213
                    WHERE DI003703 = 0 
                    AND DI000213 in {rel_dis}
                    AND DI001862 = '{sys_lic}'"""

            set_df_width_sql_without_symbol = """UPDATE DT411  
                    SET DI003703=DI000246*21+17
                    From DT411 
                    Inner Join DT412 ON DI000238 = DI000213
                    WHERE DI003703 = 0 
                    AND (DI000234 = '' or DI000234 IS NULL)
                    AND DI000213 in {rel_dis}
                    AND DI001862 = '{sys_lic}'"""
        else:
            set_df_width_sql_with_symbol = """UPDATE 
                    (SELECT DI003703 as breite, DI000246*DI029853+13+4*DI001744 as wert
                        From DT411 
                        Inner Join DT424 ON DI000234 = DI000636
                        Inner Join DT412 ON DI000238 = DI000213
                        WHERE DI003703 = 0 
                        AND DI000213 in {rel_dis}
                        AND DI001862 = '{sys_lic}'
                        )t
                    set t.breite = t.wert"""

            set_df_width_sql_without_symbol = """UPDATE 
                    (SELECT DI003703 as breite, DI000246*21+17 as wert
                        From DT411 
                        Inner Join DT412 ON DI000238 = DI000213
                        WHERE DI003703 = 0 
                        AND (DI000234 = '' or DI000234 IS NULL)
                        AND DI000213 in {rel_dis}
                        AND DI001862 = '{sys_lic}'
                        )t
                    set t.breite = t.wert"""

        self.modify_and_log(set_df_width_sql_with_symbol.format(rel_dis=format_list_for_sql_query(self.rel_dis), sys_lic=sys_lic))
        self.modify_and_log(set_df_width_sql_without_symbol.format(rel_dis=format_list_for_sql_query(self.rel_dis), sys_lic=sys_lic))


        db_df_length_sql = """UPDATE DT412 SET DI000254 = 200, DI000246 = 200
                            WHERE DI000275 = 1
                            AND DI001869 = '{sys_lic}'
                            AND DI000238 in {rel_dis}"""

        self.log("Changes the DB-Legnth and DF-Length of custom DIs")
        self.modify_and_log(db_df_length_sql.format(rel_dis=format_list_for_sql_query(self.rel_dis), sys_lic=sys_lic))

        return self.success()


def get_dependant_exit_dis(di_ids):
    relevant_dis = set(di_ids)
    relevant_dis_tmp = set([])
    while relevant_dis != relevant_dis_tmp:
        dis_of_exit_sql = """SELECT DI000238 FROM DT412 Inner Join DT445 on DI000609=DI000252
                       WHERE DI001616 in {relevant_dis}""".format(
            relevant_dis=format_list_for_sql_query(relevant_dis))
        relevant_dis_tmp = set(relevant_dis)
        dis_of_exit = ppms.db_select(dis_of_exit_sql)
        relevant_dis.update([di[0] for di in dis_of_exit])
    return relevant_dis

def get_dependant_co_dis(di_ids):
    relevant_dis = set(di_ids)
    relevant_dis_tmp = set([])
    while relevant_dis != relevant_dis_tmp:
        tmp = set([])
        relevant_dis_tmp = set(relevant_dis)
        for di in relevant_dis:

            dis_with_co_sql = """SELECT DI000238 FROM DT412
                            WHERE DI027352 like '%DI{di}%'
                            AND DI001990 = 'CO'""".format(di=di)

            dis_with_incarnation = ppms.db_select(dis_with_co_sql)
            tmp.update([di[0] for di in dis_with_incarnation])
        relevant_dis.update(tmp)
    return relevant_dis

def get_dependant_inc_dis(di_ids):
    relevant_dis = set(di_ids)
    relevant_dis_tmp = set([])
    while relevant_dis != relevant_dis_tmp:
        dis_with_incarnation_sql = """SELECT DI000286 FROM DT440
                            WHERE DI000364 in {relevant_dis}
                            AND DI001786 = 'KO'""".format(relevant_dis=format_list_for_sql_query(relevant_dis))

        relevant_dis_tmp = set(relevant_dis)
        dis_with_incarnation = ppms.db_select(dis_with_incarnation_sql)
        relevant_dis.update([di[0] for di in dis_with_incarnation])
    return relevant_dis