# -*- coding: utf-8 -*-

from planta_logging.logging import *
from ppms import ppms
from ppms.constants import *
from ppms.migration import BasePacket, SQLPacket, PacketCategory

from ppms.period import aggregate_periods_from_start_to_end
from ppms.ressource import get_all_ressources_of_department

from scheduling import *


class FixLoadPeriodDeviations(SQLPacket):
    """A special packet that provides cleanup routines for the scheduler.

    Since even a complete rescheduling pass will touch only data corresponding
    to live projects, this package provides a way to level deviations between
    resources' periods and its planned loads throughout the entire database.

    Detects and fixes
    - Deviations of actual and planned efforts between loads and periods within
      the resource's start and end dates
    - Deviations within resource periods wrt. utilization value, difference,
      and ratio
    """

    special_snowflake = True
    run_on_startup = False
    done_after_success = False
    category = PacketCategory.CATEGORY_HELPER
    wi_number = 21127

    rounding_precision = 8
    epsilon = 5e-9
    div_by_zero = 1e-300
    nullsafe = { DBMS_ORACLE: 'nvl',
                 DBMS_MSSQL:  'isnull' }


    def get_dummy_resource(self):
        return frozenset(['dummy'])


    def get_first_few_resources(self):
        top = 25
        return frozenset([row[0] for row in ppms.db_select('''select di001218 from dt467 order by di001218''')[:top]])


    def get_all_resources(self):
        return frozenset([row[0] for row in ppms.db_select('''select di001218 from dt467 order by di001218''')])


    # pick one of above selector methods to force processing, i.e.
    # get_forced_resources = get_all_resources  # to process everything
    get_forced_resources = None

    debug = False
    verbose = False


    recurring_deviations = False


    def fix(self, runner):
        booked_hours_deviations = \
        utilization_deviations = \
        load_period_deviations = None

        if self.get_forced_resources:
            booked_hours_deviations = \
            utilization_deviations = \
            load_period_deviations = self.get_forced_resources()
        else:
            booked_hours_deviations = self.check_booked_hours()
            utilization_deviations = self.check_utilization()
            load_period_deviations = self.check_period_load_deviations()

        fixed_resources = frozenset(set().union(booked_hours_deviations, utilization_deviations, load_period_deviations))

        # load/period deviations of actual effort require period fixes by
        # aggregating on same resource and date
        if len(booked_hours_deviations):
            self.fix_booked_hours(booked_hours_deviations)
            self.recheck_single(self.check_booked_hours,
                                'load vs. period deviations of actual effort')

        # load/period deviations of planned effort require symmetric zeroing of
        # reconciled values
        if len(load_period_deviations):
            self.fix_period_load_deviations(load_period_deviations)
            self.recheck_single(self.check_booked_hours,
                                'load vs. period deviations of planned effort')

        # utilization deviations require period fixes, and load/period
        # deviation fixes mess with the base for their calculation!
        if len(fixed_resources):
            self.fix_utilization(fixed_resources)
            self.recheck_single(self.check_booked_hours,
                                'utilization deviations in periods')

        # all fixes require a resource period aggregation
        if len(fixed_resources):
            self.aggregate_all_periods(fixed_resources)
            self.recheck_all('after period aggregation')

        # load/period deviations require a rescheduling to reconcile planned
        # efforts again; incremental aggregation is included
        if len(load_period_deviations):
            self.reschedule_all_projects()
            self.recheck_all('after rescheduling')

        # If we fail this, false positives when checking for recurring
        # deviations result in a rollback; plus, analysis is impossible since
        # all modifications will be reverted!
        return self.success()  # if not self.recurring_deviations else self.fail()


    def recheck_all(self, when):
        self.log_heading('Re-checking {}.'.format(when))
        self.recheck_single(self.check_booked_hours,
                            'load vs. period deviations of actual effort',
                            when)
        self.recheck_single(self.check_period_load_deviations,
                            'load vs. period deviations of planned effort',
                            when)
        self.recheck_single(self.check_utilization,
                            'utilization deviations in periods',
                            when)


    def recheck_single(self, check_method, what, when='right after fixup routine'):
        if not len(check_method()):
            self.log('OK.')
        else:
            self.recurring_deviations = True
            self.log_heading('Found recurring {} {}!'.format(what, when))


    def check_booked_hours(self):
        self.log_heading('Searching for resources with deviating actual effort between periods and loads.')
        booked_hours_deviations = self.format_select_and_log(self.check_booked_hours_deviation_stmt,
                                                             precision=self.rounding_precision,
                                                             nullsafe=self.nullsafe[self.DBMS])
        return frozenset(deviation[0] for deviation in booked_hours_deviations)  # eliminate duplicates

    def fix_booked_hours(self, booked_hours_deviations):
        booked_hours_deviations = list(booked_hours_deviations)
        booked_hours_deviations.sort()
        self.log_heading('Fixing load vs. period deviations of actual effort for resources {!s}'.format(booked_hours_deviations))
        self.fix_in_chunks(self.fix_booked_hours_chunk, booked_hours_deviations)

    def fix_booked_hours_chunk(self, booked_hours_deviations):
        for stmt in self.fix_booked_hours_deviation_stmts:
            self.format_modify_and_log(stmt,
                                       precision=self.rounding_precision,
                                       resources=booked_hours_deviations,
                                       nullsafe=self.nullsafe[self.DBMS])


    def check_utilization(self):
        self.log_heading('Searching for resource periods with deviating utilization.')
        utilization_deviations = self.format_select_and_log(self.check_utilization_deviation_stmt,
                                                            precision=self.rounding_precision,
                                                            div_by_zero=self.div_by_zero,
                                                            epsilon=self.epsilon)
        if self.debug and len(utilization_deviations):
            log_template = '| {:^20} ' * 13 + ' |'
            self.log_heading('Deviations of utilization within resource periods found!')
            log_line = log_template.format('resource_id',

                                           'used',
                                           'planned',
                                           'util_expected',
                                           'util',

                                           'avail',
                                           'util_diff_expected',
                                           'util_diff',

                                           'util_ratio',

                                           'used_aggr',
                                           'planned_aggr',
                                           'util_aggr_expected',
                                           'util_aggr')
            self.log(log_line)

            log_template = '| {:20} ' + '| {:20.10} ' * 12 + ' |'
            last_resource_id = None
            for deviation in utilization_deviations:
                log_line = log_template.format(*deviation)
                resource_id = deviation[0]
                if last_resource_id != resource_id:
                    last_resource_id = resource_id
                    self.log(log_line)

        return frozenset(deviation[0] for deviation in utilization_deviations)  # eliminate duplicates


    def fix_utilization(self, utilization_deviations):
        utilization_deviations = list(utilization_deviations)
        utilization_deviations.sort()
        self.log_heading('Fixing utilization deviations in period table for resources {!s}'.format(utilization_deviations))
        self.fix_in_chunks(self.fix_utilization_chunk, utilization_deviations)


    def fix_utilization_chunk(self, utilization_deviations):
        self.fix_own_utilization(utilization_deviations)
        self.fix_aggregated_utilization(utilization_deviations)
        self.fix_utilization_difference(utilization_deviations)
        self.fix_utilization_ratio(utilization_deviations)

    def fix_own_utilization(self, utilization_deviations):
        self.log('Own utilization:')
        self.format_modify_and_log(self.fix_own_utilization_stmt,
                                   precision=self.rounding_precision,
                                   resources=utilization_deviations)

    def fix_aggregated_utilization(self, utilization_deviations):
        self.log('Aggregated utilization:')
        self.format_modify_and_log(self.fix_aggregated_utilization_stmt,
                                   precision=self.rounding_precision,
                                   resources=utilization_deviations)

    def fix_utilization_difference(self, utilization_deviations):
        self.log('Utilization difference:')
        self.format_modify_and_log(self.fix_utilization_diff_stmt,
                                   precision=self.rounding_precision,
                                   resources=utilization_deviations)

    def fix_utilization_ratio(self, utilization_deviations):
        self.log('Utilization ratio:')
        for stmt in self.fix_utilization_ratio_stmts:
            self.format_modify_and_log(stmt,
                                       precision=self.rounding_precision,
                                       resources=utilization_deviations,
                                       div_by_zero=self.div_by_zero,
                                       epsilon=self.epsilon)


    def check_period_load_deviations(self):
        self.log_heading('Searching for resources with deviating planned effort between periods and loads.')
        load_period_deviations = self.format_select_and_log(self.check_period_load_deviation_stmt,
                                                            precision=self.rounding_precision,
                                                            nullsafe=self.nullsafe[self.DBMS])
        if self.debug and len(load_period_deviations):
            log_template = '| {:^20} ' * 3 + ' |'
            self.log_heading('Deviations in reconciled planned effort between periods and loads found!')
            log_line = log_template.format('resource_id', 'load', 'period')
            self.log(log_line)

            log_template = '| {:20} ' + '| {:20.10} ' * 2 + ' |'
            last_resource_id = None
            for deviation in load_period_deviations:
                log_line = log_template.format(*deviation)
                resource_id = deviation[0]
                if last_resource_id != resource_id:
                    last_resource_id = resource_id
                    self.log(log_line)
        return frozenset(deviation[0] for deviation in load_period_deviations)  # eliminate duplicates

    def fix_period_load_deviations(self, load_period_deviations):
        load_period_deviations = list(load_period_deviations)
        load_period_deviations.sort()
        self.log_heading('Fixing load vs. period deviations of planned effort for resources {!s}'.format(load_period_deviations))
        self.fix_in_chunks(self.fix_period_load_deviations_chunk, load_period_deviations)


    def fix_period_load_deviations_chunk(self, load_period_deviations):
        self.format_modify_and_log(self.fix_deviating_loads_stmt,
                                   precision=self.rounding_precision,
                                   resources=load_period_deviations)
        self.format_modify_and_log(self.fix_deviating_periods_stmt,
                                   precision=self.rounding_precision,
                                   resources=load_period_deviations)


    def fix_in_chunks(self, fix_method, deviations):
        chunk_size = 512
        split = range(0, len(deviations), chunk_size)
        chunks = [deviations[x:x+chunk_size] for x in split]
        for c in chunks:
            in_list = self.get_in_list(c)
            fix_method(in_list)


    def get_in_list(self, values):
        values = tuple(values)
        if len(values) > 1:
            return str(values)
        else:
            return '''('{}')'''.format(values[0])


    def reschedule_all_projects(self):
        self.log_heading('Performing rescheduling.')
        projects = [row[0] for row in self.format_select_and_log('select di001001 from dt461 where di001042 = 1 order by di001015, di001020, di041317')]
        reschedule(projects)


    def aggregate_all_periods(self, fixed_resources):
        resources = list(fixed_resources)
        resources.sort()
        self.log_heading('Performing resource period aggregation for {} resources: {!s}.'.format(len(resources), resources))

        resources_to_aggregate = set(fixed_resources)

        # Only call the period aggregation for the lowest level children
        # as those will aggregate to the top anyway
        for resource_id in fixed_resources:
            child_resources = get_all_ressources_of_department(resource_id, skip_departments=False)

            for child_resource in child_resources:
                if child_resource in resources_to_aggregate:
                    resources_to_aggregate.remove(resource_id)
                    break

        resources = list(resources_to_aggregate)
        resources.sort()
        self.log_heading('Reduced resource period aggregation to {} resources: {!s}.'.format(len(resources), resources))

        for index, resource_id in enumerate(resources_to_aggregate):
            status_update = 'Working on resource %d of %d' % (index + 1, len(resources_to_aggregate))
            log_write(LogLevel.INFO, ComponentID.PY_PLANTA_DE, "UpdatePeriodAggregationQuery", -1, 'operative', '',
                      status_update)

            aggregate_periods_from_start_to_end(resource_id)

        self.log('Done with the aggregation.')


    def format_select_and_log(self, template, **kwargs):
        stmt = template.format(**kwargs)
        self.log('Executing query "\n{}\n"'.format(stmt))
        return ppms.db_select(stmt)


    def format_modify_and_log(self, template, **kwargs):
        stmt = template.format(**kwargs)
        return self.modify_and_log(stmt)


    check_utilization_deviation_stmt = '''
select
    di001326 resource_id,

    di001346 used,
    di001352 planned,
    di001346 + di001352 util_expected,
    di001345 util,

    di001337 avail,
    di001337 - di001345 util_diff_expected,
    di001362 util_diff,

    di001345 * 100.0 / (di001337 + {div_by_zero}) util_ratio_by_formula,
    di001363 util_ratio,

    di002464 used_aggr,
    di002483 planned_aggr,
    di002464 + di002483 util_aggr_expected,
    di002455 util_aggr

from dt468 period
where
    round(di001345, {precision}) <> round(di001346 + di001352, {precision}) or  -- own: utilization != used + planned
    round(di002455, {precision}) <> round(di002464 + di002483, {precision}) or  -- aggregated: utilization != used + planned

    round(di001362, {precision}) <> round(di001337 - di001345, {precision}) or  -- utilization difference != availability (own) - utilization (own)


    (di001345 = 0.0 and round(di001363, {precision}) <> 0.0) or

    ((di001364 = 0 or di001337 <= 0.0) and di001345 > 0.0 and round(di001363, {precision}) <> 999.9) or
    ((di001364 = 0 or di001337 <= 0.0) and di001345 < 0.0 and round(di001363, {precision}) <> -999.9) or

    (di001364 = 1 and di001337 > 0.0 and di001345 <> 0.0 and round(di001363, {precision}) <> 999.9 and round(di001363, {precision}) <> -999.9 and not round(di001363, {precision}) between (round(di001345 * 100.0 / (di001337 + {div_by_zero}), {precision}) - {epsilon}) and (round(di001345 * 100.0 / (di001337 + {div_by_zero}), {precision}) + {epsilon})) or


    round(di001363, {precision}) > 999.9 or
    round(di001363, {precision}) < -999.9

order by di001326, di001327
'''


    fix_own_utilization_stmt = '''
update dt468 set
    di001345 = di001346+di001352  -- own: utilization = used + planned
where
    di001326 in {resources!s} and
    round(di001345, {precision}) <> round(di001346 + di001352, {precision})
'''

    fix_aggregated_utilization_stmt = '''
update dt468 set
    di002455 = di002464+di002483  -- aggregated: utilization = used + planned
where
    di001326 in {resources!s} and
    round(di002455, {precision}) <> round(di002464 + di002483, {precision})
'''


    fix_utilization_diff_stmt = '''
update dt468 set
    di001362 = di001337 - di001345  -- utilization difference = availability (own) - utilization (own)
where
    di001326 in {resources!s} and

    round(di001362, {precision}) <> round(di001337 - di001345, {precision})
'''


    fix_utilization_ratio_stmts = ['''
-- utilization == 0 => ratio := 0
update dt468 set
    di001363 = 0.0      -- utilization ratio (%)
where
    di001326 in {resources!s} and

    di001345 = 0.0 and  -- utilization (own)

    round(di001363, {precision}) <> 0.0
''',

        '''
-- not a workday or no availability, and positive utilization => ratio := 999.9 (max value)
update dt468 set
    di001363 = 999.9      -- utilization ratio (%)
where
    di001326 in {resources!s} and

   (di001364 = 0 or       -- workday (boolean)
    di001337 <= 0.0) and  -- availability (own)
    di001345 > 0.0 and    -- utilization (own)

    round(di001363, {precision}) <> 999.9
''', '''
-- not a workday or no availability, and negative utilization => ratio := -999.9 (min value)
update dt468 set
    di001363 = -999.9     -- utilization ratio (%)
where
    di001326 in {resources!s} and

   (di001364 = 0 or       -- workday (boolean)
    di001337 <= 0.0) and  -- availability (own)
    di001345 < 0.0 and    -- utilization (own)

    round(di001363, {precision}) <> -999.9
''',

        '''
-- workday and positive availability => apply percentage formula
update dt468 set
    di001363 = di001345 * 100.0 / (di001337 + {div_by_zero})  -- utilization ratio (%) = utilization (own) * 100 / availability (own)
where
    di001326 in {resources!s} and

    di001364 = 1 and     -- workday (boolean)
    di001337 > 0.0 and   -- availability (own)
    di001345 <> 0.0 and  -- utilization (own)

    di001363 <> 999.9 and
    di001363 <> -999.9 and
    not round(di001363, {precision}) between (round(di001345 * 100.0 / (di001337 + {div_by_zero}), {precision}) - {epsilon}) and (round(di001345 * 100.0 / (di001337 + {div_by_zero}), {precision}) + {epsilon})
''',

        '''
update dt468 set
    di001363 = 999.9    -- utilization ratio (%)
where
    di001326 in {resources!s} and

    round(di001363, {precision}) > 999.9
''', '''
update dt468 set
    di001363 = -999.9    -- utilization ratio (%)
where
    di001326 in {resources!s} and

    round(di001363, {precision}) < -999.9
''']


    check_period_load_deviation_stmt = '''
select
    loads.resource_id,
    loads.reconciled_remaining,
    periods.planned
from (
    select
        p.di001326 resource_id,
        {nullsafe}(sum(p.di001352), 0) planned
    from dt468 p
    inner join dt467 r on p.di001326 = r.di001218  -- resource id
    where p.di001327 between r.di001230 and r.di001231  -- period date between resource start and end date
    group by p.di001326  -- resource id
) periods
left join (
    select
        l.di001517 resource_id,
        {nullsafe}(sum(l.di001457), 0) reconciled_remaining
    from dt472 l
    inner join dt461 project on project.di001001 = l.di001395  -- project id
    inner join dt467 r on l.di001517 = r.di001218  -- resource id
    where project.di001042 = 1  -- status "active"; include planned load only from active projects
    and l.di001460 between r.di001230 and r.di001231  -- load reconciliation date between resource start and end date
    group by l.di001517  -- resource id
) loads
on periods.resource_id = loads.resource_id
where round(planned, {precision}) <> round(reconciled_remaining, {precision})
order by loads.resource_id
'''


    fix_deviating_loads_stmt = '''
update dt472 set
    di001457 = 0  -- reconciled remaining effort
where di001517 in {resources!s}
'''


    fix_deviating_periods_stmt = '''
update dt468 set
    di001352 = 0,  -- planned effort
    di001353 = 0,  -- planned effort code a
    di001354 = 0,  -- planned effort code b
    di001355 = 0,  -- planned effort code c
    di001356 = 0,  -- planned effort code d
    di001357 = 0   -- planned effort code e
where di001326 in {resources!s}
'''


    check_booked_hours_deviation_stmt = '''
select
    loads.resource_id,
    loads.l_date,
    loads.reconciled_actual,
    periods.used
from (
    select
        p.di001326 resource_id,
        p.di001327 p_date,
        {nullsafe}(p.di001346, 0) used
    from dt468 p
    inner join dt467 r on p.di001326 = r.di001218  -- resource id
    where p.di001327 between r.di001230 and r.di001231  -- period date between resource start and end date
    -- order by p.di001326, p.di001327  -- resource id, period
) periods
left join (
    select
        l.di001517 resource_id,
        l.di001460 l_date,
        {nullsafe}(sum(l.di001456), 0) reconciled_actual
    from dt472 l
    inner join dt467 r on l.di001517 = r.di001218  -- resource id
    where l.di001460 between r.di001230 and r.di001231  -- load reconciliation date between resource start and end date
    group by l.di001517, l.di001460  -- resource id, reconciliation date
    -- order by l.di001517, l.di001460
) loads
on periods.resource_id = loads.resource_id and periods.p_date = loads.l_date
where round(used, {precision}) <> round(reconciled_actual, {precision})
order by loads.resource_id, loads.l_date
'''

    fix_booked_hours_deviation_stmts = ['''
update dt468
set di001346 = (  -- used
    select {nullsafe}(sum(di001456), 0)  -- reconciled actual effort
    from dt472 l
    --inner join dt467 r on l.di001517 = r.di001218  -- resource id
    where l.di001517 = di001326  -- resource id
    and   l.di001460 = di001327  -- date
    --and l.di001460 between r.di001230 and r.di001231  -- load reconciliation date between resource start and end date
    group by l.di001517, l.di001460  -- resource id, reconciliation date
)
where di001326 in {resources!s}
and round(di001346, {precision}) <> (
    select round({nullsafe}(sum(di001456), 0), {precision})  -- reconciled actual effort
    from dt472 l
    --inner join dt467 r on l.di001517 = r.di001218  -- resource id
    where l.di001517 = di001326  -- resource id
    and   l.di001460 = di001327  -- date
    --and l.di001460 between r.di001230 and r.di001231  -- load reconciliation date between resource start and end date
    group by l.di001517, l.di001460  -- resource id, reconciliation date
)
and exists (  -- split on non-zero value
    select * from dt472 l
    where l.di001517 = di001326  -- resource id
    and   l.di001460 = di001327  -- date
    and   {nullsafe}(l.di001456, 0) <> 0  -- reconciled actual effort
)
''', '''
update dt468
set di001346 = 0  -- used
where di001326 in {resources!s}
and di001346 <> 0
and NOT exists (  -- split on non-zero value
    select * from dt472 l
    where l.di001517 = di001326  -- resource id
    and   l.di001460 = di001327  -- date
    and   {nullsafe}(l.di001456, 0) <> 0)  -- reconciled actual effort
''']
