# -*- coding: utf-8 -*-
from ppms import ppms
from ppms.constants import *
from ppms.migration import SQLPacket, PacketCategory, MaxVersionDependency, \
    MinVersionDependency, VersionType, PacketDependency, PacketState, BasePacket

class FixReportChanceCalculation(SQLPacket):
    '''
    - Adds a missing column in DT873 Chancenmaßnahmen/Bericht which
    is needed for the chance calculation in reports.
    - Fixes values of DI028291 "probable damage" in existing project status
    reports'''
    done_after_success = True
    run_on_startup = True
    category = PacketCategory.CATEGORY_MANDATORY
    wi_number = 16768

    add_di_ora = "alter table DT873 add COSTS_IN_PR number(5,0) default 0"
    add_di_mssql = "alter table DT873 add COSTS_IN_PR numeric(5,0) default 0"

    fix_prob_dmg = {DBMS_ORACLE:"""update dt823 set (
       di028291 -- probable damage
       ) = (select
                 nvl(sum(case when risk_active = 0
                 then 0
                 when (perc_damage - perc_reduce) <= 0
                 then reducing_costs
                 when (perc_damage - perc_reduce) > 0
                 then ((perc_damage - perc_reduce) * 0.01) * possible_damage + reducing_costs
                 else 0 end),0) total_prob_damage
             FROM (SELECT
                   DI008413, DI008416, DI008414, DI008578, DI008877 possible_damage,
                   DI008878 perc_damage, DI028285, DI008879 risk_active, DI008863,
                   nvl(sum(decode(DI028288, 3, 0, DI028286)),0) perc_reduce,
                   nvl(sum(decode(DI028288, 3, 0, DI028287)),0) reducing_costs
                   FROM dt831
                      INNER JOIN dt823 ON (DT831.DI008862 = DT823.DI008413
                                       and DT831.DI008883 = DT823.DI008416)
                      LEFT OUTER JOIN dt832 ON (DI008862 = DI008885
                                            and DI008907 = DI008416
                                            and DI008863 = DI008886)
                   WHERE DT823.DI008413 in {sub_pr_tuple}
                   and DT823.DI008416 = '{report_id}'
                   GROUP BY DI008413, DI008416, DI008414, DI008578, DI008877,
                            DI008878, DI008879, DI028285, DI008863
                   )
               group by DI008578)
      where  DT823.DI008413 = '{pr_id}'
      and DT823.DI008416 = '{report_id}'
      and exists (select * from dt831
                    where DT831.DI008862 in {sub_pr_tuple}
                    and DT831.DI008883 = DT823.DI008416)""",
    DBMS_MSSQL: """update dt823 set
        di028291 = source_table.total_prob_damage
        from (
            select
                isnull(
                    sum(
                        case when risk_active = 0 then 0
                        when (perc_damage - perc_reduce) <= 0 then reducing_costs
                        when (perc_damage - perc_reduce) > 0 then
                        ((perc_damage - perc_reduce) * 0.01) * possible_damage + reducing_costs
                        else 0
                        end
                        ),
                    0) total_prob_damage
            FROM (
                SELECT
                    DI008413, DI008416, DI008414, DI008578, DI008877 possible_damage,
                    DI008878 perc_damage, DI028285, DI008879 risk_active, DI008863,
                    perc_reduce = isnull(sum(case when DI028288 = 3 then 0 else DI028286 end),0),
                    reducing_costs = isnull(sum(case when  DI028288 = 3 then 0 else DI028287 end),0)
                FROM dt831
                INNER JOIN dt823 ON (DT831.DI008862 = DT823.DI008413
                                 and DT831.DI008883 = DT823.DI008416)
                LEFT OUTER JOIN dt832 ON (DI008862 = DI008885
                                      and DI008907 = DI008416
                                      and DI008863 = DI008886)
                WHERE DT823.DI008413 in {sub_pr_tuple}
                 and DT823.DI008416 = '{report_id}'
                GROUP BY DI008413, DI008416, DI008414, DI008578, DI008877,
                         DI008878, DI008879, DI028285, DI008863
                ) source_table
            group by DI008578
            ) source_table
          where  DT823.DI008413 = '{pr_id}'
              and DT823.DI008416 = '{report_id}'
          and exists (select * from dt831
                      where DT831.DI008862 in {sub_pr_tuple}
                        and DT831.DI008883 = DT823.DI008416)"""}

    def get_dependencies(self):
        return (MinVersionDependency(version='39.5.6' ,
                                     version_type=VersionType.CUSTOMIZING),
                MaxVersionDependency(version='39.5.6' ,
                                     version_type=VersionType.CUSTOMIZING))

    def get_pr_tree_desc(self, proj_id):
        # returns descending list of child projects + proj_id itself
        pr_select = "Select di001001 From Dt461 Where di001003 = '{0}'"
        subprojects = ppms.db_select(pr_select.format(proj_id))
        scanned_projects = [proj_id]
        for project in subprojects:
            for next_subproj in ppms.db_select(pr_select.format(project[0])):
                if next_subproj not in subprojects:
                    subprojects.append(next_subproj)
            scanned_projects.append(project[0])

        # return tuple-like str to use it in sql select
        scanned_projects = tuple(scanned_projects)
        if len(scanned_projects) == 1:
            scanned_projects = (scanned_projects[0], scanned_projects[0])
        return scanned_projects

    def fix(self, runner):
        # add COSTS_IN_PR column to DT873
        dbms_name = ppms.db_get_dbms_name()
        if dbms_name == DBMS_ORACLE:
            self.modify_and_log(self.add_di_ora)
        elif dbms_name == DBMS_MSSQL:
            self.modify_and_log(self.add_di_mssql)

        # fix values in DI028291 "probable damage" for all status reports
        reports = ppms.db_select("""select DI008413, DI008416
                                    from DT823""")
        self.log('Processing project status reports: ' + str(reports))
        for report_rec in reports:
             pr_id = report_rec[0]
             sub_pr_tuple = self.get_pr_tree_desc(pr_id)
             report_id = report_rec[1]
             self.modify_and_log(self.fix_prob_dmg[dbms_name].format(**locals()))

        return self.success()