# -*- coding: utf-8 -*-
from datetime import datetime, timedelta

from ppms import ppms


def ppms_timestamp_to_date(ppms_timestamp):
    timestamp = float(ppms_timestamp) * 24 * 60 * 60
    _datetime = datetime.fromtimestamp(0) + timedelta(seconds=timestamp)
    return _datetime.strftime("%d.%m.%Y")


def fix():
    dbms = ppms.db_get_dbms_name()
    resource_query = {'000002': """select
                                        di001218
                                      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""",
                      '000004': """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
                                    FROM
                                      r
                                    where
                                      di001218 in (select
                                                     di009753
                                                   from
                                                     dt467)
                                    order by
                                      resource_level desc"""}
    update_query = {'000002': """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 = '{parent_res_id}'
                                                         and
                                                           period_subselect.DI001327 = period.DI001327
                                                         group by 
                                                           period_subselect.DI001327)
                                    where
                                      period.DI001327 BETWEEN to_date('{start_period}', 'DD.MM.YYYY') AND to_date('{end_period}', 'DD.MM.YYYY')
                                    and
                                      period.DI001326 = '{parent_res_id}'
                                    and
                                      exists (select
                                                1
                                              from
                                                dt468 period_subselect
                                              left join
                                                DT467 child_res
                                              on
                                                period_subselect.DI001326 = child_res.DI001218
                                              where
                                                child_res.DI009753 = '{parent_res_id}'
                                              and
                                                period_subselect.DI001327 = period.DI001327)""",
                    '000004': """update
                                     period
                                   set 
                                     period.di001364 = subselect.di001364_sum
                                   FROM
                                     dt468 period
                                   INNER JOIN
                                     (select
                                        MAX(di001364) as di001364_sum,
                                        di001327
                                      from
                                        dt468
                                      left join
                                        DT467 child_res
                                      on
                                        DI001326 = child_res.DI001218
                                      where 
                                        child_res.DI009753 = '{parent_res_id}'
                                      group by
                                        DI001327) subselect
                                   ON
                                     subselect.DI001327 = period.DI001327
                                   where
                                     period.DI001327 BETWEEN convert(datetime, '{start_period}', 104) AND convert(datetime, '{end_period}', 104)
                                   and
                                     period.DI001326 = '{parent_res_id}'
                                   and
                                     exists (select
                                               1
                                             from
                                               dt468 period_subselect
                                             left join
                                               DT467 child_res
                                             on
                                               period_subselect.DI001326 = child_res.DI001218
                                             where 
                                               child_res.DI009753 = '{parent_res_id}'
                                             and 
                                               period_subselect.DI001327 = period.DI001327)"""}

    all_resources = [resource for sublist in ppms.db_select(resource_query[dbms]) for resource in sublist]

    for resource in all_resources:
        resource_record = ppms.search_record(467, [resource], ['start_period', 'end_period'], True)

        if not resource_record:
            continue

        start_period = ppms_timestamp_to_date(resource_record.start_period.get_value())
        end_period = ppms_timestamp_to_date(resource_record.end_period.get_value())

        ppms.db_modify(update_query[dbms].format(parent_res_id=resource,
                                                 start_period=start_period,
                                                 end_period=end_period))
    
    ppms.ui_message_box(str(len(all_resources)) + " parent resources were updated.")
    
fix()