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


class UpdateWorkFlag(SQLPacket):
    """Updates 'work' in period table"""
    run_on_startup = True
    done_after_success = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 22092


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

    def fix(self, packet_runner):
        resource_query = {DBMS_ORACLE: """select
                                            di001218,
                                            level
                                          from
                                            dt467
                                          where
                                            di001218 in (select
                                                           di009753
                                                         from
                                                           dt467)
                                          start with
                                            di001218 in (select
                                                           di001218
                                                         from
                                                           dt467
                                                         where
                                                           di009753 is null)
                                          connect by prior
                                            di001218 = DI009753
                                          order by
                                            level desc""",
                          DBMS_MSSQL: """WITH
                                           r(di001218, resource_level)
                                         AS 
                                           (SELECT
                                              di001218,
                                              1 as resource_level
                                            FROM
                                              dt467
                                            WHERE
                                              di001218 in (select
                                                             di001218
                                                           from
                                                             dt467
                                                           where
                                                             di009753 is null)
                                            UNION ALL
                                              SELECT
                                                sub.di001218,
                                                resource_level + 1
                                              FROM
                                                dt467 as sub,
                                                r
                                              WHERE
                                                r.di001218 = sub.di009753)
                                        SELECT
                                          di001218,
                                          resource_level
                                        FROM
                                          r
                                        where
                                          di001218 in (select
                                                         di009753
                                                       from
                                                         dt467)
                                        order by
                                          resource_level desc"""}
        update_query = {DBMS_ORACLE: """update
                                          dt468 period
                                        set
                                          period.di001364 = (select
                                                               max(di001364)
                                                             from 
                                                               dt468 period_subselect
                                                             left join 
                                                               DT467 child_res
                                                             on
                                                               period_subselect.DI001326 = child_res.DI001218
                                                             where 
                                                               child_res.DI009753 = period.DI001326
                                                             and
                                                               period_subselect.DI001327 = period.DI001327
                                                             group by 
                                                               period_subselect.DI001327)
                                        where
                                          period.DI001326 in ('{resources}')
                                        and
                                          exists (select
                                                    1
                                                  from
                                                    dt468 period_subselect
                                                  left join
                                                    DT467 child_res
                                                  on
                                                    period_subselect.DI001326 = child_res.DI001218
                                                  where
                                                    child_res.DI009753 = period.DI001326
                                                  and
                                                    period_subselect.DI001327 = period.DI001327)""",
                        DBMS_MSSQL: """update
                                         period
                                       set 
                                         period.di001364 = subselect.di001364_sum
                                       FROM
                                         dt468 period
                                       INNER JOIN
                                            (select
                                            MAX(di001364) as di001364_sum,
                                            di001327,
                                            DI009753
                                            from
                                            dt468
                                            left join
                                            DT467 child_res
                                            on
                                            DI001326 = child_res.DI001218    
                                            group by
                                            DI001327, DI009753) subselect
                                        ON
                                            subselect.DI001327 = period.DI001327
                                            and subselect.DI009753 = period.DI001326
                                       where
                                         period.DI001326 in ('{resources}')
                                       and
                                         exists (select
                                                   1
                                                 from
                                                   dt468 period_subselect
                                                 left join
                                                   DT467 child_res
                                                 on
                                                   period_subselect.DI001326 = child_res.DI001218
                                                 where 
                                                   child_res.DI009753 = period.DI001326
                                                 and 
                                                   period_subselect.DI001327 = period.DI001327)"""}

        all_resources = ppms.db_select(resource_query[self.DBMS])
        resources_by_level = {}
        max_level = 0

        for resource, _level in all_resources:
            level = int(_level)

            if level > max_level:
                max_level = level

            if level not in resources_by_level.keys():
                resources_by_level[level] = []

            resources_by_level[level].append(resource)

        for i in range(max_level, 0, -1):
            resources = "', '".join(resources_by_level[i])

            self.modify_and_log(update_query[self.DBMS].format(resources=resources))

        return self.success()
